# stinkbug.sql
#
# ICS 184 Summer 2006
# Assignment #3
#
# This is an SQL script that (re-)creates the database for the Stinkbug
# system.


DROP DATABASE IF EXISTS stinkbug;
CREATE DATABASE stinkbug;

USE stinkbug;

CREATE TABLE user(
    email_address CHAR(30),
    password CHAR(10),
    user_real_name CHAR(40));

CREATE TABLE defect(
    defect_id INTEGER,
    description VARCHAR(100),
    status INTEGER,
    severity INTEGER,
    reporting_user CHAR(30),
    reported_date_time DATETIME,
    component_id INTEGER);

CREATE TABLE comment(
    defect_id INTEGER,
    sequence_number INTEGER,
    posting_user CHAR(30),
    comment_text TEXT,
    posted_date_time DATETIME);

CREATE TABLE component(
    component_id INTEGER,
    component_name CHAR(30));

CREATE TABLE defect_assignment(
    defect_id INTEGER,
    assigned_user CHAR(30),
    role TEXT);

CREATE TABLE status_code(
    status INTEGER,
    status_description CHAR(20));

CREATE TABLE severity_code(
    severity INTEGER,
    severity_description CHAR(20));

