Предметная область ИС: Телефонная станция

selectcustomer.Name 'Названиеклиента', customer.jur_status 'юр.лицо', customer.INN 'инн', customer.Address 'Адрес', concat(contact_person.first_name, ' ', contact_person.middle_name, ' ', contact_person.last_name ) 'Контактоноелицо',contact_person.position 'Должность'from customer left join contact_personon customer.id = contact_person.customer_idwhere contact_person.id is null;Здесь используется комбинация левого соединения и условия isnull, накладываемого на поля «правой» таблицы, благодаря чему в результат попадают только записи из customer, у которых нет связанных записей в таблице contact_person.Рисунок 18. Значение запроса №14. Следующий запрос определяет значения инн и названия клиентов с одинаковыми ИНН и имеющими одинаковый статус.selectcustomer1.inn, customer1.name from customer as customer1cross join customer using (jur_status, INN) where customer1.id != customer.id;Рисунок 19. Значение запроса №15.Здесь используется кросс-соединение таблицы с собой; такие соединения называются рекурсивными. Соединение выполняется по паре полей при помощи конструкции USING.Следующий запрос определяет для каждого клиента количество контактных лиц.selectcustomer.Name 'Название клиента', count(contact_person.id) 'количествоконт.лиц'from customerleft join contact_personon customer.id = contact_person.customer_idgroupbycustomer.NameЗдесь используется сочетание левого соединения, группировки по полю из «левой» таблицы, и вычисление агрегатной функции по полю из «правой» таблицы.Рисунок 20. Значение запроса №16.Таким образом, построены запросы на языке SQL согласно заданию Курсовой работы.ЗаключениеВ курсовой работе проведено научно-прикладное исследование, целью которой является создание базы данных для хранение информации предприятия, оказывающего услуги телефонной связи.В ходе выполнения курсовой работы были выполнен анализ предметной области, связанной с телефонией, на основе которой были разработаны даталогическая и логическая схемы базы данных, представленной в виде диаграммы «Сущность-связь». На основе диаграммы был создан набор команд на языке SQL, которые создают таблицы в целевой базе данных MySQL. Созданная база данных была заполнена тестовыми данными.Далее для созданной базы данных были созданы запрос на языке SQL, которые используют средства для селекции, фильтрации данных, группировки, и используют выборки из нескольких таблиц при помощи реляционной операции соединения.Для построения диаграммы и разработки запросов была использована система баз данных MySQL и инструментальная среда разработки MySQLWorkBanch.Результатом выполнения работы является разработанная база данных, которая может быть использована на производстве. Цели курсовой работы выполнены.Используемые источникиДмитрий Осипов: Технологии проектирования баз данных// Осипов Д. - М., ДМК-Пресс, 2019. – 498 с.Материалы по системе MySQL// Сайтhttps://dev.mysql.com/doc/. (Дата обращения 20.05.2022 г.).МартишинC., Симонов В., Храпченко М.: Проектирование и реализация баз данных в СУБД MySQL с использованием MySQLWorkbench// Мартишин Сергей, Симонов Владимир, Храпченко Марина. –M., изд. Форум. – 2019 г. - 160 с.ГольцманВиктор: MySQL 5.0. Библиотека программиста// Гольцман Виктор. – СпБ. изд. Питер. – 2010 г. – 256 с.Шустова, Тараканов: Базы данных. Учебник//Шустова Лариса, Тараканов Олег. –М., изд. ИНФРА-М. – 2018 г. – 304 с.ПриложениеСкрипт для создания схемы базы данных на языке MySQL-- MySQLWorkbench Forward EngineeringSET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';-- ------------------------------------------------------- Schema telephony-- ------------------------------------------------------- ------------------------------------------------------- Schema telephony-- -----------------------------------------------------CREATE SCHEMA IF NOT EXISTS `telephony` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;USE `telephony` ;-- ------------------------------------------------------- Table `telephony`.`customer`-- -----------------------------------------------------DROP TABLE IF EXISTS `telephony`.`customer` ;CREATE TABLE IF NOT EXISTS `telephony`.`customer` ( `id` INT NOT NULL AUTO_INCREMENT COMMENT 'Суррогатныйпервичныйключ', `jur_status` VARCHAR(2) NULL DEFAULT '1' COMMENT 'статусклиента (физ.лицо (1) илиюр.лицо (2))', `INN` VARCHAR(12) NULL DEFAULT NULL, `Name` VARCHAR(500) NOT NULL COMMENT 'Имяилиназваниеклиента', `Address` VARCHAR(1000) NOT NULL COMMENT 'Адрес', `doc` VARCHAR(45) NULL DEFAULT NULL, `id_type` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Виддокумента', `id_number` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Номердокумента', `reg_number` VARCHAR(50) NOT NULL COMMENT 'регистрационныйномерклиента', `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE INDEX `Name_UNIQUE` (`Name` ASC) VISIBLE)ENGINE = InnoDBAUTO_INCREMENT = 3DEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ciCOMMENT = 'Клиентуслуг';-- ------------------------------------------------------- Table `telephony`.`simcard`-- -----------------------------------------------------DROP TABLE IF EXISTS `telephony`.`simcard` ;CREATE TABLE IF NOT EXISTS `telephony`.`simcard` ( `id` INT NOT NULL AUTO_INCREMENT, `customer_id` INT NOT NULL, `number` VARCHAR(10) NOT NULL, `status` INT NULL DEFAULT '1', PRIMARY KEY (`id`), INDEX `fk_SIMCard_Customer_idx` (`customer_id` ASC) VISIBLE, CONSTRAINT `fk_SIMCard_Customer` FOREIGN KEY (`customer_id`) REFERENCES `telephony`.`customer` (`id`))ENGINE = InnoDBDEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ciCOMMENT = 'Номеркарты';-- ------------------------------------------------------- Table `telephony`.`agreement`-- -----------------------------------------------------DROP TABLE IF EXISTS `telephony`.`agreement` ;CREATE TABLE IF NOT EXISTS `telephony`.`agreement` ( `id` INT NOT NULL AUTO_INCREMENT, `date_start` DATE NOT NULL COMMENT 'датаначаладействиядоговора', `date_end` DATE NOT NULL COMMENT 'датаокончаниядействиядоговора', `reg_number` VARCHAR(100) NOT NULL COMMENT 'номердоговора', `simcard_id` INT NOT NULL, PRIMARY KEY (`id`), INDEX `fk_Agreement_SIMCard1_idx` (`simcard_id` ASC) VISIBLE, CONSTRAINT `fk_Agreement_SIMCard1` FOREIGN KEY (`simcard_id`) REFERENCES `telephony`.`simcard` (`id`))ENGINE = InnoDBDEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ciCOMMENT = 'Договор об оказании услуг';-- ------------------------------------------------------- Table `telephony`.`service`-- -----------------------------------------------------DROP TABLE IF EXISTS `telephony`.`service` ;CREATE TABLE IF NOT EXISTS `telephony`.`service` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(200) NOT NULL COMMENT 'названиеуслуги', `reg_number` VARCHAR(20) NOT NULL, `status` INT NOT NULL COMMENT 'статусуслуги', `date_start` DATETIME NOT NULL COMMENT 'началопредоставлениеуслуги', `date_end` DATETIME NOT NULL COMMENT 'окончаниепредоставленияуслуги', `price_per_unit` DECIMAL(10,0) NOT NULL COMMENT 'ценазаединицу', `unit` VARCHAR(100) NULL DEFAULT NULL COMMENT 'названиеединицы', PRIMARY KEY (`id`))ENGINE = InnoDBDEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ciCOMMENT = 'Услугителефонии';-- ------------------------------------------------------- Table `telephony`.`applied_service`-- -----------------------------------------------------DROP TABLE IF EXISTS `telephony`.`applied_service` ;CREATE TABLE IF NOT EXISTS `telephony`.`applied_service` ( `id` INT NOT NULL, `agreement_id` INT NOT NULL, `service_id` INT NOT NULL, PRIMARY KEY (`id`), INDEX `fk_Service_Agreement1_idx` (`agreement_id` ASC) VISIBLE, INDEX `fk_Applyed_Service_Service1_idx` (`service_id` ASC) VISIBLE, CONSTRAINT `fk_Applyed_Service_Service1` FOREIGN KEY (`service_id`) REFERENCES `telephony`.`service` (`id`), CONSTRAINT `fk_Service_Agreement1` FOREIGN KEY (`agreement_id`) REFERENCES `telephony`.`agreement` (`id`))ENGINE = InnoDBDEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ciCOMMENT = 'Услуги, привязанные к карте';-- ------------------------------------------------------- Table `telephony`.`applyed_service_status`-- -----------------------------------------------------DROP TABLE IF EXISTS `telephony`.`applyed_service_status` ;CREATE TABLE IF NOT EXISTS `telephony`.`applyed_service_status` ( `id` INT NOT NULL AUTO_INCREMENT, `date_start` DATETIME NOT NULL COMMENT 'началопериодаоказанияуслуги', `date_end` DATETIME NOT NULL COMMENT 'конецпериодаоказанияуслуги', `status` INT NOT NULL DEFAULT '1' COMMENT 'Статусуслуги (включенаилиотключена)', `reason` VARCHAR(100) NOT NULL COMMENT 'Причинаизменениястатуса', `applyed_service_id` INT NOT NULL, PRIMARY KEY (`id`), INDEX `fk_Applyed_Service_Status_Applyed_Service1_idx` (`applyed_service_id` ASC) VISIBLE, CONSTRAINT `fk_Applyed_Service_Status_Applyed_Service1` FOREIGN KEY (`applyed_service_id`) REFERENCES `telephony`.`applied_service` (`id`))ENGINE = InnoDBDEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ciCOMMENT = 'Статусыуслуги';-- ------------------------------------------------------- Table `telephony`.`contact_person`-- -----------------------------------------------------DROP TABLE IF EXISTS `telephony`.`contact_person` ;CREATE TABLE IF NOT EXISTS `telephony`.`contact_person` ( `id` INT NOT NULL AUTO_INCREMENT, `customer_id` INT NOT NULL, `first_name` VARCHAR(50) NOT NULL, `middle_name` VARCHAR(50) NOT NULL, `last_name` VARCHAR(50) NOT NULL, `phone_number` VARCHAR(12) NULL DEFAULT NULL, `email` VARCHAR(50) NULL DEFAULT NULL, `position` VARCHAR(100) NOT NULL, PRIMARY KEY (`id`), INDEX `fk_Contact_Person_Customer1_idx` (`customer_id` ASC) VISIBLE, CONSTRAINT `fk_Contact_Person_Customer1` FOREIGN KEY (`customer_id`) REFERENCES `telephony`.`customer` (`id`))ENGINE = InnoDBDEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ci;-- ------------------------------------------------------- Table `telephony`.`service_call`-- -----------------------------------------------------DROP TABLE IF EXISTS `telephony`.`service_call` ;CREATE TABLE IF NOT EXISTS `telephony`.`service_call` ( `id` INT NOT NULL AUTO_INCREMENT, `applyed_service_id` INT NOT NULL, `date_start` DATETIME NOT NULL COMMENT 'началоинтервалазвонка', `date_end` DATETIME NOT NULL COMMENT 'конецинтервалазвонка', `details` VARCHAR(100) NULL DEFAULT NULL COMMENT 'подробности', PRIMARY KEY (`id`), INDEX `fk_ServiceCall_ApplyedService1_idx` (`applyed_service_id` ASC) VISIBLE, CONSTRAINT `fk_ServiceCall_ApplyedService1` FOREIGN KEY (`applyed_service_id`) REFERENCES `telephony`.`applied_service` (`id`))ENGINE = InnoDBDEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ciCOMMENT = 'Фактыиспользованияуслугами';SET SQL_MODE=@OLD_SQL_MODE;SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;