Методические указания по общепрофессиональной дисциплине "Основы проектирования баз данных"
Скачать публикацию
Язык издания: русский
Периодичность: ежедневно
Вид издания: сборник
Версия издания: электронное сетевое
Публикация: Методические указания по общепрофессиональной дисциплине "Основы проектирования баз данных"
Автор: Чурсин Владислав Анатольевич
Периодичность: ежедневно
Вид издания: сборник
Версия издания: электронное сетевое
Публикация: Методические указания по общепрофессиональной дисциплине "Основы проектирования баз данных"
Автор: Чурсин Владислав Анатольевич
Министерство образования и науки Забайкальского края Государственное профессиональное образовательное учреждение «ЧИТИНСКИЙ ТЕХНИКУМ ОТРАСЛЕВЫХ ТЕХНОЛОГИЙ И БИЗНЕСА»(ГПОУ «ЧТОТиБ»)В.А. ЧурсинОП.04 «Основы проектирования баз данных»Методические рекомендации для выполнения практических и самостоятельныхработ для студентов очного отделения специальность 09.02.09 «Веб-разработка»Издательство ГПОУ «ЧТОТиБ»2025 г.1. Пояснительная запискаУчебная дисциплина ОП.04 «Основы проектирования баз данных» входит в общепрофессиональный цикл основной профессиональной образовательной программы по специальности 09.02.09 «Веб-разработка». В рамках дисциплины предусмотрено выполнение практических и самостоятельных работ, способствующих закреплению теоретических знаний и приобретению практических навыков. Практические работы выполняются в аудитории под руководством преподавателя.Методические рекомендации по выполнению практических работ составлены в соответствии с Государственными требованиями к минимуму содержания и уровню подготовки студентов по программам подготовки специалистов среднего звена.Практические занятия стимулируют познавательную деятельность студентов, поскольку требуют их активного участия в проведении различных исследований. Они направлены на углубление и закрепление теоретических знаний в области расчета и анализа, а также на приобретение практических навыков для решения различных ситуационных задач, которые могут быть полезны в будущей профессиональной деятельности. Студенты учатся работать с разнообразной литературой, методическими и справочными материалами.Перед выполнением практической работы обучающийся должен повторить или изучить материал, относящийся к теме работы, лекционным записям, учебной литературы и соответствующим методическим инструкциям.Практические занятия служат связующим звеном между теорией и практикой. Они необходимы для закрепления теоретических знаний, полученных на уроках теоретического обучения, а также для получения практических знаний. Практические задания выполняются обучающимся самостоятельно, с применением знаний и умений, полученных на уроках, а также с использованием необходимых пояснений, полученных от преподавателя при выполнении практического задания. К практическому занятию от обучающегося требуется предварительная подготовка, которую он должен провести перед занятием.Практические задания разработаны в соответствии с учебной программой. В зависимости от содержания они могут выполняться обучающимися индивидуально или фронтально.Цель методических указаний – предоставить студентам рекомендации по выполнению практических и самостоятельных работ, а также обеспечить их необходимыми материалами для успешного освоения дисциплины.В результате изучения дисциплины и выполнения работ студент должен:Уметь:У1. Интерпретировать бизнес-требования заказчика для разработки концептуальной модели информационного ресурса.У2. Устанавливать систему управления базами данных (СУБД).У3. Использовать средства системы управления базами данных.У4. Выполнять регламентные процедуры по резервированию данных.У5. Применять регламентные процедуры управления правами доступа пользователей информационных ресурсов.Знать:З1. Основы построения концептуальных моделей информационных ресурсов средствами графических нотаций.З2. Программные средства и платформы для разработки web-ресурсов.З3. Особенности систем управления базами данных.З4. Общие основы решения практических задач по созданию резервных копий.З5. Основы резервного развертывания и резервного копирования информационных ресурсов.2. Тематический план3. Методические рекомендации к практическим и самостоятельным работамПрактическая работа №1 «Использование операций: Объединение, Пресечение, Вычитание, Декартово произведение, Выборка, Проекция, Соединение, Деление»Цель: научиться применять основные операции реляционной алгебры к множествам данных для извлечения нужной информации.Теоретическая основа:Реляционная алгебра – это формальный язык запросов к реляционным БД, основанный на множественных операциях. В данной работе рассматриваются такие операции как:∪ (Объединение). ∩ (Пересечение). − (Разность/Вычитание). × (Декартово произведение). σ (Селекция/Выборка). π (Проекция). ⨝ (Соединение). ÷ (Деление). Задание и исходные данные для выполнения работы:Сущность студентов (Students) Сущность групп (Groups) Сущность курсов (Courses) Сущность оценок (Grades) Сущность преподавателей (Teachers) Сущность аудиторий (Classrooms) Сущность расписания (Schedule) Сущность проектов (Projects) Задания:1. Объединение: Выведите всех студентов, которые сдали курс C2 или C4.2. Пересечение: Выведите студентов, сдавших оба курса: C1 и C4.3. Разность: Найдите студентов, которые сдали C2, но не C4.4. Декартово произведение: Постройте все возможные комбинации студентов и преподавателей.5. Проекция: Выведите уникальные названия курсов и тип занятия.6. Селекция: Выведите всех студентов группы G1, обучающихся в 2023 году.7. Соединение: Выведите ФИО студента, курс и оценку, используя соединения 8. Деление: Найдите студентов, которые сдали все теоретические курсы.9. Выборка + соединение: Выведите студентов, которые сдали хотя бы один предмет у преподавателя Ивановой И.А.10. Сложная выборка: Выведите студентов, участвующих в проектах и сдавших не менее 3 курсов.Контрольные вопросы:В чем разница между операцией объединения и пересечения? Почему операция деления считается одной из самых сложных? Как с помощью проекции убрать дубликаты в SQL? Что произойдёт при соединении таблиц, если ключей нет? Приведите пример задачи, где нужно использовать вычитание. Самостоятельная работа №1 «Решение задач по реляционной алгебре»Цель работы: закрепить навыки применения основных операций реляционной алгебры для работы с отношениями, научиться формулировать решения с использованием операций выборки, проекции, объединения, пересечения, разности, декартова произведения и соединения.Задание:Изучить основные операции реляционной алгебры. Для заданных отношений (таблиц) выполнить задачи: Операция выборки (σ) по заданному условию. Операция проекции (π) для указанных атрибутов. Операция объединения (∪) двух отношений. Операция пересечения (∩) двух отношений. Операция разности (−) двух отношений. Операция декартова произведения (×) двух отношений. Операция естественного соединения (⨝) двух отношений. Оформить решения в виде последовательностей применённых операций, указывая исходные и полученные отношения. Базовые отношения (таблицы):СтудентыКурсыУспеваемостьЗадания (операции реляционной алгебры):Выполнить выборку студентов группы ИС-21. Выполнить проекцию: вывести только ФИО и Группу всех студентов. Найти объединение студентов из группы ИС-21 и ИС-22. Найти пересечение студентов, у которых ГодПоступления = 2021 и Группа = ИС-22. Выполнить разность между всеми студентами и студентами, у которых возраст 18 лет. Выполнить декартово произведение таблиц Студенты и Курсы (поясните результат). Выполнить естественное соединение таблиц Студенты и Успеваемость по ID_Студента. Найти студентов, у которых оценка по курсу «Базы данных» выше 4. Вывести студентов, изучающих более одного курса (по ID_Студента). Найти курсы, которые проходят в 3-м семестре и имеют более 60 часов. Выбрать студентов, чей возраст находится между 17 и 19 годами. Найти студентов, которые не изучают курс «Компьютерные сети». Найти ФИО студентов, чья фамилия заканчивается на «ов» (LIKE-поиск). Вывести курсы, которые проходят либо во 2-м, либо в 4-м семестре. Определить студентов, которые получили оценку «5» по любому курсу. Вывести имена студентов и названия курсов, которые они изучают. Найти всех студентов, у которых нет оценок в таблице Успеваемость. Вывести количество студентов в каждой группе (GROUP BY). Вывести среднюю оценку по каждому курсу (GROUP BY). Найти студентов, поступивших после 2021 года. Определить студентов старше 18 лет, обучающихся на 3-м семестре. Найти курсы, на которых оценка у студентов только 4 или 5. Найти студентов, не обучающихся ни на одном курсе (использовать разность). Определить студентов, которые учатся на одном и том же курсе. Вывести студентов с минимальной оценкой по каждому курсу. Пояснение:Операция Выборка (σ) используется для фильтрации записей по условию. Операция Проекция (π) используется для выбора отдельных столбцов. Операция Объединение (∪) объединяет результаты двух таблиц. Операция Пересечение (∩) находит общие элементы двух таблиц. Операция Разность (−) исключает записи одной таблицы из другой. Декартово произведение (×) создает все возможные пары строк двух таблиц. Естественное соединение (⨝) объединяет строки с одинаковыми значениями по выбранному столбцу. Контрольные вопросы:Что происходит при декартовом произведении двух отношений? Когда имеет смысл использовать операцию пересечения? Чем отличается выборка от проекции? Как выполнить естественное соединение двух таблиц? Для чего нужна операция разности в реляционной алгебре? Практическая работа №2 «Проектирование ER-диаграммы базы данных по нотации Чена»Цель работы: научиться проектировать и строить ER-диаграмму базы данных, используя нотацию Чена для описания сущностей, связей и атрибутов.Задание:Ознакомьтесь с основными принципами нотации Чена Сущности отображаются прямоугольниками. Атрибуты – эллипсами. Связи между сущностями – ромбами. Связи могут быть одного из типов: один-ко-многим, многие-ко-многим, один-к-одному. Спроектируйте базу данных для предметной области: Интернет-магазин по продаже книг. База данных должна содержать следующую информацию:Сведения о книгах (название, автор, ISBN, год издания, жанр, цена). Сведения об авторах (ФИО, дата рождения, страна). Сведения о клиентах (ФИО, email, телефон, адрес). Сведения о заказах (номер заказа, дата заказа, статус заказа). Сведения о содержимом заказа (какие книги входят в каждый заказ, количество каждой книги). Требования к ER-диаграмме по нотации Чена: Не менее 5 сущностей. У каждой сущности должно быть не менее 4 атрибутов. Обязательно указать первичные ключи (PK). Указать тип связей между сущностями (1:1, 1:*, :). Использовать слабые сущности, если требуется. При необходимости указать составные атрибуты (например, ФИО может состоять из фамилии, имени, отчества). Порядок выполнения работы: Определите основные сущности предметной области. Определите атрибуты каждой сущности. Определите связи между сущностями и их типы. Постройте ER-диаграмму в нотации Чена вручную или с использованием любого графического редактора (Draw.io, Creately). Контрольные вопросы:Что такое сущность в ER-диаграмме? Что такое атрибут и какие типы атрибутов бывают? Чем слабая сущность отличается от обычной сущности? Какие бывают типы связей между сущностями? Для чего используется первичный ключ? Практическая работа №3 «Нормализация таблиц реляционной БД»Цель работы: научиться выполнять нормализацию таблиц реляционных баз данных для устранения избыточности данных и обеспечения целостности данных.Задание:Ознакомьтесь с теоретическими понятиями нормальных форм: Первая нормальная форма (1NF). Вторая нормальная форма (2NF). Третья нормальная форма (3NF). Исходная ненормализованная таблица: Требуется: Провести нормализацию данной таблицы до третьей нормальной формы (3NF). Описать каждый этап нормализации: каковы зависимости между атрибутами, какие атрибуты образуют ключи, какие атрибуты требуют вынесения в отдельные таблицы? Построить итоговые нормализованные таблицы (показать структуру и связи между ними). Дополнительно:Указать типы связей между таблицами (один-ко-многим, многие-ко-многим). Построить схему базы данных после нормализации. Порядок выполнения:Определите функциональные зависимости в исходной таблице. Проведите приведение к 1NF: устраните многозначные атрибуты. Проведите приведение ко 2NF: удалите частичные зависимости. Проведите приведение к 3NF: устраните транзитивные зависимости. Постройте итоговую схему базы данных. Контрольные вопросы:Что называется нормализацией базы данных? Что такое функциональная зависимость атрибутов? Чем отличается 1NF от 2NF и 3NF? Почему важно избегать избыточности данных? Какие проблемы возникают в ненормализованных базах данных? Практическая работа №4 «Освоение принципов проектирования БД»Цель работы: научиться применять основные принципы проектирования базы данных: анализ предметной области, выявление сущностей и атрибутов, определение связей между сущностями, построение концептуальной модели данных.Задание:Изучите теоретические основы проектирования баз данных: Понятие сущности и атрибута. Типы связей между сущностями: один-ко-многим, многие-ко-многим, один-к-одному. Принципы построения концептуальной схемы базы данных. Предметная область: разработайте базу данных для онлайн-сервиса бронирования конференц-залов. Сервис должен позволять:Вести учет залов, их характеристик и доступности. Учитывать клиентов и их заказы на бронирование залов. Учитывать стоимость аренды и дополнительного оборудования. Вести учет оплат и статуса бронирования. Выполните следующие действия: 1) Выявите сущности и их атрибуты (например: Клиент, Зал, Бронирование, Оплата, Оборудование).2) Определите связи между сущностями и их типы (например: один клиент – много бронирований, один зал – много бронирований).3) Постройте концептуальную модель базы данных (ER-диаграмму) на основе выявленных сущностей и связей.4) Определите ключевые атрибуты для каждой сущности (первичные ключи) и укажите внешние ключи.Порядок выполнения:Сформулировать задание по предметной области. Выделить основные сущности и их атрибуты. Определить связи между сущностями. Построить концептуальную ER-диаграмму. Сформировать описание структуры таблиц с указанием первичных и внешних ключей. Контрольные вопросы:Что такое сущность и атрибут в проектировании БД? Какие существуют типы связей между сущностями? Чем отличается первичный ключ от внешнего ключа? Для чего необходима концептуальная модель базы данных? Какие принципы следует соблюдать при проектировании структуры базы данных? Практическая работа №5 «Проектирование реляционной БД»Цель работы: освоить этапы проектирования реляционной базы данных на основе анализа предметной области: определение сущностей, атрибутов, связей и построение логической структуры базы данных.Задание:Изучите основные принципы проектирования реляционных баз данных: Понятие реляционной модели данных. Принципы построения таблиц и установления связей между ними. Роль первичных и внешних ключей в организации данных. Предметная область: разработайте реляционную базу данных для управления библиотекой. База данных должна обеспечивать:Учёт книг и их характеристик. Учёт авторов и жанров. Учет читателей библиотеки. Учёт фактов выдачи и возврата книг. Выполните следующие действия: 1) Выделите сущности и их атрибуты в количестве, не менее 10 и включите обязательные из них:Книга (ID, название, год издания, издательство, жанр, автор) Автор (ID, ФИО, дата рождения) Жанр (ID, название жанра) Читатель (ID, ФИО, дата рождения, телефон, email) Выдача книги (ID, ID книги, ID читателя, дата выдачи, дата возврата) 2) Определите связи между сущностями:3) Постройте логическую модель базы данных в форме схемы связей.4) Определите для каждой таблицы:Первичные ключи (PK) Внешние ключи (FK) Типы связей (один-ко-многим, многие-ко-многим). Порядок выполнения:Провести анализ предметной области. Составить список сущностей и атрибутов. Построить логическую схему реляционной БД. Описать ключи и связи между таблицами. Составить описание структуры базы данных. Контрольные вопросы:Что такое реляционная модель данных? Чем отличается первичный ключ от внешнего? Что такое нормализация базы данных? Какие типы связей существуют между таблицами? Для чего необходимо логическое проектирование БД? Практическая работа №6 «Написание простейших SELECT-запросов»Цель работы: научиться составлять базовые SQL-запросы для извлечения данных из одной таблицы с использованием операторов SELECT и FROM.Задание:Используйте следующую таблицу для выполнения запросов: Таблица STUDENTS (Студенты)Напишите SQL-запросы: 1. Вывести все данные о студентах.Вывести только имена студентов. Вывести только даты рождения студентов. Вывести названия групп всех студентов. Вывести средний балл всех студентов. Вывести ID и ФИО студентов. Вывести ФИО студентов и их средний балл. Вывести дату рождения и название группы студентов. Вывести ID студента и его группу. Вывести все данные о студентах без использования знака * (явное перечисление столбцов). Контрольные вопросы:Для чего используется оператор SELECT в SQL? Что делает оператор FROM в SQL? Как выбрать все столбцы таблицы? Как выбрать только некоторые столбцы таблицы? Можно ли использовать SELECT без FROM? Практическая работа №7 «Написание SELECT-запросов с логическими операторами, DISTINCT, условиями WHERE, операторами IS NULL, BETWEEN, IN и LIKE»Цель работы: освоить написание SQL-запросов с условиями выборки данных, использованием фильтрации, проверкой на пустые значения, выборкой по диапазону и шаблонам.Задание:Используйте следующую таблицу для выполнения запросов: Таблица EMPLOYEES (Сотрудники)Напишите SQL-запросы: 1. Вывести всех сотрудников без повторений их должностей.2. Вывести всех сотрудников, у которых зарплата больше 80000.3. Вывести сотрудников отдела IT.4. Найти сотрудников, у которых отсутствует номер телефона.5. Найти сотрудников, принятых на работу с 1 января 2020 года по 31 декабря 2022 года.6. Вывести сотрудников, занимающих должности «Программист» или «Дизайнер».7. Найти сотрудников, чьи фамилии начинаются на букву «К».8. Найти сотрудников, чьи имена содержат «а».9. Вывести всех сотрудников, работающих в отделах «Продажи» или «Финансы», с зарплатой более 65000.10. Вывести всех сотрудников, у которых зарплата не входит в диапазон от 60000 до 80000.Контрольные вопросы:Что делает оператор DISTINCT в SQL? Для чего используется условие WHERE? Как проверить значение на NULL в SQL? Что делает оператор BETWEEN? Как использовать оператор LIKE для поиска по шаблону? Практическая работа №8 «Написание SELECT-запросов с сортировками, группировками и агрегатными функциями»Цель работы: научиться писать SQL-запросы с использованием операторов ORDER BY, GROUP BY, HAVING, а также агрегатных функций (COUNT, SUM, AVG, MAX, MIN) для обработки данных.Задание:Используйте следующую таблицу для выполнения запросов: Таблица ORDERS (Заказы)Напишите SQL-запросы: 1. Вывести все заказы, отсортированные по дате заказа по возрастанию.2. Вывести все заказы, отсортированные по имени клиента в алфавитном порядке.3. Посчитать общее количество заказов.4. Найти максимальную цену за единицу товара.5. Найти минимальное количество единиц в одном заказе.6. Вычислить среднюю цену за единицу товара.7. Посчитать общую сумму всех заказов (Quantity * PricePerUnit).8. Вывести количество заказов по каждому товару.9. Вывести общую сумму заказов по каждому клиенту.10. Вывести клиентов, у которых сумма заказов превышает 100000.Контрольные вопросы:Что делает оператор ORDER BY? В чем разница между GROUP BY и ORDER BY? Для чего нужны агрегатные функции в SQL? Что делает оператор HAVING? В чем отличие WHERE и HAVING? Практическая работа №9 «Написание многотабличных запросов, запросов с ограничением выборки и подзапросов»Цель работы: научиться составлять SQL-запросы с объединением нескольких таблиц (JOIN), ограничением выборки (LIMIT) и использованием вложенных подзапросов (SELECT внутри SELECT).Задание:Используйте следующие таблицы для выполнения запросов: Таблица CUSTOMERS (Клиенты)Таблица ORDERS (Заказы)Напишите SQL-запросы: 1. Вывести список всех заказов вместе с именами клиентов.2. Вывести заказы, оформленные клиентами из Москвы.3. Найти клиентов, которые ещё не сделали ни одного заказа.4. Вывести 3 самых дорогих заказа.5. Найти клиентов, у которых сумма хотя бы одного заказа больше 40000.6. Найти клиентов, у которых все заказы меньше 40000 (подзапрос).7. Найти среднюю сумму заказа по каждому клиенту.8. Вывести клиентов, у которых более одного заказа.Контрольные вопросы:Для чего используется оператор JOIN? В чем разница между INNER JOIN, LEFT JOIN, RIGHT JOIN? Как работает оператор LIMIT? Что такое подзапрос? Когда его лучше использовать? Можно ли использовать подзапрос в WHERE и FROM одновременно? Практическая работа №10 «Написание запросов с обобщенным табличным выражением, объединением запросов и условной логикой»Цель работы: научиться использовать обобщённые табличные выражения (CTE), объединение запросов (UNION, UNION ALL) и условную логику (CASE) при составлении SQL-запросов.Задание:Используйте следующие таблицы для выполнения запросов: Таблица EMPLOYEES (Сотрудники)Таблица CONTRACTORS (Подрядчики)Напишите SQL-запросы: 1. Создать обобщённое табличное выражение (CTE), которое выберет сотрудников с зарплатой выше 60000.2. Объединить сотрудников и подрядчиков по отделам/видам услуг с использованием UNION ALL.3. Объединить сотрудников и подрядчиков с использованием UNION (без дубликатов).4. Добавить столбец «Уровень дохода» с условной логикой (CASE) для сотрудников:Если зарплата меньше 60000 – «Низкий доход» От 60000 до 80000 – «Средний доход» Более 80000 – «Высокий доход» 5. Добавить столбец «Тип» с условной логикой (CASE) для общего списка сотрудников и подрядчиковКонтрольные вопросы:Что такое обобщённое табличное выражение (CTE)? В чём разница между UNION и UNION ALL? Как работает оператор CASE? Можно ли использовать CASE в предложении SELECT? Для чего используется WITH в SQL-запросах? Практическая работа №11 «Написание запросов на создание и удаление таблиц и БД, запросов на добавление, удаление и обновление данных»Цель работы: научиться создавать и удалять базы данных и таблицы, а также выполнять базовые операции добавления (INSERT), удаления (DELETE) и обновления (UPDATE) данных.Задание:Создать новую базу данных CompanyDB. Перейти к использованию этой базы данных. Создать таблицу EMPLOYEES (Сотрудники): EmployeeID – INT PRIMARY KEY AUTO_INCREMENT, FullName – VARCHAR(100), Position – VARCHAR(50), Salary – DECIMAL(10,2), HireDate – DATE. Создать таблицу DEPARTMENTS (Отделы): DepartmentID – INT PRIMARY KEY AUTO_INCREMENT, DepartmentName – VARCHAR(100). Добавить несколько записей в таблицу EMPLOYEES. Добавить несколько записей в таблицу DEPARTMENTS. Изменить зарплату Иванова Ивана на 65000. Переименовать отдел «Финансовый отдел» в «Финансы». Удалить сотрудника по имени Петров Петр. Удалить отдел «Продажи». Удалить таблицу EMPLOYEES. Удалить всю базу данных CompanyDB. Контрольные вопросы:Чем отличается DROP TABLE от DELETE FROM? Что делает команда INSERT? Как изменить существующие данные в таблице? Что произойдет при выполнении команды DROP DATABASE? В каких случаях используют UPDATE? Практическая работа №12 «Представления»Цель работы: научиться создавать представления (VIEW) в базе данных для удобной работы с данными, а также обновлять и удалять представления.Задание:Используйте следующие таблицы для работы:Таблица EMPLOYEES (Сотрудники)Таблица DEPARTMENTS (Отделы)1. Создать представление, отображающее сотрудников только из отдела "IT".2. Создать представление, показывающее ФИО сотрудников и название их отделов.3. Создать представление, которое показывает сотрудников с зарплатой выше 80000.4. Вывести данные из представления IT_Employees.5. Вывести данные из представления Employee_Department.6. Вывести данные из представления HighSalaryEmployees.7. Обновить представление HighSalaryEmployees, чтобы отображать сотрудников с зарплатой выше 85000.8. Удалить представление IT_Employees.Контрольные вопросы:Что такое представление (VIEW) в базе данных? Какие преимущества дает использование представлений? Можно ли обновлять данные через представление? В чем разница между CREATE VIEW и CREATE OR REPLACE VIEW? Что происходит при удалении представления (DROP VIEW)? Практическая работа №13 «Хранимые процедуры и триггеры»Цель работы: научиться создавать хранимые процедуры (PROCEDURE) и триггеры (TRIGGER) в базе данных для автоматизации обработки данных и обеспечения их целостности.Задание:Этап 1. ПодготовкаИспользуйте следующую таблицу:Таблица EMPLOYEES (Сотрудники)Этап 2. Хранимые процедуры1. Создать процедуру для добавления нового сотрудника в таблицу EMPLOYEES.2. Вызвать процедуру и добавить сотрудника:3. Создать процедуру для повышения зарплаты сотрудника на 10% по его идентификатору.4. Вызвать процедуру для повышения зарплаты Иванова Ивана.Этап 3. Триггеры5. Создать таблицу SalaryChanges для фиксации изменений зарплаты, состоящей из полей: ChangeID, EmployeeID, OldSalary, NewSalary, ChangeDate.6. Создать триггер, который будет срабатывать после обновления зарплаты в EMPLOYEES и записывать изменения в SalaryChanges.7. Обновить зарплату сотруднику вручную.8. Проверить, появилась ли запись в SalaryChanges.Контрольные вопросы:Что такое хранимая процедура? Какие у неё преимущества? Что такое триггер? Когда его лучше использовать? Чем отличается триггер BEFORE UPDATE от AFTER UPDATE? Какие возможные риски существуют при использовании триггеров? Можно ли вызывать процедуру внутри триггера? Практическая работа №14 «Управление доступом к данным»Цель работы: научиться управлять правами доступа к базам данных и таблицам: предоставлять, изменять и отзывать права пользователей в СУБД.Задание:Создайте тестовую базу данных AccessDB и таблицу Employees.1. Создать нового пользователя user_viewer с паролем viewer123, который будет иметь права только на чтение таблицы Employees.2. Создать нового пользователя user_editor с паролем editor123, который будет иметь права на чтение и изменение данных в таблице Employees, но не на удаление.3. Создать пользователя user_admin с паролем admin123, который имеет полный доступ ко всей базе данных AccessDB.4. Проверить, какие права имеет пользователь user_editor.5. Отозвать у user_editor право на вставку новых записей (INSERT).6. Изменить права пользователя user_viewer, разрешив ему только просмотр имён сотрудников (через создание отдельного представления EmployeesNames).7. Удалить пользователя user_editor.Контрольные вопросы:Для чего необходимо управлять правами доступа в базе данных? В чём разница между операциями GRANT и REVOKE? Какие существуют уровни прав в СУБД (база данных, таблица, столбец)? Что произойдет, если выдать пользователю право ALL PRIVILEGES? Можно ли ограничить доступ к отдельным столбцам таблицы? Как это сделать? Практическая работа №15 «Резервное копирование и восстановление данных»Цель работы: научиться выполнять резервное копирование баз данных и восстановление данных средствами Microsoft SQL Server Management Studio (SSMS) или через T-SQL.Задание:Создайте базу данных BackupDB и таблицу Products:1. Выполните полное резервное копирование базы данных BackupDB в файл.2. Удалите базу данных BackupDB.3. Восстановите базу данных из файла .bak.4. Подключитесь к восстановленной базе BackupDB и убедитесь, что таблица Products и данные восстановлены.Контрольные вопросы:Что делает команда BACKUP DATABASE в MS SQL Server? Чем отличается параметр WITH FORMAT при бэкапе? Что означает ключевое слово WITH REPLACE при восстановлении? Какие существуют типы резервного копирования в MS SQL Server? Как можно настроить автоматическое резервное копирование базы данных? Практическая работа №16 «Работа с реляционной БД в СУБД MySQL Workbench»Цель работы: научиться работать с реляционной базой данных через графический интерфейс MySQL Workbench: создавать базу данных, проектировать таблицы, устанавливать связи между ними, заполнять данными, а также выполнять базовые SQL-запросы.Оборудование и ПО:Компьютер с установленной MySQL Workbench (версия 8.0 или выше). Сервер базы данных MySQL (локальный или удалённый). Доступ к MySQL-серверу с правами создания баз данных и таблиц. Ход работы:1. Создайте новую базу данных с именем StudentCoursesDB через SQL Editor или через вкладку «SCHEMAS» – правая кнопка мыши – «Create Schema».2. Создайте в базе данных три таблицы:Таблица Students (Студенты):Таблица Courses (Курсы):Таблица Enrollments (Записи о зачислении):3. Настройте связи между таблицами через «EER Diagram» или вручную:В таблице Enrollments поле StudentID ссылается на Students.StudentID (ON DELETE CASCADE). В таблице Enrollments поле CourseID ссылается на Courses.CourseID (ON DELETE CASCADE). 4. Вставьте тестовые данные в таблицы.5. Выполните следующие запросы:Вывести всех студентов. Вывести список всех курсов. Вывести имена студентов и названия курсов, на которые они зачислены. Посчитать количество студентов, зачисленных на каждый курс. Найти студентов, которые записаны на более чем один курс. Порядок выполнения:Создать базу данных и таблицы. Установить связи между таблицами. Заполнить таблицы данными. Построить EER-диаграмму в MySQL Workbench. Выполнить предложенные SQL-запросы. Сделать скриншоты всех этапов: создание таблиц, заполнение, EER-диаграмма, результаты запросов. Оформить отчет по работе. Контрольные вопросы:Что такое реляционная база данных? Какие типы связей существуют между таблицами? Для чего нужна внешняя связь (FOREIGN KEY)? Что делает команда ON DELETE CASCADE? Как в MySQL Workbench можно построить диаграмму связей (EER Diagram)? Практическая работа №17 «Работа с реляционной БД в СУБД PostgreSQL»Цель работы: научиться работать с реляционной базой данных через командную строку PostgreSQL (psql) или через графический интерфейс (например, pgAdmin), а также освоить создание базы данных, таблиц, связей между ними, заполнение данными и выполнение запросов.Оборудование и ПО:Сервер базы данных PostgreSQL (версия 13 или выше). pgAdmin или любой доступ к консоли psql. Права администратора для создания баз данных и таблиц. Ход работы:Создать новую базу данных с именем LibraryDB через psql или pgAdmin Подключиться к базе данных LibraryDB. Создайте в базе данных три таблицы Таблица Authors (Авторы):Таблица Books (Книги):Таблица Borrowings (Выдачи):4. Установите внешние ключи5. Вставьте тестовые данные6. Выполните следующие запросыВывести всех авторов. Вывести все книги с именами их авторов. Найти книги, выданные в феврале 2024 года. Вывести количество книг у каждого автора. Найти автора, книги которого ещё ни разу не брали. Порядок выполнения:Создать базу данных LibraryDB. Создать и настроить таблицы с ограничениями целостности данных. Заполнить таблицы тестовыми данными. Выполнить все SQL-запросы. Оформить отчет с текстом запросов и скриншотами результата выполнения. Контрольные вопросы:В чем особенности типовых данных PostgreSQL (например, SERIAL)? Чем отличается команда SERIAL от SEQUENCE в PostgreSQL? Для чего нужны внешние ключи? Что происходит при использовании ON DELETE CASCADE? Как реализовать выборку данных из нескольких связанных таблиц? Практическая работа №18 «Работа с реляционной БД в PhpMyAdmin»Цель работы: научиться использовать графический интерфейс PhpMyAdmin для создания базы данных, проектирования таблиц, установки связей, заполнения данными и выполнения базовых SQL-запросов.Оборудование и ПО:Веб-браузер. Доступ к локальному серверу (XAMPP, MAMP, WAMP) или удаленному серверу с установленным PhpMyAdmin. Учётная запись администратора или доступ к созданию БД. Ход работы:1. Через PhpMyAdmin создайте новую базу данных ShopDB: Откройте PhpMyAdmin – вкладка «Базы данных» – введите имя ShopDB – выберите кодировку utf8mb4_general_ci – нажмите Создать.2. Создайте в базе данных три таблицы через интерфейс PhpMyAdmin:Таблица Customers (Покупатели):Таблица Products (Товары):Таблица Orders (Заказы):3. Настройте связи между таблицами через вкладку «Связи» (Relation view):В таблице Orders, поле CustomerID ссылается на Customers.CustomerID. В таблице Orders, поле ProductID ссылается на Products.ProductID. Установите режим ON DELETE CASCADE для связей. 4. Через вкладку «Вставить» заполните тестовыми данными:Покупатели:Иванов Иван, телефон: +79991234567 Петрова Анна, телефон: +79998765432 Сидоров Максим, телефон: +79995678901 Товары:Ноутбук – 50000 Смартфон – 30000 Планшет – 20000 Заказы:Иванов купил Ноутбук 10.03.2024 Петрова купила Смартфон 12.03.2024 Сидоров купил Планшет 15.03.2024 5. Выполните следующие запросы:Вывести всех покупателей. Вывести все товары. Показать все заказы с именами покупателей и названиями товаров. Посчитать общее количество заказов для каждого покупателя. Показать товары стоимостью выше 25000. Порядок выполнения:Создать базу данных ShopDB. Создать таблицы и настроить их через интерфейс PhpMyAdmin. Установить внешние ключи между таблицами. Заполнить таблицы тестовыми данными. Выполнить все SQL-запросы через вкладку «SQL». Сделать скриншоты основных этапов: создание БД, таблиц, заполнение данных, выполнения запросов. Оформить отчёт по результатам работы. Контрольные вопросы:Для чего устанавливаются внешние ключи между таблицами? Какой тип связи реализован между таблицами Заказы и Покупатели? Что делает параметр ON DELETE CASCADE? В чем особенности работы с PhpMyAdmin по сравнению с текстовым написанием SQL? Как проверить целостность данных в связанном запросе? Практическая работа №19 «Проектирование реляционной БД на заданную тему в представленных СУБД»Цель работы: научиться самостоятельно проектировать реляционные базы данных на основе выбранной предметной области, создавать структуры таблиц и реализовывать проект в выбранной СУБД (Microsoft SQL Server, MySQL Workbench или pgAdmin/PostgreSQL).Оборудование и ПО:Microsoft SQL Server Management Studio (MSSQL). MySQL Workbench. pgAdmin (PostgreSQL). Ход работы:Выберите одну из предложенных тем для проектирования базы данных:База данных книжного интернет-магазина. База данных стоматологической клиники. База данных доставки еды. База данных автосалона. База данных киностудии. База данных фитнес-клуба. База данных концертных мероприятий. База данных туристического агентства. База данных университета. База данных ветеринарной клиники. База данных аренды квартир. База данных службы такси. База данных онлайн-курсов. База данных выставочного центра. База данных службы технической поддержки. База данных страховой компании. База данных библиотечного фонда. База данных языковой школы. База данных сервиса бронирования отелей. База данных корпоративного обучения сотрудников. База данных агрокомпании (учет животных/посевов). База данных онлайн-магазина одежды. База данных теннисного клуба. База данных медицинских обследований. База данных аренды строительной техники. 1. Определите сущности и атрибуты (какие таблицы нужны, какие у них будут поля).2. Определите связи между сущностямиОдин-к-одному (1:1) Один-ко-многим (1:N) Многие-ко-многим (N:N) 3. Нарисуйте концептуальную ER-диаграмму.4. Выберите СУБД для реализации проекта (MSSQL, MySQL или PostgreSQL).5. Создайте базу данных и необходимые таблицы:Укажите типы данных. Установите первичные ключи. Установите внешние ключи. Настройте ограничения (NOT NULL, UNIQUE, DEFAULT). 6. Заполните таблицы тестовыми данными (не менее 10 записей на таблицу).7. Напишите и выполните следующие SQL-запросы в количестве 40 штук:Вывод всех данных из таблиц (SELECT). Вывод связанных данных через JOIN. Агрегация данных (COUNT, SUM, AVG, MAX, MIN). Сортировка данных (ORDER BY). Фильтрация данных (WHERE, LIKE, IN, BETWEEN). Группировка данных (GROUP BY, HAVING). Порядок выполнения:Выбрать тему. Спроектировать структуру базы данных. Реализовать проект в выбранной СУБД. Заполнить таблицы тестовыми данными. Выполнить SQL-запросы для работы с данными. Построить ER-диаграмму базы. Подготовить отчёт: Название темы. Описание структуры базы данных. Скриншоты кода создания таблиц. Скриншоты тестовых данных. Скриншоты выполнения SQL-запросов. ER-диаграмму. Контрольные вопросы:Что такое реляционная модель базы данных? Чем отличается внешний ключ от первичного? Как определить типы связей между таблицами? Какие правила нормализации необходимо соблюдать при проектировании БД? Какие преимущества даёт использование внешних ключей? Самостоятельная работа №2 «Защита реляционных БД на заданную тему»Цель работы: научиться проектировать и реализовывать базовые меры защиты реляционных баз данных, развить навыки обеспечения целостности, конфиденциальности и доступности данных. Освоить создание пользователей с различными правами доступа, настройку резервного копирования, организацию контроля действий в базе данных с помощью триггеров и политик безопасности в выбранной СУБД.План выступления1. ВступлениеНазвание проекта базы данных и выбранная предметная область. Краткое описание: для чего создавалась база данных. 2. Цели разработкиОрганизация структурированного хранения информации. Обеспечение целостности, актуальности и безопасности данных. Оптимизация работы с данными: удобный доступ, обновление, удаление. Разграничение прав пользователей для защиты информации. 3. Проектирование БДКакие сущности и атрибуты были выделены. Как были установлены связи между таблицами. Какие ограничения применены (первичные, внешние ключи). 4. Защита базы данныхВыделение ролей пользователей с разными уровнями доступа. Реализация резервного копирования для защиты данных. Создание триггеров или представлений для повышения безопасности. 5. Проверка работыКак тестировалась работа с ограничениями доступа. Как тестировалась возможность восстановления данных из резервных копий. 6. ЗаключениеКак база данных решает поставленные задачи. Перспективы дальнейшего развития проекта (добавление новых функций безопасности, расширение функционала). Информационное обеспечениеОсновные источники:Учебные пособия:1. Фуфаев, Э.В. Базы данных: учеб. пособие дли студ. учреждений сред, проф. образования / Э. В.Фуфаев, Д. Э.Фуфаев. – 7-е изд., стер. – М: Издательский центр «Академия», 2022 — 320 с.: ISBN 978-5-7695-8959-12. Маркин, А. В. Программирование на SQL: учебное пособие для среднего профессионального образования / А. В. Маркин. – Москва: Издательство Юрайт, 2023 – 434 с. –(Профессиональное образование). – Текст: непосредственный ISBN 978-5-534-11093-7Дополнительные источники:Учебные пособия:1. Голицына, О.Л. Базы данных / О.Л. Голицына, Н.В. Максимов, И.И. Попов. – М.: Форум, 2020. – 352 c.2. Голицына, О.Л. Базы данных: Учебное пособие / О.Л. Голицына, Н.В. Максимов, И.И. Попов. – М.: Форум, 2022. – 400 c.3. Карпова, И.П. Базы данных: Учебное пособие / И.П. Карпова – СПб.: Питер, 2023. – 240 c.4. Кириллов, В.В. Введение в реляционные базы данных. Введение в реляционные базы данных / В.В. Кириллов, Г.Ю. Громов. – СПб.: БХВ-Петербург, 2022. – 464 c.
