InitHEADmain

1 files changed, 312 insertions, 0 deletions

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