Thursday, 10 March 2016

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_DateThis FD does not hold because a customer can visit many times. Hence, customer id cannot uniquely determine customer visit date.

Customer_ID Service_Advisor_IDThis 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_ServicedThis 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_ServicedThis 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.







2 comments:

  1. There seems to be a small error in your final answer. The final key should include Customer_Visit_Date and not 'Customer_Service_Date', as the latter is not an attribute of the original relation.

    ReplyDelete

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...