1 / 34

Chapter 2 Problem Solutions

Chapter 2 Problem Solutions. Peter Rob and Elie Semaan Databases: Design, Development, and Deployment Using Microsoft Access Second Edition. Figure P2.1 The Labeled Dependency Diagram for Problem 2.1. A B C D E F G H I J.

dudley
Download Presentation

Chapter 2 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 2 Problem Solutions Peter Rob and Elie SemaanDatabases: Design, Development,and DeploymentUsing Microsoft Access Second Edition

  2. Figure P2.1 The Labeled Dependency Diagram for Problem 2.1 A B C D E F G H I J transitive dependency partial dependency transitive dependency

  3. Figure P2.2 The Initial Normalization Procedures A No dependencies Step1: Identify each of the primary key attributes.(In this case, the PK attributes are A, B, C.)Step 2: Place each of the primary key attributes ona separate line.Step 3: Write the composite PK on a separate line.Step 4: Break out all partial dependencies to createa new table. (See Table 1, B D.) B D Table 1 3NF C No dependencies A B C E F G H I J Table 2 2NF Transitive dependencies Foreign key to table 1 Transitive dependency

  4. Figure P2.3 The Completed Normalization Procedures B D Table 1 3NF E J Table 2 3NF F H I Table 3 3NF A B C E F G Table 4 3NF Foreign key to table 1 Foreign key to table 3 Foreign key to table 2

  5. Figure P2.4 The STUDENT Table's Dependency Diagram STU_NUM STU_LNAME STU_FNAME COLL_NAME DEPT_NUM DEPT_NAME STU_CREDITS STU_CLASS STU_GPA Transitive dependency Transitive dependency Note: Although the STU_CREDITS determine the STU_CLASS,there is no unique student credit hour value that determines eachstudent classification. For example, whether a student has earned 42,45, or 51 credit hours, that student is still classified as a sophomore. Therefore, it is not appropriate to create a linked CLASS table to the STUDENT entity. Instead, you may create a CLASS look-up table, or you may let the application code determine the student classification. In short, the transitive dependency STU_CREDITS STU_CLASSis not as clear-cut as it appears to be at first glance.

  6. Figure P2.5 The Normalized Results of the STUDENT Table's Decomposition STU_NUM STU_LNAME STU_FNAME STU_CREDITS STU_CLASS STU_GPA DEPT_NUM STUDENT table, 2NF Note: Although the STU_CREDITS determine the STU_CLASS,there is no unique student credit hour value that determines eachstudent classification. For example, whether a student has earned 42,45, or 51 credit hours, that student is still classified as a sophomore. Therefore, it is not appropriate to create a linked CLASS table to the STUDENT entity. Instead, you may create a CLASS look-up table, or you may let the application code determine the student classification. DEPT_NUM DEPT_NAME COLL_CODE COLL_CODE COLL_NAME DEPARTMENT table, 3NF COLLEGE table, 3NF Note: The normalization process usually requires the creation of additional attributes to generate appropriate entities. In this case, we used the COLL_NAME attribute to help define the COLLEGE entity’s characteristics. Additional COLLEGE attributes might include a FK to an EMPLOYEE entity, recognizing that a college has a dean. Other appropriate COLLEGE entities might be the mail stop, phone extension, etc.

  7. Figure P2.5 The STUDENT-Table-Based ERD Note: The optionalities shown in this ERD are based on assumedbusiness rules. In this example, DEPARTMENT is mandatory toSTUDENT, thus requiring each student to select an existingdepartment. (The referential integrity rules require that each DEPT_NUM foreign key value in the STUDENT table must pointto an existing DEPARTMENT table primary key value. Therefore,a DEPARTMENT must exist before students can be assigned to it.) Some colleges do not have departments. Therefore, DEPARTMENT Is optional to COLLEGE. However, each department “belongs” to a college. Therefore, COLLEGE is mandatory to DEPARTMENT. COLLEGE 1 (0,N) includes M (1,1) 1 M has DEPARTMENT STUDENT (0,N) (1,1)

  8. Figure P2.(a) and (b) The Dependency Diagram Solution for Problem 2.7, Parts (a) and (b) A B C D E F G H I J K Partial dependency Transitive dependencies Transitive dependency Partial dependency This dependency diagram shows a 1NF condition, because it contains both partial and transitive dependencies. (Removal of the partial dependencywould yield a 2NF condition.)

  9. Figure P2.7(C) The Dependency Diagram Solution for Problem 2.7, Part (c) A J K B D Table 1 3NF Table 2 3NF E G F H I Table 3 3NF Table 4 3NF A B C E F G Table 5 3NF Foreign key to table 1 Foreign key to table 4 Foreign key to table 2 Foreign key to table 3

  10. Figure P2.8(a) The AIRCRAFT Table's Dependency Diagram, Part (a) RENT_NUM CUS_NUM CUS_LNAME AC_NUM AC_TTAF MOD_CODE MOD_CHG_HR RENT_HOURS RENT_CHARGE Transitive dependency Transitive dependencies Transitive dependency The AIRCRAFT table’s dependency diagram indicates a 2NF condition, based on the existence oftransitive dependencies. Note that two transitive dependencies overlap: the AC_TTAF clearly determines the MOD_CODE attribute’s value and, therefore, the MOD_CHG_HOUR. However,the MOD_CODE attribute also determines the MOD_CHG_HR. Given the proper use of namingconventions, it is clear that the MOD_CODE is likely to be the PK of a MODEL table in which the MOD_CHG_HR attribute values would be stored. (Naturally, the MODEL table should store all attributes that are properly descriptive of an aircraft model!)

  11. Figure P2.08(c), The Normalized AIRCRAFT Table's Dependency Diagrams RENT_NUM CUS_NUM AC_NUM RENT_HOURS RENT_CHARGE RENT table, 3NF CUS_NUM CUS_LNAME AC_NUM AC_TTAF MOD_CODE CUSTOMER table, 3NF AIRCRAFT table, 3NF MOD_CODE MOD_CHG_HR MODEL table, 3NF

  12. Figure P2.8d The AVIARS Company's Initial ERD Note: Optionalities are often used for operational reasons. Inthis case, AVIARS maintains a list of basic aircraft models, but some of these models are not (yet) found in the AVIARS aircraft fleet. Therefore, AIRCRAFT is kept optional to MODEL. When AVIARS puts a new aircraft on line, it has not (yet) beenrented. Therefore, RENTAL is made optional to AIRCRAFT.(If the relationship were mandatory, you would have to create a dummy rental record just to place the aircraft in your database.) Some customers have not (yet) rented an aircraft, so RENTAL ismade optional to CUSTOMER. MODEL (0,N) 1 references (1,1) M 1 M M 1 CUSTOMER AIRCRAFT RENTAL (0,N) (1,1) (1,1) (0,N)

  13. Figure P2.9a The AVIARS Company's Maintenance Table Dependency Diagram LOG_NUM LOG_DATE LINE_NUM CUS_NUM CUS_LNAME AC_NUM AC_TTAF MOD_CODE PART_CODE PART_PRICE Transitive dependency Transitive dependencies Transitive dependency Dependency diagram,continued. (LOG_NUM and LINE_NUM repeated.) LOG_NUM LINE_NUM UNITS_USED LINE_HOURS EMP_NUM Note: By itself, the log’s line number (LINE_NUM) does not determine any attribute value. Therefore, thereis no partial dependency. However, there are several transitive dependencies, so the dependency diagram in-dicates a 2NF condition.

  14. Figure P2.9 (b) and (c) The AVIARS Company's Maintenance Normalization LOG table LOG_NUM CUS_NUM AC_NUM LOG_DATE_IN EMP_NUM_IN LOG_PROBLEM LOG_DATE_OUT EMP_NUM_OUT (Note that the normalization process usually yields additional attributes to make sure that the entities aredescribed more completely and precisely. Each new attribute must be checked to ensure its compliancewith the normalization requirements.) LINE table LOG_NUM LINE_NUM PART_CODE UNITS_USED LINE_HOURS EMP_NUM PART_CODE PART_PRICE PART_QOH CUS_NUM CUS_LNAME CUS_E_MAIL PART table CUSTOMER table AC_NUM AC_TTAF MOD_CODE CUS_NUM MOD_CODE MOD_NAME MOD_PRICE AIRCRAFT table MODEL table

  15. Figure P2.9d The AVIARS Company's expanded ERD MODEL 1 (0,N) references (1,1) M 1 1 M M 1 AIRCRAFT enters CUSTOMER RENTAL (0,N) (0,N) (1,1) (1,1) (0,N) (1,1) M 1 1 M owns LOG (0,N) (1,1) (1,N) 1 (1,N) M ACTION PART contains (1,1) M (1,1) M (0,N) M (1,1) 1 (0,N) 1 1 1 uses LINE is a MECHANIC EMPLOYEE (0,1) (0,1) (1,1) 1 M signs (0,N) (1,1)

  16. Figure P2.10 The Dependency Diagram for Problem 2.10 A B C D E F G H I J

  17. Figure P2-10(Solution) The Labeled Dependency Diagram for Problem 2.10 A B C D E F G H I J Transitive dependencies Partial dependencies Partial dependencies

  18. Figure P2.11 Problem 2.11 Normalization A F G Table 1 3NF B C D Table 2 3NF A B E H I J Table 3 2NF Transitive dependency

  19. Figure P2-12 The Completed Normalization for Problem 12 A F G B C D Table 1 3NF Table 2 3NF E H I A B E J Table 3 3NF Table 4 3NF Foreign key to table 1 Foreign key to table 2 Foreign key to table 3

  20. Figure P2-13 Dependency Diagram for Problem 13 EMP_NUM DRIVER_LIC_1 DRIVER_LIC_1TYPE DRIVER_LIC_1DATE SCHOOL_CODE1 SCHOOL_NAME1 Transitive dependency Dependency diagram, continued. (PK repeated.) EMP_NUM DRIVER_LIC_2 DRIVER_LIC_2TYPE DRIVER_LIC_2DATE SCHOOL_CODE2 SCHOOL_NAME2 Transitive dependency Dependency diagram, continued. (PK repeated.) EMP_NUM ENDORSE_CODE1 ENDORSE-CODE1_DESC ENDORSE_CODE1_DATE Transitive dependencies Dependency diagram, continued. (PK repeated.) EMP_NUM ENDORSE_CODE1 ENDORSE-CODE1_DESC ENDORSE_CODE1_DATE Transitive dependencies

  21. Figure P2.14 Problem 14 Normalization EMPLOYEE table EMP_NUM EMP_LNAME EMP_E_MAIL DRIV_LICENSE table EMP_NUM LICENSE_CODE LICENSE_DATE SCHOOL_CODE SCHOOL table SCHOOL_CODE SCHOOL_NAME ENDORSE_CODE ENDORSE_DESCRIPTION LICENSE_CODE LICENSE_DESCRIPTION ENDORSEMENT table LICENSE table DRIV_ENDORSEMENT table EMP_NUM ENDORSE_CODE ENDORSE_DATE SCHOOL_CODE

  22. Figure P2-15a ERD, Version 1 1 1 is a EMPLOYEE DRIVER (0,1) (1,1) 1 (0,N) Assumptions: Drivers are employees who have uniquecharacteristics, such as a license, a medical status, andso on. Each driver has a single license. Higher-levellicenses supersede lower-level licenses. A license can have more than one endorsement for such items as hazardous materials handling, dual trailers, etc. An endorsement may or may not have been earned from a school. Not all approved schools are represented in the endorsement listing. Some endorsements have not (yet) been earned by any drivers. M (1,1) M 1 SCHOOL ENDORSE_DRIV (1,1) (0,N) M (1,1) 1 (0,N) ENDORSEMENT

  23. Figure P2-15b ERD, Version 2 1 1 1 M M is a EMPLOYEE DRIVER DRIV_LICENSE (1,N) (1,1) (1,1) (0,1) (1,1) 1 (0,N) (1,1) M Assumptions: Not all employees are drivers. Drivers can have multiple licenses and endorsements. A driver must have at least one license, but may not (yet) have earned one or more endorsements. A license must have been earned from an approved school, but an endorsement is not necessarily earned from a school. The company has not (yet) hired drivers from some of the approved schools. M (1,1) (0,N) 1 M LICENSE DRIV_ENDORSE (0,1) M (1,1) 1 (0,N) 1 1 ENDORSEMENT SCHOOL (0,N) (0,N)

  24. Figure P2-15c ERD, Version 3 1 1 1 M M is a EMPLOYEE DRIVER DRIV_LICENSE (1,N) (1,1) (1,1) (0,1) (1,1) 1 (0,N) (1,1) M M (1,1) (0,N) 1 M LICENSE DRIV_ENDORSE (0,1) 1 M (1,1) (0,N) If you want to keep track of whichschools offer what licenses, createa composite entity to link the two. The optionalities depend on whatassumptions are expressed in thebusiness rules. (1,1) M LIC_SCHOOL (1,1) M 1 (0,N) (0,N) 1 1 1 ENDORSEMENT SCHOOL (0,N) (0,N)

  25. Figure P2-16 The TrustUs Initial Dependency Diagram CLIENT_NUM CLIENT_NAME CLIENT_REGION CONTRACT_NUM CONTRACT_DATE CONTRACT_AMT Partial dependency Partial dependencies CONS_NUM1 CONS_NAME1 CONS1_REGION CONS_NUM2 CONS_NAME2 CONS2_REGION Transitive dependencies Transitive dependencies CONS_NUM3 CONS_NAME3 CONS3_REGION CONS_NUM4 CONS_NAME4 CONS4_REGION Transitive dependencies Transitive dependencies CONS_NUM1 CONS_NUM2 CONS_NUM3 CONS_NUM4 CERT_1 CERT_2 CERT_3 CERT_4 Transitive dependencies

  26. Figure P2-17a The TrustUs Normalization Results CLIENT_NUM CLIENT_NAME CLIENT_EMAIL CLIENT_PHONE REGION_CODE Table name: CLIENT Note: The normalization process often causes the designer to add attributes to describe theentities more completely and precisely. Information requirements also tend to require thedesigner to decompose the attributes into their atomic components. Keep these points inmind as you examine the entity structures shown here. Table name: REGION REGION_CODE REGION_NAME Table name: CONSULTANT CONSULTANT_NUM CONSULTANT_NAME REGION_CODE Table name: CERTIFICATION CERT_CODE CERT_DESCRIPTION CERT_CHG_HOUR Table name: CONTRACT CONTRACT_NUM CONTRACT_AMOUNT CONTRACT_DATE CLIENT_NUM

  27. Figure P2-17b The TrustUs Normalization Results, continued Normalization focuses on the attributes within an entity, rather than on the relationship between the entities.Therefore, the ERD is an indispensable production database design tool. For example, we know that there mustbe a relationship between the consultant and the contract and one between the consultant and his or her specialty,probably through certification. Without the ERD, the specialty, assignment, and certification structures shown here are not readily apparent. (The attribute names have been abbreviated to fit the screen.) CLNT_NUM CLNT_NAME CLNT_EMAIL CLNT_PHONE REG_CODE REG_CODE REG_NAME Table name: CLIENT Table name: REGION SPEC_CHG_HR CONS_NUM CONS_NAME REG_CODE SPEC_CODE SPEC_DESCR Table name: CONSULTANT Table name: SPECIALTY CONTR_NUM CONS_NUM ASSGN_HRS ASSGN_CHG CONS_NUM SPEC-CODE CERT_DATE Table name: ASSIGNMENT Table name: CERTIFICATION Table name: CONTRACT CONTR_NUM CONTR_DATE CONTR_AMT CLNT_NUM

  28. Figure P2-18 The TrustUs ERD 1 M 1 M houses CERTIFICATION REGION CONSULTANT (0,N) (1,1) 1 (0,N) (0,N) 1 (0,N) (1,1) M (1,1) (1,1) M 1 (0,N) contains ASSIGNMENT SPECIALTY (1,1) M M (1,1) (0,N) 1 1 M generates CLIENT CONTRACT (0,N) (1,1)

  29. Figure P2.19 The TrustUs Relational Schema CERTIFICATION M SPECIALTY CONS_NUM M CONSULTANT SPEC_CODE SPEC_CODE 1 1 CONS_NUM SPEC_DESCR CERT_DATE 1 SPEC_CHG_HR CONS_NAME ASSIGNMENT M CONS_E_MAIL CTRCT_NUM ………. M CONS_NUM M REG_CODE REGION ASSGN_HRS 1 CONTRACT ASSGN_CHG_HR REG_CODE CLIENT 1 CTRCT_NUM 1 REG_NAME ASSGN_CHARGE CLNT_NUM CTRCT _DATE ………. CLNT_NAME CTRCT_AMT M CLNT_E_MAIL CLNT_NUM ………. M REG_CODE Note: Designers often create “artificial” single-attribute keys for performance reasons. For example, theASSIGNMENT table shown here uses a composite key CTRCT_NUM + CONS_NUM. You could createa new PK named ASSGN_NUM and continue to use the original PK attributes as foreign keys.

  30. Figure P2-20 The Initial Dependency Diagram for Problems 2.20 Through 2.22 A B C D E F G

  31. Figure P2.20(Solution) Normalization, Initial Decomposition The original dependencies. Note that the dependencydefined by C B is not transitive, because it showsa PK attribute to be dependent on a non-key attribute.(In a transitive dependency, one non-key attribute det-ermines another non-key attribute!) A B C D E F G Transitive dependency partial dependency Initial decomposition A D 3 NF A B C E F G 2 NF Transitive dependency

  32. Figure P2-21 Normalization, Second Decomposition A D 3 NF E G 3 NF 3 NF, but not BCNF. (The dependency defined by C Binvolves a non-key attribute that determines a key attribute.) A B C E F

  33. Figure P2.22 Normalization, Final Decomposition to BCNF A D E G Table 1, 3 NF Table 2, 3 NF C B A C E F Table 3, 3 NF Table 4, 3 NF and BCNF Foreign key to table 2 Foreign key to table 3 Foreign key to table 1

  34. The End

More Related