a) The following table structure contains unsatisfactory components and characte

Question

a) The following table structure contains unsatisfactory components and characteristics. (For example, there are several multivalued attributes, some attributes are not atomic, and so on.) Given this structure, draw its dependency diagram. Label all transitive and/or partial dependencies.

b) Redesign the database to 1NF

c) Redesign the database to 2NF. Show all the steps.

d) Redesign the database to 3NF. Show all the steps.

Attribute name EMP CODE LAST NAME FIRST NAME EDUCATION DEPT CODE DEPARTMEN1T DEPT MANAGER JOB CLASS TITLE DEPENDENTS BIRTH DATE HIRE DATE TRAINING BASE SALARY OFFICE EXTENSION Sample value 1003 Willaker Taylor HS, BS DBADMIN Marketing Jill H. Martin 23 Database analyst Gerald (spouse), Mary (daughter), John (son) 12/23/92 10/14/2016 Level 1, level 2 $62,255 MCL1302 2600

Explanation / Answer

1 NF:

The columns dependents and training has multiple values hence it not in 1NF.

Need to split the table.

Emp_code, last_name,first_name,education,dept_code, department, dept_manager,job_class,title

Emp_code,education

Emp_code, depandants

Emp_code,training

2NF:

Since dept_manager,job_class,title is depandant on dept_code but the key attribute here is emp_code hence spilitng.

Emp_code, last_name, first_name

dept_code, department, dept_manager, job_class, title

Emp_code,education

Emp_code, depandants

Emp_code,training

Emp_code, dept_code

3NF:

Job_class is depandant on department, department is dependent on dept_code.

Hence a trasitive depandancy is occring hence splitting.

Emp_code, last_name, first_name

dept_code, department, dept_manager

Department, job_class, title

Emp_code,education

Emp_code, depandants

Emp_code,training

Emp_code, dept_code

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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