diff options
Diffstat (limited to 'Homework/cs5800/final/sql/create.sql')
| -rw-r--r-- | Homework/cs5800/final/sql/create.sql | 312 |
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 |