Файл: Курсовая работа по дисциплине Базы данных студента очной формы обучения.docx

2 Разработка информационной системы

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

2.1 Разработка информационного обеспечения

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

На основе созданной в 1-й главе логической модели БД, создадим физическую модель данных с указанием типов данных (рис. 5):



Рисунок 5. Физическая модель БД «Успеваемость студентов»

Программирование на стороне SQL-сервера. Создавать базу, добавлять колонки, вносить, удалять и изменять данные в базе, выполнять другие действия, проще говоря «вести базу данных» можно через pgAdmin4. Это утилита управления СУБД PosgreSQL. Создали базу данных «student_s» (рис.6)




Рисунок 6. Созданная база данных для разработки системы успеваемости студентов

Следом идет создание доменов. Доменом называют глобальные описания типа поля, доступные всей базе данных. Они полезны для вынесения общих характеристик разных полей в единое место. Для создания домена в утилите pgAdmin необходимо выбрать пункт Domains. На таблице 1 показаны характеристики созданных доменов

Таблица1 – Ограничение созданных доменов

Имя домена	Тип	Not Null	Default	Check
Form_of_education	Character (20)	+	-	= очная или = заочная
Course	Integer	+	-	>0 и <=6
Appraisal	Integer	+	-	>1 и<=5

---

Домен form_of_educationпозволяет вносить только определенные значения в данное поле, что немало важно, ведь форма обучения может быть только очная или заочная. Второй домен courseзадает ограничения на ввод курса для студента. Он может быть в диапазоне [1,6]. Третий домен appraisal задает рамки на ввод оценки студента, чтобы пользователь не смог ввести иную оценку.

Далее идет процесс создания таблиц для базы данных. Создана таблица «student». Для задания связей между таблицами, она имеет первичный ключ «number_grade_book», внешний ключ «id_specialty», который связывает его с таблицей специальностей.


Листинг 1 – Программный код таблицы student.

CREATETABLEpublic.student

(

number_grade_bookintegerNOT NULL,

surname_student character varying(20) COLLATE pg_catalog."default" NOT NULL,

name_student character varying(20) COLLATE pg_catalog."default" NOT NULL,

patronymic_student character varying(20) COLLATE pg_catalog."default",

coursecourse NOT NULL,

passport_number integer NOT NULL,

form_of_educationform_of_education COLLATE pg_catalog."default",

id_specialty integer NOT NULL,

CONSTRAINT student_pkey PRIMARY KEY (number_grade_book),

CONSTRAINT id_specialty FOREIGN KEY (id_specialty)

REFERENCES public.specialty (id_specialty) MATCH SIMPLE

ON UPDATE CASCADE

ON DELETE CASCADE

NOT VALID)

WITH (

OIDS = FALSE

)

TABLESPACEpg_default;

Конец листинга 1.

Создание составного уникального индекса для таблицы ‘student’, состоящего из двух полей («surname_student», «name_student»). Повторяющихся имен + фамилий не будет, но разрешается использовать одинаковые имена и фамилии по отдельности.

Листинг 2 – Создание индекса.

CREATE UNIQUE INDEX students_fullname_idx

ON public.student USING btree

(surname_student COLLATE pg_catalog."default" bpchar_pattern_ops ASC NULLS LAST, name_student COLLATE pg_catalog."default" bpchar_pattern_ops ASC NULLS LAST)

Продолжение листинга 2.

TABLESPACE pg_default;

Конец листинга 2.

Создание таблицы успеваемости. Она имеет первичный ключ, который является так же внешним. Для связи с таблицей расписания для данной таблицы задан внешний ключ. Программный код располагается ниже:

Листинг 3 – Создание таблицы «progress».

CREATETABLEpublic.progress

(

number_grade_book integer NOT NULL,

exam_date date NOT NULL,

appraisalappraisal NOT NULL,

semester_number integer NOT NULL,

id_class integer NOT NULL,

CONSTRAINT progress_pkey PRIMARY KEY (number_grade_book),

CONSTRAINT id_class FOREIGN KEY (id_class)

REFERENCES public.class_shedule (id_class) MATCH SIMPLE

ON UPDATE CASCADE

ON DELETE CASCADE

NOT VALID,

CONSTRAINT number_grade_book2 FOREIGN KEY (number_grade_book)

REFERENCES public.student (number_grade_book) MATCH SIMPLE

ON UPDATE CASCADE

ON DELETE CASCADE

NOT VALID

)

WITH (

OIDS = FALSE

)

TABLESPACE pg_default;

Конец листинга 3.

Остальные таблицы, а именно -

• 
  Faculty
  
• 
  Specialty
  
• 
  Teacher
  
• 
  Subject
  
• 
  Class_shedule,
  

созданы по аналогии, и программный код из разработки находится в приложении А.

Далее были заполнены таблицы. Это можно отследить на рисунке 7:

---

Рисунок 7. Заполнение таблицы преподавателей данными

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

Разработка бизнес логики на стороне SQL сервера. Представления - виртуальные таблицы, формируемые из столбцов и строк других таблиц. Они работают в запросах и командах, но не содержат никаких собственных данных. С помощью представления пользователи просматривают информацию, которая физически хранится в базовой таблице. Можно сказать, что представление - это запрос. Представление создается с помощью инструкции «CREATE VIEW».

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

Листинг 4 - Представления из таблицы «class_shedule».

CREATE OR REPLACE VIEW public.info_teacher

AS

SELECT class_shedule.id_teacher,

count(class_shedule.id_subject) AS kolvo_subject

FROM class_shedule

GROUP BY class_shedule.id_teacher;

Конец листинга 4.

Листинг 4 содержит программный код созданного представления, которое позволяет пользователю увидеть, количество предметов, которое ведет преподаватель.

Далее было создано представление, которое выводит студентов, учащихся на первом курсе на очной форме обучения (листинг 5).

Листинг 5 – Представление из таблицы student.

CREATE OR REPLACE VIEW public.first_kurs

AS

SELECT student.number_grade_book AS n,

student.surname_student AS s_surname,

student.name_student AS s_name,

student.course,

student.passport_number AS passport,

student.form_of_education AS form

FROM student

WHERE student.course::integer = 1 AND student.form_of_education::bpchar = 'очная'::bpchar;

Конец листинга 5.

Программный код остальных представлений находится в приложении Б.

Хранимые процедуры являются объектом базы данных, представляющий собой набор SQL – инструкций, которые компилируются один раз и хранятся на сервере. Созданная далее функция позволяет добавлять строки в таблицу «subject» (листинг 6).

Листинг 6 – Хранимая процедура insert_positive.

CREATE OR REPLACE FUNCTION public.insert_positive(

subname character varying)

RETURNS character varying

LANGUAGE 'sql'

COST 100

VOLATILE PARALLEL UNSAFE

AS $BODY$

insert into subject(subject_name) values ($1) returning $1;

$BODY$;

Конец листинга 6.

Хранимая процедура, программный код которой находится в листинге 7, служит для удаления данных в таблицах, а именно, в таблице преподавателей. Функция удаляет поле из таблицы, где должность преподавателя - «ректор». Данная функция может понадобиться, если в институте сменился ректор, и нам нужно удалить данные о старом ректоре.

---

Листинг 7 – Функция clear_docent.

CREATE OR REPLACE FUNCTION public.clear_docent(

)

RETURNS void

LANGUAGE 'sql'

COST 100

VOLATILE PARALLEL UNSAFE

AS $BODY$

delete from teacher where position='Ректор'

$BODY$;

Конец листинга 7.

Следующая хранимая процедура создана для модификации данных. Функция позволяет изменить курс студента (листинг 8).

Листинг 8 – Хранимая процедура update_course.

CREATE OR REPLACE FUNCTION public.update_course(

surname_student character varying,

name_student character varying,

course integer)

RETURNS integer

LANGUAGE 'sql'

COST 100

VOLATILE PARALLEL UNSAFE

AS $BODY$

update student set course=$3 where surname_student=$1 and name_student=$2 returning $3;

$BODY$;

Конец листинга 8.

Также были созданы другие функции, для добавления, удаления и модификации данных в таблицах. Их программный код находится в приложении В.

Помимо стандартных SQL функций, в PostgreSQL имеется возможность писать функции и на процедурных языках программирования (C, Java и т.д.), но также данная СУБД располагает встроенным языком PL/pgSQL. В данном языке реализованы все основные средства программирования, условные операторы, переменные, циклы и т.д. По большому счёту любая функция на данном языке имеет чёткую структуру, название функции, список входящих аргументов, описание возвращаемого значения, описываемого после RETURNS.

Создана функция на языке plpgsql, которая удаляет предмет из списка по названию, веденному пользователем. Данная функция имеет тип void, поэтому ничего не возвращает. Программный находится в листинге 9.

Листинг 9 – Функция api для удаления данных.

CREATE OR REPLACE FUNCTION public.api(

apiname character)

RETURNS void

LANGUAGE 'plpgsql'

COST 100

VOLATILE PARALLEL UNSAFE

AS $BODY$

begin delete from subject where subject_name=apiname; end;

$BODY$;

Конец листинга 9.

На рисунке 8 и 9 показана реализация функции, описанной выше.



Рисунок 8. Использование функции


Рисунок 9. Результат работы функции.

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

Листинг 10 – Программный код функции subjectinfo.

CREATE OR REPLACE FUNCTION public.subjectinfo(

---

name character varying)

RETURNS character varying

LANGUAGE 'plpgsql'

COST 100

VOLATILE PARALLEL UNSAFE

AS $BODY$

declare

total integer default 0;

subject character varying(18) default '';

begin

select count(*) into total from subject where subject_name=name;

if (total = 0) or (total > 1)

then raise exception 'Ошибка!' using hint = 'Проверьтеназвание!';

subject := 'error';

else

selectsubject.id_subject into subject from "subject" where "subject_name" = name;

end if;
Продолжение листинга 10.

return subject;

end;

$BODY$;

Конец листинга 10.

Теперь продемонстрируем работу этой функции. На рисунке 10 показан вызов этой функции и результат работы.



Рисунок 10. Результат работы функции.

Далее была создана функция, написанная на языке plpgsql, удаляющая студентов из успеваемости, которые имеют оценку 2 по текущему экзамену. Программный код находится в приложении Г.

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

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

Данная функция и сам триггер созданы для обновления данных. Для этого будет создана таблица changeTeacher, куда будут записываться обновления таблицы teacher. В листинге 11 будет программный код функции, которая будет вызвана при активации триггера.
Листинг 11 – Триггерная функция «nameChange».

CREATE or replace FUNCTION nameChange()

RETURNS trigger

LANGUAGE 'plpgsql'

AS $BODY$

begin

INSERT INTO "changeTeacher"

values(concat_ws

( "surname_teacher", old."name_teacher", old."patronymic_teacher"),

concat_ws

( new."surname_teacher", new."name_teache", new."patronymic_teacher),

current_timestamp,user);

return new;

end;

$BODY$

Конец листинга 11.

В листинг 12 показан программный код самого триггера для функции, написанной выше.

---