xvB5rQZV

1# 'USE DATABASE Iconic' doesn't work
2USE Iconic;
3SET FOREIGN_KEY_CHECKS=0;
4
5DROP TABLE IF EXISTS `Category`;
6CREATE TABLE `Category` (
7    `categoryID` INT(11) NOT NULL AUTO_INCREMENT,
8    `categoryName` VARCHAR(100) DEFAULT NULL,
9    PRIMARY KEY (`categoryID`)
10)  DEFAULT CHARSET=UTF8;
11
12INSERT INTO Category (categoryID, categoryName)
13SELECT DISTINCT categoryID, categoryName FROM Product ORDER BY categoryID ASC;
14ALTER TABLE Product DROP categoryName;
15
16# Update season from old/current to 2015/2016 
17ALTER TABLE Product CHANGE status season VARCHAR(8);
18UPDATE Product SET season = REPLACE(season, 'current', '2016');
19UPDATE Product SET season = REPLACE(season, 'old', '2015');
20ALTER TABLE Product MODIFY season INT(4);
21
22UPDATE Orders SET paymentStatus = REPLACE(paymentStatus, 'confirmed', 'paid');
23UPDATE Orders SET paymentStatus = REPLACE(paymentStatus, 'error', 'unpaid');
24
25
26# Add productID column and set as primary key
27ALTER TABLE Product ADD productID int UNIQUE;
28ALTER TABLE Orders ADD productID int;
29ALTER TABLE Orders DROP FOREIGN KEY `orders_ibfk_2`;
30ALTER TABLE Product DROP PRIMARY KEY;
31ALTER TABLE Product MODIFY productID int PRIMARY KEY AUTO_INCREMENT;
32
33ALTER TABLE Product ADD CONSTRAINT `product_ibfk_1` FOREIGN KEY (`categoryID`) REFERENCES `Category` (`categoryID`);
34
35UPDATE Orders SET Orders.productID = (SELECT productID FROM Product
36	WHERE Product.productName = Orders.productName
37);
38
39DROP TABLE IF EXISTS `CustomerPhoneNo`;
40CREATE TABLE `CustomerPhoneNo` (
41    `customerID` INT(11) NOT NULL AUTO_INCREMENT,
42    `phoneNumber` INT(11),
43    KEY `phoneNumber` (`phoneNumber`),
44    KEY `customerID` (`customerID`),
45    FOREIGN KEY (`customerID`) REFERENCES `Customer` (`customerID`)
46)  DEFAULT CHARSET=UTF8;
47
48INSERT INTO CustomerPhoneNo(customerID, phoneNumber)
49SELECT *
50	FROM(
51		(SELECT customerID, TRIM(BOTH ',' FROM SUBSTRING(phoneNumbers,1,10)) AS number FROM Customer)
52	UNION
53		(SELECT customerID, TRIM(BOTH '' FROM SUBSTRING(phoneNumbers,12,11)) AS number FROM Customer)
54	UNION
55		(SELECT customerID, SUBSTRING(phoneNumbers,25,10) AS number FROM Customer)
56	) AS tbl
57WHERE LENGTH(number) = 10 ORDER BY customerID ASC;
58
59DROP TABLE IF EXISTS `OrderContents`;
60CREATE TABLE `OrderContents` (
61    `orderID` INT(11),
62    `productID` INT(11),
63    `productUnitPrice` INT(11),
64    `productQuantity` INT(11) NOT NULL DEFAULT 1,
65    PRIMARY KEY (`orderID`, `productID`),
66    FOREIGN KEY (`orderID`) REFERENCES `Orders` (`orderID`),
67    FOREIGN KEY (`productID`) REFERENCES `Product` (`productID`)
68)  DEFAULT CHARSET=UTF8;
69
70INSERT INTO OrderContents(orderID, productID, productUnitPrice, productQuantity)
71SELECT orderID, productID, productUnitPrice, productQuantity FROM Orders;
72ALTER TABLE Orders DROP productID, DROP productName, DROP productUnitPrice, DROP productQuantity;
73
74DROP TABLE IF EXISTS `Salesperson`;
75CREATE TABLE `Salesperson` (
76    `salespersonID` INT(11) NOT NULL AUTO_INCREMENT,
77    `firstName` VARCHAR(40) DEFAULT NULL,
78    `lastName` VARCHAR(40) DEFAULT NULL,
79    `phoneNumer` TEXT,
80    `emailAdress` VARCHAR(100) DEFAULT NULL,
81    `supervisorID` INT(11) NOT NULL,
82    PRIMARY KEY (`salespersonID`),
83    FOREIGN KEY (`supervisorID`) REFERENCES `Salesperson` (`salespersonID`)
84)  DEFAULT CHARSET=UTF8;
85
86
87
88DROP TABLE IF EXISTS `Partner`;
89CREATE TABLE `Partner` (
90    `partnerID` INT(11) NOT NULL AUTO_INCREMENT,
91    `companyName` VARCHAR(40),
92    `streetNo` INT(11) DEFAULT NULL,
93    `street` VARCHAR(40) DEFAULT NULL,
94    `suburb` VARCHAR(20) DEFAULT NULL,
95    `postcode` INT(4) DEFAULT NULL,
96    `state` VARCHAR(20) DEFAULT NULL,
97    `firstName` VARCHAR(40) DEFAULT NULL,
98    `lastName` VARCHAR(40) DEFAULT NULL,
99    `phoneNumer` TEXT,
100    `emailAdress` VARCHAR(100) DEFAULT NULL,
101    `managerID` INT(11) NOT NULL,
102    PRIMARY KEY (`partnerID`),
103    CONSTRAINT `partner_ibfk_1` FOREIGN KEY (`managerID`)
104        REFERENCES `Salesperson` (`salespersonID`)
105)  DEFAULT CHARSET=UTF8;
106
107DROP TABLE IF EXISTS `Purchase`;
108CREATE TABLE `Purchase` (
109    `purchaseID` INT(11) NOT NULL AUTO_INCREMENT,
110    `partnerID` INT(11) NOT NULL,
111    `salespersonID` INT(11) DEFAULT NULL,
112    `productID` INT(11) DEFAULT NULL,
113    `purchaseDateTime` INT(11) DEFAULT NULL,
114    `paymentStatus` ENUM('paid', 'unpaid') DEFAULT NULL,
115    `streetNo` INT(4) DEFAULT NULL,
116    `suburb` VARCHAR(20) DEFAULT NULL,
117    `postcode` INT(4) DEFAULT NULL,
118    `state` VARCHAR(20) DEFAULT NULL,
119    `productUnitPrice` INT(11) DEFAULT NULL,
120    `productQuantity` INT DEFAULT NULL,
121    `discount` INT DEFAULT NULL,
122    PRIMARY KEY (`purchaseID`),
123    FOREIGN KEY (`partnerID`)
124        REFERENCES `Partner` (`partnerID`),
125    FOREIGN KEY (`salespersonID`)
126        REFERENCES `Salesperson` (`salespersonID`)
127)  DEFAULT CHARSET=UTF8;