· 9 years ago · Oct 13, 2016, 03:18 PM
1DROP DATABASE IF EXISTS vehicleRent;
2CREATE DATABASE IF NOT EXISTS vehicleRent;
3 USE vehicleRent;
4
5SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';
6
7DROP TABLE IF EXISTS VEHICLE,
8 LOCATION,
9 MODEL,
10 MANUFACTURER,
11 BOOKING,
12 CUSTOMER;
13
14CREATE TABLE LOCATION (
15 locationID VARCHAR(4) NOT NULL,
16 locationName VARCHAR(20) NOT NULL,
17 PRIMARY KEY (locationID)
18);
19
20INSERT INTO LOCATION
21(locationID,locationName)
22VALUES
23('BD','Ba Dinh'),
24('CG','Cau Giay'),
25('HK','Hoan Kiem'),
26('TX','Thanh Xuan'),
27('DD','Dong Da');
28
29CREATE TABLE MANUFACTURER (
30 manuID CHAR(2) NOT NULL,
31 manuName VARCHAR(20) NOT NULL,
32 manuAddr VARCHAR(20) NOT NULL,
33 country VARCHAR(20) NOT NULL,
34 PRIMARY KEY (manuID)
35);
36
37INSERT INTO MANUFACTURER
38(manuID,manuName,manuAddr,country)
39VALUES
40('Y1','Yamaha','Xa Dan','Japan'),
41('H1','Honda','Binh Duong','Japan'),
42('P1','Piaggio','Nguyen Thai Hoc','Italy'),
43('D1','Ducati','Thailand','Italy');
44
45CREATE TABLE MODEL (
46 modelID CHAR(5) NOT NULL,
47 modelName VARCHAR(20) NOT NULL,
48 manuID CHAR(2) NOT NULL,
49 cylinderSize INT NOT NULL,
50 manuYear INT NOT NULL,
51 FOREIGN KEY (manuID) REFERENCES MANUFACTURER (manuID) ON DELETE CASCADE,
52 PRIMARY KEY (modelID)
53);
54
55INSERT INTO MODEL
56(modelID, modelName, manuID, cylinderSize, manuYear)
57VALUES
58('Y1002','Serius','Y1',110,2015),
59('Y1003','FZ','Y1',150,2011),
60('Y1011','Nouvo','Y1',150,2014),
61('Y1012','Nozza','Y1',125,2011),
62('H1001','Wave Alpha','H1',110,2001),
63('H1002','Dream','H1',110,2014);
64
65
66
67
68CREATE TABLE VEHICLE (
69 vehicleID CHAR(4) NOT NULL,
70 modelID CHAR(5) NOT NULL,
71 locationID VARCHAR(4) NOT NULL,
72 mileage INT NOT NULL,
73 unitPrice INT NOT NULL,
74 color VARCHAR(20),
75 FOREIGN KEY (modelID) REFERENCES MODEL (modelID) ON DELETE CASCADE,
76 FOREIGN KEY (locationID) REFERENCES LOCATION (locationID) ON DELETE CASCADE,
77 PRIMARY KEY (vehicleID)
78);
79
80INSERT INTO VEHICLE
81(vehicleID, modelID, locationID, mileage, unitPrice, color)
82VALUES
83('0001','H1002','BD',12000,100000,'Blue'),
84('0002','H1002','BD',16020,80000,'Red'),
85('0003','H1002','BD',14000,100000,'Blue'),
86('0004','H1002','CG',18000,90000,'Black'),
87('0005','H1002','HK',25000,90000,'Black'),
88('0006','H1002','TX',25000,90000,'Black'),
89('0007','Y1011','BD',15600,100000,'Blue'),
90('0008','Y1011','CG',25800,80000,'Red'),
91('0009','Y1011','DD',28900,50000,'White'),
92('0010','Y1011','DD',28000,50000,'Green'),
93('0011','Y1011','DD',25540,80000,'Red'),
94('0012','H1002','BD',26860,80000,'Red'),
95('0013','H1001','BD',26740,50000,'White'),
96('0014','H1001','DD',25540,90000,'Black'),
97('0015','H1001','DD',25540,50000,'Green'),
98('0016','H1001','TX',16020,50000,'White'),
99('0017','H1001','HK',16020,100000,'Blue'),
100('0018','H1001','HK',16020,100000,'Blue'),
101('0019','H1001','CG',28000,50000,'White'),
102('0020','H1002','BD',16020,90000,'Black');
103
104
105CREATE TABLE CUSTOMER (
106 customerID CHAR(5) NOT NULL,
107 name VARCHAR(20) NOT NULL,
108 dob DATE NOT NULL,
109 phoneNo VARCHAR(20) NOT NULL,
110 address VARCHAR(20) NOT NULL,
111 identityCard VARCHAR(20) NOT NULL,
112 PRIMARY KEY (customerID)
113);
114
115INSERT INTO CUSTOMER
116(customerID, name, dob, phoneNo, address, identityCard)
117VALUES
118('04013','Stephane Wattier','1975-04-23','0935274384','Times City','FR535671'),
119('00423','Lionel Messi','1994-12-12','0943127821','My Dinh','ARG235253'),
120('03123','Doan Nhat Quang','1987-04-25','0956482104','Hoang Quoc Viet','VN325212'),
121('10012','Dang Duc Hanh','1980-02-12','0946668888','Xuan Thuy','VN828899'),
122('02132','Tran Trong Khanh','1996-12-18','01222277451','Hai Phong','VN482099');
123
124CREATE TABLE EMPLOYEE (
125 employeeID CHAR(5) NOT NULL,
126 name VARCHAR(20) NOT NULL,
127 dob DATE NOT NULL,
128 salary INT NOT NULL,
129 vehicleRented INT,
130 PRIMARY KEY (employeeID)
131);
132
133INSERT INTO EMPLOYEE
134(employeeID, name, dob, salary, vehicleRented)
135VALUES
136('00123','Do Quang Huy','1996-10-15',4500000,1),
137('09412','Ha Duong Long','1996-08-19',2500000,1),
138('01942','Nguyen Phu Hung','1996-07-07',2500000,2),
139('00192','Nguyen Khanh Duy','1996-03-12',3500000,3),
140('00094','Do Dang Ngoc Kha','1996-01-02',3500000,3);
141
142CREATE TABLE RENTING (
143 rentingID CHAR(5) NOT NULL,
144 customerID CHAR(5) NOT NULL,
145 vehicleID CHAR(4) NOT NULL,
146 employeeID CHAR(5) NOT NULL,
147 dateStart DATE NOT NULL,
148 dateEnd DATE,
149 damage REAL,
150 FOREIGN KEY (customerID) REFERENCES CUSTOMER (customerID) ON DELETE CASCADE,
151 FOREIGN KEY (vehicleID) REFERENCES VEHICLE (vehicleID) ON DELETE CASCADE,
152 FOREIGN KEY (employeeID) REFERENCES EMPLOYEE (employeeID) ON DELETE CASCADE,
153 PRIMARY KEY (rentingID)
154);
155
156INSERT INTO RENTING
157(rentingID, customerID, vehicleID, employeeID, dateStart)
158VALUES
159('00001','04013','0001','00123','2016-08-01'),
160('00002','00423','0003','00192','2016-08-10'),
161('00003','03123','0007','01942','2016-08-14'),
162('00004','10012','0009','00094','2016-08-23'),
163('00005','02132','0012','00192','2016-08-24');