path: root/Homework/cs5800/final/sql/create.sql

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;
//