Does this database design fulfill 2NF or 3NF?

Question

Ok so i have 2 tables. First table's name is owner with

owner_id primary key
first_name
last_name

and i have another table car with

car_reg_no primary key
car_type
car_make
car_model
car_origin
owner_id foreign key

Is this design in 2NF or 3NF or neither ?

Answer 1

A 3NF means its in 2NF and there are no transitive functional dependencies. In a slightly more understandable terms: "all attributes depend on key, whole key, and nothing but the key".

The first table fulfills all that, so it's in 3NF.

The second table needs some analysis: are there functional dependencies on non-keys? Specifically, can there be the same car model belonging to a different make?

• If yes, then the functional dependency car_model -> car_make does not exist, and the table is in 3NF (unless some other dependency violates 3NF - see the comment on car_origin below).
• It no, then there there is car_model -> car_make which violates 3NF.

Also, what's the meaning of car_origin? If it functionally depends on a non-key, this could also violate the 3NF.

Answer 2

AFAIK, 2NF, due to interdependence of the fields of the car table. You would need a third table for car_type which lists make, model and origin, and a foreign car_type_id in the car table.