Система управления базами данных Microsoft Access
Лабораторный практикум
Пенза 2008
Список сокращений:
CL – щелчок левой клавишей мыши;
RCL – щелчок правой клавишей мыши;
DD – перемещение при нажатой левой клавиши мыши;
DCL – двойной щелчок левой клавишей мыши.
Лабораторная работа 1.
Проектирование базы данных. Создание таблицы
Цель работы: изучение основных понятий и терминов, принятых в системах управления базами данных (далее – СУБД); изучение основных этапов проектирования базы данных (далее – БД); изучение пользовательского интерфейса СУБД Microsoft Access (далее – Access); освоение приемов создания таблиц в Access.
Порядок выполнения работы:
1 Изучить основные понятия и термины, принятые в СУБД.
2 Изучить пользовательский интерфейс Access.
3 Спроектировать базу данных (по тренировочному заданию).
4 Выполнить тренировочные задания. Ответить на контрольные вопросы.
5 Выполнить контрольное задания в соответствии с вариантом.
6 Подготовить отчет о выполнении лабораторной работы.
Рекомендации. Приступая к освоению основных возможностей Access, необходимо вспомнить приемы работы в ОС Windows и с приложениями из пакета Microsoft Office.
Общие сведения
Существует несколько классических моделей данных, полагаемых в основу информационных систем. Наиболее часто используются следующие три: иерархическая, сетевая и реляционная. Многие существующие информационные системы построены на основе реляционной модели.
Информационная система (information system) – это приложение, предназначенное для хранения и обработки данных. Основой информационной системы является база данных с информацией, хранящейся в одной или нескольких связанных таблицах.
База данных (data base) представляет собой совокупность связанных таблиц (в предельном случае – одну таблицу), предназначенных для хранения определенной информации. Термином «база данных» часто называют приложение, использующее базу данных и обладающее интерфейсом просмотра и правки, а также средствами обработки хранящейся в базе данных информации. Однако такое приложение лучше называть информационной системой. Базами данных, например, являются файлы приложения Access, входящего в пакет Microsoft Office.
Система управления базами данных (СУБД) – комплекс языковых и программных средств, предназначенных для создания, ведения и совместного использования баз данных многими пользователями.
Реляционная модель (relational model) – модель БД, основными элементами которой являются таблицы, представляющие сущности, в которых столбцы представляют атрибуты сущностей, а строки описывают экземпляры сущностей. Модель данных также подразумевает наличие операторов для генерации новых таблиц на основе существующих, называемых запросами (query), именно таким способом пользователи могут манипулировать данными и получать необходимую информацию.
Сущность (entity) – множество однотипных объектов, называемых экземплярами (instance). Каждый экземпляр характеризуется набором свойств, называемых атрибутами сущности (attribute). Каждый экземпляр индивидуален и отличается от всех остальных экземпляров в множестве.
Сущностями могут быть люди, места, самолеты, рейсы, цвет и т.д. Необходимо различать такие понятия, как тип сущности и экземпляр сущности. Понятие тип сущности относится к набору однородных личностей, предметов, событий или идей, выступающих как целое. Экземпляр сущности относится к конкретной вещи в наборе. Например, типом сущности может быть ГОРОД, а экземпляром – Москва, Киев и т.д.
Атрибут (attribute) представляет собой определенное свойство (характеристику) данной сущности. Рекомендуется в качестве атрибутов выделять атомарные (неделимые) свойства сущности.
Атрибут – поименованная характеристика сущности. Его наименование должно быть уникальным для конкретного типа сущности, но может быть одинаковым для различного типа сущностей (например, ЦВЕТ может быть определен для многих сущностей: СОБАКА, АВТОМОБИЛЬ и т.д.). Атрибуты используются для определения того, какая информация должна быть собрана о сущности. Примерами атрибутов для сущности АВТОМОБИЛЬ являются ТИП, МАРКА, НОМЕРНОЙ ЗНАК, ЦВЕТ и т.д. Здесь также существует различие между типом и экземпляром. Тип атрибута ЦВЕТ имеет много экземпляров или значений: Красный, Синий, Банановый, Белая ночь и т.д., однако каждому экземпляру сущности присваивается только одно значение атрибута. Абсолютное различие между типами сущностей и атрибутами отсутствует. Атрибут является таковым только в связи с типом сущности. В другом контексте атрибут может выступать как самостоятельная сущность. Например, для автомобильного завода цвет – это только атрибут продукта производства, а для лакокрасочной фабрики цвет – тип сущности.
Таблица (table) – множество ячеек с данными, образующих строки и столбцы прямоугольной таблицы. Таблица реализует сущность в понятии реляционной модели данных. Строки таблицы представляют экземпляры сущности и называются записями (records). Столбцы таблицы представляют атрибуты сущности и называются полями (fields).
Поле таблицы (table field) – столбец в прямоугольной таблице. Поле таблицы реализует атрибут в понятии реляционной модели, при этом данные, хранятся в ячейках одного столбца, должны принадлежать одному домену. Домен определяет набор допустимых значений и операций над данными. То есть данные в ячейках одного столбца должны быть одного типа.
Первичный ключ – атрибут, однозначно идентифицирующий каждый экземпляр сущности. Одно из сильных ограничений реляционной модели состоит в том, что любая таблица имеет первичный ключ, т.е. не бывает одинаковых записей.
Ключевое поле (key field) – поле, представляющее первичный ключ или являющееся частью составного первичного ключа.
Связь (relationship) – это логическое отношение между сущностями, выражающее некоторое ограничение или правило. В реляционной модели вводится понятие реляционной связи (relation) – это связь между записями, основанная на совпадении значений атрибутов, по которым устанавливается связь.
Связь – ассоциирование двух или более сущностей. Одно из основных требований к организации базы данных – это обеспечение возможности отыскания одних сущностей по значениям других, для чего необходимо установить между ними определенные связи.
База данных является информационной моделью, поэтому ее проект начинают с анализа предметной области и выявления требований к ней отдельных пользователей, т.е. проводят обобщенное неформальное описание проектируемой базы данных. Это описание, выполненное с использованием естественного языка, математических формул, таблиц, графиков и других средств, понятных всем людям, работающих над БД, называют инфологической (концептуальной) моделью данных. Такая человеко-ориентированная модель полностью независима от физических параметров среды хранения данных. Инфологическая модель предметной области строится первой. Затем на ее основе строится даталогическая модель (ДЛМ). Даталогическая модель является моделью логического уровня и представляет собой отображение логических связей между элементами данных безотносительно к их содержанию и среде хранения. Эта модель строится в терминах информационных единиц, допустимых в той конкретной СУБД, в среде которой мы проектируем базу данных. Этап создания ДЛМ называется даталогическим проектированием. Описание логической структуры базы данных на языке СУБД называется схемой. Для привязки даталогической модели к среде хранения используется модель данных физического уровня (для краткости часто называемая физической моделью). Эта-модель определяет используемые запоминающие устройства, способы физической организации данных в среде хранения. Модель физического уровня также строится с учетом возможностей, предоставляемых СУБД. Описание физической структуры базы данных называется схемой хранения. Соответствующий этап проектирования БД называется физическим проектированием. Нужные данные отыскиваются СУБД на внешних запоминающих устройствах по физической модели данных.
При построении инфологических моделей можно использовать язык ER-диаграмм (от англ. Entity-Relationship, т.е. сущность-связь). В них сущности изображаются помеченными прямоугольниками, ассоциации – помеченными ромбами или шестиугольниками, атрибуты – помеченными овалами, а связи между ними – ненаправленными ребрами, над которыми может проставляться степень связи (1 или буква, заменяющая слово "много") и необходимое пояснение.
Между двумя сущностям, например, А и В возможны четыре вида связей:
· ОДИН-К-ОДНОМУ (1:1), т.е. в каждый момент времени каждому представителю (экземпляру) сущности А соответствует 1 или 0 представителей сущности В;
· ОДИН-КО-МНОГИМ (1:М), т.е. одному представителю сущности А соответствуют 0, 1 или несколько представителей сущности В;
· МНОГИЕ-К-ОДНОМУ (М:1);
· МНОГИЕ-КО-МНОГИМ (М:N);
например,если связь между сущностями МУЖЧИНЫи ЖЕНЩИНЫназывается БРАК, то существует четыре возможных представления такой связи (рисунок 1).
Рисунок 1 – Виды связей
В качестве учебного объекта в данном лабораторном практикуме выбрана реляционная СУБД Microsoft Access, входящая в пакет Microsoft Office. Access предназначена для разработки диалоговых офисных информационных систем, она использует реляционную модель данных и графический интерфейс Windows.
Проектирование БД
Постановка задачи. Необходимо спроектировать базу данных Работник, содержащую поля:
ФИО, Адрес, Профессия, Дата рождения, Дата поступления на работу, Расценка, Номер бригады, ФИО бригадира, Номер телефона бригадира, Объем выполненной работы бригадой,
где Работник – имя таблицы;
ФИО, Адрес, Профессия, Дата рождения, Дата поступления на работу, Расценка, Номер бригады, ФИО бригадира, Номер телефона бригадира, Объем выполненной работы бригадой – имена полей;
Иванов; Титова 34-15; штукатур; 1949; 1983;25;500;0; 2; Лапова;2-45;0 и т.п. – записи.
Проектирование БД. Проектирование БД включает несколько основных этапов.
1) Проводится анализ целей БД и выявляются требования к ней отдельных пользователей.
2) Составляется список исходных и выходных данных.
3) Разрабатывается схема данных:
· составляется генеральный список полей, определяется тип для каждого поля;
· поля генерального списка распределяются по базовым таблицам; сущности группируются по "сходству" (частоте их использования для выполнения тех или иных действий) и по количеству ассоциативных связей между ними (самолет – пассажир, преподаватель – дисциплина, студент – сессия, бригадир – работник и т.д.);
· определяется структура таблиц и свойства полей; для каждой таблицы определяется ключевое поле, однозначно определяющее всю запись;
· устанавливаются связи между таблицами.
Проектирование БД Работник. При проектировании базы данных Работник для более эффективной обработки данных исходную таблицу необходимо разбить на несколько более мелких, при этом, каждая будет охватывать некоторую смысловую область исходной таблицы. Эти таблицы должны обязательно иметь общие поля (с одинаковыми именами и данными), по которым будет устанавливаться связь между ними.
На первом этапе проектирования нужно выделить основные сущности: Работник и Бригадир. При построении инфологической модели БД необходимо использовать метод Сущность-Связь (рисунок 1).
 |
Рисунок 1 – Метод «Сущность-Связь»
При проектировании БД нужно выделить следующие две таблицы (с входящими в них полями):
Работник(ФИО работника, Адрес, Профессия, Дата рождения, Дата поступления на работу, Номер бригады, Расценка, Объем выполненной работы);
Бригадир(Номер бригады, ФИО бригадира, Телефон бригадира, Объем выполненной работы бригадой).
Поля могут содержать данные различного типа: текстовые, числовые, логические, денежные, дата/время, счетчик и т.д.
Общими полями для таблиц Работник и Бригадир являются поля Номер бригады.
Для каждой таблицы необходимо определить ключевое поле, однозначно определяющее всю запись. Например, для таблицы Бригадир таким полем будет являться поле Номер бригады, для таблицы Работник – поле Счетчик.
Тренировочное задание 1. Создание БД в Access
Работа с Access:
1. Запустить Access:
CL Пуск, Все программы, Microsoft Office, Access, CL.
Окно Access (как и другие приложения Microsoft Office) под строкой заголовка содержит строку меню и панель инструментов (рисунок 2).
2. В окне Access CL пункты Создать Þ Новая база данных…, CL ОК. В появившемся окне Файл новой базы данных сохранить в свою папку файл под именем Работа, CL кнопку Создать (при этом появится окно БД Работа).
Окно БД в левой панели Объекты содержит перечень объектов, которые составляют БД. В правой панели представлен список таблиц, составляющих БД, в пустой БД представлен список способов создания БД (рисунок 2).
Рисунок 2 – Окно БД в Access
Внимание! База данных включает различные объекты для ввода, хранения и управления информацией. Объектом в Access считается все, что может иметь имя. Основным типом объектов в БД является таблица, остальные типы объектов – это:
· различные способы представления информации из таблиц (формы – для ввода данных и их наглядного представления, отчеты – для форматирования и группировки данных, вычисления итогов и печати данных в определенном пользователем виде)
· или действия над таблицами (запросы – для получения нужных данных из одной или нескольких таблиц, макросы – для описания одного или нескольких действий, которые автоматически выполняются в ответ на определенное событие, модули – объекты, содержащие программы на языке Visual Basic).
Все объекты БД хранятся в одном файле с расширением *.mdb. В один момент времени допускается работа только с одной БД.
Процесс создания в Access отдельной таблицы в составе БД состоит из следующих этапов:
· создание структуры таблицы (задание имен и типов полей, задание ключевого поля);
· ввод данных в таблицу.
3. Создать структуру таблицы Работник:
1) в окне БД в левой панели CL закладку Таблицы,
2) в правой панели CL строку Создание таблицы в режиме конструктора; CL кнопку ОК;
3) в появившемся окне Таблица1: Таблица в столбце Имя поля ввести имена полей (рисунок 3).
Внимание! Окно режима Конструктор состоит из двух частей: в верхней половине содержится список полей таблицы (Имя поля), тип хранимых в них данных (Тип данных) и область Описание для внесения необходимых разработчику примечаний; в нижней половине (Свойства поля) – вкладки, на которых задаются дополнительные свойства текущего поля, и область, в которой при выделении каждой из строк в верхней части окна или на вкладках появляются комментарии-подсказки. Имена полей выбираются произвольно; имя может включать до 64 произвольных символов (в том числе и русские буквы), кроме символов точка (.), восклицательный знак (!), апостроф (‘), квадратные скобки [ ] и некоторых специальных знаков (например, $, %, #). Рекомендации: избегайте слишком длинных имен; в именах из нескольких слов каждое слово начинайте с заглавной буквы, не разделяя слова пробелами, например, КодТовара (это важно в СУБД при создании процедур); избегайте имен, совпадающих с именами встроенных функций (например, Name).
4) в столбце Тип данных CL на значок раскрывающегося списка и выбрать нужный тип (рисунок 3).
Внимание! Тип поля определяется типом данных, которые оно содержит, и тем, сколько места они будут занимать на диске:
§ Счетчик – целые числа, которые задаются автоматически (но могут быть изменены пользователем);
§ Текстовый – алфавитно-цифровые данные (до255 символов);
§ Числовой – числовые данные;
§ Дата/время – значения даты и времени;
§ Денежный – числа в денежном формате;
§ Поле МЕМО – длинный текст (например, примечания);
§ Логический – поля, которые могут иметь только одно значение из двух (Да/Нет, Истина/Ложь);
§ Поле объекта OLE – внешние объекты, созданные в других приложениях;
§ Гиперссылка – адрес ссылки на документ, находящейся в WWW, локальной сети или на локальном компьютере;
§ Мастер подстановки – мастер, создающий поле, связанное с полем другой таблицы.
В нижней половине окна можно задать дополнительные свойства полей. Поле каждого типа имеет свой набор свойств:
§ Размер поля – определяет максимальную длину текстового или числового поля;
§ Формат поля – устанавливает формат данных, для которых можно определять маску ввода;
§ Обязательное поле – обязательно надо заполнить (это свойство назначается пользователем);
§ Индексированное поле – ускоряет поиск и сортировку в данном поле;
§ и др.
Описание дополнительных свойств позволяет сэкономить время при разработке приложения, так как не нужно всякий раз описывать поведение каждого поля в формах и отчетах (рисунок 3). Свойства полей таблицы можно использовать для контроля при вводе и изменении данных (например, Размер поля, Маска ввода, Обязательное поле). Проверка условий контроля данных происходит в момент, когда пользователь переходит к вводу данных в следующее поле. При нарушении каких-либо ограничений целостности выводится сообщение о том, что сделана попытка ввести недопустимые данные, и Access не позволяет пользователю перейти к вводу данных в другое поле.
5) задать ключевое поле (рисунок 3):
RCL на имени поля, в панели инструментов CL кнопку Ключевое поле (в левой части строки появится изображение ключа).
Внимание! Ключевое поле – это поле, значение которого однозначно определяет запись в таблице (может устанавливаться автоматически или пользователем). В качестве ключевого поля чаще всего используют поле, содержащее тип данных счетчик, можно другие поля (номер паспорта, код товара и т.п.). Когда задано ключевое значение поля, программа указывает, что это поле является индексированным, и совпадения в нем не допускаются. Индекс ускоряетоперации выполнения запросов, поиска и сортировки по данному полю, но замедляет ввод, редактирование и обновление данных, так как индексы автоматически обновляются для всей таблицы.
Рисунок 3 – Создание таблицы Работник в режиме Конструктор
4. Сохранить структуру таблицы: CL кнопку Сохранить на панели инструментов, ввести имя таблицы Работник, CL кнопку ОК.
5. Ввести данные:
1) перейти в режим отображения таблицы – на панели инструментов CL кнопку Вид (Режим таблицы);
2) ввести записи (8 строк – из тренировочного задания – рисунок 4),
3) при этом переход к следующей записи при вводе происходит автоматически, переход между элементами записей – с использованием клавиш управления курсором, данные сохраняются автоматически при вводе;
4) закрыть окно ввода.
Действия по вводу, удалению и редактированию данных схожи с действиями в других приложениях, но есть и некоторые особенности. При вводе данных в режиме Таблицав поле маркера выделения записи («серый» столбец, расположенный слева от полей таблицы), может отображаться один из следующих символов:
* (звездочка) – обозначает «пустую» запись в конце таблицы;
► (стрелка) – обозначает выделенную (активную) запись;
(карандаш) – обозначает, что в записи были сделаны изменения.
Рисунок 4 – Ввод данных в Режиме таблицы
6. Создать структуру таблицы Бригадир (см. п. 5).
7. Ввести данные в таблицу Бригадир (рисунок 5), ключевое поле – НомерБригады.
Рисунок 5 –Таблица Бригадир
8. Создать схему данных (указать связи):
Внимание! Связи являются необходимым элементом структуры БД. После создания различных таблиц нужно продумать, каким образом Access будет объединять эти данные при их извлечении из БД. Важным шагом является определение связей между таблицами. После этого становится возможным создание запросов, форм и отчетов, в которых выводятся данные из нескольких таблиц сразу. Обычно с ключом одной таблицы связывается одноименное поле (внешний ключ) другой таблицы. Например, для сопоставления сведений о работниках и о бригадирах, следует определить связь по полю НомерБригады в двух таблицах: в таблице Бригадир общее поле является ключом, а в таблице Работник – внешним ключом.
1) закрыть все открытые таблицы;
2) CL на панели инструментов кнопку Схема данных (или в меню окна выбрать команды СервисÞСхема данных);
3) в диалоговом окне Добавление таблицы выбрать вкладку Таблицы, CL;
4) выделить таблицы Работник и Бригадир, CL кнопку Добавить, CL Закрыть;
5) установить связи по общим полям (НомерБригады), методом DD, перетаскивая их от главной таблицы Бригада к связанной таблице Работник;
6) в диалоговом окне Изменение связей установить опцию Обеспечение целостности данных и опции Каскадное обновление связанных полей и Каскадное удаление связанных полей в связях типа 1:n (один-ко-многим), т.о. изменения, вносимые в запись одной таблицы будут отражаться в других, CL Создать (рисунок 6);
7) закрыть окно, сохранить схему данных.
Рисунок 6 – Создание схемы данных
Внимание! Образовавшаяся межтабличная связь отображается в окне Схема данных в виде линии, соединяющей два поля разных таблиц (рисунок 7). При этом одна из таблиц считается главной (Бригада), а другая – связанной (Работник). Главная – это та таблица, которая участвует в связи своим ключевым полем (название этого поля на схеме данных отображается полужирным шрифтом – НомерБригады). Смысл создания реляционных связей между таблицами состоит, с одной стороны, в защите данных (от некорректного удаления или изменения), а, с другой стороны, – в автоматизации внесения изменений сразу в несколько таблиц при изменениях, сделанных в одной таблице.
Рисунок 7 –Схема данных
Для изменения типа связи нужно дважды щелкнуть по линии связи (чтобы войти в окно Установка типа связи). Для удаления существующей связи следует выделить ее щелчком мыши и нажать клавишу Delete. Нельзя изменить тип данных для поля, которое связывает таблицу с другой таблицей, предварительно нужно удалить установленную связь.
9. Модифицировать структуру таблицы Работник, введя перед полем Адрес поле Пол:
1) CL натаблице Работник;
2) CL в панели на кнопку Конструктор;
3) CL на поле Адрес;
4) CL в меню окна на пункт Вставка, CL на команду Строки;
5) в свободную строку ввести имя поля Пол, а в типе данных выбрать тип Мастер подстановок. Откроется окно Мастера подстановок (рисунок 8).
6) в диалоговом окне Создание подстановок CL на переключателе Фиксированный набор значений, CL на Далее;
7) указать число столбцов 2 и CL на Столбец 1, ввести в него букву м, а в Столбец 2 – мужской (рисунок 9);
8) во второй строке ввести букву ж и женский, CL на Далее.
9) в качестве столбца подстановок CL на Столбец 1, CL на Далее;
10) задать подпись Пол, CL Готово.
10. Закрыть все окна, сохраняя данные.
Рисунок 8 – Окно Создание подстановок
Рисунок 9 – Создание подстановки для выбранного поля
В результате установки связи между таблицами и создания подстановки, таблица Бригада примет вид, показанный на рисунке 10.
Рисунок 10 – Вид таблицы Бригада, полученный в результате установки связи между таблицами и создания подстановки
Контрольные вопросы:
1. Что такое «База данных», «Система управления базами данных», «Сущность», «Атрибут», «Поле и запись таблицы», «Ключевое поле», «Связь»?
2. Какие типы объектов можно увидеть в окне БД Access?
3. Какие свойства имеет объект «Таблица»?
4. Какие режимы допускает окно БД для таблиц?
5. В каком режиме описывается структура таблицы БД? Можно ли изменить структуру таблицы после ее создания?
6. Какие типы полей БД предусмотрены в Access? Какие свойства поля должны быть определены обязательно? Что такое ключевое поле?
7. Как добавить в таблицу новую запись или удалить запись из таблицы? Можно ли восстановить удаленную из БД информацию?
8. Для чего необходимо описывать связи между таблицами базы данных?
9. Какие типы связей поддерживает СУБД Access?