· 5 years ago · May 15, 2020, 11:24 AM
1USE database_assignment2;
2-- Group: 6
3-- Members: Vu Hoang Trung (s3694398) & Tran Minh Quang (s3757281)
4-- Assignment Details: Assignment 2 - Practical Database Concepts (ISYS3414)
5-- Lecturer: Long Nguyen Minh
6
7 -- DATABASE CREATION --
8
9-- Delete all existing tables on code re-run:
10DROP TABLE IF EXISTS suppliers;
11DROP TABLE IF EXISTS equipment;
12DROP TABLE IF EXISTS purchases;
13DROP TABLE IF EXISTS membership;
14DROP TABLE IF EXISTS customer;
15DROP TABLE IF EXISTS business_customer;
16DROP TABLE IF EXISTS private_customer;
17DROP TABLE IF EXISTS transactions;
18DROP TABLE IF EXISTS transactions_equpments;
19DROP TABLE IF EXISTS support;
20DROP TABLE IF EXISTS support_equipment;
21
22
23
24-- Create suppliers table
25CREATE TABLE suppliers(
26 id INTEGER,
27 name VARCHAR(50) NOT NULL,
28 phone_number VARCHAR(20) NOT NULL,
29 address VARCHAR(70),
30
31 CONSTRAINT pk_suppliers
32 PRIMARY KEY (id)
33);
34
35-- Create equipment table
36CREATE TABLE equipment(
37 id INTEGER,
38 name VARCHAR(30) NOT NULL,
39 category VARCHAR(30) NOT NULL,
40 brand VARCHAR(30) NOT NULL,
41 total_quantity INT,
42 weekday_rate FLOAT(20,2) NOT NULL,
43 weekend_rate FLOAT(20,2) NOT NULL,
44
45 CONSTRAINT pk_equipment
46 PRIMARY KEY (id)
47);
48
49
50
51-- Create purchases table
52CREATE TABLE purchases(
53 supplier_id INT,
54 equipment_id INT,
55 order_date DATE NOT NULL,
56 delivery_date DATE NOT NULL,
57 price FLOAT(20,2) NOT NULL,
58 quantity INT NOT NULL,
59
60 CONSTRAINT pk_purchases
61 PRIMARY KEY (supplier_id, equipment_id),
62
63 CONSTRAINT fk_suppliers_purchases
64 FOREIGN KEY (supplier_id)
65 REFERENCES suppliers(id)
66 ON UPDATE CASCADE
67 ON DELETE CASCADE,
68
69 CONSTRAINT fk_equipment_purchases
70 FOREIGN KEY (equipment_id)
71 REFERENCES equipment(id)
72 ON UPDATE CASCADE
73 ON DELETE CASCADE
74);
75
76
77-- Create membership table
78CREATE TABLE membership (
79 id INTEGER,
80 type VARCHAR(10),
81 discount DECIMAL(3, 2),
82
83 CONSTRAINT pk_membership
84 PRIMARY KEY(id)
85);
86
87-- Create customer table
88CREATE TABLE customer(
89 id INTEGER,
90 customer_name VARCHAR(30) NOT NULL,
91 phone_number VARCHAR(20) NOT NULL,
92 address VARCHAR(70) NOT NULL,
93
94 CONSTRAINT pk_customer
95 PRIMARY KEY(id)
96);
97
98CREATE TABLE business_customer(
99 id VARCHAR(6),
100 membership_id INTEGER,
101 customer_id INTEGER,
102
103
104 CONSTRAINT pk_business_customer
105 PRIMARY KEY(id),
106
107 CONSTRAINT fk_customer_business
108 FOREIGN KEY (customer_id)
109 REFERENCES customer(id)
110 ON UPDATE CASCADE
111 ON DELETE CASCADE,
112
113 CONSTRAINT fk_membership_business_customer
114 FOREIGN KEY (membership_id)
115 REFERENCES membership(id)
116 ON UPDATE CASCADE
117 ON DELETE CASCADE
118);
119
120CREATE TABLE private_customer(
121 id VARCHAR(6),
122 customer_id INTEGER,
123 distance FLOAT(4, 2),
124
125 CONSTRAINT pk_private_customer
126 PRIMARY KEY(id),
127
128 CONSTRAINT check_distance
129 CHECK ( distance <= 5.00),
130
131 CONSTRAINT fk_customer_private
132 FOREIGN KEY (customer_id)
133 REFERENCES customer(id)
134 ON UPDATE CASCADE
135 ON DELETE CASCADE
136);
137
138
139-- Create transactions table
140CREATE TABLE transactions(
141 number INT,
142 transaction_date DATE NOT NULL,
143 customer_id INT,
144 returned BOOLEAN,
145 expected_return_date DATE NOT NULL,
146 actual_return_date DATE NOT NULL,
147 total_cost FLOAT(20,2) NOT NULL,
148
149 CONSTRAINT pk_transactions
150 PRIMARY KEY(number),
151
152 CONSTRAINT fk_customer_transaction
153 FOREIGN KEY (customer_id)
154 REFERENCES customer(id)
155 ON UPDATE CASCADE
156 ON DELETE CASCADE
157
158);
159
160CREATE TABLE transactions_equpments(
161 transaction_id INTEGER,
162 equipment_id INTEGER,
163 quantity INTEGER NOT NULL,
164
165 CONSTRAINT pk_support_equipment
166 PRIMARY KEY(transaction_id, equipment_id),
167
168 CONSTRAINT fk_transaction_id
169 FOREIGN KEY (transaction_id)
170 REFERENCES transactions(number)
171 ON UPDATE CASCADE
172 ON DELETE CASCADE,
173
174 CONSTRAINT fk_equipment
175 FOREIGN KEY (equipment_id)
176 REFERENCES equipment(id)
177 ON UPDATE CASCADE
178 ON DELETE CASCADE
179);
180
181
182-- Create Support table
183CREATE TABLE support (
184 number INTEGER,
185 customer_id INT,
186 issue VARCHAR(200) NOT NULL,
187 solution VARCHAR(100),
188 openDate DATE NOT NULL,
189 closeDate DATE,
190 solved BOOLEAN,
191
192 CONSTRAINT pk_support
193 PRIMARY KEY(number),
194
195 CONSTRAINT fk_support_customer
196 FOREIGN KEY (customer_id)
197 REFERENCES customer(id)
198 ON UPDATE CASCADE
199 ON DELETE CASCADE
200);
201
202CREATE TABLE support_equipment(
203 support_id INTEGER,
204 equipment_id INTEGER,
205 quantity INTEGER NOT NULL,
206
207 CONSTRAINT pk_support_equipment
208 PRIMARY KEY(support_id, equipment_id),
209
210 CONSTRAINT fk_support
211 FOREIGN KEY (support_id)
212 REFERENCES support(number)
213 ON UPDATE CASCADE
214 ON DELETE CASCADE,
215
216 CONSTRAINT fk_equipment_support
217 FOREIGN KEY (equipment_id)
218 REFERENCES equipment(id)
219 ON UPDATE CASCADE
220 ON DELETE CASCADE
221);