Benchmark - Build Analysis Solution Using PowerBI

Date: 02/04/2023

SYM-408 Design a Relational Schema

Schema:

-- create a table

CREATE TABLE category (

categoryID TEXT PRIMARY KEY,

categoryName TEXT NOT NULL

);

CREATE TABLE vendor (

vendorID TEXT PRIMARY KEY,

vendorName TEXT NOT NULL

);

CREATE TABLE product(

productID TEXT PRIMARY KEY,

productName TEXT NOT NULL,

price INTEGER,

vendorID TEXT references vendor(vendorID),

categoryID TEXT references category(categoryID)

);

CREATE TABLE region (

regionID TEXT PRIMARY KEY,

regionName TEXT NOT NULL

);

CREATE TABLE store (

storeID TEXT PRIMARY KEY,

storeName TEXT NOT NULL,

regionID TEXT references region(regionID)

);

CREATE TABLE customer (

customerID TEXT PRIMARY KEY,

customerName TEXT NOT NULL,

zipCode TEXT NOT NULL

);

CREATE TABLE salestransaction (

transID TEXT PRIMARY KEY,

customerID TEXT references customer(customerID),

storeID TEXT references store(storeID),

transDate date NOT NULL

);

CREATE TABLE soldvia (

productID TEXT references product(productID),

sales TEXT references salestransaction(transID),

quantity INTEGER NOT NULL

);

Queries:

1. For each product being sold: product ID, product name, and price

Query:

select p.productID, p.productName, p.price from product p join soldvia s on s.productID = p.productID;

Result:

1. For each category of product: Category ID and Category name

Query:

select categoryID, categoryName from category;

Result:

2. For each vendor: Vendor ID and Vendor Name

Query:

select vendorID, vendorName from vendor;

Result:

3. For each customer: Customer ID, name, and Zip code

Query:

select customerID, customerName, zipCode from customer;

Result:

4. For each store: Store ID, and Zip Code

Query:

select storeID, storeName from store;

Result:

5. For each region: Region ID and Region Name

Query:

select regionID, regionName from region;

Result:

6. For each Sales Transaction: Transaction ID and Date of Transaction

Query:

select transID, transDate from salestransaction;

Result: