1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
|
USE LibraryMiniWorld;
INSERT INTO Person (PersonID, Name, Address, Phone, Email) VALUES
(1, 'John Pi', '314 Main St Applewood CA 31415', '314-159-2565', 'johnpi@bruh.com'),
(2, 'Leonhard Euler', '271 Fake Cr Logan UT 27183', '271-828-1828', 'euler@math.org'),
(3, 'Ada Lovelace', '422 Ada Ave Salt Lake City UT 38382', '121-232-3443', 'ada@programming.edu'),
(4, 'Alan Turing', '314 Turing Rd Reno NV 89123', '123-571-1131', 'alan@computerscience.com'),
(5, 'Grace Hopper', '771 Hopper Ct Seattle WA 22331', '223-414-4488', 'grace@computerscience.com'),
(6, 'Charles Babbage', '314 Babbage Rd Reno NV 89123', '881-222-1818', 'bbage @computerscience.com'),
(7, 'John von Neumann', '442 Neumann St Idaho Falls ID 48323', '881-222-1818', 'john@neumann.com'),
(8, 'John Conway', '888 Conway Rd Reno NV 89123', '102-103-1004', 'game@oflife.fun'),
(9, 'Stephen Hawking', '111 Hawking Ave Seattle WA 12523', '101-102-1010', 'hawking@radiation.com');
INSERT INTO Employee (PersonID, StartDate, Salary, EmployeeType) VALUES
(1, '2001-04-02', 40000, 'Manager'),
(2, '2001-04-02', 41000, 'Manager'),
(3, '2001-04-02', 30000, 'Librarian'),
(4, '2010-06-25', 29000, 'Librarian'),
(5, '2022-12-08', 25000, 'Librarian'),
(6, '2022-11-08', 26000, 'Librarian');
INSERT IGNORE INTO Manager (EmployeeID)
SELECT PersonID
FROM Employee
WHERE EmployeeType = 'Manager';
INSERT IGNORE INTO Librarian (EmployeeID)
SELECT PersonID
FROM Employee
WHERE EmployeeType = 'Librarian';
INSERT INTO Manages(ManagerID, LibrarianID) VALUES
(1, 3),
(2, 4),
(2, 5),
(2, 6);
INSERT INTO Customer (PersonID, JoinDate) VALUES
(1, '2001-12-12'),
(3, '2011-10-25'),
(7, '2012-10-25'),
(8, '2013-10-25'),
(9, '2022-12-08');
INSERT INTO LibraryCard (CardNo, CustomerID, IssueDate, ExpiryDate) VALUES
(1, 1, '2021-12-12', '2022-12-12'),
(2, 3, '2020-12-25', '2021-12-25'),
(3, 7, '2020-09-15', '2022-09-25'),
(4, 8, '2022-01-25', '2023-01-25'),
(5, 9, '2015-12-25', '2023-01-01'),
(6, 3, '2021-12-26', '2022-12-26');
-- SET sql_safe_updates=0;
--
-- UPDATE Customer INNER JOIN LibraryCard
-- AS lc ON lc.CustomerID = Customer.PERSONID
-- AND lc.ExpiryDate =
-- (SELECT MAX(ExpiryDate) FROM LibraryCard
-- GROUP BY CustomerID HAVING CustomerID=lc.CustomerID)
-- SET Customer.CardNo=lc.CardNo;
--
-- SET sql_safe_updates=1; (commented out because we no longer store card number on the customer)
INSERT INTO Creator (CreatorID, Name) VALUES
(1, 'Fyodor Doestoyevsky'),
(2, 'J.K. Rowling'),
(3, 'Brandon Sanderson'),
(4, 'Christopher Paolini'),
(5, 'James Clear'),
(6, 'William Shakespeare'),
(7, 'Jim Dale'),
(8, 'Stefen Fangmeier'),
(9, 'Jennifer Nielsen');
INSERT INTO Media (MediaID, DeweyNumber, Name, MediaType, CreatedYear) VALUES
(1, '891.73','Crime and Punishment', 'Text', 2003),
(2, '822.33','Hamlet', 'Text', 2003),
(3, '823.91','Harry Potter and the Sorcerers Stone', 'Text', 1997),
(4, '823.91','Harry Potter and the Sorcerers Stone', 'Audio', 1997),
(5, '813.91','Eragon', 'Text', 2002),
(6, '813.91','Eragon', 'Video', 2006),
(7, '813.6','Elantris', 'Text', 2005),
(8, '155.2','Atomic Habits', 'Audio', 2018),
(9, '813.6', 'Lines of Courage', 'Text', 2022);
INSERT INTO Text (MediaID, Pages) VALUES
(1, 492),
(2, 104),
(3, 223),
(5, 509),
(7, 496),
(9, 400);
INSERT INTO Audio (MediaID, Playtime) VALUES
(4, 29880),
(8, 13380);
INSERT INTO Video (MediaID, Runtime) VALUES
(6, 6240);
INSERT INTO Genre (GenreID, Name) VALUES
(1, 'Fantasy'),
(2, 'Science Fiction'),
(3, 'Romance'),
(4, 'Drama'),
(5, 'Non-Fiction'),
(6, 'Fiction'),
(7, 'War Story');
INSERT INTO MediaGenre (MediaID, GenreID) VALUES
(1, 4),
(2, 4),
(3, 1),
(4, 1),
(5, 2),
(6, 2),
(7, 2),
(8, 5),
(9, 6),
(9, 7);
INSERT INTO Tag (TagID, Name) VALUES
(1, 'Fun'),
(2, 'Good for Kids'),
(3, 'Young Adult'),
(4, 'Sad'),
(5, 'Educational'),
(6, 'Classic'),
(7, 'Popular'),
(8, 'New'),
(9, 'Powerful');
INSERT INTO MediaTag (MediaID, TagID) VALUES
(1, 6),
(3, 1),
(3, 2),
(3, 3),
(3, 7),
(7, 3),
(8, 5),
(8, 8),
(9, 8),
(9, 9);
INSERT INTO MediaCreator (MediaID, CreatorID) VALUES
(1, 1),
(2, 6),
(3, 2),
(4, 2),
(4, 7),
(5, 4),
(6, 4),
(6, 8),
(7, 3),
(8, 5),
(9, 9);
INSERT INTO MediaInstance (InstanceID, LibrarianID, MediaID, DateAdded) VALUES
(1, 4, 3, '2004-05-08'),
(2, 4, 4, '2004-05-08'),
(3, 4, 5, '2004-05-08'),
(4, 4, 5, '2004-05-08'),
(5, 4, 5, '2004-05-08'),
(6, 5, 6, '2011-03-31'),
(7, 4, 3, '2012-05-08'),
(8, 5, 4, '2018-10-07'),
(9, 5, 5, '2019-02-18'),
(10, 4, 6, '2020-01-20'),
(11, 4, 6, '2020-01-20'),
(12, 4, 6, '2020-01-20'),
(13, 4, 7, '2021-01-20'),
(14, 6, 8, '2021-03-20'),
(15, 6, 9, '2022-05-20'),
(16, 6, 9, '2022-05-20'),
(17, 5, 4, '2009-05-08');
INSERT INTO Borrow (LibraryCardNo, InstanceID, LibrarianID, IsReturned, BorrowDate, ReturnDate) VALUES
(1, 1, 3, 0, CURDATE(), '2022-12-24'),
(1, 2, 3, 0, CURDATE(), '2022-12-24'),
(2, 10, 5, 1, '2021-10-01', '2022-10-24'),
(3, 1, 5, 1, '2022-01-01', '2022-01-24'),
(3, 2, 6, 1, '2022-01-01', '2022-01-24'),
(4, 15, 4, 0, '2022-10-01', '2022-10-24'),
(4, 6, 4, 1, '2021-12-01', '2022-12-07');
INSERT INTO CustomerRate (CustomerID, MediaID, Stars, Review) VALUES
(1, 3, 5, 'This book was great!'),
(1, 4, 5, 'The audiobook rendition was perfect too!'),
(8, 6, 2, 'The movie did not live up to the book in my opinion.');
|
