Views als Abstraktionen in MySQL

Schicht 0 Basistabelle

Stammbaum

  KARL V SUSI  WOLF V ANNA
       |            |
     ----    ----------------
      GERD V LISA KLAUS HEIKE
           |
       ---------
       HANS LARA 

Relationale Repräsentation

DROP SCHEMA PERSONEN; CREATE SCHEMA PERSONEN; USE PERSONEN;
CREATE TABLE PERSON
( PERSON     VARCHAR( 32 ) NOT NULL,
  VATER      VARCHAR( 32 )     NULL,
  MUTTER     VARCHAR( 32 )     NULL,
  GESCHLECHT CHAR( 1 )     NOT NULL,
  PRIMARY    KEY( PERSON ))
COMMENT = "PERSON hat den Vater VATER und die Mutter MUTTER und das Geschlecht GESCHLECHT (m/w)." ;
INSERT INTO PERSON
( PERSON,  VATER,  MUTTER, GESCHLECHT )VALUES
( 'HANS',  'GERD', 'LISA', 'm' ),
( 'LARA',  'GERD', 'LISA', 'w' ),
( 'LISA',  'WOLF', 'ANNA', 'w' ),
( 'KLAUS', 'WOLF', 'ANNA', 'm' ),
( 'HEIKE', 'WOLF', 'ANNA', 'w' ),
( 'GERD',  'KARL', 'SUSI', 'm' );
SELECT * FROM PERSON;

+--------+-------+--------+------------+
| PERSON | VATER | MUTTER | GESCHLECHT |
+--------+-------+--------+------------+
| GERD   | KARL  | SUSI   | m          |
| HANS   | GERD  | LISA   | m          |
| HEIKE  | WOLF  | ANNA   | w          |
| KLAUS  | WOLF  | ANNA   | m          |
| LARA   | GERD  | LISA   | w          |
| LISA   | WOLF  | ANNA   | w          |
+--------+-------+--------+------------+

Schicht 1

Mutter

Aufgabe Erstellen Sie eine Tabelle, welche die Relation „Mutter“ repräsentiert. Sie soll alle bekannten Paare (P,Q) von Personen enthalten, so daß Q die Mutter von P ist.

Lösungsweg Wir müssen zuerst den Begriff „Mutter“ definieren.

Definition Eine Mutter ist ein in der Basistabelle unter Mutter eingetragener Name (der nicht NULL ist).

CREATE VIEW MUTTER AS
SELECT PERSON.PERSON AS PERSON, 
       PERSON.MUTTER AS MUTTER 
FROM   PERSON 
WHERE  PERSON.MUTTER IS NOT NULL;
SELECT * FROM MUTTER;

+--------+--------+
| PERSON | MUTTER |
+--------+--------+
| GERD   | SUSI   |
| HANS   | LISA   |
| HEIKE  | ANNA   |
| KLAUS  | ANNA   |
| LARA   | LISA   |
| LISA   | ANNA   |
+--------+--------+

Vater

Übungaufgabe Definieren Sie einen VIEW VATER, der alle bekannten Paare (P,Q) von Personen enthält, so daß Q der Vater von P ist

Geschwister

Aufgabe Erstellen Sie eine Tabelle, welche die Relation „Geschwister“ repräsentiert. Sie soll alle Paare (P,Q) von Personen enthalten, so daß Q ein Geschwister von P ist.

Lösungsweg Wir müssen zuerst den Begriff „Geschwister“ unter Verwendung schon bekannter Verwandtschaftsbeziehungen definieren.

Definition Ein Geschwister einer Person P ist eine Person Q, so daß gilt:

Der Vater von P ist der Vater von Q.
Die Mutter von P ist die Mutter von Q.
P ist nicht gleich Q.

CREATE VIEW GESCHWISTER AS 
SELECT PERSON.PERSON AS PERSON,
       ANDERE.PERSON AS GESCHWISTER
FROM            PERSON 
     INNER JOIN 
                PERSON AS ANDERE 
ON      PERSON.VATER  = ANDERE.VATER 
    AND 
        PERSON.MUTTER = ANDERE.MUTTER 
WHERE PERSON.PERSON <> ANDERE.PERSON;
SELECT * FROM GESCHWISTER;

+--------+-------------+
| PERSON | GESCHWISTER |
+--------+-------------+
| LARA   | HANS        |
| KLAUS  | HEIKE       |
| LISA   | HEIKE       |
| HEIKE  | KLAUS       |
| LISA   | KLAUS       |
| HANS   | LARA        |
| HEIKE  | LISA        |
| KLAUS  | LISA        |
+--------+-------------+

Bemerkung Da die Gleichheitsbedingung nicht TRUE ist, wenn der Vater oder die Mutter NULL ist, ist es nicht nötig diese Möglichkeit durch kompliziertere Formulierungen auszuschließen.

Übungsaufgabe Im Ergebnis der obenstehenden SELECT-Abfrage kommt jedes Geschwisterpaar zweimal  vor (beispielsweise einmal Lara-Hans und einmal Hans-Lara). Schreiben Sie eine SELECT-Abfrage, welche eine Tabelle der Geschwister ausgibt, in welcher jedes Geschwisterpaar nur einmal  vorkommt.

Elter

Aufgabe Erstellen Sie eine Tabelle, welche die Relation „Elter“ repräsentiert. Sie soll alle Paare (P,Q) von Personen enthalten, so daß Q ein Elter von P ist.

Lösungsweg Wir müssen zuerst den Begriff „Elter“ unter Verwendung schon bekannter Verwandtschaftsbeziehungen definieren.

Definition Ein Elter einer Person P ist eine Person Q, so daß Q der Vater oder die Mutter von P ist.

So, wie die Definition des Begriffs Elter sich auf die Begriffe „Vater“ und „Mutter“ bezieht, kann sich die Definition des VIEWs ELTER auch auf die VIEWs VATER und MUTTER beziehen.

CREATE VIEW ELTER AS
SELECT PERSON, VATER  AS ELTER FROM PERSON
UNION
SELECT PERSON, MUTTER AS ELTER FROM PERSON;
SELECT * FROM ELTER;

+--------+-------+
| PERSON | ELTER |
+--------+-------+
| GERD   | KARL  |
| HANS   | GERD  |
| HEIKE  | WOLF  |
| KLAUS  | WOLF  |
| LARA   | GERD  |
| LISA   | WOLF  |
| GERD   | SUSI  |
| HANS   | LISA  |
| HEIKE  | ANNA  |
| KLAUS  | ANNA  |
| LARA   | LISA  |
| LISA   | ANNA  |
+--------+-------+

Schicht 2

Bruder und Schwester

Aufgabe Erstellen Sie eine Tabelle, welche die Relation „Bruder“ repräsentiert. Sie soll alle Paare (P,Q) von Personen enthalten, so daß Q ein Bruder von P ist.

Lösungsweg Wir müssen zuerst den Begriff „Bruder“ unter Verwendung schon bekannter Verwandtschaftsbeziehungen definieren.

Definition Ein Bruder ist ein männliches Geschwister.

So, wie die Definition des Begriffs Bruder sich auf den Begriff „Geschwister“ bezieht, kann sich die Definition des VIEWs BRUDER auch auf den VIEW GESCHWISTER beziehen.

CREATE VIEW BRUDER AS
SELECT GESCHWISTER.PERSON AS PERSON, 
       GESCHWISTER.GESCHWISTER AS BRUDER 
FROM            GESCHWISTER 
     INNER JOIN 
                PERSON 
ON GESCHWISTER.GESCHWISTER = PERSON.PERSON 
WHERE PERSON.GESCHLECHT = 'm';
SELECT * FROM BRUDER;

+--------+--------+
| PERSON | BRUDER |
+--------+--------+
| LARA   | HANS   |
| HEIKE  | KLAUS  |
| LISA   | KLAUS  |
+--------+--------+

Übungsaufgabe Erstellen Sie ein VIEW SCHWESTER dessen Ergebnis alle Paare (P,Q) von Personen enthält, so daß Q eine Schwester von P ist.

Weitere Schichten

Tante und Onkel

Aufgabe Erstellen Sie eine Tabelle, welche die Relation „Tante“ repräsentiert. Sie soll alle Paare (P,Q) von Personen enthalten, so daß Q eine Tante von P ist.

Aufgabe Erstellen Sie eine Tabelle, welche die Relation „Großmutter“ repräsentiert. Sie soll alle Paare (P,Q) von Personen enthalten, so daß Q eine Großmutter von P ist.

Aufgabe Erstellen Sie eine Tabelle, welche die Relation „Großelter“ repräsentiert. Sie soll alle Paare (P,Q) von Personen enthalten, so daß Q ein Großelter von P ist.

Aufgabe Erstellen Sie eine Tabelle, welche die Relation „Ahn“ repräsentiert. Sie soll alle Paare (P,Q) von Personen enthalten, so daß Q ein Ahn von P ist. Dabei soll ein Ahn ein Elter oder ein Großelter sein.

Anmerkungen

Views, die auf Views aufbauen, werden auch hierarchische Views  genannt.

Bei einigen Datenbanksystem soll die Verwendung hierarchischer Views Abfrage verlangsamen, allerdings wäre es nicht erklärbar, warum.

Bei den bisherigen Anwendungen konnte der Autor dieses Kurses keine Probleme beobachten.

Im Zweifel sollte man verschiedene Möglichkeiten ausprobieren.