Please visit, subscribe and share 10 Minutes Lectures in Computer Science

## 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.

## 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...

data recovery