Normalisierung von Tabellen in SQL (MySQL )

Vorbereitung

Skript (MySQL )

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE STADTLAND ( STADT VARCHAR ( 255 ), LAND VARCHAR ( 255 ), KONTINENT VARCHAR ( 255 ) );
INSERT INTO  STADTLAND ( STADT, LAND, KONTINENT ) VALUES ( 'Paris',     'Frankreich',   'Europa' );
INSERT INTO  STADTLAND ( STADT, LAND, KONTINENT ) VALUES ( 'Marseille', 'Frankreich',   'Europa' );
INSERT INTO  STADTLAND ( STADT, LAND, KONTINENT ) VALUES ( 'Lyon',      'Frankreich',   'Europa' );
INSERT INTO  STADTLAND ( STADT, LAND, KONTINENT ) VALUES ( 'Rom',       'Italien',      'Europa' );
INSERT INTO  STADTLAND ( STADT, LAND, KONTINENT ) VALUES ( 'Mailand',   'Italien',      'Europa' );
INSERT INTO  STADTLAND ( STADT, LAND, KONTINENT ) VALUES ( 'Neapel',    'Italien',      'Europa' );
INSERT INTO  STADTLAND ( STADT, LAND, KONTINENT ) VALUES ( 'Tokio',     'Japan',        'Asien'  );
INSERT INTO  STADTLAND ( STADT, LAND, KONTINENT ) VALUES ( 'Yokohama',  'Japan',        'Asien'  );
INSERT INTO  STADTLAND ( STADT, LAND, KONTINENT ) VALUES ( 'Osaka',     'Japan',        'Asien'  );
SELECT * FROM STADTLAND;

Normalisierung von Tabellen

Unter „Normalisierung“ einer Menge von Tabellen versteht man es, die Tabellenstruktur und -bedeutung so zu verändern oder zu gestalten, daß alle Regeln für relationale Datenbanken eingehalten werden. Es gibt viele verschiedene Arten der Normalisierung, je nachdem welche Regel dadurch berücksichtigt werden soll. In dieser Lektion zeigen wir ein prototypisches Beispiel einer Normalisierung: Die Aufteilung einer Tabelle in zwei kleinere Tabellen, von denen sie Join-abhängig ist.

Die folgende Tabelle zeigt zu einer Stadt an, in welchem Land und auf welchem Kontinent sie sich befindet.

+-----------+------------+-----------+
| STADT     | LAND       | KONTINENT |
+-----------+------------+-----------+
| Paris     | Frankreich | Europa    |
| Marseille | Frankreich | Europa    |
| Lyon      | Frankreich | Europa    |
| Rom       | Italien    | Europa    |
| Mailand   | Italien    | Europa    |
| Neapel    | Italien    | Europa    |
| Tokio     | Japan      | Asien     |
| Yokohama  | Japan      | Asien     |
| Osaka     | Japan      | Asien     |
+-----------+------------+-----------+

Aussprachehinweis

Lyon /lijɔ̃/

Wenn wir einmal annehmen, daß jedes Land, das für diese Tabelle in Frage kommt, sich immer ganz auf einem Kontinent befindet. so enthält diese Tabelle die Information über den Kontinent eines Landes mehrfach. Informationen sollten jedoch nicht mehrfach abgespeichert werden.

Die Wiederholung eines Wortes, wie »Frankreich« alleine ist noch keine Redundanz (unnötige Wiederholung einer Information), denn ein einzelnes Wort ist keine Aussage. Die Wiederholung eines Paares, wie »Frankreich | Europa« ist schon Redundanz, denn dieses Paar soll aussagen, daß Frankreich sich in Europa befindet.

In einer Datenbank sollte jede Information nur einmal abgespeichert werden.

Um die Redundanz zu eliminieren und die Tabellen in das übliche Format zu bringen, können wir die Tabelle in zwei Tabellen aufteilen. Die eine Tabelle gibt an, in welchem Land sich eine Stadt befindet. Die andere Tabelle gibt an, auf welchem Kontinent sich ein Land befindet.

Skript (MySQL )

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE STADT ( STADT VARCHAR ( 255 ), LAND VARCHAR ( 255 ));
INSERT INTO  STADT ( STADT, LAND ) VALUES ( 'Paris',     'Frankreich' );
INSERT INTO  STADT ( STADT, LAND ) VALUES ( 'Marseille', 'Frankreich' );
INSERT INTO  STADT ( STADT, LAND ) VALUES ( 'Lyon',      'Frankreich' );
INSERT INTO  STADT ( STADT, LAND ) VALUES ( 'Rom',       'Italien'    );
INSERT INTO  STADT ( STADT, LAND ) VALUES ( 'Mailand',   'Italien'    );
INSERT INTO  STADT ( STADT, LAND ) VALUES ( 'Neapel',    'Italien'    );
INSERT INTO  STADT ( STADT, LAND ) VALUES ( 'Tokio',     'Japan'      );
INSERT INTO  STADT ( STADT, LAND ) VALUES ( 'Yokohama',  'Japan'      );
INSERT INTO  STADT ( STADT, LAND ) VALUES ( 'Osaka',     'Japan'      );
CREATE TABLE LAND ( LAND VARCHAR ( 255 ), KONTINENT VARCHAR ( 255 ) );
INSERT INTO  LAND ( LAND, KONTINENT ) VALUES ( 'Frankreich',   'Europa' );
INSERT INTO  LAND ( LAND, KONTINENT ) VALUES ( 'Italien',      'Europa' );
INSERT INTO  LAND ( LAND, KONTINENT ) VALUES ( 'Japan',        'Asien'  );

Konsole

SELECT * FROM STADT;

+-----------+------------+
| STADT     | LAND       |
+-----------+------------+
| Paris     | Frankreich |
| Marseille | Frankreich |
| Lyon      | Frankreich |
| Rom       | Italien    |
| Mailand   | Italien    |
| Neapel    | Italien    |
| Tokio     | Japan      |
| Yokohama  | Japan      |
| Osaka     | Japan      |
+-----------+------------+

Konsole

SELECT * FROM LAND;

+------------+-----------+
| LAND       | KONTINENT |
+------------+-----------+
| Frankreich | Europa    |
| Italien    | Europa    |
| Japan      | Asien     |
+------------+-----------+

Mehrere („n“) Städte können zu einem („1“) Land gehören. Deswegen spricht man hier auch von einer n:1-Beziehung zwischen den beiden Tabellen.

Vorgestellte n:1-Beziehungen zwischen den beiden Tabellen

+-----------+------------+                          +------------+-----------+
| STADT     | LAND       |                          | LAND       | KONTINENT |
+-----------+------------+                          +------------+-----------+
| Paris     | Frankreich ------------.--------------> Frankreich | Europa    |
| Marseille | Frankreich ------------|  .-----------> Italien    | Europa    |
| Lyon      | Frankreich ------------'  |  .--------> Japan      | Asien     |
| Rom       | Italien ------------------|  |        +------------+-----------+
| Mailand   | Italien ------------------|  |
| Neapel    | Italien ------------------'  |
| Tokio     | Japan -----------------------|
| Yokohama  | Japan -----------------------|
| Osaka     | Japan -----------------------'
+-----------+------------+

Würden die Kontinente in der Basistabelle »LAND« alle in »Eurasien« umbenannt werden, würde sich dies in den abgeleiteten Tabellen der folgenden Übungsaufgabe sofort in richtiger Weise bemerkbar machen.

Durch ein Äquijoin der beiden Tabellen, der Gleichheit der Ländernamen verlangt, kann die ursprüngliche Tabelle als abgeleitete Tabelle oder als View wieder hergestellt werden. Obwohl in der abgeleiteten Tabelle die Information über den Kontinent eines Landes dann wieder in mehreren Zeilen erscheint, ist dies kein Verstoß gegen die Regel, daß dieselbe Information nicht mehrfach abgespeichert werden soll, weil die Information ja nicht in den abgeleiteten Tabellen gespeichert wird, sondern nur in den Basistabellenspeichern, und dort ist diese Information jeweils nur einmal abgespeichert.

Konsole

SELECT STADT, STADT.LAND, KONTINENT FROM STADT INNER JOIN LAND ON STADT.LAND = LAND.LAND;

+-----------+------------+-----------+
| STADT     | LAND       | KONTINENT |
+-----------+------------+-----------+
| Paris     | Frankreich | Europa    |
| Marseille | Frankreich | Europa    |
| Lyon      | Frankreich | Europa    |
| Rom       | Italien    | Europa    |
| Mailand   | Italien    | Europa    |
| Neapel    | Italien    | Europa    |
| Tokio     | Japan      | Asien     |
| Yokohama  | Japan      | Asien     |
| Osaka     | Japan      | Asien     |
+-----------+------------+-----------+

Zitate *

en.wikipedia.org/wiki/Single_source_of_truth: In information systems design and theory, single source of truth (SSOT), is the practice of structuring information models and associated schemata such that every data element is stored exactly once.