Текст книги "Искусство создания базы данных в Microsoft Office Access 2007"

В Access имеется возможность создать документ (отчет), в котором будет отображена схема данных. Создание и печать такого отчета выполняется следующим образом.

1. Открыть базу данных.

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

3. В группе Показать или скрыть щелкнуть команду Схема данных. Открыть схему данных.

4. Перейти на вкладку Конструктор.

5. В группе Сервис щелкнуть команду Отчет по схеме данных. В режиме предварительного просмотра откроется построенный отчет.

6. С помощью команд вкладки Предварительный просмотр настроить отчет для печати.

7. Щелкнуть команду Печать.

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

1. Открыть базу данных.

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

3. В группе Анализ щелкнуть команду Архивариус. Откроется диалоговое окно Архивариус.

4. В этом окне выбрать вкладку Таблицы.

5. Выбрать один или несколько объектов, которые имеются на вкладке Таблицы. Чтобы выбрать все объекты, надо нажать кнопку Выделить все.

6. Нажать кнопку Параметры. Откроется окно Печать описания таблицы, в котором следует задать характеристики, которые должны отображаться в отчете. Это могут быть свойства таблиц, межтабличные связи, описание полей, описание индексов.

7. Щелкнуть кнопку Ok. Сформированный отчет откроется в режиме Предварительный просмотр.

8. С помощью команд вкладки Предварительный просмотр настроить отчет для печати.

9. Щелкнуть команду Печать.

☞ Примечание. С помощью команды Архивариус можно документировать не только таблицы, но и все другие объекты базы данных.

1.2. Отработка практических навыков: выполняем задание 1

1. Запустить Access и создать пустую базу данных. Присвоить файлу базы данных имя, указанное в варианте задания (см. подраздел 1.3 «Варианты задания 1»).

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

3. Используя Конструктор таблиц, создать базовые таблицы, указанные в варианте задания. При создании таблиц

• всем полям дать имена, которые будет удобно применять в выражениях. Для каждого поля указать псевдоним – краткое семантически осмысленное название. Псевдоним внести в свойство поля Подпись;

• для полей типа Дата/время определить свойство Маска ввода;

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

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

• определить первичные ключи. В качестве ключей взять поля, указанные в варианте задания (они отмечены символом ). В зависимости от того, является ли ключ простым или составным, установить для ключевых полей соответствующие значения свойств Обязательное поле, Индексированное поле;

• определить индексы таблиц.

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

• Обеспечение целостности данных (Да);

• Каскадное обновление связанных записей (Да);

• Каскадное удаление связанных записей (Да).

5. Выполнить форматирование внешнего вида таблиц базы данных.

6. Заполнить таблицы базы данных, введя в каждую из них не менее 10 – 15 записей. Предварительно добавить подтаблицы (там, где это имеет смысл). Использовать команду Итоги, если в таблицах есть данные типа Числовой и Денежный.

7. Отсортировать таблицы по первичному ключу.

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

9. Проверить действие встроенных фильтров для всех типов данных, применяемых в таблицах вашей базы данных.

10. Выполнить предварительный просмотр всех таблиц базы данных. При необходимости настроить параметры страниц для печати содержимого базы данных.

11. Осуществить документирование созданной базы данных, т.е. сформировать

• отчет для схемы данных;

• отчет, содержащий характеристики таблиц.

12. Ответить на контрольные вопросы.

1.3. Варианты задания 1

Кафедры

Индекс: КодКафедры.

Ограничение целостности: номер телефона не превышает семи значащих десятичных цифр.

НаучныеРруководители

Индексы: ТабельныйНомерНаучногоРуководителя; КодКафедры.

Ограничение целостности: номер телефона не превышает семи значащих десятичных цифр.

Студенты

Индексы: ТабельныйНомерНаучногоРуководителя; НомерЗачетнойКнижки + Группа (составной индекс из двух полей).

Ограничение целостности: дата рождения строго меньше текущей даты.

Абоненты

Индексы: РегистрационныйНомерАбонента; Улица + РегистрационныйНомерАбонента (составной индекс из двух полей).

Телефоны

Индексы: НомерТелефона; РегистрационныйНомерАбонента.

Ограничение целостности: тариф оплаты не более 1000 рублей.

Оплата

Индексы: НомерТелефона; ДатаОплаты; Номер-Телефона + ДатаОплаты (составной индекс из двух полей).

Ограничение целостности: дата оплаты не больше текущей даты.

Диагнозы

Индекс: КодДиагноза.

Ограничение целостности: нормативное число дней лечения в стационаре не более 30.

Отделения

Индекс: КодОтделения.

Ограничение целостности: код отделение – трехзначное целое число.

Пациенты

Индексы: НомерКарты; КодОтделения; КодДиагноза; ДатаПоступления + НомерКарты (составной индекс из двух полей).

Ограничения целостности: номер карты не превышает 10000; дата поступления не больше текущей даты.

ТарифнаяСетка

Индекс: НомерРазряда.

Ограничения целостности: номер разряда имеет значения от 1 до 10; размер оклада не более 50000 рублей.

Должности

Индексы: КодДолжности; НомерРазряда.

Ограничение целостности: код должности ‒ трехзначное целое число.

Персонал

Индексы: ТабельныйНомерСотрудника; Номер-Отдела + ТабельныйНомерСотрудника (составной индекс из двух полей).

Ограничение целостности: номер отдела ‒ положительное целое число, значение которого не более 99.

ТоварныеГруппы

Индекс: НомерТоварнойГруппы.

Ограничение целостности: номер товарной группы ‒ двухзначное целое число.

Товары

Индексы: Штрих-код; НомерТоварнойГруппы + Штрих-код (составной индекс из двух полей).

Ограничения целостности: значения штрих-кода не превышает семи значащих десятичных цифр; цена товара не менее 5 и не более 100000 рублей.

Приход

Индексы: ДатаПрихода + Штрих-код (составной индекс из двух полей); Штрих-код; Поставщик.

Ограничение целостности: количество товара не более 1000 единиц.

ПунктыНазначения

Индексы: КодПунктаНазначения; Расстояние + КодПунктаНазначения (составной индекс из двух полей).

Ограничения целостности: время следования не более 5 часов; расстояние не более 300 км.

Поезда

Индекс: НомерПоезда.

Ограничение целостности: нормативное число посадочных мест не более 500.

Расписание

Индекс: НомерПоезда + Дата/ВремяПрибытия (Убытия)ВКрасноярск (составной индекс из двух полей).

ОтделыПредприятия

Индекс: НомерОтдела.

Ограничение целостности: номер отдела ‒ двузначное целое число.

Сотрудники

Индексы: ТабельныйНомерСотрудника; Пол + ТабельныйНомерСотрудника (составной индекс из двух полей).

Ограничение целостности: дата рождения не превышает текущей даты.

ПоступленияПереводы

Индексы: ДатаПоступления(Перевода) + ТабельныйНомерСотрудника (составной индекс из двух полей); НомерОтдела.

Ограничение целостности: дата поступления (перевода) не превышает текущей даты.

УчебныеДисциплины

Индекс: КодДисциплины.

Ограничение целостности: плановое количество часов за семестр не более 144.

Преподаватели

Индекс: ТабельныйНомерПреподавателя.

Ограничение целостности: номер телефона не превышает семи значащих десятичных цифр.

РасписаниеЗанятий

Индекс: ДеньНедели + Лента + ТабельныйНомерПреподавателя (составной индекс из трех полей).

Ограничения целостности: день недели – это числовые значения от 1 до 7; номер ленты – это числовые значения от 1 до 6.

Товары

Индексы: Штрих-код; КатегорияТовара + Штрих-код (составной индекс из двух полей).

Ограничение целостности: значения штрих-кода не превышает семи значащих десятичных цифр.

Покупатели

Индекс: РегистрационныйНомерПокупателя.

Ограничение целостности: регистрационный номер покупателя – трехзначное целое число.

Заказы

Индекс: ДатаЗаказа + НомерЗаказа (составной индекс из двух полей).

Ограничения целостности: номер заказа – это числовые значения от 1 до 1000; дата заказа не больше текущей даты; количество не более 100.

Безработные

Индекс: РегистрационныйНомерБезработного.

Ограничения целостности: номер телефона не превышает семи значащих десятичных цифр; стаж не более 50 лет.

Профессии

Индекс: КодПрофессии.

Предприятия

Индекс: КодПредприятия.

Вакансии

Индекс: КодПредприятия + КодПрофессии (составной индекс из двух полей).

Ограничение целостности: размер оклада не более 50000 рублей.

1.4. Контрольные вопросы

1. Является ли Access реляционной СУБД и почему?

2. Какие объекты может содержать база данных в Access? Укажите их назначение. Какие объекты базы данных являются обязательными, какие – нет? Какое расширение имеет исходный файл базы данных?

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

4. Для чего предназначена область переходов? Как можно выполнить настройку области переходов?

5. Какие действия необходимо осуществить, чтобы создать базу данных? Как в Access можно построить пустую базу данных?

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

7. Какие типы данных могут быть назначены полям таблицы в Access? Каким образом можно задавать и изменять тип данных поля таблицы? Какие свойства может иметь поле таблицы?

8. Какие типы данных допускают выполнение арифметических операций? Чем тип данных Числовой отличается от типа Денежный? Продемонстрируйте разницу этих типов на примере.

9. Для чего предназначен тип данных Счетчик? Какие типы счетчиков возможны в Access? Какое максимально допустимое значение может содержать поле типа Счетчик с последовательными значениями? Можно ли сортировать, фильтровать и индексировать такие поля? Продемонстрируйте использование этого типа данных на вашей базе данных.

10. Для чего используется тип данных Поле объекта OLE? Продемонстрируйте применение этого типа на вашей базе данных. Можно ли сортировать и индексировать такие поля? Какой фильтр можно установить для данных типа Поле объекта OLE?

11. Чем тип данных Текстовый отличается от типа Поле MEMO? Можно ли сортировать данные типа Поле MEMO? Покажите это на примере. Какой фильтр можно установить для данных типа Поле MEMO?

12. Для каких типов данных возможно использование свойства поля Маска ввода? Продемонстрируйте применение этого свойства на одной из таблиц вашей базы данных.

13. Для чего предназначен столбец подстановок? Как можно организовать столбец подстановок в виде списка значений из другой таблицы? Как можно сформировать столбец подстановок в виде фиксированного набора значений? Как можно пополнять этот набор значений?

14. Для чего предназначено свойство поля Значение по умолчанию? Как можно воспользоваться этими свойством, чтобы в поле типа Дата/время всегда изначально отображалась текущая дата?

15. Для какой цели служат свойства поля Условие на значение и Сообщение об ошибке? Как можно вызвать Построитель выражений при определении свойства Условие на значение?

16. Для чего предназначены свойства поля Обязательное поле и Индексированное поле? Для каких типов данных могут быть определены эти свойства?

17. С помощью какого свойства можно установить отображение календаря при вводе данных типа Дата/время?

18. Что такое первичный ключ таблицы? Можно ли работать с таблицей, для которой не объявлен первичный ключ? Покажите, как можно определить простой и составной первичный ключ. Сколько полей может содержать составной ключ? Какие свойства могут иметь ключевые поля таблицы?

19. Что такое подтаблица таблицы базы данных. Каким образом можно организовать вставку, развертывание, свертывание и удаление подтаблиц? Сколько уровней вложенности подтаблиц допускается в Access?

20. Каким образом возможно модифицирование структуры базовой таблицы (добавление, удаление, переименование полей, изменение типа данных и свойств полей и т.п.)? В каких режимах допускается внесение данных в базовую таблицу?

21. Какие свойства имеет таблица как объект базы данных? Как можно изменить эти свойства?

22. Для чего предназначена схема данных? Можно ли работать с базой данных без схемы? Укажите последовательность действий, необходимых для создания и редактирования схемы данных.

23. Какие межтабличные связи возможны в Access? Какие параметры имеет всякая межтабличная связь? Как устанавливаются Параметры объединения таблиц при создании схемы данных? Какие режимы соединений таблиц допускаются в Access?

24. Что представляет собой ссылочная целостность данных? Как осуществляется контроль ссылочной целостности базы данных? Покажите, как действует этот контроль в вашей базе данных.

25. Как работает механизм каскадных удалений и изменений таблиц базы данных? Продемонстрируйте функционирование этого механизма на вашей базе данных.

26. Какие средства форматирования внешнего вида таблиц существуют в Access? Примените эти средства к таблицам вашей базы данных.

27. На какие типы данных действует команда Итоги, находящаяся в группе Записи на вкладке Главная?

28. Каким образом можно упорядочивать информацию в таблице? По каким типам полей возможна сортировка? Как осуществить сортировку по двум и более полям?

29. Где можно просмотреть определенные для таблицы сортировки? Как можно удалить все сортировки? Как сохранить установленную сортировку, т.е. сделать так, чтобы она автоматически выполнялась при каждом открытии таблицы?

30. Какие способы поиска записей существуют в Access? Какие возможности предоставляет диалоговое окно Поиск и замена? Как вызвать это окно? Продемонстрируйте работу с данным окном для поиска записей.

31. Что представляют собой фильтры и для чего они предназначены? Какие встроенные фильтры существуют в Access? Указать способы применения и отмены встроенных фильтров.

32. Как организовать фильтр для отбора записей по значению нескольких полей? Продемонстрируйте работу такого фильтра на вашей базе данных.

33. Где можно просмотреть последний заданный в таблице фильтр? Как можно очистить все фильтры? Как сохранить установленный фильтр, т.е. сделать так, чтобы он автоматически выполнялся при каждой загрузке таблицы?

34. Зачем нужно индексировать таблицу? Можно ли работать с таблицей, для которой не создано ни одного индекса? Покажите, как можно создать простой и составной индекс? Для каких полей индекс создается автоматически? Какие свойства допустимы для индексированных полей?

35. Что происходит с индексами таблицы при добавлении, удалении, изменении записей этой таблицы? Почему замедляется ввод и редактирование таблицы при наличии у нее индексов?

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

37. Каким образом можно выполнить предварительный просмотр и печать таблицы? Продемонстрируйте эти действия на примере таблиц вашей базы данных.

38. Как можно сформировать и напечатать отчет, в котором отражена схема данных? Сохраняется ли этот отчет в базе данных? Продемонстрируйте процесс документирования созданной вами схемы данных.

39. Для чего предназначена команда Архивариус? Как с помощью этой команды сформировать отчет, содержащий характеристики таблиц базы данных? Сохраняется ли этот отчет в базе данных? Можно ли сохранить данный отчет в виде документа Word?

40. Для каких целей предназначена команда Анализ таблицы, расположенная в группе Анализ на вкладке Работа с базами данных? Используя данную команду, выполните анализ созданных вами базовых таблиц.

2. Создание запросов

Для эффективной работы с данными обычно применяют запросы. Запрос – обращение пользователя к базе данных с целью ввода, получения или изменения информации, хранимой в базе данных. Состав запросов, формируемых к базе данных, определяется преимущественно информационными потребностями ее пользователей. Такие запросы называются пользовательскими. Существуют также управляющие запросы – запросы, предназначенные для нужд администратора базы данных. Они направлены на актуализацию и управление данными. Для записи запросов в современных реляционных СУБД используют преимущественно два языка: QBE – язык запросов по образцу, SQL – структурированный язык запросов. СУБД Access располагает возможностями обоих этих языков. Данная тема посвящена созданию различных типов запросов в Access с помощью языка QBE. Здесь рассматриваются также правила записи выражений, описывающих условий отбора записей и формулы расчета вычисляемых полей, обсуждаются вопросы печати и документирования запросов.

2.1. Немного теории

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

Запрос – объект базы данных, предназначенный

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

• выполнения расчетов;

• для создания новой базовой таблицы на основе существующих таблиц базы данных;

• для добавления, изменения и удаления записей из базовых таблиц.

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

Запросы, применяемые для создания и изменения содержания базовых таблиц, называют запросами на изменение. Эти запросы предназначены, прежде всего, для администратора базы данных, т.к. направлены на актуализацию данных и управление данными. Поэтому такие запросы называют также управляющими запросами.

На первый взгляд запросы на выборку схожи с сортировкой и фильтрацией. Однако это не совсем так. Запросы являются более мощным и гибким инструментом, чем простое сочетание сортировки и фильтра. Основные отличия между этими подходами:

• достоинство команд сортировки и фильтрации в том, что они просты в применении и выполняются быстрее, чем запросы;

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

• другой недостаток сортировки и фильтрации: работа только с одной открытой таблицей. Запрос же может извлекать данные из нескольких таблиц и запросов, и при этом не требует, чтобы они были открыты.

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

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