6.830/6.814 Lecture 4Hobby SchemaBCNFify Example for HobbiesNon-BCNF ExampleThird Normal FormStudy Break (Tricky)AnswerPlan Formulation6.830/6.814 Lecture 4NormalizationDatabase InternalsSeptember 19, 2014Hobby SchemaSSN Name Address Hobby Cost123 john main st dolls $123 john main st bugs $345 mary lake st tennis $$456 joe first st dolls $“Wide” schema – has redundancy and anomalies in the presence of updates, inserts, and deletesTable key is Hobby, SSNPersonPersonHobbyHobbySSNAddressNameNameCostn:nEntity Relationship DiagramBCNFify Example for Hobbies Schema FDs(S,H,N,A,C)S,H N,A,CS N, AH CS = SSN, H = Hobby, N = Name, A = Addr, C = Costviolates bcnfSchema FDs(S, N,A)S N, ASchema FDs(S,H, C)S,H CH Cviolates bcnfSchema FDs(H, C)H CSchema FDs(S,H)Iter 1Iter 2keyIter 3Non-BCNF ExampleAccount Client OfficeA Joe 1B Mary 1A John 1C Joe 2Primary Key is Client, OfficeEach Account is handled by exactly 1 OfficeWithin a given Office, a given Client has exactly 1 AccountViolates BCNF! (account not a superkey)FDs:Client, Office AccountAccount OfficeSplitting into 1. Account,Office and2. ClientLoses correspondence between clients, offices, and accountsRedundancy!Third Normal Form•This is a minimal example of a table in “third normal form” (3NF) but not BCNF•All tables in BCNF are also in 3NF•But 3NF allows redundancy to “preserve FDs”–Meaning that all FDs can be checked in a single table•Not going to study 3NF algorithmsAccount Client OfficeA Joe 1B Mary 1A John 1C Joe 2FDs:Client, Office AccountAccount OfficeStudy Break (Tricky)Flatten this query (departments where number of machines is more than number of employees):SELECT dept.nameFROM deptWHERE dept.num-of-machines ≥ (SELECT COUNT(emp.*) FROM emp WHERE dept.name=emp.dept_name) What happens if there is a department with no employees?AnswerSELECT dept.name FROM deptLEFT OUTER JOIN emp ON (dept.name=emp.dept_name )GROUP BY dept.nameHAVING dept.num-of-machines < COUNT(emp.*)Plan Formulationemp (eno, ename, sal, dno)dept (dno, dname, bldg)kids (kno, eno, kname, bday)SELECT ename, count(*)FROM emp, dept, kidsAND emp.dno=dept.dnoAND kids.eno=emp.enoAND emp.sal > 50000AND dept.name = 'eecs'GROUP BY enameHAVING count(*) >
View Full Document