Consider the following relation: CAR_SALE( Car# , DateSold, Salesman# , Commissi

Question

Consider the following relation:

CAR_SALE(Car#, DateSold, Salesman#, Commission%, DiscountAmt). (Bolded ones are underlined*)

Assume that a car may be sold by multiple salesmen and hence {CAR#, Salesman#} is the primary key.

Suppose the following dependencies exist: DateSold -> DiscountAmt, and Salesman# -> Commission%

Is this relation in 1NF? Why or why not?

Is this relation in 2NF? Why or why not?

Is this relation in 3NF? Why or why not?

Please successively normalize this relation up to 3NF (include the detailed normalization steps for 1NF, 2NF, and 3NF, respectively). For each new relation obtained in the normalization/decomposition process, please underline the primary key of the relation.

Explanation / Answer

Dear Student ,

As per the requirement submitted above , kindly find the below solution.Initially let look at what is normalization and different forms of normalization.

Normalization :

Normalization is a the process of decomposing of complex and large data into smaller , simple tables.Before normalization data can be a row data or can be in the text file. By normalizing a table will get the advantages like removel of duplicate data , incorrect data can be avoided etc. In normalization forms are 1NF , 2NF , 3NF , BCNF , 4NF.

FIRST NORMAL FORM (1NF) :

All the columns in the table should be automic in nature, that means each column should contain one value & each row should contain the same number of columns. Repeating groups or duplicative columns are not allowed in the first normal form.

SECOND NORMAL FORM (2NF) :

It should full fill requirement of first normal form and all the columns in the table should functionally depend upon primary key column in the table. Partially dependancy are not allowed in second normal form.

THIRD NORMAL FORMS (3NF) :

It should full fill requirement of second normal form & all the columns in the table should depend upon primary key column. Transitive dependency are not allowed in third normal form. Transitive dependency means non key column depends upon non key column in the table.

Following is the relation given in the question.

CAR_SALE(Car#, DateSold, Salesman#, Commission%, DiscountAmt)

Car may be sold by multiple salesman so here {CAR#, Salesman#} is the primary key.

Above relation is in First Normal Form (1NF) as per the 1NF all the columns should be automic in nature as given above.

Normalization of above relation :

First Normal Form (1NF) :Below is the table as per relation given above

So relation in the table could be as given in below table

Second Normal Form (2NF) :

In second normal form (2NF) the above table will split into two tables like "Car" and "Salesman". Here DateSold and DiscountAmt is dependent on car and Commission is for salesman hence it is dependent upon Salesman. so above relation becomes as follows.

Table : Car

Schema : (Car-Primary key , DateSold , DiscountedAmt)

Table : Salesman

Schema : (Salesman -Primary Key ,Commission)

In salesman table salesman column is primary key , but as per defination of primark key , it should be unique in nature so need to remove second row and there is requirement to form relation between two tables , so need to normalize above tables in Third Normal Form (3NF)

Third Normal Form (3NF) :  

For maintaining relation between above tables relation will be formed using Foreign key as given below.

Table : Car

Schema : (Car -Primary key , DateSold , Commission,Salesman-Foreign key)

Table : Salesman

Schema : (Salesman-Primary key , Commission)

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

Car# DateSold Salesman# Commission% DiscountAmt 1 13/04/2018 1 20% 5600 2 13/04/2018 1 10% 5000 3 18/04/2018 2 5% 4000

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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