Файл: Методические указания к практическим занятиям для студентов направления подготовки 230100. 68.pdf

ИСПОЛЬЗОВАНИЕ СУБД
ДЛЯ СОЗДАНИЯ
ПРОГРАММНЫХ СИСТЕМ
И ИХ КОМПОНЕНТОВ
Методические указания к практическим занятиям для студентов направления подготовки 230100.68
«Информатика и вычислительная техника»
Составитель А. А. Будаева
ВЛАДИКАВКАЗ 2015

1
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ
Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
«СЕВЕРО-КАВКАЗСКИЙ ГОРНО-МЕТАЛЛУРГИЧЕСКИЙ ИНСТИТУТ
(ГОСУДАРСТВЕННЫЙ ТЕХНОЛОГИЧЕСКИЙ УНИВЕРСИТЕТ)»
Кафедра автоматизированной обработки информации
ИСПОЛЬЗОВАНИЕ СУБД
ДЛЯ СОЗДАНИЯ
ПРОГРАММНЫХ СИСТЕМ
И ИХ КОМПОНЕНТОВ
Методические указания к практическим занятиям для студентов направления подготовки 230100.68
«Информатика и вычислительная техника»
Составитель А. А. Будаева
Допущено редакционно-издательским советом
Северо-Кавказского горно-металлургического института (государственного технологического университета).
Протокол заседания РИСа № 2 от 18.02.2014 г.
ВЛАДИКАВКАЗ 2015

2
УДК 004.65
ББК 32.973.26-0.18.2
Б90
Рецензент:
Кандидат технических наук, доцент СКГМИ (ГТУ) Даурова А. А.
Б90
Использование СУБД для создания программных систем и их компонентов: Методические указания к практическим заня- тиям для студентов направления подготовки 230100.68 – «Инфор- матика и вычислительная техника». Владикавказ: "Терек", 2015. –
58 с.
Методические указания содержат описание 6 практических занятий, соответствующих рабочей программе по дисциплине «Использование
СУБД для создания программных систем и их компонентов» для студен- тов направления подготовки «Информатика и вычислительная техника»
230100.68.
В методических указаниях на примерах рассматриваются средства и возможности СУБД, используемые при проектировании и разработке про- граммных систем и их компонентов.
Методические указания способствуют приобретению навыков исполь- зования CASE-средств при моделировании прикладных задач и проекти- ровании схем баз данных; реализации бизнес-процессов программных си- стем посредством хранимых процедур и функций; поддержания целостно- сти БД посредством триггеров и транзакций; формулирования запросов к базам данных на языке SQL; разработки приложений для обработки баз данных на основе технологии ADO.
УДК 004.65
ББК 32.973.26-0.18.2
Работа публикуется в авторской редакции, пунктуации и орфографии.
Компьютерная верстка: Кравчук Т. А.

Составление. ФГБОУ ВПО «Северо-Кавказский горно-металлургический институт (государственный технологический университет)», 2015

Будаева А. А., составление, 2015
Подписано в печать 27.01.15. Формат бумаги 60х84 1
/
16
. Бумага офсетная. Гарнитура «Таймс».
Печать на ризографе. Усл. п.л. 3,37. Уч.-изд.л. 1,96. Тираж 20 экз. Заказ № ____.
Северо-Кавказский горно-металлургический институт (государственный технологический университет). Изд-во «Терек».
Отпечатано в отделе оперативной полиграфии СКГМИ (ГТУ).
362021. Владикавказ, ул. Николаева, 44.

3
Оглавление
Введение ................................................................................................ 4
Практическое занятие № 1. Проектирование и создание базы данных .......................................................................................... 5
Практическая занятие № 2. Операторы манипулирования данными языка SQL ............................................................................. 16
Практическое занятие №3. Создание и использование представлений ....................................................................................... 25
Практическое занятие №4. Создание и использование триггеров ... 33
Практическое занятие N 5. Создание и использование хранимых процедур .............................................................................. 37
Практическое занятие №6. Управление доступом в MS SQL
Server ...................................................................................................... 54
Список литературы ............................................................................... 56
Приложение........................................................................................... 57

4
Введение
Современные системы управления базами данных (СУБД) – важ- нейшее направление, без основательного знакомства с которым в настоящее время невозможно быть квалифицированным специали- стом в области информационных технологий.
Целью данной методической разработки является оказание по- мощи студентам, изучающим дисциплину «Использование СУБД для создания программных систем и их компонентов», в решении различ- ных практических задач, связанных с использованием баз данных для создания программных систем и их компонентов.
Методические указания содержат описание 6 практических заня- тий и посвящены изучению таких вопросов, как методика проектиро- вания баз данных с использованием CASE-средств; формирование за- просов на языке SQL; управление доступом к данным в СУБД; реали- зация бизнес-процессов программных систем посредством хранимых процедур и функций, администрирование данных, особенности управ- ления транзакциями, словарь данных, безопасность.
Основная цель практикума – выработка у студентов практических навыков и расширение теоретических знаний о возможностях реляци- онных баз данных, системах управления базами данных, типологией и архитектурой СУБД, основными объектами БД.
В практических работах предусматривается общее ознакомление с разным инструментарием современных СУБД, в том числе язык T-
SQL (процедуры, функции, триггеры, курсоры и т. д.), В качестве предмета исследования выбрана промышленная СУБД фирмы MS
SQL SERVER 2008.
Этот комплексный подход обеспечивает полноценное теоретиче- ское и практическое освоение технологий систем управления базами данных.

5
П р а к т и ч е с к о е з а н я т и е № 1
ПРОЕКТИРОВАНИЕ И СОЗДАНИЕ БАЗЫ ДАННЫХ
Цель работы:
1) Проектирование БД на основе ER-диаграммы
2) Написание SQL-скрипта для создания схемы базы данных с учетом ограничений и связей между данными в выбранной предмет- ной области.
Постановка задачи
Практическое задание связано с проектированием схемы базы данных. Каждый индивидуальный вариант содержит ER-диаграмму некоторой предметной области, иногда очень приблизительную (она может быть модифицирована, но не в сторону упрощения). Задачей студента является решить, для чего будет использоваться создаваемая база данных, и, исходя из этого, построить её концептуальную схему.
Результатом данного практического задания является схема базы дан- ных (в виде диаграммы, содержащей таблицы и связи между ними, без уточнения типов столбцов). Максимальная оценка за данное задание всегда составляет 8 баллов, а студент может сдавать схему несколько раз, исправляя замечания преподавателей. При сдаче задания студент должен обосновать соответствие созданной схемы поставленной задаче.
Темы для проработки
• Модель "сущность-связь" (ER-модель).
• Первичные и внешние ключи.
• Типы связей и их моделирование.
• Нормальные формы и нормализация.
• Оператор CREATE TABLE, CREATE DATABASE, типы дан- ных SQL,
• Декларативные ограничения целостности.
Требования к схеме
• Схема должна соответствовать поставленной задаче.
• Связи между сущностями должны быть правильно смоделированы.
• Таблицы должны удовлетворять, по крайней мере, третьей нормальной форме.

6
• Желательно придерживаться какой-либо системы в именова- нии таблиц и столбцов.
Выполнение работы

Изучить ER-диаграмму, приведенную в вашем варианте инди- видуального задания, при необходимости – модифицировать ее. За- данная ER-диаграмма дает лишь ориентировочное представление о структуре данных заданной Вам предметной области, Вы можете мо- дифицировать ее (но не в сторону упрощения!), добавив новые сущ- ности, атрибуты, связи.

Конвертировать ER-диаграмму в концептуальную схему, отоб- ражаемую на реляционные таблицы.

Составить SQL-скрипт для создания таблиц базы данных, включив в него все требуемые логикой предметной области деклара- тивные ограничения целостности (первичные и внешние ключи, про- верочные ограничения и т. п.).
Пример выполнения работы
В качестве примера рассмотрим предметную область «Музы- кальные предпочтения» (рис. 1)/
Первым шагом в проектировании схемы базы данных является определение цели создания базы данных. Зададимся целью создать простую социальную сеть для меломанов, позволяющую им:

Находить людей, которые имеют схожие музыкальные вкусы.

Получать от системы рекомендации, какие еще песни, альбомы и исполнители могут им понравиться.

Просматривать рейтинг исполнителей, альбомов и композиций.
Схема базы данных для этого случая может выглядеть, например, следующим образом:
Рис. 1

7
Рассмотрим получившиеся таблицы и некоторые рассуждения, которые привели к приведенной схеме:
• Таблица User хранит данные о пользователе-меломане. Поми- мо базовых данных, таких как имя, фамилия, пол, дата рождения и город, эта таблица хранит также адрес электронной почты для связи и авторизации в предполагаемой социальной сети (а также хэш пароля).
• Таблицы City и Country служат справочниками для городов и стран, позволяя избежать избыточности и аномалий обновления.
• Таблица Song хранит уникальный идентификатор и название песни. Таблица Genre является справочником жанров, а таблица
SongGenre связывает песни и жанры, моделируя связь типа «многие- ко-многим», так как одна песня может быть помечена несколькими жанрами, а также каждый жанр может включать несколько песен.
• Таблица Album хранит информацию об альбомах. Так как одна песня может быть включена в несколько альбомов, а каждый альбом может содержать несколько песен, но требуется таблица SongAlbum для организации связи «многие-ко-многим». Эта таблица также хра- нит номер трека данной песни в альбоме.
• Важным столбцом в таблице Album является ссылка на испол- нителя, если это альбом одного исполнителя. Если же это альбом- сборник, то ArtistID будет содержать неопределенное значение.
• Таблица Artist хранит данные об исполнителях, включая стра- ну исполнителя. Исполнители связаны с песнями опять же с помощью связи «многие-ко-многим» (таблица SongArtist), учитывая случаи, когда у песни есть несколько исполнителей.
• Наконец, таблица Like хранит предпочтения пользователей: какая песня, когда и насколько понравилась – пусть Score отражает оценку пользователем песни от 1 («ничего особенного») до 5 («очень нравится»).
Детальная схема представленной модели приведена на рис 2.
Пример SQL-скрипта для схемы, представленной на рисунке 2
Создадим отдельный скрипт для MS SQL Server, используя спе- цифические типы данных.
CREATE TABLE SongAlbum (
SongID INTEGER NOT NULL REFERENCES Song (ID),
AlbumID INTEGER NOT NULL REFERENCES Album (ID),
TrackNo INTEGER NOT NULL
PRIMARY KEY (SongID, AlbumID, TrackNo),

8
Рис. 2

9
);
CREATE TABLE Album (
ID INTEGER NOT NULL PRIMARY KEY,
Title VARCHAR(20),
Year INTEGER,
ArtistID INTEGER REFERENCES Artist (ID)
);
CREATE TABLE SongArtist (
SongID INTEGER NOT NULL REFERENCES Song (ID),
ArtistID INTEGER REFERENCES Artist (ID)
PRIMARY KEY (SongID, ArtistID)
);
CREATE TABLE Song (
ID INTEGER NOT NULL PRIMARY KEY,
Title VARCHAR(20),
);
CREATE TABLE Artist (
ID INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(40),
CountryID INTEGER REFERENCES Country (ID)
);
CREATE TABLE Country ( id INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(20)
);
CREATE TABLE City ( id INTEGER NOT NULL PRIMARY KEY,
CountryID INTEGER REFERENCES Country (ID),
Name VARCHAR(20)
);
CREATE TABLE User (
ID INTEGER NOT NULL PRIMARY KEY,
FirstName VARCHAR(20),
LastName VARCHAR(20),
Sex CHAR(1),

10
BirthDate DATETIME,
CityID REFERENCES City (id),
Email VARCHAR(20),
PasswordHash VARCHAR(50)
); и т.д.
Индивидуальные задания
Вариант 1. Предметная область «Библиотека»
Вариант 2. Предметная область «Университет»
Вариант 3. Предметная область «Отдел продаж»

11
Вариант 4. Предметная область «Производство»
Вариант 5. Предметная область «Кооперативы»
Вариант 6. Предметная область «Автомастерская»

12
Вариант 7. Предметная область «Сессия»
Вариант 8. Предметная область «Управление проектом»
Вариант 9. Предметная область «Поликлиника»

13
Вариант 10. Предметная область «Сотовая связь»
Вариант 11. Предметная область «Спортивная статистика»
Вариант 12. Предметная область «Поставки»

14
Вариант 13. Предметная область «Городской транспорт»
Вариант 14. Предметная область «География»
Вариант 15. Предметная область «Домоуправление»

15
Контрольные вопросы
• Объяснить, что делают написанные запросы.
• В чем различие типов CHAR и VARCHAR? VARCHAR и

NVARCHAR?
• Что такое внешний ключ?

• Какие существуют способы поддержания ссылочной целостно- сти?
• Что такое уникальный ключ?

• Что такое нормализация?
• Рассказать о нормальных формах.

• Что такое IDENTITY?
• Рассказать о значениях по умолчанию и неопределенных зна- чениях.
• Рассказать о вычисляемых столбцах.

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

• Каким образом можно вставить несколько строк с помощью одного оператора INSERT?
• Как ведет себя оператор INSERT, если в списке столбцов пере- числены не все столбцы?

16
П р а к т и ч е с к а я з а н я т и е № 2
ОПЕРАТОРЫ МАНИПУЛИРОВАНИЯ
ДАННЫМИ ЯЗЫКА SQL
Цель: освоение способов манипулирования данными в средах ин- терактивного SQL СУБД Microsoft SQL Server.
Постановка задачи
Практическое задание посвящено манипулированию данными с помощью операторов SQL. В ходе выполнения практического задания необходимо:
1. Составить SQL-скрипты для выполнения выборок, указанных в индивидуальном варианте. Кроме этого, нужно подготовить еще 3-4 выборки, которые имеют осмысленное значение для предметной об- ласти, и также составить для них SQL-скрипты.
2.
Сформулировать 3-4 запроса на изменение и удаление из базы данных. Запросы должны быть сформулированы в терминах предмет- ной области. Среди запросов обязательно должны быть такие, кото- рые будут вызывать срабатывание ограничений целостности. Соста- вить SQL-скрипты для выполнения этих запросов.
Темы для проработки
1. Оператор SELECT.
2. Оператор UPDATE.
3.
Оператор DELETE.
4.
Декларативные ограничения целостности.
Примеры
Рассмотрим несколько примеров, показывающих различные спо- собы построения запросов и некоторые полезные возможности MS
SQL Server 2005:
Вывести имена и фамилии меломанов, которым нравятся только песни, которые не нравятся больше никому.
SELECT FirstName, LastName
FROM [User] WHERE [User].ID IN