210 likes | 230 Views
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).
E N D
CSCI 2141 – Intro to Database SystemsDatabase Normalization 3NF and BCNF
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
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).
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
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
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
Practice Exercise 5 – Solution Relational schema and dependency diagram are as shown
Practice Exercise 5 – Solution Relational schema, and tables with partial dependencies removed (at least 2NF) are as shown below:
Practice Exercise 5 – Solution Relational schema, and tables with transitive dependencies removed (3NF) are as shown below:
Practice Exercise 5 – Solution ERD is shown below:
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?
Boyce-Codd Normal Form (BCNF) • Consider the following table: • Its candidate keys are given to be: • A+B • A+C
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
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?
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
BCNF – Exercise • Convert the dependency diagram given below to 3NF • Determine if the 3NF is also in BCNF • If not, convert to BCNF
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:
Exercise – Solution Step 2: Removing transitive dependencies, we get:
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