Обследование предметной области

Методические указания

к выполнению курсовой работы

по дисциплине «Проектирование баз данных»

для студентов направления подготовки

«Прикладная информатика»

Направленность (профиль): Прикладная информатика в экономике

(Уровень бакалавриата)

Ставрополь, 2016

Методические указания к выполнению курсовой работы составлены в соответствии федеральным государственным образовательным стандартом высшего образования по направлению подготовки «Прикладная информатика», направленность (профиль) «Прикладная информатика в экономике» (уровень бакалавриата) и рабочей программой по дисциплине «Проектирование баз данных».

В результате выполнения курсовой работы предполагается формирование компетенции:

ОПК-4: способность решать стандартные задачи профессиональной деятельности на основе информационной и библиографической культуры с применением информационно-коммуникационных технологий и с учетом основных требований информационной безопасности

Составитель: Дроздова В.И., доктор физ.- мат. наук, проф., Фоминов Е.И.

Рецензент: Ю.П. Стоянов, генеральный директор ООО «Стилсофт»

Рекомендовано к использованию в учебном процессе для студентов по направлению «Прикладная информатика» Ученым советом филиала МИРЭА в г. Ставрополе (протокол № __ от ______________ 20 ___ года)

Содержание

1 Порядок и этапы ВЫПОЛНЕНИЯ курсовой работы... 4

2 СТРУКТУРА РАБОТЫ... 5

3 СОДЕРЖАНИЕ ПОЯСНИТЕЛЬНОЙ ЗАПИСКИ.. 6

3.1 ВВЕДЕНИЕ.. 6

3.2 ОБСЛЕДОВАНИЕ ПРЕДМЕТНОЙ ОБЛАСТИ.. 6

3.3 КОНЦЕПТУАЛЬНОЕ ПРОЕКТИРОВАНИЕ.. 7

3.4 ИНФОЛОГИЧЕСКОЕ ПРОЕКТИРОВАНИЕ.. 7

3.5 РЕЛЯЦИОННАЯ МОДЕЛЬ БД.. 7

3.6 ДАТАЛОГИЧЕСКАЯ МОДЕЛЬ БД.. 7

3.7 Запросы... 8

3.8 Разработка механизмов защиты данных от несанкционированного доступа.. 8

3.9 Требования к техническому обеспечению... 9

3.10 Инструкция по использованию БД.. 9

3.11 ЗАКЛЮЧЕНИЕ.. 9

3.12 СПИСОК ЛИТЕРАТУРЫ... 9

3.13 ПРИЛОЖЕНИЯ К ПОЯСНИТЕЛЬНОЙ ЗАПИСКЕ.. 9

4 Возможная тематика курсовых РАБОТ.. 9

5. Список рекомендуемой литературы... 23

Приложение 1. 24

Приложение 2. 25

Приложение 3. 26

Расширенные SQL-запросы.. 26

1 Безусловная выборка значений. 26

2 Простая выборка. 27

3 Выборка уникальных значений. 27

4 Выборка вычисляемых значений. 27

5 Выборка с условием.. 29

6 Выборка с упорядочением.. 30

7 Выборка с использованием оператора BETWEEN.. 32

8 Выборка с использованием оператора IN (принадлежит) 33

9 Выборка с использованием шаблонов. 33

10 Выборка из связанных таблиц. 35

11 Использование группировки данных при организации запросов. 37

12 Использование квантора существования в запросах. 39

13 Объединение множеств. 41

14 Модификация данных в таблицах. 43

15 Удаление данных. 44

16 Добавление записей. 45

17 Запросы по дате. 46

Список рекомендуемой литературы.. 50

1 Порядок и этапы ВЫПОЛНЕНИЯ
курсовой работы

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

Рекомендуемые технические средства и операционная система:ПЭВМ типа IBM PC и ОС Microsoft Windows.

Рекомендуемая СУБД– VISUAL FOX PRO. По согласованию с руководителем курсовой работы может быть использована другая локальная реляционная СУБД или архитектура клиент-сервер.

Работу над курсовой работой по дисциплине «Базы данных» рекомендуется выполнять в следующей последовательности (таблица 1):

Таблица 1 – График выполнения курсовой работы

Наименование этапа работы	Номер недели
Получение задания на курсовую работу
Предварительное обследование предметной области и оформление его результатов
Инфологическое проектирование
Преобразование ER-модели в реляционную
Даталогическое проектирование, загрузка базы данных, тестирование и отладка
Разработка запросов
Создание справочной системы
Создание входных и выходных форм
Оформление пояснительной записки
Защита курсовой работы

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

Описание предметной области (ПО) должно:

- отражать задачи, решаемые в области экономики, организации производства, управлении производством, коммерции, рекламе или маркетинге;

- содержать описание бизнес-процессов, характерных для данной ПО;

- содержать обоснование актуальности решаемых курсовой работой задач.

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

Общий объем пояснительной записки не должен превышать 50 страниц, в том числе введение – не более 3 страниц. В пояснительной записке приводятся схемы, экранные формы, входные и выходные документы, тексты запросов. Листинг программы выносится в приложение.

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

Порядок защиты курсовой работы и состав комиссии утверждается на заседании кафедры. Студент делает доклад (около 5 минут), в котором кратко излагает результаты работы, демонстрирует экранные формы и разработанную программную реализацию.

СТРУКТУРА РАБОТЫ

Курсовая работа содержит текстовую пояснительную записку, которая оформляется в соответствии с требованиями стандарта ГОСТ 2.106-68 и ГОСТ 19.70-90.

Пояснительная записка оформляется на листах формата А4 с соблюдением следующих размеров полей: левое – 20 мм, правое – 10 мм, верхнее –
15 мм, нижнее – 20 мм. Текст пояснительной записки должен быть набран шрифтом Times New Roman 14 пт, интервал полуторный.

Титульный лист является первым листом пояснительной записки. Он должен быть оформлен на типовом бланке (приложение 2).

Пояснительная записка должна содержать следующие элементы, расположенные в указанном порядке:

- титульный лист (приложение 2);

- лист задания (приложение 1);

- аннотацию;

- содержание;

- основную часть;

- заключение с выводами;

- список использованных источников;

- приложения.

Нумерация страниц должна быть сквозной. Абзацы в тексте начинают отступом, равным 5 знакам. Содержание основной части пояснительной записки следует делить на разделы, подразделы, пункты. Обязательными разделами основной части пояснительной записки являются следующие:

1 Обследование предметной области.

2 Концептуальное проектирование.

2.1 Перечень сущностей (обосновать список).

2.2 Перечень атрибутов.

3 Инфологическое проектирование БД.

3.1 Диаграмма «сущность-связь».

3.2 Классификация связей.

4 Реляционная модель БД.

4.1 Функциональные зависимости между атрибутами.

4.2 Выбор ключей.

4.3 Нормализация отношений.

5 Даталогическое проектирование БД.

5.1 Состав таблиц БД.

5.2 Средства поддержания целостности.

6 Запросы к БД.

7 Разработка механизмов защиты данных от несанкционированного доступа.

8 Требования к техническому обеспечению.

9 Инструкция по использованию БД.

9.1 Вызов программы.

9.2 Экранные формы.

9.3 Описание отчетов.

СОДЕРЖАНИЕ ПОЯСНИТЕЛЬНОЙ ЗАПИСКИ

ВВЕДЕНИЕ

Во введении необходимо указать следующее:

- наименование предприятия (истинное или вымышленное), для которого разрабатывается БД,

- наименование предметной области,

- назначение разработки БД,

- место разрабатываемой БД в общей системе управления предприятием,

- требования заказчика к разрабатываемой БД,

- чья точка зрения используется при проектировании,

- пользователи БД, права пользователей,

- описание (перечисление) общетехнических и общесистемных программных средств.

РЕЛЯЦИОННАЯ МОДЕЛЬ БД

В этом разделе анализируются функциональные зависимости между атрибутами. Например, сущность «Покупатель» описывается атрибутами, представленными в таблице 1. Функциональную зависимость атрибута Y от X обозначают с помощью записи: X®Y. В таблице 1 показана зависимость всех атрибутов от атрибута «Код покупателя».

Таблица 1 – Функциональные зависимости между атрибутами сущности «Покупатель»

Наименование атрибутов	Функциональные зависимости
Код покупателя Название (ФИО) Индекс Страна Город Адрес Телефон ИНН

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

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

Установить связи между отношениями, используя для этого внешние ключи.

Методика перехода от инфологической модели к реляционной подробно описана в [1].

ДАТАЛОГИЧЕСКАЯ МОДЕЛЬ БД

В этом разделе приводится состав таблиц БД. Для каждого поля таблицы необходимо указать размер поля (в количестве символов), тип данных. Для первичных ключей необходимо ввести запрет неопределенных значений. Для остальных полей возможность запрета неопределенных значений определяется семантикой предметной области. Пример описания состава таблицы приведен в таблице 2.

Таблица 2 – состав таблицы «Покупатель»

Наименование атрибутов	Тип полей	Размер полей	Допустимость неопределенных значений
Код покупателя Название (ФИО) Индекс Страна Город Адрес Телефон ИНН	Integer Character Integer Character Character Character Character Integer		NOT NULL

Создать первичные и внешние ключи отношений. Для этого использовать индексы или индексные выражения.

Выбрать родительские (управляющие) таблицы, на первичные ключи которых ссылаются другие (дочерние) таблицы. Установить постоянные отношения между таблицами и рассмотреть поддержание ссылочной целостности с помощью параметров SQL: CASCADE, RESTRICT, SET NULL.

Ввести список ограничений(список включает ограничения, которые должны затем контролироваться СУБД, для обеспечения целостности базы данных). Описать требования к атрибутам и сущностям для поддержания целостности, определяемой пользователем. Например, для переменной x, которая обозначает оценку на экзамене, можно ввести ограничение: 1£ х £5. Для переменной z, которая обозначает сумму остатка средств на счете клиента банка, можно ввести ограничение: z ³ 0.

Запросы

Сформулировать не менее 10 запросов всех типов, реализуемых средствами СУБД и средствами языка SQL. Составить запросы с коррелированными и некоррелированными подзапросами. Предварительно запросы должны быть сформулированы на естественном языке и выражены в терминах реляционной алгебры или реляционного исчисления. Расширенные SQL-запросы описаны в приложении 3.

3.8 Разработка механизмов защиты данных от
несанкционированного доступа

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

Требования к техническому обеспечению

В этом разделе следует сформулировать требования к системе БД, например, быстродействие системы, требования к аппаратному обеспечению, обеспечение удобного пользовательского интерфейса, контроль доступа, возможность многопользовательского режима, средства резервного копирования и восстановления БД, финансовые критерии, распространенность СУБД, возможность использовать данные “чужого” формата для рассматриваемой СУБД и т.п.

ЗАКЛЮЧЕНИЕ

В этом разделе обобщаются результаты проведенной работы и делаются выводы.

СПИСОК ЛИТЕРАТУРЫ

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

Список рекомендуемой литературы

а) основная литература:

1. Максимов Е.М. Базы данных в системах управления производственными процессами: учебное пособие / Е.М. Максимов, Н.Н. Бахтадзе. – [Электронный ресурс]. Режим доступа: www.knigafund.ru: издательство МГОУ, 2011. – 160с.

2. Мельников В.М. СУБД: язык SQL в примерах и задачах// В.М. Мельников, И.Ф. Астахова, А.П. Толстобров, В.В. Фертиков. – [Электронный ресурс]. Режим доступа: www.knigafund.ru: изд-во Физматгиз, 2009 г. – 166с.

3. Дроздова В.И. Базы данных для бакалавров направления 080500.62 «Бизнес-информатика» [Электронный ресурс]. Режим доступа: www.ebs.lib.ncgti.ru.

б) дополнительная литература:

1. Астахова И.Ф. СУБД язык SQL в примерах и задачах / И.Ф. Астахова, В.М. Мельников, А.П. Толстобродов и др. – [Электронный ресурс]. Режим доступа: znanium.com: М.: ФИЗМАТЛИТ, 2009 г. – 168с.

2. Голицына О.Л. Базы данных: Учебное пособие/ О.Л. Голицына, Н.В. Максимов, И.И. Попов – [Электронный ресурс]. Режим доступа: znanium.com: М.: Форум, ИНФРА-М, 2009 г. – 400с.

3. Кириллов В.В. Введение в реляционные базы данных./ Кириллов В.В., Громов Г.Ю. – [Электронный ресурс]. Режим доступа: www.knigafund.ru: БХВ-Петербург, 2009г. – 450с.

Приложение 1

Министерство образования и науки Российской Федерации

Филиал федерального государственного бюджетного образовательного учреждения высшего образования «Московский технологический университет» в г. Ставрополе филиал МИРЭА в г. Ставрополе

УТВЕРЖДАЮ

Зав. кафедрой__________

______________________

(Ф.И.О.) «____»_______________г.

Задание

на выполнение курсовой работы по дисциплине «Проектирование баз данных»

­­­­­­­­­­­­­­студента __ курса, группа ________ – ____, направление 09.0303 -«Прикладная информатика»

_____________________________________________________________________________

(курс, группа, специальность полностью)

_______________________________________________________________________________________________________

(ФИО студента, полностью)

Тема: Проектирование базы данных « . . . »

указать вариант

ПЛАН

Введение 1 Обследование предметной области. 2 Концептуальное проектирование. 3 Инфологическое проектирование БД. 4 Реляционная модель БД. 5 Даталогическое проектирование БД. 6 Запросы к БД. 7 Разработка механизмов защиты данных от несанкционированного доступа. 8 Требования к техническому обеспечению. 9 Инструкция по использованию БД. Заключение Список использованной литературы

Дата выдачи задания ___________________

Дата сдачи курсовой работы___________

Руководитель курсовой работы _________

Курсовую работу выполнил студент (подпись)___________

Ставрополь ___

Приложение 2

Министерство образования и науки Российской Федерации

Филиал федерального государственного бюджетного образовательного учреждения высшего образования «Московский технологический университет» в г. Ставрополе филиал МИРЭА в г. Ставрополе

«Утверждаю»

Зав.кафедрой__________

Кандаурова Н.В.

(ФИО)

«_____»_____________г.

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

к курсовому проекту по дисциплине

«Базы данных»

На тему: Проектирование базы данных « . . . »

указать вариант

Автор работы: _____________________________________________________

Направление: 09.03.03 « Прикладная информатика» ___________________________________________________________________

Обозначение курсового проекта______________ Группа _______________

Руководитель работы:

_________________________________

(ФИО, должность)

Работа защищена ______________ Оценка ____________________________

Члены комиссии ________________________ _________________________

(подпись) (ФИО)

________________________ _________________________

(подпись) (ФИО)

________________________ _________________________

(подпись) (ФИО)

Ставрополь, 20__

Приложение 3

Расширенные SQL-запросы

FROM список таблиц

[WHERE условие выборки или соединения]

[GROUP BY список полей по условию группировки

[HAVING условие выборки группы]]

[ORDER BY список полей, по которым упорядочить вывод]

При формировании запросов можно использовать уточненные имена полей (например, CUSTOMER.CUSTOMERNO, т.е. Имя_таблицы. Имя_поля).

Ключевое слово ALLподразумевается по умолчанию.

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

SELECT*FROM таблица.

Запрос вида

SELECT*FROM R1, R2

соответствует декартову произведению таблиц R1и R2, т. е R=R1х R2.

Запрос

Простая выборка

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

SELECT kod FROM R1

является столбец kod из таблицы R1.

При выполнении данного запроса в результат выборки будут включены все дубликаты строк.

Выборка уникальных значений

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

SELECT DISTINCT kod FROM R1

Выборка с условием

Для задания условия выборки в SQL-запросе используется команда (ключевое слово) WHERE.Условие, следующее за ключевым словом WHERE, может включать:

1) арифметические операторы сравнения: =, <>, > , <, >=, <=;

2) логические операторы – AND, OR, NOT;

3) скобки, определяющие порядок вычислений.

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

Пример 2. Пусть существует некоторая база данных, в которой имеется таблица Table3, содержащая информацию о поставщиках, покупателях, товарах. Предполагается, что покупатель может приобретать товар в кредит. Произвести из данной базы выбор всех кодов (kod) и фамилий (NAME)покупателей, которые находятся в Москве и имеют кредит(CREDITLIMIT) более 200 000.

С помощью языка SQL запрос, соответствующий примеру 1, можно представить в виде:

SELECT kod, NAME

FROM Table3

WHERE CITY=“Москва”

AND CREDITLIMIT>200000

Выборка с упорядочением

ВкладкаOrdered Byв окне дизайнера запросов позволяет управлять порядком расположения записей в результирующей таблице. Для упорядочивания выделите указателем (курсором) поля, которые будут определять порядок сортировки выбранных данных, и перенесите их последовательно в список Ordering criteria.Для каждого выбранного поля можно с помощью переключателя (кнопки) Order optionsустановить критерий упорядочивания по возрастанию (Ascending) или по убыванию (Descending).

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

Пример 3.Для таблицы Table3из примера 1 необходимо произвести выбор кода (kod), имени (NAME)и суммы кредита (CREDITLIMIT) всех покупателей, проживающих в Ставрополе, расположив их в порядке убывания.

SQL-запрос, реализующий пример 3, имеет вид:

FROM Table3

WHERE CITY=“Ставрополь”

ORDER BY CREDITLIMIT DESC

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

имя поля[упорядочение]

[,имя поля [упорядочение]] ...,

где аргумент «упорядочение» может принимать значение ASC(возрастание) или DESC (убывание). По умолчанию устанавливается значение ASC. В качестве аргументов имя поля могут использоваться только поля результирующей таблицы. Поэтому недопустима следующая конструкция:

FROM Table3

ORDER BY CITY

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

SELECT Kod1, UNITPRICE * (1 + 0,18)

FROM Товары

ORDER BY 2

будет являться результирующая таблица, во второй столбец которой будет помещена информация о стоимости товаров с учетом НДС. Записи в выборке будут упорядочены по второму столбцу.

SELECT kod, NAME, UNITPRICE

FROM Table4

SELECT kod, NAME, UNITPRICE

FROM Table4

SELECT kod, NAME, UNITPRICE

FROM Table4

SELECT kod, NAME, UNITPRICE

FROM Table4

WHERE UNITPRICE=100000

OR UNITPRICE=200000

OR UNITPRICE=500000

Как и в случае с оператором BETWEEN можно также использовать конструкцию NOT IN (не принадлежит), например:

SELECT kod, NAME, UNITPRICE

FROM Table4

SELECT kod, NAME, UNITPRICE

FROM Table4

WHERE NAME LIKE “T%”

пользователь может выбрать из базы данных все товары, наименование которых начинается с буквы “Т”( таблица 4).

Таблица 4 – результат выборки по шаблону

kod	NAME	UNITPRICE
	Toshiba 1200	600.00
	Turbo Pascal	200.00

Шаблоны в языке SQL описываются с помощью оператора LIKE, который может быть представлен в виде:

Выборка из связанных таблиц

Способность «соединять» две или более таблицы в одну представляет собой одну из наиболее мощных возможностей реляционных баз данных.

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

1) выполните команду F10 → Query → Add Table;

2) нажмите кнопку Add Table.

После этого на экран будет выведено диалоговое окно Add Table or View.В этом окне следует выбратьнеобходимые для формирования запроса таблицы, затем нажать кнопку Add. Таким образом, выбранные таблицы будут размещены в окне конструктора запросов. Если между участвующими в запросе таблицами в базе данных установлены постоянные отношения, то в окне конструктора запросов эта связь будет отображаться в виде линии, соединяющей таблицы, а на вкладке Join (Объединение) появится запись, содержащая условие объединения таблиц.

Простое соединение

Простое соединение как один способов выбора информации из нескольких связанных таблиц предполагает, что на выбираемую пользователем информацию не накладывается никаких дополнительных условий. Например, с помощью SQL-запроса

SELECT Tab8.kod2, Tab9.NAME

FROM Tab8, Tab9

WHERE Tab8.STOCK= Tab9.STOCK

будет получен список кодов (kod2) и наименований (NAME) проданных покупателям товаров, представленный в таблице 5.

Таблица 5 – Результат выборки из двух таблиц

Kod2	NAME
	BORLAND C++
	PARADOX for Windows
	Книга TURBO C++
	Книга TURBO C++

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

FROM ORDSALE, GOODS

WHERE ORDSALE.STOCK=GOODS.STOCK

and GOODS.NAME=“Pentium4”

Результат выполнения данного запроса представлен в таблице 6.

Таблица 6 – Результат выборки из двух таблиц с условием

ORDSALE.CUSTOMERNO	FIRSTNAME
	Иванов
	Иванов
	Петров
	Сидоров
	Кузнецов

Соединение трех таблиц

Для выборки данных из трех таблиц в программном коде после ключевого слова WHERE необходимо указать два условия связи таблиц. Пример SQL-запроса для выбора информации из трех связанных таблиц:вывести на экран фамилии и имена всех покупателей, которые приобрели Pentium4.

CUSTOMER.LASTNAME

SELECT kod, SUM(QUANT)

FROM ORDSALE

GROUP BY kod

SELECT kod, SUM(QUANT)

FROM ORDSALE

WHERE CUSTOMERNO <> 23

GROUP BY kod

служит для выборки информации о товаре, проданном покупателям кроме покупателя с кодом 23.

Строки, не удовлетворяющие условию WHERE, исключаются перед группировкой данных.

Конструкции GROUP BY свойственно ограничение, которое заключается в том, что она работает только на одном уровне. Невозможно разбить каждую из групп на группы более низкого уровня, а затем применить некоторую стандартную функцию, например, SUM или AVERAGE на каждом уровне группировки.

Использование HAVING

Оператор HAVING используется для ограничения записей, участвующих в группировке, его нельзя использовать отдельно от конструкции GROUP BY. Оператор HAVING используется для того, чтобы исключать группы так же, как WHERE используется для исключения записей. Выражение после конструкции HAVING должно принимать единственное для группы значение. В частности, результатом выполнения запроса

SELECT kod FROM ORDSALE GROUP BY kod HAVING COUNT(*)>1

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

SELECT NAME FROM tab10

WHERE EXISTS (SELECT * FROM tab11

WHERE tab10.kod= tab11.kod and.STOCK = “Pentium”)

Фактически любой запрос, который может быть выражен с использованием оператора IN, альтернативным образом может быть сформулирован с помощью EXISTS.

Можно сконструировать отрицание существования, используя NOT EXISTS. С помощью SQL-запроса

SELECT NAME FROM tab10

WHERE NOT EXISTS (SELECT * FROM tab11

WHERE tab10.kod= tab11.kod and.STOCK = “Pentium”)

можно получить информацию о покупателях, которые не купили «Pentium».

Заключенный в скобки подзапрос, входящий в конструкцию EXISTS не обязательно использует конструкцию SELECT * ... . В конструкции SELECT можно также указать имя поля, т.е. использовать предложение вида: SELECT имя_поля FROM .... Операторы EXISTS и NOT EXISTS всегда помещаются перед подзапросом.

Объединение множеств

Объединением двух множеств называется множество всех элементов, принадлежащих какому-либо одному или обоим множествам. Поскольку отношение является множеством записей, то можно построить объединение двух отношений. Результатом будет отношение, состоящее из всех строк, входящих в какое-либо одно или в оба сразу отношения. Однако строки этих двух отношений должны быть совместимы по объединению.

В языке SQL две таблицы совместимы по объединению и к ним может быть применен оператор объединения UNION тогда и только тогда, когда:

1) они имеют одинаковое число полей, например m;

2) для всех i ( ) i-е поле первой таблицы и i-е поле второй таблицы имеют в точности одинаковый тип данных.

В SQL-92 реализованы некоторые операции реляционной алгебры в явном виде с помощью операторов: UNION, Intersect, EXCEPT, JOIN.

По отношению к таблицам операторы объединения (UNION), пересечения (Intersect) и разности (EXCEPT) можно применять только в случае объединительной совместимости.

Пример 4. SQL-запрос

SELECT kod FROM tab12 WHERE UNITPRICE>1000

UNION

SELECT kod FROM tab13 WHERE CUSTOMERNO=23

предназначен для выбора кодов товаров, которые имеют стоимость более 1000, либо приобретаются покупателем с кодом 23 (либо и то, и другое).

Использование оператора UNION исключает из результатов выборки повторяющиеся значения. Если это не устраивает пользователя, то вместо оператора UNION используют UNION ALL.Тогда повторы не будут исключены из выборки.

Оператором UNION можно соединить любое количество конструкций SELECT.

Пример 5. С помощью SQL-запроса

SELECT kod FROM tab12 WHERE UNITPRICE>1000

UNION

SELECT kod FROM tab13 WHERE CUSTOMERNO=23

UNION

SELECT kod FROM tab12 WHERE UNITPRICE< 500

пользователь может получить информацию о кодах товаров, которые имеют стоимость более 1000, либо приобретены покупателем с кодом 23, либо имеют цену менее 500.

Если к запросу, приведенному в примере 1, добавить строку OR UNITPRICE < 500, то его результирующая таблица будет аналогична результирующей таблице, соответствующей запросу 2.

Оператор ORDER BY в запрос с использованием оператора UNION может входить только в последнее предложение SELECT. При использовании критерия упорядочивания используются номера полей результирующей таблицы. Пример с включением константы в результирующую таблицу:

SELECT kod,”Стоимость товара >1000$” FROM tab12

WHERE UNITPRICE>1000

UNION

ORDER BY 2,1

Другой синтаксис оператора объединения:

(SELECT*FROM STOCK) UNION (SELECT*FROM ORDSALE)

Еще один вариант запроса:

SELECT*FROM (TABLE tab12 UNION TABLE tab13)

Если эти операции необходимо применить к отдельным столбцам, то используют команду CORRESPONDING BY (имя_поля). В этом случае объединительная совместимость таблиц не требуется, но обязательно указанные поля должны иметь одинаковый тип данных.

14 Модификация данных в таблицах

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

1) добавлять информацию в таблицу;

2) модифицировать данные в таблице;

3) удалять информацию из таблицы.

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

UPDATE таблица

SET поле=выражение [,поле=выражение] …

[WHERE условие]

В результате выполнения этой конструкции все записи в таблице, которые удовлетворяют условию, обновляются в соответствии с оператором присвоения «поле = выражение».

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

Модификация единственной записи. Для каждой записи, которая должна быть обновлена, т. е. для каждой записи, которая удовлетворяет условию WHERE, или для всех записей, если фраза WHERE опущена, ссылки во фразе SET на поля этой записи обозначают значения этих полей до их модификации. Например, с помощью SQL-запроса

UPDATE tab12

SET NAME =“Pentium4”,

UNITPRICE = UNITPRICE+10000

WHERE NAME =“Pentium”

будет изменено название товара «Pentium» на «Pentium4» и увеличена стоимость на 10000.

Модификация множества записей. При изменении значений во множестве записей таблицы условие, определяемой при помощи оператора WHERE,должно удовлетворятьвсему множеству. Используя SQL-запрос

UPDATE tab14

SET CREDITLIMIT= CREDITLIMIT*2

WHERE CITY=“Ставрополь”

можно получить список всех покупателей, проживающих в Ставрополе, с увеличенной в 2 раза суммой кредита.

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

UPDATE tab14

SET UNITPRICE = 0.8*UNITPRICE

WHERE “Мичуринск” = (SELECT CITY FROM tab15

WHERE tab15.CUSTOMERNO= tab14.CUSTOMERNO)

можно вывести на экран список всех покупателей, проживающих в Мичуринске, уменьшив стоимость, приобретенного ими товара на 20%.

Удаление данных

Для удаления данных из базы в языке SQL используется конструкция DELETE, которая имеет следующий синтаксис:

DELETE FROM таблица [WHERE условие]

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

С помощью конструкции DELETEиз базы данных может быть удалена одна запись, множество записей, все записи из одной таблицы; одна или множество записей из нескольких связанных таблиц.

Пример 6 – SQL-запрос, иллюстрирующий удаление одной записи из базы данных

DELETE FROM tab12

WHERE CUSTOMERNO=23

В результате его выполнения будет удален из базы данных покупатель со значением кода, равным «23».

Пример 7 – SQL-запрос, иллюстрирующий удаление из базы множества записей

DELETE FROM tab12 WHERE STOCK=34

После выполнения данного SQL-запроса из таблицы tab12будутудаленывсе записи, в которых поле STOCKравно 34.

Пример 8 – SQL-запрос, иллюстрирующий удаление всех записей из таблицы ORDSALE:

DELETE FROM ORDSALE

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

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

Пример 9 – Удаление с подзапросом

DELETE FROM tab12

WHERE “Киев”= (SELECT CITY FROM tab13

WHERE tab13.CUSTOMERNO= tab12.CUSTOMERNO)

После выполнения данного запроса из базы данных будет удалена информация о покупках всех клиентов, проживающих в Киеве.

Добавление записей

Для добавления записей в базу данных с помощью я