Lecture 07 Conceptual Database Design Building an Application

Lecture 07: Conceptual Database Design

Building an Application with a DBMS 1. Requirements modeling (conceptual) – Decide what entities should be part of the application and how they should be linked. 2. Schema design and implementation – Decide on a set of tables, attributes. – Define the tables in the database system. – Populate database (insert tuples). 3. Write application programs using the DBMS – Way easier now that the data management is taken care of.

Database Design • Why do we need it? – Agree on structure of the database before deciding on a particular implementation. • Consider issues such as: – What entities to model – How entities are related – What constraints exist in the domain – How to achieve good designs

Database Design Formalisms 1. Object Definition Language (ODL): – Closer in spirit to object-oriented models – Not in the scope of this course 2. Entity/Relationship model (E/R): – More relational in nature. – Related to the EER modeling in the My. SQL workbench but we use different conventions here! • Both can be translated to relational schemas

Entity / Relationship Diagrams Entity sets Product Attributes address Relationships between entities buys

Keys in E/R Diagrams • Every entity set must have a key name price Product category

name category stockprice name price Product Company makes buys employs Person address name ssn

What is a Relation ? • A mathematical definition: – if A, B are sets, then a relation R is a subset of A B 1 a • A={1, 2, 3}, B={a, b, c, d}, R = { 1, a , 1, c , 3, b } A= 2 3 b c d - makes is a subset of Product Company: Product makes Company =B

Multiplicity of E/R Relations • one-one: a b c d 1 2 3 • many-one 1 2 3 • many-many 1 2 3 a b c d

name category stockprice name price Product buys Company makes What does it mean? employs Person address name ssn

Multi-way Relationships How do we model a purchase relationship between buyers, products and stores? Product Store Purchase Person Can still model as a mathematical set (how? )

Arrows in Multiway Relationships Q: what does the arrow mean ? Role Movie Plays Salary Actor A: if I know the movie, role and actor, I know the salary too

Arrows in Multiway Relationships Q: what do these arrow mean ? Invoice Video. Store Rental Movie Person A: store, person, invoice determines movie and store, invoice, movie determines person

Arrows in Multiway Relationships Q: how do I say: “invoice determines store” ? A: no good way; best approximation: Invoice Video. Store Rental Person Q: Why is this incomplete ? Movie

Roles in Relationships What if we need an entity set twice in one relationship? Product Purchase seller Person buyer Store

Attributes on Relationships date Product Purchase Person Store

Converting Multi-way Relationships to Binary date Purchase Product. Of Product Store. Of Store Buyer. Of Person

From E/R Diagrams to Relational Schema • Entity relation • Relationship relation

Entity Set to Relation name category price Product(name, category, price) name category price gizmo gadgets $19. 99

Relationships to Relations price name category Start Year makes name Company Product Stock price Makes(product-name, product-category, company-name, year) Product-name Product-Category Company-name Starting-year gizmo gadgets gizmo. Works (watch out for attribute name conflicts) 1963

Relationships to Relations price name category Start Year makes name Company Product Stock price No need for Makes. Modify Product: name category price gizmo gadgets 19. 99 Start. Year company. Name 1963 gizmo. Works

Multi-way Relationships to Relations name Product name Purchase price address Store Person Purchase( ssn name , , )

3. Design Principles What’s wrong? Product Purchase Person Country President Person

Design Principles: What’s Wrong? date Product Purchase Store Moral: pick the right kind of entities. person. Addr person. Name

Design Principles: What’s Wrong? date Dates Product Purchase Moral: don’t complicate life more than it already is. Person Store