Library Project
Chum 240 Fall 2007
Due date: (see schedule)

Design and add tables, views, indexes, etc. to an existing database.

Draw schema for added tables.
Show SQL create statements for new tables.
Produce SQL statements to cover all use cases listed below.
Create views to make presentation easier.
Use cover sheet as usual.

This project can be done in stages, by satisfying one or two requirements at a time. Save copies of the database between stages. (Copy the database file to another file with a different name.)

Concept

A private library wishes to begin loaning out its books to the general public.

It has a catalog database already, and now needs to add tables to keep track of items in circulation.

Requirements

Library patrons will need to be registered before they will be allowed to borrow items.
Patrons must have at least one permanent address, but may have more. If a patron has more than one address, one of them will be designated as the primary residence.
Patrons may have zero or more phone numbers. If a patron has multiple phone numbers, one of them must be designated as the primary phone number.
Patrons with the same primary address are considered to be alternate contacts for each other.
Patrons are divided into two classes. First class patrons may borrow items for any length of time. Second class patrons may borrow items for a maximum of 30 days.
Every month first class patrons will be sent a reminder notice listing the items they have checked out and how many days they have had them.
Every week overdue notices will be sent to second class patrons listing their overdue items and the number of days each item is overdue. The notice will also include the total overdue charge computed as $0.25 per day.
Patrons may request a list of fines paid, including the date paid.
Patrons may request a list of items they currently have checked out including due date and any fines.
For privacy reasons, when an item is returned no record of which patron checked it out will be kept.
Second class patrons may be upgraded to first class patrons, which will affect due dates of any checked out items.
Keep track of how many times and item has been circulated, and of the last time it was circulated.

Use Cases

Add some new first class patrons with multiple addresses and phone numbers.
Add some new second class patrons with single address and phone number.
For any patron with multiple addresses, mark a different one as primary.
Add a patron with the same primary address as another patron.
Find alternate contacts for a patron. (Any patrons with same primary address.)
Check out an item to a first class patron.
Check out two or more items to a second class patron due on the same day.
Check out one or more items to the same patron due on a different day.
Generate a list of items checked out to the patron.
Change a second class patron to a first class patron, accounting for due dates of items already checked out.
Generate notices reminding first class patrons of items they have checked out.
Generate overdue notices with patron name, items overdue, and fines for each item.
A patron returns an item that is not overdue.
A patron returns an item that is overdue including a fine payment.
Generate a patron's fine payment history.
Generate a report of which books are currently checked out.
Generate a circulation report showing days since last check out and number of times checked out.

Patron Data

If you wish, you may use the patron data below.

First Class Patrons
-------------------

Name: Edward
Primary address: 7 Madison
Secondary address: 41 Ville
Main phone: 1234
Secondary phone: 4321

Name: Teresa
Primary address: 14 Monte
Secondary address: 1 Greene
Main phone: 2345
Secondary phone: 5432

Name: Monty
Primary address: same as Teresa
Secondary address: same as Teresa
Main phone: same as Teresa
Secondary phone: 5433


Second Class Patrons
--------------------

Name: Sam
Primary address: 513 First
Main phone: 9162

Name: Sally
Primary address: 781 Main
Main phone: 8410

Name: Philip
Primary address: 7 Rivera
Secondary address: 6 Hill
Main phone: 1359

Name: Bill
Primary address: Same as Sally's
Main phone: 5091

Catalog Data

Below is the existing data for the catalog of this very small library. You may also download it from: chum240-prj2.zip.


create table title(
  titleid integer primary key autoincrement,
  title varchar(128) not null check(title > ''),
  author varchar(64) not null check(author > ''),
  callno varchar(32) not null check(callno > '')
);

create index titleauthor on title(author);
create index titlecallno on title(callno);

insert into 
title  (title,      author,       callno)
values ('Thinking', 'Thomson R.', 'psy-rt-001');

insert into 
title  (title,        author,       callno)
values ('Portuguese', 'Pereira S.', 'por-ps-001');

insert into 
title  (title,  author,    callno)
values ('AWK',  'Aho A..', 'awk-aa-001');

insert into 
title  (title,          author,         callno)
values ('Programming',  'Kernighan B.', 'prg-kb-001');

insert into 
title  (title,          author,     callno)
values ('Teaching',     'Brown D.', 'tch-bd-001');

insert into 
title  (title,          author,      callno)
values ('Language',     'McNeil D.', 'lng-md-001');

insert into 
title  (title,          author,      callno)
values ('Treasure',     'Stevenson R.', 'trs-sr-001');


create table copy(
  titleid integer references title(titleid),
  copyno integer not null check(copyno between 1 and 99),
  note varchar(128)
);

create unique index copytitle on copy(titleid, copyno);

insert into
copy   (titleid, copyno, note)
values (1,       1,      '97 pgs; paperback; 2nd ed.;');

insert into
copy   (titleid, copyno, note)
values (1,       2,      '137 pgs.; ills.; hardback; 2nd ed.;');

insert into
copy   (titleid, copyno, note)
values (2,       1,      '400 pgs.; ills.; hardback; 1st ed.;');

insert into
copy   (titleid, copyno, note)
values (3,       1,      '200 pgs.; paperback;');

insert into
copy   (titleid, copyno, note)
values (4,       1,      '267 pgs.; paperback;');

insert into
copy   (titleid, copyno, note)
values (4,       2,      '267 pgs.; paperback;');

insert into
copy   (titleid, copyno, note)
values (5,       2,      '467 pgs.; paperback;');

insert into
copy   (titleid, copyno, note)
values (5,       4,      '467 pgs.; paperback;');

insert into
copy   (titleid, copyno, note)
values (6,       1,      '305 pgs.; ills.; hardback;');

insert into
copy   (titleid, copyno, note)
values (6,       2,      '305 pgs.; ills.; hardback;');

-- Show catalog by title.
create view bytitle as
  select 
    t.title as title, 
    t.author as auth, 
    t.callno as call, 
    c.copyno as '#', 
    c.note as note
  from title t left join copy c
  on t.titleid = c.titleid
  order by t.title, t.author, c.copyno;

-- Show catalog by author.
create view byauthor as
  select 
    t.author as auth, 
    t.title as title, 
    t.callno as call, 
    c.copyno as '#', 
    c.note as note
  from title t left join copy c
    on t.titleid = c.titleid
  order by t.author, t.title, c.copyno;

-- Show copy status.
create view bycopy as
  select
    t.title as  title,
    count(c.copyno) as cnt,
    case 
      when count(c.copyno) = 1 then 'single'
      when count(c.copyno) > 1 then ''
      else 'missing'
    end as stat
  from title t left join copy c
    on t.titleid = c.titleid
  group by t.titleid
  having stat <> ''
  order by t.title;

Extra credit

Use tables to allow for any number of patron classes, circulation periods, and overdue fees. Include queries necessary for overdue notices and fines.

Library Project Chum 240 Fall 2007 Due date: (see schedule)

Library Project
Chum 240 Fall 2007
Due date: (see schedule)