CSc 8711. Databases and the Web
Spring 2004, Programming Project 1 (Bulletin Board)
Due: Thursday, January 27, 2004

In this project, we will implement a bulletin board system, very much like the bulletin board in the course home page. We will use an Oracle 9i database to store the data and program the Web application in PL/SQL. The bulletin board can be used only by an authorized set of users who have a userid (their email) and a password. The database tables are defined as follows: 
create table bbusers (
  email  varchar2(50), -- used as userid
  name   varchar2(30),
  password varchar2(10),
  nickname varchar2(30),
  lastaccess date,
  u_access varchar2(30),
  primary key (email)
);

create sequence postSeq start with 1;

create table postings (
  postId    number(5),  -- use postSeq to generate this value
  postDate  date,
  postedBy  varchar2(50),
  postSubject varchar2(100),
  content   varchar2(512),
  ancestorPath  varchar2(100), -- records the path from root to parent
                               -- ex: 1:5:6:12 would indicate path
                               -- 1 -> 5 -> 6 -> 12
  primary key (postId),
  foreign key (postedBy) references bbusers
);
Some sample data is shown below: 
insert into bbusers values ('a@abc.com','Andy Smith','a123','baggy',null,null);
insert into bbusers values ('b@abc.com','Craig Smith','a123','runner',null,null);
insert into bbusers values ('c@abc.com','Craig Rich','a123','slogger',null,null);
insert into bbusers values ('d@abc.com','Mike Arlott','a123','ateam',null,null);
--
insert into postings values (1,'11-JAN-2003','a@abc.com','Welcome',
'Welcome to the bulletin board',null);
insert into postings values (2,'12-JAN-2003','b@abc.com','Welcome',
'This is posting 2','1');
insert into postings values (3,'13-JAN-2003','c@abc.com','Welcome',
'This is posting 3','1');
insert into postings values (4,'14-JAN-2003','a@abc.com','Welcome',
'This is posting 4',null);
insert into postings values (5,'15-JAN-2003','a@abc.com','Welcome',
'This is posting 5',null);
insert into postings values (6,'16-JAN-2003','a@abc.com','Welcome',
'This is posting 6',null);
insert into postings values (7,'17-JAN-2003','c@abc.com','Welcome',
'This is posting 7','1:2');
insert into postings values (8,'18-JAN-2003','d@abc.com','Welcome',
'This is posting 8','1:2');
insert into postings values (9,'19-JAN-2003','a@abc.com','Welcome',
'This is posting 9','1:2:8');
insert into postings values (10,'20-JAN-2003','b@abc.com','Welcome',
'This is posting 10','6');
The following features must be implemented:
  1. User sign-in and sign-out
  2. Default display of messages in reverse chronological order and properly indented followup messages.
  3. Post message and post follow-up message
  4. Automatic signout with 2 minutes of inactivity
  5. u_access authentication (similar to example in the book)
  6. Search feature (user should be able to choose either SUBJECT or BODY or BOTH and enter a string in a text box); Results should be displayed in a similar manner as the entire bulletin board (i.e. if a search string is found in a posting, its topmost ancestor message and the entire sub-tree under the topmost ancestor must be displayed).
The Optional Link URL/Link Title/Optional Image URL feature need not be implemented.