Outline Finish E/R diagrams (Chapter 2) Lecture 08: And E/R - PDF document

Outline • Finish E/R diagrams (Chapter 2) Lecture 08: – And E/R diagrams to relations (3.2, 3.3) • The relational data model: 3.1 • Functional dependencies: 3.4 Wednesday, October 16, 2002 1 2 Product Subclasses to Relations Difference between ODL and Name Price Category E/R inheritance Gizmo 99 gadget Camera 49 photo • ODL: classes are disjoint name category Toy 39 gadget price Product Sw.Product Product Name platforms p2 p1 ep1 p3 Gizmo unix sp1 EducationalProduct isa isa ep2 Ed.Product SoftwareProduct sp2 ep3 Name Age Group Software Product Educational Product Gizmo todler platforms Age Group Toy retired 3 4 Difference between ODL and E/R inheritance • No need for multiple inheritance in E/R • E/R: entity sets overlap Product p2 p1 Product ep1 p3 sp1 EducationalProduct p2 p1 ep2 ep1 SoftwareProduct p3 sp2 esp2 ep3 sp1 EducationalProduct esp1 ep2 SoftwareProduct • we have three entity sets, but four different kinds sp2 ep3 of objects • Still needed if we want extra attributes 5 6 1

Modeling Subclass Structure Modeling UnionTypes With Subclasses Product ageGroup Platforms FurniturePiece topic required memory isa isa Educational Company Software Person Product Product isa isa Say: each piece of furniture is owned either by a person, or by a company Educational-method Educ-software Product 7 8 Modeling Union Types with Modeling Union Types with Subclasses Subclasses Say: each piece of furniture is owned either by a Solution 2: better, more laborious person, or by a company Owner Solution 1. Acceptable, imperfect (What’s wrong ?) isa isa Person FurniturePiece Company ownedBy Person Company ownedByPerson ownedByPerson FurniturePiece 9 10 Keys in E/R Diagrams Constraints in E/R Diagrams Finding constraints is part of the modeling process. name category Commonly used constraints: Underline: price Keys: social security number uniquely identifies a person. Product No formal way Single-value constraints: a person can have only one father. to specify multiple keys in E/R diagrams Referential integrity constraints: if you work for a company, it must exist in the database. Person Other constraints: peoples’ ages are between 0 and 150. name ssn address 11 12 2

Single Value Constraints Referential Integrity Constraints makes Product Company makes v. s. makes Product Company makes 13 14 Other Constraints Weak Entity Sets Entity sets are weak when their key comes from other classes to which they are related. <100 makes Product Company affiliation Team University What does this mean ? sport number name 15 16 Handling Weak Entity Sets The Relational Data Model affiliation Team University Relational Physical Data Relational Physical Data Schema storage Modeling Schema storage Modeling sport number name Convert to a relational schema (in class) Complex E/R diagrams Tables: file organization column names: attributes and index rows: tuples structures. 17 18 3

Terminology Table name or relation name Schemas Attribute names Products: Name Price Category Manufacturer Relational Schema: – Relation name plus attribute names – E.g. Product(Name, Price, Category, Manufacturer) gizmo $19.99 gadgets GizmoWorks – In practice we add the domain for each attribute Power gizmo $29.99 gadgets GizmoWorks Database Schema SingleTouch $149.99 photography Canon – Set of relational schemas – E.g. Product(Name, Price, Category, Manufacturer), MultiTouch $203.99 household Hitachi Company(Name, Address, Phone), . . . . . . . Tuples or rows or records This is all mathematics, not to be confused with SQL tables ! 19 20 Instances Example Relational schema: Product(Name, Price, Category, Manufacturer) Instance: • Relational schema = R(A 1 ,…,A k ): Instance = relation with k attributes (of “type” R) Name Price Category Manufacturer – values of corresponding domains gizmo $19.99 gadgets GizmoWorks • Database schema = R 1 (…), R 2 (…), …, R n (…) Instance = n relations, of types R 1 , R 2 , ..., R n Power gizmo $29.99 gadgets GizmoWorks SingleTouch $149.99 photography Canon MultiTouch $203.99 household Hitachi 21 22 First Normal Form (1NF) Functional Dependencies • A database schema is in First Normal Form • A form of constraint Student if all tables are flat – hence, part of the schema Student Name GPA Alice 3.8 • Finding them is part of the database design Bob 3.7 Name GPA Courses • Also used in normalizing the relations Carol 3.9 Math Takes Course Alice 3.8 DB Student Course OS Alice Math Course DB Carol Math Math Bob 3.7 OS Alice DB DB Bob DB OS Math Carol 3.9 Alice OS OS 23 24 Carol OS 4

Functional Dependencies Examples Definition: EmpID Name Phone Position E0045 Smith 1234 Clerk If two tuples agree on the attributes E1847 John 9876 Salesrep E1111 Smith 9876 Salesrep A , A , … A 1 2 n E9999 Mary 1234 Lawyer then they must also agree on the attributes B , B , … B 1 2 m • EmpID Name, Phone, Position Formally: A , A , … A B , B , … B 1 2 n 1 2 m • Position Phone • but Phone Position 25 26 In General Example • To check A � B, erase all other columns EmpID Name Phone Position … A … B E0045 Smith 1234 Clerk X1 Y1 E1847 John 9876 Salesrep X2 Y2 E1111 Smith 9876 Salesrep … … E9999 Mary 1234 Lawyer • check if the remaining relation is many-one (called functional in mathematics) Note: this is the mathematical definition of a function. Book is wrong. 27 28 Typical Examples of FDs In Class: Find All FDs Product: name � price, manufacturer Student Dept Course Room Alice CSE C++ 020 ssn � name, age Bob CSE C++ 020 Person: Do all FDs Alice EE HW 040 make sense Carol CSE DB 045 in practice ? Company: name � stockprice, president Dan CSE Java 050 Elsa CSE DB 045 Frank EE Circuits 020 29 30 5

Formal definition of a key Examples of Keys • A key is a set of attributes A 1 , ..., A n s.t. for • Product(name, price, category, color) name, category � price any other attribute B, A 1 , ..., A n � B category � color Keys are: {name, category} and all supersets • A minimal key is a set of attributes which • Enrollment(student, address, course, room, time) is a key and for which no subset is a key student � address room, time � course student, course � room, time • Note: book calls them superkey and key Keys are: [in class] 31 32 Finding the Keys Finding the Keys of a Relation Rules: 2. If the relation comes from a many-many relationship, the key of the relation is the set of all attribute keys in the Given a relation constructed from an E/R diagram, what is its key? relations corresponding to the entity sets Rules: 1. If the relation comes from an entity set, name the key of the relation is the set of attributes which is the Person buys Product key of the entity set. price name ssn date Person( address, name, ssn ) Person name ssn address buys (name, ssn, date) 33 34 Finding the Keys Finding the Keys Except: if there is an arrow from the relationship to E, then we don’t need the key of E as part of the relation key. More rules: sname Product • Many-one, one-many, one-one relationships name • Multi-way relationships Purchase Store • Weak entity sets card-no Payment Method ssn Person (Try to find them yourself, or check book) Purchase (name , sname, ssn, card-no) 35 36 6