Find the primary key of a database table

Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? / Sets of examples to find the keys of a tables / Process of Key finding in a database – Examples

Question:

The following relation schema is used to store information about the customers of a car service center.

Customer (Customer_ID, CName, CAddr, Phone, Customer_Visit_Date, Service_Advisor_ID, SAName, SAPhone, No_of_Cars_Serviced, Service_Date)

A customer is identified with a unique Customer_ID, he can have only one phone number, and he may have visited the service center many times. There are more service advisors and each advisor services many cars on a given service date. Any service advisors can be reached at only one phone number.

Find the primary key for Customer.

Solution:

From the given information, we can derive the following set of functional dependencies;

• Customer_ID → CName CAddr Phone 
• [as customer id is unique, every id is related to exactly one name, one address, and one phone]

The FDs that do not hold Customer_ID → Customer_Visit_Date – This FD does not hold because a customer can visit many times. Hence, customer id cannot uniquely determine customer visit date. Customer_ID → Service_Advisor_ID – This FD does not hold because a might have been attended by different advisors during each of his visit. Service_Advisor_ID → Service_Date No_of_Cars_Serviced – This FD does not hold because a service advisor services every day with number of cars. Hence, we cannot uniquely determine the number of cars serviced or the service dates. Service_Date → No_of_Cars_Serviced – This FD does not hold because on a given service date other service advisors may have serviced many cars as well.

• Service_Advisor_ID → SAName SAPhone 
• [Service advisors name and phone can be uniquely determined by service advisor id]

• Service_Advisor_ID Service_Date → No_of_Cars_Servicesd 
• [if we know the service date and the advisor id, we can uniquely determine the number of cars serviced on a data by an advisor]

Finding closure of attributes/attributes sets

Normally, it is enough to find the closure for the LHS (Left Hand Side) attributes of the FDs. [Refer here to know how to find closure ofan attribute/attribute set]

(Customer_ID)⁺ = Customer_ID, CName, CAddr, Phone

• [(Customer_ID) does not derive all the attributes of customer]

(Service_Advisor_ID)⁺ = Service_Advisor_ID, SAName, SAPhone

• [(Service_Advisor_ID) does not derive all the attributes of customer]

(Service_Advisor_ID, Service_Date)⁺ = Service_Advisor_ID, SAName, SAPhone, Service_Date, No_of_Cars_Serviced

• [(Service_Advisor_ID, Service_Date) does not derive all the attributes of customer]

(Customer_ID, Service_Advisor_ID, Service_Date)⁺ = Customer_ID, CName, CAddr, Phone, Service_Advisor_ID, SAName, SAPhone, Service_Date, No_of_Cars_Serviced

• [(Customer_ID, Service_Advisor_ID, Service_Date) does not derive all the attributes of customer.]

Here customer visit date is missing. And this attribute is not part of any FDs. Hence, we can include customer_visit_date attribute to the left hand side by assuming the trivial FD, Customer_Visit_Date → Customer_Visit_Date.

Then we have,

(Customer_ID, Customer_Service_Date, Service_Advisor_ID, Service_Date)⁺ = Customer_ID, CName, CAddr, Phone, Customer_Service_Date, Service_Advisor_ID, SAName, SAPhone, Service_Date, No_of_Cars_Serviced

(Customer_ID, Customer_Service_Date, Service_Advisor_ID, Service_Date) derives all the attributes of Customer. Hence, this combination forms the key for Customer.

Customer_ID Customer_Visit_Date Service_Advisor_ID Service_Date → CName CAddr Phone SAName SAPhone No_of_Cars_Serviced - This FD holds on Customer

(Customer_ID Customer_Visit_Date Service_Advisor_ID Service_Date) is the key and it is a composite primary key.

Go back to Normalization – solved exercises page.

Related Posts:

Various uses of attribute closure

How to find Closure?