Кафедра ИУ5 МГТУ им. Н.Э.Баумана, студенческое сообщество - Вклад [ru]

ТОРА (9) - Семинар №1 - Операции реляционной алгебры

2016-09-26T17:02:25Z

213.87.153.58: /* Задача №7 */

Операции реляционной алгебры и их связь с SQL.

== Операторы SQL ==

=== SELECT ===

SELECT - выборка, DISTINCT - исключая.

<syntaxhighlight lang=sql>SELECT [DISTINCT] * | атрибуты
FROM таблицы
WHERE условие</syntaxhighlight>

=== UPDATE ===

UPDATE - обновление, изменение.

<syntaxhighlight lang=sql>UPDATE таблица
SET атрибут = выражение
WHERE условие</syntaxhighlight>

=== INSERT ===

INSERT - вставка, добавление новых записей.

<syntaxhighlight lang=sql>INSERT
INTO таблица
записи</syntaxhighlight>

=== DELETE ===

DELETE - удаление записей.

<syntaxhighlight lang=sql>DELETE
FROM таблица
WHERE условие</syntaxhighlight>

== Предметная область, используемая в задачах ==

Этого семинара, само собой.

=== Таблицы ===

Таблицы {{Формула|f=\rho=(S,P,SP)}}

*{{Формула|f=S}} - поставщики.
**SN - номер поставщика;
**SF - фамилия;
**SS - статус;
**SG - город.

*{{Формула|f=P}} - деталь.
** PN - номер детали, ключ;
** PF - название детали;
** PC - цена за единицу.

*{{Формула|f=SP}} - поставка.
** SN - номер поставщика;
** PN - номер детали;
** kol - количество поставляемых деталей.

=== Примеры экземпляров отношений ===

{|
| valign="top" |
{| class="wikitable"
|+ {{Формула|f=S}}
! SN || SF || SS || SG
|- align="center"
| S1 || Иванов || 80 || Москва
|- align="center"
| S2 || Петров || 40 || Самара
|- align="center"
| S3 || Кротов || 100 || Москва
|}
|
| valign="top" |
|
{| class="wikitable"
|+ {{Формула|f=P}}
! PN || PF || PC
|- align="center"
| P1 || болт || 20
|- align="center"
| P2 || гайка || 25
|- align="center"
| P3 || шайба || 10
|- align="center"
| P4 || гайка || 30
|}
|
| valign="top" |
|
{| class="wikitable"
|+ {{Формула|f=SP}}
! SN || PN || kol
|- align="center"
| S1 || P1 || 100
|- align="center"
| S1 || P3 || 200
|- align="center"
| S2 || P3 || 150
|- align="center"
| S3 || P3 || 50
|}
|}

== Задачи ==

=== Задача №1 ===

Проекция. Найти города, где проживают поставщики.

{{Формула|f=t=\Pi_{SG}(S)}}

Получится:

{| class="wikitable"
! SG
|- align="center"
| Москва
|- align="center"
| Самара
|}

<syntaxhighlight lang=sql>SELECT DISTINCT SG FROM S</syntaxhighlight>

=== Задача №2 ===

Селекция. Найти поставщиков со статусом больше 70.

{{Формула|f=t=\sigma_{SS>70}(S)}}

Получится:

{| class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S1 || Иванов || 80 || Москва
|- align="center"
| S3 || Кротов || 100 || Москва
|}

<syntaxhighlight lang=sql>SELECT * FROM S WHERE SS > 70</syntaxhighlight>

=== Задача №3 ===

Найти номера и фамилии поставщиков со статусом меньше 80.

{{Формула|f=t=\Pi_{SN,SF}(\sigma_{SS<80}(S))}}

Получится:

{| class="wikitable"
! SN || SF
|- align="center"
| S2 || Петров
|}

<syntaxhighlight lang=sql>SELECT SN, SF
FROM S
WHERE SS < 80</syntaxhighlight>

=== Задача №4 ===

Удалить все поставки поставщика с номером S1.

{{Формула|f=SP = SP - \sigma_{SN=S1}(SP)}}

Получится:

{|+SP | class="wikitable"
! SN || PN || kol
|- align="center"
| S2 || P3 || 150
|- align="center"
| S3 || P3 || 50
|}

<syntaxhighlight lang=sql>DELETE
FROM SP
WHERE SN = S1</syntaxhighlight>

=== Задача №5 ===

Добавить в таблицу S новых поставщиков из таблицы SNEW.

{|+SNEW | class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S4 || Петров || 30 || Тверь
|- align="center"
| S5 || Сидоров || 50 || Тверь
|}

{{Формула|f=S = SP\bigcup S}}

Получится:

{|+ таблица SNEW | class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S1 || Иванов || 80 || Москва
|- align="center"
| S2 || Петров || 40 || Самара
|- align="center"
| S3 || Кротов || 100 || Москва
|- align="center"
| S4 || Петров || 30 || Тверь
|- align="center"
| S5 || Сидоров || 50 || Тверь
|}

<syntaxhighlight lang=sql>INSERT
INTO S
SELECT * FROM SNEW</syntaxhighlight>

=== Задача №6 ===

Найти номера поставщиков, поставляющих детали по цене меньше 30.

{{Формула|f=r = \sigma_{PC<30}(P)}}

{{Формула|f=t = SP \bowtie_{PN} r }}

Получится:

{| class="wikitable"
! PN !! PF !! PC !! SN !! kol
|- align="center"
| P1 || болт || 20 || S1 || 100
|- align="center"
| P3 || шайба || 10 || S1 || 200
|- align="center"
| P3 || шайба || 10 || S2 || 150
|- align="center"
| P3 || шайба || 10 || S3 || 50
|}

{{Формула|f=z = \Pi_{SN}(t)}}

Получится:

{| class="wikitable"
! SN
|- align="center"
| S1
|- align="center"
| S2
|- align="center"
| S3
|}

<syntaxhighlight lang=sql>SELECT DISTINCT SN
FROM SP, P
WHERE PC < 30 AND SP.PN = P.PN</syntaxhighlight>

=== Задача №7 ===

Найти Фамилии поставщиков, поставляющих шайбы в количестве больше 100.

{{Формула|f=w = \sigma_{PF=шайба}(P)}}

{{Формула|f=t = w \bowtie_{PN}SP }}

{{Формула|f=z = \sigma_{kol>100}(t)}}

{{Формула|f=q = z \bowtie_{SN} S}}

{{Формула|f=x = \Pi_{SF}(q)}}

Получится:

{| class="wikitable"
! SF
|- align="center"
| Иванов
|- align="center"
| Петров
|}

<syntaxhighlight lang=sql>SELECT SN, SF
FROM S, P, SP
WHERE PF = 'шайба' AND kol > 100 AND P.PN = SP.PN AND SP.SN = S.SN</syntaxhighlight>

=== Задача №8 ===

Все поставщики переехали из Москвы в Питер. Модифицировать таблицу S.

{{Формула|f=t = \sigma_{SS=Москва}(S)}}, получится:

{| class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S1 || Иванов || 80 || Москва
|- align="center"
| S3 || Кротов || 100 || Москва
|}

{{Формула|f=\Pi_{SS,SF,SS}(t)}}, получится:

{| class="wikitable"
! SN || SF || SS
|- align="center"
| S1 || Иванов || 80
|- align="center"
| S3 || Кротов || 100
|}

Ввели новую таблицу spb:

{| class="wikitable"
!SG
|- align="center"
| Санкт-Петербург
|}

{{Формула|f=m = f\times spb}}, получится:

{| class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S1 || Иванов || 80 || Санкт-Петербург
|- align="center"
| S3 || Кротов || 100 || Санкт-Петербург
|}

{{Формула|f=z = S - t}}, получится:

{| class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S2 || Петров || 40 || Самара
|}

{{Формула|f=S = m \bigcup z}}, получится:

{| class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S1 || Иванов || 80 || Санкт-Петербург
|- align="center"
| S3 || Кротов || 100 || Санкт-Петербург
|- align="center"
| S2 || Петров || 40 || Самара
|}

<syntaxhighlight lang=sql>
UPDATE S
SET SG='Санкт Петербург'
WHERE SG='Москва'
</syntaxhighlight>

[[Категория:Теоретические основы реляционной алгебры (9 семестр)|С]]
[[Категория:Конспекты лекций и семинаров]]

ТОРА (9) - Семинар №1 - Операции реляционной алгебры

2016-09-26T17:01:56Z

213.87.153.58: /* Задача №7 */

Операции реляционной алгебры и их связь с SQL.

== Операторы SQL ==

=== SELECT ===

SELECT - выборка, DISTINCT - исключая.

<syntaxhighlight lang=sql>SELECT [DISTINCT] * | атрибуты
FROM таблицы
WHERE условие</syntaxhighlight>

=== UPDATE ===

UPDATE - обновление, изменение.

<syntaxhighlight lang=sql>UPDATE таблица
SET атрибут = выражение
WHERE условие</syntaxhighlight>

=== INSERT ===

INSERT - вставка, добавление новых записей.

<syntaxhighlight lang=sql>INSERT
INTO таблица
записи</syntaxhighlight>

=== DELETE ===

DELETE - удаление записей.

<syntaxhighlight lang=sql>DELETE
FROM таблица
WHERE условие</syntaxhighlight>

== Предметная область, используемая в задачах ==

Этого семинара, само собой.

=== Таблицы ===

Таблицы {{Формула|f=\rho=(S,P,SP)}}

*{{Формула|f=S}} - поставщики.
**SN - номер поставщика;
**SF - фамилия;
**SS - статус;
**SG - город.

*{{Формула|f=P}} - деталь.
** PN - номер детали, ключ;
** PF - название детали;
** PC - цена за единицу.

*{{Формула|f=SP}} - поставка.
** SN - номер поставщика;
** PN - номер детали;
** kol - количество поставляемых деталей.

=== Примеры экземпляров отношений ===

{|
| valign="top" |
{| class="wikitable"
|+ {{Формула|f=S}}
! SN || SF || SS || SG
|- align="center"
| S1 || Иванов || 80 || Москва
|- align="center"
| S2 || Петров || 40 || Самара
|- align="center"
| S3 || Кротов || 100 || Москва
|}
|
| valign="top" |
|
{| class="wikitable"
|+ {{Формула|f=P}}
! PN || PF || PC
|- align="center"
| P1 || болт || 20
|- align="center"
| P2 || гайка || 25
|- align="center"
| P3 || шайба || 10
|- align="center"
| P4 || гайка || 30
|}
|
| valign="top" |
|
{| class="wikitable"
|+ {{Формула|f=SP}}
! SN || PN || kol
|- align="center"
| S1 || P1 || 100
|- align="center"
| S1 || P3 || 200
|- align="center"
| S2 || P3 || 150
|- align="center"
| S3 || P3 || 50
|}
|}

== Задачи ==

=== Задача №1 ===

Проекция. Найти города, где проживают поставщики.

{{Формула|f=t=\Pi_{SG}(S)}}

Получится:

{| class="wikitable"
! SG
|- align="center"
| Москва
|- align="center"
| Самара
|}

<syntaxhighlight lang=sql>SELECT DISTINCT SG FROM S</syntaxhighlight>

=== Задача №2 ===

Селекция. Найти поставщиков со статусом больше 70.

{{Формула|f=t=\sigma_{SS>70}(S)}}

Получится:

{| class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S1 || Иванов || 80 || Москва
|- align="center"
| S3 || Кротов || 100 || Москва
|}

<syntaxhighlight lang=sql>SELECT * FROM S WHERE SS > 70</syntaxhighlight>

=== Задача №3 ===

Найти номера и фамилии поставщиков со статусом меньше 80.

{{Формула|f=t=\Pi_{SN,SF}(\sigma_{SS<80}(S))}}

Получится:

{| class="wikitable"
! SN || SF
|- align="center"
| S2 || Петров
|}

<syntaxhighlight lang=sql>SELECT SN, SF
FROM S
WHERE SS < 80</syntaxhighlight>

=== Задача №4 ===

Удалить все поставки поставщика с номером S1.

{{Формула|f=SP = SP - \sigma_{SN=S1}(SP)}}

Получится:

{|+SP | class="wikitable"
! SN || PN || kol
|- align="center"
| S2 || P3 || 150
|- align="center"
| S3 || P3 || 50
|}

<syntaxhighlight lang=sql>DELETE
FROM SP
WHERE SN = S1</syntaxhighlight>

=== Задача №5 ===

Добавить в таблицу S новых поставщиков из таблицы SNEW.

{|+SNEW | class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S4 || Петров || 30 || Тверь
|- align="center"
| S5 || Сидоров || 50 || Тверь
|}

{{Формула|f=S = SP\bigcup S}}

Получится:

{|+ таблица SNEW | class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S1 || Иванов || 80 || Москва
|- align="center"
| S2 || Петров || 40 || Самара
|- align="center"
| S3 || Кротов || 100 || Москва
|- align="center"
| S4 || Петров || 30 || Тверь
|- align="center"
| S5 || Сидоров || 50 || Тверь
|}

<syntaxhighlight lang=sql>INSERT
INTO S
SELECT * FROM SNEW</syntaxhighlight>

=== Задача №6 ===

Найти номера поставщиков, поставляющих детали по цене меньше 30.

{{Формула|f=r = \sigma_{PC<30}(P)}}

{{Формула|f=t = SP \bowtie_{PN} r }}

Получится:

{| class="wikitable"
! PN !! PF !! PC !! SN !! kol
|- align="center"
| P1 || болт || 20 || S1 || 100
|- align="center"
| P3 || шайба || 10 || S1 || 200
|- align="center"
| P3 || шайба || 10 || S2 || 150
|- align="center"
| P3 || шайба || 10 || S3 || 50
|}

{{Формула|f=z = \Pi_{SN}(t)}}

Получится:

{| class="wikitable"
! SN
|- align="center"
| S1
|- align="center"
| S2
|- align="center"
| S3
|}

<syntaxhighlight lang=sql>SELECT DISTINCT SN
FROM SP, P
WHERE PC < 30 AND SP.PN = P.PN</syntaxhighlight>

=== Задача №7 ===

Найти Фамилии поставщиков, поставляющих шайбы в количестве больше 100.

{{Формула|f=w = \sigma_{PF=шайба}(P)}}

{{Формула|f=t = w \bowtie_{PN}SP }}

{{Формула|f=z = \sigma_{kol>100}(t)}}

{{Формула|f=q = z \bowtie_{SN} S}}

{{Формула|f=x = \Pi_{SF}(q)}}

Получится:

{| class="wikitable"
! SN || SF
|- align="center"
| S1 || Иванов
|- align="center"
| S2 || Петров
|}

<syntaxhighlight lang=sql>SELECT SN, SF
FROM S, P, SP
WHERE PF = 'шайба' AND kol > 100 AND P.PN = SP.PN AND SP.SN = S.SN</syntaxhighlight>

=== Задача №8 ===

Все поставщики переехали из Москвы в Питер. Модифицировать таблицу S.

{{Формула|f=t = \sigma_{SS=Москва}(S)}}, получится:

{| class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S1 || Иванов || 80 || Москва
|- align="center"
| S3 || Кротов || 100 || Москва
|}

{{Формула|f=\Pi_{SS,SF,SS}(t)}}, получится:

{| class="wikitable"
! SN || SF || SS
|- align="center"
| S1 || Иванов || 80
|- align="center"
| S3 || Кротов || 100
|}

Ввели новую таблицу spb:

{| class="wikitable"
!SG
|- align="center"
| Санкт-Петербург
|}

{{Формула|f=m = f\times spb}}, получится:

{| class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S1 || Иванов || 80 || Санкт-Петербург
|- align="center"
| S3 || Кротов || 100 || Санкт-Петербург
|}

{{Формула|f=z = S - t}}, получится:

{| class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S2 || Петров || 40 || Самара
|}

{{Формула|f=S = m \bigcup z}}, получится:

{| class="wikitable"
! SN || SF || SS || SG
|- align="center"
| S1 || Иванов || 80 || Санкт-Петербург
|- align="center"
| S3 || Кротов || 100 || Санкт-Петербург
|- align="center"
| S2 || Петров || 40 || Самара
|}

<syntaxhighlight lang=sql>
UPDATE S
SET SG='Санкт Петербург'
WHERE SG='Москва'
</syntaxhighlight>

[[Категория:Теоретические основы реляционной алгебры (9 семестр)|С]]
[[Категория:Конспекты лекций и семинаров]]