Companies manufacture ranges of products which are purchased by customers. The relation schema for this operation is given by:

81views

written 2.0 years ago by

binitamayekar ★ 6.4k

3NF Normalization

Companies manufacture a range of products that are purchased by customers.
The Relation Schema for this operation is given by:

Company (company_code#,company_name, director#, dircector_name, {product_name,cost,{cust#,customer_name,address}})

Where { } represents a repeating group and company_code, director and cust contain unique values.

First Normal Form (1NF) -

A relation will be 1NF if it contains an atomic value.
It states that an attribute of a table cannot hold repeating groups. - It must hold only single-valued attributes.
In the above relational schema for the company table, the repeating groups are the products and customers.
A company can have many products and many customers.
Now, remove these repeating groups.
Here, it is the product and customer for each company.
After removing all the attributes related to the products and customers, we left with Company, Products, and Customers tables.
The Company, Products, and Customers tables are now in first normal (1NF) form with the repeating group removed.
The three tables are shown below:

Company (company_code#, company_name, director#, dircector_name)

Products (product_name, cost)

Customers (cust#, customer_name, address)

Second Normal Form (2NF) -

A relation will be in 2NF if it is in 1NF.
In the second normal form, all non-key attributes are fully functional dependent on the primary key.
That means there is no partial dependency in relation exists between non-key and key attributes.
The three tables are already in 1NF.
Now, convert it into a second normal form (2NF).
First, identify the Primary Keys for the new tables.
In the Products table there is no primary key to avoid duplication or to identify data uniquely, therefore primary key called P_id is created for table Products.
Second, we find that in the Company table company_name can be identified by company_code and director_name can be identified by director independently.
This is called Partial Dependency, which is not allowed in the Second Normal Form.
Therefore, we broke the relation Company into three different tables.
The first table is Company whose primary key is Company_code.
The second table is named Director whose primary key is the Director.
The third table is named Company_Director to represent the relationship between Company and Director.
The primary keys of the Company and Director tables are referred to as Foreign Keys in this third table.
So that there exists No Partial Dependency.
Now, we have here a total of 5 separate tables, in that 3 tables Company, director, and Company_Director are linked with each other through the concept of foreign keys as discussed above.
The remaining two tables Products and Customers also need to link with each other and other tables through foreign key concepts.
To do this put the primary key of those tables from one table into other tables appropriately.
As in general single product can have multiple customers or customers can buy more than one product.
This can relate to the tables using a foreign key concept.
Therefore, we put the primary key of the Customers table Cust into the Products table.
Also, the primary key of Products table P_id into Company table.
This is shown below:

Company (Company_code#, Company_name, P_id)

Director (Director#, Dircector_name)

Company_Director (Company_code#, Director)

Products (P_id#, Product_name, Cost, Cust)

Customers (Cust#, Customer_name, Address)

Third Normal Form (3NF) -

A relation will be in 3NF if it is in 2NF and does not contain any transitive partial dependency.
All non-key attributes are fully functionally dependent on the primary key.
Let's check each relation carefully to whether any relation violates the 3NF rules or not.

Company (Company_code#, Company_name, P_id)

In Company relations no columns that aren’t dependent on the primary key.
There is no transitive partial dependency.

Director (Director#, Dircector_name)

In Director relations no columns that aren’t dependent on the primary key.
There is no transitive partial dependency.

Company_Director (Company_code#, Director)

In Company_Director relation no columns that aren’t dependent on the primary key.
There is no transitive partial dependency.

Products (P_id#, Product_name, Cost, Cust)

In Products relations no columns that aren’t dependent on the primary key.
There is no transitive partial dependency.

Customers (Cust#, Customer_name, Address)

In Customers relations no columns that aren’t dependent on the primary key.
There is no transitive partial dependency.

This represents that at this stage, there should be no anomalies in the third normal form (3NF).

3NF

ADD COMMENT EDIT