Таблицы

Сегодня мы подробно поговорим о таблицах в Access.

Создать таблицу технически не сложно: в конструкторе задаете имена и типы полей, в режиме просмотра заполняете таблицу. Куда большую сложность составляет проектирование схемы БД. Рационально составленная схема (таблицы и связи между ними) – залог того, что легко пройдут все следующие этапы (составление запросов, ввод новых данных, проектирование форм). Иными словами базой будет удобно пользоваться! О том, какие приняты соглашения при разработке схемы БД, мы поговорим сегодня подробнее.

Вспомним, что мы уже знаем о таблицах.
1. Идентификатор (ключ)
Все записи в таблицах перенумерованы. Для этого используется специальное поле (его лучше делать самым первым и называть Id). Тип этого поля – счетчик. Счетчик каждой новой записи автоматически дает новый порядковый номер. Этот номер нельзя удалить, нельзя изменить (попробуйте-ка!).
Если вы удалили из таблицы запись (например с Id = 12 – щенка Тедди, когда он заболел и не смог принимать участия в выставке), то, если он выздоровеет, и вы снова добавите Тедди в таблицу, Id у него будет уже другим, на 1 больше последней записи в таблице.
Это нужно для того, чтобы идентификаторы в одной таблице ни в коем случае НЕ СОВПАДАЛИ. Иначе они перестанут быть идентификаторами!
Как следствие, нельзя, чтобы идентификаторы соответствовали номерам квартиры, выставочными номеркам собак; записи, отсортированные по Id не обязательно должны идти в алфавитном порядке, по возрастанию даты и времени. Иными словами, Id не несут никакого содержательного смысла. Они нужны только для того, чтобы устанавливать СВЯЗИ между записями в таблицах.

2. Стандарты именования
Имена таблиц и полей в таблицах (как и имена запросов, форм, отчетов и макросов) записываются по-английски без пробелов (через нижнее подчеркивание dog_age или каждое слово с большой буквы DogAge). Перед именем таблицы ставится приставка tb… Таким образом, записи становятся легко читаемы (например, tbDog.BirthDate), и не возникает проблем с кодировкой кириллицы.
Надо сказать, что Access не запрещает писать названия кириллицей и с пробелами. Чтобы имена с пробелами не «рассыпались» он автоматически заключает их в квадратные скобки вот так: [Выставка собак].[важная информация 1]. Но выглядит это непрофессионально и затрудняет визуальное восприятие имен.

3. Связи между таблицами
Существует два основных типа связей между таблицами: «один-ко-многим» и «многие-ко-многим». Помните пример из прошлого урока?

Иллюстрация к связи «один-ко-многим»:

Иллюстрация к связи «многие-ко-многим»:

Будьте внимательны! Поля-ссылки должны иметь тип ЧИСЛОВОЙ. Если тип будет текстовый, то при попытке установить связь на схеме данных будет возникать ошибка! Поля-ссылки принято именовать так: имя таблицы, на которую идет ссылка + Id, например, поле-ссылка PersonId в таблице tbDog.

4. Режим конструктора
В режиме конструктора мы задаем имена и типы полей. Типы полей нужны для того, чтобы Access в запросах корректно применял встроенные функции (так же, как и в Excel). Помните, пример: 2+3=5, если это числа, и “2” + “3” = “23”, если это строки. Другой пример: встроенная функция для временных переменных Month(12.04.05) = 4 не отработала бы, если бы дата была записана строкой.

Особняком стоит такой тип, как Мастер подстановок. Собственно, он и не является типом. Мастер позволяет устанавливать простейший тип связи между двумя таблицами: «один-ко-многим». Иными словами, он позволяет подставлять в одну таблицу значения из другой. С помощью мастера подстановок удобно, например, подставить в tbDog значения из таблицы с породами. Это будет выглядеть так:

То есть мастер прямо в ячейки таблицы вставит небольшие выпадающие списки, из которых можно будет выбирать нужное значение. Важно понимать, что И В ЭТОМ случае связь между таблицами устанавливается через ID! Просто мастер скрывает от нас техническую сторону дела.

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

ПРАВИЛА ПРОЕКТИРОВАНИЯ БАЗ ДАННЫХ

При проектировании схемы данных следует соблюдать ТРИ ПРАВИЛА:

Правило 1: 1-Я НОРМАЛЬНАЯ ФОРМА

Согласно 1-й нормальной форме, в каждой ячейке располагается минимальная единица информации.

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

С чем связано это требование?
Ответ простой: с тем, что таблицы потом обрабатываются запросами!
Гораздо проще указать поисковое условие tbMark.Exterior > 5, чем искать в длинной строке “ 5, 4, 6 " вхождение в нее нужной подстроки и вылавливать потом ошибки.

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

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

Также не составит труда выбрать из БД всех жителей Москвы:

Исключение составляют те блоки информации, по которым заведомо НЕ будет производиться поиск. Так в последнем примере вряд ли имеет смысл выносить номер квартиры в отдельное поле, поскольку маловероятно, что кому-то понадобится отобразить всех имеющихся в БД жителей 50-й квартиры.

В дополнение к 1НФ следует сказать еще одну важную мысль: как вы уже знаете, Access позволяет создавать запросы с вычислениями: по дате рождения определять возраст, из имени и отчества делать инициалы и т.п. Поэтому в ячейках таблиц следует указывать лишь ИСХОДНЫЕ данные (даты, стоимость одной единицы товара), и НЕ записывать туда те, которые могут быть вычислены с помощью функций.

Правило 2: ЛОКАЛЬНОСТЬ ХРАНЕНИЯ ДАННЫХ

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

Помните пример с породами? В таблице с собаками регулярно повторяются названия пород. Чтобы не писать двадцать раз «американский кокер-спаниель», мы заводим отдельную таблицу с породами а в таблице с собаками указываем лишь Id породы. (Кстати, какого типа здесь будет связь?)

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

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

Правило 3: ОБЪЕДИНЕНИЕ ТАБЛИЦ

Мы много говорили о том, что нужно разбивать таблицы на несколько (чтобы соблюдать первые два принципа: 1НФ и Локальности хранения данных). Теперь настало время поговорить о том, в каких случаях следует ОБЪЕДИНЯТЬ несколько таблиц в одну.

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

Однако существуют случаи, когда несколько сущностей следует вносить в одну таблицу! Что это за случаи? Это когда несколько сущностей имеют почти идентичные схемы данных (набор полей в таблице).
Например, у нас в выставке собак участвуют не только владельцы собак, но и члены экспертного совета (судьи). Можно было бы создать отдельную таблицу для судей tbExpert, но удобнее записать и их, и владельцев в одну таблицу tbPerson (введя для различения всего одно дополнительное поле IsExpert).

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

Итак, третий принцип формулируется следующим образом:

Если несколько сущностей имеют почти идентичные схемы данных, их следует объединить в одну таблицу.

Не нужно бояться, что таблица станет от этого слишком длинной. Базы данных прекрасно работают с ОЧЕНЬ длинными таблицами (я сама работала с базой данных в Access, в которой было чуть более 1 000 000 записей, и при этом Access отлично с ней справлялся!) Студенты разных групп, курсов и факультетов, товары разного вида, книги разных жанров – все они могут и должны быть объединены в одну таблицу (tbStudent, tbGoods, tbBook соответственно). В таких общих таблицах рекомендуется заводить только специальное поле (поля), для отнесения объектов к той или иной группе: курсу, классу, жанру и т.п. В таблице tbPerson, например, есть специальное поле логического типа, в котором стоит пометка, является ли данный человек экспертом.

ИТОГИ

Перечислим еще раз принципы проектирования:

1. 1НФ: в каждой ячейке располагается минимальная единица информации.
2. Локальность хранения данных: если значения в каком-то поле регулярно повторяются, их следует выносить в отдельную таблицу, а в поле оставлять ссылку.
3. Объединение таблиц: если несколько сущностей имеют почти идентичные схемы данных, их следует объединить в одну таблицу.

И не забывайте про правила, о которых мы говорили раньше:

1. У каждой таблицы первое поле – ID типа Счетчик.
2. Правила именования: по-английски без пробелов (каждое слово с большой буквы) + приставка tb…
3. Два типа связей: один-ко-многим (создаем поле-ссылку), многие-ко-многим (создаем дополнительную таблицу).

Теперь перейдем к практике.

Задания

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

1. Голливуд
Культовые фильмы Голливуда.

Образец решения:
1) у нас есть две сущности: люди tbPerson и фильмы tbFilm;
2) в таблице tbPerson перечислены актеры и режиссеры;
3) у каждого фильма может быть ТОЛЬКО ОДИН режиссер, но один режиссер может снять НЕСКОЛЬКО фильмов;
4) в каждом фильме снимается НЕСКОЛЬКО актеров, и один актер может сниматься в НЕСКОЛЬКИХ фильмах.

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

3. Мобильный оператор
Оператор мобильной связи ведет базу данных с информацией о своих абонентах.

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

5. Сеть автосалонов
Иномарки продаются в сети автосалонов.

Задание 3 Выберите одну из задач и выполните ее в Access. Дополните базу данных, так чтобы в ней использовались все типы полей, в том числе мастер подстановок. Проверьте, чтобы все связи правильно отображались на схеме данных.

ВСЕ