-- Definitions for Pam's book database
-- Bruno Wolff III
-- Started May 27, 2001
-- Last updated August 25, 2001

-- Language table defines valid languages for books.

create table lang (
  langcode serial primary key,
  langdesc text not null unique
    constraint bad_langdesc check (langdesc ~ '^[\041-\176]+( [\041-\176]+)*$'),
  langnote text
    constraint bad_langnote check (langnote ~ '^[\041-\176]+( [\041-\176]+)*$')
);
grant select, insert, update, delete on lang to nobody;
grant select, insert, update, delete on lang_langcode_seq to nobody;

-- Author table defines valid authors for books.

create table auth (
  authcode serial primary key,
  authfirs text
    constraint bad_authfirs check (authfirs ~ '^[\041-\176]+( [\041-\176]+)*$'),
  authlast text not null
    constraint bad_authlast check (authlast ~ '^[\041-\176]+( [\041-\176]+)*$'),
  authnote text
    constraint bad_authnote check (authnote ~ '^[\041-\176]+( [\041-\176]+)*$')
);
grant select, insert, update, delete on auth to nobody;
grant select, insert, update, delete on auth_authcode_seq to nobody;

-- Illustrator table defines valid illustrators for books.

create table illu (
  illucode serial primary key,
  illufirs text
    constraint bad_illufirs check (illufirs ~ '^[\041-\176]+( [\041-\176]+)*$'),
  illulast text not null
    constraint bad_illulast check (illulast ~ '^[\041-\176]+( [\041-\176]+)*$'),
  illunote text
    constraint bad_illunote check (illunote ~ '^[\041-\176]+( [\041-\176]+)*$')
);
grant select, insert, update, delete on illu to nobody;
grant select, insert, update, delete on illu_illucode_seq to nobody;

-- Media table defines valid media types for books (this includes style
-- to some extent).

create table medi (
  medicode serial primary key,
  medidesc text not null unique
    constraint bad_medidesc check (medidesc ~ '^[\041-\176]+( [\041-\176]+)*$'),
  medinote text
    constraint bad_medinote check (medinote ~ '^[\041-\176]+( [\041-\176]+)*$')
);
grant select, insert, update, delete on medi to nobody;
grant select, insert, update, delete on medi_medicode_seq to nobody;

-- Genre table defines valid genre types for books

create table genr (
  genrcode serial primary key,
  genrdesc text not null unique
    constraint bad_genrdesc check (genrdesc ~ '^[\041-\176]+( [\041-\176]+)*$'),
  genrnote text
    constraint bad_genrnote check (genrnote ~ '^[\041-\176]+( [\041-\176]+)*$')
);
grant select, insert, update, delete on genr to nobody;
grant select, insert, update, delete on genr_genrcode_seq to nobody;

-- Topic table defines valid topics for books

create table topi (
  topicode serial primary key,
  topidesc text not null unique
    constraint bad_topidesc check (topidesc ~ '^[\041-\176]+( [\041-\176]+)*$'),
  topinote text
    constraint bad_topinote check (topinote ~ '^[\041-\176]+( [\041-\176]+)*$')
);
grant select, insert, update, delete on topi to nobody;
grant select, insert, update, delete on topi_topicode_seq to nobody;

-- Book table defines a book (story) irrespective of language and media

create table book (
  bookcode serial primary key,
  booknote text
    constraint bad_booknote check (booknote ~ '^[\041-\176]+( [\041-\176]+)*$')
);
grant select, insert, update, delete on book to nobody;
grant select, insert, update, delete on book_bookcode_seq to nobody;


-- Title table lists all titles by book

create table titl (
  titlcode serial primary key,
  bookcode int4 not null
    constraint bad_bookcode references book,
  titldesc text not null
    constraint bad_title check (titldesc ~ '^[\041-\176]+( [\041-\176]+)*$'),
 unique  (bookcode, titldesc)
);
grant select, insert, update, delete on titl to nobody;
grant select, insert, update, delete on titl_titlcode_seq to nobody;
create index titlbook_index on titl (bookcode);

-- Book - Genre table defines the genre that apply to a book

create table bookgenr (
  bookcode int4 not null
    constraint bad_bookcode references book,
  genrcode int4 not null
    constraint bad_genrcode references genr,
  primary key (bookcode, genrcode)
);
grant select, insert, update, delete on bookgenr to nobody;
create index bookgenr_index on bookgenr (bookcode);
create index genrbook_index on bookgenr (genrcode);

-- Book - Topic table defines the topics that apply to a book

create table booktopi (
  bookcode int4 not null
    constraint bad_bookcode references book,
  topicode int4 not null
    constraint bad_topicode references topi,
  primary key (bookcode, topicode)
);
grant select, insert, update, delete on booktopi to nobody;
create index booktopi_index on booktopi (bookcode);
create index topibook_index on booktopi (topicode);

-- Accerated Reader color table defines possible rating colors
-- Ratings are supposed to be grouped by color, but there isn't
-- a simple constraint to enforce that.

create table colo (
  colocode serial primary key,
  colodesc text unique not null
    constraint bad_colodesc check (colodesc ~ '^[\041-\176]+( [\041-\176]+)*$'),
  colonote text
    constraint bad_colonote check (colonote ~ '^[\041-\176]+( [\041-\176]+)*$')
);
grant select, insert, update, delete on colo to nobody;
grant select, insert, update, delete on colo_colocode_seq to nobody;

-- Accerated Reader rating table defines possible rating levels and
-- matches numbers to colors.

create table acce (
  accecode serial primary key,
  accerate numeric(2,1) unique not null,
  colocode int4 not null
    constraint bad_colocode references colo,
  accenote text
    constraint bad_accenote check (accenote ~ '^[\041-\176]+( [\041-\176]+)*$')
);
grant select, insert, update, delete on acce to nobody;
grant select, insert, update, delete on acce_accecode_seq to nobody;
create index accecolo_index on acce (colocode);

-- Edition table defines particular editions of books

create table edit (
  editcode serial primary key,
  titlcode int4 not null 
    constraint bad_titlcode references titl,
  langcode int4 not null
    constraint bad_langcode references lang,
  accecode int4 
    constraint bad_accecode references acce,
  editnote text
    constraint bad_editnote check (editnote ~ '^[\041-\176]+( [\041-\176]+)*$')
);
grant select, insert, update, delete on edit to nobody;
grant select, insert, update, delete on edit_editcode_seq to nobody;
create index edittitl_index on edit (titlcode);
create index editlang_index on edit (langcode);
create index editacce_index on edit (accecode);

-- Edition - Author table defines the authors for particular editions

create table editauth (
  editcode int4 not null
    constraint bad_bookcode references edit,
  authcode int4 not null
    constraint bad_authcode references auth,
  primary key (editcode, authcode)
);
grant select, insert, update, delete on editauth to nobody;
create index editauth_index on editauth (editcode);
create index authedit_index on editauth (authcode);

-- Edition - Illustrator table defines the illustrators for particular
-- editions of books

create table editillu (
  editcode int4 not null
    constraint bad_edit references edit,
  illucode int4 not null
    constraint bad_illucode references illu,
  primary key (editcode, illucode)
);
grant select, insert, update, delete on editillu to nobody;
create index editillu_index on editillu (editcode);
create index illuedit_index on editillu (illucode);

-- Item table defines individual copies of a book

create table item (
  itemcode serial primary key,
  editcode int4 not null
    constraint bad_edit references edit,
  medicode int4 not null
    constraint bad_medicode references medi,
  itemlabl text
    constraint bad_itemlabl check (itemlabl ~ '^[\041-\176]+( [\041-\176]+)*$'),
  itemchec text
    constraint bad_itemchec check (itemchec ~ '^[\041-\176]+( [\041-\176]+)*$'),
  itempage int
    constraint bad_itempage check (itempage > 0),
  itemnote text
    constraint bad_itemnote check (itemnote ~ '^[\041-\176]+( [\041-\176]+)*$')
);
grant select, insert, update, delete on item to nobody;
grant select, insert, update, delete on item_itemcode_seq to nobody;
create index itemedit_index on item (editcode);
create index itemmedi_index on item (medicode);