«Разработка реляционной базы данных»

Для каждой группы пользователей базы данных «Библиотека» опишем права доступа к каждой таблице:– администратор БД должен иметь доступ ко всем данным, может изменять структурубазы данных и связи между таблицами. Администратор может устанавливать права доступа для всехостальных групп пользователей;– библиотекарь-каталогизатордолжен иметь доступ по записи к таблицам Сopy, book_authors, Books, Storage, Authors, Publishers, Subject и доступ по чтению к таблицам Moving;– библиотекарьдолжен иметь доступ по записи к таблицам Moving, Readers и доступ по чтению к таблицам Сopy, book_authors, Books, Storage, Authors, Publishers, Subject;– читательдолжениметьдоступпочтениюктаблицамBooks, Subject, Publishers, Authors, book_authors, MovingиReaders (только к данным по своей учетной записи.3. Физическое проектирование БД3.1. Создание таблиц в базе данныхФизическое проектирование БД в MySQLпредставляет собой создание набора SQL-запросов (скрипта), которые необходимых для формирования всех таблиц базы данных. При помощи команд языка SQL в MySQLможно создать таблицы, ввести данные в таблицы и, конечно, получить данные из таблиц по интересующим критериям (условиям).Для проектируемой базы данных «Библиотека» создание таблиц будет выполняться следующими SQL-запросами:CREATETABLESubject (id_subject INTPRIMARY KEY AUTO_INCREMENT,nm_subjectVARCHAR(150) NOT NULL);CREATE TABLE Publishers (id_Publisher INTPRIMARY KEY AUTO_INCREMENT,nm_Publisher VARCHAR(40) NOT NULL,city_Publisher VARCHAR(20)NOT NULL);CREATE TABLE Authors (id_author INTPRIMARY KEY AUTO_INCREMENT,nm_author VARCHAR(25)NOT NULL,inf_authors VARCHAR(150));CREATE TABLE Storage (id_Storage INTPRIMARY KEY AUTO_INCREMENT,nm_StorageVARCHAR(15));CREATE TABLE Employees (id_employ INTPRIMARY KEY AUTO_INCREMENT,nm_employVARCHAR(25)NOT NULL,job_employVARCHAR(20));CREATE TABLE Books (id_book INTPRIMARY KEY AUTO_INCREMENT,nm_bookVARCHAR(100)NOT NULL,id_publisher INT,id_employ INT,year_bookINT,pages INT,date_book DATE,price_bookDECIMAL(6,2),id_subject INT,rental_priceDECIMAL(6,2),FOREIGN KEY (id_subject) REFERENCES Subject(id_subject),FOREIGN KEY (id_employ) REFERENCES Employees(id_employ),FOREIGN KEY (id_publisher) REFERENCES Publishers (id_publisher));CREATE TABLE book_authors (id_author INT,id_book INT,FOREIGN KEY (id_author) REFERENCES Authors(id_author),FOREIGN KEY (id_book) REFERENCES Books(id_book));CREATE TABLE Сopy (copy_number INTPRIMARY KEY AUTO_INCREMENT,id_book INT,FOREIGN KEY (id_book) REFERENCES Books(id_book));CREATE TABLE Readers (id_reader INTPRIMARY KEY AUTO_INCREMENT,nm_readerVARCHAR(25)NOT NULL,address VARCHAR(50),telephonCHAR(12),document VARCHAR(10)NOT NULL,nb_documentCHAR(20),dt_document DATE,fhoto_reader BLOB);CREATE TABLE Moving (copy_number INT,id_reader INT,date_issue DATENOT NULL,return_period DATE,return_date DATE,FOREIGN KEY (copy_number) REFERENCES Сopy(copy_number),FOREIGN KEY (id_reader) REFERENCES Readers(id_reader));Добавление данных в таблицы выполняется командами SQLinsert:insertsubject(nm_subject) values ("Корпоративные информационные системы"), ("Базы данных");insert publishers(nm_Publisher, city_Publisher) values ("Юрайт", "Москва"), ("ИНФРА-М", "Москва"), ("Символ", "Москва"),("Вильямс", "СПб");insert authors(nm_author) values ("В. Астапчук"), ("П. Тепещенко"), ("Волик, М. В."), ("Н.М. Абдикеев"), ("О.В. Китова"), ("А. Бьюли"), ("К.Дж.Дейт"), ("Т.Коннолли"), ("Б.Каролин");insert readers(nm_reader, address, telephon, document, nb_document) values ("ИвановИ.В.", "Брянск", "896-236", "паспорт", "1234 987456"), ("АвдеевВ.Й.", "Саратов", "456-789", "паспорт", "3214 963852"),("СидорчукП.Я.", "Брянск", "789-456", "паспорт", "7419 123456");insert employees(nm_employ, job_employ) values ("ФедоровА.Ф. ", "библиотекарь"),("РомановаП.З.", "библиотекарь"), ("ЛебедеваЫ.Я.", "каталогизатор");insertstorage(nm_Storage) values("Зал А полка 12"),("Зал А полка 23"),("Зал А полка 10"),("Зал С полка 14");insert books(nm_book,id_publisher, id_employ, year_book, pages, date_book, price_book,id_subject, rental_price) values("Корпоративные информационные системы", 1, 3, 2017, 102, "2019-10-12", 560, 1, 20),("Корпоративные информационные системы на базе 1С", 2, 3, 2020, 102, "2020-12-12", 780, 1, 25),("Корпоративные информационные системы управления", 3, 3, 2014, 454, "2014-10-10", 490, 1, 28),("Изучаем SQL", 4, 3, 2014, 312, "2014-12-30", 850, 2, 30),("Введение в системы баз данных. 8-е издание", 2, 3, 2005, 1328, "2010-12-12", 980, 2, 10),("Базы данных. Проектирование, реализация и сопровождение", 3, 3, 2017, 1440, "2018-05-05", 870, 2, 15);insert сopy(id_book) values (1), (1), (1), (2), (2), (3), (3), (3), (4), (5), (5), (5), (5), (6), (6), (6);insert book_authors(id_author, id_book) values (1, 1), (2,1), (3,2), (4,3), (5,3), (6,4), (7,5), (8,6),(9,6);insert moving(copy_number, id_reader, date_issue, return_period, return_date) values (1,1, "2022-03-12", "2022-03-15","2022-03-15"),(2,2, "2022-03-22", "2022-03-31","2022-04-01"),(3,2, "2022-04-12", "2022-04-22","2022-04-22"),(11,3, "2022-04-12", "2022-04-20","2022-04-20"),(5,3, "2022-04-16", "2022-04-26","2022-04-27");insert moving(copy_number, id_reader, date_issue, return_period) values (5, 1, "2022-04-15", "2022-04-25"),(10,2, "2022-05-10", "2022-06-22"),(8,3, "2022-05-02", "2022-05-22"),(3,3, "2022-06-10", "2022-06-30");3.2. Проектирование наиболее востребованных запросовСформируем несколько запросов SQL для реализации некоторых потребностей пользователей.Cписок книг по выбранной тематике:select nm_book, year_book, pages, nm_subject from books, subject where books.id_subject = subject.id_subject and nm_subject="Базыданных";Cписоккнигвыбранногоавтора:select nm_author, nm_book, year_book, pages from books, authors, book_authors where books.id_book = book_authors.id_book and authors.id_author =book_authors.id_author and nm_author="Т.Коннолли";Списоккнигпоназванию:select nm_author, nm_book, year_book, pages from books, authors, book_authors where books.id_book = book_authors.id_book and authors.id_author =book_authors.id_author and nm_book LIKE "%SQL%";Списоквыданныхкниг:select nm_book, nm_reader, pages from books, biblio.сopy as bs, readers, moving where books.id_book = bs.id_book and bs.copy_number = moving.copy_number and readers.id_reader = moving.id_reader and return_date is null;Книгиопределенногоиздательства:select nm_book, pages,year_book, price_book, nm_publisher from books, publishers where books.id_publisher = publishers.id_publisher and nm_publisherlike "%Вил%";Информация о стоимости поступивших книг:select nm_book, sum(price_book), count(copy_number) from books, biblio.сopy as bs where books.id_book = bs.id_book group by nm_book;Самыйактивныйчитатель:select nm_reader, count(copy_number) from moving, readers where readers.id_reader=moving.id_reader group by nm_reader order by count(copy_number) desc limit 1;Результаты запросов показаны в приложении А.3.3. Установка индексовПри поиске данных командой SELECTвыполняется просмотр всех записей таблицы и происходит сравнение условия в WHERE и значении в поле. В таком случае есть две проблемы при выборке данных:– низкая скорость чтения файлов из-за расположения блоков таблицы в разных частях диска (фрагментация).– большое количество операций сравнения для поиска нужных данных.Индексы в MySQL являются хорошим инструментом для оптимизации SQL-запросов и позволяют организовать двоичный поиск, уменьшая количество операций сравнения.ВMySQL индексывсегда строятся для какого-то конкретногополя.В самом простом случае, индекс необходимо создавать для тех полей, которые присутствуют в условии WHERE. Создадим индексы, которые будут использоваться для ускорения поиска данных:ALTER TABLE books ADD INDEX nm_book(nm_book);ALTER TABLE books ADD INDEX id_publisher(id_publisher);ALTER TABLE books ADD INDEX id_subject(id_subject);ALTER TABLE authors ADD INDEX nm_author(nm_author);ALTER TABLE readers ADD INDEX nm_reader(nm_reader);ЗаключениеВ процессе выполнения контрольной работы была изучена предметная область «Библиотека», определены информационные объекты, их атрибуты и связи между объектами.Результатом данной работы являются построенныенормализованные таблицы и схема данных в СУБД MySQL, разработанные запросы,показывающиеинформацию из базы данных. Разработанная база данных может быть использована в создании автоматизированной системы. Переход к автоматизированному методу дает возможность:– наладить комплексное управление информацией в единой автоматизированной среде;– получить целостную картину обдвижении книг в библиотеке;– упростить обработку поступающей информации.Для развития данной системы можно предложить использовать штрих-коды или RFID-метки для идентификации книг и читателей. Это позволит не отыскивать читателя или название книги в каталоге, а по считанной метке автоматически переходить в базе на нужного читателя и автоматически закреплять за ним выданную книгу. Это во многом упростит и ускорит работу библиотекаря.Список литературыГолицына, О.Л. Базы данных: учебное пособие для студентов вузов по направлению 230700 «Прикладная информатика» / О.Л. Голицына, Н.В. Максимов, И.И. Попов, УМО РФ. – 4-е изд., перераб. и доп . – Москва: ФОРУМ : ИНФРА-М, 2014. – 400 с. Горшкова, Е. Основы технологий баз данных / Е.Горшкова, Б.Новиков. – М.:ДМК, 2018. – 240с.Грабер, М. SQL для простых смертных / М. Грабер. – М.: Лори, 2008. – 375 с. Грофф, Дж. SQL. Полное руководство / Дж. Грофф, П. Вайнберг, Э.Оппель. – СПб., «Вильямс», 3-е изд., 2015. – 960 с. Коннолли, Т. Базы данных. Проектирование, реализация и сопровождение. Теория и практика / Т.Коннолли, Б.Каролин. – СПб., «Вильямс», 3-е изд., 2017. – 1440 с. Новиков, Б.А. Основы технологий баз данных: учебное пособие / Б.А.Новиков, Е.А.Горшкова, Н.Г.Графеева; под ред. Е. В. Рогова. – 2-е изд. – М.: ДМК Пресс, 2020. – 582 с.Осипов, Д. Технологии проектирования баз данных / Д.Осипов. – М.: ДМК, 2019. – 498с.Полякова, Л.Н. Основы SQL: учебное пособие для студентов вузов по спец. 351400 «Прикладная информатика» / Л.Н. Полякова, УМО РФ. – 2-е изд., испр. – Москва: Интернет-ун-т Информационных Технологий: БИНОМ. Лаборатория знаний, 2014. – 336 с.Редмонд, Э. Семь баз данных за семь недель. Введение в современные базы данных и идеологию NoSQL / Э.Редмонд. – М.:ДМК, 2018. – 384с.Шаббир, Ч. MySQL 8 для больших данных / Ч.Шаббир. – М.:ДМК, 2018. – 226с.Шварц, Б. MySQL по максимуму / Б.Шварц. – СПб.: Питер, 2020. – 864с.Шустова, Л.И. Базы данных: учебник для студентов вузов по направлению 09.03.03 «Прикладная информатика» / Л.И. Шустова, О.В. Тараканов, УМО по образованию в области прикладной информатики РФ. – Москва: ИНФРА-М, 2016. – 304 с.Приложение А