diff options
| author | mo khan <mo.khan@gmail.com> | 2020-01-13 20:37:11 -0700 |
|---|---|---|
| committer | mo khan <mo.khan@gmail.com> | 2020-01-13 20:37:11 -0700 |
| commit | 3b10c30de3e84a6ff4f3bdb080646ac642fe4377 (patch) | |
| tree | e18dfdfdbde5aefec45fd070cbf775bd8d5bcee3 /assignments/1 | |
| parent | d4fbbb7131dec0dcf7d8f0d0849782a318d9fc09 (diff) | |
Justify decomposition
Diffstat (limited to 'assignments/1')
| -rw-r--r-- | assignments/1/README.md | 10 |
1 files changed, 8 insertions, 2 deletions
diff --git a/assignments/1/README.md b/assignments/1/README.md index cb859a2..a237bcc 100644 --- a/assignments/1/README.md +++ b/assignments/1/README.md @@ -231,8 +231,14 @@ Transform this relation into 3NF. Justify any decomposition.  -* Each employee belongs to a single department -* Each department can only have a single manager. To enforce this a unique constraint can be applied on `employees(department_id, manager = 1)`. +* 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. +* 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. +* 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. ### Question 6 (15 marks) |