Тема 2. Проектирование таблиц базы данных

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

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

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

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

Покупатель сообщает Сведения о заказе,

Менеджер оформляет Сведения о заказе,

Сведения о заказе содержат Заказ на товар,

Поставщик поставляет Товар на базе,

Товар на базе включается в Заказ на товар.

Принято графически изображать связи между сущностями и обозначать их глаголами (рис. 2.1). Заметим, что в проектировании информационных систем такие модели называются моделями «сущность – связь» или ER-моделями (от англ. Entity – сущность, Relationship – связь).

Рис. 2.1. ER-модель базы данных

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

Основным выходным документом комплекса задач Реализация товаров является счет-фактура. При формировании счета-фактуры производится корректировка оперативных товарных остатков в реальном времени. Вначале для каждого заказанного товара проверяется, имеется ли на базе нужное количество этого товара. Если такое количество товара есть, то в таблице Товар на базе остаток товара уменьшается на заказанное количество. Если же покупатель заказал товар в количестве большем, чем имеется на базе, то, вначале корректируется заказ покупателя (заказ на этот товар уменьшается и делается равным остатку этого товара на базе), а уже затем корректируется остаток этого товара в таблице Товар на базе.

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

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

Однако этот минимальный вариант задачи позволяет, тем не менее, легко получать решение других задач автоматизации.

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

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

Опишем функционирование комплекса задачи Реализация товаров.

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

Создание таблиц

Существует несколько способов создания таблиц в MS Access 2007:

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

• создание таблицы на основе списка на узле SharePoint;

• создание таблицы при помощи конструктора.

Для создания новой пустой таблицы выберите на второй вкладке ленты Создание пункт Таблица(рис. 2.2).

Рис. 2.2. Создание новой пустой таблицы

На экране появится новая пустая таблица (рис. 2.3). В ней два столбца: Код и Добавить поле. Поле Код является ключевым, и будет автоматически заполняться при добавлении записей. Замените название столбца на свое, так можно начать создавать таблицу.

Рис. 2.3. Новая пустая таблица

Второй способ создания таблицы на основе списка на узле SharePoint (вкладка Создание) позволяет воспользоваться шаблонами при создании своей таблицы.

Третий способ создания таблицы – с помощью конструктора, рассмотрим его подробнее. Для этого на вкладке Создание выбрать пункт Конструктор таблиц.

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

• Как будет называться таблица?

• Как будут называться столбцы (поля) таблицы?

• Какие типы данных будут закреплены за каждым столбцом?

• Какие столбцы таблицы требуют обязательного ввода?

• Из каких столбцов будет состоять первичный ключ?

Задание 1.В режиме конструктора (рис. 2.4) спроектируйтетаблицу Менеджер, которая будет хранить информацию о сотрудниках фирмы. Задайте ключевое поле этой таблицы. Обратите внимание на свойства полей.

Структура таблицы Менеджер

Имя поля	Тип данных	Примечание
Код менеджера	Текстовый *)	Ключевое поле (PK)
Фамилия	Текстовый
Имя	Текстовый
Отчество	Текстовый
Дата рождения	Дата/время
Фотография	Объект OLE
Дата приема на работу	Дата/время
Наличие водительского удостоверения	Логический
Характеристика	МЕМО

^*) Замечание.Несмотря на то, чтоКод менеджера будет представлять собой последовательность цифр, тип данных для этого поля выберем Текстовый. Тип данных Числовой следует выбирать только для полей, которые будут использоваться для вычислений.

Рис. 2.4. Таблица Менеджер в режиме конструктора

Определение полей таблицы

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

В столбце Имя указывается имя соответствующего поля (не более 64 знаков, допускаются все специальные знаки за исключением точки, восклицательного знака и квадратных скобок). Лучше в именах использовать латинские буквы и не применять пробелы! Однако для удобства восприятия мы в этом пособии не будем следовать этому совету.

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

текстовый – размер до 255 символов;

МЕМО – текст комментариев до 65 535 знаков;

числовой – числа различных форматов;

дата/время – формат определен в графе Формат поля;

денежный – денежные значения различных форматов;

счетчик – значение этого поля автоматически увеличивается на единицу после добавления новой записи;

логический – содержит логические значения НЕТ (значение 0) и ДА (значение -1);

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

гиперссылка – адрес объекта, документа или Web-узла в сети Internet или в локальной сети.

Задание свойств полей

Каждое поле имеет ряд свойств, которые можно определить. Заметим, что для разных типов данных набор свойств отличается. При конструировании таблицы нет необходимости задавать все свойства полей, их можно не определять до тех пор, пока в этом не возникнет настоятельной необходимости. Но, чтобы полностью использовать потенциал Access, вы должны при работе с программой держать «в уме» возможность применения того или иного свойства.

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

Размер поля;

Формат поля;

Число десятичных знаков;

Маска ввода;

Подпись (обозначение поля при распечатке документов, по умолчанию берется имя поля);

Значение по умолчанию (автоматически вводится по умолчанию), например, в таблице Заказ на товар в поле Дата заказа можно ввести значение по умолчанию DATE() и тогда по умолчанию будет вводиться сегодняшняя дата;

Условие на значение (выражение, которое устанавливает ограничение значения при вводе);

Сообщение об ошибке;

Обязательное поле (определяется, обязательно ли вводить данное в это поле);

Пустые строки;

Индексированное поле (определяется, надо ли индексировать поле).

Определение ключевого поля

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

Ключевое поле определяется на этапе создания структуры таблицы. Выделить поле, которое должно быть ключевым. Щелкнуть на пиктограмме Ключевое поле вкладки Конструктор главного окна Access 2007. Такой же ключ появится возле имени поля.