Helps eliminate data anomalies where insertions, deletions, updates destroy the integrity of the database
At the conclusion of this process the database will be logically correct
Second normal form (2NF)
Third normal form (3NF)
Fourth normal form (4NF)
Employee -- Employee number, Name, Job classification
The company charges its clients by billing the hours spent on each project. The hourly billing rate is dependent on the employee’s position.
Periodically, a report is generated.
The table whose contents correspond to the reporting requirements is shown in Table 5.1.
The table displays data redundancies.
The table entries invite data inconsistencies.
The data redundancies yield the following anomalies:
Addition anomalies: New employees must be assigned to a project
Deletion anomalies: If an employee leaves all information associated with the employee is lost. If the employee is the only one working on a particular project all information on that project is lost.
Repeating groups can be eliminated by adding the appropriate entry in at least the primary key column(s).
This leads to a much wider, longer table
Until 1NF is achieved we cannot create a relational database at all.
The arrows above entities indicate all desirable dependencies, in this table i.e., dependencies that are based on PK.
The arrows below the dependency diagram indicate less desirable dependencies -- partial dependencies (not based on entire PK) and transitive dependencies.
There are no repeating groups in the table.
All attributes are dependent on the primary key.
It includes no partial dependencies; that is, no attribute is dependent on only a portion of the primary key.
Writing the dependent attributes after each new key.
PROJECT (PROJ_NUM, PROJ_NAME) EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS, CHG_HOUR) ASSIGN (PROJ_NUM, EMP_NUM, HOURS)
It contains no transitive dependencies
PROJECT (PROJ_NUM, PROJ_NAME) ASSIGN (PROJ_NUM, EMP_NUM, HOURS) EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS) JOB (JOB_CLASS, CHG_HOUR)
(A determinant is any attribute whose value determines other values with a row.)
If a table contains only one candidate key, the 3NF and the BCNF are equivalent.
BCNF is a special case of 3NF.
Each project requires the services of many employees.
An employee may be assigned to several different projects.
Some employees are not assigned to a project and perform duties not specifically related to a project. Some employees are part of a labor pool, to be shared by all project teams.
Each employee has a (single) primary job classification. This job classification determines the hourly billing rate.
Many employees can have the same job classification.
PROJECT (PROJ_NUM, PROJ_NAME) EMPLOYEE (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, JOB_DESCRIPTION, JOB_CHG_HOUR)
PROJECT (PROJ_NUM, PROJ_NAME) EMPLOYEE (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, JOB_CODE) JOB (JOB_CODE, JOB_DESCRIPTION, JOB_CHG_HOUR)
Attribute ASSIGN_HOUR is assigned to the composite entity ASSIGN.
Adding ASSIGN_NUM as the primary key for ASSIGN allows multiple assignments by an employee to a project
“Manages” relationship is created between EMPLOYEE and PROJECT.
PROJECT (PROJ_NUM, PROJ_NAME, EMP_NUM) EMPLOYEE (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE) JOB (JOB_CODE, JOB_DESCRIPTION, JOB_CHG_HOUR) ASSIGN (ASSIGN_NUM, ASSIGN_DATE, PROJ_NUM, EMP_NUM, ASSIGN_HOURS)
Conversion to 4NF
Normalized (decomposed) tables require additional processing, reducing system speed.
Normalization purity is often difficult to sustain in the modern database
environment. The conflict between design efficiency, information requirements,
and processing speed are often resolved through compromises that include