1. After examining the table carefully identify the functional dependencies 2. I

Question

1. After examining the table carefully identify the functional dependencies

2. Identify the candidate keys

3. Put the previous relation (table) in a normalized Boyce-Codd Normal Form (BCNF)

4. If you have multiple values for the department phone number what will be the best solution to convert the data into relations.

5. Secure the database by creating a third relation (table) for data users identifying the fields needed (you can assume the level of user access as needed)

First ress Hiring Date |Phone # 811998 1995 811998 6/1997 1/2001 ID Name Last Name email ExtensionDepartment ID John Smith Brian Miller James Miller John Jackson Robert Davis West 0001 0002 0003 0004 0005 Accountin Human resource 5775 Assistant Palm gt 5367 Buckrd 5432 5568 corner st 5642 hyahco.com min badams@gmail.com Production Sales Production milleryahoo.com ckson sam@gmail.com Person st Davisyahco.com Analyst Bridge 0006 Paul Thom 5744 Marketin 5/2003 11/199 st

Explanation / Answer

1. Identify the functional dependencies.

If an attribute of a table uniquely identifies another attribute of the same table , it is said to be dependent on each other.
Here, Employee ID attribute uniquely identifies other attributes of table.
Therefor, Functional dependencies are :
Employee ID -> First Name
Employee ID -> Last Name
Employee ID -> email
Employee ID -> title
Employee ID -> address
Employee ID -> Extension
Employee ID -> department
Employee ID -> department id
Employee ID -> hiring date
Employee ID -> department phone

2. Candidiate key is a column or a set of columns that can uniquely identifies tuples.
Here, Candidate key is "Employee id" as it uniquely identifies all the tuples.

3. BCNF IS to remove transitive functional dependency.X -> Y,X should be the super key of the table.
BCNF can be achieved by :

Table 1(Employee Details): Employee Info (Employee ID,First name,Last Name,email,title,address.extension,hiring date);
Table 2(Department details): Department phone number (department id,department,department phone);
Table 3(User Department Mapping) : (id,Employee id,Department id);

4. Column should have atomi values also . That is 1NF. if we have multiple values,we can split the table into two table.

After Splitting : ------

Table 1 : Employee Info (Employee ID,First name,Last Name,email,title,address.extension,department id,hiring date);
Table 2 : Department phone number (department id,department,department phone);

5. we need to create table Users :

Table 1(User Details): Employee Info (Employee ID,First name,Last Name,email,title,address.extension,hiring date);
Table 2(Department details): Department phone number (department id,department,department phone);
Table 3(User Department Mapping) : (id,Employee id,Department id);

Here , we used third table for connecting

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.