bZ2XuVxd

1CREATE TABLE Shopping_Cart (
2  CustID INTEGER NOT NULL,
3  ProdID INTEGER NOT NULL,
4  NumOfItems INTEGER,
5  PRIMARY KEY CLUSTERED (CustID, ProdID)
6  CONSTRAINT FK_Cust FOREIGN KEY FK_Cat (CustID) 
7    REFERENCES Customers (CustID)
8    ON DELETE RESTRICT
9    ON UPDATE RESTRICT,
10  CONSTRAINT FK_Prod FOREIGN KEY FK_Prod (ProdID) 
11    REFERENCES Products (ProdID)
12    ON DELETE RESTRICT
13    ON UPDATE RESTRICT
14);
15	
16CREATE TABLE Shopping_Cart (
17  CustID INTEGER NOT NULL,
18  ProdID INTEGER NOT NULL,
19  NumOfItems INTEGER,
20  PRIMARY KEY CLUSTERED (CustID, ProdID),
21  CONSTRAINT FK_Cust FOREIGN KEY FK_Cat (CustID) 
22    REFERENCES Customers (CustID)
23    ON DELETE RESTRICT
24    ON UPDATE RESTRICT,
25  CONSTRAINT FK_Prod FOREIGN KEY FK_Prod (ProdID) 
26    REFERENCES Products (ProdID)
27    ON DELETE RESTRICT
28    ON UPDATE RESTRICT
29);
30	
31DROP DATABASE IF EXISTS WebStore;
32CREATE DATABASE WebStore;
33USE WebStore;
34
35CREATE TABLE Categories (
36    CatID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
37    Name VARCHAR(20) NOT NULL,
38    Descr VARCHAR(120) NOT NULL,
39    IconURL VARCHAR(64) NOT NULL,
40    PRIMARY KEY (CatID)
41);
42
43CREATE TABLE Products (
44    ProdID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
45    Name VARCHAR(20) NOT NULL,
46    Category INTEGER UNSIGNED,
47    Descr VARCHAR(120) NOT NULL,
48    Price FLOAT NOT NULL,
49    Stock INTEGER UNSIGNED NOT NULL,
50    IconURL VARCHAR(64) NOT NULL,
51    PRIMARY KEY (ProdID),
52    CONSTRAINT FK_Cat FOREIGN KEY FK_Cat (Category)
53        REFERENCES Categories (CatID)
54        ON DELETE RESTRICT
55        ON UPDATE RESTRICT
56);
57
58INSERT INTO Categories VALUES(null,"Laptops","Small computers you can carry","http://localhost/images/laptop.jpg");
59SET @laptops := LAST_INSERT_ID();
60INSERT INTO Categories VALUES(null,"Desktops","Big computers you cannot carry","http://localhost/images/desktop.jpg");
61SET @desktops := LAST_INSERT_ID();
62INSERT INTO Categories VALUES(null,"Tablets"," Flat things you lose frequently","http://localhost/images/tablet.jpg");
63SET @tablets := LAST_INSERT_ID();
64
65INSERT INTO Products VALUES(null,"DULL 1200",@desktops,"A big dull machine",1290.99,23,"http://localhost/images/dull1200.jpg");
66INSERT INTO Products VALUES(null,"8P Totalo",@desktops,"Almost as big as the DULL",990.99,2,"http://localhost/images/8ptotalo.jpg");
67INSERT INTO Products VALUES(null,"LaNuveau Bingster",@desktops,"Comes in blue and red",690.99,12,"http://localhost/images/lanuveaubingster.jpg");
68
69INSERT INTO Products VALUES(null,"DULL 122",@laptops,"Small, portable and useless",422.99,4,"http://localhost/images/dull122.jpg");
70INSERT INTO Products VALUES(null,"8P Tootsie",@laptops,"Sticky and too heavy",559.99,12,"http://localhost/images/8ptootsie.jpg");
71INSERT INTO Products VALUES(null,"LaNuveau Shoobie XT",@laptops,"Weighs a ton but looks sharp",1690.99,122,"http://localhost/images/lanuveaushoobiext.jpg");
72
73INSERT INTO Products VALUES(null,"Motor Roller 12",@tablets,"The only one with a oval screen",422.99,4,"http://localhost/images/mr12.jpg");
74INSERT INTO Products VALUES(null,"SamSings OffKey",@tablets,"Needs duct tape to run",559.99,2,"http://localhost/images/samsingsOK.jpg");
75INSERT INTO Products VALUES(null,"jPet 12",@tablets,"The first that kinda sorta works",16290.99,722,"http://localhost/images/jpet12.jpg");
76
77CREATE TABLE Customers (
78    CustID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
79    FirstName VARCHAR(40) NOT NULL,
80    LastName VARCHAR(40) NOT NULL,
81    Address VARCHAR(60) NOT NULL,
82    City VARCHAR(40) NOT NULL,
83    State VARCHAR(20) NOT NULL,
84    Zip VARCHAR(5) NOT NULL,
85    PRIMARY KEY (CustID)
86);
87
88INSERT INTO Customers VALUES(null,"Gerald","Bostock","1234 TAAB Drive","St. Cleve","FL","12345");
89INSERT INTO Customers VALUES(null,"Suzy","Creamcheese","8722 Zappa Road","Paris","TX","75460");
90
91CREATE TABLE Shopping_Cart (
92    CustID INTEGER UNSIGNED NOT NULL,
93    ProdID INTEGER UNSIGNED NOT NULL,
94    NumOfItems INTEGER,
95    PRIMARY KEY CLUSTERED (CustID, ProdID),
96    CONSTRAINT FK_Cust FOREIGN KEY FK_Cat (CustID) 
97        REFERENCES Customers (CustID)
98        ON DELETE RESTRICT
99        ON UPDATE RESTRICT,
100    CONSTRAINT FK_Prod FOREIGN KEY FK_Prod (ProdID) 
101        REFERENCES Products (ProdID)
102        ON DELETE RESTRICT
103        ON UPDATE RESTRICT
104);
105
106INSERT INTO Shopping_Cart VALUES("1","4","2");
107INSERT INTO Shopping_Cart VALUES("1","9","1");