БД и СТ

Базы данных и сетевые технологии, практика.

Требования к зачету

По результатам практических занятий Базы данных и сетевые технологии каждый студент может набрать до 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 этапов сдачи проекта

Согласование схемы БД, до 01.10.2016

#	Работа	Баллы 4
1	Соответствие БД рассматриваемой предметной области	1
2	Текстовое описание БД и её полей	2
3	Соответствие требованиям предъявляемым к объектам	1

Приемка первой части схемы БД, до 15.10.2015

#	Работа	Баллы 8
1	Соответствие дампа техническим требованиям	1
2	Подходящие типы данных	1
3	Именование таблиц, полей	2
4	Ограничения PK	1
5	Ограничения UK	1
6	Соответствие схемы 3НФ	1
7	Данные в таблицах	1

Приемка второй части схемы БД, до 29.10.2015

#	Работа	Баллы 8
1	Ограничения CK	2
2	Ограничения FK	2
3	Именование ограничений	2
4	Последовательности, связи	2

Написание 4-х легких запросов с описанием, оптимизация до 12.11.2016

#	Работа	Баллы 4

Написание 3-х средних запросов с описанием, оптимизация до 26.11.2016

#	Работа	Баллы 9

Написание 3-х сложных запросов с описанием до 10.12.2016

#	Работа	Баллы 9

Оформление работы перед зачетом, до (17|24).12.2016

#	Работа	Баллы 2
1	Оформление работы	2

Несоблюдение сроков

Баллы за каждый этап работы насчитываются 1 раз.

Крайний сдачи работы - накануне дня сдачи(т.е. в пятницу), до 23:59 включительно.

У каждого студента есть возможность один раз показать пробную версию сдаваемой части работы, в срок не менее чем за неделю до окончания соответствующего этапа, получить небольшую рецензию и исправить недочеты, но чем раньше - тем лучше, иначе я могу не успеть проверить работу.

При несоблюдении студентом сроков сдачи каждого этапа работы, из полученного количества баллов за этот этап вычитается 40%.

Допускается 20% вычет при опоздании с работой не более чем на 24 часа.

Пересдача

На пересдаче вся работа принимается заново, все недочеты могут быть исправлены, но, из результата вычитается 20%.

Оставшиеся 8 баллов можно получить написав два сложных запроса по БД Студенты, с которой мы работаем на паре, по 4 балла за запрос.

Введение, Нормализация БД

Целостность данных, последовательности

Синтаксис создания/изменения таблиц

Синтаксис создания индексов и послевовательностей

Cинтаксис выборок и группировки

Запросы по нескольким таблицам

Функции

Оптимизация запросов

Оконные функции

Работа над ошибками

Примеры оформления информаци по вашим схемам БД

База данных студентов (объектная схема)

Студент

номер зачетной книжки
Фамилия
Имя
Отчество
Дата рождения

Группа

номер

Хобби

Название

Отношения

Студент m:1 Группа (принадлежит)
Студент 1:1 Группа (является старостой)
Студент m:m Хобби (увлекается)

База данных студентов (табличная схема)

Студенты

id
номер зачетной книжки
Фамилия
Имя
Отчество
Дата рождения
id группы (принадлежит)

Группа

id
номер
id старосты (является старостой)

Хобби

id
Название

Хобби Студентов (студент увлекается)

id студента
id хобби

База данных студентов (sql дамп)

Дамп БД студентов

Пример генерации содержимого таблицы

Sql команды

Примеры запросов

Все нижеприведенные запросы характерны для разных баз данных, и приведены более для понимания того, что именно должно быть в Вашей работе.

Информация в скобках больше нужна для Вашего понимания тех таблиц, на которых производятся запросы.

Каждый запрос должен быть актуальным, т.е. не синтетическим.

Желательно у каждого запроса оставить дополнительную информацию, зачем он нужен?

Для каждого запроса будут также свои дополнения по базе данных, они не обязательны, но в случае моих запросов необходимы, т.к. базы немного отличаются.

Для параметризованного запроса необходимо указать несколько возможных комбинаций значений параметров, работающих для вашей БД, чтобы была возможность легко проверить работоспособность запроса.

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';