1
GROUP ASSIGNMENT
CSC 124/221-Database Concepts/Database Management System
QUESTION ONE:
The following normalization question refers to the simplified database activities of a construction company
that manages several building projects. Each project has its own project number, name, employees assigned
to it and so on. Each employee has an employee number, name, and job classification such as engineer or
computer technician. The company charges its clients by billing the hours spent on each contract. The hourly
billing rate is dependent on the employee’s position. Periodically, a report is generated that contains the
information displayed in Table 5.1. The Total Charge in Table 5.1 is a derived attribute and, at this point is
not stored in this table. The easiest short-term to generate the required report might seem to be a table whose
contents correspond to the reporting requirements. (See Figure 5.1.)
The structure of the data set in Figure 5.1 does not handle data very well for the following reasons:
1. The project number (PROJ_NUM) is apparently intended to be a primary key, but it contains nulls.
2. The table entries invite data inconsistencies. For example,
The JOB_CLASS value “Elect.Engineer” might be entered as “Elect.Eng.” in some cases, “El. Eng” or “EE” in others.
3. The table displays data redundancies. These data redundancies yield the following anomalies:
