diff options
| -rw-r--r-- | assignments/1/README.md | 40 | ||||
| -rw-r--r-- | doc/01-introduction.md | 2 |
2 files changed, 37 insertions, 5 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: diff --git a/doc/01-introduction.md b/doc/01-introduction.md index 9e61454..1e57ff7 100644 --- a/doc/01-introduction.md +++ b/doc/01-introduction.md @@ -157,7 +157,7 @@ A normal form is a state of a relation that requires that certain rules regardin * 5th normal form: remaining anomalies have been removed. > functional dependency: a constraint between two attributes in which the value of one attribute is determined by the value of another attribute. - +> transitive dependency: a functional dependency between the primary key and one or more nonkey attributes that are dependent on the primary key via nother nonkey attribute. * Synonyms: Two (or more) attributes that have different names but the same meaning. * Alias: An alternative name used for an attribute. |