Question

1 Approved Answer

Posted on Sep 10, 2024

The following relational schema (primary keys are underlined) are used by the security company, West Park, described in Assignment 1, with some minor modifications. Stores(sid,

The following relational schema (primary keys are underlined) are used by the security company, West Park, described in Assignment 1, with some minor modifications.

Stores(sid, name, address, phone) Products(barcode, name, description, price) Members(mid, name, contactInfo, openAtStore, openTime, lastRenewTime) Transactions(sid, pid, transactionTime, customer, employee) TransactionContent(sid, pid, barcode) ReturnTransactions(returnStore, returnPid, sid, pid, barcode, returnTime, returnComment, employee)

Express each of the following queries in SQL against the relational schema defined above. Each query should be expressed using a single SQL statement.

1. For each customer, list his/her name, contact information, and the total number of purchase transactions done by him/her during January 1st, 2019 and December 31, 2019 (inclusive). If the customer didn't make any purchase in that period, still show his/her name and contact information, and list 0 as the total number purchase transactions.

2.For each purchase transaction, list the name of the customer who made the purchase, the transaction time, the number of product items purchased in this transaction and the total amount involved in this purchase. The total amount is calculated as the sum of the price of each product purchased in this transaction.

3. List the name and contact information of each customer who has done more than 10 purchase transactions. Order the results according to the number of purchase transactions done by the customer in descending order.

The Assignment Information The following relational schema (primary keys are underlined) are used by the security company, West Park, described in Assignment 1, with some minor modifications. Stores(sid, name, address, phone) Products(barcode, name, description, price) Members(mid, name, contact Info, openAtStore, open Time, lastRenew Time) Transactions(sid, pid, transaction Time, customer, employee) TransactionContent(sid, pid, barcode) Return Transactions(returnStore, returnPid, sid, pid, barcode, return Time, returnComment, employee) The Assignment Information The following relational schema (primary keys are underlined) are used by the security company, West Park, described in Assignment 1, with some minor modifications. Stores(sid, name, address, phone) Products(barcode, name, description, price) Members(mid, name, contactinfo, openAtStore, open Time, lastRenew Time) Transactions(sid, pid, transaction Time, customer, employee) TransactionContent(sid, pid, barcode) Return Transactions(returnStore, returnPid, sid, pid, barcode, return Time, returnComment, employee) This assignment is based on the above relational schema. Many details, including foreign keys, data types, etc, are captured in the SQL definition of these tables. Express each of the following queries in SQL against the relational schema defined above. Each query should be expressed using a single SQL statement. (4 marks for each SQL query.) 1. For each customer, list his/her name, contact information, and the total number of purchase transactions done by him/her during January 1st, 2019 and December 31, 2019 (inclusive). If the customer didn't make any purchase in that period, still show his/her name and contact information, and list 0 as the total number purchase transactions. 2. For each purchase transaction, list the name of the customer who made the purchase, the transaction time, the number of product items purchased in this transaction and the total amount involved in this purchase. The total amount is calculated as the sum of the price of each product purchased in this transaction. 3. List the name and contact information of each customer who has done more than 10 purchase transactions. Order the results according to the number of purchase transactions done by the customer in descending order. 4. List the name, description and price of the most expensive product. 5. List the address and phone number of the store which has the most number of members. That is, the most number of customers chose this store to open his/her membership account. -- DROP TABLE Return Transactions; -- DROP TABLE TransactionContent; -- DROP TABLE Transactions; -- DROP TABLE Members: -- DROP TABLE Products; -- DROP TABLE Stores; CREATE TABLE Stores sid NUMBER (5) PRIMARY KEY, name VARCHAR2(100) NOT NULL, address VARCHAR2(200), phone CHAR(10) CREATE TABLE Products barcode CHAR(10) PRIMARY KEY, name VARCHAR2(30) NOT NULL, description VARCHAR2 (200), price NUMBER(8, 2) NOT NULL CREATE TABLE Members ( mid NUMBER (12) PRIMARY KEY, name VARCHAR2 (30) NOT NULL, contact Info VARCHAR2(100), openAt Store NUMBER (5) REFERENCES Stores, openTime DATE, lastRenewTime DATE ) CREATE TABLE Transactions sid NUMBER (5) REFERENCES Stores, pid NUMBER (10), transaction Time DATE, customer NUMBER (12) REFERENCES Members, employee CHAR(6), PRIMARY KEY (sid, pid) CREATE TABLE TransactionContent sid NUMBER(5), pid NUMBER(10), barcode CHAR(10) REFERENCES Products, PRIMARY KEY (sid, pid, barcode), FOREIGN KEY (sid, pid) REFERENCES Transactions CREATE TABLE ReturnTransactions returnStore NUMBER (5) REFERENCES Stores, returnPid NUMBER(10), sid NUMBER(5), pid NUMBER(10), barcode CHAR(10), returnTime DATE, returnComment VARCHAR2(200), employee CHAR(6), PRIMARY KEY (returnStore, returnpid), FOREIGN KEY (sid, pid, barcode) REFERENCES TransactionContent The Assignment Information The following relational schema (primary keys are underlined) are used by the security company, West Park, described in Assignment 1, with some minor modifications. Stores(sid, name, address, phone) Products(barcode, name, description, price) Members(mid, name, contact Info, openAtStore, open Time, lastRenew Time) Transactions(sid, pid, transaction Time, customer, employee) TransactionContent(sid, pid, barcode) Return Transactions(returnStore, returnPid, sid, pid, barcode, return Time, returnComment, employee) The Assignment Information The following relational schema (primary keys are underlined) are used by the security company, West Park, described in Assignment 1, with some minor modifications. Stores(sid, name, address, phone) Products(barcode, name, description, price) Members(mid, name, contactinfo, openAtStore, open Time, lastRenew Time) Transactions(sid, pid, transaction Time, customer, employee) TransactionContent(sid, pid, barcode) Return Transactions(returnStore, returnPid, sid, pid, barcode, return Time, returnComment, employee) This assignment is based on the above relational schema. Many details, including foreign keys, data types, etc, are captured in the SQL definition of these tables. Express each of the following queries in SQL against the relational schema defined above. Each query should be expressed using a single SQL statement. (4 marks for each SQL query.) 1. For each customer, list his/her name, contact information, and the total number of purchase transactions done by him/her during January 1st, 2019 and December 31, 2019 (inclusive). If the customer didn't make any purchase in that period, still show his/her name and contact information, and list 0 as the total number purchase transactions. 2. For each purchase transaction, list the name of the customer who made the purchase, the transaction time, the number of product items purchased in this transaction and the total amount involved in this purchase. The total amount is calculated as the sum of the price of each product purchased in this transaction. 3. List the name and contact information of each customer who has done more than 10 purchase transactions. Order the results according to the number of purchase transactions done by the customer in descending order. 4. List the name, description and price of the most expensive product. 5. List the address and phone number of the store which has the most number of members. That is, the most number of customers chose this store to open his/her membership account. -- DROP TABLE Return Transactions; -- DROP TABLE TransactionContent; -- DROP TABLE Transactions; -- DROP TABLE Members: -- DROP TABLE Products; -- DROP TABLE Stores; CREATE TABLE Stores sid NUMBER (5) PRIMARY KEY, name VARCHAR2(100) NOT NULL, address VARCHAR2(200), phone CHAR(10) CREATE TABLE Products barcode CHAR(10) PRIMARY KEY, name VARCHAR2(30) NOT NULL, description VARCHAR2 (200), price NUMBER(8, 2) NOT NULL CREATE TABLE Members ( mid NUMBER (12) PRIMARY KEY, name VARCHAR2 (30) NOT NULL, contact Info VARCHAR2(100), openAt Store NUMBER (5) REFERENCES Stores, openTime DATE, lastRenewTime DATE ) CREATE TABLE Transactions sid NUMBER (5) REFERENCES Stores, pid NUMBER (10), transaction Time DATE, customer NUMBER (12) REFERENCES Members, employee CHAR(6), PRIMARY KEY (sid, pid) CREATE TABLE TransactionContent sid NUMBER(5), pid NUMBER(10), barcode CHAR(10) REFERENCES Products, PRIMARY KEY (sid, pid, barcode), FOREIGN KEY (sid, pid) REFERENCES Transactions CREATE TABLE ReturnTransactions returnStore NUMBER (5) REFERENCES Stores, returnPid NUMBER(10), sid NUMBER(5), pid NUMBER(10), barcode CHAR(10), returnTime DATE, returnComment VARCHAR2(200), employee CHAR(6), PRIMARY KEY (returnStore, returnpid), FOREIGN KEY (sid, pid, barcode) REFERENCES TransactionContent