Given the following database instance, answer questions 1.1 through 1.3:

Employee Plan

emp_code	emp_lname	job_code
EC14	Rudell	JC2
EC15	McDade	JC4
EC16	Ruellardo	JC1
EC17	Smith	JC3
EC20	Smith	JC2

plan_code	plan_description
1	Term Life
2	Stock Purchase
3	Long-term disability
4	Dental
2	Extra Vacation

Job Benefit

job_code	job_description
JC1	Clerical
JC2	Technical
JC4	DBA
JC3	Manager

emp_code	plan_code
EC15	3
EC16	1
EC17	1
EC17	3
EC17	4
EC20	3

Extra_Benefit

job_code	plan_code
JC3	2

Assume that the following attributes are the primary keys for the tables:

emp_code is the primary key for Employee table

job_code is the primary key for the Job table

plan_code is the primary key for the Plan table

emp_code, plan_code is a composite primary key for the Benefit table

job_code, plan_code is the composite primary key for the Extra_Benefit table

1.1 (2 Points) Do all tables exhibit entity integrity? Answer yes or no and then explain your answer.

1.2 (3 Points) For each table in the database, identify foreign key(s) (if any). For each foreign key, state the referencing relation and the referenced relation (USE THE TABLE LAYOUT FROM POWER POINT SLIDE 19, IN LECTURE 2)

1.3 (2 Point) Do all tables exhibit referential integrity? Answer yes or no (for each table) and then explain your answer.

Question 2 (9 Points):

Given the following relational database schema (primary keys are bold and underlined). Answer questions 2.1 to 2.4:

Orders(orderId, customerId, dateOrdered, dateRequired, status)

Customer(customerId, customerLastName, customerStreet, customerCity, customerState, customerZip)

OrderDetails(orderId,productId, quantity, lineNumber, amount)

Products(productId, name, description, quantity, unitPrice )

Account(accountNumber, customerId, dateOpened, creditCard, mailingStreet, mailingCity, mailingState, mailingZip)

2.1 (2 Points) List all possible foreign keys. For each foreign key list both the referencing and referenced relations.

2.2 (2 Points) Devise a reasonable database instance by filling the tables with data of your choice. Make sure to have at least 3 tuples in each table. Make sure that all tables exhibits entity integrity and referential integrity constraints. Make sure to use good table layout in your answer.

2.3 (3 Points) For each of the following queries, write a relational algebra expression to answer the query:

a) Find the names of all customers who live in Hopkins, on St. Ann Street.

b) Find the names and cities of residence of all customers who have accounts.

c) Find the names, street address, and cities of residence for all customers who have ordered product number 320894.

2.4 (2 point) For each of the following relational algebra expressions, explain the output of the expression in words:

OmaiioCi- st. Paur'( account) bauumercustsgo ( customeraccount) OmaiioCi- st. Paur'( account) bauumercustsgo ( customeraccount)