Практическая работа 1. Проектирование базы данных
 Скачать 2.91 Mb.
|
|
3 ОГЛАВЛЕНИЕ Практическая работа №1. Проектирование базы данных: ............................................ 4 Практическая работа №2. Установка соединения с сервером Microsoft SQL Server и принципы создания баз данных .................................................................................... 39 Практическая работа №3. Разработка таблиц и ограничений .................................... 63 Практическая работа №4. Введение в язык SQL. Создание таблиц и ограничений на SQL .............................................................................................................................. 74 Практическая работа №5. Создание запросов на выборку. Отбор строк по условию104 Практическая работа №6. Создание многотабличных запросов. Запросы на соединение ..................................................................................................................... 118 Практическая работа №7. Создание запросов на группировку и сортировку данных. Запросы на изменение. Использование встроенных функций. ................. 130 Практическая работа №8. Создание и управление представлениями ..................... 150 Практическая работа №9. Основы программирования с помощью встроенного языка Transact-SQL в Microsoft SQL Server ............................................................... 152 Практическая работа №10. Создание, изменение, применение и удаление функций и хранимых процедур ................................................................................................... 171 Практическая работа №11. Создание, программирование и управление триггерами ..................................................................................................................... 176 Практическая работа №12. Создание и управление транзакциями ......................... 180 Практическая работа №13. Создание, применение и управление курсорами ........ 184 Практическая работа №14. Система безопасности SQL Server. ............................... 187 Практическая работа №15. Администрирование сервера баз данных MS SQL Server195 Практическая работа №16. Администрирование сервера баз данных MS SQL Server (продолжение) ............................................................................................................... 201 Библиографический список .......................................................................................... 205 4 ПРАКТИЧЕСКАЯ РАБОТА №1. ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ: Цель практической работы Получить теоретические знания и практические навыки реализации баз данных (БД). Осуществить анализ предметной области. Освоить концептуальное проектирование и научиться определять сущности и атрибуты БД. Научиться разрабатывать инфологическую модель БД в виде ER-диаграмм. Получить теоретические знания и практические навыки при физическом проектировании баз данных (БД). Научиться создавать даталогическую модель БД. I. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ 1. ОПИСАНИЕ ПРЕДМЕТНОЙ ОБЛАСТИ БАЗЫ ДАННЫХ 1.1. Понятие БД и СУБД Информационная система - система, реализующая автоматизированный сбор, обработку и манипулирование данными и включающая технические средства обработки данных, программное обеспечение и соответствующий персонал. Цель любой информационной системы - обработка данных об объектах реального мира. Основой информационной системы является база данных. В широком смысле слова база данных - это совокупность сведений о конкретных объектах реального мира в какой-либо предметной области. Под предметной областью принято понимать часть реального мира, подлежащего изучению для организации управления и в конечном счете автоматизации, например, предприятие, вуз и т.д. Создавая базу данных, пользователь стремится упорядочить информацию по различным признакам и быстро производить выборку с произвольным сочетанием признаков. Большое значение при этом приобретает структурирование данных. Структурирование данных - это введение соглашений о способах представления данных. Неструктурированными называют данные, записанные, например, в текстовом файле. Ниже приведен пример неструктурированных и структурированных данных, содержащих сведения о студентах (номер личного дела, фамилию, имя, отчество и год рождения). Неструктурированные данные: Личное дело № 16493. Сергеев Петр Михайлович, дата рождения 1 января 1976 г.; Л/д № 16593, Петрова Анна Владимировна, дата рожд. 15 марта 1975 г.; № личн. дела 16693, д.р. 14.04.76, Анохин Андрей Борисович Легко убедиться, что сложно организовать поиск необходимых данных, хранящихся в неструктурированном виде. Структурированные данные: № личного дела Фамилия Имя Отчество Дата рождения 16493 Сергеев Петр Михайлович 01.01.76 16593 Петрова Анна Владимировна 15.03.75 16693 Анохин Андрей Борисович 14.04.76 5 В современной технологии баз данных предполагается, что создание базы данных, ее поддержка и обеспечение доступа пользователей к ней осуществляются централизованно с помощью специального программного инструментария - системы управлении базами данных (СУБД). База данных (БД) - это поименованная совокупность данных, отражающая состояние объектов и их отношений в рассматриваемой предметной области. Объектом называется элемент предметной области, информацию о котором мы сохраняем. Объект может быть реальным (например, человек, изделие; или населенный пункт) и абстрактным (например, событие, счет покупателя или изучаемый студентами курс). Так, в области продажи автомобилей примерами объектов могут служить МОДЕЛЬ АВТОМОБИЛЯ, КЛИЕНТ и СЧЕТ. На товарном складе - это ПОСТАВЩИК, ТОВАР, ОТПРАВЛЕНИЕ и т. д. Понятие базы данных тесно связано с такими понятиями структурных элементов, как поле, запись, файл (таблица) (рис.1). Рис.1 Основные структурные элементы БД 1.2. Структурные элементы базы данных Поле - элементарная единица логической организации данных, которая соответствует неделимой единице информации - реквизиту. Для описания поля используются следующие характеристики: имя, например, Фамилия, Имя, Отчество, Дата рождения; тип, например, символьный, числовой, денежный; длина, например, 15 байт, причем будет определяться максимально возможным количеством символов; точность для числовых данных, например два десятичных знака для отображения дробной части числа, Запись - совокупность логически связанных полей. Экземпляр записи - отдельная реализация записи, содержащая конкретные значения ее полей. Файл (таблица) - совокупность экземпляров записей одной структуры. Описание логической структуры записи файла содержит последовательность 6 расположения полей записи и их основные характеристики, как это показано на рис. 2 и 3. Имя файла Поле Признак ключа Формат поля Имя (обозначение) Полное наименование Тип Длина Точность (для чисел) имя 1 имя N Рис. 2. Описание логической структуры записи файла В структуре записи файла указываются поля, значения которых являются ключами: первичными (ПК) и внешними (ВК), Первичный ключ (ПК) - это одно или несколько полей, однозначно идентифицирующих запись. Если первичный ключ состоит из одного поля, он называется простым, если из нескольких полей - составным ключом. Внешний ключ (ВК) - это одно или несколько полей, которые выполняют роль поисковых или группировочных признаков. В отличие от первичного, значение внешнего ключа может повторяться в нескольких записях файла, то есть он не является уникальным. Если по значению первичного ключа может быть найден один единственный экземпляр записи, то по внешнему - несколько. Имя файла: СТУДЕНТ Поле Признак ключа Формат поля Обозначение Наименование Тип Длина Точность Номер № личного дела • Симв 5 Фамилия Фамилия студента Симв 15 Имя Имя студента Симв 10 Отчество Отчество студента Симв 15 Дата Дата рождения Дата 8 Рис. 3. Описание логической структуры записи файла СТУДЕНТ 1.3. Понятие модели данных Для того, чтобы спроектировать структуру базы данных, необходима исходная информация о предметной области. Желательно, чтобы эта информация была представлена в формализованном виде. Такое формализованное описание предметной области будем называть инфологической (infological) моделью предметной области (ИЛМ) или концептуальной моделью (КМ). Ядром любой базы данных является модель данных. Модель данных представляет собой множество структур данных, ограничений целостности и операций манипулирования данными. С помощью модели данных могут быть представлены объекты предметной области и взаимосвязи между ними. Модель данных - совокупность структур данных и операций их обработки. СУБД основывается на использовании иерархической, сетевой или реляционной модели, на комбинации этих моделей или на некотором их подмножестве. Самой распространенной моделью данных является - реляционная. 7 1.3.1. Иерархическая модель данных Иерархическая модель организует данные в виде древовидной структуры К основным понятиям иерархической структуры относятся: уровень, элемент (узел), связь. Дерево представляет собой иерархию элементов, называемых узлами. Узел - это совокупность атрибутов данных, описывающих некоторый объект. На самом верхнем уровне иерархии имеется один и только один узел - корень. Каждый узел, кроме корня, связан с одним узлом на более высоком уровне, называемым исходным для данного узла. Ни один элемент не имеет более одного исходного. Каждый элемент может быть связан с одним или несколькими элементами на более низком уровне. Они называются порожденными (рис. 4). Рис. 4. Графическое изображение иерархической структуры БД К каждой записи базы данных существует только один (иерархический) путь от корневой записи. Например, как видно из рис. 4, для записи С4 путь проходит через записи А и ВЗ. 1.3.2. Сетевая модель данных Сетевая модель организует данные в виде сетевой структуры. Структура называется сетевой, если в отношениях между данными порожденный элемент имеет более одного исходного. В сетевой структуре при тех же основных понятиях (уровень, узел, связь) каждый элемент может быть связан с любым другим элементом. На рис. 5 изображена сетевая структура базы данных в виде графа. Рис. 5. Графическое изображение сетевой структуры 1.3.3. Реляционная модель данных Реляционная модель данных является совокупностью взаимосвязанных двумерных таблиц объектов модели. Например, реляционной таблицей можно представить информацию о студентах, обучающихся в вузе (рис. 6). № личного дела Фамилия Имя Отчество Дата рождения Группа 16493 Сергеев Петр Михайлович 01.01.76 111 16593 Петрова Анна Владимировна 15.03.75 112 16693 Анохин Андрей Борисович 14.04.76 111 Рис. 6. Пример реляционной таблицы 8 Связи между двумя логически связанными таблицами в реляционной модели устанавливаются по равенству значений одинаковых атрибутов этих таблиц. Каждая реляционная таблица представляет собой двумерный массив и обладает следующими свойствами: - каждый элемент таблицы - один элемент данных; - все столбцы в таблице однородные, т.е. все элементы в столбце имеют одинаковый тип (числовой, символьный и т.д.) и длину; - каждый столбец имеет уникальное имя; - одинаковые строки в таблице отсутствуют; - порядок следования строк и столбцов может быть произвольным. При описании реляционной модели часто используют следующие термины: отношение, кортеж, домен. Отношения представлены в виде таблиц, строки которых соответствуют записям (кортежам), а столбцы полям, атрибутам отношений (доменам). Поле, каждое значение которого однозначно определяет соответствующую запись, называется простым ключом (ключевым полем). Если записи однозначно определяются значениями нескольких полей, то такая таблица базы данных имеет составной ключ. В примере, показанном на рис.6, ключевым полем таблицы является "№ личного дела". Между двумя реляционными таблицами могут быть сформированы связи. Различные таблицы, могут быть связаны между собой через общее поле данных. На рис. 7 показан пример реляционной модели, построенной на основе отношений: СТУДЕНТ, СЕССИЯ, СТИПЕНДИЯ. Рис.7. Пример реляционной модели Таблица СТУДЕНТ имеет поля: Номер, Фамилия, Имя, Отчество, Дата рождения, Группа; СЕССИЯ - Номер, Оценка 1, Оценка 2, Оценка 3, Оценка 4, Результат; СТИПЕНДИЯ - Результат, Процент Таблицы СТУДЕНТ И СЕССИЯ имеют совпадающие ключи (Номер), что дает возможность легко организовать связь между ними. Таблица СЕССИЯ имеет первичный ключ Номер и содержит внешний ключ Результат, который обеспечивает ее связь с таблицей СТИПЕНДИЯ. Благодаря имеющимся связям достигаются следующие преимущества: 1. Удается избежать дублирования информации. Все необходимые данные можно хранить только в одной таблице. Так, например, нет необходимости в таблице СЕССИЯ хранить номер группы каждого студента, сдающего экзамены, достаточно задать связь с таблицей СТУДЕНТ. 2. В реляционных базах данных легко производить изменения Если в таблице 9 СЕССИЯ изменить какие-нибудь значения, то правильная информация автоматически будет связана с другими таблицами, ссылающимися на первую (например, таблица СТИПЕНДИЯ). 3. В нереляционных базах данных сложно передать все имеющиеся зависимости, т.е. связать друг с другом данные из различных таблиц Реляционная база данных выполняет все эти действия автоматически. 4. В реляционных базах данных удается легко избежать установления ошибочных связей между различными таблицами данных, а необходимый объем памяти сокращен до минимума. 2. ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ 2.1. Анализ и описание предметной области БД Предметной областью называется фрагмент реальности, который описывается или моделируется с помощью БД и ее приложений. В предметной области выделяются информационные объекты – идентифицируемые объекты реального мира, процессы, системы, понятия и т.д., сведения о которых хранятся в БД. Анализ предметной области выполняется в следующем порядке: название предприятия, цель деятельности предприятия, структура предприятия, информационные потребностей пользователей. Для описания ПО следует привести основные решаемые задачи БД. Дать словесное описание процесса функционирования ПО, проанализировать основные хозяйственные операции, которые совершаются в ПО. Привести анализ структуры предприятия, перечислить задачи, решаемые отдельными подразделениями. Анализ предметной области целесообразно разбить па три фазы: 1. анализ концептуальных требований и информационных потребностей; 2. выявление информационных объектов и связей между ними; 3. построение концептуальной модели предметной области и проектирование концептуальной схемы БД. 2.2. Анализ концептуальных требований и информационных потребностей Требования пользователей к разрабатываемой БД представляют собой список запросов с указанием их интенсивности и объемов данных. Эти сведения разработчики БД получают в диалоге с ее будущими пользователями. Здесь же выясняются требования к вводу, обновлению и корректировке информации. Требования пользователей уточняются и дополняются при анализе имеющихся и перспективных задач. Рассмотрим примерный состав вопросника при анализе различных предметных областей. Пример. Предлагается разработать БД для учета студентов вуза. Анализ предметной области: 1. Сколько студентов учится в вузе? 2. Сколько факультетов и отделений в вузе? 3. Как распределены студенты по факультетам отделений и курсам? 4. Сколько дисциплин читается на каждом курсе по каждой специальности? 5. Как часто обновляется информация в БД? 6. Сколько преподавателей в вузе? 7. Сколько иногородних студентов живет в общежитии, на частных квартирах? 8. Сколько лекционных аудиторий и аудиторий для проведения практических занятий, лабораторий? 9. Какая преемственность существует между читаемыми курсами? 10. Как информация, представленная в п.п. 1-9, используется в настоящее время (расписание занятий, экзаменов, зачетов и т.д.) и как ее собираются использовать? 11. Сколько раз в день, сколько человек и кто пользуются БД? 10 2.3. Выявление информационных объектов и связей между ними Вторая фаза анализа предметной области состоит в выборе информационных объектов, задании необходимых свойств для каждого объекта, выявлении связей между объектами, определении ограничений, накладываемых на информационные объекты, типы связей между ними, характеристики информационных объектов- Проанализируем предметную область на примере БД "Видеомагнитофоны". При выборе информационных объектов постараемся ответить на ряд вопросов: 1. На какие классы можно разбить данные, подлежащие хранению в БД? 2. Какое имя можно присвоить каждому классу данных? 3. Какие наиболее интересные характеристики (с точки зрения пользователя) каждого класса данных можно выделить? 4. Какие имена можно присвоить выбранным наборам характеристик? Пример. БД "Видеомагнитофоны", рассчитанной на пользователей, которые хотят приобрести данный вид техники. После беседы с различными пользователями и просмотра каталогов было выяснено, что интерес представляют три информационных объекта: видеомагнитофон, видеоплеер, видеокассета. Рассмотрим наиболее существенные характеристики каждого информационного объекта. |