Chuẩn hóa CSDL

The Normal Forms 3NF and BCNF BY Jasbir Jassu

Preview Normalization Solution: Normal Forms Introducing 3NF and BCNF 3NF Examples BCNF

Normalization Normalization is the process of efficiently organizing data in a database with two goals in mind First goal: eliminate redundant data for example, storing the same data in more than one table Second Goal: ensure data dependencies make sense for example, only storing related data in a table

Benefits of Normalization Less storage space Quicker updates Less data inconsistency Clearer data relationships Easier to add data Flexible Structure

The Solution: Normal Forms Bad database designs results in: redundancy: inefficient storage. anomalies: data inconsistency, difficulties in maintenance 1NF, 2NF, 3NF, BCNF are some of the early forms in the list that address this problem

Third Normal Form (3NF) Meet all the requirements of the 1NF Meet all the requirements of the 2NF Remove columns that are not dependent upon the primary key.

1) First normal form -1NF The following table is not in 1NF 1NF : if all attribute values are atomic: no repeating group, no composite attributes. Jim Carter Paul Simon 30000 30001 13456 D102 Carl Sagan Mag James Larry Bird 20000 20001 20002 12345 D101 EMP_NM EMP_NO MG_NO DPT_NO

Table in 1NF all attribute values are atomic because there are no repeating group and no composite attributes. Jim Carter 30000 13456 D102 Mag James 20001 12345 D101 Larry Bird 20002 12345 D101 Paul Simon 30001 13456 D102 Carl Sagan 20000 12345 D101 EMP_NM EMP_NO MG_NO DPT_NO

2) Second Normal Form Second normal form (2NF) further addresses the concept of removing duplicative data: A relation R is in 2NF if (a) R is 1NF , and (b) all non-prime attributes are fully dependent on the candidate keys. Which is creating relationships between these new tables and their predecessors through the use of foreign keys. A prime attribute appears in a candidate key. There is no partial dependency in 2NF. Example is next…

No dependencies on non-key attributes There are two non-key fields. So, here are the questions: If I know just Description, can I find out Cost? No, because we have more than one supplier for the same product. If I know just Supplier, and I find out Cost? No, because I need to know what the Item is as well. Therefore, Cost is fully, functionally dependent upon the ENTIRE PK (Description-Supplier) for its existence. Supplier Address Cost Supplier Description Inventory Cost Supplier Description Inventory

CONTINUED… If I know just Description, can I find out Supplier Address? No, because we have more than one supplier for the same product. If I know just Supplier, and I find out Supplier Address? Yes. The Address does not depend upon the description of the item. Therefore, Supplier Address is NOT functionally dependent upon the ENTIRE PK (Description-Supplier) for its existence. Supplier Address Name Supplier Supplier Address Cost Supplier Description Inventory

So putting things together The above relation is now in 2NF since the relation has no non-key attributes. Supplier Address Cost Supplier Description Inventory Cost Supplier Description Inventory Supplier Address Name Supplier

3) Remove columns that are not dependent upon the primary key. So for every nontrivial functional dependency X --> A, (1) X is a superkey, or (2) A is a prime (key) attribute.

Example of 3NF If I know # of Pages, can I find out Author's Name? No. Can I find out Author's Non-de Plume? No. If I know Author's Name, can I find out # of Pages? No. Can I find out Author's Non-de Plume? YES. Therefore, Author's Non-de Plume is functionally dependent upon Author's Name, not the PK for its existence. It has to go. # of Pages Author's Non-de Plume Author's Name Name Books # of Pages Author's Name Name Books Non-de Plume Name Author

Another example: Suppose we have relation S S(SUPP#, PART#, SNAME, QUANTITY) with the following assumptions: (1) SUPP# is unique for every supplier. (2) SNAME is unique for every supplier. (3) QUANTITY is the accumulated quantities of a part supplied by a supplier. (4) A supplier can supply more than one part. (5) A part can be supplied by more than one supplier. We can find the following nontrivial functional dependencies: (1) SUPP# --> SNAME (2) SNAME --> SUPP# (3) SUPP# PART# --> QUANTITY (4) SNAME PART# --> QUANTITY The candidate keys are: (1) SUPP# PART# (2) SNAME PART# The relation is in 3NF.

The table in 3NF 300 P1 Jones S2 200 P2 Yues S1 250 P3 Yues S2 100 P1 Yues S1 QTY PART# SNAME SUPP#

Example with first three forms Suppose we have this Invoice Table First Normal Form: No repeating groups. The above table violates 1NF because it has columns for the first, second, and third line item. Solution: you make a separate line item table, with it's own key, in this case the combination of invoice number and line number

Second Normal Form: Each column must depend on the *entire* primary key.

Third Normal Form: Each column must depend on *directly* on the primary key.

Boyce-Codd Normal Form (BCNF) Boyce-Codd normal form (BCNF) A relation is in BCNF, if and only if, every determinant is a candidate key. The difference between 3NF and BCNF is that for a functional dependency A  B, 3NF allows this dependency in a relation if B is a primary-key attribute and A is not a candidate key, whereas BCNF insists that for this dependency to remain in a relation, A must be a candidate key.

FD1 clientNo, interviewDate  interviewTime, staffNo, roomNo (Primary Key) FD2 staffNo, interviewDate, interviewTime  clientNo (Candidate key) FD3 roomNo, interviewDate, interviewTime  clientNo, staffNo (Candidate key) FD4 staffNo, interviewDate  roomNo (not a candidate key) As a consequece the ClientInterview relation may suffer from update anmalies. For example, two tuples have to be updated if the roomNo need be changed for staffNo SG5 on the 13-May-02. ClientInterview SG5 SG37 SG5 SG5 staffNo G102 10.30 1-Jul-02 CR56 G102 12.00 13-May-02 CR74 G101 10.30 13-May-02 CR76 G101 12.00 13-May-02 CR76 roomNo interviewTime interviewDate ClientNo

Example of BCNF(2) To transform the ClientInterview relation to BCNF, we must remove the violating functional dependency by creating two new relations called Interview and StaffRoom as shown below, Interview ( clientNo , interviewDate , interviewTime, staffNo) StaffRoom( staffNo , interviewDate, roomNo) Interview StaffRoom BCNF Interview and StaffRoom relations SG5 SG37 SG5 SG5 staffNo 10.30 1-Jul-02 CR56 12.00 13-May-02 CR74 10.30 13-May-02 CR76 12.00 13-May-02 CR76 interviewTime interviewDate ClientNo SG5 SG37 SG5 staffNo G102 1-Jul-02 G102 13-May-02 G101 13-May-02 roomNo interviewDate

Another BCNF Example Example taken from Dr. Lee’s 2004 lecture notes

Sources: http://www.troubleshooters.com/littstip/ltnorm.html http://www.cs.jcu.edu.au/Subjects/cp1500/1998/Lecture_Notes/normalisation/3nf.html Dr. Lee’s Fall 2004 lecture notes

Chuẩn hóa CSDL

More Related Content

What's hot

Viewers also liked

Similar to Chuẩn hóa CSDL

Recently uploaded

Chuẩn hóa CSDL