<aside> 🧑🏻‍🦱 Student Name: 許恒睿 Student ID: 109704001

</aside>

1.a

Relation Model

# From To Relation Type
1. Region Store 1 : M
2. Store Employee 1 : M
3. Job Employee 1 : M

Business Model

  1. On a region, it is available for The Deal Co to build zero to many stores. However, a store is built only in one region. ⇒ 1 : M
  2. Inside a store of The Deal Co, the number of employees ranges from one to many ,while an employee is merely permitted to work at one store. ⇒ 1 : M
  3. A type of job requires many employees; nevertheless, an employ is permitted to do one kind of job. ⇒ 1 : M

1.b

123.drawio.png

2.

Revised Business Model

A driver can be allocated to many tasks. Driver : Task ⇒ 1 : M

Moreover, a truck is able to finish multiple tasks. Truck : Task ⇒ 1 : M

Revised ERD

123.drawio (3).png

3.a

TABLE PRIMARY KEY FOREIGN KEY(S)
EMPLOYEE EMP_CODE JOB_CODE
BENEFIT EMP_CODE and PLAN_CODE EMP_CODE, PLAN_CODE
JOB JOB_CODE NONE
PLAN PLAN_CODE NONE

3.b

TABLE ENTITY INTEGRITY EXPLANATION
EMPLOYEE YES Every entity is unique and not null in the primary key “EMP_CODE”
BENEFIT YES Every entity is unique and not null in the primary key “EMP_CODE and PLAN_CODE”
JOB YES Every entity is unique and not null in the primary key “JOB_CODE”
PLAN YES Every entity is unique and not null in the primary key “PLAN_CODE”