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
|
-- Note that we are not using the database in a transaction, so if you run this script multiple times, you will get duplicate data.
-- This causing errors when you try to run the queries more than once.
-- If you'd like to run it more than once, just rerun the create and insert scripts as the create script will drop the database and recreate it.
-- Let's create some dummy data as we go through the functional requirements!
INSERT INTO Person (PersonID, Name, Address, Phone, Email) VALUES
(5800, 'Soukaina Filali Boubrahimi', 'Old Main, Logan, UT 84322', '435-123-1234', 'soukaina@usu.edu');
INSERT INTO Customer (PersonID, JoinDate) VALUES
(5800, CURDATE());
-- FUNCTIONAL REQUIREMENTS
-- 1) Customers should be able to obtain a Library Card, and renew it when it expires.
-- 2) Customers should be able to view only the borrowing history of their own reservations.
-- 3) A Librarian should be able to mark a media as available after the customer has returned the book.
-- 4) A Librarian should be able to view which media the library has checked out to a customer.
-- 5) Users of the database should be able to search for media by title, creator name, tag etc.
-- 6) Users should be able to find the availability of a piece of media and how much stock there is of it in the Library
-- 7) A librarian should be able to renew a borrowing
-- 8) The database should be able to track the employees of the library, including the managers and the librarians they manage.
-- 9) Users should be able to leave a rating for the media they have checked out.
-- 10) The database should track the transactions overseen by each librarian and which librarian added each piece of media into the inventory.
-- 11) A customer should be able to reserve a media item if there is one available
-- 1) Registering a new library card to a customer
INSERT INTO LibraryCard (CardNo, CustomerID, IssueDate, ExpiryDate) VALUES
(100, 5800, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 2 YEAR));
-- 6) Finding the availability of a piece of media and how much stock there is of it in the Library via a view
SELECT * FROM MediaQuery WHERE Name = "Harry Potter and the Sorcerers Stone";
-- 5) Searching for media by title, creator name, tag etc., additionally satisfies 6) again
-- By Title
CALL searchMedia("Harry Potter");
CALL searchMedia("Crime");
-- By Creator(s)
CALL searchMedia("Rowling");
CALL searchMedia("Rowling Dale");
-- By Genre(s)
CALL searchMedia("Fantasy Drama");
-- By Tag(s)
CALL searchMedia("Kids Classic");
-- 11) Borrow an instance of Harry Potter (id 3) with our Library Card through Librarian 6
SET @instance = NULL;
CALL borrow(100, 3, 6, DATE_ADD(CURDATE(), INTERVAL 2 WEEK), @instance);
-- Doing it again will cause the BorrowCheck trigger to trip - there was only one copy of Harry Potter available
CALL borrow(100, 3, 6, DATE_ADD(CURDATE(), INTERVAL 2 WEEK), @instance);
-- 2) Viewing our own reservations, also satisfies 4
CALL borrowedHistory(5800);
-- 8) We didn't enjoy our experience with Librarian 6, so we want to complain to the manager. Let's find who that is :)
SELECT Person.Phone, Person.Name FROM Manages
INNER JOIN Person ON Manages.ManagerID = Person.PersonID
WHERE LibrarianID = 6;
-- 9) Leaving a rating for the media we have checked out
INSERT INTO CustomerRate (CustomerID, MediaID, Stars, Review) VALUES
(5800, 3, 5, "I loved the book! But for some reason the Librarian was really rude to me.");
-- But it looks like we can't leave slanderous rating for a media we haven't checked out
INSERT INTO CustomerRate (CustomerID, MediaID, Stars, Review) VALUES
(5800, 1, 1, "Just awful!");
-- 3) Marking a media as available after the customer has returned the book
UPDATE Borrow SET IsReturned = 1 WHERE InstanceID = @instance AND NOT IsReturned;
-- 10) Finding the media instances that a librarian has added to the inventory
SELECT * FROM MediaInstance
INNER JOIN Media ON MediaInstance.MediaID = Media.MediaID
WHERE LibrarianID = 4;
-- Finding the borrowing transactions overseen by a librarian
SELECT Media.Name AS MediaName, MediaInstance.InstanceID, LibraryCardNo, IsReturned,
BorrowDate, CustomerPerson.Name as CustomerName, CustomerPerson.Phone as CustomerPhone,
CustomerPerson.Email as CustomerEmail
FROM Borrow
INNER JOIN MediaInstance ON MediaInstance.InstanceID = Borrow.InstanceID
INNER JOIN Media ON MediaInstance.MediaID = Media.MediaID
INNER JOIN Person AS LibraryPerson ON Borrow.LibrarianID = LibraryPerson.PersonID
INNER JOIN LibraryCard ON Borrow.LibraryCardNo = LibraryCard.CardNo
INNER JOIN Customer ON LibraryCard.CustomerID = Customer.PersonID
INNER JOIN Person AS CustomerPerson ON Customer.PersonID = CustomerPerson.PersonID
WHERE Borrow.LibrarianID = 4;
-- 1b) Renewing/registering a new library card
-- We can't register a new card if one already exists for a customer that hasn't expired. Trigger will trip.
INSERT INTO LibraryCard (CardNo, CustomerID, IssueDate, ExpiryDate) VALUES
(100, 5800, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 2 YEAR));
-- Simulate "expiration"
UPDATE LibraryCard
SET ExpiryDate = DATE_SUB(CURDATE(), INTERVAL 1 YEAR),
IssueDate = DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
WHERE CardNo = 100;
-- Now we can register for a new card
INSERT INTO LibraryCard (CardNo, CustomerID, IssueDate, ExpiryDate) VALUES
(101, 5800, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 2 YEAR));
-- Delete the card
DELETE FROM LibraryCard WHERE CardNo = 101;
-- Now renew the previously expired card
UPDATE LibraryCard
SET ExpiryDate = DATE_ADD(CURDATE(), INTERVAL 2 YEAR)
WHERE CardNo = 1000;
|
