DOC PREVIEW
MIT 6 830 - Normalization

This preview shows page 1-2-3 out of 8 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

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 AccountViolates 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
Download Normalization
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Normalization and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Normalization 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?