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

Companies manufacture ranges of products which 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 groups and company_code, and company_code,director# and cust# contains unique values. Normalize this relation to third normal form.

2
80views

## 3NF Normalization

• Companies manufacture a range of products that are purchased by customers.

• The Relation Schema for this operation is given by:

• 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)

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)

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.