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

Question

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:

Given the following database instance, answer questions 1.1 through 1.3 Employee Plan emp code em EC14 EC15 EC16 EC17 EC20 ob code JC2 JC4 JC1 JC3 JC2 name plan code Rudell McDade Ruellardo Smith Smith lan description Term Life Stock Purchase Long-term disabilit Dental Extra Vacation 4 Job Benefit ob code job description JC1 JC2 JC4 JC3 Clerical Technical DBA Manager lan code emp code EC15 EC16 EC17 EC17 EC17 EC20 Extra Benefit 4 ob code JC3 lan code

Explanation / Answer

Please Note: I have answered the first question and all of its sub-parts, as per Chegg Answering Guidelines. Please Re-Post for the answers to the other Questions.

Q1.

Answers)

1.1 (2 Points) Do all tables exhibit entity integrity?

Answer)

No.

Entity integrity of a table is its ability to maintain primary keys. The primary key should be unique to a table and also it should not match the primary key of any other row in the table.

Here,

emp_code - primary key for Employee table

job_code - primary key for the Job table

plan_code - primary key for the Plan table

emp_code, plan_code - composite primary key for the Benefit table

job_code, plan_code - composite primary key for the Extra_Benefit table

Here in Plan table, entity integrity is not maintained and void as plan_code primary key values repeat.

1.2 (3 Points) For each table in the database, identify the 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)

Answer)

Employee table - job_code is a foreign key which references the job_code column of Job table

Benefit table - emp_code and plan_code are foreign keys. emp_code references Employee - emp_code. plan_code references Plan - plan_code.

Extra_Benefit table - job_code and plan_code are foreign keys. plan_code references Plan - plan_code. job_code references Job - job_code.

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

Answer)

No.

Referential integrity means primary key and foreign key relationships.

Employee table has a primary key and foreign key relationships.

Benefit table has a primary key and foreign key relationships.

Extra_Benefit table has a primary key and foreign key relationships.

Plan and Job table do not exhibit referential integrity as there is no primary key and foreign key relationship.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.