Создание базы данных, описание структуры и ключей.
КУРСОВАЯ РАБОТА
Междисциплинарный курс Технология разработки и защиты баз данных
Тема работы Учет оказанных услуг компанией сотовой связи.
Студент Гусев И.М. Группа ПДО 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