Question

When customers place an order, the order details will need to be stored into the database, as well as details of the customer. Naturally this will include details such as name, address and phone number. For each order they can order multiple t-shirts. For each t-shirt, they will be allowed to choose:

- The style of the t-shirt

- The sleeve configuration

- The colour of the t-shirt

- The size of the t-shirt

- The text/image/logo to be printed

- The material used for t-shjirt

- The material used for the text/image/logo

It is extremely important to store the data in a structured format, because it will be used to send directly to the automated T-Shirt create equipment to be instantly created and shipped. Customers will be required to pay before the products are sent so details will need to be kept of the payment. Your friends are planning to accept payment by credit card, direct bank deposit and paypal. For credit cards they need to store the credit card number and expiry date, for direct deposit they need a field to tick off that the payment has appeared in their bank account and for paypal they again need a field to tick off plus the paypal user id of the payer.

Sample T-Shirt	T-Shirt Attrribute	Data
	Type: Shirt_Material: Sleeve: Shirt_Colour: Extras: Extras_Material: Extras_Font Extras_colour Size:	Standard Cotton Short White Text “SAMPLE T-SHIRT” Ink 48 Time New Roman Orange XL
	Type: Shirt_Material: Sleeve: Shirt_Colour: Extras: Extras_Material: Extras_Font Extras_colour Size:	Standard Leather Short Black - - - - M

REQUIREMENTS – PASS/CREDIT – DATA MDOEL

Create an ER diagram, relational model and any business rules or assumptions made.

REQUIREMENTS – HIGHER LEVEL – SQL IMPLEMENTATION

For additional marks, provide the SQL commands to create the tables and insert a few rows into each table. Also provide several business question and SQL queries to test out the tables, include at least a:

• SELECTION condition query

• GROUP BY query

• JOIN query

• NESTED query

Also create a least one visualisation of the data using Orange/Tableau/Excel.

Answer 1

REQUIREMENTS – PASS/CREDIT – DATA MDOEL

Create an ER diagram, relational model and any business rules or assumptions made.

Let’s identify the Entities in the system:

1. Customer
2. Order
3. T-Shirt
4. Payment

ER Diagram for the system given:

Relation Model for the above System:

NOTE: primary key is denoted as underlined, foreign key is having (fk) next to the column name.

1. Customer(id, name, state, city, zipcode, phonenumber)
2. OrderRecords(order_id, order_date, customer_id(fk), payment_id(fk),status)
3. Tshirt(shirt_id, type,size, shirt_material, extras, sleeves, extras_font, extras_color, extras_material)

4. Payment(payment_id, pay_type, amount, cardExpiry,cardNumber, paypalID, isAmountRecieved)

-------------------------------------------------------------------------

REQUIREMENTS – HIGHER LEVEL – SQL IMPLEMENTATION

---------------------------------------------------------------------------

Create table queries

Create customer table:

CREATE TABLE customer(Id varchar(20) PRIMARY KEY, name varchar, state varchar(20), city varchar(20), zipcode varchar(20), phoneNumber varchar(20) );

Create tshirts table:

CREATE TABLE tshirt(shirt_id int PRIMARY KEY, type varchar(20), size char(8) check (size in ('S','M', 'L', 'X', 'XL' ,'FREE')) ,shirt_color varchar(20) , shirt_material varchar(20), extras char(8) check (extras in ('Logo','Image','Text')), sleeves varchar(20), extras_font varchar(20),extras_color varchar(20), extras_material varchar(20) );

Create payment table:

CREATE TABLE payment(payment_id int PRIMARY KEY, pay_type varchar(20), amount double, cardExpiry varchar(7), cardNumber varchar(20), paypalId varchar(20), isamountrecieved char(4) check (isamountrecieved in ('YES','NO')) );

Create orderDetails table:

CREATE TABLE orderRecords (order_id int primary key,order_date DATETIME, CUSTOMER_ID varchar(20) references customer(Id),payment_id int references payment(payment_id), status varchar(20));

Inserting rows in each table

Insert into customer table

Query:

INSERT INTO customer (Id, name, state, city, zipcode, phoneNumber)

VALUES ('C003','Albert','Rjht','Urinda','1345','173-654987');

Output: After adding similar 3 records, below will be the records available n the table

Insert into tshirt table

Query:

INSERT INTO tshirt(shirt_id, type , size ,shirt_color, shirt_material, extras , sleeves , extras_font ,extras_color , extras_material )

Values (1, 'standard', 'M', 'White', 'cotton', 'Text', 'half', 'airial', 'Red', 'Rubbur');

Output: Running the above query will add the data in the table as below:

Insert into payment table:

Query: The below query will add a payment of direct debit type which will not require data for cardNumber, cardExpiry and PaypalID

INSERT INTO payment(payment_id , pay_type , amount, cardExpiry, cardNumber, paypalId , isamountrecieved )

Values (1, 'directdeposit', 1200, 'NA','NA','NA' , 'YES')

Output: Running the above query will add the data in the table as below:

Insert into orderRecords table:

Query:

INSERT INTO orderRecords (order_id ,order_date, CUSTOMER_ID ,payment_id, status)

Values (1, '2019-09-12' , 'C001', 1, 'Pending')

Output:

Business SQL Queries Examples

• Select : Select the customers where the customer id is C001

Query:   select * from customer where Id = 'C001';

Output: The result will show the customer record with id C001

• Group By: Retrieve the count of the customers by the state in customer table

Query: Select state, count(*) as customerCount from customer group by state;

Output: The result will count the number of customers in different states and display the count under ‘customerCount’ column

• JOIN query: Find the customer name along with order id and order date placed using join on orderRecords and customer tables

Query: select customer.name, orderRecords.order_id , orderRecords.order_date

FROM orderRecords INNER JOIN customer ON orderRecords.CUSTOMER_ID=Id;

Output: The output will join the two tables and display the below result:

• NESTED query: Use nested query to find the customer record where the customer name is ‘Adam’

Query: select * from customer where id in (select id from customer where name = 'Adam')

Output: