Use the database shown in Figure P3.1 to answer Problems 1, 2, 3, 4, 5, 6,...

Transcribed Image Text:

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 EMP_CODE EMP_TITLE EMP_LNAME EMP_FNAME EMP_INITIAL EMP_DOB STORE_CODE lliamson 21-May-84 09-Feb-89 1 Mr. 2 Ms. 3 Ms. 4 Mrs. 5 Mr. 6 Mr. 7 Mr. 8 Ms. 9 Mr. 10 Mrs. 11 Mr. 12 Mr. 13 Mr. 14 Ms. 15 Mr. 16 Mr. 17 Ms. 18 Mr. 19 Mr. 20 Mr. 21 Ms. Ratula Greenboro Rumpersfro Smith Renselaer Ogallo Johnsson Eindsmar Jones Broderick Washington Smith Smith Olenko Archialo Grimaldo Rosenberg Rosten Mckee Baumann 2 Database Corner 3 Tuple Charge 4 Attribute Alley 5 Primary Key Point 1 East 2 West Table name: REGION REGION_CODE REGION DESCRIPT John R S Nancy Lottie Jennie Robert Cary Roberto S Elizabeth I L A Jack Rose Tom Alan Peter Sherry Howard Barry Jeanine Andrew Peter Robert Jennifer W 1003455.76 1421987.39 986783.22 944568.56 2930098.45 W R Table name: STORE STORE_CODE STORE NAME STORE_YTD_SALES REGION_CODE EMP_CODE 1 Access Junction Y N H U V K D F S A Database name: Ch03 StoreCo 02-Oct-81 01-Jun-91 23-Nov-79 25-Dec-85 31-Jul-82 10-Sep-88 19-Apr-75 06-Mar-86 21-Oct-92 08-Sep-94 25-Aug-84 25-May-86 24-May-84 03-Sep-80 12-Nov-90 24-Jan-91 03-Oct-88 06-Mar-90 11-Dec-94 2 2 1 2 1 8 12 7 3 15 3 2 4 5 3 1 3 1 2 4 3 2 3 4 5 5 4 4 4 1 3 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.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 EMP_CODE EMP_TITLE EMP_LNAME EMP_FNAME EMP_INITIAL EMP_DOB STORE_CODE lliamson 21-May-84 09-Feb-89 1 Mr. 2 Ms. 3 Ms. 4 Mrs. 5 Mr. 6 Mr. 7 Mr. 8 Ms. 9 Mr. 10 Mrs. 11 Mr. 12 Mr. 13 Mr. 14 Ms. 15 Mr. 16 Mr. 17 Ms. 18 Mr. 19 Mr. 20 Mr. 21 Ms. Ratula Greenboro Rumpersfro Smith Renselaer Ogallo Johnsson Eindsmar Jones Broderick Washington Smith Smith Olenko Archialo Grimaldo Rosenberg Rosten Mckee Baumann 2 Database Corner 3 Tuple Charge 4 Attribute Alley 5 Primary Key Point 1 East 2 West Table name: REGION REGION_CODE REGION DESCRIPT John R S Nancy Lottie Jennie Robert Cary Roberto S Elizabeth I L A Jack Rose Tom Alan Peter Sherry Howard Barry Jeanine Andrew Peter Robert Jennifer W 1003455.76 1421987.39 986783.22 944568.56 2930098.45 W R Table name: STORE STORE_CODE STORE NAME STORE_YTD_SALES REGION_CODE EMP_CODE 1 Access Junction Y N H U V K D F S A Database name: Ch03 StoreCo 02-Oct-81 01-Jun-91 23-Nov-79 25-Dec-85 31-Jul-82 10-Sep-88 19-Apr-75 06-Mar-86 21-Oct-92 08-Sep-94 25-Aug-84 25-May-86 24-May-84 03-Sep-80 12-Nov-90 24-Jan-91 03-Oct-88 06-Mar-90 11-Dec-94 2 2 1 2 1 8 12 7 3 15 3 2 4 5 3 1 3 1 2 4 3 2 3 4 5 5 4 4 4 1 3 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.

Answer rating: 100% (QA)

Based on the information provided from the Figure P31 here are the answers to the questions 1 For each table identify the primary key and the foreign keys Table EMPLOYEE Primary key is EMPCODE There i... View the full answer