Homework - Database Integrity

Homework Use the database shown in Figure P3.1 to answer Problems 1-5 1. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None. Employee Table Primary: Employee_Code; Foreign Key: Store_Code Store Table: Primary: Store_Code; Foreign Key:Employee_Code Region Table: Primary: Region_Code; Foreign Key: None 2. Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer. Yes, the table does exhibit integrity. All primaries are unique values and they do not contain any null values. Null values would be problematic for this model. 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. Yes, both the employee and the store table exhibit referential integrity. The Store code is a valid foreign key that correlates to the store code value in the Store Table. Likewise, the Employee code is a valid foreign key in the Store Table that correlates the employee code that is already in the Employee table. Also the region code in the Store Table correlates to the existing region code in the Region Table. 4. Describe the type(s) of relationship(s) between STORE and REGION. The relationship of Store Region is M:1; There are many stores that are in one region only. (Access Junction, Database Corner, Attribute Alley, all only in region 2. 5. Describe the type(s) of relationship(s) between EMPLOYEE and STORE. ( Hint: Each store employs many employees, one of whom manages the store.) The relationship between Employee Store is M:1; There is a range from 3-5 employees to each store as shown both the Employee Table. Another relationship amongst the Employee  Store is 1:1; where only one manager is in each store. Use the database shown in Figure P3.2 to answer Problems 6-8 Figure P3.2 The CH03 CH03_BENECO _BENECO Database T Tables ables 6. 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. Employee: Primary: Employee_Code Foreign Key: Job_Code Benefit: Primary: Plan_Code Foreign Key: Employee_Code Job: Primary: Job_Code Foreign Key: N/A Plan: Primary: Plan_Code Foreign Key: N/A 7. Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer. Yes, the tables do exhibit integrity. Each Employee, Job, and Plan code are all unique and none of them contain nulls. 8. 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. Yes, both the Employee Table and Benefit table exhibit referential integrity. The foreign job_code key in the Employee table has a correlation to a job_code in the Job Table. Similarly, the employee_code in the Benefit table has a correlation to the employee_code in the Employee table. Use the database shown in Figure P3.3 to answer Problems 9-11 Figure P3.2 The CH03 CH03_TRANSCO _TRANSCO Database T Tables ables 9. 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. Truck: Primary: Truck_Num Foreign: Base_code; Type_Code Base: Primary: Base_Code Foreign: None Type: Primary: Type_Code Foreign: None 10. Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer. Yes, each table exhibits entity integrity. Truck_num, Base_Code, and Type_Code are all unique and none of them produce any nulls. 11. 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. Yes, the Truck Table exhibits referential integrity. The Base_code values in the Truck table reference the existing Base_code values in the Base Table. Additionally, the base_code for truck number 1004 is null.