2NF, 3NF, and BCNF - Which is the correct normal form of the given relation.

Find the normal form of the given relation schema / Find keys of relational table / Normalize the relation to 2NF, 3NF, and BCNF

Question:

6. Consider the sample table CARS given below; here, SSN is the Social Security Number, OName is the name of the car owner, Car_Reg_No is the registration number of the car, KM_covered is the total number of kilometers the car travelled so far.

SSN	OName	Car_Reg_No	KM_Covered	Model	Manufacturer
123AV10	Steve	MH 01 AA 1100	1200	Figo	Ford
124CC23	Ramkumar	GJ 21 C 0025	10000	Figo	Ford
452PO90	Vishnu	TN 20 BC 1234	5000	Brezza	Maruti Suzuki
123AV10	Steve	MH 02 AB 1100	10000	Rapid	Skoda
323TY23	Sukumar	AP 12 C 2344	10289	Swift	Maruti Suzuki

Which of the following is TRUE for this table?

(a) CARS is in 2NF

(b) CARS is in 3NF

(c) CARS is in BCNF

(d) None of the above

Answer:

(d)None of the above
To find the normal of the given table, we need to find the set of functional dependencies that are holding in the given relation (table). Then we have to find the key for the given table.

From the given data, we can derive the following set of functional dependencies;

F = {SSN → OName, Car_Reg_No → KM_Covered, Model, Manufacturer}

The key for this relation will be,

(SSN)+ = SSN, OName

(Car_Reg_No)+ = Car_Reg_No, KM_Covered, Model, Manufacturer

(SSN, Car_Reg_No)+ = SSN, OName, Car_Reg_No, KM_Covered, Model, Manufacturer

The closure of (SSN, Car_Reg_No) identifies all the attributes of CARS. Hence, (SSN, Car_Reg_No) is the candidate key for CARS.

2NF – Table should be in 1NF and all non-key attributes should fully functionally dependent on the candidate key.

In our relation,

• Candidate key – (SSN, Car_Reg_No)

• Non-key attributes - OName, KM_Covered, Model, and Manufacturer

CARS is not in 2NF because of the following reasons;

The candidate key is the composite key of two attributes [SSN and Car_Reg_No]. The non-key attributes can be determined by either of the key attributes without the help of the other key attributes. For example, the non-key attribute OName can be determined uniquely by SSN alone without Car_Reg_No. Also, KM_Covered, Model, and Manufacturer non-key attributes can be determined by Car_Reg_No alone without SSN. This kind of functional dependency is called as partial functional dependency.

If a relation is not in 2NF, then we may not say that the relation is in further normal forms like 3NF, and BCNF.

         Previous Question                                                                                Next Question