## TOPICS (Click to Navigate)

`  I request GUEST WRITERS for this web site. Anyone interested please contact me at saravu2k2013@gmail.com Thanks  `

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

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;

• [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 advisors name and phone can be uniquely determined by service advisor id]

• [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) does not derive all the attributes of customer]

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

• [(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) 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.

Related Posts: