Teil 2 -
Werbung
Teil 2 6. Vorlesung Modul: Programmierung B-PRG Grundlagen der Programmierung II Professur für Datenbanken und Informationssysteme Dr. Karsten Tolle [email protected] 1 Schlüssel ? PLZ ORT STRASSE_NR 30419 Hannover Schaumburgstr. 2 30419 Hannover Quetlinburger Weg 12 37308 Schirmberg Bergstraße 1 37308 Bodenrode Hauptstraße 12 37308 Geismar Bergstraße 1 35279 Neustadt Gartenstraße 7 53577 Neustadt Gartenstraße 7 keine Abhängigkeiten Schlüssel: PLZ, ORT, STRASSE_NR 2 Grundlagen der Programmierung II DBIS - SS2011 Fahrzeuge der Firma AB HERSTELLER KENNZEICHEN ZUGEL_GES_GEW FÜHRERSCHEIN VW F-AB 123 bis 3.5t B MB F-AB 234 bis 7.5t C1 MB F-AB 235 bis 7.5t C1 MAN F-AB 236 bis 12t C MB F-AB 239 bis 12t C … … … Abhängigkeiten? 3 Grundlagen der Programmierung II DBIS - SS2011 Fahrzeuge der Firma AB HERSTELLER KENNZEICHEN ZUGEL_GES_GEW FÜHRERSCHEIN VW F-AB 123 bis 3.5t B MB F-AB 234 bis 7.5t C1 MB F-AB 235 bis 7.5t C1 MAN F-AB 236 bis 12t C MB F-AB 239 bis 12t C MB F-AB 230 bis 17t C … … … … … aus ZUGEL_GES_GEW folgt hier FÜHRERSCHEIN: ZUGEL_GES_GEW FÜHRERSCHEIN … umgekehrt nicht! 4 Grundlagen der Programmierung II DBIS - SS2011 … ein wenig abgewandelt … HERSTELLER TYP ZUGEL_GES_GEW FÜHRERSCHEIN VW Transporter bis 3.5t B MB Laster bis 7.5t C1 MB Laster bis 7.5t C1 MAN Laster bis 12t C MB Lastzug bis 17t CE MB Lastzug bis 7t CE … … … … … aus ZUGEL_GES_GEW und TYP folgt hier FÜHRERSCHEIN: ZUGEL_GES_GEW TYP FÜHRERSCHEIN 5 Grundlagen der Programmierung II DBIS - SS2011 … ein wenig mehr abgewandelt … HERSTELLER TYP ZUGEL_GES_GEW FÜHRERSCHEIN VW Transporter bis 3.5t B MB Laster bis 7.5t C1 MB Laster bis 7.5t C1 MAN Laster bis 12t C MB Lastzug bis 17t CE MB Lastzug bis 7.5t C1E VW Lastzug bis 12t C1E … … … … … C1E: (nur mit C1) C1 Fahrzeuge + Anhänger von mehr als 750 kg bis max. 12 t 6 Grundlagen der Programmierung II DBIS - SS2011 Funktionale Abhängigkeit (FD) Seien X und Y Teilmengen von R. Eine Relation r(R) erfüllt (satisfies) die funktionale Abhängigkeit (functional dependency) FD X → Y, wenn für je zwei (beliebige) Tupel u, v ∈ r(R) gilt: u(X) = v(X) ⇒ u(Y) = v(Y). X → Y ⇔ u(X) = v(X) ⇒ u(Y) = v(Y) 7 Grundlagen der Programmierung II DBIS - SS2011 Beispiel Gegeben ist die Relation r(R): A B C D E a1 b1 c1 d1 e1 a1 b2 c2 d2 e1 a2 b1 c3 d2 e1 a2 b1 c4 d3 e1 a3 b2 c5 d1 e1 Geben Sie an, welche der folgenden Abhängigkeiten r nicht widerspricht: A→D AB → D C → BDE E→A 8 A→E A → BC Grundlagen der Programmierung II DBIS - SS2011 Schlüssel Ein Schlüssel identifiziert eine Entität. Er besteht aus einer Menge von Attributen, deren Werte alle Instanzen einer Entität eindeutig bestimmen. Ein Schlüssel (key) einer Relation r(R) ist eine minimale Teilmenge K von R, so dass für je zwei verschiedene Tupel t1, t2 ∈ r gilt: • t1(K) ≠ t2(K) und • keine echte Teilmenge K' von K hat diese Eigenschaft. Ein Schlüssel kann als Integritätsbedingung angesehen werden. Falls K Schlüssel von r(R), t1 ∈ r, t1(K) = t2(K), t1 ≠ t2 dann dürfte t2 nicht in r(R) eingefügt werden. 9 Grundlagen der Programmierung II DBIS - SS2011 Schlüssel Gegeben seien ein Relationenschema R und eine Menge F von FDs. X ⊆ R ist ein Oberschlüssel für R ⇔ X→R X ist ein Schlüssel für R ⇔ X → R und X minimal (¬(∀A∈X: X\A → R)) 10 Grundlagen der Programmierung II DBIS - SS2011 1. Normalform FIRSTNME LASTNME BIRTH STREET NUMBER TOWN ZIP Jon Lucas 12.03.1969 {Zeil, Lange Str.} {12, 114} {Frankfurt, Frankfurt} {60313, 60313} Lucas Jon 24.12.1980 Gräfstr. 27 Frankfurt 60325 … … … … … … … FIRSTNME LASTNME BIRTH STREET NUMBER TOWN ZIP Jon Lucas 12.03.1969 Zeil 12 Frankfurt 60313 Jon Lucas 12.03.1969 Lange Str. 114 Frankfurt 60313 Lucas Jon 24.12.1980 Gräfstr. 27 Frankfurt 60325 … … … … … … … DBIS - SS2011 1. Normalform Definition: Ein Relationenschema R ist in 1. Normalform (1NF), wenn die Wertebereiche aller Attribute von R atomar sind. 1.NF ⇔ Wertebereiche atomar 12 Grundlagen der Programmierung II DBIS - SS2011 vorrat (Teil Lager Menge Lageradresse) 1 101 1 25 Waag. 10 102 3 410 Krugerstr. 42 102 1 300 Waag. 10 112 4 10 Brunnerstr. 105 Probleme: Redundanz – Lageradresse für jedes Teil im Lager redundant gespeichert Einfüge-Anomalie – Kein Teil ohne Lager eingefügt werden Lösch-Anomalie – Lager ohne Teile könnten gelöscht werden 13 Grundlagen der Programmierung II DBIS - SS2011 vorrat (Teil Lager Menge Lageradresse) 101 1 25 Waag. 10 102 3 410 Krugerstr. 42 102 1 300 Waag. 10 112 4 10 Brunnerstr. 105 F = { Teil Lager → Menge Lageradresse Lager → Lageradresse } 14 Grundlagen der Programmierung II DBIS - SS2011 Prim Ein Attribut A heißt prim in R, wenn es in einem Schlüssel von R enthalten ist, sonst heiße es nicht prim. A ist prim ⇔ X: X ist Schlüssel, A∈X 15 Grundlagen der Programmierung II DBIS - SS2011 volle funktionale Abhängigkeit Eine funktionale Abhängigkeit X → Y heißt volle funktionale Abhängigkeit, wenn für keine Teilmenge X´ ⊂ X, X´ → Y gilt. Y heißt dann voll funktional abhängig von X. X → Y voll funktional ⇔ ∃ X´⊂ X : X´ → Y 16 Grundlagen der Programmierung II DBIS - SS2011 2. Normalform Ein Relationenschema R ist in 2. Normalform (2NF), wenn es in 1NF ist und jedes nicht prime Attribut voll funktional von jedem Schlüssel von R abhängig ist. 2. NF ⇔ A nicht prim ⇒ Schlüssel → A voll funktional Die 2. NF ist verletzt, wenn ein Teil eines Schlüssels ein Nicht-Schlüsselattribut funktional bestimmt. 17 Grundlagen der Programmierung II DBIS - SS2011 Beispiel 1 vorrat (Teil Lager Menge Lageradresse) 101 1 25 Waag. 10 102 3 410 Krugerstr. 42 102 1 300 Waag. 10 112 4 10 Brunnerstr. 105 F = { Teil Lager → Menge Lageradresse Lager → Lageradresse } vorrat 18 (Teil Lager Menge lager 101 1 25 1 Waag. 10 102 3 410 3 Krugerstr. 42 102 1 300 4 Brunnerstr. 105 112 4 10 Grundlagen der Programmierung II (Lager Lageradresse) DBIS - SS2011 Beispiel 2 R = ABCD F = {AB→CD, B→D} B→D verletzt 2. NF Zerlegung in: R1 = (ABC) mit F1 = {AB→C} R2 = (BD) mit F2 = {B→D} 19 Grundlagen der Programmierung II DBIS - SS2011 2. NF ? ID HERSTELLER ZUGEL_GES_GEW FÜHRERSCHEIN 1 VW bis 3.5 B 2 MB bis 7.5 C1 3 MB bis 8.5 C 4 MAN bis 12 C 5 MB bis 12 C 6 VW bis 12 C … … … F = { ID R, ZUGEL_GES_GEW 20 FÜHRERSCHEIN} Grundlagen der Programmierung II DBIS - SS2011 Trotzdem Probleme ? ID HERSTELLER ZUGEL_GES_GEW FÜHRERSCHEIN 1 VW bis 3.5 B 2 MB bis 7.5 C1 3 MB bis 8.5 C 4 MAN bis 12 C 5 MB bis 12 C 6 VW bis 12 C … … … 21 Grundlagen der Programmierung II DBIS - SS2011 Normalformen Über die Normalformen (NF) werden Kriterien definiert, um Redundanzen und Anomalien zu verhindern. Es gibt: 1 NF 2 NF 3 NF 4 NF 5 NF 6 NF BCNF 1 NF 2 NF 3 NF… DBIS - SS2011 Normalisierung / Denormalisierung Normalisierung: Der Prozess des Aufspaltens von Relationen, um Anomalien/Redundanzen zu verhindern. Denormalisierung: Der Prozess des Zusammenlegens von Relationen, um Performanz zu gewinnen. DBIS - SS2011 Vorgehen beim Prüfen auf NF (WICHTIG!!!) Ausgangspunkt: Relation R (mind. 1. NF) und Menge der FDs gegeben. 1. Welche Schlüssel gibt es? nicht primen Attribute! was sind die 2. Prüfe auf 2. NF (voll funktional abhängig von ALLEN Schlüsseln?) 24 Grundlagen der Programmierung II DBIS - SS2011 SQL (ein Einblick) Structured Query Language DBS – Sprachen Das DBS stellt als Schnittstelle eine Datenbanksprache(n) für die folgenden Zwecke zur Verfügung: • Datenabfrage und -manipulation – Data Manipulation Language (DML) • Verwaltung der Datenbank – Data Definition Language (DDL) • Berechtigungssteuerung – Data Control Language (DCL) z.B. SQL DBIS - SS2011 Structured Query Language SQL ist für Relationale Datenbanksysteme! Standards: SQL-1 von 1986 bzw. 1989 (ca. 120 Seiten) SQL-2 (SQL92) von 1992 (ca. 580 Seiten) http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt SQL-3 (SQL99) von 2000 (ca. 1200 Seiten) http://www.cse.iitb.ac.in/dbms/Data/Papers-Other/SQL1999/ansi-iso-9075-2-1999.pdf SQL 2003 - ISO/IEC 9075:2003 SQL:2006 - ISO/IEC 9075-14:2006 (SQL/XML) DBIS - SS2011 Erstellen - create Einfügen - insert Anfragen - select Was braucht man zum üben/testen? 28 Grundlagen der Programmierung II DBIS - SS2011 SQL-Online-Tutorial http://sqlzoo.net/ DBIS - SS2011 SQL Online Tutorial Anfragen können interaktiv ausgeführt werden. DBIS - SS2011 Tabellen erstellen Eine Tabelle wird im Minimalfall mit ihrem eindeutigen Namen sowie der Liste der zugehörigen Attribute samt Domänen nach folgendem Schema definiert: create table Relations-Name ( Attribut-Name Domäne { , Attribut-Name Domäne}∗ ); DBIS - SS2011 die wichtigsten SQL Datentypen • integer • blob(n) • character(n) • … • varchar(n) • float(m) • date • time • timestamp • clob(n) DBIS - SS2011 create table Konto ( KtoNr integer, KundenName varchar(25), FilialName varchar(25), Saldo real ); create table Kunde ( Name varchar(25), Vorname varchar(25), Straße varchar(25), Stadt varchar(25), GebDatum date ); DBIS - SS2011 Primärschlüssel Mittels der Klausel primary key kann eine unter den Attributfolgen einer Relation – bei der Definition der Tabelle – als Primärschlüssel ausgezeichnet werden. Die Benutzung dieser Klausel ist nur einmal pro Relation gestattet. create table Konto ( KtoNr integer primary key not null, KundenName varchar(25), FilialName varchar(25), Saldo real ) ; abhängig vom DBMS, ob „not null“ nötig ist DBIS - SS2011 Primärschlüssel Wenn mehr als ein Attribut als Primärschlüssel definiert werden sollen, wird die Klausel in der Form primary key (Attributnamen-Liste) verwendet. create table Transaktion ( vonKtoNr integer not null, anKtoNr integer not null, Datum date not null, Betrag real, primary key (vonKtoNr, anKtoNr, Datum) ) ; DBIS - SS2011 Einfügen von Tupeln Um Daten einzufügen, spezifiziert man entweder das Tupel, das eingefügt werden soll … Die Werte für die Attribute der Tupel müssen aus der Domäne der Attribute sein. insert into Kunde values ( 'Otto', 'Hans', 'Bäckerweg 12', 'Frankfurt', '1970-12-01' ) ; Der Kunde "Hans Otto" wird eingefügt. DBIS - SS2011 Syntaktische Grundform der SQL-Anfrage select A1,A2, ... ,An from R1, R2, ... ,Rm [where conditions] [group by clause] [having clause] [order by clause]; DBIS - SS2011 Anfragen ohne Bedingungen Filiale ( Name Leiter Stadt Einlagen ) select Name, Leiter from Filiale; select Leiter, Name from Filiale; select Stadt from Filiale; select * from Filiale; DBIS - SS2011 SQL verwirklicht das Prinzip der „Vielfachmenge“ (engl. multiset). In den Ergebnismengen können demnach Duplikate auftreten. Sind keine Duplikate erwünscht, müssen sie explizit durch den Zusatz distinct entfernt werden. select distinct Stadt from Filiale; DBIS - SS2011 Aggregatfunktionen Die sog. Aggregatfunktionen können in der select-Klausel anstelle von einzelnen Attributen angegeben werden. Ergebnis einer Aggregatfunktion ist ein Wert, kein Tupel. select count(∗) as AnzahlKonten from Konto; DBIS - SS2011 Aggregatfunktionen • min( A ) zur Berechnung des Minimalwerts aller Tupel unter dem Attribut A. • max( A ) zur Berechnung des Maximalwerts aller Tupel unter dem Attribut A. • avg( [ distinct ] A ) zur Berechnung des Durchschnittswerts aller Tupel unter dem Attribut A, wobei unter Angabe von distinct mehrfach gleiche Werte nur einmal in die Berechnung eingehen. • sum( [ distinct ] A ) zur Berechnung der Summe aller Tupel unter dem Attribut A, wobei unter Angabe von distinct mehrfach gleiche Werte nur einmal in die Berechnung eingehen. • count( ∗ ) zum Zählen der Tupel der betrachteten Relation. • count( [ distinct ] A) zum Zählen der Tupel der betrachteten Relation, wobei zunächst eine Duplikateneliminierung bezogen auf Werte unter dem Attribut A stattfindet. DBIS - SS2011 where-Klausel Bezüglich der Bedingung sind Vergleiche mit den üblichen Operatoren, den logischen Verknüpfungen and und or sowie beliebige Klammerungen gestattet. Konto ( KontoNr KundenNr FilialName Saldo ) select * from Konto where Saldo > 5000 or KundenNr <= 100; DBIS - SS2011 Textvergleiche - LIKE Kunde ( KundenNr Name Vorname Straße Stadt ) select KundenNr from Kunde where Name like 'To%' and Vorname not like '_arste%'; Bem.: % für beliebige Zeichenfolgen _ für genau ein Zeichen DBIS - SS2011 Anfrage über mehrere Relationen Werden in der from-Klausel mehrere Relationen spezifiziert, so erfolgt die Berechnung des kartesischen Produktes. Konto ( KontoNr KundenNr FilialName Saldo ) Filiale ( Name Leiter Stadt Einlagen ) select * from Filiale, Konto; … im Ergebnis wird jeder DS auf Filiale mit jedem DS aus Konto verbunden! DBIS - SS2011 Beispiel seit Person AusweisNr. 45 (0:n) lebt_in (0:n) Name Vorname Ort PLZ PERSON (AusweisNr., Name, Vorname) ORT (PLZ, Ortsname) LEBT_IN (AusweisNr., PLZ, seit) Grundlagen der Programmierung II Ortsname DBIS - SS2011 Beispiel mit Instanzen PERSON Ort AusweisNr Name Vorname PLZ Ortsname 001 Jon Lucas 501 Buli 003 Jon Smith 503 Wali 103 Lucas Jon 603 Kali LEBT_IN AusweisNr PLZ seit 001 501 23.12.2000 003 501 17.08.2004 001 503 01.01.1999 Jon Lucas (001) lebt_in Buli (501), seit dem 23.12.2000! 46 Grundlagen der Programmierung II DBIS - SS2011 CREATE TABLE EMPLOYEE (FIRSTNME varchar(20), LASTNME varchar(20), BIRTH date, Foreign Key (ADR_SID) references ADDRESS(ID)); Fremdschlüssel / Forein Key FIRSTNME LASTNME BIRTH ADR_ID Jon Lucas 12.03.1969 1 Jon Smith 30.09.1973 2 Lucas Jon 24.12.1980 3 Jon Smith 24.12.1976 4 Lucas Smith 01.01.1975 1 ID STREET NUMBER TOWN ZIP 1 Zeil 12 Frankfurt 60313 2 Lange Str. 114 Frankfurt 60313 3 Gräfstr. 27 Frankfurt 60325 4 Gundhofstr. 27 MörfeldenWalldorf 64546 DBIS - SS2011 Join – Verknüpfung von Tabellen Konto ( KontoNr KundenNr FilialName Saldo ) Filiale ( Name Leiter Stadt Einlagen ) select * from Filiale, Konto where Filiale.Name = Konto.FilialName; alternativ mit Alias select * from Filiale F, Konto K where F.Name = K.FilialName; DBIS - SS2011 Weitere Dinge … Inner und Outer Join Alter und Drop Table Mengenoperationen: Union, Minus, Intersect Sichten/Views Index Trigger … und vieles vieles mehr!!!! • … auch abhängig vom DBMS und der SQL-Version DBIS - SS2011
