Создание спроектированной базы данных

Для реализации спроектированной базы данных была выбрана система управления базами данных Microsoft SQL Server 2008. Это обусловлено ряд причинами:

1) поддержка БД практически неограниченного размера;

2) мощные и надёжные механизмы транзакций и репликации;

3) легкая расширяемость.

В создаваемой базе данных будут использоваться следующие типы данных:

1 INT – Целочисленный тип. Размер – 4 байта

2 VARCHAR – Строковый тип

3 BIT – Целочисленный тип размером в 1 байт. Определяется как логический тип.

4 DATE – Тип, определяющий дату.

Опишем все таблицы, которые будут созданы в базе данных.

Таблица tipograf содержит данные о типографиях, имеющихся на сервере. Ее структура приведена в таблице 3.1.

Таблица 3.1 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_tipograf	int	Идентификатор типографии. Ключевой атрибут
name	varchar(30)	Название типографии
adres	varchar(60)	Адрес типографии

Таблица vid_pack содержит о виде отправлений. Ее структура приведена в таблице 3.2.

Таблица 3.2 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_vid_pack	int	Идентификатор вида отправления. Ключевой атрибут
vid	varchar(30)	Вид отправления

Таблица tiraj содержит информацию о тиражах изданий. Ее структура приведена в таблице 3.3.

Таблица 3.3 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_tiraj	int	Идентификатор тиража. Ключевой атрибут
fk_tipograf	int	Идентификатор типографии. Внешний ключ
fk_izd	int	Идентификатор издания. Внешний ключ
kolvo	int	Количество изданий в тираже
busy	int	Занятость тиража

Таблица post_pack_operation содержит информацию об проведенных операциях с отправлениями. Ее структура приведена в таблице 3.4.

Таблица 3.4 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_pack_operation	int	Идентификатор операции отправления. Ключевой атрибут
fk_poluch	int	Идентификатор получателя. Внешний ключ
fk_otpr	int	Идентификатор отправителя. Внешний ключ
fk_poluch_adres	int	Идентификатор адреса получателя. Внешний ключ
fk_otpr_adres	int	Идентификатор адреса отправителя. Внешний ключ
fk_operator	int	Идентификатор оператора. Внешний ключ
data	date	Дата регистрации отправления
tip	bit	Тип получения (полученные/отправленные)

Таблица post_pack содержит об отправлении. Ее структура приведена в таблице 3.5.

Таблица 3.5 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_post_pack	int	Идентификатор почтового отправления. Ключевой атрибут
fk_vid_pack	int	Идентификатор вида отправления. Внешний ключ
ves	int	Вес отправления
cennost	int	Ценность отправления
sposob_dost	varchar(20)	Категория отправления
cena	int	Итоговая стоимость отправления
track	varchar(13)	Track-номер отправления

Таблица fio содержит информацию фамилиях, именах и именах клиентов/сотрудников почтового отделения. Ее структура приведена в таблице 3.6.

Таблица 3.6 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_fio	int	Идентификатор ФИО. Ключевой атрибут
Fam	varchar(30)	Фамилия
Im	varchar(20)	Имя
Otch	varchar(30)	Отчество

Таблица client содержит информацию о клиентах. Ее структура приведена в таблице 3.7.

Таблица 3.7 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
fk_fio	int	Идентификатор ФИО. Внешний ключ
telefon	varchar(15)	Телефон
id_client	int	Идентификатор клиента. Ключевой атрибут

Таблица sotrudnik содержит информацию о сотрудниках почтового отделения. Ее структура приведена в таблице 3.8.

Таблица 3.8 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_sotrudnik	int	Идентификатор сотрудника. Ключевой атрибут
fk_fio	int	Идентификатор ФИО. Внешний ключ
doljnost	varchar(20)	Должность сотрудника

Таблица ab_operation содержит информацию о проведенных операциях подписки на а/я. Ее структура приведена в таблице 3.9.

Таблица 3.9 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
fk_client	int	Идентификатор клиента. Внешний ключ
fk_abonent	int	Идентификатор а/я. Внешний ключ
data	date	Дата регистрации оформления а/я
srok	date	Срок подписки на а/я
cena	int	Цена итого
oplata	bit	Произведение оплаты
id_ab_operation	int	Идентификатор оформления а/я. Ключевой атрибут

Таблица uch_operation описывает информацию об обслуживании работниками почтового отделения. Ее структура приведена в таблице 3.10.

Таблица 3.10 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
fk_uchastok	int	Идентификатор участка. Внешний ключ
fk_sotrudnik	int	Идентификатор сотрудника. Внешний ключ
data_n	date	Дата начала службы
data_o	date	Дата окончания службы
fk_office	int	Идентификатор почтового отделения. Внешний ключ
id_uch_operation	int	Идентификатор обслуживающего персонала. Ключевой атрибут

Таблица abonent содержит информацию об абонентских ящиках. Ее структура приведена в таблице 3.11.

Таблица 3.11 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
nomer	int	Номер а/я
cena	int	Цена за 1 день подписки а/я
fk_office	int	Идентификатор почтового отделения. Внешний ключ
id_abonent	int	Идентификатор а/я. Ключевой атрибут

Таблица podpiska содержит информацию о совершенных подписках. Ее структура приведена в таблице 3.12.

Таблица 3.12 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_podpiska	int	Идентификатор подписки. Ключевой атрибут
id_izd	int	Идентификатор издания. Внешний ключ
fk_client	int	Идентификатор подписчика. Внешний ключ
fk_adres	int	Идентификатор адреса доставки. Внешний ключ
fk_nomer	int	Идентификатор а/я. Внешний ключ
fk_lgota	int	Идентификатор льготы. Внешний ключ
cena	int	Цена подписки
oplata	bit	Произведенная оплата
data	date	Дата подписки
data1	bit	Январь
data2	bit	Февраль
data3	bit	Март
data4	bit	Апрель
data5	bit	Май
data6	bit	Июнь
data7	bit	Июль
data8	bit	Август
data9	bit	Сентябрь
data10	bit	Октябрь
data11	bit	Ноябрь
data12	bit	Декабрь

Таблица lgota содержит информацию о льготах для подписки. Ее структура приведена в таблице 3.13.

Таблица 3.13 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_lgota	int	Идентификатор льготы. Ключевой атрибут
razmer	int	Размер льготы

Таблица office содержит информацию о почтовых отделениях. Ее структура приведена в таблице 3.14.

Таблица 3.14 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
adres	varchar(60)	Адрес почтового отделения
indeks	int	Индекс почтового отделения
id_office	int	Идентификатор почтового отделения. Ключевой атрибут

Таблица izd содержит информацию об изданиях для подписки. Ее структура приведена в таблице 3.15.

Таблица 3.15 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_izd	int	Идентификатор издания. Ключевой атрибут
name	varchar(30)	Название издания
tip	varchar(15)	Тип издания
indeks	int	Подписной индекс издания
period	int	Периодичность выхода в месяц
cena	int	Цена за 1 ед.

Таблица zayavka содержит о связи тиража с изданиями, которые они производят. Ее структура приведена в таблице 3.16.

Таблица 3.16 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_zayavka	int	Идентификатор заявки. Ключевой атрибут
fk_tiraj	int	Идентификатор тиража. Внешний ключ
fk_podpiska	int	Идентификатор подписки. Внешний ключ

Таблица uchastok содержит информацию об обслуживаемых участках почтового отделения. Ее структура приведена в таблице 3.17.

Таблица 3.17 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_uchastok	int	Идентификатор участок. Ключевой атрибут
nomer	int	Номер участка
fk_office	int	Идентификатор почтового отделения. Внешний ключ

Таблица adres содержит информацию об адресах (для получения/отправления, подписки). Ее структура приведена в таблице 3.18.

Таблица 3.18 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_adres	int	Идентификатор адреса. Ключевой атрибут
indeks	int	Индекс
strana	varchar(30)	Страна
gorod	varchar(20)	Город
ulica	varchar(30)	Улица
dom	int	Дом
korpus	int	Корпус
kvartira	int	Квартира
fk_uchastok	int	Идентификатор участка. Внешний ключ

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

Таблица 3.19 – Характеристика атрибутов таблицы

Имя атрибута	Тип	Описание
id_dostavki	int	Идентификатор доставки. Ключевой атрибут
fk_pochtal	int	Идентификатор почтальона. Внешний ключ
fk_podpiska	int	Идентификатор подписки. Внешний ключ

Запись выражений указанных в варианте задания типов запросов на языке SQL

1. Вывести типографии, у которых исчерпан тираж для подписки на указанное издание:

CREATE PROCEDURE [dbo].[zapros1]

@name varchar (30)

AS

BEGIN

if (@name is not null)

begin

SELECT tipograf.name as Типография, tipograf.adres as Адрес, izd.name AS [Наименование издания]

FROM tiraj INNER JOIN

tipograf ON tiraj.fk_tipograf = tipograf.id_tipograf INNER JOIN

izd ON tiraj.fk_izd = izd.id_izd

WHERE (tiraj.busy = 1) and (izd.name = @name)

end

else

begin

SELECT tipograf.name as Типография, tipograf.adres as Адрес, izd.name AS [Наименование издания]

FROM tiraj INNER JOIN

tipograf ON tiraj.fk_tipograf = tipograf.id_tipograf INNER JOIN

izd ON tiraj.fk_izd = izd.id_izd

WHERE (tiraj.busy = 1)

End

2. Вывести общее количество подписчиков на указанное издание и указать количество подписчиков для остальных изданий:

CREATE PROCEDURE [dbo].[zapros2]

@name varchar (30)

AS

BEGIN

if (@name is not null)

begin

SELECT izd.name as Название, COUNT(izd.name) AS [Количество подписчиков]

FROM izd INNER JOIN

podpiska ON izd.id_izd = podpiska.id_izd

where izd.name = @name

GROUP BY izd.name

end

else

begin

SELECT izd.name as Название, COUNT(izd.name) AS [Количество подписчиков]

FROM izd INNER JOIN

podpiska ON izd.id_izd = podpiska.id_izd

GROUP BY izd.name

End

3. Вывести свободные абонентские ящики:

CREATE VIEW [dbo].[free_ab]

AS

SELECT nomer, cena, fk_office, id_abonent

FROM dbo.abonent

WHERE (id_abonent NOT IN

(SELECT fk_abonent

FROM dbo.ab_operation))

4. Вывести клиентов, которым необходимо доставить почтовые отправления в текущем месяце:

select fio.Fam as Имя, vid_pack.vid as Обслуживание

from fio, client, post_pack_operation, vid_pack, post_pack

where post_pack_operation.fk_poluch = client.id_client and client.fk_fio = fio.id_fio and

post_pack_operation.id_pack_operation = post_pack.id_post_pack and post_pack.fk_vid_pack =

vid_pack.id_vid_pack and (post_pack_operation.tip = 'false') and (DATEPART (MONTH,data) = DATEPART (MONTH,GETDATE()))

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

select fio.Fam as Имя, COUNT(fio.Fam) as [Количество подписок]

from fio, client, podpiska, izd

where podpiska.fk_client = client.id_client and client.fk_fio = fio.id_fio and

podpiska.id_izd = izd.id_izd and podpiska.fk_lgota = 1

group by (fio.Fam)

order by COUNT(fio.Fam)

6. Вывести список участков с указанием количества доставок на них. Указать самый «активный» участок:

select uchastok.nomer as [Номер участка], COUNT (uchastok.nomer) as [Количество доставок]

from uchastok, uch_operation, dostavka

where dostavka.fk_pochtal = uch_operation.id_uch_operation and uch_operation.fk_uchastok = uchastok.id_uchastok

group by uchastok.nomer

order by COUNT (uchastok.nomer)

7. Вывести общую стоимость и общий вес для входящих и исходящих отправлений:

select SUM(post_pack.ves) as [Общий вес отправлений], SUM (post_pack.cena) as [Общая стоимость отправлений]

from post_pack, post_pack_operation

where post_pack_operation.id_pack_operation = post_pack.id_post_pack

8. Вывести всех работников почтового отделения – операторов и почтальонов:

select fio.Fam as Фамилия, fio.Im as Имя, fio.Otch as Отчество, sotrudnik.doljnost as Должность

from fio, sotrudnik

where sotrudnik.fk_fio = fio.id_fio

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

select vid_pack.vid, SUM (post_pack.cena)

from vid_pack, post_pack,post_pack_operation

where post_pack_operation.id_pack_operation = post_pack.id_post_pack and post_pack.fk_vid_pack = vid_pack.id_vid_pack

group by (vid_pack.vid)

10. Вывести издания, выходящие с указанным периодом и меньше указанной стоимости:

CREATE PROCEDURE [dbo].[zapros10]

@period int,

@cena int

AS

BEGIN

if (@period is not null) and (@cena is not null)

begin

SELECT name as Название, tip as Тип, indeks as [Подписной индекс], cena as [Цена за 1 ед.]

FROM izd

WHERE (period = @period) and (cena<@cena)

end

else

begin

if (@period is not null)

begin

SELECT name as Название, tip as Тип, indeks as [Подписной индекс], cena as [Цена за 1 ед.]

FROM izd

WHERE (period = @period)

end

else begin

SELECT name as Название, tip as Тип, indeks as [Подписной индекс], cena as [Цена за 1 ед.]

FROM izd

WHERE (cena<@cena)

End end

11. Вывести список почтальонов и отправлений с адресами, которые они должны доставить:

select fio.Fam, adres.ulica, adres.dom, adres.korpus, adres.kvartira, vid_pack.vid

from vid_pack, post_pack, post_pack_operation, adres, uchastok, uch_operation, sotrudnik, fio

where vid_pack.id_vid_pack = post_pack.fk_vid_pack and post_pack.id_post_pack = post_pack_operation.id_pack_operation and

post_pack_operation.fk_poluch_adres = adres.id_adres and adres.fk_uchastok is not null and

adres.fk_uchastok = uchastok.id_uchastok and uch_operation.fk_uchastok = uchastok.id_uchastok and

uch_operation.fk_sotrudnik = sotrudnik.id_sotrudnik and fio.id_fio = sotrudnik.fk_fio

12. Вывести список абонентских ящиков, у которых в следующем месяце (относительно текущего) истекает срок подписки:

CREATE PROCEDURE [dbo].[zapros12]

AS

BEGIN

if (12 = DATEPART(month, GETDATE()))

begin

SELECT abonent.nomer as [Номер а/я], ab_operation.data as [Время регистрации], ab_operation.srok as [Время окончания подписки на а/я]

FROM abonent, ab_operation

WHERE ab_operation.fk_abonent = abonent.id_abonent and

DATEPART(month, ab_operation.srok) = 1

end

else

begin

SELECT abonent.nomer as [Номер а/я], ab_operation.data as [Время регистрации], ab_operation.srok as [Время окончания подписки на а/я]

FROM abonent, ab_operation

WHERE ab_operation.fk_abonent = abonent.id_abonent and

DATEPART(month, GETDATE())+1 = DATEPART(month, ab_operation.srok)

End

Дата добавления: 2015-08-18