106 Problems Use the database shown in Figure P3.1 to answer Problems 1, 2, 3, 4, 5, 6, 7, 8 and 9. Figure P3.1 The CH03_Storeco Database Tables Table name: EMPLOYEE Database name: Ch03_StoreCo EMP CODE EMP_TITLE EMP_LNAME 1 Mr. 2 Ms. 3 Ms. 4 Mrs. Williamson Ratula Greenboro Rumpersfro EMP FNAME John EMP_INITIAL EMP_DOB STORE_CODE W 21-May-84 3 Nancy 09-Feb-89 2 Lottie R 02-Oct-81 4 Jennie S 01-Jun-91 5 5 Mr. Smith Robert L 23-Nov-79 3 6 Mr. Renselaer Cary A 25-Dec-85 1 7 Mr. Ogallo Roberto S 31-Jul-82 3 8 Ms. 9 Mr. 10 Mrs. 11 Mr. Johnsson Eindsmar Jones Broderick Elizabeth Jack I 10-Sep-88 1 W 19-Apr-75 2 Rose R 06-Mar-86 4 Tom 21-Oct-92 3 12 Mr. Washington Alan Y 08-Sep-94 2 13 Mr. Smith Peter N 25-Aug-84 3 14 Ms. Smith Sherry H 25-May-86 4 15 Mr. Olenko Howard U 24-May-84 5 16 Mr. 17 Ms. 18 Mr. Archialo Grimaldo Rosenberg Barry V 03-Sep-80 5 Jeanine K 12-Nov-90 4 Andrew D 24-Jan-91 4 19 Mr. Rosten Peter F 03-Oct-88 4 20 Mr. Mckee Robert S 06-Mar-90 1 21 Ms. Baumann Jennifer A 11-Dec-94 3 Table name: STORE STORE_CODE STORE_NAME STORE_YTD_SALES REGION_CODE EMP_CODE 1 Access Junction 1003455.76 2 8 2 Database Corner 1421987.39 2 12 3 Tuple Charge 986783.22 1 27 4 Attribute Alley 944568.56 2 3 5 Primary Key Point 2930098.45 1 15 Table name: REGION REGION_CODE REGION_DESCRIPT 1 East 2 West 1. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None. 2. Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer. 3. Do the tables exhibit referential integrity? Answer yes or no, and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key. 4. Describe the type(s) of relationship(s) between STORE and REGION. 5. Create the ERD to show the relationship between STORE and REGION. 6. Create the relational diagram to show the relationship between STORE and REGION. 7. Describe the type(s) of relationship(s) between EMPLOYEE and STORE. (Hint: Each store employs many employees, one of whom manages the store.) 8. Create the ERD to show the relationships among EMPLOYEE, STORE, and REGION. 9. Create the relational diagram to show the relationships among EMPLOYEE, STORE, and REGION. Use the database shown in Figure P3.10 to work Problems 10, 11, 12, 13, 14, 15 and 16. Note that the database is composed of four tables that reflect these relationships: . Figure P3.10 The CH03_BENECO Database Tables Table name: EMPLOYEE EMP_CODE EMP_LNAME JOB_CODE 14 Rudell Table name: BENEFIT EMP_CODE PLAN_CODE 2 15 15 McDade 1 15 3 16 Ruellardo 1 16 1 17 Smith 3 17 1 20 Smith 2 17 3 17 4 20 3 Database name: Ch03_BeneCo Table name: JOB JOB_CODE JOB DESCRIPTION 1 Clerical 2 Technical Table name: PLAN PLAN_CODE PLAN_DESCRIPTION 1 Term life 2 Stock purchase Details 3 Managerial 3 Long-term disability 4 Dental An EMPLOYEE has only one JOB_CODE, but a JOB_CODE can be held by many EMPLOYEES. An EMPLOYEE can participate in many PLANS, and any PLAN can be assigned to many EMPLOYEES. Note also that the M:N relationship has been broken down into two 1:M relationships for which the BENEFIT table serves as the composite or bridge entity. 10. For each table in the database, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None. 11. Create the ERD to show the relationship between EMPLOYEE and JOB. 12. Create the relational diagram to show the relationship between EMPLOYEE and JOB. 13. Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer. 14. Do the tables exhibit referential integrity? Answer yes or no, and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key. 15. Create the ERD to show the relationships among EMPLOYEE, BENEFIT, JOB, and PLAN. 16. Create the relational diagram to show the relationships among EMPLOYEE, BENEFIT, JOB, and PLAN. 108 Use the database shown in Figure P3.17 to answer Problems 17, 18, 19, 20, 21, 22 and 23. Figure P3.17 The CH03_TRANSCO Database Tables Table name: TRUCK Primary key: TRUCK_NUM Foreign key: BASE_CODE, TYPE_CODE TRUCK_NUM BASE_CODE TYPE_CODE TRUCK_MILES TRUCK_SERIAL_NUM 1001 501 1 32123.5 AA-322-12212-W11 1002 502 1 76984.3 AC-342-22134-023 1003 501 2 12346.6 AC-445-78656-Z99 1004 1 1005 503 2 2894.3 VVQ-112-23144-T34 45673.1 FR-998-32245-W12 1006 501 2 193245.7 AD-456-00845-R45 1007 502 3 32012.3 AA-341-96573-Z84 1008 502 3 44213.6 DR-559-22189-D33 1009 503 2 10932.9 DE-887-98456-E94 Table name: BASE Primary key: BASE_CODE Foreign key: none Database name: Ch03_TransCo BASE_CODE BASE_CITY BASE_STATE BASE_AREA_CODE BASE_PHONE 501 Murfreesboro TN 615 123-4567 BASE_MANAGER Andrea D. Gallager 502 Lexington KY 568 234-5678 George H. Delarosa 503 Cape Girardeau MO 456 345-6789 Maria J. Talindo 504 Dalton GA 901 456-7890 Peter F. McAvee Table name: TYPE Primary key: TYPE_CODE Foreign key: none TYPE_CODE TYPE_DESCRIPTION 1 Single box, double-axle 2 Single box, single-axle 3 Tandem trailer, single-axle Details 17. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None. 18. Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer. 19. Do the tables exhibit referential integrity? Answer yes or no, and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key. 20. Identify the TRUCK table's candidate key(s). 21. For each table, identify a superkey and a secondary key. 22. Create the ERD for this database. 23. Create the relational diagram for this database. Use the database shown in Figure P3.24 to answer Problems 24, 25, 26, 27, 28, 29, 30 and 31. AviaCo is an aircraft charter company that supplies on-demand charter flight services using a fleet of four aircraft. Aircraft are identified by a unique registration number. Therefore, the aircraft registration number is an appropriate primary key for the AIRCRAFT table. Figure P3.24 The CH03_AVIACO Database Tables Details Table name: CHARTER Database name: Ch03 AviaCo CHAR TRIP CHAR DATE OHAR PLOT OHAR COPLOTACJAMBER CHAR DESTINATION CHAR DISTANCE OHAR HOURS FLOWNOWAR HOURS _JWAT | CHAR_FUEL_GALLONS | CHAR_OR_QTS CUSCODE A 131 130 109427 105 277 136 101 10414 320 0 15/40 4720 15740 3541 726 52 1171 MLA 440 154 D 234 459 99 D 2791 53 3000 www 3004 16450 ga 622 0 100 1065 0 10014 ATL Atlanta, GA Table name: AIRCRAFT AC_NUMBER | MOD_CODE | AC_TTAF | AC_TTEL | AC_TTER || 1484P 2289L 2778V PA23-250 C-90A PA31-350 1833.1 1833.1 The destinations are indicated by standard three-letter airport codes. For example, STL St. Louis, MO AC-TTAF = Aircraft total time, airframe (hours) AC-TTEL Total time, left engine (hours) AC_TTER Total time, right engine (hours) BNA Nashville, TN 101.8 4243.8 768.9 1123.4 7992.9 1513.1 4278Y PA31-350 622.1 789.5 243.2 Table name: MODEL In a fully developed system, such attribute values would be updated by application software when the CHARTER table entries were posted. MOD_CODE MOD MANUFACTURER MOD_NAME MOD_SEATS MOD_CHO MILE B200 Beechcraft C-90A Beechcraft PA23-250 Piper PA31-350 Piper KingAir Aztec Navajo Chieftain 10 1.93 8 6 2.67 1.93 Customers are charged per round-trip mile, using the MOD_CHG MILE rate. The MOD SEATS column lists the total number of seats in the airplane, including the pilot and copilot seats. Therefore, a PA31-350 trip that is flown by a pilot and a copilot has eight passenger seats available. Table name: PILOT EMP_NUM PIL LICENSE PIL RATINGS 101 ATP 104 ATP 105 COM 106 COM 109 COM ATP/SELMELAnstr/CFI ATP/SELMELAnstr COMMISELMELAnstr/CFI 2 COMM/SELMELAnstr 2 ATP/SELMEL/SESAnstr/CFI 1 Database name: Ch03_AviaCo PIL_MED_TYPE PIL_MED_DATE | PIL_PT135_DATE 20-Jan-18 11-Jan-18 18-Dec-17 17-Jan-18 05-Jan-18 02-Jan-18 10-Dec-17 02-Feb-18 22-Jan-18 15-Jan-18 The pilot licenses shown in the PILOT table include the ATP = Airline Transport Pilot and COM Commercial Pilot. Businesses that operate "on demand" air services are governed by Part 135 of the Federal Air Regulations (FARS) that are enforced by the Federal Aviation Administration (FAA). Such businesses are known as "Part 135 operators." Part 135 operations require that pilots successfully complete flight proficiency checks each six months. The "Part 135" flight proficiency check date is recorded in PIL PT135 DATE. To fly commercially, pilots must have at least a commercial license and a 2nd class medical certificate (PIL_MED_TYPE = 2.) The PIL RATINGS include SEL Single Engine, Land SES Single Engine (Sea) CFI = Certified Flight Instructor Table name: EMPLOYEE MEL Multi-engine Land Instr. Instrument CFII = Certified Flight Instructor, Instrument EMP NUM EMP_TITLE EMP_LNAME EMP_FNAME EMP_NITIAL EMP_DOB EMP_HIRE DATE 100 Mr. Kolmycz George D 15-Jun-62 15-Mar-08 101 Ms Lewis Rhonda G 19-Mar-85 25-Apr-06 102 Mr Vandam Rhett 14-Nov-78 18-May-13 Jones 11-May-94 26-Jul-17 104 Mr. Lange P 12-Jul-91 20-Aug-10 105 Mr. Williams Robert D 14-Mar-95 19-Jun-17 106 Mrs Duzal Jeanine K 12-Feb-88 13-Mar-18 107 Mr. Diante D 01-May-95 02-Jul-16 108 Mr. Paul 03-Jun-13 109 Ms Travis 110 Mrs Genkazi Elizabeth Leighla K 18-Jun-81 14-Feb-16 W 19-May-90 29-Jun-10 Table name: CUSTOMER CUS_CODE | CUS LNAME | CUS FNAME | CUS_INITIAL | CUS_AREACODE | CUS PHONE | CUS BALANCE 10010 Ramas Alfred 10011 Dunne Leona A K 615 844-2573 0.00 894-1238 0.00 10012 Smith Kathy w 894-2295 896.54 10013 Olowski Paul F 615 894-2180 1285.19 10014 Orlando Myron 615 222-1672 673.21 10015 0Brian B 713 442-3381 1014.56 10016 Brown James G 615 297-1228 0.00 10017 Williams George 615 290-2556 0.00 10018 Famiss Anne G 382-7185 0.00 10019 Smith Olette K 297-3809 453.90 110 111 The nulls in the CHARTER table's CHAR_COPILOT column indicate that a copilot is not required for some charter trips or for some aircraft. Federal Aviation Administration (FAA) rules require a copilot on jet aircraft and on aircraft that have a gross take-off weight over 12,500 pounds. None of the aircraft in the AIRCRAFT table are governed by this requirement; however, some customers may require the presence of a copilot for insurance reasons. All charter trips are recorded in the CHARTER table. Note Earlier in the chapter, you were instructed to avoid homonyms and synonyms. In this problem, both the pilot and the copilot are listed in the PILOT table, but EMP_NUM cannot be used for both in the CHARTER table. Therefore, the synonyms CHAR_PILOT and CHAR_COPILOT were used in the CHARTER table. Although the solution works in this case, it is very restrictive, and it generates nulls when a copilot is not required. Worse, such nulls proliferate as crew requirements change. For example, if the AviaCo charter company grows and starts using larger aircraft, crew requirements may increase to include flight engineers and load masters. The CHARTER table would then have to be modified to include the additional crew assignments; such attributes as CHAR_FLT_ENGINEER and CHAR_LOADMASTER would have to be added to the CHARTER table. Given this change, each time a smaller aircraft flew a charter trip without the number of crew members required in larger aircraft, the missing crew without the number of crew members required in larger aircraft, the missing crew members would yield additional nulls in the CHARTER table. You will have a chance to correct those design shortcomings in Problem 27. The problem illustrates two important points: 1. 2. Don't use synonyms. If your design requires the use of synonyms, revise the design! To the greatest possible extent, design the database to accommodate growth without requiring structural changes in the database tables. Plan ahead and try to anticipate the effects of change on the database. 24. For each table, identify each of the following when possible: a. The primary key b. A superkey c. A candidate key d. The foreign key(s) e. A secondary key 25. Create the ERD. (Hint: Look at the table contents. You will discover that an AIRCRAFT can fly many CHARTER trips but that each CHARTER trip is flown by one AIRCRAFT, that a MODEL references many AIRCRAFT but that each AIRCRAFT references a single MODEL, and so on.) 26. Create the relational diagram. 27. Modify the ERD you created in Problem 25 to eliminate the problems created by the use of synonyms. (Hint: Modify the CHARTER table structure by eliminating the CHAR_PILOT and CHAR_COPILOT attributes; then create a composite table named CREW to link the CHARTER and EMPLOYEE tables. Some crew members, such as flight attendants, may not be pilots. That's why the EMPLOYEE table enters into this relationship.) 28. Create the relational diagram for the design you revised in Problem 27. You want to see data on charters flown by either Robert Williams (employee number 105) or Elizabeth Travis (employee number 109) as pilot or copilot, but not charters flown by both of them. Complete Problems 29, 30 and 31 to find this information. 29. Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by either employee 105 or employee 109. 28. Create the relational diagram for the design you revised in Problem 27. You want to see data on charters flown by either Robert Williams (employee number 105) or Elizabeth Travis (employee number 109) as pilot or copilot, but not charters flown by both of them. Complete Problems 29, 30 and 31 to find this information. 112 29. Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by either employee 105 or employee 109. 30. Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by both employee 105 and employee 109. 31. Create the table that would result from applying a DIFFERENCE relational operator of your result from Problem 29 to your result from Problem 30.