/* sample database create */
/* This was originally done for Sybase which not support longs. */
/* All dates are stored as BigDate ordinal integers, days since 1970 Jan 1 */
/* Phone numbers are stored as decimal integers area/phone e.g. 6049225555 */
/* There are tables for establishments that serve alchol, people who serve alcohol
   and telephone exchanges */

CREATE DATABASE rbs;
USE rbs;

CREATE TABLE ESTABS(
        phone           decimal(10,0) NOT NULL,
        establishment   char(30) NOT NULL,
        city            char(20),
PRIMARY KEY (phone));

CREATE TABLE EXCHANGES(
        phone           decimal(10,0) NOT NULL, /* last 4 digits always 0000 */
        usual_city      char(18) NOT NULL,
PRIMARY KEY (phone));

CREATE TABLE PEOPLE(
        acct            integer  NOT NULL DEFAULT AUTOINCREMENT,
        surname         char(30) NOT NULL,
        given_name      char(30) NOT NULL,
        second_init     char(1),
        greet_name      char(30),
        address_1       char(30),
        address_2       char(30),
        city            char(20),
        prov            char(2),
        postal          char(6),
        home_phone      decimal(10,0),
        work_phone      decimal(10,0), /* poss blank, poss of non-estab */
        work_extension  smallint,
        birth_date      integer,
        sex             char(1), /* M F U */
        sol             char(1), /* Y or N, Special Occasion License */
        payment_status  char(1), /* N=None P=Print W=Wait D=Done
                                    R=Returned B=Bounced F=Failed M=Mailed */
        mail_date       integer, /* when mailed wallet card */
        course_date     integer,
        course_type     char(1), /* L or S */
        course_sequence smallint,
        course_roster   smallint,
        questions_missed integer, /* bit map low order 20 or 25 bits */
        score           smallint,
        percent         smallint,
        create_date     integer,
        change_date     integer,
        changed_by      char(3),
        comments1       char(30),
        comments2       char(30),
        comments3       char(30),
     PRIMARY KEY (acct));

CREATE TABLE TAGS(
        acct            integer NOT NULL,
        user_initials   char(3) NOT NULL,
PRIMARY KEY (acct,user_initials));

CREATE TABLE TARGET_DESCS(
        target          smallint NOT NULL DEFAULT AUTOINCREMENT,
        description     char(28) NOT NULL,
        people_count    integer NOT NULL,
PRIMARY KEY (target));

CREATE TABLE TARGETS(
        acct            integer NOT NULL,
        target          smallint NOT NULL,
PRIMARY KEY (acct,target));

CREATE TABLE USERS(
        user_initials   char(3) NOT NULL,
        privilege       smallint NOT NULL,
        /*  1=Look 2=Update 3=Money 4=Administrator 5=CMP */
        people_tagged   integer NOT NULL,
PRIMARY KEY (user_initials));

COMMIT WORK;

/* -30- */