The Database Approach – Answers to Textbook Questions

...

Online answer: The most efficient way to alter the file structure is to break down everything to its most component parts. PROJECT_MANAGER could be broken down into MANAGER_FNAME,MANAGER_LNAME, and MANAGER_INITIAL. MANAGER_PHONE could be broken down intoMANAGER_AREACODE and MANAGER_PHONE. And lastly, MANAGER_ADDRESS could be broken down into MANAGER_STREET, MANAGER_CITY, MANAGER_STATE, and MANAGER_ZIP. Dividing everything up makes search flexibility more efficient

4. Data redundancies are the Project Manager name: Holly B. Parker and the Manager Address: 180 Boulavard du General, Paris 64700. Data anomalies occur when the required changes made to redundant data are not completed successfully. A deletion anomaly will occur when the project is completed and the project is deleted from the database, so will the Project manager name and manager address.

Online answer: There is a one too many relationships between PROJECT and MANAGER. Each project is managed by one manager, but one manager can manage more than one project. Holly B. Parker manages projects 21-5Z, 25-9T, and 29-2D. If Holly’s name, phone number, or address information needs to be updated or deleted, it would need to be done so 3 times, and they must all be made correctly to avoid anomalies. The same goes for the data on George F. Dorts.

5. There are multiple entries for John D Newson, Anne R Ramoras, and David F Schwann. If any person moves and/or changes their phone number, these changes must be made more than once and they must be done correctly. If any occurrence is missed or there is a mistake when entering in the information on one, the data are different for the same person creating anomalies like with John D Newson’s phone number

6. That is, break up the data componnts whenever possible. For example, separate the EMP_NAME into its componenst EMP_FNAME, EMP_INITIAL, and EMP_LNAME. This change will make it much easier to organize employee data through the employee name component. Similarly, the EMP_PHONE data should be decomposed into EMP_AREACODE and EMP_PHONE. For example, breaking up the phone number 653-234-3245 into the area code 653 and the phone number 234-3245 will make it much easier to organize the phone numbers by area code.

7. Employee data such as names and phone numbers.

Project data such as project names

Job data such as the job charge per hour.

8. New files such as Project files, Employee files and Job files should be created. The PROJECT file should contain project characteristics such as the project name, the project manager/coordinator, the project budget, and so on. The EMPLOYEE file might contain the employee names, phone number, address, and so on. JOB file would contain the billing charge per hour for each of the job types – a database designer, an applications developer, and an accountant would generate different billing charges per hour. The CHARGE file would be used to keep track of the number of hours by job type that will be billed for each employee who worked on the project.

9. The teacher characteristics occur multiple times in this file. For example, the teacher named Maria Cordoza’s first name, last name, and initial occur three times. If changes must be made for any given teacher, those changes must be made multiple times. All it takes is one incorrect entry or one forgotten change to create data inconsistencies. Redundant data are not a luxury you can afford in a data environment.

10. You would lose all the time assignment data about teachers Williston, Cordoza, and Hawkins, as well as the KOM rooms 204E, 123, and 34. Here is yet another good reason for keeping data about specific entities in their own tables! This kind of an anomaly is known as a deletion anomaly.