Лабораторный практикум по курсу “СУБД” для студентов гуманитарных факультетов (стр. 1 )

Лабораторный практикум по курсу “СУБД”

для студентов

гуманитарных факультетов

Содержание

Лабораторная работа № 1. 3

Лабораторная работа № 2. 7

Лабораторная работа № 3. 13

Лабораторная работа № 4. 19

Лабораторная работа № 5. 22

Лабораторная работа № 6. 28

Лабораторная работа № 7. 34

Лабораторная работа № 8. 40

Лабораторная работа № 9. 49

Лабораторная работа № 10. 52

Лабораторная работа № 11. 58

Лабораторная работа № 12. 63

Лабораторная работа № 13. 68

Лабораторная работа № 14. 77

Лабораторная работа № 1.

Тема. Знакомство с СУБД Access.

Цель лабораторной работы – получить первое представление о реляционных БД и СУБД Access.

Microsoft Access - позволяет

·  хранить почти безграничное количество информации (ограничено объемом внешней памяти)

·  располагать информацию в удобном для Вас порядке

·  получать информацию по заданным Вами критериям отбора

·  создавать формы облегчающие ввод информации

·  разрабатывать информативные и компактные отчеты, включающие текст, графику и даже звук

БД созданная Access храниться в одном файле с расширением MDB (Microsoft Date Base). Например, Борей.mdb.

При создании Вы указываете, в какой директории будет храниться файл базы данных, и даете своей базе данных имя.

Задание I. Научитесь открывать и работать существующие БД (на примере БД, включенной в как пример в программу MS), предварительно изучив объекты Access.

1. Внимательно изучите состав и взаимосвязи объектов Access.

a.   

b.   

c.   

a.  таблица — основное средство для хранения информации в БД;

b.  запрос — это инструмент для извлечения необходимой информации из исходных таблиц и представления ее в удобной форме;

c.  форма — это основное средство для ввода данных, управления СУБД и вывода результатов на экран монитора;

d.  отчет — это специальное средство для формирования выходных документов и вывода их на принтер;

e.  макросы в Access представляют собой совокупность внутренних команд, предназначенных для автоматизации работы с БД;

f.  модули являются программами, создаваемыми средствами языка VBA, и похожи на макросы в Word и Excel.

2.  Научитесь запускать Access и открывать существующую базу данных - на примере базы данных Борей.

3.  Познакомьтесь с основными объектами Access базы данных Борей – побывать на различных вкладках (таблицы, запросы, формы, отчеты, макросы, модули).

4.  Поработайте с таблицами базы данных, научитесь

Открывать и закрывать таблицы;

Просматривать нескольких таблиц одновременно;

Изменять размеры окна;

Прокручивать содержимое таблицы;

Переходить на заданную запись.

5.  Посмотрите схему данных – открывается окно с помощью кнопки – "Схема данных"    на панели инструментов Access.

Основные необходимы понятия.

Реляционная база данных – совокупность таблиц, которые содержат информацию об объектах и связях предметной области. Вся информация содержится в таблицах!

Таблица – в таблице хранится информация по какой-то определенной теме или информацию о связях объектов, такой как товары, поставщики или поставлено поставщиками. В таблице содержится сведения об однотипных объектах или однотипных связях. Использование отдельной таблицы для каждой темы означает, что соответствующие данные сохранены только один раз, что делает базу данных более эффективной и уменьшает число ошибок при вводе данных.

Таблицы организованы в виде строк и столбцов. Каждая строка содержит набор данных, называемый записью (record) и содержит информацию об одном объекте данного класса. Столбец соответствует одной характеристики (часто еще пользуются синонимом – атрибуту) данного класса объектов (столбец называют часто - полем (fields)). Каждая таблица имеет свое уникальное имя.

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

Поля таблицы просто определяют ее структуру и свойства данных, записываемых в ячейках. Рассмотрим основные свойства полей БД:

1.  Имя поля — определяет, как надо обращаться к данным поля (имена используются как заголовки таблиц).

2.  Тип поля — определяет тип данных, которые могут содержаться в данном поле (текстовые, числовые, дата, Memo, денежный, счетчик и др.).

3.  Размер поля — определяет предельную длину данных, которые могут размещаться в поле.

4.  Формат поля — задает способ отображения данных в ячейках.

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

Любому значению первичного ключа должна соответствовать одна и только одна строка таблицы. Первичный ключ включает одно поле только в том случае, если это поле не содержит повторяющихся значений. Для роли первичного ключа часто вводят специальный атрибут – Код, Номер. Это делают для того, чтобы обеспечить уникальность значений этого атрибута. Искусственный ключ называют суррогатным. Часто естественные атрибуты не могут выполнять такой роли. Например, если взять атрибут Фамилию в таблицы Студенты – то, вы с уверенностью можете сказать, что она может повториться.

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

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

Внешний ключ — это столбец, значения которого соответствуют значениям первичного ключа другой связанной таблицы. 

Задание II. БД можно создать двумя способами:

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

2.  С помощью мастера баз данных.

Мастер – это программа, которая в интерактивном режиме просит вас уточнить детали создаваемого объекта и создает сама объект.

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

Будем создавать с помощью мастера создания баз данных БД Контакты.

Для этого - запустите Access.

1.  Нажмите кнопку Создать на панели инструментов.

В области задач Создание файла в группе Шаблоны выберите На моем компьютере. Выберите значок подходящего шаблона базы данных на вкладке Базы данных и нажмите кнопку OK. В диалоговом окне Файл новой базы данных введите имя базы данных и укажите ее расположение, а затем нажмите кнопку Создать. Следуйте инструкциям мастера баз данных.

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

Лабораторная работа № 2.

Тема: Обсуждение модели предметной области.

При проектировании базы данных решаются две основных проблемы:

·  Каким образом отобразить объекты предметной области в абстрактные объекты модели данных, чтобы это отображение не противоречило семантике предметной области и было по возможности лучшим (эффективным, удобным и т. д.). Часто эту проблему называют проблемой логического проектирования баз данных.

·  Как обеспечить эффективность выполнения запросов к базе данных, т. е. каким образом, имея в виду особенности конкретной СУБД, расположить данные во внешней памяти, создание каких дополнительных структур (например, индексов) потребовать и т. д.? Эту проблему называют проблемой физического проектирования баз данных.

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

·  из каких отношений должна состоять БД и

какие атрибуты должны быть у этих отношений.

Потребности проектировщиков баз данных в более удобных и мощных средствах моделирования предметной области вызвали к жизни направление семантических моделей данных. Главным назначением семантических моделей является обеспечение возможности выражения семантики данных.

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

Одной из наиболее популярных семантических моделей данных является модель СУЩНОСТЬ-СВЯЗЬ (ENTITY-RELATION). Ключевыми понятиями этой модели являются Сущность – Связь-Атрибут.

ПРОЕКТИРОВАНИЕ БД ОПТОВОЙ ФИРМЫ "ЗДОРОВЬЕ"

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

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

1.  Товары, которыми располагает фирма

2.  Список магазинов, через которые происходит сбыт

3.  Форма заказа

Необходимые данные хранятся в следующих документах:

·  Прайс-листы фирмы

·  Бланки заказов

·  Договора с магазинами на сбыт товара и с описанием форм расчета

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

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

Методика получение реляционной схемы из ER-схемы

Шаг 1. Каждая простая сущность превращается в таблицу. Простая сущность - сущность, не являющаяся подтипом и не имеющая подтипов. Имя сущности становится именем таблицы.

Шаг 2. Каждый атрибут становится возможным столбцом с тем же именем; может выбираться более точный формат. Столбцы, соответствующие необязательным атрибутам, могут содержать неопределенные значения; столбцы, соответствующие обязательным атрибутам, - не могут.

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

Шаг 4. Связи многие-к-одному (и один-к-одному) становятся внешними ключами. Т. е. делается копия уникального идентификатора с конца связи "один", и соответствующие столбцы составляют внешний ключ. Необязательные связи соответствуют столбцам, допускающим неопределенные значения; обязательные связи - столбцам, не допускающим неопределенные значения.

Шаг 5. Индексы создаются для первичного ключа (уникальный индекс), внешних ключей и тех атрибутов, на которых предполагается в основном базировать запросы.

Шаг 6.Для реализации связи многие-ко-многим создается отдельная таблица с внешними ключами связываемых сущностей.

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

Разберитесь с ER-диаграммой.

Данной диаграмме в реляционной схеме будет соответствовать следующий набор таблиц:

Магазины Продукты ТипыПродуктов Заказы ПродуктыЗаказа

Магазины (КодМагазина, Название, Адрес, Район города, Директор, Телефон)

Часть Таблицы Магазины

Магазины
КодМагазина	Название	Адрес	РайонГорода	ДиректорМагазина	Телефон
1	Абрикос		Кировский район
2	Быстроном	а	Кировский район
3	Фог-сити	ул. Учебная	Кировский район
4	Лама	ул. Красноармейская (ТЦ «Садко»)	Кировский район
5	Лама	ул. Новгородская	Кировский район
6	Экономный		Кировский район		412-419
7	Русский		Кировский район
8	Добрыня	а	Кировский район
9	Лайт	пр. Кирова 22	Кировский район
10	Копеечка	а	Кировский район	Дорошенко

Продукты (КодПродуктаНазвание, ТипПродукта, Белки, Жиры, Углеводы, Калории)

ТипыПродуктов(КодТипа, Название)

Заказы(КодЗаказа, КодМагазина, ДатаРазмещения, ДатаНазначения, ДатаИсполнения, ДатаОплаты)

ПродуктыЗаказа(КодЗаказа, КодТовара, Цена, Количество, Скидка)

После того, как характеристики типов сущностей мы установим, необходимо подумать какими типами данных они будут представлены в Access и какие дополнительные свойства будут иметь поля БД.

Типы данных в Access:

Текстовый – буквы или цифры. Максимальный размер поля - 255. Максимальное число символов, которое можно ввести в поле указывается в свойстве Размер поля.

Memo – некоторое описание или примечание, размер до 65535 символов

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

Денежный – числовые данные, длиной 8 байт, в операциях предотвращается округление.

Дата/время – значения даты/времени. Длина поля 8 байт.

Счетчик – тип данных, значения которого формируются программой автоматически.

Логический – данные, которые могут иметь одно из возможных значений – Да/Нет, Истина/Ложь.

Поле объекта OLE – объект, подготовленный другим приложением – графический рисунок, документ Word или таблица Excel.

Гиперссылка – адрес гиперссылки, включающий путь к файлу на жестком диске в локальной сети или глобальной.

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

Выбор типа данных.

При выборе типа данных, используемых в поле, необходимо учитывать следующее:

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

-  Сколько места необходимо для хранения значений в поле.

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

-  Нужна ли сортировка или индексирование поля. Сортировать и индексировать поля объектов OLE невозможно.

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

-  Каким образом должны быть отсортированы значения в поле. Числа в текстовых полях сортируются как строки цифр (1, 10, 100, 2, 20, 200 и т. д.), а не как числовые значения. Для сортировки чисел как числовых значений используйте числовые поля или поля, имеющие денежный формат. Также многие форматы дат невозможно отсортировать надлежащим образом, если они были введены в текстовое поле. Для обеспечения сортировки дат используйте поле типа «Дата/время».

-  Необходимо ли хранение таких данных, как документы Microsoft Word и Microsoft Excel, рисунки, звуковые объекты и другие виды двоичных объектов, созданных в других программах. Объекты OLE могут быть связаны или внедрены в поле объекта OLE таблицы Microsoft Access. Для отображения на экране объекта OLE используйте элементы управления в форме или отчете.

-  Для хранения данных в виде текста или комбинации текста и цифр в Microsoft Access существует два типа данных для полей: текстовые и поля МЕМО.

Выбор между типом данных Текстовый и Memo.

-  Текстовый тип данных используется для хранения таких данных, как имена, адреса, а также чисел, не требующих вычислений, например номеров телефонов, инвентарных номеров или почтовых индексов. В текстовом поле может находиться до 255 знаков. По умолчанию устанавливается размер поля 50 знаков. Свойство Размер поля (FieldSize) определяет максимальное количество знаков, которые можно ввести в текстовое поле.

-  Тип данных «Поле MЕМО» используется для хранения более 255 знаков. В поле MЕМО может находится до 65 536 знаков. Для хранения форматированного текста или длинных документов вместо поля MЕМО создайте поле объекта OLE.

-  В текстовых полях и полях MЕМО могут храниться только введенные знаки, знаки пробелов для неиспользованных позиций в поле храниться не будут.

-  Можно отсортировать или сгруппировать текстовые поля и поля типа MEMO, но Microsoft Access использует только первые 255 знаков при сортировке и группировке поля MEMO.

Выбор между типом данных Числовой и Денежный

-  Для хранения числовых данных в Microsoft Access существует два типа данных для полей: числовой и денежный.

-  Числовые поля используются для хранения числовых данных, которые должны использоваться в математических вычислениях, за исключением денежных расчетов, а также вычислений, требующих высокой точности. Тип и размер значений, которые могут находиться в числовом поле, можно изменить в свойстве Размер поля (FieldSize). Например, в занимающее 1 байт на диске поле с размеров «Байт» допускается ввод только целых чисел без десятичных знаков от от 0 до 255.

-  Денежный тип поля используют для предотвращения округления во время вычислений. В денежных полях обеспечивается 15 знаков слева от десятичной запятой и 4 знака справа. Денежное поле занимает 8 байт на диске.

-  Числовые и денежные поля имеют стандартный формат отображения, также можно создать собственный формат.

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

-  Для создания полей, в которые при добавлении записи автоматически вводится уникальное число, в Microsoft Access существует тип данных счетчика. Созданный для записи номер уже не может быть удален или изменен. Поле счетчика может генерировать три типа чисел: последовательно возрастающие на единицу, случайные числа, а также коды репликации (также называемые GUID — глобальные уникальные идентификаторы). Наиболее часто используется счетчик последовательно возрастающих чисел. Такой тип счетчика удобно использовать как первичный ключ таблицы. Счетчик случайных чисел создает случайный номер для каждой записи в таблице. Код репликации используется при репликации базы данных для создания уникальных идентификаторов, обеспечивающих синхронизацию реплик.

Лабораторная работа № 3.

Тема: Создание БД средствами Access.

Рассмотрим последовательность необходимых действий при создании файла и таблиц на примере проекта базы данных “Здоровье”.

Для создания файла новой БД можно воспользоваться командой Файл -> Создать или кнопкой Создать. На панели задач, которая располагается справа выберите строку Новая база данных в группе Создать. Выберите каталог, в котором вы собираетесь сохранить файл базы данных и задайте имя БД. После этого Вы попадаете в окно БД, объекты которой пока еще не заданы.

Создать нашу БД начнем с таблицы ТипыПродуктов. Продукты, которыми торгует наша оптовая фирма, делятся на группы (или категории):

·  Мюсли

·  Соевые продукты

·  Сухофрукты

·  Орехи

·  Орехово-фруктовые смеси

Мы могли эти категории задать в соответствующем столбце таблицы Продукты. При этом у нас были очень часто повторения, на хранение такого типа как “Орехово-фруктовые смеси” потребуется в каждой записи 23 байта (1 байт на символ), поэтому такие характеристики объектов часто выносят в отдельную таблицу, а в основной таблице сохраняют ссылку на соответствующую строку. Такие классы объектов еще называют атрибутивные сущности.

В таблице ТипыПродуктов будет всего лишь два столбца:

КодТипаПродукта и НазваниеТипаПродукта.

Есть несколько правил, которые необходимо соблюдать в Access:

Имена объектов должны быть не более 64 символов и могут включать любые комбинации букв, цифр, пробелов и спецсимволов за исключением. ! и прямых скобок [].

Имя не должно начинаться с пробела и содержать управляющие символы (коды ASCII 0-31)

Избегайте в именах пробелов и слишком длинных имен.

Сделайте текущей вкладку Таблицы и создайте таблицу в режиме конструктора.

Основные параметры структуры таблицы ТипыПродуктов:

Имя поля	Ключ. уникан. поле	Тип данных	Размер	Число десят. знаков	Подпись поля	Значение по умолчанию	Условие на значение	Сооб-щение об ошибке	Обяза- тельное поле	Индексиро-ванное поле
КодТипаПродукта	Да	Счетчик	Длинное целое	-	Код типа	-	-	-	Да совпадения не допускаются
НазваниеТипаПродукта	-	Текстовый	50	-	Название типа	-	-	-	Да, совпадения не допускаются	Да

Для указания того, что поле ключевое – необходимо его выделить и щелкнуть на инструменте Ключ!