XHMvJ736

1CREATE TABLE IF NOT EXISTS ingredients (
2  IngredientID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3  Name VARCHAR(50) NOT NULL,
4  Description VARCHAR(45) NOT NULL,
5  Units VARCHAR(45) NOT NULL,
6  UnitsInStock INT(10),
7  PRIMARY KEY (IngredientID))
8ENGINE = InnoDB
9AUTO_INCREMENT = 1
10DEFAULT CHARACTER SET = latin1;
11
12
13
14CREATE TABLE IF NOT EXISTS ice_cream_ingredient 
15(
16  fkIngredientID INT(10) UNSIGNED NOT NULL,
17  fkIceCreamID INT(10) UNSIGNED NOT NULL,
18  Quantity INT(10) UNSIGNED NOT NULL,
19  PRIMARY KEY (fkIngredientID, fkIceCreamID),
20  INDEX FK_ice_IceCreamID (fkIceCreamID ASC),
21  CONSTRAINT FK_ice_IceCreamID
22    FOREIGN KEY (fkIceCreamID)
23    REFERENCES ice_cream (IceCreamID)
24    ON DELETE CASCADE
25    ON UPDATE CASCADE,
26  CONSTRAINT FK_ice_IngredientID
27    FOREIGN KEY (fkIngredientID)
28    REFERENCES ingredients (IngredientID)
29    ON DELETE CASCADE
30    ON UPDATE CASCADE)
31ENGINE = InnoDB
32DEFAULT CHARACTER SET = latin1;
33
34
35
36
37CREATE TABLE IF NOT EXISTS company (
38  CompanyID INT(10) NOT NULL AUTO_INCREMENT,
39  CompanyName VARCHAR(50) NOT NULL,
40  Address VARCHAR(45) NOT NULL,
41  Phone VARCHAR(29) NOT NULL,
42  Email VARCHAR(45) NOT NULL,
43  PRIMARY KEY (CompanyID))
44ENGINE = InnoDB
45AUTO_INCREMENT = 1
46DEFAULT CHARACTER SET = latin1;
47
48
49CREATE TABLE IF NOT EXISTS supplier_list (
50  fkIngredientID INT(10) UNSIGNED NOT NULL,
51  fkIceCreamID INT(10) UNSIGNED NOT NULL,
52  PricePerUnit DOUBLE(3,2) NOT NULL,
53 PRIMARY KEY (fkIngredientID, fkIceCreamID));
54
55
56
57
58CREATE TABLE IF NOT EXISTS sales (
59  SalesID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
60  fkIngredientID INT(10) UNSIGNED NOT NULL,
61  fkIceCreamID INT(10) UNSIGNED NOT NULL,
62  Quantity TINYINT(3),
63  DateOrdered DATETIME,
64  DateDispatched DATETIME,
65  DatePaid DATETIME,
66  AmountPaid DOUBLE(3,2),
67   PRIMARY KEY (SalesID, fkIngredientID, fkIceCreamID));