diff options
| -rw-r--r-- | assignments/1/README.pdf | bin | 233845 -> 232419 bytes | |||
| -rw-r--r-- | assignments/1/question-5-after.png | bin | 40637 -> 39278 bytes | |||
| -rw-r--r-- | assignments/1/question-5-after.puml | 6 | ||||
| -rw-r--r-- | assignments/1/run.sql | 100 |
4 files changed, 47 insertions, 59 deletions
diff --git a/assignments/1/README.pdf b/assignments/1/README.pdf Binary files differindex 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 Binary files differindex 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; |
