summaryrefslogtreecommitdiff
path: root/Homework/cs5800/final
diff options
context:
space:
mode:
Diffstat (limited to 'Homework/cs5800/final')
-rw-r--r--Homework/cs5800/final/README.md4
-rw-r--r--Homework/cs5800/final/sql/create.sql312
-rw-r--r--Homework/cs5800/final/sql/insert.sql191
-rw-r--r--Homework/cs5800/final/sql/queries.sql103
4 files changed, 610 insertions, 0 deletions
diff --git a/Homework/cs5800/final/README.md b/Homework/cs5800/final/README.md
new file mode 100644
index 0000000..ce40259
--- /dev/null
+++ b/Homework/cs5800/final/README.md
@@ -0,0 +1,4 @@
+# Library Mini World
+This contains the create, insert, and example queries for the database implementation
+
+These three files are found under the sql folder
diff --git a/Homework/cs5800/final/sql/create.sql b/Homework/cs5800/final/sql/create.sql
new file mode 100644
index 0000000..c0cf0b7
--- /dev/null
+++ b/Homework/cs5800/final/sql/create.sql
@@ -0,0 +1,312 @@
+DROP DATABASE IF EXISTS LibraryMiniWorld;
+
+CREATE DATABASE IF NOT EXISTS LibraryMiniWorld;
+USE LibraryMiniWorld;
+
+CREATE TABLE Person (
+ PersonID INTEGER PRIMARY KEY AUTO_INCREMENT,
+ Phone VARCHAR(20) NOT NULL,
+ Name VARCHAR(50) NOT NULL,
+ Email VARCHAR(100) NOT NULL,
+ Address VARCHAR(255) NOT NULL
+);
+
+CREATE TABLE Customer (
+ PersonID INTEGER PRIMARY KEY,
+ JoinDate DATE NOT NULL,
+ FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
+);
+
+CREATE TABLE LibraryCard (
+ CardNo INTEGER PRIMARY KEY AUTO_INCREMENT,
+ CustomerID INTEGER NOT NULL,
+ IssueDate DATE NOT NULL,
+ ExpiryDate DATE NOT NULL,
+ FOREIGN KEY (CustomerID) REFERENCES Customer(PersonID)
+);
+
+CREATE TABLE Employee (
+ PersonID INTEGER PRIMARY KEY,
+ StartDate DATE NOT NULL,
+ Salary INTEGER NOT NULL,
+ EmployeeType VARCHAR(20) NOT NULL, -- Either "Librarian" or "Manager"
+ FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
+);
+
+CREATE TABLE Manager (
+ EmployeeID INTEGER PRIMARY KEY,
+ FOREIGN KEY (EmployeeID) REFERENCES Employee(PersonID)
+);
+
+CREATE TABLE Librarian (
+ EmployeeID INTEGER PRIMARY KEY,
+ FOREIGN KEY (EmployeeID) REFERENCES Employee(PersonID)
+);
+
+CREATE TABLE Manages (
+ ManagerID INTEGER NOT NULL,
+ LibrarianID INTEGER NOT NULL,
+ FOREIGN KEY (ManagerID) REFERENCES Manager(EmployeeID),
+ FOREIGN KEY (LibrarianID) REFERENCES Librarian(EmployeeID),
+ PRIMARY KEY (ManagerID, LibrarianID)
+);
+
+CREATE TABLE Media (
+ MediaID INTEGER PRIMARY KEY AUTO_INCREMENT,
+ DeweyNumber VARCHAR(20) NOT NULL,
+ Name VARCHAR(100) NOT NULL,
+ MediaType VARCHAR(20) NOT NULL, -- Either "Text", "Audio", or "Video"
+ CreatedYear YEAR NOT NULL
+);
+
+CREATE TABLE Genre (
+ GenreID INTEGER PRIMARY KEY AUTO_INCREMENT,
+ Name VARCHAR(50) NOT NULL UNIQUE
+);
+
+CREATE TABLE Tag (
+ TagID INTEGER PRIMARY KEY AUTO_INCREMENT,
+ Name VARCHAR(50) NOT NULL UNIQUE
+);
+
+CREATE TABLE Creator (
+ CreatorID INTEGER PRIMARY KEY AUTO_INCREMENT,
+ Name VARCHAR(50) NOT NULL
+);
+
+CREATE TABLE MediaGenre (
+ MediaID INTEGER NOT NULL,
+ GenreID INTEGER NOT NULL,
+ FOREIGN KEY (MediaID) REFERENCES Media(MediaID),
+ FOREIGN KEY (GenreID) REFERENCES Genre(GenreID),
+ PRIMARY KEY (MediaID, GenreID)
+);
+
+CREATE TABLE MediaTag (
+ MediaID INTEGER NOT NULL,
+ TagID INTEGER NOT NULL,
+ FOREIGN KEY (MediaID) REFERENCES Media(MediaID),
+ FOREIGN KEY (TagID) REFERENCES Tag(TagID),
+ PRIMARY KEY (MediaID, TagID)
+);
+
+CREATE TABLE MediaCreator (
+ MediaID INTEGER NOT NULL,
+ CreatorID INTEGER NOT NULL,
+ FOREIGN KEY (MediaID) REFERENCES Media(MediaID),
+ FOREIGN KEY (CreatorID) REFERENCES Creator(CreatorID),
+ PRIMARY KEY (MediaID, CreatorID)
+);
+
+CREATE TABLE Text (
+ MediaID INTEGER PRIMARY KEY,
+ Pages INTEGER NOT NULL,
+ FOREIGN KEY (MediaID) REFERENCES Media(MediaID)
+);
+
+CREATE TABLE Audio (
+ MediaID INTEGER PRIMARY KEY,
+ Playtime INTEGER NOT NULL,
+ FOREIGN KEY (MediaID) REFERENCES Media(MediaID)
+);
+
+CREATE TABLE Video (
+ MediaID INTEGER PRIMARY KEY,
+ Runtime INTEGER NOT NULL,
+ FOREIGN KEY (MediaID) REFERENCES Media(MediaID)
+);
+
+CREATE TABLE MediaInstance (
+ InstanceID INTEGER PRIMARY KEY AUTO_INCREMENT,
+ LibrarianID INTEGER NOT NULL,
+ MediaID INTEGER NOT NULL,
+ DateAdded DATE NOT NULL,
+ FOREIGN KEY (MediaID) REFERENCES Media(MediaID),
+ FOREIGN KEY (LibrarianID) REFERENCES Librarian(EmployeeID)
+);
+
+CREATE TABLE CustomerRate (
+ CustomerID INTEGER NOT NULL,
+ MediaID INTEGER NOT NULL,
+ Stars INTEGER NOT NULL CHECK (Stars >= 0 AND Stars <= 5),
+ Review VARCHAR(690),
+ FOREIGN KEY (CustomerID) REFERENCES Customer(PersonID),
+ FOREIGN KEY (MediaID) REFERENCES Media(MediaID),
+ PRIMARY KEY (CustomerID, MediaID)
+);
+
+CREATE TABLE Borrow (
+ LibraryCardNo INTEGER NOT NULL,
+ InstanceID INTEGER NOT NULL,
+ LibrarianID INTEGER NOT NULL,
+ IsReturned BOOLEAN NOT NULL,
+ BorrowDate DATE NOT NULL,
+ ReturnDate DATE NOT NULL,
+ CONSTRAINT BorrowDateBeforeReturn CHECK (BorrowDate <= ReturnDate),
+ FOREIGN KEY (LibraryCardNo) REFERENCES LibraryCard(CardNo),
+ FOREIGN KEY (InstanceID) REFERENCES MediaInstance(InstanceID),
+ FOREIGN KEY (LibrarianID) REFERENCES Librarian(EmployeeID),
+ PRIMARY KEY (InstanceID, BorrowDate)
+);
+
+ALTER TABLE Creator ADD FULLTEXT (Name);
+ALTER TABLE Tag ADD FULLTEXT (Name);
+ALTER TABLE Genre ADD FULLTEXT (Name);
+ALTER TABLE Media ADD FULLTEXT (Name, MediaType, DeweyNumber);
+
+
+CREATE VIEW MediaQuery
+AS
+ SELECT me.*,
+ GROUP_CONCAT(DISTINCT(c.name)) AS Creators,
+ GROUP_CONCAT(DISTINCT(t.name)) AS Tags,
+ GROUP_CONCAT(DISTINCT(g.name)) AS Genres,
+ COUNT(DISTINCT(mi.InstanceID)) AS Copies,
+ COUNT(DISTINCT(mi.InstanceID)) - COUNT(DISTINCT(b.InstanceID)) AS AvailableCopies
+ FROM Media AS me
+ INNER JOIN MediaInstance AS mi ON me.MediaID = mi.MediaID
+ LEFT JOIN MediaGenre AS mg ON me.MediaID = mg.MediaID
+ LEFT JOIN Genre AS g ON mg.GenreID = g.GenreID
+ LEFT JOIN MediaTag AS mt ON me.MediaID = mt.MediaID
+ LEFT JOIN Tag AS t ON mt.TagID = t.TagID
+ LEFT JOIN MediaCreator AS mc ON me.MediaID = mc.MediaID
+ LEFT JOIN Creator AS c ON mc.CreatorID = c.CreatorID
+ LEFT JOIN Borrow AS b ON b.InstanceID = mi.InstanceID AND b.IsReturned
+ GROUP BY me.MediaID;
+
+
+DELIMITER //
+CREATE TRIGGER BorrowCheck
+BEFORE INSERT ON Borrow
+FOR EACH ROW
+BEGIN
+ IF NEW.InstanceID IN
+ (SELECT InstanceID FROM Borrow AS b
+ WHERE New.BorrowDate <= b.ReturnDate
+ AND New.ReturnDate >= b.BorrowDate
+ AND NOT b.IsReturned)
+ THEN
+ SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Media instance is already borrowed';
+ END IF;
+END;
+//
+
+DELIMITER //
+CREATE TRIGGER BorrowNowOrFuture
+BEFORE INSERT ON Borrow
+FOR EACH ROW
+BEGIN
+ IF NEW.BorrowDate < CURDATE() AND NEW.ReturnDate >= CURDATE() THEN
+ SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Borrow date cannot be in the past unless return date is as well';
+ END IF;
+END;
+//
+
+DELIMITER //
+CREATE TRIGGER CustomerCantRegisterForNewCardWhenHasOneNotExpired
+BEFORE INSERT ON LibraryCard
+FOR EACH ROW
+BEGIN
+ IF NEW.ExpiryDate >= CURDATE() AND NEW.CustomerID IN
+ (SELECT CustomerID FROM LibraryCard WHERE ExpiryDate >= CURDATE())
+ THEN
+ SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer already has an active library card';
+ END IF;
+END;
+//
+
+DELIMITER //
+CREATE TRIGGER LibraryCardNotExpired
+BEFORE INSERT ON Borrow
+FOR EACH ROW
+BEGIN
+ IF NOT EXISTS
+ (SELECT CardNo FROM LibraryCard
+ WHERE CardNo = NEW.LibraryCardNo
+ AND ExpiryDate >= NEW.BorrowDate)
+ THEN
+ SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Library card is expired';
+ END IF;
+END;
+//
+
+DELIMITER //
+CREATE TRIGGER CustomerCanOnlyRateMediaTheyBorrowed
+BEFORE INSERT ON CustomerRate
+FOR EACH ROW
+BEGIN
+ IF New.CustomerID NOT IN
+ (SELECT lc.CustomerID
+ FROM Borrow AS b, MediaInstance AS i, Media AS m, LibraryCard AS lc
+ WHERE b.InstanceID = i.InstanceID
+ AND b.LibraryCardNo = lc.CardNo
+ AND i.MediaID = m.MediaID
+ AND m.MediaID = New.MediaID)
+ THEN
+ SIGNAL SQLSTATE '45000'
+ SET MESSAGE_TEXT = 'Customer can only rate media they borrowed';
+ END IF;
+END;
+//
+
+DELIMITER //
+CREATE PROCEDURE
+ borrowedHistory( customerID INT )
+BEGIN
+ SELECT mi.MediaID, me.Name, b.BorrowDate, b.ReturnDate, b.IsReturned, l.Name AS Librarian
+ FROM MediaInstance AS mi, Media AS me, Borrow AS b, Person AS l, LibraryCard AS lc
+ WHERE mi.InstanceID = b.InstanceID
+ AND mi.MediaID = me.MediaID
+ AND b.LibrarianID = l.PersonID
+ AND b.LibraryCardNo = lc.CardNo
+ AND lc.CustomerID = customerID;
+END;
+//
+
+DELIMITER //
+CREATE PROCEDURE
+ searchMedia (searchQuery VARCHAR(255))
+ BEGIN
+ SELECT q.MediaID, q.DeweyNumber, q.Name, q.MediaType, q.CreatedYear,
+ q.Creators, q.Tags, q.Genres, q.Copies, q.AvailableCopies
+ FROM (
+ SELECT me.*,
+ GROUP_CONCAT(DISTINCT(c.name)) AS Creators,
+ GROUP_CONCAT(DISTINCT(t.name)) AS Tags,
+ GROUP_CONCAT(DISTINCT(g.name)) AS Genres,
+ COUNT(DISTINCT(mi.InstanceID)) AS Copies,
+ COUNT(DISTINCT(mi.InstanceID)) - COUNT(DISTINCT(b.InstanceID)) AS AvailableCopies,
+ MAX(MATCH (me.Name, me.MediaType, me.DeweyNumber) AGAINST (searchQuery)) AS NameScore,
+ MAX(MATCH (c.Name) AGAINST (searchQuery)) AS CreatorScore,
+ MAX(MATCH (t.Name) AGAINST (searchQuery)) AS TagScore,
+ MAX(MATCH (g.Name) AGAINST (searchQuery)) AS GenreScore
+ FROM Media AS me
+ LEFT JOIN MediaInstance AS mi ON me.MediaID = mi.MediaID
+ LEFT JOIN MediaGenre AS mg ON me.MediaID = mg.MediaID
+ LEFT JOIN Genre AS g ON mg.GenreID = g.GenreID
+ LEFT JOIN MediaTag AS mt ON me.MediaID = mt.MediaID
+ LEFT JOIN Tag AS t ON mt.TagID = t.TagID
+ LEFT JOIN MediaCreator AS mc ON me.MediaID = mc.MediaID
+ LEFT JOIN Creator AS c ON mc.CreatorID = c.CreatorID
+ LEFT JOIN Borrow AS b ON b.InstanceID = mi.InstanceID AND b.IsReturned
+ GROUP BY me.MediaID) AS q
+ WHERE NameScore > 0 OR CreatorScore > 0 OR TagScore > 0 OR GenreScore > 0
+ ORDER BY NameScore DESC, CreatorScore DESC, TagScore DESC, GenreScore DESC, AvailableCopies DESC;
+ END;
+//
+
+DELIMITER //
+CREATE PROCEDURE borrow( libraryCardNo INT, mediaID INT, librarianID INT, returnDate DATE, OUT instanceID INT )
+BEGIN
+ DECLARE instanceID INT;
+
+ SELECT mi.InstanceID INTO instanceID
+ FROM MediaInstance AS mi
+ LEFT JOIN Borrow AS b ON b.InstanceID = mi.InstanceID AND b.IsReturned
+ WHERE mi.MediaID = mediaID AND b.InstanceID IS NULL
+ LIMIT 1;
+
+ INSERT INTO Borrow (LibraryCardNo, InstanceID, LibrarianID, BorrowDate, ReturnDate, IsReturned)
+ VALUES (libraryCardNo, instanceID, librarianID, CURDATE(), returnDate, FALSE);
+END;
+// \ No newline at end of file
diff --git a/Homework/cs5800/final/sql/insert.sql b/Homework/cs5800/final/sql/insert.sql
new file mode 100644
index 0000000..bc6ab18
--- /dev/null
+++ b/Homework/cs5800/final/sql/insert.sql
@@ -0,0 +1,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.');
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
generated by cgit v1.3 (git 2.53.0) at 2026-07-18 12:36:24 +0000