Change schema for question 5

1 files changed, 44 insertions, 56 deletions

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;