wgBTJJPh

1/* Drop old DB and create a new one */
2DROP SCHEMA IF EXISTS `ARrive`;
3CREATE DATABASE `ARrive`;
4USE `ARrive`;
5
6/* Create tables */
7CREATE TABLE Location (
8	ID INT NOT NULL AUTO_INCREMENT,
9	Latitude DOUBLE NOT NULL,
10	Longitude DOUBLE NOT NULL,
11	Address VARCHAR(64) NOT NULL,
12	
13	PRIMARY KEY (ID)
14);
15
16CREATE TABLE Image (
17	ID INT NOT NULL,
18	Data BLOB NOT NULL,
19	Location VARCHAR(30) NOT NULL,
20	Description VARCHAR(99) NOT NULL,
21	
22	PRIMARY KEY (ID)
23);
24
25CREATE TABLE Purchased_Cities (
26	ID INT NOT NULL AUTO_INCREMENT,
27	Customer_ID INT NOT NULL,
28	City_ID INT NOT NULL,
29	
30	PRIMARY KEY (ID)
31);
32
33CREATE TABLE Customer (
34	ID INT NOT NULL AUTO_INCREMENT,
35	First_Name VARCHAR(32) NOT NULL,
36	Last_Name VARCHAR(32) NOT NULL,
37	Email_Address VARCHAR(66) NOT NULL,
38	
39	PRIMARY KEY (ID)
40);
41
42CREATE TABLE Customer_Locations_Join (
43	ID INT NOT NULL AUTO_INCREMENT,
44	Customer_ID INT,
45	Location_ID INT,
46	Card INT,
47	
48	PRIMARY KEY (`ID`)
49);
50
51CREATE TABLE Card (
52	ID INT NOT NULL AUTO_INCREMENT,
53	Number VARCHAR(16) NOT NULL,
54	Name_On_Card VARCHAR(15) NOT NULL,
55	
56	PRIMARY KEY (ID)
57);
58
59/* Set storage engine for relational view (Important) */
60ALTER TABLE `Location` ENGINE = InnoDB;
61ALTER TABLE `Image` ENGINE = InnoDB;
62ALTER TABLE `Purchased_Cities` ENGINE = InnoDB;
63ALTER TABLE `Customer` ENGINE = InnoDB;
64ALTER TABLE `Customer_Locations_Join` ENGINE = InnoDB;
65ALTER TABLE `Card` ENGINE = InnoDB;
66
67/* Assign foreign keys to tables */
68ALTER TABLE `Customer_Locations_Join` ADD CONSTRAINT `Customer_ID_FK` FOREIGN KEY (`Customer_ID`) 
69REFERENCES `Customer`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
70ALTER TABLE `Customer_Locations_Join` ADD CONSTRAINT `Location_ID_FK` FOREIGN KEY (`Location_ID`)
71REFERENCES `Location`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
72ALTER TABLE `Customer_Locations_Join` ADD CONSTRAINT `Card_FK` FOREIGN KEY (`Card`)
73REFERENCES `Card`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
74
75ALTER TABLE `Purchased_Cities` ADD CONSTRAINT `Customer_ID_FoK` FOREIGN KEY (`Customer_ID`)
76REFERENCES `Customer`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
77ALTER TABLE `Purchased_Cities` ADD CONSTRAINT `Location_ID_FoK` FOREIGN KEY (`City_ID`)
78REFERENCES `Location`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
79
80ALTER TABLE `Image` ADD CONSTRAINT `ID_FK` FOREIGN KEY (`ID`)
81REFERENCES `Location`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
82
83/* Populate tables */
84INSERT INTO `Customer` (`ID`, `First_Name`, `Last_Name`, `Email_Address`) VALUES ('1', 'McCauley', 'Hallam', 'mccauleyhallam@hotmail.com');
85INSERT INTO `Customer` (`ID`, `First_Name`, `Last_Name`, `Email_Address`) VALUES ('2', 'Alexis', 'Turner', 'alexisturner@gmail.com');
86INSERT INTO `Customer` (`ID`, `First_Name`, `Last_Name`, `Email_Address`) VALUES ('3', 'Noah', 'Jones', 'noahjones@gmail.com');
87
88INSERT INTO `Card` (`ID`, `Number`, `Name_On_Card`) VALUES ('1', '4871099563721273', 'McCauley Hallam');
89INSERT INTO `Card` (`ID`, `Number`, `Name_On_Card`) VALUES ('2', '4850340092677726', 'Alexis Turner');
90INSERT INTO `Card` (`ID`, `Number`, `Name_On_Card`) VALUES ('3', '4255805492194796', 'Noah Jones');
91
92INSERT INTO `Location` (`ID`, `Latitude`, `Longitude`, `Address`) VALUES ('1', '50.7234737', '-3.5319809999999734', 'The Guildhall, High Street, Exeter, UK');
93INSERT INTO `Location` (`ID`, `Latitude`, `Longitude`, `Address`) VALUES ('2', '50.7218301', '-3.5348527999999533', 'St Nicholas Priory, Fore Street, Exeter, UK');
94INSERT INTO `Location` (`ID`, `Latitude`, `Longitude`, `Address`) VALUES ('3', '50.722478', '-3.5373180000000275', 'St Bartholomew Cemetery, Exe Street, Exeter, UK');
95
96/* Populate table with foreign keys */
97INSERT INTO `Customer_Locations_Join` (`ID`, `Customer_ID`, `Location_ID`, `Card`) VALUES ('1', '2', '1', '2');
98INSERT INTO `Customer_Locations_Join` (`ID`, `Customer_ID`, `Location_ID`, `Card`) VALUES ('2', '1', '3', '1');
99INSERT INTO `Customer_Locations_Join` (`ID`, `Customer_ID`, `Location_ID`, `Card`) VALUES ('3', '3', '2', '3');
100
101INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('1', '1', '2');
102INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('2', '1', '1');
103INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('3', '1', '3');
104INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('4', '3', '1');
105INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('5', '3', '2');
106INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('6', '2', '3');
107INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('7', '2', '1');