Question 5 of 5: (20 points)
Consider the following “proposal” report for the editing work at the ABC Information System company. The company has several editing departments and
each department can work on proposals submitted by the company systems designers/developers.
1. Each proposal can be “authored” by one or many systems designers/developers
and each systems designer/developer could author one or many proposals.
2. Each editor can be assigned to edit one or many proposals but each proposal
is edited by one editor.
proposal
report |
editor name |
editor department number |
editor department name |
editor department address |
author ID |
author name |
author address |
1234 | jsmith | 100 | requirement | abc-suite1 | 45 | ccorey | abc-fairfax |
1234 | jsmith | 100 | requirement | abc-suite1 | 90 | xzhang | abc-lorton |
1234 | jsmith | 100 | requirement | abc-suite1 | 63 | rwhite | abc-fairfax |
5678 | kwalker | 200 | analysis | abc-suite2 | 24 | bjohnson | abc-vienna |
5678 | kwalker | 200 | analysis | abc-suite2 | 90 | xzhang | abc-lorton |
Perform the normalization process to convert the above un-normalized table to
a. First normal form (1NF)
b. Second normal form (2NF)
c. Third normal form (3NF)
Please show all your work and each step along the way. Identify primary keys, if any, in each table in each step.
Expert Answer
As the above table is already in the 1NF.
It has following attributes
- A primary key cannot be NULL
- A primary key value must be unique
- The primary key values cannot be changed
For 2NF
Rule 1- Be in 1NF
Rule 2- Single Column Primary Key
For 3NF
Rule 1- Be in 2NF
Rule 2- Has no transitive functional dependencie