БД и СТ
Базы данных и сетевые технологии, практика.
Требования к зачету
По результатам практических занятий Базы данных и сетевые технологии каждый студент может набрать до 40 баллов.
- Описать схему базы данных, особенности о которых необходимо знать её пользователю.
- Желательно чтобы схема была не синтетической - являлась реальной моделью, схема предварительно согласовывается.
- Не менее трех "объектных" таблиц и, желательно, не более семи таблиц во всей схеме.
- Две таблицы(главные) должны содержать не менее четырех полей данных каждая(поля с id не учитываются).
- Не менее 9 записей для каждой из таблиц, не менее 15 для "главных", в общем случае записей должно быть достаточно, чтобы запросы были интересными.
- Схема должна быть такой, чтобы в ней были представлены как минимум отношения вида: m:1 и m:m.
- Каждый объект должен иметь хотябы одну связь.
- Правильное именование таблиц и их полей.
- Использование типов данных соответствующих значениям, находящимся в базе. Типы должны быть подобраны оптимально!
- Создать ограничения PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, FOREIGN KEY, INDEX для:
- Описания сущностей реального мира
- Соответствия 3НФ
- Добавления правильных ограничений целостности как сущностей так и их арибутов
- Оптимизации запросов
Вопросы семантически не должны быть похоже друг на друга.
- Каждый запрос, перед его составлением на SQL, описать на русском языке. Как и база, запрос не должен быть синтетическим, а должен быть "реальным". Запрос должен выбирать данные из БД.
- Составить четыре простых запроса.
- Составить три средних запроса.
- Составить три сложных запроса.
- Оптимизировать.
Примеры легких запросов:
- Любые запросы на выборку из одной таблицы
Примеры средних запросов:
- Как правило все средние запросы - это обычные запросы со склейкой таблиц по условию равенства значений в столбцах
- Запросы из нескольких таблиц с "обычными" inner, left, right join-ами
- Группировка данных из нескольких таблиц в несколько строк
- Все вложенные запросы которые делают тоже, что и приведенные выше запросы
- Пример: Выбрать имена и фамилии всех студентов, определившихся с кафедрой, и короткое название этой кафедры
- Пример: Выбрать каждую кафедру и количество студентов на ней
- Пример: Выбрать каждого студента и короткое название кафедры, если он определился в её выбором.
Примеры сложных запросов:
- Запросы из нескольких таблиц с избирательными inner, left, right join-ами (т.е. с изменением стандартных условий на склейку)
- Группировка данных таких запросов
- Подсчет различной промежуточной информации для выборки
- Пример: Определить самую популярную кафедру(ы). Вывести её(их) короткое название и кол-во студентов (т.е. в случае наличия двух и более кафедр с равным количеством студентов выводиться должны все такие кафедры)
- Пример: Вывести рейтинг кафедр с подсчитанным средним баллом среди всех студентов сдававших экзамены в летней сессии 14 года с учетом того что средний бал подсчитывается только в том случае если на кафедре не менее 2-х студентов имеющих хотя бы по одной оценке в учетный период.
- Пример: Определить какой предмет был лучше всего пройден кафедрой 'ТП'. Критерием является наибольший средний балл по предметам за все время.
При построении сложных запросов надо отдавать приоритет join операциям нежели подзапросам!
- Работа сдается в виде pdf документа включающего в себя:
- Титульный лист с названием БД
- Описание схемы БД
- Легкие запросы, описание, реализация, оптимизация
- Средние запросы, описание, реализация, оптимизация
- Сложные запросы, описание и реализация
- При оптимизации под каждым запросом необходимо составить список индексов созданых для этой цели, если используются уже существующие индексы - перечислить их.
- К работе прикладывается sql дамп базы включающий в себя:
- Команды по удалению/созданию самой бд
- Команды по созданию всех сущностей бд: таблиц, очередей, представлений, ограничений, связыванию их
- Команды наполнения данными таблиц
- Других команд быть не должно (необходимо вырезать их вручную из дампа)
- Порядок команд не важен, - самое главное чтобы дамп загружался без ошибок.
- Крайне желательно все команды, создающие отдельные таблицы, сгруппировать, а все ограничения убрать в конец дампа, где их также сгруппировать по таблицам.
- Кодировка БД и таблиц - utf8
- Не используем кириллицу для наполнения таблиц (в случае отсутствия проблем с кирилицей, она допускается)
- Дамп базы, как и сама база, должны именоваться следующим образом: "первая буква имени" . "фамилия" на английском языке, например бд k.malinin файл k.malinin.sql
Сроки сдачи проектов
7 этапов сдачи проекта
# | Работа | Баллы 4 |
---|---|---|
1 | Соответствие БД рассматриваемой предметной области | 1 |
2 | Текстовое описание БД и её полей | 2 |
3 | Соответствие требованиям предъявляемым к объектам | 1 |
# | Работа | Баллы 8 |
---|---|---|
1 | Соответствие дампа техническим требованиям | 1 |
2 | Подходящие типы данных | 1 |
3 | Именование таблиц, полей | 2 |
4 | Ограничения PK | 1 |
5 | Ограничения UK | 1 |
6 | Соответствие схемы 3НФ | 1 |
7 | Данные в таблицах | 1 |
# | Работа | Баллы 8 |
---|---|---|
1 | Ограничения CK | 2 |
2 | Ограничения FK | 2 |
3 | Именование ограничений | 2 |
4 | Последовательности, связи | 2 |
# | Работа | Баллы 4 |
---|
# | Работа | Баллы 9 |
---|
# | Работа | Баллы 9 |
---|
# | Работа | Баллы 2 |
---|---|---|
1 | Оформление работы | 2 |
Несоблюдение сроков
Пересдача
Примеры оформления информаци по вашим схемам БД
Студент
- номер зачетной книжки
- Фамилия
- Имя
- Отчество
- Дата рождения
Группа
- номер
Хобби
- Название
Отношения
- Студент m:1 Группа (принадлежит)
- Студент 1:1 Группа (является старостой)
- Студент m:m Хобби (увлекается)
Студенты
- id
- номер зачетной книжки
- Фамилия
- Имя
- Отчество
- Дата рождения
- id группы (принадлежит)
Группа
- id
- номер
- id старосты (является старостой)
Хобби
- id
- Название
Хобби Студентов (студент увлекается)
- id студента
- id хобби
Все нижеприведенные запросы характерны для разных баз данных, и приведены более для понимания того, что именно должно быть в Вашей работе.
Информация в скобках больше нужна для Вашего понимания тех таблиц, на которых производятся запросы.
Каждый запрос должен быть актуальным, т.е. не синтетическим.
Желательно у каждого запроса оставить дополнительную информацию, зачем он нужен?
Для каждого запроса будут также свои дополнения по базе данных, они не обязательны, но в случае моих запросов необходимы, т.к. базы немного отличаются.
Для параметризованного запроса необходимо указать несколько возможных комбинаций значений параметров, работающих для вашей БД, чтобы была возможность легко проверить работоспособность запроса.
1 Запрос
Выбрать Номер студенческого билета, Фамилию и Имя студента нижний регистр Имени которого равен заданному значению.
(БД: Предполагаем что на столбце имен нет проверки на initcap и соответственно имя может быть написано в каком угодно регистре)
SELECT s.number, s.lastname, s.firstname< FROM students AS s WHERE lower(s.firstname) = __FIRSTNAME__;
Допустимые параметры [__FIRSTNAME__]:
- ('иван')
- ('анастасия')
- ('евгений')
Необходимость:
Поиск студента по имени
Оптимизация:
Был добавлен индекс students_firsname_idx_lower для осуществления фильтрации по нижнему регистру поля firstname.
CREATE INDEX students_firsname_idx_lower ON students ((lower(firstname)));
2 Запрос
Выбрать Фамилию, Имя и Средний балл студента определенного курса, определенного пола. Отсортировать по убыванию среднего балла. Вывести первые 10 результатов.
(БД: Предположим что у нас есть столбец со средним баллом прямо в таблице студентов,- mark)
SELECT s.lastname, s.firstname, s.mark FROM students AS s WHERE s.course = __COURSE__ AND s.sex = __SEX__ ORDER BY s.mark DESC NULLS LAST LIMIT 10;
Допустимые параметры [__COURSE__, __SEX__]:
- (4, 'male')
- (2, 'female')
- (2, 'male')
Необходимость:
Предполагаем что у нас будет повышенная стипендия которую будут выплачивать топ 10 студентов каждого курса и пола (необходимость тут надуманная, таких запросов не должно быть, тут он больше для запроса с двумя параметрами)
Оптимизация:
Был добавлен индекс students_course_idx для осуществления фильтрации студентов по курсу.
CREATE INDEX students_course_idx ON students (course);
Нет необходимости добавлять мультиколоночный ключ на столбцы (course, sex) т.к. количество студентов мужского пола и женского примерно равное. Такой индекс не будет эффективным.
3 Запрос
Выбрать Фамилию, Имя и Средний балл студента мужского пола второго курса со средним баллом более 4, отсортировать по Фамилии и Имени.
SELECT s.lastname, s.firstname, s.mark FROM students AS s WHERE s.course = 2 AND s.sex = 'male' ORDER BY s.lastname, s.firstname;
Необходимость:
Подаем список студентов на военную кафедру. (что важно, запрос именно с этими параматрами нас интересует а с другими - нет, и это прописано в необходимости, поэтому оптимизация будет специфической!)
Оптимизация:
Был добавлен индекс students_course_sex_idx_2_male с условием по курсу и полу, который сразу вернет нам идентификаторы нужных записей.
CREATE INDEX students_course_sex_idx_2_male ON students (course, sex) WHERE course = 2 AND sex = 'male';