Different Normal Forms (1st, 2nd, 3rd, BCNF, DKNF)

Unnormalised Normal Form (UNF)

A relation is said to be Unnormalised when it has not had any normalization rules applied to it, and it suffers from various anomalies. A relation has the repeating group so it has more than one value for a given key. A repeating group is an attribute or set of attributes that can have more than one value for a primary key value. Repeating groups are not allowed in a relational design since all attributes have to be 'atomic' that is there can only be one value per cell in a table.

First Normal Form (1NF)

A relation schema R is in first normal form if the domains of all attributes of R are atomic. Domain is atomic if its elements are considered to be indivisible units. First Normal Form ensures that each table has a primary key, a minimal set of attributes which can uniquely identify a record. It eliminates the repeating groups (categories of data which would seem to be required a different number of times on different records) by defining keyed and non-keyed attributes appropriately. First, Normal Form supports atomicity which means that each attribute must contain a single value not a set of values.

Second Normal Form (2NF)

A relation is in 2NF if and only if it is in 1NF and every non-ley attribute is fully dependent on the primary key. It removes partial functional dependencies into a new relation. A relation R is in 2NF if

• R is in 1NF, and
• All non-prime attributes are fully dependent on the candidate keys.

Conversion of 1NF to 2NF:

• Identify the primary key for 1NF relation.
• Identify the functional dependencies in the relation.
• If partial dependencies exist on the primary key then we need to remove them by placing them in a new relation along with a copy of their determinant.

Third Normal Form (3NF)

A relation is in 3NF if and only if it is in 2NF and every non-key attribute is non-transitively dependent on the primary key. It removes transitive dependencies into a new relation. A relation schema R is in third normal form (3NF) if for all:

α→β in F⁺

At least one of the following holds:

• α→β is trivial (i.e.,β∈ α)
• α is a superkey for R
• Each attributes A in β - α is contained in a candidate key for R.

Note that each attribute may be in a different candidate key.

Conversion of 2NF to 3NF:

• Identify the primary key in the 2NF relation.
• Identify the functional dependencies in the relation.
• If transitive dependencies exist on the primary key then we need to remove them by placing them in a new relation along with a copy of their determinant.

Boyce-Codd Normal Form (BCNF)

BCNF refers to decompositions involving relations with more than one candidate key where the candidate keys are composite and overlapping. A relation is in BCNF if and only if every determinant is a candidate key. A determinant is any attribute whose value determines other values with a row. If a table contains only one candidate key, the 3NF and the BCNF are equivalent. BCNF is a special case of 3NF. A relation schema is in BCNF with respect to a set F of functional dependencies if for all functional dependencies in F⁺of the form

α→β

Whereα⊆ R andβ⊆ R, at least one of the following holds:

• α→β is trivial (i.e.,β⊆α)
• α is a superkey for R.

Example 1:

In UNF

ORDER (order-no, order-date, cust-no, cust-name, cust-add, prod-no, prod-desc, unit-price, ord-qty, line-total, order-total)

In 1NF

ORDER (order-no, order-date, cust-no, cust-name, cust-add, order-total)

PRODUCT_DES (order-no, prod-no,prod-desc, unit-price, ord-qty, line-total)

In 2NF

ORDER (order-no, order-date, cust-no, cust-name, cust-add, order-total)

PRODUCT_DES (prod-no,prod-desc)

ORDER-LINE-2 (order-no, prod-no,ord-qty, line-total)

In 3NF

PRODUCT-2 (prod-no,prod-desc, unit-price)

ORDER-LINE-2 (order-no, prod-no,ord-qty, line-total)

CUSTOMER-3 (cust-no, cust-name, cust-add)

ORDER-3 (order-no, order-date, cust-no, order-total)

Example 2:

In UNF

T(Patient_Num, F_name, L_name, Ward_num, ward_Name, Prescription_date, Drug_code, Drug_Name, Dosage, length_Of_Treatment)

In 1NF

T1(Patient_Num, F_name, L_name, Ward_num, ward_Name)

T2(Patient_Num,Prescription_Date, Drug_Code, Drug_Name, Dosage, length_Of_Treatment)

In 2NF

T1(Patient_Num, F_name, L_name, Ward_num, ward_Name)

T2(Patient_Num,Prescription_Date, Drug_Code,Dosage, length_Of_Treatment)

T3( Drug_Code, Drug_Name)

In 3NF

T1(Patient_Num, F_name, L_name, Ward_num)

T2(Patient_Num,Prescription_Date, Drug_Code,Dosage, length_Of_Treatment)

T3( Drug_Code, Drug_Name)

T4(Ward_num, ward_Name)

Example 1: BCNF

Relation R and functional dependency F

R = (branch_name, branch_city, assets, customer_name, loan_number, amount)

F = {branch_name→ assets branch_city, loan_number→ amountbranch_name}

Key = {loan_number, customer_name}

Decomposition:

• R₁ =(branch_name, branch_city, assets)
• R₂= (branch_name, customer_name, loan_number, amount)
• R₃ = (branch_name,loan_number, amount)
• R₄= (customer_name, loan_number)

Final decomposition: R₁, R₃, R₄

Example 2: BCNF

Let us assume the following reality

• For each student, each student is taught by one Instructor
• Each Instructor teaches only one subject
• Each Subject is taught by several Instructors

This relation is in 3NF but NOT in BCNF, so we should decompose so to meet BCNF property.

Domain-key Normal Form (DKNF)

R is said to be in DKNF if and only if every constraint of R is a logical consequence of domain constraints and key constraints. Ronald Fagin (1981) proved that if a Relation is in DKNF then it is free from any anomalies or redundancies including the ones caused by FDs, MVDs, JDs. DKNF is not always achievable and there is no formal definition to verify if a relation schema is in DKNF. Examples for DKNF are:

• Accounts whose account number begins with the digit 9 are special high-interest accounts with a minimum balance of 2500.
• General constraint: “If the first digit of t [account-number] is 9 then t [balance] ≥ 2500”.
• DKNF design:
  Regular-acct-schema = (branch-name, account-number, balance)
  Special-acct-schema = (branch-name, account-number, balance)
• Domain constraints for {Special-acct-schema} require that for each account:
  The account number begins with 9.
  The balance is greater than 2500.

References:

1. H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
2. A.K.Majumdar and p, Bhattacharya,"Database Management Systems",Tata McGraw Hill,India,2004
3. F.Korth, Henry. Database System Concepts. 6th edition.