The following is a database design about receipts. A receipt represents a sale,

Question

The following is a database design about receipts. A receipt represents a sale, and belongs to a store. A receipt has many receipt_item's, where each item is for a product, and has a quantity. Each product belongs to exactly one category. The relational schema diagram is shown as follows: Keep in mind that in the diagram the arrows represent foreign keys; the arrows are directional - they point from FK to PK. write SQL statements to create table for receipt_item. write SQL statements to create table for receipt. write SQL statements to create table for product. write SQL statements to create table for store. write SQL statements to create table for category. write a SQL query to display the id and name of all the products that belong to the category with id = 1. write a SQL query to display the total number of different products belonging to category 1 (categoryId =1), and their average price. write a SQL query to display the id and name of each category, with the number of products that belong to the category. write a SQL query to display the id and name of each category, with the number of products that belong to the category, and make sure ALL categories appear, even those with no products.

Explanation / Answer

Multiple Questions : ANswering 1st 5.

CREATE TABLE Store
(
Id int NOT NULL,
Name varchar(255) NOT NULL,
PRIMARY KEY (Id)
)

CREATE TABLE Category
(
Id int NOT NULL,
Name varchar(255) NOT NULL,
PRIMARY KEY (Id)
)

CREATE TABLE Receipt
(
Id int NOT NULL,
IssuedOn DATE NOT NULL,
StoreId int NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (StoreId) REFERENCES Store(Id)
)

CREATE TABLE Product
(
Id int NOT NULL,
Name varchar(255) NOT NULL,
CategoryId int NOT NULL,
Price FLOAT(10,2) NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (CategoryId) REFERENCES Category(Id)
)  

CREATE TABLE Receipt_Item
(
ReceiptId int NOT NULL,
ProductId int NOT NULL,
Quantity int NOT NULL,
PRIMARY KEY (ReceiptId,ProductId),
FOREIGN KEY (ReceiptId) REFERENCES Receipt(Id),
FOREIGN KEY (ProductId) REFERENCES Product(Id)
)  

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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