Change schema for question 5

4 files changed, 47 insertions, 59 deletions

diff --git a/assignments/1/README.pdf b/assignments/1/README.pdf
index ce52805..9ef17ec 100644
--- a/assignments/1/README.pdf
+++ b/assignments/1/README.pdf
diff --git a/assignments/1/question-5-after.png b/assignments/1/question-5-after.png
index be5df7e..cba2ed6 100644
--- a/assignments/1/question-5-after.png
+++ b/assignments/1/question-5-after.png
diff --git a/assignments/1/question-5-after.puml b/assignments/1/question-5-after.puml
index 4f7ebba..ff5c66c 100644
--- a/assignments/1/question-5-after.puml
+++ b/assignments/1/question-5-after.puml
@@ -15,15 +15,13 @@ Table(employees, "employee") {
   foreign_key(project_id) integer
   hired_at datetime
   title varchar[255]
-  foreign_key(phone_number) varchar[255]
-  foreign_key(office_number) varchar[255]
   area varchar[255]
   salary decimal
 }
 
 Table(departments, "department") {
   primary_key(id) integer
-  foreign_key(manager_id) bit
+  foreign_key(manager_id) integer
   budget decimal
 }
 
@@ -35,10 +33,12 @@ Table(projects, "project") {
 
 Table(phones, "phone") {
   primary_key(number) varchar[255]
+  foreign_key(employee_id) integer
 }
 
 Table(offices, "office") {
   primary_key(number) varchar[255]
+  foreign_key(employee_id) integer
 }
 
 employees --> departments : "belongs to"
diff --git a/assignments/1/run.sql b/assignments/1/run.sql
index 1fb0061..b258bd6 100644
--- a/assignments/1/run.sql
+++ b/assignments/1/run.sql
@@ -1,10 +1,25 @@
-DROP TABLE IF EXISTS department_employees CASCADE;
-DROP TABLE IF EXISTS employees CASCADE;
+ALTER TABLE IF EXISTS employees DROP CONSTRAINT departments_fk;
+ALTER TABLE IF EXISTS employees DROP CONSTRAINT projects_fk;
+
 DROP TABLE IF EXISTS projects CASCADE;
 DROP TABLE IF EXISTS departments CASCADE;
+DROP TABLE IF EXISTS phones CASCADE;
+DROP TABLE IF EXISTS offices CASCADE;
+DROP TABLE IF EXISTS employees CASCADE;
+
+CREATE TABLE employees (
+  id bigint primary key,
+  department_id bigint,
+  project_id bigint,
+  hired_at date,
+  title varchar(255),
+  area varchar(255),
+  salary decimal
+);
 
 CREATE TABLE departments (
   id bigint primary key,
+  manager_id bigint references employees(id),
   budget decimal
 );
 
@@ -14,75 +29,48 @@ CREATE TABLE projects (
   budget decimal
 );
 
-CREATE TABLE employees (
-  id bigint primary key,
-  project_id bigint references projects(id),
-  hired_at date,
-  title varchar(255),
-  phone_number varchar(255) NOT NULL,
-  office_number varchar(255) NOT NULL,
-  area varchar(255),
-  salary decimal
+CREATE TABLE phones (
+  number varchar(255) primary key,
+  employee_id bigint references employees(id)
 );
 
-CREATE TABLE department_employees (
-  department_id bigint references departments(id),
-  employee_id bigint references employees(id),
-  manager bit,
-  PRIMARY KEY (department_id, employee_id)
+CREATE TABLE offices (
+  number varchar(255) primary key,
+  employee_id bigint references employees(id)
 );
 
 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 department_manager_idx on departments(manager_id);
 
-CREATE UNIQUE INDEX managers_idx on department_employees(employee_id);
-CREATE UNIQUE INDEX employee_office_idx on employees(office_number);
-CREATE UNIQUE INDEX employee_phone_idx on employees(phone_number);
-
-
-CREATE OR REPLACE FUNCTION ensure_each_department_has_one_manager() RETURNS trigger AS $$
-BEGIN
-  IF NEW.manager = 1 THEN
-    SELECT count(*) FROM department_employees de WHERE de.department_id = NEW.department_id;
-    -- UPDATE departments set mgrsin = NEW.sin WHERE mgrsin = OLD.sin;
-  END IF;
-  RETURN NEW;
-END
-$$ LANGUAGE plpgsql;
-
-DROP TRIGGER IF EXISTS update_department_manager on department_employees CASCADE;
-CREATE TRIGGER update_department_manager BEFORE INSERT, UPDATE
-  ON department_employees
-  FOR EACH ROW
-  WHEN (NEW.manager = '1')
-  EXECUTE PROCEDURE ensure_each_department_has_one_manager();
-
-INSERT INTO departments VALUES (1, 1000.00);
-INSERT INTO departments VALUES (2, 2000.00);
-INSERT INTO departments VALUES (3, 2000.00);
+INSERT INTO departments VALUES (1, NULL, 1000.00);
+INSERT INTO departments VALUES (2, NULL, 2000.00);
+INSERT INTO departments VALUES (3, NULL, 2000.00);
+INSERT INTO departments VALUES (4, NULL, 4000.00);
 
 INSERT INTO projects VALUES (1, 1, 500.00);
 INSERT INTO projects VALUES (2, 1, 500.00);
 INSERT INTO projects VALUES (3, 3, 1000.00);
 
 \echo '--- insert valid manager'
-INSERT INTO employees VALUES(1, 1, '2019-01-01', 'manager', '+1-555-555-5555', '+1-777-777-7777', 'area 51', 50000.00);
-INSERT INTO department_employees VALUES(1, 1, '1');
+INSERT INTO employees VALUES(1, 1, 1, '2019-01-01', 'manager', 'area 51', 50000.00);
 
 \echo '--- insert valid employee'
-INSERT INTO employees VALUES(2, 1, '2019-01-01', 'engineer', '+1-555-555-0001', '+1-777-777-0001', 'area 51', 100000.00);
-INSERT INTO department_employees VALUES(1, 2, '0');
+INSERT INTO employees VALUES(2, 1, 2, '2019-01-01', 'engineer', 'area 51', 100000.00);
+
+\echo '--- insert duplicate phone number'
+INSERT INTO employees VALUES(3, 1, 3, '2019-01-01', 'manager', 'area 51', 50002.00);
 
--- \echo '--- insert duplicate phone number'
-INSERT INTO employees VALUES(3, 1, '2019-01-01', 'manager', '+1-555-555-5555', '+1-888-888-8888', 'area 51', 50002.00);
--- \echo '--- insert duplicate office number'
-INSERT INTO employees VALUES(3, 1, '2019-01-01', 'manager', '+1-666-666-6666', '+1-777-777-7777', 'area 51', 50003.00);
+INSERT INTO phones VALUES('+1-555-555-0001', 1);
+INSERT INTO phones VALUES('+1-555-555-0002', 2);
+INSERT INTO phones VALUES('+1-555-555-0003', 3);
 
--- two managers for the same department
--- INSERT INTO employees VALUES(3, 1, '2019-01-01', 'manager', '+1-555-555-0002', '+1-777-777-0002', 'area 51', 50000.00);
-UPDATE department_employees SET manager = '1' WHERE employee_id = 2;
+INSERT INTO offices VALUES('1000', 1);
+INSERT INTO offices VALUES('2000', 2);
+INSERT INTO offices VALUES('3000', 3);
 
-SELECT e.*, de.manager
-FROM employees e
-INNER JOIN department_employees de on de.employee_id = e.id;
+UPDATE departments SET manager_id = 1 WHERE id = 1;
+UPDATE departments SET manager_id = 2 WHERE id = 2;
+UPDATE departments SET manager_id = 3 WHERE id = 3;
+\echo '--- An employee who is a manager of two different departments'
+UPDATE departments SET manager_id = 1 WHERE id = 4;