HelpGuide - Ein Handbuch zur Hilfe

  Datenbankdesign

• Die Tabellen
  • Programme
  • Sprachen
  • Programme_Sprachen
  • Autoren
  • OS
  • Programme_OS
  • Formate
  • Konvertierungen
• Beispielanfragen

  Die Tabellen

  Programme

mysql> describe Programme;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| ProgrammID | int(11)       |      | PRI | NULL    | auto_increment |
| Name       | varchar(64)   | YES  |     | NULL    |                |
| AutorID    | int(11)       | YES  |     | NULL    |                |
| Groesse    | int(11)       | YES  |     | NULL    |                |
| Punkte     | int(11)       | YES  |     | NULL    |                |
| Stimme     | int(11)       | YES  |     | NULL    |                |
| Datum      | timestamp(14) | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

  Sprachen

mysql> describe Sprachen;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| SprachID    | int(11)     |      | PRI | NULL    | auto_increment |
| Bezeichnung | varchar(64) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

  Programme_Sprachen

mysql> describe Programme_Sprachen;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| ProgrammID | int(11) |      |     | 0       |       |
| SprachID   | int(11) |      |     | 0       |       |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

  Autoren

mysql> describe Autoren;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| AutorID | int(11)     |      | PRI | NULL    | auto_increment |
| Name    | varchar(64) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

  OS

mysql> describe OS;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| OSID  | int(11)     |      | PRI | NULL    | auto_increment |
| Name  | varchar(64) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

  Programme_OS

mysql> describe Programme_OS;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| ProgrammID | int(11) |      |     | 0       |       |
| OSID       | int(11) |      |     | 0       |       |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

  Formate

mysql> describe Formate;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| FormatID    | int(11)     |      | PRI | NULL    | auto_increment |
| Bezeichnung | varchar(64) | YES  |     | NULL    |                |
| Extensions  | varchar(64) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

  Konvertierungen

mysql> describe Konvertierungen;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| ProgrammID  | int(11) |      |     | 0       |       |
| VonFormatID | int(11) |      |     | 0       |       |
| ZuFormatID  | int(11) |      |     | 0       |       |
+-------------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

  Beispielanfragen

In welchen Sprachen ist Programm 'Foo2000' verfügbar?

SELECT Sprachen.Bezeichnung
FROM Sprachen
	LEFT JOIN Programme_Sprachen USING (SprachID)
	LEFT JOIN Programme USING (ProgrammID)
WHERE Programme.Name = 'Foo2000'
;

Welche Programme sind für das OS 'FooBar' verfügbar?

SELECT Programme.Name
FROM Programme
	LEFT JOIN Programme_OS USING (ProgrammID)
	LEFT JOIN OS USING (OSID)
WHERE OS.Name = 'FooBar'
;

Und für welche Betriebssysteme kann ich das Programm 'Foo2000' kriegen?

SELECT OS.Name
FROM OS
	LEFT JOIN Programme_OS USING (OSID)
	LEFT JOIN Programme USING (ProgrammID)
WHERE Programme.Name = 'Foo2000'
;