аудиторный фонд

Описание персоналий

pers(kodPers, Fam, kodTip)

kodPers - int– код персоны. Для студентов – номер зачётки, для преподователей, табельный номер

Fam – строка не более 12 символов – фамилия персоны

kodTip - int– код типа персоны(1-студент, 2-сотрудник, 0 - гость)

Описание состава групп

sostGR(nameGr, idPers)

idPers int - код персоны

nameGr – строка – наименование группы, например ИТб112, МРи101

Прохождение через турникет - proh(kodPers, dataP, inOut, nk)

Где -

kodPers (int)– код персоны

dataP (DataTime) – дата прохода

inOut (bit) - направление прохода(1-туда, 0-выход)

nk – номер корпуса

Расписание

rasp(nаmeGR, dataNach, DataEnd, idPredm, idPrep, idAud)

dataNach (DataTime) – дата начала занятия

DataEnd (DataTime) - дата окончания занятия

idPredm (int) – код предмета

idAud (int) – номер аудитории по принципу нумерации КузГТУ

idPrep (int) – код персоны, ведущей занятия (FK на pers)

аудиторный фонд

audFond(idAud, nk)

Задания.

1.

1.1. вывести Фамилии студентов, не входивших в корпус на заданную дату

SELECT Fam FROM `pers` WHERE kodPers IN (SELECT kodPers FROM `proh`) AND kodTip='1'

1.2. Вывести фамилии преподавателей, у которых есть занятия в третьем корпусе на сегодня.

SELECT Fam FROM `pers` WHERE kodPers IN (SELECT rasp.idPrep FROM `rasp` WHERE rasp.idPredm IN (SELECT audfond.idAud FROM audfond WHERE audfond.nk='3'))

1.3. Вывести фамилии студентов, занесённых в две учебные группы

1.4. Занести в таблицу аудиторный фонд аудитории из таблицы расписаний.

INSERT INTO `audfond` (`idAud`) VALUES(SELECT rasp.idAud FROM `rasp` WHERE rasp.idAud NOT IN (SELECT audfond.idAud FROM `audfond`))

2.

2.1. Вывести фамилии преподавателей, проводящих занятие в третьем корпусе за период ‘2015-12-12 10: 30’ ‘2015-12-12 12: 30’ у группы ит-131, ит-132

SELECT pers.Fam FROM `pers` WHERE pers.kodPers IN (SELECT rasp.idPrep FROM `rasp` WHERE rasp.dataNach IN ('2015-11-10 17: 00: 00', '2015-11-02 20: 38: 00') AND rasp.idPredm IN (SELECTaudfond.idAud FROM audfond WHERE audfond.nk = '3') AND rasp.nameGr LIKE 'ITb-13')

2.2. Id студентов, присутствующих в третьем корпусе за период ‘2015-10-30 10: 30’ ‘2015-10-30 12: 30’ согласно расписанию (решаемо через предикат in)

SELECT pers.Fam FROM `pers` WHERE pers.kodPers IN (SELECT proh.kodPers FROM `proh` WHERE proh.dataP ='2015-11-02 00: 00: 00' AND proh.inOut='1' AND proh.nk='3', SELECT proh.kodPers FROM `proh` WHERE proh.dataP ='2015-11-02 10: 00: 00' AND proh.inOut='2' AND proh.nk='3')

2.3. Вывести занятия (idPredm) проводимые за перид

‘2015-10-30 10: 30’ ‘2015-10-30 12: 30’ в третьем корпусе у группы ит-131, ИТ-132.

SELECT rasp.idPredm FROM rasp WHERE rasp.dataNach='2015-11-10 17: 00: 00' AND rasp.DataEnd='2015-11-02 20: 38: 00' AND rasp.nameGr='ITb-13%' AND rasp.idPredm IN (SELECT audfond.idAudFROM `audfond` WHERE audfond.nk='3')

2.4. Вывести преподавателей, присутствующих в 3-м корпусе согласно расписанию за период ‘2015-10-30 10: 30’ ‘2015-10-30 12: 30’

SELECT pers.Fam FROM `pers` WHERE pers.kodPers IN (SELECT rasp.idPrep FROM rasp WHERE rasp.dataNach='2015-11-10 17: 00: 00' AND rasp.DataEnd='2015-11-02 20: 38: 00' AND rasp.idPredmIN(SELECT audfond.idAud FROM `audfond` WHERE audfond.nk='3'))

3.

3.1. Вывести всех персоналий оставшихся в корпусе после даты ‘2015-10-30 12: 30’

SELECT pers.Fam FROM `pers` WHERE pers.kodPers NOT IN (SELECT proh.kodPers FROM `proh` WHERE proh.dataP> '2015-11-02 06: 00: 00' AND proh.nk='3' AND proh.inOut='2')

3.2. Найти студентов (id) которые должны находится согласно расписанию за период ‘2015-10-30 10: 30’ ‘2015-10-30 12: 30’ в корпусе, но в корпус не входивших

3.3. Вывести студентов группы итб-131 входивших в корпус 3 за период ‘2015-10-30 10: 30’ ‘2015-10-30 12: 30’

3.4. Найти группы, которые не должны находится в корпусе согласно расписанию в период ‘2015-10-30 10: 30’ ‘2015-10-30 12: 30’

4.

4.1. Что такое потенциальный ключ отношения. Что такое сложный ключ и простой? Требование предъявляемые к первой нормальной форме?

Потенциальный ключ отношения – это подмножество атрибутов отношения, которые обладают свойствами уникальности и не избыточности (Что это такое?).

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

* Не избыточность подразумевает то, что в нём нельзя выделить некоторое подмножество атрибутов, обладающим свойством уникальности.

Сложный ключ – это тот ключ, который состоит из нескольких атрибутов. Особенность такого ключа в том, что в нём можно выделить части подмножества атрибутов, обладающих уникальностью.

Простой ключ – это тот ключ, который состоит из одного атрибута. Особенность такого ключа в том, что в нём нет частей, т.е. невозможны зависимости от ключа и не ключевых атрибутов.

***

Требования к 1НФ. Все данные, содержащиеся в таблице, должны быть атомарными, т.е. в каждом поле каждой записи должна находиться только одна величина, но не массив или какая-либо другая структура данных.

Отклонения, которых нужно избежать, для приведения отношения к 1 НФ:

1. Избыточность – это происходит тогда, когда определённый параметр дублируется для каждого атрибута, например:

*В конкретном примере (отношение не относится к 1 НФ!!!) избыточность состоит в том, что фамилия преподавателя и данные о его степени повторяются для каждой оценки, которую он поставил!

2. Аномалия удаления – при необходимости удалить данные о студенте есть возможность удаления данных о преподавателях или о предметах;

3. Аномалия вставки – необходимость дополнения пустыми значениями атрибутов при вставке данных о новом преподавателе/студенте/предмете.

4.2. Требования ко второй нормальной форме? Что можно сказать об отношении которое имеет простой ключ, с точки зрения нормальных форм? Привести пример отношения не отвечающего требованиям второй нормальной формы.

Требования ко 2НФ:

1. Отношение должно находиться в первой нормальной форме;

2. Отсутствие не ключевых атрибутов, зависящих от части сложного ключа.

Короче говоря, значение каждого поля должно полностью определяться значением первичного ключа!

Про отношение, которое имеет простой ключ можно сказать то, что оно всегда находится в состоянии 2НФ, к примеру, A = {‘ID’, “Name”}, оба ключа простые, подмножества этих ключей пусты.

Пример отношения, не относящийся к 2НФ:

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

4.3. Каким образом отношение приводится ко второй нормальной форме? Требует ли отношение нормализации по второй, третье нормальной форме. Изменить отношение (добавить атрибуты), что бы оно не отвечало требованиям 2НФ, 3НФ Отношение Продукт(КодПродукта, НаименованиеПродукта, КодПроизводителя, ЦенаДанногоПроизводителя).

Для того чтобы привести (нормализовать) наше отношение ко 2НФ, как правило, производят декомпозицию одной таблицы на несколько таких таблиц.

Исходное:

R = { ’Название группы’, ’Название СД-диска’, ’Название песни’, 'Автор слов’, ’Композитор’ }

Нормализованнное:

R1 = { ’Название группы’, ’Название СД’, ’Название песни’ }

R2 = { ’Название группы’, ’Название песни’, ’Автор’, ’Композитор’ }

Изменить отношение (добавить атрибуты), чтобы оно не отвечало требованиям 2НФ, 3НФ. Отношение: Продукт (Код Продукта, Наименование Продукта, Код Производителя, Цена Данного Производителя).

***

Изменим отношение (нарушим состояние 2НФ, добавим атрибут - № Поставщика): Продукт (Код Продукта, Наименование Продукта, Код Производителя, Цена Данного Производителя (ДП), Номер Поставщика).

Поставщик может поставлять различные товары, а один и тот же товар может поставляться разными поставщиками. Тогда ключ отношения - " N_поставщика + Наименование продкта". Пусть все поставщики поставляют товар по одной и той же цене. Тогда имеем следующие функциональные зависимости:

N_поставщика, Наименование продукта -> Цена ДП

Наименование продукта -> Цена ДП

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

***

Изменим отношение (нарушим состояние 3НФ, добавим атрибуты: Склад, Объём): Продукт (Код Продукта, Наименование Продукта, Код Производителя, Цена Данного Производителя (ДП), Склад, Объём).

Ключевой атрибут - " Код Производителя ". Если каждая фирма может получать товар только с одного склада, то в данном отношении имеются следующие функциональные зависимости:

• Код Производителя -> склад
• склад -> объем

При этом возникают аномалии:

• если в данный момент ни один производитель не получает товар со склада, то в базу данных нельзя ввести данные о его объеме (т.к. не определен ключевой атрибут)
• если объем склада изменяется, необходим просмотр всего отношения и изменение кортежей для всех производителей, связанных с данным складом.

4.4. Требование к третьей нормальной форме реляционного отношения? Привести пример отношения не отвечающего требованием третьей нормальной формы.

Требования к 3НФ. Таблица находится в 3НФ, когда она находится во 2НФ и все не ключевые столбцы взаимно связаны. Также необходимо учитывать тот факт, что нормализация должна быть обратима, т.е., должна быть возможность получить исходное отношение в виде запроса, соединяющего полученное отношение.

* Столбцы взаимно зависимы тогда, когда нельзя изменить один из них, не меняя другой.

***

Пусть имеется отношение ХРАНЕНИЕ (ФИРМА, СКЛАД, ОБЪЕМ), которое содержит информацию о фирмах, получающих товары со складов, и объемах этих складов. Ключевой атрибут - " фирма ". Если каждая фирма может получать товар только с одного склада, то в данном отношении имеются следующие функциональные зависимости:

фирма -> склад

склад -> объем

При этом возникают аномалии:

1) если в данный момент ни одна фирма не получает товар со склада, то в базу данных нельзя ввести данные о его объеме (т.к. не определен ключевой атрибут)

2) если объем склада изменяется, необходим просмотр всего отношения и изменение кортежей для всех фирм, связанных с данным складом.

Для устранения этих аномалий необходимо декомпозировать исходное отношение на два:

ХРАНЕНИЕ (ФИРМА, СКЛАД)

ОБЪЕМ_СКЛАДА (СКЛАД, ОБЪЕМ)