1 / 69

B. Information Technology CMPB245: Database Design

B. Information Technology CMPB245: Database Design. Normalization Pt. 2. Objectives. Identify the Boyce-Codd Normal Form (BCNF) Normalize a given table from Unnor-malized Form (UNF) to Boyce-Codd Nor-mal Form (BCNF). The Boyce-Codd Normal Form. Objective 1. Boyce-Codd Normal Form (BCNF).

tejano
Télécharger la présentation

B. Information Technology CMPB245: Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. B. Information TechnologyCMPB245:Database Design Normalization Pt. 2

  2. Objectives • Identify the Boyce-Codd Normal Form (BCNF) • Normalize a given table from Unnor-malized Form (UNF) to Boyce-Codd Nor-mal Form (BCNF)

  3. The Boyce-CoddNormal Form Objective 1

  4. Boyce-Codd Normal Form(BCNF) • A relation is in BCNF • If and only if every determinant is a candidate key • Identify all the determinants • Make sure they are candidate keys • If this is true, then the relation is in BCNF

  5. Boyce-Codd Normal Form(BCNF) • BCNF is violated when a relation • contains two or more composite candidate keys • that overlaps and share at least one common attribute

  6. Client_No Interview_Date Interview_Time Staff_No Room_No CR76 13-May-95 10.30 SG5 G101 CR56 13-May-95 12.00 SG5 G101 CR74 13-May-95 12.00 SG37 G102 CR56 1-Jul-95 10.30 SG5 G102 Boyce-Codd Normal FormExample Consider the Client_Interview relation CLIENT_INTERVIEW

  7. CLIENT_INTERVIEW Client_No Interview_Date Interview_Time Staff_No Room_No CR76 13-May-95 10.30 SG5 G101 CR56 13-May-95 12.00 SG5 G101 CR74 13-May-95 12.00 SG37 G102 CR56 1-Jul-95 10.30 SG5 G102 Boyce-Codd Normal FormExample • Identify all the candidate keys, i.e. which will produce unique rows

  8. CLIENT_INTERVIEW Client_No Interview_Date Interview_Time Staff_No Room_No CR76 13-May-95 10.30 SG5 G101 CR56 13-May-95 12.00 SG5 G101 CR74 13-May-95 12.00 SG37 G102 CR56 1-Jul-95 10.30 SG5 G102 Boyce-Codd Normal FormExample • By itself, all attributes cannot be a candidate key

  9. CLIENT_INTERVIEW Client_No Interview_Date Interview_Time Staff_No Room_No CR76 13-May-95 10.30 SG5 G101 CR56 13-May-95 12.00 SG5 G101 CR74 13-May-95 12.00 SG37 G102 CR56 1-Jul-95 10.30 SG5 G102 Boyce-Codd Normal FormExample • (Client_No, Interview_Date) both identifies a unique row and is a candidate key -- (Set 1)

  10. CLIENT_INTERVIEW Client_No Interview_Date Interview_Time Staff_No Room_No CR76 13-May-95 10.30 SG5 G101 CR56 13-May-95 12.00 SG5 G101 CR74 13-May-95 12.00 SG37 G102 CR56 1-Jul-95 10.30 SG5 G102 Boyce-Codd Normal FormExample • What about (Staff_No, Interview_Date) together? • No, because there are two rows

  11. CLIENT_INTERVIEW Client_No Interview_Date Interview_Time Staff_No Room_No CR76 13-May-95 10.30 SG5 G101 CR56 13-May-95 12.00 SG5 G101 CR74 13-May-95 12.00 SG37 G102 CR56 1-Jul-95 10.30 SG5 G102 Boyce-Codd Normal FormExample • What about (Staff_No, Interview_ Date, Interview_ Time) together? • Yes, they can be another candidate key - (Set 2)

  12. CLIENT_INTERVIEW Client_No Interview_Date Interview_Time Staff_No Room_No CR76 13-May-95 10.30 SG5 G101 CR56 13-May-95 12.00 SG5 G101 CR74 13-May-95 12.00 SG37 G102 CR56 1-Jul-95 10.30 SG5 G102 Boyce-Codd Normal FormExample • Since there are two sets of composite candidate key, then BCNF could be vio-lated 5

  13. CLIENT_INTERVIEW Client_No Interview_Date Interview_Time Staff_No Room_No CR76 13-May-95 10.30 SG5 G101 CR56 13-May-95 12.00 SG5 G101 CR74 13-May-95 12.00 SG37 G102 CR56 1-Jul-95 10.30 SG5 G102 Boyce-Codd Normal FormExample • Try use (Client_No, Interview_Date) as the primary key

  14. CLIENT_INTERVIEW Client_No Interview_Date Interview_Time Staff_No Room_No CR76 13-May-95 10.30 SG5 G101 CR56 13-May-95 12.00 SG5 G101 CR74 13-May-95 12.00 SG37 G102 CR56 1-Jul-95 10.30 SG5 G102 Boyce-Codd Normal FormExample • Then, identify all functional dependen-cies • Identify all the determinants

  15. CLIENT_INTERVIEW Client_No Interview_Date Interview_Time Staff_No Room_No CR76 13-May-95 10.30 SG5 G101 CR56 13-May-95 12.00 SG5 G101 CR74 13-May-95 12.00 SG37 G102 CR56 1-Jul-95 10.30 SG5 G102 Boyce-Codd Normal FormExample Staff_No Client_No, Interview_Date Interview_Time Room_No Client_No Staff_No, Interview_Date, Interview_Time Staff_No, Interview_Date Room_No

  16. Client_No, Interview_Date Staff_No, Interview_Date, Interview_Time Boyce-Codd Normal FormExample Staff_No Client_No, Interview_Date Interview_Time Room_No Client_No Staff_No, Interview_Date, Interview_Time Staff_No, Interview_Date Room_No • The candidate keys overlap and share a common attribute Interview_Date • Hence, the BCNF is violated

  17. Staff_No Client_No, Interview_Date Interview_Time Room_No Client_No Staff_No, Interview_Date, Interview_Time Staff_No, Interview_Date Room_No Boyce-Codd Normal FormExample Staff_No, Interview_Date • It is also not in BCNF • Due to the presence of the (Staff_No, Inter-view_Date) determinant • Which is not a candidate key 4

  18. Staff_No Client_No, Interview_Date Interview_Time Room_No Client_No Staff_No, Interview_Date, Interview_Time Staff_No, Interview_Date Room_No Boyce-Codd Normal FormExample • Normalize Client_Interview relation to BCNF by • removing the violating functional dependency based on (Staff_No, Interview_Date) determinant

  19. CLIENT_INTERVIEW Client_No Interview_Date Interview_Time Staff_No Room_No CR76 13-May-95 10.30 SG5 G101 CR56 13-May-95 12.00 SG5 G101 CR74 13-May-95 12.00 SG37 G102 CR56 1-Jul-95 10.30 SG5 G102 Boyce-Codd Normal FormExample

  20. Client_No Interview_Date Interview_Time Staff_No CR76 13-May-95 10.30 SG5 CR56 13-May-95 12.00 SG5 CR74 13-May-1995 12.00 SG37 CR56 1-Jul-95 10.30 SG5 Staff_No Interview_Date Room_No SG5 13-May-95 G101 SG5 13-May-95 G101 SG37 13-May-1995 G102 SG5 1-Jul-95 G102 Boyce-Codd Normal FormExample INTERVIEW STAFF_ROOM

  21. Review of NormalizationProcess (UNF to BCNF) Objective 2

  22. A DreamHomeScenario • DreamHome Co. manages property on behalf of the owners • The company’s staffs inspect properties • A staff may inspect several properties at a given date • A property is inspected once on that date • A staff is allocated a car for the day

  23. Property_No PAddress IDate ITime Comments Staff_No SName Car_RegProperty_No PAddress IDate ITime Comments Staff_No SName Car_Reg PG4 6 Lawrence Street, 18-Oct-94 10.00 Need to replace crockery SG37 Anne Beech M231 JGR Glasgow 22-Apr-95 09.00 In good order SG14 David Ford M533 HDR 1-Oct-95 12.00 Damp rot in bathroom SG14 David Ford N721 HFR PG16 5 Novar Drive, 22-Apr-95 13.00 Replace living room SG14 David Ford M533 HDR Glasgow carpet 24-Oct-95 14.00 Good condition SG37 Ann Beech N721 HFR Unnormalized Form(UNF) • Transfer some sample information from the report into a table • This table is the Unnormalized (UNF) table PROPERTY_INSPECTION TABLE

  24. PROPERTY_INSPECTION TABLE Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg PG4 6 Lawrence Street, 18-Oct-94 10.00 Need to replace crockery SG37 Anne Beech M231 JGR Glasgow 22-Apr-95 09.00 In good order SG14 David Ford M533 HDR 1-Oct-95 12.00 Damp rot in bathroom SG14 David Ford N721 HFR PG16 5 Novar Drive, 22-Apr-95 13.00 Replace living room SG14 David Ford M533 HDR Glasgow carpet 24-Oct-95 14.00 Good condition SG37 Ann Beech N721 HFR Unnormalized Form(UNF) • Identify the key attributes for table • The key attribute is Property_No

  25. PROPERTY_INSPECTION TABLE Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg PG4 6 Lawrence Street, 18-Oct-94 10.00 Need to replace crockery SG37 Anne Beech M231 JGR Glasgow 22-Apr-95 09.00 In good order SG14 David Ford M533 HDR 1-Oct-95 12.00 Damp rot in bathroom SG14 David Ford N721 HFR PG16 5 Novar Drive, 22-Apr-95 13.00 Replace living room SG14 David Ford M533 HDR Glasgow carpet 24-Oct-95 14.00 Good condition SG37 Ann Beech N721 HFR First Normal Form (1NF)First Approach • Identify the repeating group in the unnormalized table

  26. PROPERTY_INSPECTION TABLE Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg PG4 6 Lawrence Street, 18-Oct-94 10.00 Need to replace crockery SG37 Anne Beech M231 JGR Glasgow 22-Apr-95 09.00 In good order SG14 David Ford M533 HDR 1-Oct-95 12.00 Damp rot in bathroom SG14 David Ford N721 HFR PG16 5 Novar Drive, 22-Apr-95 13.00 Replace living room SG14 David Ford M533 HDR Glasgow carpet 24-Oct-95 14.00 Good condition SG37 Ann Beech N721 HFR First Normal Form (1NF)First Approach • The repeating attributes are • IDate, ITime, Comments, Staff_No, SName, Car_Reg

  27. PROPERTY_INSPECTION RELATION Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg PG4 6 Lawrence Street 18-Oct-94 10.00 Need to replace crockery SG37 Anne Beech M231 JGR PG4 6 Lawrence Street 22-Apr-95 09.00 In good order SG14 David Ford M533 HDR PG4 6 Lawrence Street 1-Oct-95 12.00 Damp rot in bathroom SG14 David Ford N721 HFR PG16 5 Novar Drive 22-Apr-95 13.00 Replace room carpet SG14 David Ford M533 HDR PG16 5 Novar Drive 24-Oct-95 14.00 Good condition SG37 Ann Beech N721 HFR First Normal Form (1NF)First Approach • Normalize to 1NF • Enter the appropriate property details (non-repeating data) into each row

  28. PROPERTY_INSPECTION RELATION Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg PG4 6 Lawrence Street 18-Oct-94 10.00 Need to replace crockery SG37 Anne Beech M231 JGR PG4 6 Lawrence Street 22-Apr-95 09.00 In good order SG14 David Ford M533 HDR PG4 6 Lawrence Street 1-Oct-95 12.00 Damp rot in bathroom SG14 David Ford N721 HFR PG16 5 Novar Drive 22-Apr-95 13.00 Replace room carpet SG14 David Ford M533 HDR PG16 5 Novar Drive 24-Oct-95 14.00 Good condition SG37 Ann Beech N721 HFR First Normal Form (1NF)First Approach • The resulting relation is in First Normal Form (1NF) • There is a single value at the intersection of each row and column

  29. PROPERTY_INSPECTION RELATION Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg PG4 6 Lawrence Street 18-Oct-94 10.00 Need to replace crockery SG37 Anne Beech M231 JGR PG4 6 Lawrence Street 22-Apr-95 09.00 In good order SG14 David Ford M533 HDR PG4 6 Lawrence Street 1-Oct-95 12.00 Damp rot in bathroom SG14 David Ford N721 HFR PG16 5 Novar Drive 22-Apr-95 13.00 Replace room carpet SG14 David Ford M533 HDR PG16 5 Novar Drive 24-Oct-95 14.00 Good condition SG37 Ann Beech N721 HFR First Normal Form (1NF)First Approach • Identify the candidate keys for the rela-tion • Candidate keys are (Property_No, IDate) and (Staff_No, IDate, ITime)

  30. PROPERTY_INSPECTION RELATION Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg PG4 6 Lawrence Street 18-Oct-94 10.00 Need to replace crockery SG37 Anne Beech M231 JGR PG4 6 Lawrence Street 22-Apr-95 09.00 In good order SG14 David Ford M533 HDR PG4 6 Lawrence Street 1-Oct-95 12.00 Damp rot in bathroom SG14 David Ford N721 HFR PG16 5 Novar Drive 22-Apr-95 13.00 Replace room carpet SG14 David Ford M533 HDR PG16 5 Novar Drive 24-Oct-95 14.00 Good condition SG37 Ann Beech N721 HFR First Normal Form (1NF)First Approach • Let’s select (Property_No, IDate) as the primary key

  31. First Normal Form (1NF)Second Approach • Normalize to 1NF by removing the repeating group to form a new relation • Copy the primary key from the main relation to the new relation • Identify the primary key for the new relation

  32. First Normal Form (1NF)Second Approach Unnormalized Form (UNF) First Normal Form (1NF) Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg Which attributes are repeating?

  33. First Normal Form (1NF)Second Approach Unnormalized Form (UNF) First Normal Form (1NF) Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg MOVE

  34. First Normal Form (1NF)Second Approach Unnormalized Form (UNF) First Normal Form (1NF) Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg MOVE

  35. First Normal Form (1NF)Second Approach Unnormalized Form (UNF) First Normal Form (1NF) Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg Copy the primary key

  36. First Normal Form (1NF)Second Approach Unnormalized Form (UNF) First Normal Form (1NF) Property_No PAddress Property_No IDate ITime Comments Staff_No SName Car_Reg Identify primary key

  37. First Normal Form (1NF)Second Approach Unnormalized Form (UNF) First Normal Form (1NF) Property_No PAddress Property_No IDate ITime Comments Staff_No SName Car_Reg Property_No and IDate

  38. First Normal Form (1NF)Second Approach Unnormalized Form (UNF) First Normal Form (1NF) Property_No PAddress Property_No IDate ITime Comments Staff_No SName Car_Reg Property_No PAddress Property_No IDate ITime Comments Staff_No SName Car_Reg

  39. Second Normal Form(2NF) • Normalization to 2NF is associated with • Composite primary key only! • The normalization to 2NF involves • The removal of partial dependencies on the pri-mary key • If partial dependencies exist • Remove the dependent attributes to a new relation • Copy their determinant to the new relation

  40. PROPERTY_INSPECTION RELATION Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg PG4 6 Lawrence Street 18-Oct-94 10.00 Need to replace crockery SG37 Anne Beech M231 JGR PG4 6 Lawrence Street 22-Apr-95 09.00 In good order SG14 David Ford M533 HDR PG4 6 Lawrence Street 1-Oct-95 12.00 Damp rot in bathroom SG14 David Ford N721 HFR PG16 5 Novar Drive 22-Apr-95 13.00 Replace room carpet SG14 David Ford M533 HDR PG16 5 Novar Drive 24-Oct-95 14.00 Good condition SG37 Ann Beech N721 HFR Second Normal FormFirst Approach • Identify the functional dependen-cies in the Property_Inspection rela-tion

  41. PROPERTY_INSPECTION RELATION Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg PG4 6 Lawrence Street 18-Oct-94 10.00 Need to replace crockery SG37 Anne Beech M231 JGR PG4 6 Lawrence Street 22-Apr-95 09.00 In good order SG14 David Ford M533 HDR PG4 6 Lawrence Street 1-Oct-95 12.00 Damp rot in bathroom SG14 David Ford N721 HFR PG16 5 Novar Drive 22-Apr-95 13.00 Replace room carpet SG14 David Ford M533 HDR PG16 5 Novar Drive 24-Oct-95 14.00 Good condition SG37 Ann Beech N721 HFR Second Normal FormFirst Approach Property_No, IDate ITime Comments Staff_No SName Car_Reg Property_No PAddress Staff_No SName

  42. PROPERTY_INSPECTION RELATION Property_No PAddress IDate ITime Comments Staff_No SName Car_Reg PG4 6 Lawrence Street 18-Oct-94 10.00 Need to replace crockery SG37 Anne Beech M231 JGR PG4 6 Lawrence Street 22-Apr-95 09.00 In good order SG14 David Ford M533 HDR PG4 6 Lawrence Street 1-Oct-95 12.00 Damp rot in bathroom SG14 David Ford N721 HFR PG16 5 Novar Drive 22-Apr-95 13.00 Replace room carpet SG14 David Ford M533 HDR PG16 5 Novar Drive 24-Oct-95 14.00 Good condition SG37 Ann Beech N721 HFR Second Normal FormFirst Approach Staff_No, IDate Car_Reg Staff_No, IDate, ITime Property_No PAddress Comments

  43. Car_Reg Property_No, IDate ITime Comments Staff_No SName Property_No PAddress Staff_No SName Staff_No, IDate Car_Reg Staff_No, IDate, ITime Property_No PAddress Comments Second Normal FormFirst Approach • PAddress is functionally dependent on Pro-perty_No • Remove it to form a new relation with a copy its determinant, Property_No

  44. Second Normal FormFirst Approach Comments Primary Key PAddress Property_No Car_Reg IDate ITime Staff_No Alternate Key SName

  45. Second Normal FormFirst Approach • The relations have the form: Prop (Property_No, Address) Prop_ (Property_No, IDate, ITime, Inspection Comments, Staff_No, SName, Car_Reg) Which is the same as the 1NF generated from the second approach

  46. Second Normal FormSecond Approach First Normal Form (UNF) Second Normal Form (1NF) Property_No PAddress Property_No IDate ITime Comments Staff_No SName Car_Reg All the non-key attributes are dependent on both key-attributes Which of the non-key attributes depend on part of the primary key?

  47. Second Normal FormSecond Approach First Normal Form (UNF) Second Normal Form (1NF) Property_No PAddress Property_No IDate ITime Comments Staff_No SName Car_Reg MOVE MOVE

  48. Second Normal FormSecond Approach First Normal Form (1NF) Second Normal Form (2NF) Property_No PAddress Property_No IDate ITime Comments Staff_No SName Car_Reg Property_No PAddress Property_No IDate ITime Comments Staff_No SName Car_Reg

  49. Third Normal Form(3NF) • Normalization to 3NF involves • The removal of transitive dependencies • If a transitive dependency exists • Remove the dependent attributes to a new relation • Copy their determinant to the new relation

  50. Property_No PAddress Property_No, IDate ITime Comments Staff_No SName Car_Reg Staff_No SName Staff_No, IDate Car_Reg Staff_No, Idate, ITime Property_No Comments Third Normal FormFirst Approach • Identify the functional dependencies in the Prop and Prop_Inspection relation

More Related