Complete question 4

1 files changed, 36 insertions, 4 deletions

diff --git a/assignments/1/README.md b/assignments/1/README.md
index c39845c..d49b516 100644
--- a/assignments/1/README.md
+++ b/assignments/1/README.md
@@ -125,6 +125,7 @@ Clearly state any further assumptions made, but note that you must not override
 Assumptions:
 
 * An instructor can also be a student.
+* Different instructors can teach a course but not for the same term.
 
 Rationale:
 
@@ -136,25 +137,56 @@ Rationale:
 * Each student and instructor has a profile record. (Represented as a weak entity)
 * An instructor can also be a student. This would yield a instructor record, student record and a single shared profile record.
 * A scheduled course is a Associative entity that represents the offering of a course during a specific term.
-* Different instructors can teach a course but not for the same term.
 
 ### Question 4 (15 marks)
 
 For each of the following relations, identify the Normal Form(s) each relation satisfies, and transform it into 3NF.
 
 * Consider the relation STUDENT, where a student can have only one major:
-	```text
-	RELATION = STUDENT (StuID, StuName, Major), Primary Key = {StuID}.
-	```
+```text
+RELATION = STUDENT (StuID, StuName, Major), Primary Key = {StuID}.
+```
+  * Passes: 1st normal form
+  * Passes: 2nd normal form
+  * Passes: 3rd normal form
+
+Alternative 3NF:
+
+```text
+Students(id, name, major_id), primary key = {id}, foreign key = {majors(major_id)}
+Majors(id, name), primary key = {id}
+```
+
 * Consider the relation EMPLOYEE, where an employee can have more than one specialization:
 	```text
 	RELATION = EMPLOYEE (EmpID, Name, Specialization), Primary Key = {EmpID}.
 	```
+  * Passes: 1st normal form (0 multi-valued attributes)
+  * Passes: 2nd normal form (0 functional dependencies)
+  * Fails: 3rd normal form (Specialization is a transitive dependency)
+
+3NF:
+```text
+Employee(id, name), Primary Key = {id}
+Specialization(id, name), Primary Key {id}
+EmployeeSpecialization(employee_id, specialization_id), Primary Key {employee_id, specialization_id}
+```
+
 * Consider the relation LEASE, where a person can live in only one building, and a building can charge only one rental rate:
 	```text
 	RELATION = LEASE (PersonID, BuildingID, Rent), Primary Key – {PersonID}.
 	```
 
+  * Passes: 1st normal form (0 multi-valued attributes)
+  * Fails: 2nd normal form (1 functional dependency. Rent is determined by BuildingID)
+
+3NF
+```text
+Lease(id, person_id, building_id), Primary Key = {id}, Foreign Key = {person(person_id}, {buildings(building_id)}
+Person(id), Primary Key = {id}
+Building(id, rent), Primary Key = {id}
+```
+
 ### Question 5 (20 marks)
 
 Consider a one-relation database with the following attributes: