Андрей Попов. Доцент.

Еврейский SQL-сервер отвечает запросом на запрос...

Базы данных

Короткий вводный курс в базы данных: основные понятия, модели и механизмы.

Вопросы к экзамену

  1. Значение данных. Базы данных как часть технологий работы с данными.
  2. Основные понятия баз данных. Файловая система как прообраз баз данных.
  3. СУБД как средство обеспечения логической и физической независимости данных.
  4. СУБД: основные понятия и функции, классификация.
  5. Архитектуры СУБД. Технологии файл-сервер и клиент-сервер.
  6. Три уровня описания данных в базах данных. Основные этапы проектирования базы данных.
  7. Концептуальное моделирование БД. ER-диаграммы.
  8. Типовые модели данных СУБД. Ранние модели данных (иерархическая и сетевая).
  9. Теоретические основы реляционной модели данных.
  10. Практические принципы реляционной модели данных.
  11. Нормализация таблиц в реляционной базе данных.
  12. Транзакции в базе данных. ACID-свойства и CAP-теорема.
  13. Блокировки в базе данных. Уровни изоляции транзакций.
  14. Физические модели представления данных. Последовательное размещение и списковая структура.
  15. Индексирование данных.
  16. Хранение данных в виде B-дерева.
  17. Возможности языка запросов SQL. Типы операторов SQL.
  18. Выборка данных из одной и нескольких таблиц (SQL). Функции агрегирования в запросах.
  19. Сортировка и группировка результатов SQL-запросов.
  20. Вложенные и связанные подзапросы SQL.
  21. Использование SQL для изменения данных в таблицах.
  22. Объектно-реляционные отображения.
  23. NoSQL базы данных.

Литература к курсу

  1. Токмаков Г.П. "Базы данных. Концепция баз данных, реляционная модель данных, языки SQL и XML : учебное пособие" – Ульяновск : УлГТУ, 2010. − 192 с.
  2. Швецов В.И., Визгунов А.Н., Мееров И.Б. "Базы данных. Учебное пособие" - Нижний Новгород : Изд-во ННГУ, 2004. - 217 с.
  3. Редмонд Э., Уилсон Д.Р. "Семь баз данных за семь дней. Введение в современные базы данных и идеологию NoSQL" - М.: ДМК Пресс, 2013. – 384с.
  4. Грабер М. "Понимание SQL" - М.: 1993. – 291 с.
  5. Форта Б. "SQL за 10 минут" - М.: Вильямс, 2014. – 288 с.
  6. Тарасов С.В. "СУБД для программиста. Базы данных изнутри" — М.: СОЛОН-Пресс, 2015. — 320 с.
  7. Кузнецов М.В., Симдянов И.В. "MySQL 5" — СПб.: БХВ-Петербург, 2006. — 1024 с.
  8. Дюбуа П. "MySQL. Сборник рецептов" — СПб.: Символ-плюс, 2006. — 1056 с.
  9. Маклафлин Б. "PHP и MySQL. Исчерпывающее руководство" — СПб.: Питер, 2013. — 512 с.
  10. Колисниченко Д. "PHP и MySQL. Разработка web-приложений" — СПб.: БХВ-Петербург, 2013. — 560 с.
  11. Смольянов А.Г., Карьгин И.П., Смольянова Е.Г. "Работа с удаленными базами данных средствами Web : учебное пособие" — Саранск: Мордов. гос. пед. ин-т, 2017. — 122 с.

Презентации к лекциям

1.Значение и классификация данных. Базы данных как часть технологий работы с данными.

2. История возникновения баз данных. Файловая система как их прообраз. Типы задач, для решения которых необходимы базы данных.

3. СУБД как средство обеспечения логической и физической независимости данных.

4. Классификация СУБД.

5. Трехуровневая архитектура данных. Этапы проектирования базы данных.

6. Концептуальная модель данных. ER-диаграммы.

7. Логическое проектирование базы данных. Типовые модели данных.

8. Реляционная модель данных.

9. Нормализация таблиц в реляционной базе данных.

11. Физические модели представления данных.

12. Транзакции и блокировки в базе данных.

13. Объектно-реляционные отображения.

14. NoSQL базы данных.

Лабораторные работы

Работа с реляционными базами данных на примере СУБД MySQL: интерактивный режим (утилиты phpMyAdmin и mysql) и программный доступ к данным (сценарии на языке PHP).

  • Для выполнения работ необходимо запустить локальный WAMP-сервер Open Server, расположенный в каталоге C:\DBMS_Popov\OpenServer_mini\OSPanel.
  • Для надежности после завершения каждого занятия лучше делать резервную копию вашей базы данных. Для этого в утилите phpMyAdmin можно экспортировать базу данных в sql-файл и сохранить этот файл в вашем домашнем каталоге.

I. Интерактивная работа с базами данных. Создание баз данных и таблиц, внесение и изменение данных (утилита phpMyAdmin)

1. (5 баллов) Создать пользователя, базу данных и таблицы.
  • Запустить СУБД MySQL (локально).
  • Зайти в утилиту phpMyAdmin под пользователем root. Создать нового пользователя своя_фамилия_303_user, предоставить этому пользователю привилегии на выполнение всех операций в СУБД. Зайти в phpMyAdmin под созданным пользователем.
  • Создать базу своя_фамилия_303 для хранения данных об успеваемости студентов 303 группы. В базе должны быть три таблицы:
    • Student - персональная информация о студентах. Фамилия, имя, отчество, пол, дата рождения, номер студенческого билета, дата выдачи студенческого билета, признак старосты группы.
    • Subject - информация о дисциплинах, которые преподавались на 2-3 курсах направления "Прикладная математика и информатика". Код дисциплины по учебному плану, наименование дисциплины, количество часов, вид аттестации (зачет, экзамен).
    • Exam - информация об набранных баллах и оценках на экзамене. Дата, дисциплина, студент, баллы (от 0 до 100), оценка (зачет, 2, 3, 4, 5).

2. (5 баллов) Вручную заполнить созданные таблицы данными. Данные о дисциплинах взять из учебного плана. Недостающую информацию внести произвольно (ФИО студентов должны быть правильные).


3. (5 баллов) Используя процедуру экспорта данных в phpMyAdmin, подготовить скрипты для создания таблиц Student, Subject и Exam в другой базе данных. Создать три комплекта скриптов:

  1. Создается только структура таблиц, введенные данные не переносятся. Если в новой базе уже были таблицы с такими названиями, они остаются без изменений.
  2. Создается только структура таблиц, введенные данные не переносятся. Если в новой базе уже были таблицы с такими названиями, они удаляются.
  3. Таблицы копируются полностью, вместе с введенными данными. Если в новой базе уже были таблицы с такими названиями, они удаляются.

Пояснить, чем отличаются друг от друга эти скрипты (на уровне SQL-запросов). Создать новую базу данных своя_фамилия_303_copy и импортировать в нее данные с помощью подготовленных скриптов.


4. (5 баллов) Изменить структуру таблиц в базах данных таким образом, чтобы в них можно было хранить данные о нескольких группах и дисциплинах по нескольким направлениям. В таблицах дополнительно должны храниться:

  • Student - номер группы, код учебного направления.
  • Subject - код учебного направления.

Изменения в базе данных своя_фамилия_303 выполнить вручную через phpMyAdmin, в базе данных своя_фамилия_303_copy - с помощью заранее подготовленного скрипта (команда ALTER TABLE).


5. (5 баллов) Внести изменения в новые поля для данных по группе 303 с помощью команды UPDATE. Подготовить скрипты (с командой INSERT) для добавления информации о группах 302 (ФИиИТ) и 304 (ПИ). Данные о дисциплинах (за 2 и 3 курс) взять из учебных планов ФИиИТ и ПИ. Добавить данные в таблицы с помощью этих скриптов.


II. Интерактивная работа с базами данных. Выборка и удаление данных (утилиты phpMyAdmin и mysql.exe)

6. (5 баллов) Составить и сохранить в текстовом файле SQL-запросы (команда SELECT) для получения следующей информации (один пункт - один запрос!):

  1. Анкетные данные студентов из группы 303 (ФИО, пол, дата рождения). Данные должны выводиться в алфавитном порядке.
  2. Анкетные данные студентов из всех групп (номер группы, ФИО, пол, дата рождения). Сначала должны выводиться данные о студентах 302 группы, затем 303 и 304 (внутри группы - сортировка по ФИО).
  3. ФИО, номера студенческих билетов старост групп 302, 303 и 304.
  4. ФИО трех студентов из группы 303, набравших наибольшее количество баллов по физике (количество баллов тоже должно быть выведено).
  5. Сводная информация об экзамене по физике в 303 группе: количество оценок "2", "3", "4", "5".
  6. Анкетные данные студентов, сдавших все экзамены на "4" и "5".
  7. Гендерный анализ: количество мужчин и женщин в каждой из групп.
  8. Номер группы, ФИО, номера студенческих билетов всех студентов, которые не сдавали экзамен по курсу "Теория автоматов и формальных языков".
  9. Данные о всех ваших экзаменах (ФИО, дата экзамена, код и название дисциплины, полученные баллы и оценка).
Полученные запросы выполнить в phpMyAdmin и в консольной утилите mysql.exe. Изменить полученные запросы, чтобы результат их выполнения сохранялся во внешних текстовых файлах.


7. (4 балла) Удаление записей с помощью оператора DELETE.

  • Выбрать несколько студентов, записи об экзаменах которых имеются в таблице Exam. Удалить этих студентов из таблицы Student и убедиться в том, что данные о их экзаменах остались в таблице Exam (потеря логической целостности данных в базе).
  • Установить связь между таблицами, чтобы при наличии в таблице Exam записей о сданных экзаменах определенного студента запись о нем нельзя было бы удалить из таблицы Student.

III. Программная работа с базами данных (веб-приложения на языке PHP)

Во всех заданиях требуется создавать сценарии на языке PHP, в которых происходит подключение к базе данных своя_фамилия_303 с помощью драйвера MySqli или PDO.

8. (8 баллов) С помощью сценария PHP подключиться к базе данных и сформировать HTML-страницу со списком студентов 301-303 групп. Список выводить в табличном виде (номер группы, ФИО, пол, дата рождения, номер студенческого билета, дата выдачи студенческого билета, признак старосты группы). Информация должна быть отсортирована по номеру группы и фамилиям студентов.

9. (8 баллов) Создать форму для ввода данных о студентах (номер группы выбирать из раскрывающегося списка, пол устанавливать с помощью переключателя, признак старосты группы - с помощью флажка). Написать сценарий для добавления новой записи в таблицу Student на основе данных, передаваемых из этой формы.

10. (16 баллов) Написать веб-приложение для вывода и редактирования данных студентах и экзаменах. На стартовой странице должен выводиться список всех студентов (сортировка по номеру группы и фамилии). Для каждой записи нужно добавить:

  • Кнопки/ссылки "Редактировать" и "Удалить", при нажатии на которые будет открываться форма для изменения персональных данных студента или форма с запросом на удаление данной записи.
  • Кнопку "Результаты экзаменов", при нажатии на которую будет открываться список с результатами всех экзаменов (в хронологическом порядке), которые сдавал этот студент.
Внизу списка студентов разместить кнопку "Добавить", вызывающая форму для ввода новой записи.

Удаление данных о студенте должно вызывать каскадное удаление данных о всех его экзаменах.

Предусмотреть в приложении возможность построения отчетов и выгрузки их в текстовый файл на рабочей станции:

  • Сводная информация об экзамене по определенному предмету в одной группе: количество оценок "2", "3", "4", "5". Название предмета выбирать из раскрывающегося списка, номер группы устанавливать с помощью радиокнопки.
  • Анкетные данные студентов, сдавших все экзамены на "4" и "5".
  • Гендерный анализ: количество мужчин и женщин в каждой из групп.

Контрольная работа

База данных содержит три таблицы: продавцов, покупателей и заказов.
Prodavec Pokupatel  Zakaz
Prod_Nom (ID продавца)
Prod_Name (Фамилия)
City (Город)
Procent (Процент от продаж)
  Pok_Nom (ID покупателя)
Pok_Name (Фамилия)
City (Город)
Rating (Рейтинг)
Prod_Nom (ID продавца)
  Zak_Nom (ID заказа)
Zak_Summa (Сумма заказа)
Zak_Date (Дата заказа)
Pok_Nom (ID покупателя)
Prod_Nom (ID продавца)

Вариант 1 (студенты с фамилиями от А до К)

  1. Для каждого продавца найти общую сумму, на которую он продал товар за все время. В результате запроса должно быть видно фамилию продавца, а не его номер.
  2. Найти фамилии всех продавцов, не имеющих покупателей.
  3. Определить, на какую общую сумму Иванов продал товар Потапову за все время. В запросе должны фигурировать фамилии продавца и покупателя, а не их номера.
  4. Для каждого покупателя найти максимальную сумму, которую он тратил за один раз. В результате запроса должно быть видно фамилию покупателя, а не его номер.

Вариант 2 (студенты с фамилиями от Л до Я)

  1. Для каждого продавца определить количество покупателей, которых он обслуживает. В результате запроса должно быть видно фамилии продавцов.
  2. Найти фамилии всех продавцов, имеющих хотя бы одного покупателя.
  3. Для каждого продавца найти общую сумму, полученную им в качестве процента от продаж товара за все время. В результате запроса должно быть видно фамилию продавца, а не его номер.
  4. Для каждого покупателя найти минимальную сумму, которую он тратил за один раз. В результате запроса должно быть видно фамилию покупателя, а не его номер.
Проверять выполнение запросов можно здесь.

Статистика группы 303

  • Полное выполнение задания к контрольному сроку - 100% баллов за это задание.
  • Задержка с выполнением задания на срок до 14 дней - 80% баллов за это задание.
  • Задержка с выполнением задания на срок более 14 дней - 50% баллов за это задание.

Видеолекции (2017 год)

1. Значение и классификация данных. Базы данных как часть технологий работы с данными. История возникновения баз данных. Файловая система как их прообраз. Типы задач, для решения которых необходимы базы данных (18.02.2017)


2. Необходимость в программном интерфейсе между прикладными программами и базой данных. Основные функции и классификация СУБД (04.03.2017)


3. Три уровня представления данных в базе данных. Этапы проектирования баз данных. Концептуальная модель данных, ER-диаграммы. Логические модели данных. Иерархическая модель (18.03.2017)


4. Реляционная модель данных. (01.04.2017)


5. Нормализация реляционной базы данных. Язык SQL. (15.04.2017)


6. Язык SQL (продолжение). Структуры хранения данных во внешней памяти (последовательное размещение записей, связанный список). Индексирование (обычные и сбалансированные индексы). (29.04.2017)


7. Транзакции и блокировки в базе данных. ACID-свойства транзакций. CAP-теорема. Проблемы параллелизма. Виды блокировок в БД. Уровни изоляции транзакций. (13.05.2017)


8. NoSQL базы данных. Узкие места реляционных СУБД, причины развития NoSQL СУБД. Объектно-реляционные проекции. Особенности NoSQL СУБД. Модели данных NoSQL (27.05.2017)

Обзорные лекции (2016 год)

  1. Три уровня описания данных в базах данных. Основные этапы проектирования базы данных.
  2. Концептуальное моделирование БД. ER-диаграммы.
  3. Нормализация таблиц в реляционной базе данных.
  4. Практические задачи.