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.
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 nonkey attributes should
fully functionally dependent on the candidate key.
In our relation,
 Candidate key – (SSN, Car_Reg_No)
 Nonkey 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 nonkey attributes can
be determined by either of the key attributes without the help of the other key
attributes. For example, the nonkey attribute OName can be determined uniquely
by SSN alone without Car_Reg_No. Also, KM_Covered, Model, and Manufacturer nonkey
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.