1、Chapter 3 Problems 8-16.Use the database shown in Figure P3.8 to answer Problems 8-15.FIGURE P3.8 The Ch03_StoreCo Database Tables1. 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.TABLEPRIMARY KEYFOREIGN KEY(S

2、)EMPLOYEEEMP_CODESTORE_CODESTORESTORE_CODEREGION_CODE, EMP_CODEREGIONREGION_CODENONE2. Do the tables exhibit entity integrity? Answer yes or no; then explain your answer.TABLEENTITY INTEGRITYEXPLANATIONEMPLOYEEYesEach EMP_CODE value is unique and there are no nulls.STOREYesEach STORE_CODE value is u

3、nique and there are no nulls.REGIONYesEach REGION_CODE value is unique and there are no nulls.3. Do the tables exhibit referential integrity? Answer yes or no; then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key.TABLEREFERENTIAL INTEGRITYEXPLANATIONEMPLOYEEYe

4、sEach STORE_CODE value in EMPLOYEE points to an existing STORE_CODE value in STORE.STOREYesEach 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.REGIONNA4. Describe the type(s) of relationsh

5、ip(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 relationsh

6、ip between REGION and STORE is 1:M.)5. Create the ERD to show the relationship between STORE and REGION.The Crows Foot ERD is shown in Figure P3.12. Note that each store is located in a single region, but that each region can have many stores located in it. (Its always a good time to focus a discuss

7、ion on the role of business rules in the creation of a database design.)Figure P3.12 ERD for the STORE and REGION Relationship6. Create the relational diagram to show the relationship between STORE and REGION.The relational diagram is shown in Figure P3.13. Note (again) that the location of the enti

8、ties 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.13 The Relatio

9、nal Diagram for the STORE and REGION Relationship7. 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 EM

10、PLOYEE, 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.NOTEIt is u

11、seful 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 whi

12、ch 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,

13、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, an

14、d REGION.The Crows Foot ERD is shown in Figure P3.15. 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

15、 entity. In this case, only one EMPLOYEE manages each STORE and each STORE is managed by only one EMPLOYEE.We have shown Figure P3.15s 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. Tha

16、ts 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 manda