6. Große Übung

(1)

Jan-Christoph Kalo Florian Plötzky

Institut für Informationssysteme

Technische Universität Braunschweig www.ifis.cs.tu-bs.de

6. Große Übung

Relational Calculus

(2)

• Finde die Vornamen aller Studenten mit einem besseren Prüfungsergebnis als 2.7

Relational Database Systems 1 –Wolf-Tilo Balke –Institut für Informationssysteme –TU Braunschweig 2

Allquantor und Implikation

mat firstname lastname sex

1005 Clark Kent m

2832 Louise Lane f

4512 Lex Luther m

5119 Charles Xavier m

6676 Erik Magnus m

8024 Jeanne Gray f

9876 Logan m

student course result

9876 100 3.7

2832 102 2.0

1005 101 4.0

1005 100 1.3

6676 102 4.3

5119 101 1.7

Student

exam

𝑠. 𝑓𝑖𝑟𝑠𝑡𝑛𝑎𝑚𝑒

𝑆𝑡𝑢𝑑𝑒𝑛𝑡 𝑠 ∧ ∃𝒆 (𝑒𝑥𝑎𝑚 𝑒 ∧ 𝑠. 𝑚𝑎𝑡 = 𝑒. 𝑠𝑡𝑢𝑑𝑒𝑛𝑡 ∧ 𝑒. 𝑟𝑒𝑠𝑢𝑙𝑡 < 2.7)}

(3)

• Finde alle Vornamen von Studenten, die ausschließlich bessere Prüfungsergebnisse als 2.7 erzielt haben.

Allquantor und Implikation

1005 Clark Kent m

2832 Louise Lane f

4512 Lex Luther m

6676 Erik Magnus m

8024 Jeanne Gray f

9876 Logan m

9876 100 3.7

2832 102 2.0

1005 101 4.0

1005 100 1.3

6676 102 4.3

5119 101 1.7

Student

exam

𝑆𝑡𝑢𝑑𝑒𝑛𝑡 𝑠 ∧ ∀𝒆 (𝑒𝑥𝑎𝑚 𝑒 ∧ 𝑠. 𝑚𝑎𝑡 = 𝑒. 𝑠𝑡𝑢𝑑𝑒𝑛𝑡 ⇒ 𝑒. 𝑟𝑒𝑠𝑢𝑙𝑡 < 2.7)}

(4)

• Finde die Vornamen aller Studenten mit einem besseren Prüfungsergebnis als 2.7

• Finde alle Vornamen von Studenten, die ausschließlich bessere Prüfungsergebnisse als 2.7 erzielt haben.

Allquantor und Implikation

𝑆𝑡𝑢𝑑𝑒𝑛𝑡 𝑠 ∧ ∀𝒆 (𝑒𝑥𝑎𝑚 𝑒 ∧ 𝑠. 𝑚𝑎𝑡 = 𝑒. 𝑠𝑡𝑢𝑑𝑒𝑛𝑡 ⇒ 𝑒. 𝑟𝑒𝑠𝑢𝑙𝑡 < 2.7)}

𝑆𝑡𝑢𝑑𝑒𝑛𝑡 𝑠 ∧ ∃𝒆 (𝑒𝑥𝑎𝑚 𝑒 ∧ 𝑠. 𝑚𝑎𝑡 = 𝑒. 𝑠𝑡𝑢𝑑𝑒𝑛𝑡 ∧ 𝑒. 𝑟𝑒𝑠𝑢𝑙𝑡 < 2.7)}

𝑆𝑡𝑢𝑑𝑒𝑛𝑡 𝑠 ∧ ∀𝒆 (¬𝑒𝑥𝑎𝑚 𝑒 ∨ 𝑠. 𝑚𝑎𝑡 ≠ 𝑒. 𝑠𝑡𝑢𝑑𝑒𝑛𝑡) ∨ 𝑒. 𝑟𝑒𝑠𝑢𝑙𝑡 < 2.7}

oder

(5)

• Was tut dieser Query?

Allquantor und Implikation

1005 Clark Kent m

2832 Louise Lane f

4512 Lex Luther m

6676 Erik Magnus m

8024 Jeanne Gray f

9876 Logan m

9876 100 3.7

2832 102 2.0

1005 101 4.0

1005 100 1.3

6676 102 4.3

5119 101 1.7

Student

exam

𝑆𝑡𝑢𝑑𝑒𝑛𝑡 𝑠 ∧ ∀𝒆 (𝑒𝑥𝑎𝑚 𝑒 ⇒ 𝑠. 𝑚𝑎𝑡 = 𝑒. 𝑠𝑡𝑢𝑑𝑒𝑛𝑡 ∧ 𝑒. 𝑟𝑒𝑠𝑢𝑙𝑡 < 2.7)}

(6)

• Mit dem eben gezeigten nun nochmal das Divisionsbeispiel aus der Vorlesung

Division revisited

(7)

Grundidee

• Wir wollen alle Studenten haben, die mindestens die Kurse gehört haben, die Clark Kent gehört hat.

• Wir müssen also wissen:

– Welche Kurse hat Clark Kent gehört?

– Welche anderen Kurse hat ein spezifischer Student gehört?

– Finden sich für alle Tupel von C.K. Joinpartner zu allen Kursen des derzeitigen Studenten?

matNr name crsNr

1003 Charles Xavier C103 1003 Charles Xavier C100

matNr name crsNr

1000 Clark Kent C100

1000 Clark Kent C102

(8)

t₁. matNr, t₁. name SC(t₁) ∧ F₁(t₁)}

F₁ t₁ = ∀t₂( SC t₂ ∧ t₂. name =^′ Clark Kent^′ → F₂ t₁ )

F₂ t₁ = ∃t₃(SC(t₃) ∧ t₃. matNr = t₁. matNr ∧ t₃. crsNr = t₂. crsNr)

Aufbau (Mit Implikation)

(9)

t₁. matNr, t₁. name SC( ) ∧ F₁( )}

F₁ t₁ = ∀t₂( SC t₂ ∧ t₂. name =^′ Clark Kent^′ → F₂ )

F₂ t₁ = ∃t₃(SC(t₃) ∧ t₃. matNr = t₁. matNr ∧ t₃. crsNr = t₂. crsNr)

Beispiel

Binde t₂ auf Clark Kent: d.h. wir haben alle C.K.

Kurse

Binde t₃ auf

Lex Luther Joinversuch

für alle Kurse von C.K.

(10)

• Im Grunde identisch zu der Implikation auf der Vorfolie aber umgeformt:

– SC t

₂

∧ t

₂

. name =

^′

Clark Kent

^′

→ F

₂

t

₁

– ¬SC(t

₂

) ∨ ¬t

₂

. name =

^′

Clark Kent

^′

∨ F

₂

(t

₁

)

Aufbau (Vorlesung)

(11)

Es gilt folgendes Schema:

Kunde(kunr, vorname, name, geburtsdatum) Konto(ktnr, kunde → Kunde, kontotyp)

Buchung(konto → Konto, bid, datum, uhrzeit, kennzeichen, betrag, FremdesKonto → Konto)

Formulieren Sie TRC-Queries für die angegebenen Fragestellungen

Aufgabe 1

(12)

Geben Sie die Kontonummern aller Konten des Kunden „Lex Luther“ an.

Aufgabe 1.1

ko. ktnr, Konto ko ∧

∃𝑘𝑢 (Kunde ku

∧ ko. kunde = ku. kunr ∧ ku. vorname =^′ Lex′ ∧ ku. name

= ′Luther′)}

Kunde(kunr, vorname, name, geburtsdatum) Konto(ktnr, kunde→ Kunde, kontotyp)

Buchung(konto→ Konto, bid, datum, uhrzeit, kennzeichen, betrag, FremdesKonto→ Konto)

(13)

Finden Sie die Vornamen der ältesten Kunden.

Aufgabe 1.2

ku. vorname Kunde ku ∧ ¬∃ ku2 (Kunde ku2 ∧

ku. kunr ≠ ku2. kunr ∧ ku2.geburtsdatum < ku.geburtsdatum)}

ku. vorname Kunde ku ∧ ∀ku2: ¬(Kunde ku2 ∧

ku. kunr ≠ 𝑘𝑢2. kunr ∧ ku2.geburtsdatum < ku.geburtsdatum)}

ku. vorname Kunde ku ∧ ∀ku2: (¬Kunde ku2 ∨

ku. kunr = 𝑘𝑢2. kunr ∨ku2.geburtsdatum ≥ ku.geburtsdatum)}

ku. vorname Kunde ku ∧ ∀ku2: (Kunde ku2 ∧

ku. kunr ≠ 𝑘𝑢2. kunr ⇒ ku2.geburtsdatum ≥ ku.geburtsdatum)}

Buchung(konto→ Konto, bid, datum, uhrzeit, kennzeichen, betrag,

oder

(14)

Geben Sie die Nachnamen aller Kunden zurück, die sowohl über ein Sparkonto als auch über ein Girokonto verfügen.

Aufgabe 1.3

ku. name Kunde ku ∧

∃ko₁( Konto ko₁ ∧ ko₁. kunde = ku. kunr ∧ ko₁. kontotyp =^′ Sparkonto^′) ∧

∃ko₂(Konto ko₂ ∧ ko₂. kunde = ku. kunr ∧ ko₂. kontotyp =^′ Girokonto^′)}

Kunde(kunr, vorname, name, geburtsdatum) Konto(ktnr, kunde → Kunde, kontotyp)

Buchung(konto→ Konto, bid, datum, uhrzeit, kennzeichen, betrag, FremdesKonto → Konto)

(15)

Geben Sie die Nachnamen aller Kunden zurück, die sowohl über ein Sparkonto als auch über ein Girokonto verfügen.

Aufgabe 1.3 (Alternative)

ku. name Kunde ku ∧ ∃ko₁, ko₂(Konto ko₁ ∧ Konto ko₂

∧ ko₁. kunde = ko₂. kunde = ku. kunr

∧ ko₁. kontotyp =^′ Girokonto^′

∧ ko₂. kontotyp =^′ Sparkonto′)}

(16)

Geben Sie Vor- und Nachnamen aller Kunden zurück, die über kein Konto verfügen.

Aufgabe 1.4

ku. vorname, ku. name Kunde ku ∧ ¬∃ko(Konto(ko)

∧ ko. kunde = ku. kunr)}

(17)

Finden Sie die Vornamen und Nachnamen aller ältesten

Kunden auf deren Konten noch nie Geld abgebucht wurde.

Aufgabe 1.5

ku. vorname, ku. name Kunde ku ∧

¬∃ ku2 (Kunde ku2 ∧ ku. kunr ≠ ku2. kunr ∧ ku2.geburtsdatum < ku.geburtsdatum ∧

¬∃ko (Konto ko ∧ ko. kunde = ku. kunr ∧

∃b (Buchung b ∧ b. konto = ko. kontonr ∧ Kennzeichen = ^′ Abbuchung′)))}

ku. vorname, ku. name Kunde ku ∧

¬∃ ku2 (Kunde ku2 ∧ ku. kunr ≠ ku2. kunr ∧ ku2.geburtsdatum < ku.geburtsdatum ∧ F(ku))}

F ku = ¬∃ko(Konto ko ∧ ko. kunde = ku. kunr ∧

∃b (Buchung b ∧ b. konto = ko. kontonr ∧ Kennzeichen = ^′ Abbuchung^′))

oder

(18)

Wir übersetzen die TRC-Queries aus 1.1-1.3 in DRC, es gilt daher dasselbe Schema:

Kunde(kunr, vorname, name, geburtsdatum) Konto(ktnr, kunde → Kunde, kontotyp)

Buchung(konto → Konto, bid, datum, uhrzeit, kennzeichen, betrag, FremdesKonto → Konto)

Aufgabe 2

(19)

Aufgabe 2.1

Geben Sie die Kontonummern aller Konten des Kunden „Lex Luther“ an.

ko. ktnr Konto ko ∧ ∃ku: (Kunde ku ∧ ko. kunde = ku. kunr ∧ ku. vorname =^′ Lex′ ∧ ku. name = ′Luther′)}

ktnr ∃kun, ktyp (Konto ktnr, kun, ktyp

∧ ∃kunr, vn, nam, gebd(

)

Kunde kunr, vn, nam, gebd

∧ kunr = kun ∧ vn =

^′

Lex

^′

∧ nam =

^′

Luther

^′

}

(20)

Aufgabe 2.1 (mit Shortcuts)

Geben Sie die Kontonummern aller Konten des Kunden „Lex Luther“ an.

ko. ktnr, Konto ko ∧ ∃ku: (Kunde ku ∧ ko. kunde = ku. kunr ∧ ku. vorname =^′ Lex′ ∧ ku. name = ′Luther′)}

ktnr ∃kun, ktyp (Konto ktnr, kun, ktyp

∧ ∃gebd Kunde 𝐤𝐮𝐧,

^′

𝐋𝐞𝐱

^′

,

^′

𝐋𝐮𝐭𝐡𝐞𝐫′, gebd }

(21)

Aufgabe 2.2

Finden Sie die Vornamen der ältesten Kunden.

vn ∃kunr, n, gebdat (Kunde kunr, vn, n, gebdat

∧ ¬∃k2, vn2, n2, geb2 (Kunde(k2, vn2, n2, geb2)

∧ kunr ≠ k2 ∧ geb2 < gebdat))}

(22)

Aufgabe 2.3

Geben Sie die Nachnamen aller Kunden zurück, die sowohl über ein Sparkonto als auch über ein Girokonto verfügen.

n ∃k, v, g (Kunde(k, v, n, g)

∧ ∃kt1(Konto kt1, 𝐤,

^′

𝐒𝐩𝐚𝐫𝐤𝐨𝐧𝐭𝐨

^′

)

∧ ∃kt2(Konto kt2, 𝐤,

^′

𝐆𝐢𝐫𝐨𝐤𝐨𝐧𝐭𝐨

^′

))}

(23)

• SQL

– Queries (nächste Woche)

• SELECT

– Data Manipulation Language (nach der Weihnachtspause)

• INSERT

• UPDATE

• DELETE

– Data Definition Language (nach der Weihnachtspause)

• CREATE TABLE

• ALTER TABLE

• DROP TABLE