Unification Representation dz

URdz.sql

CREATE TABLE Teachers (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(MAX) NOT NULL,
    Surname NVARCHAR(MAX) NOT NULL
);

CREATE TABLE Assistants (
    Id INT PRIMARY KEY IDENTITY(1,1),
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teachers(Id)
);

CREATE TABLE Curators (
    Id INT PRIMARY KEY IDENTITY(1,1),
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teachers(Id)
);

CREATE TABLE Deans (
    Id INT PRIMARY KEY IDENTITY(1,1),
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teachers(Id)
);

CREATE TABLE Heads (
    Id INT PRIMARY KEY IDENTITY(1,1),
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teachers(Id)
);

CREATE TABLE Faculties (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Building INT NOT NULL CHECK (Building BETWEEN 1 AND 5),
    Name NVARCHAR(100) UNIQUE NOT NULL,
    DeanId INT NOT NULL FOREIGN KEY REFERENCES Deans(Id)
);

CREATE TABLE Departments (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Building INT NOT NULL CHECK (Building BETWEEN 1 AND 5),
    Name NVARCHAR(100) UNIQUE NOT NULL,
    FacultyId INT NOT NULL FOREIGN KEY REFERENCES Faculties(Id),
    HeadId INT NOT NULL FOREIGN KEY REFERENCES Heads(Id)
);

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

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

CREATE TABLE LectureRooms (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Building INT NOT NULL CHECK (Building BETWEEN 1 AND 5),
    Name NVARCHAR(10) UNIQUE NOT NULL
);

CREATE TABLE Subjects (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE Lectures (
    Id INT PRIMARY KEY IDENTITY(1,1),
    SubjectId INT NOT NULL FOREIGN KEY REFERENCES Subjects(Id),
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teachers(Id)
);

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

CREATE TABLE Schedules (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Class INT NOT NULL CHECK (Class BETWEEN 1 AND 8),
    DayOfWeek INT NOT NULL CHECK (DayOfWeek BETWEEN 1 AND 7),
    Week INT NOT NULL CHECK (Week BETWEEN 1 AND 52),
    LectureId INT NOT NULL FOREIGN KEY REFERENCES Lectures(Id),
    LectureRoomId INT NOT NULL FOREIGN KEY REFERENCES LectureRooms(Id)
);













-- 1
SELECT Name
FROM LectureRooms
WHERE Id IN (
    SELECT LectureRoomId
    FROM Schedules
    WHERE LectureId IN (
        SELECT Id
        FROM Lectures
        WHERE TeacherId IN (
            SELECT Id FROM Teachers
            WHERE Name = 'Edward' AND Surname = 'Hopper'
        )
    )
);

-- 2
SELECT Surname
FROM Teachers
WHERE Id IN (
    SELECT TeacherId
    FROM Assistants
    WHERE TeacherId IN (
        SELECT TeacherId
        FROM Lectures
        WHERE Id IN (
            SELECT LectureId
            FROM GroupsLectures
            WHERE GroupId IN (
                SELECT Id FROM Groups WHERE Name = 'F505'
            )
        )
    )
);

-- 3
SELECT Name
FROM Subjects
WHERE Id IN (
    SELECT SubjectId
    FROM Lectures
    WHERE TeacherId IN (
        SELECT Id FROM Teachers
        WHERE Name = 'Alex' AND Surname = 'Carmack'
    )
    AND Id IN (
        SELECT LectureId
        FROM GroupsLectures
        WHERE GroupId IN (
            SELECT Id FROM Groups WHERE Year = 5
        )
    )
);

-- 4
SELECT Surname
FROM Teachers
WHERE Id NOT IN (
    SELECT TeacherId
    FROM Lectures
    WHERE Id IN (
        SELECT LectureId FROM Schedules WHERE DayOfWeek = 1
    )
);

-- 5
SELECT Name, Building
FROM LectureRooms
WHERE Id NOT IN (
    SELECT LectureRoomId
    FROM Schedules
    WHERE DayOfWeek = 3 AND Week = 2 AND Class = 3
);

-- 6
SELECT Name, Surname
FROM Teachers
WHERE Id IN (
    SELECT HeadId
    FROM Departments
    WHERE FacultyId IN (
        SELECT Id FROM Faculties WHERE Name = 'Computer Science'
    )
)
AND Id NOT IN (
    SELECT TeacherId
    FROM Curators
    WHERE Id IN (
        SELECT CuratorId
        FROM GroupsCurators
        WHERE GroupId IN (
            SELECT Id
            FROM Groups
            WHERE DepartmentId IN (
                SELECT Id FROM Departments WHERE Name = 'Software Development'
            )
        )
    )
);

-- 7
SELECT Building FROM Faculties
UNION
SELECT Building FROM Departments
UNION
SELECT Building FROM LectureRooms;

-- 8
SELECT Name, Surname, 'Dean' AS Status
FROM Teachers WHERE Id IN (SELECT TeacherId FROM Deans)
UNION
SELECT Name, Surname, 'Head'
FROM Teachers WHERE Id IN (SELECT TeacherId FROM Heads)
UNION
SELECT Name, Surname, 'Curator'
FROM Teachers WHERE Id IN (SELECT TeacherId FROM Curators)
UNION
SELECT Name, Surname, 'Assistant'
FROM Teachers WHERE Id IN (SELECT TeacherId FROM Assistants)
UNION
SELECT Name, Surname, 'Teacher'
FROM Teachers;

-- 9
SELECT DISTINCT DayOfWeek
FROM Schedules
WHERE LectureRoomId IN (
    SELECT Id FROM LectureRooms WHERE Name IN ('A311', 'A104')
);

-- 10
CREATE VIEW TeachersInfo AS
SELECT Name, Surname,
       CASE WHEN Id IN (SELECT TeacherId FROM Assistants) THEN 'Assistant' END AS AssistantStatus,
       CASE WHEN Id IN (SELECT TeacherId FROM Deans) THEN 'Dean' END AS DeanStatus,
       CASE WHEN Id IN (SELECT TeacherId FROM Curators) THEN 'Curator' END AS CuratorStatus,
       CASE WHEN Id IN (SELECT TeacherId FROM Heads) THEN 'Head' END AS HeadStatus
FROM Teachers;

-- 11
CREATE VIEW FacultiesInfo AS
SELECT Name AS FacultyName,
       (SELECT Name + ' ' + Surname
        FROM Teachers
        WHERE Id = (SELECT TeacherId FROM Deans WHERE Id = Faculties.DeanId)
       ) AS DeanName
FROM Faculties;

GO
DROP VIEW IF EXISTS GroupsInfo;
GO
CREATE VIEW GroupsInfo AS
SELECT Name AS GroupName, Year,
       (SELECT Name + ' ' + Surname
        FROM Teachers
        WHERE Id = (SELECT TeacherId
                    FROM Curators
                    WHERE Id = (SELECT CuratorId
                                FROM GroupsCurators
                                WHERE GroupId = Groups.Id)
                   )
       ) AS CuratorName
FROM Groups;
GO