КР База данных Городская дума. Курсовая БД_20220827. Курсовая работа по дисциплине Базы и хранилища данных База данных Городская дума
 Скачать 208.24 Kb.
|
|
МИНОБРНАУКИ РОССИИ Федеральное государственное бюджетное образовательное учреждение высшего образования «» ( Институт информационных технологий Кафедра информационных технологий и экономической информатики КУРСОВАЯ РАБОТА по дисциплине «Базы и хранилища данных» _______________________«База данных «Городская дума»»______________________ (тема) Выполнил студент__. __ (Ф.И.О.) Группы ИТЗ-201 заочной формы обучения направления подготовки Фундаментальная информатика ____________________________________ (подпись) «____» ____________ 20___г.
Челябинск 2021 Содержани Анализ предметной области 4 Инфологическое проектирование 5 Логическое проектирование 9 Процесс нормализации БД 14 Физическое проектирование 16 Приложение 1 18 Приложение 2 19 Анализ предметной области 3 Инфологическое проектирование. 4 Логическое проектирование 8 Процесс нормализации БД 13 Физическое проектирование 15 Приложение 1 17 Анализ предметной областиЦелью работы является разработка системы учета для городской думы, которая будет иметь информацию обо всех членах городской думы, а также составе комиссий по различным направлениям, которые формируются из членов думы. У каждой комиссии есть председатель, который является членом думы, комиссии заседают в разные дни с разной продолжительностью. Заседания происходят при помощи организаторов, это отдельный штат сотрудников (не члены думы). В качестве первичных документов для решения данной задачи используются данные, приведенные в Таблице 1, Приложения 1. Созданная в результате проектирования база данных должна формировать на выход следующие отчеты (запросы): Определить количество членов думы, чей стаж соответствует нижнему порогу для становления председателем; Вывести всю информацию о составе комиссии, которая заседает по направлению, указанном пользователем при поиске; Определить количество заседаний за каждый месяц в году, указанном пользователем. Вывести в алфавитном порядке Ф.И.О. членов думы пенсионного возраста, с указанием их стажа Инфологическое проектированиеНа этапе «Инфологического проектирования» выполняется построение информационно-логической или инфологической модели предметной области, называемой также концептуальной моделью. Под инфологической моделью понимается описание предметной области, выполненное с использованием специальных языковых средств, не зависящих от используемых в дальнейшем программных средств. В состав инфологической модели входят описание объектов предметной области и отношений между ними, задач и запросов пользователей. Процесс проектирования состоит из следующих шагов: 1) Определение сущностей. 2) Определение типов связей между сущностями. 3) Определение ограничений (бизнес-правила). 4) Описание сущностей и их атрибутов. Шаг 1. Сущность описывает некоторый объект предметной области. В рассматриваемой предметной области можно выделить следующие сущности: 1. Члены думы; 2. Комиссии. 3. Организаторы 4. Заседания Шаг 2. Между наборами сущностей предметной области «Городская дума» в результате анализа предметной области были установлены указанные ниже связи: Между экземплярами сущностей «Члены думы» и «Комиссии» имеется 2 связи: «Формируются», степень связи бинарная, кратность связи: M:N так как 1 Комиссия состоит из нескольких Членов думы, каждый член Думы может состоять более чем в одной Комиссии, связь обязательная со стороны Комиссии и не обязательная со стороны Членов думы. У данной связи имеется атрибут – Члены думы «Является председателем», степень связи бинарная, кратность связи: 1:1, так как у каждой Комиссии должен быть один председатель и Членом думы может ьыть председателем только в одной Комиссии, связь обязательная со стороны Комиссии и не обязательная со стороны Членов думы Между экземплярами сущности «Заседания» и «Комиссии» имеется связь «Заседают», степень связи бинарная, кардинальность связи: 1:М, так как в один день могут заседать несколько Комиссии, однако Комиссии заседают с разной продолжительностью, и одна Комиссия может заседать не чаще чем раз в пол месяца, связь обязательная со стороны сущности «Заседания». Между экземплярами сущности «Заседания» и «Организаторы» имеется связь «Проходят», степень связи бинарная, кардинальность связи: М:1, так как один организатор может организовывать несколько. Заседаний, связь обязательная со стороны сущности «Заседания» Шаг 3. Ограничения предметной области: Состав комиссий формируется из членов думы; Председатели комиссий формируются из членов думы, у которых стаж выше 7 полных лет; В один день могут заседать несколько комиссий; Все даты хранятся в виде: DD.MM.YYYY; Заседания проходят раз в пол месяца; У каждой комиссии только один председатель, и каждый председатель заседает только в одной комиссии; Стаж хранится в виде полных лет, без округления. Шаг 4. Набор сущностей имеет атрибуты – характеристики, определяющие свойства объекта. Все сущности набора имеют одни и те же атрибуты. Совокупность атрибутов должна быть такой, чтобы отличить один объект от другого. Атрибуты, как правило, являются простыми значениями, такими как целые числа, числа с плавающей запятой, строки символов, даты и т. д. Выделенные на шаге 1 сущности имеют следующие атрибуты представленные ниже в Таблице 2. Таблица 2 – Сущности и их атрибуты
Результат инфологического проектирования как правило оформляется в виде схемы модели «сущность-связь», которую также называют «ER-моделью» (essence – сущность, relation – связь), которая была предложена Питером Ченов в 1976 году. Схема ER-модели анализируемой предметной области «Городская дума приведена на рис. 1.  Рис.1. Схема ER модели анализируемой предметной области «Городская дума» в нотации Питера Чена. Логическое проектированиеОсновной задачей логического проектирования является разработка логической схемы, ориентированной на выбранную Систему управления базами данных (далее – СУБД). В настоящее время подавляющее большинство современных СУБД – реляционные. В основе реляционной модели используется понятие “отношения”, которое используется для представления: набора экземпляров объекта (сущности), отношений (связей) между объектами. На данном этапе проектирования разработанная инфологическая модель отражается в логическую, понятную для конкретной СУБД. При проектировании логической структуры базы данных (далее – БД) необходимо определить все информационные единицы и связи между ними, задать их имена, а также некоторые количественные характеристики, например - длину элементов. Отношение представляется как определенным образом организованная таблица. Преобразование одной модели в другую выполняется в соответствии со следующим алгоритмом: Каждой сущности модели «сущность-связь» ставится в соответствие отношение реляционной модели (таблица БД). Каждый атрибут сущности становится атрибутом соответствующего отношения, при этом задаются дополнительные параметры (длина поля, обязательность или необязательность данного атрибута (допустимость NULL-значений), является ли поле ключевым и проч.). Первичный ключ сущности становится первичным ключом соответствующего отношения. В каждое отношение, соответствующее сущности со стороны «многие» (связь 1:М), добавляется набор атрибутов сущности со стороны «один», являющихся первичным ключом сущности со стороны «один». Этот набор атрибутов становится внешним ключом отношения (FOREIGN KEY). Для связи M:N («многие ко многим») вводится дополнительное отношение, связанное с исходными отношениями связью «один ко многим». Его атрибуты – первичные ключи исходных отношений. Связь 1:1 реализуется в рамках одной таблицы если связь имеет класс принадлежности обязательная с одной и необязательным с другой стороны реализуется следующим образом: под каждую сущность формируется по отношению с первичными ключами, являющимися ключами соответствующих сущностей. К отношению, сущность которого имеет обязательный класс принадлежности, добавляется в качестве атрибута ключ сущности с необязательным классом принадлежности. Для моделирования необязательного и обязательного класса принадлежности: у атрибутов сущности необязательного класса принадлежности, соответствующих внешнему ключу, устанавливается свойство допустимости неопределенных значений NULL (пустого значения) при обязательном классе принадлежности атрибуты получают свойство отсутствия неопределенных значений – NOT NULL (недопустимость пустого значения атрибутов). Каждая связь, имеющая атрибуты, преобразуется в отдельную таблицу. Создадим таблицы для реляционной модели БД согласно разработанной на этапе инфологического проектирования ER-модели (Рис.1.). Приведем соответствие имен таблиц и полей на логическом и физическом уровне, который предполагает перенос готовых таблиц В указанной модели мы имеем дело со следующими сущностями: 1. Члены думы 2. Комиссии 3. Организаторы 4. Заседания Следовательно, и в реляционной модели будут участвовать четыре отношения с такими же именами. Таблица 2.1 – Члены думы
Между сущностями «Комиссии» и «Члены думы имеется связь «Является председателем» с кардинальностью «один к одному» с классом принадлежности необязательная со стороны «Члены думы». Как уже было сказано ранее такая связь реализуется в реляционной модели БД в рамках одной таблицы. Таблица 2.2 – Комиссии
Таблица 2.3 – Организаторы
Между сущностями «Комиссии» и «Заседания» имеется связь с кардинальностью «один ко многим». Такие связи в реляционной модели БД проектируются как включение ключевых атрибутов сущности со стороны «один» в каждое отношение, соответствующее сущности со стороны «многие». Таблица 2.4 – Заседания
В предметной области «Городская дума» имеется бинарная связь «Формируются» с кардинальностью «многие ко многим». Как уже было сказано ранее для дальнейшего проектирования БД с учетом требований выбранной модели БД необходимо вести дополнительное отношение, связанное с исходными отношениями связью «один ко многим» и его атрибутами будут выступать первичные ключи исходных отношений. Таблица 2.5 – Члены комиссии
Таким образом на данном этапе мы получили ненормализованную схему БД «Городская дума», представленную на Рис.2  Рис.2. Схема БД «Городская дума» Процесс нормализации БДПосле составления логической схемы БД ее надо проверить на отсутствие аномалий модификации данных. При неправильно спроектированной схеме БД могут возникать аномалии выполнения операций модификации данных. Выделяют три вида аномалий: аномалии обновления, добавления, удаления. Процесс преобразования отношения в состояние, обеспечивающее лучшие условия выборки, добавления, изменения и удаления данных называется нормализация БД. В рамках реляционной модели Э.Ф. Кодом был разработан аппарат нормализации отношений и предложен механизм, позволяющий любое отношение преобразовать к третей нормальной форме. Приведем наши отношения к третей нормальной форме (далее – НФ). Каждой нормальной форме соответствует определенный набор ограничений. 1НФ: Отношение называется нормализованным или приведенным к первой НФ тогда и только тогда, когда все его атрибуты простые (неделимые). Таблица находится в первой НФ тогда и только тогда, когда ни одна из ее строк не содержит в любом ее поле более одного значения, и не одно из ее ключевых полей не пусто. Для того чтобы привести наши отношения к первой нормальной форме необходимо: В отношении «Члены думы» атрибут «ФИО», которое является неатомарным, разделить на отдельные поля «Фамилия», «Имя», «Отчество» с типом данных – Текстовый (15) В отношении «Организаторы» - аналогично. 2НФ: Отношение находится во второй НФ, если оно приведено к 1НФ и каждый неключевой атрибут функционально полно зависит от составного ключа. Таким образом приводить ко 2НФ необходимо только отношения с составным ключом. В нашей схеме имеется одно такое отношение – «Члены комиссий». Неключевой атрибут «Должность» функционально полно зависит от ключевых атрибутов «Код Комиссии» и «Код и Члена думы». 3НФ: Отношение находится в третьей НФ если оно находится во второй НФ и в нем отсутствуют транзитивные зависимости. При анализе нашей схеме БД таких зависимостей не было выявлено. Итоговая схема БД приведена на Рис.3  Рисунок 3. Схема базы данных Физическое проектированиеРеализация проекта курсовой работы по разработке системы учета для Городской думы будет произведена с использованием программного обеспечения MS Access (далее по тексту – ПО MS Access). ПО MS Access позволяет при создании таблиц (отношений) использовать как графический интерфейс, схожий с табличным редактором MS Excel, так и команды SQL, которые по синтаксису незначительно отличаются от стандартного. Спроектированные и приведенные к 3НФ отношения теперь необходимо создать. Все SQL команды и запросы для создания и обработки Таблиц приведены в Приложении 2. Для удобства ввода информации в базу данных реализованы Формы: 1. Члены думы 2. Комиссии. При заполнении формы Комиссии в поле «Председатель» учитывается ограничение предметной области - председатели комиссий формируются из членов думы, у которых стаж выше 7 полных лет, которое реализовано через запрос: SELECT Фамилия, Имя, Отчество, Стаж FROM [Члены Думы] WHERE Стаж >= 7; При помощи данного запроса формируется таблица строками которой являются только те Членов думы, которые соответствуют указанным критериям ограничения предметной области и только они будут доступны при выборе Председателя в таблице Комиссии и в Форме Комиссии, созданной для удобного заполнения. 3. Члены комиссии 4. Заседания Реализованы следующие отчеты: 1. Количество членов думы, чей стаж соответствует нижнему порогу для становления председателем 2. Состав комиссии, которая заседает по направлению, указанном пользователем при поиске 3. Члены думы пенсионного возраста, с указанием их стажа 4. Сводная форма отчета, с применением Макросов, на которой расположены кнопки для запуска, вышеуказанных отчетов. Приложение 1Таблица 1 – Первичные входные документы
Приложение 21. SQL команды для создания таблиц: 1.1 Отношение Члены думы CREATE TABLE Члены думы (Код_ЧленаДумы int RPIMARY KEY Фамилия CHAR(15) Имя CHAR(15) Отчество CHAR(15) Дата рождения DATA Номер_телефона INTEGER Дата вступления в думу DATA ); 1.2 Отношение Комиссии CREATE TABLE Комиссии (Код_Комисии int PRIMARY KEY Председатель CHAR(15) Направление CHAR(15) ); 1.3 Отношение Организаторы CREATE TABLE Организаторы (Код_Организатора int RPIMARY KEY Фамилия CHAR(15) Имя CHAR(15) Отчество CHAR(15) Дата рождения DATA Номер_телефона INTEGER ); 1.4 Отношение Заседания CREATE TABLE Заседания (Код_Заседания int RPIMARY KEY Коммисия CHAR(15) Организатор CHAR(15) Дата DATA Время_начала Продолжительность INTEGER ); 1.5 Отношение Члены Комиссий CREATE TABLE Члены комиссий (Код_ЧленаДумы int RPIMARY KEY Код_Комиссии ); 2. SQL запросы для реализации ограничений 2.1 Количество членов думы, которые могу претендовать на место председателя комиссии: SELECT Count([Члены думы].Фамилия & [Члены думы].Имя & [Члены думы].Отчество & Стаж.Стаж) AS Количество FROM [Члены думы] INNER JOIN Стаж ON ([Члены думы].Отчество = Стаж.Отчество) AND ([Члены думы].Имя = Стаж.Имя) AND ([Члены думы].Фамилия = Стаж.Фамилия) WHERE (((Стаж.Стаж) Between 7 And 7)); 2.2 Состав комиссии, которая заседает по направлению, указанном пользователем при поиске: SELECT Комиссии.Направление AS Направление, [Члены комиссий].Код_ЧленаДумы AS Фамилия, [Члены Думы].Имя, [Члены Думы].Отчество, Комиссии.Председатель FROM [Члены Думы] INNER JOIN (Комиссии INNER JOIN [Члены комиссий] ON Комиссии.Код_Комиссии = [Члены комиссий].Код_Комиссии) ON [Члены Думы].Код_ЧленаДумы = [Члены комиссий].Код_ЧленаДумы WHERE (((Комиссии.Направление) Like [Введите направление] & "*")); 2.3 Вывести в алфавитном порядке Ф.И.О. членов думы пенсионного возраста, с указанием их стажа: SELECT [Члены Думы].Фамилия, [Члены Думы].Имя, [Члены Думы].Отчество, Round((Date()-[Дата вступления])/365) AS Стаж FROM [Члены Думы] WHERE ((((Date()-[Члены Думы].[Дата рождения])/365)>60) AND (([Члены Думы].Пол)="Мужской")) ORDER BY [Члены Думы].Фамилия, [Члены Думы].Имя, [Члены Думы].Отчество; |