1 / 37

Sets in een RDBS

Sets in een RDBS. Een database bevat een set tabelschema’s (entiteitsklassen) Een tabelschema (entiteitsklasse) bevat een set attribuutnamen met hun type Een tabel bevat een set records (entiteiten) Een record bevat een set waarden gekoppeld aan attribuutnamen

hope
Download Presentation

Sets in een RDBS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Sets in een RDBS Een database bevat een set tabelschema’s (entiteitsklassen) Een tabelschema (entiteitsklasse) bevat een set attribuutnamen met hun type Een tabel bevat een set records (entiteiten) Een record bevat een set waarden gekoppeld aan attribuutnamen Een resultaat van een query is een set records NB hoewel settheorie de basis is van het relationeel model wordt in Access niet altijd in sets gewerkt en kun je toch duplicaat records krijgen, gebruik zo nodig DISTINCT(ROW).

  2. Verzamelingen Het relationeel model is gebaseerd op de verzamelingenleer. Een verzameling (set) bestaat uit 0 - N ()elementen. Het aantal elementen: de kardinaliteit van de set. Een element kan zelf weer een set zijn. {} lege set {1,8,3} een set met 3 elementen {{2,4},{1,8}} een set met twee sets als elementen De elementen van een set zijn ongeordend {1,8,3} = {8,1,3} De elementen van een set zijn uniek (vgl groep individuen) {1,8,3,3,1} = {1,8,3}

  3. Operaties op verzamelingen 1 Samenvoegen van twee sets: vereniging / union A B {1,2,5,6}  {2,6,7} = {1,2,5,6,7} Gemeenschappelijke deel van twee sets: doorsnede / intersection A  B {1,2,5,6}  {2,6,7} = {2,6} verschil / difference van twee sets: A - B {1,2,5,6} – {2,6,7} = {1,5} 2 7 5 6 1 2 7 5 6 1 2 7 5 6

  4. Setoperaties op hele tabellen: UNION(opdracht in Access SQL) Vereniging entiteiten uit twee tabellen samenvoegen VB: samenvoegen van 2 ledenlijsten Naam Adres Wpl Naam Adres Wpl AA Aweg 1 A AA Aweg 1 A BB Bweg 2 B DD Dweg 4 D CC Cweg 3 C union EE Eweg 5 E Naam Adres Wpl AA Aweg 1 A BB Bweg 2 B CC Cweg 3 C DD Dweg 4 D EE Eweg 5 E ADO VVV

  5. Setoperaties op hele tabellen: INTERSECTIONniet als opdracht in Access SQL Doorsnede gemeensch. elementen uit twee tabellen VB: vergelijken van 2 ledenlijsten: wie is van beide lid? Naam Adres Wpl Naam Adres Wpl AA Aweg 1 A AA Aweg 1 A BB Bweg 2 B DD Dweg 4 D CC Cweg 3 C intersection EE Eweg 5 E Naam Adres Wpl AA Aweg 1 A VVV ADO

  6. Setoperaties op hele tabellen: DIFFERENCEniet als opdracht in Access SQL Difference/exceptverschil tussen twee tabellen VB: wie is wel van VVV maar niet van ADO lid? VVV ADO Naam Adres Wpl Naam Adres Wpl AA Aweg 1 A AA Aweg 1 A BB Bweg 2 B DD Dweg 4 D CC Cweg 3 C VVV-ADO EE Eweg 5 E Naam Adres Wpl BB Bweg 2 B CC Cweg 3 C

  7. Setoperaties op groepen records Vereniging keus tussen verschillende restricties OR b.v. geef me planten met rode OF paarse kleur Intersectiemeerdere restricties tegelijk AND b.v. Welke planten zijn van de soort bol EN bloeien in maart? Verschil positive en negatieve restrictie (AND NOT) b.v.Welke planten zijn van de soort bol maar bloeien NIET in maart?

  8. Cartesisch product van tabellen Product entiteiten uit twee tabellen combineren tot een nieuwe entiteit VB alle mogelijke combinaties voor gemengd spel VNaam VLft MNaam MLft V1 28 M1 28 V2 24 M2 18 V3 18 product VNaam VLft Mnaam MLft V1 28 M1 28 V2 24 M1 28 V3 18 M1 28 V1 28 M2 18 V2 24 M2 18 V3 18 M2 18

  9. Joins-1 Equi join product van tabellen met restrictie: alleen de rijen met een gelijke waarde voor een gelijk attribuut (gewoonlijk sleutel/verwijzende sleutel) VB: Geef paren met gelijke leeftijd: VNaam VLft MNaam MLft V1 28 M1 28 V2 24 M2 18 V3 18 equi-join VLft=MLft VNaam VLft Mnaam Lft V1 28 M1 28 V3 18 M2 18

  10. Joins-2 Theta join: product van 2 tabellen met restrictie: alleen rijen met ongelijke waarde voor een gelijk attribuut ( < , > , =<, => ) VB: Geef paren waarbij de man ouder is dan de vrouw VNaam VLft MNaam MLft V1 28 M1 28 V2 24 M2 18 V3 18 theta join MLft > VLft VNaam VLft Mnaam MLft V2 24 M1 28 V3 18 M1 28

  11. Joins-3 Inner join of kortweg join (SQL join of via restrictie) product van 2 (of meer) tabellen restrictie: rijen met gelijke waarde voor gelijke attributen Natural join (als boven + selectie velden) : inner join, maar dubbele kolom(men) maar 1 keer in resultaat tabel Left/right outer join (SQL left/right join of via restrictie) Van één tabel alle records, ook als er geen combinatie bestaat Full outer join (alleen via restricties) Self join (tabel dupliceren) Een tabel combineren met zichzelf

  12. Twee gerelateerde tabellen BOOKS PUBLISHERS Publisher en Book zijn aparte entiteitsklassen/ tabellen, de relatie wordt gelegd via PubID, dit is de verwijzende sleutel in BOOKS naar een Publisher in PUBLISHERS

  13. Opties voor een relatie -1 Jointype definieren in relatieschema of QBE Wil je, als je de relatie boek-uitgever gebruikt in een query 1. alleen de combinaties van beiden zien? 2. alle boeken, en alleen de bijbehorende uitgevers? 3. alle uitgevers, en alleen de bijbehorende boeken? Default in Access is 1, maar soms is een andere keus nuttig, dan kun je b.v. zoeken naar de uitgevers zonder boeken. Als je een relatie definieert kun je kiezen. 1 is de equi-join, 2 en 3 zijn outer joins

  14. Twee eisen aan een veld? Wie verkoopt tofu en konbu? Eerste interpretatie: of Select s.SupplierName FROM Suppliers s, Products p WHERE s.SupplierID = p.SupplierID AND (p.ProductName = ‘tofu’ OR p.Productname = ‘konbu’); s p SupplierName SupplierID SupplierID ProductName Mayumi’s 6 6 konbu Mayumi’s 6 6 tofu NewSupp 11 11 konbu Een WHERE clause geeft de restricties aan voor één record (regel) van de (combinatie)tabel. Hoe regel je de interpretatie zowel .. als .. ?

  15. Een tabel tweemaal raadplegen Stileren: Geef de naam van de Supplier s waarvoor geldt dat s verkoopt ‘tofu’ en s verkoopt ‘konbu’ oftewel: er is een product p1 in Products met p1.ProductName = ‘tofu’ te koop bij s (p1.SupID = s.SupID) en er is een product p2 in Products met p2.ProductName = ‘konbu’ te koop bij s (p2.SupID = s.SupID)

  16. 2 eisen aan 1 record via duplicaattabel(ook andere mogelijkheden) SELECT s.CompanyName FROM Suppliers s, Products p1, Products p2 WHERE s.SupplierID = p1.SupplierID AND p1.ProductName = ‘tofu’ AND s.SupplierID = p2.SupplierID AND p2.ProductName = ‘konbu’ s p1 p2 SupName SupID SupID Product SupID Product Mayumi’s 6 6 tofu 6 konbu

  17. Statistische functies Geef aantal boeken, som, gemiddelde, maximum en minimum prijs van BOOKS: SELECT COUNT(*) AS N, SUM(Price) AS Sum, AVG(Price) AS Avg, MAX(Price) AS Max, MIN(Price) AS Min FROM BOOKS N Sum Avg Max Min 15 F 659,90 F 43,99 F 250,00 F 12,00

  18. Statistische functies per groep -1 Geef aantal boeken, totale prijs en gemiddelde prijs per PubID SELECT COUNT(*) AS N, SUM(Price) AS Sum, AVG(Price) AS Avg, PubID FROM BOOKS GROUP BY PubID N Sum Avg PubID 6 F 139,95 F 23,33 1 3 F 103,00 F 34,33 2 5 F 166,95 F 33,39 3 1 F 250,00 F 250,00 4

  19. Stat. functies per groep - 2 Hoe duur is het duurste boek van elke uitgever? Geef naam en nummer van de uitgever en zijn hoogste prijs, sorteer op prijs SELECT p.PubName, p.PubID, Max(b.Price) AS Maximum FROM BOOKS b, PUBLISHERS p WHERE b.PubID = p.PubID GROUP BY p.PubName, p.PubID ORDER BY Max(b.Price); In ORDER BY en GROUP BY geen aliasvoor veldnaam (dus niet Maximum gebruiken i.p.v. Max(b.Price) In GROUP BY allevelden uit de SELECT-clause die geen stat. functie bevatten.

  20. Statistische functies: groepscriterium Geef de gemiddelde prijs van de boeken van die uitgever(s) waarvan we meerdere boeken hebben Stileren: Geef de PubIDs in BOOKS, met de gemiddelde prijs van hun BOOKS voor die PubID waarvoor geldt dat het aantal BOOKS > 1 Genereren: SELECT PubID, AVG(Price) FROM BOOKS GROUP BY PubID HAVING COUNT(*) > 1; Een criterium op recordniveau zet je inWHERE HAVINGgeeft een statistisch criterium voor een groep

  21. Syntax van SELECT.. SELECT (DISTINCT) attribuut|expression (AS kolomnaam), .. -> keuze van de velden in de resultaattabel FROM tabel (AS) alias, .. -> benodigde tabellen, eventueel met afkorting WHERE relatiecriteria AND zoekcriterium AND/OR .. -> met diverse vergelijkingsoperatoren (<, >, =) GROUP BY alle niet-statistische velden in resultaattabel -> bij gebruik van stat. functies in SELECT clause HAVING statistisch criterium -> criteria voor subgroepen ORDER BY attr -> sorteren van resultaattabel

  22. Oefenen Geef de uitgevernamen + aantal door hen uitgegeven boeken van de uitgevers die een gemiddelde prijs onder de 25 hebben SELECT p.PubName, COUNT(b.ISBN) As Aantal FROM PUBLISHERS p, BOOKS b WHERE p.PubID = b.PubID GROUP BY p.PubID, p.PubName HAVING AVG(b.Price) < 25; Waarom zou je hier (eventueel) PubID in GROUP BY zetten?

  23. Subquery 1 1. Wat is de hoogste boekenprijs in de collectie? SELECT MAX(b.Price) FROM BOOKS b; 2. Wat is het duurste boek uit de collectie? ???

  24. Subquery 1 - vervolg Stileren: Title van BOOKS waarvoor-geldt-dat Price is de hoogste prijs in BOOKS SQL genereren: SELECT Title FROM BOOKS WHERE Price = (SELECT MAX(Price) FROM BOOKS); In de subquery (subselect) wordt opnieuw naar de tabel BOOKS gekeken om het maximum op te zoeken NIET:WHERE Price = Max(Price) want wat zou dat betekenen?

  25. Subquery 2 Geef me de boeken die duurder zijn dan gemiddeld Stileren: BOOKS b waarvoor geldt dat Price groter is dan de gemiddelde Price van BOOKS Genereren: SELECT ISBN, Title, Price FROM BOOKS WHERE Price > (SELECT AVG(Price) FROM BOOKS);

  26. Subquery 3 Wat is de langste auteursnaam in ons bestand en hoe lang is hij? Stileren: a.AuName en lengte van a.AuName van AUTHORs a waarvoor-geldt-dat de lengte van a.AuName is de maximumlengte van AuName in AUTHORS of de lengte van a.AuName is groter of gelijk aan alle waarden van AuName in AUTHORS

  27. Subquery 3 - SQL Genereren: SELECT AuName, Len(AuName) FROM AUTHORS WHERE Len(AuName) = (SELECT Max(Len(AuName)) FROM AUTHORS); of: SELECT AuName, Len(AuName) FROM AUTHORS WHERE Len(AuName) >= ALL (SELECT Len(AuName) FROM AUTHORS); NB naast ALL bestaat ook ANY en SOME

  28. IN met een subquery IN kan wordt gebruikt als meer dan een waarde kan voldoen ; zulke waarden kunnen worden opgehaald met een subselect. Geef de boeken van publishers met een naam op ‘House’ SELECT * FROM BOOKS WHERE PubID IN (SELECT p.PubID FROM PUBLISHERS p WHERE p.PubName LIKE "*House";) N.B. Alternatief in dit geval natuurlijk SELECT .. (BOOKSvelden) FROM BOOKS b, PUBLISHERS p WHERE b.PubID = p.PubID AND p.PubName LIKE "*House";

  29. 2 eisen aan 1 record: Via Exists Omdat tabel s niet in de FROM-clause staat, wordt de ‘centrale’ s.SupplierID gebruikt! EXISTS: geeft aan dat een subselect een resultaat heeft (de set niet leeg is) SELECT s.CompanyName FROM Suppliers s WHERE EXISTS (SELECT * FROM Products p WHERE s.SupplierID = p.SupplierID AND p.ProductName = 'tofu’) AND EXISTS (SELECT * FROM Products p WHERE p.SupplierID = s.SupplierID AND p.ProductName = 'konbu');

  30. NOT EXISTS Is er een uitgever zonder boeken? Stileren: Geef de publisher p van PUBLISHERS waarvoor geldt dat er geen boek b van BOOKS bestaat dat p heeft b uitgegeven (dus PubID gelijk) SELECT * FROM PUBLISHERS p WHERE NOT EXISTS (SELECT * FROM BOOKS b WHERE p.PubID = b.PubID); (alternatief: outer join + NULL-criterium)

  31. Statistische eisen aan een groep Uitgevers met minder dan 3 boeken: SELECT p.PubName FROM PUBLISHERS p, BOOKS b WHERE p.PubID = b.PubID GROUP BY p.PubName HAVING COUNT(b.Title) < 3; De uitgever met de minste boeken?

  32. Subselect in HAVING SELECT p.PubName, COUNT(b.ISBN) FROM Publishers AS p, Books AS b WHERE p.PubID=b.PubID GROUP BY p.PubName HAVING COUNT(b.ISBN) <= ALL (SELECT COUNT(ISBN) FROM BOOKS GROUP BY PubID)

  33. Meer dan één Vraag: Geef me de auteurs die bij meer dan één uitgever zijn Stileren: Geef de a.AuNames in AUTHORS waarvoor geldt dat voor hun BOOKS b (relatie via BOOK/AUTHOR) geldt dat het aantal verschillende b.PubID >1

  34. Meer dan een? SELECT a.AuName FROM BOOKS b, [BOOK/AUTHOR] ba, AUTHORS a WHERE a.AuID = ba.AuID AND b.ISBN = ba.ISBN GROUP BY a.AuName HAVING COUNT(b.PubID) > 1; Wat gaat hier fout? Probleem: COUNT(DISTINCT ..) wordt niet geaccepteerd, dus bij elk boek wordt PubID meegeteld, of hij hetzelfde of anders is dan de vorige

  35. Meer dan 1: dubbele tabel JOIN- Structuur: BOOKS BOOK/AUTH AUTHORS BOOK/AUTH BOOKS b1 --- ba1 --- a --- ba2 --- b2 SELECT DISTINCT a.AuName FROM BOOKS AS b1,[BOOK/AUTHOR] AS ba1, AUTHORS AS a, [BOOK/AUTHOR] AS ba2, BOOKS AS b2 WHERE b1.ISBN = ba1.ISBN AND ba1.AuID = a.AuID AND b2.ISBN = ba2.ISBN AND ba2.AuID = a.AuID AND b1.PubID <> b2.PubID; of via EXISTS

  36. Meer dan x???

  37. Meer dan x uitgevers: via 2 queries Maak eerst een query die alle bestaande relaties AUTHOR-PUBLISHER toont, sla hem op als AUTH_PUB SELECT DISTINCT a.AuName, p.PubID FROM AUTHORS a, [BOOK/AUTHOR] ba, BOOKS b, PUBLISHER p WHERE a.AuID = ba.AuID AND ba.ISBN = b.ISBN AND b.PubID = p.PubID; Dan een query die in AUTH_PUB de juiste records selecteert: SELECT AuName FROM AUTH_PUB GROUP BY AuName HAVING COUNT(*) > 1;

More Related