conspects.iliay.ar

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

Statements

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

  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:

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

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

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

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

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

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

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

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

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

(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) и множеством ФЗ:

находится в 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) и множеством ФЗ:

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

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

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

A B C
A1 B1 C1
A2 B2 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НФ

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

(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 столбцов