CREATE TABLE student(
name VARCHAR(50) NOT NULL PRIMARY KEY,
office VARCHAR(50),
position VARCHAR(50),
email VARCHAR(50),
phone VARCHAR(50),
supervisor VARCHAR(50),
FOREIGN KEY (supervisor) REFERENCES academicStaff (name)
);

CREATE TABLE academicStaff(
name VARCHAR(50) NOT NULL PRIMARY KEY,
office VARCHAR(50),
position VARCHAR(50),
email VARCHAR(50),
phone VARCHAR(50)
);

CREATE TABLE adminStaff(
name VARCHAR(50) NOT NULL PRIMARY KEY,
office VARCHAR(50),
position VARCHAR(50),
email VARCHAR(50),
phone VARCHAR(50)
);

CREATE TABLE technicalStaff(
name VARCHAR(50) NOT NULL PRIMARY KEY,
office VARCHAR(50),
email VARCHAR(50),
phone VARCHAR(50)
);

CREATE TABLE areasOfInterest(
name VARCHAR(50) NOT NULL,
area VARCHAR(50) NOT NULL,
PRIMARY KEY (name,area),
FOREIGN KEY (name) REFERENCES student (name),
FOREIGN KEY (name) REFERENCES academicStaff (name)
);

CREATE TABLE roles(
name VARCHAR(50) NOT NULL,
researchGroup VARCHAR(50) NOT NULL,
PRIMARY KEY (name, researchGroup),
FOREIGN KEY (name) REFERENCES student (name),
FOREIGN KEY (name) REFERENCES academicStaff (name)
);

CREATE TABLE course(
courseNumber VARCHAR(40) NOT NULL PRIMARY KEY,
courseTitle VARCHAR(50),
instructor VARCHAR(50),
area VARCHAR(50),
description VARCHAR(200),
prerequisite VARCHAR(200),
FOREIGN KEY (instructor) REFERENCES academicStaff (name)
);

CREATE TABLE taAssignment(
courseNumber VARCHAR(40) NOT NULL,
studentName VARCHAR(50) NOT NULL,
PRIMARY KEY (courseNumber, studentName),
FOREIGN KEY (courseNumber) REFERENCES course (courseNumber),
FOREIGN KEY (studentName) REFERENCES student (name)
);