Given the file structure shown in Figure P1.1, answer Problems 1-4. Figure P1.1 The File Structure...

Transcribed Image Text:

Given the file structure shown in Figure P1.1, answer Problems 1-4. Figure P1.1 The File Structure for Problems 1-4 PROJECT_CODE 21-5Z Holly B. Parker 25-2D Jane D. Grant 25-5A George F. Dorts 25-9T Holly B. Parker 27-4Q George F. Dorts 29-2D Holly B. Parker 31-7P William K. Moor 904-338-3416 615-898-9909 615-227-1245 904-338-3416 615-227-1245 904-338-3416 904-445-2719 PROJECT MANAGER MANAGER_PHONE MANAGER_ADDRESS 3334 Lee Rd., Gainesville, FL 37123 218 Clark Blvd., Nashville, TN 36362 124 River Dr., Franklin, TN 29185 3334 Lee Rd., Gainesville, FL 37123 124 River Dr., Franklin, TN 29185 PROJECT_BID_PRICE 16833460.00 12500000.00 32512420.00 21563234.00 10314545.00 3334 Lee Rd., Gainesville, FL 37123 216 Morton Rd., Stetson, FL 30155 25559999.00 56850000.00 Details A table shows the following column headers: project code project manager manager phone manager address and project bid price. There are seven rows of data in the table. The data in the address fields includes the street address, city, state, and zip. The data in the Project Manager field includes multiple entries for several of the managers. 1. How many records does the file contain? How many fields are there per record? 2. What problem would you encounter if you wanted to produce a listing by city? How would you solve this problem by altering the file structure? 3. If you wanted to produce a listing of the file contents by last name, area code, city, state, or zip code, how would you alter the file structure? 4. What data redundancies do you detect? How could those redundancies lead to anomalies? 5. Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure P1.5. Figure P1.5 The File Structure for Problems 5-8 PROJ_NUM PROJ_NAME | EMP_NUM EMP_NAME 1 Hurricane 1 Hurricane 1 Hurricane 101 John D. Newson 105 David F. Schwann 110 Anne R. Ramoras 85.00 JOB CODE JOB_CHG_HOUR PROJ_HOURS EMP_PHONE EE 13.3 653-234-3245 CT 60.00 16.2 653-234-1123 CT 60.00 14.3 615-233-5568 2 Coast 101 John D. Newson EE 85.00 19.8 653-234-3254 2 Coast 108 June H. Sattlemeir EE 85.00 17.5 905-554-7812 3 Satellite 110 Anne R. Ramoras CT 62.00 11.6 615-233-5568 3 Satellite 105 David F. Schwann CT 26.00 23.4 653-234-1123 3 Satelite 123 Mary D. Chen EE 85.00 19.1 615-233-5432 3 Satellite 112 Allecia R. Smith BE 85.00 20.7 615-678-6879 Details A table shows the following column headers: proj num proj name, E M P num, job code, job C H O Hour, Proj Hours, and E M P Phone. There are nine rows of data in the table. The data in the E M P name field includes the first & last names and middle initials. The data in the EM P Phone field includes the area code, 33 prefix, and last four digits, separated by hyphens. There are multiple entries for each Project Number, Project Name, Employee Number, Employee Name, and Job Code in the table. 6. Looking at the EMP_NAME and EMP_PHONE contents in Figure P1.5, what change(s) would you recommend? 7. Identify the various data sources in the file you examined in Problem 5. 8. Given your answer to Problem 7, what new files should you create to help eliminate the data redundancies found in the file shown in Figure P1.5? 9. Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure P1.9. (The file is meant to be used as a teacher class assignment schedule. One of the many problems with data redundancy is the likely occurrence of data inconsistencies-two different initials have been entered for the teacher named Maria Cordoza.) Figure P1.9 The File Structure for Problems 9-10 BUILDING CODE ROOM_CODE TEACHER_LNAME TEACHER_FNAME TEACHER_INITIAL KOM 204E Williston Horace KOM 123 Cordoza Maria G L DAYS_TIME MVVF 8:00-8:50 MVVF 8:00-8:50 LDB 504 Patroski Donald J TTh 1:00-2:15 KOM 34 Hawkins Anne W MVVF 10:00-10:50 JKP 2258 Risell James TTH 9:00-10:15 LDB 301 Robertson Jeanette P TTH 9:00-10:15 KOM 204E Cordoza Maria | MWVF 9:00-9:50 LDB 504 Williston Horace G TTh 1:00-2:15 KOM 34 Cordoza Maria L LDB 504 Patroski Donald J MWVF 11:00-11:50 MVVF 2:00-2:50 Details A table shows the following column headers: building code, room code, teacher L name, teacher F name, Teacher initial, and Days Time. There are 10 rows of data in the table. The data in the Building Code field includes multiple entries for each code, including KCM, JK P, and KO M. There are multiple entries for each building code, room code, teacher, and days time in the table. 10. Given the file structure shown in Figure P1.9, what problem(s) might you encounter if building KOM were deleted? 11. Using your school's student information system, print your class schedule. The schedule probably would contain the student identification number, student name, class code, class name, class credit hours, class instructor name, the class meeting days and times, and the class room number. Use Figure P1.11 as a template to complete the following actions. Figure P1.11 Student Schedule Data Format STU_ID STU_NAME CLASS_CODE CLASS_NAME CLASS_CR a) Create a spreadsheet using the template shown in Figure P1.11 and enter your current class schedule. Figure P1.11 Student Schedule Data Format STU_ID STU_NAME CLASS_CODE CLASS_NAME CLASS_CR a) Create a spreadsheet using the template shown in Figure P1.11 and enter your current class schedule. b) Enter the class schedule of two of your classmates into the same spreadsheet. c) Discuss the redundancies and anomalies caused by this design. Given the file structure shown in Figure P1.1, answer Problems 1-4. Figure P1.1 The File Structure for Problems 1-4 PROJECT_CODE 21-5Z Holly B. Parker 25-2D Jane D. Grant 25-5A George F. Dorts 25-9T Holly B. Parker 27-4Q George F. Dorts 29-2D Holly B. Parker 31-7P William K. Moor 904-338-3416 615-898-9909 615-227-1245 904-338-3416 615-227-1245 904-338-3416 904-445-2719 PROJECT MANAGER MANAGER_PHONE MANAGER_ADDRESS 3334 Lee Rd., Gainesville, FL 37123 218 Clark Blvd., Nashville, TN 36362 124 River Dr., Franklin, TN 29185 3334 Lee Rd., Gainesville, FL 37123 124 River Dr., Franklin, TN 29185 PROJECT_BID_PRICE 16833460.00 12500000.00 32512420.00 21563234.00 10314545.00 3334 Lee Rd., Gainesville, FL 37123 216 Morton Rd., Stetson, FL 30155 25559999.00 56850000.00 Details A table shows the following column headers: project code project manager manager phone manager address and project bid price. There are seven rows of data in the table. The data in the address fields includes the street address, city, state, and zip. The data in the Project Manager field includes multiple entries for several of the managers. 1. How many records does the file contain? How many fields are there per record? 2. What problem would you encounter if you wanted to produce a listing by city? How would you solve this problem by altering the file structure? 3. If you wanted to produce a listing of the file contents by last name, area code, city, state, or zip code, how would you alter the file structure? 4. What data redundancies do you detect? How could those redundancies lead to anomalies? 5. Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure P1.5. Figure P1.5 The File Structure for Problems 5-8 PROJ_NUM PROJ_NAME | EMP_NUM EMP_NAME 1 Hurricane 1 Hurricane 1 Hurricane 101 John D. Newson 105 David F. Schwann 110 Anne R. Ramoras 85.00 JOB CODE JOB_CHG_HOUR PROJ_HOURS EMP_PHONE EE 13.3 653-234-3245 CT 60.00 16.2 653-234-1123 CT 60.00 14.3 615-233-5568 2 Coast 101 John D. Newson EE 85.00 19.8 653-234-3254 2 Coast 108 June H. Sattlemeir EE 85.00 17.5 905-554-7812 3 Satellite 110 Anne R. Ramoras CT 62.00 11.6 615-233-5568 3 Satellite 105 David F. Schwann CT 26.00 23.4 653-234-1123 3 Satelite 123 Mary D. Chen EE 85.00 19.1 615-233-5432 3 Satellite 112 Allecia R. Smith BE 85.00 20.7 615-678-6879 Details A table shows the following column headers: proj num proj name, E M P num, job code, job C H O Hour, Proj Hours, and E M P Phone. There are nine rows of data in the table. The data in the E M P name field includes the first & last names and middle initials. The data in the EM P Phone field includes the area code, 33 prefix, and last four digits, separated by hyphens. There are multiple entries for each Project Number, Project Name, Employee Number, Employee Name, and Job Code in the table. 6. Looking at the EMP_NAME and EMP_PHONE contents in Figure P1.5, what change(s) would you recommend? 7. Identify the various data sources in the file you examined in Problem 5. 8. Given your answer to Problem 7, what new files should you create to help eliminate the data redundancies found in the file shown in Figure P1.5? 9. Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure P1.9. (The file is meant to be used as a teacher class assignment schedule. One of the many problems with data redundancy is the likely occurrence of data inconsistencies-two different initials have been entered for the teacher named Maria Cordoza.) Figure P1.9 The File Structure for Problems 9-10 BUILDING CODE ROOM_CODE TEACHER_LNAME TEACHER_FNAME TEACHER_INITIAL KOM 204E Williston Horace KOM 123 Cordoza Maria G L DAYS_TIME MVVF 8:00-8:50 MVVF 8:00-8:50 LDB 504 Patroski Donald J TTh 1:00-2:15 KOM 34 Hawkins Anne W MVVF 10:00-10:50 JKP 2258 Risell James TTH 9:00-10:15 LDB 301 Robertson Jeanette P TTH 9:00-10:15 KOM 204E Cordoza Maria | MWVF 9:00-9:50 LDB 504 Williston Horace G TTh 1:00-2:15 KOM 34 Cordoza Maria L LDB 504 Patroski Donald J MWVF 11:00-11:50 MVVF 2:00-2:50 Details A table shows the following column headers: building code, room code, teacher L name, teacher F name, Teacher initial, and Days Time. There are 10 rows of data in the table. The data in the Building Code field includes multiple entries for each code, including KCM, JK P, and KO M. There are multiple entries for each building code, room code, teacher, and days time in the table. 10. Given the file structure shown in Figure P1.9, what problem(s) might you encounter if building KOM were deleted? 11. Using your school's student information system, print your class schedule. The schedule probably would contain the student identification number, student name, class code, class name, class credit hours, class instructor name, the class meeting days and times, and the class room number. Use Figure P1.11 as a template to complete the following actions. Figure P1.11 Student Schedule Data Format STU_ID STU_NAME CLASS_CODE CLASS_NAME CLASS_CR a) Create a spreadsheet using the template shown in Figure P1.11 and enter your current class schedule. Figure P1.11 Student Schedule Data Format STU_ID STU_NAME CLASS_CODE CLASS_NAME CLASS_CR a) Create a spreadsheet using the template shown in Figure P1.11 and enter your current class schedule. b) Enter the class schedule of two of your classmates into the same spreadsheet. c) Discuss the redundancies and anomalies caused by this design.