Практическое задание номер 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
Автор
1/--страниц