1 / 21

Database Normalization 3NF and BCNF: Converting to 3NF

Learn about the process of converting tables to 3NF (Third Normal Form) by eliminating transitive dependencies and ensuring 2NF (Second Normal Form). Also, understand the concept of Boyce-Codd Normal Form (BCNF).

losgood
Télécharger la présentation

Database Normalization 3NF and BCNF: Converting to 3NF

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. CSCI 2141 – Intro to Database SystemsDatabase Normalization 3NF and BCNF

  2. Converting to 3NF • Tables are in 3NF (Third Normal Form) when • They are in 2NF, and • They have no transitive dependencies • Transitive dependency: An attribute functionally depends on another non-key attribute • Converting to 3NF • Ensure that the tables are in 2NF • Make new tables to eliminate transitive dependencies • Reassign corresponding dependent attributes

  3. Example CUS_ADDRESS CUS_PHONE INV_NUM INV_DATE INV_AMOUNT CUS_NUM Identify all dependencies. Identify which normal form the table is in. Convert to the third normal form (3NF).

  4. Example – Solution Original table CUS_ADDRESS CUS_PHONE INV_NUM INV_DATE INV_AMOUNT CUS_NUM Transitive Dependencies • Dependencies are shown in figure below. • Table is in 1NF as all attributes show functional dependency on the PK Table is also in 2NF as the PK is not composite, and there is no candidate key. • There are no partial dependencies

  5. Example – Solution INV_NUM INV_DATE INV_AMOUNT CUS_NUM New Tables CUS_NUM CUS_ADDRESS CUS_PHONE Eliminating transitive dependencies result in the following tables Each of the table above is in 3NF

  6. Practice Exercise 5 • Using the INVOICE table structure shown below: • Write the relational schema, and draw its dependency diagram identifying all dependencies (Assume there are no repeating groups and invoice number references more than one product) • Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you create • Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also, draw the ERD

  7. Practice Exercise 5 – Solution Relational schema and dependency diagram are as shown

  8. Practice Exercise 5 – Solution Relational schema, and tables with partial dependencies removed (at least 2NF) are as shown below:

  9. Practice Exercise 5 – Solution Relational schema, and tables with transitive dependencies removed (3NF) are as shown below:

  10. Practice Exercise 5 – Solution ERD is shown below:

  11. Boyce-Codd Normal Form (BCNF) BCNF is a special case of 3NF Table is in BCNF when every determinant in the table is a candidate key When a table in 3NF has only one candidate key, it is also in BCNF How can a table be in 3NF but not in BCNF?

  12. Boyce-Codd Normal Form (BCNF) • Consider the following table: • Its candidate keys are given to be: • A+B • A+C

  13. Boyce-Codd Normal Form (BCNF) • Is the table in 1NF? • Key (or prime) attributes are defined • All non-key attributes are determined by the key • Is the table in 2NF? • It is in 1NF • No partial dependencies • Is the table in 3NF • It is in 2NF • No transitive dependencies • Note that C → B is not transitive • The table is in 3NF, however, it is not in BCNF

  14. Boyce-Codd Normal Form (BCNF) 1NF The dependency C → B means that effectively, C is a superset of B In order to convert to BCNF, change the PK to A+C Can you identify the normal form for the table below?

  15. Boyce-Codd Normal Form (BCNF) 1NF

  16. BCNF - Example • Table below reflects the following conditions: • A class represents one section of a course (that may have many) • A student can take many classes • A staff member can teach many classes, but one class is taught by one staff member

  17. BCNF - Example

  18. BCNF – Exercise • Convert the dependency diagram given below to 3NF • Determine if the 3NF is also in BCNF • If not, convert to BCNF

  19. Exercise – Solution A D 3 NF A B C E F G 2 NF Transitive dependency Not a transitive dependency Step 1: Removing partial dependencies, we get:

  20. Exercise – Solution Step 2: Removing transitive dependencies, we get:

  21. Exercise – Solution • Step 3: Converting to BCNF • Change the PK to A+C • Table is now in 1NF due to a partial dependency C →B • Convert table to 3NF • All tables are in 3NF and BCNF

More Related