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 2016 1 / 55 Vad är en databashanterare? EDAA20 (Föreläsning databaser) HT 2016 2 / 55 HT 2016 4 / 55 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 2016 3 / 55 EDAA20 (Föreläsning databaser) Exempel på en relation Relationers schema relation attribut personNr 720101-1234 förnamn Bo efternamn Ek adress Malmö 740105-2335 850318-2345 860101-3446 Lars Eva Sara Bok Alm Alm Malmö Lund Lund Relationens schema (beskrivning av vad som kan lagras i tabellen) skrivs så här: Kunder(personNr, förnamn, efternamn, adress) relationens namn attribut tupel EDAA20 (Föreläsning databaser) HT 2016 5 / 55 Exempel: relationen Bankkonton HT 2016 6 / 55 Nycklar Bankkonton(kontoNr, saldo, personNr) KontoNr saldo personNr 123456 4300 850318-2345 345678 5020 720101-1234 357911 30000 860101-3446 678901 27000 850318-2345 890123 120 720101-1234 EDAA20 (Föreläsning databaser) 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. 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 2016 7 / 55 EDAA20 (Föreläsning databaser) HT 2016 8 / 55 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 2016 9 / 55 Hämta all information från en tabell EDAA20 (Föreläsning databaser) HT 2016 10 / 55 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 2016 11 / 55 EDAA20 (Föreläsning databaser) kontoNr 123456 345678 357911 678901 890123 HT 2016 12 / 55 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 2016 13 / 55 Sortera namn Ek, Bo Bok, Lars Alm, Eva Alm, Sara EDAA20 (Föreläsning databaser) HT 2016 14 / 55 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 2016 15 / 55 EDAA20 (Föreläsning databaser) HT 2016 16 / 55 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. Man kan sätta ihop flera delvillkor i WHERE-delen med AND (och), OR (eller) och NOT (icke). 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) HT 2016 17 / 55 Flera delvillkor EDAA20 (Föreläsning databaser) HT 2016 18 / 55 Matcha strängar forts % 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. Exempel: Vilken konton har ett saldo mellan 10000 och 50000 kr? SELECT * FROM Bankkonton WHERE saldo >= 10000 AND saldo < 50000; eller SELECT kontoNr FROM Bankkonton WHERE personNr LIKE ’85*’; SELECT * FROM Bankkonton WHERE saldo BETWEEN 10000 AND 50000; Resultat: Resultat: kontoNr 357911 678901 saldo 30000 27000 EDAA20 (Föreläsning databaser) kontoNr 123456 678901 personNr 860101-3446 850318-2345 HT 2016 19 / 55 EDAA20 (Föreläsning databaser) HT 2016 20 / 55 Mönster för en fråga Aggregatfunktioner Med aggregatfunktioner kan man t.ex. summera alla värden i en kolumn eller beräkna medelvärdet: SUM, AVG, MIN, MAX, COUNT SELECT kolumner FROM tabeller WHERE villkor; Hur många konton finns det? SELECT COUNT(*) FROM Bankkonton; SELECT Vilka kolumner (attribut) ska med? FROM Vilka tabeller ska data hämtas ifrån? Resultat: 5 WHERE Vilka rader (tupler) ska med? Vilket är största saldot? Tips! Det är oftast enklast att börja med FROM när man ska formulera en fråga. SELECT MAX(saldo) FROM Bankkonton; Resultat: 30000 EDAA20 (Föreläsning databaser) HT 2016 21 / 55 Hämta information från flera tabeller HT 2016 22 / 55 Tabellerna Bankkonton och Kunder 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: SELECT personNr, kontoNr 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: Bankkonton( kontoNr, saldo, personNr) Kunder(personNr, förnamn, efternamn, adress) EDAA20 (Föreläsning databaser) EDAA20 (Föreläsning databaser) HT 2016 23 / 55 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 adress Malmö Malmö Lund Lund HT 2016 24 / 55 Första försök att hämta data från två tabeller Hämta data från två tabeller Kartesisk produkt Forts. Ta med bägge tabellerna i FROM-delen: SELECT efternamn, förnamn, kontoNr FROM Bankkonton, Kunder; Vi vill bara matcha de rader som har samma personnummer i de bägge tabellerna: Resultatet blir den kartesiska produkten (alla rader matchas med alla): efternamn Ek Bok Alm Alm Ek Bok Alm Alm Ek ... förnamn Bo Lars Eva Sara Bo Lars Eva Sara Bo ... kontoNr 123456 345678 357911 678901 890123 kontoNr 123456 123456 123456 123456 345678 345678 345678 345678 357911 ... EDAA20 (Föreläsning databaser) HT 2016 25 / 55 Hämta data från två tabeller – JOIN saldo 4300 5020 30000 27000 120 personNr 850318-2345 720101-1234 860101-3456 850318-2345 720101-1234 EDAA20 (Föreläsning databaser) personNr 720101-1234 740105-2335 850318-2345 860101-3456 förnamn Bo Lars Eva Sara efternamn Ek Bok Alm Alm HT 2016 adress Malmö Malmö Lund Lund 26 / 55 Mönster för JOIN Tag reda på alla kontons innehavare (namn) och kontonummer. SELECT kolumnlista FROM tabell1 {INNER | LEFT [OUTER] | RIGHT [OUTER]} JOIN tabell2 ON tabell1.kolumn1 = tabell2.kolumn2; SELECT efternamn, förnamn, kontoNr FROM Bankkonton INNER JOIN Kunder ON Bankkonton.personNr = Kunder.personNr; Man kan använda AND i ON-villkoret om man vill utföra join på flera attribut. Resultatet blir en rad per konto: efternamn Alm Ek Alm Alm Ek förnamn Eva Bo Sara Eva Bo 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. 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 2016 27 / 55 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 2016 28 / 55 OUTER JOIN Subfrågor 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 personNr FROM Bankkonton WHERE saldo = (SELECT MAX(saldo) FROM Bankkonton); 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 Resultat: 860101-3456 kontoNr 345678 890123 Observera att man inte kan ha aggregatfunktioner direkt i where-villkoret, utan måste använda en subfråga. 678901 123456 357911 EDAA20 (Föreläsning databaser) Tag reda på vem som har högsta saldot. HT 2016 29 / 55 IN EDAA20 (Föreläsning databaser) HT 2016 30 / 55 Gruppera – GROUP BY Ibland vill man gruppera raderna i en tabell och behandla varje grupp för sig. Tag reda på vilka kunder som saknar konto. SELECT efternamn, förnamn FROM Kunder WHERE personNr NOT IN (SELECT personNr FROM Bankkonton); Resultat: 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: Bok Lars 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. EDAA20 (Föreläsning databaser) Så här gör man för att summera alla kontons saldo: HT 2016 31 / 55 SELECT personNr, SUM(saldo) AS saldoTotalt FROM Bankkonton GROUP BY personNr; EDAA20 (Föreläsning databaser) HT 2016 32 / 55 Gruppera – GROUP BY Gruppering Forts. Kommentarer Gruppering: kontoNr 345678 890123 saldo 5020 120 personNr 720101-1234 720101-1234 123456 678901 4300 27000 850318-2345 850318-2345 357911 30000 860101-3456 Aggregatfunktionerna, SUM, AVG, MIN, MAX, COUNT, arbetar egentligen mot grupper och returnerar ett resultat per grupp. Om man använder aggregatfunktionerna utan att gruppera räknas alla tupler i tabellen som en grupp. Endast attribut som räknas upp i GROUP BY får väljas ut i SELECT. Exempel: Resultat: personNr 720101-1234 850318-2345 860101-3456 SELECT personNr, SUM(saldo) AS saldoTotalt FROM Bankkonton GROUP BY personNr; saldoTotalt 5140 31300 30000 EDAA20 (Föreläsning databaser) HT 2016 33 / 55 HAVING EDAA20 (Föreläsning databaser) HT 2016 34 / 55 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 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. Resultat: SaldoTotalt 5140 Kunder1.PersonNr 850318-2345 HT 2016 35 / 55 EDAA20 (Föreläsning databaser) Kunder2.PersonNr 860101-3456 efternamn Alm HT 2016 36 / 55 ER-modell Från problem till databas För att uttrycka en modell av vårt system använder vi en ER-modell. I ER-modellen visas vilken information som ska finnas i databasen. hur de olika komponenterna hänger ihop. ER står för 1 Beskriv systemet i en ER-modell. 2 Översätt ER-modellen till relationer (tabeller). 3 Skapa tabellerna i en databashanterare. Entity – entitet (”sak”, jfr objekt i programmering) Relationship – samband EDAA20 (Föreläsning databaser) HT 2016 37 / 55 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. HT 2016 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 2016 38 / 55 39 / 55 EDAA20 (Föreläsning databaser) 1 Institution namn adress HT 2016 40 / 55 ER-modell 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 2016 41 / 55 Samband kan ha namn EDAA20 (Föreläsning databaser) HT 2016 42 / 55 Samband kan ha attribut Attributet betyg kan inte placeras i någon av entitetstyperna Student eller 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 * Företag * Student personNr förnamn efternamn Vid sambandets ändar kan man skriva ut roller. Ex: Person EDAA20 (Föreläsning databaser) anställd * arbetsgivare * * Resultat betyg Företag HT 2016 Kurs kurskod kursnamn * poäng 43 / 55 EDAA20 (Föreläsning databaser) HT 2016 44 / 55 Alternativ till samband med attribut Undvik redundans Kurs kurskod kursnamn poäng institutionsnamn Ett annat sätt att hantera betygen är att lägga till en entitet Resultat: Student personNr förnamn efternamn 1 Resultat * betyg * Kurs kurskod 1 kursnamn poäng 1 * Institution namn adress Vanligt fel! Attributet instiutionsnamn uttrycker samma sak som sambandet mellan kurs och institution. 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 2016 45 / 55 Primärnyckel 46 / 55 Kurs kurskod kursnamn * poäng * Resultat betyg I exemplet ärver Fysisk person och Juridisk person gemensamma egenskaper från Person. Fysisk person personnummer 1 Institution namn adress HT 2016 En speciell typ av samband mellan entitetstyper är arv. Person namn * EDAA20 (Föreläsning databaser) HT 2016 Arv 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. Student personNr förnamn efternamn EDAA20 (Föreläsning databaser) 47 / 55 EDAA20 (Föreläsning databaser) * Fastighet * fastighetsnummer Juridisk person organisationsnummer HT 2016 48 / 55 Relationsmodellen Från ER-modell till relationer 1 Relationsmodellen beskriver vilka tabeller som ska finnas i databasen. Tabeller i exemplet studentregister: Studenter(personNr, förnamn, efternamn) Kurser(kurskod, kursnamn, poäng, institutionsNamn) Institutioner(namn, adress) Resultat(personNr, kurskod, betyg) Varje entitetstyp blir en relation med samma attribut som entitetstypen. Studenter(personNr, förnamn, efternamn) Kurser(kurskod, kursnamn, poäng) Institutioner(namn, adress) 2 Varje samband blir en relation vars attribut är nycklarna för de inblandade entitetstyperna plus sambandets egna attribut. 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 2016 49 / 55 Olika modeller HT 2016 50 / 55 Integritetsvillkor 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. Relationsmodellen är en implementeringsmodell och beskriver vilka tabeller som ska finnas. EDAA20 (Föreläsning databaser) EDAA20 (Föreläsning databaser) HT 2016 51 / 55 Villkor som begränsar vilka data som kan lagras i databasen. Hindrar oss att lägga in felaktiga data. Nyckelvillkor Två tupler kan inte ha samma värden på primärnyckeln. Attributen i primärnyckeln kan inte ha värdet NULL. Referensintegritet – se följande bilder. EDAA20 (Föreläsning databaser) HT 2016 52 / 55 Referensintegritet Referensintegritet Främmande nyckel Med referensintegritet menas alltså att det måste finnas en motsvarande entitet ”i andra änden” av sambandet. En främmande nyckel är ett attribut (eller en kombination av attribut) som refererar till en nyckel i en annan relation. Exempel: institutionsNamn är främmande nyckel och refererar till nyckeln namn i relationen Institution. Kurs kurskod kursnamn poäng Kurser(kurskod, kursnamn, poäng, institutionsNamn) Institutioner(namn, adress) * Institution namn 1 adress Detta betyder att om det i tabellen Kurser finns en rad EDAA20 Programmering och databaser 7.5 Datavetenskap 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 namnet finnas i tabellen Institution. 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 2016 53 / 55 Index 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. Men insättningar, borttagningar och uppdateringar går långsammare. Ofta skapas det automatiskt ett index till nyckelattributen. EDAA20 (Föreläsning databaser) HT 2016 55 / 55 EDAA20 (Föreläsning databaser) HT 2016 54 / 55