database.sql

CREATE TABLE IF NOT EXISTS schools(
	id SERIAL PRIMARY KEY,
	name VARCHAR(120) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS departments(
	id SERIAL PRIMARY KEY,
	school INTEGER REFERENCES schools(id) ON DELETE CASCADE ON UPDATE CASCADE,
	name VARCHAR(120) UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS courses(
	id SERIAL PRIMARY KEY,
	name VARCHAR(120) UNIQUE NOT NULL,
	department INTEGER REFERENCES departments(id)
);


CREATE TABLE IF NOT EXISTS hods(
	id SERIAL PRIMARY KEY,
	id_num INTEGER UNIQUE NOT NULL,
	username VARCHAR(25) UNIQUE NOT NULL,
	email VARCHAR(255) UNIQUE NOT NULL,
	phone VARCHAR(12) UNIQUE NOT NULL,
	department INTEGER REFERENCES departments(id) ON DELETE CASCADE ON UPDATE CASCADE,
	fullname VARCHAR(255) NOT NULL,
	password VARCHAR(513) NOT NULL
);

CREATE TABLE IF NOT EXISTS admins(
	id SERIAL PRIMARY KEY,
	username VARCHAR(25) UNIQUE NOT NULL,
	fullname VARCHAR(120) NOT NULL,
	phone VARCHAR(12) UNIQUE NOT NULL,
	email VARCHAR(255) UNIQUE NOT NULL,
	password VARCHAR(513) NOT NULL

);

CREATE TABLE IF NOT EXISTS students(
	id SERIAL PRIMARY KEY, 
	firstname VARCHAR(25) NOT NULL, 
	surname VARCHAR(25) NOT NULL,
	lastname VARCHAR(25) NOT NULL,
	registration_no VARCHAR(25) UNIQUE NOT NULL,
    course INTEGER UNIQUE REFERENCES courses(id) ON DELETE CASCADE ON UPDATE CASCADE,
    year_of_study INTEGER NOT NULL,
    status BOOLEAN DEFAULT(TRUE),
    phone VARCHAR(12) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(25) UNIQUE NOT NULL,
    password VARCHAR(513) NOT NULL,
    semester INTEGER NOT NULL
  );

CREATE TABLE IF NOT EXISTS evaluations(
	id SERIAL PRIMARY KEY,
	start_date DATE DEFAULT(NOW()),
	end_date DATE DEFAULT(NOW()),
	status INTEGER DEFAULT(1) NOT NULL,
	note VARCHAR(513) DEFAULT('No Notes') NOT NULL
);

CREATE TABLE IF NOT EXISTS lecturers(
	id SERIAL PRIMARY KEY,
	email VARCHAR(255) UNIQUE NOT NULL,
	id_number INTEGER UNIQUE NOT NULL,
	phone VARCHAR(12) UNIQUE NOT NULL,
	name VARCHAR(255) NOT NULL,
	status BOOLEAN NOT NULL DEFAULT(true)

);

CREATE TABLE IF NOT EXISTS units(
	id SERIAL PRIMARY KEY,
	course INTEGER REFERENCES courses(id) ON DELETE CASCADE ON UPDATE CASCADE,
	year_of_study INTEGER NOT NULL,
	semester INTEGER NOT NULL,
	code VARCHAR(255) UNIQUE NOT NULL,
	title VARCHAR(255) NOT NULL,
	lecturer INTEGER DEFAULT(0) REFERENCES lecturers(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
	description VARCHAR(513) DEFAULT('No description') NOT NULL
);


CREATE TABLE IF NOT EXISTS questions(
	 id SERIAL PRIMARY KEY,
	 content VARCHAR(513) NOT NULL,
	 evaluation_id INTEGER REFERENCES evaluations(id) ON DELETE CASCADE ON UPDATE CASCADE,
	 question_type INTEGER DEFAULT(1) NOT NULL,
	 date_added DATE DEFAULT(NOW()) NOT NULL
);
CREATE TABLE IF NOT EXISTS responses(
	id SERIAL PRIMARY KEY,
	lecturer INTEGER REFERENCES lecturers(id) ON DELETE CASCADE ON UPDATE CASCADE,
	unit INTEGER REFERENCES units(id) ON DELETE CASCADE ON UPDATE CASCADE,
	student INTEGER REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE,
	value INTEGER NOT NULL,
	evaluation INTEGER REFERENCES evaluations(id) ON DELETE CASCADE ON UPDATE CASCADE,
	question INTEGER REFERENCES questions(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS evaluation_logs(
	id SERIAL PRIMARY KEY,
	evaluation_id INTEGER REFERENCES evaluations(id) ON DELETE CASCADE ON UPDATE CASCADE,
	student_id INTEGER REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE,
	lecturer INTEGER REFERENCES lecturers(id) ON DELETE CASCADE ON UPDATE CASCADE,
	unit_id INTEGER REFERENCES units(id) ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO admins(username, fullname, phone, email, password)VALUES('silaskenn', 'Silas Kenneth', '0791350402', 'silaskenn@gmail.com', '7e156e4442ca0844e24d9672cfee63032faa82a34b950a3dd7c6a1dd131b5d97bc9230452f4c36bde132eb9ff78cc143957552a2624dbf7c6bc5703c818dffae');