Создание базы данных, описание структуры и ключей.

КУРСОВАЯ РАБОТА

Междисциплинарный курс Технология разработки и защиты баз данных

 

 

Тема работы Учет оказанных услуг компанией сотовой связи.

 

 

Студент Гусев И.М. Группа ПДО 42-13

(фамилия, инициалы)

 

 

Преподаватель Гуськов Глеб Юрьевич

(фамилия, инициалы)

 

Дата защиты ______________________ Оценка _____________________

 

 

Подпись _____________________________

 

Ульяновск

2017г.

Постановка задачи

Вариант 24.

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

Модель данных в 3НФ

ER-модель данных соответствующей предметной области 3НФ вUML (диаграмма классов: UML diagrams).

Создание базы данных, описание структуры и ключей.

База данных состоит из 5 таблиц, связанных между собой:

1.Таблица «Клиент» хранит в себе информацию о Клиенте и содержит четыре атрибута:

§ паспортныеДанны– номер паспорта клиента является первичным ключом (тип integer(11));

§ имя – имя клиента (тип VARCHAR(11));

§ фамилия – фамилия клиента(тип VARCHAR(11));

§ отчество – отчество клиента(тип VARCHAR(11));

2.Таблица «Договор» хранит в себе информацию о клиенте, номер договора и дату подключения и содержит пять атрибутов:

§ паспортныеДанны– номер паспорта клиента (тип integer(11));

§ номерДоговора– номер договора клиента является первичным ключом (тип integer(11));

§ дата–дата подключения клиента(тип date);

§ клиентИмя– имя клиента(тип VARCHAR (11));

§ клиентФамилия – фамилия клиента(тип VARCHAR (11));

3.Таблица «Расчет» хранит в себе информацию о том сколько минут, смс, мб у клиента, даты начала и конца расчета и стоимость расчета и содержит семь атрибутов:

§ номерДоговора– номер договора клиента (тип integer(10));

§ колвоМинут–количество минут у клиента(тип CHAR(11));

§ колвоСмс– количество смс у клиента(тип CHAR(11));

§ началоРасчета – дата начала расчета является первичным ключом (тип date);

§ колвоМб –количество мб у клиента(тип CHAR(11));

§ сумма – стоимость расчета (тип CHAR(11))

§ конецРасчета – дата конца расчета (тип date);

4.Таблица «Тариф» хранит в себе информацию название тарифа количество звонков, смс, мб интернета и название услуги и содержит шесть атрибутов:

§ номерДоговора– номер договора клиента (тип integer(10));

§ названиеТарифов– название тарифов является первичным ключом (тип VARCHAR(11));

§ звонки– количество звонков у клиента (тип integer(11));

§ смс– количество смс у клиента (тип integer(11));

§ интернет – количество мб интернета у клиента (тип integer(11));

§ названиеУслуги– varchar(100) NULL);

5.Таблица «Услуга» хранит в себе информацию и содержит четыре атрибута:

§ название– название услуги является первичным ключом (тип VARCHAR(10));

§ стоимость– стоимость услуги (тип integer(11));

§ номерДоговора– номер договора клиента (тип integer(11));

Создание таблиц:

CREATE TABLE Клиент (

паспортныеДанны int(11) NOT NULL Primary key,

имя VARCHAR(11) NOT NULL,

фамилия VARCHAR(11) NULL,

отчество VARCHAR(11) NULL);

CREATE TABLE Договор (

паспортныеДанны int(10) NOT NULL,

номерДоговора int(11) NOT NULL Primary key,

дата date NULL,

клиентИмя VARCHAR(11) NULL,

клиентФамилия VARCHAR(11) NULL);

CREATE TABLE Расчет (

номерДоговора int(10) NOT NULL,

колвоМинут CHAR(11) NULL,

колвоСмс CHAR(11) NULL,

началоРасчета date NULL Primary key,

колвоМб CHAR(11) NULL,

сумма CHAR(11) NULL,

конецРасчета date NULL);

CREATE TABLE тариф (

номерДоговора int(10) NOT NULL,

названиеТарифов VARCHAR(11) NULL Primary key,

звонки int(11) NULL,

смс int(11) NULL,

интернет int(11) NULL,

названиеУслуги varchar(100) NULL);

CREATE TABLE услуга (

название VARCHAR(10) NOT NULL Primary key,

стоимость int(11) NULL,

номерДоговора int(10)NULL);

ALTER TABLE `договор` ADD FOREIGN KEY (`паспортныеДанны`) REFERENCES `клиент` (`паспортныеДанны`) ON DELETE cascade;

ALTER TABLE `расчет` ADD FOREIGN KEY (`номерДоговора`) REFERENCES `договор` (`номерДоговора`) ON DELETE cascade;

ALTER TABLE `тариф` ADD FOREIGN KEY (`номерДоговора`) REFERENCES `договор` (`номерДоговора`) ON DELETE cascade;

ALTER TABLE `услуга` ADD FOREIGN KEY (`номерДоговора`) REFERENCES `договор` (`номерДоговора`) ON DELETE cascade;

ALTER TABLE `тариф` ADD FOREIGN KEY (`названиеУслуги`) REFERENCES `услуга` (`название`) ON DELETE cascade;

Заполнение таблиц.

Для заполнения таблиц созданы последовательности и тестовые наборы значений. Последовательность – это объект, который генерирует ряд последовательных уникальных чисел. Тем самым, при добавлении новой записи, ей будет присвоен свой уникальный номер, т.е. следующее значение в последовательности. В нашем случае мы использовали генерацию последовательностей при заполнении данными пяти таблиц (заполнение первичных ключей таблиц): «Клиент», «Договор», «Расчет», «Тариф», «Услуга». Первыми заполняются таблицы «Клиент», «Договор», «Расчет», а затем «Услуга» и в последний момент «Тариф».

INSERT INTO `клиент` (`паспортныеДанны`, `имя`, `фамилия`, `отчество`) VALUES ('12345671', 'Жора', 'Андреев', 'Григорьевич');

INSERT INTO `клиент` (`паспортныеДанны`, `имя`, `фамилия`, `отчество`) VALUES ('13425683', 'Андрей', 'Леонтьев', 'Валентинови');

INSERT INTO `клиент` (`паспортныеДанны`, `имя`, `фамилия`, `отчество`) VALUES ('12345602', 'Антон', 'Леонов', 'Герасимович');

INSERT INTO `клиент` (`паспортныеДанны`, `имя`, `фамилия`, `отчество`) VALUES ('12345605', 'Иван', 'Сидаров', 'Альбертович');

INSERT INTO `клиент` (`паспортныеДанны`, `имя`, `фамилия`, `отчество`) VALUES ('12345608', 'Вася', 'Смирнова', 'Борисович');

INSERT INTO `клиент` (`паспортныеДанны`, `имя`, `фамилия`, `отчество`) VALUES ('12345622', 'Лена', 'Петрова', 'Александровна');

 

INSERT INTO `договор` (`паспортныеДанны`, `номерДоговора`, `дата`, `клиентИмя`, `клиентФамилия`) VALUES ('12345679', '43659486', '2017-01-19', 'Валенти', 'Валентинов');

INSERT INTO `договор` (`паспортныеДанны`, `номерДоговора`, `дата`, `клиентИмя`, `клиентФамилия`) VALUES ('12345670', '54456539', '2017-01-03', 'Илья', 'Гусев');

INSERT INTO `договор` (`паспортныеДанны`, `номерДоговора`, `дата`, `клиентИмя`, `клиентФамилия`) VALUES ('12345678', '65743597', '2017-01-18', 'Вася', 'Васькин');

INSERT INTO `договор` (`паспортныеДанны`, `номерДоговора`, `дата`, `клиентИмя`, `клиентФамилия`) VALUES ('75632746', '86047697', '2017-01-08', 'Алла', 'Капустина');

INSERT INTO `договор` (`паспортныеДанны`, `номерДоговора`, `дата`, `клиентИмя`, `клиентФамилия`) VALUES ('12345671', '87654576', '2017-01-11', 'Жора', 'Андреев');

INSERT INTO `договор` (`паспортныеДанны`, `номерДоговора`, `дата`, `клиентИмя`, `клиентФамилия`) VALUES ('13425683', '98067594', '2017-01-26', 'Андрей', 'Леонтьев');

INSERT INTO `договор` (`паспортныеДанны`, `номерДоговора`, `дата`, `клиентИмя`, `клиентФамилия`) VALUES ('12345602', '43659757', '2017-01-20', 'Антон', 'Леонов');

INSERT INTO `договор` (`паспортныеДанны`, `номерДоговора`, `дата`, `клиентИмя`, `клиентФамилия`) VALUES ('12345605', '43659364', '2017-01-22', 'Иван', 'Сидаров');

INSERT INTO `договор` (`паспортныеДанны`, `номерДоговора`, `дата`, `клиентИмя`, `клиентФамилия`) VALUES ('12345608', '43659686', '2017-01-15', 'Вася', 'Смирнова');

INSERT INTO `договор` (`паспортныеДанны`, `номерДоговора`, `дата`, `клиентИмя`, `клиентФамилия`) VALUES ('12345622', '43659976', '2017-01-01', 'Лена', 'Петрова');

 

INSERT INTO `расчет` (`номерДоговора`, `колвоМинут`, `колвоСмс`, `началоРасчета`, `колвоМб`, `сумма`, `конецРасчета`) VALUES ('43659486', '100', '100', '2017-01-01', '100', '100', '2017-02-01');

INSERT INTO `расчет` (`номерДоговора`, `колвоМинут`, `колвоСмс`, `началоРасчета`, `колвоМб`, `сумма`, `конецРасчета`) VALUES ('54456539', '200', '200', '2017-01-02', '200', '200', '2017-02-02');

INSERT INTO `расчет` (`номерДоговора`, `колвоМинут`, `колвоСмс`, `началоРасчета`, `колвоМб`, `сумма`, `конецРасчета`) VALUES ('65743597', '300', '300', '2017-01-03', '300', '300', '2017-02-03');

INSERT INTO `расчет` (`номерДоговора`, `колвоМинут`, `колвоСмс`, `началоРасчета`, `колвоМб`, `сумма`, `конецРасчета`) VALUES ('86047697', '400', '400', '2017-01-04', '400', '400', '2017-02-04');

INSERT INTO `расчет` (`номерДоговора`, `колвоМинут`, `колвоСмс`, `началоРасчета`, `колвоМб`, `сумма`, `конецРасчета`) VALUES ('87654576', '500', '500', '2017-01-05', '500', '500', '2017-02-05');

INSERT INTO `расчет` (`номерДоговора`, `колвоМинут`, `колвоСмс`, `началоРасчета`, `колвоМб`, `сумма`, `конецРасчета`) VALUES ('98067594', '600', '600', '2017-01-06', '600', '600', '2017-02-06');

INSERT INTO `расчет` (`номерДоговора`, `колвоМинут`, `колвоСмс`, `началоРасчета`, `колвоМб`, `сумма`, `конецРасчета`) VALUES ('43659757', '700', '700', '2017-01-07', '700', '700', '2017-02-07');

INSERT INTO `расчет` (`номерДоговора`, `колвоМинут`, `колвоСмс`, `началоРасчета`, `колвоМб`, `сумма`, `конецРасчета`) VALUES ('43659364', '800', '800', '2017-01-08', '800', '800', '2017-02-08');

INSERT INTO `расчет` (`номерДоговора`, `колвоМинут`, `колвоСмс`, `началоРасчета`, `колвоМб`, `сумма`, `конецРасчета`) VALUES ('43659686', '900', '900', '2017-01-08', '900', '900', '2017-02-09');

INSERT INTO `расчет` (`номерДоговора`, `колвоМинут`, `колвоСмс`, `началоРасчета`, `колвоМб`, `сумма`, `конецРасчета`) VALUES ('43659976', '1000', '1000', '2017-01-10', '1000', '1000', '2017-02-10');

 

INSERT INTO `услуга` (`название`, `стоимость`, `номерДоговора`) VALUES ('S.O.S', '10', '43659486');

INSERT INTO `услуга` (`название`, `стоимость`, `номерДоговора`) VALUES ('Выделенный IP', '100', '54456539');

INSERT INTO `услуга` (`название`, `стоимость`, `номерДоговора`) VALUES ('Роутер', '1499', '65743597');

INSERT INTO `услуга` (`название`, `стоимость`, `номерДоговора`) VALUES ('ТВ', '500', '86047697');

INSERT INTO `услуга` (`название`, `стоимость`, `номерДоговора`) VALUES ('Роутер', '200', '87654576');

INSERT INTO `услуга` (`название`, `стоимость`, `номерДоговора`) VALUES ('вызавМастера', '0', '98067594');

INSERT INTO `услуга` (`название`, `стоимость`, `номерДоговора`) VALUES ('Телафон', '100', '43659757');

INSERT INTO `услуга` (`название`, `стоимость`, `номерДоговора`) VALUES ('самМодуль', '150', '43659364');

INSERT INTO `услуга` (`название`, `стоимость`, `номерДоговора`) VALUES ('Утилазация', '0', '43659686');

INSERT INTO `услуга` (`название`, `стоимость`, `номерДоговора`) VALUES ('Подарок', '0', '43659976');

 

INSERT INTO `тариф` (`номерДоговора`, `названиеТарифов`, `звонки`, `смс`, `интернет`) VALUES ('43659486', 'L', '100', '100', '100');

INSERT INTO `тариф` (`номерДоговора`, `названиеТарифов`, `звонки`, `смс`, `интернет`) VALUES ('54456539', 'M', '200', '200', '200');

INSERT INTO `тариф` (`номерДоговора`, `названиеТарифов`, `звонки`, `смс`, `интернет`) VALUES ('65743597', 'S', '300', '300', '300');

INSERT INTO `тариф` (`номерДоговора`, `названиеТарифов`, `звонки`, `смс`, `интернет`) VALUES ('86047697', 'X', '400', '400', '400');

INSERT INTO `тариф` (`номерДоговора`, `названиеТарифов`, `звонки`, `смс`, `интернет`) VALUES ('87654576', 'XL', '500', '500', '500');

INSERT INTO `тариф` (`номерДоговора`, `названиеТарифов`, `звонки`, `смс`, `интернет`) VALUES ('98067594', '2L', '600', '600', '600');

INSERT INTO `тариф` (`номерДоговора`, `названиеТарифов`, `звонки`, `смс`, `интернет`) VALUES ('43659757', '3L', '700', '700', '700');

INSERT INTO `тариф` (`номерДоговора`, `названиеТарифов`, `звонки`, `смс`, `интернет`) VALUES ('43659364', '4L', '800', '800', '800');

INSERT INTO `тариф` (`номерДоговора`, `названиеТарифов`, `звонки`, `смс`, `интернет`) VALUES ('43659686', '5L', '900', '900', '900');

INSERT INTO `тариф` (`номерДоговора`, `названиеТарифов`, `звонки`, `смс`, `интернет`) VALUES ('43659976', '6L', '1000', '1000', '1000');

Результат заполнения таблиц

Примеры результатов выборки данных:

Первый запрос проводит выборку значений таблицы «расчет» по шаблону, относящемуся к полю «колвоМиннут», «колвоСмс», «колвоМб», и по условию, относящемуся к полю «сумма» (выбираются те строки, значение атрибута «сумма», у которых сумма - между величинами 400 и 900).

Select `колвоМинут`, `колвоСмс`, `колвоМб`, `сумма`

From `расчет`

Where `сумма` >=400 AND `сумма` <=900;

Второй запрос проводит выборку значений атрибутов «паспортныеДанны», «номерДоговора», «дата» таблицы «договор» и, сортирует по убыванию поля «дата»:

SELECT `паспортныеДанны`, `номерДоговора`, `дата`

FROM `договор`

WHERE 1

ORDER BY паспортныеДанны ASC, дата DESC;

 

Третий запрос проводит выборку и группировку значений таблицы «член семьи» по полю «зарплата», и выводит сумму (зарплату) по возрастанию в семье, если зарплата одинаковая, то пишется количество членов семьи, у которых заработная плата дублируется.

SELECT `клиентИмя`, `клиентФамилия`, COUNT(*) FROM `договор` WHERE 1

GROUP BY `клиентИмя`;

Четвертый запрос осуществляет выборку значений атрибутов «клиентИмя», «клиентФамилия», «колвоМинут», «колвоСмс», «колвоМб», «сумма» посредством внутреннего соединения таблиц «договора» и «расчета» по соответствующим условиям выборки сортируется по значению:

 

select u.`клиентИмя`, `клиентФамилия`, a.`колвоМинут`, `колвоСмс`, `колвоМб`, `сумма`

from договор u

inner join расчет a ON a.`номерДоговора` = u.`номерДоговора`

Триггеры

Триггер – именованный блок PL/SQL, который в отличие от процедур и функций не может быть вызван из другой процедуры и не имеет параметров при вызове. Триггер срабатывает при операциях INSERT, UPDATE и DELETE, до, после или вместо их выполнения.

Триггер DELIMITER если удалить клиента из таблички «Клиент» то из таблички «Договор» клиент автоматически удалится:

DELIMITER //

CREATE TRIGGER `delete_trigger` BEFORE DELETE ON `договор`

FOR EACH ROW BEGIN

DELETE FROM `клиент` WHERE `паспартныеДанны`=OLD.`номерДоговора`;

END