Föreläsning SQL - Course material created by nikos dimitrakas

ITK3:DB/EIT:DB ht2008
nikos dimitrakas
ITK3:DB/EIT:DB
Databasmetodik
nikos dimitrakas
[email protected]
Rum 6626
Kapitel 1, 2, 3 och 13
1
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
•
•
•
•
•
•
•
•
•
Innehåll
Introduktion till databasmetodik
Relationsmodellen
Normalisering
Relationsalgebra
Databasmodellering
SQL
Databashanteringssystem
Embedded SQL i Java
Trender och fördjupning
2
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
•
•
•
•
•
•
Upplägg
Föreläsningar
Lektioner
Introduktioner
Labbar
Quizzar
Projektarbete
– Redovisningar
– Inlämning
– Handledning
• Tenta
Närvarouppföljning!
3
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Material
• Bok
– Database Systems, Connoly/Begg, upplaga 3 / 4
•
•
•
•
•
Föreläsningsbilder
Delkursanvisningar
Introduction to Microsoft Access
MySQL Essentials
Annat material
–
–
–
–
Exempeldatabaser
Gamla tentor
Lösningsförslag till lektioner
Extra övningar
4
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Examination
• Examination 1 (3 hp) F-A eller U-VG
– Tenta
• Examination 2 (4,5 hp)
– Projektuppgift
– 2 Labbar
– 3 Quizzar
• ”Examination” 3 (0 hp)
– Utvärdering
5
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Övrigt
• Läs igenom delkursanvisningarna
–
–
–
–
Förslag på arbetsgång
Handledning
Projektarbetet
Gruppindelning (projektet)
» 3 per grupp
» Anmäl er i Daisy
– Datormiljö
» FirstClass (EITKDB)
» Daisy
» Rational Rose
» Modellator
» Access
» MySQL
» Java
– Och mycket annat!
6
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Databaser
• Vad är data?
– Värden
• Vad är en databas?
– En gemensam samling av logiskt relaterade data för att möta en
verksamhets informationsbehov
– A shared collection of logically related data, and a description of this
data, designed to meet the information needs of an organization
7
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Databasegenskaper
• Avspeglar en minivärld, "Universe of Discourse" (UoD)
• En databas är en logiskt sammanhängande samling
data med viss inbyggd betydelse
– Data Dictionary / Metadata / Modell
• En databas är skapad för ett visst syfte och för en viss
användargrupp (eller applikationer)
8
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Filbaserade system
• Data lagras i filer
• Varje applikation använder sina filer
• Problem:
–
–
–
–
–
–
Redundans
Svåråtkomliga data
Databeroende: program är beroende av filernas struktur
Oflexibelt
Kompatibilitetsproblem
En användare i taget
9
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
•
•
•
•
•
•
•
•
Mål / Fördelar (med databaser)
Gemensam resurs
Tillgänglighet
Centraliserad kontroll
Aktualitet
Ej redundans (= konsekvens)
Flexibilitet - utbyggbarhet
Generella säkerhetssystem
Dataoberoende
10
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Dataoberoende
• Data oberoende av program
• 3-nivåer (ANSI-SPARK)
– Extern
» Användarnas vyer
– Konceptuell
» Datamodellen för hela organisationen
– Intern
» Fysisk lagring av data
11
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
ANSI-SPARK - dataoberoenden
12
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
ANSI-SPARK - dataoberoenden
• Logiskt dataoberoende
– Ändringar kan göras i det konceptuella schemat, utan att externa
scheman eller applikationer behöver ändras
• Fysiskt dataoberoende
– Ändringar kan göras i det interna schemat, utan att det konceptuella
schemat behöver ändras
13
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Databashanteringssystem
• DBHS (DataBasHanteringsSystem)
• DBMS (DataBase Management System)
• Definition av databasschemat (modellen)
– Regler för integritet
•
•
•
•
Mellan användaren (applikationen) och data
Behörighetskontroller
Konsekvens och samtidighet
Återställning
14
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Roller
• Dataadministratör (DA)
– Dataadministratören (DA) har till uppgift att ansvara för
organisationens data och innebär planering, utveckling och underhåll
av standarder, policy och rutiner, samt konceptuell och logisk
databasutformning.
– DA är involverad i de tidiga etapperna av en databas livscykel
• Databasadministratör (DBA)
– Databasadministratören är ansvarig för implementationen och driften
av databasen, inklusive säkerhet (back-up, återställning), behörighet,
integritet och prestanda.
15
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Roller
• Databasdesigner
– Analyserar verksamheten och modellerar en databas utifrån
verksamhetens databehov (konceptuell och logisk design) (DA)
– Analyserar databasens användning och anpassar den för bättre
prestanda, säkerhet och integritet (fysisk design) (DBA)
• Applikationsutvecklare
– Analyserar processer i organisationen och utvecklar
användarapplikationer som använder sig av databasen för att stödja
processerna och aktörerna
• (Slut)Användare
– Använder databasen via en applikation, oftast omedvetna om att en
databas existerar
– Använder databasen direkt via DBHS (DA, DBA, expertanvändare)
16
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Databasevolutionen
• Filbaserade system
• Första generations DBHS
–
–
–
–
–
Hierarkiska modellen
Nätverksmodellen
CODASYL / DBTG
DDL & DML
1960-talet
• Andra generations DBHS
–
–
–
–
Relationsmodellen
Codd
SQL
1970-talet
• Tredje generations DBHS
– Objektorienterade
– Objekt-relations
– Slutet på 1980-talet
17
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Risker / Nackdelar
• Komplexitet
• Kostnad
• Centralisering
– Effekter av systemkrasch
– Prestanda
18
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Relationsmodellen
• Relation (eng. relation, INTE relationship)
– Tabell
• Data struktureras i form av relationer/tabeller
– Rader/tupler (rows/tuples)
– Kolumner/attribut (columns/attributes)
– Domäner/värdeförråd (domains)
19
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Domäner & NULL
• Domän
– Värdeförråd
– Datatyp eller mer begränsad
– Kontroll
• NULL
–
–
–
–
–
Ett icke-värde
NULL är inte noll
NULL är inte en tom sträng
NULL är inte större eller mindre än ett annat värde. Kan inte jämföras.
Kan betyda att
» Värdet finns inte (ännu)
» Värdet är inte tillämpligt
» Värdet kan inte visas (t ex pga låg behörighet)
20
ITK3:DB/EIT:DB ht2008
Terminologi – Relation/Tabell
nikos dimitrakas
Tabellnamnet
Kolumnnamn
Bok
ISBN
Titel
År
Förlag
Pris
123-122-121
Prins Annorlunda
2005
Brombergs
140
234-234-222
Svensk Maffia
2007
Brombergs
155
123-435-777
De välvilliga
2007
Bonniers
140
Rad
Kolumn
21
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Relationsmodellen - Krav
• Inga dubblettrader (inom relationen)
• Ordningen mellan raderna är betydelselös
• Ordningen mellan attributen (vänster-höger) är
oväsentlig
• Varje attribut har ett unikt namn (inom relationen)
• Inga multipla värden (endast atomära)
• Ett attribut hämtar värden från en domän
22
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Terminologi – Relation/Tabell
• Intension
– Tabellens beskrivning, strukturens beskrivning, vilka kolumner, vilka
domäner, etc.
• Extension (state)
– Tabellens innehåll (raderna)
• Grad
– Antal kolumner
• Kardinalitet
– Antal rader
23
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Terminologi – Nycklar
• Supernyckel (superkey)
– Ett eller flera attribut (kolumner) som identifierar raderna unikt.
• Kandidatnyckel (candidate key)
– En supernyckel som inte innehåller onödiga kolumner.
– Minimalitet (irreducibility)
• Primärnyckel (primary key)
– Den kandidatnyckel som vi väljer att använda för att identifiera
raderna unikt.
• Alternativnyckel (alternate key)
– Kandidatnycklar som inte valdes till primärnyckel.
• Främmande nyckel (foreign key)
– En eller flera kolumner i en tabell som matchar en kandidatnyckel i
någon tabell (oftast en annan tabell, men det kan vara samma).
– Möjliggör att länka tabeller genom att referera från en tabell till en
annan.
24
ITK3:DB/EIT:DB ht2008
Nycklar - exempel
nikos dimitrakas
Bok
ISBN
Titel
År
Förlag
Pris
123-122-121
Prins Annorlunda
2005
Brombergs
140
234-234-222
Svensk Maffia
2007
Brombergs
155
123-435-777
De välvilliga
2007
Bonniers
140
•
•
•
•
Supernyckel?
Kandidatnyckel?
Primärnyckel?
Alternativnyckel?
25
ITK3:DB/EIT:DB ht2008
Nycklar - exempel
nikos dimitrakas
Bok
ISBN
Titel
År
Förlag
Pris
123-122-121
Prins Annorlunda
2005
Brombergs
140
234-234-222
Svensk Maffia
2007
Brombergs
155
123-435-777
De välvilliga
2007
Bonniers
140
• Främmande nyckel?
Förlag
Namn
Webbplats
Kontaktperson
Brombergs
www.blombergs.se
Marie Lind
Bonniers
www.bonniers.se
Karl Sigg
Green Planet
www.greenplanet.net Lisa Brown
26
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Terminologi – Regler/Constraints
• Regler för tillåtna värden
–
–
–
–
–
–
NOT NULL
UNIQUE
Domän
Verksamhetsregler
Kandidatnycklar
Främmande nycklar (referensintegritet)
• Entitetsintegritet (entity integrity)
– Varje rad är unik (PN är unik och kan användas för att identifiera varje
rad)
– PN (eller del av PN) får inte vara NULL
• Referensintegritet (referential integrity)
– Värdet i en främmande nyckel måste motsvara ett värde som finns i
den refererade kandidatnyckeln eller vara NULL
27
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
•
•
•
•
Vyer (Views)
Presentation av data i form av virtuella tabeller
Lagras oftast inte
Alltid uppdaterad (automatiskt)
Redundant eller härledbar information
28
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Normalisering
• Mål
– Minimera redundans
– Undvika dataanomalier
• Metod
– Dela upp en tabell i flera tabeller (Tabellerna skall alltid kunna slåss
ihop för att komma till den ursprungliga tabellen utan att förlora
information)
– Utgå från funktionella beroenden (2NF-3NF)
• Används oftast som en komplementmetod till
modellering
• Funktionellt beroende
– Attribut "bestämmer" andra attribut
– Givet värdet i A vet man värdet i B
29
ITK3:DB/EIT:DB ht2008
Normalformer
nikos dimitrakas
• Normalformer bygger på varandra
• Normalformer testas per tabell
Alla tabeller
Tabeller i 1NF
Tabeller i 2NF
Tabeller i 3NF
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Normalformer – 1NF
• En tabell är i 1NF om
– Alla kolumner innehåller atomära värden (inga listor, inga tabeller)
– Tabellen har en primärnyckel
– Alla kolumner som inte ingår i primärnyckeln är funktionellt beroende
av primärnyckeln
• Är en tabell inte i 1NF kan man säga att den är i 0NF
(eller är onormaliserad)
• En tabell som inte är i 1NF följer inte relationsmodellen.
• Funktionellt beroende
– AB
ITK3:DB/EIT:DB ht2008
Normalisering – 0NF
nikos dimitrakas
Projekt
ProjNr
ProjNamn Deltagare
1
Panda
2
Koala
PersNr
PersNamn
Arbetsgivare Webbplats
Projektroll
1111
Karl Nilson
StepStone
www.ss.se
ledare
2222
Lisa Berg
ManPower
www.mp.se testare
3333
Tove Wass
StepStone
www.ss.se
PersNr
PersNamn
Arbetsgivare Webbplats
Projektroll
1111
Karl Nilson
StepStone
www.ss.se
PR
3333
Tove Wass
StepStone
www.ss.se
ledare
testare
32
ITK3:DB/EIT:DB ht2008
Normalisering – 1NF
nikos dimitrakas
Projektdeltagande
ProjNr ProjNamn
PersNr
PersNamn
Arbetsgivare Webbplats
Projektroll
1
Panda
1111
Karl Nilson
StepStone
www.ss.se
ledare
1
Panda
2222
Lisa Berg
ManPower
www.mp.se testare
1
Panda
3333
Tove Wass
StepStone
www.ss.se
testare
2
Koala
1111
Karl Nilson
StepStone
www.ss.se
PR
2
Koala
3333
Tove Wass
StepStone
www.ss.se
ledare
• Alla celler har atomära värden
• Primärnyckel: ProjNr och PersNr
• Funktionella beroenden:
– ProjNr, PersNr  ProjNamn, PersNamn, Arbetsgivare, Webbplats, Projektroll
33
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Normalformer – 2NF
• En tabell är i 2NF om
– Den är i 1NF och
– Alla kolumner som inte ingår i PN är fullt funktionellt beroende på PN
(det finns inga partiella funktionella beroenden på PN)
• Partiellt funktionellt beroende
– AB
– A,X p B
ITK3:DB/EIT:DB ht2008
Normalisering – 2NF
nikos dimitrakas
Projektdeltagande (1NF)
ProjNr ProjNamn
PersNr
PersNamn
Arbetsgivare Webbplats
Projektroll
1
Panda
1111
Karl Nilson
StepStone
www.ss.se
ledare
1
Panda
2222
Lisa Berg
ManPower
www.mp.se testare
1
Panda
3333
Tove Wass
StepStone
www.ss.se
testare
2
Koala
1111
Karl Nilson
StepStone
www.ss.se
PR
2
Koala
3333
Tove Wass
StepStone
www.ss.se
ledare
• Partiella funktionella beroenden på PN:
– ProjNr  ProjNamn
– PersNr  PersNamn, Arbetsgivare, Webbplats
35
ITK3:DB/EIT:DB ht2008
Normalisering – 2NF
nikos dimitrakas
Projektdeltagande
ProjNr PersNr
Projektroll
1
1111
ledare
1
2222
testare
1
3333
testare
2
1111
PR
2
3333
ledare
Person
PersNr
PersNamn
Arbetsgivare Webbplats
1111
Karl Nilson
StepStone
www.ss.se
2222
Lisa Berg
ManPower
www.mp.se
3333
Tove Wass
StepStone
www.ss.se
Projekt
ProjNr ProjNamn
• Tabellnamn
• Primärnycklar
• Främmande nycklar
1
Panda
2
Koala
– Projektdeltagande.ProjNr << Projekt.ProjNr
– Projektdeltagande.PersNr << Person.PersNr
36
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Normalformer – 3NF
• En tabell är i 3NF om
– Den är i 2NF och
– Alla kolumner som inte ingår i PN är direkt funktionellt beroende på
PN (det finns inga transitiva funktionella beroenden)
• Transitivt beroende:
– AB
– BC
– A t C
ITK3:DB/EIT:DB ht2008
Normalisering – 3NF
nikos dimitrakas
Projektdeltagande
ProjNr PersNr
Projektroll
1
1111
ledare
1
2222
testare
1
3333
testare
2
1111
PR
2
3333
ledare
Person
PersNr
PersNamn
Arbetsgivare Webbplats
1111
Karl Nilson
StepStone
www.ss.se
2222
Lisa Berg
ManPower
www.mp.se
3333
Tove Wass
StepStone
www.ss.se
Projekt
ProjNr ProjNamn
• Projekt redan i 3NF
• Projektdeltagande redan i 3NF
• Person har ett transitivt funktionellt beroende
1
Panda
2
Koala
– PersNr  Arbetsgivare  Webbplats
– PersNr t Webbplats
38
ITK3:DB/EIT:DB ht2008
Normalisering – 3NF
nikos dimitrakas
Person
Företag
PersNr
PersNamn
Arbetsgivare
Namn
Webbplats
1111
Karl Nilson
StepStone
StepStone
www.ss.se
2222
Lisa Berg
ManPower
ManPower
www.mp.se
3333
Tove Wass
StepStone
• Tabellnamn
• Primärnycklar
• Främmande nycklar
– Person.Arbetsgivare << Företag.Namn
39
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Normalisering
• 0NF  1NF
–
–
–
–
Eliminera repetitiva grupper
Eliminera tabeller eller listor i celler
Tabellnamn
Primärnyckel
• 1NF  2NF
– Eliminera partiella funktionella beroenden
» Skapa nya tabeller
» Främmande nycklar
• 2NF  3NF
– Eliminera transitiva funktionella beroenden
» Skapa nya tabeller
» Främmande nycklar
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Dataanomalier
• Lägga till ett företag
• Ändra ett företags webbadress
• Ta bort den sista personen från ett företag
Projektdeltagande (1NF)
ProjNr ProjNamn
PersNr
PersNamn
Arbetsgivare Webbplats
Projektroll
1
Panda
1111
Karl Nilson
StepStone
www.ss.se
ledare
1
Panda
2222
Lisa Berg
ManPower
www.mp.se testare
1
Panda
3333
Tove Wass
StepStone
www.ss.se
testare
2
Koala
1111
Karl Nilson
StepStone
www.ss.se
PR
2
Koala
3333
Tove Wass
StepStone
www.ss.se
ledare
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Dataanomalier
• Lägga till ett företag
• Ändra ett företags webbadress
• Ta bort den sista personen från ett företag
Projektdeltagande (3NF)
Person (3NF)
ProjNr PersNr
Projektroll
PersNr
PersNamn
Arbetsgivare
1
1111
ledare
1111
Karl Nilson
StepStone
1
2222
testare
2222
Lisa Berg
ManPower
1
3333
testare
3333
Tove Wass
StepStone
2
1111
PR
2
3333
ledare
Projekt (3NF)
Företag (3NF)
Namn
Webbplats
ProjNr ProjNamn
StepStone
www.ss.se
1
Panda
ManPower
www.mp.se
2
Koala
ITK3:DB/EIT:DB ht2008
nikos dimitrakas
Normalisering
• Starkare definition
– 2NF och 3NF för varje kandidatnyckel (istället för endast PN)
• BCNF
• 4NF
• 5NF