Skip to content

Latest commit

 

History

History
572 lines (502 loc) · 20.9 KB

README.org

File metadata and controls

572 lines (502 loc) · 20.9 KB
Raw

HW 2

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

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;
CREATE DATABASE

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;
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE

DML

INSERT INTO Persons (Name) VALUES
       ('Иванов Иван'),
       ('Петров Петр'),
       ('Казимиров Казимир'),
       ('Игнатьев Игнат'),
       ('Тимуров Тимур'),
       ('Станиславов Станислав'),
       ('Корнеев Георгий'),
       ('Кохась Константин'),
       ('Маврин Павел');
INSERT 0 9
 
SELECT Name FROM Persons;
name
Иванов Иван
Петров Петр
Казимиров Казимир
Игнатьев Игнат
Тимуров Тимур
Станиславов Станислав
Корнеев Георгий
Кохась Константин
Маврин Павел
 
INSERT INTO Subjects (Name) VALUES
       ('Матанализ'),
       ('Базы Данных'),
       ('АиСД');
INSERT 0 3
 
SELECT (Name) FROM Subjects;
name
Матанализ
Базы Данных
АиСД
 
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;
BEGIN
SET CONSTRAINTS
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
COMMIT
 
SELECT Name FROM Groups;
name
M3139
M34371
M3234
 
SELECT GroupName, Persons.Name AS StudentName FROM PersonGroups
JOIN Persons ON Persons.Id = PersonId;
groupnamestudentname
M3139Иванов Иван
M3139Игнатьев Игнат
M34371Петров Петр
M34371Тимуров Тимур
M3234Казимиров Казимир
M3234Станиславов Станислав
 
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 = 'Маврин Павел';
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
 
SELECT Role, Persons.Name AS TeacherName FROM Teachers
JOIN Persons ON Persons.Id = Teachers.PersonId;
roleteachername
ЛекторКорнеев Георгий
ЛекторКохась Константин
ЛекторМаврин Павел
ПрактикМаврин Павел
 
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 = 'Практик';
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
 
SELECT TeacherRole, SubjectName, GroupName, Teachers.Name AS TeacherName FROM ActiveSubjects
JOIN Persons AS Teachers ON TeacherId = Teachers.Id;
teacherrolesubjectnamegroupnameteachername
ЛекторМатанализM3139Кохась Константин
ЛекторБазы ДанныхM34371Корнеев Георгий
ЛекторАиСДM3234Маврин Павел
ПрактикАиСДM3234Маврин Павел
 
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;
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
 
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;
markteacherrolesubjectnamegroupnamestudentnameteachername
10ЛекторМатанализM3139Иванов ИванКохась Константин
-1ЛекторБазы ДанныхM34371Петров ПетрКорнеев Георгий
5ЛекторАиСДM3234Казимиров КазимирМаврин Павел
2ПрактикАиСДM3234Казимиров КазимирМаврин Павел

Comments

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