Декомпозиция исходных отношений, допускающая соединение без потерь.

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

ЗНАКОМСТВО С СУБД MS ACCESS

Первая лабораторная работа посвящена знакомству с популярной системой управления базами данных MS Access. Цель работы: изучение базовых методов работы с СУБД MS Access.

Теоретический материал.

Система Microsoft Access является одним из основных компонентов Microsoft Office и предназначена для работы с реляционными базами данных. Особенность данной СУБД: вся информация базы данных хранится в одном файле (*.mdb). Кроме информации таблиц, в этом же файле сохраняются компоненты приложения для работы с базой данных - экранные формы, отчеты, запросы, программные модули.

Для работы с базой данных система использует Microsoft Jet database engine - систему управления базами данных, извлекающую и сохраняющую данные в пользовательских и системных задачах. Ядро базы данных Microsoft Jet можно рассматривать как компонент диспетчера данных, с помощью которого строятся остальные системы доступа к данным, такие как Microsoft Access и Microsoft Visual Basic.

Создание базы данных

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

Таблица 1.1. Поставщики

Номер	ФИО	Адрес	Товар	Цена,р
	Иванов И.И.	ул. Цандера, 26	Хлеб ржаной
	Петров П.Н.	пр. Мира, 67	Шоколад

В системе Microsoft Access процесс создания базы данных выполняется следующим образом. При запуске системы появляется диалоговое окно для выбора режима работы (рис. 1.1.), в котором следует выбрать пункт «Новая база данных».

Рис. 1.1. Создание файла

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

Выберем папку на диске для сохранения файла базы данных и зададим имя базы - SUPPLIER, нажмем на кнопку «Создать», после чего откроется окно базы данных.

Далее необходимо задать структуру таблиц в соответствии с описанной заранее моделью.

Выберем пункт Создание таблицы в режиме конструктора (рис. 1.2) и опишем структуру главной таблицы базы данных, т.е. зададим имя, тип, размер каждого поля таблицы, а также первичный ключ (если необходимо) и индексированные поля (рис. 1.3).

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

Рис. 1.2. Окно базы данных

Рис. 1.3. Описание структуры таблицы в конструкторе

Стандартный режим работы с таблицами

Если база данных состоит из нескольких связанных таблиц, заполнение базы данных информацией следует начинать со справочников - иначе при заполнении главных таблиц возникнут конфликты сохранения ссылочной целостности базы. Например, если в таблице-справочнике товаров не будет наименования товара, указанного для поставщика, появится сообщение "Введенное значение не подходит для данного поля" и Вы не сможете сохранить данные, пока не укажете правильное значение.

Закроем окно, сохранив при этом новую таблицу под именем «Supplier». Теперь наша таблица появится в окне базы данных (рис. 1.4.).

Рис. 1.4. Сохранение структуры базы данных «Supplier»

Чтобы работать с информацией таблицы базы данных (добавлять записи, редактировать записи и удалять записи) следует выбрать ее в разделе объектов базы данных "Таблицы" (см. рис. 1.5.) и двойным щелчком мыши открыть.

Рис. 1.5. Выбор таблицы для обработки

Таблица откроется в стандартном режиме работы с информацией, как показано на рис. 1.6.

Рис. 1.6. Стандартный режим работы с таблицей базы данных

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

Рис. 1.7. Задание параметров подстановки

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

В СУБД Microsoft Access существуют также дополнительные возможности использования в таблицах режима "Подстановка" для показа и выбора данных из раскрывающихся списков ("тип источника строк" - таблица или запрос, список значений или список полей). Этот режим можно создать с помощью Мастера, если выбрать тип поля "Мастер подстановок" или описать самому на странице "Подстановка". В примере этот режим задается для поля FIO таблицы «Supplier» (см. рис. 1.7.). В дальнейшем для поля с описанными свойствами раздела "Подстановка" на экранной форме будет автоматически создаваться объект типа "Поле со списком".

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

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

Задание

Создать в среде MS Access таблицу согласно заданному варианту.

Порядок выполнения работы на примере первого варианта:

1. Разработайте структуру БД "Студенты".

1. После запуска MS Access установите переключатель "Новая база данных".

1. После щелчка на кнопке ОК откроется диалоговое окно "Файл новой базы данных". Сохраните файл базы данных "Студенты".

1. В окне "База данных" выберите объект "Таблицы" и щелкните на кнопке "Создать".

1. В диалоговом окне "Новая таблица" выберите "Конструктор таблиц".

1. Создайте таблицу тбл_Студенты_анкданные. Для этого в столбце "Имя поля" укажите все необходимые имена полей; в столбце "Тип данных" укажите тип данных для каждого поля:

Фамилия	Текстовый
Имя	Текстовый
Отчество	Текстовый
№зачетки	Текстовый
ДатаРождения	Дата/время (краткий формат даты)
ДомашнийАдрес	Текстовый
Телефон	Текстовый
СемейноеПоложение	Поле со списком. Чтобы его создать, в столбце "Тип поля" выберите "Мастер подстановок". - в появившемся окне "Создание подстановки" выберите "Будет использоваться фиксированный набор значений" и щелкните на кнопке <Далее>; - укажите число столбцов - 1, - и заполните столбец (замужем, незамужем, женат, холост), <Далее>; - оставьте без изменения подпись столбца подстановки, <Готово>.

7. Выберите ключевое поле - №зачетки. Для этого щелкните на кнопке области выделения, слева от столбца "Имя поля", - поле будет выделено; щелкните правой кнопкой мыши и в открывшемся контекстном меню выберите пункт "Ключевое поле" (или кнопкой "Ключевое поле" на панели инструментов). В окне "Свойства поля" в строке "Обязательное поле" должно стоять значение "Да", в строке "Индексированное поле" - "Да (Совпадения не допускаются)".

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

1. Чтобы начать ввод данных в таблицу, щелкните на кнопке "Вид" на панели инструментов и перейдите в режим таблицы.

1. Заполните таблицу:

- для перехода к следующему полю используйте клавишу ТАВ или ®;

- после заполнения всех полей записи, для перехода к следующей записи также нажмите клавишу ТАВ или ENTER;

- изменить ширину столбца таблицы можно аналогично тому, как это делается в таблицах Word;

- для редактирования данных щелкните мышкой в той ячейке, содержимое которой хотите изменить;

- для удаления записи: щелкните на кнопке области выделения (слева от первого поля записи) - вся запись выделяется целиком, затем нажмите клавишу DELETE на клавиатуре или щелкните на кнопке "Вырезать" на панели инструментов.

1. Закончив ввод и редактирование данных, закройте таблицу.

1. Создайте ленточную автоформу для просмотра таблицы:

- щелкните на вкладке "Формы" в окне "База данных";

- щелкните на кнопке "Создать";

- в диалоговом окне "Форма" выберите пункт "Автоформа: ленточная";

- в раскрывающемся списке выберите имя нужной таблицы;

- щелкните на кнопке ОК - автоформа будет создана.

1. Просмотрите таблицу с помощью созданной формы.

1. Сохраните форму, используя префикс фрм при задании имени формы.

1. Создайте форму для ввода и редактирования записей, используя автоформу в столбец (действия как в п.12).

1. Добавьте несколько записей в таблицу с помощью новой формы.

1. Сохраните форму.

Варианты заданий

Номер варианта	Задание	Данные
	Составить таблицу анкетных данных студентов.	ФИО, номер студенческого билета, номер зачетной книжки, группа, дата и место рождения, домашний адрес, телефон, нуждается ли в общежитии
	Составить таблицу с данными о имеющихся в наличии телевизорах	Порядковый номер, модель, серийный номер
	Составить таблицу для автосалона	марка, модель, комплектация, цвет, цена, наличие автомобилей в автосалоне
	Составить таблицу для автосервиса	номера автомобилей, принятых в ремонт, марка, модель, неисправность, цена, дата приема, дата возврата
	Составить таблицу для салона связи	порядковый номер, марка товара, модель, серийный номер, наличие товара
	Составить таблицу клиентов салона красоты	порядковый номер, имя , фамилия , вид услуги, адрес
	Составить таблицу для автошколы	ФИО, год рождения, кол-во часов, категория, дата выпуска
	Составить таблицу наличия автошин в магазине	шифр резины, производитель, сезонность, размеры, наличие на складе
	Составить таблицу расписания поездов	номер поезда, пункт отправки, пункт назначения, время отправления, ожидаемое время прибытия
	Составить таблицу расписания движения пригородных автобусов	Шифр рейса, марка автобуса, остановки, время остановки
	Составить таблицу игроков в игру «Counter-strike»	Личный номер игрока ,Ф.И.О., сколько играет, предпочитаемые сервера
	Составить таблицу родившихся детей в роддоме	Личный номер, Ф.И.О., родители, дата
	Составить график дежурств сетевых администраторов	номер, ФИО, день, время дежурства
	Составить таблицу оборудования сетевой лаборатории	инвентарный номер, вид оборудования, производитель, модель, дата поступления, техническое состояние
	Составить таблицу штрафов, выписанных ГИБДД	номер квитанции, ФИО плательщика, адрес, паспортные данные, ФИО инспектора.
	Реализовать структуру телефонной книжки	номер записи, имя, фамилия, компания, должность, домашний телефон , мобильный телефон, примечания
	Реализовать структуру сети магазинов	порядковый номер, название магазина, адрес, имя администратора и т.д.
	Создать базу данных соревнований по легкой атлетике	спортивная дисциплина, вид соревнований, дата проведения, победитель, призеры, результат
	Реализовать базу данных оранжереи	номер записи, латинское название растения, русское название растения, откуда получено, дата получения, состояние
	Создать базу данных снегоуборочной техники	организация, порядковый номер, вид машины, производитель, марка, год выпуска

Содержание отчёта

• титульный лист
• задание
• распечатки экрана СУБД MS Access, показывающие заданную таблицу и ее структуру.

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

СОЗДАНИЕ СВЯЗАННЫХ ТАБЛИЦ

Вторая лабораторная работа посвящена созданию базы данных из нескольких связанных таблиц. Целью работы является изучение методов работы со схемой данных в среде MS Access

Теоретический материал

Связи между объектами реального мира могут находить свое отражение в структуре данных, а могут и подразумеваться, т.е. присутствовать на неформальном уровне.

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

Выделяют три разновидности связи между таблицами базы данных:

• «один–ко–многим»;
• «один–к–одному»;
• «многие–ко–многим».

Отношение «один–ко–многим»

Отношение "один–ко–многим" имеет место, когда одной записи главной таблицы может соответствовать несколько записей в подчиненной. Связь «один–ко–многим» является самой распространенной для реляционных баз данных. Она позволяет моделировать также иерархические структуры данных.

Отношение «один–к–одному»

Отношение «один–к–одному» имеет место, когда одной записи в главной таблице соответствует одна запись в подчиненной. Это отношение встречается намного реже, чем отношение «один–ко–многим». Для чтения связанной информации в нескольких таблицах приходится производить ряд операций чтения вместо одной, когда данные хранятся в одной таблице.

Отношение «многие–ко–многим»

Отношение «многие–ко–многим» применяется в следующих случаях:

• одной записи в главной таблице соответствует более одной записи в подчиненной;
• одной записи в подчиненной таблице соответствует более одной записи в главной.

Каждую связь «многие–ко–многим» в реляционной базе данных необходимо заменить на связь «один–ко–многим» (одну или более) с помощью введения дополнительных таблиц.

Задание

Взяв за основу таблицу, созданную в предыдущей лабораторной работе, разделить ее на две таблицы и связать их отношением «один-ко-многим».

Порядок выполнения работы

1. Возьмем исходную таблицу Supplier (см. рис. 2.1.) и разделим ее на

Рис. 2.1. Таблица Supplier

две таблицы: Supp2_1 и Supp2_2 (см. рис. 2.2.).

Рис. 2.2. Таблица Supp2_1

Рис. 2.3. Таблица Supp2_2

2. Откроем таблицу Supp2_1 в режиме конструктора. Щелкнув правой кнопкой по полю перед именем поля FIO, выберем пункт меню «Ключевое поле» (Рис. 2.4.). Этим мы обеспечим уникальность значений в поле FIO – каждый поставщик упомянут не более одного раза.

Рис. 2.4. Изменение ключевого поля

3. Выберем на панели инструментов кнопку «Схема данных»:

При этом откроется окно «Добавление таблицы» (рис. 2.4.).

Рис. 2.5. Добавление таблицы в схему данных

Нажимая на кнопку «Добавить», добавим в схему данных таблицы Supp2_1 и Supp2_2. Затем нажмем «Закрыть».

4. В открывшейся схеме данных нажмем левой кнопкой на поле FIO таблицы Supp2_1 и «перетащим» связь к полю FIO таблицы Supp2_2. При этом откроется окно «Изменение связей». Проставим «галочки» в пунктах «Обеспечение целостности данных», «Каскадное обновление связанных полей» и «Каскадное удаление связанных записей» (рис.2.6.). Таким образом, при удалении поставщика из главной таблицы (в нашем примере Supp2_1) , все записи, содержащие его имя, будут удалены и из подчиненной таблицы (в нашем примере Supp2_2).

Рис. 2.6. Окно «Изменение связей»

Нажав кнопку «Создать», завершим создание связи «один-ко-многим» (рис. 2.7.).

Если в окне «Изменение связей» выбрать только пункт «Обеспечение целостности данных», то каскадного обновления не будет, но СУБД не позволит вводить в поле FIO таблицы Supp2_2 значение, которого нет в таблице Supp2_1.

Рис. 2.7. Связь «один-ко-многим»

Содержание отчёта

• титульный лист
• задание
• распечатки экрана СУБД MS Access, показывающие:
• исходную таблицу
• полученные таблицы и их структуры
• окно изменения связей
• схему данных со связью «один-ко-многим»

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

ДЕКОМПОЗИЦИЯ ИСХОДНЫХ ОТНОШЕНИЙ, ДОПУСКАЮЩАЯ СОЕДИНЕНИЕ БЕЗ ПОТЕРЬ.

Третья лабораторная работа развивает тему второй и демонстрирует методы разбиения одной таблицы на несколько без потерь структурной информации. Целью работы является изучение разновидностей связи между таблицами в среде MS Access.

Теоретический материал

Любое отношение реляционной базы данных содержит информацию не только в виде значений элементов, но и в виде самой структуры отношения. Эта структурная информация легко может быть потеряна при разделении отношения на несколько. Если отношение Supplier (рис.2.1.) разделить на отношения R1(FIO, Nom, Tovar) и R2(Adress, Price), то соединить их обратно в одну таблицу будет невозможно, так как будет потеряна связь между адресами и фамилиями, товарами и ценами. Поэтому каждый вариант декомпозиции должен проверяться на возможность соединения без потерь.

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

В нашем примере пересечение R1∩R2 даст пустое множество, что сразу указывает на потерю информации при таком разделении.

Задание

Взяв за основу отношение, созданное в лабораторной работе №1, произвести его декомпозицию на два отношения, допускающую соединение без потерь, и связать их отношением «один-к-одному».

Порядок выполнения работы

1.Для исходной таблицы из первой лабораторной работы придумать не менее двух вариантов декомпозиции. Например, таблицу Supplier (рис.2.1.) можно разделить на таблицы Supp2_1 (рис.2.2.) и Supp2_2 (рис.2.3.), а можно на таблицы R1(FIO, Nom, Tovar) и R2(Adress, Price), как показано выше.

2. Для обоих вариантов декомпозиции выполняется проверка на возможность соединения без потерь. В нашем примере:

Supp2_1 ∩ Supp2_2 = FIO

Supp2_1 - Supp2_2 = Nom, Adress

FIO → Nom, Adress

Таким образом, такая декомпозиция допускает соединение без потерь. Вариант с декомпозицией на отношения R1(FIO, Nom, Tovar) и R2(Adress, Price) рассмотрен выше.

3. Создадим в среде MS Access таблицы Supp2_1 и Supp2_2, как это сделано в предыдущей работе. При этом в обеих таблицах, в режиме «Конструктор» установим ключевое поле FIO.

4. В режиме «Схема данных» создадим связь между таблицами по выбранному полю. Это должна быть связь «один-к-одному» (рис. 3.1.).

Рис. 3.1. Связь «один-ко-одному»

Содержание отчёта

• титульный лист
• задание
• расчет декомпозиции исходного отношения, допускающей соединение без потерь
• распечатки экрана СУБД MS Access, показывающие:
• исходную таблицу
• полученные таблицы и их структуры
• окно изменения связей
• схему данных со связью «один-к-одному»

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

ИСПОЛЬЗОВАНИЕ ЗАПРОСОВ

Четвертая лабораторная работа посвящена использованию запросов в СУБД MS Access. Целью работы является изучение различных видов запросов к базе данных и механизма их реализации в СУБД MS Access.

Теоретический материал

Работа любой современной СУБД может быть представлена в виде последовательности запросов и ответов.

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

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

2. Перекрестный запрос с формированием двухмерной итоговой таблицы, с группировкой по двум выражениям, одно из которых становится заголовком строки, другое - заголовком столбца.

3. Запрос на создание новой таблицы.

4. Запросы на изменение данных:

• обновление данных - команда занесения общих изменений в группу записей одной или нескольких таблиц;
• добавление данных - команда добавления группы записей из одной или нескольких таблиц в конец одной или нескольких таблиц;
• удаление данных - команда удаления группы записей из одной или нескольких таблиц.

Задание

Над отношением, созданным в лабораторной работе №1, выполнить операции «проекция» и «выборка по условию», используя механизм «Создание запроса в режиме конструктора».

Порядок выполнения работы

1. Открыть базу данных (в нашем примере база данных SUPPLIER), выбрать пункт меню «Запросы», затем выбрать «Создание запроса в режиме конструктора».

2. Добавить в запрос нужные таблицы, например, таблицу Supplier.

3. В открывшемся окне выбрать нужные поля таблицы Supplier, например Nom и Address. Фактически, этим выполняется операция «проекция» (рис. 4.1).

Рис. 4.1. Запрос на выборку

4. В поле «Условие отбора» записывается условие выборки. Затем запрос сохраняется. Всякий раз при запуске этого запроса, он будет формироваться заново, выполняя заданные операции над выбранными таблицами.

Содержание отчёта

• титульный лист
• задание
• распечатки экрана СУБД MS Access, показывающие:
• исходную таблицу
• окно «запрос на выборку»
• результат выполнения запроса

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