Databaser
Vad är en databas?
Vad du ska lära dig:
Använda UML för att modellera ett system
En samling data som hör ihop
Förstå hur modellen kan översättas till en relationsdatabas
som modellerar en del av verkligheten.
Använda SQL för att ställa frågor till databasen
som är persistent (dvs. inte försvinner när man avslutar programmet).
Använda en databashanterare
En databas kan till exempel vara:
Ordlista
en textfil.
UML – ett språk för modellering av alla typer av problem
ett excel-dokument.
relationsdatabas – data ordnade i tabeller
en databas som hanteras av en databashanterare.
SQL – ett språk för att hämta och ändra data i databasen
databashanterare – program som hanterar databasen
EDAA20 (Föreläsning databaser)
HT 2012
1 / 52
Vad är en databashanterare?
EDAA20 (Föreläsning databaser)
HT 2012
2 / 52
HT 2012
4 / 52
Relationsdatabas
Ett program som har till uppgift att lagra och hantera databaser.
Kan hantera mycket data på ett effektivt sätt.
Låter användaren specificera strukturen för databasen.
En relationsdatabas består av relationer (tabeller).
Låter användare ställa frågor till databasen.
En relation har attribut (kolumner i tabellen).
Hanterar samtidig access till databasen.
En relation består av en mängd tupler (rader i tabellen).
Exempel på databashanterare:
1970 E. F. Codd
MySQL
Oracle
Microsoft Access
EDAA20 (Föreläsning databaser)
HT 2012
3 / 52
EDAA20 (Föreläsning databaser)
Exempel på en relation
Relationers schema
relation
attribut
personNr
720101-1234
740105-2335
850318-2345
860101-3456
förnamn
Bo
Lars
Eva
Sara
efternamn
Ek
Bok
Alm
Alm
Relationens schema (beskrivning av vad som kan lagras i tabellen)
skrivs så här:
adress
Malmö
Malmö
Lund
Lund
Kunder(personNr, förnamn, efternamn, adress)
relationens namn
attribut
tupel
EDAA20 (Föreläsning databaser)
HT 2012
5 / 52
Exempel: relationen Bankkonton
EDAA20 (Föreläsning databaser)
saldo
4300
5020
30000
27000
120
HT 2012
6 / 52
Nycklar
Bankkonton(kontoNr, saldo, personNr)
kontoNr
123456
345678
357911
678901
890123
EDAA20 (Föreläsning databaser)
En nyckel är ett (eller flera attribut tillsammans) vars värde
garanterat är unikt. Det får alltså inte finnas flera tupler (rader) med
samma värde på nyckeln.
personNr
850318-2345
720101-1234
860101-3456
850318-2345
720101-1234
Man brukar välja ut en av nycklarna och kalla denna primärnyckel.
Exempel:
Kunder(personNr, förnamn, efternamn, adress)
Bankkonton(kontoNr, saldo, personNr)
HT 2012
7 / 52
EDAA20 (Föreläsning databaser)
HT 2012
8 / 52
SQL
SQL-frågor
SQL – Structured Query Language
Datadefinitionsspråk – för att definiera tabeller. Ex:
CREATE TABLE Kunder(
personNr
CHAR(11) PRIMARY KEY,
förnamn
VARCHAR(40),
efternamn
VARCHAR(40),
adress
VARCHAR(60)
);
På följande sidor finns exempel på SQL-frågor för att hämta information
från en databas med tabellerna:
Kunder(personNr, förnamn, efternamn, adress)
Bankkonton(kontoNr, saldo, personNr)
Datamanipuleringsspråk – för att ställa frågor till databasen, t ex söka
i databasen eller uppdatera data. Ex:
SELECT *
FROM Kunder;
EDAA20 (Föreläsning databaser)
HT 2012
9 / 52
Hämta all information från en tabell
EDAA20 (Föreläsning databaser)
HT 2012
10 / 52
Välja ut vissa kolumner
Man kan välja ut vissa kolumner (attribut) genom att ange de
önskade attributen i SELECT-delen.
Exempel: Tag reda på alla kontons innehavare (personnummer) och
kontonummer.
Exempel: Tag reda på all information i tabellen Bankkonton.
SELECT *
FROM Bankkonton;
SELECT personNr, kontoNr
FROM Bankkonton;
Resultat:
kontoNr
123456
345678
357911
678901
890123
EDAA20 (Föreläsning databaser)
saldo
4300
5020
30000
27000
120
Resultat:
personNr
850318-2345
720101-1234
860101-3446
850318-2345
720101-1234
personNr
850318-2345
720101-1234
860101-3446
850318-2345
720101-1234
HT 2012
11 / 52
EDAA20 (Föreläsning databaser)
kontoNr
123456
345678
357911
678901
890123
HT 2012
12 / 52
Döpa om kolumner
Slå ihop kolumner
Man kan döpa om namnen på de kolumner som visas.
Exempel: Tag reda på alla kontons innehavare (personnummer) och
kontonummer, men visa resultatet med andra kolumnnamn.
SELECT personNr AS kontoinnehavare, kontoNr AS kontonummer
FROM Bankkonton;
Exempel: Visa personnummer och namn på alla kunder. Namnen ska
vara på formen efternamn, förnamn.
SELECT personNr, efternamn + ’, ’ + förnamn AS namn
FROM Kunder;
Resultat:
Resultat:
kontoinnehavare
850318-2345
720101-1234
860101-3446
850318-2345
720101-1234
personNr
720101-1234
740105-2335
850318-2345
860101-3446
kontonummer
123456
345678
357911
678901
890123
EDAA20 (Föreläsning databaser)
HT 2012
13 / 52
Sortera
namn
Ek, Bo
Bok, Lars
Alm, Eva
Alm, Sara
EDAA20 (Föreläsning databaser)
HT 2012
14 / 52
Visa ej dubletter
Exempel: Tag reda på alla kunder. Visa kunderna sorterade efter
efternamn i första hand och förnamn i andra hand.
Exempel: Tag reda på alla kontoinnehavares personnummer. Även om
personen har flera konton ska personens personnummer bara med en
gång.
SELECT *
FROM Kunder
ORDER BY efternamn, förnamn;
SELECT DISTINCT personNr
FROM Bankkonton;
Resultat:
personNr
850318-2345
860101-3446
740105-2335
720101-1234
förnamn
Eva
Sara
Lars
Bo
efternamn
Alm
Alm
Bok
Ek
adress
Lund
Lund
Malmö
Malmö
Resultat:
personNr
720101-1234
850318-2345
860101-3446
Sortera i avtagande ordning:
ORDER BY efternamn DESC, förnamn DESC;
EDAA20 (Föreläsning databaser)
HT 2012
15 / 52
EDAA20 (Föreläsning databaser)
HT 2012
16 / 52
Välja ut vissa rader
Flera delvillkor
Man kan välja ut vissa rader (tupler) genom att skriva ett villkor i
WHERE-delen.
Exempel: Tag reda på de kontonummer som hör till en
kontoinnehavaren med personnummer 850318-2345.
Exempel: Vilken adress har personen med namnet Sara Alm.
SELECT adress
FROM Kunder
WHERE förnamn = ’Sara’ AND efternamn = ’Alm’;
SELECT kontoNr
FROM Bankkonton
WHERE personNr = ’850318-2345’;
Resultat:
Resultat:
adress
Lund
kontoNr
123456
678901
EDAA20 (Föreläsning databaser)
Man kan sätta ihop flera delvillkor i WHERE-delen med AND (och),
OR (eller) och NOT (icke).
HT 2012
17 / 52
Matcha strängar
EDAA20 (Föreläsning databaser)
HT 2012
18 / 52
Mönster för en fråga
% motsvarar 0-många tecken, _ motsvarar exakt ett tecken.
OBS! I den databashanterare som används på laborationerna är det *
som matchar 0-många tecken och ? som matchar exakt ett tecken.
Exempel: Visa kontonumren för de konton vars innehavare är födda år
1985.
SELECT kontoNr
FROM Bankkonton
WHERE personNr LIKE ’85*’;
SELECT kolumner
FROM tabeller
WHERE villkor;
SELECT Vilka kolumner (attribut) ska med?
FROM Vilka tabeller ska data hämtas ifrån?
WHERE Vilka rader (tupler) ska med?
Resultat:
Tips! Det är oftast enklast att börja med FROM när man ska
formulera en fråga.
kontoNr
123456
678901
EDAA20 (Föreläsning databaser)
HT 2012
19 / 52
EDAA20 (Föreläsning databaser)
HT 2012
20 / 52
Aggregatfunktioner
Hämta information från flera tabeller
En aggregatfunktion arbetar på en hel kolumn. Man kan t.ex.
summera alla värden i kolumnen eller beräkna medelvärdet:
SUM, AVG, MIN, MAX, COUNT
Vi har tidigare använt en SQL-fråga som visar information om alla
kontons innehavare (personnummer) och kontonummer. All
information finns i tabellen Bankkonton och SQL-frågan ser ut så här:
Hur många konton finns det?
SELECT personNr, kontoNr
FROM Bankkonton;
SELECT COUNT(*)
FROM Bankkonton;
Antag att vi istället vill ta reda på kontoinnehavarnas namn istället för
personnummer. Problemet är att de data vi vill ha finns i två olika
tabeller:
Resultat: 5
Vilket är största saldot?
Bankkonton( kontoNr, saldo, personNr)
Kunder(personNr, förnamn, efternamn, adress)
SELECT MAX(saldo)
FROM Bankkonton;
Resultat: 30000
EDAA20 (Föreläsning databaser)
HT 2012
21 / 52
Tabellerna Bankkonton och Kunder
EDAA20 (Föreläsning databaser)
HT 2012
22 / 52
Första försök att hämta data från två tabeller
Kartesisk produkt
kontoNr
123456
345678
357911
678901
890123
saldo
4300
5020
30000
27000
120
personNr
720101-1234
740105-2335
850318-2345
860101-3456
EDAA20 (Föreläsning databaser)
personNr
850318-2345
720101-1234
860101-3456
850318-2345
720101-1234
förnamn
Bo
Lars
Eva
Sara
efternamn
Ek
Bok
Alm
Alm
Ta med bägge tabellerna i FROM-delen:
SELECT efternamn, förnamn, kontoNr
FROM Bankkonton, Kunder;
Resultatet blir den kartesiska produkten (alla rader matchas med alla):
efternamn
Ek
Bok
Alm
Alm
Ek
Bok
Alm
Alm
Ek
...
adress
Malmö
Malmö
Lund
Lund
HT 2012
23 / 52
EDAA20 (Föreläsning databaser)
förnamn
Bo
Lars
Eva
Sara
Bo
Lars
Eva
Sara
Bo
...
kontoNr
123456
123456
123456
123456
345678
345678
345678
345678
357911
...
HT 2012
24 / 52
Hämta data från två tabeller
Hämta data från två tabeller – JOIN
Forts.
Tag reda på alla kontons innehavare (namn) och kontonummer.
SELECT efternamn, förnamn, kontoNr
FROM Bankkonton INNER JOIN Kunder ON
Bankkonton.personNr = Kunder.personNr;
Vi vill bara matcha de rader som har samma personnummer i de bägge
tabellerna:
Resultatet blir en rad per konto:
kontoNr
123456
345678
357911
678901
890123
saldo
4300
5020
30000
27000
120
personNr
850318-2345
720101-1234
860101-3456
850318-2345
720101-1234
personNr
720101-1234
740105-2335
850318-2345
860101-3456
förnamn
Bo
Lars
Eva
Sara
efternamn
Ek
Bok
Alm
Alm
adress
Malmö
Malmö
Lund
Lund
efternamn
Alm
Ek
Alm
Alm
Ek
förnamn
Eva
Bo
Sara
Eva
Bo
kontoNr
123456
345678
357911
678901
890123
Vi har matchat raderna i Bankkonton och Kunder, men bara tagit med
de rader där kontotinnehavarens personnummer är lika med kundens
personnummer.
EDAA20 (Föreläsning databaser)
HT 2012
25 / 52
Mönster för JOIN
EDAA20 (Föreläsning databaser)
HT 2012
26 / 52
OUTER JOIN
Exempel
Tag reda på alla kontons innehavare (namn) och kontonummer. Men
tag med även med namnen på de kunder som inte har något konto:
SELECT kolumnlista
FROM tabell1 {INNER | LEFT [OUTER] | RIGHT [OUTER]}
tabell2 ON tabell1.kolumn1 = tabell2.kolumn2;
Man kan använda AND i ON-villkoret om man vill utföra join på flera
attribut.
Med INNER JOIN menas att vi tar den kartesiska produkten av
tabell1 och tabell2, men bara behåller de rader med samma värde
på tabell1.kolumn1 och tabell2.kolumn2.
OUTER JOIN används då man vill ha med tupler från ena tabellen
som inte har någon motsvarighet i den andra tabellen.
T ex innebär RIGHT OUTER JOIN att varje tupel i högra tabellen
matchas med de som passar i vänstra tabellen, om ingen matchande
tupel finns fylls attributen från vänstra tabellen med null.
EDAA20 (Föreläsning databaser)
HT 2012
27 / 52
SELECT efternamn, förnamn, kontoNr
FROM Bankkonton RIGHT OUTER JOIN Kunder ON
Bankkonton.personNr = Kunder.personNr;
Resultatet blir en rad per konto samt en rad för de kunder som ej har
något konto:
efternamn
Ek
Ek
Bok
Alm
Alm
Alm
förnamn
Bo
Bo
Lars
Eva
Eva
Sara
EDAA20 (Föreläsning databaser)
kontoNr
345678
890123
678901
123456
357911
HT 2012
28 / 52
Subfrågor
IN
Tag reda på vilka kunder som saknar konto.
Tag reda på vem som har högsta saldot.
SELECT efternamn, förnamn
FROM Kunder
WHERE personNr NOT IN (SELECT personNr FROM Bankkonton);
SELECT personNr
FROM Bankkonton
WHERE saldo = (SELECT MAX(saldo) FROM Bankkonton);
Resultat:
Resultat:
Bok Lars
860101-3456
Observera att IN används här (och inte =). Tecknet = kan bara
användas för att jämföra ett värde med ett annat. Men subfrågan i
exemplet kan ge flera personnummer som resultat.
Observera att man inte kan ha aggregatfunktioner direkt i
where-villkoret, utan måste använda en subfråga.
EDAA20 (Föreläsning databaser)
HT 2012
29 / 52
Gruppera – GROUP BY
EDAA20 (Föreläsning databaser)
HT 2012
30 / 52
HT 2012
32 / 52
Gruppera – GROUP BY
Forts.
Ibland vill man gruppera raderna i en tabell och behandla varje grupp
för sig.
Gruppering:
Så här gör man för att summera alla kontons saldo:
SELECT SUM(saldo)
FROM Bankkonton;
Om man istället vill se hur mycket pengar varje enskild person har på
sina konton måste man dela in kontona i en grupp per kund och
summera saldot i varje grupp:
SELECT personNr, SUM(saldo) AS saldoTotalt
FROM Bankkonton
GROUP BY personNr;
EDAA20 (Föreläsning databaser)
kontoNr
345678
890123
saldo
5020
120
personNr
720101-1234
720101-1234
123456
678901
4300
27000
850318-2345
850318-2345
357911
30000
860101-3456
Resultat:
personNr
720101-1234
850318-2345
860101-3456
HT 2012
31 / 52
EDAA20 (Föreläsning databaser)
saldoTotalt
5140
31300
30000
HAVING
JOIN mellan samma tabell
Having kan användas för att välja ut visa rader när man grupperat.
Exempel: Summera hur mycket pengar varje enskild person har på sina
konton. Men tag bara med personer som har mindre än 10000 kr:
SELECT personNr, SUM(saldo) AS saldoTotalt
FROM Bankkonton
GROUP BY Bankkonton.personNr
HAVING SUM(saldo) < 10000;
SELECT Kunder1.personNr, Kunder2.PersonNr, Kunder1.efternamn
FROM Kunder AS Kunder1 INNER JOIN Kunder AS Kunder2 ON
Kunder1.efternamn = Kunder2.efternamn
WHERE Kunder1.personNr < Kunder2.personNr;
Resultat:
personNr
720101-1234
Resultat:
SaldoTotalt
5140
EDAA20 (Föreläsning databaser)
Tag reda på de kunder som har samma efternamn som någon annan
kund.
Tips! Låtsas som om det finns två tabeller med kunder där vi ska
jämföra efternamnet från den ena tabellen med efternamnet från den
andra. Vi behöver då två olika namn på tabellen Kunder, t ex Kunder1
och Kunder2.
Kunder1.PersonNr
850318-2345
HT 2012
33 / 52
ER-modell
Kunder2.PersonNr
860101-3456
efternamn
Alm
EDAA20 (Föreläsning databaser)
HT 2012
34 / 52
HT 2012
36 / 52
Databasdesign
För att uttrycka en modell av vårt system använder vi en ER-modell.
Utveckla en ER-modell för systemet (uttryckt i UML).
I ER-modellen visas
vilken information som ska finnas i databasen.
hur de olika komponenterna hänger ihop.
Omvandla ER-modellen till relationer (tabeller).
Skapa tabellerna i en databashanterare.
ER står för
Entity – entitet (”sak”, jfr objekt i programmering)
Relationship – samband
EDAA20 (Föreläsning databaser)
HT 2012
35 / 52
EDAA20 (Föreläsning databaser)
Exempel
ER-modell
Studentregister
Studentregister
Student
personNr
förnamn
efternamn
En databas för registrering av kurser, studenter och resultat ska
utvecklas.
Kurs
kurskod
kursnamn
* poäng
*
Studenter har personnummer och ett namn. När en student är
godkänd på en kurs registreras betyget 3, 4 eller 5.
*
Resultat
betyg
En kurs har en kurskod (ex: EDAA20), ett namn och ett poängantal.
Kursen ges av en institution. Institutionen har ett namn och en adress.
EDAA20 (Föreläsning databaser)
HT 2012
37 / 52
ER-modell
1
Institution
namn
adress
EDAA20 (Föreläsning databaser)
HT 2012
38 / 52
Multiplicitet
Vid sambandens ändar kan man ange multiplicitet (aritet).
Ex 1: En företag har flera anställda. En person kan arbeta i flera
företag.
I ER-modellen ser man:
entitetstyper – påminner om klasser i programmering men innehåller
bara attribut.
attribut – egenskaper hos entitetsyper och även hos samband ibland
samband mellan entitetstyperna
Vi använder UML (Unified Modeling Language) för att uttrycka
ER-modellen.
Företag
*
*
Person
Ex 2: En företag har flera anställda. En person är bara anställd vid ett
företag.
Företag
1
*
Person
Multipliciteten anges som ett heltal (t.ex. 1) eller ett intervall (t.ex.
0..1).
* betyder ett obegränsat antal.
EDAA20 (Föreläsning databaser)
HT 2012
39 / 52
EDAA20 (Föreläsning databaser)
HT 2012
40 / 52
Samband kan ha namn
Samband kan ha attribut
Attributet betyg kan inte placeras i någon av entitetstyperna Student
eler Kurs. En student klarar flera kurser och har då flera betyg. En
kurs har flera godkända studenter. Attributet betyg hör istället ihop
med sambandet mellan Student och Kurs.
Man kan ge sambanden namn. Ex:
anställer
Företag
*
Person
*
eller
arbetar hos
Person
*
Student
personNr
förnamn
efternamn
Företag
*
Kurs
kurskod
kursnamn
* poäng
*
Vid sambandets ändar kan man skriva ut roller. Ex:
Person
anställd
arbetsgivare
*
*
EDAA20 (Föreläsning databaser)
Resultat
betyg
Företag
HT 2012
41 / 52
Undvik redundans
Kurs
kurskod
kursnamn
poäng
institutionsnamn
EDAA20 (Föreläsning databaser)
42 / 52
Primärnyckel
*
1
En nyckel är ett (eller flera attribut tillsammans) vars värde garanterat
är unikt. Man brukar välja ut en av nycklarna och kalla denna
primärnyckel.
Primärnycklarna kan markeras genom understrykning I
UML-diagrammet.
Institution
namn
adress
Student
personNr
förnamn
efternamn
Vanligt fel! Attributet instiutionsnamn uttrycker samma sak som
sambandet mellan kurs och institution.
HT 2012
Kurs
kurskod
kursnamn
* poäng
*
*
Bort med det onödiga attributet i ER-diagrammet! (Däremot kanske
det dyker upp som attribut i tabellen så smånigom.)
EDAA20 (Föreläsning databaser)
HT 2012
43 / 52
Resultat
betyg
EDAA20 (Föreläsning databaser)
1
Institution
namn
adress
HT 2012
44 / 52
Arv
Relationsmodellen
En speciell typ av samband mellan entitetstyper är arv.
I exemplet ärver Fysisk person och Juridisk person gemensamma
egenskaper från Person.
Person
namn
Fysisk person
personnummer
*
Relationsmodellen beskriver vilka tabeller som ska finnas i databasen.
Tabeller i exemplet studentregister:
Fastighet
* fastighetsnummer
Studenter(personNr, förnamn, efternamn)
Kurser(kurskod, kursnamn, poäng, institutionsNamn)
Institutioner(namn, adress)
Resultat(personNr, kurskod, betyg)
Juridisk person
organisationsnummer
EDAA20 (Föreläsning databaser)
HT 2012
45 / 52
Från ER-modell till relationer
EDAA20 (Föreläsning databaser)
HT 2012
46 / 52
Olika modeller
Varje entitetstyp blir en relation med samma attribut som
entitetstypen.
Studenter(personNr, förnamn, efternamn)
Kurser(kurskod, kursnamn, poäng)
Institutioner(namn, adress)
ER-modellen är en konceptuell modell. Den beskriver verkligheten,
t.ex. hur olika saker hänger ihop.
Samma teknik att modellera verkligheten kan man använda i andra
sammanhang, t.ex. vid programmering eller annat problemlösande.
Varje samband blir en relation vars attribut är nycklarna för de
inblandade entitetstyperna plus sambandets egna attribut.
Relationsmodellen är en implementeringsmodell och beskriver vilka
tabeller som ska finnas.
Resultat(personNr, kurskod, betyg)
Undantag: Om man har ett många-till-en-samband kan man istället
lägga till nyckeln från ”en-sidan” som attribut på ”många-sidan”.
Kurser(kurskod, kursnamn, poäng, institutionsNamn)
EDAA20 (Föreläsning databaser)
HT 2012
47 / 52
EDAA20 (Föreläsning databaser)
HT 2012
48 / 52
Integritetsvillkor
Referensintegritet
Främmande nyckel
En främmande nyckel är ett attribut (eller en kombination av
attribut) som refererar till en primärnyckel i en annan relation.
Villkor som begränsar vilka data som kan lagras i databasen.
Hindrar oss att lägga in felaktiga data.
Exempel: institutionsNamn är främmande nyckel och refererar till
namn.
Nyckelvillkor
Kurser(kurskod, kursnamn, poäng, institutionsNamn)
Institutioner(namn, adress)
Två tupler kan inte ha samma värden på primärnyckeln.
Attributen i primärnyckeln kan inte ha värdet NULL.
Om det i tabellen Kurser finns en rad
Referensintegritet – se följande bilder.
EDAA20 Programmering och databaser 7.5 Datavetenskap
så måste det i tabellen institutioner finnas en rad med samma
institutionsnamn:
Datavetenskap Ole Römers väg 3 223 63 Lund
EDAA20 (Föreläsning databaser)
HT 2012
49 / 52
Referensintegritet
EDAA20 (Föreläsning databaser)
HT 2012
50 / 52
Index
Med referensintegritet menas att det måste finnas en motsvarande
entitet ”i andra änden” av sambandet.
Kurs
kurskod
kursnamn
poäng
*
Man kan sätta ett index på ett attribut. Detta innebär att tabellen
lagras på ett sådant sätt att det går snabbare att söka efter tupler
med ett visst värde på attributet.
Institution
namn
1 adress
Men insättningar, borttagningar och uppdateringar går långsammare.
Ofta skapas det automatiskt ett index till nyckelattributen.
Exempel: Varje kurs hör till en institution. För att man ska kunna
lägga in ett institutionsnamn i tabellen Kurs måste det finnas i
tabellen Institution.
EDAA20 (Föreläsning databaser)
HT 2012
51 / 52
EDAA20 (Föreläsning databaser)
HT 2012
52 / 52
