Week 2 Assignment
Part 1: Keys and Data Integrity
Below is a company database with three tables. Please try to understand all the tables and columns.
1.1 For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None.
1.2 Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer.
1.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
Part 2 SQL
2.1 Design and run SQL statements to create the three tables in Part 1. Your SQL statements should use appropriate data types for all columns. You also need to specify primary key and foreign key constraints based on your answer to Q1 in Part 1. Paste the SQL code in your submission.
2.2 Type in 3 rows of data for EMPLOYEE and STORE tables and 2 rows data for REGION table. Please use exactly the same data as shown in the picture in Part 1. Paste the screenshots of those tables with data in your submission.
2.3 When typing in data in 2.2, you may find you have to start with the REGION table, and then the STORE table, and finally the EMPLYEE table. Can you explain why you have to follow this order?
2.4 (Graduate students only) Suppose you have one manager for each store and you want to have data about store managers. Then you can add a column of Emp_Code to the store table (as shown in the following picture). How do you revise your SQL code in 2.1 to create the table? Please note Emp_Code in the STORE table should be a foreign key because Emp_Code is the primary key of EMPLOYEE table. Paste the SQL code in your submission.
(Hint: In order to satisfy the rule of referential integrity, we have to create tables without FKs first. In this case, both EMPLOYEE and STORE have FKs. Which one do you create first? To solve this problem, you can first create the tables without FK constraints. Then use “Alter Table” command to add FK constraints. There is an example of “SQL FOREIGN KEY on ALTER TABLE” here https://www.w3schools.com/sql/sql_foreignkey.asp )