diff options
| -rw-r--r-- | assignments/1/README.md | 19 | ||||
| -rwxr-xr-x | assignments/1/run.sh | 4 | ||||
| -rw-r--r-- | assignments/1/run.sql | 34 |
3 files changed, 38 insertions, 19 deletions
diff --git a/assignments/1/README.md b/assignments/1/README.md index 35894cc..ec410dc 100644 --- a/assignments/1/README.md +++ b/assignments/1/README.md @@ -186,25 +186,6 @@ Transform this relation into 3NF. Justify any decomposition. * 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/run.sh b/assignments/1/run.sh new file mode 100755 index 0000000..f32b6ab --- /dev/null +++ b/assignments/1/run.sh @@ -0,0 +1,4 @@ +#!/bin/sh + +dir="$(dirname $0)" +./bin/console -f "${dir}/run.sql" diff --git a/assignments/1/run.sql b/assignments/1/run.sql new file mode 100644 index 0000000..d1ce2ea --- /dev/null +++ b/assignments/1/run.sql @@ -0,0 +1,34 @@ +DROP TABLE IF EXISTS employees CASCADE; +DROP TABLE IF EXISTS projects CASCADE; +DROP TABLE IF EXISTS departments CASCADE; + +CREATE TABLE departments ( + id bigint primary key, + budget decimal +); + +CREATE TABLE projects ( + id bigint primary key, + department_id bigint references departments(id), + budget decimal +); + +CREATE TABLE employees ( + id bigint primary key, + department_id bigint references departments(id), + 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, + 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); |