Проектирование реляционных баз данных

Чтобы преобразовать ER-модель в реляционную модель, необходимо выполнить следующие действия:

1. Каждой сущности ER-модели ставится в соответствие отношение реляционной модели, при этом каждому атрибуту сущности ставится в соответствие атрибут отношения реляционной модели. Ключ сущности становится первичным ключом соответствующего отношения

(PRIMARY KEY). Имена  сущностей и отношений, равно  как и атрибутов, могут не  совпадать. Желательно при указании  имен отношений и атрибутов реляционной модели использовать латиницу, поскольку эти имена чаще всего являются идентификаторами в некотором языке про-

граммирования.

2. В каждое  отношение, соответствующее подчиненной  сущности, добавляется набор атрибутов,  соответствующий ключу основной  сущности, если, конечно, он там  не присутствовал. В любом случае  этот на бор атрибутов становится внешним ключом в подчиненном отношении

(FOREIGN KEY).

3. При обязательном  характере связи у атрибутов,  соответствующих внешнему ключу,  устанавливается свойство отсутствия  неопределенных значений (NOT NULL).

4. Если в ER-модели  имеются связи «многие ко многим»,  то их надо преобразовать в связи «один ко многим», поскольку связи «многие ко многим» в реляционной модели не допускаются. Для этого в реляционную модель добавляется связующее отношение, атрибуты которого соответствуют атрибутам первичных ключей обоих отношений, участвующих в связи «многие ко многим». Связующее отношение будет находиться в связи «один ко многим» с каждым из этих отношений. В рассматриваемом примере связь «один ко многим» имеют сущности «преподаватели» и «дисциплины». В реляционной модели вводится связующее отношение, атрибутами которого будут «id_subject» и «tab_num». Первый атрибут соответствует первичному ключу сущности «дисциплины», а второй – первичному ключу сущности «преподаватели». Это отношение будет иметь связь «один ко многим» с отношениями, соответствующими сущностям «преподаватели» и «студенты» (рис. 2).

В принципе, уже  на уровне ER-модели можно провести ее нормализацию. В данной работе требуется, чтобы модель удовлетворяла условиям третьей нормальной формы.

 

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

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

у составного первичного ключа.

ER-модель

 

 

 

 

Реляционная модель

Рис. 2

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

ной формы удовлетворяет.

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

ряют условиям третьей нормальной формы.

Полученную  реляционную модель изобразим в  графической форме. Каждому отношению будет соответствовать прямоугольник, в который будут вписаны имена атрибутов отношения и их типы. Набор возможных типов определяется официальным стандартом языка SQL, однако у конкретных СУБД обычно имеются расхождения со стандартом. В нашей модели атрибуты могут принимать текстовые, числовые значения или представляют собой какую-либо дату. Рассмотрим эти типы

подробнее:

1. Тип даты  вообще не определен в стандарте  SQL и в каждой СУБД определяется по-своему. В настоящей работе для создания таблиц используется СУБД MS ACCESS, в которой для указания даты и времени имеется тип Date.

2. Для представления  строчных атрибутов используется  стандартный тип Character(n) (сокращенно CHAR(n)), поддерживаемый любой СУБД. Здесь n – максимальная длина атрибута в символах. Длину каждого строчного атрибута необходимо согласовывать с заказчиком, но в данной работе ее можно выбирать произвольно, по усмотрению разработчика.

3. Для числовых  атрибутов можно использовать  тип integer, если атрибут может принимать только целочисленные значения, или тип float, если атрибут может иметь дробную часть.

 

На рис. 3 приведена  реляционная модель для нашего примера. В ней 6 отношений, причем 5 из них соответствуют 5 сущностям ER-модели, а 6 отношение teachers_ subjects появилось в результате преобразования связи «многие ко многим» между сущностями «преподаватели» и «дисциплины» к двум связям «один ко многим» между отношениями subjects и teachers_ subjects, а также teachers и teachers_ subjects.

Рис.3

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

1. Связь между  отношениями teachers и groups является  связью один к одному, поскольку только один из преподавателей может быть куратором группы. Однако эта связь рассматривается как частный случай связи «один ко многим», причем отношение groups является подчиненным, так

как значение атрибута curator (табельный номер преподавателя) берется из табельных номеров отношения teachers. Таким образом, атрибут curator подчиненного отношения groups является внешним ключом для первичного ключа tab_num главного отношения teachers.

 

Создание  таблиц базы данных

 

Та часть  языка SQL, которая служит для создания объектов базы данных, называется DDL (Data Definition Language). Основными объектами любой базы данных являются таблицы, соответствующие отношениям реляционной модели. Для создания таблицы употребляется оператор CREATE TABLE. Синтаксис команды имеет следующий вид:

CREATE TABLE <имя таблицы> (<имя столбца> <тип данных столбца>[<ограничения столбца>] [,(<определение столбца> <тип данных>[<ограничения столбца>]..][<определения ограничений таблицы>])

Имена столбцов и их типы для каждой таблицы приведены  на рис. 3.

На столбцы  в данной работе могут быть наложены следующие ограничения:

1) ограничение  NOT NULL, означающее, что значение столбца должно быть обязательно задано при вводе новой записи. По умолчанию обычно действует ограничение NULL, по которому задавать значение для данного столбца не обязательно;

2) ограничение UNIQUE, означающее, что в данной таблице не должно быть двух и более записей с одинаковым значением этого столбца;

3) ограничение  первичного ключа PRIMARY KEY. Это ограничение  для столбца возможно в том  случае, если первичный ключ таблицы состоит из одного данного столбца. В противном случае столбцы, входящие в первичный ключ, указываются в конце всего оператора CREATE TABLE в ограничениях для всей таблицы. То, что столбец является первичным ключом, означает также и то, что его значение должно быть обязательно задано (ограничение NOT NULL), и это значение должно быть уникальным для данной таблицы (ограничение UNIQUE);

4) ограничение  внешнего ключа REFERENCES <ИМЯ ГЛАВНОЙ ТАБЛИЦЫ>. Оно накладывается на столбец в том случае, если он является внешним ключом в связи «один ко многим» с другой, главной в данной связи, таблицей. В этом случае любое значение данного столбца должно соответствовать некоторому значению первичного ключа главной

таблицы.

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

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

Ниже приведены  операторы CREATE TABLE для создания каждой таблицы рассматриваемого примера:

1) таблицы subjects:

Create table subjects(id_subj integer primary key, name char(100));

2) таблицы teacher:

Create table teachers(tab_num char(8) primary key, fio char(50) not null);

3) таблицы teachers_ subjects, связывающей таблицы teachers и subjects:

Create table teachers_ subjects (id_subj integer references subjects, tab_num char(6) references teachers, constraint pk primary key(id_subj, tab_num)).

Здесь столбцы id_subj и tab_num заданы как внешние ключи для главных таблиц subjects и teachers соответственно, а совокупность столбцов id_subj и tab_num объявлена как первичный ключ таблицы, причем ограничение первичного ключа имеет имя pk;

4) таблицы groups:

Create table groups(id_group char(8) primary key, name char(100), tab_num char(8) references teachers).

Здесь первый столбец  таблицы является первичным ключом, второй – название группы, третий столбец является внешним ключом, ссылающимся на таблицу teachers;

5) таблицы students:

Create table students(nz char(6) primary key, fio char(50) not null, id_group char(8) references groups).

При создании таблицы  для столбца fio (фамилия) указано ограничение not null. Это означает, что при вводе новой записи значение для этого столбца должно быть указано обязательно.

6) таблицы marks:

Create table marks(nz char(6) references students, id_subj integer references subjects, tab_num char(8) references teachers, dat date , val integer not null, constraint pmk primary key (nz, id_subj, tab_num, dat)).

Здесь первые три  столбца являются внешними ключами, ссылающимися на первичные ключи  родительских таблиц students, subjects и teachers, а первые четыре столбца в совокупности образуют составной первичный ключ, имеющий имя pmk.

Для дальнейшей работы необходимо в программе MS ACCESS создать новую базу данных. Назовем  ее LAB1_BD.mdb. Для этого необходимо выполнить пункт основного меню Файл/Создать/Новая база данных. Для создания запроса в окне базы данных необходимо во вкладке «Запросы», предназначенной для создания запросов к данным, выбрать пункт «Создать», затем пункт «Создание запроса в режиме конструктора» и в появившемся окне «Новый запрос» выбрать первый пункт «Конструктор» (рис. 4).

Затем надо закрыть  появившееся окно «Добавление таблицы» и, выполнив пункт основного меню «Вид/Режим SQL», перейти к режиму непосредственного ввода текста запроса на языке

SQL (рис. 5).

Далее в окне запроса необходимо ввести его текст  и затем выполнить, выбрав пункт основного меню «Запрос/Запуск». Запросы (их в данной работе будет шесть) желательно сохранять, давая им имена соответственно таблицам, которые они создают. Например, первый запрос, создающий таблицу subjects, желательно сохранить под именем create_subjects.

Рис. 4

Рис. 5

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

Рис. 6

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

 

 

Примеры описания предметной области

1)

 

Агентство занимается продажей авиабилетов на различные рейсы, ведет учет проданных билетов и учет пассажиров, купивших билеты, поэтому возникает потребность в хранении и обработке данных, сгруппированных следующим образом:

1) информация  о расписании рейсов (номер рейса,  тип самолета, пункт отправления, пункт назначения, дата вылета, время вылета, время полета, цена билета);

2) информация  о свободных местах на рейс (номер рейса, дата вылета, общее  количество мест, количество свободных  мест);

3) информация  о пассажирах, купивших билеты на рейсы (номер паспорта, фамилия, имя, отчество, номер рейса, дата вылета);

4) архив, в  который помещается информация о выполненном рейсе (номер рейса, дата вылета, общее количество мест, количество проданных мест).

 

2)

 

Издательство  – предприятие, занимающееся выпуском разнообразной печатной продукции. Издательство заключает договор с заказчиком (клиентом) на выполнение заказа. Заказчиком может выступать частное лицо или организация. Частное лицо может быть автором издания (или

одним из авторов, если их несколько) или представителем автора.

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