Проектирование и реализация базы данных «On-line магазин»

Номер партии → название игры 

 

Номер партии → название поставщика

Номер партии → адрес электронной почты поставщика

Номер партии → адрес поставщика

Номер партии → номер счета «WebMoney» поставщика

Номер партии → контактный телефон поставщика

 

Название игры      название поставщика

Название игры      адрес электронной почты поставщика

Название игры      адрес поставщика

Название игры      номер счета «WebMoney» поставщика

Название игры      контактный телефон поставщика

 

Декомпозиция:

 

Покупатели (ФИО покупателя (РК), логин покупателя, пароль покупателя, адрес электронной почты покупателя, полный домашний адрес покупателя, номер счета «WebMoney» покупателя, размер накопительной скидки покупателя, контактный телефон покупателя)

Сотрудники (ФИО сотрудника (РК), логин сотрудника, пароль сотрудника, должность сотрудника, адрес электронной почты сотрудника, полный домашний адрес сотрудника, номер счета «WebMoney» сотрудника, оклад сотрудника, процентная ставка к зарплате с продажи товара сотрудника, контактный телефон сотрудника)

Продажа (номер продажи (РК), номер счета, дата отправки товара, скидка на доставку, способ доставки, стоимость доставки, дата продажи, скидка на продажу, количество на продажу, ФИО сотрудника (FК), ФИО покупателя (FК), название игры (FК))

Товар (название игры (РК),  разработчик игры, издатель игры, дата выпуска игры, описание игры, количество дисков на складе, процент надбавки на цену покупки у поставщика для получения прибыли, цена последней поставки)

Партии товара (номер партии (РК), количество товара в партии, цена поставки партии, дата поставки партии, название игры (FK), название поставщика (FK))

Поставщики (название поставщика (РК), адрес электронной почты поставщика, адрес поставщика, номер счета «WebMoney» поставщика, контактный телефон поставщика)

 

 

3.4 Четвертая  нормальная форма.

 

Отношение находится в четвертой нормальной форме, если оно находится в третьей нормальной форме, и отсутствуют многозначные зависимости между ключами:

название игры → → номер счета

название игры → → дата отправки товара

название игры → → скидка на доставку

название игры → → способ доставки

название игры → → стоимость доставки

название игры → → ФИО сотрудника

название игры → → ФИО покупателя

 

Декомпозиция:

 

Покупатели (ФИО покупателя (РК), логин покупателя, пароль покупателя, адрес электронной почты покупателя, полный домашний адрес покупателя, номер счета «WebMoney» покупателя, размер накопительной скидки покупателя, контактный телефон покупателя)

Сотрудники (ФИО сотрудника (РК), логин сотрудника, пароль сотрудника, должность сотрудника, адрес электронной почты сотрудника, полный домашний адрес сотрудника, номер счета «WebMoney» сотрудника, оклад сотрудника, процентная ставка к зарплате с продажи товара сотрудника, контактный телефон сотрудника)

Счета (номер счета (РК), дата отправки товара, скидка на доставку, способ доставки, стоимость доставки, ФИО сотрудника (FК), ФИО покупателя (FК))

Продажа (номер продажи (РК), дата продажи, скидка на продажу, количество на продажу, название игры (FК), номер счета (FK))

Товар (название игры (РК),  разработчик игры, издатель игры, дата выпуска игры, описание игры, количество дисков на складе, процент надбавки на цену покупки у поставщика для получения прибыли, цена последней поставки)

Партии товара (номер партии (РК), количество товара в партии, цена поставки партии, дата поставки партии, название игры (FK), название поставщика (FK))

Поставщики (название поставщика (РК), адрес электронной почты поставщика, адрес поставщика, номер счета «WebMoney» поставщика, контактный телефон поставщика)

 

 

 

 

 

 

 

 

3.5 Описание  основных сущностей и их атрибутов.

 

Описание основных сущностей и их атрибутов приводится в таблице 3.1.

 

 

Таблица 3.1. Описание сущностей и атрибутов

Сущность	Описание сущности	Атрибут	Описание атрибута
Покупатели	Содержит информацию о покупателях	ФИО покупателя (РК)	Фамилия, имя, отчество покупателя (первичный ключ)
		Логин покупателя	Логин покупателя для входа в информационную систему
		Пароль покупателя	Пароль покупателя для входа в информационную систему
		Адрес электронной почты покупателя	Адрес электронной почты для обратной связи с покупателем
		Полный домашний адрес покупателя	Адрес места проживания покупателя, куда будут оправлены покупки
		Номер счета «WebMoney» покупателя	Счет «WebMoney» покупателя для осуществления безналичных расчетов
		Размер накопительной скидки покупателя	Накопительная скидка покупателя, зависящая от общей суммы его покупок
		Контактный телефон покупателя	Контактный телефон для обратной связи с покупателем
Сотрудники	Содержит информацию о сотрудниках	ФИО сотрудника (РК)	Фамилия, имя, отчество сотрудника (первичный ключ)
		Логин сотрудника	Логин сотрудника для входа в информационную систему
		Пароль сотрудника	Пароль сотрудника для входа в информационную систему
		Адрес электронной почты сотрудника	Адрес электронной почты для обратной связи с сотрудником
		Полный домашний адрес сотрудника	Адрес, где прописан сотрудник
		Номер счета «WebMoney» сотрудника	Счет «WebMoney» сотрудника для осуществления безналичных расчетов
		Должность сотрудника	Должность, занимаемая сотрудником
		Оклад сотрудника	Оклад, ежемесячно получаемый сотрудником
		Процентная ставка к зарплате с продажи товара сотрудника	Процент от стоимости оформленных продавцом продаж, прибавляемый к его окладу
		Контактный телефон сотрудника	Контактный телефон для обратной связи с сотрудником
Счета	Содержит информацию о счетах покупателей	Номер счета (РК)	Номер счета (первичный ключ)
		Дата отправки товара	Дата, когда будет оправлен покупателю купленный им товар
		Скидка на доставку	Скидка на товар, отправляемый покупателю
		Способ доставки	Способ доставки (наземная почта, авиапочта, курьер)
		Стоимость доставки	Стоимость перевозки товара
		ФИО сотрудника (FК)	Фамилия, имя, отчество сотрудника (внешний ключ от сущности «Сотрудники»)
		ФИО покупателя (FК)	Фамилия, имя, отчество покупателя (внешний ключ от сущности «Покупатели»)
Продажа	Содержит информацию о продажах	Номер продажи (РК)	Номер продажи (первичный ключ)
		Дата продажи	Дата осуществления продажи
		Скидка на продажу	Скидка на покупаемый товар
		Количество на продажу	Количество покупаемого товара
		Название игры (FК)	Название игры (внешний ключ от сущности «Товар»)
		Номер счета (FK)	Номер счета (внешний ключ от сущности «Счета»)
Товар	Содержит информацию о товарах	Название игры (РК)	Название игры (первичный ключ)
		Разработчик игры	Название компании-разработчика игры
		Издатель игры	Название компании-издателя игры
		Дата выпуска игры	Дата, когда игра была отправлена на прилавки магазинов
		Количество дисков на складе	Хранимое на складе количество копий данной игры
		Описание	Краткое описание сюжета и возможностей игры
		Процент надбавки на цену покупки у поставщика для получения прибыли	Процент надбавки, который суммируется с ценой последней поставки данной игры
		Цена последней поставки	Цена последней поставки
Партии товара	Содержит информацию о поступивших партиях товаров	Номер партии (РК)	Номер партии (первичный ключ)
		Количество товара в партии	Количество товара в пришедшей на склад партии
		Цена поставки партии	Цена поставки данной партии товара
		Дата поставки партии	Дата поставки данной партии товара
		Название игры (FK)	Название игры (внешний ключ от сущности «Товар»)
		Название поставщика (FK)	Название поставщика (внешний ключ от сущности «Поставщики»)
Поставщики	Содержит информацию о поставщиках	Название поставщика (РК)	Название компании поставщика (первичный ключ)
		Адрес электронной почты поставщика	Адрес электронной почты для обратной связи с поставщиком
		Адрес поставщика	Адрес компании поставщика
		Номер счета «WebMoney» поставщика	Счет «WebMoney» поставщика для осуществления безналичных расчетов
		Контактный телефон поставщика	Контактный телефон для обратной связи с поставщиком

 

3.6 Выявление связей между сущностями

 

В рассматриваемой предметной области можно выделить связи, приведенные в таблице 3.2:

 

                                                                        Таблица 3.2 – Связи сущностей

 

Родительская сущность	Дочерняя сущность	Описание связи	Мощность связи
Покупатели	Счета	Покупатели имеют Счета	1:M
Сотрудники	Счета	Сотрудники оформляют Счета	1:M
Поставщики	Партии товара	Поставщики поставляют Партии товара	1:M
Товар	Продажа	Товар помещается в Продажу	1:M
Товар	Партии товара	Товар поставляется в Партии товара	1:M
Счета	Продажи	Счета содержат Продажи	1:M

 

3.7 Инфологическая модель данных в нотации Чена.

 

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

 

 

Рисунок 3.1. Модель данных в нотации Чена.

4. Концептуальная модель

 

В качестве СУБД была выбрана PostgreSQL по следующим причинам:

 

• PostgreSQL является бесплатной СУБД.
• Отличная интеграция с языком высокого уровня Java.
• Как следствие предыдущего пункта, PostgreSQL – идеальное решение для реализации web-приложений, написанных на Java.
• Поддержка БД практически неограниченного размера.
• Мощные и надёжные механизмы транзакций и репликации (механизм синхронизации содержимого нескольких копий объекта (например, содержимого базы данных). Репликация — это процесс, под которым понимается копирование данных из одного источника на множество других и наоборот) [4].
• Наследование.
• Легкая расширяемость.

 

В дальнейшем в работе для создания концептуальной модели данных  используется CASE-средство ERwin, которое  позволяет быстро и наглядно спроектировать модель в виде диаграмм «сущность-связь», а затем сгенерировать SQL код базы данных. Так как ERwin 7.3 не поддерживает PostgreSQL, в качестве СУБД была выбрана MySql 5.x, потому что SQL синтаксис и основные типы данных в PostgreSQL и MySql совпадают.

 

4.1 Логический уровень модели данных.

 

В ERwin результат проектирования на концептуальном уровне представляется логической моделью данных (рисунок 4.1).

В логической модели данных отображаются сущности и атрибуты, ключевые атрибуты в модели представлены в сущности, над чертой. Внешние ключи (мигрирующие атрибуты из родительской сущности) обозначаются как (FK – Foreign Key)[2]. Логический уровень означает прямое отображение фактов из реальной жизни. Они именуются на естественном языке, с любыми разделителями слов (пробелы, запятые и т.д.). На логическом уровне не рассматривается использование конкретной СУБД, не определяются типы данных (например, целое или вещественное число) и не определяются индексы для таблиц[2].

Суррогатный ключ – это дополнительное служебное поле, добавленное к уже имеющимся информационным полям таблицы, единственное предназначение которого — служить первичным ключом [4].

Главное достоинство суррогатного ключа состоит в том, что он никогда не изменяется, поскольку не является информативным полем таблицы (не несёт никакой информации об описываемом записью объекте) [4].

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

Введем суррогатные ключи для сущностей «Покупатели», «Сотрудники», «Поставщики» и «Товар», кроме того, атрибуты «ФИО покупателя» и «ФИО сотрудника» разделим на три атрибута («фамилия», «имя», «отчество») каждый и сгруппируем их в составные альтернативные ключи. Так же альтернативными ключами сделаем атрибуты «название поставщика» и «название игры». Альтернативные ключи (AK – Alternative Key) служат для ускорения поиска по базе данных.

 

 

Рисунок 4.1 – Модель данных на логическом уровне в нотации IDEF1x

 

4.2 Физический уровень модели данных.

 

Модель данных на физическом уровне отличается от модели данных на логическом уровне тем, что она полностью ориентирована на выбранную СУБД, т.е. в отличие от логической модели, в которой не имеет значения, какой конкретно тип данных имеет атрибут, в физической модели данных важно описать информацию о конкретных физических объектах - таблицах, полях, индексах, процедурах и т.д [2]. Для СУБД PostgreSQL характерно то, что все объекты базы данных, должны иметь англоязычное наименование.

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

Рисунок 4.2 – Модель данных на физическом уровне в нотации IDEF1x

 

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

 

Сущность	Таблица
Название	Название
Партии товара	Accounts
Персонал магазина	Buyers
Покупатели	Consignment
Поставщики	Goods
Продажа	Reteil
Счета	Shop_Staff
Товар	Vendor

 

Attribute(s) of "Партии товара" Entity	Column(s) of "Consignment" Table
Название	Name	Datatype	Is PK	Is FK
Номер партии	Consignment_ID	NUMERIC	Yes	No
Дата	Consignment_date	DATE	No	No
Номер поставщика	Vendor_ID	NUMERIC	No	Yes
Количество товара в партии	Goods_count	INTEGER	No	No
Цена поставки	Delivery_price	DOUBLE PRECISION	No	No
Номер товара	Goods_ID	NUMERIC	No	Yes

 

Attribute(s) of "Сотрудники" Entity	Column(s) of "Shop_Staff" Table
Название	Name	Datatype	Is PK	Is FK
Номер сотрудника	Employee_ID	NUMERIC	Yes	No
Фамилия	Last_Name	VARCHAR(20)	No	No
Имя	First_Name	VARCHAR(20)	No	No
Отчество	Third_Name	VARCHAR(20)	No	No
Логин	Login	VARCHAR(20)	No	No
Пароль	Password	VARCHAR(20)	No	No
Должность	Post	VARCHAR(20)	No	No
email	email	VARCHAR(20)	No	No
Номер счета WebMoney	WebMoney_account_number	VARCHAR(20)	No	No
Домашний адрес	Home_address	VARCHAR(100)	No	No
Контактный телефон	Phone	VARCHAR(12)	No	No
Ставка от продажи, %	Wage_rate	NUMERIC	No	No
Оклад, руб	Salary	DOUBLE PRECISION	No	No