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

Y7	Y6	Y5
+	a
+	a
+	aa
+	aa
-	aa
-	aaa
-	aaa

4. Строим

Y1	Y2	Y3	Y4	Y5	Y6	Y7
i	a	I	x		a	+
i	c	II	x		aaa	+
ii	b	III	y		aa	+
iii	b	IV	z		a	+

Лабораторная работа №3

Анализ качества проекта Базы данных

Цель занятия

Изучение применения реляционной алгебры к оценке качества проекта базы данных:

а) определение множества функциональных зависимостей;

б) построение третьей нормальной формы для набора схем отношений;

в) проверка правильности построения третьей нормальной формы (исследование на свойство соединения без потерь).

Постановка задачи

1.Нормализация схем отношений. В данном случае необходимо:

а)составить схемы исходных отношений;

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

в)определить множество функциональных зависимостей;

г) разработать вторую нормальную форму для каждой схемы либо удостовериться в ее наличии;

д) разработать третью нормальную форму для каждой схемы либо удостовериться в ее наличии.

2. Проверка выполнения нормализации. Здесь необходимо провести исследование полученной декомпозиции на соединение без потерь информации.

Ход работы

1. Нормализация схем отношений:

а)схемы исходных отношений:

Железнодорожный вокзал ¤

Полное название вокзала

Адрес,

Контактный телефон,

Директор;

Сотрудник ¤

№ трудового договора

Фамилия,

Имя,

Отчество,

Дата рождения,

Должность,

Оклад,

Контактный телефон;

Маршрут движения ¤

Название маршрута,

Время отправления

Дата отправления,

Стоимость билета,

№ поезда,

№ платформы;

Пассажир ¤

Личный №

Фамилия,

Имя,

Отчество,

№ и серия паспорта,

Дата покупки билета,

б)атрибуты всех схем отношений являются атомарными, следовательно, схемы Железнодорожный вокзал ( ), Сотрудник ( ), Маршрут движения ( ), Пассажир ( ) находятся в первой нормальной форме;

в)определяем наборы функциональных зависимостей для схем отношений:

Сотрудник ( ):

№ трудового договора ® {¼} – зависимость от ключа,

Должность ® Оклад;

Железнодорожный вокзал ( ):

Полное название вокзала ® {¼} – зависимость от ключа;

Маршрут движения ( ):

Название маршрута, время отправления, дата отправления, ® {¼} – зависимость от ключа,

Пассажир ( ):

Личный № ® {¼} – зависимость от ключа,

г)первичные ключи отношений Железнодорожный вокзал ( ), Сотрудник ( ), Пассажир ( ) являются атомарными, следовательно, каждая схема соответствует требованиям второй нормальной формы; А в отношении Маршрут движения ( ) отсутствуют функциональные зависимости в которых слева находятся один или два атрибута входящие в состав первичного ключа этого отношения, а справа находятся не ключевые атрибуты, что означает что отношение Маршрут движения ( ) также находится во второй нормальной форме.

д)в схемах Железнодорожный вокзал ( ), Маршрут движения ( ), Пассажир ( ) имеются только зависимости от ключа, поэтому эти схемы находятся в третьей нормальной форме;

В схеме Сотрудник ( ) существует зависимость между не ключевыми атрибутами Должность ® Оклад, таким образом, Сотрудник ( ) не находится в третьей нормальной форме и необходимо провести декомпозицию, образовав две новые схемы:

Сотрудник1(

№ трудового договора,

Фамилия,

Имя,

Отчество,

Дата рождения,

Должность,

Оклад,

Контактный телефон

Полное название вокзала );

Справочник окладов (

Должность,

Оклад);

Полученная в результате декомпозиции совокупность пяти схем отношений Железнодорожный вокзал ( ), Сотрудник1 ( ), Справочник окладов(), Маршрут движения ( ), Пассажир ( ) удовлетворяет требованиям третьей нормальной формы.

2. Проверка выполнения нормализации:

a)для исследования декомпозиции r₁{Сотрудник1( ), Справочник окладов ( )} на соединение без потерь построим следующую таблицу:

После рассмотрения функциональной зависимости Должность ® Оклад будет получена таблица, которая содержит первую строку, состоящую только из значений a, поэтому декомпозиция r₁ обладает свойством соединения без потерь:

Лабораторная работа №4

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

SQL

Цель занятия

Изучение возможностей и практическое использование языка запросов SQL по созданию структуры базы данных.

Постановка задачи

1. Создание структуры таблиц базы данных. Сюда входит:

а) определение имен полей и типов данных;

б) назначение первичных ключей.

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

Ход работы

Создаем структуру реляционной базы данных на основании модели, оптимизированной в лабораторной работе №3.

1.Создание структуры таблиц базы данных:

а)создание таблицы Железнодорожный вокзал (соответствует схеме Железнодорожный вокзал):

CREATE TABLE [Железнодорожный вокзал] (

[Полное название вокзала] TEXT(20),

Адрес TEXT(50),

Телефон TEXT(9),

Директор TEXT(12)

);

создание таблицы Сотрудник (соответствует схеме Сотрудник1):

CREATE TABLE Сотрудник (

[№ трудового договора] NUMBER,

Фамилия TEXT(12),

Имя TEXT(12),

Отчество TEXT(12),

[Дата рождения] DATETIME,

Должность TEXT(10),

[Контактный телефон] TEXT(9),

[Полное название вокзала] TEXT(20)

);

создание таблицы Справочник окладов (соответствует схеме Справочник окладов):

CREATE TABLE [Справочник окладов] (

Должность TEXT(10),

Оклад CURRENCY

);

создание таблицы Маршрут движения (соответствует схеме Маршрут движения):

CREATE TABLE [маршрут движения] (

[Название маршрута] TEXT(30),

[Дата отправления] DATETIME,

[Время отправления] TEXT(10),

[Стоимость билета] CURRENCY,

[№ поезда] NUMBER,

[№ платформы] NUMBER,

[Полное название вокзала] TEXT(20)

);

создание таблицы Пассажир (соответствует схеме Пассажир):

CREATE TABLE Пассажир (

[Личный №] NUMBER,

Фамилия TEXT(12),

Имя TEXT(12),

Отчество TEXT(12),

[№ и серия паспорта] TEXT(30),

[Дата покупки билета] DATETIME,

[Название маршрута] TEXT(30),

[Дата отправления] DATETIME,

[Время отправления] TEXT(10),

);

б)назначение атомарным первичным ключом таблицы Железнодорожный вокзал поля Полное название вокзала:

CREATE INDEX [первичный ключ] ON [Железнодорожный вокзал] ([полное название вокзала])

WITH PRIMARY;

назначение атомарным первичным ключом таблицы Сотрудник поля № трудового договора:

CREATE INDEX [первичный ключ] ON Сотрудник ([№ трудового договора])

WITH PRIMARY;

назначение атомарным первичным ключом таблицы Справочник окладов поля Должность:

CREATE INDEX [первичный ключ] ON [Справочник окладов] (Должность)

WITH PRIMARY;

назначение состовым первичным ключом таблицы Маршрут движения полей Название маршрута, время отправления, дата отправления:

CREATE INDEX [первичный ключ] ON Маршрут (

[Название маршрута],

[Дата отправления],

[Время отправления])

WITH PRIMARY;

назначение атомарным первичным ключом таблицы Пассажир поля Личный №:

CREATE INDEX [первичный ключ] ON Пассажир ([Личный №])

WITH PRIMARY;

2.Установление взаимосвязей:

создание взаимосвязи вида “один-ко-многим” между таблицами Железнодорожный вокзал и Сотрудник:

ALTER TABLE Сотрудник

ADD CONSTRAINT Связь FOREIGN KEY ([Полное название вокзала])

REFERENCES [Железнодорожный вокзал] ([Полное название вокзала]);

создание взаимосвязи вида “один-ко-многим” между таблицами Справочник окладов и Сотрудник:

ALTER TABLE Сотрудник

ADD CONSTRAINT Связь FOREIGN KEY (Должность)

REFERENCES [Справочник окладов] (Должность);

создание взаимосвязи вида “один-ко-многим” между таблицами Железнодорожный вокзал и Маршрут движения:

ALTER TABLE [Маршрут движения]

ADD CONSTRAINT Связь2 FOREIGN KEY ([Полное название вокзала])

REFERENCES [Железнодорожный вокзал] ([Полное название вокзала]);

создание взаимосвязи вида “один-ко-многим” между таблицами Маршрут движения и Пассажир:

ALTER TABLE Пассажир

ADD CONSTRAINT Связь FOREIGN KEY (

[Название маршрута],

[Дата отправления],

[Время отправления])

REFERENCES [Маршрут движения] (

[Название маршрута],

[Дата отправления],

[Время отправления]

);

Сотрудник		Железнодорожный вокзал		Маршрут движения
№ трудового договора		Полное название вокзала		Название маршрута
Фамилия		Адрес		Дата отправления
Имя		Телефон		Время отправления
Отчество		Директор		Стоимость билета
¥ Дата рождения				№ поезда
Должность				№ платформы
Контактный тел.		Пассажир	¥	Полное название вокзала
Полное название вокзала	¥	Личный №
		Фамилия
		Имя
		Отчество
Справочник окладов		№ и серия паспорта
Должность		¥ Дата покупки билета
Оклад		¥ Название маршрута
		¥ Дата отправления
		Время отправления

Лабораторная работа №5