ddl.sql

/* =============================== */
/* Database used: SQLite */
/* =============================== */
/* Definitions for USER, FACULTY, ADMIN */

CREATE TABLE User
(
	ID INTEGER (2)  PRIMARY KEY,
	FName VARCHAR (30),
	LName VARCHAR (30),
	Street VARCHAR (30),
	City VARCHAR (30),
	PCode VARCHAR (30),
	Country VARCHAR (30),
	Email VARCHAR (50),
	Password VARCHAR(25), 
	ProfilePicture LONGBLOB 
);

CREATE TABLE Faculty
(
	FID INT PRIMARY KEY,
	Title VARCHAR (30),
	Affiliation VARCHAR (30),
	Website VARCHAR (30),
	CONSTRAINT fk1 FOREIGN KEY (FID)
		REFERENCES User(ID) 
);

CREATE TABLE Admin
(
	AID INT PRIMARY KEY,
	GrantorID INT,
	AuthDate DATE,
	AuthTime TIME (0),
	CONSTRAINT fk1 FOREIGN KEY (AID)
		REFERENCES User(ID),
	CONSTRAINT fk2 FOREIGN KEY (GrantorID)
		REFERENCES User(ID)
);

CREATE TABLE Authentication
(
	FID INT PRIMARY KEY,
	AID INT DEFAULT '',
	AuthDate DATE,
	AuthTime TIME (0),
	CONSTRAINT fk1 FOREIGN KEY (FID)
		REFERENCES Faculty(FID),
	CONSTRAINT fk2 FOREIGN KEY (AID)
		REFERENCES Admin(AID)
); 	

/* User Relations */

CREATE TABLE ContactInfo(
	UID INT,
	MobileNo VARCHAR(10),
	
	PRIMARY KEY (UID, MobileNo),
	
	CONSTRAINT fk1 FOREIGN KEY (UID) 
		REFERENCES User(ID)
);


/* User-to-Course Relations */

CREATE TABLE IsInterest(
	SID INT,
	CID INT,
	
	PRIMARY KEY (SID, CID),
	CONSTRAINT fk1 FOREIGN KEY (SID) 
		REFERENCES User(ID),
	CONSTRAINT fk2 FOREIGN KEY (CID) 
		REFERENCES Course(ID)
);

CREATE TABLE Enroll(
	SID INT,
	CID INT,
	
	PRIMARY KEY (SID, CID),
	CONSTRAINT fk1 FOREIGN KEY (SID) 
		REFERENCES User(ID),
	CONSTRAINT fk2 FOREIGN KEY (CID) 
		REFERENCES Course(ID)
);

CREATE TABLE Payment(
	SID INT,
	CID INT,
	code VARCHAR(30),
	date DATE,
	time TIME (0),
	
	PRIMARY KEY (SID, CID),
	CONSTRAINT fk1 FOREIGN KEY (SID) 
		REFERENCES User(ID),
	CONSTRAINT fk2 FOREIGN KEY (CID) 
		REFERENCES Course(ID)
);

CREATE TABLE Teach(
	FID INT,
	CID INT,
	date DATE,
	time TIME (0),
	
	PRIMARY KEY(FID, CID),
	
	CONSTRAINT fk1 FOREIGN KEY (FID) 
		REFERENCES User(ID),
	CONSTRAINT fk2 FOREIGN KEY (CID) 
		REFERENCES Course(ID)
);

/* User-to-Others */

CREATE TABLE CompletesMaterial(
	SID INT,
	time TIME (0),
	date DATE,
	MID INT,
	CCID INT,
	PRIMARY KEY(SID, MID, CCID),
	
	CONSTRAINT fk1 FOREIGN KEY (SID) 
		REFERENCES User(ID),
	CONSTRAINT fk2 FOREIGN KEY (MID, CCID) 
		REFERENCES Material(ID, CID)
);

CREATE TABLE CompletesCourse(
	SID INT,
	time TIME (0),
	date DATE,
	CID INT,
	comment LONGBLOB,
	rating INT DEFAULT 5,
	PRIMARY KEY(SID, CID, comment),
	
	CONSTRAINT fk1 FOREIGN KEY (SID) 
		REFERENCES User(ID),
	CONSTRAINT fk2 FOREIGN KEY (CID) 
		REFERENCES Course(ID)
);

/* ============================== */
/* Course */

CREATE TABLE Course(
	name VARCHAR(30),
	icon LONGBLOB,
	cost INT DEFAULT 0,
	ID INT PRIMARY KEY,
	creationDate DATE,
	creationTime TIME (0),
	description LONGBLOB,
	primaryTopic INT,
	
	CONSTRAINT fk1 FOREIGN KEY (primaryTopic)
		REFERENCES Topic(ID)
);

CREATE TABLE Topic(
	ID INT PRIMARY KEY,
	Name VARCHAR(30)
);

CREATE TABLE Sec_Topic(
	TID INT,
	CID INT,
	PRIMARY KEY (TID,CID),
	CONSTRAINT fk1 FOREIGN KEY (TID)
		REFERENCES Topic(ID),
	CONSTRAINT fk2 FOREIGN KEY (CID)
		REFERENCES Course(ID)
);

CREATE TABLE Material(
	Name VARCHAR(30),
	ID INT,
	CID INT,
	PRIMARY KEY (ID, CID),
	CONSTRAINT fk1 FOREIGN KEY (CID)
		REFERENCES Course(ID)
);

/* These are subclasses of materials, with the same
primary keys of MID, CID */

CREATE TABLE File(
	Path VARCHAR(128),
	Size INT DEFAULT 0,
	Type VARCHAR(10),
	FID INT,
	FCID INT,
	PRIMARY KEY (FID, FCID),
	CONSTRAINT fk1 FOREIGN KEY (FID, FCID)
		REFERENCES Material(ID, CID)
);

CREATE TABLE Link(
	url VARCHAR(128),
	IsVideo BOOLEAN,
	LID INT,
	LCID INT,
	PRIMARY KEY (LID, LCID),
	CONSTRAINT fk1 FOREIGN KEY (LID, LCID)
		REFERENCES Material(ID, CID)
);

CREATE TABLE Post(
	Text LONGBLOB,
	PID INT,
	PCID INT,
	PRIMARY KEY (PID, PCID),
	CONSTRAINT fk1 FOREIGN KEY (PID, PCID)
		REFERENCES Material(ID, CID)
);


/* ============================== */
/* Question Entity and its Relations */

CREATE TABLE Questions(
	QuestionText LONGBLOB,
	ID INT PRIMARY KEY,
	IsVisible BOOLEAN,
	Answer LONGBLOB
);

CREATE TABLE RelateToMaterial(
	RMID INT,
	QID INT,
	RCID INT,
	PRIMARY KEY (RMID, QID, RCID),
	CONSTRAINT fk1 FOREIGN KEY (RMID, RCID)
		REFERENCES Material(ID, CID),
	CONSTRAINT fk2 FOREIGN KEY (QID)
		REFERENCES Questions(ID) 
);

/* Question-to-User Relations */

CREATE TABLE LikesQuestion(
	SID INT,
	QID INT,
	PRIMARY KEY (SID, QID),
	CONSTRAINT fk1 FOREIGN KEY (SID)
		REFERENCES User(ID),
	CONSTRAINT fk2 FOREIGN KEY (QID)
		REFERENCES Questions(ID)
);

CREATE TABLE Ask(
	SID INT,
	QID INT,
	PRIMARY KEY (SID, QID),
	CONSTRAINT fk1 FOREIGN KEY (SID)
		REFERENCES User(ID),
	CONSTRAINT fk2 FOREIGN KEY (QID)
		REFERENCES Questions(ID)
);

CREATE TABLE Answers(
	QID INT,
	AFID INT,
	PRIMARY KEY (QID, AFID),
	CONSTRAINT fk1 FOREIGN KEY (AFID)
		REFERENCES Faculty(FID),
	CONSTRAINT fk2 FOREIGN KEY (QID)
		REFERENCES Questions(ID)
);

CREATE TABLE Quiz(
	MID INT,
	QCID INT,
	P_Score INT DEFAULT 0,
	PRIMARY KEY (MID, QCID),
	CONSTRAINT fk1 FOREIGN KEY (MID, QCID)
		REFERENCES Material(ID, CID)
);

CREATE TABLE Quiz_Questions(
	MID INT,
	CID INT,
	NUMBER INT,
	Text LONGBLOB,
	PRIMARY KEY (MID, CID, NUMBER),
	CONSTRAINT fk1 FOREIGN KEY (MID, CID)
		REFERENCES Quiz(MID, QCID)
);

CREATE TABLE Quiz_Answers(
	MID INT,
	CID INT,
	NUMBER INT,
	ID INT,
	A_Text LONGBLOB,
	Feedback LONGBLOB,
	PRIMARY KEY (MID, CID, NUMBER, ID),
	CONSTRAINT fk1 FOREIGN KEY (MID, CID, NUMBER)
		REFERENCES Quiz_questions(MID, CID, NUMBER)

);