bNJsEWCA

1/*
2MySQL RDBMS used.
3*/
4
5DROP DATABASE IF EXISTS EVENT_DATABASE;
6CREATE DATABASE EVENT_DATABASE;
7USE EVENT_DATABASE;
8
9-- creating Event table
10CREATE TABLE `EVENT` (
11`ID` INT NOT NULL AUTO_INCREMENT,
12`TYPE` VARCHAR(15) NOT NULL,
13`NAME` VARCHAR(50) NOT NULL,
14`LOCATION` VARCHAR(30) NOT NULL,
15`DATE` DATETIME NOT NULL,
16`START` DATETIME NOT NULL,
17`END` DATETIME NOT NULL,
18PRIMARY KEY (`ID`)
19);
20
21CREATE TABLE `CUSTOMER` (
22`ID` INT NOT NULL AUTO_INCREMENT,
23`TITLE` VARCHAR(30) NOT NULL,
24`FNAME` VARCHAR(100) NOT NULL,
25`LNAME` VARCHAR(100) NOT NULL,
26`ADDRESS_1` VARCHAR(255) NOT NULL,
27`ADDRESS_2` VARCHAR(255),
28`TOWN` VARCHAR(255) NOT NULL,
29`POSTCODE` VARCHAR(10) NOT NULL,
30`TEL_NUMBER` VARCHAR(16) NOT NULL,
31`EMAIL` VARCHAR(255) NOT NULL,
32PRIMARY KEY (`ID`)
33);
34
35CREATE TABLE `PAYMENT_CARD` (
36`ID` INT NOT NULL AUTO_INCREMENT,
37`CUSTOMER_ID` INT NOT NULL,
38`CARD_NUMBER` VARCHAR(16) NOT NULL,
39`EXPIRY_DATE` DATE NOT NULL,
40PRIMARY KEY (`ID`),
41FOREIGN KEY (`CUSTOMER_ID`) REFERENCES CUSTOMER(`ID`)
42);
43
44CREATE TABLE `PAYMENT` (
45`ID` INT NOT NULL AUTO_INCREMENT,
46`PAYMENT_CARD_ID` INT,
47`METHOD` VARCHAR(20) NOT NULL,
48`PAYMENT_TIME` DATETIME NOT NULL UNIQUE,
49`REFUND_NEEDED` VARCHAR(10) NOT NULL, CONSTRAINT CHK_VALID CHECK (REFUND_NEEDED IN ('True', 'False')),
50`CUSTOMER_ID` INT NOT NULL,
51PRIMARY KEY (`ID`),
52FOREIGN KEY (`PAYMENT_CARD_ID`) REFERENCES PAYMENT_CARD(`ID`),
53FOREIGN KEY(`CUSTOMER_ID`) REFERENCES CUSTOMER(`ID`)
54);
55
56CREATE TABLE `TICKET` (
57`ID` INT NOT NULL AUTO_INCREMENT,
58`EVENT_ID` INT NOT NULL,
59`TICKET_TYPE` VARCHAR (20),
60`TICKET_PRICE` DECIMAL(10,2),
61`NO_TICKET` INT NOT NULL,
62PRIMARY KEY (`ID`),
63FOREIGN KEY (`EVENT_ID`) REFERENCES EVENT(`ID`)
64);
65
66CREATE TABLE `BOOKING` (
67`REFERENCE` INT NOT NULL AUTO_INCREMENT,
68`EVENT_ID` INT,  
69`CUSTOMER_ID` INT,
70`RECIEVED` VARCHAR(20) NOT NULL, CONSTRAINT CHK_RECIEVED CHECK (RECIEVED IN ('Email', 'Collection')),
71`BOOKING_DATE` DATETIME,
72`TICKET_TYPE` INT,
73`TICKETS_BOUGHT` INT,
74PRIMARY KEY (`REFERENCE`),
75FOREIGN KEY (`BOOKING_DATE`) REFERENCES PAYMENT(`PAYMENT_TIME`),
76FOREIGN KEY (`CUSTOMER_ID`) REFERENCES CUSTOMER(`ID`),
77FOREIGN KEY (`EVENT_ID`) REFERENCES EVENT(`ID`),
78FOREIGN KEY (`TICKET_TYPE`) REFERENCES TICKET(`ID`)
79);
80
81CREATE TABLE `PRICE` (
82`ID` INT NOT NULL AUTO_INCREMENT,
83`EVENT_ID` INT,
84`TOTAL` DECIMAL(13,2) NOT NULL,
85`DISCOUNT_VAL` DECIMAL(10,2) NOT NULL,
86`BOOKING_ID` INT NOT NULL,
87PRIMARY KEY (`ID`),
88FOREIGN KEY (`EVENT_ID`) REFERENCES EVENT(`ID`),
89FOREIGN KEY (`BOOKING_ID`) REFERENCES BOOKING(`REFERENCE`)
90);
91
92INSERT INTO EVENT VALUES
93(NULL, 'Festival', 'Wireless', 'London', '2020-10-01', '2020-10-01 06:15:46', '2020-10-04 12:30:32'),
94(NULL, 'Food', 'Bristol Food Market', 'Bristol', '2021-10-01', '2020-10-01 22:17:32', '2020-11-01 06:15:46'),
95(NULL, 'Circus', 'Zoo', 'Exeter', '2029-10-01', '2020-10-01 06:15:46', '2020-10-01 23:51:56'),
96(NULL, 'Music', 'Hybrid Minds', 'Bristol', '2020-07-04', '2020-07-06 09:30:00', '2020-07-06 21:45:00'),
97(NULL, 'Food', 'Bristol Food Festival 2020', 'Bristol', '2020-07-04', '2020-07-04 10:30:00', '2020-07-04 21:30:00'),
98(NULL, 'Music', 'Keynsham Music Festival 2020', 'Pind', '2020-06-03', '2020-06-03 08:30:00', '2020-06-03 22:50:00');
99
100INSERT INTO CUSTOMER VALUES
101(NULL, 'MR', 'Mark', 'Jacobs', '39 Monty Road', 'Gtown', 'Sommerville', 'XY1 2ZG', '099812345', 'marky@gmail.com'),
102(NULL, 'MRS', 'Joe', 'Smiths', '51 Spring Road', 'Keema', 'Croydon', 'RG21 4JR', '084324311345', 'ann@aol.com'),
103(NULL, 'MR', 'Akhminder', 'Singh', '23 Jump Street', 'Kentucky', 'Uxbridge', 'UK1 696', '07100', 'akhminder.saab@outlook.in'),
104(NULL, 'MR', 'Bob', 'Sally', '21 Jump Street', 'Kansas', 'Farnborough', 'FB1 696', '07104', 'examplemail'),
105(NULL, 'MR', 'Minder', 'Hallo', '23 Jump Street', 'Kentucky', 'Uxbridge', 'UK1 696', '07200', 'example2'),
106(NULL, 'MR', 'Akh', 'Kaur', '23 Jump Street', 'Kentucky', 'Uxbridge', 'UK1 696', '07160', 'ex3');
107
108-- ID, CUSTOMERID, CARDNUMBER, EXPIRYDATE
109INSERT INTO PAYMENT_CARD VALUES
110(NULL, 1, '0987654321234567', '2025-10-01'),
111(NULL, 2, '0987678621234567', '2021-10-01'),
112(NULL, 3, '0987654321287367', '2019-10-01'),
113(NULL, 4, '1234567891011121', '2024-04-17'),
114(NULL, 5, '1234567732011121', '2022-08-21'),
115(NULL, 6, '1234567899811121', '2023-01-28');
116
117-- ID, PAYMENTCARDID, METHOD, PAYMENTTIME, REFUNDNEEDED
118INSERT INTO PAYMENT VALUES
119(NULL, 1, 'Visa', '2019-12-28 13:09:00', 'False', 1),
120(NULL, 2, 'Mastercard',  '2019-10-28 10:15:00', 'True', 2),
121(NULL, 3, 'Visa', '2019-11-28 17:34:00', 'False', 3),
122(NULL, 4, 'Mastercard', '2020-02-27 20:30:00', 'True', 1),
123(NULL, 5, 'Visa', '2020-03-27 12:30:00', 'True', 3),
124(NULL, 6, 'Visa', '2020-03-17 18:30:00', 'False', 3),
125(NULL, 2, 'Mastercard', '2019-11-27 12:00:00', 'True', 2),
126(NULL, 6, 'Visa', '2019-11-25 11:30:00', 'False', 5);
127
128/*
129(NULL, 1, 1, 'Email', '2019-12-28 13:09:00', 1, 1),
130(NULL, 1, 2, 'Collection', '2019-10-28 10:15:00', 2, 2),
131(NULL, 6, 3, 'Email', '2019-11-28 17:34:00', 3, 3),
132(NULL, 6, 1, 'Email', '2020-03-17 18:30:00', 4, 5),
133(NULL, 6, 3, 'Email', '2020-02-27 20:30:00', 2, 5),
134(NULL, 6, 3, 'Collection', '2020-03-27 12:30:00', 6, 5),
135(NULL, 1, 2, 'Email', '2019-11-27 12:00:00', 1, 1),
136(NULL, 4, 5, 'Collection', '2019-11-25 11:30:00', 3, 9);
137*/
138
139-- ID, EVENTID, TYPE, PRICE, NOTICKETS
140INSERT INTO TICKET VALUES
141(NULL, 1, 'ADULT', 14.99, 70),
142(NULL, 1, 'CHILD', 9.99, 30),
143(NULL, 6, 'BRONZE', 4.99, 70),
144(NULL, 6, 'SILVER', 13.99, 50),
145(NULL, 6, 'GOLD', 19.99, 30),
146(NULL, 3, 'STANDARD', 24.99, 250),
147(NULL, 5, 'ADULT', 15.00, 100),
148(NULL, 5, 'CHILD', 12.00, 60),
149(NULL, 4, 'STANDARD', 75.00, 350);
150
151-- REFERENCE, EVENT_ID, CUSTOMERID, RECEIVED, BOOKINGDATE, TICKETSBOUGHT, TICKETTYPE
152INSERT INTO BOOKING VALUES
153(NULL, 1, 1, 'Email', '2019-12-28 13:09:00', 1, 1),
154(NULL, 1, 2, 'Collection', '2019-10-28 10:15:00', 2, 2),
155(NULL, 6, 3, 'Email', '2019-11-28 17:34:00', 3, 3),
156(NULL, 6, 1, 'Email', '2020-03-17 18:30:00', 4, 5),
157(NULL, 6, 3, 'Email', '2020-02-27 20:30:00', 2, 5),
158(NULL, 6, 3, 'Collection', '2020-03-27 12:30:00', 6, 5),
159(NULL, 1, 2, 'Email', '2019-11-27 12:00:00', 1, 1),
160(NULL, 4, 5, 'Collection', '2019-11-25 11:30:00', 3, 9);
161
162INSERT INTO PRICE VALUES
163(NULL, NULL, 324.34, 0.8, 1),
164(NULL, NULL, 34543.3, 0.99, 2),
165(NULL, NULL, 10.56, 0.01, 3);