Министерство образования и науки Российской Федерации
Федеральное государственное бюджетное образовательное учреждение
высшего образования
Псковский государственный университет
Институт инженерных наук

Кафедра информационно-коммуникационных технологий
________________________________________________

Курсовой проект по дисциплине

«Базы данных»

Вариант 2

Выполнил студент

группы 1433-03с

Шкуренко В.А.

Проверил преподаватель:

Вертешев А.С.

Псков

2022

Задание

Тема: работа туристического агентства, имеющего филиалы в различных странах.
Объекты: страны, филиалы, клиенты, договора, маршруты.
Для моделирования задачи необходимо хранить следующую информацию:

* Страна, в которой действует филиал агентства

* N филиала агентства

* Адрес филиала агентства

* N договора, заключенного филиалом агентства с клиентом

* Дата начала действия договора

* Дата окончания действия договора

* Количество мест в договоре

* Маршрут, на который заключен договор

* ФИО клиента, заключившего договор

* Паспортные данные клиента

* Страна проживания клиента
Дополнительные условия:

* в одной стране может находиться несколько филиалов;

* у одного клиента могут быть заключены договоры с разными филиалами

турагенства;

* один маршрут может проходить по нескольким странам.

Схема БД

Перечень и Структура таблиц

1. country - cписок стран (только название)

Поле	Тип	ключ	Описание
id	integer	РК	Идентификатор записи
title	text	Нет	Название страны

---

2. route - список маршрутов (название, адрес и id страны)

Поле	Тип	ключ	Описание
id	integer	РК	Идентификатор записи
title	text	Нет	Название организации
country_id	integer	FK	Id страны

3. filial – филиалы турфирмы

Поле	Тип	ключ	Описание
id	integer	PK	Идентификатор записи
Num_filial	Char(20)	Нет	Номер филиала
address	text		Адрес филиала
country_Id	integer	FK	Id страны

4. client – клиенты заключившие договор

Поле	Тип	ключ	Описание
id	integer	PK	Идентификатор записи
fio	text	Внешний	Фамилия клиента
passport	Char(20)	нет	Номер паспорта
country_of_residanse	integer	Нет	Место проживания

5. dogovor - список участников конкретной выставки. Вспомогательная таблица для обеспечения связи многие ко многим между “shows” и “organizations”

Поле	Тип	ключ	Описание
Num_dogovor	integer	PK	Идентификатор записи
Data_start	date	нет	Выставка (ссылка на выставку из таблицы “shows”)
Data_stop	date	нет	Участник выставки (ссылка на организацию из таблицы “organizations”)
number_of_seats	integer	нет	Кол-во мест в договоре
Filial_id	integer	FK	Id филиала
Client_id	integer	FK	Id клиента
Route_id	integer	FK	Id маршрута

---

Кодпосозданиютаблиц

1)Create table country (id integer primary key, title text not null);

2)Create table route (id integer primary key, title text not null, country_id integer references country(id) on delete no action on update cascade);

3)Create table filial (id integer primary key, num_filial char(20) not null, address text not null, country_id integer references country(id) on delete no action on update cascade);

4)Create table client (id integer primary key, fio text not null, passport char(20) not null, country_of_residanse text not null);

5)Create table dogovor ( num_dogovor serial primary key, data_start date not null, data_stop date not null,number_of_seats integer not null, filial_id integer references filial(id) on delete no action on update cascade, client_id integer references client(id) on delete no action on update cascade, route_id integer references route(id) on delete no action on update cascade);



Код заполнения таблиц

Таблица Client

Insert into client values (1,’Shkurenko’,5812345123,’Pskov’),(2,’Popov’,5812345654,’Pskov’),(3,’Parusov’,5812332123,’Moscov’),(4,’Ivanov’,5812857123,’Sankt-Peterburg’),(5,’Smirnov’,5812384623,’Moscov’),(6,’Pupkin’,5812345124,’Vel.Luki’);



Таблица Country

Insert into country values (1,’Russia’),(2,’Poland’), (3,’Belarusia’), (4,’France’), (5,’Germany’),(6,’USA’),(7,’Italia’);



Таблица filial

Insert into filial values (1,1,’moscov_sokolovskoe_31’,1),

(2,2,’moscov minskay 5’,1),

(3,3,’minsk kirila turovskogo 4’,2),

(4,4,’milenova 1’,4),

(5,5,’ogorodovay 8’,4);



Таблица Route

Insert into route values (1,’Sochi’,1), (2,’Pskov’,1),( 3,’Penza’,1), (4,’Krakov’,2), (5,’Praga’,2), (6,’Budapesht’,2),(7,’Minsk’, 3),(8,’Skoki’,3), (9,’New_Orlean’,6), (10,’New_York’,6), (11,’Frankfurt’,5), (12,’Gamburg’,5), (13,’Marcel’,7), (14,’Rim’,7);



Таблица DOGOVOR

Create table dogovor ( num_dogovor char(20) primary key, data_start date not null, data_stop date not null,number_of_seats integer not null, filial_id integer references filial(id) on delete no action on update cascade, client_id integer references client(id) on delete no action on update cascade, route_id integer references route(id) on delete no action on update cascade);



Запросы

1)Три запроса на выборку с использованием join, использовать не мене 3 таблиц (3)

Запрос №1:

Выборка номера договора с адресом филиала и заключение договора с клиеном Попов

New_turfirma=# select dogovor.num_dogovor, client.fio, filial.num_filial from dogovor inner join filial on dogovor.filial_id=filial.id inner join client on dogovor.client_id=client.id where client.fio='Popov';

---

Запрос №2:

turfirma=# select filial.address,country.route_id,route.title from filial inner join country on country.route_id=filial.country_id inner join route on country.route_id=country.route_id;

Запрос №3:

turfirma=# select dogovor.num_dogovor, client.fio, filial.address, client.pasport from client inner join dogovor on dogovor.client_id=c

lient.id inner join filial on dogovor.filial_id=filial.id inner join route on dogovor.route_id=route.id;

2) Запросы на объединение, пересечение, разность, произведение, проекцию, деление (6)

Обьединение

turfirma=# select client.fio, filial.address,dogovor.num_dogovor from client,filial,dogovor where dogovor.client_id=client.id;



Пересечение

select r.id from route r intersect (select c.id from country c) intersect (select f.id from filial f);



Разность

turfirma=# select id, title from country except select id,title from route;

произведение

select*from filial,country;

проекция

turfirma=# select distinct filial_id, client_id,route_id from dogovor;

Деление

3) Запросы на соединение: left join, right join, full outer join (3)

left join

select filial_id,route_id,route.title from dogovor left outer join route on dogovor.route_id=route.id order by title;



right join

select filial_id,route_id,route.title from dogovor right outer join route on dogovor.route_id=route.id order by title;



full outer join

select filial_id,client.fio,route_id from dogovor full outer join client on dogovor.client_id=client.id order by fio;



4) 2 подзапроса: простой, сложный (2)

Простой

Выдать все данные по договору клиента Парусов

SELECT * FROM dogovor WHERE client_id IN (SELECT id FROM client WHERE fio = 'Parusov');



Сложный

turfirma=# select num_dogovor, client_id, route_id from dogovor,(select * from (select*from client) as client) as client where dogovor.client_id=client.id order by client desc;

5) Запросы с различными уровнями вложенности подзапросов в частях select, From where, having (4)

Select

select *,(select fio from client where dogovor.client_id=client.id) as client from dogovor;

from

turfirma=# select data_start,data_stop from (select*from dogovor) as dogovor;

where

select *from route where id in (select id from route where id>3);

having

select filial_id, count (client_id) from dogovor group by filial_id having count (filial_id)=1;

---

Смотрите также файлы

• Контрольная работа на промежуточной аттестации 5 класс 1 вариант 1. Выполните действия а б в г д е 2.doc

• Рыночный механизм и его элементы.docx

• Методические указания по проведению практических занятий.docx

• Необходимые инвестиции для открытия и месяца работы автомобильной мойки составляет 460 000 рублей.docx

• Общие требования безопасности при выполнении работ.docx