GROUP ASSIGNMENT 2 - Problem 1
Data Modeling Deliverable (ERD + Relational Schema)
Scope: Identify entities, attributes, relationships (with correct notation) and constraints derived from
the problem statement.
1. Requirement Interpretation
- The company has many employees working in departments.
- For each employee, store: employee number, full name, date of birth, gender, work start date, and
department.
- Maintain salary history per employee: effective-from date, salary coefficient, bonus coefficient,
notes.
- For each department, store: department code, department name, and which employee is the
department manager.
- Employees communicate with customers and are responsible for customer projects; when assigning
customers and projects to staff, store assignment start date.
- For each customer, store: customer code, full name, phone, address, email.
- For each project, store: project code, name, start date, end date, content, and lead manager (an
employee). One employee can lead multiple projects simultaneously.
2. Assumptions and Clarifications
- Each employee belongs to exactly one department at a time (the problem statement does not
mention multi-department assignment).
- Each department has exactly one manager; an employee may manage zero or one department
(typical organizational rule).
- Each project is associated with exactly one customer ("projects that the client deals with").
- Salary history is recorded as non-overlapping effective periods per employee; the key uses (EmpID,
EffectiveFrom).
- Assignment represents the staff-in-charge mapping between Employee, Customer, and Project and
stores AssignedFrom (start date). Re-assignments over time are allowed.
Entity-Relationship Diagram (Crow's Foot Notation)
DEPARTMENT EMPLOYEE SALARY_HISTORY
PK DeptCode PK EmpID PK (EmpID, EffectiveFrom)
DeptName works_in FullName salary FK EmpID
manager
FK ManagerEmpID DOB SalaryCoeff
Gender BonusCoeff
WorkStartDate Notes
FK DeptCode
leads
PROJECT ASSIGNMENT
CUSTOMER
PK ProjectCode PK (EmpID, CustomerCode,
PK CustomerCode ProjectName ProjectCode, AssignedFrom)
FullName has StartDate FK EmpID
Phone EndDate FK CustomerCode
Address Content FK ProjectCode
Email FK CustomerCode AssignedFrom
FK LeadManagerEmpID
Legend: | = one, O| = zero or one, < = many, O< = zero or many
3. Entities and Attributes
Entity Primary Key Key Attributes (non-exhaustive)
DEPARTMENT DeptCode DeptName, ManagerEmpID (FK)
EMPLOYEE EmpID FullName, DOB, Gender, WorkStartDate, DeptCode (FK)
SALARY_HISTORY (EmpID, EffectiveFrom) SalaryCoeff, BonusCoeff, Notes
CUSTOMER CustomerCode FullName, Phone, Address, Email
PROJECT ProjectCode ProjectName, StartDate, EndDate, Content, CustomerCode
(FK), LeadManagerEmpID (FK)
ASSIGNMENT (EmpID, CustomerCode, AssignedFrom (start date of assignment)
ProjectCode,
AssignedFrom)
Notes: PK = Primary Key, FK = Foreign Key. Composite keys are written in parentheses.
4. Relationships and Cardinalities
Relationship Cardinality Explanation
DEPARTMENT works_in DEPARTMENT 1 --- N A department has many employees; each
EMPLOYEE EMPLOYEE employee belongs to one department.
DEPARTMENT managed_by DEPARTMENT 1 --- 1 Each department has one manager; an employee
EMPLOYEE EMPLOYEE (role: may manage at most one department
Manager); EMPLOYEE (assumption).
0..1
EMPLOYEE has EMPLOYEE 1 --- N Salary records are maintained over time for each
SALARY_HISTORY SALARY_HISTORY employee.
CUSTOMER owns PROJECT CUSTOMER 1 --- N A customer can have multiple projects; each
PROJECT project belongs to one customer.
EMPLOYEE leads PROJECT EMPLOYEE 1 --- N A project has one lead manager; an employee
PROJECT can lead many projects.
ASSIGNMENT links EMPLOYEE- Each of EMPLOYEE/CUS Assignment is a ternary association that stores
CUSTOMER-PROJECT TOMER/PROJECT 1 --- N AssignedFrom (start date).
ASSIGNMENT
5. Business Rules and Constraints
- BR1: DeptCode in EMPLOYEE must reference an existing DEPARTMENT.
- BR2: Department manager must be an existing EMPLOYEE; recommended rule: manager belongs
to the same department.
- BR3: For each employee, salary history is uniquely identified by (EmpID, EffectiveFrom).
EffectiveFrom values must not duplicate per employee.
- BR4: PROJECT must reference exactly one CUSTOMER and one lead manager EMPLOYEE.
- BR5: ASSIGNMENT must reference valid EMPLOYEE, CUSTOMER, and PROJECT records and
store AssignedFrom.
- BR6: Consistency recommendation: the customer in ASSIGNMENT should match the customer of
the project ([Link] = [Link]).
6. Relational Schema (PK/FK)
DEPARTMENT(DeptCode, DeptName, ManagerEmpID FK->[Link])
EMPLOYEE(EmpID, FullName, DOB, Gender, WorkStartDate, DeptCode
FK->[Link])
SALARY_HISTORY(EmpID FK->[Link], EffectiveFrom, SalaryCoeff, BonusCoeff,
Notes, PK(EmpID, EffectiveFrom))
CUSTOMER(CustomerCode, FullName, Phone, Address, Email)
PROJECT(ProjectCode, ProjectName, StartDate, EndDate, Content, CustomerCode
FK->[Link], LeadManagerEmpID FK->[Link])
ASSIGNMENT(EmpID FK->[Link], CustomerCode FK->[Link],
ProjectCode FK->[Link], AssignedFrom, PK(EmpID, CustomerCode, ProjectCode,
AssignedFrom))
Prepared on 2025-12-29