Question

2. Consider the following – (employees only work in one building and only have one email account):

EMPLOYEE (EmployeeID, LastName, Building, Email, DegreesEarned)

a. Why isn’t Email used as the primary key? (2 points)

b. List any multi-valued dependencies. (2 points)

c. Write the table(s) to be in 4NF. (6 points)

Answer 1

Consider a sample EMPLOYEE table :

EmployeeID	lastname	Building	Email	DegreesEarned
1	Musk	XYZ	e1xyz@abc	34
2	Singh	XYZ	e2xyz@abc	45
3	Sharma	XYZ	e3xyz@abc	65

Since all employees work in same building ,they have same building name .

All the employees have only one email account and email accounts are always unique.

Let EmployeeID be primary key.

Answer A:

Using email as primary key has many disadvantages such as :

1.If you store information regarding users in multiple tables, the foreign keys to the users table are the e-mail address, meaning that you just store the e-mail address multiple times.

2.String comparison is slower than int comparison. It will matter if we use complicated queries with multiple joins.

3.People often change their email account thus the database needs to be updated each time.

Answer B:

No multi -value dependency exists. Each employee has only one value for last name , building name , email and DegreesEarned.

Answer C :

Table is already in 1st Normal form.

Since it doesn't have partial dependency because every non primary attribute depends on primary key so it is in 2nd NF also.

However this table does have transitive dependency i.e. a non-primary key derives a non-primary

(email->DegreesEarned).

So we will split the table into 2 tables as given below.

EmployeeID	lastname	Building
1	Musk	XYZ
2	Singh	XYZ
3	Sharma	XYZ

EmployeeID	Email	DegreesEarned
1	e1xyz@abc	34
2	e2xyz@abc	45
3	e3xyz@abc	65

Now the tabe is in 3NF.

Since both the tables doesn't have multi-value dependencies so they are in 4th NF