PMG/Sammlung alt DB
Aus ProgrammingWiki
Wiki-Quelltext dieser Seite.
<language>SQL</language>
Datenbanken und Statistiken sind im täglichen Leben ständig präsent, sowohl im privatwirtschaftlichen als auch staatlichen Bereich.<br>
Zum ersten Bereich gehören beispielsweise die Datenbanken der Versicherungen, der Reisebüros, der Online-Händler oder der SCHUFA (Schutzgemeinschaft zur allgemeinen Kreditsicherung).<br>
Zum staatlichen Bereich zählen z.B. die statistischen Landesämter, die Meldebehörden oder das Verkehrszentralregister ("Flensburg").
<br><br>
In den folgenden Wochen werden wir uns mit
* der Modellierung von Datenbanken sowie der Optimierung dieser Modelle
* Eigenschaften von Datenbankmanagementsystemen
* Möglichkeiten der Auswertung von Daten
beschäftigen.
<br>
Bevor wir uns der Modellierung widmen, soll anhand der SQL-Syntax die Auswertung einer bereits bestehenden
Datenbank durchgeführt, eine DB erstellt werden sowie ein weiteres Beispiel analysiert werden.
<br>
#SQL und [[SQL-Syntax]] am Beispiel der Terra-Datenbank
#Datenbank erstellen [[Datenbanken vorbereiten]]
#Beispiel [[Bankraub]]
#Bibliothek [[Bibliothek]]
#Abitur [[Abitur]]
#CD-Versand [[CD-Versand]]
#[[Datenbankbeleg]]
Die in der Praxis am meisten verwendete relationale Datenbanksprache ist SQL.<br>
SQL ist eine deskriptive Sprache, d.h. eine Anfrage hat das Ergebnis ohne Angabe des Algorithmus zu beschreiben.
Grundlegend basieren die Anfrageanweisungen auf der '''SELECT-FROM-WHERE - Klausel'''
Beispiel:
SELECT Feldnamen
FROM Tabelle(n)
WHERE Bedingung;
'''Dabei erfolgt unter SELECT eine <u>Projektion</u> auf die Spalten der Tabelle(n) und unter WHERE eine <u>Selektion (Auswahl)</u> der gewünschten Datensätze bzw. Datenfelder!'''
<br><br>
''Für die WHERE-Klausel:''<br>
Vergleichsoperatoren =; <>; <; <=; >; >=
Prädikate können mit AND, OR und NOT zu komplexeren Bedingungen verknüpft werden.<br>
Priorität: NOT vor AND vor OR oder Klammerung!<br>
BETWEEN-Prädikat: entspricht einer Verknüpfung >= AND <=<br>
Sortierung: ORDER BY <spaltenname> ASC/DESC (auf- bzw. absteigend)
SELECT Feldnamen
FROM Tabelle(n)
WHERE Bedingung
ORDER BY Feldname ASC/DESC;
weitere Hinweise zur Syntax siehe [http://www.sqltutorial.de SQL-Tutorial]<br>
bzw. <u>speziell</u> für das PWiki siehe [http://hsqldb.org/doc/guide/ch09.html hier]
Die ersten Übungen zur Abfrage an Datenbanken erfolgt mit Hilfe der [http://marvin.sn.schule.de/terra/ Terra-Datenbank online]
Lösen Sie dazu folgende Aufgaben:
# Ermitteln Sie die Namen aller Kontinente
# Gesucht sind alle Inseln (Name) der Inselgruppe der Philippinen.
# Gesucht sind die Einwohnerzahlen der deutschsprachigen Länder (D, A, CH).
# Gesucht ist die Einwohnerzahl der deutschsprachigen Länder (D, A, CH).
# Gesucht sind alle Berge der Alpen (Beachten Sie, dass die Alpen in Teilgebirge aufgegliedert sind).
# Gesucht sind alle Hochgebirge (Gebirge mit Bergen über 3000 m Höhe).
# Gesucht sind alle Flüsse (Name), die in die Ostsee oder Nordsee münden und über 1000 km lang sind.
# Gesucht sind alle Wüsten (Name) der Art 'Sandwueste' mit einer Fläche größer als 25000km².
# Geben Sie für alle Millionenstädte, die in den Tropen liegen, die Namen und ihre Koordinaten an. (Die Tropen liegen zwischen 23.27 Grad nördlicher und 23.27 Grad südlicher Breite.)
# Geben Sie die Ländernamen und die Zahl der Einwohner für alle Länder an, die mehr als 45 Millionen Einwohner haben. Ordnen Sie nach der Einwohnerzahl!
<br>
Z: Geben Sie von jedem Land in Afrika den Namen, die Anzahl der Einwohner und die Fläche an.
* Diese Einleitung entstammt der Seite von [http://programmingwiki.de/SQL Michael Hielscher].
* Es wird eine Datenbank angelegt.
* Die Datenfelder werden gefüllt.
* Eine Abfrage gestellt.
Eine kleine DB vorbereiten:
<eval id="4c8fc835525fc">
DROP TABLE Place IF EXISTS;
CREATE TABLE Place (Code INTEGER,Name VARCHAR(255));
CREATE INDEX iCode ON Place (Code);
DROP TABLE Customer IF EXISTS;
CREATE TABLE Customer (Nr INTEGER,Name VARCHAR(255),Place INTEGER);
CREATE INDEX iNr ON Customer (Nr);
</eval>
Führen wir ein paar Inserts durch:
<eval id="4c8fc8355270c">
INSERT INTO Place VALUES (4900,'Langenthal');
INSERT INTO Place VALUES (8000,'Zurich');
INSERT INTO Place VALUES (3000,'Berne');
INSERT INTO Place VALUES (1200,'Geneva');
INSERT INTO Place VALUES (6900,'Lugano');
INSERT INTO Customer VALUES (1,'Meier',3000);
INSERT INTO Customer VALUES (2,'Mueller',8000);
INSERT INTO Customer VALUES (3,'Devaux',1200);
INSERT INTO Customer VALUES (4,'Rossi',6900);
INSERT INTO Customer VALUES (5,'Rickli',3000);
INSERT INTO Customer VALUES (6,'Graf',3000);
INSERT INTO Customer VALUES (7,'Mueller',4900);
INSERT INTO Customer VALUES (8,'May',1200);
INSERT INTO Customer VALUES (9,'Berger',8000);
INSERT INTO Customer VALUES (10,'D''Ascoli',6900);
INSERT INTO Customer VALUES (11,'Padruz',1200);
INSERT INTO Customer VALUES (12,'Hug',4900);
</eval>
Ein Query dazu:
<eval id="4c8fc83552820">
SELECT * FROM Customer;
SELECT * FROM Customer,place WHERE Customer.place=place.code AND place.name='Berne'
</eval>
Natürlich kann man auch CODE Boxen verwenden oder versteckten Code für das anlegen einer Datenbank
mit Beispielinhalten usw.
Elemente, die in einer CODE Box hinterlegt sind, werden IMMER vor jedem EVAL mit ausgeführt.
Damit eignen Sie sich prima, um Tabellen und Datensätze anzulegen:
<code id="4c8fc835524da">
drop table Artikel if exists;
Create Table Artikel(
A_Nr integer,
A_Name varchar(50),
A_Preis decimal);
Insert Into Artikel Values (12, 'Oberhemd', 22.23);
Insert Into Artikel Values (22, 'Mantel', 360.00);
Insert Into Artikel Values (11, 'Oberhemd', 44.20);
Insert Into Artikel Values (13, 'Hose', 110.50);
</code>
<eval id="4c8fc8355292f">
SELECT * FROM Artikel;
SELECT *
FROM Artikel
WHERE A_Preis = (SELECT MAX(A.A_Preis) FROM Artikel As A)
</eval>
Dieses Beispiel stammt von der Programming-Wiki-Seite von [http://programmingwiki.de/SQL_Bankraub Michael Hielscher]
== Was ist passiert ==
Ein Gängstertrio hat am 23.08.2009 die Central Bank in New York ausgeräumt. Es waren Profis wie der Polizeichef bestätigte: "Hier waren ganz klar Kenner mit Erfahrung am Werk. Sie haben den Coup sicher schon über Wochen im Vorfeld geplant."
== Die Hinweise ==
Von den Augenzeugen haben wir nur zwei Hinweise auf die '''drei''' männliche Täter erhalten:
1. Hinweis:
Ein Täter bedrohte die Angestellten und wurde von einem Komplizen „Lie“ gerufen.
Er soll mindestens 1'80 groß gewesen sein, zwischen 20 und 30 Jahre alt und trug
einen auffallend chinesischen Kleidungsstil.
2.Hinweis:
Eine Augenzeugin erinnert sich an das Fluchtfahrzeug. Es ist ein blauer oder
schwarzer BMW gewesen sagt sie und das Kennzeichen soll auf 557 geendet haben.
== Die Datenbank ==
Als FBI-Ermittler und Datenbank-Spezialist bist du für diesen Fall zuständig. Du hast bereits 100 Personen herausgesucht, die als Täter in Frage kommen könnten. Du hast Zugriff auf folgende Tabellen der FBI-Datenbank:
Die Spalten Vater, Mutter, Fahrzeughalter und Person sind Fremdschlüssel auf die Spalte ID der Tabelle Einwohner.
[[Bild:Bankraub_ERD.png]]
<code id="4c8fbc9b50b14" hidden>
DROP TABLE Einwohner IF EXISTS;
CREATE TABLE Einwohner (ID INTEGER,Name VARCHAR(255), Vorname VARCHAR(255), Nationalitaet VARCHAR(50), Geburtsdatum VARCHAR(30), Groesse INTEGER, Strasse VARCHAR(200), Hausnummer INTEGER, Vater INTEGER, Mutter INTEGER);
DROP TABLE Vorstrafen IF EXISTS;
CREATE TABLE Vorstrafen (Person INTEGER, Grund VARCHAR(255));
DROP TABLE KFZ IF EXISTS;
CREATE TABLE KFZ (Kennzeichen VARCHAR(20), Fahrzeughalter INTEGER, Farbe VARCHAR(255), Automarke VARCHAR(255));
DROP TABLE Strafzettel IF EXISTS;
CREATE TABLE Strafzettel (Kennzeichen VARCHAR(20), Datum VARCHAR(255),Zeit VARCHAR(255), Strasse VARCHAR(255));
INSERT INTO Einwohner VALUES (1,'Anderson','George','USA','02.11.1932', 172, 'Corn Road', 104, NULL,NULL);
INSERT INTO Einwohner VALUES (2,'Anderson','Vivian','England','03.05.1936', 165, 'Corn Road', 104, NULL,NULL);
INSERT INTO Einwohner VALUES (3,'Smith','Tom','USA','07.12.1938', 181, 'Malven Street', 65, NULL,NULL);
INSERT INTO Einwohner VALUES (4,'Smith','Sara','USA','16.07.1937', 167, 'Malven Street', 65, NULL,NULL);
INSERT INTO Einwohner VALUES (5,'Linguini','Hans','USA','05.10.1953', 169, 'St. Luis Road', 12, NULL,NULL);
INSERT INTO Einwohner VALUES (6,'Anderson','Julia','USA','23.04.1956', 162, 'St. Luis Road', 9, 3,4);
INSERT INTO Einwohner VALUES (7,'Jones','Eva','USA','23.12.1938', 152, 'Earding Hall', 43, NULL,NULL);
INSERT INTO Einwohner VALUES (8,'Jones','Sahra','USA','16.09.1956', 154, 'Crossroads', 59, NULL,8);
INSERT INTO Einwohner VALUES (9,'Watson','Emilie','German','02.08.1981', 161, 'Lancaster Road', 3, NULL,NULL);
INSERT INTO Einwohner VALUES (10,'Armstrong','Lars','Kanada','01.09.1977', 182, '5th Ave', 176, NULL,NULL);
INSERT INTO Einwohner VALUES (11,'Anderson','Egon','USA','12.05.1976', 176, 'St. Luis Road', 9, NULL,6);
INSERT INTO Einwohner VALUES (12,'Faith','Tom','USA','22.07.1951', 176, '12th Ave', 54, NULL,NULL);
INSERT INTO Einwohner VALUES (13,'Faith','Salma','USA','31.08.1954', 161, '12th Ave', 54, NULL,NULL);
INSERT INTO Einwohner VALUES (14,'Faith','Larry','USA','02.11.1976', 180, '12th Ave', 88, 12,13);
INSERT INTO Einwohner VALUES (15,'Thommas','Max','USA','07.12.1978', 182, 'St. George Street', 41, NULL,NULL);
INSERT INTO Einwohner VALUES (16,'Eddison','Eve','USA','21.09.1923', 148, 'Placa De Toro', 136, NULL,NULL);
INSERT INTO Einwohner VALUES (17,'Tell','Egon','USA','22.07.1973', 165, '6th Ave', 112, NULL,NULL);
INSERT INTO Einwohner VALUES (18,'Jang','Lie','China','15.08.1982', 182, '8th Road', 78, NULL,19);
INSERT INTO Einwohner VALUES (19,'Jang','Chia','China','12.06.1971', 165, 'Bardich Road', 54, NULL,NULL);
INSERT INTO Einwohner VALUES (20,'La Fe','Largo','France','18.08.1978', 183, 'Bosten Place', 3, NULL,NULL);
INSERT INTO Einwohner VALUES (21,'La Fe','Morgan','France','17.06.1979', 173, 'Bosten Place', 3, NULL,NULL);
INSERT INTO Einwohner VALUES (22,'Cook','Lie','USA','26.02.1925', 188, 'Mercury Road', 21, NULL,NULL);
INSERT INTO Einwohner VALUES (23,'Chong','Lie','China','12.07.1954', 156, 'St Elmar Road', 141, NULL,NULL);
INSERT INTO Einwohner VALUES (24,'Gandi','Lea','India','04.12.1978', 163, 'Mandara Street', 31, NULL,NULL);
INSERT INTO Einwohner VALUES (25,'Gandi','Chuan','India','23.02.1973', 183, 'Mandara Street', 31, NULL,NULL);
INSERT INTO Einwohner VALUES (26,'Urs','Rene','Swizerland','04.03.1972', 182, 'Bold Street', 43, NULL,NULL);
INSERT INTO Einwohner VALUES (27,'Urs','Lisbet','Swizerland','11.02.1975', 166, 'Bold Street', 43, NULL,NULL);
INSERT INTO Einwohner VALUES (28,'Urs','Sandra','Swizerland','23.01.1993', 171, 'Bold Street', 43, 26,27);
INSERT INTO Einwohner VALUES (29,'Clark','Denis','USA','08.11.1969', 173, 'Odessa Street', 87, NULL,NULL);
INSERT INTO Einwohner VALUES (30,'Talent','Adam','USA','17.05.1983', 193, 'Furca Street', 199, NULL,NULL);
INSERT INTO Einwohner VALUES (31,'Jeys','Billy','USA','21.09.1982', 187, 'Erding Road', 218, 32,NULL);
INSERT INTO Einwohner VALUES (32,'Jeys','Egon','USA','05.11.1971', 175, 'Erding Road', 218, NULL,NULL);
INSERT INTO Einwohner VALUES (33,'Verne','Eddy','USA','01.07.1981', 185, 'Erding Road', 112, NULL,NULL);
INSERT INTO Einwohner VALUES (34,'Tagar','Loui','France','09.08.1982', 179, 'St Jones Road', 3, NULL,NULL);
INSERT INTO Einwohner VALUES (35,'Jang','Chia','China','07.09.1977', 165, 'Bardich Road', 32, NULL,NULL);
INSERT INTO Einwohner VALUES (36,'Jang','Jachi','China','21.02.1981', 185, 'Bardich Road', 32, NULL,NULL);
INSERT INTO Einwohner VALUES (37,'Thong','Lo','China','22.01.1951', 165, '26th Street', 152, NULL,NULL);
INSERT INTO Einwohner VALUES (38,'Smith','Thomas','USA','11.04.1976', 185, 'Placa Avante', 19, NULL,NULL);
INSERT INTO Einwohner VALUES (38,'Smith','Kira','USA','23.08.1981', 163, 'Placa Avante', 19, NULL,NULL);
INSERT INTO Einwohner VALUES (39,'Jones','Lara','USA','01.09.1982', 161, '2th Ave', 197, NULL,NULL);
INSERT INTO Einwohner VALUES (40,'Mueller','Hans','German','05.11.1952', 167, 'Erding Road', 134, NULL,NULL);
INSERT INTO Einwohner VALUES (41,'Delgardo','Lillian','Italy','08.01.1981', 163, 'Erding Road', 122, NULL,NULL);
INSERT INTO Einwohner VALUES (42,'Delgardo','Luigi','Italy','17.03.1977', 184, 'Erding Road', 122, NULL,NULL);
INSERT INTO Einwohner VALUES (43,'Cook','Thomas','USA','15.03.1978', 172, 'Lago Street', 81, NULL,NULL);
INSERT INTO Einwohner VALUES (44,'Cook','Tim','USA','04.01.1953', 171, 'Placa Palace', 55, NULL,NULL);
INSERT INTO Einwohner VALUES (45,'Cook','Lisa','USA','15.12.1955', 153, 'Placa Palace', 55, NULL,NULL);
INSERT INTO Einwohner VALUES (46,'Cook','Jack','USA','11.09.1932', 162, 'St George Place', 1, NULL,NULL);
INSERT INTO Einwohner VALUES (47,'Cook','Elisabeth','USA','21.08.1931', 152, 'St George Place', 1, NULL,NULL);
INSERT INTO Einwohner VALUES (48,'Rauch','Peter','German','22.01.1981', 182, 'Lago Street', 23, NULL,NULL);
INSERT INTO Einwohner VALUES (49,'Rauch','Eva','German','12.08.1986', 166, 'Lago Street', 23, NULL,NULL);
INSERT INTO Einwohner VALUES (50,'Darzzy','Mike','USA','21.11.1955', 182, 'Rose Avenue', 44, NULL,NULL);
INSERT INTO Einwohner VALUES (51,'Darzzy','Elenore','USA','30.01.1960', 163, 'Rose Avenue', 44, NULL,NULL);
INSERT INTO Einwohner VALUES (52,'Kingz','Phil','USA','22.04.1987', 197, 'Town Street', 1, 54,53);
INSERT INTO Einwohner VALUES (53,'Kingz','Carly','USA','08.09.1968', 163, 'Silver Road', 98, NULL,NULL);
INSERT INTO Einwohner VALUES (54,'Gibson','Sam','USA','12.03.1966', 187, 'Aron Avenue', 11, NULL,NULL);
INSERT INTO Einwohner VALUES (55,'Soure','Lily','USA','24.05.1970', 158, 'Kline Street', 15, NULL,NULL);
INSERT INTO Einwohner VALUES (56,'Nealy','Oscar','England','01.01.1924', 172, 'Kensington Road', 43, NULL,NULL);
INSERT INTO Einwohner VALUES (57,'Nealy','Marlene','England','16.12.1930', 154, 'Kensington Road', 43, NULL,NULL);
INSERT INTO Einwohner VALUES (58,'Rose','Charlene','England','26.08.1962',165, 'London Street', 5, 56,57);
INSERT INTO Einwohner VALUES (59,'Nealy','Sam','Irland','07.07.1958', 185, 'Middle Street', 34, 56,57);
INSERT INTO Einwohner VALUES (60,'Breaker','Fred','USA','27.11.1974', 193, 'City Place', 86, NULL,NULL);
INSERT INTO Einwohner VALUES (61,'Summer','Aron','USA','19.09.1980', 178, 'Florres Road', 62, NULL,NULL);
INSERT INTO Einwohner VALUES (62,'Summer','Caroline','USA','29.11.1985', 159, 'Florres Road', 62, NULL,NULL);
INSERT INTO Einwohner VALUES (63,'Hayden','Luise','USA','31.12.1957', 167, 'Arrlington Road', 94, NULL,NULL);
INSERT INTO Einwohner VALUES (64,'Hayden','Marcel','USA','23.04.1953', 184, 'Arrlington Road', 94, NULL,NULL);
INSERT INTO Einwohner VALUES (65,'Runes','Vivianne','German','07.10.1980', 169, 'Seestrasse', 56, 64,63);
INSERT INTO Einwohner VALUES (66,'Hayden','Loris','USA','06.05.1977', 188, 'Arrow Road', 99, 64,63);
INSERT INTO Einwohner VALUES (67,'Wild','Mario','USA','17.02.1940', 180, 'Flower Road', 79, NULL,NULL);
INSERT INTO Einwohner VALUES (68,'Cream','Jonas','USA','09.06.1985', 191, 'Night Avenue', 5, NULL,NULL);
INSERT INTO Einwohner VALUES (69,'Cave','Marie','Irland','21.09.1933', 160, 'Scot Way', 52, NULL,NULL);
INSERT INTO Einwohner VALUES (70,'Cave','Linol','Irland','25.03.1930', 186, 'Scot Way', 52, NULL,NULL);
INSERT INTO Einwohner VALUES (71,'Rinol','Luise','Italy','01.06.1951', 166, 'Wall Way', 9, NULL,NULL);
INSERT INTO Einwohner VALUES (72,'Meyer','Sina','German','16.10.1955', 181, 'Am Wall', 7, NULL,NULL);
INSERT INTO Einwohner VALUES (73,'Cave','Gliff','England','29.01.1953', 189, 'Picadilly Circus', 11, 70,69);
INSERT INTO Einwohner VALUES (74,'Cave','Lisa','England','15.09.1957', 163, 'Picadilly Circus', 11, NULL,NULL);
INSERT INTO Einwohner VALUES (75,'Cave','Lina','England','01.01.1978', 176, 'Picadily Circus', 104, 73,74);
INSERT INTO Einwohner VALUES (76,'Crudo','Joel','USA','03.12.1979', 172, '4th Avenue', 34, NULL,NULL);
INSERT INTO Einwohner VALUES (77,'Birdy','Michael','USA','26.04.1958', 183, 'Circus Road', 354, NULL,NULL);
INSERT INTO Einwohner VALUES (78,'Lester','Susan','USA','30.03.1940', 167, 'Brook Street', 26, NULL,NULL);
INSERT INTO Einwohner VALUES (79,'Chester','Larry','USA','14.12.1930', 182, 'Clester Street', 67, NULL,NULL);
INSERT INTO Einwohner VALUES (80,'Quin','Elia','USA','09.07.1956', 156, 'St Louis Street', 120, NULL,NULL);
INSERT INTO Einwohner VALUES (81,'Quin','Freddy','USA','19.03.1952', 175, 'St Louis Street', 120, NULL,NULL);
INSERT INTO Einwohner VALUES (82,'Quin','Eddy','USA','09.07.1979', 182, 'Jefferson Street', 12, 81,80);
INSERT INTO Einwohner VALUES (83,'Flame','Samanta','USA','04.04.1977', 163, 'Madera Street', 32, NULL,NULL);
INSERT INTO Einwohner VALUES (84,'Flame','Georg','USA','01.09.1980', 193, 'Madera Street', 32, 85,86);
INSERT INTO Einwohner VALUES (85,'Flame','Logan','USA','26.07.1951', 181, 'Elmar Street', 176, NULL,NULL);
INSERT INTO Einwohner VALUES (86,'Flame','Antonia','USA','21.01.1955', 160, 'Elmar Street', 176, NULL,NULL);
INSERT INTO Einwohner VALUES (87,'Pitt','Lisa','USA','22.04.1983', 163, 'Jefferson Street', 204, NULL,NULL);
INSERT INTO Einwohner VALUES (88,'Pitt','Joe','USA','10.09.1979', 182, 'Jefferson Street', 204, NULL,NULL);
INSERT INTO Einwohner VALUES (89,'Lenka','Eddy','USA','30.12.1981', 188, 'Woody Road', 19, NULL,NULL);
INSERT INTO Einwohner VALUES (90,'Lenka','Gina','USA','21.05.1984', 171, 'Woody Road', 19, NULL,NULL);
INSERT INTO Einwohner VALUES (91,'Long','Lie','China','13.05.1990', 191, 'Indigo Street', 63, NULL,NULL);
INSERT INTO Einwohner VALUES (92,'Fray','Paolo','USA','09.11.1981', 186, 'Indigo Street', 103, 93,94);
INSERT INTO Einwohner VALUES (93,'Fray','William','USA','01.09.1961', 177, 'Tortuga Bay', 10, NULL,NULL);
INSERT INTO Einwohner VALUES (94,'Fray','Julia','USA','09.05.1963', 161, 'Tortuga Bay', 10, NULL,NULL);
INSERT INTO Einwohner VALUES (95,'Delaware','Ira','USA','07.07.1923', 143, 'August Street', 132, NULL,NULL);
INSERT INTO Einwohner VALUES (96,'Honk','Igor','Russland','13.06.1976', 182, 'August Street', 150,NULL,NULL);
INSERT INTO Einwohner VALUES (97,'Honk','Tatjana','Russland','18.03.1979', 170, 'August Street', 150, NULL,NULL);
INSERT INTO Einwohner VALUES (98,'Bent','Arthur','USA','19.02.1982', 187, 'St Elmar Street', 201, NULL,NULL);
INSERT INTO Einwohner VALUES (99,'Bent','Lora','USA','18.01.1986', 171, 'St Elmar Street', 201, NULL,NULL);
INSERT INTO Einwohner VALUES (100,'Brown','Peter','German','31.08.1980', 180, 'St Elmar Street', 193, NULL,NULL);
INSERT INTO KFZ VALUES ('V325634',43,'grau','BMW');
INSERT INTO KFZ VALUES ('E405AD3',12,'blau','GM');
INSERT INTO KFZ VALUES ('E4D4557',15,'weiss','Opel');
INSERT INTO KFZ VALUES ('24553DF',23,'rot','BMW');
INSERT INTO KFZ VALUES ('3D35G53',30,'blau','BMW');
INSERT INTO KFZ VALUES ('4055557',34,'gelb','BMW');
INSERT INTO KFZ VALUES ('X4455F3',96,'braun','Lada');
INSERT INTO KFZ VALUES ('593FDNS',8,'lila','Opel');
INSERT INTO KFZ VALUES ('4576557',9,'weiss','Toyota');
INSERT INTO KFZ VALUES ('X54S435',38,'lila','Peugot');
INSERT INTO KFZ VALUES ('X566435',40,'rot','VW');
INSERT INTO KFZ VALUES ('D34D442',42,'weiss','VW');
INSERT INTO KFZ VALUES ('L343KD3',43,'grün','Opel');
INSERT INTO KFZ VALUES ('3D34VN9',45,'blau','Mercedes');
INSERT INTO KFZ VALUES ('445DSS3',46,'rot','Toyota');
INSERT INTO KFZ VALUES ('445D557',47,'blau','Toyota');
INSERT INTO KFZ VALUES ('4KE8965',48,'schwarz','Porsche');
INSERT INTO KFZ VALUES ('495DDF3',50,'weiss','Opel');
INSERT INTO KFZ VALUES ('X345557',6,'blau','BMW');
INSERT INTO KFZ VALUES ('4334DDD',52,'gelb','Ford');
INSERT INTO KFZ VALUES ('Y65478D',53,'schwarz','Peugot');
INSERT INTO KFZ VALUES ('L49210F',55,'silber','Mercedes');
INSERT INTO KFZ VALUES ('L344DF3',57,'rot','BMW');
INSERT INTO KFZ VALUES ('X3332SD',59,'rot','VW');
INSERT INTO KFZ VALUES ('X4440AQ',60,'braun','VW');
INSERT INTO KFZ VALUES ('U743DE2',68,'gelb','Toyota');
INSERT INTO KFZ VALUES ('R405553',72,'blau','VW');
INSERT INTO KFZ VALUES ('A47V394',76,'rot','Ford');
INSERT INTO KFZ VALUES ('Y453557',76,'gelb','BMW');
INSERT INTO KFZ VALUES ('A19324D',84,'blau','BMW');
INSERT INTO KFZ VALUES ('64E23F4',81,'silber','GM');
INSERT INTO KFZ VALUES ('A435D25',89,'gelben','Ford');
INSERT INTO KFZ VALUES ('A59225E',90,'blau','Mercedes');
INSERT INTO KFZ VALUES ('D459634',90,'rot','Porsche');
INSERT INTO KFZ VALUES ('F34053D',98,'rot','Mercedes');
INSERT INTO Strafzettel VALUES ('F34053D','06.07.2009','10:41','St Jones Road');
INSERT INTO Strafzettel VALUES ('495DDF3','07.09.2009','06:43','St Jones Road');
INSERT INTO Strafzettel VALUES ('Y65478D','15.08.2009','12:52','St Jones Road');
INSERT INTO Strafzettel VALUES ('D34D442','14.09.2009','14:23','Placa Avante');
INSERT INTO Strafzettel VALUES ('D34D442','10.09.2009','12:13','August Street');
INSERT INTO Strafzettel VALUES ('X4440AQ','26.08.2009','16:07','St Jones Road');
INSERT INTO Strafzettel VALUES ('4334DDD','15.08.2009','18:15','St Jones Road');
INSERT INTO Strafzettel VALUES ('4055557','04.07.2009','09:20','Elmar Street');
INSERT INTO Strafzettel VALUES ('4055557','08.08.2009','12:20','Clester Street');
INSERT INTO Strafzettel VALUES ('X345557','21.05.2009','12:04','St Jones Road');
INSERT INTO Strafzettel VALUES ('X345557','12.08.2009','18:02','Jefferson Street');
INSERT INTO Strafzettel VALUES ('495DDF3','12.08.2009','11:26','St Jones Road');
INSERT INTO Strafzettel VALUES ('X345557','17.08.2009','17:54','Jefferson Street');
INSERT INTO Strafzettel VALUES ('R405553','01.09.2009','09:19','St Jones Road');
INSERT INTO Strafzettel VALUES ('X345557','22.08.2009','18:45','Jefferson Street');
INSERT INTO Strafzettel VALUES ('445DSS3','16.08.2009','10:41','St Jones Road');
INSERT INTO Strafzettel VALUES ('Y65478D','17.07.2009','06:43','Scot Way');
INSERT INTO Strafzettel VALUES ('Y65478D','05.09.2009','12:52','Brook Street');
INSERT INTO Strafzettel VALUES ('L344DF3','04.08.2009','14:23','Brook Street');
INSERT INTO Strafzettel VALUES ('D34D442','00.08.2009','12:13','Picadilly Circus');
INSERT INTO Strafzettel VALUES ('X4440AQ','16.09.2009','16:07','Madera Street');
INSERT INTO Strafzettel VALUES ('495DDF3','25.09.2009','18:15','St Jones Road');
INSERT INTO Strafzettel VALUES ('L49210F','14.07.2009','09:20','Elmar Street');
INSERT INTO Strafzettel VALUES ('495DDF3','18.06.2009','12:20','Clester Street');
INSERT INTO Strafzettel VALUES ('L344DF3','01.07.2009','12:04','Madera Street');
INSERT INTO Strafzettel VALUES ('Y65478D','02.08.2009','18:02','Picadilly Circus');
INSERT INTO Strafzettel VALUES ('495DDF3','22.08.2009','11:26','St Jones Road');
INSERT INTO Strafzettel VALUES ('4KE8965','27.09.2009','17:54','St Louis Street');
INSERT INTO Strafzettel VALUES ('D34D442','11.09.2009','09:19','St Jones Road');
INSERT INTO Strafzettel VALUES ('445DSS3','12.08.2009','18:45','Am Wall');
INSERT INTO Vorstrafen VALUES (2,'Ladendiebstahl');
INSERT INTO Vorstrafen VALUES (8,'Erpressung');
INSERT INTO Vorstrafen VALUES (11,'Raub und schwerer Einbruch');
INSERT INTO Vorstrafen VALUES (12,'Raub');
INSERT INTO Vorstrafen VALUES (18,'Raub und Erpressung');
INSERT INTO Vorstrafen VALUES (22,'Ladendiebstahl');
INSERT INTO Vorstrafen VALUES (24,'Raub und schwerer Einbruch');
INSERT INTO Vorstrafen VALUES (26,'Körperverletzung');
INSERT INTO Vorstrafen VALUES (28,'Raub und Ladendiebstahl');
INSERT INTO Vorstrafen VALUES (29,'Ladendiebstahl');
INSERT INTO Vorstrafen VALUES (30,'Körperverletzung');
INSERT INTO Vorstrafen VALUES (32,'Steuerhinterziehung');
INSERT INTO Vorstrafen VALUES (38,'Raub und schwerer Einbruch');
INSERT INTO Vorstrafen VALUES (40,'Raub und Ladendiebstahl');
INSERT INTO Vorstrafen VALUES (43,'schwerer Einbruch');
INSERT INTO Vorstrafen VALUES (48,'Ladendiebstahl');
INSERT INTO Vorstrafen VALUES (49,'Raub');
INSERT INTO Vorstrafen VALUES (52,'Körperverletzung');
INSERT INTO Vorstrafen VALUES (56,'Raub');
INSERT INTO Vorstrafen VALUES (60,'Ladendiebstahl');
INSERT INTO Vorstrafen VALUES (63,'Steuerhinterziehung');
INSERT INTO Vorstrafen VALUES (66,'Körperverletzung');
INSERT INTO Vorstrafen VALUES (69,'Steuerhinterziehung');
INSERT INTO Vorstrafen VALUES (73,'Ladendiebstahl');
INSERT INTO Vorstrafen VALUES (75,'Erpressung');
INSERT INTO Vorstrafen VALUES (80,'Raub');
INSERT INTO Vorstrafen VALUES (82,'Raub und Ladendiebstahl');
INSERT INTO Vorstrafen VALUES (90,'Ladendiebstahl');
INSERT INTO Vorstrafen VALUES (98,'Steuerhinterziehung');
</code>
Hier kannst du einen Blick in die Datenbank werfen. Es werden jeweils 10 Datensätze angezeigt.
<eval id="4c8fc263e000b">
SELECT * FROM Einwohner AS Ausschnitt LIMIT 10;
SELECT * FROM Vorstrafen AS Ausschnitt LIMIT 10;
SELECT * FROM KFZ AS Ausschnitt LIMIT 10;
SELECT * FROM Strafzettel AS Ausschnitt LIMIT 10;
</eval>
SELECT * FROM Einwohner;
SELECT * FROM Vorstrafen;
SELECT * FROM KFZ;
SELECT * FROM Strafzettel
== Die Aufgabe ==
Als FBI-Ermittler sollst du die Datenbank verwenden, um den Tätern auf die Spur zu kommen. Du kannst davon ausgehen, dass alle Täter in der Datenbank erfasst sind und eindeutig nach dem Ausschlussprinzip bestimmt werden können. Wie bereits vom Polizeichef erwähnt handelt es sich um Profis. Du kannst davon ausgehen, dass jeder Täter eine oder mehrere Vorstrafen besitzt.
Vorgehen:
* Skizziere dir zunächst ein Datenbankschema auf einem Blatt Papier und kennzeichne die Beziehungen zwischen den einzelnen Tabellen.
* Verwende geeignete SQL Anfragen, um die Datenbank zu durchsuchen.
* Notiere dir die Namen der drei Täter und Begründe deine Entscheidung. Durch logische Schlussfolgerungen kannst du den Täterkreis einengen.
<eval id="4a9790521d0b8">
// Gib hier deine SQL Befehle ein, um zu probieren und die 3 Täter zu bestimmen.
</eval>
<eval id="4c8fc5e200452">
// Gib hier deine SQL Befehle ein...
</eval>
<eval id="4c8fc5e2005a0">
// Gib hier deine SQL Befehle ein...
</eval>
<eval id="4c8fc5e2006e3">
// Gib hier deine SQL Befehle ein...
</eval>
<popup label="Lösung">
1. Täter: Lie Jang (Er ist der einzige mit dem Vornamen "Lie" und dem passendem Alter.)
2. Täter: Egon Anderson (Seine Mutter ist Halterinn des Fluchtautos und ist die einzige männliche Person in der Familie Anderson mit Vorstrafe.)
3. Täter: Eddy Quin (Er wohnt als einziger Vorbestrafter auf der Jefferson Street, wo das Fluchtauto die Wochen vor dem Einbruch mehrere Strafzettel bekommen hat.)
</popup>
Anlegen einer Tabelle '''Leser''':
<code id="4cac9b9b9cf57">
DROP TABLE IF EXISTS leser;
CREATE TABLE leser (
L_NR varchar(255),
Name varchar(255),
Vorname varchar(255),
Geb_datum varchar(30),
PLZ varchar(255),
Ort varchar(255),
Straße varchar(255)
);
INSERT INTO leser VALUES ('001', 'Franz', 'Werner', '12.08.1969', '01259', 'Dresden', 'Ottostr.12');
INSERT INTO leser VALUES ('002', 'Bert', 'Kuni', '01.01.2010', '02625', 'Bautzen', 'Bismarkstr. 10');
INSERT INTO leser VALUES ('003', 'Hummel', 'Elfriede', '05.12.1940', '02222', 'Doosdorf', 'Nr. 5');
INSERT INTO leser VALUES ('004', 'Stramm', 'Berta', '01.02.1988', '01226', 'Dresden', 'Heideweg 7');
INSERT INTO leser VALUES ('010', 'Bunt', 'Sebastian', '11.11.1985', '02625', 'Bautzen', 'Reichenstr. 17');
</code>
<eval id="4cac9c50ee28a">
select *
from leser;
</eval>
SELECT buch2.Schlagwort, buch1.Titel, buch1.Verlag, buch1.Jahr
FROM buch1, buch2
WHERE buch1.T_NR = buch2.T_NR and ((buch2.Schlagwort)='LISP') ;
select T_Nr, Titel, Verlag, Aus_datum
from leser, ausleihe, bestand, buch1
where leser.L_NR=ausleihe.L_NR and ausleihe.reg_nr=bestand.reg_nr and bestand.T_nr=buch1.T_Nr and L_Nr='001';
Außerdem wird eine Tabelle '''Buch1''' benötigt, die die Angaben zu den Büchern enthält:
<br>
<code hidden id="4caca38a0ae95">
DROP TABLE IF EXISTS buch1;
CREATE TABLE buch1 (
T_NR varchar(255) ,
Autor varchar(255) ,
Titel varchar(255) ,
Verlag varchar(255) ,
Jahr varchar(255) ,
ISBN varchar(255)
);
INSERT INTO buch1 VALUES ('00001', 'Abelson, H. u.a.', 'Struktur und Interpretation von Computerprogrammen', 'Springer', '1991', '3-540-56934-0');
INSERT INTO buch1 VALUES ('00002', 'Appelrath, H.-J. u.a', 'Starthilfe Informatik', 'BG Teubner', '1998', '3-519-00241-8');
INSERT INTO buch1 VALUES ('00003', 'Claus, V. u.a.', 'Duden Informatik', 'Meyers Lexikonv', '1993', '3-411-05232-5');
INSERT INTO buch1 VALUES ('00004', 'Horn, Ch. u.a.', 'Lehr- und Übungsbuch Informatik, Bd. 1', 'Fachbuchverlag ', '1995', '3-343-00891-5');
INSERT INTO buch1 VALUES ('00005', 'Horn, Ch. u.a.', 'Lehr- und Übungsbuch Informatik, Bd. 3', 'Fachbuchverlag ', '1997', '3-446-18699-9');
INSERT INTO buch1 VALUES ('00006', 'Precht, M. u.a.', 'EDV-Grundwissen', 'Addison-Wesley', '1997', '3-8273-1222-1');
</code>
Lassen Sie sich die Inhalte der Tabelle buch1 anzeigen!
<eval id="4caca38a0afb3">
</eval>
Im Folgenden sind noch die Tabellen '''buch2''', '''bestand''' und '''ausleihe''' angelegt und mit Datensätzen gefüllt.<br>
Lassen Sie sich ebenfalls die Inhalte anzeigen.
<code id="4cacd69914c4f" hidden>
DROP TABLE IF EXISTS buch2;
CREATE TABLE buch2 (
T_NR varchar(255),
Schlagwort varchar(255)
);
INSERT INTO buch2 VALUES ('00001', 'Programmierung');
INSERT INTO buch2 VALUES ('00001', 'LISP');
INSERT INTO buch2 VALUES ('00002', 'Grundlagen');
INSERT INTO buch2 VALUES ('00003', 'Nachschlagewerk');
INSERT INTO buch2 VALUES ('00004', 'Grundlagen');
INSERT INTO buch2 VALUES ('00004', 'Programmierung');
INSERT INTO buch2 VALUES ('00004', 'Algorithmierung');
INSERT INTO buch2 VALUES ('00004', 'Betriebssysteme');
INSERT INTO buch2 VALUES ('00004', 'Tabellenkalkula');
INSERT INTO buch2 VALUES ('00004', 'Datenbanken');
INSERT INTO buch2 VALUES ('00005', 'Software-Engine');
INSERT INTO buch2 VALUES ('00005', 'SQL');
INSERT INTO buch2 VALUES ('00005', 'PROLOG');
INSERT INTO buch2 VALUES ('00005', 'LISP');
INSERT INTO buch2 VALUES ('00005', 'C++');
INSERT INTO buch2 VALUES ('00005', 'Objektorientier');
INSERT INTO buch2 VALUES ('00006', 'Programmierung');
INSERT INTO buch2 VALUES ('00006', 'Tabellenkalkula');
INSERT INTO buch2 VALUES ('00006', 'Datenbanken');
INSERT INTO buch2 VALUES ('00006', 'Betriebssysteme');
DROP TABLE IF EXISTS bestand;
CREATE TABLE bestand (
T_NR varchar(255),
Reg_Nr varchar(255),
vorhanden boolean
);
INSERT INTO bestand VALUES ('00001', '00001', 0);
INSERT INTO bestand VALUES ('00001', '00002', 0);
INSERT INTO bestand VALUES ('00001', '00003', 0);
INSERT INTO bestand VALUES ('00002', '00004', 0);
INSERT INTO bestand VALUES ('00003', '00005', 0);
INSERT INTO bestand VALUES ('00003', '00006', 0);
INSERT INTO bestand VALUES ('00004', '00007', 0);
INSERT INTO bestand VALUES ('00004', '00008', 0);
INSERT INTO bestand VALUES ('00004', '00009', 0);
INSERT INTO bestand VALUES ('00004', '00010', 0);
INSERT INTO bestand VALUES ('00005', '00011', 0);
INSERT INTO bestand VALUES ('00005', '00012', 0);
INSERT INTO bestand VALUES ('00005', '00013', 0);
INSERT INTO bestand VALUES ('00006', '00014', 0);
INSERT INTO bestand VALUES ('00006', '00015', 0);
INSERT INTO bestand VALUES ('', '', 0);
DROP TABLE IF EXISTS ausleihe;
CREATE TABLE ausleihe (
L_NR varchar(255) ,
Reg_Nr varchar(255) ,
Aus_datum varchar(30)
);
INSERT INTO ausleihe VALUES ('001', '00011', '01.01.2010');
INSERT INTO ausleihe VALUES ('003', '00012', '20.03.2010');
INSERT INTO ausleihe VALUES ('010', '00002', '21.05.2009');
INSERT INTO ausleihe VALUES ('002', '00007', '11.10.2010');
INSERT INTO ausleihe VALUES ('001', '00001', '05.09.2010');
</code>
<eval id="4cacd69914d8d">
</eval>
Analysieren Sie die Tabellen und finden Sie die Beziehungen zwischen diesen heraus. ([[Media:Bibo.JPG|vergleiche hier]])
<br>
Abschließend stellen Sie folgende Anfragen:
1. Welche Bücher beinhalten Ausführungen zu der Programmiersprache LISP?
2. Welche Bücher (T_Nr, Titel, Verlag) hat der Leser mit der L_Nr '001' wann ausgeliehen?
SELECT buch2.Schlagwort, buch1.Titel, buch1.Verlag, buch1.Jahr
FROM buch1, buch2
WHERE buch1.T_NR = buch2.T_NR and ((buch2.Schlagwort)='LISP') ;
select T_Nr, Titel, Verlag, Aus_datum
from leser, ausleihe, bestand, buch1
where leser.L_NR=ausleihe.L_NR and ausleihe.reg_nr=bestand.reg_nr and bestand.T_nr=buch1.T_Nr and L_Nr='001';
<eval id="4cacdaf17bbc6">
</eval>
Gegeben ist folgende, stark vereinfachte Datenbank mit den Tabellen ''Schueler'' und ''Noten'', die neben den Adressen die Prüfungsnoten der Schüler in einigen Fächern speichert.<br>
<code hidden id="4cacebae2b337">
DROP TABLE IF EXISTS schueler;
CREATE TABLE schueler (
Name varchar(20),
Vorname varchar(20) ,
Wohnort varchar(15) ,
PLZ varchar(5) ,
Telefon varchar(11) ,
Straße varchar(25) ,
Schuelernr varchar(7) ,
PRIMARY KEY (Schuelernr)
);
INSERT INTO schueler VALUES ('Scheurich', 'Erich', 'Bautzen', '02625', '40256', 'Mättigstr. 12', '1234567');
INSERT INTO schueler VALUES ('Schlenkrich', 'Ricarda', 'Teichritz', '02689', '23456', 'Dorfstr. 2', '1234568');
INSERT INTO schueler VALUES ('Angst', 'Beatrice', 'Wurschen', '02645', '67255', 'Nr. 12a', '1234569');
INSERT INTO schueler VALUES ('Waurick', 'Andre', 'Bautzen', '02625', '30445', 'Brecht-Str. 34', '1234572');
INSERT INTO schueler VALUES ('Scheurich', 'Erick', 'Bautzen', '02625', '40256', 'Mättigstr. 12', '1234573');
DROP TABLE IF EXISTS Noten;
CREATE TABLE Noten (
Schuelernr varchar(7),
Mathe integer default '0',
Deutsch integer,
Bio integer
);
INSERT INTO Noten VALUES ('1234567', 2, 3, 1);
INSERT INTO Noten VALUES ('1234568', 2, 2, 2);
INSERT INTO Noten VALUES ('1234569', 3, 4, 2);
INSERT INTO Noten VALUES ('1234572', 4, 3, 3);
INSERT INTO Noten VALUES ('1234573', 1, 1, 1);
</code>
'''1. Erstellen Sie folgende Abfragen:'''
* Gesucht sind alle Namen, Vornamen und Adressen derjenigen Schüler, deren Noten in allen Fächern besser als 3 sind.
* Gesucht sind alle Schüler aus Bautzen.
<eval id="4cacebc7a138b">
</eval>
select Name, Vorname, Wohnort, PLZ
from schueler, Noten
where schueler.schuelernr=Noten.schuelernr and (Mathe<3 and Deutsch<3 and Bio<3);
select Name, Vorname
from schueler
where Wohnort='Bautzen';
select * from schueler;
'''2. Ergänzen Sie die Datenbank um eine Tabelle Pruefer.'''<br>
mit den Attributen
* Pruefernr varchar(7)
* Name varchar(20)
* Fach varchar(4))
Füllen Sie die Tabelle mit mindestens 3 Datensätzen
'''3. Aufgabe'''<br>
Die Tabelle ist noch "losgelöst" von den anderen Tabellen.<br>
Beschreiben Sie eine Möglichkeit, damit die Tabelle ''pruefer'' in Beziehung zu den anderen steht.
<eval id="4cd111f4e4723">
begin
write(' // Hier sollte Ihr Text stehen ');
end.
</eval>
Erstelle eine Datenbank '''''CD-Versand''''': zur Verwaltung von Kundendaten, der vorhandenen CDs und des Versands an Kunden
Datenbank_SQL-Syntax zum Nachlesen: [http://hsqldb.org/doc/2.0/guide/index.html] <br><br>
{| border="1"
|-
| [[Bild:CD-Versand.jpeg|Miniatur|250px]] || [[Bild:1234.jpeg|Miniatur|250px]] || [[Bild:Diagramm2.jpeg|Miniatur|250px]]
|-
| Astrid|| Linda || David
|-
| [[Bild:Ll1.jpeg|Miniatur|250px]] || [[Bild:Modell_cd-versand.jpeg|Miniatur|250px]] || [[Bild:Dia.jpeg|Miniatur|250px]]
|-
| Jonas || Martin || Franz
|}
<code id="4cda754fb9361">
//Hier Datenstruktur anlegen
</code>
<eval id="4cda754fb947d">
//Hier Abfragen formulieren
</eval>
<eval id="4cecf79a4840c">
//Hier Abfragen formulieren
</eval>
<eval id="4cecf79a48523">
//Hier Abfragen formulieren
</eval>
zurück zu [[Datenbanken]]
'''Anforderungen an das Datenbank-Projekt'''
Bearbeiten Sie ein Datenbank-Projekt zu einem Thema Ihrer Wahl.
Dazu gehören:
* Ziel der Datenbank ("Begründung" der Themenwahl)
* Modellierung in einem ERM
* Transformation in das Relationenmodell
* Erstellen der DB (Struktur/notwendige Datensätze)
* Erstellen von verschiedenen Abfragen (unterschiedlicher „Schwierigkeit“)
Das Projekt ist komplett im PWiki zu bearbeiten.
Dabei arbeitet jeder auf einer eigenen Seite (siehe Links unten). Unterseiten werden mit "/" neu eingefügt.[[Bild:Unterseiten.JPG|right|thumb|250px|in der Adresszeile hinter die aktuelle Seite einfügen]]<br>
Wichtige Merkmale der Modelle (Kardinalität, Normalform, ...) sind zu erläutern.<br>
Das ERM ist als Bild einzufügen.
Bearbeitung des Projekts während der Unterrichtsstunden.
vorgesehener Zeitraum: