Functional Dependencies

1. Functional Dependencies Quiz

2. Project Relation Attributes • project name – pname • start date – start • budget • customer name – cname • customer phone – cphone • customer email – cemail • employee SIN – sin • employee first name – fname • employee last name – lname • employee category - cat (employee's occupation, e.g. welder, electrician) • employee rating – rank (a measure of seniority, a senior electrician might have a rank of 3) • employee hourly rate – rate (charged to customers) • machine ID – mid • machine description – desc • machine model – model • machine weight – weight • machine size – size • machine cost – cost • hours worked on a project by an employee - hours

3. Functional Dependencies • pname • start • budget • cname • cphone • cemail • sin • fname • lname • cat • rank • rate • mid • desc • model • weight • size • cost • hours • pname, cnamestart, budget (one start date and budget) • cname cphone, cemail(a customer has one phone and email) • pname cname(projects are for only one customer) • sin, pname hours (number of hours an employee works on a project) • sin  fname, lname(a customer has one first and one last name) • sin, fname, lname cat, rank, rate (an employee has a categoty, rank and rate) • cat, rank  rate (the rate is derived from category and rank) • mid  desc(each machine has a description) • mid  model, cost (a machine is of a model, and has a cost) • mid, model  weight, size (a machine of a model type has a weight and size) • model  weight, size (weight and size are the same for machines of a model) • mid  pname(machines are only assigned to one project)

4. Armstrong's Axioms • pname • start • budget • cname • cphone • cemail • sin • fname • lname • cat • rank • rate • mid • desc • model • weight • size • cost • hours • pname, cnamestart, budget • cname cphone, cemail • pname cname • sin, pname hours • sin  fname, lname • sin, fname, lname cat, rank, rate • cat, rank  rate • mid  desc • mid  model, cost • mid, model  weight, size • model  weight, size • mid  pname Is mid weight implied by F? Reflexivity – if XY, then XY Augmentation – if XY, then XZYZ Transitivity – if XY, and YZ then XZ Union – if XY and XZ, then XXZ Decomposition – if XYZ, then XY and XZ Pseudotransitivity – if XY and WYZ, then XWZ

5. Armstrong's Axioms • pname • start • budget • cname • cphone • cemail • sin • fname • lname • cat • rank • rate • mid • desc • model • weight • size • cost • hours • pname, cnamestart, budget • cname cphone, cemail • pname cname • sin, pname hours • sin  fname, lname • sin, fname, lname cat, rank, rate • cat, rank  rate • mid  desc • mid  model, cost • mid, model  weight, size • model  weight, size • mid  pname Is mid sin implied by F? Reflexivity – if XY, then XY Augmentation – if XY, then XZYZ Transitivity – if XY, and YZ then XZ Union – if XY and XZ, then XXZ Decomposition – if XYZ, then XY and XZ Pseudotransitivity – if XY and WYZ, then XWZ

6. Armstrong's Axioms • pname • start • budget • cname • cphone • cemail • sin • fname • lname • cat • rank • rate • mid • desc • model • weight • size • cost • hours • pname, cnamestart, budget • cname cphone, cemail • pname cname • sin, pname hours • sin  fname, lname • sin, fname, lname cat, rank, rate • cat, rank  rate • mid  desc • mid  model, cost • mid, model  weight, size • model  weight, size • mid  pname Is sin, mid fname, lname implied by F? Reflexivity – if XY, then XY Augmentation – if XY, then XZYZ Transitivity – if XY, and YZ then XZ Union – if XY and XZ, then XXZ Decomposition – if XYZ, then XY and XZ Pseudotransitivity – if XY and WYZ, then XWZ

7. Armstrong's Axioms • pname • start • budget • cname • cphone • cemail • sin • fname • lname • cat • rank • rate • mid • desc • model • weight • size • cost • hours • pname, cnamestart, budget • cname cphone, cemail • pname cname • sin, pname hours • sin  fname, lname • sin, fname, lname cat, rank, rate • cat, rank  rate • mid  desc • mid  model, cost • mid, model  weight, size • model  weight, size • mid  pname Is sin, mid hours implied by F? Reflexivity – if XY, then XY Augmentation – if XY, then XZYZ Transitivity – if XY, and YZ then XZ Union – if XY and XZ, then XXZ Decomposition – if XYZ, then XY and XZ Pseudotransitivity – if XY and WYZ, then XWZ

8. Attribute Closure • pname • start • budget • cname • cphone • cemail • sin • fname • lname • cat • rank • rate • mid • desc • model • weight • size • cost • hours • pname, cnamestart, budget • cname cphone, cemail • pname cname • sin, pname hours • sin  fname, lname • sin, fname, lname cat, rank, rate • cat, rank  rate • mid  desc • mid  model, cost • mid, model  weight, size • model  weight, size • mid  pname Compute the attribute closure of {sin, mid} Is {sin, mid} a superkey? Compute the canonical cover of F

9. BCNF and Third Normal Form • pname • start • budget • cname • cphone • cemail • sin • fname • lname • cat • rank • rate • mid • desc • model • weight • size • cost • hours • pname, cnamestart, budget • cname cphone, cemail • pname cname • sin, pname hours • sin  fname, lname • sin, fname, lname cat, rank, rate • cat, rank  rate • mid  desc • mid  model, cost • mid, model  weight, size • model  weight, size • mid  pname Is machine = {mid, model, cost, weight, size} in BCNF? 3NF?

10. BCNF and Third Normal Form • pname • start • budget • cname • cphone • cemail • sin • fname • lname • cat • rank • rate • mid • desc • model • weight • size • cost • hours • pname, cnamestart, budget • cname cphone, cemail • pname cname • sin, pname hours • sin  fname, lname • sin, fname, lname cat, rank, rate • cat, rank  rate • mid  desc • mid  model, cost • mid, model  weight, size • model  weight, size • mid  pname Is company = {cname, cphone, cemail} in BCNF? 3NF?

11. FBCNF and Third Normal Form • pname • start • budget • cname • cphone • cemail • sin • fname • lname • cat • rank • rate • mid • desc • model • weight • size • cost • hours • pname, cnamestart, budget • cname cphone, cemail • pname cname • sin, pname hours • sin  fname, lname • sin, fname, lname cat, rank, rate • cat, rank  rate • mid  desc • mid  model, cost • mid, model  weight, size • model  weight, size • mid  pname Is works = {sin, pname, mid, hours} in BCNF? 3NF?

12. Database Characteristics • pname • start • budget • cname • cphone • cemail • sin • fname • lname • cat • rank • rate • mid • desc • model • weight • size • cost • hours • pname, cnamestart, budget • cname cphone, cemail • pname cname • sin, pname hours • sin  fname, lname • sin, fname, lname cat, rank, rate • cat, rank  rate • mid  desc • mid  model, cost • mid, model  weight, size • model  weight, size • mid  pname Consider the set of relation schema: machine = {mid, cost, mid, hours}, model = {model, cost, weight} Do the schemas form a lossless join? Are they dependency preserving?

13. Database Characteristics • pname • start • budget • cname • cphone • cemail • sin • fname • lname • cat • rank • rate • mid • desc • model • weight • size • cost • hours • pname, cnamestart, budget • cname cphone, cemail • pname cname • sin, pname hours • sin  fname, lname • sin, fname, lname cat, rank, rate • cat, rank  rate • mid  desc • mid  model, cost • mid, model  weight, size • model  weight, size • mid  pname Consider the set of relation schema: works = {sin, mid, hours}, machine_use= {mid, pname} Do the schemas form a lossless join? Are they dependency preserving?

14. Decomposition • pname • start • budget • cname • cphone • cemail • sin • fname • lname • cat • rank • rate • mid • desc • model • weight • size • cost • hours • pname, cnamestart, budget • cname cphone, cemail • pname cname • sin, pname hours • sin  fname, lname • sin, fname, lname cat, rank, rate • cat, rank  rate • mid  desc • mid  model, cost • mid, model  weight, size • model  weight, size • mid  pname Is the project relation in 1NF or 2NF? Produce a 3NF, dependency preserving decomposition of the project relation