Advanced Database Management System - Tutorials and Notes: 2NF, 3NF, and BCNF - Which is the correct normal form of the given relation.

Thursday, 20 October 2016

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


No comments:

Post a comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents