From 6bf4b90c90f15f4ab60833bddf5b5756d1a6b1f6 Mon Sep 17 00:00:00 2001 From: Elizabeth Alexander Hunt Date: Thu, 2 Jul 2026 11:55:17 -0700 Subject: Init --- Homework/cs5800/final/sql/queries.sql | 103 ++++++++++++++++++++++++++++++++++ 1 file changed, 103 insertions(+) create mode 100644 Homework/cs5800/final/sql/queries.sql (limited to 'Homework/cs5800/final/sql/queries.sql') 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 -- cgit v1.3