· 4 years ago · Feb 09, 2021, 03:08 PM
1
2CREATE DATABASE IF NOT EXISTS `restaurant_project` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
3USE `restaurant_project`;
4
5CREATE TABLE `city` (
6 `ID` int(11) NOT NULL,
7 `name` varchar(100) NOT NULL
8) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
9
10INSERT INTO `city` (`ID`, `name`) VALUES
11(8, 'Bydgoszcz'),
12(9, 'Warszawa');
13
14CREATE TABLE `image` (
15 `ID` int(11) NOT NULL,
16 `file_name` varchar(256) NOT NULL,
17 `description` varchar(100) NOT NULL DEFAULT 'Obrazek',
18 `restaurant.ID` int(11) DEFAULT NULL
19) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
20
21INSERT INTO `image` (`ID`, `file_name`, `description`, `restaurant.ID`) VALUES
22(93, 'eed623c9ad6ce1a4cbd777869e12b395.jpg', 'Stół', 15),
23(94, '8932afed3defbffbe2924aea58db607c.jpg', 'sala', 15),
24(95, '78aad3ede4b7e4ef2c3f5b4d83b14c07.jpg', 'bar', 16);
25
26CREATE TABLE `rating` (
27 `ID` int(11) NOT NULL,
28 `value` varchar(100) NOT NULL,
29 `quality` tinyint(4) NOT NULL
30) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
31
32INSERT INTO `rating` (`ID`, `value`, `quality`) VALUES
33(1, 'Najlepszy wybór!', 5),
34(2, 'Dobra jakość', 4),
35(3, 'Neutralna', 3),
36(4, 'Należy coś zmienić', 2),
37(5, 'Nie zbliżać się!', 1);
38
39CREATE TABLE `restaurant` (
40 `ID` int(11) NOT NULL,
41 `name` varchar(100) NOT NULL,
42 `description` longtext DEFAULT NULL,
43 `street_name` varchar(64) NOT NULL,
44 `house_number` varchar(20) NOT NULL,
45 `postcode` varchar(10) NOT NULL,
46 `city_id` int(11) NOT NULL,
47 `phone_number` varchar(30) NOT NULL,
48 `email_address` varchar(256) DEFAULT NULL,
49 `website_address` varchar(256) DEFAULT NULL
50) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
51
52INSERT INTO `restaurant` (`ID`, `name`, `description`, `street_name`, `house_number`, `postcode`, `city_id`, `phone_number`, `email_address`, `website_address`) VALUES
53(15, 'Restauracja', 'super', 'Leśna', '21', '219313', 8, '293193123', 'wwww@o2.pl', 'www.wpl.pl'),
54(16, 'Gesler', 'ekskluzywne ', 'Szybka', '1', '921919', 9, '9394934', 'wdjiad@o2.pl', 'www.wp.pl');
55CREATE TABLE `restaurant_view` (
56`restaurant.ID` int(11)
57,`restaurant.name` varchar(100)
58,`restaurant.description` longtext
59,`restaurant.street_name` varchar(64)
60,`restaurant.house_number` varchar(20)
61,`restaurant.postcode` varchar(10)
62,`restaurant.city_name` varchar(100)
63,`restaurant.city_id` int(11)
64,`restaurant.phone_number` varchar(30)
65,`restaurant.email_address` varchar(256)
66,`restaurant.website_address` varchar(256)
67,`restaurant.opinions_number` bigint(21)
68,`restaurant.icon` varchar(256)
69,`restaurant.icon.description` varchar(100)
70,`restaurant.rating_quality` decimal(26,0)
71);
72
73CREATE TABLE `review` (
74 `ID` int(11) NOT NULL,
75 `description` varchar(2000) NOT NULL,
76 `id_customer` varchar(50) DEFAULT NULL,
77 `restaurant.ID` int(11) NOT NULL,
78 `id_rating` int(11) NOT NULL,
79 `created_at` datetime DEFAULT NULL
80) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
81
82INSERT INTO `review` (`ID`, `description`, `id_customer`, `restaurant.ID`, `id_rating`, `created_at`) VALUES
83(19, 'dobrze', 'tak było', 15, 2, '2021-02-09 15:18:43'),
84(21, 'ok', 'test', 15, 1, '2021-02-09 15:23:36'),
85(22, '85', '5', 15, 3, '2021-02-09 15:43:38'),
86(23, '48', 'test', 16, 1, '2021-02-09 15:45:21');
87CREATE TABLE `review_view` (
88`ID` int(11)
89,`description` varchar(2000)
90,`id_customer` varchar(50)
91,`restaurant.ID` int(11)
92,`rating_value` varchar(100)
93,`created_at` datetime
94);
95DROP TABLE IF EXISTS `restaurant_view`;
96
97CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `restaurant_view` AS SELECT `restaurant`.`ID` AS `restaurant.ID`, `restaurant`.`name` AS `restaurant.name`, `restaurant`.`description` AS `restaurant.description`, `restaurant`.`street_name` AS `restaurant.street_name`, `restaurant`.`house_number` AS `restaurant.house_number`, `restaurant`.`postcode` AS `restaurant.postcode`, `city`.`name` AS `restaurant.city_name`, `city`.`ID` AS `restaurant.city_id`, `restaurant`.`phone_number` AS `restaurant.phone_number`, `restaurant`.`email_address` AS `restaurant.email_address`, `restaurant`.`website_address` AS `restaurant.website_address`, count(`review`.`ID`) AS `restaurant.opinions_number`, (select `image`.`file_name` from `image` where `image`.`restaurant.ID` = `restaurant`.`ID` limit 1) AS `restaurant.icon`, (select `image`.`description` from `image` where `restaurant.icon` = `image`.`file_name` limit 1) AS `restaurant.icon.description`, round(sum(`rating`.`quality`) / count(`review`.`restaurant.ID`),0) AS `restaurant.rating_quality` FROM ((((`restaurant` join `city` on(`restaurant`.`city_id` = `city`.`ID`)) left join `review` on(`restaurant`.`ID` = `review`.`restaurant.ID`)) left join `rating` on(`review`.`id_rating` = `rating`.`ID`)) left join `image` on(`restaurant`.`ID` = `image`.`restaurant.ID`)) GROUP BY `restaurant`.`ID` ;
98DROP TABLE IF EXISTS `review_view`;
99
100CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `review_view` AS SELECT `review`.`ID` AS `ID`, `review`.`description` AS `description`, `review`.`id_customer` AS `id_customer`, `review`.`restaurant.ID` AS `restaurant.ID`, `rating`.`value` AS `rating_value`, `review`.`created_at` AS `created_at` FROM (`review` join `rating` on(`review`.`id_rating` = `rating`.`ID`)) ;
101
102
103ALTER TABLE `city`
104 ADD PRIMARY KEY (`ID`),
105 ADD KEY `name` (`name`);
106
107ALTER TABLE `image`
108 ADD PRIMARY KEY (`ID`),
109 ADD KEY `id_restaurant` (`restaurant.ID`);
110
111ALTER TABLE `rating`
112 ADD PRIMARY KEY (`ID`);
113
114ALTER TABLE `restaurant`
115 ADD PRIMARY KEY (`ID`),
116 ADD KEY `fk_city_id` (`city_id`);
117
118ALTER TABLE `review`
119 ADD PRIMARY KEY (`ID`),
120 ADD KEY `id_restaurant` (`restaurant.ID`),
121 ADD KEY `id_rating` (`id_rating`);
122
123
124ALTER TABLE `city`
125 MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
126
127ALTER TABLE `image`
128 MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=96;
129
130ALTER TABLE `rating`
131 MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
132
133ALTER TABLE `restaurant`
134 MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;
135
136ALTER TABLE `review`
137 MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;
138
139
140ALTER TABLE `image`
141 ADD CONSTRAINT `image_ibfk` FOREIGN KEY (`restaurant.ID`) REFERENCES `restaurant` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
142
143ALTER TABLE `restaurant`
144 ADD CONSTRAINT `fk_city_id` FOREIGN KEY (`city_id`) REFERENCES `city` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION;
145
146ALTER TABLE `review`
147 ADD CONSTRAINT `review_ibfk` FOREIGN KEY (`restaurant.ID`) REFERENCES `restaurant` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
148 ADD CONSTRAINT `review_ibfk_2` FOREIGN KEY (`id_rating`) REFERENCES `rating` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
149COMMIT;
150