Каждый студент обязательно состоит ровно в одной группе. Группа целиком записана на определенные предметы у определенного набора преподавателей (лектор, практики). Группа имеет уникальное название. Преподаватели могу преподавать один или больше предметов. Преподаватель может ставить оценки ученикам (сколько угодно). Преподаватель может быть еще и студентом. Группы не пустые.

# Statements

Спроектируйте базу данных «Университет», позволяющую хранить информацию о студентах, группах, преподавателях, дисциплинах и оценках.

  1. Составьте модель сущность-связь.
  2. Преобразуйте модель сущность-связь в физическую модель.
  3. Запишите физическую модель на языке SQL. Запись должна включать объявления ограничений.
  4. Создайте базу данных по спроектированной модели.
  5. Запишите операторы SQL, заполняющие базу тестовыми данными. Достаточно 2–3 записей на таблицу, если они в полной мере демонстрируют особенности БД.

Примечания:

  1. Не требуется поддержка:
    • нескольких университетов;
    • дисциплин по выбору;
    • дисциплин с необычным распределением по группам (таких как физическая культура и иностранный язык);
    • переводов между группами;
    • нескольких оценок по одной дисциплине.
  2. Многосеместровые дисциплины считаются по семестрам, например: Математический анализ (семестр 1); Математический анализ (семестр 2).

# Модель сущность связь

## Нотация

Ограничения с нотацией по Мерису. Атрибуты по умолчанию обязательны, связи по умолчанию одиночные не обязательные

## Сущности и атрибуты

Subject - дисциплина. Name - уникальное название дисциплины. Group - учебная группа. Name - уникальный номер группы. Person - Человек (преподаватель либо студент). Name - ФИО. Teacher - Преподаватель (определенный человек). Role - Преподавательская роль (лектор, практик, ...) Mark - оценка. Выставлена определенным преподвателем ровно одному студенту

## Связи и ассоциации

ActiveSubject - Читающаяся определенная дисциплина. Может проводиться одним или более преподавателями у одной или более груп. По читающимся дисциплинам могут выставляться отметки. Оценка выставлятся по определенному предмету, определенным преподавателем в определнной группе определенному студенту. Предмет может не читаться, преподаватели могу не вести никакой предмет, группа может быть не записана ни на один предмет. Учебная группа включает как минимум одного студента Человек может состоять опционально в группе (студент) и может опционально быть преподавателем

# Физическая модель

## Нотация

Столбцы по умолчанию обязательны

## Домены

name → varchar(50) mark -> int role -> varchar(50) id -> int

## Преобразования

Связь Person - Group преобразуется в join-таблицу и столбец PersonGroupId в Group, который гарантирует непустосту группы. Ассоциация ActiveSubject сначала преобразуется в слабую сущность с идентифицируещими одинарными связями к Subject, Teacher и Group, связь с Mark становиться связью один (строго один) к многим (ноль и больше). В соответсвующую таблицу добавляются идентифицирующие поля. Mark имеет связь с ActiveSubject и Person - добавляются соответсвующие внешние ключи.

# SQL

CREATE DATABASE hw2;

## DDL

DROP TABLE IF EXISTS Subjects CASCADE;
DROP TABLE IF EXISTS Marks CASCADE;
DROP TABLE IF EXISTS Persons CASCADE;
DROP TABLE IF EXISTS PersonGroups CASCADE;
DROP TABLE IF EXISTS Groups CASCADE;
DROP TABLE IF EXISTS Teachers CASCADE;
DROP TABLE IF EXISTS ActiveSubjects CASCADE;

CREATE TABLE Persons (
       Id int GENERATED ALWAYS AS IDENTITY,
       Name varchar(50) not null,
       PRIMARY KEY (Id)
);

CREATE TABLE Subjects (
       Name varchar(50) not null,
       PRIMARY KEY (Name)
);

CREATE TABLE Groups (
       Name varchar(50) not null,
       PersonGroupId int not null,
       PRIMARY KEY (Name)
);

CREATE TABLE Teachers (
       Role varchar(50) not null,
       PersonId int not null,
       PRIMARY KEY (Role, PersonId),
       FOREIGN KEY (PersonId)
       REFERENCES Persons(Id)
);

CREATE TABLE ActiveSubjects (
       SubjectName varchar(50) not null,
       TeacherId int not null,
       TeacherRole varchar(50) not null,
       GroupName varchar(50) not null,
       PRIMARY KEY (SubjectName, TeacherId, TeacherRole, GroupName),
       FOREIGN KEY (SubjectName)
       REFERENCES Subjects(Name),
       FOREIGN KEY (GroupName)
       REFERENCES Groups(Name),
       FOREIGN KEY (TeacherId, TeacherRole)
       REFERENCES Teachers(PersonId, Role)
);


CREATE TABLE Marks (
       Id int GENERATED ALWAYS AS IDENTITY,
       Mark int not null,
       TeacherId int not null,
       TeacherRole varchar(50) not null,
       SubjectName varchar(50) not null,
       GroupName varchar(50) not null,
       StudentId int not null,
       PRIMARY KEY (Id),
       FOREIGN KEY (StudentId)
       REFERENCES Persons(Id),
       FOREIGN KEY (TeacherId, TeacherRole, SubjectName, GroupName)
       REFERENCES ActiveSubjects(TeacherId, TeacherRole, SubjectName, GroupName)
);

CREATE TABLE PersonGroups (
       GroupName varchar(50) not null,
       PersonId int not null,
       PRIMARY KEY (PersonId),
       FOREIGN KEY (PersonId)
       REFERENCES Persons(Id)
);

ALTER TABLE Groups
      ADD CONSTRAINT fk_person_group_id
	  FOREIGN KEY (PersonGroupId)
	  REFERENCES PersonGroups(PersonId)
      DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE PersonGroups
      ADD CONSTRAINT fk_group_name
	  FOREIGN KEY (GroupName)
	  REFERENCES Groups(Name)
      DEFERRABLE INITIALLY IMMEDIATE;

## DML

INSERT INTO Persons (Name) VALUES
       ('Иванов Иван'),
       ('Петров Петр'),
       ('Казимиров Казимир'),
       ('Игнатьев Игнат'),
       ('Тимуров Тимур'),
       ('Станиславов Станислав'),
       ('Корнеев Георгий'),
       ('Кохась Константин'),
       ('Маврин Павел');

SELECT Name FROM Persons;

INSERT INTO Subjects (Name) VALUES
       ('Матанализ'),
       ('Базы Данных'),
       ('АиСД');

SELECT (Name) FROM Subjects;

BEGIN TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;

INSERT INTO Groups(Name, PersonGroupId)
SELECT 'M3139', Persons.Id
FROM Persons
WHERE Persons.Name = 'Иванов Иван';

INSERT INTO PersonGroups(GroupName, PersonId)
SELECT 'M3139', Persons.Id
FROM Persons
WHERE Persons.Name = 'Иванов Иван';

INSERT INTO PersonGroups(GroupName, PersonId)
SELECT 'M3139', Persons.Id
FROM Persons
WHERE Persons.Name = 'Игнатьев Игнат';

INSERT INTO Groups(Name, PersonGroupId)
SELECT 'M34371', Persons.Id
FROM Persons
WHERE Persons.Name = 'Петров Петр';

INSERT INTO PersonGroups(GroupName, PersonId)
SELECT 'M34371', Persons.Id
FROM Persons
WHERE Persons.Name = 'Петров Петр';

INSERT INTO PersonGroups(GroupName, PersonId)
SELECT 'M34371', Persons.Id
FROM Persons
WHERE Persons.Name = 'Тимуров Тимур';

INSERT INTO Groups(Name, PersonGroupId)
SELECT 'M3234', Persons.Id
FROM Persons
WHERE Persons.Name = 'Казимиров Казимир';

INSERT INTO PersonGroups(GroupName, PersonId)
SELECT 'M3234', Persons.Id
FROM Persons
WHERE Persons.Name = 'Казимиров Казимир';

INSERT INTO PersonGroups(GroupName, PersonId)
SELECT 'M3234', Persons.Id
FROM Persons
WHERE Persons.Name = 'Станиславов Станислав';

COMMIT;

SELECT Name FROM Groups;

SELECT GroupName, Persons.Name AS StudentName FROM PersonGroups
JOIN Persons ON Persons.Id = PersonId;

INSERT INTO Teachers(Role, PersonId)
SELECT 'Лектор', Persons.Id
FROM Persons
WHERE Persons.Name = 'Корнеев Георгий';

INSERT INTO Teachers(Role, PersonId)
SELECT 'Лектор', Persons.Id
FROM Persons
WHERE Persons.Name = 'Кохась Константин';

INSERT INTO Teachers(Role, PersonId)
SELECT 'Лектор', Persons.Id
FROM Persons
WHERE Persons.Name = 'Маврин Павел';

INSERT INTO Teachers(Role, PersonId)
SELECT 'Практик', Persons.Id
FROM Persons
WHERE Persons.Name = 'Маврин Павел';

SELECT Role, Persons.Name AS TeacherName FROM Teachers
JOIN Persons ON Persons.Id = Teachers.PersonId;

INSERT INTO ActiveSubjects(SubjectName, GroupName, TeacherId, TeacherRole)
SELECT 'Матанализ', 'M3139', Teachers.PersonId, Teachers.Role
FROM Teachers
     JOIN Persons ON Teachers.PersonId = Persons.Id
WHERE Persons.Name = 'Кохась Константин'
      AND Teachers.Role = 'Лектор';

INSERT INTO ActiveSubjects(SubjectName, GroupName, TeacherId, TeacherRole)
SELECT 'Базы Данных', 'M34371', Teachers.PersonId, Teachers.Role
FROM Teachers
     JOIN Persons ON Teachers.PersonId = Persons.Id
WHERE Persons.Name = 'Корнеев Георгий'
      AND Teachers.Role = 'Лектор';

INSERT INTO ActiveSubjects(SubjectName, GroupName, TeacherId, TeacherRole)
SELECT 'АиСД', 'M3234', Teachers.PersonId, Teachers.Role
FROM Teachers
     JOIN Persons ON Teachers.PersonId = Persons.Id
WHERE Persons.Name = 'Маврин Павел'
      AND Teachers.Role = 'Лектор';

INSERT INTO ActiveSubjects(SubjectName, GroupName, TeacherId, TeacherRole)
SELECT 'АиСД', 'M3234', Teachers.PersonId, Teachers.Role
FROM Teachers
     JOIN Persons ON Teachers.PersonId = Persons.Id
WHERE Persons.Name = 'Маврин Павел'
      AND Teachers.Role = 'Практик';

SELECT TeacherRole, SubjectName, GroupName, Teachers.Name AS TeacherName FROM ActiveSubjects
JOIN Persons AS Teachers ON TeacherId = Teachers.Id;

INSERT INTO Marks(Mark, SubjectName, TeacherId, TeacherRole, GroupName, StudentId)
SELECT 10, SubjectName, TeacherId, TeacherRole, GroupName, StudentId
FROM (
     SELECT
	SubjectName,
	TeacherId,
	TeacherRole,
	ActiveSubjects.GroupName AS GroupName,
	Students.Id AS StudentId
     FROM ActiveSubjects
	  JOIN Persons AS Teachers
	    ON ActiveSubjects.TeacherId = Teachers.Id
	  JOIN PersonGroups
	    ON ActiveSubjects.GroupName = PersonGroups.GroupName
	  JOIN Persons AS Students
	    ON PersonGroups.PersonId = Students.Id
     WHERE SubjectName = 'Матанализ'
	   AND Teachers.Name = 'Кохась Константин'
	   AND ActiveSubjects.TeacherRole = 'Лектор'
	   AND ActiveSubjects.GroupName = 'M3139'
	   AND Students.Name = 'Иванов Иван'
     ) subjects_and_person;

INSERT INTO Marks(Mark, SubjectName, TeacherId, TeacherRole, GroupName, StudentId)
SELECT -1, SubjectName, TeacherId, TeacherRole, GroupName, StudentId
FROM (
     SELECT
	SubjectName,
	TeacherId,
	TeacherRole,
	ActiveSubjects.GroupName AS GroupName,
	Students.Id AS StudentId
     FROM ActiveSubjects
	  JOIN Persons AS Teachers
	    ON ActiveSubjects.TeacherId = Teachers.Id
	  JOIN PersonGroups
	    ON ActiveSubjects.GroupName = PersonGroups.GroupName
	  JOIN Persons AS Students
	    ON PersonGroups.PersonId = Students.Id
     WHERE SubjectName = 'Базы Данных'
	   AND Teachers.Name = 'Корнеев Георгий'
	   AND ActiveSubjects.TeacherRole = 'Лектор'
	   AND ActiveSubjects.GroupName = 'M34371'
	   AND Students.Name = 'Петров Петр'
     ) subjects_and_person;

INSERT INTO Marks(Mark, SubjectName, TeacherId, TeacherRole, GroupName, StudentId)
SELECT 5, SubjectName, TeacherId, TeacherRole, GroupName, StudentId
FROM (
     SELECT
	SubjectName,
	TeacherId,
	TeacherRole,
	ActiveSubjects.GroupName AS GroupName,
	Students.Id AS StudentId
     FROM ActiveSubjects
	  JOIN Persons AS Teachers
	    ON ActiveSubjects.TeacherId = Teachers.Id
	  JOIN PersonGroups
	    ON ActiveSubjects.GroupName = PersonGroups.GroupName
	  JOIN Persons AS Students
	    ON PersonGroups.PersonId = Students.Id
     WHERE SubjectName = 'АиСД'
	   AND Teachers.Name = 'Маврин Павел'
	   AND ActiveSubjects.TeacherRole = 'Лектор'
	   AND ActiveSubjects.GroupName = 'M3234'
	   AND Students.Name = 'Казимиров Казимир'
     ) subjects_and_person;

INSERT INTO Marks(Mark, SubjectName, TeacherId, TeacherRole, GroupName, StudentId)
SELECT 2, SubjectName, TeacherId, TeacherRole, GroupName, StudentId
FROM (
     SELECT
	SubjectName,
	TeacherId,
	TeacherRole,
	ActiveSubjects.GroupName AS GroupName,
	Students.Id AS StudentId
     FROM ActiveSubjects
	  JOIN Persons AS Teachers
	    ON ActiveSubjects.TeacherId = Teachers.Id
	  JOIN PersonGroups
	    ON ActiveSubjects.GroupName = PersonGroups.GroupName
	  JOIN Persons AS Students
	    ON PersonGroups.PersonId = Students.Id
     WHERE SubjectName = 'АиСД'
	   AND Teachers.Name = 'Маврин Павел'
	   AND ActiveSubjects.TeacherRole = 'Практик'
	   AND ActiveSubjects.GroupName = 'M3234'
	   AND Students.Name = 'Казимиров Казимир'
     ) subjects_and_person;

SELECT Mark, TeacherRole, SubjectName, GroupName, Students.Name AS StudentName, Teachers.Name AS TeacherName FROM Marks
JOIN Persons AS Students ON StudentId = Students.Id
JOIN Persons AS Teachers ON TeacherId = Teachers.Id;

# Comments

  1. Модель сущность-связь
    Студенты не учатся в группах
  2. Физическая модель
    Один домен используется для несвязанных атрибутов (обычно имя студента и название предмета): name
  3. DDL
    Необоснованное использование alter-table
  4. DML
    Использование SELECT в DDL или DML
    Использование set constraints all deferred (надо откладывать только требуемые ограничения)
    Непереносимый SQL: SET CONSTRAINTS ALL DEFERRED
Made with Org mode, © 2023 - ∞ iliayar