БАЗА ДАННЫХ РАСПИСАНИЯ ЗАНЯТИЙ

1 Федеральное государственное автономное образовательное учреждение высшего профессионального образования «Московский физико-технический институт (государственный университет)» Факультет нано-, био-, информационных и когнитивных технологий Поликарпов Василий Николаевич БАЗА ДАННЫХ РАСПИСАНИЯ ЗАНЯТИЙ Итоговая работа по курсу "Базы данных" г. Москва 2015

2 Оглавление БАЗА ДАННЫХ РАСПИСАНИЯ ЗАНЯТИЙ Инфологическое проектирование Анализ предметной области Анализ информационных задач и круга пользователей системы Определение требований к операционной обстановке Выбор СУБД и других программных средств Логическое проектирование реляционной БД Преобразование ER диаграммы в схему базы данных Составление реляционных отношений Нормализация полученных отношений (до 4НФ) Определение дополнительных ограничений целостности Описание групп пользователей и прав доступа Реализация проекта базы данных Создание таблиц Создание представлений Создание последовательностей Назначение прав доступа Создание индексов Создание процедур и функций Создание триггеров

3 1. Инфологическое проектирование 1.1. Анализ предметной области База данных (БД) создаётся для информационного обслуживания преподавателей и студентов одного отдельно взятого вуза. БД должна содержать данные о расписании занятий, преподавателях, группах и студентах. В соответствии с предметной областью система строится с учётом следующих особенностей: Каждый студент учится в одной группе. В одной группе может учиться произвольное число студентов Каждое занятие может проводиться у любого числа групп, ровно как и у каждой группы может быть произвольное число занятий. Каждому занятию может соответствовать произвольное число заданий. Кажое задание соответствует ровно одному занятию. Каждое занятие соответствует определенному предмету. Каждый предмет может быть представлен любим числом занятий Каждое занятие ведет один преподаватель. Каждый преподаватель может вести произвольное число занятий. Каждый преподаватель может разбираться в произвольном числе предметов, ровно как каждый предмет может преподаваться любым числом преподавателей. Для создания ER-модели необходимо выделить сущности предметной области: 1. Студенты (ФИО, дата рождения) 2. Группы (название) 3. Занятия (аудитория, вид занятия, начало, конец, день недели) 4. Задания (дата, текст) 5. Предметы (название) 6. Преподаватели (ФИО) Исходя из выявленных сущностей, построим ER-диаграмму. Пометки у линий обозначают степень связи (1:1, 1:N, M:N). Сплошная линия обозначает обязательную связь. Пунктирная линия обозначает факультативную связь. 3

4 1.2. Анализ информационных задач и круга пользователей системы Определим группы пользователей, их основные задачи и запросы к БД. 1. Отдел кадров изменение списка преподавателей 2. Деканат изменение списка студентов, групп изменение списка предметов изменение расписания 3. Преподаватель просмотр расписания занятий, проводимых преподавателем добавление и изменение домашних заданий по своим занятиям просмотр информации о преподавателях и предметах, которые они преподают 4. Студент чтение своего расписания чтение своих заданий просмотр информации о преподавателях и предметах, которые они преподают 4

5 2. Определение требований к операционной обстановке На основе результатов анализа ПрО можно приблизительно оценить объём памяти, требуемой для хранения данных. Примем ориентировочно, что: количество студентов в группе порядка 50 (по 0,5 Кб на студента) количество групп в вузе порядка 100 (по 0,5 Кб на группу) количество занятий за неделю порядка 1000 (каждое занятие по 1 Кб) количество разнообразных предметов примерно совпадает с количеством преподавателей и порядка 100 (на каждый предмет или преподавателя по 0,5 Кб) каждому занятию может соответствовать до 100 заданий (по одному-двум на неделю) (до 10 Кб на задание) Исходя из этих предположений, объём памяти для хранения данных составит примерно: M Д = 2*(0,5* ,5* * ,5* *100000)

2 ГБ Предполагается, что база данных будет очищаться от значительной части информации дважды в год (по окончании осеннего и весеннего семестров), поэтому значительного роста объёма данных БД не должно наблюдаться. Предполагается, что для базы данных будет осуществляться высокая интенсивность запросов при большом объёме результирующих данных. В целях обеспечения стабильной работы желательно использование современного оборудования. 5

6 3. Выбор СУБД и других программных средств Анализ информационных задач показывает, что для реализации требуемых функций подходят почти все СУБД для ПЭВМ (Oracle Database, PostgreSQL, MySQL и др.). Все они поддерживают реляционную модель данных и предоставляют разнообразные возможности для работы с данными. Так как на целевом оборудовании уже имеется в нашем распоряжении СУБД "Oracle Database 11g Express Edition" и ОС "Windows 7 Professional", то в целях экономии материальных и производительных средств база данных будет реализована с использованием вышеуказанных программных средств. 6

7 4. Логическое проектирование реляционной БД 4.1. Преобразование ER диаграммы в схему базы данных Преобразуем имеющуюся ER-диаграмму в схему базы данных. Связи типа 1:N реализуем через внешние ключи. Связи типа N:M реализуем через вспомогательные отношения. В полученной БД применяется только один тип связи отношений: Полученная схема реляционной базы данных (РБД): 4.2. Составление реляционных отношений Каждое реляционное отношение соответствует одной сущности (объекту предметной области), и содержит в себе все атрибуты этой сущности. Каждое отношение содержит в себе первичный ключ и, при необходимости, внешние ключи. Если в отношении нет потенциальных ключей, вводится суррогатный первичный ключ, служащий только для идентификации записей. Отношения приведены в таблицах ниже. Для каждого отношения указаны атрибуты с их внутренним названием, типом и длиной. Условные обозначения для типов данных: 7

8 N числовой C символьный тип фиксированной длины V символьный тип переменной длины D дата Группы [groups] Идентификатор группы id N(3,0) Первичный ключ Наименование name V(30) Обязательное поле Студенты [students] Идентификатор id N(5,0) Первичный ключ ФИО name V(100) Обязательное поле Группа grp N(3,0) Внешний ключ Дата рождения birthday D Обязательное поле Предметы [subjects] Идентификатор id N(3,0) Первичный ключ Наименование name V(50) Обязательное поле Преподаватели [preps] Идентификатор id N(3,0) Первичный ключ ФИО name V(100) Обязательное поле Занятия [lessons] Идентификатор id N(7,0) Первичный ключ Предмет subj N(3,0) Внешний ключ Преподаватель prep N(3,0) Внешний ключ Аудитория room V(20) Вид занятия type V(20) Начало start N(5,0) Обязательное поле Конец end N(5,0) Обязательное поле День недели day N(1,0) Обязательное поле 8

9 Задания [tasks] Идентификатор id N(7,0) Первичный ключ Занятие lesson N(7,0) Внешний ключ Дата day D Обязательное поле Текст text NCLOB Занятия-группы (вспомогательное отношение) [grp_less] В данном отношении используется составной первичный ключ, покрывающий оба поля. Занятие lesson N(7,0) Внешний ключ Группа grp N(3,0) Внешний ключ Предметы-преподаватели (вспомогательное отношение) [prep_subj] В данном отношении используется составной первичный ключ, покрывающий оба поля. Предмет subj N(3,0) Внешний ключ Преподаватель prep N(3,0) Внешний ключ 4.3. Нормализация полученных отношений (до 4НФ) 1НФ: сложные (делимые) атрибуты среди приведённых выше таблиц: "ФИО" в отношениях "Преподавателя" и "Студенты". Для нормализации разделим их на два атрибута: "Фамилия" и "Имя, Отчество". 2НФ: составных первичных ключей (за исключением вспомогательных отношений) нет. Приведение к это форме не требуется. 3НФ: транзитивных зависимостей нет. Приведение к этой форме не требуется. 4НФ: нетривиальные многозначные зависимости отсутствуют. Приведение к этой форме не требуется. В результате нормализации изменились таблицы "Преподавателя" и "Студенты": Студенты [students] Идентификатор id N(5,0) Первичный ключ Фамилия lastname V(30) Обязательное поле Имя, отчество firstname V(100) Обязательное поле Группа grp N(3,0) Внешний ключ Дата рождения birthday D Обязательное поле 9

10 Преподаватели [preps] Идентификатор id N(3,0) Первичный ключ Фамилия lastname V(30) Обязательное поле Имя, отчество firstname V(100) Обязательное поле 4.4. Определение дополнительных ограничений целостности Ограничения целостности базы данных, которые не были указаны выше: День недели может принимать значения от 1 до 7 включительно Значения начала и конца занятия могут принимать значения от 0 до 86399, значение конца должно быть больше значения начала Значение поля "типа занятия" может быть пустым или принимать одно из следующих значений: "лекция", "семинар", "практика", "зачет", "экзамен". Преподаватель, преподающий предмет, должен знать его (должна быть соответствующая запись в таблице "Предметы-преподаватели") Ограничения целостности, не реализуемые за счёт языка DCL, должны быть реализованы программно Описание групп пользователей и прав доступа Опишем для каждой группы пользователей права доступа к каждой таблице. Используются следующие сокращения: s чтение данных (select) i добавление данных (insert) u модификация данных (update) d удаление данных(delete) Таблицы groups Группы пользователей Отдел кадров Деканат Преподаватель Студент SUID students SUID S subjects SUID preps SUID S S lessons SUID S tasks S SUID S grp_less SUID S prep_subj SUID S S 10

11 5. Реализация проекта базы данных 5.1. Создание таблиц create table groups ( id number(3,0) primary key, name varchar2(30) not null ); create table students ( id number(5,0) primary key, lastname varchar2(30) not null, firstname varchar2(100) not null, grp number(3,0) references groups (id), birthday date not null ); create table subjects ( id number(3,0) primary key, name varchar2(50) not null ); create table preps ( id number(3,0) primary key, lastname varchar2(30) not null, firstname varchar2(100) not null ); create table lessons ( id number(7,0) primary key, subj number(3,0) references subjects (id), prep number(3,0) references preps (id), room varchar2(20), type varchar2(20), tstart number(5,0) not null, tend number(5,0) not null, day number(1,0) not null, check (type in ('','лекция','семинар','практика','экзамен','зачет')), check (tstart between 0 and and tend between 0 and and tend>tstart), check (day between 1 and 7) ); create table tasks ( id number(7,0) primary key, lesson number(7,0) references lessons (id), day date not null, text nclob ); create table grp_less ( lesson number(7,0) references lessons (id), grp number(3,0) references groups (id) ); 11

12 create table prep_subj ( ); subj number(3,0) references subjects (id), prep number(3,0) references preps (id) 5.2. Создание представлений Приведем примеры нескольких готовых запросов (представлений). Представление "readable_timeline" Предоставляет доступ к читаемому варианту расписания всех групп (с заменой всех идентификаторов на осмысленные значения, такие как имена и названия) create or replace view readable_timeline as select l.id id, s.name subj, (p.lastname ' ' p.firstname) prep, l.room room, l.type type, formattime(l.tstart) tstart, formattime(l.tend) tend, convertdaytostr(l.day) day from lessons l, preps p, subjects s where l.subj = s.id and l.prep = p.id order by day, tstart; Представление "prep_workload" Предоставляет данные по загрузке преподавателей. create or replace view prep_workload as select p.id id, (p.lastname ' ' p.firstname) name, t.time time from preps p, (select prep, formattime(sum(tend)-sum(tstart)) time from lessons group by prep) t where t.prep = p.id; 5.3. Создание последовательностей Для корректного назначения идентификаторов записей для отношений, обладающих суррогатными первичными ключами, для каждого такого отношения создается последовательность вида: CREATE SEQUENCE tasks_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE; -- последовательность для отношения "Задания" 5.4. Назначение прав доступа Названия групп пользователей: Отдел кадров "staffoffice" Деканат "deanery" Преподаватель "leacher" 12

13 Студент - "student" Примеры назначения прав доступа различным группам пользователей: grant select, insert, update, delete on groups to deanery; grant select, insert, update, delete on preps to staffoffice; grant select on readable_timeline to student; Права доступа назначаются в соответствии с разделом Создание индексов Анализ готовых запросов и общий анализ БД показывает, что имеет смысл создать индексы для, например: внешних ключей: create index lessons_prep on lessons(prep); create index students_grp on students(grp); названий предметов, фамилий студентов и преподавателей: create index preps_lastname on preps(lastname); create index students_lastname on students(lastname); create index subjects_name on subjects(name); 5.6. Создание процедур и функций Функция "formattime" Принимает время в виде количества секунд от начала дня. Возвращает читаемую строку вида "HH:MM" create or replace function formattime (n IN number) return varchar is s number(2,0) := MOD(n,60); m number(2,0) := 0; h number(2,0) := 0; begin m := MOD((n - s)/60,24); h := (n-s-m*60)/24; return to_char(h,'0') ':' to_char(m,'0'); exception when others then end; dbms_output.put_line('error in formattime function!'); raise; Функция "convertdaytostr" create or replace function convertdaytostr (n IN number) return varchar is name varchar(30); begin 13

14 if (n = 1) then name:= 'Понедельник'; elsif (n = 2) then name:= 'Вторник'; elsif (n = 3) then name:= 'Среда'; elsif (n = 4) then name:= 'Четверг'; elsif (n = 5) then name:= 'Пятница'; elsif (n = 6) then name:= 'Суббота'; elsif (n = 7) then else name:= 'Воскресенье'; name:= '[нет данных]'; end if; return name; exception when others then end; dbms_output.put_line('error in convertdaytostr function!'); raise; Процедура "addnexttask" Процедура принимает идентификатор предмета и текст задания и добавляет задание на следующее занятие. Следующим занятием в данном случае считается ближайшей занятие, начиная со следующего дня. create or replace procedure addnexttask (lid IN number, text IN nclob) is curdate date := sysdate+1; lday number(1,0) := 0; dif number(1,0) := 0; begin select day into lday from lessons where dif := lday-to_char(sysdate,'d'); if (dif<0) then dif := dif+7; end if; insert into tasks(id,lesson,day,text) values(tasks_seq.nextval,lid,trunc(curdate+dif,'dd'),text); exception end; when TIMEOUT_ON_RESOURCE then dbms_output.put_line('system seems to not functional. Try again later.'); when others then raise; 14

15 Процедура "checkintersections" Процедура проверяет, если ошибкт в расписании такие, что преподаватель или студент должен быть на двух занятиях одновременно. create or replace procedure checkintersections is cday number(1,0); ctime number(5,0); cprep number(3,0); cursor grp_list is select distinct grp from grp_less; cursor less_list(grp_id number) is select * from lessons where id in (select lesson from grp_less where grp = grp_id) order by day,tstart; cursor less_list_p is select * from lessons order by prep,day,tstart; begin for cgrp in grp_list loop cday := 0; ctime := 0; for cless in less_list(cgrp.grp) loop if (cless.day = cday AND cless.tstart > ctime) then dbms_output.put_line('error found! Day:' cday '; groupid:' cgrp.grp); end if; cday := cless.day; ctime := cless.tend; end loop; end loop; cprep := 0; cday := 0; ctime := 0; for cless in less_list_p loop if (cless.prep = cprep AND cless.day = cday AND cless.tstart > ctime) then dbms_output.put_line('error found! Day:' cday '; prepid:' cprep); end if; cprep := cless.prep; cday := cless.day; 15

16 ctime := cless.tend; end loop; exception end; when TIMEOUT_ON_RESOURCE then dbms_output.put_line('system seems to not functional. Try again later.'); when others then raise; 5.7. Создание триггеров Триггер "validateprep" Проверяет, что преподаватель, который будет проводить занятие, владеет прежметом занятия. create or replace trigger validateprep before insert or update on lessons for each row declare n number(1,0) := 0; begin select count(*) into n from prep_subj where prep=:new.prep and subj=:new.subj; if (n=0) then raise_application_error (-20001, 'This lecturer can not teach the subject'); end if; end; Триггер "lessons_seq_key_i" Служит для автоматического назначения идентификаторов занятий в соответствии с последовательностью. create or replace trigger lessons_seq_key_i before insert on lessons for each row begin select tasks_seq.nextval into :new.id from dual; exception end; when dup_val_on_index then dbms_output.put_line('please, let the system to create IDs automatically'); raise; Триггер "lessons_seq_key_u" Служит для предотвращения изменения идентификаторов занятий, назначенных ранее в соответствии с последовательностью. create or replace trigger lessons_seq_key_u before update of id on lessons for each row begin 16

17 raise_application_error (-20001, 'Please, dont change id'); end; Триггеры, аналогичные последним двум, создаются для каждой таблицы, для которой необходимо автоматическое назначение суррогатных первичных ключей в соответствии с последовательностями. 17