620 likes | 751 Views
Datamodellering en –verwerking 8C020 college 4. Terugblik college 3. Relationeel Model Extra verhaal: Turing machines, Cellulaire automaten, Game of Life Transformatie van ERM naar Relationeel Model Transformatie van entiteiten en relaties Opdracht Oefening: Zeikenhuis (RM).
E N D
Terugblik college 3 • Relationeel Model • Extra verhaal: Turing machines, Cellulaire automaten, Game of Life • Transformatie van ERM naar Relationeel Model • Transformatie van entiteiten en relaties • Opdracht • Oefening: Zeikenhuis (RM)
Onderwerpen college 4 • Normalisatie • Querytalen • SQL • Inleiding • Data definitie • Data manipulatie • Apsecten van SQL • Oefeningen
Goed gestructureerd • bij het ontwerpen van relaties streef je naar • een minimum aan redundantie en • de situatie dat gebruikers rijen kunnen • toevoegen, • veranderen, of • wegnemen • zonder dat er fouten ontstaan
WN2 WNID NAAM AFD SALARIS CURSUS DATUM 100 Anna Bruinsma Verkoop 50.000 PR 6-6-94 100 Anna Bruinsma Verkoop 50.000 Word 1-12-94 140 Carla Dik Administratie 41.000 Belastingen 25-8-94 110 Eef Groen Informatiesystemen 49.000 C++ 13-11-94 110 Eef Groen Informatiesystemen 49.000 Word 1-12-94 190 Henk Israël Inkoop 39.500 Logistiek 8-3-94 150 Janny Kramer Verkoop 45.000 PR 6-6-94 150 Janny Kramer Verkoop 45.000 Workflow 11-12-94 Een niet goed gestructureerde relatie
Anomalieën • anomalie van toevoegen • nieuwe werknemer toevoegen • een waarde voor beide cursus en wind moet er ingevoerd worden • werknemer zonder cursus? • anomalie van verwijderen • verwijderen gegevens werknemer • als werknemer 140 wordt verwijderd zijn we de informatie kwijt dat er op 25-8-94 iemand een cursus heeft afgerond • anomalie van wijzigen • veranderen van salaris • moet in elk van de rijen voor de weknemer gebeuren • niet efficient
Normalisatie • Probleem • WN2 bevat gegevens over tweeentiteiten • Oplossing • Verdeel zo’n relatie in twee relaties: WN1 en WN-CURSUS
WN1 WN-CURSUS WNID WNID CURSUS NAAM DATUM AFD SALARIS 100 100 PR Anna Bruinsma 6-6-94 Verkoop 50.000 140 100 Word Carla Dik 1-12-94 Administratie 41.000 140 110 Belastingen Eef Groen 25-8-94 Informatiesystemen 49.000 110 190 C++ Henk Israël 13-11-94 Inkoop 39.500 150 110 Word Janny Kramer 1-12-94 Verkoop 45.000 190 Logistiek 8-3-94 150 PR 6-6-94 150 Workflow 11-12-94 Normalisatie
Software Life Cycle/Fasering analyseren Omgevings anlyse, ontwikkelingsplan (kosten en tijd) eisen Eisen, planning en begroting, haalbarheidsanalyse Conceptueel model specificeren Iplementatiemodel, handleidigen, testmodel ontwerpen coderen Code, onderhoudshandeling testen tesrapporten invoeren Installatie, acceptierapport onderhouden Correctief, preventief, modificatief
Querytaal • de wijze van verwerken van gegevens met een database hangt af van • de eigenschappen van de database • de eigenschappen van de taal en het gereedschap voor het verwerken • een querytaal (eigenlijk query- en updatetaal) is een taal voor het specificeren van de definitie en manipulatie van data
Querytaal als interface • een querytaal biedt een interface tussen DBMS en gebruiker • sommige querymechanismen zijn intuïtief en eenvoudig • QBE (Query By Example) • sommige querytalen zijn uitgebreider en krachtiger en lijken meer op programmeertalen (''wel wat, niet hoe'') • SQL • een querytaal heeft een duale rol: • definitie • manipulatie
SQL • SQL (Structured Query Language) is geintroduceerd door IBM, 1979 • San Jose Research Laboratory • Sequel (voorouder), begin jaren ‘70 • SQL is de meest gebruikte querytaal en de de-facto standaard • er zijn veel dialecten en varianten van SQL beschikbaar
SQL • data-definitie • het definiëren van relaties en databases en het veranderen van definities • data-manipulatie • formuleren van queries • toevoegen van gegevens • verwijderen van gegevens • wijzigen van gegevens
Taal en DBMS • een querytaal is nauw verbonden met een DBMS • om gegevens te gaan definiërenzijn meestal privileges(autorisatie) nodig • SQL (of de SQL-omgeving) biedt faciliteiten voor de toegang tot de gegevens • eerst inloggen op het DBMS • Interactief gebruik
Definiëren van een database • Coderen/implementatie fase van de “life cycle” • we gebruiken de volgende opdracht om een database aan te makenmet de naam ziekenhuis • CREATE DATABASE ziekenhuis • ANSI-standaard gebruikt schema in plaats van database • CREATE SCHEMA ziekenhuis
Definiëren van een tabel • we beginnen met de volgende uitdrukkingals we een tabel willen definiëren met gegevens over patiënten • CREATE TABLE patienten • het DBMS weet dan dat er een tabel gaat zijnmet gegevens over patiënten
Attributen en domeinen • we gebruiken de volgende uitdrukking om vast te leggen dat we een attribuut hebben voor patiëntennummer met als domein alle rijtjes van 5 karakters en dat bovendien een primaire sleutel is • patnr char(5) not null unique • de naam van het attribuut • het domein van het attribuut • integer • smallint • float • real • date • mag het attribuut null-waarden hebben? • moeten de waarden in verschillende records verschillend zijn?
Volledige definitie van een tabel • CREATE TABLE patienten • ( patnr char(5) not null unique, • naam char(20), • voornaam char(15), • huisarts integer not null, • gebdatum date • );
Veranderen van definities • verwijderen van een database • DROP DATABASE ziekenhuis; • verwijderen van een tabel • DROP TABLE patienten; • veranderen van een tabel • ALTER TABLE patienten DROP huisarts; • ALTER TABLE patienten ADD aantkind integer;
Onderhouden van privileges • SQL biedt commando’s voorhet onderhouden van de toegangsrechten • GRANT SELECT .. • GRANT INSERT .. • GRANT UPDATE .. • GRANT DELETE ..
View, index • view • een imaginaire, niet echt bestaande tabeldie door het DBMS wordt afgeleid van bestaande tabellen en net als een gewone tabel is te gebruiken • index • een DBMS-mechanismeom snelle toegang te bieden tot gegevens • standaard is er bijvoorbeeldeen index gebaseerd op de primaire sleutel
Data-manipulatie • data-manipulatie is niet mogelijk zonder data-definitie • in de praktijk zijn deze activiteiten soms gescheiden(bijvoorbeeld bij verschillende gebruikers) • de doelstelling van data-manipulatie is om gegevens te • inspecteren • wat is de waarde van ...? • veranderen • vervang de waarde van ... door ...
patnr naam voornaam huisarts gebdatum 12345 Appels Arno 13 03-05-68 23456 Peren Pom 13 23-06-70 34567 Storms Pim 14 13-06-36 12346 Appels Arno 14 23-12-45 24564 Janssen Jeff 15 01-07-39 76543 Brandsma Hein 15 01-11-71 Voorbeeld tabel voorpatienten
SELECT FROM • eerst specificeren we welke attributen we willen hebben • SELECT patnr, naam • dan duiden we aan uit welke tabel de gegevens komen • FROM patienten • de volledige query ziet er dan als volgt uit • SELECT patnr, naam • FROM patienten;
patnr naam 12345 Appels patnr naam voornaam huisarts gebdatum 23456 Peren 12345 Appels Arno 13 03-05-68 34567 Storms 23456 Peren Pom 13 23-06-70 12346 Appels 34567 Storms Pim 14 13-06-36 24564 Janssen 12346 Appels Arno 14 23-12-45 76543 Brandsma 24564 Janssen Jeff 15 01-07-39 76543 Brandsma Hein 15 01-11-71 Voorbeeld de query SELECT patnr, naam FROM patienten; toegepast op levert op
DISTINCT, * • de tabellen in SQL bevatten dubbele rijen • dus ook de antwoorden op queries bevatten dubbele waarden • met DISTINCT worden dubbele waarden uitgesloten • SELECT DISTINCT naam • FROM patienten; • * • met * worden alle attributen aangeduid • SELECT * • FROM patienten;
WHERE • met behulp van een WHERE-uitdrukking kunnen we een voorwaarde vastleggen voor de selectie van rijen uit een tabel • SELECT patnr, naam • FROM patienten • WHERE huisarts = 13; • 1. beschouw stuk voor stuk de rijen uit de tabel van FROM • 2. verifieer of de rij voldoet aan de voorwaarde van WHERE • 3. geef voor de overgebleven rijen de attributen van SELECT
patnr patnr naam naam voornaam huisarts gebdatum 12345 12345 Appels Appels Arno 13 03-05-68 23456 23456 Peren Peren Pom 13 23-06-70 34567 Storms Pim 14 13-06-36 12346 Appels Arno 14 23-12-45 24564 Janssen Jeff 15 01-07-39 76543 Brandsma Hein 15 01-11-71 Voorbeeld • SELECT patnr, naam • FROM patienten • WHERE huisarts = 13; resultaat
AND en OR • voorwaarden in de WHERE-regel kunnen gecombineerd worden met AND en OR • SELECT patnr, naam • FROM patienten • WHERE huisarts = 3 • AND gebdatum = '01-01-94'; • SELECT patnr, huisarts • FROM patienten • WHERE gebdatum = '31-12-93' • OR gebdatum = '01-01-94';
Opgave 5.1 • wat drukt de volgende query uit? • SELECT patnr, huisarts • FROM patienten • WHERE gebdatum = '31-12-93' • AND gebdatum = '01-01-94';
!= • het symbool != wordt gebruikt voor de ongelijkheid • SELECT patnr, naam • FROM patienten • WHERE naam != 'De Groot';
NOT • het symbool NOT wordt gebruikt voor de ontkenning • SELECT patnr • FROM patienten • WHERE NOT(gebdatum ='31-12-93' • OR gebdatum = '01-01-94');
LIKE • het symbool LIKE wordt gebruikt voor bijna-gelijk • SELECT naam • FROM patienten • WHERE naam LIKE 'J%'; • of • ... • WHERE naam LIKE 'J__s';
Opgave 5.2 • wat drukt de volgende query uit? • SELECT naam • FROM patienten • WHERE voornaam != 'Bas';
Voorbeeld • we hebben een relatie (tabel) ouders • met attributen ouder en kind • ouders(ouder,kind) • opgave 5.3 • wat drukt de volgende query uit? • SELECT ouder • FROM ouders • WHERE kind != 'Bas';
BETWEEN • het symbool BETWEEN wordt gebruikt voor intervallen • SELECT naam • FROM patienten • WHERE huisarts BETWEEN 7 AND 10;
IN • het symbool IN wordt gebruikt voor verzamelingen • SELECT * • FROM patienten • WHERE gebdatum IN (%90,%91,%92);
UNION • het symbool UNION wordt gebruikt voor de vereniging • SELECT naam, huisarts • FROM patienten • WHERE gebdatum IN (%90,%91,%92) • UNION • SELECT naam, huisarts • FROM patienten • WHERE voornaam = 'Jan';
Veranderen van gegevens • je wil niet alleen opgeslagen informatie opvragenmaar ook opgeslagen informatie veranderen(onderhoud) • SQL biedt commando's voor hetverwijderen, toevoegen en wijzigen van gegevens
DELETE • met het DELETE-command verwijderen we rijen uit een tabel • DELETE FROM patienten • WHERE patnr = '9399'; • DELETE FROM patienten • WHERE naam = 'Appels'; • DELETE FROM patienten;
INSERT • met het INSERT-commando voegen we een record toe aan een tabel • INSERT INTO patienten • VALUES('9401','Dikker','Ruud', • 12,25-09-69);
UPDATE • met het UPDATE-commando • wijzigen we (waarden van) records • UPDATE patienten • SET huisarts = 25 • WHERE huisarts = 20;
hanr Patnr hanaam pnaam padres haadres pwoonpl haplaats huisarts geb 12345 Appels Brink 12 Son 13 03-05-68 13 B. Brandsma Brink 89 Son 23456 Peren Markt 1 Son 13 23-06-70 14 S. Storms Laan 84 Oss 34567 Storms Laan 88 Oss 14 13-06-36 15 G. de Groot Weg 17 Lelystad 12346 Appels Laan 86 Oss 14 23-12-45 16 G. Groten Weg 19 Almere 24564 Janssen Weg 4a Almere 15 01-07-39 76543 Brandsma Weg 67 Almere 15 01-11-71 Join - voorbeeld
Join • het leggen van een verbinding tussen tabellen • kan met behulp van een join-operatie • SELECT pnaam, hanaam • FROM patienten, huisartsen • WHERE patienten.pwoonpl = • huisartsen.haplaats; • 1. beschouw stuk voor stuk de rijen uit de eerste FROM tabel • 2. beschouw stuk voor stuk de rijen uit de tweede FROM tabel • 3. verifieer of de combinatie voldoet aan de WHERE regel • 4. geef voor de goede combinaties de SELECT attributen
pnaam hanaam Appels B. Brandsma Peren B. Brandsma Storms S. Storms Appels S. Storms Janssen G. Groten Brandsma G. Groten Join geef de namen van patiënten en de namen van huisartsen waarvoor geldt dat de patiënt woont in de plaats waar de huisarts praktijk houdt
Join • in principe krijgen we bij een join combinaties van willekeurige tupels • meestal hebben we behoefte aan combinaties van gerelateerde tupels • SELECT pnaam, hanaam • FROM patienten, huisartsen • WHERE patienten.huisarts = • huisartsen.hanr; • 1. beschouw stuk voor stuk de rijen uit de eerste FROM tabel • 2. beschouw stuk voor stuk de rijen uit de tweede FROM tabel • 3. verifieer of de combinatie voldoet aan de WHERE regel • 4. geef voor de goede combinaties de SELECT attributen
pnaam hanaam Appels B. Brandsma Peren B. Brandsma Storms S. Storms Appels S. Storms Janssen G. de Groot Brandsma G. de Groot Join geef de namen van patiënten en de namen van hun huisartsen
pnaam hanaam Appels B. Brandsma Peren B. Brandsma Storms S. Storms Appels S. Storms Join SELECT pnaam, hanaam FROM patienten, huisartsen WHERE patienten.huisarts = huisartsen.hanr AND patienten.woonpl = huisartsen.haplaats; geef de namen van patiënten en de namen van hun huisartsen voor die patiënten waarvoor geldt dat zij wonen in de plaats waar hun huisarts praktijk houdt
hanr Patnr hanaam pnaam padres haadres pwoonpl haplaats huisarts geb 12345 Appels Brink 12 Son 13 03-05-68 13 B. Brandsma Brink 89 Son 23456 Peren Markt 1 Son 13 23-06-70 14 S. Storms Laan 84 Oss 34567 Storms Laan 88 Oss 14 13-06-36 15 G. de Groot Weg 17 Lelystad 12346 Appels Laan 86 Oss 14 23-12-45 16 G. Groten Weg 19 Almere 24564 Janssen Weg 4a Almere 15 01-07-39 76543 Brandsma Weg 67 Almere 15 01-11-71 Join - voorbeeld
Join • voorbeeld • SELECT * • FROM patienten, huisartsen; • voorbeeld • SELECT * • FROM patienten, huisartsen • WHERE patienten.huisarts = • huisartsen.hanr;