add unique constraint

3 files changed, 6 insertions, 1 deletions

diff --git a/assignments/1/README.md b/assignments/1/README.md
index 751598d..1855b10 100644
--- a/assignments/1/README.md
+++ b/assignments/1/README.md
@@ -144,12 +144,14 @@ Consider a one-relation database with the following attributes:
 
   * A separate projects relation has a surrogate key with a foreign key to the departments relation to ensure that a project can only be own by one department.
   * A phone and office relation can use the number as the primary key to ensure that the number is unique and not null.
+  * A phone and office number can be own by a single employee so both relations have a foreign key to the employees table with a unique constraint.
   * Each employee relation has a surrogate key used to identify the employee.
   * Each employee relation has a foreign key reference to the department they work for.
   * The employee relation includes a foreign key to the project that they are currently working on.
-  * Each department has a foreign key to the employees table to specify the single manager of the department.
+  * Each department has a foreign key to the employees table to specify the single manager of the department with a unique constraint.
   * Primary keys ensures global uniqueness within the relation and not null values.
   * Foreign key constraints ensure that a valid corresponding relation must exist in the relation that the foreign key references.
+  * A unique constraint on `departments(manager_id);` to ensure an employee cannot manage more than one department.
 
 ## Question 6
 
diff --git a/assignments/1/README.pdf b/assignments/1/README.pdf
index 9ef17ec..8a74b0b 100644
--- a/assignments/1/README.pdf
+++ b/assignments/1/README.pdf
diff --git a/assignments/1/run.sql b/assignments/1/run.sql
index b258bd6..4606510 100644
--- a/assignments/1/run.sql
+++ b/assignments/1/run.sql
@@ -42,6 +42,8 @@ CREATE TABLE offices (
 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);
 CREATE UNIQUE INDEX department_manager_idx on departments(manager_id);
+CREATE UNIQUE INDEX employee_phones_idx on phones(employee_id);
+CREATE UNIQUE INDEX employee_offices_idx on offices(employee_id);
 
 INSERT INTO departments VALUES (1, NULL, 1000.00);
 INSERT INTO departments VALUES (2, NULL, 2000.00);
@@ -64,6 +66,7 @@ INSERT INTO employees VALUES(3, 1, 3, '2019-01-01', 'manager', 'area 51', 50002.
 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);
+INSERT INTO phones VALUES('+1-555-555-0004', 1);
 
 INSERT INTO offices VALUES('1000', 1);
 INSERT INTO offices VALUES('2000', 2);