отчет. Отчет По дисциплине "Базы данных" Студент гр. Бис1902 Е. Р. Тимохина
 Скачать 1.44 Mb.
|
ЗаключениеВ процессе выполнения поставленных задач были освоены методы управления профессионально- ориентированной информационной системой, основных принципов организации БД информационных, работы с инструментальными средствами для реализация физической модели хранения информации, а также обеспечения целостности БД и защиты информации в них, методы и средства проектирования БД, особенности администрирования БД в локальных и глобальных сетях, требования к защите информации определенного типа. На основе проделанной работы был подготовлен отчет. Таким образом, в ходе изучения дисциплины «Базы данных продвинутый курс» были освоены навыки по проектированию реляционных моделей баз данных, по общим принципам построения и функциональным особенностям основных систем управления базами данных (СУБД), представленным на рынке ПО, построению информационных систем на основе архитектуры «клиент-сервер» с использованием систем управления базами данных, особенностям языка SQL. Приложение 1Содержание БД `mydb` -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ; USE `mydb` ; -- ----------------------------------------------------- -- Table `mydb`.`client` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`client` ( `id_client` INT NOT NULL auto_increment, `name` VARCHAR(100) NULL, PRIMARY KEY (`id_client`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`contracts` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`contracts` ( `id_contract` INT NOT NULL auto_increment, `number` VARCHAR(45) NULL, `start_date` DATE NULL, `special` VARCHAR(100) NULL, `fromClient_id` INT NOT NULL, `end_date` DATE NULL, PRIMARY KEY (`id_contract`, `fromClient_id`), CONSTRAINT `fk_contracts_client` FOREIGN KEY (`fromClient_id`) REFERENCES `mydb`.`client` (`id_client`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`staff` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`staff` ( `id_staff` INT NOT NULL, `name` VARCHAR(100) NULL, `total_all` INT NULL, `order_permonth`INT NULL, `done_permonth`INT NULL, `total_sum` INT NULL, PRIMARY KEY (`id_staff`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`Neworder` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`Neworder` ( `id_Neworder` INT NOT NULL auto_increment, `articule` VARCHAR(45) NULL, `start_date` DATE NULL, `prepayment` INT NULL, `summa` INT NULL, #сумма заказа `paidstatus` VARCHAR(45) NULL, #оплачен ли заказ `status` VARCHAR(45) NULL, `fromContracts_id` INT NOT NULL, `fromStaff_id` INT NOT NULL, `end_date` DATE NULL, PRIMARY KEY (`id_Neworder`, `fromContracts_id`, `fromStaff_id`), CONSTRAINT `fk_Neworder_contracts1` FOREIGN KEY (`fromContracts_id`) REFERENCES `mydb`.`contracts` (`id_contract`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Neworder_staff1` FOREIGN KEY (`fromStaff_id`) REFERENCES `mydb`.`staff` (`id_staff`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`goods` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`goods` ( `id_good` INT NOT NULL auto_increment, `name` VARCHAR(45) NULL, `starting_price` INT NULL, `stock_quantity` INT NULL, PRIMARY KEY (`id_good`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`drawings` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`drawings` ( `id_drawings` INT NOT NULL, `name` VARCHAR(45) NULL, `price` INT NULL, PRIMARY KEY (`id_drawings`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`goods_in_Neworder` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`goods_in_Neworder` ( `id_goodsinNeworder` INT NOT NULL auto_increment, `quantity` INT NULL, `old_price` INT NULL, `application_price` INT NULL, `fromDrawings_id` INT NOT NULL, `fromNeworder_id` INT NOT NULL, `fromGoods_id` INT NOT NULL, PRIMARY KEY (`id_goodsinNeworder`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`), CONSTRAINT `fk_goods_in_Neworder_drawings1` FOREIGN KEY (`fromDrawings_id`) REFERENCES `mydb`.`drawings` (`id_drawings`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_goods_in_Neworder_Neworder1` FOREIGN KEY (`fromNeworder_id`) REFERENCES `mydb`.`Neworder` (`id_Neworder`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_goods_in_Neworder_goods1` FOREIGN KEY (`fromGoods_id`) REFERENCES `mydb`.`goods` (`id_good`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`transport` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`transport` ( `id_transport` INT NOT NULL, `name` VARCHAR(100) NULL, PRIMARY KEY (`id_transport`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`delivery` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`delivery` ( `id_delivery` INT NOT NULL, `number` VARCHAR(45) NULL, `address` VARCHAR(100) NULL, `date_sended` DATE NULL, `date_received` DATE NULL, `fromTransport_id` INT NOT NULL, PRIMARY KEY (`id_delivery`, `fromTransport_id`), CONSTRAINT `fk_delivery_transport1` FOREIGN KEY (`fromTransport_id`) REFERENCES `mydb`.`transport` (`id_transport`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`delivery_list` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`delivery_list` ( `id_delivery_list` INT NOT NULL auto_increment, `number` VARCHAR(45) NULL, `fromDelivery_id` INT, `fromGoodsinNeworder_id` INT NOT NULL, PRIMARY KEY (`id_delivery_list`, `fromGoodsinNeworder_id`), CONSTRAINT `fk_delivery_list_delivery1` FOREIGN KEY (`fromDelivery_id`) REFERENCES `mydb`.`delivery` (`id_delivery`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_delivery_list_goods_in_Neworder1` FOREIGN KEY (`fromGoodsinNeworder_id`) REFERENCES `mydb`.`goods_in_Neworder` (`id_goodsinNeworder`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Data for table `mydb`.` client ` -- ----------------------------------------------------- START TRANSACTION; USE `mydb`; INSERT INTO `mydb`.`client` (`id_client`, `name`) VALUES (1, 'Иванов Иван'); INSERT INTO `mydb`.`client` (`id_client`, `name`) VALUES (2, 'Гонцов Бистров'); INSERT INTO `mydb`.`client` (`id_client`, `name`) VALUES (3, 'Успею Написатов'); INSERT INTO `mydb`.`client` (`id_client`, `name`) VALUES (4, 'ООО Отличный клиент'); COMMIT; -- ----------------------------------------------------- -- Data for table `mydb`.`contracts` -- ----------------------------------------------------- START TRANSACTION; USE `mydb`; INSERT INTO `mydb`.`contracts` (`id_contract`, `number`, `start_date`, `special`, `fromClient_id`, `end_date`) VALUES (1, 'А-001', '2022-03-02', NULL, 1, '2022-03-02'); INSERT INTO `mydb`.`contracts` (`id_contract`, `number`, `start_date`, `special`, `fromClient_id`, `end_date`) VALUES (2, 'B-021', '2022-03-02', NULL, 2, NULL); INSERT INTO `mydb`.`contracts` (`id_contract`, `number`, `start_date`, `special`, `fromClient_id`, `end_date`) VALUES (3, 'C-123', '2022-03-02', 'побыстрей', 3, NULL); INSERT INTO `mydb`.`contracts` (`id_contract`, `number`, `start_date`, `special`, `fromClient_id`, `end_date`) VALUES (4, 'А-022', '2022-03-03', NULL, 2, NULL); INSERT INTO `mydb`.`contracts` (`id_contract`, `number`, `start_date`, `special`, `fromClient_id`, `end_date`) VALUES (5, 'С-234', '2022-03-04', 'аккуратней', 1, '2022-03-08'); INSERT INTO `mydb`.`contracts` (`id_contract`, `number`, `start_date`, `special`, `fromClient_id`, `end_date`) VALUES (6, 'Р-001', '2022-03-03', NULL, 4, NULL); COMMIT; -- ----------------------------------------------------- -- Data for table `mydb`.`staff` -- ----------------------------------------------------- START TRANSACTION; USE `mydb`; INSERT INTO `mydb`.`staff` (`id_staff`, `name`) VALUES (1, 'Иванов иван'); INSERT INTO `mydb`.`staff` (`id_staff`, `name`) VALUES (2, 'Сроки Горелов'); INSERT INTO `mydb`.`staff` (`id_staff`, `name`) VALUES (3, 'Работать Задаром'); INSERT INTO `mydb`.`staff` (`id_staff`, `name`) VALUES (4, 'Сверхплана Сделов'); INSERT INTO `mydb`.`staff` (`id_staff`, `name`) VALUES (5, 'Лучшепотом Доделов'); INSERT INTO `mydb`.`staff` (`id_staff`, `name`) VALUES (6, 'Начальник Строгов'); COMMIT; -- ----------------------------------------------------- -- Data for table `mydb`.`Neworder` -- ----------------------------------------------------- START TRANSACTION; USE `mydb`; INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (1, 'АА-02', '2022-03-02', 0, 'готов', 1, 1, '2022-03-02'); INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (2, 'АА-123', '2022-03-03', 12000, NULL, 2, 4, NULL); INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (3, 'В-32', '2022-03-03', NULL, 'обрабатывается', 3, 2, NULL); INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (4, 'Ф-23', '2022-05-03', 2344, NULL, 4, 1, NULL); INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (5, 'АА-47', '2022-06-04', 233, 'Самовывоз', 5, 3, NULL); INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (6, 'АА-07', '2022-03-05', 0, NULL, 6, 3, NULL); INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (7, 'АА-08', '2022-07-05', 0, NULL, 6, 5, NULL); INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (8, 'АА-09', '2022-03-05', 0, NULL, 6, 3, NULL); INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (9, 'АO-09', '2022-04-25', 0, 'в процессе', 6, 3, '2022-04-28'); INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (10, 'АO-09', '2022-05-25', 100, 'завершен', 6, 1, '2022-05-28'); INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (11, 'АА-48', '2022-03-04', 233, 'Самовывоз', 5, 3, NULL); INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (12, 'АO-43', '2022-05-25', 1000, 'завершен', 6, 1, '2022-05-28'); INSERT INTO `mydb`.`Neworder` (`id_Neworder`, `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES (13, 'АO-13', '2022-05-25', 1000, 'завершен', 6, 1, '2022-05-28'); COMMIT; -- ----------------------------------------------------- -- Data for table `mydb`.`goods` -- ----------------------------------------------------- START TRANSACTION; USE `mydb`; INSERT INTO `mydb`.`goods` (`id_good`, `name`, `starting_price`,`stock_quantity`) VALUES (1, 'Отличный товар', 50000,100); INSERT INTO `mydb`.`goods` (`id_good`, `name`, `starting_price`,`stock_quantity`) VALUES (2, 'товар похуже', 4999,100); INSERT INTO `mydb`.`goods` (`id_good`, `name`, `starting_price`,`stock_quantity`) VALUES (3, 'плохой товар', 1,100); INSERT INTO `mydb`.`goods` (`id_good`, `name`, `starting_price`,`stock_quantity`) VALUES (4, 'Никогла не покупай', 50,100); INSERT INTO `mydb`.`goods` (`id_good`, `name`, `starting_price`,`stock_quantity`) VALUES (5, 'Сами не знаем зачем продаем', 0,100); COMMIT; -- ----------------------------------------------------- -- Data for table `mydb`.`drawings` -- ----------------------------------------------------- START TRANSACTION; USE `mydb`; INSERT INTO `mydb`.`drawings` (`id_drawings`, `name`, `price`) VALUES (1, 'Нанесли', 26555); INSERT INTO `mydb`.`drawings` (`id_drawings`, `name`, `price`) VALUES (2, 'нанесем', 2342); INSERT INTO `mydb`.`drawings` (`id_drawings`, `name`, `price`) VALUES (3, 'не смогут', 4353); COMMIT; -- ----------------------------------------------------- -- Data for table `mydb`.`goods_in_Neworder` -- ----------------------------------------------------- START TRANSACTION; USE `mydb`; INSERT INTO `mydb`.`goods_in_Neworder` (`id_goodsinNeworder`, `quantity`, `old_price`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) VALUES (1, 3, 350, 4530, 1, 1, 1); INSERT INTO `mydb`.`goods_in_Neworder` (`id_goodsinNeworder`, `quantity`, `old_price`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) VALUES (2, 4, 123, 2354, 3, 2, 2); INSERT INTO `mydb`.`goods_in_Neworder` (`id_goodsinNeworder`, `quantity`, `old_price`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) VALUES (3, 20, 350, 4560, 2, 4, 1); INSERT INTO `mydb`.`goods_in_Neworder` (`id_goodsinNeworder`, `quantity`, `old_price`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) VALUES (4, 1, 400, 5530, 2, 3, 3); INSERT INTO `mydb`.`goods_in_Neworder` (`id_goodsinNeworder`, `quantity`, `old_price`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) VALUES (5, 5, 350, 4520, 1, 5, 1); INSERT INTO `mydb`.`goods_in_Neworder` (`id_goodsinNeworder`, `quantity`, `old_price`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) VALUES (6, 2, 450, 6640, 1, 5, 4); INSERT INTO `mydb`.`goods_in_Neworder` (`id_goodsinNeworder`, `quantity`, `old_price`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) VALUES (7, 1, 50, 625, 1, 5, 5); INSERT INTO `mydb`.`goods_in_Neworder` (`id_goodsinNeworder`, `quantity`, `old_price`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) VALUES (8, 1, 50, 625, 1, 11, 5); COMMIT; -- ----------------------------------------------------- -- Data for table `mydb`.`transport` -- ----------------------------------------------------- START TRANSACTION; USE `mydb`; INSERT INTO `mydb`.`transport` (`id_transport`, `name`) VALUES (1, 'Бистрая Машина'); INSERT INTO `mydb`.`transport` (`id_transport`, `name`) VALUES (2, 'Небыстрой Машина'); INSERT INTO `mydb`.`transport` (`id_transport`, `name`) VALUES (3, 'самолет'); COMMIT; -- ----------------------------------------------------- -- Data for table `mydb`.`delivery` -- ----------------------------------------------------- START TRANSACTION; USE `mydb`; INSERT INTO `mydb`.`delivery` (`id_delivery`, `number`, `address`, `date_sended`, `date_received`, `fromTransport_id`) VALUES (1, 'ОО-12', 'к дому', '2022-03-02', '2022-03-02', 1); INSERT INTO `mydb`.`delivery` (`id_delivery`, `number`, `address`, `date_sended`, `date_received`, `fromTransport_id`) VALUES (2, 'Ф-43', 'сюда', NULL, NULL, 2); INSERT INTO `mydb`.`delivery` (`id_delivery`, `number`, `address`, `date_sended`, `date_received`, `fromTransport_id`) VALUES (3, 'А-22', 'магазин', NULL, NULL, 2); INSERT INTO `mydb`.`delivery` (`id_delivery`, `number`, `address`, `date_sended`, `date_received`, `fromTransport_id`) VALUES (4, 'Р-12', 'улица', '2022-03-06', '2022-03-08', 3); INSERT INTO `mydb`.`delivery` (`id_delivery`, `number`, `address`, `date_sended`, `date_received`, `fromTransport_id`) VALUES (5, 'П-54', 'вгуэс', '2022-03-07', NULL, 2); INSERT INTO `mydb`.`delivery` (`id_delivery`, `number`, `address`, `date_sended`, `date_received`, `fromTransport_id`) VALUES (6, 'П-55', 'Озон', '2022-03-07', NULL, 2); INSERT INTO `mydb`.`delivery` (`id_delivery`, `number`, `address`, `date_sended`, `date_received`, `fromTransport_id`) VALUES (7, 'П-58', 'голубиная падь', '2022-03-07', NULL, 2); COMMIT; -- ----------------------------------------------------- -- Data for table `mydb`.`delivery_list` -- ----------------------------------------------------- START TRANSACTION; USE `mydb`; INSERT INTO `mydb`.`delivery_list` (`id_delivery_list`, `number`, `fromDelivery_id`, `fromGoodsinNeworder_id`) VALUES (1, 'В-21', 1, 1); INSERT INTO `mydb`.`delivery_list` (`id_delivery_list`, `number`, `fromDelivery_id`, `fromGoodsinNeworder_id`) VALUES (2, 'А-23', 2, 2); INSERT INTO `mydb`.`delivery_list` (`id_delivery_list`, `number`, `fromDelivery_id`, `fromGoodsinNeworder_id`) VALUES (3, 'И-54', 3, 3); INSERT INTO `mydb`.`delivery_list` (`id_delivery_list`, `number`, `fromDelivery_id`, `fromGoodsinNeworder_id`) VALUES (4, 'Р-55', 4, 4); INSERT INTO `mydb`.`delivery_list` (`id_delivery_list`, `number`, `fromDelivery_id`, `fromGoodsinNeworder_id`) VALUES (5, 'М-01', 5, 5); INSERT INTO `mydb`.`delivery_list` (`id_delivery_list`, `number`, `fromDelivery_id`, `fromGoodsinNeworder_id`) VALUES (6, 'O-01', NULL, 5); COMMIT; |