Add normalization for question 5

3 files changed, 73 insertions, 2 deletions

diff --git a/assignments/1/README.md b/assignments/1/README.md
index df7e3a1..35894cc 100644
--- a/assignments/1/README.md
+++ b/assignments/1/README.md
@@ -142,6 +142,8 @@ For each of the following relations, identify the Normal Form(s) each relation s
 
 Consider a one-relation database with the following attributes:
 
+![one-relation](./question-5-before.png)
+
 * Employee number (emp_no),
 * Date hired (date),
 * Job title (job),
@@ -156,6 +158,7 @@ Consider a one-relation database with the following attributes:
 * and Department manager employee number (mgr_emp_no).
 
 The following business rules apply:
+
 * No employee can manage more than one department at a time.
 * No employee can work in more than one department at a time.
 * No employee can work on more than one project at a time.
@@ -163,8 +166,8 @@ The following business rules apply:
 * No employee can have more than one phone at a time.
 * No employee can have more than one job at a time.
 * No project can be assigned to more than one department at a time.
-* No office can be assigned to more than one department at a time.
 * Department numbers, employee numbers, project numbers, office numbers, and phone numbers are all "globally" unique.
+* No office can be assigned to more than one department at a time.
 
 The following functional dependencies also apply:
 
@@ -177,7 +180,32 @@ The following functional dependencies also apply:
 
 Transform this relation into 3NF. Justify any decomposition.
 
-  ![one-relation](./associative-entity.png)
+  ![one-relation](./question-5-after.png)
+
+* Each employee belongs to a single department
+* Each department can only have a single manager. To enforce this a unique constraint can be applied on `employees(department_id, manager = 1)`.
+
+```SQL
+CREATE TABLE employees(
+  id bigint primary key,
+  department_id bigint references departments(id),
+  project_id bigint references projects(id),
+  hired_at datetime,
+  title varchar(255),
+  phone_number varchar(255) NOT NULL,
+  office_number varchar(255) NOT NULL,
+  area varchar(255),
+  salary decimal,
+  manager bit
+);
+
+ALTER TABLE employees ADD CONSTRAINT departments_fk FOREIGN KEY (department_id) REFERENCES departments(id);
+ALTER TABLE employees ADD CONSTRAINT projects_fk FOREIGN KEY (project_id) REFERENCES projects(id);
+ALTER TABLE projects ADD CONSTRAINT departments_fk FOREIGN KEY (department_id) REFERENCES departments(id);
+CREATE UNIQUE INDEX managers_idx on employees(department_id, manager) WHERE manager IS NULL;
+CREATE UNIQUE INDEX employee_office_idx on employees(office_number);
+CREATE UNIQUE INDEX employee_phone_idx on employees(phone_number);
+```
 
 ### Question 6 (15 marks)
 
diff --git a/assignments/1/question-5-after.png b/assignments/1/question-5-after.png
new file mode 100644
index 0000000..d3b87cf
--- /dev/null
+++ b/assignments/1/question-5-after.png
diff --git a/assignments/1/question-5-after.uml b/assignments/1/question-5-after.uml
new file mode 100644
index 0000000..ae7ba7c
--- /dev/null
+++ b/assignments/1/question-5-after.uml
@@ -0,0 +1,43 @@
+@startuml
+!define Table(name,desc) class name as "desc" << (T,#FFAAAA) >>
+!define primary_key(x) <b>x</b>
+!define foreign_key(x) <color:blue>x</color>
+!define composite_key(x,y) <b>x, y</b>
+!define unique(x) <color:green>x</color>
+!define not_null(x) <u>x</u>
+hide methods
+hide stereotypes
+
+Table(employees, "employee") {
+  primary_key(id) integer
+  foreign_key(department_id) integer
+  foreign_key(project_id) integer
+  hired_at datetime
+  title varchar[255]
+  phone_number varchar[255]
+  office_number varchar[255]
+  area varchar[255]
+  salary decimal
+  manager bit
+}
+
+Table(departments, "department") {
+  primary_key(id) integer
+  budget decimal
+}
+
+' Table(department_managers, "department_manager") {
+'   composite_key(employee_id, department_id) integer
+' }
+
+Table(projects, "project") {
+  primary_key(id) integer
+  foreign_key(department_id) integer
+  budget decimal
+}
+
+' department_managers --> departments : "belongs to"
+' department_managers --> employees : "belongs to"
+employees --> departments : "belongs to"
+projects --> departments : "belongs to"
+@enduml