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

В ходе проектирования физического уровня была получена модель, представленная на рисунке 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

 

 

Attribute(s) of "Покупатели" Entity	Column(s) of "Buyers" Table
Название	Name	Datatype	Is PK	Is FK
Номер покупателя	Buyer_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
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
Накопительная скидка, %	Summ_Discount	INTEGER	No	No

 

 

Attribute(s) of "Поставщики" Entity	Column(s) of "Vendor" Table
Название	Name	Datatype	Is PK	Is FK
Номер поставщика	Vendor_ID	NUMERIC	Yes	No
Название	Vendor_Name	VARCHAR(30)	No	No
Адрес	Address	VARCHAR(100)	No	No
email	email	VARCHAR(20)	No	No
Номер счета WebMoney	WebMoney_account_number	VARCHAR(20)	No	No
Телефон	Phone	VARCHAR(12)	No	No

 

 

Attribute(s) of "Продажа" Entity	Column(s) of "Reteil" Table
Название	Name	Datatype	Is PK	Is FK
Номер продажи	Reteil_ID	NUMERIC	Yes	No
Номер счета	Account_ID	NUMERIC	No	Yes
Количество	Reteil_count	INTEGER	No	No
Скидка, %	Discount	INTEGER	No	No
Номер товара	Goods_ID	NUMERIC	No	Yes
Дата продажи	Date_of_reteil	DATE	No	No

 

 

Attribute(s) of "Счета" Entity	Column(s) of "Accounts" Table
Название	Name	Datatype	Is PK	Is FK
Номер счета	Account_ID	NUMERIC	Yes	No
Номер покупателя	Buyer_ID	NUMERIC	No	Yes
Дата отправки товара	Date_of_sending	DATE	No	No
Скидка на доставку, %	Discount_on_ shipping	INTEGER	No	No
Номер сотрудника	Employee_ID	NUMERIC	No	Yes
Способ доставки	Type_of_shipping	VARCHAR(30)	No	No
Стоимость доставки	Price_of_shipping	DOUBLE PRECISION	No	No

 

 

Attribute(s) of "Товар" Entity	Column(s) of "Goods" Table
Название	Name	Datatype	Is PK	Is FK
Номер товара	Goods_ID	NUMERIC	Yes	No
Разработчик	Developer	VARCHAR(25)	No	No
Название	Name	VARCHAR(25)	No	No
Издатель	Publisher	VARCHAR(25)	No	No
Описание	Description	TEXT	No	No
Дата выпуска	Date_of_release	DATE	No	No
Процент увеличения цены	Percent_of_price_ increasing	INTEGER	No	No
Количество на складе	Count_at_storehouse	NUMERIC	No	No
Цена последней поставки	Price_of_last_ delivery	DOUBLE PRECISION	No	No
Изображение	Image	VARCHAR(50)	No	No

 

 

4.3 Сгенерированный в ERwin SQL код таблиц.

 

CREATE TABLE Vendor

(

Vendor_ID            NUMERIC NOT NULL,

Vendor_Name          VARCHAR(30),

Address              VARCHAR(100),

Phone                VARCHAR(12),

email                VARCHAR(20),

WebMoney_account_number VARCHAR(20),

PRIMARY KEY (Vendor_ID)

);

 

 

CREATE TABLE Goods

(

Goods_ID             NUMERIC NOT NULL,

Developer            VARCHAR(25),

Name                 VARCHAR(25),

Publisher            VARCHAR(25),

Description          TEXT,

Percent_of_price_increasing INTEGER,

Count_at_storehouse  NUMERIC,

Price_of_last_delivery DOUBLE PRECISION,

Date_of_release      DATE,

Image                VARCHAR(50),

PRIMARY KEY (Goods_ID)

);

 

 

CREATE TABLE Consignment

(

Consignment_ID       NUMERIC NOT NULL,

Consignment_date     DATE,

Vendor_ID            NUMERIC NOT NULL,

Goods_count          INTEGER,

Delivery_price       DOUBLE PRECISION,

Goods_ID             NUMERIC NOT NULL,

PRIMARY KEY (Consignment_ID),

(Vendor_ID) REFERENCES Vendor (Vendor_ID),

(Goods_ID) REFERENCES Goods (Goods_ID)

);

 

 

 

CREATE TABLE Buyers

(

Buyer_ID             NUMERIC NOT NULL,

Last_Name            VARCHAR(20),

First_Name           VARCHAR(20),

Third_Name           VARCHAR(20),

Login                VARCHAR(20),

Password             VARCHAR(20),

email                VARCHAR(20),

WebMoney_account_number VARCHAR(20),

Home_address         VARCHAR(100),

Phone                VARCHAR(12),

Summ_Discount        INTEGER,

PRIMARY KEY (Buyer_ID)

);

 

 

CREATE TABLE Shop_Staff

(

Employee_ID          NUMERIC NOT NULL,

Last_Name            VARCHAR(20),

First_Name           VARCHAR(20),

Third_Name           VARCHAR(20),

Login                VARCHAR(20),

Password             VARCHAR(20),

Post                 VARCHAR(20),

email                VARCHAR(20),

WebMoney_account_number VARCHAR(20),

Home_address         VARCHAR(100),

Phone                VARCHAR(12),

Wage_rate            NUMERIC,

Salary               DOUBLE PRECISION,

PRIMARY KEY (Employee_ID)

);

 

 

CREATE TABLE Accounts

(

Account_ID           NUMERIC NOT NULL,

Buyer_ID             NUMERIC NOT NULL,

Date_of_sending      DATE,

Discount_on_shipping INTEGER,

Employee_ID          NUMERIC NOT NULL,

Type_of_shipping     VARCHAR(30),

Price_of_shipping    DOUBLE PRECISION,

PRIMARY KEY (Account_ID),

(Buyer_ID) REFERENCES Buyers (Buyer_ID),

(Employee_ID) REFERENCES Shop_Staff (Employee_ID)

);

 

 

CREATE TABLE Reteil

(

Reteil_ID            NUMERIC NOT NULL,

Account_ID           NUMERIC NOT NULL,

Reteil_count         INTEGER,

Discount             INTEGER,

Goods_ID             NUMERIC NOT NULL,

Date_of_reteil       DATE,

PRIMARY KEY (Reteil_ID),

(Account_ID) REFERENCES Accounts (Account_ID),

(Goods_ID) REFERENCES Goods (Goods_ID)

);

 

5. Проектирование представлений, последовательностей, триггеров, хранимых процедур.

 

 

5.1 Последовательности.

 

 

При использовании  суррогатных ключей не следует озадачивать  пользователя вводом значений, которые  не несут для него никакой информации. Эти поля в среде СУБД PostgreSQL заполняются автоматически с помощью, так называемых последовательностей (Sequences).

 

Список последовательностей:

 

id_accounts – последовательность для суррогатного ключа таблицы Accounts

id_vendor - последовательность для суррогатного ключа таблицы Vendor

id_goods - последовательность для суррогатного ключа таблицы Goods

id_reteil - последовательность для суррогатного ключа таблицы Reteil

id_buyers - последовательность для суррогатного ключа таблицы Buyers

id_staff - последовательность для суррогатного ключа таблицы Shop_Staff

id_consignment - последовательность для суррогатного ключа таблицы Consignment

 

 

 

5.2 Триггеры.

 

 

Триггер — это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено наступлением определенного события (действием) — по сути добавлением INSERT или удалением DELETE строки в заданной таблице, или модификации UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан. Все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера [4].

В среде PostgreSQL код триггера содержит только событие  для срабатывания и вызов триггерной функции, в которой содержится вся  логика триггера.

Разработанные триггеры представлены в таблице 5.1.

 

 

 

Таблица 5.1. Описание разработанных  триггеров.

 

Название триггера	Соответствующая триггерная функция	Событие для срабатывания триггера	Описание
consigment_datе_check	cons_datе_check	До вставки или до изменения	Триггер для таблицы Сonsigment. Проверка даты поставки партии (она должна быть меньше или равна текущей)
goods_date_ check	goods_datecheck	До вставки или до изменения	Триггер для таблицы Goods. Проверка даты выхода игры (она должна быть меньше или равна текущей)
reteil_date_check	ret_date_check	До вставки или до изменения	Триггер для таблицы Reteil. Проверка даты продажи (она должна быть меньше или равна текущей)
goods_update_from_consig	goods_works	После вставки	Триггер для таблицы Goods. Автоматическое добавление в поле «Количество на складе» таблицы «Товары» количества товара, указанного при осуществлении поставки, а также заполнение поля «Цена последней поставки»
reteil_ dateSending	dateSending	После вставки	Триггер для таблицы Reteil. Автоматическая установка даты отправки товара (дата покупки + 5 дней)
reteil_update_ count	reteil_works	До вставки	Триггер для таблицы Reteil. При осуществлении продажи – автоматическое уменьшение числа товара на складе на количество проданного товара, а также вывод на экран ошибки в случае, когда количество на продажу больше, чем количество товара на складе
user_summ_ discount	user_sum_discount	После вставки	Триггер для таблицы Reteil. Подсчет накопительной скидки для покупателя при осуществлении новой покупки

 

 

 

5.3 Представления.

 

В отличие  от обычных таблиц реляционной БД, представление не является самостоятельной  частью набора данных, хранящегося  в базе. Содержимое представления  динамически вычисляется на основании  данных, находящихся в реальных таблицах. Изменение данных в реальной таблице  БД немедленно отражается в содержимом всех представлений, построенных на основании этой таблицы.

Разработанные представления описаны в таблице 5.2.

 

Таблица 5.2. Описание разработанных  представлений.