CODEWITHSUNDEEP
rdbmsdatabase-normalization3nf
Zaid Khan
Zaid Khan

Reputation: 826

Is normalization in 3NF applicable on a schema consisting of multiple candidate keys?

I have a relation consisting of the following attributes:

Employee: Emp_Id(Primary Key), Name, E_mail, Phone_Number, Date_Of_Joining, Address

I'm assuming that two people can have the same Name or Address, but not the same E_mail Id's or Phone_Number's(i.e. they should be unique).

So, according to what I know to normalize the table; I need to separate E-mail and Phone_Number information into a separate table (for 3NF):

From 3NF:

The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E.F. Codd in 1971.[2] Codd's definition states that a table is in 3NF if and only if both of the following conditions hold:

  • The relation R (table) is in second normal form (2NF)
  • Every non-prime attribute of R is non-transitively dependent on every key of R.

So I divide the main table into these resulting tables:

E_Mail Information: E_Mail_Id(Primary Key), E_Mail Address, ...

Contact/Phone Number Information: Phone_Id(Primary Key), Phone_Number, ...

(New)Employee: Emp_Id(Primary Key), Name, E_mail_Id(foreign key), Phone_Number_Id(foreign key), Date_Of_Joining, Address

My question(s) are

  1. Without dividing the relation as given above in order to achieve 3NF, could we have just let Employee be as it is without running into trouble (this question is only specific to the example I described above)?

  2. Even after dividing the table we might have values which despite being Foreign keys are unique(due to one-to-one relation) and therefore be considered as candidate keys in (New)Employee relation which are E_mail_Id and Phone_Number_Id. So wouldn't they violate 3NF?

Upvotes: 0

Views: 1441

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753695

Restating your assumptions

This is not intended to change your assumptions but merely to clarify them.

You have a relation schema:

Employee: Emp_Id, Name, E_mail, Phone_Number, Date_Of_Joining, Address

and you are stipulating for the purposes of this question that:

  1. Each employee has one email address and that is unique to them.
  2. Each employee has one phone number and that is unique to them.

Thus, you have three 'prime attributes' (in Codd's notation) or three Candidate Keys for this schema:

  1. Emp_Id
  2. E_mail
  3. Phone_number

Given an employee ID, the employee is uniquely determined; given the phone number, the employee is uniquely determined; given the email address, the employee is uniquely determined.

Your relation schema is in 3NF

If what's stated above is a correct interpretation of the relation schema, then your observation about "I need to separate E-mail and Phone_Number information into a separate table (for 3NF)" is wrong. There is no need to separate them.

Under the stated conditions, your relation schema is already in 3NF; indeed, it is in BCNF (Boyce-Codd Normal Form) too. The relation is in 2NF and there are no transitive dependencies.

Answer to Question 1

Yes — you could leave the table as it is because it is already in 3NF.

Answer to Question 2

No — because 3NF does not require a single candidate key, which you seem to think is necessary. Also, there's no particular requirement to store the Email ID in the main table; the Email address table would have a primary key which is the Employee ID, and doesn't need an Email ID number because email addresses are unique to the employee (under the rules of engagement for this question). Similarly for phone numbers.

In practice, an employee might have multiple email addresses, and may have multiple phone numbers, even just for their private use (separately from a corporate email address and corporate phone number). Under those circumstances, you'd have a 'non-empty list of email addresses' and a 'non-empty list of phone numbers' for a particular employee, and then you would need separate tables to record them. The Phone Number would be the primary key of the Phone Information table, and the Employee ID would be an FK in the phone number table; the Email Address would be the primary key of the Email table and the Employee ID would be an FK in the email address table.

Your relation schema would have to list these multiple entries somehow, and that wouldn't be 1NF, let alone 2NF or 3NF (under some reasonable assumptions for how the lists might be represented). And the 'non-empty' criterion would need careful enforcement.

Upvotes: 3

Related Questions