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:
Answer D, A, B, and
CThe 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 beused 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 remainthe 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.