Advanced Database Management System - Tutorials and Notes: Find the primary key of a database table

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

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents