1.In the following table what is the main entities that need to be tracked in th
ID: 662941 • Letter: 1
Question
1.In the following table what is the main entities that need to be tracked in the system (1 point)
2.After examining the table carefully identify the functional dependences (2 points)
3.Identify the candidate keys (2 point)
Employee ID
First Name
Last Name
Extension
Department
Department ID
Hiring Date
Department Phone #
0001
John
Smith
jsmith@yahoo.com
5775
Accounting
2100
8/1998
407-366-5700
0002
Brian
Miller
badams@gmail.com
5367
Human resource
2300
4/1995
407-366-5300
0003
James
Miller
miller@yahoo.com
5432
Sales
2520
8/1998
407-366-5400
0004
John
Jackson
jackson_sam@gmail.com
5568
Marketing
2002
6/1997
407-366-5500
0005
Robert
Davis
Davis@yahoo.com
5642
Production
2890
1/2001
407-366-5600
0006
Paul
Thompson
thompsonp@yahoo.com
5744
Accounting
2100
5/2003
407-366-5700
4.Put the previous relation (table) in a normalized Boyce-Codd Normal Form (BCNF) (3 points)
5.If you have multiple values for the department phone number what will be the best solution to convert the data into relations.
Employee ID
First Name
Last Name
Extension
Department
Department ID
Hiring Date
Department Phone #
0001
John
Smith
jsmith@yahoo.com
5775
Accounting
2100
8/1998
407-366-5700
0002
Brian
Miller
badams@gmail.com
5367
Human resource
2300
4/1995
407-366-5300
0003
James
Miller
miller@yahoo.com
5432
Sales
2520
8/1998
407-366-5400
0004
John
Jackson
jackson_sam@gmail.com
5568
Marketing
2002
6/1997
407-366-5500
0005
Robert
Davis
Davis@yahoo.com
5642
Production
2890
1/2001
407-366-5600
0006
Paul
Thompson
thompsonp@yahoo.com
5744
Accounting
2100
5/2003
407-366-5700
Explanation / Answer
Employee ID and Department ID.
EmployeeIDFirstName,LastName
EmployeeIDHiringDate
EmployeeIDFirstName,LastName,HiringDate
DepartmentIDDepartment,HiringDate
EmployeeIDExtension,Department
EmployeeID,FirstName,LAstName
EmployeeID,Department
EmployeeID
A table complies with BCNF if it is in 3NF and any attribute is fully functionally dependent that is A B. (where Attribute 'A' is determinant).If every determinant is a candidate key, then it is said to be BCNF.
TABLE EMPLOYEE
Employee ID
First Name
Last Name
Extension
0001
John
Smith
jsmith(atrateof)yahoodotcom
5775
0002
Brian
Miller
badams(atrateof)maildotcom
5367
0003
James
Miller
miller(atrateof)yahoodotcom
5432
0004
John
Jackson
jackson_sam(atrateof)maildotcom
5568
0005
Robert
Davis
Davis(atrateof)yahoodotcom
5642
0006
Paul
Thompson
thompsonp(atrateof)yahoodotcom
5744
EMPLOYEE_TABLE DEPARTMENT
Employee ID
Department
Department ID
0001
Accounting
2100
0002
Human resource
2300
0003
Sales
2520
0004
Marketing
2002
0005
Production
2890
006
Accounting
2100
DEPARTMENT TABLE
Department
Department ID
Hiring Date
Department Phone #
Accounting
2100
8/1998
407-366-5700
Human resource
2300
4/1995
407-366-5300
Sales
2520
8/1998
407-366-5400
Marketing
2002
6/1997
407-366-5500
Production
2890
1/2001
407-366-5600
Accounting
2100
5/2003
407-366-5700
To convert our table into BCNF,
a)EMPLOYEE TABLE
Employee ID
First Name
Last Name
0001
John
Smith
jsmith(atrateof)yahoodotcom
0002
Brian
Miller
badams(atrateof)maildotcom
0003
James
Miller
miller(atrateof)yahoodotcom
0004
John
Jackson
jackson_sam(atrateof)maildotcom
0005
Robert
Davis
Davis(atrateof)yahoodotcom
0006
Paul
Thompson
thompsonp(atrateof)yahoodotcom
EmployeeID
Extension
0001
5775
0002
5367
0003
5432
0004
5568
0005
5642
0006
5744
b) EMPLOYEE_DEPARTMENT TABLE
Employee ID
Department
0001
Accounting
0002
Human resource
0003
Sales
0004
Marketing
0005
Production
006
Accounting
Employee ID
Department ID
0001
2100
0002
2300
0003
2520
0004
2002
0005
2890
006
2100
c) DEPARTMENT table
Department
Department ID
Accounting
2100
Human resource
2300
Sales
2520
Marketing
2002
Production
2890
Accounting
2100
Department ID
Hiring Date
Department Phone #
2100
8/1998
407-366-5700
2300
4/1995
407-366-5300
2520
8/1998
407-366-5400
2002
6/1997
407-366-5500
2890
1/2001
407-366-5600
2100
5/2003
407-366-5700
5.If we have multiple values for the department phone number what will be the best solution to convert the data into relations.
Department
Department ID
Accounting
2100
Human resource
2300
Sales
2520
Marketing
2002
Production
2890
Accounting
2100
Department Phone#
Department ID
407-366-5700
2100
407-366-5300
2300
407-366-5400
2520
407-366-5500
2002
407-366-5600
2890
407-366-5700
2100
Employee ID
First Name
Last Name
Extension
0001
John
Smith
jsmith(atrateof)yahoodotcom
5775
0002
Brian
Miller
badams(atrateof)maildotcom
5367
0003
James
Miller
miller(atrateof)yahoodotcom
5432
0004
John
Jackson
jackson_sam(atrateof)maildotcom
5568
0005
Robert
Davis
Davis(atrateof)yahoodotcom
5642
0006
Paul
Thompson
thompsonp(atrateof)yahoodotcom
5744
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.