Business Data Management and Analytics: myTShirt Database Project

This assignment focuses on designing a database for a custom t-shirt business, myTShirt, as part of a Business Data Management and Analytics course. The student created an Entity-Relationship Diagram (ERD) and a relational model to meet the business requirements. The solution includes SQL queries for creating tables (customer, product, sales, and payment), inserting data, and performing selection, group by, join, and nested queries. The assignment also addresses business rules, such as constraints on payment dates and sales values. Additionally, the student provides example queries and showcases data visualization using Tableau to analyze average amounts paid for different products, demonstrating a comprehensive understanding of database design, SQL, and data analysis principles. The assignment adheres to third normal form principles and addresses potential database anomalies.

Running head: BUSINESS DATA MANAGEMENT AND ANALYTICS
Business Data Management and Analytics
Name of the Student
Name of the University
Authors note

1BUSINESS DATA MANAGEMENT AND ANALYTICS
ERD for myTShirt

2BUSINESS DATA MANAGEMENT AND ANALYTICS

3BUSINESS DATA MANAGEMENT AND ANALYTICS
Relational Model

4BUSINESS DATA MANAGEMENT AND ANALYTICS
Business rule
A t-shirt product can have only single type of extra material.
The Sleeve of t-shirt will be half as a default value in the database.
Payment date must be greater than the sales date or same as any customer would not
pay before buying a product.
The customer ID in sales table must be previously present in the customer table.
The total sales value in the sales table cannot be zero for any order or sales in order to
maintain consistency.
SQL queries
Sql queries for creation of the database and data insertion
CREATE TABLE `myTshirt`.`customertable` ( `customerid` INT NOT NULL AUTO
_INCREMENT , `customername` VARCHAR(50) NOT NULL , `contact` INT(12) NOT NU
LL , `address` VARCHAR(75) NOT NULL , `city` VARCHAR(40) NOT NULL , `state` VA
RCHAR(20) NOT NULL , PRIMARY KEY (`customerid`)) ENGINE = InnoDB;
CREATE TABLE `mytshirt`.`producttable` ( `productid` INT NOT NULL AUTO_IN
CREMENT , `product_type` VARCHAR(25) NOT NULL , `shirt_material` VARCHAR(20)
NOT NULL , `sleeve` VARCHAR(20) NOT NULL , `shirtcolour` VARCHAR(35) NOT NU
LL , `extras_text` VARCHAR(50) NOT NULL , `extra_material` VARCHAR(35) NOT NU
LL , `Extra_Font` VARCHAR(35) NOT NULL , `Extra_colour` VARCHAR(35) NOT NUL
L , `size` VARCHAR(10) NOT NULL , PRIMARY KEY (`productid`)) ENGINE = InnoDB;

5BUSINESS DATA MANAGEMENT AND ANALYTICS
CREATE TABLE `mytshirt`.`salestable` ( `salesid` INT NOT NULL AUTO_INCRE
MENT , `salesdate` DATE NOT NULL , `customerid` INT NOT NULL , `price` DOUBLE
NOT NULL , `productid` INT NOT NULL , PRIMARY KEY (`salesid`)) ENGINE = InnoD
B;
CREATE TABLE `mytshirt`.`paymentable` ( `paymentid` INT NOT NULL AUTO_I
NCREMENT , `paymentdate` DATE NOT NULL , `customerid` INT NOT NULL , `amount
paid` DOUBLE NOT NULL , `saleid` INT NOT NULL , PRIMARY KEY (`paymentid`)) E
NGINE = InnoDB;
Insert query in customer table;
INSERT INTO `customertable` (`customerid`, `customername`, `contact`, `address`,
`city`, `state`) VALUES ('1', 'JENNIFER', '987456123', '13 th street', 'Sydney', 'NSW'), ('2',
'MATTHEW', '741852451', 'Reven street', 'Adelaide', 'VIC'), ('3', 'VIVIEN', '852741216',
'Middletown', 'Gabba', 'OH'), ('4', 'Kiley', '215874122', '73 State Road 434 E\r\n', 'Brighton',
'MI'), ('5', 'Mattie', '440808425', '56 E Morehead St\r\n', 'Laredo', 'TX')
INSERT INTO `producttable` (`productid`, `product_type`, `shirt_material`, `sleeve`,
`shirtcolour`, `extras_text`, `extra_material`, `Extra_Font`, `Extra_colour`, `size`) VALUES
('1', 'standard', 'Cotton', 'short', 'Black', 'Get Busy', 'Ink', 'Time New Roman', 'Orange', 'M'),
('2', 'standard', 'Leather', 'short', 'White', 'What Ever It takes', 'Ink', 'Calibri', 'Blue', 'XL'), ('3',
'standard', 'Cotton', 'Full', 'Blue', 'Avenge the Fallen', 'Ink', 'Calibri', 'Green', 'L'), ('4',

6BUSINESS DATA MANAGEMENT AND ANALYTICS
'standard', 'Cotton', 'short', 'Green', 'Get it done', 'Ink', 'Gothic', 'Blue', 'XL'), ('5', 'standard',
'Leather', 'short', 'orange', 'Be yourself', 'Ink', 'Gothic', 'Grey', 'L')
INSERT INTO `salestable` (`salesid`, `salesdate`, `customerid`, `productid`)
VALUES ('1', '2019-09-10', '1', '1'), ('2', '2019-09-03', '3', '2'), ('3', '2019-08-07', '1', '4'), ('4',
'2019-09-15', '4', '4'), ('5', '2019-09-27', '3', '5')
INSERT INTO `paymentable` (`paymentid`, `paymentdate`, `customerid`,
`amountpaid`, `saleid`) VALUES ('1', '2019-09-12', '1', '2500', '1'), ('2', '2019-09-08', '3',
'1500', '2'), ('3', '2019-08-13', '1', '1250', '3'), ('4', '2019-09-18', '4', '3500', '4'), ('5', '2019-10-
30', '3', '2480', '5')
Selection Condition Query
SELECT * FROM CUTOMERTABLE WHERE STATE= “NSW”
GROUP BY query

7BUSINESS DATA MANAGEMENT AND ANALYTICS
SELECT COUNT(customerid),SUM(amountpaid) FROM paymentable GROUP BY
customerid
JOIN query
SELECT DISTINCT(salestable.customerid),customertable.customername FROM
salestable INNER JOIN customertable ON salestable.customerid = customertable.customerid

8BUSINESS DATA MANAGEMENT AND ANALYTICS
NESTED query
SELECT * FROM customertable WHERE customerid IN (SELECT customerid
FROM paymentable WHERE paymentable.amountpaid > 1500)
Visualization
In tableau
Average amount paid for different products

9BUSINESS DATA MANAGEMENT AND ANALYTICS