Задание на контрольную работу

ЗАДАНИЕ НА КОНТРОЛЬНУЮ РАБОТУ

Постройте инфологическую концептуальную модель (ER-модель), для чего:

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

определите требуемый набор атрибутов для каждой сущности, выделив идентифицирующие атрибуты;

проклассифицируйте сущности (стержневые, ассоциативные и пр.);

определите связи между объектами, включая связи "супертип-подтип", где это необходимо;

формализуйте связи между объектами (множественность, условность и т. д.);

опишите полученную ER-модель на языке инфологического проектирования и на языке ER-диаграмм.

2.  Получите реляционную схему из ER-модели, для чего:

постройте набор необходимых отношений базы данных;

выделите первичные и внешние ключи определенных отношений;

приведите полученные отношения к третьей нормальной форме;

определите ограничения целостности для внешних ключей отношений и для отношений в целом;

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

3.  Используя имеющуюся СУБД создайте спроектированную базу данных.

4.  На языке SQL запишите выражения для указанных в варианте задания типов запросов. Проверьте работоспособность написанных запросов в интерактивном режиме.

5.  Выбрав средства разработки приложений, реализуйте законченное приложение, работающее с созданной базой данных (DELPHI).

Приложение должно

заносить информацию в таблицы созданной базы данных;

выполнять необходимые действия по модификации и удалению данных в таблицах созданной базы данных;

поддерживать целостность базы данных, выполняя все действия в рамках транзакций;

выполнять запросы из варианта задания.

Вариант №29. Известно расписание движения скорого поезда от Иркутска до Красноярска.

Станция	Расстояние от Иркутска (в км)	Прибытие	Отправление
Иркутск	0	18 ч 55 мин
Ангарск	39	19 ч 43 мин	19 ч 45 мин
Усолье	67	20 ч 08 мин	20 ч 10 мин
Черемхово	130	21 ч 04 мин	21 ч 06 мин
Залари	195	21ч 59 мин	22 ч 01 мин
Зима	250	22 ч 54 мин	23 ч 14 мин
Куйтун	310	0 ч 06 мин	0 ч 08 мин
Тулун	389	1 ч 14 мин	1 ч 16 мин
Нижнеудинск	506	2 ч 47 мин	2 ч 02 мин
Тайшет	669	5 ч 40 мин	5 ч 45 мин
Иланская	808	7 ч 50 мин	7 ч 10 мин
Канск	840	8 ч 43 мин	8 ч 45 мин
Заозерная	921	9 ч 54 мин	9 ч 56 мин
Красноярск	1087	12 ч 51 мин

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

1) Найти суммарное время движения поезда без учета времени стоянок.

2) Найти максимальное время стоянки.

3) Найти длину участка в километрах между указанными станциями.

4) Найти участок с максимальной скоростью движения.

5) Найти время следования поезда между двумя указанными станциями.

ПРИМЕР!!!

ПРИМЕР ВЫПОЛЕНИЯ КОНРОЛЬНОЙ РАБОТЫ

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

Необходимо:

       1. Создать схему базы данных и нормализовать её.

       2. Создать и заполнить таблицы в выбранной СУБД так, чтобы в ответах на запросы получались не пустые таблицы.

       3. Получить ответы на запросы:

       1) какие породы рыб водятся в указанном озере;

       2) в каких озёрах водятся ценные породы рыб;

       3) в каких озёрах максимальное поголовье рыб;

       4) в каких озёрах хищники преобладают;

       5) какая порода рыб содержится только в одном озере;

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

       Решение. Как видно из задания - в базе данных необходимо записывать информацию об озёрах (LAKERS) и о разводимой в этих озерах рыбе (FISHES). Озёра и рыбы представляют собой основные сущности, о которых необходимо хранить информацию.

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

Используя эту связь, можно ответить на следующие вопросы:

- какие породы рыб водятся в указанном озере;

       - в каких озёрах водятся ценные породы рыб;

       - в каких озёрах максимальное поголовье рыб;

       - в каких озёрах хищники преобладают;

       - какая порода рыб содержится только в одном озере;

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

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

Сущности FISHES и LAKES, а также связь SUMMARY обладают свойствами. Например, у озера есть название, глубина, площадь, степень прозрачности; у рыбы – название, степень хищности, цена; у связи между озером и рыбой можно выделить такое свойство как количество рыб. Некоторые свойства сущностей являются ключевыми, то есть уникальными для данной сущности (например, название озера и порода рыбы должны быть ключевыми, а у сущности-связи ключевым является свойство FISH_ID; LAKE_ID (идентификатор озера и идентификатор рыбы), образующее составной ключ).

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

       FISHES { FISH_ID, NAME, COST, RAPACITY }

primary key { FISH_ID }

       LAKES { LAKE_ID, NAME, AREA, DEPTH, LIGHT }

               primary key { LAKE_ID }

       SUMMARY { FISH_ID, LAKE_ID, NUMBER }

               primary key { FISH_ID, LAKE_ID }

Учитывая все вышесказанное можно построить ER-диаграмму (модель «сущность/связь») или модель Чена (Рис. 1).

Описание таблицы FISHES:

CREATE TABLE Fishes (

  fish_id  smallint NOT NULL PRIMARY KEY,

  name  varchar(50) NOT NULL,

  cost  smallint NOT NULL,

  rapacity  smallint NOT NULL

);

Описание таблицы LAKES:

CREATE TABLE lakes (

  lake_id  smallint NOT NULL PRIMARY KEY,

  name  varchar(50) NOT NULL,

  area  smallint NOT NULL,

  depth  smallint NOT NULL,

  light  smallint NOT NULL

);

Описание таблицы SUMMARY:

CREATE TABLE Summary (

  fish_id  smallint NOT NULL,

  lake_id  smallint NOT NULL,

  number  smallint NOT NULL

) PRIMARY KEY (fish_id, lake_id)

  FOREIGN KEY (fish_id) REFERENCES Fishes

  FOREIGN KEY (lake_id) REFERENCES Lakes;

Для управления базой данных была разработана программа-оболочка. Программа разрабатывалась в среде Microsoft Visual Studio на языке C++ с применением ActiveX компонентов. Интерфейс программы приведен на Рис. 2.

Краткое описание меню программы. Основное окно содержит в себе поля для ввода запросов, окно для оперирования ключевыми словами update, insert, replace, delete, а также окно для выводов результатов запросов. При оперировании с данными в этой форме используется дескриптор, полученный от классов ODBC.

При нажатии на кнопку Table Editor рождается дочерняя форма несущая в себе ActiveX компоненты ADO и DataGrid. Доступ к данным из этой формы осуществляется через OLE провайдер и имеется возможность непосредственного редактирования данных без использования SQL.

SQL-запросы:

1) какие породы рыб водятся в указанном озере:

select name

from fishes

where fish_id in (select fish_id

  from summary

  where lake_id in (select lake_id

  from lakes

  where name = 'Озеро1'))

2) в каких озёрах водятся ценные породы рыб:

select name

from lakes

where lake_id in (select lake_id

  from summary

  where fish_id in(select fish_id

  from fishes

  where cost >60))

3) в каких озёрах максимальное поголовье рыб:

select name

from lakes

where lake_id in (select lake_id

  from summary

  where number = (select max(number) from summary))

4) в каких озёрах хищники преобладают:

select distinct s. lake_id

from summary s

where (select sum(s2.number)

  from summary s2, fishes f2

  where f2.rapacity > 5 and

  s2.fish_id = f2.fish_id and

  s. lake_id = s2.lake_id)>(select sum(s3.number)

  from summary s3, fishes f3

  where f3.rapacity <= 5 and

  s3.fish_id = f3.fish_id

  and s. lake_id = s3.lake_id)

5) какая порода рыб содержится только в одном озере:

select name

from fishes

where fish_id = (select distinct s. fish_id

  from summary s

  where (select count (s1.fish_id)

  from summary s1

  where s1.fish_id = s. fish_id) = 1)

6) найти самое светлое озеро, в котором водится заданная порода рыб:

select name

from lakes

where lake_id in (select lake_id

  from summary

  where fish_id = (select fish_id

  from fishes

  where name = 'Рыба5' and

  light = (select min(light)

  from lakes, summary, fishes

where lakes. lake_id = summary. lake_id and fishes. fish_id = summary. fish_id and fishes. name = 'Рыба5')))