SYM-408 WK1

Design a Relational Schema

The purpose of this assignment is to create a relational schema using an entity relationship diagram (ERD) and then map the relational schema into a dimensional star schema. The design will be created using industry standards and practices.

For this assignment, refer to the “SYM-408 Design a Relational Schema” (attached) document to complete the assignment.

Design a relational schema and include the following:

1. 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.

2. Map the ERD into a relational schema using SQL Server Management Studio (SSMS). Capture the Create statements for the creation of the schema.

3. Insert the data provided by the instructor. Display the newly created tables. Capture the insert statements, the select statements and a display of each table with the required data inserted.

Submit the following in a single document compatible with the digital classroom (e.g., Microsoft Word). Include a one-line description above each screenshot.

· Screenshot of the full entity relationship diagram (ERD)

· Screenshot(s) showing the successful table creation statements

· Screenshot(s) showing the insert statements, select statements, and a display of each table with the required data inserted

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

Rubric Criteria

Expand All Rubric CriteriaExpand All

Entity-Relationship Diagram (ERD)

20 points

ERD Mapping

20 points

Data Insertion

10 points

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

image1

Assessment Description

Designing a dimensional (star) model involves considering which dimensions to use with the Fact table representing the chosen subject. The goal of this part is to design a dimensional (star) model for a data warehouse that enables analysis of sales by Product, Customer, Store, and Date.

For this assignment, refer to the “SYM-408 Dimensional Diagram” (attached below) document to complete the assignment.

Design a dimensional (star) model and include the following:

1. Design the Dimensions and the Fact.

2. Take a screenshot showing your Dimensions and Fact.

3. Load the Dimensions and the Fact with relevant data from the operational database you designed in Step 1 above, making sure to capture all of the queries.

4. Take a screenshot(s) to show that all relevant data has been loaded.

5. Write a query in SQL to get the quantities of the sold products on Wednesday in the category Footwear provided by vendor Pacific Gear within the tri-state region between the 1st and the 2nd quarter of 2013.

6. Take a screenshot showing the query and the results.

7. In a succinct paragraph, explain how the ability to obtain information like that discovered in the above query could guide critical business decisions.

Submit the following in a single document compatible with the digital classroom (e.g., Microsoft Word). Include a one-line description above each screenshot.

· Screenshot showing Dimensions and Fact

· Screenshot(s) showing all relevant data loaded

· Screenshot(s) showing query and results of query

· Paragraph explaining how queries like this could guide critical business decisions

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

Rubric Criteria

Expand All Rubric CriteriaExpand All

Dimensions and Fact

17.5 points

Loads Dimensions and Fact with Relevant Data

17.5 points

Query in SQL

14 points

Explains How the SQL Query Can Guide Critical Business Decisions

14 points

Mechanics of Writing

3.5 points

Solid Academic Writing and Documentation

SYM-408 Dimensional Diagram

image1