4.1.4. Нормальная форма Бойса - Кодда (bcnf )

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

1. Объясните сущность нормализации?

2. В чем заключается аномалия вставки, удаления , модификации, когда проявляется?

3. Что такое потенциальный ключ отношения. Что такое сложный ключ и простой?

4. Требование предъявляемые к первой нормальной форме?

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

6. Каким образом отношение приводится ко второй нормальной форме?

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

Лекция 6. Объекты данных и объекты манипулирования данными. Язык SQL.

6.1. Объекты данных и объекты манипулирования данными в модели базы данных.

В модели СУБД выделяют следующие объекты данных:

1. Собственно данные. Представляются в виде отношений (таблиц).

2. Индексы – специальные объекты, предназначенные для быстрого поиска элементов данных.

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

4. Триггер – специальная процедура, связанная с таблицей или с отдельным полем таблицы (с отношением или с атрибутом отношения). Триггер выполняется при обращении к данному отношению или атрибуту. Его можно использовать для контроля значений в записываемых отношениях, или для выполнения действий, связанных с обращениями к отношению (ведение журнала, значения связанных таблиц и т.д.)

Объекты, используемые при обработке данных:

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

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

Обычно с объектами БД связываются дополнительные объекты, обеспечивающие интерфейс пользователя с объектами БД: формы, отчёты.

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

Отчет – Объект позволяющий рассматривать результаты запроса с выполнением дополнительных итоговых вычислений и оформлением итоговых данных. Формы и отчеты не являются объектами манипулирования данными, однако многие СУБД включают средства разработки форм и отчетов (например, ACCESS).

6.2. Структурированный язык запросов SQL

Общая характеристика.

SQL содержит набор стандартных операторов доступа к данными.

SQL (Structured Query Language, структурированный язык запросов) — это язык программирования, предназ­наченный для выборки и обработки информации, содержащейся в реляционной базе данных. SQL является единственным стандартным языком для работы с реляционными базами данных. Существуют следующие версии SQL:

SQL1 (принята в 1986 году, дополнена в 1989 году стандарт ANSI) ,

ANSI - Америка́нский национа́льный институ́т станда́ртов (англ. American National Standards Institute, ANSI) — объединение американских промышленных и деловых групп, разрабатывающее торговые и коммуникационные стандарты. Входит в ISO и Международную электротехническую комиссию, представляя там свою страну.

SQL2 (SQL-92 принята в 1992 году),

SQL3 (SQL-99 ) расширяет SQL2 за счет включения объектно-реляционных инструментов и новых функциональных возможностей.

SQL — это слабо структурированный язык, особенно по сравнению с такими высокоструктурированными языками, как С, Pascal или Java. В нем нет инструкции IF..THEN для проверки условий, нет инструкции GOTO для организации переходов и нет инструкций DO или FOR для создания циклов.

SQL является языком реляционных баз данных, его основа реляционная алгебра и реляционное исчисление.

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

Версии SQL предлагаемые основными поставщиками СУБД несколько отличаются. они, как правило, удовлетворяют требованиям ANSI, реализуют многие возможности SQL2 и имеют некоторые особенности.

Язык SQL является интерпретируемым языком.

Инструкции SQL могут использоваться самостоятельно в рамках СУБД, встраиваются в базовый язык (C/C++), используемый при разработке программных модулей, предназначенных для работы БД, или используются в качестве аргументов специальных функций (API), обеспечивающих передачу инструкций SQL базе данных из программ на C/C++ или Java.

Существуют также диалекты SQL позволяющие создавать самостоятельные программные модули, например, PL/SQL и Transact-SQL. В этих диалектах стандартный SQL дополнен инструкциями IF..THEN, GOTO и др., однако эти диалекты не получили статус стандарта и являются частными разработками отдельных компаний (PL/SQL применяется в СУБД Oracle, a Transact SQL - в СУБД MS SQL Server).

SQL не является отдельным прог­раммным продуктом. SQL — это неотъемлемая часть СУБД ее Манипуляционная часть, инструмент, с помощью которого осуществляется связь пользователя с БД.

Различают несколько групп операторов (подъязыки):

I. Язык определения данных DDL.

К языку запросов относятся операторы

CREATE TABLE - создания нового отношения;

DROP TABLE - удаление отношения;

ALTER TABLE - изменение структуры таблицы;

CREATE VIEW - создания представления;

DROP VIEW - удаления представления;

CREATE INDEX - удаление индексов.

II. Операторы манипулирования данными DML.

Они включают три оператора:

DELETE - удаление одного или нескольких кортежей;

INSERT - вставка одного или нескольких кортежей;

UPDATE - обновления значения одного или нескольких атрибутов отношения.

III. Язык запросов DQL.

Он выполняет один оператор SELECT.

IV. Средства управления транзакциями.

Операторы:

COMMIT - оператор завершения транзакция и фиксирование нового состояния;

ROLLBACK - SAVEPOINT - оператор отката, возврат базы к предшествующему до начала выполнения группы операторов, обобщенных в транзакцию;

SAVEPOINT - сохранение промежуточного состояния БД, возврат к данному промежуточному состоянию.

V. Средства администрирования данными.

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

6.3. Типы данных В SQL

Используемы в SQL типы данных аналогичны применяемым в других языках программирования:

varchar(n) – символьный переменной длинны, n – максимально возможная длинна;

char(n) – строки фиксированной длиной (могут содержать буквы, цифры и специальные символы). Фиксированный размер указан в скобках. Можно записать до 255 символов;

BOOLEAN – логический тип, в отличии от логического типа в языках программирования кроме возможных значений false/true может иметь значение unknown (неопределенно), соответственно изменены таблицы истинности базовых операций (false считается наименьшим значением true наибольшим, unknown - промежуточное значение );

В SQL SERVER - bit Хранит значения 1, 0 или NULL, которое обозначает «unknown». В одном байте может храниться до 8 значений из столбцов типа BIT таблицы. В еще одном байте можно разместить дополнительные 8 значений типа BIT Столбцы типа BIT нельзя индексировать.

varingbit(n);

int;

real(float) – вещественный, возможно использование для задания типа, как имени real, так и float;

datеtime - ’0000-00-00 00:00:00′

BLOB ( в SQL SERVER - IMAGE (n) ) - Хранит двоичное значение переменной длины до 2 147 483 647 байт. Этот тип данных часто используется для хранения графики, звука и файлов, таких, как документы MS Word и электронные таблицы MS Excel. Значениями типа IMAGE нельзя свободно манипулировать. Столбцы типа IMAGE и TEXT имеют множество ограничений на способы использования. См. описание типа TEXT, где приведен список команд и функций, которые применимы и к типу IMAGE.

SQLVARIANT (тип данных определен в SQL SERVER, в SQL2003: отсутствует) . Хранит значения, относящиеся к другим поддерживаемым SQL Server типам данных, за исключением типов TEXT, NTEXT, ROWVERSION и других значений типа SQL VARIANT. Может хранить до 8016 байт данных, поддерживаются значения NULL и DEFAULT. Тип SQL VARIANT используется в столбцах, параметрах, переменных и возвращаемых функциями и хранимыми процедур, ми значениях.

6.4. Значение NULL и его применение.

Атрибутам отношения или переменным SQL допускает присвоение специального значения NULL.

Значение NULL имеет следующий смысл:

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

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

• значение умалчивается, то есть, если значение атрибута, выдается по запросу, но данный атрибут запрещен для просмотра для данного источника запроса.

Если атрибут или выражение со значением NULL участвует в арифметической операции, то результат операции будет иметь значение NULL.

При сравнении выражения, имеющего значение NULL с другим выражением с помощью операций сравнения (=, !=, <>, <, >, >=, <=, !> ,!<) результат будет иметь значение unknown.

Для проверки выражения на значение NULL операция сравнения не используется. Для Этого необходимо использовать специальный предикат IS NULL (IS NOT NULL), он будет рассмотрен ниже.

6.5. Использование условий в операторах SQL

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

WHERE УсловиеПоиска ,

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

Сравнения включает выражения, константы или переменные, соединенные операциями сравнения (=, !=, <>, <, >, >=, <=, !> ,!<) . Результат операции сравнения имеет тип Logical.

Предикаты, это утверждение состоящее из ключевых слов и выражений, результат которого так же может иметь значение True/False/Unknown.

Используются следующие типы предикатов.

Предикат диапазона

between A and B – между A и B;

Предикат вхождения в множество

IN <множество> – в множестве (NOT IN <множество> – не в множестве);

Предикат определенности значения

<атрибут> IS NULL – значение не определено (<атрибут> NOT NULL – значение атрибута не определено);

Предикат шаблона

<атрибут> LIKE <шаблон>

Шаблон - это текстовая константа, которая может содержать включает любые и специальные символы % - любое количество любых символов, _ - один любой символ.

Например, A LIKE "_СA" указывает на то, что атрибут а должен иметь значение, состоящее из трех символов, первый символ – любой, второй и третий символы – прописные "С" и "А".

В качестве предикатов могут использоваться кванторы существования (существует EXIST, не существует NOT EXIST).

Логические операции, используемые в условиях поиска, аналогичны используемым в других языках программирования ( NOT, AND, OR).

1. Что такое триггер, как объект данных?

2. Что такое транзакция, как объект данных?

3. Что позволяет обеспечить использование Форм?

4. Для чего предназначен язык SQL?

5. Особенности языка SQL?

6. За что отвечают операторы языка определения данных DDL?

7. Назовите три оператора манипулирования данными?

8. Какое значение будет иметь результат при сравнение значения NULL с другим выражением?

9. Что такое сравнения и предикаты?

Даны отношения

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

propUdst(nUdstv, idPers, kodTip )

nUdstv - int– номер удостоверения персоны

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

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

описание учебных групп

uchGr(idGR,nаmeGR)

idGR int- код группы

nаmeGR nvarchar - наименование группы

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

sostGR(idGr,idPers)

idGR int - код группы

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

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

Где -

nUdstv (int)– номер удостоверения проходившего

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

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

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

Расписание

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

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

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

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

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

audFond(idAud,nk)

1. вывести все группы ит.

2.Найти группы, которые должны находится в корпусе согласно расписанию в период начДата, конДата.

1. Вывести занятия проводимые за период НачДата КонДата в третьем корпусе у группы ит-081

2. студентов, отсутствующих в третьем корпусе за период НачДата, конДата

1. Вывести студентов группы ит-072 входивших в корпус 3 за период НачДата, КонДата

2. Вывести всех персоналий оствшихся в корпусе после даты КонДата.

Лекция 7. Операторы SQL

7.1. Операторы Языка определения данных DDL И МОДИФИКАЦИИ ОТНОШЕНИЙ.

Оператор задание схем отношений.

То есть,  создание отношений (таблиц) – и их атрибутов.

 

Общий формат

Create table ОТН (atr1 ТипАтр [DEFAULT Значение] [огрАтр] [,

                               аtr2 …]

                              ОгрКортежа),

где  atr1, atr2 … - идетификаторы (имена) атрибутов отношения;

       DEFAULT Значение – значение, присваиваемое атрибуту по умолчанию;

        огрАтр – ограничения на значение атрибута (будут рассмотрены позже ;

       ОгрКортежа - ограничения на значение корежа (будут рассмотрены позже ;

 

Удаление отношения Drop table Отн.

 

Модификация отношений.

Модификация отношения может быть следующих разновидностей:

                   удаление атрибута   

  Alter table ОТН drop атр – из отношения ОТН будет удалён атрибут Атр;

                   вставка атрибута

  Alter table ОТН add Aтр ТипАтр  [DEFAULT Значение] [огрАтр]

   , где   Aтр ТипАтр  [DEFAULT Значение] [огрАтр] – описание атрибута, аналогичное используемому в операторе Create table.

 

 

Создание виртуальных таблиц ( представлений).

Create view ИмяВиртОтн as запрос ,

где ИмяВиртОтн   - имя создаваемой  виртуальной  таблицы;

  запрос   - запрос, на который создается виртуальная таблица.

.

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

 

 

Создание индекса

Общий формат -

Create index ИмяИндекса on.. ОТН (АТР1, АТР2, …)

По данному оператору для отношения ОТН будет создан индекс ИмяИндекса на атрибуты Атр1, Атр2.

Создание индексов должно зависеть от условиями использования отношения ОТН. Индексы ускоряют выполнение запросов, но замедляют модификацию БД, поэтому если отношения часто модифицируются и при этом к нему не выполняются запросы, то создание индексов для него не целесообразно, и наоборот.

7.2. ОПЕРАТОРЫ SQL МАНИПУЛИПРВАНИЯ ДАННЫМИ.               

.               

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

     Вставка кортежей (INSERT).

insert into ИмяОтн(ИмяАтр1, ИмяАтр2, ..) values(знач1, знач2,…)

В результате выполнения данной команды в отношение с именем ИмяОтн будет вставлен кортеж, при этом атрибутам с именами ИмяАтр1, ИмяАтр2… будут присвоены значения знач1, знач2,… Атрибутам, не перечисленным в списке, будет присвоено значение по умолчанию. Если значения по умолчанию не заданы, то система попытается присвоить им значения NULL.

                   Удаление кортежей

Delete from отн where усл – из отношения ОТН будут удалены кортежи, значение атрибутов которых будет соответствовать условию.

                   Модификация (обновление) кортежей.

Update ОТН set atr1=V1, atr2=V2 where усл

Кортежам, отвечающим заданным условиям будет изменено значение заданных атрибутов.

Изменение атрибутов будет отменено, если они противоречат условиям целостности  базы данных, или другим ограничениям.

 

Лекция №8 Оператор выбора  SELECT. Соединение отношений в запросе. Ограничения и триггеры.

8.1. Оператор выбора  SELECT

Общий формат

SELECT [ALL/DISTINCT] <список полей> FROM <таблица>

[WHERE < предикат - условие выбора или соединения>]

[GROUP BY <список полей группирования>]

[HAVING <предикат-условие для группирования>]

[ORDER BY <список полей упорядочивания>]

ALL -выводить все выбранные кортежи, даже в том случае, если выбранные кортежи дублируются (значение по умолчанию)

DISTINCT – выводить только не повторяющиеся кортежи.

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

<таблица> - имена таблиц, из которых производится выбор. Можно задать несколько таблиц.

<предикат - условие выбора> - обычно задаётся в виде операций отношения, однако может быть использование задание диапазона, или квантора.

8.2. Объединение, пересечение и разность запросов

Результаты выполнения отдельных операторов SELECT можно соединять с помощью объединения union, пресечения INTERSECT и EXCEPT.

Например (select…) union (select…)

Результата этого запроса будет объединение исходных подзапросов.

Подзапрос – запрос, являющийся частью другого запроса. Подзапрос может использоваться в разделе FROM или в разделе WHERE.

Например

SELECT * From отн1 where atr1 in(select atr2 from …)

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

 

Соединение отношений в запросе.

 

Запросы могут выполняться над отношениями, соединёнными с помощью оператора JOIN. Различают следующие типы соединений:

                   декартово произведение CROSS JOIN

отн1 cross join отн2,

                   соединение по условию JOIN ON

   отн1 join отн2 on <условие>

        Например,

select atr2,art10 From отн1 join отн2 on atr2 = atr10 where atr1>10,

где atr1 и atr2 – атрибуты отношения ОТН1, atr10 – атрибут отношения отн2

                   Естественное соединение NATURAL JOIN (в Transact SQL, используемом MS SQL SERVER, INNER JOIN).

отн1 natural join отн2

При естественном соединении кортежам исходного отношения соединяются по условию равенства одноимённых атрибутов. В результирующие отношение попадает только один атрибут.

 

                   Внешнее соединение  OUTER JOIN.

отн1 natural full OUTER join отн2

Внешнее соединение отличается от естественного соединения тем, что в естественное соединение включают и те кортежи, для которых не находится пары. При этом отсутствующие атрибуты принимают значение NULL.

Существуют разновидности внешнего соединения:

Полное внешние соединение full OUTER JOIN (см. выше)

Левое внешние соединение LEFT OUTER JOIN

Правое внешние соединение RIGHT OUTER JOIN

В левое внешние соединение попадают кортежи, определяемые левым отношением. Отсутствующие кортежи правого отношения дополняются значениями NULL. В правое – наоборот.

В правое – наоборот.

Общий формат соединенных отношений может выглядеть следующим образом

[NATURAL|{{LEFT|RIGHT|FULL}[OUTER]}] JOIN on <условие соединения>