-- create a table
CREATE TABLE Course (
  courseID INT IDENTITY (1, 1) PRIMARY KEY,
  courseName varcher (255) NOT NULL,
  courseDesc varchar (25) NOT NULL
);


-- create a table
CREATE TABLE Students (
  ICASNo INT IDENTITY (1, 1) PRIMARY KEY,
  studentName varcher (50) NOT NULL,
  studentSurname varchar (25) NOT NULL,
  studentContact varchar (25),
  studentEmail varchar (255) NOT NULL UNIQUE,
  HighSchoolAttended varchar (50) NOT NULL,
  courseID INT NOT NULL,
  FOREIGN KEY (courseID)
    REFERENCES Course (store_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

-- create a table
CREATE TABLE Facilitator (
  facilitatorID INT IDENTITY (1, 1) PRIMARY KEY,
  facilitatorName varcher (50) NOT NULL,
  facilitatorContact varchar (25),
  facilitatorEmail varchar (255) NOT NULL UNIQUE,
  ICASNo INT NOT NULL,
  FOREIGN KEY (ICASNo)
    REFERENCES Students (ICASNo)
    ON DELETE CASCADE ON UPDATE CASCADE
);
  

-- create a table
CREATE TABLE Semester (
  semesterID INT IDENTITY (1, 1) PRIMARY KEY,
  semesterStartDate DATE NOT NULL,
  semesterEndDate DATE NOT NULL,
  ICASNo INT NOT NULL,
  FOREIGN KEY (ICASNo)
    REFERENCES Students (ICASNo)
    ON DELETE CASCADE ON UPDATE CASCADE
);