StudentId, StudentName, GroupId, GroupName, CourseId, CourseName, LecturerId, LecturerName, Mark

# Statements

Дано отношение с атрибутами StudentId, StudentName, GroupId, GroupName, CourseId, CourseName, LecturerId, LecturerName, Mark. И функциональными зависимостями:

  • StudentId → StudentName, GroupId, GroupName;
  • GroupId → GroupName;
  • GroupName → GroupId;
  • CourseId → CourseName;
  • LecturerId → LecturerName;
  • StudentId, CourseId → Mark;
  • GroupId, CourseId → LecturerId, LecturerName.
  1. Инкрементально приведите данное отношение в пятую нормальную форму.
  2. Постройте соответствующую модель сущность-связь.
  3. Постройте соответствующую физическую модель.
  4. Реализуйте SQL-скрипты, создающие схему базы данных.
  5. Создайте базу данных по спроектированной модели.
  6. Заполните базу тестовыми данными.

# 1

## 1

Функциональные зависимости: StudentId → StudentName, GroupId, GroupName; GroupId → GroupName; GroupName → GroupId; CourseId → CourseName; LecturerId → LecturerName; StudentId, CourseId → Mark; GroupId, CourseId → LecturerId, LecturerName.

Из предыдущего ДЗ, у этого отношения единственный ключ: {StudentId, CourseId}:

Возьмем надключ из всех аттрибутов. Заметим что StudentId не встерчается в правых частях ФЗ, поэтому ключ обязательно будет содержать StudentId. Для любого множества аттрибутов без StudentId -- это множество не надключ и следовательно не ключ

Аналогичное утверждение верно для CourseId.

Следовательно не будет рассматривать множества аттрибутов не содержащих StudentId, CourseId в качестве ключей

Если множество аттрибутов {StudentId, CourseId} -- надключ, то оно так же и ключ, т.к. любой другой надключ будет содержать в себe это множество и следовательно оно минимально по включению.

Проверим что {StudentId, CourseId} -- надключ: {StudentId, CourseId}* = {StudentId, CourseId, StudentName, GroupId, GroupName, CourseName, Mark, LecturerId, LecturerName} {StudentId, CourseId} -- ключ

Критерии 1НФ:

  1. Повторяющихся групп аттрибутов нет
  2. Все аттрибуты атомарны
  3. Есть ключ при чем единственный 
(StudentId, StudentName, GroupId, GroupName, CourseId, CourseName, LecturerId, LecturerName, Mark) => (StudentId, StudentName, GroupId, GroupName, CourseId, CourseName, LecturerId, LecturerName, Mark).

## 2

Ключ: {StudentId, CourseId} Найдем "мешающие" ФЗ:

  1. Для аттрибута StudentId StudentId -> StudentName, GroupId, GroupName
  2. Для аттрибута CourseId CourseId -> CourseName

Разобъем по ФЗ StudentId -> StudentName, GroupId, GroupName:

  • (StudentId, StudentName, GroupId, GroupName)
  • (StudentId, CourseId, CourseName, LecturerId, LecturerName, Mark)

Распалась ФЗ GroupId, CourseId -> LecturerId, LecturerName. Попробуем вместо этого разбить по ней, добавив в парвую часть CourseName, т.к. нужно разбить по "мешающей" ФЗ CourseId -> CourseName.

  • (GroupId, CourseId, LecturerId, LecturerName, CourseName)
  • (StudentId, CourseId, StudentName, GroupId, GroupName, Mark)

Все ФЗ сохранились, теперь разобъем второе отношение по ФЗ StudentId -> StudentName, GroupId, GroupName:

  • (StudentId, StudentName, GroupId, GroupName)
  • (StudentId, CourseId, Mark)

Рассмотри отношение (GroupId, CourseId, LecturerId, LecturerName, CourseName): Оно содержит ФЗ:

  • CourseId -> CourseName
  • GroupId, CourseId -> LecturerId, LecturerName
  • LecturerId -> LecturerName

Ключ в нем: {GroupId, CourseId}. Мешающая ФЗ: CourseId -> CourseName. Разобъем по ней:

  • (CourseId, CourseName)
  • (GroupId, CourseId, LecturerId, LecturerName)

Получим отношения:

  • (CourseId, CourseName) ФЗ:
    • CourseId -> CourseName

    Ключ: CourseId Во 2НФ (ключ не составной)

  • (GroupId, CourseId, LecturerId, LecturerName) ФЗ:
    • GroupId, CourseId -> LecturerId, LecturerName
    • LecturerId -> LecturerName

    Ключ: {GroupId, CourseId} Во 2НФ (нет "мешающих" правил)

  • (StudentId, StudentName, GroupId, GroupName) ФЗ:
    • StudentId -> StudentName, GroupId, GroupName
    • GroupId -> GroupName
    • GroupName -> GroupId

    Ключ: StudentId Во 2НФ (ключ не составной)

  • (StudentId, CourseId, Mark) ФЗ:
    • StudentId, CourseId -> Mark

    Ключ: {StudentId, CourseId} Во 2НФ (нет "мешающих" правил) 

(StudentId, StudentName, GroupId, GroupName, CourseId, CourseName, LecturerId, LecturerName, Mark) => (StudentId, CourseId, Mark); (CourseId, CourseName); (GroupId, CourseId, LecturerId, LecturerName); (StudentId, StudentName, GroupId, GroupName).

## 3

Рассмотрим отношения:

  • (CourseId, CourseName) ФЗ:
    • CourseId -> CourseName

    Ключ: CourseId Все неключевые аттрибуты (CourseId) непосредственно зависят от ключевых (CourseId) (единственная ФЗ) В 3НФ

  • (GroupId, CourseId, LecturerId, LecturerName) ФЗ:
    • GroupId, CourseId -> LecturerId, LecturerName
    • LecturerId -> LecturerName

    Ключ: (GroupId, CourseId) Все неключевые аттрибуты (LecturerId, LecturerName) непосредственно зависят от ключевых (GroupId, CourseId) (по первой ФЗ) В 3НФ

  • (StudentId, StudentName, GroupId, GroupName) ФЗ:
    • StudentId -> StudentName, GroupId, GroupName
    • GroupId -> GroupName
    • GroupName -> GroupId

    Ключ: StudentId Все неключевые аттрибуты (StudentName, GroupId, GroupName) непосредственно зависят от ключевых (StudentId) (по первой ФЗ) В 3НФ

  • (StudentId, CourseId, Mark) ФЗ:
    • StudentId, CourseId -> Mark

    Ключ: (StudentId, CourseId) Все неключевые аттрибуты (Mark) непосредственно зависят от ключевых (StudentId, CourseId) (единственная ФЗ) В 3НФ 

(StudentId, CourseId, Mark) => (StudentId, CourseId, Mark).
(CourseId, CourseName) => (CourseId, CourseName).
(GroupId, CourseId, LecturerId, LecturerName) => (GroupId, CourseId, LecturerId, LecturerName).
(StudentId, StudentName, GroupId, GroupName) => (StudentId, StudentName, GroupId, GroupName).

## 4

Рассмотрим отношения:

  • (CourseId, CourseName) Отношение из двух аттрибутов В НФБК
  • (GroupId, CourseId, LecturerId, LecturerName) ФЗ:
    • GroupId, CourseId -> LecturerId, LecturerName
    • LecturerId -> LecturerName

    Ключ: (GroupId, CourseId) Во второй ФЗ LecturerId - не надключ, декомпозируем по ней. Перед этим построит эквивалентное множество ФЗ:

    • GroupId, CourseId -> LecturerId
    • GroupId, CourseId -> LecturerName
    • LecturerId -> LecturerName

    После декомпозиции потеряем ФЗ GroupId, CourseId -> LecturerName, получим:

    • (GroupId, CourseId, LecturerId) ФЗ:
      • GroupId, CourseId -> LecturerId

      Ключ: (GroupId, CourseId) Имеем единственную ФЗ, такую что в левой части ключ => нет ФЗ таких что в левой части не надключ В НФБК

    • (LecturerId, LecturerName) Отношение из двух аттрибутов В НФБК
  • (StudentId, StudentName, GroupId, GroupName) ФЗ:
    • StudentId -> StudentName, GroupId, GroupName
    • GroupId -> GroupName
    • GroupName -> GroupId

    Ключ: StudentId Во второй и третьей ФЗ GroupId и GroupName соответственно - не надключи, декомпозируем по второй. Перед этим построит эквивалентное множество ФЗ:

    • StudentId -> StudentName, GroupId
    • StudentId -> GroupName
    • GroupId -> GroupName
    • GroupName -> GroupId

    После декомпозиции потеряем ФЗ StudentId -> GroupName, получим:

    • (StudentId, StudentName, GroupId) ФЗ:
      • StudentId -> StudentName, GroupId

      Ключ: (StudentId) Имеем единственную ФЗ, такую что в левой части ключ => нет ФЗ таких что в левой части не надключ В НФБК

    • (GroupId, GroupName) Отношение из двух аттрибутов В НФБК
  • (StudentId, CourseId, Mark) ФЗ:
    • StudentId, CourseId -> Mark

    Ключ: (StudentId, CourseId) Имеем единственную ФЗ, такую что в левой части ключ => нет ФЗ таких что в левой части не надключ В НФБК

Потеряли две ФЗ:

  • StudentId -> GroupName
  • GroupId, CourseId -> LecturerName 
(StudentId, CourseId, Mark) => (StudentId, CourseId, Mark).
(CourseId, CourseName) => (CourseId, CourseName).
(GroupId, CourseId, LecturerId, LecturerName) => (GroupId, CourseId, LecturerId); (LecturerId, LecturerName).
(StudentId, StudentName, GroupId, GroupName) => (StudentId, StudentName, GroupId); (GroupId, GroupName).

## 5

Лемма 1: Отношенине R с аттрибутами (A, B, C) и множеством ФЗ:

  • A, B -> C

находится в 4НФ

Рассмотрим потенциальные нетривиальные МЗ:

  • A -> B | C x = A, y1 = B1, y2 = B2, z1 = C1, z2 = C2 {y | (x, y, z1) in R} = { B1 } != { B2 } = {y | (x, y, z2) in R } Не МЗ

    Следовательно A -> C | B тоже не МЗ

  • B -> A | C
    A B C
    A1 B C1
    A2 B C2

    x = B, y1 = A1, y2 = GI2, z1 = C1, z2 = C2 {y | (x, y, z1) in R} = { A1 } != { GI2 } = {y | (x, y, z2) in R } Не МЗ

    Следовательно B -> C | A тоже не МЗ

  • C -> A | B
    A B C
    A1 B1 C
    A2 B2 C

    x = C, y1 = A1, y2 = GI2, z1 = B1, z2 = B2 {y | (x, y, z1) in R} = { A1 } != { GI2 } = {y | (x, y, z2) in R } Не МЗ

    Следовательно C -> B | A тоже не МЗ

  • {} -> A, C | B
    A B C
    A1 B1 C
    A2 B2 C

    x = {}, y1 = (A1, C), y2 = (GI2, C), z1 = B1, z2 = B2 {y | (y, z1) in R} = { (A1, C) } != { (GI2, C) } = {y | (y, z2) in R } Не МЗ

    Следовательно {} -> B | A, C тоже не МЗ

  • {} -> B, C | A
    A B C
    A1 B1 C
    A2 B2 C

    x = {}, y1 = (B1, C), y2 = (B2, C), z1 = A1, z2 = GI2 {y | (y, z1) in R} = { (B1, C) } != { (B2, C) } = {y | (y, z2) in R } Не МЗ

    Следовательно {} -> A | B, C тоже не МЗ

  • {} -> B, A | C
    A B C
    A1 B1 C1
    A2 B2 C2

    x = {}, y1 = (B1, C1), y2 = (B2, C2), z1 = A1, z2 = GI2 {y | (y, z1) in R} = { (B1, C1) } != { (B2, C2) } = {y | (y, z2) in R } Не МЗ

    Следовательно {} -> C | B, A тоже не МЗ

Рассмотрим отношения:

  • (CourseId, CourseName) ФЗ:
    • CourseId -> CourseName

    Ключ: (CourseId) По теореме Дейта-Фейгина 1 - отношение в 5НФ В 4НФ

  • (GroupId, CourseId, LecturerId) ФЗ:
    • GroupId, CourseId -> LecturerId

    Ключ: (GroupId, CourseId) По Лемме 1 при A = GroupId, B = CourseId, C = LecturerId находится в 4НФ

  • (LecturerId, LecturerName) ФЗ:
    • LecturerId -> LecturerName

    Ключ: (LecturerId) По теореме Дейта-Фейгина 1 - отношение в 5НФ В 4НФ

  • (StudentId, StudentName, GroupId) ФЗ:
    • StudentId -> StudentName, GroupId

    Ключ: (StudentId) По теореме Дейта-Фейгина 1 - отношение в 5НФ В 4НФ

  • (GroupId, GroupName) ФЗ:
    • GroupId -> GroupName
    • GroupName -> GroupId

    Ключи: (GroupId), (GroupName) По теореме Дейта-Фейгина 1 - отношение в 5НФ В 4НФ

  • (StudentId, CourseId, Mark) ФЗ:
    • StudentId, CourseId -> Mark

    Ключ: (StudentId, CourseId) По Лемме 1 при A = StudentId, B = CourseId, C = Mark находится в 4НФ 

(StudentId, CourseId, Mark) => (StudentId, CourseId, Mark).
(CourseId, CourseName) => (CourseId, CourseName).
(LecturerId, LecturerName) => (LecturerId, LecturerName).
(GroupId, CourseId, LecturerId) => (GroupId, CourseId, LecturerId).
(StudentId, StudentName, GroupId) => (StudentId, StudentName, GroupId).
(GroupId, GroupName) => (GroupId, GroupName).

## 6

Лемма 2: Отношенине R с аттрибутами (A, B, C) и множеством ФЗ:

  • A, B -> C

находится в 5НФ

По Лемме 1 R не содержит МЗ и по теореме Фейгина не содержит ЗС из двух отношений

Рассмотрим оставшуюся ЗС *{{A}, {B}, {C}}: Рассмотрим проекции для примера:

A B C
A1 B1 C1
A2 B2 C2
  • P1
    A
    A1
    A2
  • P2
    B
    B1
    B2
  • P3
    C
    C1
    C2

Получим что P1 join P2 join P3:

A B C
A1 B1 C1
A1 B2 C1
A1 B1 C2
A1 B2 C2
A2 B1 C1
A2 B2 C1
A2 B1 C2
A2 B2 C2

Что не равно исходной зависимости и следовательно не является ЗС

Следовательно нет нетривиальных ЗС => В 5НФ

Рассмотрим отношения:

  • (CourseId, CourseName) ФЗ:
    • CourseId -> CourseName

    Ключ: (CourseId) По теореме Дейта-Фейгина 1 - отношение в 5НФ

  • (GroupId, CourseId, LecturerId) ФЗ:
    • GroupId, CourseId -> LecturerId

    Ключ: (GroupId, CourseId) По Лемме 2 при A = GroupId, B = CourseId, C = LecturerId в 5НФ

  • (LecturerId, LecturerName) ФЗ:
    • LecturerId -> LecturerName

    Ключ: (LecturerId) По теореме Дейта-Фейгина 1 - отношение в 5НФ

  • (StudentId, StudentName, GroupId) ФЗ:
    • StudentId -> StudentName, GroupId

    Ключ: (StudentId) По теореме Дейта-Фейгина 1 - отношение в 5НФ

  • (GroupId, GroupName) ФЗ:
    • GroupId -> GroupName
    • GroupName -> GroupId

    Ключи: (GroupId), (GroupName) По теореме Дейта-Фейгина 1 - отношение в 5НФ

  • (StudentId, CourseId, Mark) ФЗ:
    • StudentId, CourseId -> Mark

    Ключ: (StudentId, CourseId) По Лемме 2 при A = StudentId, B = CourseId, C = Mark в 5НФ 

(StudentId, CourseId, Mark) => (StudentId, CourseId, Mark).
(CourseId, CourseName) => (CourseId, CourseName).
(LecturerId, LecturerName) => (LecturerId, LecturerName).
(GroupId, CourseId, LecturerId) => (GroupId, CourseId, LecturerId).
(StudentId, StudentName, GroupId) => (StudentId, StudentName, GroupId).
(GroupId, GroupName) => (GroupId, GroupName).

# TODO 2

# TODO 3

# TODO 4

CREATE DATABASE hw4;

DROP TABLE IF EXISTS Students CASCADE;
DROP TABLE IF EXISTS Groups CASCADE;
DROP TABLE IF EXISTS Lecturers CASCADE;
DROP TABLE IF EXISTS Courses CASCADE;
DROP TABLE IF EXISTS Marks CASCADE;
DROP TABLE IF EXISTS Teachers CASCADE;

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

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

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

    UNIQUE (Name)
);

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

   FOREIGN KEY (GroupId)
   REFERENCES Groups(Id)
);

CREATE TABLE Teachers (
    CourseId int not null,
    GroupId int not null,
    LecturerId int not null,
    PRIMARY KEY (CourseId, GroupId),

    FOREIGN KEY (CourseId)
    REFERENCES Courses(Id),

    FOREIGN KEY (GroupId)
    REFERENCES Groups(Id),

    FOREIGN KEY (LecturerId)
    REFERENCES Lecturers(Id)
);

CREATE TABLE Marks (
    CourseId int not null,
    StudentId int not null,
    Mark int not null,
    PRIMARY KEY (CourseId, StudentId),

    FOREIGN KEY (CourseId)
    REFERENCES Courses(Id),

    FOREIGN KEY (StudentId)
    REFERENCES Students(Id)
);

# TODO 6

# TODO 7

INSERT INTO Groups (Name) VALUES
       ('M33371'),
       ('M3133'),
       ('M34391');

SELECT * FROM Groups;

INSERT INTO Students (Name, GroupId) VALUES
       ('Иванов Иван', 1),
       ('Петров Петр', 2),
       ('Казимиров Казимир', 3),
       ('Игнатьев Игнат', 1),
       ('Тимуров Тимур', 2),
       ('Станиславов Станислав', 3);

SELECT * FROM Students;

INSERT INTO Lecturers (Name) VALUES
       ('Корнеев Георгий'),
       ('Кохась Константин'),
       ('Маврин Павел');

SELECT * FROM Lecturers;

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

SELECT * FROM Courses;

INSERT INTO Teachers (CourseId, GroupId, LecturerId) VALUES
       (1, 2, 2),
       (1, 1, 2),
       (2, 1, 3),
       (3, 1, 3),
       (3, 2, 3);

INSERT INTO Marks (CourseId, StudentId, Mark) VALUES
       (1, 1, 2),
       (1, 3, 5),
       (2, 6, 1),
       (3, 2, 10);

# Comments

  1. НФ-1
  2. НФ-2
  3. НФ-3 Не в 3NF: LecturerId -> LecturerName в [CourseId, GroupId, LecturerId, LecturerName]
    Не в 3NF: GroupId -> GroupName в [GroupId, GroupName, StudentId, StudentName]
    Не в 3NF: GroupName -> GroupId в [GroupId, GroupName, StudentId, StudentName]
  4. НФБК
  5. НФ-4
  6. НФ-5
  7. Модели
    1. Сущность-связь
    2. Физическая
  8. SQL
    1. DDL
      Использование serial,autoincrement или identity
    2. DML
      Предположения о конкретных значениях generated/serial столбцов
Made with Org mode, © 2023 - ∞ iliayar