CPSC 343 Database Theory and Practice Fall 2017

Triggers

You will once again be working with the library database, this time with one additional table to keep track of borrowers' unpaid fines.

BOOK(Book_id,Title,Publisher_name)
BOOK_AUTHORS(Book_id,Author_name)
PUBLISHER(Name,Address,Phone)
BOOK_COPIES(Book_id,Branch_id,No_of_copies)
BOOK_LOANS(Book_id,Branch_id,Card_no,Date_out,Due_date)
LIBRARY_BRANCH(Branch_id,Branch_name,Address)
BORROWER(Card_no,Name,Address,Phone)
FINES(Card_no,Amount)

Key attributes are underlined. The foreign key constraints are the following:

Assume that BOOK_LOANS contains only information about books currently checked out - when a book is returned, the corresponding entry is removed from BOOK_LOANS.

For each of the following, explain how the constraint is already or could be enforced in the database: via column-based settings (by choosing the domain or, default value, or by setting primary key, NOT NULL, or UNIQUE tags), foreign keys, or triggers. Be specific; don't just say "domain", for example, but indicate which column and what particular domain. For triggers, give the full CREATE TRIGGER command to accomplish the task. Modifications that violate the constraint should be disallowed unless otherwise specified. If there is more than one way to enforce a constraint, choose the best or most appropriate solution. (Only use triggers if there isn't another alternative.)

Treat each question separately, that is, don't assume that constraints imposed by earlier questions are in place when you answer a later question.

  1. Every book loan must refer to a book in BOOK, a branch in LIBRARY_BRANCH, and a borrower in BORROWER.

    Foreign key - Book_id, Branch_id, and Card_no in BOOK_LOANS refer to the attributes with the same names in BOOK, LIBRARY_BRANCH, and BORROWER, respectively.

  2. A book that is loaned out must have both the date it was checked out and the due date recorded.

    NOT NULL constraints for both Date_out and Due_date in BOOK_LOANS.

  3. The check out date defaults to the current date and the due date to one month after the check out date.

        CREATE TRIGGER loanDateDefaults
        BEFORE INSERT ON BOOK_LOANS FOR EACH ROW
        BEGIN
          IF NEW.Date_out IS NULL THEN
            SET NEW.Date_out=CURDATE();
          END IF;
          IF NEW.Due_date IS NULL THEN
            SET NEW.Due_date = DATE_ADD(NEW.Date_out,INTERVAL 1 MONTH);
          END IF;
        END
      
  4. A book's due date must be after the date it is checked out.

        CREATE TRIGGER checkDueDateOnInsert
        BEFORE INSERT ON BOOK_LOANS FOR EACH ROW
        BEGIN
          IF NEW.Due_date <= NEW.Date_out
          THEN SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Due date must be after date out';
          END IF;
        END
      

    Plus a similar definition for updates - BEFORE UPDATE.

  5. A book cannot be loaned out for more than one month. Adjust the due date accordingly.

        CREATE TRIGGER checkLoanPeriod
        BEFORE INSERT ON BOOK_LOANS FOR EACH ROW
        BEGIN
          IF DATE_ADD(NEW.Date_out,INTERVAL 1 MONTH) < NEW.Due_date THEN
            SET NEW.Due_date = DATE_ADD(NEW.Date_out,INTERVAL 1 MONTH);
          END IF;
        END
      

    Plus a similar definition for updates - BEFORE UPDATE.

    Note that this assumes that the dates are not NULL, but that is OK because the Date_out and Due_date columns have NOT NULL constraints.

  6. A book can only be loaned out from a branch that has at least one copy of that book.

        CREATE TRIGGER loanCheck
        BEFORE INSERT ON BOOK_LOANS FOR EACH ROW
        BEGIN
          IF NOT EXISTS ( SELECT *
                          FROM BOOK_COPIES C
                          WHERE C.Book_id = NEW.Book_id AND C.Branch_id = NEW.Branch_id 
          THEN SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Branch must have at least one copy of the book in order for it to be loaned';
          END IF;
        END
      

    Plus a similar definition for updates - BEFORE UPDATE.

  7. Book IDs uniquely identify each book.

    Book_id is the primary key for BOOK.

  8. Every book must have a title.

    NOT NULL constraint on Title.

  9. There cannot be two different books with the same title.

    UNIQUE constraint on Title.

  10. There cannot be two different books with the same title from one publisher.

    UNIQUE constraint on Title and Publisher_name. (Note that this is a single UNIQUE constraint involving both attributes, not two separate constraints. Separate constraints would mean that Title is unique among all books, and Publisher_name is also unique amongst all books.)

  11. The number of copies for an entry in BOOK_COPIES must be non-negative.

    Make the data type UNSIGNED.

  12. The number of copies for an entry in BOOK_COPIES must be at least 1.

        CREATE TRIGGER numCopiesCheck
        BEFORE INSERT ON BOOK_COPIES FOR EACH ROW
        BEGIN
          IF NEW.No_of_copies < 1 
          THEN SIGNAL SQLSTATE '45000'
    	SET MESSAGE_TEXT = 'There must be at least one copy of a book';
          END IF;
        END
      

    Plus a similar definition for updates - BEFORE UPDATE.

  13. A borrower cannot have more than 10 books checked out at a time from a single branch.

        CREATE TRIGGER max10CheckedOut
        BEFORE INSERT ON BOOK_LOANS FOR EACH ROW
        BEGIN
          IF ( SELECT COUNT(*)
               FROM BOOK_LOANS BL
               WHERE BL.Card_no=NEW.Card_no AND BL.Branch_id=NEW.Branch_id ) > 9
          THEN SIGNAL SQLSTATE '45000'
    	SET MESSAGE_TEXT = 'Borrower can\'t check out more than 10 books at a time from one branch';
          END IF;
        END
      

    > 9 because the new row has not been inserted yet, so to prevent more than 10 books being checked out after the insert, there can't be more than 9 currently checked out.

    Plus a similar definition for updates - BEFORE UPDATE.

  14. A borrower may not check out a book if he or she has any overdue books at any branch (as of the check out date) or any unpaid fines.

        CREATE TRIGGER checkOutRestrictions
        BEFORE INSERT ON BOOK_LOANS FOR EACH ROW
        BEGIN
          IF EXISTS ( SELECT *
                      FROM BOOK_LOANS BL
                      WHERE BL.Card_no=NEW.Card_no AND BL.Due_date < CURDATE() ) OR
    	                ( SELECT Amount
    			  FROM FINES F
    			  WHERE F.Card_no=NEW.Card_no )
          THEN SIGNAL SQLSTATE '45000'
    	SET MESSAGE_TEXT = 'Borrower has overdue books or unpaid fines';
          END IF;
        END
      

    Plus a similar definition for updates - BEFORE UPDATE.

  15. There must be an entry in FINES for every borrower. (Add an entry to FINES when needed; the amount will be 0 if there are no unpaid fines.)

        CREATE TRIGGER ensureFinesEntry
        AFTER INSERT ON BORROWER FOR EACH ROW
        BEGIN
          IF NOT EXISTS ( SELECT *
                          FROM FINES F
                          WHERE F.Card_no=NEW.Card_no ) THEN
            INSERT INTO FINES(Card_no,Amount) VALUES(NEW.Card_no,0);
          END IF;
        END
      

    This must be AFTER INSERT because the foreign key constraint on FINES.Card_no means there can't be an entry in FINES before the corresponding borrower has been added to BORROWER.

    A BEFORE/AFTER UPDATE version of the trigger is not needed because FINES.Card_no refers to BORROWER.Card_no - if the Card_no in BORROWER is changed, either the corresponding entry in FINES will be updated as well (ON UPDATE CASCADE) or the initial change will be denied (ON UPDATE RESTRICT).

  16. When an overdue book is returned, the amount of the fine (25 cents per day) is added to the borrower's unpaid fines.

    When a book is returned, the entry is removed from BOOK_LOANS.

        CREATE TRIGGER assessFine
        AFTER DELETE ON BOOK_LOANS FOR EACH ROW
        BEGIN
          IF CURDATE() > OLD.Due_date THEN
            UPDATE FINES
            SET Amount = Amount+0.25*DATEDIFF(CURDATE(),OLD.Due_date)
            WHERE Card_no=OLD.Card_no;
          END IF;
        END
      
  17. Unpaid fines max out at $200. Adjust the amount accordingly.

        CREATE TRIGGER maxFine
        BEFORE UPDATE ON FINES FOR EACH ROW
        BEGIN
          IF NEW.Amount > 200 THEN
            SET NEW.Amount = 200;
          END IF;
        END
      

    Plus a similar definition for inserts - BEFORE INSERT. (Though UPDATE is expected to be the most common.)

  18. A borrower cannot be deleted if he or she still has books checked out.

    BOOK_LOANS.Card_no refers to BORROWER.Card_no so an ON DELETE RESTRICT setting on the foreign key constraint will accomplish this.

  19. A borrower cannot be deleted if he or she has any unpaid fines.

    This is a trigger because "unpaid fines" means the amount is 0, but there is still an entry in FINES.

        CREATE TRIGGER blockUnpaidDelete
        BEFORE DELETE ON BORROWER FOR EACH ROW
        BEGIN
        IF ( SELECT Amount FROM FINES F WHERE F.Card_no=OLD.Card_no ) > 0 
          THEN SIGNAL SQLSTATE '45000'
    	SET MESSAGE_TEXT = 'Borrower has unpaid fines';
          END IF;
        END
      

Valid HTML 4.01!