Database Schema

Databases are software programs for storing data. The data may be stored in Tables . For example:

Table USERS

USERID FIRSTNAME NAME     ADDRESS          ...
====== ========= ======== ================
     1 Franziska Meier    Victoria St 12   ...
     2 John      Smith    Tothill St 2     ...
     3 Gabriele  Merthens Caxton 31        ...
     4 Stephanie Jakobi   Caxton St 44     ...
     5 Elena     Jimenez  Tothill St       ...
     6 Peter     Baker    Cowley St 73     ...

The USERS table stores the users of a library. BOOKS stores the books data, and BOOKS_ON_LOAN records which specifies which books were loan to which user.

The USERID is a number which uniquely identifies each user. Similar with BOOKID, which identifies each book. The table creation SQL is:

CREATE TABLE USERS (
    USERID           NUMBER( 10 ) NOT NULL,
    ADDRESS          VARCHAR2( 40 ) NOT NULL,
    ADDRESS_ZIP      VARCHAR2( 10 ) NOT NULL,
    BIRTHDATE        DATE NOT NULL,
    FIRSTNAME        VARCHAR2( 40 ) NOT NULL,
    NAME             VARCHAR2( 40 ) NOT NULL,
    PHONE_HOME       VARCHAR2( 30 ),
    PHONE_MOBIL      VARCHAR2( 40 ),
    PHONE_WORK       VARCHAR2( 40 ),
    PHOTO            BLOB,
    CONSTRAINT PK_USERS PRIMARY KEY(USERID)) ;

CREATE TABLE BOOKS (
    BOOKID           NUMBER( 10 ) NOT NULL,
    AUTHORS          VARCHAR2( 400 ) NOT NULL,
    TITLE            VARCHAR2( 4000 ) NOT NULL,
    SUBTITLE         VARCHAR2( 4000 ),
    SERIES_TITLE     VARCHAR2( 300 ),
    PUBLISHERID      NUMBER( 10 ),
    VOLUME_NO        NUMBER( 2 ),
    CATEGORYID       NUMBER( 10 ),
    PUBLICATION_DATE DATE,
    IMAGE_URL        VARCHAR2( 2000 ),
    BIC_CODE         VARCHAR2( 60 ),
    CONSTRAINT PK_BOOK PRIMARY KEY(BOOKID));

CREATE TABLE BOOKS_OUT_ON_LOAN (
    BOOKID           NUMBER( 10 ) NOT NULL,
    USERID           NUMBER( 10 ) NOT NULL,
    LOAN_DATE        DATE NOT NULL,
    LOAN_DAYS        NUMBER( 2 ) NOT NULL,
    STATUS           CHAR( 1 ) NOT NULL,
    CONSTRAINT LOAN_PK PRIMARY KEY(BOOKID, USERID, LOAN_DATE),
    CONSTRAINT FK_LOAN_BOOKS FOREIGN KEY( BOOKID ) REFERENCES BOOKS ( BOOKID ) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK_LOAN_USERS FOREIGN KEY( USERID ) REFERENCES USERS ( USERID ) ON DELETE CASCADE ON UPDATE CASCADE) ;

As you see, each column has a data type ( for example NUMBER ) with a specific precision. The constraints enforces different rules the data has to pass. Like for example the Primary Key ( BOOKID ) which is in fact verified using an unique index on BOOKID.

The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship.

The constraint FK_LOAN_BOOKS is a Foreign Key that enforces that each specified BOOKID from FK_LOAN_BOOKS should exist in the BOOKS table. When a BOOK is deleted from the BOOKS table, the coresponding records from BOOKS_ON_LOAN will be deleted too.

Below you can see a sample layout with this two tables: