Лабораторная работа: Разработка базы данных для списка судей

МИНИСТЕРСТВО НАУКИ И ВЫСШЕГО ОБРАЗОВАНИЯ
РОССИЙСКОЙ ФЕДЕРАЦИИ
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ
УЧРЕЖДЕНИЕ ВЫСШЕГО ОБРАЗОВАНИЯ
«КУРСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ»
Факультет физики, математики, информатики
Кафедра программного обеспечения и администрирования информационных
систем
Лабораторные работы
На тему: «Разработка базы данных «Список судей – секретарей и статистиков на
игру 2022.1»
по дисциплине
«Основы проектирования баз данных»
обучающейся 2 курса заочной формы обучения
направления
подготовки
09.03.01
«Информатика и вычислительная техника»,
направленность (профиль)
«Автоматизированные системы обработки
информации»
Барановский Антон Юрьевич
Руководитель:
к.т.н., доцент кафедры ПОиАИС,
Белов Владимир Геннадьевич
Курск, 2025
Оглавление
Лабораторная работа №1 «Построение словаря данных по документу» ............................................. 3
Лабораторная работа №2 «Построение ER – диаграммы»...................................................................... 7
Лабораторная работа №3 «Нормализация схемы базы данных. Описание каждой сущности» .... 12
3.1 Нормализация сущности < officials_ent >........................................................................................ 12
3.2 Нормализация сущности < tournaments_ent > ............................................................................... 13
3.3 Нормализация сущности <game_information_ent> ....................................................................... 13
3.4 Нормализация сущности < judicial category_ent > ........................................................................ 14
3.5 Нормализация сущности < official_roles_ent > ............................................................................... 14
3.6 Нормализация сущности < team_players_ent > .............................................................................. 15
Лабораторная работа №4 «Реализация базы данных»........................................................................... 17
Лабораторная работа №5 «Запрос к базе данных» ................................................................................. 25
Приложение 1 ................................................................................................................................................. 26
2
Лабораторная работа №1 «Построение словаря данных по документу»
Анализ предметной области осуществляется на основании документа
«Список бригад судей – секретарей, статистиков и операторов видеоповтора на
игру», представленной на рисунке 1.1.
Рисунок 1.1 – Исходные данные заявки
Проанализировав документ можно выделить следующие сущности:
сведенья об игре; турниры; список бригады; список должностей; команды; игроки
команды; основные судейские категории; представитель клуба - хозяина. Нами
3
была заполнена аналогичная заявка для более наглядной работы с базой данных.
Раскраска элементарных данных представлена на рисунке 1.2
На следующем этапе необходимо составить словарь данных, где имеются
следующие обозначения: синий – сущности; красный – атрибуты, которые
присутствуют в документе; черный - атрибуты, которых нет в документе; зеленый
– значения; желтый – названия в базе данных.
Рисунок 1.2 – Пример анализа списка игры №1313 в виде раскраски
элементарных данных
4
Структура словаря, составленного по списку игры №1313, представлена на
рисунках 1.3-1.8.
Сведенья об игре <game_information_ent>:
Данные:
Лига:<league>
Дата:<date>
Город:<city>
Игра №:<game>
Время:<time>
Арена:<arena>
Команда «А»:< team_name >
Команда «Б»: < team_name >
ФИО представителя клуба-хозяина <fio_representative>
Рисунок 1.3 – Сущность «Сведенья об игре»
Турниры <tournaments_ent>:
Данные:
Лига (краткое наименование)<league> ПЛ, МСЛ, МВЛ; ЖСЛ , ЖВЛ -; МКР, ЖКР, ДЮБЛ-Д, ДЮБЛ-Ю
Полное наименование лиги<leagua_long_name> женская Премьер-лига; мужская Суперлига, мужская
Высшая лига, женская Суперлига, женская Высшая лига, Кубок России (мужчины, женщины), детская
баскетбольная лига (юниоры, юниорки)., юношеская баскетбольная лига (юниоры, юниорки).
Рисунок 1.4 – Сущность «Турниры»
Список бригады
<officials_ent>:
Данные:
Игра №:<game>
Должность < role_name >
Фамилия, Имя <sec_name> <name>
Судейская категория <judicial_category>
№ паспорта судьи-секретаря / статистика / оператора видеоповтора <passport_number>
Добавленные:
Отчество <patrony>
Мобильный телефон <mob_phone>
5
Рисунок 1.5 – Сущность «Список бригады»
Список должностей
< official_roles_ent>:
Данные:
Должности < role_name> Секретарь, Помощник секретаря (табло), Секундометрист, Оператор времени
для броска, Судья-информатор, Оператор-статистик, Статистик, Статистик, Оператор видеоповтора
Рисунок 1.6 – Сущность «Список должностей»
Игроки команды <team_players_ent>:
Добавленные:
Роль в команде <role_in_team>
Команда <team_name>
Фамилия<sec_name>
Имя<name>
Отчество <patrony>
Мобильный телефон <mob_phone>
Рисунок 1.7 – Сущность «Игроки команды»
Основные судейские категории <judicial category_ent>:
Добавленные:
Категория <category>
Рисунок 1.8– Сущность «Основные судейские категории»
6
Лабораторная работа №2 «Построение ER – диаграммы»
Для построения ER-диаграммы для каждой сущности из словаря данных
(пункт 1.4-1.7) определены:
 типы данных по конкретным значениям;
 ключи: первичный и уникальные.
Внешние ключи определены автоматически заданием связей между
родительской и дочерней сущностями типа один ко многим (one to many):
идентифицирующими, не идентифицирующими и подтипом.
Логическая схема модели данных на физическом уровне Visual Paradigm и
описание элементов данных сущности «Сведенья об игре» game_information_ent
представлены на рисунке 2.1.
Рисунок 2.1 – Описание элементов данных «Сведенья об игре»
7
Типы данных и ключи для сущности «Турниры» tournaments_ent представлены
на рисунке 2.2.
Рисунок 2.2 - Типы данных и ключи для сущности «Турниры»
Типы данных и ключи для сущности «Список бригады» officials_ent
представлены на рисунке 2.3.
8
Рисунок 2.3 - Типы данных и ключи для сущности «Список бригады»
Типы данных и ключи для сущности «Основные судейские категории»
judicial category_ent представлены на рисунке 2.4.
Рисунок 2.4 – Типы данных и ключи для сущности «Основные судейские
категории»
9
Типы данных и ключи для сущности «Список должностей» official_roles_ent
представлены на рисунке 2.5.
Рисунок 2.5 – Типы данных и ключи для сущности «Список должностей»
Типы данных и ключи для сущности Игроки команды team_players_ent
представлены на рисунке 2.6.
10
Рисунок 2.6 – Типы данных и ключи для сущности «Игроки команды»
ER- диаграмма (схема базы данных на логическом уровне), представлена на
рисунке 2.7.
Рисунок 2.7 – ER-диаграмма заявки
11
Лабораторная работа №3 «Нормализация схемы базы данных. Описание
каждой сущности»
3.1 Нормализация сущности < officials_ent >
Надо записать систему функциональных зависимостей функциональную
зависимость для сущности < officials_ent >:
Один номер телефона не может принадлежать двум и более игрокам.
Данному ограничению следует функциональная зависимость (3.1.1).
{𝑚𝑜𝑏_𝑝ℎ𝑜𝑛𝑒 → 𝑟𝑜𝑙𝑒_𝑛𝑎𝑚𝑒 , 𝑠𝑒𝑐_𝑛𝑎𝑚𝑒, 𝑛𝑎𝑚𝑒, 𝑗𝑢𝑑𝑖𝑐𝑖𝑎𝑙_𝑐𝑎𝑡𝑒𝑔𝑜𝑟𝑦, passport_number, patrony
(3.1.1)
Один и тот же паспорт судьи, секретаря, статистика, оператора
видеоповтора не может принадлежать двум и более личностям.
Данным
ограничениям
соответствует
система
функциональных
зависимостей сущности < officials_ent > (3.1.2):
𝐹𝑜𝑓𝑓𝑖𝑐𝑖𝑎𝑙𝑠_𝑒𝑛𝑡
= {passport_number → 𝑟𝑜𝑙𝑒_𝑛𝑎𝑚𝑒 , mob_phone, 𝑠𝑒𝑐_𝑛𝑎𝑚𝑒, 𝑛𝑎𝑚𝑒, 𝑗𝑢𝑑𝑖𝑐𝑖𝑎𝑙_𝑐𝑎𝑡𝑒𝑔𝑜𝑟𝑦, patrony
(3.1.2)
Первичным атрибутом для таблицы «officials_ent» является: 𝑚𝑜𝑏_𝑝ℎ𝑜𝑛𝑒.
Не первичными атрибутами для таблицы «officials_ent» являются:
role_name , sec_name, name, judicial_category, passport_number, patrony.
В этой сущности две зависимость, причем ключевая, следовательно,
таблица находится в НФБК.
12
3.2 Нормализация сущности < tournaments_ent >
Надо записать систему функциональных зависимостей функциональную
зависимость для сущности < tournaments_ent >:
Одна и та же лига не может принадлежать двум и более личностям.
Данному ограничению соответствует функциональная зависимость (3.2.1).
{𝑙𝑒𝑎𝑔𝑢𝑒 → 𝑙𝑒𝑎𝑔𝑢𝑒_𝑙𝑜𝑛𝑔_𝑛𝑎𝑚𝑒
(3.2.1)
Данным
ограничениям
соответствует
система
функциональных
зависимостей сущности < tournaments_ent > (3.2.2).
𝐹𝑡𝑜𝑢𝑟𝑛𝑎𝑚𝑒𝑛𝑡𝑠_𝑒𝑛𝑡 = {𝑙𝑒𝑎𝑔𝑢𝑒 → 𝑙𝑒𝑎𝑔𝑢𝑒_𝑙𝑜𝑛𝑔_𝑛𝑎𝑚𝑒
(3.2.2)
Первичным атрибутом для таблицы «tournaments_ent» является: league.
Не первичными атрибутами для таблицы <tournaments_ent> являются:
league_long_name.
В этой сущности одна зависимость, причем ключевая, следовательно,
таблица находится в НФБК.
3.3 Нормализация сущности <game_information_ent>
Надо записать систему функциональных зависимостей функциональную
зависимость для сущности < game_information_ent >:
Один и тот же индивидуальный номер игры не может принадлежать двум
и более спискам. Данному ограничению соответствует функциональная
зависимость (3.3.1).
{𝑔𝑎𝑚𝑒 → 𝑑𝑎𝑡𝑒, 𝑐𝑖𝑡𝑦, 𝑙𝑒𝑎𝑔𝑢𝑒, 𝑡𝑖𝑚𝑒, 𝑎𝑟𝑒𝑛𝑎, 𝑡𝑒𝑎𝑚𝑛𝑎𝑚𝑒𝑎 , 𝑡𝑒𝑎𝑚𝑛𝑎𝑚𝑒 _𝑏, 𝑓𝑖𝑜_𝑟𝑒𝑝𝑟𝑒𝑠𝑒𝑛𝑡𝑎𝑡𝑖𝑣𝑒
(3.3.1)
13
В одном списке не может быть более чем одна лига. Данному ограничению
соответствует функциональная зависимость (3.3.2).
{𝑙𝑒𝑎𝑔𝑢𝑒 →data, city, game, time, arena, team_name_a, team_name_b, fio_representative
(3.3.2)
Данным
ограничениям
соответствует
система
функциональных
зависимостей сущности game_information_ent (3.3.3).
𝐹𝑔𝑎𝑚𝑒_𝑖𝑛𝑓𝑜𝑟𝑚𝑎𝑡𝑖𝑜𝑛_𝑖𝑛𝑡 = {
𝑔𝑎𝑚𝑒 → 𝑑𝑎𝑡𝑒 , 𝑐𝑖𝑡𝑦, league, 𝑡𝑖𝑚𝑒, 𝑎𝑟𝑒𝑛𝑎, 𝑡𝑒𝑎𝑚𝑛𝑎𝑚𝑒𝑎 , 𝑡𝑒𝑎𝑚𝑛𝑎𝑚𝑒 , 𝑓𝑖𝑜_𝑟𝑒𝑝𝑟𝑒𝑠𝑒𝑛𝑡𝑎𝑡𝑖𝑣𝑒
𝑙𝑒𝑎𝑔𝑢𝑒 → 𝑑𝑎𝑡𝑒 , 𝑐𝑖𝑡𝑦, game, 𝑡𝑖𝑚𝑒, 𝑎𝑟𝑒𝑛𝑎, 𝑡𝑒𝑎𝑚𝑛𝑎𝑚𝑒𝑎 , 𝑡𝑒𝑎𝑚𝑛𝑎𝑚𝑒 , 𝑓𝑖𝑜_𝑟𝑒𝑝𝑟𝑒𝑠𝑒𝑛𝑡𝑎𝑡𝑖𝑣𝑒
(3.3.3)
Первичным атрибутом для таблицы «game_information_ent» является:
game.
Не
являются:
первичными
атрибутами
date,
league,
city,
time,
для
таблицы
arena,
<game_information_ent>
team_name_a,
team_name_b,
fio_representative.
В этой сущности две зависимость, одна ключевая, следовательно, таблица
находится в НФБК.
3.4 Нормализация сущности < judicial category_ent >
Функциональная зависимость предполагает наличие как минимум двух
атрибутов, где один определяет другой. Функциональные зависимости в
сущности < judicial category_ent > отсутствуют.
Данная сущность является справочником основных судейских категорий,
которые могут быть внесены в список бригад судей – секретарей, статистиков и
операторов видеоповтора на игру.
В этой сущности одна зависимость, причем ключевая, следовательно,
таблица находится в НФБК.
3.5 Нормализация сущности < official_roles_ent >
14
Функциональная зависимость предполагает наличие как минимум двух
атрибутов, где один определяет другой. Функциональные зависимости в
сущности < official_roles_ent > отсутствуют.
Данная сущность является справочником должностей, которые могут быть
внесены в список бригад судей – секретарей, статистиков и операторов
видеоповтора на игру.
В этой сущности одна зависимость, причем ключевая, следовательно,
таблица находится в НФБК.
3.6 Нормализация сущности < team_players_ent >
Надо записать систему функциональных зависимостей функциональную
зависимость для сущности < team_players_ent >:
Один номер телефона не может принадлежать двум и более личностям в
составе команды. Данному ограничению соответствует функциональная
зависимость (3.6.1).
{𝑚𝑜𝑏_𝑝ℎ𝑜𝑛𝑒 → 𝑟𝑜𝑙𝑒_𝑖𝑛_𝑡𝑒𝑎𝑚,𝑡𝑒𝑎𝑚_𝑛𝑎𝑚𝑒,sec_name, name, patrony
(3.6.1)
Данным ограничениям соответствует система функциональных
зависимостей сущности < team_players_ent > (3.6.2).
𝐹𝑡𝑒𝑎𝑚_𝑝𝑙𝑎𝑦𝑒𝑟𝑠_𝑒𝑛𝑡 = {𝑚𝑜𝑏_𝑝ℎ𝑜𝑛𝑒 → 𝑟𝑜𝑙𝑒_𝑖𝑛_𝑡𝑒𝑎𝑚,𝑡𝑒𝑎𝑚_𝑛𝑎𝑚𝑒, sec _𝑛𝑎𝑚𝑒, 𝑛𝑎𝑚𝑒,patrony
(3.6.2)
Первичными атрибутами для таблицы « team_players_ent » являются:
mob_phone.
Не первичными атрибутами для таблицы < team_players_ent > являются:
role_in_team, team_name, sec_name, name, patrony
В
данной
сущности
есть
ключевая
зависимость
mob_phone,
соответственно, данная таблица находится в НФБК.
15
Нормализованная схема базы данных в среде VisualParadigm представлена
на рисунке 3.7
Рисунок 3.7 – Нормализованная таблица
16
Лабораторная работа №4 «Реализация базы данных»
База данных «Список судей – секретарей и статистиков на игру 2022.1»
сформирована в среде на основании кода SQL, полученного из схемы базы
данных в VisualParadigm рисунок 4.1.
Рисунок 4.1 – Сформированный SQL код базы данных в VisualParadigm
17
После выполнения этого кода в среде PgAdmin сформирована база данных
на сервере, представленная на рисунках 4.2. – 4.8
Рисунок 4.2 – Программный код создания таблицы game_information_ent
Рисунок 4.3 – Программный код создания таблицы judicial category
18
Рисунок 4.4 – Программный код создания таблицы official_roles_ent
Рисунок 4.5 – Программный код создания таблицы <officials_ent>
19
Рисунок 4.6 – Программный код создания таблицы <team_players_ent>
Рисунок 4.7 – Программный код создания таблицы <tournaments_ent>
Рисунок 4.8 – Сформированные таблицы
20
Часть схемы базы данных в виде ER- диаграммы в среде PgAdmin
представлена на рисунке 4.9.
Рисунок 4.9 - ER- диаграмма
На рисунке 4.10 представлено содержание таблицы «Сведенья об игре»
game_information_ent.
21
Рисунок 4.10 – Содержание таблицы «Сведенья об игре» game_information_ent
На рисунке 4.11 представлено содержание таблицы «Основные судейские
категории» judicial category_ent.
Рисунок 4.11 – Содержание таблицы «Основные судейские категории» judicial
category_ent.
22
На рисунке 4.12 представлено содержание таблицы «Список должностей»
official_roles_ent.
Рисунок 4.12 – Содержание таблицы «Список должностей» official_roles_ent
На
рисунке
4.13
представлено
содержание
таблицы
«Турниры»
tournaments_ent
Рисунок 4.13 – содержание таблицы таблицы «Турниры» tournaments_ent
23
На рисунке 4.14 представлено содержание «Список бригады»
officials_ent.
Рисунок 4.14 – Содержание таблицы «Список бригады»
officials_ent.
На рисунке 4.15 представлено содержание таблицы «Игроки команды»
team_players_ent.
Рисунок 4.15 – Содержание таблицы «Игроки команды» team_players_ent.
24
Лабораторная работа №5 «Запрос к базе данных»
Нами был сформирован запрос к базе данных ««Список бригад судей –
секретарей, статистиков и операторов видеоповтора на игру»» с сортировкой по
дате
проведения
игр.
Запрос
формулировался
следующим
образом:
«Отсортировать команды по дате проведения от более ранней к более поздней».
Полученный ответ представлен на рисунке 5.1.
Рисунок 5.1 – Сформированный ответ базы данных
25
Приложение 1
Ниже представлен SQL код, сформированный в Visual Paradigm.
CREATE TABLE game_information_ent (game SERIAL NOT NULL, league
varchar(255) NOT NULL, "date" date NOT NULL, city varchar(255) NOT NULL, time
time NOT NULL, arena varchar(255) NOT NULL, team_name_a varchar(255) NOT
NULL, team_name_b varchar(255) NOT NULL, fio_representative varchar(255) NOT
NULL, PRIMARY KEY (game));
COMMENT ON TABLE game_information_ent IS 'Сведенья об игре
<game_information_ent>:
Данные:
Лига:<league>
Дата:<date>
Город:<city>
Игра №:<game>
Время:<time>
Арена:<arena>
Команда «А»:< team_name > Команда «Б»: < team_name >
ФИО представителя клуба-хозяина <fio_representative> ';
CREATE TABLE tournaments_ent (league varchar(255) NOT NULL,
leagua_long_name varchar(255) NOT NULL, PRIMARY KEY (league));
COMMENT ON TABLE tournaments_ent IS 'Турниры <tournaments_ent>:
Данные:
Лига (краткое наименование)<league> ПЛ, МСЛ, МВЛ; ЖСЛ , ЖВЛ -;
МКР, ЖКР, ДЮБЛ-Д, ДЮБЛ-Ю
Полное наименование лиги<leagua_long_name> женская Премьер-лига;
мужская Суперлига, мужская Высшая лига, женская Суперлига, женская Высшая
лига, Кубок России (мужчины, женщины), детская баскетбольная лига (юниоры,
юниорки)., юношеская баскетбольная лига (юниоры, юниорки). ';
26
CREATE TABLE officials_ent (mob_phone int4 NOT NULL, game_num int4
NOT NULL, role_name varchar(255) NOT NULL, sec_name varchar(255) NOT
NULL, name varchar(255) NOT NULL, patrony varchar(255) NOT NULL,
judicial_category varchar(255) NOT NULL, passport_number varchar(255) NOT
NULL UNIQUE, PRIMARY KEY (mob_phone));
COMMENT ON TABLE officials_ent IS 'Список бригады
<officials_ent>:
Данные:
Игра №:<game_num>
Должность < role_name >
Фамилия, Имя <sec_name> <name>
Судейская категория <judicial_category>
№ паспорта судьи-секретаря / статистика / оператора видеоповтора
<passport_number>
Добавленные:
Отчество <patrony> ';
CREATE TABLE official_roles_ent (role_name varchar(255) NOT NULL,
PRIMARY KEY (role_name));
COMMENT ON TABLE official_roles_ent IS 'Список должностей
< official_roles_ent>:
Данные:
Должности < role_name> Секретарь, Помощник секретаря (табло),
Секундометрист, Оператор времени для броска, Судья-информатор, Операторстатистик, Статистик, Статистик, Оператор видеоповтора ';
CREATE TABLE team_players_ent (mob_phone SERIAL NOT NULL,
role_in_team varchar(255) NOT NULL, team_name varchar(255) NOT NULL,
27
sec_name varchar(255) NOT NULL, name varchar(255) NOT NULL, patrony
varchar(255) NOT NULL, PRIMARY KEY (mob_phone));
COMMENT
ON
TABLE
team_players_ent
IS
'Игроки
команды
<team_players_ent>:
Добавленные:
Роль в команде <role_in_team>
Команда <team_name>
Фамилия<sec_name>
Имя<name>
Отчество <patrony>
Мобильный телефон <mob_phone> ';
CREATE TABLE "judicial category_ent" (judicial_category varchar(255) NOT
NULL, PRIMARY KEY (judicial_category));
COMMENT ON TABLE "judicial category_ent" IS 'Основные судейские
категории <judicial category_ent>:
Добавленные:
Категория <judicial category> ';
В таблице представлен запрос к базе данных.
SELECT game, league, date, city, "time", arena, team_name_a, team_name_b,
fio_representative
FROM public.game_information_ent
ORDER BY date
28