1 / 22

Chapter 3 Problem Solutions

Chapter 3 Problem Solutions. Peter Rob and Elie Semaan Databases: Design, Development, and Deployment Using Microsoft Access Second Edition. Figure P3.1 Modify The ERD. Figure P3-2 Initial TruckCo ERD. 1. M. M. 1. BASE. locates. VEHICLE. references. TYPE. (0,N). (1,1). (1,1).

audra-ford
Download Presentation

Chapter 3 Problem Solutions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 3 Problem Solutions Peter Rob and Elie SemaanDatabases: Design, Development,and DeploymentUsing Microsoft Access Second Edition

  2. Figure P3.1 Modify The ERD

  3. Figure P3-2 Initial TruckCo ERD 1 M M 1 BASE locates VEHICLE references TYPE (0,N) (1,1) (1,1) (0,N) (0,N) 1 houses M (1,1) 1 1 1 M M is a DRIVER DRIV_LICENSE EMPLOYEE (1,1) (0,1) (1,1) (1,N) (1,1) 1 (0,N) 1 (0,N) (1,1) has M M (0,N) M DRIV_ENDORSE LICENSE M (1,1) DEPENDENT M (1,1) (1,1) 1 (0,N) 1 1 SCHOOL ENDORSEMENT (0,N) (0,N) Note that this ERD allows the TruckCo management to track from which school the license or the endorsementwas earned, on what date each license and endorsement was earned, and so on. This ERD assumes that a drivercan have more than one license and more than one endorsement. A driver must have at least one license, but mayor may not have one or more endorsements.

  4. Figure P3.3d Initial FlyHigh ERD 1 1 M M 1 CUSTOMER RENTAL AIRCRAFT (0,N) (1,1) (1,1) (0,N) 1 (0,N) (1,1) M references M (1,1) (0,N) 1 M 1 QUALIFICATION CERTIFICATION MODEL (1,1) (0,N) Problem: This design does not include the ability to track payments on account, not does it enableFlyhigh’s management to track payment types -- cash, check, credit card, and payments on account.This design, while correct, is incomplete. (The design requires a TRANSACTION entity.)

  5. Figure P3.3e Revised FlyHigh ERD Note: A customer may make multiple payments to pay off a charged rental activity. Therefore, a rent number may be referenced more than once in the TRANSACTION entity. However, each of the TRANSACTION entries references only one CUSTOMER and one RENTAL record. M TRANSACTION makes (1,1) M (0,1) 1 (0,N) 1 (1,N) 1 1 M M 1 CUSTOMER AIRCRAFT RENTAL (0,N) (1,1) (1,1) (0,N) 1 (0,N) (1,1) M references M (1,1) (0,N) 1 M 1 QUALIFICATION CERTIFICATION MODEL (1,1) (0,N) Note: The TRANSACTION entity is used to track all payments, including any payments on account. To simplify the queries that will be used to report the current customer account balances, a derived attribute, perhaps named CUST_BALANCE , may be included in the CUSTOMER entity. The TRANSACTION entity would include such attributes as the date, the type (debit or credit), the amount, the customer reference, and the rental reference. The design is easily expanded to keep track of all balances for individual rental charges to help determine when “late” interest is appropriate.

  6. Figure P3.3f Table 3.3 Sample FlyHigh TRANSACTION Records Some sample TRANSACTION records are shown in Table 3.3. If you want to keep track of all balances by the rental number, add a column named TRANS_BALANCE. For example, customer number 123 charged a $375.46 rental to his or her account on 18-May-2002 and paid $250.00 on that rental on 20-May-2002, thus leaving a balance for that particular rental transaction of $125.46. Such a “running balance” is desirable, but it takes some work to design the application to perform this task. Table 3.3 Sample TRANSACTION Records

  7. Figure P3.4 The FlyHigh Relational Schema Note: When the rental time has been calculated, that time must be used to update the appropriatefields in the AIRCRAFT and CUSTOMER tables. To make sure that the update is made only once,the RENTAL table contains a field, named RENT_UPDATED, that will be used to control the updateprocess. Here is an example of an attribute (field) that is used to enable an application process, ratherthan to describe the entity. The RENT_CHG_HOUR is copied from the MODEL entity to ensure that the rental transactions are historically accurate. (Rental charges per hour can change over time!)

  8. Figure P3.5d TruckCo Design Option 1 All drivers and mechanics licenses and endorsements are storedin the LICENSE table. The QUALIFICATION table is used tostore licenses and endorsements for all mechanics and drivers, as well as the date on which each qualification was earned and the school (number) from which the qualification was earned. 1 LICENSE (1,N) (1,1) 1 1 1 M is a DRIVER SCHOOL_LICENSE EMPLOYEE M (0,1) (1,1) 1 (1,N) (1,1) 1 (0,1) M (1,1) (0,N) M 1 1 QUALIFICATION SCHOOL (1,1) (0,N) M (1,1) 1 (1,N) 1 becomes a MECHANIC (1,1) Each mechanic and driver must have at least one license. Therefore, QUALIFICATION is mandatory to both DRIVER and MECHANIC. Not all approved schools are necessarilyrepresented. For example, there may be 15 approved schools, but TruckCo’s drivers andmechanics have graduated from only a few of these schools. Therefore, the entity named SCHOOL_LICENSE is optional to SCHOOL.

  9. Figure P3.5e TruckCo Option 2 Option 1: Each maintenance log opening and closing generates a separate record in the MECH_LOG table. This table records the date, the identity of the mechanic, and the log action (open or close) for each MAINT_LOG record. 1 1 M M 1 MECHANIC MECH_LOG MAINT_LOG (0,N) (1,1) (1,1) (0,N) Option 2: Each maintenance log opening generates a record in MAINT_LOG. The record contains a nullclosing date and a null for the mechanic who closes the log … until the log is closed. Requires using synonyms. 1 1 MECHANIC (0,N) (0,N) opens closes M (1,1) (1,1) M MAINT_LOG (Note that Access generates a “shadow” virtual table to indicate the use of multiple relationships.)

  10. 1 M (0,N) (1,1) 1 M 1 M 1 M signs MAINT_DETAIL generates MAINT_LOG MECH_LOG (0,N) (1,1) (1,1) (1,N) M (1,1) M (1,1) (1,N) (1,1) 1 PART is used in enters (0,N) 1 (0,N) 1 M M 1 BASE locates VEHICLE references TYPE (0,N) (1,1) (1,1) (0,N) 1 (0,N) 1 (0,N) M (1,1) M 1 houses DRIVER_LOG DRIV_TEST TEST (1,1) (0,N) M (1,1) 1 M (1,1) M (1,1) 1 (0,N) 1 1 (0,N) M 1 M DRIV_LICENSE EMPLOYEE is a DRIVER (1,N) (1,1) (1,1) (0,1) (1,1) 1 (0,N) 1 (0,1) 1 (0,N) (1,1) M (0,N) 1 has 1 M (1,1) LICENSE M (1,1) M DRIV_ENDORSE (0,N) (1,1) DEPENDENT (1,1) M becomes M (1,1) SCHOOL_LICENSE M 1 1 M M 1 (1,1) 1 (0,N) (1,1) ENDORSEMENT MECHANIC MECH_ENDORSE (0,N) 1 1 1 (1,N) (0,N) (1,1) (1,1) (0,N) SCHOOL M (1,1) (0,N) M (1,1) (0,N) (0,N) 1 M 1 MECH_LICENSE (0,N) (1,1)

  11. M M 1 Figure P3.6a ProdCo (PumpCo Database) ERD requires PAYMENT (0,N) (1,1) (1,1) makes (1,N) 1 M 1 M 1 generates contains CUSTOMER INVOICE INV_LINE 1 (0,N) (1,1) (1,N) (1,1) (0,N) M (1,1) M requests is seen in PROD_RETURN (1,1) 1 (1,N) 1 (0,N) M M 1 1 yields is found in VENDOR PRODUCT PROD_RET_LINE (0,N) (1,1) (1,1) (1,N) (0,N) 1 (0,N) 1 (1,1) M receives ASSEMBLY approves M (1,1) M (1,1) (0,N) 1 M M 1 includes ORDER ORD_LINE is included in PART (1,N) (1,1) (1,1) (0,N) M 1 M M 1 has shows in PART_RETURN PART_RET_LINE (0,N) (1,1) (1,N) (1,1) (1,1)

  12. Figure P3.6b PumpCo Database Design Option PRODUCT 1 (1,N) contains M (1,1) 1 1 PART (0,N) (0,N) M M ASSEMBLY (1,1) (1,1) The M:N recursive relationship “PART includes PART” is implemented through the composite entityASSEMBLY. By implementing such a design, the end user can track what components are found ineach multi-component part. The design can easily be modified to track serialized parts that are foundin each product.

  13. Figure P3.6c PumpCo Final (Part 1) ERD 1 Note: The ACCOUNT entity represents all thetransactions. Because the transactions includeproduct returns, customer payments, and in-voicing, the invoice number can occur manytimes in the ACCOUNT entity. creates (0,1) M makes ACCOUNT (1,1) M (0,1) PROD_SERIAL_PART_SERIAL enters M (1,1) 1 (0,N) 1 (1,N) 1 M generates CUSTOMER INVOICE (0,N) (1,1) 1 (0,N) (1,N) 1 1 (0,N) M contains consists of produces INV_LINE (1,1) M (1,1) M (1,1) 1 (1,N) 1 M PROD_RETURN yields references PROD_SERIAL (1,1) (1,1) 1 (1,N) M (1,1) 1 (0,N) M M 1 1 possesses is shown in has PROD_RET_LINE PRODUCT (1,1) (1,1) (0,N) (1,N) Connector to part 2

  14. Figure P3.6d PumpCo Final (Part 2) ERD ships produces PRODUCT Connector to Part 1 1 (0,N) (1,1) M 1 (1,N) 1 M (1,1) VENDOR ORDER (0,N) PROD_PART 1 (0,N) 1 (1,N) M (1,1) includes 1 M M (1,1) 1 (1,N) (0,N) (1,1) M 1 receives is found in ORD_LINE PART ASSEMBLY (1,1) (0,N) 1 (0,N) 1 (0,N) 1 M (0,N) (1,1) comprises appears in M (1,1) M (1,1) 1 M holds ORD_LINE_PART_SERIAL PART_RET_LINE (1,1) (0,N) 1 (1,1) (1,1) M 1 is included in PART_RETURN PART_RET_LINE_PART_SERIAL (1,N) M (1,1)

  15. Figure P3.7 PumpCo Relational Schema

  16. Problem 3.9a1 The Chen AC_Museum ERD, part 1 M makes CONTRIBUTION (1,1) 1 (0,N) DONOR 1 M 1 1 (0,N) 1 is a STORY REFERENCE (0,1) (1,1) (0,N) (1,1) donates (0,N) (1,1) M M (1,1) 1 (0,N) M 1 1 1 DONATION is an ARTIFACT yields AIRCRAFT (0,1) (1,1) (0,1) (0,N) M (1,1) 1 describes MODEL (0,N)

  17. STORY_NUM (PK,FK) AC_NUM (PK,FK) Problem 3.9a2 The Enhanced Chen AC_Museum ERD, part 1 M makes CONTRIBUTION (1,1) CONTRIB_NUM (PK) M (0,N) 1 DONOR_NUM (FK) CONTRIB_DATE CONTRIB_AMOUNT includes REFERENCE DONOR (1,1) DONOR_NUM (PK) 1 (0,N) 1 DONOR_LNAME is a STORY PHOTO_PATH (1,1) 1 (0,N) STORY_NUM (PK) 1 M (1,1) DONATE_NUM (FK) STORY_TEXT (0,1) donates is found in M 1 ARTIFACT is an M (1,1) 1 (0,1) (0,N) (1,1) 1 1 ARTI_NUM (PK) DONATION yields AIRCRAFT DONATE_NUM (FK) ARTI_DESCRIPT AC_NUM (FK) ARTI_LOCATION PHOTO_PATH (0,1) (0,N) DONATE_NUM (PK) AC_NUM (PK) DONOR_NUM (FK) DONATE_DATE DONATE_TYPE MOD_CODE (FK) AC_BLOCK (1,1) 1 M describes MODEL (0,N) MOD_CODE (PK) MOD_DESCRIPT MOD_ENGINE

  18. STORY_NUM (PK,FK) AC_NUM (PK,FK) Problem 3.9a3 The Crow's Foot AC_Museum ERD, part 1 makes CONTRIBUTION CONTRIB_NUM (PK) includes DONOR_NUM (FK) CONTRIB_DATE CONTRIB_AMOUNT REFERENCE DONOR DONOR_NUM (PK) DONOR_LNAME STORY PHOTO_PATH STORY_NUM (PK) is a DONATE_NUM (FK) STORY_TEXT donates is found in ARTIFACT is an yields ARTI_NUM (PK) DONATION AIRCRAFT DONATE_NUM (FK) ARTI_DESCRIPT AC_NUM (FK) ARTI_LOCATION PHOTO_PATH DONATE_NUM (PK) AC_NUM (PK) DONOR_NUM (FK) DONATE_DATE DONATE_TYPE MOD_CODE (FK) AC_BLOCK describes MODEL MOD_CODE (PK) MOD_DESCRIPT MOD_ENGINE

  19. Problem 3.9b1 The Chen AC_Museum ERD, part 2 Note: The ASSIGNMENT entity would include foreign keys to four other entities. (AIRCRAFT, SQUADRON, WING, and OTHER.) The reference to the OTHER entity is N/A (not applicable) in most of the assignments. Therefore, to avoid the use of nulls in the ASSIGNMENT entity, the OTHER entity will include a dummy record in which the PK value is N/A. Note: The OTHER entity would be used to record assignment to an Air Force storage facility, a museum, a disposal unit (to scrap the aircraft), a flight research facility, or an Air Force / Air Guard facility in which the aircraft would be displayed as a so-called gate guard. AIRCRAFT 1 (0,N) (1,1) M 1 M 1 M SQUADRON OTHER ASSIGNMENT (1,1) (0,N) (0,N) (1,1) (0,N) 1 (1,1) M Note: Optionalities are often used to make initial data entry easier. For example, an aircraft must be assigned to a wing and a squadron. However, making ASSIGNMENT optional to AIRCRAFT will make it much easier for the end user to enter an aircraft record if that aircraft’s assignment is not yet known. The same argument may be made for several of the other optionalities shown here. M (1,1) (0,N) 1 WING SQUA_COMM (0,N) 1 (1,1) M 1 (0,N) M (1,1) 1 M PERSONNEL WING_COMM (0,N) (1,1)

  20. Problem 3.9b2 The Enhanced Chen AC_Museum ERD, part 2 AIRCRAFT 1 1 SQUADRON OTHER (0,N) AC_NUM (PK) (0,N) SQUA_NUM (PK) OTHER_NUM (PK) MOD_CODE (FK) SQUA_NAME OTHER_TYPE OTHER_DESCRIPT 1 (0,N) 1 (0,N) is given requires has shows in (1,1) M M (1,1) M (1,1) M M ASSIGNMENT gets SQUA_LEAD (1,1) (1,1) ASSIGN_NUM (PK) SQLEAD_NUM (PK) 1 (0,N) ASSIGN_DATE AC_NUM (FK) WING_NUM (FK) SQUA_NUM (FK) OTHER_NUM (FK) 1 SQUA_NUM (FK) PERS_NUM(FK) SQLEAD_DATE SQLEAD_RANK WING (0,N) WING_NUM (PK) WING_NAME uses M (1,1) Note: The entities that had composite primary keys now have simple PKs to ensure that they will be referenced more easily. Entry of unique records will be ensured (at the application level) through their composite indexes. You will learn about CI in Chapter 4. M (1,1) leads M WING_COMMAND (1,1) 1 (0,N) WINGCOM_NUM (PK) 1 commands PERSONNEL WING_NUM (FK) PERS_NUM(FK) WINGCOM_DATE WINGCOM_RANK (0,N) PERS_NUM (PK) PERS_LNAME

  21. Problem 3.9b3 The Crow's Foot AC_Museum ERD, part 2 AIRCRAFT SQUADRON OTHER AC_NUM (PK) SQUA_NUM (PK) OTHER_NUM (PK) MOD_CODE (FK) SQUA_NAME OTHER_TYPE OTHER_DESCRIPT is given requires has ASSIGNMENT gets SQUA_LEAD shows in ASSIGN_NUM (PK) SQLEAD_NUM (PK) ASSIGN_DATE AC_NUM (FK) WING_NUM (FK) SQUA_NUM (FK) OTHER_NUM (FK) uses SQUA_NUM (FK) PERS_NUM(FK) SQLEAD_DATE SQLEAD_RANK WING WING_NUM (PK) WING_NAME Note: The entities that had composite primary keys now have simple PKs to ensure that they will be referenced more easily. Entry of unique records will be ensured (at the application level) through their composite indexes. You will learn about the composite indexes in Chapter 4. leads commands WING_COMMAND PERSONNEL WINGCOM_NUM (PK) PERS_NUM (PK) WING_NUM (FK) PERS_NUM(FK) WINGCOM_DATE WINGCOM_RANK PERS_LNAME

  22. The End

More Related