Chris Bell | 'A business that makes nothing but money is a poor business.' | |
- Henry Ford |
SNHU - IT-330 Database Design and Management
Written by: Chris Bell - April, 2013
It contains 7 records and there are 5 fields per record.
The city is referenced within the complete address which makes it difficult to produce a listing when there are many records. In order to solve the problem one could split MANAGER_ADDRESS into 4 fields named MANAGER_ADDRESS, MANAGER_CITY, MANAGER_STATE and MANAGER_ZIP. Then there can be reports for each portion of the address.
Same as above. In order to produce a listing by first and last name separately one must break the field into 2 (PROJECT_MANAGER_FIRST AND PROJECT_MANAGER_LAST).
The same name, phone and address are mentioned more than once creates a redundancy within the table and also when mentioned in more than one table. Redundancies can lead to anomalies which can cause an error message when trying to view the document. Assume one makes a change to the last name of Angela Dunstable because she got married but forgot to change it in all fields and tables; this could cause many problems within the company itself and the database.
There are a lot of redundancies in this file such as the same PROJ_NUM's. There needs to be a 2nd table with a many-to-many relationship between PROJ_NUM and EMP_NAME to support the redundancies in this file.
I would recommend changing EMP_NAME to EMP_NAME_F and EMP_NAME_L in order to have the first and last name in separate fields. That will produce a listing for either of the two names for each employee. I don't see a need to change the format of the EMP_PHONE contents unless there is a need to produce a listing for the area code specifically. In that case it could be split into two fields as EMP_AREA_CODE and EMP_PHONE.
The data sources are the raw data or information within the file. In this case, I examined similarities in the PROJ_NUM, PROJ_NAME, EMP_NUM, EMP_NAME, EMP_CODE, and EMP_PHONE.
There should be a separate table for each of the 3 projects and a 1-to-many relationship from the PROJECT to the EMPLOYEE. The job codes also prove to be redundant and should have a separate table with a 1-to-many relationship between JOB_CODE and EMPLOYEE.