Question

Given the following relational database schema (primary keys are bold and underlined). Answer questions 2.1 to 2.4 Orders(orderld, customerld, dateOrdered, dateRequired, status) Customer(customerld, customerLastName, customerStreet, customerCity, customerState, customer Lip OrderDetails(orderld.productld, quantity, lineNumber, amount) Products(productld, name, description, quantity, unitPrice) Account(accountNumber, customerld, 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 4 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 id and last names of all customers who live on St. Anny St. in Blaine. b) Find the Customer last names and street address of all customers who have an order. c) Find the names, street address, and cities of residence for all customers who have ordered product number 310794. 2.4 (2 point) For each of the following relational algebra expressions, explain the output of the expression in words: a) IIcredit card(OmailingZip-55444(account) b) Icustomer ID,accountNumber customerCity-'chicago (customer Daccpunt)

Answer 1

2.2)

Relation 1: Orders

OrderId	CustomerId	dateOrdered	dateRequired	status
123456	234567	10/05/2014	14/05/2014	completed
123457	234567	12/04/2019	20/06/2019	pending
123458	234568	10/08/2018	16/08/2018	completed
123459	234569	17/04/2019	20/04/2019	completed

Relation 2: Customer

customerId	customerLastName	customerStreet	customerCity	customerState	customerZip
234567	Joseph	St. anny	Blaine	Minnesota	55444
234568	Maria	W 18	chicago	Illinois	45544
234569	Blood	Wall street	New york	New york	34544
234570	Varte	300th ave	phoenix	Arizona	24544

Relation 3: OrderDetails

orderId	productId	quantity	lineNumber	amount
123456	310794	50	2	100000
123457	310795	20	1	50000
123458	310796	10	3	8000
123459	310797	20	4	500000

Relation 4: Products

productId	name	description	qunatity	unitPrice
310794	chairs	for educational institute	50	2000
310795	tables	for office	20	2500
310796	fans	for hotel	10	800
310797	Airconditioner	for adminstration	20	25000

Relation 5:

accountNumber	customerId	dateOpened	creditCard	mailingStreet	mailingCity	mailingState	mailingZip
534567	234567	12/10/2010	2056458932	St. anny	Blaine	Minnesota	55444
534568	234568	10/04/2014	1045862378	W 18	chicago	Illinois	45544
534569	234569	11/01/2013	2598374691	Wall street	New york	New york	34544
534570	234570	12/07/2015	1673089443	300th ave	phoenix	Arizona	24544

relatjon or) Customen Id Ordurk Custo mer Ordeunid Ordu Prodect 1d CCU "Gaine" (Custome)) Cb) TT チー·

2.4)

a) this query will result credit card details of the customer who have a mailingZip code as 55444 in the account table

b) this query will result the customerId and the account number of the customer where the customer city is chicago.

I hope this will help you so please give positive ratings :)))