iyzeuWP5

1CREATE TABLE IF NOT EXISTS `ice_cream` (
2  `IceCreamID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3  `IceCream` VARCHAR(45) NOT NULL,
4  `Description` TEXT NOT NULL,
5  `Price` DOUBLE NOT NULL,
6  PRIMARY KEY (`IceCreamID`))
7ENGINE = InnoDB
8AUTO_INCREMENT = 1
9DEFAULT CHARACTER SET = latin1;
10
11CREATE TABLE IF NOT EXISTS `ingredients` (
12`IngredientID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
13`Name` VARCHAR(50) NOT NULL,
14`Description` VARCHAR(45) NOT NULL,
15`Units` VARCHAR(45) NOT NULL,
16`UnitsInStock` INT(10) NOT NULL,
17PRIMARY KEY (`IngredientID`))
18ENGINE = InnoDB
19AUTO_INCREMENT = 1
20DEFAULT CHARACTER SET = latin1;
21
22CREATE TABLE IF NOT EXISTS `ice_cream_ingredient` (
23`fkIngredientID` INT(10) UNSIGNED NOT NULL,
24`fkIceCreamID` INT(10) UNSIGNED NOT NULL,
25`Quantity` INT(10) UNSIGNED NOT NULL,
26PRIMARY KEY (`fkIngredientID`, `fkIceCreamID`),
27INDEX `FK_ice_IceCreamID` (`fkIceCreamID` ASC),
28CONSTRAINT `FK_ice_IceCreamID`
29FOREIGN KEY (`fkIceCreamID`)
30REFERENCES `ice_cream` (`IceCreamID`)
31ON DELETE CASCADE
32ON UPDATE CASCADE,
33CONSTRAINT `FK_ice_IngredientID`
34FOREIGN KEY (`fkIngredientID`)
35REFERENCES `ingredients` (`IngredientID`)
36ON DELETE CASCADE
37ON UPDATE CASCADE)
38ENGINE = InnoDB
39DEFAULT CHARACTER SET = latin1;
40
41CREATE TABLE IF NOT EXISTS `company` (
42`CompanyID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
43`CompanyName` VARCHAR(50) NOT NULL,
44`Address` VARCHAR(45) NOT NULL,
45`Phone` VARCHAR(29) NOT NULL,
46`Email` VARCHAR(45) NOT NULL,
47PRIMARY KEY (`CompanyID`))
48ENGINE = InnoDB
49AUTO_INCREMENT = 1
50DEFAULT CHARACTER SET = latin1;
51
52CREATE TABLE IF NOT EXISTS `supplier_list` (
53`fkCompanyID` INT(10) UNSIGNED NOT NULL,
54`fkIngredientID` INT(10) UNSIGNED NOT NULL,
55`PricePerUnit` DOUBLE(3,2) UNSIGNED NOT NULL,
56PRIMARY KEY (`fkCompanyID`, `fkIngredientID`),
57INDEX `FK_IngredientID` (`fkIngredientID` ASC),
58CONSTRAINT `FK_IngredientID`
59FOREIGN KEY (`fkIngredientID`)
60REFERENCES `ingredients` (`IngredientID`)
61ON DELETE CASCADE
62ON UPDATE CASCADE,
63CONSTRAINT `FKCompanyID`
64FOREIGN KEY (`fkCompanyID`)
65REFERENCES `company` (`CompanyID`)
66ON DELETE CASCADE
67ON UPDATE CASCADE)
68ENGINE = InnoDB
69DEFAULT CHARACTER SET = latin1;
70
71CREATE TABLE IF NOT EXISTS `sales` (
72`SalesID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
73`fkCompanyID` INT(10) UNSIGNED NOT NULL,
74`fkIceCreamID` INT(10) UNSIGNED NOT NULL,
75`Quantity` TINYINT(3) NOT NULL,
76`DateOrdered` DATETIME NOT NULL,
77`DateDispatched` DATETIME NOT NULL,
78`DatePaid` DATETIME NOT NULL,
79`AmountPaid` DOUBLE(3,2) NOT NULL,
80PRIMARY KEY (`SalesID`),
81INDEX `FK_CompanyID` (`fkCompanyID` ASC),
82INDEX `FK_IceCreamID` (`fkIceCreamID` ASC),
83CONSTRAINT `FK_IceCreamID`
84FOREIGN KEY (`fkIceCreamID`)
85REFERENCES `ice_cream` (`IceCreamID`)
86ON DELETE CASCADE
87ON UPDATE CASCADE,
88CONSTRAINT `FK_CompanyID`
89FOREIGN KEY (`fkCompanyID`)
90REFERENCES `company` (`CompanyID`)
91ON DELETE CASCADE
92ON UPDATE CASCADE)
93ENGINE = InnoDB
94AUTO_INCREMENT = 1
95DEFAULT CHARACTER SET = latin1;