close

Вход

Забыли?

вход по аккаунту

?

Sorokin 1

код для вставкиСкачать
Министерство образования и науки российской федерации
Федеральное государственное автономное образовательное
учреждение высшего профессионального образования
САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ
РЕШЕНИЕ ЗАДАЧ НА ЯЗЫКЕ SQL
Методические указания для студентов очной
и заочной форм обучения
Санкт-Петербург
2014
Составитель: преподаватель А. А. Сорокин
Рецензент: кандидат технических наук, доцент В. П. Попов
Методические указания по курсу «Базы данных» включают варианты лабораторных работ для студентов очного и заочного отделений с пояснениями к их выполнению и оформлению, литературу
и интернет-ресурсы.
Предназначены для студентов очной и заочной форм обучения по
направлению 230100.62 «Информатика и вычислительная техника»
Ивангородского гуманитарно-технического института (филиала)
ФГАОУ ВПО «Санкт-Петербургский государственный университет
аэрокосмического приборостроения».
Подготовлены на кафедре Прикладной математики и информатики Ивангородского гуманитарно-технического института (филиала)
ФГАОУ ВПО «Санкт-Петербургский государственный университет
аэрокосмического приборостроения».
В авторской редакции
Компьютерная верстка Ю. А. Гайнутдинова
Подписано к печати 2.04.2014 г. Формат 60×84 1/16.
Бумага офсетная. Усл. печ. л. 2,9.
Тираж 100 экз. Заказ № 165.
Редакционно-издательский центр ГУАП
190000, Санкт-Петербург, Б. Морская ул., 67
© Санкт-Петербургский государственный
университет аэрокосмического
приборостроения (ГУАП), 2014
ПРЕДИСЛОВИЕ
Учебно-методическое издание подготовлено в соответствии с требованиями ФГОС и программой дисциплины «Базы данных», разработанной и утвержденной в ГУАП.
Учебная дисциплина «Базы данных» заключается не только
в умении правильно сформировать для хранения имеющиеся данные, но и в анализе поставленной задачи для выявления недостающей информации и формирования методов ее хранения и обработки.
Изучение курса «Базы данных» студентами очной формы обучения включает в себя лекционные занятия, лабораторные занятия,
направленные на освоение и закрепление материала, а также самостоятельную работу. Для студентов заочной курс дисциплины сводится к установочным лекциям, лабораторным и самостоятельным
занятиям, в том числе подготовке контрольных работ.
Настоящее пособие нацелено на изучение методик работы с базами данных, перевод задач в удобный для хранения данных в СУБД
и их обработки вид. По завершению курса студенты должны обладать достаточными теоретическими знаниями и практическими навыками для разработки различных по структуре и назначению баз
данных, знать базы данных и системы управления базами данных
для информационных систем различного назначения, уметь разрабатывать инфологические и даталогические схемы баз данных.
3
Лабораторная работа 1.
Проектирование базы данных.
1. Концептуальная модель
В основе любой базы данных лежит ее схема. Существует несколько уровней построения подобных схем, самым основным
и, следовательно, самым абстрактным из них является концептуальная (семантическая, инфологическая) модель. Чаще всего в нее
включается описание информационных объектов и связей между
ними и описание ограничений целостности (требование к допустимым значениям и связям между ними). Самым распространенным
вариантом представления концептуальной модели является ERдиаграмма.
Посмотрим на исходное задание (Приложение А, Пример):
«У Маши пять яблок и четыре груши. У Пети на одно яблоко больше и на две груши меньше, чем у Маши. У Кости столько
яблок, сколько груш у Маши и Пети вместе, и нет груш. Сколько
яблок и груш у каждого из ребят?» В исходном варианте имеется следующий набор данных: дети (их имена), яблоки (количество) и груши (количество). Эти данные
можно представить в виде одной таблицы,
как на рис. 1.1.
У данного подхода есть несколько недостатков. Один из которых – неудобство разРис. 1.1 – Таблица
«Фрукты у детей»
вития представленной системы. Предположим, что потребуется добавить еще один
фрукт – апельсин, для этого необходимо
добавить еще один столбец (рис. 1.2).
А теперь предположим, что подобным
образом мы уже добавили с десяток фруктов, при этом в таблице имеется несколько сотен записей. Что делать, если появится необходимость добавить какой-нибудь
Рис. 1.2 – Таблица
арбуз при чем только кому-то одному?
«Фрукты у детей»
В очередной раз перестроить заполненс апельсинами
ную таблицу ради одной единственной записи? Очевидно, что необходимо изменить
4
подход, тем более, что в задании имеется следующий пункт:
«Виды фруктов не должны ограничиваться яблоками и грушами.
Предусмотреть добавление новых видов фруктов». Оставим в исходной таблице имена, а виды фруктов вынесем в отдельную таблицу
как на рис. 1.3.
Рис. 1.3 – Дополнительная таблица со списком фруктов
Теперь отсутствует ограничение на количество видов фруктов.
Достаточно просто внести новый вид в таблицу Фрукты. Приведенная на рисунке схема показывает, что Любой фрукт может множество принадлежать многим детям (связь один-ко-многим), при этом
тип данных для столбца Фрукт в таблице Дети умышленно не определен. Здесь следует учесть еще одну особенность данного подхода:
Маша из исходной задачи является обладательницей двух видов
фруктов, а значит будет вписана в таблицу Дети дважды.
Теперь возникает вопрос, что делать с теми, у кого нет фруктов?
Обратим внимание на следующие пункты из задания: «Предусмотреть вероятность того, что будет несколько ребят с одинаковыми
именами» и «Добавить не менее одной отличительной особенности
для ребят в соответствующую таблицу». Если у нас будет несколько
Маш, то мы не сможем без их идентификации определить, кому из
них соответствует отдельная запись из таблицы Дети. Вынесем детей в отдельную таблицу, добавив им фамилию в качестве отличительной особенности как на рис. 1.4.
Рис. 1.4 – Схема из трех таблиц
Теперь добавляя детей мы не должны указывать количество
фруктов. Мы легко различаем тех, у кого не совпадают имя и фамилия одновременно.
5
2. Логическая модель
Логическая модель – схема базы данных на основе конкретной
модели данных, например, концептуальной модели. В логической
модели учитывается специфика модели данных, но не учитываются особенности конечной реализации. Отличительной особенностью является меньший уровень абстракции, чем в концептуальной
модели: наличие первичных и внешних ключе.
Построим на основе концептуальной модели (рис. 1.4) логическую с учетом первичных (pi) и внешних (fi) ключей. Пример можно
увидеть на рис. 1.5.
Рис. 1.5 – Логическая модель в PowerDesigner
В таблице «Дети» первичным ключом является связка «Имя-Фамилия», при чем эта же связка – внешний ключ таблицы «Фрукты у
детей». В таблице «Фрукты» первичный ключ – «Название», оно же
является вторым внешним ключом для таблицы «Фрукты у детей».
Сама таблица «Фрукты у детей» не содержит первичного ключа. Данная модель, как и предыдущие (рис.1.1–1.4) построена с помощью
Sybase PowerDesigner. Ту же самую модель можно изобразить средствами программ из пакета Microsoft Office: Microsoft Word – рис.
1.6 или Microsoft Access – рис. 1.7. На этом список программного обеспечения не заканчивается, доступны и любые другие инструменты.
Рис. 1.6 – Логическая модель в MS Word
6
Рис. 1.7 – Логическая модель в MS Access
3. Таблицы на основе логической модели
Смоделируем таблицы на основе логической модели и исходных
данных. Дополним пустые поля в полученных таблицах произвольными данными. Эти действия помогут проанализировать полученную модель и увидеть ее недостатки, от которых нужно будет избавиться на следующих стадиях проектирования. Табл.1.1 будет
представлять собой таблицу «Дети» из логической модели.
Таблица 1.1
Дети
Имя
Фамилия
Маша
Иванова
Петя
Петров
Костя
Сидоров
Табл. 1.2 будет представлять собой таблицу «Фрукты».
Таблица 1.2
Фрукты
Название
Яблоко
Груша
В табл. 1.3 – «Фрукты у детей» – занесем внешние ключи из табл.
1.1 и 1.2. Для заполнения таблицы не обязательно решать исходную
задачу.
7
Таблица 1.3
Фрукты у детей
Имя
Фамилия
Фрукт
Количество
Маша
Иванова
Яблоко
5
Маша
Иванова
Груша
4
Петя
Петров
Яблоко
6
Петя
Петров
Груша
2
Костя
Сидоров
Яблоко
6
4. Задание
Цель работы:
Познакомиться с основой построения баз данных. Закрепить
знания о концептуальной и логической моделях базы данных. Научиться проектировать простые базы данных на основе имеющейся
информации. Закрепить полученный теоретический материал.
Формулировка задания:
1. Выбрать задание согласно варианту (ПРИЛОЖЕНИЕ А):
1.1. Для студентов очного отделения варианты заданий определяются преподавателем.
1.2. Для студентов заочного отделения варианты заданий определяются согласно номеру зачетной книжки/студенческого билета
как остаток от деления номера на 20. 0 соответствует 20 варианту.
2. Построить доступными средствами концептуальную модель
на основе пунктов 1 и 2 из задания. При наличии спорных моментов
построить по модели на каждый из вариантов.
3. Построить на основе имеющейся концептуальной модели логическую модель. При наличии нескольких концептуальных моделей выполнить операцию для каждой. Определить наиболее подходящую для поставленной задачи.
4. Сформировать заполненные таблицы (подразумеваются прототипы как в пункте 3, а не таблицы в базе данных) на основе логической модели с учетом количественных требований из задания.
5. Написать отчет.
Содержание отчета:
Отчет о лабораторной работе должен содержать:
1. Титульный лист, содержащий (образец присутствует на
сайте ВУЗа):
8
1.1. Полное название ВУЗа, кафедры, дисциплины и лабораторной работы.
1.2. Фамилию и инициалы студента, а также номер группы.
1.3. Информацию о преподавателе.
2. Цель работы.
3. Исходные данные согласно варианту.
4. Задание.
5. Концептуальную модель.
6. Логическую модель.
7. Заполненные таблицы.
8. Вывод.
9
Лабораторная работа 2.
Создание таблиц и заполнение их данными.
1. Базы данных, язык SQL и СУБД
База данных (БД) – представленная в объективной форме совокупность самостоятельных материалов, систематизированных таким образом, чтобы эти материалы могли быть найдены и обработаны с помощью электронной вычислительной машины. В общем
виде БД представляет собой совокупность связанных таблиц. Для
разработки БД и алгоритмов обработки данных, хранящихся в БД,
не редко используется язык SQL.
SQL (Structured Query Language) – универсальный компьютерный язык, применяемы для создания – DDL (Data Definition
Language), модификации – DML (Data Manipulation Language) и
управления – DCL (Data Control Language) данными в БД. Основывается на исчислении кортежей. Несмотря на существующий стандарт языка SQL, выделяется множество его диалектов. Это причина, по который не все тексты SQL-запросов можно с легкостью перенести из одной СУБД в другую. Далее в методических указаниях
будет использоваться Transact-SQL (T-SQL).
Система управления базами данных (СУБД) – совокупность
программных и лингвистических средств, обеспе5чивающих
управление созданием и использованием БД. Обычно термин СУБД
заменяют термином SQL-сервер. С диалектом T-SQL работают SQLсервера от Microsoft (Microsoft SQL Server – MS SQL) и Sybase
(Sybase Adaptive Server Enterprise – ASE, Sybase Adaptive Server
Anywhere – ASA).
Для выполнения лабораторных работ рекомендуется использование СУБД от Microsoft или Sybase. Несмотря на наличие в большинстве СУБД инструментов для администрирования БД, позволяющих создавать базы данных, таблицы и
различные запросы в режиме конструктора, для выполнения
лабораторных работ требуется самостоятельное осмысленное
составление запросов. Все приведенные примеры написаны на
языке T-SQL.
В упрощенном виде, БД создается следующим запросом:
CREATE DATABASE <database_name>
GO
10
где database_name – уникальное имя базы данных на данном
сервере, оператор GO сообщает SQL-серверу об окончании пакета
инструкций T-SQL и является необязательным.
Выбор БД выполняется запросом:
USE <database_name>
GO
где database_name – имя существующей на сервере БД.
Созданные таким образом БД в различных диалектах SQL и разновидностях СУБД могут отличаться. Особенности этих БД можно
узнать из документации к конкретной СУБД. Так, в качестве справочного пособия по T-SQL, рекомендуется использовать сайт «Сеть
разработчиков Microsoft» http://msdn.microsoft.com, содержащий
актуальную документацию по языку и СУБД MS SQL.
2. Физическая модель
Все данные в БД хранятся в таблицах. Каждая запись в таблице
(строка) четко структурирована. Структура задается при создании
таблице через порядок столбцов и их типы данных. Стандартные
типы данных T-SQL представлены в табл. 2.1. В самом языке присутствует еще несколько типов данных и возможность объявления
синонимов и пользовательских типов.
Таблица 2.1
Типы данных T-SQL
Название
Описание
Размерность
-263
Пример
bigint
Целое со знаком
binary[(n)]
Двоичные данные
фиксированной длины, по умолчанию 1
байт
1 .. 8000
-
bit
0 или 1
0 .. 1
0
char[(n)]
Строка длиной n символов, по умолчанию
1 символ
0 .. 8000
‘string’
Date
Дата
01/01/1753 ..
31/12/9999
‘2014-01-01’
Дата и время с учетом 01/01/1753 ..
микросекунд.
31/12/9999
‘2014-01-01
12:01:00.001’
datetime
..
263-1
-125
11
Окончание таблицы 2.1
decimal[(p[,s])]]
Вещественное, с фиксированной точкой
(p – значащие цифры,
s – после запятой)
p ≤ 38
s≤p
-25,41
float[(n)]
Вещественное с плавающей точкой
-1,79E+308 ..
1,79E+308
-25,41E15
image
Двоичные данные
переменной длины
231-1
-
money
Веществненное с фиксированной точкой с
4-мя знаками после
точки
-263 .. 263-1
25,4100
nchar
Строка в Юникоде
длиной n символов,
по умолчанию 1
символ
1 .. 4000
‘string’
ntext
Текстовые данные
переменной длины в
Юникоде
230-1
‘string’
numeric[(p[,s])]]
Вещественное, с фиксированной точкой
(p – значащие цифры,
s – после запятой)
p ≤ 38
s≤p
-25,41
nvarchar[(n)]
Строка в Юникоде
длиной от 1 до n символов, по умолчанию
1 символ
1 .. 4000
‘string’
real
Вещественное с плавающей точкой
-3,40E+38 ..
3,40E+38
-25,41E15
smalldatetime
Дата и время с точностью до минут.
01/01/1900 ..
06/06/2079
‘2014-01-01
12:01:00’
smallint
Целое со знаком
-215 .. 215-1
-25,41
text
Текстовые данные
переменной длины
231-1
‘string’
time
Время
00:00:00.000
..
23:59:59.999
’12:01:00.001’
tinyint
Целое без знака
0 .. 255
125
varbinary[(n)]
Двоичные данные
произвольной длины
1 .. 8000
-
varchar[(n)]
Строка длиной от 1 до
n символов, по умолчанию 1 символ
1 .. 8000
‘string’
12
На основе имеющихся типов данных в диалекте SQL и конкретной СУБД, а также имеющейся логической модели (рис. 1.5 – 1.7)
можно сделать выводы о структуре конечной БД и построить ее физическую модель. На рис. 2.1 представлен промежуточный вариант
такой модели.
Рис. 2.1 – Физическая модель, промежуточный вариант
На данном этапе присутствует дублирование данных, ставящее
под сомнение полученную модель. Чтобы устранить возникшую
проблему, необходимо обратиться к методом создания таблиц.
3. Создание, удаление, изменение таблиц
Упрощенный вариант создания таблицы:
CREATE TABLE <table_name> (
<column_name> <column_type>[, …]
)
GO
где table_name – уникальное имя таблицы в текущей БД,
column_name – уникальное имя столбца в текущей таблице с
типом данных column_type. Подобным образом описывается необходимое количество столбцов, разделенных запятой. Если имя таблицы или какого-либо из столбцов содержит пробелы, его необходимо заключать в квадратные скобки.
Пример, таблица «Количество фруктов»:
CREATE TABLE [Фрукты у детей] (
Имя varchar(20),
Фамилия varchar(20),
Фрукт varchar(20),
Количество int
)
GO
13
В данном примере будет создана таблица с четырьмя столбцами,
где первые три хранят строки длиной до 20 символов (приставка var
говорит о динамическом выделении памяти под строку), последний –
числа. При этом допустимо хранение пустых (NULL) ячеек. Запретить это можно добавив после типа данных NOT NULL:
CREATE TABLE [Фрукты у детей](
Имя varchar(20) NOT NULL,
Фамилия varchar(20) NOT NULL,
Фрукт varchar(20) NOT NULL,
Количество int
)
GO
Такой вариант написания не позволит оставить поля Имя, Фамилия и Фрукт пустыми с точки зрения языка SQL.
Кроме этого при создании таблицы в определениях столбцов
можно использовать различные директивы, определяющие поведение определенных столбцов:
1. [NOT] NULL – разрешение или запрет на пустые поля (по умолчанию NULL);
2. DEFAULT <значение> – значение по умолчанию;
3. CHECK <условие> – условие проверки;
4. UNIQUE – требование к уникальности записи в столбце;
5. PRIMARY KEY – определение первичного ключа таблицы для
однозначной идентификации (первичный ключ по определению
уникален);
6. IDENTITY [(<начальное значение>,<приращение>)] – определение поля-счетчика с начальным значением и шагом, по умолчанию начиная с 1 с шагом 1.
Примеры использования некоторых директив:
CREATE TABLE Фрукты(
ID int IDENTITY PRIMARY KEY,
Название varchar(20) NOT NULL,
)
CREATE TABLE [Фрукты у детей](
Имя varchar(20) NOT NULL,
Фамилия varchar(20) NOT NULL,
Фрукт varchar(20) NOT NULL,
Количество int DEFAULT 0
CHECK (Количество >= 0)
)
GO
Здесь ID – первичный ключ (суррогатный ключ) таблицы, Имя,
Фамилия, Фрукт – поля, которые нельзя оставить незаполненны14
ми, Количество – поле, значение которого больше 0, принимает значение 0, если оно не задано.
Суррогатный ключ – простое числовое поле, в которое записываются значения из возрастающей числовой последовательности.
В ряде диалектов SQL – специальный тип данных, поддерживаемый СУБД, в T-SQL используется директива IDENTITY для типа
INT. Используется в качестве первичного ключа. Обладает следующими свойствами:
− неизменен – не несет информации из предметной области, следовательно, не зависит от изменений в ней;
− уникален – не теряет уникальность при различных обстоятельствах в отличии от естественного ключа;
− гибок – свободно заменяем при необходимости;
− эффективен – ссылки (внешние ключи) хранятся в виде чисел,
а не громоздких естественных ключей;
− удобен при программировании – позволяет сократить запросы
из внешних источников;
− неинформативен – усложняет проверку БД, добавляя лишние
запросы к нескольким таблицам или объединения (декартово произведение полей таблиц);
− частично заменяет нормализацию – использование суррогатного ключа проще, чем приведение к связям один-комногим;
− затрудняет оптимизацию – необходимость использование дополнительного индекса;
− привязка к поведению генератора ключа – в зависимости от реализации и настроек в некоторых СУБД уникальные ключи не являются последовательными.
Первичный ключ – основной ключ таблицы, зачастую совпадает
с кластерным индексом. Может быть простым (один столбец) или
составным (несколько столбцов). Составной ключ объявляется после перечисления столбцов следующим образом:
CREATE TABLE Дети(
Имя varchar(20) NOT NULL,
Фамилия varchar(20) NOT NULL,
PRIMARY KEY (Имя, Фамилия)
)
GO
Здесь Имя и Фамилия не могут быть ключами по отдельности.
При этом уникальность поддерживается именно для связки Имя,
Фамииля, а не для каждого столбца в отдельности.
15
Внешний ключ – набор столбцов в таблице, значения которого
должно входить в подмножество значений аналогичных столбцов в
другой таблице (потенциальный или внешний ключ). Используется для обеспечения ссылочной целостности. Создается директивой:
FOREIGN KEY (<columns>)
REFERENCES <table_name> (<columns>)
где columns – имена столбцов, table_name – имя внешней таблицы.
Пример:
CREATE TABLE [Фрукты у детей](
Имя varchar(20) NOT NULL,
Фамилия varchar(20) NOT NULL,
Фрукт varchar(20) NOT NULL,
Количество int,
FOREIGN KEY (Имя, Фамилия)
REFERENCES Дети(Имя, Фамилия),
FOREIGN KEY (Фрукт)
REFERENCES Фрукты(Название)
)
GO
Здесь связка Имя, Фамилия будет ссылаться на таблицу Дети,
а Фрукт – на таблицу Фрукты.
Перестроим физическую модель, используя суррогатные ключи.
Результат можно увидеть на рис. 2.2.
Рис. 2.2 – Физическая модель
Чтобы реализовать данную модель на языке SQL, необходимо
удалить существующие таблицы из базы данных оператором:
DROP TABLE <table_name>
где table_name – имя удаляемой таблицы.
Пример, удаление таблицы Фрукты у детей:
DROP TABLE [Фрукты у детей]
GO
Не обязательно для каждого изменения структуры базы данных
выполнять удаление существующих таблиц с их последующим созданием. Язык SQL позволяет выполнять обновление уже существующих таблиц. Для данной операции используется оператор:
16
ALTER TABLE <table_name>
где table_name – имя обновляемой таблицы. Удалим из таблицы
Фрукты первичный ключ:
ALTER TABLE Фрукты DROP PRIMARY KEY
GO
Добавим в таблицу Фрукты суррогатный ключ ID:
ALTER TABLE Фрукты ADD ID int IDENTITY
PRIMARY KEY
GO
Модификация таблиц не останавливается на добавлении столбцов и удалении ключей. Для ознакомления с полным списком возможностей рекомендуется обратиться к документации по выбранным диалекту SQL и СУБД.
4. Вставка данных в таблицы
Прежде чем начать заполнять таблицы данными, необходимо
определиться с порядком. Наличие внешних ключей требует, чтобы при заполнении дочерней таблицы главная таблица была уже заполнена. Поэтому порядок заполнения таблиц из примера должен
быть следующим: сначала таблицы Дети и Фрукты (в любом порядке) и только потом Фрукты у детей.
Вставка данных осуществляется следующим оператором:
INSERT INTO <table_name> (<colums>)
VALUES (<values>)
где table_name – имя заполняемой таблицы, columns – список столбцов, values – список значений. Порядок значений соответствует порядку столбцов. Если заполняются все столбцы, их список можно пропустить. Если таблица содержит суррогатный ключ, список
столбцов (без суррогатного ключа) необходим.
Удаление существующих записей из таблицы осуществляется
оператором:
DELETE FROM <table_name>
[WHERE <conditions>]
где table_name – имя таблицы, из которой удаляются записи,
conditions – список условий. Если не перечислены условия, произойдет удаление всех записей из таблицы.
Изменение записей в таблице осуществляется оператором:
UPDATE <table_name> SET <column>=<value>
[WHERE <conditions>]
17
где table_name – имя таблицы, в которой изменяются записи, связка
column-value устанавливает значение в указанный столбец, может
повторяться через запятую, conditions – список условий. Если не перечислены условия, произойдет изменение всех записей в таблице.
Все эти операторы входя в стандарт языка SQL, но имеют небольшие различия в написании. Подробное описание операторов
INSERT, DELETE, UPDATE можно найти в документации по выбранным диалекту SQL и СУБД.
5. Запросы на создание БД из примера
CREATE TABLE Дети(
ID int IDENTITY PRIMARY KEY,
Имя varchar(20) NOT NULL,
Фамилия varchar(20) NOT NULL
)
CREATE TABLE Фрукты(
ID int IDENTITY PRIMARY KEY,
Название varchar(20) NOT NULL
)
CREATE TABLE [Фрукты у детей](
ID int IDENTITY PRIMARY KEY,
Ребенок int NOT NULL,
Фрукт int NOT NULL,
Количество int DEFAULT 0
CHECK (Количество >= 0),
FOREIGN KEY (Ребенок) REFERENCES Дети(ID),
FOREIGN KEY (Фрукт) REFERENCES Фрукты(ID)
)
GO
INSERT INTO Дети (Имя, Фамилия)
VALUES (‘Маша’,’Иванова’)
INSERT INTO Дети (Имя, Фамилия)
VALUES (‘Петя’,’Петров’)
INSERT INTO Дети (Имя, Фамилия)
VALUES (‘Костя’,’Сидоров’)
GO
INSERT INTO Фрукты (Название) VALUES (‘Яблоко’)
INSERT INTO Фрукты (Название) VALUES (‘Груша’)
GO
INSERT INTO [Фрукты у детей]
(Ребенок, Фрукт, Количество) VALUES (1,1,5)
INSERT INTO [Фрукты у детей]
(Ребенок, Фрукт, Количество) VALUES (1,2,4)
INSERT INTO [Фрукты у детей]
(Ребенок, Фрукт, Количество) VALUES (2,1,6)
INSERT INTO [Фрукты у детей]
(Ребенок, Фрукт, Количество) VALUES (2,2,2)
18
INSERT INTO [Фрукты у детей]
(Ребенок, Фрукт, Количество) VALUES (3,1,6)
GO
6. Задание
Цель работы:
Познакомиться с основой создания БД на языке SQL. Познакомиться с понятием физическая модель БД. Создать и заполнить
данными спроектированную БД. Закрепить полученный теоретический материал.
Формулировка задания:
1. Взять задание из предыдущей лабораторной работы.
2. Построить доступными средствами физическую модель БД
с использование первичных, суррогатных и внешних ключей.
3. Составить запросы на создание БД.
4. Составить запросы на заполнение БД согласно требованиям из
задания, пункты 1-3.
5. Написать отчет.
Содержание отчета:
Отчет о лабораторной работе должен содержать:
1. Титульный лист, содержащий (образец присутствует на сайте
ВУЗа):
1.1. Полное название ВУЗа, кафедры, дисциплины и лабораторной работы.
1.2. Фамилию и инициалы студента, а также номер группы.
1.3. Информацию о преподавателе.
2. Цель работы.
3. Исходные данные согласно варианту.
4. Задание.
5. Физическую модель.
6. Запросы на создание БД и таблиц в БД.
7. Запросы на заполнение БД данными.
8. Заполненные таблицы.
9. Вывод.
19
Лабораторная работа 3.
Решение исходной задачи.
1. Выборка
Для просмотра данных в таблице используется оператор:
SELECT * FROM <table_name>
где table_name – имя таблицы. Данный пример выведет все содержимое таблицы. При необходимости вывода определенных столбцов запрос принимает следующий вид:
SELECT <columns> FROM <table_name>
где colums – список столбцов. Так же поддерживаются различные
варианты условий WHERE:
1. Операторы сравнения =, <, <=, >, >=, !=;
2. [NOT] IN – вхождение в набор;
3. [NOT] LIKE – неточное сравнение строк (% в условии – любые
символы, _ – любой символ).
Поддерживаются условия HAVING для работы с агрегатными
функциями. Директивы GROUP BY для группировок и ORDER BY
для сортировки. Некоторые конструкции в запросе соответствуют
стандарту SQL, некоторые являются специфическими или имеют
специфическое написание в диалекте SQL.
Кроме перечисленного выше существуют еще вложенные запросы, запросы к нескольким таблицам, так называемые объединения –
JOIN. Подробное их описание можно найти в документации к диалекту SQL и СУБД. Необходимый минимум для выполнения лабораторных работ будет приведен далее.
2. Переменные
Наличие различных конструкций не позволяет использовать все
желаемые операции над информацией. Время от времени для упрощения сложных конструкций, обхода ограничений отдельных диалектов SQL и СУБД необходимо использовать переменные. Данная
конструкция присутствует в стандарте SQL.
Переменные объявляются конструкцией:
DECLARE @<name> <type>
где name – имя переменной (начинается с символа “), type – тип
данных.
20
Значения переменных устанавливаются оператором:
SET @<name> = <value>
где name – имя переменной, value – значение или его источник, например, запрос.
3. Решение исходной задачи с помощью выборки
Исходная задача: «У Маши пять яблок и четыре груши. У Пети
на одно яблоко больше и на две груши меньше, чем у Маши. У Кости столько яблок, сколько груш у Маши и Пети вместе, и нет груш.
Сколько яблок и груш у каждого из ребят?». Основываясь на разработанной в предыдущей лабораторной БД сформируем запросы:
1. Занесем Машу в БД и добавим ей пять яблок и четыре груши.
Пусть фамилия у Маши будет Иванова (ID = 1), яблоки (ID = 1)
и груши (ID = 2) уже занесены в таблицу Фрукты:
INSERT INTO [Фрукты у детей]
(Ребенок, Фрукт, Количество) VALUES (1,1,5)
INSERT INTO [Фрукты у детей]
(Ребенок, Фрукт, Количество) VALUES (1,2,4)
GO
2. Пусть фамилия у Пети – Петров (ID = 2). Объявим переменные
(а – яблоки, и – груши), в которых будем хранить промежуточные
значения, выполним расчеты и запишем результат в таблицу:
DECLARE @a int
SET @a = (
SELECT Количество
FROM [Фрукты у детей]
WHERE Ребенок = 1 AND Фрукт = 1
)
DECLARE @b int
SET @b = (
SELECT Количество
FROM [Фрукты у детей]
WHERE Ребенок = 1 AND Фрукт = 2
)
INSERT INTO [Фрукты у детей]
(Ребенок, Фрукт, Количество)
VALUES (2,1,@a+1)
INSERT INTO [Фрукты у детей]
(Ребенок, Фрукт, Количество)
VALUES (2,2,@b-2)
GO
3. Пусть фамилия у Кости – Сидоров (ID = 3). Воспользуемся объявленными ранее переменными (a – груши у Пети), выполним расчеты и запишем результат в таблицу:
21
SET @a = (
SELECT Количество
FROM [Фрукты у детей]
WHERE Ребенок = 2 AND Фрукт = 2
)
INSERT INTO [Фрукты у детей]
(Ребенок, Фрукт, Количество)
VALUES (2,1,@a+@b)
GO
4. Теперь в таблице «Фрукты у детей» хранится ответ на вопрос
«Сколько яблок и груш у каждого из ребят?». Напишем запрос, превращающий содержимое таблицы «Фрукты у детей» и таблиц
«Дети» и «Фрукты» в удобный для восприятия вид (табл. 3.1):
SELECT Ребенок.Имя AS Имя,
Фрукты.Название AS Фрукт,
[Фрукты у детей].Количество AS Количество
FROM Ребенок, Фрукты, [Фрукты у детей]
WHERE [Фрукты у детей].Ребенок = Дети.ID
AND [Фрукты у детей].Фрукт = Фрукты.ID
ORDER BY Ребенок.Имя
GO
Таблица 3.1
Сколько яблок и груш у каждого из ребят?
Имя
Фрукт
Количество
Маша
Яблоко
5
Маша
Груша
4
Петя
Яблоко
6
Петя
Груша
2
Костя
Яблоко
6
4. Задание
Цель работы:
Научиться формулировать вопросы на языке SQL в виде выборки. Научиться работать с переменными и сложными запросами.
Научиться решать математические задачи с помощью языка SQL.
Закрепить полученный теоретический материал.
Формулировка задания:
1. Взять задание из предыдущих лабораторных работ.
2. Составить запросы на языке SQL, выполняющие исходные
условия.
22
3. Составить запрос на языке SQL на вывод ответа.
4. Написать отчет.
Содержание отчета:
Отчет о лабораторной работе должен содержать:
1. Титульный лист, содержащий (образец присутствует на сайте ВУЗа):
1.1. Полное название ВУЗа, кафедры, дисциплины и лабораторной работы.
1.2. Фамилию и инициалы студента, а также номер группы.
1.3. Информацию о преподавателе.
2. Цель работы.
3. Исходные данные согласно варианту.
4. Задание.
5. Запросы на выполнение условий задачи.
6. Запросы на вывод ответа.
7. Ответ.
8. Вывод.
23
Лабораторная работа 4.
Использование хранимых процедур.
1. Хранимые процедуры
Хранимые процедуры – набор команд, хранящихся в БД в откомпилированном виде. Использование хранимых процедур позволяет снизить нагрузку на прикладные программы, работающие
с БД (преобразование типов данных, формирование подзапросов),
уменьшить объем передаваемых данных, увеличить скорость выполнения, повысить безопасность БД за счет ограничения доступа
к обрабатываемой информации и ее структуре.
Хранимые процедуры создаются оператором:
CREATE PROC[EDURE] <proc_name>[;<nr>]
[<params>]
AS
<sql>
GO
Где proc_name – имя процедуры, nr – номер (для одноименных
процедур), sql – операторы на языке SQL (тело процедуры), params –
список параметров вида (name – имя параметра, type – тип данных,
def_val – значение по умолчанию):
@<name> <type> [= <def_val>] [OUTPUT]
Вызывается процедура командой (имена полей соответствуют
именам в создании):
[EXEC[UTE]] <proc_name>[;<nr>]
[<params>]
EXECUTE необходим, если в пакете выполняются какие-либо
действия кроме вызова самой процедуры.
Подробную документацию по хранимым процедурам можно найти в справочном руководстве по выбранным диалекту SQL и СУБД.
Пример: Создадим хранимую процедуру для добавления в исходные таблицы запись о количестве фруктов у ребенка. Предусмотрим, что ребенка могло не быть в базе данных (его нужно добавить), что фрукт ни разу не встречался (его нужно добавить), что такой фрукт уже есть у данного ребенка (необходимо увеличить количество).
CREATE PROCEDURE [Добавим фрукт]
@fname varchar(20),
@lname varchar(20),
@fruit varchar(20),
24
@count int
AS
BEGIN
DECLARE @childid int
DECLARE @fruitid int
IF ((SELECT COUNT(*) FROM Дети
WHERE Имя = @fname
AND Фамииля = @lname)=0)
INSERT INTO Дети (Имя, Фамилия)
VALUES (@fname,@lname)
SET @childid = (SELECT ID FROM Дети
WHERE Имя = @fname
AND Фамииля = @lname)
IF ((SELECT COUNT(*) FROM Фрукты
WHERE Название = @fruit)=0)
INSERT INTO Фрукты (Название)
VALUES (@fruit)
SET @fruitid = (SELECT ID FROM Фрукты
WHERE Название = @fruit)
IF ((SELECT COUNT(*) FROM [Фрукты у детей]
WHERE Ребенок = @childid
AND Фрукт = @fruitid)=0)
INSERT INTO [Фрукты у детей]
(Ребенок, Фрукт, Количество)
VALUES (@childid, @fruitid, @count)
ELSE
UPDATE [Фрукты у детей]
SET Количество=Количество+@count
WHERE Ребенок = @childid
AND Фрукт = @fruitid
END
GO
2. Задание
Цель работы:
Научиться строить и использовать хранимые процедуры. Научиться группировать имеющиеся запросы в хранимые процедуры.
Закрепить полученный теоретический материал.
Формулировка задания:
1. Взять задание из предыдущих лабораторных работ.
2. Написать запросы для решения вопросов из пункта
5 задания.
3. Написать хранимые процедуры, отвечающие на вопросы.
4. Написать отчет.
Содержание отчета:
Отчет о лабораторной работе должен содержать:
25
1. Титульный лист, содержащий (образец присутствует на сайте
ВУЗа):
1.1. Полное название ВУЗа, кафедры, дисциплины и лабораторной работы.
1.2. Фамилию и инициалы студента, а также номер группы.
1.3. Информацию о преподавателе.
2. Цель работы.
3. Исходные данные согласно варианту.
4. Задание.
5. Разработанные хранимые процедуры с комментариями.
6. Вывод.
26
Лабораторная работа 5.
Использование триггеров для решения задачи.
1. Триггеры
Триггер – это разновидность хранимой процедуры, которая вызывается автоматически при вставке, обновлении или удалении
данных из некоторой таблицы, и не имеет входных и выходных параметров. Основное назначение триггеров – поддержка целостности
данных.
Триггеры – не единственный механизм для поддержания целостности. В качестве примеров можно привести параметр CHECK и каскадные обновления и удаления. Кроме этого некорректное использование триггеров, ошибочные циклические или рекурсивные вызовы триггеров не только не гарантируют целостность, но и приводят к сбоям в работе СУБД с конкретной БД.
Существует три типа поведения триггеров:
1. BEFORE – триггер выполняется перед событием (не поддерживается T-SQL);
2. INSTEAD OF – триггер выполняется вместо события;
3. AFTER – триггер выполняется после события.
Существует три типа триггеров:
1. INSERT – триггер, срабатывающий при добавлении;
2. DELETE – триггер, срабатывающий при удалении;
3. UPDATE – триггер, срабатывающий при обновлении.
Если происходит удаление или изменение данных, СУБД создает временную таблицу DELETED, хранящую удаленные или исходные данные. Если происходит добавление или изменение данных,
СУБД создает временную таблицу INSERTED, хранящую добавленные или измененные данные.
Триггер создается оператором:
CREATE TRIGGER <trigger_name>
ON <table_name>
{AFTER | INSTEAD OF}
{[INSERT][,][UPDATE][,][DELETE]}
AS
<sql>
GO
где trigger_name – уникальное для таблицы имя триггера, table_name –
имя таблицы, sql – оператор или операторы на языке SQL (тело триггера).
27
Прервать работу триггера с отменой всех изменений можно оператором:
ROLLBACK TRANSACTION
Существует так же возможность обновлять и удалять триггеры.
Операторы для этого аналогичны оператором для таблиц.
Пример: создадим триггер, запрещающий удалять записи из таблицы Фрукты, если они используются в таблице Фрукты у детей.
Данный триггер должен срабатывать перед или вместо оператора
удаления. С учетом особенностей T-SQL, триггер будет INSTEAD OF
DELETE.
CREATE TRIGGER [Проверка фруктов]
ON Фрукты
INSTEAD OF
DELETE
AS
IF ((SELECT COUNT(*) FROM [Фрукты у детей]
WHERE Фрукт IN
(SELECT Название FROM DELETED))>0)
ROLLBACK TRANSACTION
ELSE
DELETE FROM Фрукты
WHERE Название IN
(SELECT Название FROM DELETED)
GO
2. Задание
Цель работы:
Научиться строить и использовать триггеры для обеспечения целостности и решения дополнительных задач. Закрепить полученный теоретический материал.
Формулировка задания:
1. Взять задание из предыдущих лабораторных работ.
2. Составить триггеры, обеспечивающие целостность БД.
3. Выполнить требования из пункта 4 задания.
4. Написать отчет.
Содержание отчета:
Отчет о лабораторной работе должен содержать:
1. Титульный лист, содержащий (образец присутствует на сайте
ВУЗа):
1.1. Полное название ВУЗа, кафедры, дисциплины и лабораторной работы.
1.2. Фамилию и инициалы студента, а также номер группы.
28
1.3. Информацию о преподавателе.
2. Цель работы.
3. Исходные данные согласно варианту.
4. Задание.
5. Триггеры, обеспечивающие целостность с комментариями.
6. Запросы и триггеры по пункту 4 задания.
7. Вывод.
29
Лабораторная работа 6.
Отчеты при решении задач.
1. Задание
Цель работы:
Научиться связывать БД с прикладными интерфейсами. Научиться использовать запросы и хранимые процедуры через прикладные интерфейсы. Научиться формировать отчеты на основе
вопросов и имеющихся данных. Закрепить полученный теоретический материал.
Формулировка задания:
1. Взять задание из предыдущих лабораторных работ.
2. Написать прикладную программу для формирования отчетов по пункту 5 задания или воспользоваться встроенными в СУБД
средствами построения отчетов.
3. Написать отчет.
Содержание отчета:
Отчет о лабораторной работе должен содержать:
1. Титульный лист, содержащий (образец присутствует на сайте
ВУЗа):
1.1. Полное название ВУЗа, кафедры, дисциплины и лабораторной работы.
1.2. Фамилию и инициалы студента, а также номер группы.
1.3. Информацию о преподавателе.
2. Цель работы.
3. Исходные данные согласно варианту.
4. Задание.
5. Выбор методики решения.
6. Сформированные отчеты.
7. Вывод.
30
Библиографический список
1. Астахова И.Ф. СУБД: язык SQL в примерах и задачах / И. Ф.
Астахова, В. М. Мельников, А. П. Толстобров, В. В. Фертиков М.:
ФИЗМАТЛИТ, 2009. 168 с.
2. Дейт К. Дж. Введение в системы баз данных./Пер. с англ. 8-е изд. М.:
Вильямс, 2005. 1138 с.
3. Хомоненко А. Д. Базы данных: учебник для высших учебных заведений/ А. Д. Хомоненко, В. М. Цыганков, М. Г. Мальцев ;
ред. А. Д. Хомоненко. 6-е изд., доп. и перераб.. СПб.: КОРОНАВек, 2010. 736 с.
4. Справочник по Transact-SQL // Microsoft Developer Network
[Электрон. ресурс]. Режим доступа: http://msdn.microsoft.com/ruru/library/bb510741.
31
ПРИЛОЖЕНИЕ А.
Варианты заданий.
Пример
1. Задача:
У Маши пять яблок и четыре груши. У Пети на одно яблоко больше и на
две груши меньше, чем у Маши. У Кости столько яблок, сколько груш у Маши и Пети вместе, и нет груш. Сколько яблок и груш у каждого из ребят?
2. Требования к БД:
2.1. Виды фруктов не должны ограничиваться яблоками и грушами. Предусмотреть добавление новых видов фруктов.
2.2. Предусмотреть вероятность того, что будет несколько ребят
с одинаковыми именами.
2.3. Добавить не менее одной отличительной особенности для ребят в соответствующую таблицу.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Добавить не менее десяти ребят.
3.2. Добавить не менее десяти фруктов.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
3.5. Раздать остальным имеющиеся фрукты.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, хранящую информацию
по общему количеству фруктов у ребят.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. У кого больше всего фруктов?
5.2. У кого больше всего фруктов каждого вида?
5.3. Сколько каждого вида фруктов в среднем приходится на
одного человека?
Вариант 1
1. Задача:
Лена, Катя, Витя и Саша ходили в лес за грибами. Лена принесла домой 5 подберезовиков, 6 подосиновиков и один боровик. Катя
32
не нашла подосиновиков, зато боровиков у нее было в 3 раза больше,
чем у Лены, а подберезовиков всего на 2 больше. Витя принес домой столько подберезовиков, сколько Катя и Лена вместе, а остальных грибов по одному. Сколько каких грибов принес Саша, если
известно, что всего они нашли 30 подберезовиков, 10 подосиновиков и 6 боровиков?
2. Требования к БД:
2.1. Виды грибов не должны ограничиваться подберезовиками,
подосиновиками и боровиками. Предусмотреть добавление новых
видов грибов.
2.2. Предусмотреть вероятность того, что ребята каждый день
ходят за грибами.
2.3. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Добавить не менее десяти ребят.
3.2. Добавить не менее пяти видов грибов.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
3.5. Увеличить количество походов в лес минимум на четыре.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, хранящую информацию
по общему количеству грибов, собранных за день.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. Кто в какой день принес больше всего грибов?
5.2. У кого больше всего собрано грибов каждого вида за весь
период?
5.3. Сколько каждого вида грибов в среднем приходится на одного
человека?
Вариант 2
1. Задача:
Три шахматиста играли в шахматы. Первый выиграл у второго
11 партий. Второй выиграл у третьего 9 партий. Третий выиграл у
первого на 2 партии меньше, чем проиграл второму партий. Сколько всего партий сыграли шахматисты была всего одна партия в ничью между первым и третьим?
33
2. Требования к БД:
2.1. Шахматисты не должны быть безликими. Добавить каждому шахматисту несколько индивидуальных признаков.
2.2. Шахматистов может быть больше трех.
2.3. Необходимо считать не только победы шахматистов, но и поражения, и общее количество сыгранных партий.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Добавить не менее пяти шахматистов.
3.2. Вести рейтинг на протяжении всех сыгранных партий.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, победителя каждого дня.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. Кто в какой день играл меньше всего?
5.2. У кого больше всего ничьих за весь период?
5.3. Каково соотношение побед и поражений у каждого?
Вариант 3
1. Задача:
Девочки собирали ракушки на пляже. Лена нашла 5 больших
и 3 маленьких ракушки. Маша нашла столько маленьких, сколько Лена всего, больших же было в 2 раза меньше. Ксюша собирала только маленькие ракушки, у нее их набралось на пять больше,
чем всех ракушек у Лены и Маши. Катя собрала в 2 раза меньше
больших ракушек, чем остальные вместе, и столько же маленьких.
Сколько каких ракушек было собрано?
2. Требования к БД:
2.1. Девочки могут ходить на пляж не только за ракушками, но и
за красивыми камушками.
2.2. Девочек, собирающих ракушки и камушки больше четырех.
2.3. Девочки почти каждый день ходят на пляж.
2.4. Не все хранят то, что собрали.
2.5. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
34
3. Требования к хранимой информации:
3.1. Добавить не менее пяти девочек.
3.2. Отслеживать, у кого сколько ракушек и камушков дома.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, показывающую, сколько человек в какой день ходило на пляж.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. Кто выкинул больше всего ракушек и камушков?
5.2. У кого камушков больше, чем ракушек?
5.3. Кто каждый день собирает больше всего больших ракушек?
Вариант 4
1. Задача:
На юг летели три стаи уток. В каждой было по 30 уток. В первой
селезней было 15. Во второй на 4 меньше, чем в первой. В третьей на
одного меньше, чем в среднем в двух предыдущих. Каково соотношение уток к селезням в среднем?
2. Требования к БД:
2.1. В стаях присутствуют молодые особи (выводок этого года).
2.2. Утки летят на юг каждый год.
2.3. Обычно стай летит больше трех.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Увеличить количество стай.
3.2. Отследить миграцию за предыдущие пять лет.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, сколько молодняка выводят каждый год.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. В какой год популяция уток достигла максимального значения?
35
5.2. Больше или меньше уток в этом году, чем пять лет назад?
5.3. Какое соотношение молодых уток к взрослым за последний год?
Вариант 5
1. Задача:
За последние четыре дня выпало 12 мм осадков. В первый день
выпало 2 мм осадков. Во второй день на 2 мм больше. В третий день
осадков не было. Сколько осадков выпало в четвертый день?
2. Требования к БД:
2.1. Осадки необходимо учитывать ежедневно.
2.2. В разных регионах количество осадков разное.
2.3. Температура воздуха бывает как положительной, так и отрицательной.
2.4. Предположим, что при отрицательной температуре воздуха
выпадает снег.
2.5. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Ввести вид осадков (минимальный вариант дождь-снег).
3.2. Отследить количество за месяц в нескольких регионах.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, хранящую процентное
соотношение осадков за день по регионам.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. В какой день общее количество осадков было максимальным?
5.2. Какое соотношение осадков (например, дождь и снег) было
за последний месяц?
5.3. Сколько дней в каждом регионе не было осадков?
Вариант 6
1. Задача:
В классе учится 22 человека. Половина из них написали последнюю контрольную на оценку 4. Если бы оставшихся было на одного
36
человека больше, то можно было бы сказать, что треть из них не сдали работу. На одного человека больше получили оценку 5. Сколько
человек получило оценку 3?
2. Требования к БД:
2.1. В школе учится не один класс.
2.2. Периодически каждый класс пишет контрольную по какому-либо предмету.
2.3. Для каждого класса хранится средний балл.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Провести анализ успеваемости (по контрольным работам) за
последнюю четверть.
3.2. Выделить количество отличников в каждом классе.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, хранящую средний бал
по каждому предмету.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. Какой предмет оказался самым простым?
5.2. Какой класс хуже всего справляется по каждой дисциплине?
5.3. Сколько человек не получило положительных оценок за последнюю четверть?
Вариант 7
1. Задача:
В отборочных соревнованиях по бегу приняли участие 15 человек. Пятая часть из них пробежала дистанцию менее чем за 200 секунд. Четверть из оставшихся уложилась в интервал от 200 до 230
секунд.
Один пробежал за 231 секунду, остальные не уложились и в это
время.
Сколько человек прошло отборочный тур, если расчетное время
было 231 секунда?
2. Требования к БД:
2.1. Бег бывает на разные дистанции.
37
2.2. Один спортсмен может принимать участие в нескольких видах соревнований.
2.3. Ведется список спортсменов, принимающих участие не менее чем в трех видах соревнований.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Ведение рейтинга успешных отборочных туров для каждого
спортсмена.
3.2. Хранение информации только за последний сезон.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, хранящую соотношение
количества непрошедших к общему количеству участников для
каждого вида соревнований.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. В каком виде соревнований проходит более половины участников?
5.2. В каком виде соревнований никто не прошел отборочный тур?
5.3. Сколько человек прошло отборочный тур более чем в трех
видах соревнований?
Вариант 8
1. Задача:
Вася каждый день по дороге в школу считает птиц на деревьях.
На первом дереве он насчитал 3 вороны и 15 воробьев. На втором на
одну ворону больше и на 3 воробья меньше. На третьем количество
ворон и воробьев было обратным ко второму. На четвертом ворон
было в два раза меньше, чем на втором, а воробьев в два раза больше, чем на третьем. Сколько ворон насчитал Вася?
2. Требования к БД:
2.1. Вася считает птиц каждый день, когда идет в школу.
2.2. Деревьев у него на пути намного больше четырех.
2.3. Вася принципиально не считает всех остальных птиц.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
38
3. Требования к хранимой информации:
3.1. Выделение деревьев, где соотношение воробьев к воронам
больше чем 5 к 1.
3.2. Хранение информации за последние 10 дней.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, хранящую среднее соотношение ворон и воробьев за каждый день.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. На каких деревьях сидят только вороны или только воробьи?
5.2. Сколько деревьев без птиц Вася видел за последний день?
5.3. Как изменилась популяция ворон за последние 10 дней?
Вариант 9
1. Задача:
Во время вырубки леса обнаружили несколько деревьев с зимними запасами. В дупле одного было прятано 20 желудей и 15 шишек.
Внутри другого на 12 желудей больше и в 3 раза меньше шишек,
чем в первом. В третьем в 2 раза меньше желудей и в два раза больше шишек, чем в первом. Сколько желудей было обнаружено всего?
2. Требования к БД:
2.1. Не во всех деревьях находят запасы.
2.2. Деревьев с запасами больше трех.
2.3. Лесные животные могут запасать не только желуди и шишки.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Хранение информации о каждом дереве.
3.2. Хранение информации о нескольких вырубках.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, хранящую соотношение
найденных запасов для каждой вырубки.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
39
5. Вопросы для создания хранимых процедур и отчетов:
5.1. Есть ли деревья, где запасены только желуди?
5.2. Что больше любят запасать лесные животные?
5.3. Сколько деревьев в каждой вырубке было без запасов?
Вариант 10
1. Задача:
Миша стал считать, сколько звезд на небе становится видно каждый день в течение пяти и десяти минут после появления первой.
В один день он насчитал 7 звезд за 5 минут и в 3 раза больше за
10. В другой, за 5 минут, на одну меньше, чем в первый, и за 10 –
на 3 больше. В третий, в первые 5 минут, половину от количества
звезд за 10 минут предыдущего дня и в два раза больше за 10 минут.
Сколько всего звезд насчитал за эти дни Миша, загоревшихся после
первых пяти минут?
2. Требования к БД:
2.1. Хранить подсчеты Миши более чем за три дня.
2.2. Увеличит время, которое тратит Миша на подсчеты еще на
5 минут.
2.3. Учесть облачную погоду.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Хранение информации о каждом дне.
3.2. Хранение информации о каждых пяти минутах.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, звезд за 5 и 10 минут на
каждый ясный день.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. Сколько дней за последнюю неделю наблюдения за звездами
были невозможны?
5.2. Возросло или уменьшилось число звезд, которые Миша замечает каждый день за 10 минут?
5.3. В среднем больше звезд загорается за первые и вторые
5 минут?
40
Вариант 11
1. Задача:
Лена, Катя, Витя и Саша ходили в лес за грибами. Лена принесла домой 4 подберезовика, 6 подосиновиков и один боровик. Катя не
нашла подосиновиков, зато боровиков у нее было в 3 раза больше,
чем у Лены, а подберезовиков всего на 2 больше. Витя принес домой
столько подберезовиков, сколько Катя и Лена вместе, а остальных
грибов по одному. Сколько каких грибов принесли мальчики, если
известно, что всего ребята нашли 30 подберезовиков, 12 подосиновиков и 7 боровиков?
2. Требования к БД:
2.1. Виды грибов не должны ограничиваться подберезовиками,
подосиновиками и боровиками. Предусмотреть добавление новых
видов грибов.
2.2. Предусмотреть вероятность того, что ребята каждый день
ходят за грибами.
2.3. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Увеличить количество ребят до десяти.
3.2. Добавить несколько видов грибов.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
3.5. Хранить информацию о каждом походе в лес.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, хранящую информацию
по общему количеству грибов, собранных девочками и мальчиками
за каждый день.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. Кто в какой день принес меньше всего грибов?
5.2. Какой вид грибов больше всего находят девочки (на основе
последнего периода)?
5.3. Сколько подберезовиков в среднем приходится на одного
человека?
41
Вариант 12
1. Задача:
Три шахматиста играли в шахматы. Первый выиграл у второго
11 партий. Второй выиграл у третьего 9 партий. Третий выиграл у
первого на 2 партии меньше, чем проиграл второму партий и сыграл
по одной партии с каждым в ничью. Сколько партий проиграл третий, если всего партий было 80?
2. Требования к БД:
2.1. Шахматисты не должны быть безликими. Добавить каждому шахматисту несколько индивидуальных признаков.
2.2. Шахматистов может быть больше трех.
2.3. Необходимо считать не только победы шахматистов, но и поражения, и общее количество сыгранных партий.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Увеличить количество шахматистов до 10.
3.2. Вести рейтинг на протяжении всех сыгранных партий.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, отмечающую самого неудачливого игрока каждого дня.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. Кто в какой день играл больше всего?
5.2. У кого не было ничьих за весь период?
5.3. Каково соотношение побед и общего количества партий
у каждого?
Вариант 13
1. Задача:
Девочки собирали ракушки на пляже. Лена нашла 3 больших
и 5 маленьких ракушки. Маша нашла столько маленьких, сколько Лена всего, больших же было в 2 раза меньше. Ксюша собирала только большие ракушки, у нее их набралось на пять больше,
чем всех ракушек у Лены и Маши. Катя собрала в 2 раза меньше
42
больших ракушек, чем остальные вместе, и столько же маленьких.
Сколько каких ракушек было собрано?
2. Требования к БД:
2.1. Ракушки могут собирать не только девочки.
2.2. Детей, собирающих ракушки больше четырех.
2.3. дети почти каждый день ходят на пляж.
2.4. Не все хранят то, что собрали.
2.5. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Добавить не менее пяти детей.
3.2. Отслеживать, у кого сколько ракушек дома.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, показывающую, сколько мальчиков и девочек в какой день ходило на пляж за ракушками
(хранят ракушки).
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. Кто выкинул меньше всего ракушек?
5.2. У кого ракушек больше у мальчиков или у девочек?
5.3. Кто каждый день собирает больше всего маленьких ракушек?
Вариант 14
1. Задача:
На юг летели три стаи уток. В каждой было по 30 уток. В первой
селезней было 8. Во второй на 4 больше, чем в первой. В третьей на
одного меньше, чем в среднем в двух предыдущих. Каково соотношение уток к селезням в среднем?
2. Требования к БД:
2.1. В стаях присутствуют молодые особи (выводок этого года).
2.2. Утки летят на юг каждый год.
2.3. Обычно стай летит больше трех.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Увеличить количество стай.
43
3.2. Отследить миграцию за каждый год.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, сколько селезней летит
на юг каждый год.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. В какой год популяция уток достигла минимального значения?
5.2. Больше или меньше уток в этом году, чем в прошлом?
5.3. Какое соотношение уток к селезням было каждый год?
Вариант 15
1. Задача:
За последние четыре дня выпало 50 мм осадков. В первый день
выпало 20 мм осадков. Во второй день на 2 мм больше. В третий
день осадков не было. Сколько осадков выпало в четвертый день?
2. Требования к БД:
2.1. Осадки необходимо учитывать ежедневно.
2.2. В разных регионах количество осадков разное.
2.3. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Отследить количество осадков за месяц в нескольких регионах.
3.2. Разработать запросы для решения исходной задачи.
3.3. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, хранящую количество
дней без осадков по регионам.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. В какой день общее количество осадков было минимальным?
5.2. В каком регионе за последние 10 дней выпало меньше всего
осадков
5.3. Сколько дней в каждом регионе были осадков?
44
Вариант 16
1. Задача:
В классе учится 24 человека. Половина из них написали последнюю контрольную по математике на оценку 3. Если бы оставшихся
было на двух человек меньше, то можно было бы сказать, что половина из них сдали работу на 5. На одного человека больше получили
оценку 4. Сколько человек не сдало работу?
2. Требования к БД:
2.1. Математику изучает не один класс.
2.2. Периодически каждый класс пишет контрольную.
2.3. Для каждого класса хранится средний балл.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Провести анализ успеваемости по математике за последний год.
3.2. Выделить количество отличников за год в каждом классе.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, хранящую средний
каждого учащегося по математике.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. Какой класс лучше знает математику?
5.2. Кто в каждом классе хуже всего справляется с математикой?
5.3. Сколько человек получило только положительные оценки за
год по математике?
Вариант 17
1. Задача:
В отборочных соревнованиях по бегу приняли участие 20 человек. Пятая часть из них пробежала дистанцию менее чем за 200 секунд. Четверть из оставшихся уложилась в интервал от 200 до 230
секунд.
Один пробежал за 231 секунду, остальные не уложились и в это время.
Сколько человек прошло отборочный тур, если расчетное время
было 231 секунда?
45
2. Требования к БД:
2.1. Бег бывает на разные дистанции.
2.2. Один спортсмен может принимать участие не более, чем в
трех видах соревнований.
2.3. Ведется список спортсменов, принимающих участие только
в одном виде соревнований.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Ведение рейтинга успешных отборочных туров для каждого
спортсмена.
3.2. Хранение информации только за последний сезон.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, хранящую количество
прошедших отборочные соревнования по каждому виду.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. В каком виде соревнований все прошли отборочный тур?
5.2. В каком виде соревнований отборочный тур прошло менее
пяти спортсменов?
5.3. Сколько человек приняло участие в двух и более видах соревнований?
Вариант 18
1. Задача:
Вася каждый день по дороге в школу считает птиц на деревьях.
На первом дереве он насчитал 3 вороны и 15 воробьев. На втором на
одну ворону больше и на 3 воробья меньше. На третьем количество
ворон и воробьев было обратным ко второму. На четвертом ворон
было в два раза меньше, чем на втором, а воробьев в три раза больше, чем на третьем. Сколько ворон насчитал Вася?
2. Требования к БД:
2.1. Вася считает птиц каждый день, когда идет в школу.
2.2. Деревьев у него на пути намного больше четырех.
2.3. Вася считает всех птиц, которых видит.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
46
3. Требования к хранимой информации:
3.1. Выделение деревьев, где видов птиц больше двух.
3.2. Хранение информации за последние 10 дней.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, среднее количество
птиц на каждом дереве.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. На скольких деревьях сидят вороны и воробьи вместе?
5.2. Сколько деревьев без птиц Вася видел за последний день?
5.3. На каких деревьях Вася видел больше всего видов птиц?
Вариант 19
1. Задача:
Во время вырубки леса обнаружили несколько деревьев с зимними запасами. В дупле одного было прятано 20 шишек и 15 грибов.
Внутри другого на 12 шишек больше и в 3 раза меньше грибов, чем
в первом. В третьем в 2 раза меньше шишек и в два раза больше грибов, чем в первом. Сколько грибов было обнаружено всего?
2. Требования к БД:
2.1. Не во всех деревьях находят запасы.
2.2. Деревьев с запасами больше трех.
2.3. Лесные животные могут запасать только шишки, грибы и
желуди.
2.4. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Хранение информации о каждом виде запаса.
3.2. Хранение информации о нескольких вырубках.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, хранящую соотношение
деревьев с запасами и без для каждой вырубки.
4.2. Обеспечить динамическое обновление дополнительной
таблицы при внесении изменений в остальные таблицы.
47
5. Вопросы для создания хранимых процедур и отчетов:
5.1. Есть ли деревья, где запасены не только грибы?
5.2. Что меньше любят запасать лесные животные?
5.3. Сколько деревьев в вырубке в среднем без запасов?
Вариант 20
1. Задача:
Миша стал считать, сколько звезд на небе становится видно
каждый день в течение пяти и десяти минут после появления первой. В один день он насчитал 6 звезд за 5 минут и в 4 раза больше
за 10. В другой, за 5 минут, на одну меньше, чем в первый, и за 10 –
на 2 больше. В третий, в первые 5 минут, половину от количества
звезд за 10 минут предыдущего дня и в два раза больше за 10 минут.
Сколько всего звезд насчитал за эти дни Миша, загоревшихся после
первых пяти минут?
2. Требования к БД:
2.1. Хранить подсчеты Миши за последние пять дней.
2.2. Отслеживать соотношения звезд за 5 и 10 минут за каждый день.
2.3. Предусмотреть возможность решения класса однотипных
задач, в том числе и одновременно.
3. Требования к хранимой информации:
3.1. Хранение информации о каждом дне.
3.2. Хранение информации о каждых пяти минутах.
3.3. Разработать запросы для решения исходной задачи.
3.4. Решить исходную задачу.
4. Дополнительные требования для триггеров:
4.1. Добавить дополнительную таблицу, соотношение звезд за
каждые два дня.
4.2. Обеспечить динамическое обновление дополнительной таблицы при внесении изменений в остальные таблицы.
5. Вопросы для создания хранимых процедур и отчетов:
5.1. Увеличилось или уменьшилось количество звезд за последние два дня наблюдений?
5.2. Возросло или уменьшилось число звезд, которые Миша замечает каждый день за первые 5 минут?
5.3. В среднем меньше звезд загорается за первые и вторые 5 минут?
48
Содержание
Предисловие ............................................................... Лабораторная работа 1. Проектирование базы данных ........ 1. Концептуальная модель ................................................ 2. Логическая модель ....................................................... 3. Таблицы на основе логической модели ............................. 4. Задание ...................................................................... Лабораторная работа 2. Создание таблиц и заполнение
их данными ................................................................. 1. Базы данных, язык SQL и СУБД ..................................... 2. Физическая модель ...................................................... 3. Создание, удаление, изменение таблиц ............................ 4. Вставка данных в таблицы ............................................ 5. Запросы на создание БД из примера ................................ 6. Задание ...................................................................... Лабораторная работа 3. Решение исходной задачи .............. 1. Выборка ...................................................................... 2. Переменные ................................................................ 3. Решение исходной задачи с помощью выборки .................. 4. Задание ...................................................................... Лабораторная работа 4. Использование
хранимых процедур ...................................................... 1. Хранимые процедуры ................................................... 2. Задание ...................................................................... Лабораторная работа 5. Спользование триггеров
для решения задачи ...................................................... 1. Триггеры .................................................................... 2. Задание ...................................................................... Лабораторная работа 6. Отчеты при решении задач ............ 1. Задание ...................................................................... Библиографический список .............................................. Приложение А. Варианты заданий .................................. 3
4
4
6
7
8
10
10
11
13
17
18
19
20
20
20
21
22
24
24
25
27
27
28
30
30
31
32
49
Для заметок
50
Документ
Категория
Без категории
Просмотров
0
Размер файла
1 225 Кб
Теги
sorokin
1/--страниц
Пожаловаться на содержимое документа