ITI Examination SystemDatabase Management

-- Core Tables Structure
CREATE TABLE dbo.Users (
    UserID       INT            IDENTITY(1,1) PRIMARY KEY,
    Username     VARCHAR(50)    NOT NULL UNIQUE,
    [Password]   VARCHAR(255)   NOT NULL,
    UserType     VARCHAR(20)    NOT NULL
);
GO

CREATE TABLE dbo.Students (
    StudentID      INT            IDENTITY(1,1) PRIMARY KEY,
    UserID         INT            NOT NULL,
    NationalID     CHAR(14)       NOT NULL,
    FirstName      VARCHAR(50)    NOT NULL,
    LastName       VARCHAR(50)    NOT NULL,
    Email          VARCHAR(100)   NOT NULL,
    Phone          VARCHAR(15)    NULL,
    Address        VARCHAR(200)   NULL,
    EnrollmentDate DATE           NOT NULL,
    BranchIntakeTrackID INT NOT NULL,
    IsActive       BIT            NOT NULL DEFAULT(1),
    CONSTRAINT FK_Students_User FOREIGN KEY (UserID) REFERENCES dbo.Users(UserID),
    CONSTRAINT FK_BranchIntakeTrack FOREIGN KEY (BranchIntakeTrackID) 
        REFERENCES dbo.BranchIntakeTrack(BranchIntakeTrackID)
);
GO

CREATE TABLE dbo.Exams (
    ExamID           INT            IDENTITY(1,1) PRIMARY KEY,
    ExamTitle         VARCHAR(200)   NOT NULL,
    ExamType          VARCHAR(50)    NOT NULL,
    TotalDegrees      DECIMAL(5,2)  NOT NULL,
    ExamDuration      INT            NOT NULL,
    StartDateTime     DATETIME       NOT NULL,
    EndDateTime       DATETIME       NOT NULL,
    IsRandomOrder     BIT            NOT NULL DEFAULT(0),
    QuestionNumber    INT            NOT NULL,
    CourseID          INT            NOT NULL,
    InstructorID      INT            NOT NULL,
    BranchIntakeTrackID INT          NOT NULL,
    CONSTRAINT FK_Exams_Course FOREIGN KEY (CourseID) REFERENCES dbo.Courses(CourseID),
    CONSTRAINT FK_Exams_Instructor FOREIGN KEY (InstructorID) REFERENCES dbo.Instructors(InstructorID)
);
GO