Q5: ER Diagram Error Analysis & Correction

Database Management, Spring 2026 — Assignment #3

Part A: Original (Incorrect) ER Diagram

Original Design:

DEPARTMENT (DeptCode[PK], Name, Manager{ID-Number, Phone, Name})
EMPLOYEE (ID-Number[PK], Name, Phone, Project{Project-ID, Responsible Dept, ProjectName, StartDate, EndDate})
Relationship: Department HAS Employee (1:M)

✗ Error 1: Manager should NOT be a composite attribute of Department
（Manager 不應該是 Department 的複合屬性）

Manager is actually an Employee (has ID-Number, Phone, Name — same attributes as Employee).（Manager 其實是一個 Employee。）
Storing an entity's attributes inside another entity as a composite attribute creates data redundancy and violates ER modeling principles.（把一個 entity 的屬性存成另一個 entity 的複合屬性，會造成資料冗餘。）
Fix: Replace the Manager composite attribute with a MANAGES relationship between Employee and Department (1:1).（用 MANAGES relationship 取代。）

✗ Error 2: Project should NOT be an attribute of Employee
（Project 不應該是 Employee 的屬性）

Project has its own attributes (Project-ID, ProjectName, StartDate, EndDate), making it a full entity, not an attribute.（Project 有自己的屬性，應該是獨立的 Entity。）
An employee can work on multiple projects, and a project can have multiple employees — this is a M:N relationship that cannot be captured by a simple attribute.（員工和專案是 M:N 關係，不能用屬性表示。）
Fix: Create a PROJECT entity and a WORKS_ON relationship (M:N) between Employee and Project.（建立 PROJECT entity 和 WORKS_ON relationship。）

✗ Error 3: "Responsible Department" should NOT be an attribute of Project
（Responsible Department 不應該是 Project 的屬性）

This represents a relationship between Project and Department, not a simple attribute value.（這代表 Project 和 Department 之間的關係。）
Storing a foreign entity reference as an attribute violates ER modeling — relationships between entities should be modeled as relationship types.（把外部 entity 的引用存成屬性違反 ER 建模原則。）
Fix: Create a RESPONSIBLE_FOR relationship between Department and Project.（建立 RESPONSIBLE_FOR relationship。）

Component	Original (Incorrect)	Corrected
Manager	Composite attribute of Department ✗ Redundant, duplicates Employee data	MANAGES relationship (Employee — Department) ✓ 1:1, reuses existing Employee entity
Project	Composite attribute of Employee ✗ Cannot model M:N, has own PK	Separate PROJECT entity ✓ With Project-ID, ProjectName, StartDate, EndDate
Responsible Dept	Attribute inside Project composite ✗ Entity ref stored as attribute	RESPONSIBLE_FOR relationship (Dept — Project) ✓ 1:M, proper ER modeling
Employee-Project	Not modeled (Project is just an attribute) ✗	WORKS_ON relationship (M:N) ✓

Relationship	Cardinality	Explanation
HAS	Dept(1,1) — Emp(1,N)	Each employee belongs to exactly one department; each department has at least one employee.（每個員工屬於一個部門；每個部門至少有一個員工。）
MANAGES	Dept(1,1) — Emp(0,1)	Each department has exactly one manager; an employee may manage at most one department (most don't).（每個部門有一個經理；一個員工最多管理一個部門。）
WORKS_ON	Emp(0,N) — Proj(1,N)	An employee can work on many projects (or none); each project has at least one employee.（員工可以參與多個專案；每個專案至少有一個員工。）
RESPONSIBLE_FOR	Dept(0,N) — Proj(1,1)	Each project has exactly one responsible department; a department can be responsible for many projects.（每個專案有一個負責部門；一個部門可以負責多個專案。）

Assignment #3 — Database Management, Spring 2026 — 1134542 俞綱皓