The Relational Data Model Lecture 6 1 Outline Relational Data - PDF document

The Relational Data Model Lecture 6 1 Outline • Relational Data Model • Functional Dependencies • Logical Schema Design Reading Chapter 8 2 1

The Relational Data Model Relational Physical Data Schema storage Modeling Have seen this in SQL Complex E/R diagrams Tables: file organization column names: attributes and index rows: tuples structures. Have seen Discuss next this too 3 Terminology Table name or relation name Attribute names Products: Name Price Category Manufacturer gizmo $19.99 gadgets GizmoWorks Power gizmo $29.99 gadgets GizmoWorks SingleTouch $149.99 photography Canon MultiTouch $203.99 household Hitachi Tuples or rows or records 4 2

Schemas Relational Schema: – Relation name plus attribute names – E.g. Product(Name, Price, Category, Manufacturer) – In practice we add the domain for each attribute Database Schema – Set of relational schemas – E.g. Product(Name, Price, Category, Manufacturer), Company(Name, Address, Phone), . . . . . . . 5 Instances • Relational schema = R(A 1 ,…,A k ) Instance = relation with k attributes (of “type” R) – values of corresponding domains • Database schema = R 1 (…), R 2 (…), …, R n (…) Instance = n relations, of types R 1 , R 2 , ..., R n This is all mathematics, not to be confused with SQL tables ! (What's a difference?) 6 3

Example Relational schema: Product(Name, Price, Category, Manufacturer) Instance: Name Price Category Manufacturer gizmo $19.99 gadgets GizmoWorks Power gizmo $29.99 gadgets GizmoWorks SingleTouch $149.99 photography Canon MultiTouch $203.99 household Hitachi 7 First Normal Form (1NF) • A database schema is in First Normal Student Form if all tables are flat Student Name GPA Alice 3.8 Bob 3.7 Name GPA Courses 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 May need Bob DB OS to add keys Math Carol 3.9 Alice OS 8 OS Carol OS 4

Functional Dependencies • A form of constraint – hence, part of the schema • Finding them is part of the database design • Also used in normalizing the relations • Warning: this is the most abstract, and “hardest” part of the course. 9 Functional Dependencies Definition: If two tuples agree on the attributes A 1 , A 2 , …, A n then they must also agree on the attributes B 1 , B 2 , …, B m Formally: A 1 , A 2 , …, A n  B 1 , B 2 , …, B m 10 5

Examples EmpID Name Phone Position E0045 Smith 1234 Clerk E1847 John 9876 Salesrep E1111 Smith 9876 Salesrep E9999 Mary 1234 Lawyer • EmpID  Name, Phone, Position • Position  Phone • but Phone  Position 11 In General • To check A  B, erase all other columns … A … B X1 Y1 X2 Y2 … … • check if the remaining relation is many-one (called functional in mathematics) 12 6

Example EmpID Name Phone Position E0045 Smith 1234 Clerk E1847 John 9876 Salesrep E1111 Smith 9876 Salesrep E9999 Mary 1234 Lawyer Position  Phone 13 Typical Examples of FDs Product: name  price, manufacturer Person: ssn  name, age Company: name  stockprice, president 14 7

Example Product(name, category, color, department, price) Consider these FDs: name  color category  department color, category  price What do they say ? 15 Example FD’s are constraints on relations: name  color • On some instances they hold category  department • On others they don’t color, category  price name category color department price Gizmo Gadget Green Toys 49 Tweaker Gadget Green Toys 99 Does this instance satisfy all the FDs ? 16 8

Example name  color category  department color, category  price name category color department price Gizmo Gadget Green Toys 49 Tweaker Gadget Black Toys 99 Gizmo Stationary Green Office-supp. 59 What about this one ? 17 Example If some FDs are satisfied, then others are satisfied too name  color category  department If all these FDs are true: color, category  price name, category  price Then this FD also holds: Why ?? 18 9

Inference Rules for FD’s A 1 , A 2 , …, A n  B 1 , B 2 , …, B m Splitting rule and Is equivalent to Combining rule A1 ... Am B1 ... Bm A 1 , A 2 , …, A n  B 1 A 1 , A 2 , …, A n  B 2 . . . . . A 1 , A 2 , …, A n  B m 19 Inference Rules for FD’s (continued) Trivial Rule A 1 , A 2 , …, A n  A i where i = 1, 2, ..., n A 1 … A m Why ? 20 10

Inference Rules for FD’s (continued) Transitive Closure Rule A 1 , A 2 , …, A n  B 1 , B 2 , …, B m If and B 1 , B 2 , …, B m  C 1 , C 2 , …, C p A 1 , A 2 , …, A n  C 1 , C 2 , …, C p then Why ? 21 A 1 … A m B 1 … B m C 1 ... C p 22 11

Example (continued) 1. name  color Start from the following FDs: 2. category  department 3. color, category  price Infer the following FDs: Which Rule Inferred FD did we apply ? 4. name, category  name 5. name, category  color 6. name, category  category 7. name, category  color, category 8. name, category  price 23 Example (continued) 1. name  color 2. category  department Answers: 3. color, category  price Which Rule Inferred FD did we apply ? 4. name, category  name Trivial rule 5. name, category  color Transitivity on 4, 1 6. name, category  category Trivial rule 7. name, category  color, category Split/combine on 5, 6 8. name, category  price Transitivity on 3, 7 24 12

Another Example • Enrollment(student, major, course, room, time) student  major major, course  room course  time What else can we infer ? 25 Another Rule Augmentation A 1 , A 2 , …, A n  B If then A 1 , A 2 , …, A n , C 1 , C 2 , …, C p  B Augmentation follows from trivial rules and transitivity How ? 26 13

Problem: infer ALL FDs Given a set of FDs, infer all possible FDs How to proceed ? • Try all possible FDs, apply all 3 rules – E.g. R(A, B, C, D): how many FDs are possible ? • Drop trivial FDs, drop augmented FDs – Still way too many • Better: use the Closure Algorithm (next) 27 Closure of a set of Attributes Given a set of attributes A 1 , …, A n The closure , {A 1 , …, A n } + , is the set of attributes B s.t. A 1 , …, A n  B Example: name  color category  department color, category  price Closures: name + = {name, color} {name, category} + = {name, category, color, department, price} color + = {color} 28 14

Closure Algorithm Start with X={A1, …, An}. Example: name  color Repeat until X doesn’t change do : category  department color, category  price if B 1 , …, B n  C is a FD and B 1 , …, B n are all in X {name, category} + = then add C to X. {name, category, color, department, price} 29 Example A, B  C R(A,B,C,D,E,F) A, D  E B  D A, F  B Compute {A,B} + X = {A, B, } Compute {A, F} + X = {A, F, } 30 15

Using Closure to Infer ALL FDs Example: A, B  C A, D  B B  D Step 1: Compute X + , for every X: A+ = A, B+ = BD, C+ = C, D+ = D AB+ = ABCD, AC+ = AC, AD+ = ABCD ABC+ = ABD+ = ACD + = ABCD (no need to compute– why ?) BCD + = BCD, ABCD+ = ABCD Step 2: Enumerate all FD’s X  Y, s.t. Y ⊆ X + and X ∩ Y = ∅ : AB  CD, AD  BC, ABC  D, ABD  C, ACD  B 31 Problem: Finding FDs • Approach 1: During Database Design – Designer derives them from real-world knowledge of users – Problem: knowledge might not be available • Approach 2: From a Database Instance – Analyze given database instance and find all FD’s satisfied by that instance – Useful if designers don’t get enough information from users – Problem: FDs might be artifical for the given instance 32 16

Find All FDs Student Dept Course Room Alice CSE C++ 020 Bob CSE C++ 020 Do all FDs Alice EE HW 040 make sense Carol CSE DB 045 in practice ? Dan CSE Java 050 Elsa CSE DB 045 Frank EE Circuits 020 33 Answer Course  Dept, Room Do all FDs Dept, Room  Course make sense Student, Dept  Course, Room in practice ? Student, Course  Dept, Room Student, Room  Dept, Course 34 17

Keys • A key is a set of attributes A 1 , ..., A n s.t. for any other attribute B, we have A 1 , ..., A n  B • A minimal key is a set of attributes which is a key and for which no subset is a key • Note: book calls them superkey and key 35 Computing Keys • Compute X + for all sets X • If X + = all attributes, then X is a key • List only the minimal keys Note: there can be many minimal keys ! • Example: R(A,B,C), AB  C, BC  A Minimal keys: AB and BC 36 18

Examples of Keys • Product(name, price, category, color) name, category  price category  color Keys are: {name, category} and all supersets • Enrollment(student, address, course, room, time) student  address room, time  course student, course  room, time 37 Relational Schema Design (or Logical Schema Design) Main idea: • Start with some relational schema • Find out its FD’s • Use them to design a better relational schema 38 19