· 4 years ago · Dec 08, 2020, 05:56 PM
1SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
2SET time_zone = "+00:00";
3
4/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
5/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
6/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
7/*!40101 SET NAMES utf8mb4 */;
8
9DROP DATABASE IF EXISTS routes;
10CREATE DATABASE IF NOT EXISTS routes DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
11USE routes;
12
13DROP TABLE IF EXISTS ROUTE;
14CREATE TABLE IF NOT EXISTS ROUTE (
15 ROUTEID int(11) NOT NULL AUTO_INCREMENT,
16 ROUTETITLE varchar(30) NOT NULL,
17 DEPARTUREPOINT varchar(30) NOT NULL,
18 PRIMARY KEY (ROUTEID)
19) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
20
21INSERT INTO ROUTE (ROUTEID, ROUTETITLE, DEPARTUREPOINT) VALUES
22(1, 'Oor Wullie Route', 'GCU'),
23(2, 'Religious Route', 'Glasgow Cathedral'),
24(3, 'Art Route', 'Kelvingrove Art Gallery and Museum'),
25(4, 'Education Route', 'GCU');
26
27DROP TABLE IF EXISTS STAGE;
28CREATE TABLE IF NOT EXISTS STAGE (
29 STAGEID int(11) NOT NULL AUTO_INCREMENT,
30 STAGENUMBER int(11) NOT NULL,
31 ENDPOINT varchar(30) NOT NULL,
32 STAGELENGTH DECIMAL(5,2) NOT NULL,
33 ROUTEID int(11) NOT NULL,
34 PRIMARY KEY (STAGEID),
35 KEY ALBUMID (ROUTEID)
36) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;
37
38INSERT INTO STAGE (STAGEID, STAGENUMBER, ENDPOINT, STAGELENGTH, ROUTEID) VALUES
39(1, 1, 'City Chambers', 0.75, 1),
40(2, 2, 'Sir Chris Hoy Velodrome', 3.8, 1),
41(3, 3, 'People''s Palace', 2.7, 1),
42(4, 4, 'Riverside Museum', 5.4, 1),
43(5, 5, 'Botanic Gardens', 2.4, 1),
44(6, 6, 'GCU', 3.4, 1),
45(7, 1, 'St Andrew''s Cathedral', 1.8, 2),
46(8, 2, 'Central Mosque', 0.75, 2),
47(9, 3, 'University Chapel', 5.4, 2),
48(10, 4, 'Om Hindu Mandir', 1.3, 2),
49(11, 5, 'Gurdwara Singh Sabha', 0.6, 2),
50(12, 6, 'Quaker Meeting House', 1.2, 2),
51(13, 7, 'Glasgow Buddhist Centre', 0.35, 2),
52(14, 8, 'Garnethill Synagogue', 0.45, 2),
53(15, 9, 'Glasgow Cathderal', 3.3, 2),
54(16, 1, 'Hunterian Art Gallery', 1.2, 3),
55(17, 2, 'MacKintosh Building', 2.2, 3),
56(18, 3, 'Gallery Of Modern Art', 1.4, 3),
57(19, 4, 'St. Mungo Museum Of Religious Life & Art', 1.3, 3),
58(20, 5, 'People''s Palace', 2.0, 3),
59(21, 6, 'The Burrell Collection', 7.1, 3),
60(22, 7, 'House For An Art Lover', 2.8, 3),
61(23, 8, 'Kelvingrove Art Gallery and Museum', 5.0, 3),
62(24, 1, 'University Of Strathclyde', 0.65, 4),
63(25, 2, 'City Of Glasgow College - Riverside Campus', 1.4, 4),
64(26, 3, 'School of Simulation and Visualisation', 3.9, 4),
65(27, 4, 'Glasgow Science Centre', 0.7, 4),
66(28, 5, 'University of Glasgow', 2.4, 4),
67(29, 6, 'The Mitchell Library', 1.9, 4),
68(30, 7, 'Glasgow School Of Art', 0.9, 4),
69(31, 8, 'Royal Conservatoire Of Scotland', 0.75, 4),
70(32, 9, 'GCU', 0.6, 4);
71
72
73ALTER TABLE STAGE
74 ADD CONSTRAINT STAGE_ibfk_1 FOREIGN KEY (ROUTEID) REFERENCES ROUTE (ROUTEID);
75
76DELIMITER $$
77--
78-- Procedures
79--
80DROP PROCEDURE IF EXISTS `AddRoute`$$
81CREATE DEFINER=`root`@`localhost` PROCEDURE `AddRoute` (IN `inRouteTitle` TEXT, IN `inDeparturePoint` TEXT) BEGIN
82 INSERT INTO ROUTE (ROUTETITLE, DEPARTUREPOINT)
83 VALUES (inRouteTitle, inDeparturePoint);
84END$$
85
86DROP PROCEDURE IF EXISTS `AddStage`$$
87CREATE DEFINER=`root`@`localhost` PROCEDURE `AddStage` (IN `inStageNumber` INT, IN `inEndPoint` TEXT, IN `inStageLength` FLOAT, IN `inRouteId` INT) BEGIN
88 INSERT INTO STAGE (STAGENUMBER, ENDPOINT, STAGELENGTH, ROUTEID)
89 VALUES (inStageNumber, inEndPoint, inStageLength, inRouteId);
90END$$
91
92DROP PROCEDURE IF EXISTS `GetRoute`$$
93CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRoute` (IN `inRouteId` INT) BEGIN
94 SELECT *
95 FROM ROUTE
96 WHERE ROUTEID = inRouteId ;
97END$$
98
99DROP PROCEDURE IF EXISTS `GetRoutes`$$
100CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRoutes` () READS SQL DATA
101BEGIN
102 Select ROUTEID, ROUTETITLE, DEPARTUREPOINT From ROUTE Order By ROUTEID;
103END$$
104
105DROP PROCEDURE IF EXISTS `GetRouteStages`$$
106CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRouteStages` (IN `inRouteId` INT) NO SQL
107BEGIN
108 SELECT STAGEID, STAGENUMBER, ENDPOINT, STAGELENGTH FROM STAGE
109 WHERE ROUTEID = inRouteId
110 Order By STAGENUMBER;
111End$$
112
113DELIMITER ;
114
115/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
116/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
117/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
118