InitHEADmain

1 files changed, 103 insertions, 0 deletions

diff --git a/Homework/cs5800/final/sql/queries.sql b/Homework/cs5800/final/sql/queries.sql
new file mode 100644
index 0000000..286a1b4
--- /dev/null
+++ b/Homework/cs5800/final/sql/queries.sql
@@ -0,0 +1,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;
\ No newline at end of file