Datenbanken I

Datenbanken I Zusammenfassung
Datenbanken I
Views
Verwendung
Ein View kapselt eine bestimmte Implementierung.
Ein zentral erstellter View kann bei Schemaänderungen angepasst werden.
Häufig verwendete identische Abfragen werden einmal zentral durch einen View definiert.
Syntax
CREATE VIEW TeleView AS
SELECT name, vorwahl, rufnummer
FROM Telephon
WHERE vorwahl = ‘0711’ OR lebensalter < 25
Primärschlüssel, Verweise und Itegritätsbedingungen
Begriffe
- Kardinalität (Die Anzahl der Zeilen einer Relation)
- Tupel (Eine einzelne Zeile einer Relation)
- Primary Key (ermöglicht die eindeutige Identifikation eines Tupels)
- Domäne (der Wertebereich, den eine Spalte einer Relation annehmen kann) Beispiel, die
Domäne der Wochentage {Mo, Di, Mi, Do, Fr, Sa, So}
Relationen
Als Ausgangspunkt betrachten wir zwei Mengen M={Tisch, Stuhl, Vase} und F={rot, gelb} und bilden
aus diesen die Menge aller Kombinationen, die so genannte Kreuzmenge:
K=M×F={(Tisch, rot),(Stuhl, rot),(Vase, rot),(Tisch, gelb),(Stuhl, gelb),(Vase, gelb)}
Diese Kreuzmenge K darf Menge genannt werden, da alle Wertepaare untereinander verschieden
sind.
Wir können nun eine Teilmenge R der Kreuzmenge betrachten, z.B.:
R={(Stuhl, rot),(Tisch, rot),(Tisch, gelb)} K
Eine solche Teilmenge R der Kreuzmenge K heißt in der Mengenlehre eine Relation zwischen den
Mengen M und F.
Eine Relation ist eine logisch zusamenhängende Einheiten von Informationen. Bestehend aus einer
festen Anzahl von Attributen (Spalten) und einer variablen Anzahl von Tupeln (Datensätzen)
Eigenschaften
- keine doppelten Tupel (d.h. Tupel, bei denen alle Attribute gleichen Inhalt haben)
- die Reihenfolge, in der die Tupel in der Relation gespeichert sind, ist nich definiert
- die Reihenfolge der Attribute in der Relation ist nicht definiert (ansprechen nur mit dem
Namen des Attributs)
- Die Werte eines Attributs unterliegen einer Domäne. Und sind deshalb atomar.Ist diese
Eigenschaft erfüllt entspricht die DB der 1. Normalform
Candidate Key
Der C. ist ein eindeutiger Schlüssel, dass heisst, dass alle Werte der Attributmenge, die den Schlüssel
bilden eindeutig (unique) sind. Ausserdem dürfen keine überflüssigen Attribute Teil des Schlüssels
sein, jedes A ist für die Eindeutigkeit notwendig. Jede Relation hat mindestens einen Candidate Key
(wegen der Eigenschaft keine doppelten Tupel) Candidate Keys werden in SQL über das
Schlüsselwort UNIQUE definiert.
Primary Keys
Der P. dient dazu, ein Tupel eindeutig zu identifizieren. Primary Keys sind also Candidate Keys. PK
dürfen keine Nullwerte enthalten. Der PK ist quasi die Adresse des Tupels.
1/7
ver. 0.1| mh066
Datenbanken I Zusammenfassung
Foreign Key
Ein Schlüssel wird Foreign Key gennant, wenn sein Wertebereich in einer anderen Relation definiert
wird. FK dienen dazu, unterschiedliche Relationen in Beziehung zu setzen.
Relationen und Schemata
Definition
Es gibt Relationen die syntaktisch voneinander abhängig sind z.B. die Relationen Lieferung,
Lieferanten, Teile
Tritt nun ein Fehler beim Erstellen einer der Relationen auf, so bedeutet dies, eine teilweise,
unvollständige Umsetzung des Konzepts. Dieser Zustand ist prinzipiell unbefriedigend, da die
Relationen sogar konzeptionell eine Einheit bilden.
Aus diesem Grund gibt es die Möglichkeit einer Schemadefinition , in welcher entweder alle am
Schema beteiligten Relationen erstellt werden, oder überhaupt keine.
Syntax
CREATE SCHEMA AUTHORIZATION dbo
CREATE TABLE Teil(
tid CHAR(4) PRIMARY KEY
,tname CHAR(20)
,farbe CHAR(15)
,gewicht NUMERIC(6)
,stadt CHAR(25)
)
CREATE TABLE Lieferanten(
lid CHARR(4) PRIMARY KEY
,lname CHAR(20)
,status NUMERIC(3)
,stadt CHAR(25)
)
CREATE TABLE Lieferung(
lsid NUMERIC(5) PRIMARY KEY
,tid CHAR(4)
,lid CHAR(4)
,menge NUMERIC(6)
,UNIQUE(tid,lid)
,FOREIGN KEY (tid) REFERENCES Teil
,FOREIGN KEY (lid) REFERENCES Lieferanten
)
Erzwingen von Integritätsregeln
CHECK Constraint
Beispielsweise soll die Lieferantenidentität lid im Lieferanten/Teile Beispiel stets mit einem "L"
beginnen und danach von einer Ziffer gefolgt werden, Rest beliebig (%). Wir erzielen dies mittels einer
CHECK Klausel
Syntax
CREATE TABLE Namen(
name CHAR(5)
,CHECK (name LIKE "L[0-9]%")
)
CREATE TABLE Blume(
name CHAR(10)
,farbe CHAR(10)
,CHECK (farbe IN ('rot','gelb','blau','grün'))
)
2/7
ver. 0.1| mh066
Datenbanken I Zusammenfassung
Garantieren der referenziellen Integrität
Bislang haben wir über Datenbankzustände mit referentieller Integrität gesprochen. Es stellt sich die
Frage, wie diese bei allgemeinen Operationen wie update, insert und delete zu garantieren ist.
RESTRICT
Die Löschoperation wird auf diejenigen Datensätze begrenzt, für welche keine
referenzierenden Foreign Keys in anderen Relationen existieren. In unserem Beispiel kann die
Lieferung 1 nicht gelöscht werden.
CASCADE
Die Löschoperation planzt sich auf alle Datensätze in referenzierenden Relationen mit
passenden Foreign Keys fort. In unserem Beispiel werden die Lieferung 1 und 10 ebenfalls
gelöscht.
Operationen in SQL
Selektion durch eine Indexmenge
Eine weitere Möglichkeit zur Bildung eines logischen Ausdrucks ist die Bedingung, dass ein Attribut in
einer Menge von Werten enthalten ist. Die Menge ist in diesem Beispiel {0711, 0221}:
Syntax
SELECT *
FROM Telephon
WHERE vorwahl IN ('0711','0221')
Subqueries
Definition
Dabei wird die im SELECT Zweig enthaltene Aufzählung ersetzt durch eine Abfrage, deren Ergebnis
aus einer Relation mit einem einzigen Attribut besteht.
Bedingung für Subqueries
- Das Ergebnis der "inneren" Abfrage darf nur aus genau einer Spalte bestehen.
- Der Datentyp des Ergebnisses der "inneren" Abfrage muss zum Typ des Attributs im WHERE Clause
der "äußeren" Abfrage passen.
Syntax
SELECT *
FROM Telephon
WHERE vorwahl IN (
SELECT vorwahl
FROM Stadtvorwahl
WHERE bundesland = 'BaWü'
)
Reguläre Ausdrücke
Die Sprache SQL erlaubt über das Schlüsselwort LIKE die Einbindung von Suchmustern. Reguläre
Ausdrücke können auch in CHECK CONSTRAINTS von CREATE TABLE Anweisungen auftreten
SELECT *
FROM Orte
WHERE name LIKE '[0-9][0-9][0-9][0-9] [A-Z]%'
Automatische Vergabe von Zählern
Syntax
CREATE SCHEMA AUTHORIZATION dbo
CREATE TABLE Rechnung(
rechNr NUMERIC(5) IDENTITY PRIMARY KEY
,name CHAR(16)
,datum DATETIME
)
3/7
-- Automatische Vergabe --
ver. 0.1| mh066
Datenbanken I Zusammenfassung
Aggregatfunktionen
Funktion und Syntax
AVG (Durchschnitt)
MIN (Minimum)
MAX (Maximum)
COUNT (Zähe Stand)
MIN (Zähle Alle)
SELECT
SELECT
SELECT
SELECT
SELECT
AVG(stand) AS Durchschnitt FROM Konto
MIN(stand) AS Minimum FROM Konto
MAX(stand) AS Maximum FROM Konto
COUNT(stand) AS non_null_stand FROM Konto
COUNT(*) AS selected_lines FROM Konto
Die Verwendung des Schlüsselwortes DISTINCT führt zu einer Eliminierung doppelt vorhandener
Werte vor der Auswertung der jeweiligen Aggregatfunktion!
Benutzerdefinierte Datentypen
Erstellen eines Benutzerdefinierten Datentyps über stored procedures:
Syntax:
EXECUTE sp_addtype POSITIONSNUMMER, 'NUMERIC(5)', 'IDENTITY'
EXECUTE sp_addtype BEZEICHNUNG, 'CHAR(20)'
EXECUTE sp_addtype GEWICHT, 'NUMERIC(5)'
Unter Verwendung der zuvor definierten Datentypen kann nun eine Relation erstellt werden:
CREATE TABLE Lieferung(
posNr POSITIONSNUMMER
,bezeichnung BEZEICHNUNG
,gewicht GEWICHT
)
Löschen von Benutzerdefinierten Datentypen über sp_droptype GEWICHT
Das Entity-Relationship Modell
Weak Entities
Weak Entities sind nur identifizierbar durch ihre Beziehung zu einem "Besitzer". Wir betrachten
als Beispiel Familien mit Kindern: Die Kinder haben als Attribute den Vornamen und das
Geburtsdatum. Kinder verschiedener Eltern können in allen Attributen übereinstimmen.
Für die Eltern kann ein Elternteil als identifizierender Stellvertreter verwendet werden, im
gewählten Beispiel der Vater. Ein Kind wird nun indirekt durch seinen Bezug zum Vater und
dessen eindeutigem Merkmal svNr identifiziert.
Weak Entities haben immer eine totale Teilnahme in einer Relation zu ihrer "Besitzer"-Entity. Die
4/7
ver. 0.1| mh066
Datenbanken I Zusammenfassung
Umkehrung gilt nicht: Ein Führerschein hat ebenfalls eine totale Teilnahme in der Relation zu
seinem Besitzer. Dennoch kann der Führerschein unabhängig von seinem Besitzer durch die
Seriennummer identifiziert werden und ist daher keine Weak Entity.
Repräsentation
1. Fall
Dies ist der einfachste Fall: Wir bilden auf der "n" Seite einen Foreign Key auf den Primärschlüssel der
"1" Seite:
CREATE SCHEMA AUTHORIZATION dbo
CREATE TABLE Studiengang(
bezeichnung CHAR(10) PRIMARY KEY
,dauer NUMERIC(5)
)
CREATE TABLE Student(
matrNr NUMERIC(10) PRIMARY KEY
,name CHAR(10)
,studiert CHAR(10) REFERENCES Studiengang
)
5/7
-- Matrikel-Nr. --- Familienname --- Fachrichtung --
ver. 0.1| mh066
Datenbanken I Zusammenfassung
2. Fall
Wir nehmen an, ein Student dürfte auch eingeschrieben sein, wenn er keinem Studiengang angehört.
Ein Student kann also einem Studiengang zugeordnet sein oder nicht. Im letzteren Fall hat ein
Studentensatz keinen Verweis auf einen Studiengang. Wir können dies in SQL abbilden, indem wir für
den Verweis NULL Werte zulassen:
CREATE TABLE Student(
svNr CHAR(10) PRIMARY KEY
,name CHAR(10)
,studiert CHAR(10) NULL
REFERENCES Studiengang
)
-- Sozialvers.-Nr --- Familienname --- Fachrichtung --
3.Fall
Interessant ist nun der Fall, wenn wir erlauben, dass jeder Student in mehreren Studiengängen
eingeschrieben sein kann, wir haben dann den Fall einer n zu m Relation.
CREATE SCHEMA AUTHORIZATION dbo
6/7
ver. 0.1| mh066
Datenbanken I Zusammenfassung
CREATE TABLE Studiengang(
bezeichnung CHAR(10) PRIMARY
,dauer NUMERIC(5)
)
CREATE TABLE Student(
svNr NUMERIC(10) PRIMARY KEY
,name CHAR(10)
)
CREATE TABLE Matrikel(
studiert CHAR(10) REFERENCES
,svnr NUMERIC(10) REFERENCES
,PRIMARY KEY(studiert, svnr)
Studiengang -)
KEY
-- Sozialvers.-Nr --- Familienname -Studiengang
Student
-- Max. ein Eintrag pro Student u.
Normalformen
Informelle Regeln zum Erzielen eins guten Datenbankdesigns
- Bedeutung der Attribute
- Vermeiden redundanter Informationen
- Vermeiden von NULL Werten
Regel zur Normalisierung von Datenbanken
Nicht Normalisierte Datenbank Es exisitieren Wiederholungsfelder
Erste Normalform ist erfüllt
„Felder, die wiederholt auftreten in eine gesonderte Tabelle
auslagern“.
Zweite Normalform ist erfüllt
„Felder aus 1.NF-Tabelle entfernen, die vom zusammengesetzten
Primärschlüssel funktional abhängig sind“.
Dritte Normalform ist erfüllt
„Felder aus 2.NF-Tabellen entfernen, die von anderen NichtSchlüsselfelder funktional abhängig sind. Informationsneutrale
Sortierbarkeit sicherstellen“.
Eine Normalisierte Datenbank ist Redundanzfrei!
7/7
ver. 0.1| mh066