Задание 1. Этап начальной разработки базы данных

 Цель: ознакомиться с понятием «жизненный цикл БД», изучить структуру жизненного цикла БД, научиться выполнять работы на этапе начальной разработки БД.

Ход работы:

1. Анализ деятельности компании/предприятия/организации

Разработать прикладное программное обеспечение деятельности судоходной компании “Балтика”. Эта крупная компания занимается перевозками грузов между континентами. В ее собственности несколько десятков судов различного класса и грузоподъемности. К услугам этой компании прибегают тысячи клиентов из различных стран мира.

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

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

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

Информация о получателе:

1. Название получателя;
2. ИНН получателя груза;
3. Банк получателя груза;
4. Юридический адрес получателя.

Информация об отправителе:

1. Название отправителя;
2. ИНН отправителя;
3. Банк отправителя груза;
4. Юридический адрес отправителя.

Информация о судне:

1. Регистрационные номер судна;
2. Название судна;
3. ФИО капитана судна;
4. Тип судна;
5. Фотография судна;
6. Год постройки судна;
7. Порт приписки;
8. Грузоподъемность судна.

Информация о партии груза судна:

1. Таможенный номер партии

Информация о грузе:

1. Название груза;
2. Заявленная величина груза;
3. Единица измерения груза;
4. Застрахованная величина груза;
5. Отправитель груза;
6. Получатель груза;
7. Примечание.

Информация о маршруте следования:

1. Дата убытия судна;
2. Дата прибытия судна;
3. Пункт отправления;
4. Пункт назначения;
5. Необходимость таможенной проверки;
6. Номер груза в партии.

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

 

Задачами автоматизированной системы являются:

1. Регистрация новых грузов;
2. Регистрация новых отправителей;
3. Регистрация новых получателей;
4. Регистрация новых судов;
5. Оптимизация и систематизация маршрута;
6. Подготовка отчетов о перевозках.
7. Предоставление информации об отслеживании груза.
8. Отслеживание груза.

Вопрос для защиты: как с помощью разрабатываемой базы данных и приложения будет происходить выполнение п. 5?

Задание 2. Концептуальное проектирование базы данных

 

Цель: создание концептуальной модели БД для анализируемой предметной области

Определение сущностей:

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

1. Сущность «Получатель». Содержит информацию обо всех получателях;
2. Сущность «Отправитель». Содержит информацию обо всех отправителях;
3. Сущность «Судно». Содержит информацию обо всех суднах, на которых перевозятся грузы;
4. Сущность «Партия судна груза?». Содержит информацию о партии груза (несколько грузов);
5. Сущность «Груз». Содержит информации обо всех грузах;
6. Сущность «Маршрут следования». Содержит информацию обо всех маршрутах следования судна с партиями грузов.

Определение атрибутов:

Сущность «Получатель» имеет следующие атрибуты: название получателя, ИНН получателя, банк получателя, юридический адрес получателя.  В атрибуте «Название получателя» будет храниться ФИО, либо название компании. Атрибут «Банк получателя» является составным атрибутом, так как там будет храниться информация о городе банка, наименовании банка клиента. «Юридический адрес получателя» также является составным атрибутом, так как в нем будет храниться информация о городе, номере здания, улице.

Сущность «Отправитель» имеет следующие атрибуты: название отправителя, ИНН отправителя, банк отправителя, юридический адрес отправителя.  В атрибуте «Название отправителя» будет храниться ФИО, либо название компании. Атрибут «Банк отправителя» является составным атрибутом, так как там будет храниться информация о городе банка, наименовании банка клиента и т. д. «Юридический адрес отправителя» также является составным атрибутом, так как в нем будет храниться информация о городе, номере здания, улице.

Сущность «Судно» имеет следующие атрибуты: регистрационный номер судна, название судна, ФИО капитана судна, тип судна, фотография судна, год постройки судна, порт приписки и грузоподъемность.

Сущность «Партия судна груза» имеет следующие атрибуты: таможенный номер партии судна груза.

Сущность «Груз» имеет следующие атрибуты: название груза, заявленная величина груза, застрахованная величина груза, отправитель груза, получатель груза и примечание.

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

Включение ключевых атрибутов:

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

У сущности «Получатель» ключевым атрибутом будет являться атрибут «Код получателя» с типом СЧЕТЧИК.  Так как среди атрибутов сущности отсутствуют такие, которые могли бы выполнять роль ключевых как по отдельности, так и в различных их сочетаниях.

У сущности «Отправитель» ключевым атрибутом будет являться атрибут «Код отправителя» с типом СЧЕТЧИК.  Так как среди атрибутов сущности отсутствуют такие, которые могли бы выполнять роль ключевых как по отдельности, так и в различных их сочетаниях.

У сущности «Судно» ключевым атрибутом (первичным ключом) будет являться атрибут «Регистрационный номер», так как значение этого атрибута может уникально идентифицировать экземпляры сущности.

У сущности «Партии судна груза» нет таких атрибутов, которые бы подходили на роль первичного ключа, поэтому следует добавить суррогатный атрибут «Код партии», который будет однозначно идентифицировать каждую партию груза.

У сущности «Груз» нет таких атрибутов, которые могли бы однозначно идентифицировать экземпляр сущности, поэтому также, как и в предыдущем случае, следует добавить суррогатный атрибут «Код груза».

У сущности «Маршрут следования» нет атрибутов, которые могли бы однозначно идентифицировать экземпляр сущности, в сущность будет добавлен суррогатный атрибут «Код движения груза» для однозначной идентификации экземпляра сущности.

Рисунок 90 – Изображение сущностей и их атрибутов в нотации Чена

Определение связей:

Между сущностью «Отправитель» и сущностью «Груз» существует связь «Отправляет», тип «один-ко-многим». Один отправитель может отправить сколько угодно грузов. Данная связь является не идентифицирующей.

Между сущностью «Получатель» и сущностью «Груз» существует связь «Получает», тип «один-ко-многим». Один получатель может получить сколько угодно грузов. Данная связь является не идентифицирующей.

Между сущностью «Маршрут следования» и «Судно» существует связь один ко многим. У одного судна может быть сколь угодно маршрутов следования. Данная связь является идентифицирующей.

Между сущностью «Маршрут следования» и «Груз» существует тип связи «многие-ко-многим» в результате чего создается новая сущность «Партия груза». Так как на судне может находиться несколько партий грузов для различных грузополучателей из различных стран и городов. Одна партия груза может состоять из нескольких разновидностей грузов. У одной партии груза может быть только один отправитель и только один получатель. Данная связь является идентифицирующей.

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

Атрибут «Получатель груза» сущности «Груз» является внешним ключом сущности «Получатель», который ссылается на первичный ключ «Название получателя» сущности «Получатель»

Между сущностями «Груз» и «Партия груза» связь типа «один-ко-многим», в результате в сущность «Груз» добавляется соответственно новый атрибут «Код партии груза», являющийся внешним ключом, который ссылается на первичный ключ «Код партии» сущности «Партия груза». Конкретный груз может принадлежать только к одной партии, но в одной партии может быть много разных грузов. Исправьте на рис. 91 обозначения «1» и «М» для связи «Принадлежит».

Между сущностями «Маршрут следования» и «Партия груза» связь типа «один-ко-многим», в результате в сущность «Маршрут следования» добавляется соответственно новый атрибут «Код партии», являющийся внешним ключом, который ссылается на первичный ключ «Код партии» сущности «Партия груза». Связи в одной ER-диаграмме должны отличаться по именам. Исправьте вторую связь «Принадлежит», например, на «Принадлежит1». А еще лучше подобрать название, которое отражает смысл связи.

Между сущностями «Маршрут следования» и «Судно» связь типа «один-ко-многим», скорее, «многие-ко-многим», иначе у вас получается, что одним и тем же маршрутом следования может идти только одно судно в результате чего в сущность «Маршрут следования» добавляется новый атрибут «Код судна», являющийся внешним ключом, который ссылается на первичный ключ «Регистрационный номер судна» сущности «Судно»

Общие замечания: 1) на данном этапе еще рано добавлять внешние ключи в качестве атрибутов сущностей; 2) на рис. 91 не указаны многозначные атрибуты, мощность связей, обязательность участия сущностей в связи, идентификация; 3) на рис. 92 не указана идентификация связей, также в нотации IDEF1x как правило не указываются символы «1» и «М».

 

В результате добавления связей итоговая ER-диаграмма в нотации Чена представлена на рисунке 91.

Рисунок 91 – Итоговая ER-диаграмма в нотации Чена

 

 

 

Рисунок 92 –Итоговая ER-диаграмма в нотации IDEF1X

 

 

         Задание 3. Логическое проектирование базы данных

 

         Цель: преобразовать концептуальную модель на основе выбранной модели данных в логическую модель, не зависимую от особенностей используемой в дальнейшем СУБД для физической реализации базы данных.

 

Преобразование сущностей

Шаг 1. Работа со сильными сущностями.

В построенной концептуальной модели имеются шесть сильных сущностей:

«Отправитель», «Получатель», «Груз», «Судно», «Маршрут следования», «Партии грузов». В результате выполнения первого шага данные сущности преобразуются в шесть «отношений» (таблиц) с соответствующими названиями: «Отправитель», «Получатель», «Груз», «Судно», «Маршрут следования», «Партии грузов». Количество столбцов в каждом из «отношений» (таблиц) определяется количеством атрибутов соответствующих сущностей. Первичными ключами (PK) будут являться ключевые атрибуты соответствующих сущностей.

Отношение «Отправитель»

Код отправителя (PK)	Название отправителя	ИНН отправителя	Банк отправителя	Юридический адрес отправителя
1	Петров Михаил Андреевич	8348593457894	Доп.офис №509450 ПАО Сбербанк, 328403842034820394,	142100, Московская область, город Подольск, ул. Б. Серпуховская, дом 43, офис 2
2	КазаньЭкспресс	5823095820	Офис №4395540 ПАО Сбербанк, 38520934850239582	124412, Ленинградская область, город Подольск, ул. Б. Серпуховская, дом 43, офис 2
3	…	…	…

 

 

Отношение «Получатель»

Код получателя (PK)	Название получателя	ИНН получателя	Банк получателя	Юридический адрес получателя
1	Озон	3473487349273498	123112, г. Москва, Пресненская наб., д. 10, эт. 41, Пом. I, комн. 6 1027739244741 7704217370	…
2	Аптека от склада	4834985394739	ИНН 5908987207, ОГРН 1145958085954, КПП 590301001.	…
3	…	…	…

 

 

Отношение «Груз»

Код груза(PK)	Название груза	Заявленная величина груза	Застрахованная величина груза	Отправитель груза	Получатель груза	Примечание
1	медикаменты	1000	500	Аптека от склада	Озон	–
2	медикаменты	50000	10000	Здравсити	КазаньЭкспресс	–
3	машина	700000	700000	Дмитрий Олегович Хвостов	Николаева Ольга Владимировна	–
…	…	…	…	…	…	…

 

Отношение «Партия груза»

Код партии (PK)	Таможенный номер партии
1	564645645645
2	234634743227
….	…

 

Отношение «Маршрут следования»

Код движения груза(PK)	Дата убытия	Дата прибытия	Пункт отправления	Пункт прибытия	Номер груза партии	Необходимость таможенной проверки
1	04.04.22	24.04.22	Порт Архангельск	Порт Таганрог	4	да
2	04.04.22	14.04.22	Порт Уфа	Порт Екатеринбург	2	нет
3	04.05.22	10.05.22	Порт Архангельск	Порт Санкт-Петербург	1	да
…	…	…	…	…	…	…

 

Отношение «Судно»

Регистрационный номер судна (PK)	Название судна	ФИО Капитана Судна	Фотография судна	Порт приписки	Тип судна	Грузоподъемность	Год постройки судна
PMOO11-02	Ролкер	Михайлов В.А	Image.jpeg	Порт Москва	сухогруз	10000т	2005
АОГА342-3	Лихтервоз	Лебедева А.А	Image.jpeg	Порт Москва	танкер	20000т	2011
…	…	…	…	…	…	…	…

 

Шаг 2. Работа со слабыми сущностями.

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

 

Преобразование связей. Внести исправления в шаги 3 и 4 в соответствии с замечаниями и исправлениями в ER-диаграмме выше.

Шаг 3. Работа со связями «многие-ко-многим».

В построенной концептуальной модели имеется три??? связи «многие-ко-многим». На рис. 91 и 92 отсутствуют связи «многие-ко-многим»…

Эта связь между сущностями «Груз» и «Маршрут следования» так как на маршруте может находиться партия грузов для отправителя. Связь «многие-ко-многим» создает дополнительную сущность между данными таблицами «Партия грузов».

Для преобразования этой связи необходимо в отношение (таблицу) «Груз», соответствующее сущности «Груз», находящейся на стороне связи «многие» добавить ссылку на таблицу «Партия груза» атрибута «Код партии груза», так как он соответствует ключевому атрибуту сущности «Партия груза», находящейся на стороне «один» рассматриваемой связи. Откуда берется связь «многие-ко-многим», если сущность «Партия груза» находится на стороне «один»?

Данный столбец будет выполнять роль внешнего ключа, реализуя связь между грузом и партией груза. Таким образом, таблица «Груз» изменит свою структуру Здесь вы показали преобразование связи «многие-к-одному».

Код груза(PK)	Название груза	Заявленная величина груза	Застрахованная величина груза	Отправитель груза	Получатель груза	Примечание	Код партии груза (FK)
1	медикаменты	1000	500	Аптека от склада	Озон	–	1
2	медикаменты	50000	10000	Здравсити	КазаньЭкспресс	–	2
3	машина	700000	700000	Дмитрий Олегович Хвостов	Николаева Ольга Владимировна	–	3
…	…	…	…	…	…	…

 

Далее необходимо в отношение (таблицу) «Маршрут следования», соответствующее сущности «Маршрут следования», находящейся на стороне связи «многие» добавить ссылку на таблицу «Партия груза» атрибута «Код партии», так как он соответствует ключевому атрибуту сущности «Партия груза», находящейся на стороне «один» рассматриваемой связи. Данный столбец будет выполнять роль внешнего ключа, реализуя связь между маршрутом следования и партией груза. Таким образом, таблица «Маршрут следования» изменит свою структуру

Код движения груза(PK)	Дата убытия	Дата прибытия	Пункт отправления	Пункт прибытия	Номер груза партии	Необходимость таможенной проверки	Код партии (FK)
1	04.04.22	24.04.22	Порт Архангельск	Порт Таганрог	4	да	1
2	04.04.22	14.04.22	Порт Уфа	Порт Екатеринбург	2	нет	2
3	04.05.22	10.05.22	Порт Архангельск	Порт Санкт-Петербург	1	да	3
…	…	…	…	…	…	…

 

Шаг 4. Преобразование связей «один-ко-многим».

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

Один получатель может получить несколько грузов, но при этом у одной партии груза один получатель. Для преобразования связи «один-ко-многим» необходимо в отношение (таблицу) «Груз», соответствующее сущности «Груз», находящейся на стороне связи «многие» добавить ссылку на таблицу «Получатель» атрибута «Получатель груза», так как он соответствует ключевому атрибуту сущности «Получатель», находящейся на стороне «один» рассматриваемой связи. Данный столбец будет выполнять роль внешнего ключа, реализуя связь между получателем и грузом.

У одного судна может быть несколько маршрутов следования, но в одном маршруте находится только одно судно (конкретный маршрут). Для преобразования связи «один-ко-многим» необходимо в отношение (таблицу) «Маршрут следования», соответствующее сущности «Маршрут следования», находящейся на  стороне связи «многие», добавить дополнительный столбец «Код судна», так как он соответствует ключевому атрибуту сущности «Судно», находящейся на стороне «один» рассматриваемой связи. Данный столбец будет выполнять роль внешнего ключа, реализуя связь между судном и маршрутом следования. Таким образом, таблица «Маршрут следования» изменит свою структуру

 

Код движения груза(PK)	Дата убытия	Дата прибытия	Пункт отправления	Пункт прибытия	Номер груза партии	Необходимость таможенной проверки	Код партии	Код судна
1	04.04.22	24.04.22	Порт Архангельск	Порт Таганрог	4	да	1	3
2	04.04.22	14.04.22	Порт Уфа	Порт Екатеринбург	2	нет	2	1
3	04.05.22	10.05.22	Порт Архангельск	Порт Санкт-Петербург	1	да	3	2

 

Шаг 5. Преобразование связей «один-к-одному»

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

Преобразование многозначных атрибутов

Шаг 6. Работа с многозначными атрибутами.

Сущность «Получатель» и отправитель имеют многозначный атрибут «Юридический адрес <…>».  Атрибут «Юридический адрес <…>»» преобразовывается в отдельное «отношение» (таблицу) Юридический адрес <…>», состоящее из трех столбцов – «id_Получатель», «id_Отправитель» и «Юридический адрес ».

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

«Код юридического адреса», значения которого будут однозначно идентифицировать строки таблицы.

Отношение «Юридический адрес» (итоговый вариант)

Код юридического адреса (PK)	Id_Получатель(FK)	Id_Отправитель(FK)	Юридический адрес
1	1	3	…
2	1	2	…
3	2		…

 

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

Получатель (Код получателя, ИНН получателя, Банк получателя, Название получателя, Код юридического адреса)

Отправитель (Код отправителя, ИНН отправителя, Банк отправителя, Название отправителя, Код юридического адреса)

Груз (Код груза, название груза, заявленная величина груза, застрахованная величина груза, примечание, отправитель груза, получатель груза, код партии груза)

Партия груза (код партии, таможенный номер партии)

Маршрут следования (Код движения груза, дата убытия, дата прибытия, пункт отправления, пункт прибытия, номер груза партии, необходимость таможенной проверки, код партии код судна)

Судно (регистрационный номер судна, название судна, ФИО капитана судна, фотография судна, порт приписки, грузоподъемность, тип судна, год постройки судна)

Юридический адрес (Код юридического адреса, id_Отправитель, id_Получатель, юридический адрес).

 

 

Задание 4. Даталогическое проектирование базы данных.

 

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

Отношение «Отправитель»

Код отправителя (PK)	Название отправителя	ИНН отправителя	Банк отправителя	Юридический адрес отправителя
1	Петров Михаил Андреевич	8348593457894	Доп.офис №509450 ПАО Сбербанк, 328403842034820394	142100, Московская область, город Подольск, ул. Б. Серпуховская, дом 43, офис 2
1	Петров Михаил Андреевич	8348593457894	Доп.офис №509450 ПАО Сбербанк, 450827485592393	239423, Московская область, город Подольск, ул. Б Мира 23
2	Казань-Экспресс	5823095820	Офис №4395540 ПАО Сбербанк, 38520934850239582	124412, Ленинградская область, город Подольск, ул. Б. Серпуховская, дом 43, офис 2
3	Казань-Экспресс	5823095820	Офис №4395540 ПАО Сбербанк, 38520934850239582	124412, Ленинградская область, город Подольск, ул. Б. Серпуховская, дом 43, офис 4

 

Исходная ненормализованная таблица «Отправитель» разделяется на три, находящиеся в 1НФ: таблицу «Банк» и «Юридический адрес», в которой находятся атрибуты повторяющейся группы и атрибут, и суррогатный первичный ключ. Те же самые действия проведены с таблицей «Получатель».

В «отношении» (таблице) «Банк» атрибут «Банк» хранит значения, являющиеся списком: Офис, Название Банка, ИНН, БИК

дома. Для приведения «отношения» «Банк» к 1НФ следует для каждого элемента списка создать свой атрибут/столбец

 

Код банка	Офис	Название банка	ИНН	БИК
1	Офис №4395540	ПАО Сбербанк	38520934850239582	38475728
2	Офис №43235	Альфа- Банк	127379853958293484	39193874
…	…	…	…	…

 

В «отношении» (таблице) «Юридический адрес» атрибут «Юридический адрес» хранит значения, являющиеся списком: Индекс, город, улица, номер дома, офис. Для приведения «отношения» «Банк» к 1НФ следует для каждого элемента списка создать свой атрибут/столбец.

Код_Юр.адрес	Индекс	Город	Название улицы	Номер дома	Офис
1	322463	Москва	Мира	34	1
2	346753	Архангельск	Комсомольский проспект	1
…	…	…	…	…

 

Все отношения находятся во 2НФ и 3НФ, так как находятся в первой нормальной форме, нет частичных и функциональных зависимостей.

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

Отсутствуют справочные таблицы.

Получатель (Код получателя, ИНН получателя, Название получателя, Код юридического адреса, Код банка)

Отправитель (Код отправителя, ИНН отправителя, Название отправителя, Код юридического адреса, Код банка)

Груз (Код груза, название груза, заявленная величина груза, застрахованная величина груза, примечание, отправитель груза, получатель груза, код партии груза)

Партия груза (код партии, таможенный номер партии)

Маршрут следования (Код движения груза, дата убытия, дата прибытия, пункт отправления, пункт прибытия, номер груза партии, необходимость таможенной проверки, код партии код судна)

Судно (регистрационный номер судна, название судна, ФИО капитана судна, фотография судна, порт приписки, грузоподъемность, тип судна, год постройки судна)

Юридический адрес (Код юридического адреса, Индекс, Город, название улицы, номер дома, офис).

Банк (Код банка, Офис, Название банка, ИНН, БИК).

 

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