Reference no: EM133117899

SYM-408 Design a Relational Schema - Grand Canyon University

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 spotted the following requirements for the future database.

Data will capture 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.

The purpose of this assignment is to map the relational schema into a dimensional star schema, along with also to learn the differences between online transaction processing (OLTP) and online analytical processing (OLAP).

Part 1: Design a relational schema and include the following:

Based on the attached business requirements, design an entity relationship diagram for the Canyon Sales Department using a tool of your choice to design the ERD. Make sure to deliver a screenshot that captures the full diagram.

Map the ERD into a relational schema using SSMS in LopesCloud.

Insert the data provided by the instructor.

Attachment:- Design a Relational Schema.rar