SYM-408-RS-T1.DesignaRelationalSchema.docx
SYM-408 Design a Relational Schema
Canyon Sales Business Requirements
The sales department at Canyon Retail Company has decided to create a database that contains the details of its sales process. After gathering all the requirements, conducting interviews, and studying Canyon documentation, the database team identified the following requirements for the future database. Data will be captured about the following:
1. For each product being sold: product ID, product name, and price
2. For each category of product: Category ID and Category name
3. For each vendor: Vendor ID and Vendor Name
4. For each customer: Customer ID, name, and Zip code
5. For each store: Store ID, and Zip Code
6. For each region: Region ID and Region Name
7. For each Sales Transaction: Transaction ID and Date of Transaction
8. Each product is supplied by exactly one Vendor. Each Vendor supplies one or more products.
9. Each Product belongs to exactly one category. Each category contains one or more products.
10. Each store is located in exactly one region. Each region contains one or more stores.
11. Each sales transaction occurs in one store. Each store has one or more transactions occurring at it.
12. Each sales transaction involves exactly one customer. Each customer can be involved in one or more sales transactions.
13. Each product is sold via one or more sales transactions. Each sales transaction includes one or more products.
14. For each instance of a product being sold via sales transaction, the quantity of sold products is recorded.
Canyon Sales transactional data:
Insert INTO vendor Values ('PG', 'Pacific Gear')
Insert INTO vendor Values ('MK', 'Mountain King')
Insert INTO category VALUES ('CP', 'Camping')
Insert INTO category VALUES ('FW', 'Footwear')
Insert INTO product VALUES ('1X1', 'Zzz Bag', 100, 'PG','CP')
Insert INTO product VALUES ('2X2', 'Easy Boot', 70, 'MK','FW')
Insert INTO product VALUES ('3X3', 'Cosy Sock', 15, 'MK','FW')
Insert INTO product VALUES ('4X4', 'Dura Boot', 90, 'PG','FW')
Insert INTO product VALUES ('5X5', 'Tiny Tent', 150, 'MK','CP')
Insert INTO product VALUES ('6X6', 'Biggy Tent', 250, 'MK','CP')
Insert INTO region Values ('C', 'Chicagoland')
Insert INTO region Values ('T', 'Tristate')
Insert INTO store VALUES ('S1','60600','C')
Insert INTO store VALUES ('S2','60605','C')
Insert INTO store VALUES ('S3','35400','T')
Insert INTO customer VALUES ('1-2-333', 'Tina', '60137')
Insert INTO customer VALUES ('2-3-444', 'Tony', '60611')
Insert INTO customer VALUES ('3-4-555', 'Pam', '35401')
Insert INTO salestransaction VALUES ('T111', '1-2-333','S1', '01/Jan/2013')
Insert INTO salestransaction VALUES ('T222', '2-3-444','S2', '01/Jan/2013')
Insert INTO salestransaction VALUES ('T333', '1-2-333','S3', '02/Jan/2013')
Insert INTO salestransaction VALUES ('T444', '3-4-555','S3', '02/Jan/2013')
Insert INTO salestransaction VALUES ('T555', '2-3-444','S3', '02/Jan/2013')
Insert Into soldvia values ('1×1','T111',1)
Insert Into soldvia values ('2×2','T222',1)
Insert Into soldvia values ('3×3','T333',5)
Insert Into soldvia values ('1×1','T333',1)
Insert Into soldvia values ('4×4','T444',1)
Insert Into soldvia values ('2×2','T444',2)
Insert Into soldvia values ('4×4','T555',4)
Insert Into soldvia values ('5×5','T555',2)
Insert Into soldvia values ('6×6','T555',1)
© 2023. Grand Canyon University. All Rights Reserved.
2
