Question

(Ch. 07) Consider the relation below: Car_Sales (Car_model (A), Equipment (B), Color (C), Price (D)),
with the data (only two Equipment options (Fully_equipped or Standard_equipped), and only two
colors (Red or Blue), but many different prices are possible) : You need to answer the following
questions based on the only values in the table below.
Car_model (A) Equipment (B) Color (C) Price (D)
A-001 Standard_equipped Red 12,000
A-001 Fully_equipped Blue 16,000
A-001 Fully_equipped Red 16,000
A-001 Standard_equipped Blue 12,000
A-002 Standard_equipped Red 10,000
A-002 Fully_equipped Blue 15,000
A-002 Fully_equipped Red 15,000
A-002 Standard_equipped Blue 10,000
1) Based on a common-sense understanding of the above data, what are the possible candidate
keys of this relation?
2) What is a functional dependency in the above (You do not need to list all possible FDs)?
3) Decompose the above relation into two smaller relations based on the functional dependency in
2).
4) List all multivalued dependencies in the result of 3).
5) What will the final decomposition look like?

Answer 1

1.)
- Candidate keys are those columns which take part in forming the primary key for a table / relation. It means that the candidate key/keys uniquely identify a row in the given table.

- From the given table Car_model ,Color and Equipment will be the candidate keys. It is so because, combination of these three values of a row will uniquely identify a cars information.
- As for the given data, if we only consider Car_model, it alone cannot be candidate key as there are repetetion of that value. Same is with other columns. So, there has to be a combination of candidate keys as defined above.


2.)
- Functionaly dependency is generally a relation between the candidate key and non candidate key.
- Here, we have 3 candidate keys and only 1 non candidate key (price).
- Functional Dependency -> ( Car_model ,Color ,Equipment ) ------> Price

3.)
There will be 2 relations:

i) Car_model , Color -----------> Carmodel has a color variant (Colors table)
iI) Car_Model , Equipment , Price ------------> As from the data, we can see that Price is depending on the equipment of the the carmodel. Therefore, knowing carmodel and equipment, we can have Price info.


4)
- Multivalue dependency means that the columns are independent in a table but they are dependent on some other table.
- In our case, multivalue dependency are:
Car_model -> Color (As we know each car model as 2 colors. So color is not dependent on Car_model)
Car_model -> Equipment (As we know each car model as 2 equipments. So equipmentsis not dependent on Car_model)
- We are not considering price as price is dependent on car_model as well as equipment


5.)
Final Decomposition has be provided in (3)



Kindly upvote if this helped