Задание №22: БД Компьютерной фирмы.

Таблицы:

Сотрудники (Код сотрудника, ФИО, Возраст, Пол, Адрес, Телефон, Паспортные данные, Код должности)[10 записей]. Должности (Код должности, Наименование должности, Оклад, Обязанности, Требования)[5 записей]. Виды комплектующих (Код вида, Наименование, Описание)[15 записей]. Комплектующие (Код комплектующего, Код вида, Марка, Фирма производитель, Страна производитель, Дата выпуска, Характеристики, Срок гарантия, Описание, Цена)[15 записей]. Заказчики (Код заказчика, ФИО, Адрес, Телефон)[10 записей]. Услуги (Код услуги, Наименование, Описание, Стоимость)[5 записей]. 7. Заказы (Дата заказа, Дата исполнения, Код заказчика, Код комплектующего 1, Код комплектующего 2, Код комплектующего 3, Доля предоплаты, Отметка об оплате, Отметка об исполнении, Общая стоимость, Срок общей гарантии, Код услуги 1, Код услуги 2, Код услуги 3, Код сотрудника)[10 записей].

Запросы:

Отдел кадров Список комплектующих Список заказов Сотрудники отдельных должностей Комплектующие отдельных видов Заказы отдельных заказчиков Заказы по датам заказа

Задание №23: БД ГИБДД.

Таблицы:	Сотрудники (Код сотрудника, ФИО, Возраст, Пол, Адрес, Телефон, Паспортные данные, Код должности, Код звания)[10 записей]. Должности (Код должности, Наименование должности, Оклад, Обязанности, Требования)[5 записей]. Звания (Код звания, Наименование, Надбавка, Обязанности, Требования)[5 записей]. Марки автомобилей (Код марки, Наименование, Фирма производитель, Страна производитель, Дата начала производства, Дата окончания производства, Характеристики, Категория, Описание)[10 записей]. Водители (Код водителя, ФИО, Дата рождения, Адрес, Паспортные данные, Номер водительского удостоверения, Дата выдачи удостоверения, Дата окончания удостоверения, Категория удостоверения, Описание, Код сотрудника)[15 записей]. Автомобили (Код автомобиля, Код водителя, Код марки, Регистрационный номер, Номер кузова, Номер двигателя, Номер техпаспорта, Дата выпуска, Дата регистрации, Цвет, Технический осмотр, Дата технического осмотра, Описание, Код сотрудника)[15 записей]. Автомобили в угоне (Дата угона, Дата обращения, Код автомобиля, Код водителя, Обстоятельства угона, Отметка об нахождении, Дата нахождения, Код сотрудника)[5 записей].
Запросы:	Отдел кадров Список автомобилей Список угонов Сотрудники отдельных должностей Сотрудники отдельных званий Автомобили одного владельца Автомобили прошедшие и не прошедшие технический осмотр Найденные и не найденные угнанные автомобили

Задание №24: БД Кинотеатра.

Таблицы:	Сотрудники (Код сотрудника, ФИО, Возраст, Пол, Адрес, Телефон, Паспортные данные, Код должности)[10 записей]. Должности (Код должности, Наименование должности, Оклад, Обязанности, Требования)[5 записей]. Жанры (Код жанра, Наименование, Описание)[5 записей]. Фильмы (Код фильма, Наименование, Код жанра, Длительность, Фирма производитель, Страна производитель, Актёры, Возрастные ограничения, Описание)[10 записей]. Репертуар (Код сеанса, Дата, Время начала, Время окончания, Цена билета)[10 записей]. Места (Код сеанса, Номер места, Занятость, Код сотрудника)[15 записей].
Запросы:	Отдел кадров Список фильмов Билеты Сотрудники отдельных должностей Фильмы отдельных жанров Билетов на сеансы определённых дат Занятые и незанятые места

Задание №25: БД Автосалона.

Таблицы:	Сотрудники (Код сотрудника, ФИО, Возраст, Пол, Адрес, Телефон, Паспортные данные, Код должности)[10 записей]. Должности (Код должности, Наименование должности, Оклад, Обязанности, Требования)[5 записей]. Производители (Код производителя, Наименование, Страна, Адрес, Описание, Код сотрудника)[5 записей]. Дополнительное оборудование (Код оборудования, Наименование, Характеристики, Цена)[5 записей]. Тип кузова (Код типа кузова, Название, Описание)[5 записей]. Автомобили (Код автомобиля, Марка, Код производителя, Код типа кузова, Дата производства, Цвет, Номер кузова, Номер двигателя, Характеристики, Код оборудования 1, Код оборудования 2, Код оборудования 3, Цена, Код сотрудника)[10 записей]. Заказчики (ФИО, Адрес, Телефон, Паспортные данные, Код автомобиля, Дата заказа, Дата продажи, Отметка о выполнении, Отметка об оплате, Процент предоплаты, Код сотрудника)[10 записей].
Запросы:	Отдел кадров Каталог автомобилей Список заказов Сотрудники отдельных должностей Автомобили отдельных производителей Автомобили с отдельными типами кузова Выполненные и невыполненные заказы Оплаченные и неоплаченные заказы

Приложение

Примеры проектирования баз данных

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

ПРИМЕР 1

Рассмотрим пример проектирования с использованием метода сущность-связь построения ER-диаграммы и в последствии – реляционной схемы БД.

Предметная область: информационная система «ОТДЕЛ КАДРОВ».

Исходные данные:

1. Фамилия сотрудника, имя, отчество, домашний адрес, телефон, дата рождения, образование.

2. Должность, дата зачисления, оклад, объем должности (число ставок).

3. Наименование подразделения, количество штатных единиц, фонд заработной платы.

Один и тот же сотрудник может зачисляться на работу на разные ставки по разным должностям; в одном и том же подразделении может работать несколько сотрудников.

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

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

Один сотрудник может быть зачислен на несколько работ.

После выделения сущностей и определения связей [1] получим следующую ER-диаграмму:

 

 

Рис 1. ER-диаграмма предметной области

 

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

 

 

Рис 2. Уточненная ER-диаграмма

Атрибут Объем должности сущности Штатное расписание – это Число ставок, а атрибут Объем – Код строки штатного расписания.

Должность выбирается из предустановленного набора должностей и не зависит ни от сотрудника, ни от отдела. Поэтому ДОЛЖНОСТЬ становится отдельной сущностью.

ШТАТНОЕ_ РАСПИСАНИЕ является ассоциативной сущностью, связывающей сущности ОТДЕЛ и ДОЛЖНОСТЬ. Строка штатного расписания содержит информацию о количестве ставок одной должности в одном отделе. Таким образом, одной должности может соответствовать несколько строк в ШТАТНОЕ_РАСПИСАНИЕ (для разных отделов), и одному отделу - тоже несколько строк (для разных должностей).

Одна строка ШТАТНОЕ_ РАСПИСАНИЕ может быть связана с несколькими строками РАБОТ, так как ставок по данной строке может быть несколько и зачисление может происходить на дробную ставку (например, объем ставки 0,5). "Теоретически" общий объем работ (объем должностей), связанных со строкой штатного расписания, не должен превышать указанное в штатном расписании количество ставок, но на практике это правило, по-видимому, может нарушаться. Кроме того, опять-таки "теоретически", сумма окладов по работам, связанным (через штатное расписание) с одним отделом, не должна превышать фонда зарплаты отдела, но это правило тоже не абсолютное.

Среди возможных запросов может быть запрос, подразумевающий наличие информации об образовании сотрудника. Отсюда – необходимость иметь атрибут Образование в сущности СОТРУДНИК.

После нормализации схемы данных [2-5], получаем схему, представленную на рис. 3. Сущности этой схемы представимы в виде реляционных таблиц.

 

Рис.3. Реляционная схема базы данных

 

Атрибуты сущности ОТДЕЛ – кодотдела (первичный ключ), наименование и фонд отдела.

Атрибуты сущности СОТРУДНИК: идентификационный Код_сотрудника  (первичный ключ); ФИО, Дата_рождения, Адрес, Телефон, Образование, Код отдела.

Атрибуты сущности ДОЛЖНОСТЬ: Код_должности (первичный ключ);Наименование; граничные оклады для должности (Мин_оклад и Мак_оклад);

Атрибуты сущности ШТАТНОЕ_РАСПИСАНИЕ: Код_строки шт_расписания (первичный ключ); Код_должности; Код_отдела; Число_ставок.

Атрибуты сущности РАБОТА (Зачисление на должность): Код_сотрудника; Код_штатного_расписания; Оклад ("теоретически" оклад должен лежать в рамках граничных значений для должности); Дата_зачисления;  Объем_должности.

Пример 2

Рассмотрим пример проектирования, основанный на анализе документов, содержащих данные, которые должны быть размещены в БД.

Предметная область: информационная система «ПОСТАВКА ТОВАРОВ»

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

В договоре о поставке товара кроме реквизитов поставщика и покупателя указываются реквизиты товара, срок поставки, минимальная партия поставки, количество поставляемого товара и сумма поставки.

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

Для выделения информационных объектов необходимо сделать следующее:

· выявить документы, используемые фирмой в своей деятельности, и их реквизиты (поля);

· определить функциональную зависимость между реквизитами для каждого документа в отдельности;

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

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

Совокупность полей - реквизитов выделенного объекта должна отвечать требованиям нормализации:

· ИО должен содержать уникальный ключ (простой или составной);

· все остальные (описательные) реквизиты должны быть независимыми друг от друга;

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

· каждый описательный реквизит должен функционально полно зависеть от ключа, т.е. каждому значению ключа должно соответствовать только одно значение описательного реквизита;

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

· каждый описательный реквизит не должен зависеть от ключа транзитивно, через другой промежуточный реквизит.

Определим в документе Справочник Товаров (ТОВАР) функциональные зависимости между реквизитами и присвоим им сокращенные имена (рис. 4).

Реквизиты	Имя реквизита	Функциональные зависимости
Код товара	Код_тов
Наименование товара	Наим_тов
Единица измерения	Еи
Цена	Цена
Ставка НДС	Ндс

Рис. 4. Функциональные зависимости в документе Справочник Товаров

Из анализа документа очевидно, что ключом является Код_тов, от которого функционально полно зависят все остальные описательные реквизиты. Все реквизиты составляют содержание ИО ТОВАР.

Аналогично легко определить ИО ПОКУПАТЕЛЬ и ИО СКЛАД:

ПОКУПАТЕЛЬ ( Код_пок – ключевое поле, ИНН, Наим_пок, Адрес_пок, Ном_расч, Банк).

СКЛАД ( Код_ск -ключевое поле, Наим_ск, Адрес_ск, Отв_лицо).

Определим состав ИО ДОГОВОР, содержащего данные о плановых поставках товара.

Кодом покупателя однозначно определяются описательные реквизиты покупателя – наименование, ИНН, адрес, телефон, расчетный счет, банк. В таблице зависимостей Договор эти реквизиты можно не отображать, поскольку информационный объект ПОКУПАТЕЛЬ, образованный этими реквизитами, был уже выделен.

Описательные реквизиты товара (наименование, единица измерения, цена) однозначно определены кодом товара. Эти реквизиты также можно не включать в таблицу зависимостей Договор, поскольку ранее их взаимосвязи были установлены при анализе ИО ТОВАР. Остальные реквизиты одного договора (количество поставки товара, минимальная партия, сумма за товар) однозначно определяются кодом товара. На всем же множестве договоров эти реквизиты будут функционально полно зависеть от составного ключа:                     Ном_ дог+код_ тов. Будем исходить из того, что в договоре для одного товара возможно несколько сроков поставки, тогда срок поставки войдет в составной ключ Ном_дог+Код_тов+Срок_пост (рис. 5).

Наименование реквизитов	Имя реквизита	Функциональные зависимости
Номер договора	Ном_дог
Дата договора	Дата_дог
Код покупателя	Код_пок
Сумма по договору	Сумма_дог
Код товара	Код_тов
Срок поставки	Срок_пост
Количество поставки	Кол_ пост
Минимальная партия	Мин_пост
Сумма поставки	Сумма_пост

    Рис.5. Функциональные зависимости в документе Договор

Сгруппируем реквизиты, одинаково зависимые от ключевых, и объединим их с ключевыми реквизитами в один информационный объект. В результате получим следующие ИО:

ДОГОВОР (Ном_дог – ключевое поле, Дата_дог, Код_пок, Сумма_дог).

ПОСТАВКА _ПЛАН(Ном_дог, Код_тов, Срок_пост, Кол_пост, Мин_пост, Сумма_пост).

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

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

Описательные реквизиты товара (Наим_тов, Еи, Цена, НДС) однозначно определены Код_тов, что уже учтено в ИО ТОВАР.

Количество отгруженного товара и сумма за товар определяются Код_тов в соответствующей строке, а полная идентификация по всем накладным определяется составным ключом: Ном_накл+Код_ск+Код_тов (рис. 6)

 

Наименование реквизитов	Имя реквизита	Функциональные зависимости
Номер накладной	Ном_накл
Код склада	Код_ ск
Дата отгрузки	Дата_ отгр
Номер договора	Ном_дог
Сумма всего	Сумма_ накл
Код товара	Код_тов
Количество отгруженного товара	Кол_ отгр
Сумма за товар	Сумма_отгр

Рис. 6. Функциональные зависимости в документе Накладная

Из рис.6 видно, что реквизиты Дата_отгр, Ном_дог и Сумма_накл зависят от ключевых атрибутов Ном_накл + Код_ск, а реквизиты Кол_отгр и Сумм_отгр зависят от Ном_накл+ Код_ск + Код_ тов.

Сгруппируем реквизиты, одинаково зависимые от ключевых, и объединим их вместе с ключевыми реквизитами в соответствующие информационные объекты:

НАКЛАДНАЯ (Ном_накл, Код_ск, Дата_отгр, Ном_дог, Сумма_накл). Ключ составной– Ном_накл + Код_ск.

ОТГРУЗКА( Ном_накл, Код_ск, Код_тов, Кол_отгр, Сумма_ отгр). Ключ составной – Ном_накл+Код_ск+Код_тов.

Определение структуры базы данных.

Для определения структуры БД (построения информационно-логической модели) необходимо установить связи между сущностями-ИО. Объекты ДОГОВОР и ТОВАР имеют отношения М:М, поэтому необходима сущность-связка, в качестве которой выступает сущность ПОСТАВКА_ПЛАН. Такая же ситуация и между объектами ТОВАР и НАКЛАДНАЯ – в качестве связки между ними выступает сущность ОТГРУЗКА. Остальные связи – 1:М - между объектами ПОКУПАТЕЛЬ-ДОГОВОР, СКЛАД-НАКЛАДНАЯ, ДОГОВОР-НАКЛАДНАЯ.

 

Рис. 7. Логическая структура (реляционная схема) БД

Логическая структура БД изображена на рис.7. Все связи характеризуются отношением 1:М. Имена ключевых полей подчеркнуты.

Список литературы

1. Шакин В.Н., Сосновиков Г.К, Юскова И.Б. Методические указания по дисциплине ТЕОРЕТИЧЕСКИЕ ОСНОВЫ ПОСТРОЕНИЯ БД/ МТУСИ.- М.,  2004.

2. Сосновиков Г.К., Шакин В.Н., Юскова И.Б.. Методические указания и контрольные задания по дисциплине ОСНОВЫ ПОСТРОЕНИЯ БД/ МТУСИ.- М.,  2004.

3. Сосновиков Г.К., Шакин В.Н.. Практикум по проектированию баз данных средствами СУБД Access./ МТУСИ.- М., 2006.

4. Бекаревич Ю.Б., Пушкина Н.В. Microsoft Access за 21 занятие. – СПб.:БХВ-Петербург, 2005.-544 с.:ил.

5. Пушников А.Ю. Введение в системы управления базами данных. Часть 1. Реляционная модель данных: Учебное пособие/Изд-е Башкирского ун-та. - Уфа, 1999. - 108 с. - ISBN 5-7477-0350-1.

6. Пушников А.Ю. Введение в системы управления базами данных. Часть 2. Нормальные формы отношений и транзакции: Учебное пособие/Изд-е Башкирского ун-та. - Уфа, 1999. - 138 с. - ISBN 5-7477-0351-X.

7. Зверева Н.Н. Методические указания к лабораторным работам по Access 97/Изд. Уфимского авиационного института./Каф. информатики и информационных технологий.-Уфа, 2001.

8. Палеес А.Б. Уч. пособие по курсу:  Базы данных / Лаб. работа:  Использование Erwin для разработки модели данных. – М: МГУПП, 2002.

9. Маклаков С.В.. BPwin и ERwin CASE-средства разработки информационных систем - М.: "ДИАЛОГ-МИФИ", 2000.

10. Microsoft Office Access 2007. Русская версия. Серия «Шаг за шагом»/ Ламберт С., Ламберт М.Д. III,  Преппернау Дж.; пер. с англ.–М:ЭКОМ Паблишера, 2007.- 432 с.: ил

11. Геннадий Гурвиц: Microsoft Access 2010. Разработка приложений на реальном примере (+СD)/ БХВ-Петербург, СПб.2010.

12.  Базы данных. Учебник для вузов./Под ред. Хомоненко А.Д. СПБ.: КОРОНА ПРИНТ 2009.

 

 

Содержание

1. Лабораторная работа 1. СОЗДАНИЕ И РЕДАКТИРОВАНИЕ ТАБЛИЦ. ИЗМЕНЕНИЕ СТРУКТУРЫ ТАБЛИЦ. СОРТИРОВКА И ПОИСК ДАННЫХ. ФОРМЫ.

2. Лабораторная работа 2. Работа с данными с использованием запросов. Запросы-выборки.