Некоторые базовые принципы разработки таблиц

Объекты базы данных. СУБД Microsoft Access позволяет создавать и использовать объекты различных типов (таблицы, запросы, формы, отчеты и т. д.)

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

Некоторые базовые принципы разработки таблиц.

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

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

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

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

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

Создание новой пустой таблицы. В Microsoft Access существует несколько способов создания новой таблицы:

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

Мастер таблиц позволяет выбрать поля для данной таблицы из числа определенных ранее таблиц.

2. Ввод данных непосредственно в пустую таблицу в режиме таблицы. При сохранении новой таблицы в Microsoft Access данные анализируются и каждому полю присваивается необходимый тип данных и формат.

Определение всех параметров структуры таблицы в режиме конструктора.

Импорт в текущую базу данных структур таблиц и данных из внешнего источника.

Создание в текущей базе данных таблиц, связанных с таблицами внешнего источника.

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

3.Создание таблицы в режиме конструктора. Режим конструктора позволяет наиболее гибко управлять всеми создаваемыми и уже имеющимися компонентами определения таблицы. Чтобы создать таблицу в режиме конструктора:

Перейти в окно базы данных.

Выбрав вкладку Таблицы, нажать кнопку Создать.

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

В окне Новая таблица выбрать элемент Конструктор.

Определить в таблице каждое поле. Поля таблицы содержат данные, представляющие порции записи. Пользователь имеет возможность определять формат отображения данных, указывать значения по умолчанию и ускорять операции поиска и сортировки, задавая значения свойств полей в разделе Свойства поля в режиме конструктора таблицы. В Microsoft Access свойства полей используются при просмотре или изменении данных пользователем. Например, заданные пользователем значения свойств Формат поля, Маска ввода и Подпись определяют вид базы данных таблицы и запроса. Элементы управления в новых формах и отчетах, присоединенные к полям таблицы, наследуют эти свойства полей базовой таблицы по умолчанию. Другие свойства позволяют определить условия на значения полей или задать обязательный ввод данных в поле. Microsoft Access будет проверять выполнение этих условий при каждом добавлении или изменении данных в таблице. Для добавления поля в конец структуры таблицы нужно выбрать первую пустую строку структуры. Для вставки поля в середину структуры следует выбрать строку, над которой требуется добавить новое поле, и нажать кнопку Добавить строки на панели инструментов. В столбец Имя столбца ввести имя поля; в столбце Тип данных выбрать требуемый тип данных в раскрывающемся списке либо оставить настройку по умолчанию (Текстовый). В столбце Описание можно ввести необязательное краткое описание поля. Текст описания будет выводится в строке состояния при добавлении данных в поле, а также будет включен в описание объекта таблицы. При необходимости можно задать значения свойств поля в бланке свойств в нижней части окна.

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

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

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

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

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

- какие операции должны производиться со значениями в поле. Например, суммировать

- значения можно в числовых полях и в полях, имеющих валютный формат, а в текстовых полях и полях объектов OLE, – нельзя;

- нужна ли сортировка или индексирование поля. Сортировать и индексировать поля МЕМО, гиперссылки и объекты OLE невозможно;

- будут ли поля использоваться в группировке записей в запросах или отчетах. Поля МЕМО, гиперссылки и объекты OLE использовать для группировки записей нельзя;

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

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

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

Схема данных

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

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

Важно:

1)Числовые, денежные и логические типы данных, а также Дата/Время обеспечивают стандартные форматы отображения. Для выбора форматов для каждого типа данных следует определить свойство Формат. Для всех данных, кроме объектов OLE, можно также создать пользовательский формат отображения. й точкой.

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

3) После наполнения таблицы данными сохранять их не надо – все сохраняется автоматически. Однако если при работе с таблицей произошло редактирование ее макета (например, изменялась ширина столбцов), СУБД попросит подтвердить сохранение этих изменений.

Если возникнет необходимость изменить структуру таблицы (состав полей или их свойства), таблицу надо открыть в режиме Конструктора.

4. Задание:

1) Спроектировать базу данных для собственной фирмы. База данных должна содержать не менее трех таблиц. Необходимо установить связи между таблицами. (этап бумажного проектирования).

2) Создать базу данных на компьютере: составить структуры таблиц, заполнить таблицы, установить взаимосвязи (схема данных).

5. Содержание отчета:

1) Бумажное проектирование (список атрибутов, структуры таблиц, схема данных)

2) Ответы на контрольные вопросы.

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

1) Каким требованиям должна удовлетворять база данных?

2) Перечислите, какие возможности должна предоставлять СУБД.

3) Как запустить СУБД MS Access?

4) Как открыть файл базы данных?

5) Как создать файл базы данных?

6) Как создать таблицу базы данных?

7) Чем определяется структура таблицы базы данных?

8) Перечислить основные объекты базы данных.

Лабораторная работа 14

Базы данных MS Access. Формирование запросов.

1. Цель работы:

1) Создание простых запросов с помощью мастера.

2) Создание запросов с помощью конструктора.

3) Создание фильтров.

2. Перечень справочной литературы:

1) Голицына, Н.В.Максимов, И.И.Попов «Базы данных» М. Форум-Инфра-М,2004

2) Хомоненко А.Д., Цыганков В.М., Мальцев М.Г. Базы данных: Учебник для высших учебных заведений. / Под ред. проф. А.Д. Хомоненко. – СПб: БХВ-Петербург, 2000.

3. Краткий теоретический материал:

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

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

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

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

4. Задание:

1) Готовую базу данных «RUSICH», расположенную в каталоге D:\WORK скопировать в свой каталог,, переименовать (новое имя файла должно соответствовать вашей фамилии) и только потом загружать переименованную базу в ACCESS.

В базе данных хранится информация, представляющая интерес для охранного предприятия RUSICH (сведения об охранниках города, их подготовке и организациях в которых они работают). Каждый охранник служит в одной из пяти организаций и проходит подготовку (одну из трёх – в соответствии со своими обязанностями). Структура базы данных показана на рисунке 1, а характеристики полей – в таблицах 1 – 3.

Рисунок 1 – Схема базы данных «RUSICH»

Таблица 1 – Характеристики полей таблицы «СОТРУДНИКИ»

Имя поля	Тип данных	Длина поля	Примечание
Номер	счётчик		Ключевое поле
Фамилия	текстовый
Имя	текстовый
Дата рождения	дата
Рост	числовой	с плав. точкой (4 байта)
Вес	числовой	с плав. точкой (4 байта)
Организация	текстовый		поле связи с таблицей «ОРГАНИЗАЦИЯ»
Номер подготовки	текстовый		поле связи с таблицей «ПОДГОТОВКА»
Оплата 1 дня	денежный
Характеристика	поле МЕМО

Таблица 2 – Характеристика полей таблицы «ОРГАНИЗАЦИЯ»

Имя поля	Тип поля	Длина поля	Примечание
Организация	текстовый		ключевое поле
Директор	текстовый
Адрес	текстовый
Телефон	текстовый

Таблица 3 – Характеристика полей таблицы «ПОДГОТОВКА»

Имя поля	Тип поля	Длина поля	Примечание
Номер подготовки	текстовый		ключевое поле
Бег	числовой	с плав. точкой (4 байта)	Суточная длина пробежки (км)
Штанга	числовой	с плав. точкой (4 байта)	Суточная вес (кг)
Стрельба	числовой	с плав. точкой (4 байта)	Суточное время на стрельбище (час)
Рукопашный бой	числовой	с плав. точкой (4 байта)	Суточное время на тотами (час)

2) Создать и выполнить следующие запросы:

a) Создать запрос «ГИГАНТ» на поиск охранников, рост которых не меньше 190 см. Вывести на экран фамилию, имя, рост, организацию и фамилию её директора.

b) Создать запрос «ГРИНВИЧ» на поиск охранников из организации «Гринвич» с именем Василий и из организации «S&T» с именем, начинающимся на И. Вывести на экран фамилию, имя и организацию.

c) Создать запрос «АМСТЕРДАМ» на поиск охранников, вес которых удовлетворяет условию 60 ≤ Вес ≤ 65. Вывести на экран имя, фамилию и вес выбранных сотрудников.

d) Создать запрос «АЛХИМИЯ» на поиск охранников из этой организации с ростом > 182 см.

e) С помощью запроса «АНДРЕЙ» вычислить количество охранников с таким именем и их средний рост.

f) Создать запрос «MINMAX» на вычисление количества охранников с подготовкой №2 из организации, фамилия директора которого начинается с буквы Ф, их минимального роста и максимального веса.

g) Создать запрос «ПОДГОТОВКА» на поиск охранников с ростом >185 см и номером подготовки 1. Вывести на экран фамилию, имя, рост, номер подготовки и соответствующие показатели упражнений.

h) Создать запрос «ДЛИНА» на поиск охранников, работающих в организации «S&T» и у которых длина имени не более 5 символов.

i) Создать запрос «ДОЛГОЛЕТИЕ» на поиск самой ранней даты рождения охранников в организации «Алхимия» и вычислить разницу в днях с таким же показателем для организации «Таркус». Вывести на экран фамилию, имя, организацию, дату рождения, разницу в днях.

j) Создать запрос «ПЁРЫШКО» на поиск самого лёгкого охранника, родившегося в пределах от 1975 года до 1979 года. Вывести на экран фамилию, имя, дату рождения и вес в граммах.

k) Создать запрос «ЮБИЛЕЙ» на определение у кого из охранников в этом году будет юбилей и сколько лет ему исполняется.

l) С помощью фильтра по выделенному найти всех охранников с именем Иван.

m) С помощью расширенного фильтра найти всех охранников из организации «Sunrise» с оплатой одного дня > 1000 руб. и охранников из организации «Таркус» с весом > 75 кг.