The table structure below contains many unsatisfactory components and characteri
ID: 3670782 • Letter: T
Question
The table structure below contains many unsatisfactory components and characteristics. For example, there are several multivalued attributes and some attributes are not atomic.
Table: Sample EMPLOYEE Records
Attribute Name
Sample Value
Sample Value
Sample Value
Sample Value
EMP_NUM
1003
1018
1019
1023
EMP_LNAME
Willaker
Smith
McGuire
McGuire
EMP_EDUCATION
BBA, MBA
BBA
BS, MS, Ph.D.
JOB_CLASS
SLS
SLS
JNT
DBA
EMP_DEPENDENTS
Gerald (spouse),
Mary (daughter),
John (son)
JoAnne (spouse)
George (spouse)
Jill (daughter)
DEPT_CODE
MKTG
MKTG
SVC
INFS
DEPT_NAME
Marketing
Marketing
General Service
Info. Systems
DEPT_MANAGER
Jill H. Martin
Jill H. Martin
Hank B. Jones
Carlos G. Ortez
JOB_TITLE
Sales Agent
Sales Agent
Janitor
DB Admin
EMP_DOB
23-Dec-1968
28-Mar-1979
18-May-1982
20-Jul-1959
EMP_HIRE_DATE
14-Oct-1997
15-Jan-2006
21-Apr-2003
15-Jul-1999
EMP_TRAINING
L1, L2
L1
L1, L3, L8, L15
EMP_SALARY
$38,255.00
$30,500.00
$19.750.00
$127,900.00
b. Draw the dependency diagrams that are in 3NF. (Hint: You might have to create some new tables and columns. Also make sure that the new dependency diagrams contain attributes that meet proper design criteria, e.g., there are no multivalued attributes)
Attribute Name
Sample Value
Sample Value
Sample Value
Sample Value
EMP_NUM
1003
1018
1019
1023
EMP_LNAME
Willaker
Smith
McGuire
McGuire
EMP_EDUCATION
BBA, MBA
BBA
BS, MS, Ph.D.
JOB_CLASS
SLS
SLS
JNT
DBA
EMP_DEPENDENTS
Gerald (spouse),
Mary (daughter),
John (son)
JoAnne (spouse)
George (spouse)
Jill (daughter)
DEPT_CODE
MKTG
MKTG
SVC
INFS
DEPT_NAME
Marketing
Marketing
General Service
Info. Systems
DEPT_MANAGER
Jill H. Martin
Jill H. Martin
Hank B. Jones
Carlos G. Ortez
JOB_TITLE
Sales Agent
Sales Agent
Janitor
DB Admin
EMP_DOB
23-Dec-1968
28-Mar-1979
18-May-1982
20-Jul-1959
EMP_HIRE_DATE
14-Oct-1997
15-Jan-2006
21-Apr-2003
15-Jul-1999
EMP_TRAINING
L1, L2
L1
L1, L3, L8, L15
EMP_SALARY
$38,255.00
$30,500.00
$19.750.00
$127,900.00
Explanation / Answer
To Draw the dependency diagrams for above table structure in 3NF,first we must find out transitive dependencies in the above table.In detail Third Normal Form deals with ‘transitive’ dependencies. This means if we have a primary key A and a non-key domain B and C where C is more dependent on B than A and B is directly dependent on A, then C can be considered transitively dependant on A.
In the first we draw the Dependency Diagram for above table
EMP_CODE
EMP_LNAME
EMP_EDUCATION
DEPT_CODE
DEPT_NAME
DEPT_MANAGER
Transitive dependencies
EMP_DEPENDENTS
EMP_DOB
EMP_HIRE_DATE
EMP_TRAINING
JOB_TITLE
JOB_CLASS
EMP_BASE_SALARY
EMP_COMMISSION_RATE
Transitive dependencies
The relational schema is
EMPLOYEE(EMP_CODE, EMP_LNAME, EMP_EDUCATION, JOB_CLASS, EMP_DEPENDENTS,DEPT_CODE, DEPT_NAME, DEPT_MANAGER, EMP_TITLE, EMP_DOB, EMP_HIRE_DATE, EMP_TRAINING, EMP_BASE_SALARY, EMP_COMMISSION_RATE)
Now in the second step we have break up the dependency diagram for produced dependency diagram in 3NF. For this find out Dependency diagrams have no way to indicate multi-valued attributes, nor do they provide the means through which such attributes can be handled.
EMPLOYEE
EMP_CODE
EMP_LNAME
DEPT_CODE
JOB_CLASS
EMP_DOB
EMP_HIRE_DATE
DEPARTMENT
DEPT_CODE
DEPT_NAME
EMP_CODE
QUALIFICATION
EMP_CODE
EDU_CODE
QUAL_DATE
DEPENDENT
EMP_CODE
DEP_NUM
DEP_FNAME
DEP_TYPE
JOB
JOB_CLASS
JOB_TITLE
JOB_BASE_SALARY
EDUCATION
EDUC_CODE
EDUC_DESCRIPTION
The relational schemas are written as:
EMPLOYEE(EMP_CODE, EMP_LNAME, DEPT_CODE, JOB_CLASS, EMP_DOB, EMP_HIREDATE)
DEPENDENT(EMP_CODE, DEP_NUM, DEP_FNAME, DEP_TYPE)
DEPARTMENT(DEPT_CODE, DEPT_NAME, EMP_CODE)
JOB(JOB_CLASS, JOB_TITLE, JOB_BASE_SALARY)
EDUCATION(EDUC_CODE, EDUC_DESCRIPTION)
QUALIFICATION(EMP_CODE, EDUC_CODE, QUAL_DATE_EARNED)
EMP_CODE
EMP_LNAME
EMP_EDUCATION
DEPT_CODE
DEPT_NAME
DEPT_MANAGER
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.