SQL
IAM-TDI-V2-SQL, handleiding datamodel
Ontwerp een datamodel Fons van Kesteren, okt 2008 , HvA IAM
IAM-V2-TDI-SQL
1
Doelstelling ........................................................................................................... 3 Het ontwerpproces................................................................................................ 4 Afbakening van het informatiedomein................................................................... 5 Entiteiten, attributen en relaties......................................................................... 5 Functionaliteiten ................................................................................................ 5 Onderdelen van een datamodel ........................................................................... 6 ERD diagram..................................................................................................... 6 Tabeldefinities................................................................................................... 7 Views, Stored procedures en Triggers .............................................................. 7 Eisen voor een goed bruikbaar datamodel ........................................................... 9 Naamgevingsconventies ................................................................................... 9 Normalisatie ...................................................................................................... 9 Gewaarborgde integriteit................................................................................. 10 Optimalisaties.................................................................................................. 11 Veelvoorkomende ERD patronen ....................................................................... 12 One-to-many relatie ........................................................................................ 12 Many-to-many relatie ...................................................................................... 13 Enumeratie...................................................................................................... 14 Herhalend attribuut.......................................................................................... 15 Boomstructuur................................................................................................. 17 Generalisatie ................................................................................................... 18 extra vraagstukken ............................................................................................. 19
IAM-V2-TDI-SQL
2
Doelstelling Handleiding, tips en truuks voor het ontwerpen van een genormaliseerde database model maken dat voldoet aan bepaalde functionele eisen en/of domeinspecificaties.
IAM-V2-TDI-SQL
3
Het ontwerpproces Fase 1: afbakening
Fase 2: conceptueel model
Fase 3: fysiek model
IAM-V2-TDI-SQL
4
Afbakening van het informatiedomein Om een goed datamodel te kunnen ontwerpen is het nodig het informatiedomein waarvoor je een datamodel gaat maken goed af te bakenen. Je wilt niet teveel data in je database op nemen, maar ook niet te weinig. De afbakening van je informatiedomein bestaat uit twee delen: 1. Vaststellen welke entiteiten, attributen en relaties een rol spelen 2. vaststellen welke functionaliteiten de applicatie moet kunnen bieden
Entiteiten, attributen en relaties Als eerste maak je een beknopte inhoudelijke beschrijving van je product. Vertaal dat naar enkelvoudige korte zinnen. Hieruit destilleer je de entiteiten en relaties. De zelfstandige naamwoorden zijn kandidaat-entiteiten. 1. Verwijder alle synoniemen 2. Verwijder alle ‘onbelangrijke’ woorden 3. Bepaal van de overgebleven woorden of deze betrekking hebben op informatie die je wilt kunnen bewaren in een database De lijst woorden zijn waarschijnlijk de entiteiten die je wilt gaan gebruiken. De werkwoorden in de zinnen zijn waarschijnlijk de relaties. Deze entiteiten en relaties kan je gebruiken om een conceptmap te maken. Deze conceptmap vormt de basis voor je eerste datamodel, waarin je het een en ander verder uitwerkt.
Functionaliteiten Waarschijnlijk heb je in de beschrijving van je product ook zinnen opgenomen die de gewenste functionaliteit van het product beschrijven. De meest eenvoudige en effectieve manier om overzicht te krijgen van de functionaliteiten is een use case diagram. Deze use case diagram wordt tijdens het ontwikkelen van je datamodel gebruikt om te controleren of het model goed genoeg is om deze functionaliteiten te kunnen waarmaken.
IAM-V2-TDI-SQL
5
Onderdelen van een datamodel ERD diagram Hierin worden de relaties tussen entiteiten (tabellen) grafisch worden weergegeven. In een ERD staan op zijn minst: de namen van de entiteiten (tabellen), lijnen voor iedere relatie waarbij duidelijk wordt welke soort relatie het is. En wat de PK’s en FK’s zijn in deze relatie.
MS ACCESS Een andere vorm is:
DBDesigner
NB1: in de ERD’s zijn alleen one-to-one en on-to-many relaties mogelijk. Many-to-many relaties kunnen alleen worden weergegeven met een ‘tussentabel’ NB2: merk op waar de ForeignKey’s staan bij one-to-many relaties. De FK staat altijd aan de many-kant van de relatie. Dwz in de many-tabel ( product) staat op IAM-V2-TDI-SQL
6
iedere rij een verwijzing (producentID) naar de rij aan de one-kant van de relatie (producent). Hoe zou het anders kunnen?
Tabeldefinities Voor iedere tabel is het volgende gedefinieerd: - Welke zijn de primaire sleutels van deze tabel - voor iedere kolom het datatype - voor iedere kolom of ie verplicht is of niet - voor iedere kolom eventueel default waarde als er niets is ingevuld - voor iedere kolom een index, om efficient te kunnen zoeken - voor iedere kolom wordt bepaald welke speciale beprekingen worden opgelegd aan de waarden in die kolom. Bijvoorbeeld de eindtijd is later dan de begintijd, en andersom.
Views, Stored procedures en Triggers Een ERD en de tabeldefinities zijn de belangrijkste onderdelen van een datamodel.Ze beschrijven de statische structuur van je database. Maar naast die beschrijving van de structuur van je data is het ook nuttig en soms zelfs noodzakelijk om te definiëren hoe de database gebruikt kan worden. We hebben het dan over de procedurele aspecten van je datamodel. ( NB: sommige puristen zullen beweren dat deze procedurele aspecten niet tot het datamodel behoren. ) VIEW / Query Een beschrijving van veel gebruikte en handige SELECT queries uit de database. Denk daarbij aan views van de eerste orde normaalvorm, waarbij kolommen uit gerelateerde tabellen worden geselecteerd en waarbij afgeleide kolommen worden berekend. Bijvoorbeeld: - Voor een product-tabel zal je waarschijnlijk een ProductView willen definieren waarin producentnaam, categorienaam, etc uit de gerelateerde tabellen zijn meegeselecteerd en waarbij de prijs_inclusief_btw wordt uitgerekend. ( lees les 18 uit “SQL in 10min”, voor redenen om views te definieren ) In de meeste RDBMSen is het mogelijk om een SELECT querie op te slaan. Dit wordt ook wel een VIEW genoemd. Bijvoorbeeld de ProductView uit bovenstaand voorbeeld. De tabel die het resultaat is van de SELECT querie kan benaderd worden als een gewone tabel. Je kan dan bijvoorbeeld “SELECT * FROM ProductView WHERE producentID = 1 “. STORED PROCEDURE NB: Voor IAM TDI V2-SQL is het niet nodig deze STORED PROCEDURES uit te werken. Procedures die direct te maken hebben met de integriteit en betekenis van de data in de database kunnen het beste gedefinieerd worden in de database. Je moet dan denken aan procedures die uitgevoerd moeten worden bij INSERTs, UPDATEs of DELETEs van rijen in een tabel. Enkele voorbeelden daarvan kunnen zijn
IAM-V2-TDI-SQL
7
- wanneer een bestelling wordt uitgevoerd, gaat het niet alleen de status van de bestelling , maar moet ook alle aantallen in voorraad van de bestelde producten omlaag. - Wanneer een gebruiker een boek selecteert moet dat boek toegevoegd worden aan de tabel meest_recent_geselecteerde_boeken van die gebruiker. ( lees les 19 en 22 van “SQL in 10min”, voor redenen om SP’s te definiëren) Dit soort procedures kan je beter niet overlaten aan de applicatie die gebruik maakt van de database. Het kan natuurlijk wel, maar als ontwerper van de database zal je willen voorkomen dat een andere programmeur per ongeluk een fout maakt waardoor de data ‘corrupt’ worden. Om dat te voorkomen definieer je zelf de procedures die gebruikt moeten worden om de data in de database te manipuleren. TRIGGER Voor IAM VIO-2-SQL is het niet nodig deze TRIGGERS uit te werken. Stored procedures kunnen van buiten de database worden aangeroepen, maar ze kunnen ook automatisch worden aangeroepen bij bepaalde gebeurtenissen in de database. Die gebeurtenissen kunnen zijn: updaten het verwijderen of toevoegen van een rij in een tabel. Zo zou je bij een INSERT van een nieuwe productbestelling een procedure kunnen aanroepen die het aantal in voorraad van dat product verlaagd. Met dit sort TRIGGERS kun je er voor zorgen dat je database ‘integer’ blijft. Cascading delete Een veelgebruikte trigger is de cascading-delete trigger. Dit is bijvoorbeeld handig bij bestellingen en productenbestellingen. Wanneer je een bestelling verwijdert uit de tabel bestelling, wil je ook allen bestelling_product rijen uit de tabel bestelling_product verwijderen. Deze trigger wordt zo vaak gebruikt dat er in ACCESS een mogelijkheid bestaat om een vinkje ‘cascading delete’ aan te vinken bij de definitie van de relatie.
IAM-V2-TDI-SQL
8
Eisen voor een goed bruikbaar datamodel Een goed datamodel voldoet aan de volgende eisen: begrijpelijk, gemakkelijk, uitbreidbaar en robuust. Enkele goede voorbeelden van slecht ontwerpen: http://www.sum-it.nl/cursus/dbdesign/hollands/index.php3 Daarnaast mag een databasemodel niet te veel resources verbruiken. Dwz het mag niet te veel harde schijfruimte in beslag nemen en queries mogen niet onnodig veel processortijd verbruiken.
Naamgevingsconventies Naamgeving is altijd belangrijk wanneer er iets gecodeerd wordt. Goede namen maken het model leesbaar en begrijpelijk. Voor naamgeving van tabellen en kolommen zou je de volgende conventie kunnen hanteren: - Tabelnaam is naam van de entiteit die op één rij wordt beschreven in enkelvoud. Bijvoorbeeld: klant, bestelling, product. Wanneer je bijvoorbeeld de berichten op een forum wilt opslaan in een tabel, dan heet die tabel ‘bericht’ . Niet ‘berichten’. En zeker niet ‘forum’. - Kolomnaam is uniek binnen de tabel, ze hoeven niet betekenisvol te zijn buiten de tabel. In de tabel klant zijn dat bijvoorbeeld naam, ID. NB.:Veel ontwikkelaars hebben de neiging meervoud te maken van tabelnamen en scoop van de kolomnamen groter te maken dan de tabel. Dat kan ook, maar bovenstaande conventies sluiten beter aan bij een object-georiënteerde ontwikkelstijl. Bovendien maakt je het jezelf ermee gemakkelijker om aan de eis van normalisatie te voldoen. Als het niet mogelijk is om aan de conventies te voldoen is de database waarschijnlijk niet goed genormaliseerd.
Normalisatie Normalisatie betekent twee dingen: - alle data-elementen zijn atomair - de database bevat geen redundante informatie Beide aspecten van normalisatie dragen bij aan de begrijpelijkheid, eenvoud, uitbreidbaarheid en robuustheid van de database. Data-elementen zijn atomair Het volgende moet je niet doen: - een kolom vullen met waarden die samengesteld worden uit kleinere elementen. Bijvoorbeeld voor- en achternaam in één kolom. - een kolom vullen met herhalende waarden, bijvoorbeeld een lijst namen als er meer dan één auteur is. In dat geval moet je een nieuwe tabel gebruiken. - de betekenis van een element afhankelijk maken van de waarde in een ander element. Bijvoorbeeld bij jongens het veld ‘contact’ gebruiken voor een emailadres en meisjes datzelfde veld gebruiken voor een telefoonnummer. Geen redundante informatie De database bevat geen redundante informatie. IAM-V2-TDI-SQL
9
Dwz geen enkel stukje informatie staat meer dan één keer in de database en er staat geen data-element in die afgeleid kan worden uit andere data-elementen in de database. Om dat te bereiken moet het volgende het geval zijn: - Iedere rij kan worden geïdentificeerd met een primaire key ( of een gecombineerde sleutel ), en alle data-elementen in die rij zijn afhankelijk van die sleutel en alleen van die sleutel - Iedere rij in een tabel bevat attributen van precies één instantie van de betreffende entiteit. - Wanneer er data-elementen zijn die op één of andere manier kunnen worden afgeleid, dan worden die elementen verwijderd. NB: Vaak wordt met ‘ genormaliseerd’ de derde normaalvorm bedoeld. Er bestaat ook een eerste en tweede, maar die hebben meer te maken met het proces van normaliseren. De vierde en vijfde normaal vorm worden hier niet behandeld.
Gewaarborgde integriteit Een goed ontworpen maakt het gemakkelijk om de integriteit van de data in de database te waarborgen. Integriteit betekent dat alle data correct zijn volgens in het model gedefinieerde regels: er staan geen inconsistenties, er zijn geen verwijzingen naar dingen die niet bestaan in de database, alle data voldoen aan de gestelde beperkingen. Dat doe je in eerste instantie door de database te normaliseren. Er staat dan nooit redundante data in de database en de database kan dan dus ook niet tegenstrijdige informatie bevatten. Daarnaast kan je de RDBMS de opdracht geven de relaties altijd te checken zodat er sprake blijft van referentiele integriteit. Integriteit heeft ook te maken met gestelde beperkingen op de waarden in een kolom. Zo kan je stellen dat de vertrektijd van een trein altijd eerder is dan de aankomstijd. Of het aantal bestellingen van een product mogen niet groter zijn dan het aantal in voorraad. Er zijn vele manieren om dit soort beperkingen te waarborgen, afhankelijk van je RDBMS. Belangrijk is in ieder geval om deze beprekingen goed vast te leggen om vervolgens de beste handhavingsmethode te kiezen. Voorbeeld van een handhavingsmethode: Je kunt TRIGGERs definieren op het toevoegen, veranderen of verwijderen van data, waarmee je bepaald procedures opstart die er voor zorgen dat de data integer blijft. Een voorbeeld van een veel gebruikte TRIGGER is het cascading delete mechanisme, waarmee je er voor zorgt dat wanneer er een rij uit de primaire tabel wordt verwijdert ( een klant ) , ook alle rijen uit de afhankelijk tabel worden verwijderd ( de bestellingen van die klant ). Deze TRIGGER is zo vaak gebruikt dat MS ACCESS er een speciaal vinkje voor heeft gemaakt, zodat je het niet handmatig hoeft te definieren. Een ander voorbeeld van data-integriteit is de eis dat begintijd van een voorstelling in een theater eerder is dan de eindtijd van diezelfde voorstelling. Wanneer je deze beperking van mogelijk waarden van de velden ‘begintijd’ en ‘eindtijd’ wilt waarborgen kan je een TRIGGER op het invoeren van deze velden IAM-V2-TDI-SQL
10
definiëren waarin je een controle procedure uitvoert die een error geeft als de begintijd later is dan de eindtijd. In veel databasesystemen bestaat de mogelijkheid om deze beperkingen in te voeren en automatische te laten controleren.
Optimalisaties Wanneer database heel erg groot zijn en/of vaak gebruikt worden, kan het noodzakelijk zijn om het model zo te ontwerpen dat ie zo min mogelijk resources gebruikt. INDEXEREN De meest gemakkelijke manier om een database te optimaliseren is het door gebruik te maken van ‘indexen’. Wanneer er vaak gezocht moet worden in een bepaalde kolom van een database, kan je data zoeken optimaliseren door een index aan te maken. Dat is simple weg een vinkje aan zetten in de tabeldefinitie. De database kan dan sneller zoeken, maar het heeft ook een nadeel: het creëren van de index kost ook tijd. Het update van zo’n tabel gaat dus weer trager. Je moet die twee dus afwegen Meestal is dat maken van een index echter niet zo’n groot probleem. Bij grote tekst velden echter kan het bijhouden van een index van alle woorden die gevonden kunnen worden zo’n zware klus dat dat vaak maar één keer per uur, of alleen ‘s nachts plaats vindt. NORMALISEREN Het normaliseren van je database heeft tot gevolg dat informatie slechts één keer wordt opgeslagen. Dat betekent dus automatisch dat je minder harde schijf ruimte verbruikt. Twee vliegen in één klap zou je zeggen. Maar er is meer: DENORMALISEREN We hebben geleerd dat je data nooit dubbel mag op slaan, omdat het daardoor moeilijk wordt de integriteit van de data te waarborgen. Soms echter zijn queries op zo’n genormaliseerde database zo tijdrovend dat het toch de moeite loont om data dubbel op te slaan. Dit moet echter wel op een goed doordachte manier. Er moet een afweging gemaakt worden tussen normalisatie en optimalisatie. In de praktijk van tegenwoordig zijn database systemen en computers echter zo snel dat het in de meeste gevallen niet nodig is om gedenormaliseerde databasemodel te ontwerpen. NB: je zou de programmeurs niet de kost willen geven die beweren dat ze een niet-genormaliseerde database gebruiken omdat dat efficiënter is, maar het eigenlijk gewoon lastig vinden om goed te normaliseren.
IAM-V2-TDI-SQL
11
Veelvoorkomende ERD patronen In dit document worden een aantal zeer algemene ontwerpproblemen opgelost.
One-to-many relatie Probleem: Bijvoorbeeld: een product wordt geleverd door een leverancier en de leverancier levert meerdere producten. Oplossing: De tabel aan de ‘many’-kant van de relatie bevat een Foreign Key die verwijst naar de PrimaryKey van rij in de ‘one’-kant van de relatie.
IAM-V2-TDI-SQL
12
Many-to-many relatie Probleem: Bijvoorbeeld: een lezer heeft meerdere boeken, en een boek is in het bezit van meerdere lezers. Oplossing: Een many-to-many relatie kan worden gerealiseerd doormiddel van een tussentabel.
IAM-V2-TDI-SQL
13
Enumeratie Probleem: Een attribuut kan een waarde hebben uit een bepaald lijstje. Bijvoorbeeld dag_van_de_week, postcode, IAM_studie_stroming, categorie. De mogelijke waarde van deze attributen zijn steeds waarden uit een bepaalde lijst. Hoe implementeer je dat in een relationele database? Oplossing: Er zijn twee oplossingen: 1. In sommige DBMS’s kun je bij het datatype-definitie een lijstje mogelijk waarden opgeven. Dit is echter geen standaard SQL 2. Voor een enumeratie maak je een tabel waarin de mogelijke waarden worden opgenomen. Je legt een relatie tussen het attribuut en de waarden in deze enumeratie-tabel
voordeel van deze oplossing: - de database zorgt voor integriteit. Bij ‘student.stroming_naam’ kunnen alleen stromingnamen worden gebruikt die voorkomen in de tabel ‘stroming’. - deze enumeratie tabel kan gebruikt worden voor popup menu’s e.d.
IAM-V2-TDI-SQL
14
Herhalend attribuut Probleem: Soms kan een attribuut meerdere waarden hebben. Een persoon kan bijvoorbeeld twee telefoonnummers hebben. Of een speler in een kaartspel heeft acht kaarten in zijn hand. Een student volgt twee studies. Dit kan je op verschillende manieren implementeren in een fysiek datamodel Oplossing: 1. Vaak zie je dat in de tabel twee of drie kolommen worden gedefinieerd: ‘telefoonnummer1’, ‘telefoonnummer2’, ‘telefoonnummer3’. Op deze manier kan je meerdere telefoonnummers kwijt. Er zit wel een maximum aan maar dat is vaak geen probleem.
2. Je kunt voor deze waarden een nieuwe tabel definieren.
De eerste oplossing lijkt in eerste instantie het eenvoudigst. Je kunt met een simpele ‘SELECT * FROM persoon’ alle informatie selecteren, inclusief de telefoonnummers. IAM-V2-TDI-SQL
15
In andere gevallen is het echter niet zo handig. Bijvoorbeeld bij een model waarin kaartspelers en kaarten worden verdeeld over de spelers.
of
IAM-V2-TDI-SQL
16
Boomstructuur Probleem: In vele domeinen komen boomstructuren of hiërarchieën voor. Bijvoorbeeld: berichten, reacties op berichten, reacties op reacties. Categorieen, subcategorieen, subsubcategorieen. Hoe kunnen deze patronen worden gerealiseerd in een fysiek datamodel? Oplossingen: 1. aparte tabellen per niveau van de hierarchie
NB: merk op dat een product in een subsubcategorie wordt geplaatst, en daarmee automatisch in de subcategorie en categorie. 2. de hiërarchie samenstellen binnen een tabel, door middel van een ‘parentID’.
Ieder bericht verwijst naar het bericht waarop wordt gereageerd. Alleen het bovenste berichten zijn geen reactie. Daar zal de FK; bericht_ID leeg zijn. Het is wel lastig, zo niet onmogelijk, om nu een SQL querie te maken waarmee de boomstructuur van berichten netjes gesorteerd uit de database worden geselecteerd. Maar daar is wel een oplossing voor. … IAM-V2-TDI-SQL
17
Generalisatie Probleem Soms heb je in je domein entiteiten die onderverdeeld kunnen worden in verschillende typen. Bijvoorbeeld. Een database met producten bevat verschillende producttypen: vervoermiddel, audiovisuele_apparatuur, meubel. Ieder producttype heeft gemeenschappelijk attributen, zoals prijs, beschikbaarheid, levertijd. En er zijn specifieke attributen voor specifieke producttypen, bijvoorbeeld: vervoermiddel maximumsnelheid, meubel afmeting, etc. Oplossingen De relatie tussen subtype en generalisatie is een speciale 1 op 1 relatie.
IAM-V2-TDI-SQL
18
Extra vraagstukken Onderstaande vraagstukken kunnen je aan het denken zetten. geen adres of het gehele adres In je tabeldefinitie kan je aangeven of een element verplicht is of niet. Hoe zou je het aanpakken als de adresgegevens niet verplicht wilt stellen, maar als het adres wordt ingevuld dan moet het wel compleet zijn (straat,nummer,plaats, etc). de prijs verandert De bestellingendatabase is netjes genormaliseerd ( bijvoorbeeld de webwinkeldatabase). Wat gebeurt er als de prijs van een product verandert? In het huidige model zou de prijs van een bestelling ook veranderen, maar dat kan je niet maken. Die klant heeft het product tegen een bepaalde prijs besteld en wil op de bon geen andere prijs zien. Hoe zou je dat oplossen? de straatnaam normaliseren In een adressenbestand staat voor iedere persoon de straatnaam. Maar sommige personen wonen in dezelfde straat. Zou je niet een aparte tabel moeten maken voor die straten en bij de persoon alleen de straatnaamID (FK) op slaan? Hoe ver ga je met normaliseren, wat zou het criterium daarvoor kunnen zijn? het sorteren van een berichten in forum (bonuspunt) In een forum worden berichten geplaatst. Ieder bericht kan een reactie zijn op een ander bericht. Het vereenvoudige datamodel voor de tabel ‘bericht’ zou kunnen zijn: Bericht ID reactieop_berichtID Tekst Datum Reactieop_berichtID bevat het ID van het bericht waarop het een reactie is. Hoe zou je de berchten uit deze tabel kunnen sorteren op een manier dat de berchten gemakkelijk in een boomstructuur geplaatst kunnen worden. Bericht1 Reactieopbericht1.1 Reactieopreactieopbericht1.1.1 Reactieopbericht1.2 Bericht2 Reactieopberich2.1
TIP misschien is het handig om je datamodel aan te passen en er toch maar wat redundante informatie in te zetten. categorieen en subcategorieen Je hebt een database met producten ( of teams, of berichten, of competenties ) en je wilt die producten categoriseren. Een lijst met categorieen maar je hebt ook subcategorieen en misschien subsubcategorieen. Hoe ga je dat in je database weer geven? IAM-V2-TDI-SQL
19
