Database Systems Design Implementation and Management 9th Edition Coronel Solution Manual PDF

Preview

Summary

Chapter 3 The Relational Database Model Problem Solutions Use the database shown in Figure P3 to answer Problems FIGURE P3 The Ch03_StoreCo Database Tables 1. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the space provided. TA...

Description

Chapter 3 The Relational Database Model

Problem Solutions Use the database shown in Figure P3.1 to answer Problems 1-9.

FIGURE P3.1 The Ch03_StoreCo Database Tables

1. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the space provided. TABLE EMPLOYEE STORE REGION

PRIMARY KEY EMP_CODE STORE_CODE REGION_CODE

FOREIGN KEY(S) STORE_CODE REGION_CODE, EMP_CODE NONE

39

Chapter 3 The Relational Database Model 2. Do the tables exhibit entity integrity? Answer yes or no and then explain your answer. TABLE EMPLOYEE

ENTITY INTEGRITY Yes

STORE

Yes

REGION

Yes

EXPLANATION Each EMP_CODE value is unique and there are no nulls. Each STORE_CODE value is unique and there are no nulls. Each REGION_CODE value is unique and there are no nulls.

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. TABLE EMPLOYEE

REFERENTIAL INTEGRITY Yes

STORE

Yes

REGION

NA

EXPLANATION Each STORE_CODE value in EMPLOYEE points to an existing STORE_CODE value in STORE. Each REGION_CODE value in STORE points to an existing REGION_CODE value in REGION and each EMP_CODE value in STORE points to an existing EMP_CODE value in EMPLOYEE.

4. Describe the type(s) of relationship(s) between STORE and REGION. Because REGION_CODE values occur more than once in STORE, we may conclude that each REGION can contain many stores. But since each STORE is located in only one REGION, the relationship between STORE and REGION is M:1. (It is, of course, equally true that the relationship between REGION and STORE is 1:M.)

40

Chapter 3 The Relational Database Model 5. Create the ERD to show the relationship between STORE and REGION. The Crow’s Foot ERD is shown in Figure P3.5. Note that each store is located in a single region, but that each region can have many stores located in it. (It’s always a good time to focus a discussion on the role of business rules in the creation of a database design.)

Figure P3.5 ERD for the STORE and REGION Relationship

6. Create the relational diagram to show the relationship between STORE and REGION. The relational diagram is shown in Figure P3.6. Note (again) that the location of the entities is immaterial … the relationships are carried along with the entity. Therefore, it does not matter whether you locate the REGION on the left side or on the right side of the display. But you always read from the “1” side to the “M” side, regardless of the entity location.

Figure P3.6 The Relational Diagram for the STORE and REGION Relationship

41

Chapter 3 The Relational Database Model 7. Describe the type(s) of relationship(s) between EMPLOYEE and STORE. ( Hint: Each store employs many employees, one of whom manages the store.) There are TWO relationships between STORE and REGION. The first relationship, expressed by STORE employs EMPLOYEE, is a 1:M relationship, because one store can employ many employees and each employee is employed by one store. The second relationship, expressed by EMPLOYEE manages STORE, is a 1:1 relationship, because each store is managed by one employee and an employee manages only one store.

NOTE It is useful to introduce several ways in which the manages relationship may be implemented. For example, rather than creating the manages relationship between EMPLOYEE and STORE, it is possible to simply list the manager's name as an attribute in the STORE table. This approach creates a redundancy which may not do much damage if the information requirements are limited. However, if it is necessary to keep track of each manager's sales and personnel management performance by store, the manages relationship we have shown here will do a much better job in terms of information generation. Also, you may want to introduce the notion of an optional relationship. After all, not all employees participate in the manages relationship. We will cover optional relationships in detail in Chapter 4, “Entity relationship (ER) Modeling.”

8. Draw the ERD to show the relationships among EMPLOYEE, STORE, and REGION. The Crow’s Foot ERD is shown in Figure P3.8. Remind students that you always read from the “1” side to the “M” side in any 1:M relationship, i.e., a STORE employs many EMPLOYEEs and a REGION contains many STORES. In a 1:1 relationship, you always read from the “parent” entity to the related entity. In this case, only one EMPLOYEE manages each STORE … and each STORE is managed by only one EMPLOYEE. We have shown Figure P3.8’s Visio Professional-generated ERD to include the properties of the manages relationship. Note that there is no mandatory 1:1 relationship available at this point. That’s why there is an optional relationship – the O symbol – next to the STORE entity to indicate that an employee is not necessarily a manager. Let your students know that such optional relationships will be explored in detail in Chapter 4. (Explain that you can create mandatory 1:1 relationships when you add attributes to the entity boxes and specify a mandatory data entry for those attributes that are involved in the 1:1 relationship.) If you use Microsoft Visio Professional, let your students use the Visio tutorial in Appendix A, located in the book’s Premium Website.

42

Chapter 3 The Relational Database Model

Figure P3.8 StoreCo Crow’s Foot ERD

43

Chapter 3 The Relational Database Model 9. Create the relational diagram to show the relationships among EMPLOYEE, STORE, and REGION. The relational diagram is shown in Figure P3.9.

Figure P3.9 The Relational Diagram

NOTE The relational diagram in Figure P3.9 was generated in Microsoft Access. If a relationship already exists between two entities, Access generates a virtual table (in this case, EMPLOYEE_1) to generate the additional relationship. The virtual table cannot be queried; its only function is to store the manages relationship between EMPLOYEE and STORE. Just how multiple relationships are stored and managed is a function of the software you use.

44

Chapter 3 The Relational Database Model

Use the database shown in Figure P3.10 to work Problems 10−16. Note that the database is composed of four tables that reflect these relationships:  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.

FIGURE P3.10 The Ch03_BeneCo Database Tables

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 in the assigned space provided. TABLE EMPLOYEE BENEFIT JOB PLAN

PRIMARY KEY EMP_CODE EMP_CODE + PLAN_CODE JOB-CODE PLAN_CODE

45

FOREIGN KEY(S) JOB_CODE EMP_CODE, PLAN_CODE None None

Chapter 3 The Relational Database Model 11. Create the ERD to show the relationship between EMPLOYEE and JOB. The ERD is shown in Figure P3.11. Note that the JOB_CODE = 1 occurs twice in the EMPLOYEE table, as does the JOB_CODE = 2, thus providing evidence that a JOB can be assigned to many EMPLOYEEs. But each EMPLOYEE has only one JOB_CODE, so there exists a 1:M relationship between JOB and EMPLOYEE.

Figure P3.11 The ERD for the EMPLOYEE-JOB Relationship

12. Create the relational diagram to show the relationship between EMPLOYEE and JOB. The relational schema is shown in Figure P3.12.

Figure P3.12 The Relational Diagram

13. Do the tables exhibit entity integrity? Answer yes or no and then explain your answer. TABLE EMPLOYEE

ENTITY INTEGRITY Yes

BENEFIT

Yes

JOB

Yes

PLAN

Yes

EXPLANATION Each EMP_CODE value is unique and there are no nulls. Each combination of EMP_CODE and PLAN_CODE values is unique and there are no nulls. Each JOB_CODE value is unique and there are no nulls. Each PLAN_CODE value is unique and there are no nulls.

46

Chapter 3 The Relational Database Model 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. TABLE EMPLOYEE

REFERENTIAL INTEGRITY Yes

BENEFIT

Yes

JOB PLAN

NA NA

EXPLANATION Each JOB_CODE value in EMPLOYEE points to an existing JOB_CODE value in JOB. Each EMP_CODE value in BENEFIT points to an existing EMP_CODE value in EMPLOYEE and each PLAN_CODE value in BENEFIT points to an existing PLAN_CODE value in PLAN.

15. Create the ERD to show the relationships among EMPLOYEE, BENEFIT, JOB, and PLAN. The Crow’s Foot ERD is shown in Figure P3.15.

Figure P3.15 BeneCo Crow’s Foot ERD

47

Chapter 3 The Relational Database Model 16. Create the relational diagram to show the relationships among EMPLOYEE, BENEFIT, JOB, and PLAN. The relational diagram is shown in Figure P3.16. Note that the location of the entities is immaterial – the relationships move with the entities.

Figure P3.16 The Relational Diagram

48

Chapter 3 The Relational Database Model

Use the database shown in Figure P3.17 to answer Problems 17-23.

FIGURE P3.17 The Ch03_TransCo Database Tables

17. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the space provided. TABLE TRUCK BASE TYPE

PRIMARY KEY TRUCK_NUM BASE_CODE TYPE_CODE

FOREIGN KEY(S) BASE_CODE, TYPE_CODE None None

NOTE Note: The TRUCK_SERIAL_NUM could also be designated as the primary key. Because the TRUCK_NUM was designated to be the primary key, TRUCK_SERIAL_NUM is an example of a candidate key.

49

Chapter 3 The Relational Database Model 18. Do the tables exhibit entity integrity? Answer yes or no and then explain your answer. TABLE TRUCK

ENTITY INTEGRITY Yes

BASE

Yes

TYPE

Yes

EXPLANATION The TRUCK_NUM values in the TRUCK table are all unique and there are no nulls. The BASE_CODE values in the BASE table are all unique and there are no nulls. The TYPE_CODE values in the TYPE table are all unique and there are no nulls.

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. TABLE TRUCK

BASE TYPE

REFERENTIAL INTEGRITY Yes

EXPLANATION The BASE_CODE values in the TRUCK table reference existing BASE_CODE values in the BASE table or they are null. (The TRUCK table's BASE_CODE is null for TRUCK_NUM = 1004.) Also, the TYPE_CODE values in the TRUCK table reference existing TYPE_CODE values in the TYPE table.

NA NA

20. Identify the TRUCK table’s candidate key(s). A candidate key is any key that could have been used as a primary key, but that was, for some reason, not chosen to be the primary key. For example, the TRUCK_SERIAL_NUM could have been selected as the PK, but the TRUCK_NUM was actually designated to be the PK. Therefore, the TRUCK_SERIAL_NUM is a candidate key. Also, any combination of attributes that would uniquely identify any truck would be a candidate key. For example, the combination of BASE_CODE, TYPE_CODE, TRUCK_MILES, and TRUCK_BUY_DATE is not likely to be duplicated and this combination would, therefore, be a candidate key. However, while the latter combination might constitute a candidate key, such a combination would not be practical. (An extreme – and impractical -- example of a candidate key would be the combination of all of a table’s attributes.)

50

Chapter 3 The Relational Database Model

NOTE Some of the answers to the following problem 21 define only a few of the available correct choices. For example, a superkey is, in effect, a candidate key containing redundant attributes. Therefore, any primary key plus any other attribute(s) is a superkey. Because a secondary key does not necessarily yield unique outcomes, the number of attributes that constitute a secondary key is somewhat arbitrary. The adequacy of a secondary key depends on the extent of the end-user's willingness to accept multiple matches.

21. For each table, identify a superkey and a secondary key. TABLE

SUPERKEY

SECONDARY KEY

TRUCK

TRUCK_NUM + TRUCK_MILES

BASE_CODE + TYPE_CODE

TRUCK_NUM + TRUCK_MILES + TRUCK_BUY_DATE TRUCK_NUM + TRUCK_MILES + TRUCK_BUY_DATE + TYPE_CODE

(This secondary key is likely to produce multiple matches, but it is not likely that end-users will know attribute values such as TRUCK_MILES or TRUCK_BUY_DATE. Therefore, the selected attributes create a reasonable secondary key.)

BASE_CODE + BASE_CITY

BASE_CITY + BASE_STATE

BASE_CODE + BASE_CITY + BASE_CITY

(This a very effective secondary key, since it is not likely that a state contains two cities with the same name.)

TYPE_CODE + TYPE_DESCRIPTION

TYPE_DESCRIPTION

BASE

TYPE

51

Chapter 3 The Relational Database Model 22. Create the ERD for this database. The Crow’s Foot ERD is shown in Figures P3.22.

Figure P3.22 TransCo Crow's Foot ERD

23. Create the relational diagram for this database. The relational diagram is shown in Figure P3.23.

Figure P3.23 The Ch03_TransCo Relational Diagram

52

Chapter 3 The Relational Database Model Use the database shown in Figure P3.24 to answer Problems 24−31. ROBCOR is an aircraft charter company that supplies on-demand charter flight services using a fleet of four aircraft. Aircrafts 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 (Part 1)

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 having 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.

53

Chapter 3 The Relational Database Model

FIGURE P3.24 The Ch03_AviaCo Database Tables (Part 2)

54

Chapter 3 The Relational Database Model NOTE Earlier in the chapter, it was stated that it is best to avoid homonyms and synonyms. In this problem, both the pilot and the copilot are pilots 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 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. Don’t use synonyms. If your design requires the use of synonyms, revise the design! 2. 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.

55

Chapter 3 The Relational Database Model ROBCOR 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. The nulls in the CHARTER table’s CHAR_COPILOT column indicate that a copilot is not necessarily required for some charter trips or for some aircraft. Federal Aviation Administration (FAA) rules require a copilot on jet aircraft and on aircraft having a gross take-off weight over 12,500 pounds. None of the aircraft in the AIRCRAFT table is 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 it was stated that it is best to avoid homonyms and synonyms. In this problem, both the pilot and the copilot are pilots 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 members would yield additional nulls in the CHARTER table. You will have a chance to correct those design shortcomings in Problem 33. The problem illustrates two important points: 1. Don’t use synonyms. If your design requires the use of synonyms, revise the design! 2. 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.

56

Chapter 3 The Relational Database Model 24. For each table, where possible, identify: a. The primary key TABLE CHARTER AIRCRAFT MODEL PILOT EMPLOYEE CUSTOMER

PRIMARY KEY CHAR_TRIP AC_NUMBER MOD_CODE EMP_NUM EMP_NUM CUS_CODE

b. A superkey TABLE CHARTER AIRCRAFT MODEL PILOT EMPLOYEE CUSTOMER

SUPER KEY CHAR_TRIP + CHAR_DATE AC_NUM + MOD-CODE MOD_CODE + MOD_NAME EMP_NUM + PIL_LICENSE EMP_NUM + EMP_DOB CUS_CODE + CUS_LNAME

NOTE The super key and secondary key compositions were described in the note that accompanied Problem 21. The same arguments may be pursued with reference to parts b, c and e of this problem.

57

Chapter 3 The Relational Database Model c. A candidate key TABLE CHARTER

CANDIDATE KEY No practical candidate key is available. For example, CHAR_DATE + CHAR_DESTINATION + AC_NUMBER + CHAR_PILOT + CHAR_COPIL...