Файл: Курсовой проект по дисциплине Базы данных Вариант 2 Выполнил студент группы 143303с Шкуренко В. А.docx
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 30.11.2023
Просмотров: 77
Скачиваний: 1
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
Министерство образования и науки Российской Федерации
Федеральное государственное бюджетное образовательное учреждение
высшего образования
Псковский государственный университет
Институт инженерных наукКафедра информационно-коммуникационных технологий
________________________________________________Курсовой проект по дисциплине«Базы данных»Вариант 2Выполнил студентгруппы 1433-03сШкуренко В.А.Проверил преподаватель:Вертешев А.С.Псков2022ЗаданиеТема: работа туристического агентства, имеющего филиалы в различных странах.Объекты: страны, филиалы, клиенты, договора, маршруты.Для моделирования задачи необходимо хранить следующую информацию:* Страна, в которой действует филиал агентства* N филиала агентства* Адрес филиала агентства* N договора, заключенного филиалом агентства с клиентом* Дата начала действия договора* Дата окончания действия договора* Количество мест в договоре* Маршрут, на который заключен договор* ФИО клиента, заключившего договор* Паспортные данные клиента* Страна проживания клиентаДополнительные условия:* в одной стране может находиться несколько филиалов;* у одного клиента могут быть заключены договоры с разными филиалами турагенства;* один маршрут может проходить по нескольким странам.Схема БД
2. route - список маршрутов (название, адрес и id страны)
3. filial – филиалы турфирмы
4. client – клиенты заключившие договор
5. dogovor - список участников конкретной выставки. Вспомогательная таблица для обеспечения связи многие ко многим между “shows” и “organizations”
Кодпосозданиютаблиц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);Код заполнения таблицТаблица ClientInsert 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’);Таблица CountryInsert into country values (1,’Russia’),(2,’Poland’), (3,’Belarusia’), (4,’France’), (5,’Germany’),(6,’USA’),(7,’Italia’);Таблица filialInsert 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);Таблица RouteInsert 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);Таблица DOGOVORCreate 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=client.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 joinselect 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)Selectselect *,(select fio from client where dogovor.client_id=client.id) as client from dogovor;fromturfirma=# select data_start,data_stop from (select*from dogovor) as dogovor;whereselect *from route where id in (select id from route where id>3);havingselect filial_id, count (client_id) from dogovor group by filial_id having count (filial_id)=1;
Федеральное государственное бюджетное образовательное учреждение
высшего образования
Псковский государственный университет
Институт инженерных наукКафедра информационно-коммуникационных технологий
________________________________________________Курсовой проект по дисциплине«Базы данных»Вариант 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 страны |
| Поле | Тип | ключ | Описание |
| id | integer | PK | Идентификатор записи |
| Num_filial | Char(20) | Нет | Номер филиала |
| address | text | | Адрес филиала |
| country_Id | integer | FK | Id страны |
| Поле | Тип | ключ | Описание |
| id | integer | PK | Идентификатор записи |
| fio | text | Внешний | Фамилия клиента |
| passport | Char(20) | нет | Номер паспорта |
| country_of_residanse | integer | Нет | Место проживания |
| Поле | Тип | ключ | Описание |
| 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);Код заполнения таблицТаблица ClientInsert 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’);Таблица CountryInsert into country values (1,’Russia’),(2,’Poland’), (3,’Belarusia’), (4,’France’), (5,’Germany’),(6,’USA’),(7,’Italia’);Таблица filialInsert 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);Таблица RouteInsert 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);Таблица DOGOVORCreate 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=client.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 joinselect 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)Selectselect *,(select fio from client where dogovor.client_id=client.id) as client from dogovor;fromturfirma=# select data_start,data_stop from (select*from dogovor) as dogovor;whereselect *from route where id in (select id from route where id>3);havingselect filial_id, count (client_id) from dogovor group by filial_id having count (filial_id)=1;