Lecture 9: Conceptual Database Design January 27th, 2003

Lecture 9: Conceptual Database Design January 27th, 2003

Building an Application with a DBMS Requirements modeling (conceptual, pictures) – Decide what entities should be part of the application and how they should be linked. Schema design and implementation – Decide on a set of tables, attributes. – Define the tables in the database system. – Populate database (insert tuples). 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 – I don’t teach it anymore. 2. Entity/Relationship model (E/R): – More relational in nature. Both can be translated (semi-automatically) to relational schemas ODL to OO-schema: direct transformation (C or Smalltalk based system).

2. Entity / Relationship Diagrams Entities 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 name price makes Company Product stockprice 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 AxB 1 a A {1,2,3}, B {a,b,c,d}, R {(1,a), (1,c), (3,b)} A b 2 c 3 B - makes is a subset of Product x Company: Product makes Company d

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 a b c d

name category name price makes Company Product buys stockprice What does this say ? employs Person address name ssn

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

Arrows in Multiway Relationships Q: what does the arrow mean ? Invoice VideoStore Rental Movie Person A: if I know the store, person, invoice, I know the movie too

Arrows in Multiway Relationships Q: what do these arrow mean ? Invoice VideoStore 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 VideoStore Rental Person Q: Why is this incomplete ? Movie

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

Attributes on Relationships date Product Purchase Person Store

Converting Multi-way Relationships to Binary date ProductOf Product StoreOf Store BuyerOf Person Purchase

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

Entity Set to Relation name category price Product 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 gizmoWorks (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 StartYear companyName 1963 gizmoWorks

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

3. Design Principles What’s wrong? Product Country Moral: be faithful! Purchase President Person Person

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

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

Modeling Subclasses The world is inherently hierarchical. Some entities are special cases of others We need a notion of subclass. This is supported naturally in object-oriented formalisms. Products Software products Educational products

Subclasses in E/R Diagrams name category price Product isa Software Product platforms isa Educational Product Age Group

Understanding Subclasses Think in terms of records: – Product – SoftwareProduct field1 field2 field1 field2 field3 – EducationalProduct field1 field2 field4 field5

Product Subclasses to Relations name category price Software Product platforms Price Category Gizmo 99 gadget Camera 49 photo Toy 39 gadget Sw.Product Product isa Name isa Name platforms Gizmo unix Name Age Group Gizmo todler Toy retired Ed.Product Educational Product Age Group

Modeling UnionTypes With Subclasses FurniturePiece Person Company Say: each piece of furniture is owned either by a person, or by a company

Modeling Union Types with Subclasses Say: each piece of furniture is owned either by a person, or by a company Solution 1. Acceptable, imperfect (What’s wrong ?) Person FurniturePiece ownedByPerson Company ownedByPerson

Modeling Union Types with Subclasses Solution 2: better, more laborious Owner isa isa ownedBy Company Person FurniturePiece

Constraints in E/R Diagrams Finding constraints is part of the modeling process. Commonly used constraints: Keys: social security number uniquely identifies a person. Single-value constraints: a person can have only one father. Referential integrity constraints: if you work for a company, it must exist in the database. Other constraints: peoples’ ages are between 0 and 150.

Keys in E/R Diagrams name Underline: category price No formal way to specify multiple keys in E/R diagrams Product Person address name ssn

Single Value Constraints makes v. s. makes

Referential Integrity Constraints Product makes Company Product makes Company

Other Constraints 100 Product makes What does this mean ? Company

Weak Entity Sets Entity sets are weak when their key comes from other classes to which they are related. affiliation Team sport number University name

Handling Weak Entity Sets affiliation Team sport number Convert to a relational schema (in class) University name