Проектирование и создание баз данных по семантическому описанию

Размещено на http://www.allbest.ru/

Белорусский государственный университет

Международный государственный экологический институт имени А. Д. Сахарова

Факультет мониторинга окружающей среды

Кафедра информационных технологий в экологии и медицине

Курсовой проект по дисциплине «Базы данных» студента 3-го курса направления специальности 1-40 05-01 «Информационные системы и технологии (в экологии)»

ПРОЕКТИРОВАНИЕ И СОЗДАНИЕ БАЗ ДАННЫХ ПО СЕМАНТИЧЕСКОМУ ОПИСАНИЮ

Гладырева Данилы Михайловича

Научный руководитель

к.ф.-м.н., доцент. В. А. Иванюкович

Минск 2021



Содержание

• база инфологический семантический модель
• Введение
• 1. Информационно-логическая модель
• 2. Проверка таблиц на соответствие нормальным формам
• 2.1 Нормальные формы
• 2.2 Проверка на нормальность организованных таблиц
• 3. Создание запросов
• 3.1 Запросы на создание таблиц и установлению связей между ними
• 3.2 Запросы на выборку
• Заключение
• Список литературы


Введение

Целью данного курсового проекта является проектирование и разработка базы данных, основанной на инфологической модели по семантическому описанию, хранящей в себе сведения о работе магистратуры института.

Для показа работы магистратуры будут созданы следующие таблицы: поступающие, магистранты, успеваемость, публикации, магистрант-Публикации, руководители, степень, степень-Рук, ИтогАт, заявления, практика, испытания, а также связи между ними. При выполнении работы данные таблицы будут проверены на соответствие нормальным формам.

Для выполнения задания будет использована программа Microsoft SQL Server Management Studio 18. Данное задание будет выполнено с целью продемонстрировать навыки работы с базами данных, а также с некоторыми возможностями и инструментами Microsoft SQL Server Management Studio 18.



1. Информационно-логическая модель

Информационно-логическая модель отображает данные предметной области в виде совокупности информационных объектов и связей между ними. Данная модель представляет данные, подлежащие хранению в базе данных.

Информационный объект(сущность) - это информационное описание некоторого реального объекта, процесса, явления или события. Сущность образуется совокупностью взаимосвязанных реквизитов, представляющих качественные и количественные характеристики предметной области. Каждой сущности нужно присвоить уникальное имя, соответствующее этому объекту [1].

Атрибутом(полем) сущности является характеристика, которая является свойством сущности. Каждому элементу при описании базы данных присваивается имя. По этому имени к нему обращаются при работе.

Условие для выделения сущностей и образованию связей между ними:

У одного руководителя может быть несколько магистрантов.

Заявление можно подавать сразу только на одну специальность.

У магистранта может быть только одно место практики.

У магистранта может быть несколько публикаций.

У руководителя может быть несколько степеней, но только одно звание.

В данном случае выделяется 12 сущностей:

Сущность, содержащая сведения о Поступающих. Атрибуты сущности: ID поступающего, ФИО поступающего, полученная специальность, оконченный ВУЗ, средний балл. При наличии нескольких высших образований из разных учебных заведений, вносится то, в котором была получена специальность связанная, непосредственно, с поступлением в магистратуру;

Сущность, содержащая сведения о Заявлениях. Атрибуты сущности: ID заявления, ID поступающего, специальность в магистратуре, на которую подается заявление, итог-прошел ли поступающий на выбранную специальность или нет;

Сущность, содержащая сведения о Испытаниях. Атрибуты сущности: ID поступающего, оценка по экзамену, соответствующему выбранной специальности, оценка по иностранному языку;

Сущность, содержащая сведения о Итоговой аттестации. Атрибуты сущности: ID магистранта, дата аттестации, итог-прошел итоговую аттестацию или нет, присвоенная степень после окончания магистратуры;

Сущность, содержащая сведения о Магистрантах-Публикациях. Атрибуты сущности: ID публикации, ID магистранта;

Сущность, содержащая сведения о Магистрантах. Атрибуты сущности: ID магистранта, ID руководителя, тема исследования магистранта, ID поступающего;

Сущность, содержащая сведения о Практике. Атрибуты сущности: ID магистранта, дата начала практики, дата конца практики, организация в которой проходилась практика;

Сущность, содержащая сведения о Публикациях. Атрибуты сущности: ID публикации, название публикации, дата публикации, издание в котором была публикация;

Сущность, содержащая сведения о Руководителях. Атрибуты сущности: ID руководителя, ФИО руководителя, звание руководителя, специальность руководителя;

Сущность, содержащая сведения о Степени руководителя. Атрибуты сущности: ID степени, степень;

Сущность, содержащая сведения о Степени-Руководителях. Атрибуты сущности: ID степени, ID руководителя;

Сущность, содержащая сведения о Успеваемости. Атрибуты сущности: ID магистранта, зачет1-зачтено/нет, зачет2-зачтено/нет, зачет3-зачтено/нет, оценка по экзамену, соответствующему выбранной специальности, оценка по философии, оценка по ИТ, оценка по иностранному языку;

Необходимо выделить и создать связи между сущностями, согласно семантическому описанию.

Связи "один к одному" имеют место, когда каждому экземпляру первой сущности соответствует только один экземпляр второй сущности и наоборот, каждому экземпляру второй сущности соответствует только один экземпляр первой сущности.

Связи "один ко многим" - это такие связи, когда каждому экземпляру одной сущности может соответствовать несколько экземпляров другой сущности, а каждому экземпляру второй сущности может соответствовать только один экземпляр первой сущности. В такой связи первая сущность является главной, а вторая - подчиненной.

Связи "многие ко многим" - это такие связи, когда каждому экземпляру первой сущности соответствуют несколько экземпляров второй сущности и каждому экземпляру второй сущности может соответствовать несколько экземпляров первой сущности. Такие связи не могут непосредственно реализовываться в реляционной базе данных.

В выделенных сущностях используются связи “один ко многим” и “один к одному”

Инфологическая модель, созданная на основе сематического описания и выделенных сущностей, выглядит так (Рисунок1).



Рисунок 1 Инфологическая модель



2. Проверка таблиц на соответствие нормальным формам

2.1 Нормальные формы

В реляционных базах данных есть такое понятия, как «Нормализация».

Нормализация - это процесс удаления избыточных данных.

Нормализацию можно рассматривать с позиции проектирования базы данных, в таком случае мы можем сформулировать определение нормализации следующим образом.

Нормализация - это метод проектирования базы данных, который позволяет привести базу данных к минимальной избыточности.

Избыточность устраняется, как правило, за счёт разбиения одной таблицы на несколько.

Первая нормальная форма далее (НФ)

Отношение находится в 1НФ, если все его атрибуты являются простыми, все используемые домены должны содержать только скалярные значения. Не должно быть повторений строк в таблице.

Вторая нормальна форма

Отношение находится во 2НФ, если оно находится в 1НФ и каждый не ключевой атрибут неприводимо зависит от Первичного Ключа (ПК). Неприводимость означает, что в составе потенциального ключа отсутствует меньшее подмножество атрибутов, от которого можно также вывести данную функциональную зависимость.

Третья нормальная форма

Отношение находится в 3НФ, когда находится во 2НФ и каждый не ключевой атрибут не транзитивно зависит от первичного ключа. Проще говоря, второе правило требует выносить все не ключевые поля, содержимое которых может относиться к нескольким записям таблицы в отдельные таблицы.

Нормальная форма Бойса-Кодда (НФБК) (частная форма третьей нормальной формы)

Определение 3НФ не совсем подходит для следующих отношений:

1)отношение имеет два или более потенциальных ключа;

2)два и более потенциальных ключа являются составными;

3)они пересекаются, т.е. имеют хотя бы один общий атрибут.

Для отношений, имеющих один потенциальный ключ (первичный), НФБК является 3НФ.

Отношение находится в НФБК, когда каждая нетривиальная и неприводимая слева функциональная зависимость обладает потенциальным ключом в качестве детерминанта.

Четвертая нормальная форма

Отношение находится в 4НФ, если оно находится в НФБК и все нетривиальные многозначные зависимости фактически являются функциональными зависимостями от ее потенциальных ключей.

Пятая нормальная форма

Отношения находятся в 5НФ, если оно находится в 4НФ и отсутствуют сложные зависимые соединения между атрибутами. Если «Атрибут_1» зависит от «Атрибута_2», а «Атрибут_2» в свою очередь зависит от «Атрибута_3», а «Атрибут_3» зависит от «Атрибута_1», то все три атрибута обязательно входят в один кортеж. Это очень жесткое требование, которое можно выполнить лишь при дополнительных условиях. На практике трудно найти пример реализации этого требования в чистом виде.

Шестая нормальная форма

Переменная отношения находится в шестой нормальной форме тогда и только тогда, когда она удовлетворяет всем нетривиальным зависимостям соединения. Из определения следует, что переменная находится в 6НФ тогда и только тогда, когда она неприводима, то есть не может быть подвергнута дальнейшей декомпозиции без потерь. Каждая переменная отношения, которая находится в 6НФ, также находится и в 5НФ.



2.2 Проверка на нормальность организованных таблиц

Для проверки выделенных сущностей были построены функциональные диаграммы каждой сущности (Рисунок 2).

Так как все их поля содержат неделимую информацию и ключевые поля не нулевые (1НФ);

Так как каждый не ключевой атрибут неприводимо зависит от первичного ключа (2НФ).

Так как каждый не ключевой атрибут нетранзитивно зависит от первичного ключа (3НФ).

Так как каждая нетривиальная и неприводимая слева функциональная зависимость обладает потенциальным ключом в качестве детерминанта (НФБК).

Так как каждая переменная не может быть подвергнута дальнейшей декомпозиции без потерь (6НФ И 5НФ).



Рисунок 2 Функциональные диаграммы



3. Создание запросов

3.1 Запросы на создание таблиц и установлению связей между ними

Запрос на создание таблицы «Поступающие»

create table поступающие (

idп int identity (1,1) not null constraint pk_idп primary key references заявления(idзаяв),

ФИО varchar (100) not null,

СП varchar (100) not null,

ЗАВ varchar (100) not null,

СРБ float not null);

Запрос на создание таблицы «Магистранты»

create table магистранты (

idм int identity (1,1) not null constraint p_idм primary key references практика(idм),

idрук int not null constraint f_idрук foreign key references руководители(idрук),

ти varchar (100) not null,

idп int constraint f_idп foreign key references поступающие(idп) not for replication);

Запрос на создание таблицы «Публикации»

create table публикации (

idпуб int identity (1,1) not null constraint p_idпуб primary key,

название varchar (100) not null,

дата date not null,

издание varchar (100) not null);

Запрос на создание таблицы «МагистрантПубликации»

create table магистрантПубликации (

idпуб int identity(1,1) not null constraint p_idруб primary key references публикации(idпуб),

idм int not null constraint fs_idм foreign key references магистранты(idм));

Запрос на создание таблицы «Руководители»

create table руководители (

idрук int identity (1,1) not null constraint pk_idрук primary key,

ФИО varchar (100) not null,

зван varchar (100) not null,

спец varchar (100) not null);

Запрос на создание таблицы «Степень»

create table степень (

idстеп int identity (1,1) not null constraint pk_idстеп primary key,

степень varchar (100) not null);

Запрос на создание таблицы «СтепеньРук»

create table степеньРук (

idстеп int identity (1,1) not null constraint p_idстеп primary key references степень(idстеп),

idрук int not null constraint fk_idрук foreign key references руководители(idрук));

Запрос на создание таблицы «Испытания»

create table испытания (

idп int identity (1,1) not null constraint p_idп primary key references заявления(idзаяв),

экзпоСпец varchar (100) not null,

инЯз varchar (100) not null);

Запрос на создание таблицы «Заявления»

create table заявления (

idзаяв int identity (1,1) not null constraint pr_idзаяв primary key references заявления(idзаяв),

спец varchar (100) not null,

итог varchar (100) not null);

Запрос на создание таблицы «ИтогАт»

create table ИтогАт(

idм int identity (1,1) not null constraint ps_idм primary key references магистранты(idм),

дата date not null,

итог varchar (100) not null,

степень varchar (100));

Запрос на создание таблицы «Успеваемость»

create table успеваемость (

idм int identity (1,1) not null constraint po_idм primary key references магистранты(idм),

зачет1 varchar (100) not null,

зачет2 varchar (100) not null,

зачет3 varchar (100) not null,

ЭкзпоСпец int not null,

философия int not null,

ит int not null,

инЯз int not null);

Запрос на создание таблицы «Степень»

create table практика(

idм int identity (1,1) not null constraint pr_idм primary key,

началоП date not null,

конецП date not null,

организация varchar(100) not null);

В результате выполнения данных запросов в базе данных было создано 12 таблиц с соответствующими связями между ними (Рисунок 3).



Рисунок 3 Диаграмма БД

3.2 Запросы на выборку

Запрос 1: Найти магистрантов, которые проходили практику в 2008 году(рис. 4)

select фио

from практика inner join магистранты on практика.idм=магистранты.idм join поступающие on поступающие.idп=магистранты.idп

where практика.началоп>'01.01.2007' and практика.конецп<'31.12.2009'



Рисунок. 4 Запрос 1

Запрос 2: Найти ВУЗ, из которого вышло наибольшее число магистрантов (рис.5)

select зав

from заявления inner join поступающие on заявления.idзаяв=поступающие.idп

where итог='прошел'

group by зав

having count (*) = (select max(x) as kol

from (select count (*) as x

from заявления inner join поступающие on заявления.idзаяв=поступающие.idп

where итог='прошел' and зав is not null

group by зав

) as tab);

Рисунок. 5 Запрос 2

Запрос 3: Найти тройку: магистрант-тема исследования-название публикации, чтобы тема исследования была последней по алфавитному порядку. (рис.6)

select фио, ти,название

from магистранты inner join поступающие on магистранты.idм=поступающие.idп join магистрантпубликации on магистранты.idм=магистрантпубликации.idм join публикации on магистрантпубликации.idпуб=публикации.idпуб

where ти = (select max(ти)

from магистранты

where ти<>'');

Рисунок. 6 Запрос 3

Запрос 4: Получить пару преподаватель-магистрант, где преподаватель- доцент и доктор физмат наук, а магистрант публиковался 3 и более раз (рис. 7)

select distinct руководители.фио as префио,поступающие.фио

from ((((руководители inner join магистранты on руководители.idрук=магистранты.idрук) inner join поступающие on поступающие.idп=магистранты.idм) inner join магистрантпубликации on магистрантпубликации.idм=поступающие.idп)

inner join степеньрук on руководители.idрук=степеньрук.idрук) inner join степень on степень.idстеп=степеньрук.idстеп

where руководители.зван='доцент' and степень.степень = 'доктор физмат наук'

group by руководители.фио,поступающие.фио

having count(магистрантпубликации.idм)>=3



Рисунок 7 Запрос 4

Запрос 5: Найти тех магистрантов, название, издание и дату публикации, которые проходили практику в компании, принявшей больше всего человек на практику (рис. 8)

select фио,название,издание,дата

from поступающие inner join магистранты on поступающие.idп=магистранты.idм join магистрантпубликации on магистранты.idм=магистрантпубликации.idм join публикации on магистрантпубликации.idпуб=публикации.idпуб join практика on магистранты.idм=практика.idм

where организация=(select организация

from заявления inner join поступающие on заявления.idзаяв=поступающие.idп join практика on поступающие.idп=практика.idм

where итог='прошел'

group by организация

having count(*) = (select max(x) as kol

from (select count(*) as x

from заявления inner join поступающие on заявления.idзаяв=поступающие.idп join практика on поступающие.idп=практика.idм

where итог='прошел' and организация is not null

group by организация

) as tab))



Рисунок 8 Запрос 5

Запрос 6: Найти пару магистрант-преподаватель, в которой у магистранта средний балл меньше среднеарифметического вступительных испытаний(рис. 9)

select distinct поступающие.фио as постфио,руководители.фио

from поступающие, испытания,руководители,магистранты

where поступающие.idп=магистранты.idм and магистранты.idрук=руководители.idрук and поступающие.idп=испытания.idп and (испытания.экзпоспец+испытания.иняз)/2 > срб

group by поступающие.фио,испытания.экзпоспец,испытания.иняз,руководители.фио

Рисунок 9 Запрос 6

Запрос 7: Найти магистранта, у которого сданы все зачеты, средний балл больше 9 и у его куратора 2 и больше степени (рис.10)

select поступающие.фио

from поступающие,магистранты, успеваемость, руководители, степеньрук

where поступающие.idп=магистранты.idм and магистранты.idм=успеваемость.idм and магистранты.idрук=руководители.idрук and степеньрук.idрук=руководители.idрук and зачет1='зач' and зачет2='зач' and зачет3='зач' and (философия+ит+иняз+экзпоспец)/4>=9

group by поступающие.фио

having count(степеньрук.idрук)>=2

Рисунок 10 Запрос 7

Запрос 8: Найти поступающих с наименьшим и наибольшим баллом, где балл есть среднее арифметическое из вступительных экзаменов и среднего балла после окончания вуза (рис.11)

select фио, балл

from(select фио, (срб+экзпоспец+иняз)/3 as балл, итог

from заявления inner join поступающие on заявления.idзаяв=поступающие.idп join испытания on поступающие.idп=испытания.idп ) as кол1

where балл = (select max(балл) from (select фио, (срб+экзпоспец+иняз)/3 as балл,итог

from заявления inner join поступающие on заявления.idзаяв=поступающие.idп join испытания on поступающие.idп=испытания.idп) as кол11

where итог='прошел')

union

select фио, балл

from(select фио, (срб+экзпоспец+иняз)/3 as балл, итог

from заявления inner join поступающие on заявления.idзаяв=поступающие.idп join испытания on поступающие.idп=испытания.idп) as кол2

where балл = (select min(балл) from (select фио, (срб+экзпоспец+иняз)/3 as балл,итог

from заявления inner join поступающие on заявления.idзаяв=поступающие.idп join испытания on поступающие.idп=испытания.idп) as кол2

where итог='прошел')

Рисунок 11 Запрос 8



Заключение

В результате выполнения работы была создана инфологическая модель базы данных по семантическому описанию. Для показа работы магистратуры были созданы таблицы с соответствующими данными, а также связи между ними. При выполнении работы данные таблицы были проверены на соответствие нормальным формам.

Во ходе выполнения работы были рассмотрены, изучены и использованы такие элементы SQL Server, как запросы, диаграммы, представления. Созданы запросы, которые доказали, что работа выполнена верно и с полученной в результате базой можно работать. Стоит учесть, что данную базу можно дополнить, улучшить и использовать, как реальный рабочий аппарат.

Получены и закреплены навыки работы в программе Microsoft SQL Server Management Studio 18. Закреплены основы баз данных.

В данной работе использовалась только небольшая часть инструментов и возможностей СУБД SQL Server. При возникновении потребности, в СУБД SQL Server можно выполнять более сложные задачи, используя более мощные инструменты манипулирования базой данных и самим сервером.



Список литературы

1. Построение информационно-логической модели данных [Электронный ресурс]/под ред. uCoz 2007. Режим доступа: http://adder74-access.narod.ru/7.html.

2. Атрибут базы данных: основные понятия [Электронный ресурс]/под ред. Astor 2011 - Режим доступа: http://access.avorut.ru/publ/bazy_dannykh_osnovnye_ponjatija/a/atribut/28-1-0-17.

3. Иванюкович В.А. Системы баз данных. Учебное пособие для студентов специальности «Информационные системы и технологии». 2008., 164 стр.

4. Нормализация отношений. Шесть нормальных форм [Электронный ресурс]/под ред. Сивков Сергей 2015. Режим доступа: https://habr.com/ru/post/254773/.

5. https://www.youtube.com/channel/UCmdZlsCXz57nazOdp_o5wAg

Размещено на Allbest.ru