Разработка базы данных "Прокат видеокассет"

16

Министерство образования Российской Федерации

Тольяттинский Государственный Университет

Кафедра «Информатика и ВТ»

Контрольная работа по курсу

«Базы данных»

Вариант №3

«Прокат видеокассет»

Тольятти 2009

Описание предметной области

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

База данных "Прокат видеокассет" выполняет следующие основные функции:

· Регистрация клиентов

· Ввод товаров

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

· Гибкая система обслуживания клиентов в зависимости от их Категории: Накопительный, Постоянный, Постоянный VIP, Бесплатно, Без залога...

· Прием оплаты от клиентов, договора с клиентом и т.п.

· Различные отчеты по клиентам, справка о клиенте

· Разграничение доступа к функциям системы для сотрудников пункта проката

· Простой и понятный, настраиваемый интерфейс пользователя

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

Инфологическое моделирование

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

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

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

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

Сущность имеет имя, уникальное в пределах модели. При этом имя сущности - это имя типа, а не конкретного экземпляра.

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

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

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

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

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

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

Между двумя сущностям, например, А и В возможны четыре вида связей:

Первый тип - «связь один-к-одному» (1:1): в каждый момент времени каждому экземпляру сущности А соответствует 1 или 0 представителей сущности В.

Второй тип - связь «один-ко-многим» (1:М): одному представителю сущности А соответствуют 0, 1 или несколько представителей сущности В.

Так как между двумя сущностями возможны связи в обоих направлениях, то существует еще два типа связи «много-к-одному» (М:1) и «много-ко-многим» (М:М).

Модель «сущность-связь»

Инфологическая модель отображает реальный мир в некоторые понятные человеку концепции, полностью независимые от параметров среды хранения данных. Существует множество подходов к построению таких моделей: графовые модели, семантические сети, модель "сущность-связь" и т.д. Наиболее популярной из них оказалась модель "сущность-связь" или называемая ещё ER-моделью (от англ. Entity-Relationship, т.е. сущность-связь).

Инфологическая модель применяется после словесного описания предметной области.

Между сущностями могут быть установлены связи - бинарные ассоциации, показывающие, каким образом сущности соотносятся или взаимодействуют между собой. Связь может существовать между двумя разными сущностями или между сущностью и ей же самой (рекурсивная связь). Она показывает, как связаны экземпляры сущностей между собой. Если связь устанавливается между двумя сущностями, то она определяет взаимосвязь между экземплярами одной и другой сущности

Связи делятся на три типа по множественности: один-ко-одному (1:1), один-ко-многим (1:М), много-к-одному (М:1), много-ко-многим (М:М).

Связь один-ко-одному означает, что экземпляр одной сущности связан только с одним экземпляром другой сущности. Связь один-ко-многим (1:М) означает, что один экземпляр сущности, расположенный слева по связи, может быть связан с несколькими экземплярами сущности, расположенными справа по связи.

Связь много-к-одному (М:1) означает, что несколько экземпляров сущности, расположенных слева по связи, могут быть связаны с одним экземпляром сущности, расположенным справа по связи.

Связь «многие-ко-многим (М:М) означает, что несколько экземпляров первой сущности могут быть связаны с несколькими экземплярами второй сущности, и наоборот. Между двумя сущностями может быть задано сколько угодно связей с разными смысловыми нагрузками.

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

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

Номер клиента: ФИО (частичная зависимость)

адрес (частичная зависимость)

телефон (частичная зависимость)

категория (частичная зависимость)

дата взятия кассеты (полная зависимость)

дата возврата кассеты (полная зависимость)

стоимость (полная зависимость)

Код заказа (полная зависимость)

Код сотрудника (транзитивная зависимость)

Номер кассеты: жанр (частичная зависимость)

ФИО (частичн. завис-ть)

наименование (частичная зависимость)

табельный номер (частичн. завис-ть)

режиссер (частичная зависимость)

аннотация (частичная зависимость)

стоимость (частичная зависимость)

Вторая нормальная форма (2НФ)

Клиент

Код клиента	ФИО	Адрес	Телефон	Категория
1	Маркина Н.В.	ул. Степана Разина, д. 3, кв. 123	32-24-43	VIP
2	Елисеев Д.А.	ул. Фрунзе, д. 5, кв. 54	76-53-21	Без залога
3	Салтыкова Е.Ю.	Ленинский пр., д. 3, кв. 34	86-76-65	VIP
4	Батталов Р.С.	ул. Фрунзе, д. 2, кв. 56	45-35-46	Без залога
5	Брусникина О.А.	б-р Туполева, д. 3, кв. 675	11-21-33	VIP

Видеотека

Код кассеты	Жанр	Наименование	Режиссер	Аннотация	Стоимость
100	Исторический фильм	Гладиатор	Ридли Скотт	История великого генерала Максимуса.	200,00р.
107	Боевик	Брат 2	Алексей Балабанов	Одного из боевых друзей, работающего охранником, убивают.	200,00р.
109	Исторический фильм	Жанна Д'арк	Люк Бессон	Жанне еще в детстве слышался в церкви голос красивого мужчины.	150,00р.
111	Фэнтэзи	Люди в черном	Бэрри Сонненфелд	Захватывающий фантастический боевик.	150,00р.
113	Триллер	Тень вампира	И. Элайс Мэридж	Таинственные события, якобы произошедшие на съемках.	100,00р.

Заказ

Код	Дата	Код клиента	Код кассеты	Сумма	Дата возврата	Код сотрудника	ФИО	Табельный номер	Личный пароль
1312	03.03.09	1	100	200,00р.	03.04.09	111-А	Смирнова А.Л.	43845854	*****
2244	07.03.09	2	111	150,00р.	07.04.09	222-Б	Галкина А.Ю.	23423455	*****
3434	10.03.09	3	107	200,00р.	10.04.09	333-В	Дуброва К.Ю.	75534523	*****
4464	11.03.09	4	109	150,00р.	11.04.09	444-Г	Мельников А.Ф.	76864432	*****
5087	17.03.09	5	113	100,00р.	17.04.09	555-Д	Зернова Е.А.	87112211	*****

Третья нормальная форма (3НФ)

Третья нормальная форма связана с понятием транзитивной зависимости. Пусть имеются функциональные зависимости А В, В С. Тогда считается, что С транзитивно зависит от А (А С) через элемент В.

Таблица Заказ содержит транзитивную зависимость атрибутов:

Код заказа (полная зависимость) Код сотрудника (транзитивная зависимость)

ФИО

табельный номер

личный пароль

Таблица Заказ изменяется, и в отдельную таблицу выделяются Код сотрудника, ФИО, Табельный номер и Личный пароль.

1. Клиент

2. Видеотека

3. Заказ

Код заказа	Дата	Код клиента	Код кассеты	Сумма	Дата возврата	Код сотрудника
1312	03.03.09	1	100	200,00р.	03.04.09	111-А
2244	07.03.09	2	111	150,00р.	07.04.09	222-Б
3434	10.03.09	3	107	200,00р.	10.04.09	333-В
4464	11.03.09	4	109	150,00р.	11.04.09	444-Г
5087	17.03.09	5	113	100,00р.	17.04.09	555-Д

4. Сотрудник

Код сотрудника	ФИО	Табельный номер	Личный пароль
111-А	Смирнова А.Л.	43845854	*****
222-Б	Галкина А.Ю.	23423455	*****
333-В	Дуброва К.Ю.	75534523	*****
444-Г	Мельников А.Ф.	76864432	*****
555-Д	Зернова Е.А.	87112211	*****

Клиент

1. Код клиента

2. ФИО

3. Адрес

4. Телефон

5. Категория

Видеотека

1. Код кассеты

2. Жанр

3. Наименование

4. Режиссер

5. Аннотация

6. Обложка

7. Стоимость

Заказ

1. Код заказа

2. Дата

3. Код клиента

4. Код кассеты

5. Сумма

6. Дата возврата

7. Код сотрудника



Сотрудник

1. Код сотрудника

2. ФИО

3. Табельный номер

4. Личный пароль

Связи между сущностями

Определим связи между выявленными сущностями. Связь один-ко-многим (1:М): одному представителю сущности А соответствуют 0, 1 или несколько представителей сущности В.

Моделирование связей между сущностями предметной области.



Четвертая нормальная форма (4НФ)

В отношении R атрибут В многозначно зависит от атрибута А, если каждому значению А соответствует множество значений В, не связанных с другими атрибутами из R.

Обозначение:

А => В (отношение 1:М)

А <= В (отношение М:1)

А В (отношение М:М)

Отношение R находится в 4НФ в том и только в том случае, когда существует многозначная зависимость А => В, а все остальные атрибуты R функционально зависят от А.

или

Переменная-отношение R находится в 4НФ тогда и только тогда, когда в случае существования таких подмножеств А и В атрибутов этой переменной-отношения R, для которых выполняется нетривиальная многозначная зависимость А => В, все атрибуты переменной-отношения R также функционально зависят от атрибута А.

Рассмотрим пример по данной теме:

Дата	Номер клиента	Сумма
01.04.09	17	200,00 р.
01.04.09	34	170,00 р.
13.04.09	17	200,00 р.
13.04.09	17	200,00 р.
21.04.09	34	200,00 р.
21.04.09	17	170,00 р.

Анализ отношения позволяет заключить, что первичным ключом в данном отношении является составной ключ Дата + Номер_клиента + Сумма. Отсюда следует, что существует многозначная зависимость Дата => Номер_клиента, Дата => Сумма.

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

Теорема Фейджина. Отношение R (А, В, С) можно спроецировать без потерь в отношения R1(А, В) и R2(А, С) в том и только том случае, когда существуеи зависимость А => В|С.

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

ДАТА-СУММА
01.04.09	170,00 р.
01.04.09	200,00 р.
13.04.09	200,00 р.
21.04.09	170,00 р.
21.04.09	200,00 р.
ДАТА-КЛИЕНТ
01.04.09	17
01.04.09	34
13.04.09	17
21.04.09	17
21.04.09	34

Первичный ключ -и Дата + Номер_клиента Дата + Сумма

Пятая нормальная форма (5НФ)

Переменная-отношение R находится в 5НФ, которую иногда иначе называют проекционно-соединительной формой (ПСНФ), тогда и только тогда, когда каждая нетривиальная зависимость соединения в переменной-отношении R подразумевается ее потенциальными ключами.

Привести отношение R к 5НФ, значит попробовать образовать такие отношения R1, R2, …,на которые может быть спроецировано отношение R, а затем соединить данные отношения и получить исходное R.

Проанализируем следующее отношение:

Код_склада	Номер_пункта_проката	Номер_клиента
89763	A-1	31
83456	F-6	53
87126	A-1	64
88972	F-6	31
81345	Q-17	53
83567	Q-17	72
82792	F-6	64
86965	Q-17	72
85443	Q-17	31
84557	A-1	53

1) Предположим, что необходимо рассмотреть следующие три отношения - зависимости соединения

СП={Код_склада, Номер_пункта_проката}

СК={Код_склада, Номер_клиента}

ПК={Номер_пункта_проката, Номер_клиента}

Код_склада	Номер_пункта_проката
89763	A-1
83456	F-6
87126	A-1
88972	F-6
81345	Q-17
83567	Q-17
82792	F-6
86965	Q-17
85443	Q-17
84557	A-1
Код_склада	Номер_клиента
89763	31
83456	53
87126	64
88972	31
81345	53
83567	72
82792	64
86965	72
85443	31
84557	53

Номер_пункта_проката	Номер_клиента
F-6	31
F-6	53
F-6	64
Q-17	31
Q-17	53
Q-17	72
A-1	31
A-1	53
A-1	64

2) Произвести соединения каждой из пар.

*(СП, СК)

89763	A-1	31
83456	F-6	53
87126	A-1	64
88972	F-6	31
81345	Q-17	53
83567	Q-17	72
82792	F-6	64
86965	Q-17	72
85443	Q-17	31
84557	A-1	53
89763	A-1	31
89763	A-1	53
89763	A-1	64
83456	F-6	31
83456	F-6	53
83456	F-6	64
87126	A-1	31
87126	A-1	53
87126	A-1	64
88972	F-6	31
88972	F-6	53
88972	F-6	64
81345	Q-17	31
81345	Q-17	53
81345	Q-17	72
83567	Q-17	31
83567	Q-17	53
83567	Q-17	72
82792	F-6	31
82792	F-6	53
82792	F-6	64
86965	Q-17	31
86965	Q-17	53
86965	Q-17	72
85443	Q-17	31
85443	Q-17	53
85443	Q-17	72
84557	A-1	31
84557	A-1	53
84557	A-1	64

*(СК, ПК)

89763	31	A-1
89763	31	F-6
89763	31	Q-17
83456	53	A-1
83456	53	F-6
83456	53	Q-17
87126	64	A-1
87126	64	F-6
88972	31	A-1
88972	31	F-6
88972	31	Q-17
81345	53	A-1
81345	53	F-6
81345	53	Q-17
83567	72	Q-17
82792	64	A-1
82792	64	F-6
86965	72	Q-17
85443	31	A-1
85443	31	F-6
85443	31	Q-17
84557	53	A-1
84557	53	F-6
84557	53	Q-17

3) Произвести пересечение всех отношений. Это пересечение должно дать исходное отношение. 5НФ имеет свои недостатки. Считается, что 4НФ и 5НФ на практике встречаются очень редко.