You work as a Database Designer for Tritech Inc. You are designing a table named Info. The table
contains the following attributes:
Login and Password
Name
ContactNumber
Address
Each new employee joining the company is assigned an E-mail Login and Password. This E-mail Login
and Password is used by each employee
to access his account on the company’s Web site. The value of both the attributes should be unique
and cannot be NULL. You design an
artificial key EmpID in the Info table to serve as a primary key. Which of the following factors lead
you to define an artificial key for use as a
primary key in the Info table?
Each correct answer represents a part of the solution. Choose all that apply.
A.
The value of the primary key cannot be NULL.
B.
The value of the primary key must be persistent.
C.
The natural key contains highly sensitive data.
D.
The value of the primary key must be unique.
Explanation:
The Name attribute cannot be used as a primary key, as two employees may have the same name in
a company. For an attribute to be used as a primary key, it must have a unique value.The primary
key attribute cannot contain NULL values. ContactNumber cannot be used as a primary key, for it is
possible that some employees in the company may not have a ContactNumber, which will result in
NULL values of ContactNumber for such employees. To be used as a primary key, a natural key must
contain a unique value. This is a prerequisite for a primary key. In addition to this, if the natural key
contains sensitive data, it cannot be used as a primary key. The primary key must be persistent, i.e.
the value of the attribute must remain the same throughout the lifetime of the instance. Therefore,
Address cannot be used as a primary key, as it is possible that an employee might change his house
in future and this may lead to a change in the value of the attribute. In the given scenario, Login and
Password of each employee is unique and cannot be NULL, but sharing this information with others
is not at all appropriate, as these are classified as sensitive data. If one employee comes to know
about the login and password of another employee, he can access his mail account and read his
confidential mails or even send mails from his account to other people. Therefore, all these factors
lead you to define an artificial key.
The primary key must be persistent, i.e. the value of the attribute must remain the same throughout the lifetime of the instance. Therefore, Address cannot be used as a primary key, as it is possible that an employee might change his house in future and this may lead to a change in the value of the attribute.