Теория баз данных: Методические указания

Шмыгалева Т.А.
Методические указания по курсу «Теория баз данных»
Алматы, 2018
Содержание.
Лабораторное занятие №1.
Лабораторное занятие №2.
Лабораторное занятие №3.
Лабораторное занятие №4.
Лабораторное занятие №5.
Лабораторное занятие №6.
Лабораторное занятие №7.
Лабораторное занятие №8.
Лабораторное занятие №9.
Лабораторное занятие №10.
Лабораторное занятие №11.
Лабораторное занятие №12.
Лабораторное занятие №13.
Лабораторное занятие №14.
Лабораторное занятие №15.
Лабораторное занятие №1. Создание базы данных в среде Microsoft SQL
Server в автоматическом режиме. Создание таблиц, создание структуры
таблиц в автоматическом режиме. Заполнение данными. Удаление
таблиц, базы данных. Создание диаграмм в среде Microsoft SQL Server
Для входа в Microsoft SQL Server нужно щелкнуть по ярлыку программы
на рабочем столе, либо выбрать программу из Пуск-Программы. После
запуска откроется окно подключения. Для подключения с правами
администратора возле надписи Authentication нужно выбрать Windows
Authentication, возле надписи Server Name выбрать имя сервера, далее нажать
кнопку Connect. Для подключения к серверу с правами пользователя нужно
выбрать возле надписи Authentication – SQL Server Authentication, далее
ввести логин и пароль в полях Login и Password. После подключения к базе
данных можно создать базу данных. Для этого нужно нажать правой кнопкой
мыши на папке Databases и выбрать команду New Database, в поле Database
Name ввести имя базы данных, во второй части окна в поле Path можно
задать другой путь, а можно оставить путь по умолчанию, далее нажать
кнопку Ok. Далее нужно нажать + возле папки Databases и в списке увидеть
созданную базу данных. База данных состоит из различных объектов, таких
как, например, таблицы, представления, хранимые процедуры, диаграммы и
т.д. Для начала создадим таблицы во вновь созданной базе данных. Для этого
нужно нажать + возле имени базы данных и правой кнопкой мыши щелкнуть
на папке Tables, выбрать команду New Table и заполнить таблицу нужными
полями: в поле Column Name вводить имя поля, в поле Data Type выбрать тип
данных для значений данного поля из предложенного списка, в поле Null
поставить галочку, если значение в данное поле может быть не введено.
Если поле является ключевым, на панели инструментов нужно щелкнуть
значок Set Primary Key. После заполнения всех полей (после создания
структуры данных) нужно закрыть окно, нажав крестик (кнопка Close),
выйдет окно с надписью Save changes to the following items, нужно нажать
кнопку Yes, далее откроется окно, в котором нужно дать имя таблицы и
нажать кнопку OK. Если открыть папку Tables, то можно увидеть созданную
таблицу. Нажав + возле имени созданной таблицы, раскрыв папку Columns,
увидим структуру созданной таблицы. Аналогично создаются другие
таблицы для базы данных. Чтобы связать главную и дополнительную
таблицы по ключам (первичному в главной и вторичному в дополнительной)
нужно выполнить следующие действия: нужно открыть дополнительную
таблицу в режиме структуры командой Design (при нажатии правой кнопкой
мыши на имени таблицы), на панели инструментов нажать кнопку
Relationships, в открывшемся окне нажать кнопку Add, в поле Tables and
Columns Specification нажать на …, в поле Primary key table нужно выбрать
имя главной таблицы, в поле ниже выбрать имя соответствующего
первичного ключа, под именем дополнительной таблицы выбрать имя
вторичного ключа и нажать кнопку Ok и затем Close. Далее таблицу нужно
закрыть, затем можно просмотреть структуру этой таблицы, нажав + на папке
Columns, увидим значок вторичного ключа возле нужного имени поля.
Также можно установить связи между другими главными и
дополнительными таблицами. Установив связи между всеми таблицами,
можно посмотреть графически связь между таблицами, для этого нужно
создать диаграмму, для этого нужно при нажатии правой кнопкой мыши на
папке Database Diagrams выбрать команду New database diagram, в
появившемся окне нажать кнопку Yes, в новом окне выбрать нужные
таблицы, выделяя их по очереди и нажимая кнопку Add. После добавления
всех таблиц нужно нажать кнопку Close. В следующем окне появится готовая
диаграмма, которую можно сохранить. Для заполнения таблицы данными
нужно щелкнуть правой кнопкой мыши по имени таблицы и выбрать
команду, далее ввести значения в поля, после заполнения первой записи
нужно нажать клавишу Enter и продолжать заполнение других записей. Если
ключевое поле является счетчиком, то данные по нему не нужно заполнять,
они вставляются автоматически. После заполнения всех полей значениями,
нужно закрыть таблицу, данные сохраняются автоматически. Для удаления
базы данных нужно сначала удалить таблицы, содержащиеся в ней. Если
установлены связи между таблицами, сначала нужно удалить
дополнительные таблицы, а затем уже главные связанные с
дополнительными. Для удаления таблицы нужно нажать правой клавишей
мыши на имени нужной таблицы и выбрать команду Delete, аналогично
можно удалить базу данных. Другие объекты баз данных рассмотрим в
следующих лабораторных работах.
Задание 1. Создать базу данных sclad и таблицы tovar, otdel, uchet со
следующей структурой:
tovar
idtov - код товара (счетчик)
nametov - название товара
kol - количество товара
otdel
idotd - код отдела
nameotd – название отдела
uchet
idtov – код товара
idotd – код отдела
datav – дата взятия товара
kolvo количество взятого товара
Лабораторное занятие №2. Создание базы данных в среде Microsoft SQL
Server. Создание таблиц, создание структуры таблиц. Заполнение
данными. Удаление таблиц
Для создания базы данных с помощью Transact-SQL используется
команда CREATE DATABASE.
CREATE DATABASE sclad
ON PRIMARY
(NAME = sclad_data, FILENAME='C:\Program Files\Microsoft SQL
Server\MSSQL\Data\sclad_data.mdf', size = 4, maxsize =25, filegrowth = 1 mb)
LOG ON
(NAME = sclad_log, FILENAME='C:\Program Files\Microsoft SQL
Server\MSSQL\Data\sclad_log.ldf', size = 4, maxsize = 20, filegrowth =1 mb)
Размещение базы и журнала транзакций - 'С:\…’ - может меняться в
зависимости от версии SQL и размещения Program Files.
Здесь:
sclad - Имя создаваемой базы данных.
ON - определяет список файлов на диске, в которых будет храниться
информация базы данных.
PRIMARY - определяет файл, содержащий логическое начало базы
данных и системных таблиц. В базе данных может быть только один
первичный (PRIMARY) файл. Если этот параметр пропущен, то первичным
считается первый файл в списке.
LOG ON - определяет список файлов на диске, в которых будет
храниться журнал транзакций. Если этот параметр не определен, то размер
журнала транзакций будет составлять 25% от общего размера файлов
данных.
sclad_data – определяет логическое имя, которое SQL Server будет
использовать для обращения к файлу.
FILENAME – задает параметры файла операционной системы (имя
файла, который должен находиться на сервере, где установлен SQL Server).
size, maxsize, filegrowth - первоначальный, максимальный размеры базы
данных и приращение для увеличения размера базы данных.
Создание и удаление таблиц
Таблицу можно создать с помощью оператора CREATE TABLE языка
Transact-SQL. Главную роль в создании таблицы играет определение типов
данных для столбцов таблицы. Для столбца можно определить не только тип
данных, но и еще одну дополнительную характеристику NULL или NOT
NULL. Если для столбца определен атрибут NULL, это позволяет опустить
при вводе данных значения данного столбца. Если же, наоборот, для столбца
определен атрибут NOT NULL, SQL Server не позволит оставить данный
столбец пустым во время вставки строки. Таким образом, атрибуты NULL и
NOT NULL представляют собой нечто вроде проверки допустимости данных
(это не пробел и не нуль). Элемент NULL для столбца интерпретируется как
неопределенный или отсутствующий, так как при вставке строки в столбец
не было введено явное или неявное его значение.
Синтаксис команды CREATE TABLE имеет следующий вид:
CREATE TABLE <table name >
( <column namel > <data type>,
<column name2 > <data type>)
В качестве примера можно спроектировать таблицы базы данных Sclad.
Предположим, что она нужна для проведения учета товаров на складе.
Запрос на создание таблицы приведен ниже:
CREATE TABLE tovar
(idtov int identity primary key,
nametov char (20),
kol int )
CREATE TABLE otdel
(idotd int identity primary key,
nameotd char (20) )
CREATE TABLE uchet
(idtov int foreign key references tovar(idtov),
idotd int foreign key references otdel(idotd),
datav datetime,
kolvo int )
Если необходимо добавить ключевое поле, которое является счетчиком,
то нужно добавить ключевое слово identity. Например, создание такого поля
будет выглядеть так:
idtov
int identity not null PRIMARY KEY
Для удаления таблицы можно воспользоваться командой DROP TABLE.
Ее синтаксис чрезвычайно прост:
DROP TABLE <table name>.
Пример 1.
Создание таблицы reader базы данных bibl с первичным ключом.
Create table reader
(nomer int constraint pk_nomer primary key,
family char(15),
name_r char(10))
Создание таблицы book базы данных bibl с первичным ключом.
Create table book
(shifr char(10) constraint pk_shifr primary key,
avtor char(15),
nazvanie char(15))
Создание третьей таблицы с вторичными ключами.
Create table uchet
(nomer int constraint VK_nomer foreign key references reader (nomer),
shifr char(10) constraint VK_Shifr foreign key references book (shifr),
data_v datetime)
Добавление данных, оператор Insert
Все записи в таблицы добавляются с использованием команды
INSERT. В самой простой форме SQL-запрос INSERT использует
следующий синтаксис:
INSERT INTO <table name> (<field1>, < field2>, . . .)
VALUES (<valuel>, <value2> ,. . .);
Если данные добавляются во все поля таблицы, то поля можно не
указывать. Например, если в таблицу reader, поля nomer, fam, name_r нужно
добавить данные, то можно выполнить следующий оператор:
Insert into reader
Values (5, ‘Иванов’,’Иван’)
Если ключевое поле nomer является счетчиком, то данные по нему не
надо вводить, т.е. оператор добавления данных будет выглядеть так:
Insert into reader
Values (‘Иванов’,’Иван’)
Задание 1. Создать базу данных для программного комплекса «Учет
товарооборота в магазине», заполнить таблицы данными с помощью языка
Transact-SQL.
Программный комплекс «Магазин»
База данных Shop.
Таблица Kategory:
Codekat- код категории
Namekat – название категории
Таблица tovar:
Codetov- код товара
Nametov-название товара
Codekat-код категории, к которой относится товар. К одной категории может
относиться несколько товаров.
Таблица firm:
CodeF – код фирмы
nameF – Наименование фирмы, с которой приходит товар.
Address - адрес фирмы
Tel – телефон фирмы
Manager – фамилия менеджера
Таблица prihod_rashod
Code_op –код операции (0-приход, 1-расход, 2 – брак)
Codetov – код товара
Colvo – количество приобретенного, проданного или списанного товара
(зависит от кода операции)
Data - дата приобретенного, проданного или списанного товара (зависит от
кода операции)
Price - цена приобретенного, проданного или списанного товара (зависит от
кода операции)
Summa – сумма приобретенного, проданного или списанного товара (зависит
от кода операции) cena*colvo
CodeF – код фирмы
Задание 2. Создать базу данных для торговой фирмы и соответствующие
таблицы.
tabel- табельный номер продавца
family –фамилия продавца
name-имя
father-отчество
data_p-дата поступления на работу
data_prod-дата продажи
vir-выручка
voznagr-вознаграждение
Задание 3. Создать базу данных для библиотеки bibl и соответствующие
таблицы.
Структура таблиц:
Таблица reader
nomer – номер читательского, первичный ключ.
fam – фамилия читателя
phone – телефон читателя (текстовое поле)
Таблица book:
shifr-шифр книги, первичный ключ. (текстовое поле)
namebook- название книги
avtor- автор книги
kol- количество книг на складе
Таблица uchet
nomer –номер читательского, вторичный ключ
shifr- шифр книги, вторичный ключ
datav – дата взятия книги
Лабораторное занятие 3. Изменение данных, удаление данных с
помощью языка Transact SQL. Изменение структуры данных
Модификация данных, оператор Update
Команда UPDATE используется для изменения данных в таблицах.
Запрос на модификацию данных имеет следующую структуру:
UPDATE <table name>
SET fieldl = <valuel>, field2 = <value2>, . . . WHERE условие
Непосредственно после оператора UPDATE указывается имя таблицы,
затем следует оператор SET, после которого перечисляются поля и
присваиваемые им значения. Можно рассмотреть самый простой запрос
модификации данных:
UPDATE reader SET family =’Иванов’
В ходе выполнения этого запроса поле family во всех записях получит
значение «Иванов», так как не было указано условие для выбора изменяемых
записей. Это упущение исправляется в следующем запросе:
UPDATE reader
SET reader = 'Иванов'
WHERE nomer = 7
Измененные данные можно просмотреть при помощи нового запроса:
SELECT nomer, family, name_r FROM reader
WHERE nomer = 7
Например, чтобы сделать стаж работы всех сотрудников 10 лет нужно
ввести команду:
UPDATE person SET
Staj = 10
Аналогично команде DELETE, команда UPDATE может использовать
условия для выбора записей, подлежащих изменению. Вот так можно
изменить общий стаж сотрудников:
UPDATE person SET ostaj = 25
WHERE data_p > 01/01/1982
В предложении SET можно указывать несколько столбцов, разделяя их
запятыми.
Удаление данных, оператор Delete
Оператор DELETE используется для удаления записей из таблицы.
Структура запроса выглядит следующим образом:
DELETE FROM <table name>
WHERE условие отбора записей
Структура этого запроса очень проста. Ее иллюстрирует несложный
запрос:
DELETE FROM reader
В ходе выполнения этого запроса будут удалены все записи, так как не
задано условие их отбора. Лучше выполнить другой запрос:
DELETE FROM reader
WHERE family = 'Иванов'
Будут удалены те записи, у которых поле family имеет значение Иванов.
Например, необходимо удалить те записи, поля которых не имеют
значений в других полях. Соответствующий запрос можно придумать
довольно быстро:
DELETE FROM reader
WHERE family is NULL and name_r is NULL
Если поле какой-либо записи не имеет значения, то, как правило, ему
присваивается значение NULL. Условие IS NULL используется для того,
чтобы проверить, содержит ли поле значение NULL. Если содержит, то
оператор возвращает значение True.
Чтобы удалить все содержание таблицы сотрудников вы можете ввести
команду:
DELETE FROM person
Чаще всего не рекомендуется выполнять эту команду! Обычно Вам
требуется удалять некоторые определенные строки в таблице. Чтобы
определить какие строки будут удалены, используйте условие отбора.
Например, чтобы удалить определенную запись о товаре, можно ввести:
DELETE FROM tovar
WHERE nametov = 'Монитор'.
Для изменения структуры существующей таблицы можно использовать
оператор ALTER TABLE. Применяя его, можно добавить или удалить поле
или серверное ограничение. Существует четыре разновидности оператора
ALTER TABLE.
Первая разновидность этого оператора используется для добавления
колонки к таблице, и ее синтаксис имеет вид:
ALTER TABLE table ADD [COLUMN] column datatype
[(size)]
[CONSTRAINT sinlge-column-constraint]
В запросах такого вида определяется имя таблицы, имя нового поля, его
тип данных и, если нужно, размер. Помимо этого можно указать серверное
ограничение, связанное с данным полем. Например, для добавления поля
Phone к таблице Simple, можно выполнить следующий запрос:
ALTER TABLE Simple ADD Phone varchar(30)
Вторая разновидность оператора ALTER TABLE применяется для
добавления серверных ограничений к таблице, а ее синтаксис имеет вид:
ALTER TABLE table ADD CONSTRAINT constraint
Такие запросы позволяют только добавлять индексы, позволяющие
использовать соответствующие поля в качестве первичных или внешних
ключей.
Третья разновидность предложения ALTER TABLE применяется для
удаления поля из таблицы:
ALTER TABLE table DROP COLUMN column
Например:
ALTER TABLE Simple DROP COLUMN Phone
Обратите внимание на то, что для удаления проиндексированных полей
следует сначала удалить индекс. Это можно сделать с помощью четвертой
разновидности предложения ALTER TABLE:
ALTER TABLE table DROP CONSTRAINT index
Примеры.
Добавим в таблицу firma поле, где будет храниться информация о
номере телефона:
USE shop
ALTER TABLE firma
ADD phone char(10)
Удалим введенное поле из таблицы:
ALTER TABLE firma
DROP COLUMN phone
Если в таблице не были определены первичные или внешние ключи,
это также можно исправить с помощью команды ALTER TABLE.
Предположим, что в таблице Tovar не был определен первичный ключ:
ALTER TABLE tovar
ADD CONSTRAINT PK_tovar PRIMARY KEY (idtov).
Если в таблице uchet не был описан один из внешних ключей:
ALTER TABLE uchet
ADD CONSTRAINT VK_ uchet FOREIGN KEY (kodotd)
REFERENCES otdel (kodotd).
Редактирование первой таблицы. Удаление первичного ключа.
Use bibl
Alter Table reader
Drop constraint pk_nomer
Редактирование первой таблицы. Добавление первичного ключа.
Use bibl
Alter table reader
Add constraint pk_nomer primary key(nomer)
Редактирование третьей таблицы. Удаление вторичного ключа.
Use bibl
alter table uchet
drop constraint vk_nomer
Редактирование третьей таблицы. Добавление вторичного ключа.
alter table uchet
Add constraint vk_nomer foreign key (nomer) references reader(nomer)
Задание 1. В таблице otdel базы данных Sclad изменить значение поля
по конкретному условию.
Задание 2. В базе данных Shop в таблице Firma изменить значение поля
NameFirma на другое значение, во всех записях должно быть одно и то же
значение.
Задание 3. Удалить записи из промежуточной таблицы Uchet базы
данных bibl по конкретному номеру читательского билета.
Задание 4. Удалить записи из промежуточной таблицы Uchet базы
данных bibl по конкретному шифру книги.
Задание 5. Удалить все записи из промежуточной таблицы Uchet базы
данных bibl.
Задание 6. Добавить в таблицу Firma базы данных поле manager.
Задание 7. Удалить поле manager из таблицы firma.
Задание 8. Удалить первичный ключ из таблицы reader.
Задание 9. Добавить первичный ключ в таблицу reader.
Задание 10. Удалить вторичный ключ из таблицы Uchet базы данных
bibl.
Задание 11. Добавить вторичный ключ в таблицу Uchet базы данных bibl.
Лабораторное занятие 4. Привести примеры реляционной, сетевой,
иерархической, постреляционной, многомерной баз данных.
Охарактеризовать основные виды связей между таблицами в
реляционной модели данных
Лабораторное занятие 5. Составление простых запросов. Оператор
выбора Select. Сортировка данных по одному полю, по нескольким
полям, по возрастанию, убыванию. Использование операторов IN,
Between, LIKE. Агрегатные функции. Вычисляемые поля
Запросы к отдельным таблицам
Достаточно распространенной является задача получения данных из
одной или нескольких таблиц и формирования на их основе каких-либо
отчетов.
Использование выражения SELECT
Команда SELECT представляет собой выражение, инициирующее
выполнение запроса. В данном случае запрос является командой на
получение данных.
Выражение SELECT имеет строго определенный формат:
SELECT <список имен полей> FROM <таблица>
WHERE <условие отбора> ORDER by <список имен полей>
Выражение SELECT является ключевым словом, сообщающим базе
данных о том, что эта команда является запросом. Далее следует список
полей, которые будут возвращены в запросе, либо символ *, указывающий на
то, что должны быть возвращены все поля. Ключевое слово FROM должно
присутствовать в каждом запросе. После него указывается имя таблицы, к
которой будет осуществлен запрос. В выражении SELECT ключевые слова
WHERE, ORDER BY необязательны.
Выборка по условию
Выборку по условию реализует оператор WHERE. Оператор является
частью выражения SELECT и служит для задания условий отбора записей в
результирующий набор. В ходе выполнения запроса происходит проверка
всех записей на соответствие условию отбора. В предложении WHERE
можно использовать один из шести операторов отношений, определенных в
SQL. Эти операторы приведены в таблице 1.
Таблица 1.
Оператор
Описание
<
Меньше
<=
Меньше или равно
!=
Не равно
=
Равно
>
Больше
>=
Больше или равно
В качестве примера можно привести довольно простой запрос:
SELECT nametov, kol FROM tovar WHERE nametov = 'Пр'
При обработке запроса был производен отбор всех записей, поле
nametov которых имеет значение Пр.
Можно произвести выборку по совпадающим значениям полей.
Например, необходимо найти компании, в которых телефон и факс имеют
один и тот же номер. Условие запроса в этом случае будет довольно
простым:
SELECT Company, Phone, Fax FROM Customer WHERE Phone = Fax
Вычисляемые поля
Автоматическое вычисление значений полей довольно часто
применяется в самых разнообразных запросах. Пример соответствующего
запроса выглядит довольно просто:
Select fam, name_r, (GetDate()-Year_r) AS Age from Pers
создает поле Age, вычисляемое по формуле (GetDate()-Year_r),
GetDate() – текущая дата в MS SQL Server.
В данном примере производится вычисление возраста по формуле
(GetDate()-Year_r).
Оператор AS присваивает данному полю имя Age,
которое будет использовано в результирующем наборе. Запрос SELECT
может также включать в себя числовые и текстовые константы.
Операторы сравнения и логические операторы
Логические операторы позволяют задать в запросе логические условия.
Оператор AND реализует логическое «И». Оператор OR реализует
логическое «ИЛИ». Выражение с его использованием, будет считаться
истинным, если хотя бы одно из условий тоже истинно. Оператор NOT
означает логическое отрицание. Его действие сводится к тому, что он
инвертирует логическое условие, перед которым его располагают.
В качестве примера можно привести запрос, позволяющий выбрать
сотрудников, получающих заработную плату в определенном численном
промежутке. Данные будут извлекаться из таблицы Employee:
SELECT LastName, FirstName, Salary FROM Employee
WHERE Salary >= 70000 AND Salary <=100000
В результате выполнения запроса возвращаются имена сотрудников с
заработной платой от 70 до 100 тысяч включительно. В данном случае
оператор AND используется для задания диапазона выбираемых значений.
Теперь можно изменить данный запрос. Можно отыскать всех
сотрудников, поле PhoneExt которых имеет значение 22:
SELECT LastName, FirstName, Salary, PhoneExt FROM Employee
WHERE Salary >= 70000 AND Salary <= 100000 and PhoneExt = '22'
Если же потребуется отыскать сотрудников, поле PhoneExt которых
имеет значение, не равное 22, запрос будет незначительно изменен:
SELECT LastName, FirstName, Salary, PhoneExt FROM Employee
WHERE Salary >= 70000 AND Salary <= 100000 and not PhoneExt = '22'
Как видно из текста запроса, логическое условие NOT позволило
исключить ненужные номера.
Теперь можно рассмотреть пример запроса с использованием оператора
OR. Предположим, менеджеру понадобилось получить списки всех
сотрудников по фамилии Johnson, либо тех сотрудников, которые получают
заработную плату более 80000. Составить запрос будет нетрудно:
SELECT LastName, FirstName, Salary FROM Employee
WHERE LastName = 'Johnson' or Salary > 80000
Стоит обратить внимание на действие оператора OR. В набор данных
были включены записи, значение поля Salary которых превышало 80000, и те
записи, в которых поле LastName имело значение Johnson.
В SQL –можно использовать и специальные операторы сравнения,
приведенные в таблице 2.
Таблица 2.
Оператор
BETWEEN
IN
LIKE
Описание
Применяется при проверке нахождения значения внутри
заданного интервала (включая его границы)
Применяется для проверки наличия значения в списке
Применяется при проверке соответствия значения
заданной маске
Использование оператора IN
Операция IN имеет следующий синтаксис:
<поле> in (<множество>)
и отбирает записи, в которых значение указанного поля является одним из
элементов указанного множества.
Например, необходимо выбрать сотрудников с заработной платой 70000,
80000 и 100000. Запрос будет выглядеть следующим образом:
SELECT LastName, FirstName, Salary FROM Employee
where Salary = 70000 or Salary = 80000 or Salary =100000
Однако этот же запрос можно написать в более короткой и красивой
форме при помощи оператора IN:
SELECT LastName, FirstName, Salary FROM Employee
where Salary IN (70000, 80000, 100000)
В качестве аргументов оператору IN были переданы значения полей, по
которым производился отбор записей.
Оператор IN может использоваться и для поиска символьных значений.
Оператор
Select fam, Year_r from Pers where fam in( ‘Иванов’,’Петров’,’Сидоров’)
отберет записи сотрудников с заданными фамилиями, а оператор
Select fam, Year_r from Pers where Year_r in( 1980, 1990)
отберет записи сотрудников указанных годов рождения.
Использование оператора BETWEEN
Оператор BETWEEN используется для указания диапазона значений,
которые используются для установки условия отбора записей. Этот оператор
чувствителен к порядку перечисления параметров, определяющих границы
диапазона. Оператор between…and имеет синтаксис:
<поле> between <значение>and <значение>
В качестве примера можно привести простой запрос:
SELECT fam, namer,year_r FROM pers
WHERE year_r BETWEEN 1985 AND 1990
В результате выполнения запроса были выбраны записи, значения поля
year_r, которых находятся в промежутке от 1985 до 1990 включительно.
Использование оператора LIKE
Оператор LIKE используется для выбора всех записей, в которые входит
подстрока, указанная в качестве параметра. В качестве условия оператор
также принимает специальные символы. Символ подчеркивания заменяет
любой одиночный символ, а знак процента обозначает любое количество
символов. Оператор LIKE имеет синтаксис:
<поле> LIKE <последовательность символов>
Предположим, необходимо выбрать компанию, в названии которой не
хватает нескольких букв. В этом случае название можно обозначить как
S?mons?bistro. Соответствующий запрос будет использовать указанный
оператор LIKE:
SELECT CompanyName, ContactName FROM Customers
WHERE CompanyName LIKE 'S_rnons_bistro'
Можно составить запрос, в котором будет производиться поиск некоей
подстроки, входящей в запись. Предположим, что необходимо найти все
компании, в названиях которых встречается последовательность символов
«RIC». Задачу решает несложный запрос:
SELECT CompanyName, ContactName FROM Customers
WHERE CompanyName LIKE '%Ric%’
Например, оператор
Select * from Pers where fam LIKE ‘A%’ означает, что будут отобраны все
записи, фамилии которых начинаются с заглавной русской буквы А.
Операция LIKE различает строчные и прописные буквы. Следующий запрос
Select * from Pers where fam LIKE ‘Иванов%’
отберет фамилии сотрудников Иванов и Иванова, а запрос
Select * from Pers where fam LIKE ‘%ван%’
кроме перечисленных фамилий выберет например, фамилию Иванников.
Агрегатные функции
В некоторых случаях требуется в самом запросе произвести вычисление
значений полей, получить количество найденных записей, произвести поиск
максимального значения поля или выполнить иную вычислительную работу.
Функции, реализующие эти возможности, называются агрегатными.
Агрегатные функции возвращают одно значение для всего поля таблицы.
Список агрегатных функций приведен ниже:
 Оператор COUNT возвращает количество записей, удовлетворяющих
условию запроса.
 Оператор SUM суммирует значения записей поля.
 Оператор AVG вычисляет среднее значение записей поля.
 Оператор МАХ возвращает наибольшее значение данного поля.
 Оператор MIN возвращает наименьшее значение данного ноля.
Агрегатные функции используются подобно именам полей в запросе, а
настоящие имена полей передаются им как аргументы. С операторами SUM и
AVG могут использоваться только числовые поля. С операторами COUNT,
MAX и MIN могут использоваться числовые и символьные поля. В случае
применения функций МАХ и MIN к символьным полям их значения будут
транслированы в ASCII-код. Минимальному значению функции будет
соответствовать символ алфавита, находящийся ближе к его началу,
максимальному — находящийся ближе к концу. Функция COUNT
производит подсчет всех записей. Для того чтобы исключить повторы,
следует использовать оператор DISTINCT. Этот оператор располагается
перед названием поля, внутри функции COUNT.
Например, оператор
Select count(*) from Pers
подсчитает полное количество записей в таблице Pers, а оператор
Select count(*) from Pers where Dep=’Бухгалтерия’
выдаст число записей сотрудников бухгалтерии. Оператор
Select count(DISTINCT Dep) from Pers
вернет число различных подразделений, упомянутых в поле Dep таблицы
Pers. Например, оператор
Select min(Year_r), max(Year_r), avg(Year_r) from Pers
вернет минимальное, максимальное и среднее значение года рождения, а
оператор
Select min(GetDate()-Year_r), max(GetDate()-Year_r), avg(GetDate()-Year_r)
from Pers
выдаст аналогичные данные, но относящиеся к возрасту сотрудников,
GetDate() – текущая дата в MS SQL Server.
Для исключения повторов при использовании функций AVG и SUM
тоже может быть использован оператор DISTINCT.
При использовании агрегатных характеристик следует учитывать, что в
списке возвращаемых значений после ключевого слова Select могут
фигурировать или поля, или агрегатные характеристики, но не могут
фигурировать и те и другие. Это очевидно, так как оператор может
возвращать или множество значений полей, или агрегатные характеристики
по таблице. Нельзя например, написать запрос,
Select fam, max(Year_r) from Pers
в котором определяется фамилия самого молодого сотрудника.
Смешение в одном операторе полей и агрегатных характеристик
возможно, если использовать группировку записей, задаваемую ключевыми
словами Group By.
Оператор GROUP BY используется для определения полей, к которым
могут применяться агрегатные функции. В случае, если этот оператор явно
не указан, все поля, указанные в выражении SELECT, трактуются как
аргументы агрегатных функций. Поля, указанные в качестве параметров
оператора GROUP BY, становятся группирующими. Все записи
результирующего набора, имеющие одинаковые значения группирующих
полей, образуют единую группу. Далее к каждой такой группе будет
применена агрегатная функция. Фактически, оператор GROUP BY дает
возможность объединять поля и агрегатные функции в едином запросе.
Оператор HAVING, который является аналогом оператора WHERE может
работать с агрегатными функциями.
Оператор
Select Dep, Count(*) From Pers Group By Dep
вернет таблицу, в которой будет 2 столбца: столбец с названиями отделов, и
столбец, в котором будет отображено число сотрудников в каждом отделе, а
оператор
Select Dep, Count(*) From Pers Group By Dep Having Dep!= ‘Бухгалтерия’
вернет строки, относящиеся ко всем отделам, кроме бухгалтерии.
Упорядочивание записей
Оператор ORDER BY используется для упорядочивания записей
результирующего набора данных. Записи сортируются в соответствии с
порядком следования полей и их значений. Если сортировка будет
производиться по возрастанию, то следует использовать параметр ASC. Для
сортировки по убыванию используется параметр DESC.
Оператор
Select Dep, Fam, Year_r From Pers Order By Year_r
задает упорядочивание возвращаемых значений по нарастанию года
рождения, а оператор
Select Dep, Fam, Year_r From Pers Order By Year_r Desc
располагает результаты по убыванию значений.
Если в списке после ORDER BY перечисляется несколько полей, то
первое из них главное и сортировка проводится, прежде всего, по значениям
этого поля. Записи, имеющие одинаковое значение первого поля
упорядочиваются по значению второго поля. Например, оператор
Select Dep, Fam, Year_r FROM Pers ORDER By Dep, Fam
сортирует записи прежде всего по отделам, а внутри каждого отдела – по
алфавиту. Оператор
Select Dep, Fam, Year_r, Sex FROM Pers ORDER By Dep, Sex, Fam
сортирует записи по отделам, полу и алфавиту.
Задания.
Задание 1. Выбрать все записи из таблицы reader, отсортировать их по
фамилии в порядке возрастания.
Задание 2. Осуществить выборку данных из таблицы reader по условию,
по ключевым словам.
Задание 3. Написать запрос с вычисляемыми полями с использованием
псевдонимов.
Задание 4. Написать запрос с использованием агрегатных характеристик.
Задание 5. Выбрать все записи из таблицы tovar, отсортировать их по
названию в порядке возрастания, убывания.
Задание 6. Осуществить выборку данных из таблицы tovar по условию, по
нескольким условиям, по ключевым словам.
Задание 7. Написать запрос с вычисляемыми полями:
A) Вычислить количество записей в таблице uchet.
B) Вычислить максимальное, минимальное, среднее количество товара.
C) Вычислить общее количество всех товаров на складе.
Задание 8. Выдать наименования
отделов, содержащихся в заданном
множестве.
Задание 9. Выдать названия товаров, содержащихся в заданном множестве.
Задание 10. Выдать названия отделов по первой букве.
Задание 11. Добавить в таблицу отдел поле phone, sort(сотрудник).
Выполнить сортировку по 2 м полям: по сотруднику и телефону.
Задание 12. Выдать количество товара в заданном диапазоне.
Задание 13. Написать запрос с вычисляемым полем.
Лабораторное занятие 6. Оператор выбора Select. Составление
вложенных запросов
Вложенные подзапросы
Результаты, возвращаемые оператором Select, можно использовать в
другом операторе Select.
Причем это относится и к операторам,
возвращающим совокупные характеристики, и к операторам, возвращающим
множество значений.
Например, найдем фамилию самого молодого
сотрудника.
Select Fam, Year_r From Pers
Where Year_r=(Select max(Year_r) From Pers)
В этом операторе второй вложенный оператор Select max(Year_r) From
Pers возвращает максимальный год рождения, который используется в
элементе Where основного оператора Select для поиска сотрудника или
сотрудников, чей год рождения совпадает с максимальным. Найдем всех
однофамильцев двух организаций с помощью вложенного запроса.
Select * From Pers Where Fam IN (Select Fam From Pers1)
Вложенный оператор Select Fam From Pers1 возвращает множество
фамилий из таблицы Pers1, а конструкция Where основного оператора Select
отбирает из фамилий в таблице Pers те, которые имеются во множестве
фамилий из Pers1.
Вложенные запросы могут использоваться в качестве дополнительных
условий отбора записей.
Рассмотрим еще один пример. Необходимо выдать книги, взятые
выбранным читателем.
Select shifr, avtor, nazvanie from book
where shifr in (Select shifr from readbook where nomer in (Select nomer from
reader where fam=’Иванов’)
Задание 1. Написать вложенный запрос, осуществляющий выборку
товаров, взятых конкретным отделом.
Задание 2. Написать вложенный запрос, осуществляющий выборку
отделов, взявших конкретный товар.
Задание 3. Написать вложенный запрос, осуществляющий выборку
читателей, взявших конкретную книгу.
Лабораторное занятие 7. Многотабличные запросы
Многотабличные запросы
Как правило, при проектировании таблиц в них стараются включать
только те поля, которые однозначно связаны с данной сущностью. Это
делается для того, чтобы было проще модифицировать базу данных и
поддерживать ее целостность. В связи с этим возникает необходимость
создания многотабличных запросов, то есть запросов, использующих для
формирования результата данные из нескольких таблиц.
Объединение таблиц
Во многих случаях требуется получать данные из нескольких таблиц и
сводить их в одну результирующую таблицу. Такая операция называется
объединением таблиц. При объединении производится связывание полей
разных таблиц. При этом между полями устанавливаются связи за счет
использования соответствующих справочных значений. После оператора
FROM таблицы перечисляются через запятую. Полное имя поля фактически
состоит из имени таблицы и самого поля, разделенного точкой. Если все
столбцы объединяемых таблиц имеют разные имена, то к ним можно
обращаться напрямую, не указывая имя таблицы, которой они принадлежат.
Пусть, например, мы хотим получить список сотрудников всех
производственных подразделений. В таблице Person мы имеем список
сотрудников с указанием в поле Dep подразделений, в которых они
работают. А в таблице Dep мы имеем список всех подразделений в поле Dep
и характеристику каждого подразделения в поле Proisv (true, если
подразделение производственное). Тогда получить список сотрудников всех
производственных подразделений можно оператором:
Select Person.* From Person, Dep
WHERE (Person.Dep=Dep.Dep) AND (Dep.Proisv=true)
Перед полем Proisv ссылку на таблицу можно опустить, так как оно
используется только в одной таблице. В операторах, работающих с
несколькими таблицами, обычно каждой таблице дается псевдоним,
сокращающий ссылки на таблицы, а иногда придающий им некоторый
смысл. Псевдоним таблицы может записываться в списке таблиц после слова
FROM, отделяясь от имени таблицы пробелом. Приведенный выше оператор
может быть переписан следующим образом:
Select P.* From Person P, Dep D
WHERE (P.Dep=D.Dep) AND (D.Proisv=true)
В этом примере таблице Person дан псевдоним P, а таблице Dep – D.
Возможно самообъединение таблицы. В этом случае одной таблице
даются два псевдонима. Пусть, например, мы хотим найти всех ровесников в
организации. Это можно сделать оператором:
Select p1.fam, p2.fam, p1.Year_r, From Person p1, Person p2
Where (p1.Year_r=p2.Year_r) AND (p1.Fam!=p2.Fam)
В этом примере для таблицы Person введено два псевдонима p1, p2. В
конструкции Where мы ищем в этих якобы разных таблицах записи с
одинаковым годом рождения.
Второе условие p1.fam!=p2.fam нужно, чтобы сотрудник не отображался
как ровесник сам себя. Правда, приведенный оператор выдает в результате
по две записи на каждую пару ровесников, сначала, например, «Николаев –
Иванов», а потом «Иванов – Николаев». Чтобы исключить такое
дублирование можно добавить еще одно условие:
Select p1.fam, p2.fam, p1.Year_r, From Person p1, Person p2
Where
(p1.Year_r=p2.Year_r)
AND
(p1.Fam!=p2.Fam)
AND
(p1.Fam<p2.Fam)
Дополнительное условие упорядочивает появление фамилий в p1 и p2 и
исключает дублирование результатов. При помощи этого механизма можно
объединять более двух таблиц, указывая связующие поля и условия отбора
записей.
Рассмотрим пример. Отобразить отделы, взявшие выбранный товар,
дату и количество товара.
Select p1.otdel, p2.data_v, p2.kolvo from otdel p1, uchet p2, Tovar p3 where
(p1.kodotd=p2.kodotd) and (p2.kodtov=p3.kodtov) and (p2.nametov=’Монитор’)
Задание 1. Написать запрос, связанный с объединением данных из
нескольких таблиц: отобразить товары, взятые выбранным отделом, дату
взятия товара, количество взятого товара.
Задание 2. Написать запрос, связанный с объединением данных из
нескольких таблиц: отобразить отделы, взявшие конкретный товар, дату
взятия товара, количество взятого товара.
Задание 3. Отобразить товары относящиеся к категориям.
Лабораторное занятие 8. Выявить зависимости между атрибутами на
примере конкретной базы данных
Зависимости между атрибутами
Рассмотрим основные виды зависимостей между атрибутами
отношений: функциональные, транзитивные и многозначные.
Понятие функциональной зависимости является базовым, так как на его
основе формулируются определения всех остальных видов зависимостей.
Атрибут В функционально зависит от атрибута А, если каждому
значению А соответствует в точности одно значение В. Это означает, что во
всех кортежах с одинаковым значением атрибута А атрибут В будет иметь
также одно и то же значение. Отметим, что А и В могут быть составными состоять из двух и более атрибутов. Рассмотрим виды зависимостей на
примере отношения ПРЕПОДАВАТЕЛЬ.
ФИО
Иванов
И.М.
Иванов
Должн
преп
Оклад Стаж Д_Стаж Каф Предм Группа ВидЗан
100000 5
2000
25
СУБД 256
Практ
преп
100000 5
2000
25
С++
123
Практ
И.М.
Петров
М.И.
Петров
М.И.
Сидоров
Н.Г.
Сидоров
Н.Г.
Егоров
В.В.
ст.преп 120000 7
5000
25
СУБД
256
Лекция
ст.преп 120000 7
5000
25
С#
256
Практ
преп
100000 10
7000
25
C++
123
Лекция
преп
100000 10
7000
25
C#
256
Лекция
преп
100000 5
2000
24
ИКТ
244
Лекция
Рисунок 1. Отношение ПРЕПОДАВАТЕЛЬ
В отношении на рис. 1 можно выделить функциональные зависимости
между атрибутами ФИО→ Каф, ФИО→ Должн, Должн→Оклад и другие.
Наличие функциональной зависимости в отношении определяется природой
вещей, информация о которых представлена кортежами отношения. В
отношении на рис. 1 ключ является составным и состоит из атрибутов ФИО,
Предмет, Группа.
Функциональная
взаимозависимость.
Если
существует
функциональная зависимость вида А→В и В→А, то между А и В имеется
взаимно однозначное соответствие, или функциональная взаимозависимость.
Наличие функциональной взаимозависимости между атрибутами А и В
обозначим как А→В или В→А.
Пример 1. Пусть имеется некоторое отношение, включающее два
атрибута, функционально зависящие друг от друга. Это серия и номер
паспорта (N) и фамилия, имя и отчество владельца (ФИО). Наличие
функциональной зависимости поля ФИО от N означает не только тот факт,
что значение поля N однозначно определяет значение поля ФИО, но и то, что
одному и тому же значению поля N соответствует только единственное
значение поля ФИО. Понятно, что в данном случае действует и обратная ФЗ:
каждому значению поля ФИО соответствует только одно значение поля N. В
данном примере предполагается, что ситуация наличия полного совпадения
фамилий, имен и отчеств двух людей исключена.
Если отношение находится в 1НФ, то все неключевые атрибуты
функционально зависят от ключа с различной степенью зависимости.
Частичной
зависимостью
(частичной
функциональной
зависимостью) называется зависимость неключевого атрибута от части
составного ключа. В рассматриваемом отношении атрибут Должн находится
в функциональной зависимости от атрибута ФИО, являющегося частью
ключа. Тем самым атрибут Должн находится в частичной зависимости от
ключа отношения.
Альтернативным вариантом является полная функциональная
зависимость неключевого атрибута от всего составного ключа. В нашем
примере атрибут ВидЗан находится в полной функциональной зависимости
от составного ключа.
Атрибут С зависит от атрибута А транзитивно (существует
транзитивная зависимость), если для атрибутов А, В, С выполняются
условия А→В и В→С, но обратная зависимость отсутствует. В отношении на
рис. 1.4. транзитивной зависимостью связаны атрибуты:
ФИО→Должн→Оклад
Между атрибутами может иметь место многозначная зависимость.
В отношении R атрибут В многозначно зависит от атрибута А, если
каждому значению А соответствует множество значений В, не связанных с
другими атрибутами из R.
Многозначные зависимости могут быть «один ко многим» (1:М),
«многие к одному» (М:1) или «многие ко многим» (М:М), обозначаемые
соответственно: А  В, А  В и А  В.
Например, пусть преподаватель ведет несколько предметов, а каждый
предмет может вестись несколькими преподавателями, тогда имеет место
зависимость ФИО  Предмет. Так, из таблицы приведенной на рис. 1.4.,
видно, что преподаватель Иванов И.М. ведет занятия по двум предметам, а
дисциплина СУБД - читается двумя преподавателями: Ивановым И.М. и
Петровым М.И.
Замечание. В общем случае между двумя атрибутами одного
отношения могут существовать зависимости: 1:1, 1:М, М:1 и М:М.
Поскольку зависимость между атрибутами является причиной аномалий,
стараются расчленить отношения с зависимостями атрибутов на несколько
отношений. В результате образуется совокупность связанных отношений
(таблиц) со связями вида 1:1, 1:М, М:1 и М:М. Связи между таблицами
отражают зависимости между атрибутами различных отношений.
Взаимно независимые атрибуты. Два или более атрибута называются
взаимно независимыми, если ни один из этих атрибутов не является
функционально зависимым от других атрибутов.
В случае двух атрибутов отсутствие зависимости атрибута А от
атрибута В можно обозначить так: А  →В. Случай, когда А  →В и В  →А,
можно обозначить А  =В.
Выявление зависимостей между атрибутами
Выявление зависимостей между атрибутами необходимо для
выполнения
проектирования
БД
методом
нормальных
форм,
рассматриваемого далее.
Основной способ определения наличия функциональных зависимостей
— внимательный анализ семантики атрибутов. Для каждого отношения
существует, но не всегда, определенное множество функциональных
зависимостей между атрибутами. Причем если в некотором отношении
существует одна или несколько функциональных зависимостей, можно
вывести другие функциональные зависимости, существующие в этом
отношении.
Пример. Пусть задано отношение R со схемой R(A1, A2, A3) и
числовыми значениями, приведенными в следующей таблице:
А1
12
17
11
13
15
14
А2
21
21
24
25
23
22
A3
34
34
33
31
35
32
Априори известно, что в R существуют функциональные зависимости:
А1→А2 и А2→АЗ.
Анализируя это отношение, можно увидеть, что в нем существуют еще
зависимости:
A1→A3, А1А2→ A3, А1А2АЗ→А1А2,
А1А2→А2АЗ и т. п.
В то же время в отношении нет других функциональных зависимостей,
что во введенных нами обозначениях можно отразить следующим образом:
А2  →А1, А3  →А1 и т.д.
Отсутствие зависимости А1 от А2 (A2  →Al) объясняется тем, что
одному и тому же значению атрибута А2 (21) соответствуют разные значения
атрибута А1 (12 и 17). Другими словами, имеет место многозначность, а не
функциональность.
Перечислив все существующие функциональные зависимости в
отношении R, получим полное множество функциональных зависимостей,
которое обозначим F+.
Таким образом, для последнего примера исходное множество
F = (А1→А2, А2→A3), а полное множество F+ = (А1→А2, А2→A3, А1→A3,
А1А2→A3, А1А2АЗ→А1А2, А1А2→А2АЗ, ...).
Для построения F+ из F необходимо знать ряд правил (или аксиом)
вывода одних функциональных зависимостей из других.
Существует 8 основных аксиом вывода: рефлексивности, пополнения,
транзитивности,
расширения,
продолжения,
псевдотранзитивности,
объединения и декомпозиции. Перечисленные аксиомы обеспечивают
получение всех ФЗ, т. е. их совокупность применительно к процедуре вывода
можно считать «функционально полной».
Выявим
зависимости
между
атрибутами
отношения
ПРЕПОДАВАТЕЛЬ, приведенного на рис. 1.4. При этом учтем следующее
условие, которое выполняется в данном отношении: один преподаватель в
одной группе может проводить один вид занятий (лекции или практические
занятия).
В результате анализа отношения получаем зависимости между
атрибутами, показанные на рис. 1.5.
а)
б)
Рис. 1.5. Зависимости между атрибутами
К выделению этих ФЗ для рассматриваемого примера приводят
следующие соображения.
Фамилия, имя и отчество у преподавателей факультета уникальны.
Каждому преподавателю однозначно соответствует его стаж, т. е. имеет
место функциональная зависимость ФИО→Стаж. Обратное утверждение
неверно, так как одинаковый стаж может быть у разных преподавателей.
Каждый преподаватель имеет определенную добавку за стаж, т. е.
имеет место функциональная зависимость ФИО→Д_Стаж, но обратная
функциональная зависимость отсутствует, так как одну и ту же надбавку
могут иметь несколько преподавателей.
Каждый преподаватель имеет определенную должность (преп.,
ст.преп., доцент, профессор), но одну и ту же должность могут иметь
несколько преподавателей, т. е. имеет место функциональная зависимость
ФИО→Должн, а обратная функциональная зависимость отсутствует.
Каждый преподаватель является сотрудником одной и только одной
кафедры. Поэтому функциональная зависимость ФИО→Каф имеет место. С
другой стороны, на каждой кафедре много преподавателей, поэтому
обратной функциональной зависимости нет.
Каждому преподавателю соответствует конкретный оклад, который
одинаков для всех педагогов с одинаковыми должностями, что учитывается
зависимостями ФИО→Оклад и Должн→Оклад. Нет одинаковых окладов для
разных должностей, поэтому имеет место функциональная зависимость
Оклад→Должн.
Один и тот же преподаватель в одной группе по разным предметам
может проводить разные виды занятий. Определение вида занятий, которые
проводит преподаватель, невозможно без указания предмета и группы,
поэтому имеет место функциональная зависимость ФИО, Предм,
Группа→ВидЗан. Действительно, Петров М.И. в 256 группе читает лекции и
проводит практические занятия. Но лекции он читает по СУБД, а практику
проводит по Паскалю.
Не были выделены зависимости между атрибутами ФИО, Предм и
Группа, поскольку они образуют составной ключ и не учитываются в
процессе нормализации исходного отношения.
После того, как выделены все функциональные зависимости, следует
проверить их согласованность с данными исходного отношения
ПРЕПОДАВАТЕЛЬ (рис. 1.4).
Например, Должн='преп' и Оклад=500 всегда соответствуют друг другу
во всех кортежах, т. е. подтверждается функциональная зависимость
Должн↔Оклад. Так же следует верифицировать и остальные
функциональные зависимости, не забывая об ограниченности имеющихся в
отношении данных.
Лабораторное занятие 9. Спроектировать базу данных с помощью метода
нормальных форм. Привести примеры.
Нормальные формы
Процесс Проектирования БД с использованием метода нормальных
форм является итерационным и заключается в последовательном переводе
отношений из первой нормальной формы в нормальные формы более
высокого порядка по определенным правилам. Каждая следующая
нормальная форма ограничивает определенный тип функциональных
зависимостей, устраняет соответствующие аномалии при выполнении
операций над отношениями БД и сохраняет свойства предшествующих
нормальных форм.
Выделяют следующую последовательность нормальных форм:
 первая нормальная форма (1НФ);
 вторая нормальная форма (2НФ);
 третья нормальная форма (ЗНФ);
 усиленная третья нормальная форма, или нормальная форма БойсаКодда (БКНФ);
 четвертая нормальная форма (4НФ);
 пятая нормальная форма (5НФ).
Первая нормальная форма. Отношение находится в 1НФ, если все его
атрибуты являются простыми (имеют единственное значение). Исходное
отношение строится таким образом, чтобы оно было в 1НФ.
Перевод отношения в следующую нормальную форму осуществляется
методом «декомпозиции без потерь». Такая декомпозиция должна
обеспечить то, что запросы (выборка данных по условию) к исходному
отношению и к отношениям, получаемым в результате декомпозиции, дадут
одинаковый результат.
Основной операцией метода является операция проекции. Поясним ее
на примере. Предположим, что в отношении R(A,B,C,D,E,...) устранение
функциональной зависимости C→D позволит перевести его в следующую
нормальную форму. Для решения этой задачи выполним декомпозицию
отношения R на два новых отношения R1(A,B,C,E,...) и R2(C,D). Отношение
R2 является проекцией отношения R на атрибуты С и D.
Исходное отношение ПРЕПОДАВАТЕЛЬ, используемое для иллюстрации
метода, имеет составной ключ ФИО, Предм, Группа и находится в 1НФ,
поскольку все его атрибуты простые.
В этом отношении в соответствии с рис. 1. б можно выделить частичную
зависимость атрибутов Стаж, Д_Стаж, Каф, Должн, Оклад от ключа указанные атрибуты находятся в функциональной зависимости от атрибута
ФИО, являющегося частью составного ключа.
Эта частичная зависимость от ключа приводит к следующему:
1.
В отношении присутствует явное и неявное избыточное
дублирование данных, например:
 повторение сведений о стаже, должности и окладе преподавателей,
проводящих занятия в нескольких группах и/или по разным предметам;
 повторение сведений об окладах для одной и той же должности или о
надбавках за одинаковый стаж.
2.
Следствием избыточного дублирования данных является
проблема их редактирования. Например, изменение должности у
преподавателя Иванова И.М. потребует просмотра всех кортежей отношения
и внесения изменений в те из них, которые содержат сведения о данном
преподавателе.
Часть избыточности устраняется при переводе отношения в 2НФ.
Вторая нормальная форма. Отношение находится в 2НФ, если оно
находится в 1НФ и каждый неключевой атрибут функционально полно
зависит от первичного ключа (составного).
Для устранения частичной зависимости и перевода отношения в 2НФ
необходимо, используя операцию проекции, разложить его на несколько
отношений следующим образом:
 построить проекцию без атрибутов, находящихся в частичной
функциональной зависимости от первичного ключа;
 построить проекции на части составного первичного ключа и
атрибуты, зависящие от этих частей.
В результате получим два отношения R1 и R2 в 2НФ (рис. 1.б).
а)
б)
R1
ФИО
Предм Группа ВидЗан
Иванов И.М. СУБД
256
Практ
Иванов И.М. С++
123
Практ
Петров М.И.
Петров М.И.
Сидоров Н.Г.
Сидоров Н.Г.
Егоров В.В
R2
ФИО
Иванов И.М.
Петров М.И.
Сидоров Н.Г
Егоров В.В
СУБД
С++
ИТ
С++
ИТ
256
256
123
256
244
Долж
преп
ст.преп
преп
преп
Лекция
Практ
Лекция
Лекция
Лекция
Оклад
70000
80000
70000
70000
Стаж
5
7
10
5
ФИО
ВидЗан
Предм
Группа
Д_Стаж
2000
3000
5000
2000
Каф
25
25
25
24
Рис. 1.б. Отношения БД в 2НФ
В отношении R1 первичный ключ является составным и состоит из
атрибутов ФИО, Предм, Группа. Напомним, что данный ключ в отношении
R1 получен в предположении, что каждый преподаватель в одной группе по
одному предмету может либо читать лекции, либо проводить практические
занятия. В отношении R2 ключ ФИО.
Исследование отношений R1 и R2 показывает, что переход к 2НФ
позволил исключить явную избыточность данных в таблице R2 - повторение
строк со сведениями о преподавателях. В R2 по-прежнему имеет место
неявное дублирование данных.
Для дальнейшего совершенствования отношения необходимо
преобразовать его в ЗНФ.
Третья нормальная форма.
Определение 1. Отношение находится в ЗНФ, если оно находится в
2НФ и каждый неключевой атрибут нетранзитивно зависит от первичного
ключа.
Существует и альтернативное определение.
Определение 2. Отношение находится в ЗНФ в том и только в том
случае, если все неключевые атрибуты отношения взаимно независимы и
полностью зависят от первичного ключа.
Доказать справедливость этого утверждения несложно. Действительно,
то, что не ключевые атрибуты полностью зависят от первичного ключа,
означает, что данное отношение находится в форме 2НФ. Взаимная
независимость атрибутов (определение приведено выше) означает отсутствие
всякой зависимости между атрибутами отношения, в том числе и
транзитивной зависимости между ними. Таким образом, второе определение
ЗНФ сводится к первому определению.
Если в отношении R1 транзитивные зависимости отсутствуют, то в
отношении R2 они есть:
ФИО→Должн→Оклад,
ФИО→Оклад→Должн,
ФИО→Стаж →Д_Стаж
Транзитивные
зависимости
также
порождают
избыточное
дублирование информации в отношении. Устраним их. Для этого используя
операцию проекции на атрибуты, являющиеся причиной транзитивных
зависимостей, преобразуем, отношение R2, получив при этом отношения R3,
R4 и R5, каждое из которых находится в ЗНФ (рис. 2. а). Графически эти
отношения представлены на рис. 2.б. Заметим, что отношение R2 можно
преобразовать по-другому, а именно: в отношении R3 вместо атрибута
Должн взять атрибут Оклад.
На практике построение ЗНФ схем отношений в большинстве случаев
является достаточным и приведением к ним процесс проектирования
реляционной БД заканчивается. Действительно, приведение отношений к
ЗНФ в нашем примере, привело к устранению избыточного дублирования.
а)
б)
R3
ФИО
Должн Стаж
Иванов И.М.
Преп
5
Петров М.И. Ст.преп
7
Сидоров Н.Г.
Преп
10
Егоров В.В.
преп
5
R4
Должн
преп
Ст. преп
R5
Оклад
500
800
Каф
25
25
25
24
Стаж
5
7
10
Д_Стаж
100
100
150
Рис. 2. Отношения БД в ЗНФ
Если в отношении имеется зависимость атрибутов составного ключа от
неключевых атрибутов, то необходимо перейти к усиленной ЗНФ.
Усиленная ЗНФ или нормальная форма Бойса-Кодда (БКНФ).
Отношение находится в БКНФ, если оно находится в ЗНФ и в нем
отсутствуют зависимости ключей (атрибутов составного ключа) от
неключевых атрибутов.
У нас подобной зависимости нет, поэтому процесс проектирования на
этом заканчивается. Результатом проектирования является БД, состоящая из
следующих таблиц: R1, R3, R4, R5. В полученной БД имеет место
необходимое дублирование данных, но отсутствует избыточное.
Пример 1. Спроектировать базу данных с помощью метода нормальных форм
для программного комплекса “Учет книг в библиотеке”.
Запишем исходные данные по читателям и книгам в таблицу.
R
Nomer
Family
Name
Shifr
Avtor
Nazvanie
Имеем отношение R (Nomer, Family, Name, Shifr, Avtor, Nazvanie);
Выявим зависимости между атрибутами:
Nomer→ Family
Nomer→ Name
Shifr→ Avtor
Shifr→ Nazvanie
Атрибуты Family и Name частично зависят от ключа nomer. Тогда
получаем вторую нормальную форму:
Вторая нормальная форма
В R2 имеем транзитивную зависимость:
Shifr→ Avtor→ Nazvanie,
тогда получаем 3 отношения:
Задание 1. Спроектировать базу данных с помощью метода нормальных
форм для программного комплекса «Учет материальных ценностей»
Задание 2. Пусть у нас имеется базовое отношение, содержащее
информацию о результатах экзаменационной сессии. Вариант 1 схемы базы
данных.
Сессия (№ зачетной книжки, Фамилия, Имя, Отчество, Предмет, Оценка)
В этом отношении, как видно из изображения схемы базового отношения,
задан составной первичный ключ:
Primary key (№ зачетной книжки, Предмет);
Лабораторное занятие 10,11. Проектирование баз данных с помощью
метода «Сущность-связь» на конкретных примерах.
Правила формирования отношений
Правила формирования отношений основываются на учете
следующего:
• степени связи между сущностями (1:1, 1:М, М:1, М;М);
• класса принадлежности экземпляров сущностей (обязательный и
необязательный).
Рассмотрим формулировки шести правил формирования отношений
на основе диаграмм ER-типа.
Формирование отношений для связи 1:1
Правило 1. Если степень бинарной связи 1:1 и класс принадлежности
обеих сущностей обязательный, то формируется одно отношение.
Первичным ключом этого отношения может быть ключ любой из двух
сущностей.
На рис. 1.13. приведены диаграмма ER-типа и отношение,
сформированное по правилу 1 на ее основе.
Рис. 1.13. Диаграмма и отношения для правила 1
На рисунке используются следующие обозначения:
Cl, С2 - сущности 1 и 2;
Kl, K2 - ключи первой и второй сущности соответственно;
R1 - отношение 1, сформированное на основе первой и второй
сущностей;
Kl, K2,... означает, что ключом сформированного отношения может
быть либо К1, либо К2.
Это и другие правила будем проверять, рассматривая различные
варианты связи, ПРЕПОДАВАТЕЛЬ ВЕДЕТ ДИСЦИПЛИНУ. Пусть
сущность
ПРЕПОДАВАТЕЛЬ
характеризуется
атрибутами
НП
(идентификационный номер преподавателя), ФИО (фамилия, имя и
отчество), Стаж (стаж преподавателя). Сущность ДИСЦИПЛИНА
характеризуется соответственно атрибутами КД (код дисциплины), Часы
(часы, отводимые на дисциплину). Тогда схема отношения, содержащего
информацию об обеих сущностях, и само отношение для случая, когда
степень связи равна 1:1, а КП обязательный для всех сущностей, могут иметь
вид, показанный на рис.1.14.
ПРЕПОДАВАТЕЛЬ_ДИСЦИПЛИНА (НП, ФИО, Стаж, КД, Часы)
ПРЕПОДАВАТЕЛЬ_ДИСЦИПЛИНА
НП
П1
П2
ПЗ
П4
ФИО
Стаж
Иванов 5
Петров 7
Сидоров 10
Егоров 5
КД
К1
К2
КЗ
К4
Часы
62
74
102
80
Рис. 1.14. Полученные по правилу 1 схема и отношение
Сформированное отношение содержит полную информацию о
преподавателях, дисциплинах и о том, как они связаны между собой. Так,
преподаватель Иванов ведет только дисциплину с кодом К1, а дисциплина К1
ведется только Ивановым (связь 1:1). В этом отношении отсутствуют пустые
поля (КП обязательный для всех сущностей), т. к. нет преподавателей, которые
бы что-то не вели, и нет дисциплин, которые никто не ведет. Таким образом,
одного отношения в данном случае достаточно. В качестве первичного ключа
может быть выбран ключ первого отношения НП или ключ второго отношения
КД.
Правило 2. Если степень связи 1:1 и класс принадлежности одной
сущности обязательный, а второй - необязательный, то под каждую из
сущностей формируется по отношению с первичными ключами,
являющимися ключами соответствующих сущностей. Далее к отношению,
сущность которого имеет обязательный КП, добавляется в качестве атрибута
ключ сущности с необязательным КП.
На рис. 1.15. приведены диаграмма ER-типа и отношения,
сформированные по правилу 2 на ее основе.
Рис. 1.15. Диаграмма и отношения для правила 2
Чтобы убедиться в справедливости правила, рассмотрим следующий
пример. На рис. 1.16. приведено исходное отношение, содержащее
информацию о преподавателях и дисциплинах. Оно представляет вариант, в
котором класс сущности ПРЕПОДАВАТЕЛЬ является обязательным, а
сущности ДИСЦИПЛИНА - необязательным. При этом пробелы «—»
(пустые поля) присутствуют во всех кортежах с информацией о
дисциплинах, которые не ведутся ни одним из преподавателей.
ПРЕПОДАВАТЕЛЬ_ДИСЦИПЛИНА
ФИО
Стаж
И.М.
П2 Петров
М.И.
Сидоров
П3
Н.Г
--- ---
5
7
10
---
Н
П1
П Иванов
КД
К1
К2
КЗ
К4
Часы
62
74
102
80
Рис. 1.16. Исходное отношение
Избежать этой ситуации можно, применив правило 2, в соответствии с
которым, выделяются два отношения, приведенные на рис. 1.17.
ПРЕПОДАВАТЕЛЬ (НП, Стаж, КД)
ДИСЦИПЛИНА (КД, Часы)
ПРЕПОДАВАТЕЛЬ
ДИСЦИПЛИНА
ФИО
Н
П1 Иванов И.М.
П
П2 Петров М.И.
Сидоров Н.Г
П3
П4 Егоров
Часы
Стаж
5 КД
К1
7 К2
10 КЗ
5 К4
КД
К1 62
К2 74
КЗ 102
К4 80
Рис. 1.17. Отношения, полученные по правилу 2
В результате мы избежали пустых полей в отношениях, не потеряв
данных. Добавив атрибут КД - ключ сущности ДИСЦИПЛИНА (с
необязательным КП) в качестве внешнего ключа в отношение,
соответствующее сущности ПРЕПОДАВАТЕЛЬ (с обязательным КП), мы
связали отношения (рис. 1.18.).
НП ФИО
Стаж
КД
КД
Часы
Рис. 1.18. Связь отношений по внешнему ключу
Точнее говоря, мы создали условия для связывания отношений. Это
связывание при работе с базой данных позволяет, например, получать
одновременно данные о преподавателе и о ведущихся им дисциплинах
(часах).
Правило 3. Если степень связи 1:1 и класс принадлежности обеих
сущностей является необязательным, то необходимо использовать три
отношения. Два отношения соответствуют связываемым сущностям, ключи
которых являются первичными в этих отношениях. Третье отношение
является связным между первыми двумя, поэтому его ключ объединяет
ключевые атрибуты связываемых отношений.
Рис. 1.19. Диаграмма и отношения для правила 3
На рис. 1.19. приведены диаграмма ER-типа и отношения,
сформированные по правилу 3 на ее основе.
На рис. 1.20. приведены примеры отношений, подтверждающие
необходимость использования трех отношений при наличии необязательного
КП для обеих связываемых сущностей.
а) одно отношение
ПРЕПОДАВАТЕЛЬ_ДИСЦИПЛИНА
НП ФИО
Стаж КД
Часы
П1 Иванов
5
К1
62
П2 Петров
7
----П3 Сидоров 10
К2
74
------К3
102
б) два отношения
НП
П1
П2
П3
ПРЕПОДАВАТЕЛЬ
ФИО
Стаж КД
Иванов
5
К1
Петров
7
--Сидоров 10
К2
с) три отношения
ПРЕПОДАВАТЕЛЬ
НП
П1
П2
П3
ФИО
Иванов
Петров
Сидоров
Стаж
5
7
10
ДИСЦИПЛИНА
КД Часы НП
К1 62
П1
К2 74
П2
К3 102
---
ВЕДЕТ
НП
П1
П3
КД
К1
К2
ДИСЦИПЛИНА
КД
К1
К2
К3
Часы
62
74
102
Рис.1.20. Варианты отношений для правила 3
Использование одного отношения в рассматриваемом случае приводит
к наличию нежелательных пустых полей в этом отношении (рис. 1.20а). При
использовании двух отношений (рис. 1.20б) нам пришлось добавить ключи
каждой из сущностей в отношение, соответствующее другой сущности, чтобы
не потерять сведения о том, какую дисциплину ведет каждый преподаватель и
наоборот. При этом также появились пустые поля.
Выход заключается в использовании трех отношений, сформированных
по правилу 3 (рис. 1.20в). Объектные отношения (с атрибутами сущностей)
содержат полную информацию обо всех преподавателях и дисциплинах
соответственно. Связное отношение ВЕДЕТ содержит данные о
преподавателях, которые ведут дисциплины и о дисциплинах, которые
ведутся преподавателями. При этом в нем имеется только одно упоминание о
каждом преподавателе и дисциплине в силу связи 1:1. Это отношение
содержит в данном случае только ключевые атрибуты обеих сущностей, но
может иметь и другие атрибуты, характеризующие эту связь. Например,
номер семестра, в котором преподаватель ведет дисциплину.
Итак, сформулированы три правила, позволяющие формировать
отношения на основе ER-диаграмм, для вариантов со степенью связи типа
1:1. Сформулируем аналогичные два правила для вариантов, степень связи,
между сущностями которых 1:М.
Формирование отношений для связи 1:М
Если две сущности С1 и С2 связаны как 1:М, сущность С1 будем называть
односвязной (1-связной), а сущность С2 - многосвязной (М-связной).
Определяющим фактором при формировании отношений, связанных этим
видом связи, является класс принадлежности М-связной сущности. Так, если
класс принадлежности М-связной сущности обязательный, то в результате
применения правила получим два отношения, если необязательный - три
отношения. Класс принадлежности односвязной сущности не влияет на
результат.
Чтобы
убедиться
в
этом,
рассмотрим
отношение
ПРЕПОДАВАТЕЛЬ_ДИСЦИПЛИНА
(рис.
1.21.),
соответствующее
диаграммам, приведенным на рис. 1.11., т. е. случаю, когда связь типа 1:М,
класс принадлежности М-связной сущности обязательный, 1-связной необязательный.
ПРЕПОДАВАТЕЛЬ_ДИСЦИПЛИНА
ФИО
Стаж
Часы
Н
П1 Иванов 5
П
П1 Иванов 5
П2 Петров 7
Сидоров 10
П3 Сидоров 10
П3
П4 Егоров 5
П4 Егоров 5
КД
К1 62
К2
К4
К5
Кб
КЗ
К7
74
80
96
120
102
89
П5 Козлов
8
---
---
Рис. 1.21. Исходное отношение
С отношением ПРЕПОДАВАТЕЛЬ_ДИСЦИПЛИНА (рис. 1.21.)
связаны следующие проблемы:
• имеются кортежи с пустыми полями (преподаватель не ведет
дисциплины),
• избыточное
дублирование
данных
(повторяется
стаж
преподавателя) в кортежах со сведениями о преподавателях, ведущих
несколько дисциплин.
Если бы класс принадлежности 1 -связной сущности был обязательным
(нет преподавателя без дисциплины), то исчезли бы пустые поля, но
повторяющиеся данные в атрибутах преподавателя сохранились бы. Для
устранения названных проблем отношения могут быть сформированы по
следующему правилу.
Правило 4. Если степень связи между сущностями 1:М (или М:1) и класс
принадлежности М-связной сущности обязательный, то достаточно
формирование двух отношений (по одному на каждую из сущностей). При
этом первичными ключами этих отношений являются ключи их сущностей.
Кроме того, ключ 1 -связной сущности добавляется как атрибут (внешний
ключ) в отношение, соответствующее М-связной сущности.
На рис. 1.22. приведены диаграмма ER-типа и отношения,
сформированные по правилу 4.
Рис. 1.22. Диаграмма и отношения для правила 4
В соответствии с правилом 4 преобразуем отношение на рис. 1.21. в два
отношения (рис. 1.23.).
ПРЕПОДАВАТЕЛЬ
ДИСЦИПЛИНА
НП
П1
П2
П3
П4
П5
ФИО
Иванов
Петров
Сидоров
Егоров
Козлов
Стаж
5
7
10
5
8
Рис. 1.23. Отношения, полученные
КД
К1
К2
К3
К4
К5
К6
К7
Часы
62
74
102
80
96
120
89
НП
П1
П1
П4
П2
П3
П3
П4
по правилу 4
Из рис. 1.23. видно, что пустые поля и дублирование информации
удалось устранить. Потери сведений о том, кто из преподавателей ведет
какую дисциплину, не произошло благодаря введению ключа НП сущности
ПРЕПОДАВАТЕЛЬ в качестве внешнего ключа в отношение
ДИСЦИПЛИНА.
Для формулирования и обоснования необходимости использования
следующего правила рассмотрим следующий пример.
Пример. Связь между сущностями 1:М, а класс принадлежности Мсвязной сущности необязательный.
Пусть
класс
принадлежности
1-связной
сущности
также
необязательный, хотя это и не принципиально, так как определяющим
является класс принадлежности М-связной сущности. Посмотрим, к чему
может привести использование одного отношения в этом случае (рис. 1.24.).
ПРЕПОДАВАТЕЛЬ_ДИСЦИПЛИНА
ФИО
Стаж
Часы
Н
П1 Иванов 5
П
П1 Иванов 5
К
К1 62
Д
К2 74
П2 Петров 7
--- ----Сидоров 10
П3
П4 Егоров 5
П4 Егоров 5
П5 Козлов 8
К4 80
К5 96
Кб 120
КЗ 102
К7 89
--- --Рис. 1.24. Исходное отношение
С приведенным отношением связаны следующие проблемы:
1. Имеются пустые поля в кортежах, которые содержат следующее:
а)
данные о преподавателях, не ведущих дисциплин;
б) данные о дисциплинах, которые не ведутся преподавателями.
2. Избыточное дублирование данных о преподавателях, ведущих более
одной дисциплины.
В случае обязательного класса принадлежности 1-связной сущности
исчезают проблемы 1 а). Для устранения всех проблем нужно перейти к
трем отношениям в соответствии со следующим правилом.
Правило 5. Если степень связи 1:М (М:1) и класс принадлежности М-
связной сущности является необязательным, то необходимо формирование
трех отношений (рис. 1.25.).
Рис. 1.25. Диаграмма и отношение для правила 5
Два отношения соответствуют связываемым сущностям, ключи которых
являются первичными в этих отношениях. Третье отношение является
связным между первыми двумя (его ключ объединяет ключевые атрибуты
связываемых отношений).
В результате применения правила 5 к рассматриваемому отношению
содержащиеся в нем данные (рис. 1.24.) распределяются по трем отношениям
(рис. 1.26.).
ПРЕПОДАВАТЕЛЬ
НП
П1
П2
П3
П4
П5
ФИО
Иванов
Петров
Сидоров
Егоров
Козлов
ВЕДЕТ
Стаж
5
7
10
5
8
НП
П1
П1
П2
П3
П4
П4
КД
К1
К2
К4
К6
К3
К7
ДИСЦИПЛИНА
КД
К1
К2
К3
К4
К5
К6
К7
Часы
62
74
102
80
96
120
89
Рис. 1.26. Отношения, полученные по правилу 5
Таким образом, указанные проблемы удалось разрешить. Ключ в
связном отношении ВЕДЕТ, является составным и включает в себя ключевые
атрибуты обоих связываемых отношений (сущностей). В практических
ситуациях связное отношение может содержать и другие характеризующие
связь атрибуты.
Подчеркнем, что определяющим фактором при выборе между 4-м или
5-м правилом является класс принадлежности М-связной сущности.
Формирование отношений для связи М:М
При наличии связи М:М между двумя сущностями необходимо три
отношения независимо от класса принадлежности любой из сущностей.
Использование одного или двух отношений в этом случае не избавляет от
пустых полей или избыточно дублируемых данных.
Правило 6. Если степень связи М:М, то независимо от класса
принадлежности сущностей формируются три отношения. Два отношения
соответствуют связываемым сущностям и их ключи являются первичными
ключами этих отношений. Третье отношение является связным между
первыми двумя, а его ключ объединяет ключевые атрибуты связываемых
отношений.
На рис. 1.27. приведены диаграмма ER-типа и отношения,
сформированные по правилу 6. Нами показан вариант с классом
принадлежности сущностей Н-Н, хотя, согласно правилу 6, он может быть
произвольным.
Применим правило 6 к примеру, приведенному на рис. 1.12. В нем
степень связи равна М:М, класс принадлежности для сущности
ПРЕПОДАВАТЕЛЬ обязательный, а для сущности ДИСЦИПЛИНА необязательный. Соответствующее этому примеру исходное отношение
показано на рис. 1.28.
Рис. 1.27. Диаграмма и отношения для правила 6
ПРЕПОДАВАТЕЛЬ_ДИСЦИПЛИНА
ФИО
Стаж
Часы
Н
КД
П1 Иванов 5
К1 62
П
П1 Иванов 5
К2 74
П2 Петров 7
К4 80
--- ----КЗ 102
Сидоров 10
Кб 120
П3
П4 Егоров 5
К2 74
П4 Егоров 5
К7 89
П5 Козлов 8
К5 96
Рис. 1.28. Исходное отношение
В результате применения правила 6 получаются три отношения (рис. 1.29.).
ПРЕПОДАВАТЕЛЬ
ВЕДЕТ
ДИСЦИПЛИНА
НП
П1
П2
П3
П4
П5
НП
П1
П1
П2
П3
П4
П4
КД
К1
К2
К3
К4
К5
К6
К7
ФИО
Иванов
Петров
Сидоров
Егоров
Козлов
Стаж
5
7
10
5
8
КД
К1
К2
К4
К6
К3
К7
Часы
62
74
102
80
96
120
89
Рис. 1.29. Отношения, полученные по правилу 6
Аналогичные результаты получаются и для трех других вариантов,
различающихся классами принадлежности их сущностей.
Пример 1. Спроектировать базу данных для программного комплекса «Учет
книг в библиотеке».
Выделим сущности «Читатель» и «Книга». Степень связи М:М. Класс
принадлежности для сущности «Читатель» -необязательный, для книги –
необязательный. Необходимые данные запишем в таблицу:
Читатель-Книга
Nomer
Family
Name
Shifr
Avtor
Namebook
1
Иванов
Иван
111
Фаронов
Паскаль
------222
Архангельский
Delphi 6
2
Петров
Петр
------1
Иванов
Иван
333
Гейл
Visual Basic
3
Сидоров
Сидор
111
Фаронов
Паскаль
Диаграмма ER – экземпляров имеет вид:
ER – диаграмма имеет следующий вид:
или
Получаем отношения для правила 6.
или
В результате получаем 3 отношения
Опишем все поля:
Nomer – номер читательского, ключевое поле;
Family – фамилия читателя;
Name – имя читателя;
Shifr – шифр книги;
Avtor – фамилия автора;
Namebook – название книги.
Если взять сущности Темы, Книги, Читатели, то ЕR – диаграмма
выглядит следующим образом:
В результате получим 4 отношения:
где
Kodt – код темы;
Nametem – название темы.
Задания.
Задание 1. Спроектировать базу данных для программного комплекса
«КСК».
Задание 2. Спроектировать базу данных для программного комплекса
«Отдел кадров на научном предприятии».
Задание 3. Спроектировать базу данных для программного комплекса
«Учебный процесс университета».
Задание 4. Спроектировать базу данных для программного комплекса
«Агентство недвижимости».
Лабораторное занятие 12. Разграничение прав доступа. Создание логина.
Резервное копирование и восстановление баз данных.
Разграничение прав доступа
Создание логина
Базу данных можно сделать доступной для одних пользователей и
недоступной для других. Рассмотрим на примере. Создадим новый логин,
для этого на сервере войдем в папку Security, выделим правой клавишей
мыши Logins и выберем пункт меню New Login. Дадим имя логину в поле
Login Name, установим переключатель SQL Server Authentication, в поле
Password введем пароль, в поле Confirm Password введем тот же самый
пароль. Чтобы пароль можно было повторять, нужно снять флажок User must
change password at next login.
Щелкнуть левой кнопкой мыши на User Mapping и пометить флажком
базу, к которой пользователь будет иметь доступ, соединив созданный логин
с базой данных, пометив в нижней части окна флажок db_owner (задав права
хозяина). Можно также задать другие права.
Здесь же можно создать серверные роли, щелкнув в левой верхней
части окна на Server Roles и пометив флажком нужную роль.
Разграничение прав доступа
После
создания
нового
логина,
например,
QA
можно
переподключиться с этим логином. На имени сервера нажать правой кнопкой
мыши и выбрать Connect. В появившемся окне в поле Authentication выбрать
SQL Server Autthentication. В поле login ввести QA, в поле Password ввести
пароль, например 1234567 и нажать Connect. Предыдущий сервер с правами
администратора можно отключить, нажав на имени сервера с правами
администратора правую кнопку мыши и выбрав Disconnect. Теперь если у
логина QA есть полные права к базе Products, то мы можем работать с этой
базой данных, добавляя новые таблицы, добавляя новые данные. Если же у
логина QA нет доступа к базе Tovar, то мы не можем работать с этой базой
данных: нельзя создавать новые таблицы, нельзя редактировать данные и т.д.
Для подключения с правами администратора нужно на имени сервера нажать
правой клавишей мыши и выбрать Connect, в поле Authentication выбрать
Windows Authentication. Предыдущий сервер с логином QA можно
отключить.
Сервер можно приостановить командой Pause (нажать правую кнопку
мыши на имени сервера), остановить командой Stop, запустить командой
Start. Состояние сервера –включен, приостановлен, остановлен, можно
видеть по его значку.
Резервное копирование баз данных
Для того, чтобы выполнить резервное копирование базы данных,
необходимо щелкнуть правой кнопкой мыши на базе данных, например, на
shop и выбрать Tasks– Back Up, выбрать вид резервного копирования Full,
Differential, Transaction log или оставить по умолчанию Full
и нажать
кнопку ОК. Далее можно все сделать по умолчанию. Появится сообщение об
успешном копировании.
Восстановление базы данных из резервной копии
Создадим новую базу данных с именем shop1. Щелкнув правой
клавишей на shop1, выберем команду Tasks- Restore-Database. В
появившемся окне выбрать переключатель Device, в открывшемся окне
нажать кнопку Add, выбрать shop.bak и нажать ОК, ОК. В DestinationDatabase поменять название базы данных shop на shop1ю Щелкнуть на
Options в левом верхнем углу, пометить флажок Owertrite the existing
database, нажать кнопку ОК, появится сообщение Database ‘shop1’ restored
successfully. В базе данных shop1 появятся все таблицы из базы shop.
Задания.
Задание 1. Создать новый логин, соединить его с базой данных, задать
права доступа, войти с созданным логином в SQL Server посмотреть права
доступа к базе данных.
Задание 2. Выполнить резервное копирование базы данных.
Задание 3. Выполнить восстановление базы данных из резервной копии.
Лабораторное занятие 13. Представления. Использование представлений
как средств выборки по условию. Сложные представления. Сценарии и
пакеты.
Представления
Представление – это заранее определенный запрос, сохраненный в
базе данных, имеющий вид обычной таблицы и проявляющий себя подобно
таблице, но не требующий дополнительного места для хранения.
Представление используется точно так же, как и обычная таблица. Данными
представления тоже можно манипулировать, хотя и с некоторыми
ограничениями. Если используемая в представлении таблица удаляется,
представление становится недоступным.
Представление можно использовать для защиты данных, то есть для
ограничения доступа пользователей к определенным столбцам и строкам
таблиц, в зависимости от условий, задаваемых выражением ключевого слова
WHERE в определении представления. Например, чтобы не все пользователи
базы данных видели оклады сотрудников из соответствующей таблицы,
можно создать для этой таблицы представление, содержащее только
требуемую информацию (без поля, содержащего оклады сотрудников).
Представление является удобным средством для создания итоговых
отчетов на основе данных таблиц, которые обновляются очень часто.
Представления являются таблицами, содержание которых выбирается из
других таблиц. Они работают в SQL-запросах так же, как и обычные
таблицы.
Структура запроса, создающего представление, довольно проста:
CREATE VIEW имя представления
AS подзапрос <с условием>
После предложения CREATE VIEW следует имя представления, а
затем указывается подзапрос, в котором определяются возвращаемые поля и
условия отбора записей. Следующий запрос создает простое представление:
CREATE VIEW SimpleView
AS SELECT * FROM Employees
WHERE City = 'London'
Условием отбора записей в представление явилось соответствие
значений поля City значению London. Это представление потом можно
использовать как обычную таблицу:
SELECT * FROM SimpleView
В ходе выполнения запроса были выбраны все записи представления.
Если из преставления нужно извлечь только поля City, FirstName и LastName,
следует применить другой запрос.
SELECT City, FirstName, LastName FROM SimpleView
Можно создать другое представление, ограничив список возвращаемых
полей при его описании. Предположим, что необходимо получить название
товара из поля ProductName и тип его упаковки из поля QuantityPerUnit,
которые хранятся в таблице Products:
CREATE VIEW SecondView
AS SELECT ProductName, QuantityPerUnit FROM Products
Соответствующий запрос с условием поможет получить записи из
этого представления:
SELECT * FROM SecondView WHERE ProductName like '%Cha%’
Используя представления, можно даже модифицировать данные. Эта
возможность иллюстрируется следующим запросом:
UPDATE SecondView
SET QuantityPerUnit = ' 1 '
WHERE ProductName like '%Cha%’
В ходе выполнения запроса полю QuantityPerUnit будет присвоено
значение 1 для тех записей, в поле ProductName которых встретилась
подстрока «Cha». После этого можно извлечь данные из представления:
SELECT * FROM SecondView
WHERE ProductName like '%Cha%’
Для удаления представлений из базы данных используется команда
DROP VIEW. У этой команды есть две опции – RESTRICT и CASCADE.
Если используется RESTRICT и в условиях имеются зависимые
представления, то оператор DROP VIEW возвращает ошибку. При
использовании опции CASCADE и наличии зависимых представлений
оператор DROP VIEW завершается успешно и все зависимые представления
тоже удаляются.
Применение представлений обоснованно в связи с необходимостью
защиты данных и упрощения работы пользователей. Редактирование
представлений осуществляется с помощью команды ALTER VIEW.
При создании представлений следует учитывать, что представления, как и
таблицы, должны иметь уникальные имена в рамках той же базы данных.
Представления могут иметь не более 1024 столбцов и могут обращать не
более чем к 256 таблицам.
Также можно создавать представления на основе других представлений.
Такие представления еще называют вложенными (nested views). Однако
уровень вложенности не может быть больще 32-х. Команда SELECT,
используемая в представлении, не может включать выражения INTO или
ORDER BY (за исключением тех случаев, когда также применяется
выражение TOP или OFFSET). Если же необходима сортировка данных в
представлении, то выражение ORDER BY применяется в команде SELECT,
которая извлекает данные из представления.
Пример 1.
Задание 1.
Лабораторное занятие 14,15. Хранимые процедуры на добавление,
удаление, редактирование данных. Написание различных хранимых
процедур с параметрами по умолчанию. Написание произвольных хранимых
процедур.
Использование процедур
Команды SQL (CREATE TABLE, INSERT, UPDATE, SELECT) дают
возможность сообщить базе данных, что делать, но не как делать. Сообщить
ядру базы данных, каким образом следует обрабатывать данные, можно
посредством составления процедур.
Хранимые процедуры – это набор операторов SQL, созданный для
удобства использования в программах. Сохраненную процедуру
использовать проще, чем каждый раз записывать весь набор входящих в нее
операторов SQL. Сохраненные процедуры можно вкладывать одну в другую
(уровень вложенности не может превышать 16).
Сохраненная процедура может возвращать значения, выполнять
сравнения вводимых пользователем значений с заранее установленными
условиями, вычислить какие-либо результаты и т.п.
Некоторые из преимуществ использования сохраненных процедур:
- операторы процедуры уже сохранены в базе данных;
- операторы процедуры уже проверены и находятся в готовом для
использования виде;
- при использовании процедур результат получается быстрее;
- возможность сохранения процедур позволяет использовать
модульное программирование;
- сохраненные процедуры могут вызывать другие процедуры;
- сохраненные процедуры могут вызываться другими программами.
В SQL - Server
процедуры создаются с помощью оператора
следующего вида:
CREATE PROCEDURE имя_процедуры
[ [ ( ] @имя_параметра
ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ) [, МАСШТАБ ])
[=DEFAULT][OUTPUT] ]
[, @ИМЯ_ПАРАМЕТРА
ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ) [, МАСШТАБ ])
[=DEFAULT][OUTPUT] ]
[WITH RECOMPILE]
AS операторы SQL
Сохраненные процедуры используются следующим образом:
EXECUTE [ @ = ] имя_процедуры
[ [ @ имя_параметра =] значение |
[ @ имя_параметра = ] @ переменная [ OUTPUT ] ]
[WITH RECOMPILE]
Пример 1. Например, мы хотим увеличить на единицу значения
номеров курсов (в поле Grup_COURSE) в таблице GRUPPA:
CREATE PROCEDURE new_course
AS
UPDATE GRUPPA
SET Grup_COURSE = Grup_COURSE +1
Проверим работу процедуры:
EXEC new_course
SELECT * FROM GRUPPA
Вернем таблицу GRUPPA в первоначальное состояние:
CREATE PROCEDURE old_course
AS
UPDATE GRUPPA
SET Grup_COURSE = Grup_COURSE -1
EXEC old_course
SELECT * FROM GRUPPA
Пример 2. Простая процедура по использованию оператора SELECT:
CREATE PROCEDURE spisok_stud
AS
SELECT * from Students
Применение параметров
Хранимая процедура предоставляет определенные процедурные
возможности (а если она применяется в инфрастуктуре .NET, такие
возможности становятся весьма значительными), а также обеспечивает
повышение производительности, но в большинстве обстоятельств хранимая
процедура не позволяет добиться многого, если не предусмотрена
возможность передать ей некоторые данные, указывающие на то, какие
действия должны быть выполнены с ее помощью. Аналогичным образом,
часто требуется получить из хранимой процедуры определенную
информацию, причем не просто один или несколько наборов строк с
данными из таблицы, а более конкретную информацию. Еще одним
примером той задачи, которую можно было бы решить с использованием
хранимой процедуры, может стать обновление нескольких строк в таблице и
получение сведений, ограничивающихся лишь тем, какие именно строки
были обновлены. Чаще всего такие данные нелегко получить в форме набора
строк, поэтому возникает необходимость предусмотреть использование
выходных параметров.
При вызове хранимой процедуры параметры могут быть заданы либо с
учетом позиции, либо по имени, а в самой вызываемой хранимой процедуре
способ, применяемый для передачи параметров, не играет особой роли,
поскольку для всех параметров, независимо от способа их передачи в
процедуру, используется одинаковый формат объявления.
Объявление параметров
Для объявления параметра необходимо задать от двух до четырех
указанных ниже фрагментов информации.
Имя.
Тип данных.
Заданное по умолчанию значение.
Обозначение выходного параметра.
Объявление параметра имеет следующий синтаксис: (@parameter_name
[AS] datatype [= default|NULL] [VARYING] [OUTPUT|OUT]
Правила именования, в соответствии с которыми формируется имя
параметра, являются довольно простыми. В основном они совпадают с
правилами составления имен объектов, за исключением того, что в них не
допускается использование пробелов. Кроме того, имена параметров должны
начинаться со знака @.
Тип данных, во многом аналогично имени, должен быть объявлен так
же, как и для переменной, — с указанием допустимого встроенного или
определяемого пользователем типа данных СУБД SQL Server. Кроме того,
следует учитыватъ, что ключевое слово OUTPUT может быть сокращенно
записано как OUT.
Значительные различия между объявлениями параметров хранимых
процедур и объявлениями переменных начинают впервые обнаруживаться,
когда дело касается значений, заданных по умолчанию. Прежде всего, при
инициализации переменным всегда присваиваются NULL-значения, а на
параметры это правило не распространяется. В действительности, если в
объявлении параметра не предусмотрено заданное по умолчанию значение,
то подразумевается, что этот параметр должен быть обязательным и что при
вызове хранимой процедуры должно быть указано его начальное значение.
Чтобы задать предусмотренное по умолчанию значение, необходимо
добавить знак равенства (=) после обозначения типа данных, а затем указать
применяемое по умолчанию значение. Благодаря этому пользователи
получают возможность при вызове хранимой процедуры принимать решение
о том, следует ли задать другое значение параметра или воспользоваться
значением, предусмотренным по умолчанию.
Пример 3. Создадим хранимую процедуру, предусмотрим
использование нескольких входных параметров для ввода новой строки в
таблицу Sclad:
USE Sclad
GO
CREATE PROC Add_otdel
@otdel char (15),
@Phone char(10)
AS
INSERT INTO otdel
VALUES
(@otdel, @Phone)
Хранимую процедуру можно использовать для вставки в эту таблицу
дополнительных строк. В объявлении хранимой процедуры для двух
указанных параметров не были предусмотрены значения, применяемые по
умолчанию, поэтому оба параметра рассматриваются как обязательные. Это
означает, что для успешного вызова хранимой процедуры необходимо
предоставить оба параметра. В этом можно легко убедиться, осуществив
попытку вызвать хранимую процедуру, указав только один параметр, как в
следующем примере, или вообще не указывая параметры:
EXEC Add_otdel 'Бухгалтерия'
СУБД SQL Server немедленно сообщает о том, что возникла ошибка.
Пример 4. Пример процедуры, которую можно использовать для
добавления новых строк в таблицу GRUPPA:
CREATE PROCEDURE new_gruppa
(@Grup_ID int ,
@Grup_NAME char (9),
@Grup_KOLSTUD int,
@Grup_COURSE int)
AS
INSERT INTO Gruppa
VALUES (@Grup_NAME, @Grup_KOLSTUD, @Grup_COURSE)
Столбец Grup_ID таблицы GRUPPA имеет тип identity, поэтому
определяется только при вводе.
Работа этой процедуры проверяется следующим образом:
EXEC new_gruppa 18, 'ECT-04-5', 25, 1
Обратите внимание, что при вводе данных система автоматически
присваивает полю Grup_ID очередное значение независимо от того, какое
значение определил пользователь.
Пример 5. Рассмотрим пример хранимой процедуры на удаление
записи из таблицы
Gruppa.
Create procedure gruppa_delete
(@Grup_id int)
AS
Delete from Gruppa where Grup_id=@Grup_id
Обращение к этой процедуре в среде
MS
SQL
Server
будет
выглядеть так:
Exec gruppa_delete 17
Здесь число 17 – это значение поля Grup_id.
Пример 6. Создадим хранимую процедуру, осуществляющую
редактирование данных в таблице gruppa:
Create procedure gruppa_Edit
(@Grup_ID int,
@Grup_NAME char (9),
@Grup_KOLSTUD int,
@Grup_COURSE int)
AS
Update Gruppa Set
Grup_Name=@Grup_Name,
Grup_KOLSTUD=@Grup_KOLSTUD,
Grup_COURSE=@Grup_COURSE
Where Grup_id=@Grup_id
Производится редактирование той записи, в которой значение поля
Grup_id равно
@Grup_id. Обращение к хранимой процедуре в среде MS
SQL Server запишется следующим образом:
Exec gruppa_Edit 17,’IS101’, 20, 3
Значения записи с Grup_ID =17 поменяются на новые значения
’IS101’, 20, 3.
Предоставление значений, применяемых no умолчанию
Чтобы указать, что параметр является необязательным, необходимо
ввести для него значение, предусмотренное по умолчанию. Для этого
достаточно ввести знак = и задать значение, которое должно применяться по
умолчанию, после указания типа данных, но перед запятой.
Пример 7. Подготовим хранимую процедуру, предназначенную для
вставки данных о отделах, обозначим параметр, соответствующий номеру
телефона, как необязательный:
USE Sclad
GO
CREATE PROC Add_otdel
@otdel nvarchar(15),
@Phone nvarchar(10) = NULL
AS
INSERT INTO otdel
VALUES
(@otdel, @Phone)
Введем оператор, выполняющий данную хранимую процедуру:
EXEC Add_otdel 'Ректорат'
Эта попытка ввода данных осуществляется успешно, и в базу данных
вставляется новая строка:
(1 row(s) affected)
B данном случае в качестве заданного no умолчанию применяется
NULL-значение, но как применяемое по умолчанию может быть задано
любое другое значение, совместимое с типом данных параметра, для
которого определяется предусмотренное по умолчанию значение. Кроме
того, следует отметить, что мы не обязаны задавать предусмотренное по
умолчанию значение для обоих параметров, поскольку мы сами вправе
решать, для какого из этих параметров должно быть предусмотрено
применяемое по умолчанию значение и для какого — нет, т.е. определять,
какие параметры являются обязательными (не имеющими заданных по
умолчанию значений), a какие — нет (имеющими заданные по умолчанию
значения).
Задания.
Задание 1. Создать базу данных sclad. Создать хранимые процедуры на
добавление, удаление, редактирование данных в таблицах tovar, otdel.
Задание 2. Создать хранимые процедуры на добавление, удаление,
данных в промежуточной таблице uchet.