-- Create database base tables for Assignment 3
-- AV Peterson 20-09-2002

-- Tutorial(*TutId, Day, Time, Room, TutorName )
CREATE TABLE B_Tutorial
(
  TutId VARCHAR(10),
  Day   VARCHAR(3) NOT NULL,
  Time  NUMBER(2) NOT NULL,
  Room  VARCHAR(12) NOT NULL,
  TutorName VARCHAR(16),
  CONSTRAINT Tutorial_pk PRIMARY KEY (TutId)
)
/

-- A3Student(*StudentId, Surname, FirstName, Username, TutId, Withdrawn)
CREATE TABLE B_A3Student
(
  StudentId NUMBER(7),
  Surname   VARCHAR(16) NOT NULL,
  FirstName VARCHAR(16),
  Username  VARCHAR(8),
  TutId     VARCHAR(10),
  Withdrawn DATE,
  CONSTRAINT A3Student_pk PRIMARY KEY(StudentId),
  CONSTRAINT Student_Tut_fk FOREIGN KEY (TutId) REFERENCES B_Tutorial(TutId)
)
/

-- AssessmentItem(*AId, ADate, TotalMark, FinalPercent )
CREATE TABLE B_AssessmentItem
(
  AId     VARCHAR(8),
  ADate   DATE,
  TotalMark    NUMBER(3),
  FinalPercent NUMBER(3),
  CONSTRAINT AssessmentItem_pk PRIMARY KEY (AId)
)
/

-- Mark(*StudentId, *AId, AMark, DateSubmitted )
CREATE TABLE B_Mark
(
  StudentId NUMBER(7),
  AId       VARCHAR(8),
  AMark     NUMBER(3),
  DateSubmitted DATE,
  CONSTRAINT Mark_pk PRIMARY KEY (StudentId, AId),
  CONSTRAINT Mark_A3Student_fk
    FOREIGN KEY (StudentId) REFERENCES B_A3Student(StudentId),
  CONSTRAINT Mark_Assess_fk 
    FOREIGN KEY (AId) REFERENCES B_AssessmentItem(AId)
)
/