Week 2 Homework (35 points) Page 1 of 3 INTRODUCTION The purpose of this assignment is to give you practice in identifying the Primary Keys (PKS) and Foreign Keys (FKs) in an existing relational schema or database, determining if the schema satisfies both entity integrity and referential integrity based on the dataset provided, identifying the connectivities and cardinalities associated with the schema, and producing a Chen and Crow's Foot diagram to represent that schema. WHAT YOU WILL NEED FOR THIS ASSIGNMENT A diagramming tool (click HERE for choices) ACTION ITEMS Study the MS-Access relational diagram/schema below that displays the entities and attributes for the Energy Company along with sample data. All Primary Keys (PKs) are in bold in the diagram. All Foreign Keys (FKs) are at the many end of the 1-M relationships ) and are named the same as the PK they reference. Use this information to answer the questions below: The Energy Company Relational Schema REGION REGION_CODE REGION_DESCRIPT STORE STORE CODE STORE MAME STORE_YTD_SALES REGION_CODE EMPLOYEE EMP_CODE EM TITLE UNPUNE EMP FRAME EMP_INITIA. ENP_DOB SC_CODE STORE_CODE JOB KXI COXX DOB_DESCRIPTION OB_BASE PAY The Energy Sample Data CODE HT w 21-May-54 1 ME FRAME John Nancy Lomi Jennie 2 R 5 FORET 011 1 EMPLOYEE TABLE Serge Dua EMP CORE TEMP MEE 3 Mr. WI 2 Matula 31 NE Green 4 M Rumah 3 M Em 7 ME Mr. JO Mr. Cindum 10 Mes nes 11 Mr. Broderi OS 25-Bases 11-02 1 w BE Lack Bose Tom 21-Oct-72 SIRSYID SALESIANO 200.5455.78 STORE TABLE Sample Data RECE SERE MANE Account Date Comer 3 Tuple Charpe Listoles Primary CayPost 966753.221 2350098.01 REGION TABLE Sample Busa REGION_CODE REGION_DESCRIPT We North South NOTEWe do then it is considered to KB TABLES ERCERA TRACCION KAP 20.75 Tech 230 Merapuriel 80.00 2 Erarehaidar Week 2 Homework (35 points) Page 2 of 3 1. Download and use the MS Word template file (click HERE) to fill in your answers to the questions below. [0-1 pt] 2. Identify the primary keys and foreign keys in the template file you downloaded above where indicated. If an entity does not have a foreign key, write N/A. (0-4 pts] 3. Do the entities exhibit entity integrity? Answer yes or no in the template file you downloaded above, and then explain your answer where indicated. Refer to the Meet session for words to use in explanation. [0-4 pts] 4. Do the entities exhibit referential integrity? Answer yes or no in the template file you downloaded above, and then explain your answer where indicated. Write N/A (Not Applicable) if the table does not have a foreign key. [0-4 pts] 5. Identify each relationship type (1:1, 1:M or M:N) and write all the relationships in English sentences (refer to Figure 3.17 in the textbook for an example) in the template file you downloaded. Click HERE for example problem (connectivities and cardinalities are at the bottom of this file). [0-4 pts] 6. Identify the cardinalities associated with this entire schema. NOTE: You will not be able to decipher an upper limit because no specific business rules were provided to you so you will simply need to use (1.1) or (1,N) as cardinalities throughout. Put your answers in the template file downloaded above. [0-4 pts) 7. Draw an ERD using proper Chen notation that corresponds to the relational schema. Include all connectivities, cardinalities and attributes as shown in the dataset given. There are NO optional relationships in this ERD. Save a copy of your diagram and import that diagram into your template file downloaded above. Remember, cardinalities go next to the entity they reference. [0-7 pts] 8. Draw an ERD using proper Crow's Foot notation, including all connectivities, cardinalities and attributes. Again, there are to be NO optional relationships in this ERD. Save a copy of your diagram and import that diagram into your template file from above. [0-7 pts] WITATTOSUDIT