Question

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
EMP_TITLE	Sales Agent	Sales Agent	Janitor	DB Admin
EMP_DOB	23-Dec-1978	28-Mar-1985	18-May-1982	20-Jul-1969
EMP_HIRE_DATE	14-Oct-2007	15-Jan-2016	21-Apr-2003	15-Jul-1999
EMP_TRAINING	L1, L2	L1	L1	L1, L3, L8, L15
EMP_BASE_SALARY	$45,255.00	$35,500.00	$25,750.00	$150,900.00
EMP_COMISSION_RATE	0.015	0.010

a) List all the attributes that contain multi-valued dependencies.

b) List all attributes that are not atomic.

c) List (if there are any) partial dependencies.

d) List (if there are any) transitive dependencies?

e) How many entities would you need to convert this data to 4NF? List the entities.

f) Draw the 4NF relational diagram.

Answer 1

A)EMP_DEPENDENTS and EMP_JOBCLASS both are independent of each other but both are dependent on EMP_num.

EMP_DOB and JOB_CLASS both are independent of each other but both are dependent on EMP_num.

THESE are multi-valued dependencies.

B)EMP_EDUCATION,EMP_DEPENDENTS,EMP_TRAINING are non atomic attributes.

C)EMP_LNAME and EMP_DEPENDENTS have partial dependency.

D)