For a table of the following first normalized form: (orderID, productID, partID,

Question

For a table of the following first normalized form:

(orderID, productID, partID, customerID, productName, partName, customerName, customerAddress, customerPhone), where (orderID, productID, partID) is the primary key for the table.

If we have the following dependencies:

orderID-> (customerID, customerName, customerAddress, customerPhone)

customerID-> (customerName, customerAddress, customerPhone)

productID-> productName

partID->partName

Question 1 Write down the second normalization form

Question 2 Write down the third normalization form

Explanation / Answer

Dear Student ,

As per the requirement submitted above , kindly find the below solution.Table given in the question is in the First Normal Form.

Normalization : Normalization is process of decomposing a large and complex table into simple and smaller form, with normalization redundancy will be removed and related data will be stored in related table.Before normalization data is in the text file or might be row data.

First Normal Form (1NF) : This normal form says all the columns in tables should be automic in nature that means all column should contain only one value , multivalued column is not allowed in first normal form.So above table given in the question is as follows

Table Name : OrderDetails

Schema : orderID,-Primary Key productID, partID, customerID, productName, partName, customerName, customerAddress, customerPhone

Second Normal Form (2NF) :This normal form says it should follow first normal form and all the columns in the table should depend upon primary key column this means partially dependancy is not allowed in second normal form.

In the above table orderDetails customer information is depends upon customerID , part information depends upon partID likewiese for order and prodcut information depends upon product and order hence above table needs to normalize into second normal form , so there is requirement to seprate above table as given below

1.Table Name : Customer

Schema :customerID - Primary Key ,customerName, customerAddress, customerPhone

2.Table Name : Product

Schema : productID-Primary Key, productName

3.Table Name : Part

Schema : partID-Primary Key ,partName

Third Normal Form (3NF) : In Third Normal Form (3NF), It should full fill all the requirements of second normal form and all the columns in the table should be depend upon primary key column. Transitive dependency are not allowed in the 3NF. Transitive dependency means non key column depends upon non key column in the table.

Here in the above tables needs to normalize into third normal form because relation amoung the tables needs to maintain for order this means primary key of one table is acting as foreign key in another table as shown below.

Table Name : Order

Schema : orderID-Primary Key,customerID-Foreign key,productID-Foreign key, partID-Foreign key

This means order table contains the order details and primary key of each of the above table is in the order table to form a relationship. So customer details can be accessed by using customerID and likewise for product and part details by using partID and productID.

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.