Создать реляционную базу данных

Титульный листОглавление TOC \o "1-3" \h \z \u Введение PAGEREF _Toc101089253 \h 31.Инфологическое проектирование PAGEREF _Toc101089254 \h 41.1.Анализ предметной области PAGEREF _Toc101089255 \h 41.2.Анализ информационных задач и пользователей системы PAGEREF _Toc101089256 \h 61.2.1.Определение бизнес требований PAGEREF _Toc101089257 \h 71.2.2.Определение требований пользователя PAGEREF _Toc101089258 \h 102.Логическое проектирование РБД PAGEREF _Toc101089259 \h 112.1Выявление реквизитов сущностей PAGEREF _Toc101089260 \h 112.2Формирование схемы данных PAGEREF _Toc101089261 \h 112.3Выбор СУБД и других программных средств PAGEREF _Toc101089262 \h 122.4Составление реляционных отношений PAGEREF _Toc101089263 \h 122.5Нормализация полученных отношений PAGEREF _Toc101089264 \h 142.6Определение требований к операционной обстановке PAGEREF _Toc101089265 \h 142.7Описание групп пользователей и прав доступа PAGEREF _Toc101089266 \h 153.Физическое проектирование БД PAGEREF _Toc101089267 \h 163.1Создание таблиц в БД PAGEREF _Toc101089268 \h 163.2Готовые запросы PAGEREF _Toc101089269 \h 21Заключение PAGEREF _Toc101089270 \h 22Список использованных источников PAGEREF _Toc101089271 \h 23ВведениеРабота предполагает постановку задач проектирования базы данных, формулировку и анализ проблемной ситуации, разработку технического предложения, содержащего рассмотрение нескольких возможных путей ее разрешения, обоснование избираемого варианта решения, выполнение расчетных, исследовательских, конструкторских, технологических работ, включая обязательную разработку комплекта или отдельных элементов технической документации. В содержание курсовой работы могут быть включаются задачи заполнения тестовой информацией разработанной базы данных или ее отдельных таблиц. Задачами курсовой работы являются:закрепление, углубление, расширение и систематизация знаний, полученных при изучении данной дисциплины («Базы данных») и других, предшествовавших ей дисциплин;закрепление умений применять эти знания для решения типовых и нестандартных задач;формирование умений работы с программным инструментарием систем управления базами данных;приобретение опыта аналитической, расчетной, конструкторской работы и формирование соответствующих умений;развитие умений работы со специальной литературой и иными информационными источниками;приобретение опыта научно-исследовательской работы и формирование соответствующих умений;формирование умений формулировать логически обоснованные выводы, предложения и рекомендации по результатам выполненной работы; формирование умений выступать перед аудиторией с докладом при защите проекта, компетентно отвечать на вопросы, вести профессиональную дискуссию, убеждать оппонентов в правильности принятых решений. Целью выполнения курсовой работы по данной дисциплине научиться:определять границы предметной области в части выявления основных сущностей и связей между ними;определять внутренние и внешние сущности предметной области;выделять функциональные и обеспечивающие подсистемы информационной системы управления бизнес-процессами предприятия;выполнять постановку задачи на проектирование БД на примере конкретизированной предметной области;применять математический аппарат для обоснования выбора оптимального проектного решения;применять методы моделирования для описания и анализа предметной области и проектирования базы данных. Инфологическое проектированиеАнализ предметной областиБиблиотека представляет собой хранилище книг. Основные ее функции - выдавать читателям книги и принимать их. Остальные функции относятся к обслуживанию процесса выдачи и приема. Экземпляр каждой книги, хранящейся в библиотеке, должен иметь индивидуальный номер. Каждый читатель должен иметь свой формуляр, который хранит как историю взятых книг, так и информацию о самом читателе. Кроме этого, имеет значение учет должников. Книги выдаются читателям на руки, на определенный срок. Индивидуальный номер книги, дата выдачи и дата возврата записываются. Если читатель не вернул книгу в срок и не продлил её, он не имеет право получить следующую книгу.Для того чтобы сократить ручные операции библиотекаря и ускорить обработку информации, необходимо разработать реляционную базу данных, которая будет хранить список читателей и книг, зарегистрированных в библиотеке, а также информацию о задолжниках, которые не вернули книгу вовремя. Ниже представлен словарь терминов предметной области, который описан в виде таблицы (таблицу 1). Таблица 1 – Словарь терминов предметной области ТерминНазначениеЧитательЗарегистрированное лицо, пользующееся услугами библиотекиКнигаПечатное издание, находящееся в библиотекеАвторЧеловек, который написал определенную книгуИздательствоКомпания, которая взяла в тираж написанную книгу автора, распечатала её в множестве экземпляров и пустила в продажиФормулярФорма, в которой библиотекарем фиксируется информация о взятых читателем книгахБиблиотекарьРаботник библиотеки, обслуживающий читателей.ОтчетДокумент, который предоставляет информацию по книгам на руках или читателям с задолженностямиПосле описания предметной области, необходимо по методу Аббота сформировать словарь предметной области (см. таблицу 2) и объектно-ориентированный словарь (см. таблицу 3). Таблица 2 – Словарь предметной области по методу АбботаСуществительноеГлаголЧитательрегистрировать / редактировать / удалять / проверятьКнигавыдать / принять Автордобавить/удалитьИздательстводобавить/удалитьФормуляробновитьБиблиотекарьзаписать/обновить/удалитьОтчетсформироватьПосле формирования словаря предметной области, на основе её данных строится объектно-ориентированный словарь предметной области.Таблица 3 – Объектно-ориентированный словарь Класс/СущностьСвойство/СостояниеМетод/Функция/ПоведениеЧитательидентификатор, ФИО, паспортные данные, номер телефона,адрес проживанияотметка о запрете выдачиРегистрация читателя, Редактирование данных читателя,Удаление читателя,КнигаИдентификатор книги, наименование, автор, издательство, год выпускаВыдача книги,Принятие книгиАвторИдентификатор автора, ФИОДобавить автора,Удалить автора.ИздательствоИдентификатор издательства, наименованиеДобавить издательство,Удалить издательство.Формуляридентификатор читателя,выданная книга, дата выдачи, дата возвратаОбновлениеБиблиотекарьИдентификатор библиотекаря, ФИОЗаписать/обновить/удалитьСоответственно базовыми сущностями предметной области являются:Книга;Читатель;Формуляр;Авторы;Издательство;Библиотекарь.На основании выявленных сущностей определена начальная ER-диаграмма разрабатываемой базы данныхРисунок 1 – ER-диаграммаАнализ информационных задач и пользователей системыПроектируемая база данных «Библиотека», должна предоставлять следующие возможности:Вся информация о читателях библиотеки будет храниться в БД;Доступ к данной информации будет возможен только после авторизации библиотекаря по логину и паролю;У библиотекаря должны быть следующие возможности:Авторизация в системе (ввод логина и пароля);Регистрация новых читателей. При регистрации в библиотеку читатель предъявляет паспорт или заменяющий его документ, для внесения данных в БД читателей. После того, как библиотекарь внёс данные читателя в форму, система проверяет корректность введённых данных, в случае того, если данные введены неверно, появится информации об ошибке. Далее, новому зарегистрированному читателю присваивается индивидуальный номер читателя;Редактирование информации о читателе (паспортные данные, номер телефона, адрес проживания);Удаление читателя из БД читателя;Просмотр списка должников, которые не продлили книгу, и не вернули ее в течение 14 дней;Просмотр списка прочитанных и взятых книг, определенного читателя;Добавление новых книг, посредством ввода названия книги, автора, издательства и года выпуска;Редактирование информации о книге;Удаление книги из БД книг;Выдача книг читателю;Возврат книг;Изменение возврата книг в формуляре (в случае продления книг, или если должник вернул книгу).Запросы, которые будут разработаны в нашей БД:Список должников;Количество взятых книг каждым читателем;Список книг с указанием автора и издательства.Определение бизнес требованийМодель в нотации IDEF0 представляет собой совокупность иерархически упорядоченных и взаимосвязанных диаграмм. Вершина этой древовидной структуры, представляющая собой самое общее описание системы и ее взаимодействия с внешней средой. Описание контекста модели:Предмет моделирования – процесс обслуживания читателей. Область моделирования – модель охватывает процессы внутри библиотеки, связанные с выдачей и сдачей литературы, регистрацией читателей, а также регистрацией новой литературы.Цель моделирования – показать, как будут выглядеть процессы после внедрения системы;Точка зрения – библиотекарь.Основные потоки:Входящие потоки:Данные читателя (ФИО, паспортные данные читателя, номер телефона, адрес проживания);Данные книги;Уникальный номер книги.Управляющие потоки:ФЗ от 29.12.1994 N 78-ФЗ «О библиотечном деле»;Правила пользования библиотекой.Ресурсные потоки:База данных библиотеки;Библиотекарь.Выходящие потоки:Номер читателя;Отчёты по книгам;Отказ о выдаче книги;127444523495000Запись о новой книге.Рисунок 2 –Диаграмма процесса «Обслуживание читателей»Декомпозиция верхнего уровня «Обслуживание читателей» представлена на рисунке 3.Основными бизнес-функциями процесса будут:Регистрация читателя;Выдача книги;Возврат книги;Регистрация книги;Формирование отчета.9391651905000Рисунок 3 – Диаграмма декомпозиции процесса обслуживание читателейДля более подробного описания мы декомпозируем процесс «Регистрация читателя» (Рис.4).10382252413000Рисунок 4 – Диаграмма декомпозиции процесса регистрация читателяИз рисунка 4 мы видим, что процесс регистрации разделился на три подсистемы. Библиотекарь вводит данные о читателе, далее идет проверка корректного ввода, после чего переходим к формированию идентификатора. Декомпозируем процесс выдачи книги (Рис.5).11220451905000Рисунок 5 – Диаграмма декомпозиции процесса выдачи книгиОтсюда видно, что выдача книги делится на четыре процесса. Далее рассмотрим декомпозиции процессов «Принятие книги» (Рис.6), «Регистрации новой книги» (Рис.7), и «Формирование отчётов» (Рис.8).Рисунок 6 – Диаграмма декомпозиции процесса возврата книги9772652540000Рисунок 7 – Диаграмма декомпозиции процесса регистрации книги10382251905000Рисунок 8 – Диаграмма декомпозиции процесса формирование отчетаПредоставленные схемы дают полное представление о разрабатываемой системе и о том, как эти процессы будут выглядеть после внедрения данной системы.Определение требований пользователяОпишем функциональное назначение БД с помощью диаграммы вариантов использования, таким образом, показав, что будет делать разрабатываемая база данных.76390524130Рисунок 9 – Диаграмма вариантов использования БДБД разрабатывается для одного человека, поэтому на диаграмме всего одно действующее лицо. Все описанные функции выполняет библиотекарь, других пользователей в системе не предусмотрено.Логическое проектирование РБД2.1Выявление реквизитов сущностейВ сфере разработки программного обеспечения ER-диаграмма, как правило, служит первым шагом в определении требований проекта по созданию информационных систем. На дальнейших этапах работы ER-диаграммы также применяются для моделирования конкретных баз данных. Реляционная база данных сопровождается соответствующей реляционной таблицей и при необходимости может быть представлена в этом формате.На рисунке 10 изображена уточненная ER-диаграмма сущность-связь для разрабатываемой системы, в ней: прямоугольниками обозначены активные части системы, овалами – атрибуты, многоугольниками активные действия.Рисунок 10 – Уточненная ER-диаграмма2.2Формирование схемы данныхПриведённая уточненная ER-диаграмма отражает основные таблицы, связи и атрибуты, на ее основе можно построить схему БД. Созданная схема базы данных, приведена на рисунке 11.Рисунок 11 – Схема данных БД «Библиотека»2.3Выбор СУБД и других программных средствВ качестве СУБД для реализации базы данных была использована программа MS SQL Server.SQL сервер - программа, которая предназначена для хранения базы данных и обеспечения доступа к этим данным из других программ. В SQL Server включена поддержка языка XML и протокола HTTP, средства повышения быстродействия и доступности, позволяющие распределить нагрузку и обеспечить бесперебойную работу. Сложный доступ к данным используется для надежности их хранения. SQL сервер позволяет резервное копирование в любой момент рабочего дня без отключения пользователей. Также если размер вашей базы данных стремиться к гигабайту и продолжает увеличиваться, то SQL сервер единственно возможный метод обеспечения ее функционирования.2.4Составление реляционных отношенийИсходя из созданной ER-диаграммы и схемы данных, количество таблиц в базе данных «Библиотека» будет 6.Таблица SEQ Таблица \* ARABIC 1 - Схема отношения Авторы (Authors)Содержание поляИмя поляТип, длинаПримечаниеКод автораid_AuthorsintПервичный ключ, уникальныйФамилияSurnamevarchar(50)Обязательное полеИмяNamevarchar(20)Обязательное полеОтчествоPatronymicvarchar(20)Обязательное полеТаблица SEQ Таблица \* ARABIC 2- Схема отношения Издательства (Publishing)Содержание поляИмя поляТип, длинаПримечаниеКод издательстваid_PublishingintПервичный ключ, уникальныйНаименованиеNamevarchar(50)Обязательное полеТаблица SEQ Таблица \* ARABIC 3- Схема отношения Книги (Books)Содержание поляИмя поляТип, длинаПримечаниеИдентификационный номерid_BooksintПервичный ключ, уникальныйНаименование книгиNameBooksvarchar(50)Обязательное полеАвторid_AuthorsintВнешний ключ к Авторы, необязательное полеИздательствоid_PublishingintВнешний ключ к Издательства, необязательное полеГод изданияYearintОбязательное полеТаблица SEQ Таблица \* ARABIC 4 - Схема отношения Читатели (Readers)Содержание поляИмя поляТип, длинаПримечаниеНомер читательского билетаid_ReadersintПервичный ключ, уникальныйФамилияSurnamevarchar(50)Обязательное полеИмяNamevarchar(20)Обязательное полеОтчествоPatronymicvarchar(20)Обязательное полеАдрес проживанияAddressvarchar(70)Обязательное полеПаспортные данныеSerNumintintОбязательное полеОбязательное полеНомер телефонаPhoneDecimal (11,0)Обязательное полеТаблица SEQ Таблица \* ARABIC 5 - Схема отношения Библиотекари (Librarian)Содержание поляИмя поляТип, длинаПримечаниеНомер библиотекаряid_LibrarianintПервичный ключ, уникальныйФамилияSurnamevarchar(50)Обязательное полеИмяNamevarchar(20)Обязательное полеОтчествоPatronymicvarchar(20)Обязательное полеТаблица SEQ Таблица \* ARABIC 6 - Схема отношения Формуляр (Form)Содержание поляИмя поляТип, длинаПримечаниеНомер записиid_FormintПервичный ключ, уникальныйНомер читательского билетаid_ReadersintВнешний ключ к Читатели, необязательное полеИдентификационный номерid_BooksintВнешний ключ к Книги, необязательное полеДата выдачиDateTakedateОбязательное полеДата возвратаDatePutdateОбязательное полеБиблиотекарьid_LibrarianintВнешний ключ к Библиотекари, необязательное поле2.5Нормализация полученных отношенийЦелью нормализации является получение БД, где каждый факт появляется только один раз, т.е исключена избыточность. Всего существует 5 НФ, но нам необходимо привести нашу БД, к 4 НФ1 НФ: Все атрибуты отношения простые;2 НФ: Каждое неключевое поле связано полной функциональной зависимостью с первичным ключом;3 НФ: Любой неключевой атрибут нетранзитивно зависит от ключа;4 НФ: Каждый детерминант отношений является возможным ключомИсходя из описанных выше нормальных форм, наши описанные выше отношения находятся в 4 НФ, поэтому далее нормализация не требуется.2.6Определение требований к операционной обстановкеПримем во внимание, что данная база данных физически реализована в MS SQL Server, поэтому, примем средние системные требования, которые устанавливает производитель данного программного обеспечения. Процессор:Intel Pentium IV и выше;Операционная система:Windows 2007 или более поздняя версия;Память:Рекомендуется 512 Мб, а лучше 1 ГБ;Дисковое пространство:Для SQL Server требуется как минимум 6 ГБ свободного места на диске;Объём внешней памяти, необходимый для функционирования системы, складывается из двух составляющих: память, занимаемая модулями СУБД и память, отводимая под данные (МД). Примем ориентировочно, что: одновременно выдаётся около 5 книг, выдача книг длится в среднем 10 минут (по 0,2K на каждую книгу); в день обслуживается порядка 20 читателей (по 0,1К)работает 3 сотрудника (по 0,4К на каждого сотрудника);в библиотеке числится 1000 читателей (по 0,3K на каждого читателя)Тогда объём памяти для хранения данных за первый год примерно составит: Mд = 2 * (1000 * 0,3 + 6 * (5 * 0,2) + 300 * (20 * 0,1) + 3 * 0,4) = 1813К2.7Описание групп пользователей и прав доступаСотрудники библиотеки имеют право изменять поля дата возврата в таблице ФОРМУЛЯР, а также изменить номер телефона, и адрес проживания для читателя. Сотрудники библиотеки: имеют доступ по чтению и доступ по записи ко всем отношениям.Администратор БД: имеет доступ ко всем данным (по записи), может изменять структуру базы данных и связи между отношениями. Устанавливает права доступа для всех остальных групп.Читатели БД: имеют доступ по чтению к формуляру.3.Физическое проектирование БД3.1Создание таблиц в БДФизическое проектирование БД приводит к созданию набора SQL запросов, достаточного, чтобы полностью сформировать базу данных на соответствующей СУБД.Создадим SQL-запросы для создания таблиц:Create table Authors ( id_Authors INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Surname VARCHAR(50) NOT NULL, Name VARCHAR(20) NOT NULL, Patronymic VARCHAR(20) NOT NULL,);Рисунок 12 – Созданная таблица АвторыCreate table Publishing ( id_Publishing INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, );Рисунок 13 – Созданная таблица ИздательстваCreate table Librarian ( id_Librarian INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Surname VARCHAR(50) NOT NULL, Name VARCHAR(20) NOT NULL, Patronymic VARCHAR(20) NOT NULL,);Рисунок 14 – Созданная таблица БиблиотекариCreate table Books ( id_Books INT NOT NULL PRIMARY KEY, NameBooks VARCHAR(50) NOT NULL, Year INT NOT NULL, id_Authors INT, id_Publishing INT, FOREIGN KEY (id_Authors) REFERENCES Authors (id_Authors), FOREIGN KEY (id_Publishing) REFERENCES Publishing (id_Publishing),);Рисунок 15 – Созданная таблица КнигиCreate table Readers ( id_Readers INT NOT NULL PRIMARY KEY, Surname VARCHAR(50) NOT NULL, Name VARCHAR(20) NOT NULL, Patronymic VARCHAR(20) NOT NULL, Address VARCHAR(70) NOT NULL, Ser INT NOT NULL, Num INT NOT NULL, Phone Decimal(11,0),);Рисунок 16 – Созданная таблица ЧитателиCreate table Form ( id_Form INT IDENTITY(1,1) NOT NULL PRIMARY KEY, DateTake Date NOT NULL, DatePut Date, id_Readers INT, id_Books INT, id_Librarian INT, FOREIGN KEY (id_Readers) REFERENCES Readers (id_Readers), FOREIGN KEY (id_Books) REFERENCES Books (id_Books), FOREIGN KEY (id_Librarian) REFERENCES Librarian (id_Librarian),);Рисунок 17 – Созданная таблица ФормулярТеперь, после создания всех таблиц, необходимо их заполнить данными, также через sql-запросы.Добавление записей в таблицу Авторы:INSERT Authors VALUES ('Turgenev', 'Ivan', 'Sergeevich')INSERT Authors VALUES ('Dostoevsky', 'Fyodor', 'Mikhailovich')INSERT Authors VALUES ('Stephen', ' King', '-')INSERT Authors VALUES ('Bulgakov', 'Mikhail', 'Afanasievich')INSERT Authors VALUES ('Zemlyanoi', 'Andrey', 'Borisovich')INSERT Authors VALUES ('Tolstoy', 'Lev', ' Nikolaevich')INSERT Authors VALUES ('Chekhov', 'Anton', 'Pavlovich')INSERT Authors VALUES ('Pushkin', 'Alexander', 'Sergeevich')Рисунок 18 – Заполненная таблица АвторыДобавление записей в таблицу Издательства:INSERT Publishing VALUES ('children's literature')INSERT Publishing VALUES ('abc')INSERT Publishing VALUES ('eksmo')INSERT Publishing VALUES ('artistic literature')INSERT Publishing VALUES (' education')Рисунок 19 – Заполненная таблица ИздательстваДобавление записей в таблицу Библиотекари:INSERT Librarian VALUES ('Petrova', 'Marina', 'Alexandrovna')INSERT Librarian VALUES ('Prosvyatova', 'Oksana', 'Stanislavovna')INSERT Librarian VALUES ('Ivanova', 'Maria', 'Georgievna')Рисунок 20 – Заполненная таблица БиблиотекариДобавление записей в таблицу Читатели:INSERT Readers VALUES ('146302', 'Sidorov', 'Ivan', 'Stepanovich', 'Refrigeration 12-45', 2698, 596524, 89044613496)INSERT Readers VALUES ('146311', 'Leontiev', 'Kirill', 'Eduardovich', 'Michurina 35-96', 2625, 154646, 89524856382)INSERT Readers VALUES ('146316', 'Stydnikov', 'Oleg', 'Borisovich', ' Belinsky 2-69', 2641, 496164, 89104663118)INSERT Readers VALUES ('487623', 'Ogorodnikov', 'Dmitry', 'Sergeevich', ' Lenina 36-35', 2652, 564646, 89153496390)INSERT Readers VALUES ('485689', 'Petrichenko', 'Elena', 'Alekseevna', 'Chaplin 8-52', 2626, 466493, 89485934008)INSERT Readers VALUES ('145613', 'Eremina', 'Yulia', 'Nikolaevna', 'Field 64-145', 2631, 694648, 89134634892)INSERT Readers VALUES ('646147', 'Kochubeeva', 'Angelina', 'Andreevna', 'Vodoprovolnaya 3-12', 2616, 479461, 89489314982)INSERT Readers VALUES ('146492', 'Boyko', 'Roman', 'Vasilyevich', 'Republics 92-25', 2679, 496139, 89144969830)Рисунок 21 – Заполненная таблица ЧитателиДобавление записей в таблицу Книги:INSERT Books VALUES ('14572598', 'Fathers and children', 1862, 2, 4)INSERT Books VALUES ('25623461', ' First love', 1860, 2, 5)INSERT Books VALUES ('24963166', ' Glow', 1977, 4, 2)INSERT Books VALUES ('49614966', ' Institute', 2019, 4, 3)INSERT Books VALUES ('41461464', ' Matryoshka in feathers', 2014, 8, 2)INSERT Books VALUES ('32549649', ' Cool heirs', 2001, 8, 3)INSERT Books VALUES ('25479641', 'The Master and Margarita', 1967, 5, 1)INSERT Books VALUES ('14961491', ' Zoyas apartment', 1925, 5, 1)INSERT Books VALUES ('64961168', 'Anna Karenina', 1878, 7, 4)INSERT Books VALUES ('58964141', ' Childhood', 1852, 7, 5)INSERT Books VALUES ('78651461', 'Cherry Orchard', 1904, 9, 4)INSERT Books VALUES ('96149611', 'Lady with a dog', 1899, 9, 2)INSERT Books VALUES ('81496169', ' Road Master', 2006, 6, 3)Рисунок 22 – Заполненная таблица КнигиДобавление записей в таблицу Формуляр:INSERT Form VALUES ('2020-08-31', '2020-09-14', 146302, 14572598, 2)INSERT Form VALUES ('2020-03-02', '', 146311, 25623461, 2)INSERT Form VALUES ('2020-11-25', '2020-12-18', 146492, 25479641, 1)INSERT Form VALUES ('2020-01-15', '', 146492, 49614966, 1)INSERT Form VALUES ('2020-09-12', '', 487623, 58964141, 3)INSERT Form VALUES ('2021-07-10', '2021-07-20', 646147, 32549649, 3)INSERT Form VALUES ('2021-09-30', '', 145613, 14961491, 2)INSERT Form VALUES ('2021-07-18', '2021-07-30', 145613, 24963166, 1)INSERT Form VALUES ('2021-12-12', '2022-01-10', 146302, 81496169, 3)Рисунок 23 – Заполненная таблица Формуляр3.2Готовые запросыКоличество взятых книг, каждым читателемSELECT id_readers, COUNT(*) as count FROM Readers GROUP BY id_readersСписок должниковSELECT * FROM FormWHERE dateput