· 4 years ago · Sep 09, 2021, 06:56 PM
1DROP DATABASE IF EXISTS bookstore;
2CREATE DATABASE bookstore;
3USE bookstore;
4
5CREATE TABLE `admin` (
6 `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
7 `username` varchar(255) NOT NULL,
8 `password` varchar(255) NOT NULL,
9 `email` varchar(255) NOT NULL,
10 `created_at` datetime NOT NULL,
11 `updated_at` datetime NOT NULL
12) ENGINE=InnoDB DEFAULT CHARSET=utf8;
13
14INSERT INTO `admin` (`id`, `username`, `password`, `email`, `created_at`, `updated_at`)
15 VALUES ('1', 'rudra', '5f4dcc3b5aa765d61d8327deb882cf99', 'hello@rudra0x01.xyz', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
16
17CREATE TABLE `author` (
18 `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
19 `name` TEXT NOT NULL,
20 `created_at` datetime NOT NULL,
21 `updated_at` datetime NOT NULL
22) ENGINE=InnoDB DEFAULT CHARSET=utf8;
23
24INSERT INTO `author` (`id`, `name`, `created_at`, `updated_at`)
25 VALUES ('1', 'J. K. Rowling', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
26INSERT INTO `author` (`id`, `name`, `created_at`, `updated_at`)
27 VALUES ('2', 'Masashi Kishimoto', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
28INSERT INTO `author` (`id`, `name`, `created_at`, `updated_at`)
29 VALUES ('3', 'Akira Toriyama', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
30INSERT INTO `author` (`id`, `name`, `created_at`, `updated_at`)
31 VALUES ('4', 'Koyoharu Gotouge', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
32
33CREATE TABLE `book` (
34 `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
35 `title` TEXT NOT NULL,
36 `author` int(11) NOT NULL,
37 `image_url` TEXT NOT NULL,
38 `price` REAL NOT NULL, /* floating point number */
39 `published_at` DATE NOT NULL,
40 `created_at` DATETIME NOT NULL,
41 `updated_at` DATETIME NOT NULL
42) ENGINE=InnoDB DEFAULT CHARSET=utf8;
43
44INSERT INTO `book` (`id`, `title`, `author`, `image_url`, `price`, `published_at`, `created_at`, `updated_at`)
45 VALUES ('1', 'Harry Potter and the Philosopher''s Stone', '1', 'https://images.moviesanywhere.com/143cdb987186a1c8f94d4f18de211216/fdea56fa-2703-47c1-8da8-70fc5382e1ea.jpg?h=375&resize=fit&w=250', '12.99', '1997-06-26 00:00:00', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
46INSERT INTO `book` (`id`, `title`, `author`, `image_url`, `price`, `published_at`, `created_at`, `updated_at`)
47 VALUES ('2', 'Naruto', '2', 'https://upload.wikimedia.org/wikipedia/en/9/94/NarutoCoverTankobon1.jpg', '10.99', '1999-09-21 00:00:00', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
48INSERT INTO `book` (`id`, `title`, `author`, `image_url`, `price`, `published_at`, `created_at`, `updated_at`)
49 VALUES ('3', 'Dragon Ball', '3', 'https://m.media-amazon.com/images/M/MV5BMGMyOThiMGUtYmFmZi00YWM0LWJiM2QtZGMwM2Q2ODE4MzhhXkEyXkFqcGdeQXVyMjc2Nzg5OTQ@._V1_FMjpg_UX1000_.jpg', '9.99', '1986-02-26 00:00:00', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
50INSERT INTO `book` (`id`, `title`, `author`, `image_url`, `price`, `published_at`, `created_at`, `updated_at`)
51 VALUES ('4', 'SAND LAND', '3', 'https://encrypted-tbn3.gstatic.com/images?q=tbn:ANd9GcThMwyoDq9sUuoVlCk_BRjDUoniiMvgZ6hcY639vHDr-elKkVJd', '8.99', '1999-09-21 00:00:00', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
52INSERT INTO `book` (`id`, `title`, `author`, `image_url`, `price`, `published_at`, `created_at`, `updated_at`)
53 VALUES ('5', 'Demon Slayer', '4', 'https://upload.wikimedia.org/wikipedia/en/thumb/0/09/Demon_Slayer_-_Kimetsu_no_Yaiba%2C_volume_1.jpg/220px-Demon_Slayer_-_Kimetsu_no_Yaiba%2C_volume_1.jpg', '10.99', '2016-06-03 00:00:00', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
54
55CREATE INDEX `book_author_index` ON `book` (`author`);
56ALTER TABLE `book` ADD CONSTRAINT `author_id` FOREIGN KEY (`author`) REFERENCES `author`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
57
58CREATE TABLE `review` (
59 `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
60 `book` int(11),
61 `rating` int(11) NOT NULL,
62 `comment` TEXT NOT NULL,
63 `created_at` DATETIME NOT NULL,
64 `updated_at` DATETIME NOT NULL
65) ENGINE=InnoDB DEFAULT CHARSET=utf8;
66
67INSERT INTO `review` (`id`, `book`, `rating`, `comment`, `created_at`, `updated_at`)
68 VALUES ('1', '1', '5', 'Great book!', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
69INSERT INTO `review` (`id`, `book`, `rating`, `comment`, `created_at`, `updated_at`)
70 VALUES ('2', '2', '4', 'Good book!', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
71INSERT INTO `review` (`id`, `book`, `rating`, `comment`, `created_at`, `updated_at`)
72 VALUES ('3', '3', '3', 'Not bad!', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
73INSERT INTO `review` (`id`, `book`, `rating`, `comment`, `created_at`, `updated_at`)
74 VALUES ('4', '4', '2', 'Not good!', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
75INSERT INTO `review` (`id`, `book`, `rating`, `comment`, `created_at`, `updated_at`)
76 VALUES ('5', '5', '1', 'Terrible!', '2021-09-03 00:00:00', '2021-09-03 00:00:00');
77
78CREATE INDEX `review_book_index` ON `review` (`book`);
79ALTER TABLE `review` ADD CONSTRAINT `book_id` FOREIGN KEY (`book`) REFERENCES `book`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;