SQLize Online / PHPize Online  /  SQLtest Online

CREATE DATABASE Examination_System GO USE Examination_System GO -- Instructor table CREATE TABLE Instructor ( Ins_ID INT PRIMARY KEY IDENTITY(1, 1), Ins_Fname VARCHAR(50) NOT NULL, Ins_Lname VARCHAR(50) NOT NULL, Ins_Email VARCHAR(100) NOT NULL UNIQUE, Ins_Password VARCHAR(255) NOT NULL, Ins_UserName VARCHAR(100) NOT NULL UNIQUE ); -- Student table CREATE TABLE Student ( St_ID INT PRIMARY KEY IDENTITY(1, 1), St_Fname VARCHAR(50) NOT NULL, St_Lname VARCHAR(50) NOT NULL, St_Email VARCHAR(100) NOT NULL UNIQUE, St_Password VARCHAR(255) NOT NULL, St_UserName VARCHAR(100) NOT NULL UNIQUE ); -- Course table CREATE TABLE Course ( Crs_ID INT PRIMARY KEY IDENTITY(1, 1), Crs_Name VARCHAR(100) NOT NULL, Crs_Description TEXT, Crs_MaxDegree INT NOT NULL, Crs_MinDegree INT NOT NULL ); -- Question_Pool table CREATE TABLE Question_Pool ( Quest_ID INT PRIMARY KEY IDENTITY(1, 1), Quest_Name TEXT NOT NULL, Quest_CorrectAns CHAR(1) NOT NULL, Quest_Type VARCHAR(50) NOT NULL, Crs_ID INT, FOREIGN KEY (Crs_ID) REFERENCES Course (Crs_ID) ); -- Quest_Options table CREATE TABLE Quest_Options ( Quest_ID INT, Quest_Options VARCHAR(255), Ans_Content TEXT NOT NULL, PRIMARY KEY (Quest_ID, Quest_Options), FOREIGN KEY (Quest_ID) REFERENCES Question_Pool (Quest_ID) ); -- Exam table CREATE TABLE Exam ( Ex_ID INT PRIMARY KEY IDENTITY(1, 1), Ex_Name VARCHAR(100) NOT NULL, Ex_MinDegree INT NOT NULL, Ex_MaxDegree INT NOT NULL, Ex_Year INT NOT NULL, Ex_StartTime DATETIME NOT NULL, Ex_EndTime DATETIME NOT NULL, Ins_ID INT, Crs_ID INT, [Time] INT NOT NULL, [Date] DATE NOT NULL, FOREIGN KEY (Ins_ID) REFERENCES Instructor (Ins_ID), FOREIGN KEY (Crs_ID) REFERENCES Course (Crs_ID) ); -- Training_Manager table CREATE TABLE Training_Manager ( TM_ID INT PRIMARY KEY IDENTITY(1, 1), TM_Fname VARCHAR(50) NOT NULL, TM_Lname VARCHAR(50) NOT NULL, TM_Email VARCHAR(100) NOT NULL UNIQUE, TM_Password VARCHAR(255) NOT NULL, TM_UserName VARCHAR(100) NOT NULL UNIQUE ); -- Branch table CREATE TABLE Branch ( Br_ID INT PRIMARY KEY IDENTITY(1, 1), Br_Name VARCHAR(100) NOT NULL, Br_Location VARCHAR(255) NOT NULL, TM_ID INT, FOREIGN KEY (TM_ID) REFERENCES Training_Manager (TM_ID) ); -- Intake table CREATE TABLE Intake ( In_ID INT PRIMARY KEY IDENTITY(1, 1), In_Description TEXT, In_Name VARCHAR(100) NOT NULL, Br_ID INT, FOREIGN KEY (Br_ID) REFERENCES Branch (Br_ID) ); -- Track table CREATE TABLE Track ( Tr_ID INT PRIMARY KEY IDENTITY(1, 1), Tr_Name VARCHAR(100) NOT NULL, Tr_Description TEXT, In_ID INT, FOREIGN KEY (In_ID) REFERENCES Intake (In_ID) ); -- Many-to-Many Relationships -- Student-Exam relationship CREATE TABLE St_Ex ( St_ID INT, Ex_ID INT, [Result] INT, PRIMARY KEY (St_ID, Ex_ID), FOREIGN KEY (St_ID) REFERENCES Student (St_ID), FOREIGN KEY (Ex_ID) REFERENCES Exam (Ex_ID) ); -- Student-Course relationship CREATE TABLE St_Crs ( St_ID INT, Crs_ID INT, PRIMARY KEY (St_ID, Crs_ID), FOREIGN KEY (St_ID) REFERENCES Student (St_ID), FOREIGN KEY (Crs_ID) REFERENCES Course (Crs_ID) ); -- Course-Instructor relationship CREATE TABLE Crs_Ins ( Crs_ID INT, Ins_ID INT, PRIMARY KEY (Crs_ID, Ins_ID), FOREIGN KEY (Crs_ID) REFERENCES Course (Crs_ID), FOREIGN KEY (Ins_ID) REFERENCES Instructor (Ins_ID) ); -- Questions-Instructor relationship CREATE TABLE Quest_Ins ( Quest_ID INT, Ins_ID INT, Quest_Degree INT NOT NULL, PRIMARY KEY (Quest_ID, Ins_ID), FOREIGN KEY (Quest_ID) REFERENCES Question_Pool (Quest_ID), FOREIGN KEY (Ins_ID) REFERENCES Instructor (Ins_ID) ); -- Question-Exam relationship CREATE TABLE Quest_Ex ( Quest_ID INT, Ex_ID INT, PRIMARY KEY (Quest_ID, Ex_ID), FOREIGN KEY (Quest_ID) REFERENCES Question_Pool (Quest_ID), FOREIGN KEY (Ex_ID) REFERENCES Exam (Ex_ID) ); -- Student-Manager relationship CREATE TABLE St_Mgr ( St_ID INT PRIMARY KEY, TM_ID INT, FOREIGN KEY (St_ID) REFERENCES Student (St_ID), FOREIGN KEY (TM_ID) REFERENCES Training_Manager (TM_ID) ); -- Student-Branch relationship CREATE TABLE St_Br ( St_ID INT PRIMARY KEY, Br_ID INT, FOREIGN KEY (St_ID) REFERENCES Student (St_ID), FOREIGN KEY (Br_ID) REFERENCES Branch (Br_ID) ); -- Student-Exam-Question relationship CREATE TABLE St_Ex_Quest ( St_ID INT, Ex_ID INT, Quest_ID INT, PRIMARY KEY (St_ID, Ex_ID, Quest_ID), Answer CHAR(1), FOREIGN KEY (St_ID) REFERENCES Student (St_ID), FOREIGN KEY (Ex_ID) REFERENCES Exam (Ex_ID), FOREIGN KEY (Quest_ID) REFERENCES Question_Pool (Quest_ID), CONSTRAINT Quest_Ex_FK FOREIGN KEY (Quest_ID, Ex_ID) REFERENCES Quest_Ex (Quest_ID, Ex_ID) )