-
Notifications
You must be signed in to change notification settings - Fork 0
/
Table Creation.sql
162 lines (144 loc) · 4.41 KB
/
Table Creation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
CREATE TYPE payment_method as ENUM ( 'visa', 'mastercard', 'american express', 'paypal');
CREATE TABLE Customer
(
customerID int,
firstName varchar(50) NOT NULL,
lastName varchar(50) NOT NULL,
email varchar(50) UNIQUE NOT NULL,
address varchar(100) NOT NULL,
PRIMARY KEY (customerID)
);
CREATE TABLE Product
(
manufacturerPrice double precision NOT NULL CHECK (manufacturerPrice > 0),
retailPrice double precision NOT NULL CHECK (retailPrice > 0),
cutPercentage double precision,
CHECK (cutPercentage > 0),
CHECK (cutPercentage < 1),
productName varchar(50) UNIQUE NOT NULL,
productID int,
PRIMARY KEY (productID)
);
CREATE TABLE Orders
(
orderID int,
payment payment_method NOT NULL,
orderDate date NOT NULL,
PRIMARY KEY (orderID)
);
CREATE TABLE Warehouse
(
warehouseID int,
address varchar(100) NOT NULL,
PRIMARY KEY(warehouseID)
);
CREATE TABLE Supplier
(
supplierName varchar(50),
address varchar(100) NOT NULL,
PRIMARY KEY(supplierName)
);
CREATE TABLE Shipment
(
shipmentID int,
shipmentDate date NOT NULL,
PRIMARY KEY(shipmentID)
);
CREATE TABLE Team
(
teamName varchar(50),
accountNumber int UNIQUE NOT NULL,
PRIMARY KEY(teamName)
);
CREATE TABLE Player
(
playerNumber int NOT NULL CHECK (playerNumber >= 0),
accountNumber int UNIQUE NOT NULL,
firstName varchar(50) NOT NULL,
lastname varchar(50) NOT NULL,
teamName varchar(50) NOT NULL,
PRIMARY KEY(playerNumber, teamName),
FOREIGN KEY(teamName) REFERENCES Team(teamName) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE TeamMerchandise
(
productID int,
PRIMARY KEY(productID),
FOREIGN KEY (productID) REFERENCES Product(productID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE PlayerMerchandise
(
productID int,
PRIMARY KEY (productID),
FOREIGN KEY (productID) REFERENCES Product(productID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE CustomerOrder
(
customerID int NOT NULL,
orderID int,
PRIMARY KEY(orderID),
FOREIGN KEY(customerID) REFERENCES Customer(customerID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(orderID) REFERENCES Orders(orderID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE ProductOrderWarehouse
(
quantity int NOT NULL CHECK (quantity > 0),
productID int,
orderID int,
warehouseID int,
PRIMARY KEY(productID, orderID, warehouseID),
FOREIGN KEY(productID) REFERENCES Product(productID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(orderID) REFERENCES Orders(orderID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(warehouseID) REFERENCES Warehouse(warehouseID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE ShipmentSupplier
(
supplierName varchar(50) NOT NULL,
shipmentID int,
PRIMARY KEY (shipmentID),
FOREIGN KEY (shipmentID) REFERENCES Shipment(shipmentID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (supplierName) REFERENCES Supplier(supplierName) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE ShipmentWarehouse
(
shipmentID int,
warehouseID int NOT NULL,
PRIMARY KEY(shipmentID),
FOREIGN KEY(shipmentID) REFERENCES Shipment(shipmentID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(warehouseID) REFERENCES Warehouse(warehouseID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE ShipmentProduct
(
quantity int NOT NULL CHECK (quantity > 0),
shipmentID int,
productID int,
PRIMARY KEY (shipmentID, productID),
FOREIGN KEY (shipmentID) REFERENCES Shipment(shipmentID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (productID) REFERENCES Product(productID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE WarehouseProduct
(
quantity int NOT NULL CHECK (quantity > 0),
warehouseID int,
productID int,
PRIMARY KEY(warehouseID, productID),
FOREIGN KEY (warehouseID) REFERENCES Warehouse(warehouseID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (productID) REFERENCES Product(productID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE TeamMerchandiseTeam
(
productID int,
teamName varchar(50),
PRIMARY KEY (productID),
FOREIGN KEY (productID) REFERENCES TeamMerchandise(productID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (teamName) REFERENCES Team(teamName) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE PlayerMerchandisePlayer
(
productID int,
playerNumber int,
teamName varchar(50),
PRIMARY KEY (productID),
FOREIGN KEY (productID) REFERENCES PlayerMerchandise(productID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (playerNumber, teamName) REFERENCES Player(playerNumber, teamName) ON DELETE CASCADE ON UPDATE CASCADE
);