Learning Oracle PL/SQL

8.2. Using the Mail Sender in the Library System

Conceptually, it doesn't require a great leap to design a system that notifies someone via email when a particular event occurs in the database. Let's take a close look at how we can use such a feature in the library system, starting with a look at the underlying database structures.

8.2.1. Book Transactions and Reservations

We do have to introduce two more tables into the design, because so far, our library has books and borrowers, but no transaction records. We can create a simple table to hold transaction information; that is, it correlates a borrower, a particular copy of a book, an event (like check-in or check-out), and a date:

CREATE TABLE user_book_copy_events (
   barcode_id VARCHAR2(100) NOT NULL
      REFERENCES book_copies (barcode_id),
   borrower_id NUMBER NOT NULL
      REFERENCES lib_users (id),
   event_name VARCHAR2(30) NOT NULL
      CHECK (event_name IN ('checkin','checkout')),
   timestamp DATE DEFAULT SYSDATE NOT NULL,
   CONSTRAINT user_book_events_pk PRIMARY KEY
      (barcode_id, borrower_id, event_name, timestamp)
);

The primary key is made up of all four columns because:

• Each borrower can check out many books.

• Over time, a book will be repeatedly checked in or out.

• A given user might check a book out more than once.

As designed, the system prevents two borrowers from performing the exact operation on the same copy of a book at the same time.

This table cannot store information about book-reserving events, because books should be reserved ...