databaseconnect.sql

CREATE DATABASE MyDatabase;
GO
USE MyDatabase;
GO

-- TABLE: Assignments
CREATE TABLE dbo.Assignments (
    FacultyId       VARCHAR(100) NULL,
    Batch           VARCHAR(100) NULL,
    Semester        INT NULL,
    CreatedAt       DATETIME NULL,
    UpdatedAt       DATETIME NULL,
    Subject         NVARCHAR(200) NULL
);
GO

-- TABLE: Batches
CREATE TABLE dbo.Batches (
    Id              INT IDENTITY(1,1) PRIMARY KEY,
    CourseId        INT NULL,
    Section         NVARCHAR(20) NULL,
    SemesterNumber  INT NOT NULL,
    RoomNumber      NVARCHAR(100) NULL,
    BatchLabel      NVARCHAR(200) NULL
);
GO

-- TABLE: Courses
CREATE TABLE dbo.Courses (
    Id              INT IDENTITY(1,1) PRIMARY KEY,
    Name            NVARCHAR(200) NOT NULL
);
GO

-- TABLE: Faculties
CREATE TABLE dbo.Faculties (
    Id              INT IDENTITY(1,1) PRIMARY KEY,
    Name            NVARCHAR(200) NULL,
    Email           NVARCHAR(200) NULL,
    Password        NVARCHAR(1020) NULL,
    Role            NVARCHAR(80) NULL,
    Position        NVARCHAR(200) NULL,
    AmiId           NVARCHAR(200) NULL,
    UUID            UNIQUEIDENTIFIER NULL,
    CreatedAt       DATETIME2 NULL,
    UpdatedAt       DATETIME2 NULL,
    Department      NVARCHAR(200) NULL
);
GO

-- TABLE: FacultyAssignments
CREATE TABLE dbo.FacultyAssignments (
    Id              INT IDENTITY(1,1) PRIMARY KEY,
    FacultyId       INT NOT NULL,
    SubjectId       INT NOT NULL,
    BatchId         INT NOT NULL
);
GO

-- TABLE: FacultyRoles
CREATE TABLE dbo.FacultyRoles (
    Id              INT IDENTITY(1,1) PRIMARY KEY,
    FacultyId       INT NOT NULL,
    Role            NVARCHAR(200) NOT NULL
);
GO

-- TABLE: Options
CREATE TABLE dbo.Options (
    Id              INT IDENTITY(1,1) PRIMARY KEY,
    QuestionId      INT NULL,
    Text            NVARCHAR(MAX) NULL,
    IsCorrect       BIT NULL
);
GO

-- TABLE: Questions
CREATE TABLE dbo.Questions (
    Id              INT IDENTITY(1,1) PRIMARY KEY,
    QuizId          INT NULL,
    Text            NVARCHAR(MAX) NULL,
    CorrectAnswer   NVARCHAR(MAX) NULL
);
GO

-- Relationships (guessed from names, adjust as needed)
ALTER TABLE dbo.Batches
    ADD CONSTRAINT FK_Batches_Courses FOREIGN KEY (CourseId)
    REFERENCES dbo.Courses(Id);

ALTER TABLE dbo.FacultyAssignments
    ADD CONSTRAINT FK_FacultyAssignments_Faculties FOREIGN KEY (FacultyId)
    REFERENCES dbo.Faculties(Id);

ALTER TABLE dbo.FacultyAssignments
    ADD CONSTRAINT FK_FacultyAssignments_Batches FOREIGN KEY (BatchId)
    REFERENCES dbo.Batches(Id);

ALTER TABLE dbo.Options
    ADD CONSTRAINT FK_Options_Questions FOREIGN KEY (QuestionId)
    REFERENCES dbo.Questions(Id);
GO