Database Schema for CS4400

gistfile1.sql

CREATE TABLE users (
       username	   VARCHAR(20) NOT NULL PRIMARY KEY,
       password	   CHAR(40) NOT NULL,
       firstname   VARCHAR(20) NOT NULL,
       lastname    VARCHAR(20) NOT NULL,
       email 	   VARCHAR(20) NOT NULL,
       is_director CHAR(1) DEFAULT '0'
);
CREATE INDEX fullname ON users(firstname, lastname);


CREATE TABLE bags (
       bag_name    VARCHAR(20) NOT NULL PRIMARY KEY
);

CREATE TABLE sources (
       name        VARCHAR(20) NOT NULL PRIMARY KEY
);

CREATE TABLE products (
       name    	   VARCHAR(20) NOT NULL PRIMARY KEY,
       cost	   INTEGER NOT NULL,
       source_name VARCHAR(20) NOT NULL,
       FOREIGN KEY (source_name) REFERENCES sources (name)
);

CREATE TABLE financial_aids (
       is_fed	   CHAR(1) NOT NULL,     
       name        VARCHAR(20) NOT NULL PRIMARY KEY
);

CREATE TABLE clients (
       id    	   INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
       gender	   CHAR(1) NOT NULL,
       dob	   CHAR(10) NOT NULL,
       start_date  CHAR(10) NOT NULL,
       street	   VARCHAR(20) NOT NULL,
       city	   VARCHAR(20) NOT NULL,
       state	   VARCHAR(20) NOT NULL,
       zip	   CHAR(5) NOT NULL,
       apartment   VARCHAR(10) DEFAULT '0',
       firstname   VARCHAR(20) NOT NULL,
       lastname	   VARCHAR(20) NOT NULL,
       phone	   CHAR(13) NOT NULL,
       pickup_day  INTEGER NOT NULL,
       bag_name	   VARCHAR(20),
       /* on delete set null sets bag_name to null when a bag is deleted
        * to avoid a foreign key constraint violation */
       FOREIGN KEY (bag_name) REFERENCES bags (bag_name) ON DELETE SET NULL
);
CREATE INDEX address ON clients (street, city, state, zip, apartment);
CREATE INDEX namephone ON clients (firstname, lastname, phone);


CREATE TABLE family_members (
       firstname   VARCHAR(20) NOT NULL,
       lastname	   VARCHAR(20) NOT NULL,
       dob	   VARCHAR(10) NOT NULL,
       gender	   CHAR(1) NOT NULL,
       client_id   INTEGER NOT NULL,
       /* on delete cascade means if a client gets deleted all family members
        * get deleted too */
       FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE, 
       PRIMARY KEY (firstname, lastname, client_id)
);
CREATE INDEX name ON family_members(firstname, lastname);

CREATE TABLE bag_holds (
       current_qty INTEGER,
       last_month_qty INTEGER,
       bag_name VARCHAR(20) NOT NULL,
       product_name VARCHAR(20) NOT NULL,
       FOREIGN KEY (bag_name) REFERENCES bags (bag_name) ON DELETE CASCADE,
       FOREIGN KEY (product_name) REFERENCES products (name) ON DELETE CASCADE,
       PRIMARY KEY (bag_name, product_name)
);

CREATE TABLE pickup_transaction (
       pickup_id   INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
       pickup_date CHAR(10) NOT NULL,
       client_id   INTEGER NOT NULL,
       bag_name    VARCHAR(20) NOT NULL,
       FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE,
       FOREIGN KEY (bag_name) REFERENCES bags (bag_name) ON DELETE CASCADE
);

CREATE TABLE client_finaid_relationships (
       client_id   INTEGER NOT NULL,
       finaid_name VARCHAR(20) NOT NULL,
       FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE,
       FOREIGN KEY (finaid_name) REFERENCES financial_aids (name) ON DELETE CASCADE,
       PRIMARY KEY (client_id, finaid_name)
);

CREATE TABLE dropoff_transactions (
       dropoff_id   INTEGER NOT NULL AUTO_INCREMENT,
       date	    CHAR(10) NOT NULL,
       qty	    INTEGER NOT NULL,
       source_name  VARCHAR(20) NOT NULL,
       product_name VARCHAR(20) NOT NULL,
       FOREIGN KEY (source_name) REFERENCES sources (name) ON DELETE CASCADE,
       FOREIGN KEY (product_name) REFERENCES products (name) ON DELETE CASCADE,
       PRIMARY KEY (dropoff_id, source_name, product_name)
);

CREATE VIEW clients_with_family_size AS
SELECT *, (SELECT COUNT(*) + 1 FROM family_members WHERE family_members.client_id = id) 
AS family_size FROM clients;