1 / 15

Normalization (Chapter 2)

Normalization (Chapter 2). Peter Rob and Elie Semaan Databases: Design, Development, and Deployment Using Microsoft Access Second Edition. Dependencies. All key attributes must be dependent on the Primary Key (PK)

wanda
Download Presentation

Normalization (Chapter 2)

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. Normalization(Chapter 2) Peter Rob and Elie SemaanDatabases: Design, Development,and DeploymentUsing Microsoft Access Second Edition

  2. Dependencies • All key attributes must be dependent on the Primary Key (PK) • If the PK is a composite PK and an attribute is dependent on only a part of the composite PK, a partial dependency exists • If an attribute is dependent on a non-key attribute, it exhibits a transitive dependency

  3. The Dependency Diagram EMP_NUM EMP_LNAME EMP_FNAME EMP_DOB • PK is bold-faced and underlined (shown in orange) • Single-attribute PK (simple PK) • All employee attributes are dependent on the PK

  4. Partial Dependency PROJ_CODE EMP_NUM PROJ_NAME HRS_WORKED Partial dependency • PK components are bold-faced and underlined • Multi-attribute PK (composite PK) • All work assignment attributes are dependent on the PK • But the PROJ_CODE, which is only a part of the PK, • determines the PROJ_NAME (or the PROJ_NAME is • dependent on the PROJ_CODE)

  5. Transitive Dependency STU_NUM STU_LNAME DEPT_CODE DEPT_NAME Transitive dependency • PK is bold-faced and underlined • Single-attribute PK (simple PK) • All student attributes are dependent on the PK • But DEPT_CODE determines DEPT_NAME (or DEPT_NAME is dependent on DEPT_CODE, a non-key attribute

  6. The First Normal Form (1NF) • All key attributes are defined • Each row/column intersection contains one and only one value, rather than a set of values • All attributes are dependent on the primary key (PK)

  7. First Normal Form (1NF) INV_NUM PROD_CODE PROD_TITLE CUS_NUM LINE_NUM LINE_UNITS Transitive dependency Partial dependency

  8. The Second Normal Form (2NF) • Meets 1NF requirements • Does not contain partial dependencies • But does contain transitive dependencies

  9. Normalization: Initial Decomposition 1. Identify each PK attribute in the 1NF dependency diagram. In this case, INV_NUM and LINE_NUM constituted the PK. INV_NUM 2. Write each PK attribute on a separate line LINE_NUM 3. Write the original PK on the final line INV_NUM LINE_NUM

  10. Normalization: Creating New Structures INV_NUM CUS_NUM Table in 3NF; no partial or transitive dependencies LINE_NUM No dependencies, no table Table is in 2NF, with a remaining transitive dependency INV_NUM PROD_CODE PROD_TITLE LINE_NUM LINE_UNITS Transitive dependency

  11. The Third Normal Form (3NF) • Meets 2NF requirements • Does not contain transitive dependencies

  12. Normalization: Completed 3NF Structures Table name: INVOICE INV_NUM CUS_NUM 3NF; no partial or transitive dependencies Table name: PRODUCT PROD_CODE PROD_TITLE 3NF; no partial or transitive dependencies Table name: LINE 3NF; no partial or transitive dependencies INV_NUM PROD_CODE LINE_UNITS LINE_NUM

  13. The Boyce-Codd Normal Form (BCNF) • Meets 3NF requirements • Every determinant in the table is a candidate key

  14. Normalization: Boyce-Codd NF Panel 1 A B C D E F G 3NF, but not BCNF Panel 2 A E C D B F G Conversion to 2NF Partial dependency Panel 3 A E C D F G E B 3NF and BCNF

  15. The END

More Related