Введение
Современные системы управления базами данных (СУБД) – важнейшее направление, без основательного знакомства с которым в настоящее время невозможно быть квалифицированным специалистом в области информационных технологий.
Целью данной методической разработки является оказание помощи студентам, изучающим дисциплину «Использование СУБД для создания программных систем и их компонентов», в решении различных практических задач, связанных с использованием баз данных для создания программных систем и их компонентов.
Методические указания содержат описание 6 практических занятий и посвящены изучению таких вопросов, как методика проектирования баз данных с использованием CASE-средств; формирование запросов на языке SQL; управление доступом к данным в СУБД; реализация бизнес-процессов программных систем посредством хранимых процедур и функций, администрирование данных, особенности управления транзакциями, словарь данных, безопасность.
Основная цель практикума – выработка у студентов практических навыков и расширение теоретических знаний о возможностях реляционных баз данных, системах управления базами данных, типологией и архитектурой СУБД, основными объектами БД.
В практических работах предусматривается общее ознакомление с разным инструментарием современных СУБД, в том числе язык T-SQL (процедуры, функции, триггеры, курсоры и т. д.), В качестве предмета исследования выбрана промышленная СУБД фирмы MS SQL SERVER 2008.
Этот комплексный подход обеспечивает полноценное теоретическое и практическое освоение технологий систем управления базами данных.
Практическое занятие № 1
Проектирование и создание базы данных
Цель работы:
1) Проектирование БД на основе ER-диаграммы
2) Написание SQL-скрипта для создания схемы базы данных с учетом ограничений и связей между данными в выбранной предметной области.
Постановка задачи
Практическое задание связано с проектированием схемы базы данных. Каждый индивидуальный вариант содержит ER-диаграмму некоторой предметной области, иногда очень приблизительную (она может быть модифицирована, но не в сторону упрощения). Задачей студента является решить, для чего будет использоваться создаваемая база данных, и, исходя из этого, построить её концептуальную схему. Результатом данного практического задания является схема базы данных (в виде диаграммы, содержащей таблицы и связи между ними, без уточнения типов столбцов). Максимальная оценка за данное задание всегда составляет 8 баллов, а студент может сдавать схему несколько раз, исправляя замечания преподавателей. При сдаче задания студент должен обосновать соответствие созданной схемы поставленной задаче.
Темы для проработки
Модель "сущность-связь" (ER-модель).
Первичные и внешние ключи.
Типы связей и их моделирование.
Нормальные формы и нормализация.
Оператор CREATE TABLE, CREATE DATABASE, типы данных SQL,
Декларативные ограничения целостности.
Требования к схеме
Схема должна соответствовать поставленной задаче.
Связи между сущностями должны быть правильно смоделированы.
Таблицы должны удовлетворять, по крайней мере, третьей нормальной форме.
Желательно придерживаться какой-либо системы в именовании таблиц и столбцов.
Выполнение работы
Изучить ER-диаграмму, приведенную в вашем варианте индивидуального задания, при необходимости – модифицировать ее. Заданная ER-диаграмма дает лишь ориентировочное представление о структуре данных заданной Вам предметной области, Вы можете модифицировать ее (но не в сторону упрощения!), добавив новые сущности, атрибуты, связи.
Конвертировать ER-диаграмму в концептуальную схему, отображаемую на реляционные таблицы.
Составить SQL-скрипт для создания таблиц базы данных, включив в него все требуемые логикой предметной области декларативные ограничения целостности (первичные и внешние ключи, проверочные ограничения и т. п.).
Пример выполнения работы
В качестве примера рассмотрим предметную область «Музыкальные предпочтения» (рис. 1)/
Первым шагом в проектировании схемы базы данных является определение цели создания базы данных. Зададимся целью создать простую социальную сеть для меломанов, позволяющую им:
Находить людей, которые имеют схожие музыкальные вкусы.
Получать от системы рекомендации, какие еще песни, альбомы и исполнители могут им понравиться.
Просматривать рейтинг исполнителей, альбомов и композиций.
Схема базы данных для этого случая может выглядеть, например, следующим образом:
Рис. 1
Рассмотрим получившиеся таблицы и некоторые рассуждения, которые привели к приведенной схеме:
Таблица 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),
Рис. 2
);
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),
BirthDate Datetime,
CityID REFERENCES City (id),
Email VARCHAR(20),
PasswordHash VARCHAR(50)
);
и т.д.
Индивидуальные задания
Вариант 1. Предметная область «Библиотека»
Вариант 2. Предметная область «Университет»
Вариант 3. Предметная область «Отдел продаж»
Вариант 4. Предметная область «Производство»
Вариант 5. Предметная область «Кооперативы»
Вариант 6. Предметная область «Автомастерская»
Вариант 7. Предметная область «Сессия»
Вариант 8. Предметная область «Управление проектом»
Вариант 9. Предметная область «Поликлиника»
Вариант 10. Предметная область «Сотовая связь»
Вариант 11. Предметная область «Спортивная статистика»
Вариант 12. Предметная область «Поставки»
Вариант 13. Предметная область «Городской транспорт»
Вариант 14. Предметная область «География»
Вариант 15. Предметная область «Домоуправление»
Контрольные вопросы
Объяснить, что делают написанные запросы.
В чем различие типов CHAR и VARCHAR? VARCHAR и NVARCHAR?
Что такое внешний ключ?
Какие существуют способы поддержания ссылочной целостности?
Что такое уникальный ключ?
Что такое нормализация?
Рассказать о нормальных формах.
Что такое IDENTITY?
Рассказать о значениях по умолчанию и неопределенных значениях.
Рассказать о вычисляемых столбцах.
Как можно представить значение булевского типа?
Как можно хранить даты и время?
Рассказать о числовых типах данных.
Каким образом можно вставить несколько строк с помощью одного оператора INSERT?
Как ведет себя оператор INSERT, если в списке столбцов перечислены не все столбцы?
|
