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

 

Transcribed Image Text:

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, customer Street, customerCity, customer State, customerZip) Order Details (orderId.productId, quantity, lineNumber, amount) Products (productId, name, description, quantity, unitPrice) Account (account Number, customerId, date Opened, credit Card, mailing Street, mailing City, 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) credit card(OmailingZip-55444(account) b) customer_ID,accountNumber(customerCity="chicago (customer Daccount) 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, customer Street, customerCity, customer State, customerZip) Order Details (orderId.productId, quantity, lineNumber, amount) Products (productId, name, description, quantity, unitPrice) Account (account Number, customerId, date Opened, credit Card, mailing Street, mailing City, 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) credit card(OmailingZip-55444(account) b) customer_ID,accountNumber(customerCity="chicago (customer Daccount)

Answer rating: 100% (QA)

24 a this query will result credit card detail... View the full answer