close

Вход

Забыли?

вход по аккаунту

?

Bazy dannykh PZ 1 otchet(1)

код для вставкиСкачать
Практическое задание номер 1 "Реляционная Алгебра" Кустов Николай Андреевич (МОиАИС - ПРГ1.3)
R1ФилиалСтранаГородБерлинскийГерманияБерлинВладимирскийРоссияВладимирМосковскийРоссияМоскваПарижскийФранцияПарижПитерскийРоссияСанкт-ПетербургФранкфуртскийГерманияФранкфурт
R2КлиентНомер_договораСтранаБерингБГ-01ГерманияБерингБГ-02ГерманияИвановИР-01РоссияЛюпенЛФ-01ФранцияЛюпенЛФ-02ФранцияПетровПР-01РоссияПетровПР-02РоссияСидоровСР-01РоссияСидоровСР-02РоссияСидоровСР-03РоссияСидоровСР-04РоссияСидоровСР-05РоссияСидоровСР-06РоссияСидоровСР-07РоссияШульцШГ-01ГерманияШульцШГ-02ГерманияШульцШГ-03ГерманияR3Номер_договораФилиалДата_началаДата_окончанияБГ-01Московский01.01.201301.02.2013БГ-02Московский01.05.201301.06.2013ИР-01Питерский02.01.201302.02.2013ЛФ-01Московский03.01.201303.02.2013ЛФ-02Франкфуртский03.03.201303.04.2013ПР-01Питерский04.01.201304.02.2013ПР-02Питерский04.03.201304.04.2013СР-01Питерский05.01.201305.02.2013СР-02Питерский05.03.201305.04.2013СР-03Франкфуртский05.05.201305.06.2013СР-04Московский05.07.201305.08.2013СР-05Берлинский05.09.201305.10.2013СР-06Парижский05.11.201305.12.2013СР-07Владимирский05.12.201305.01.2014ШГ-01Франкфуртский06.01.201306.02.2013ШГ-02Московский06.03.201306.04.2013ШГ-03Берлинский06.05.201306.06.2013
Задача 1. Клиенты, заключившие договоры с несколькими филлиалами.
1. Объединяем R2 иR3 по номеру договора
Реляционная алгебра:
MS Access
SELECT R2.Клиент, R2.Страна, R2.Номер_договора, R3.Номер_договора, R3.Филиал, R3.Дата_начала, R3.Дата_окончания
FROM R2 INNER JOIN R3 ON R2.Номер_договора = R3.Номер_договора
WHERE (((R2.Номер_договора)=[R3].[Номер_договора]));
R4КлиентСтранаR2.Номер_договораR3.Номер_договораФилиалДата_началаДата_окончанияБерингГерманияБГ-01БГ-01Московский01.01.201301.02.2013БерингГерманияБГ-02БГ-02Московский01.05.201301.06.2013ЛюпенФранцияЛФ-01ЛФ-01Московский03.01.201303.02.2013ИвановРоссияИР-01ИР-01Питерский02.01.201302.02.2013ПетровРоссияПР-01ПР-01Питерский04.01.201304.02.2013ПетровРоссияПР-02ПР-02Питерский04.03.201304.04.2013СидоровРоссияСР-01СР-01Питерский05.01.201305.02.2013СидоровРоссияСР-02СР-02Питерский05.03.201305.04.2013ЛюпенФранцияЛФ-02ЛФ-02Франкфуртский03.03.201303.04.2013СидоровРоссияСР-03СР-03Франкфуртский05.05.201305.06.2013СидоровРоссияСР-04СР-04Московский05.07.201305.08.2013СидоровРоссияСР-05СР-05Берлинский05.09.201305.10.2013СидоровРоссияСР-06СР-06Парижский05.11.201305.12.2013СидоровРоссияСР-07СР-07Владимирский05.12.201305.01.2014ШульцГерманияШГ-01ШГ-01Франкфуртский06.01.201306.02.2013ШульцГерманияШГ-02ШГ-02Московский06.03.201306.04.2013ШульцГерманияШГ-03ШГ-03Берлинский06.05.201306.06.2013
R5КлиентФилиалБерингМосковскийИвановПитерскийЛюпенМосковскийЛюпенФранкфуртскийПетровПитерскийСидоровБерлинскийСидоровВладимирскийСидоровМосковскийСидоровПарижскийСидоровПитерскийСидоровФранкфуртскийШульцБерлинскийШульцМосковскийШульцФранкфуртский2. Делаем проекцию с R4 на атрибуты "Клиент" и "Филиал"
Реляционная алгебра:
MS Access
SELECT DISTINCT R4.[Клиент], R4.[Филиал]
FROM R4;
3. Соединяем отношение R5 с собой по предикату R5.Клиент=R5_1.Клиент And R5.Филиал<>R5_1.Филиал.
R6КлиентФилиалЛюпенМосковскийЛюпенФранкфуртскийСидоровБерлинскийСидоровВладимирскийСидоровМосковскийСидоровПарижскийСидоровПитерскийСидоровФранкфуртскийШульцБерлинскийШульцМосковскийШульцФранкфуртский Таким образом получаем клиентов, которые заключили минимум 2 договора с разными Филиалами, но могли также заключить еще несколько договоров с одним филиалом.
Реляционная алгебра:
MS Access
SELECT DISTINCT R5.Клиент, R5.Филиал
FROM R5, R5 AS R5_1
WHERE (([R5].[Клиент]=[R5_1].[Клиент] And [R5].[Филиал]<>[R5_1].[Филиал]));
4. Проекцией оставляем только Клиентов, заключивших договора с несколькими филиалами
Реляционная алгебра:
R7КлиентЛюпенСидоровШульц
MS Access
SELECT DISTINCT R6.[Клиент]
FROM R6;
Задача 2. Найти филиалы, которые работают с клиентами только одной страны.
1. Соединяем отношение R4 с собой по предикату R4.Филиал=R4_1.Филиал And R4.Страна<>R4_1.Страна.
Получаем Филиалы, которые заключили договора как минимум с двумя клиентами из разных стран, но так же могли заключить еще несколько договоров с одним клиентом.
R8ФилиалБерлинскийМосковскийФранкфуртскийРеляционная алгебра:
MS Access
SELECT DISTINCT R4.Филиал
FROM R4, R4 AS R4_1
WHERE (([R4].[Филиал]=[R4_1].[Филиал]
And [R4].[Страна]<>[R4_1].[Страна]));
2. Вычитаем из отношения всех филиалов, те, которые заключили как минимум два договора с клиентами из разных стран.
Получаем филиалы, которые работают с клиентами только одной страны.
R9ФилиалВладимирскийПарижскийПитерскийРеляционная алгебра:
MS Access
SELECT DISTINCT R1.Филиал
FROM R1 LEFT JOIN R8 ON R1.[Филиал] = R8.[Филиал]
WHERE (((R8.Филиал) Is Null));
Задача 3. Найти Клиентов, которые заключили несколько договоров с одним филиалом.
1. Объединим R4 с предикатом Клиент=Клиент, Филиал=Филиал, Номер_договора<>Номер_договора и получим нужное нам отношение.
R10КлиентФилиалБерингМосковскийПетровПитерскийСидоровПитерский Реляционная алгебра:
R10=ПКлиент,Филиал(R4⋈R4')
R4.Клиент=R4'.Клиент
AND
R4.Номер_договора<>R4'.Номер_договора
AND
R4.Филиал=R4'.Филиал
MS Access
SELECT DISTINCT R4.Клиент, R4.Филиал
FROM R4, R4 AS R4_1
WHERE (([R4].[Клиент]=[R4_1].[Клиент]
And [R4].[Филиал]=[R4_1].[Филиал]
And [R4].[R2]![Номер_договора]<>[R4_1].[R3]![Номер_договора]));
2. Проекция по Клиент даст нам клиентов, которые заключили несколько договоров с одним филиалом.
R11=ПКлиент(R10)
R11КлиентБерингПетровСидоров
Задача 4. Выбрать филиалы, которые заключили договоры только с клиентами из той же страны, в которой расположен филиал.
1. Объединим R1 c R4 по филиалу с условием, что страна филиала не равна стране клиента. Таким образом мы уберем клиентов, которые заключали договора с филиалами из той же страны что и клиент.
Реляционная алгебра:
R12=ПR1.Филиал,R1.Страна, R4.Клиент, R4.Страна, R4.Номер_договора(R1⋈R4)
R4.Клиент=R4'.Клиент
AND
R4.Номер_договора<>R4'.Номер_договора
AND
R1.Филиал=R4.Филиал
MS Access
SELECT DISTINCT R1.Филиал, R1.Страна, R4.Клиент, R4.Страна, R4.R2.Номер_договора
FROM R1 INNER JOIN R4 ON R1.Филиал = R4.Филиал
WHERE (([R1].[Страна]<>[R4].[Страна] And [R1].[Филиал]=[R4].[Филиал]));
R12ФилиалR1.СтранаКлиентR4.СтранаНомер_договораБерлинскийГерманияСидоровРоссияСР-05МосковскийРоссияБерингГерманияБГ-01МосковскийРоссияБерингГерманияБГ-02МосковскийРоссияЛюпенФранцияЛФ-01МосковскийРоссияШульцГерманияШГ-02ПарижскийФранцияСидоровРоссияСР-06ФранкфуртскийГерманияЛюпенФранцияЛФ-02ФранкфуртскийГерманияСидоровРоссияСР-03
2. Вычтем из всех филиалов те, которые заключали договора с клиентами из других стран. Получим филиалы, которые заключили договоры только с клиентами из той же страны, в которой расположен филиал.
Реляционная алгебра:
R13 = ПФилиал(R1)\ ПФилиал(R12)
R13ФилиалПитерскийВладимирский
MS Access
SELECT R1.Филиал
FROM R1 LEFT JOIN R12 ON R1.[Филиал] = R12.[Филиал]
WHERE (((R12.Филиал) Is Null));
Задача 5. Найти Клиентов, которые заключили договоры с разными филиалами - по одному с каждым.
Для этого достаточно вычесть из результатов первой задачи (R7) результаты 3-ей задачи (R11).
Реляционная алгебра:
R14КлиентЛюпенШульцR14 = R7\ R11
MS Access
SELECT DISTINCT R7.Клиент
FROM R7 LEFT JOIN R11 ON R7.[Клиент] = R11.[Клиент]
WHERE (((R11.Клиент) Is Null));
Задача 6. Найти Клиентов, заключивших только один договор.
1. Соединим отношения R7 и R11, чтобы получить тех клиентов, которые заключили несколько договоров с одним филиалом и(или) несколько договоров с разными филиалами.
Реляционная алгебра:
R15КлиентБерингЛюпенПетровСидоровШульц R15 = R7 ∪ R11
MS Access
SELECT R7.Клиент
FROM R7
UNION SELECT R11.Клиент
FROM R11;
2. Вычитаем из R2 Клиентов R15 и получаем Клиентов, заключивших только один договор.
Реляционная алгебра:
R16 = ПКлиент(R2)\ R15
MS Access
R16КлиентИванов SELECT R2.Клиент
FROM R2 LEFT JOIN R15 ON R2.[Клиент] = R15.[Клиент]
WHERE (((R15.Клиент) Is Null));
Задача 7. Найти Клиентов, заключивших договора только с филиалами из той же страны, в которой проживает клиент.
Для решения достаточно из всех клиентов исключить клиентов, заключавших договоры с филиалами из других стран.
Реляционная алгебра:
R17КлиентИвановПетров R17 = ПФилиал(R1)\ ПФилиал(R12)
MS Access
SELECT DISTINCT R2.Клиент
FROM R2 LEFT JOIN R12 ON R2.Клиент = R12.Клиент
WHERE (((R12.Клиент) IS NULL));
Задача 8. Найти филиалы, с которыми заключили договоры только клиенты из других стран.
1. Находим филиалы, которые заключили хотя бы один договор с клиентом из своей страны.
Реляционная алгебра:
R18ФилиалБерлинскийВладимирскийМосковскийПитерскийФранкфуртский R18=ПФилиал(R1⋈R4)
R1.Филиал = R4.Филиал
AND
R1.Страна = R4.Страна
MS Access
SELECT DISTINCT Филиал
FROM R1, R4
WHERE (([R1].[Филиал]=[R4].[Филиал] And [R1].[Страна] = [R4].[Страна]));
2. Вычитаем из всех филиалов те, которые заключали договора с клиентами из своей страны. Получаем филиалы, с которыми заключили договоры только клиенты из других стран.
R19ФилиалПарижский
R19 = ПФилиал(R1)\ R18
Задача 9. Найти филиалы, которые работают с клиентами из нескольких стран.
Для решения задачи достаточно вычесть из множества всех филиалов (ПФилиал(R1)) - те филиалы, которые работают с клиентами только одной страны (R9)
Реляционная алгебра:
R20ФилиалБерлинскийФранкфуртскийМосковскийR20 = ПФилиал(R1)\ R9
MS Access
SELECT R1.Филиал
FROM R1 LEFT JOIN R9 ON R1.[Филиал] = R9.[Филиал]
WHERE (((R9.Филиал) Is Null));
R21КлиентФилиалБерингБерлинскийБерингВладимирскийБерингМосковскийБерингПарижскийБерингПитерскийБерингФранкфуртскийИвановБерлинскийИвановВладимирскийИвановМосковскийИвановПарижскийИвановПитерскийИвановФранкфуртскийЛюпенБерлинскийЛюпенВладимирскийЛюпенМосковскийЛюпенПарижскийЛюпенПитерскийЛюпенФранкфуртскийПетровБерлинскийПетровВладимирскийПетровМосковскийПетровПарижскийПетровПитерскийПетровФранкфуртскийСидоровБерлинскийСидоровВладимирскийСидоровМосковскийСидоровПарижскийСидоровПитерскийСидоровФранкфуртскийШульцБерлинскийШульцВладимирскийШульцМосковскийШульцПарижскийШульцПитерскийШульцФранкфуртскийЗадача 10. Клиенты, которые заключили договоры со всеми филиалами фирмы (1-й способ).
1. Делаем отношение, в котором каждому клиенту соответствует каждый филиал.
Реляционная алгебра:
R21=ПКлиент, Филиал(R1⋈R2)
MS Access
SELECT DISTINCT R2.Клиент, R1.Филиал
FROM R1, R2;
2. Находим Клиентов, которые не заключали договора со всеми филиалами.
R22КлиентБерингИвановЛюпенПетровШульц Реляционная алгебра:
R22 = ПКлиент(R21)\ ПКлиент(R4)
MS Access
SELECT DISTINCT R21.Клиент
FROM R21 LEFT JOIN R4 ON (R21.[Клиент] = R4.[Клиент]) AND (R21.[Филиал] = R4.[Филиал])
WHERE (((R4.Клиент) Is Null));
3. Из всех клиентов вычитаем тех, кто не заключал договоров со всеми филиалами, и находим ответ на вопрос
R23 = ПКлиент(R2)\ R22
R23КлиентСидоров
Вторым способом отношение R23 получается операцией:
R23= ПКлиент(R4÷ Пфилиал(R1))
Задача 11. Клиенты, которые заключили договоры ровно с тремя филиалами.
Если мы возьмём отношение, в котором будут все договора клиентов в соответствии с филиалами, с которыми эти договора были заключены, то выполняя соединение этого отношение на себя с предикатом Клиент=Клиент и Филиал<>Филиал 1 раз мы получим клиентов, заключавших договора более чем с одним филиалом, если 2 раза, то получим клиентов, заключавших договора более чем с 2-мя филиалами, если 3 раза, то получим клиентов, заключавших договора более чем с 3-мя филиалами.
Если мы вычтем из отношения >2Филиалов отношение >3Флиалов, то получим искомых клиентов.
1. Больше двух филиалов
R24=ПКлиент(R6⋈R6'⋈R6'')
R6.Клиент = R6'. Клиент = R6''. Клиент
AND
R6.Филиал <> R6'.Филиал <> R6''.Филиал
AND
R6.Филиал <> R6''.Филиал
SELECT DISTINCT R6.Клиент
FROM R6, R6 AS R6_1, R6 AS R6_2
WHERE (((R6.Клиент)=R6_1.Клиент Аnd (R6.Клиент)=R6_2.Клиент) And ((R6.Филиал)<>R6_1.Филиал And (R6.Филиал)<>R6_2.Филиал) And ((R6_1.Филиал)<>R6_2.Филиал));
R24КлиентСидоровШульц 2. Больше 3-х филиалов
R25=ПКлиент(R6⋈R6'⋈R6''⋈R6''')
R6.Клиент = R6'. Клиент = R6''. Клиент= R6'''. Клиент
AND
R25КлиентСидоров R6.Филиал <> R6'.Филиал <> R6''.Филиал<> R6'''.Филиал
AND
R6.Филиал <> R6''.Филиал AND
R6.Филиал <> R6'''.Филиал AND
R6'.Филиал <> R6'''.Филиал
3. Ровно с 3-мя
R26КлиентШульцR26 = R24\ R25
Документ
Категория
Разное
Просмотров
204
Размер файла
73 Кб
Теги
bazy, dannykh, otchet
1/--страниц
Пожаловаться на содержимое документа