-- Create table schemas and constraints for the "Homework" database.

-- Remove prior instances of the relevant tables, they will
-- be (re-)created below
DROP TABLE STUDENTS;
DROP TABLE EXERCISES;
DROP TABLE RESULTS;

-----------------------------------------------------------------------
-- DATABSE SCHEMA
 
-- Create table `STUDENTS'
CREATE TABLE STUDENTS (
             SID   NUMERIC(3)  NOT NULL,
             FIRST VARCHAR(20) NOT NULL,
					   LAST  VARCHAR(20) NOT NULL,
					   EMAIL VARCHAR(80));


-- Create table `EXERCISES'
CREATE TABLE EXERCISES (CAT   CHAR(1)    NOT NULL,
                        ENO   NUMERIC(2) NOT NULL,
                        TOPIC VARCHAR(40),
                        MAXPT NUMERIC(2));

-- Create table 'RESULTS'
CREATE TABLE RESULTS (SID NUMERIC(3) NOT NULL,
                      CAT CHAR(1)    NOT NULL,
                      ENO NUMERIC(2) NOT NULL,
                      POINTS NUMERIC(2));


-----------------------------------------------------------------------
-- DATABASE CONSTRAINTS

-- Primary key constraints
ALTER TABLE STUDENTS  ADD PRIMARY KEY (SID);
-- Secondary (alternate) key
ALTER TABLE STUDENTS  ADD UNIQUE (FIRST,LAST);
ALTER TABLE EXERCISES ADD PRIMARY KEY (CAT,ENO);
ALTER TABLE RESULTS   ADD PRIMARY KEY (SID,CAT,ENO);



-- Foreign key constraints
ALTER TABLE RESULTS ADD FOREIGN KEY (SID) 
                        REFERENCES STUDENTS (SID) ON DELETE CASCADE;

ALTER TABLE RESULTS ADD FOREIGN KEY (CAT,ENO) 
                        REFERENCES EXERCISES (CAT,ENO);



-- General constraints                        
ALTER TABLE EXERCISES ADD CONSTRAINT HMF CHECK (CAT IN ('H','M','F'));