Assume that the following attributes are the primary keys for the tables: emp_co

Question

Assume that the following attributes are the primary keys for the tables:

emp_code is the primary key for Employee table

job_code is the primary key for the Job table

plan_code is the primary key for the Plan table

emp_code, plan_code is a composite primary key for the Benefit table

job_code, plan_code is the composite primary key for the Extra_Benefit table

(2 Points) Do all tables exhibit entity integrity? Answer yes or no and then explain your answer.

1.2 (2 Points) For each table in the database, identify foreign key(s) (if any). For each foreign key, state the referencing relation and the referenced relation.

1.3 (2 Points) Do all tables exhibit referential integrity? Answer yes or no and then explain your answer.

Explanation / Answer

1.

JOB ---- Primary Key -> JobCode, Foreign key -> none

PLAN---- Primary Key -> PlanCode, Foreign key -> none

BENEFIT---- Primary Key -> EmpCode,PlanCode, Foreign key ->EmpCode Reference EMPLOYEE table ,PlanCode Reference PLAN table

EXTRA_BENEFIT---- Primary Key -> EmpCode,PlanCode, Foreign key ->EmpCode Reference EMPLOYEE table ,PlanCode Reference PLAN table

EMPLOYEE---- Primary Key ->EmpCode, Foreign key -> JobCode Reference JOB table

Entity Integrity

JOB ---- violates entity integrity as JobCode 3 is repeated.

PLAN---- ensures entity integrity as PlanCode is unique and not null.

BENEFIT---- ensures entity integrity as EmpCode+PlanCode is unique and not null.

EMPLOYEE---- ensures entity integrity as EmpCode is unique and not null.

1.3.

Referential integrity

JOB ---- N/A, no foreign keys

PLAN---- N/A, no foreign keys

BENEFIT---- ensures referential integrity as EmpCode(15,16,17 and 20) exists in EMPLOYEE table , violates referential integrity for PlanCode(6) as it does not exists in PLAN table.

EMPLOYEE---- ensures referential integrity as JobCode(1,2,3) exists in JOB table.

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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