Employee ID First Name Last Name email Title Address Extension Department Depart
ID: 3870451 • Letter: E
Question
Employee ID
First Name
Last Name
Title
Address
Extension
Department
Department ID
Hiring Date
Department Phone #
0001
John
Smith
jsmith@yahoo.com
Accountant
1300 West st
5775
Accounting
2100
8/1998
407-366-5700
0002
Brian
Miller
badams@outlook.com
Admin Assistant
1552 Palm dr
5367
Human resource
2300
4/1995
407-366-5300
0003
James
Miller
miller@yahoo.com
Inventory Manager
2713 Buck rd
5432
Production
2520
8/1998
407-366-5400
0004
John
Jackson
jackson_sam@outlook.com
Sales Person
433 tree dr
5568
Sales
2102
6/1997
407-366-5500
0005
Robert
Davis
Davis@yahoo.com
Manager
713 corner st
5642
Production
2520
1/2001
407-366-5400
0006
Paul
Thompson
thompsonp@yahoo.com
Market Analyst
205 Bridge dr
5744
Marketing
2101
5/2003
407-366-5600
0007
Sandy
Davis
SDavis@yahoo.com
Manager
713 Corner st
5702
Accounting
2100
11/1999
407-366-5700
1. After examining the table carefully identify the functional dependences
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)
Employee ID
First Name
Last Name
Title
Address
Extension
Department
Department ID
Hiring Date
Department Phone #
0001
John
Smith
jsmith@yahoo.com
Accountant
1300 West st
5775
Accounting
2100
8/1998
407-366-5700
0002
Brian
Miller
badams@outlook.com
Admin Assistant
1552 Palm dr
5367
Human resource
2300
4/1995
407-366-5300
0003
James
Miller
miller@yahoo.com
Inventory Manager
2713 Buck rd
5432
Production
2520
8/1998
407-366-5400
0004
John
Jackson
jackson_sam@outlook.com
Sales Person
433 tree dr
5568
Sales
2102
6/1997
407-366-5500
0005
Robert
Davis
Davis@yahoo.com
Manager
713 corner st
5642
Production
2520
1/2001
407-366-5400
0006
Paul
Thompson
thompsonp@yahoo.com
Market Analyst
205 Bridge dr
5744
Marketing
2101
5/2003
407-366-5600
0007
Sandy
Davis
SDavis@yahoo.com
Manager
713 Corner st
5702
Accounting
2100
11/1999
407-366-5700
Explanation / Answer
1)
Functional dependencies are:
EmployeeID---->First Name, Last Name, email,Title,Address,extension
EmployeeID,Title----->DepartmentID
DepartmentID---->Department,DepartmentPhhone#
EmployeeID,Department----->HiringDate.
2)Cadidate keys: Candidate keys are noting but minimal super keys. So we are goint to find super keys using clousures of functional dependencies:
{EmployeeID}+ ------->{EmployeeID, First Name, Last Name, email,Title,Address,extension,DepartmentID,Department,DepartmentPhhone#,HiringDate}
{EmployeeID,Title}+---->{EmployeeID, First Name, Last Name, email,Title,Address,extension,DepartmentID,Department,DepartmentPhhone#,HiringDate}
{DepartmentID}+ ---->{DepartmentID,Department,DepartmentPhhone#}
{EmployeeID,DepartmentID}+-->{EmployeeID, First Name, Last Name, email,Title,Address,extension,DepartmentID,Department,DepartmentPhhone#,HiringDate}
{EmployeeID,Department}+--->{EmployeeID, First Name, Last Name, email,Title,Address,extension,DepartmentID,Department,DepartmentPhhone#,HiringDate}
Here the minimal superkey is {EmployeeID}. Therefore candidate key is {EmployeeID}
3) The above realtional schema is not is BCNF, because {department,depertment phhonenumber} are not completely dependent on {EmployeeID}
So by, performing normalization, the relational schema would be
EMPLOYEE(EmployeeID, First Name, Last Name, email,Title,Address,extension,DepartmentID,HiringDate)
DEPARTMENT(DepartmentID,Department,DepartmentPhhone#}
4) After parforming BCNF noramalization there is no problem with multiple values of DepartmentPhonenumber#
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.