academy

academy.sql

CREATE TABLE Curator (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(MAX) NOT NULL CHECK (Name <> ''),
    Surname NVARCHAR(MAX) NOT NULL CHECK (Surname <> '')
);

CREATE TABLE Faculty (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Financing MONEY NOT NULL CHECK (Financing >= 0) DEFAULT 0,
    Name NVARCHAR(100) NOT NULL UNIQUE CHECK (Name <> '')
);

CREATE TABLE Department (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Financing MONEY NOT NULL CHECK (Financing >= 0) DEFAULT 0,
    Name NVARCHAR(100) NOT NULL UNIQUE CHECK (Name <> ''),
    FacultyId INT NOT NULL
        FOREIGN KEY REFERENCES Faculty(Id)
);



CREATE TABLE Subject (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL UNIQUE CHECK (Name <> '')
);

CREATE TABLE Teacher (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(MAX) NOT NULL CHECK (Name <> ''),
    Surname NVARCHAR(MAX) NOT NULL CHECK (Surname <> ''),
    Salary MONEY NOT NULL CHECK (Salary > 0)
);

CREATE TABLE Lecture (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    LectureRoom NVARCHAR(MAX) NOT NULL CHECK (LectureRoom <> ''),
    SubjectId INT NOT NULL FOREIGN KEY REFERENCES Subject(Id),
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teacher(Id)
);




CREATE TABLE Groups (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(10) NOT NULL UNIQUE CHECK (Name <> ''),
    Year INT NOT NULL CHECK (Year BETWEEN 1 AND 5),
    DepartmentId INT NOT NULL
        FOREIGN KEY REFERENCES Department(Id)
);

CREATE TABLE GroupCurator (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CuratorId INT NOT NULL FOREIGN KEY REFERENCES Curator(Id),
    GroupId INT NOT NULL FOREIGN KEY REFERENCES Groups(Id)
);

CREATE TABLE GroupLecture (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    GroupId INT NOT NULL FOREIGN KEY REFERENCES Groups(Id),
    LectureId INT NOT NULL FOREIGN KEY REFERENCES Lecture(Id)
);















INSERT INTO Faculty (Financing, Name)
VALUES (100000, 'Engineering'),
       (80000, 'Science'),
       (60000, 'Arts');

INSERT INTO Department (Financing, Name, FacultyId)
VALUES (50000, 'Computer Science', 1),
       (30000, 'Physics', 2),
       (20000, 'History', 3);

INSERT INTO Curator (Name, Surname)
VALUES ('Alice', 'Johnson'),
       ('Bob', 'Smith'),
       ('Clara', 'Brown');

INSERT INTO Groups (Name, Year, DepartmentId)
VALUES ('CS101', 1, 1),
       ('PHY201', 2, 2),
       ('HIS301', 3, 3);


INSERT INTO GroupCurator (CuratorId, GroupId)
VALUES (1, 1),
       (2, 2),
       (3, 3);
INSERT INTO Subject (Name)
VALUES ('Databases'),
       ('Quantum Mechanics'),
       ('World History');


INSERT INTO Teacher (Name, Surname, Salary)
VALUES ('David', 'Miller', 2000),
       ('Emma', 'Wilson', 2500),
       ('Frank', 'Taylor', 1800);


INSERT INTO Lecture (LectureRoom, SubjectId, TeacherId)
VALUES ('Room A1', 1, 1),
       ('Room B2', 2, 2),
       ('Room C3', 3, 3);


INSERT INTO GroupLecture (GroupId, LectureId)
VALUES (1, 1),
       (2, 2),
       (3, 3);