CREATE TABLE dbperson(
personName VARCHAR(50) NOT NULL PRIMARY KEY,
personTitle VARCHAR(50),
homepage VARCHAR(50),
researchINterests VARCHAR(50),
email VARCHAR(50),
advisor VARCHAR(50)
);

CREATE TABLE dbfaculty(
personName VARCHAR(50) NOT NULL PRIMARY KEY,
personTitle VARCHAR(50),
homepage VARCHAR(50),
researchINterests VARCHAR(50),
email VARCHAR(50),
FOREIGN KEY (personName) REFERENCES dbperson (personName)
);

CREATE TABLE dbphdstudent(
personName VARCHAR(50) NOT NULL PRIMARY KEY,
personTitle VARCHAR(50),
homepage VARCHAR(50),
researchINterests VARCHAR(50),
email VARCHAR(50),
advisor VARCHAR(50),
FOREIGN KEY (advisor) REFERENCES dbfaculty (personName),
FOREIGN KEY (personName) REFERENCES dbperson (personName)
);

CREATE TABLE dbmscstudent(
personName VARCHAR(50) NOT NULL PRIMARY KEY,
personTitle VARCHAR(50),
homepage VARCHAR(50),
researchINterests VARCHAR(50),
email VARCHAR(50),
advisor VARCHAR(50),
FOREIGN KEY (advisor) REFERENCES dbfaculty (personName),
FOREIGN KEY (personName) REFERENCES dbperson (personName)
);

CREATE TABLE dbassociate(
personName VARCHAR(50) NOT NULL PRIMARY KEY,
personTitle VARCHAR(50),
homepage VARCHAR(50),
researchINterests VARCHAR(50),
email VARCHAR(50),
FOREIGN KEY (personName) REFERENCES dbperson (personName)
);

CREATE TABLE dbalumi(
personName VARCHAR(50) NOT NULL PRIMARY KEY,
personTitle VARCHAR(50),
homepage VARCHAR(50),
researchINterests VARCHAR(50),
email VARCHAR(50),
FOREIGN KEY (personName) REFERENCES dbperson (personName)
);

CREATE TABLE project(
projectTitle VARCHAR(50) NOT NULL PRIMARY KEY,
description VARCHAR(50),
host VARCHAR(50),
link VARCHAR(50),
FOREIGN KEY (host) REFERENCES dbperson (personName)
);

CREATE TABLE person_prject(
personName VARCHAR(50) NOT NULL,
projectTitle VARCHAR(50) NOT NULL,
PRIMARY KEY (personName,projectTitle),
FOREIGN KEY (personName) REFERENCES dbperson (personName),
FOREIGN KEY (projectTitle) REFERENCES project (projectTitle)
);

CREATE TABLE paper(
title VARCHAR(50) NOT NULL PRIMARY KEY,
description VARCHAR(50),
publicationYear VARCHAR(50),
FOREIGN KEY (publicationYear) REFERENCES year (yr)
);

CREATE TABLE paper_author(
paperTitle VARCHAR(50) NOT NULL,
personName VARCHAR(50) NOT NULL,
PRIMARY KEY (paperTitle,personName),
FOREIGN KEY (paperTitle) REFERENCES paper (title),
FOREIGN KEY (personName) REFERENCES dbperson (personName)
);

CREATE TABLE year(
yr VARCHAR(50) NOT NULL PRIMARY KEY
);

CREATE TABLE seminar(
title VARCHAR(50) NOT NULL PRIMARY KEY,
speaker VARCHAR(50),
date VARCHAR(50),
location VARCHAR(50),
host VARCHAR(50),
FOREIGN KEY (speaker) REFERENCES dbperson (personName),
FOREIGN KEY (host) REFERENCES dbperson (personName)
);

CREATE TABLE course(
number VARCHAR(50) NOT NULL PRIMARY KEY,
title VARCHAR(50),
description VARCHAR(50),
prerequisites VARCHAR(50),
instructor VARCHAR(50),
FOREIGN KEY (instructor) REFRENCES dbperson (personName)
);