Question

Please help me answer the following questions regarding data relationship tables.

Employee ID	First Name	Last Name	email	Title	Address	Extension	Department	Department ID	Hiring Date	Department Phone #
0001	John	Smith	jsmith	Accountant	1300 West st	5775	Accounting	2100	8/1998	407-366-5700
0002	Brian	Miller	badams	Admin Assistant	1552 Palm dr	5367	Human resource	2300	4/1995	407-366-5300
0003	James	Miller	miller	Inventory Manager	2713 Buck rd	5432	Production	2520	8/1998	407-366-5400
0004	John	Jackson	jackson_sam	Sales Person	433 tree dr	5568	Sales	2102	6/1997	407-366-5500
0005	Robert	Davis	Davis	Manager	713 corner st	5642	Production	2520	1/2001	407-366-5400
0006	Paul	Thompson	thompsonp	Market Analyst	205 Bridge dr	5744	Marketing	2101	5/2003	407-366-5600
0007	Sandy	Davis	SDavis	Manager	713 Corner st	5702	Accounting	2100	11/1999	407-366-5700

1. List the major entities identified in the table above

2. After examining the table carefully identify candidate keys. Remember from the lecture that a candidate key field has to be unique, but should not hold private information that might compromise person's identity. For example SSN is unique and can be used to determine student information, so it is a candidate key, but using SSN might compromise student security for that it will not be used as primary key. The combination of first name and last name is not unique and cannot be used as a candidate key. Once the candidate keys been identified, some will be as primary keys and will be used to normalize the table. To connect the tables, the primary key of one table can be used as a foreign key in the other.

3. Identify the functional dependences. Functional dependencies uses the identified candidate key as determinant field to determine other attributes/fields in the table. For example student ID in the DSC system can pull student information including DOB, address, city, state, zip code, so on. This is what we call functional dependency. Student ID is a determinant field and a candidate key in this example.

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

5. If you have multiple values for the department phone number (the department has multiple phones), what will be the best solution to convert the data into relations. Review the lecture titled normalization before answering this question.

***Please answer ALL questions! For the questions that require tables, please post a table! Correct answers will get positive reviews, thanks!***

Answer 1

1. The major entities identified in the above relations are

Employee (EmployeeID, FirstName, LastName, Title, Address, Extension, HiringDate)

DepartmentInfo(Department, DepartmentID,DepartmentPhone)

Where the Primary keys of the entities are underlined.

------------------------------------------------------------------------------------------------------------------------------------------------------------------

2. The possible candidate key for the entities Employee is EmployeeID

the primary key for the table Department\info is DepartmentID

----------------------------------------------------------------------------------------------------------------------------------------------------------------

3. Set of functional Dependencies are as follows

EmployeeID ------------> FirstName, LastName

EmployeeID---------------> Email

EmployeeID----------> Address

DepartmentID---------> Department

DepartmentID-------->DepartmentPhone#

--------------------------------------------------------------------------------------------------------------------------------------------------------------

4. for the relation to be in BCNF the following condition must hold true

• For every FD's in the form of X------> Y X must be the superkey of R
• the relation must be in the 3NF form
• for the above-mentioned table the relation is only in 1NF form
• the primary key for the table is {EmployeeID, DepartmentID}
• the relation is in 2NF if there should not be any partial dependencies among the non-prime attribute with the primary key of the given table
• but from the above set of Functional dependencies, we can conclude that
• DepartmentID---------> Department
• so first let us decompose it into 2NF form
• Employee(EmployeeID,DepartmentID, FirstName, LastName, Email, Title, Address,Extension, HiriringDate)
• DepartmentInfo(Department, DepartmentID,DepartmentPhone)
• the decomposed table are now in 2nf as well as in 3NF form
• for the relation to be in 3NF form the following condition must be true

1. for every dependencies X-------->Y X must be the super key
2. there should not be any transitive dependencies among the non prime attributes
3. For every dependency X------>Y it must be trivial

hence the given decomposition is in 3NF as well as in BCNF form

-------------------------------------------------------------------------------------------------------------------------------------------------------

• 5. we need to find out the maximum number of the phone number that a department can have
• Accordingly, we have to create a separate attribute like PhoneNumber1, PhoneNumber2.............
• We can set the constraint like the First two are primary numbers that cannot be set null for any departments
• The rest can be set Null if they dont have that many numbers of phone