· 7 years ago · Nov 19, 2018, 08:56 AM
1DROP TABLE IF EXISTS `Delivery_Man`;
2CREATE TABLE `Delivery_Man` (
3 `ssn` VARCHAR(13) NOT NULL,
4 `name` VARCHAR(15) NOT NULL,
5 `tel` VARCHAR(9),
6 `email` VARCHAR(255) NOT NULL UNIQUE,
7 `companyname` VARCHAR(255),
8 PRIMARY KEY (`ssn`)
9);
10DROP TABLE IF EXISTS `Wallet`;
11CREATE TABLE `Wallet` (
12 `wallet_id` INT NOT NULL AUTO_INCREMENT,
13 `value` INT UNSIGNED NOT NULL DEFAULT 0,
14 PRIMARY KEY (`wallet_id`)
15);
16DROP TABLE IF EXISTS `Customer`;
17CREATE TABLE `Customer` (
18 `ssn` VARCHAR(13) NOT NULL,
19 `name` VARCHAR(15) NOT NULL,
20 `tel` VARCHAR(10),
21 `email` VARCHAR(255) NOT NULL UNIQUE,
22 `gender` ENUM('m', 'f'),
23 `address` VARCHAR(255) NOT NULL,
24 `wallet_id` INT,
25 PRIMARY KEY (`ssn`),
26 CONSTRAINT `fk__Customer_wallet_id`
27 FOREIGN KEY (`wallet_id`)
28 REFERENCES `Wallet`(`wallet_id`)
29 ON DELETE CASCADE
30 ON UPDATE CASCADE
31
32);
33DROP TABLE IF EXISTS `Seller`;
34CREATE TABLE `Seller` (
35 `ssn` VARCHAR(13) NOT NULL,
36 `name` VARCHAR(15) NOT NULL,
37 `tel` VARCHAR(10),
38 `email` VARCHAR(255) NOT NULL UNIQUE,
39 `address` VARCHAR(255) NOT NULL,
40 `wallet_id` INT,
41 PRIMARY KEY (`ssn`),
42 CONSTRAINT `fk__Seller_wallet_id`
43 FOREIGN KEY (`wallet_id`)
44 REFERENCES `Wallet`(`wallet_id`)
45 ON DELETE CASCADE
46 ON UPDATE CASCADE
47
48);
49DROP TABLE IF EXISTS `Webmaster`;
50CREATE TABLE `Webmaster` (
51 `ssn` VARCHAR(13) NOT NULL,
52 `name` VARCHAR(15) NOT NULL,
53 `tel` VARCHAR(10),
54 `email` VARCHAR(255) NOT NULL UNIQUE,
55 `salary` INT UNSIGNED NOT NULL,
56 PRIMARY KEY (`ssn`)
57
58);
59
60
61
62DROP TABLE IF EXISTS `Order`;
63CREATE TABLE `Order` (
64 `order_id` INT NOT NULL AUTO_INCREMENT,
65 `isDelivered` TINYINT(1) DEFAULT 0,
66 `deliveryMan_ssn` VARCHAR(13),
67 `deliveryDate` DATE,
68 `customer_ssn` VARCHAR(13) NOT NULL,
69 `submitDate` DATE NOT NULL,
70 PRIMARY KEY (`order_id`),
71 CONSTRAINT `fk__Order_deliveryMan_ssn`
72 FOREIGN KEY (`deliveryMan_ssn`)
73 REFERENCES `Delivery_Man`(`ssn`)
74 ON DELETE SET NULL,
75 CONSTRAINT `fk__Order_customer_ssn`
76 FOREIGN KEY (`customer_ssn`)
77 REFERENCES `Customer`(`ssn`)
78 ON DELETE CASCADE
79 ON UPDATE CASCADE
80);
81DROP TABLE IF EXISTS `Product`;
82CREATE TABLE `Product` (
83 `product_id` INT NOT NULL AUTO_INCREMENT,
84 `name` VARCHAR(15) NOT NULL,
85 `price` INT UNSIGNED NOT NULL,
86 `description` VARCHAR(255) NOT NULL,
87 `pictureUrl` VARCHAR(255),
88 `category` VARCHAR(255) NOT NULL,
89 `stock` INT UNSIGNED NOT NULL,
90 `seller_ssn` VARCHAR(13) NOT NULL,
91
92 PRIMARY KEY (`product_id`),
93 CONSTRAINT `fk_Product_seller_ssn`
94 FOREIGN KEY (`seller_ssn`)
95 REFERENCES `Seller`(`ssn`)
96 ON DELETE CASCADE
97 ON UPDATE CASCADE
98);
99DROP TABLE IF EXISTS `Product_Item`;
100CREATE TABLE `Product_Item` (
101 `product_item_id` INT NOT NULL AUTO_INCREMENT,
102 `amount` INT UNSIGNED NOT NULL DEFAULT 0,
103 `order_id` INT,
104 `product_id` INT,
105 `customer_ssn` VARCHAR(13) NOT NULL,
106 PRIMARY KEY (`product_item_id`),
107 CONSTRAINT `fk__Product_Item_customer_ssn`
108 FOREIGN KEY (`customer_ssn`)
109 REFERENCES `Customer`(`ssn`)
110 ON DELETE CASCADE
111 ON UPDATE CASCADE,
112 CONSTRAINT `fk_Product_Item_order_id`
113 FOREIGN KEY (`order_id`)
114 REFERENCES `Order`(`order_id`)
115 ON DELETE CASCADE
116 ON UPDATE CASCADE,
117 CONSTRAINT `fk_Product_Item_product_id`
118 FOREIGN KEY (`product_id`)
119 REFERENCES `Product`(`product_id`)
120 ON DELETE CASCADE
121 ON UPDATE CASCADE
122);
123
124
125DROP TABLE IF EXISTS `Coupon`;
126CREATE TABLE `Coupon` (
127 `coupon_id` INT NOT NULL AUTO_INCREMENT,
128 `code` VARCHAR(6) NOT NULL,
129 `discountAmount` INT UNSIGNED NOT NULL,
130 `product_item_id` INT,
131 PRIMARY KEY (`coupon_id`),
132 CONSTRAINT `fk_Coupon_product_item_id`
133 FOREIGN KEY (`product_item_id`)
134 REFERENCES `Product_Item`(`product_item_id`)
135 ON DELETE SET NULL
136);
137DROP TABLE IF EXISTS `Review`;
138CREATE TABLE `Review` (
139 `product_id` INT NOT NULL,
140 `review_id` INT NOT NULL,
141 `comment` VARCHAR(255) NOT NULL,
142 `rating` ENUM('1', '2','3','4','5') NOT NULL,
143 `customer_ssn` VARCHAR(13),
144 CONSTRAINT `pk_Review_product_id_review_id` PRIMARY KEY (`product_id`,`review_id`),
145 CONSTRAINT `fk__Review_customer_ssn`
146 FOREIGN KEY (`customer_ssn`)
147 REFERENCES `Customer`(`ssn`)
148 ON DELETE CASCADE
149 ON UPDATE CASCADE,
150 CONSTRAINT `fk__Review_product_id`
151 FOREIGN KEY (`product_id`)
152 REFERENCES `Product`(`product_id`)
153 ON DELETE CASCADE
154 ON UPDATE CASCADE
155);