Read the following scenario carefully: Banyon Corporation currently keeps track

Question

Read the following scenario carefully:

Banyon Corporation currently keeps track of employee data in a spreadsheet table. However, they see that the single-table approach is not working well since there is duplication of data and various anomalies arise as new rows are inserted, old rows deleted, and current rows updated. Here are the current column headers for Banyon%u2019s spreadsheet table, Employees.

EMPLOYEES (Emp_id, Emp_name, Emp_phone, Dept_name, Dept_phone, Dept_manager, Skill_id, Skill_name, Skill_date, Skill_level)

Employees

Banyon has decided to use a database rather than a single-table spreadsheet. They conducted the following analysis. Each employee is identified by a unique emp_id. Knowing an emp_id, you can determine the corresponding employee name, phone and department in which the employee works. Department names are also unique. Knowing a dept_name you can determine the department%u2019s phone and manager. Each employee also posess a set of skills and they must periodically update each skill by passing a test that certifies a certain skill level at a given point in time (skill_date). Any given skill can be identified by a unique skill_id.

Here are your tasks for this assignment:

2) List any multivalued dependencies you have identified

3) List any functional dependencies you have identified.

4) Use the proper design principles to remove any multivalued dependencies from the Employees table.

5) Put the Employees table into BCNF.

Explanation / Answer

// i cannot explain you the whole splitting process but can show you brief splitting steps

functional dependencies:-

1. Emp_id ---> Emp_name, Emp_phone,Dept_name

2. Dept_name ----> Dept_phone, Dept_manager

3. Skill_id ----> Skill_name, Skill_date, Skill_level

when we split the given large employee table to satisfy bcnf , we wiil get folowing tables

1. employee(Emp_id,Emp_name, Emp_phone,Dept_name,skill_Id)

2. department(Dept_name ,Dept_phone, Dept_manager)

3. Skill(Skill_id ,Skill_name, Skill_date, Skill_level)

There are some multivalued dependencies in above created tables so we have to again split the tables

multivalued dependencies In employee table :-

1. Emp_id,Emp_name, Dept_name -->---> Emp_phone

2. Emp_id,Emp_name, Dept_name -->--> skill_id

so when we remove above two multivalued dependencies, the table we will get is

1.Employee(Emp_id,Emp_name, Dept_name)

2. Employee_Phone(Emp_id,Emp_phone)

3. Employee_Skill(Emp_id,skill_id)

multivalued dependencies In department table table :-

1. Dept_name , Dept_manager -->---> Dept_phone

so when we remove above two multivalued dependencies, the table we will get is

4,Department( Dept_name,Dept_manager)

5. Department_Phone(Dept_name,Dept_phone)

so the resultant tables we created after splitting the large employee table is:--

1.Employee(Emp_id,Emp_name, Dept_name)

2. Employee_Phone(Emp_id,Emp_phone)

3. Employee_Skill(Emp_id,skill_id)

3,Department( Dept_name,Dept_manager)

4. Department_Phone(Dept_name,Dept_phone)

5. Skill(Skill_id ,Skill_name, Skill_date, Skill_level)

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

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