Integriteitsbewaking bij een relationele database

Collegedictaat IS0-cursus
Integriteits-bewakingsdeel
Integriteitsbewaking bij een relationele database
Bij het ontwikkelen van een informatiesysteem voor een organisatie moet er koste wat koste voor gezorgd
worden, dat er geen tegenstrijdige gegevens in de database terecht komen. Als voorbeeld werd al eerder
genoemd, zal voor het informatiesysteem van een bibliotheek dus beslist voorkomen moeten worden dat volgens
de in de database opgeslagen gegevens een níet-bestaand boek uitgeleend is aan een niet-bestaand bibliotheeklid.
Uiteraard hoef je je alleen te bekommeren om die constraints als je wijzigingen (via een INSERT-, DELETE- of
UPDATE-commando) in de opgeslagen gegevens wilt aanbrengen.
In deze cursus hebben we gezien, dat een van de uitkomsten van de informatie-modellering is, dat de
beperkingsregels (constraints) die binnen een database gerespecteerd en/of afgedwongen moeten worden, boven
water zijn gekomen. De kwestie is nu dus om die gevonden constraints ook daadwerkelijk te implementeren.
Bij informatie-modellering hebben we gezien, dat tot die beperkingsregels behoren:
• totale (verplichte) rollen;
• uniciteitsregels;
• aantallenregels;
• deelverzamelingsregels;
• waarderegels;
• uitsluitingsregels.
Moesten in het verleden deze constraints door noest programmeerwerk in een derde generatietaal (3GL) worden
afgedwongen, gelukkig is de tendens om de (R)DBMS’s zodanig te maken, dat de informatiesysteem-ontwerper
en -bouwer slechts aan het DBMS hoeft duidelijk te maken dat die constraints bestaan en dat het RDBMS ze dan
maar moet bewaken.
Een tweetal (in de praktijk toegepaste) mogelijkheden dienen zich hiervoor aan:
• gebruik maken van (beter wordende) DDL (Data Definition Language) mogelijkheden van SQL;
• gebruik maken van zogenaamde stored procedures en triggers voor het aanroepen daarvan.
Gezien de aard van de cursus zullen we ons hier vooral richten op de eerst genoemde mogelijkheid. We zullen
daarbij kijken naar welke mogelijkheden de SQL1-definitie (uit 1986) en de SQL2-definitie (uit 1992) ons
bieden. Ofschoon er reeds druk gewerkt wordt aan het opstellen van een SQL3-standaard, gebiedt de eerlijkheid
te stellen, dat er momenteel nog geen (of maar héél weinig) RDBMS’s zijn, die de volledige SQL2-standaard
geïmplementeerd hebben.
Integriteitsbewaking zònder SQL-DDL (Data Definition Language) te gebruiken
Behalve via het eerder genoemde ‘noeste programmeerwerk’ in vaak een 3GL (al dan niet gecombineerd met
SQL-statements tot embedded SQL) is een tamelijk ‘primitieve’ manier van integriteitsbewaking die waarbij
alleen gebruik gemaakt wordt van de ROLLBACK WORK- en COMMIT WORK-instructies van SQL.
Dat betekent, dat we eerst een wijziging voorlopig doorvoeren, dan controleren of er in de ontstane situatie geen
beperkingsregels zijn overtreden en tot slot: als de database integer is, het COMMIT WORK-commando geven,
doch als er tegenstrijdige gegevens in de database zijn opgeslagen, het ROLLBACK WORK-commando geven,
waardoor alle wijzigingen teniet worden gedaan en we weer terugkeren naar de oude, integere situatie.
Het controleren van de integriteit van de ontstane (voorlopige!) situatie kan dan gebeuren met zogenaamde
negatieve SELECT-statements. Dat betekent dat we SELECT-queries zodanig formuleren, dat niet-toegestane
situaties zouden worden opgespoord en getoond.
In zo’n pre-SQL1-situatie zou de definitie van de president-gegevenstabel slechts kunnen zijn:
CREATE TABLE PRESIDENT
(PRES_NAME
CHAR (16),
BIRTH_YR
SMALLINT ,
YRS_SERV
SMALLINT ,
DEATH_AGE
SMALLINT ,
PARTY
CHAR (11),
STATE_BORN CHAR (15)
)
1
Collegedictaat IS0-cursus
Integriteits-bewakingsdeel
Als we er bijvoorbeeld toch voor moeten zorgen, dat in de kolommen Pres_name, Birth_yr, Yrs_serv en Party
altijd een waarde moet zijn ingevuld (dus: er geen NULL-waarden in mogen staan), dan zouden we na
(voorlopige) veranderingen in de tabelgegevens als negatieve SELECT-commando kunnen geven:
SELECT
FROM
WHERE
OR
*
PRESIDENT
PRES_NAME IS NULL
YRS_SERV IS NULL
OR
OR
BIRTH_YR IS NULL
PARTY IS NULL
Indien hierna een of meer tabelregels getoond worden, dan voldoen een of meer velden uit de getoonde regels
niet aan de totale-rol-beperkingsregel en via een ROLLBACK WORK-commando moeten we vervolgens zo’n
niet toegestane gegevensverandering weer teniet doen. Als er ná voorgaand commando géén tabelregels getoond
worden, dan lijkt alles (althans wat de genoemde totaliteitsregels betreft) in orde te zijn en kunnen we via een
COMMIT WORK-commando de voorlopige wijzigingen definitief maken.
Op eenzelfde manier kunnen we ook allerlei andere beperkingsregels testen.
Neem bijvoorbeeld de uniciteitsregel dat in de president-tabel een bepaalde presidentsnaam maar één keer mag
voorkomen en (waarderegel:) dat het geboortejaar van een president nooit voor 1700 kan zijn geweest.
De hierbij behorende negatieve SELECT-commando’s zijn dan achtereenvolgens:
SELECT
FROM
GROUP BY
HAVING
en:
SELECT
FROM
WHERE
*
PRESIDENT
PRES_NAME
COUNT (*) > 1
*
PRESIDENT
BIRTH_YR < 1700
( <= uniciteitsregel !)
(<= waarderegel of ‘domein-eis’ voor jaartallen in deze database)
Voor de tabel ADMINISTRATION geldt, dat de combinatie ‘admin_nr + pres_name’ niet vaker dan éénmaal
mag voorkomen:
SELECT
FROM
GROUP BY
HAVING
ADMIN_NR, PRES_NAME
ADMINISTRATION
ADMIN_NR, PRES_NAME
COUNT (*) > 1
Leeftijden noch kindertal zullen ooit negatief kunnen zijn, dus moeten we checken:
SELECT
FROM
WHERE
*
PRES_MARRIAGE
PR_AGE < 0 OR
SP_AGE < 0
OR NR_CHILDREN < 0
De combinatie van ‘admin_nr + pres_name’, zoals die voorkomt in de Admin_pr_vp -tabel zal (immers:
deelverzamelingsregel) voor moeten komen in de Administration-tabel:
SELECT
FROM
WHERE
*
ADMIN_PR_VP APV
NOT EXISTS (SELECT *
FROM
ADMINISTRATION
WHERE
ADMIN_NR = APV.ADMIN_NR
AND PRES_NAME = APV.PRES_NAME)
En zo moeten we het naleven van nog enorm veel andere constraints controleren. Voor het integer houden van
de gehele presidentiële database zijn dus enorm veel negatieve SELECT’s nodig.
Voor elke negatieve SELECT geldt, dat bij het tonen van een of meer tabelregels een constatering van een
overtreding van een beperkingsregel heeft plaatsgevonden en dus: ROLLBACK WORK, want die ontstane
voorlopige situatie moet teruggedraaid worden.
2
Collegedictaat IS0-cursus
Integriteits-bewakingsdeel
Integriteitsbewaking via SQL1-DDL
De Data Definition Language van SQL1 biedt mogelijkheden om 2 genoemde constraint-typen af te dwingen:
• totale rollen zijn af te dwingen via de NOT NULL-optie in het CREATE TABLE-commando;
• uniciteitsregels zijn af te dwingen via de UNIQUE INDEX-mogelijkheid.
eventueel hoort hier nog de optie bij:
• het voor wijzigen benaderen van gegevenstabellen via View-definities WITH CHECK OPTION.
Het opzetten van de president-gegevenstabel kan met SQL1-DLL als volgt:
CREATE TABLE PRESIDENT
(PRES_NAME
CHAR (16)
BIRTH_YR
SMALLINT
YRS_SERV
SMALLINT
DEATH_AGE
SMALLINT
PARTY
CHAR (11)
STATE_BORN CHAR (15)
met daarbij:
CREATE UNIQUE INDEX
PRES_PRIM
NOT NULL ,
NOT NULL ,
NOT NULL ,
,
NOT NULL ,
)
ON
PRESIDENT (PRES_NAME)
We hebben hierbij gebruik gemaakt van de Data Definition Language mogelijkheden van de NOT NULL-optie
en de UNIQUE INDEX. Op deze manier zal het RDBMS zélf ervoor zorgen, dat de betreffende totaliteitsregels
(via het NOT NULL) en de uniciteitsregel (primary key) over de presidentsnaam gehandhaafd zal blijven. Als
we proberen om toch een wijziging door te voeren, waardoor die regels overtreden zouden worden, dan zal het
RDBMS een (door ons aanpasbare) foutmelding over de afgewende schending van de constraints geven.
Uiteraard zijn hier met betrekking tot de presidentiële database nog vele andere voorbeelden te geven. We geven
hier nog:
CREATE TABLE PRES_MARRIAGE
(PRES_NAME
CHAR (16)
SPOUSE_NAME CHAR (16)
PR_AGE
SMALLINT
SP_AGE
SMALLINT
NR_CHILDREN SMALLINT
MAR_YEAR
SMALLINT
met daarbij:
NOT NULL ,
NOT NULL ,
,
,
,
)
CREATE UNIQUE INDEX PRESMAR_PRIM
ON PRES_MARRIAGE (PRES_NAME, SPOUSE_NAME)
Let hier op het gebruik de mogelijkheid om impliciet een primary key te definiëren over twéé kolommen.
Middels de definitie van Views With Check Option is het mogelijk domeinintegriteitseisen (lees: waarderegels),
niet-sleutel-verwijzingen en op beperkte schaal zelfs bijzondere integriteitseisen te bewaken. We illustreren dit
voor de tabellen PRESIDENT en PRES_MARRIAGE:
CREATE VIEW PRES_VIEW AS
SELECT *
FROM
PRESIDENT
WHERE
BIRTH_YR >= 1700 AND
AND
PRES_NAME IN ( SELECT
FROM
WITH CHECK OPTION
CREATE VIEW PRES_MAR_VIEW
SELECT *
FROM
PRES_MARRIAGE
WHERE
PR_AGE >= 0 AND
AND NR_CHILD>= 0 AND
WITH CHECK OPTION
DEATH_AGE >= 0
PRES_NAME
ADMINISTRATION)
AS
SP_AGE >= 0
MAR_YEAR >= 1700
Bij de bespreking van views is besproken, dat dankzij de With Check Option het niet mogelijk zal zijn om
(opererend op de view!) in de onderliggende gegevenstabel wijzigingen aan te brengen, die indruisen tegen de
criteria zoals gesteld in de WHERE-regel. Uiteraard moet er dan wel voor gezorgd worden dat niemand nog
rechtstreeks de onderliggende gegevenstabellen kan benaderen voor het doorvoeren van veranderingen.
3
Collegedictaat IS0-cursus
Integriteits-bewakingsdeel
Opvragingen kunnen echter beter - want sneller - direct via de basistabellen blijven verlopen.
Het is soms zelfs mogelijk om via Views With Check Option deelverzamelingsregels af te dwingen. We kunnen
dat bereiken door een subquery-constructie. Als voorbeeld geven we, dat de waarde voor Admin_entered in de
State-gegevenstabel alleen die kan zijn van een bestaand Admin_nr in de Administration-tabel:
CREATE VIEW STATE_VIEW AS
SELECT *
FROM
STATE
WHERE
ADMIN_ENTERED IN (SELECT
AND YEAR_ENTERED >= 1700
WITH CHECK OPTION
ADMIN_NR
FROM
ADMINISTRATION )
Gezien het karakter van subqueries, kan deze constructie níet toegepast worden bij verwijzing naar een
gecombineerde sleutel over twee of meer kolommen.
Alle overige beperkingsregels zullen nog steeds via bijvoorbeeld de combinatie van negatieve SELECT’s en de
commando’s ROLLBACK WORK versus COMMIT WORK bewaakt moeten worden.
Vergemakkelijkte integriteitsbewaking via SQL2-DDL
Een aantal extra’s voor wat betreft de integriteitsbewaking op database -niveau is bij SQL2-systemen mogelijk
door gebruik van de volgende DDL-constructies:
• Primary Key -constructies
• Foreign Key - constructies
• Check-instructies (al dan niet voor Domein-integriteitseisen)
N.B. In sommige RDBMS’s kan ook met alternate keys gewerkt (en dus: de data-integriteit bewaakt) worden.
We geven hier een aantal voorbeelden (refererend aan de presidentiële database):
CREATE TABLE PRESIDENT
(PRES_NAME
CHAR (16) NOT NULL,
BIRTH_YR
SMALLINT
NOT NULL,
YRS_SERV
SMALLINT
NOT NULL,
DEATH_AGE
SMALLINT
,
PARTY
CHAR (11) NOT NULL,
STATE_BORN CHAR (15)
,
PRIMARY KEY (PRES_NAME)
,
FOREIGN KEY (STATE_BORN) REFERENCES STATE (STATE_NAME)
ON DELETE NULLIFIES
ON UPDATE CASCADE )
CREATE TABLE PRES_MARRIAGE
(PRES_NAME
CHAR (16) NOT NULL,
SP_NAME
CHAR (16) NOT NULL,
PR_AGE
SMALLINT
,
SP_AGE
SMALLINT
,
NR_CHILD
SMALLINT
,
MAR_YEAR
SMALLINT
,
PRIMARY KEY (PRES_NAME, SP_NAME)
FOREIGN KEY (PRES_NAME) REFERENCES PRESIDENT (PRES_NAME)
ON DELETE CASCADE
ON UPDATE CASCADE )
CREATE TABLE ADMIN_PR_VP
(ADMIN_NR
SMALLINT
NOT NULL,
PRES_NAME
CHAR (16) NOT NULL,
VICE_PRES_NAME CHAR (16) NOT NULL,
PRIMARY KEY (ADMIN_NR, PRES_NAME, VICE_PRES_NAME),
FOREIGN KEY (ADMIN_NR, PRES_NAME)
REFERENCES ADMINISTRATION (ADMIN_NR, PRES_NAME)
ON DELETE RESTRICT
ON UPDATE CASCADE
)
Het gebruik van de primary key-regels in deze DLL-tabeldefinities behoeft waarschijnlijk geen nadere uitleg.
4
Collegedictaat IS0-cursus
Integriteits-bewakingsdeel
Anders ligt dat bij de foreign key-regels. We zien daar als toevoegingen opties (let op: het is niet verplicht ze te
gebruiken) verschijnen voor wat er moet gebeuren als uit de tabel waar naar verwezen wordt veranderingen (ON
DELETE / ON UPDATE) plaatsvinden in de waarden waar via die foreign key naar gerefereerd worden. We
zullen hiervan twee voorbeelden noemen:
• Wat moet er bijvoorbeeld gebeuren indien bij nader inzien blijkt dat de naam van president ‘Reagan R’ toch
beslist ‘Reagan RA’ zou moeten zijn? Als we die naamsverandering zomaar in de president -tabel zouden
doorvoeren, dan zou de deelverzamelingsregel (foreign key) uit de pres_marriage-tabel, waardoor vastgelegd
is dat een presidentsnaam uit die pres_marriage-tabel beslist óók moet voorkomen in de president-tabel, in de
problemen komen omdat in de pres_marriage-tabel nog steeds ‘Reagan R’ staat, maar in de president -tabel
‘Reagan RA’.
• Wat moet er gebeuren als besloten zou worden om (stel je een splitsing voor, waarbij de staat ‘Texas’ zich
zou afscheiden) de regel over ‘Texas’ uit de state -tabel te schrappen? De foreign key-verwijzing voor
‘State_born’ in de president -tabel verwijst immers naar een State_Name in de State-tabel.
Mogelijke nadere aanduidingen zijn hierbij: ‘Restrict’, ‘Cascade’ of ‘Nullifies’.
De acties die door het RDBMS daarbij moeten worden ondernomen zijn respectievelijk de volgende:
RESTRICT:
het veranderen van gegevens in de kolom waarnaar vanuit een andere tabel middels een foreign
key gerefereerd wordt (of het verwijderen van een hele tabelregel) mag alleen indien de
betreffende waarde in die andere tabel (of tabellen!) niet voorkomt. Komt die waarde daar wél
voor, dan moet de verandering door het RDBMS geweigerd worden. (soms: ‘Reject’)
CASCADE:
veranderingen in de tabel waarnaar vanuit een andere tabel middels een foreign key verwezen,
moeten in die andere tabel (of tabellen!) óók worden doorgevoerd. In ons eerste voorbeeld zou
dit betekenen, dat verandering in de naam ‘Reagan R’ in de president -tabel automatisch door
het RDBMS óók moeten worden doorgevoerd in de tabellen waarbij pres_name (een deel van)
een vreemde sleutel naar die president-tabel is. (cascade: ‘waterval’)
NULLIFIES:
indien in een kolom waarnaar vanuit een andere tabel middels een foreign key wordt verwezen,
een verandering plaats vindt, dan moet het betreffende veld (of velden) in die andere tabel
automatisch de waarde NULL krijgen. Uiteraard is dit alleen mogelijk indien bij het creëren
van die tabel het bewuste veld géén NOT NULL-optie heeft meegekregen. In ons tweede
voorbeeld zou dit betekenen, dat bij verwijderen van de staat ‘Texas’ uit de state -tabel
automatisch door het RDBMS op alle plaatsen in de president-tabel waar ‘Texas’ als
state_born staat, deze waarde wordt verwijderd (dus de ‘waarde’ NULL komt; dit mag omdat
in het CREATE TABLE-commando deze kolom géén NOT NULL heeft). (soms: ‘Set Null)’.
Domeinen en CHECK op domein-integriteitseisen
Er zijn inmiddels RDBMS’s op de markt verschenen, die middels CREATE -instructies ook ‘domeinen’
(verzamelingen van mogelijke waarden van een bepaald gegevenstype) inclusief de daarvoor geldende domeinintegriteitseisen kunnen creëren.
We passen dit als voorbeeld toe op een deel van de presidentiële database:
CREATE DOMAIN PERSON_NAME AS CHAR(16)
CREATE DOMAIN YEAR_NUMBER AS SMALLINT
CHECK (VALUE >= 1700)
CREATE DOMAIN NUMBER_OF_YEARS AS SMALLINT
CHECK (VALUE >= 0)
CREATE DOMAIN PARTY_NAME AS CHAR(11)
CREATE DOMAIN STATE_NAME AS CHAR (15)
CREATE DOMAIN HOBBY_NAME AS CHAR (20)
CREATE DOMAIN ADMIN_NUMBER AS SMALLINT
CHECK (VALUE > 0)
CREATE DOMAIN COUNT_NUMBER AS SMALLINT
CHECK (VALUE >= 0)
CREATE DOMAIN NUMBER_OF_VOTES AS SMALLINT
CHECK (VALUE >= 0)
CREATE DOMAIN W_L_INDIC AS CHAR(1)
CHECK (VALUE IN ('W', 'L'))
5
Collegedictaat IS0-cursus
CREATE TABLE
(PRES_NAME
BIRTH_YR
YRS_SERV
DEATH_AGE
PARTY
STATE_BORN
PRIMARY KEY
FOREIGN KEY
Integriteits-bewakingsdeel
PRESIDENT
PERSON_NAME
NOT NULL ,
YEAR_NUMBER
NOT NULL ,
NUMBER_OF_YEARS NOT NULL ,
NUMBER_OF_YEARS
,
PARTY_NAME
NOT NULL ,
STATE_NAME
,
(PRES_NAME)
,
(STATE_BORN) REFERENCES STATE (STATE_NAME)
CREATE TABLE PRES_HOBBY
(PRES_NAME
PERSON_NAME NOT NULL ,
HOBBY
HOBBY_NAME
NOT NULL ,
PRIMARY KEY (PRES_NAME, HOBBY)
,
FOREIGN KEY (PRES_NAME) REFERENCES PRESIDENT (PRES_NAME)
)
)
Door het gebruik van een domein-naam als type-aanduiding voor een kolom in een CREATE TABLEcommando is het met behulp van de domein-CHECK-optie mogelijk het RDBMS ervoor te laten zorgen, dat
alleen toegestane waarden in die kolom opgeslagen zullen worden.
CREATE DOMAIN YEAR_NUMBER AS SMALLINT , inclusief de domein-eis:
Vanwege de domein-definitie
CHECK (VALUE >= 1700) , zal het (gecontroleerd door het RDBMS) onmogelijk worden om zowel voor
bijvoorbeeld een geboortejaar van een president als voor een verkiezingsjaar als voor een jaar-van-toetreden etc.
een waarde die kleiner is dan 1701 op te slaan.
Andere CHECK-instructies
Sommige recente RDBMS-versies kennen aanvullende mogelijkheden voor het op database-niveau bewaken van
integriteitsen, door ook CHECK-componenten in de CREATE TABLE instructies op te nemen.
Ook daarvan geven we hier enkele voorbeelden:
Voor de kolom STATE (YEAR_ENTERED) geldt de bijzondere integriteitseis, dat alle waarden >= 1776
moeten zijn. Daatoe nemen we in de CREATE TABLE instructie voor de tabel STATE nog op:
CHECK (YEAR_ENTERED >= 1776)
Een nog niet genoemde bijzondere integriteitseis voor de kolom MAR_YEAR in de tabel PRES_MARRIAGE
kan door het RDBMS bewaakt worden via:
CHECK (MAR_YEAR >= (SELECT BIRTH_YEAR + 18
FROM
PRESIDENT P
WHERE PRES_NAME = P.PRES_NAME))
Ook kunnen we op deze wijze de níet-vreemde-sleutel-verwijzingen bewaken, door respectievelijk de volgende
CHECK-componenten op te nemen in de tabellen PRESIDENT, STATE en ADMINISTRATION:
CHECK (PRES_NAME IN (SELECT PRES_NAME FROM ADMINISTRATION))
CHECK (ADMIN_ENTERED IN (SELECT ADMIN_NR FROM ADMINISTRATION))
CHECK (ADMIN_NR IN (SELECT ADMIN_NR FROM ADMIN_PR_VP))
Eventueel kunnen zulke CHECK’s opgenomen worden in ALTER TABLE -instructies, voor het nadat de
tabellen waarnaar wordt verwezen zijn gecreëerd, achteraf aanpassen van deze tabel-definities.
Ondanks de verbeterde DLL-integriteitsbewaking onder SQL2 zal het (afhankelijk van de mogelijkheden van het
gebruikte RDBMS) meestal toch nodig zijn nog een aantal constraints via b.v. negatieve SELECT’s af te
dwingen.
We geven hier een aantal voorbeelden 1 van zulke ‘bijzondere integriteitseisen’ die zowel op intra- , maar
meestal vooral op inter-relationeel niveau zullen zitten:
1. In de kolom ADMINISTRATION (ADMIN_NR) moeten alle waarden vanaf 1 t/m de hoogste opgenomen
waarde allemaal aanwezig zijn (N.B. ‘>= 1’ wordt op een andere manier reeds bewaakt!):
1
Met dank aan Guido Bakema van de Hogeschool Arnhem Nijmegen
6
Collegedictaat IS0-cursus
SELECT
FROM
WHERE
AND
Integriteits-bewakingsdeel
*
ADMINISTRATION
ADMIN_NR < (SELECT MAX (ADMIN_NR)
FROM
ADMINISTRATION)
ADMIN_NR + 1 NOT IN (SELECT ADMIN_NR
FROM
ADMINISTRATION)
2. In de kolom STATE (ADMIN_ENTERED) moet dan en slechts dan een NULL-waarde staan als in dezelfde
rij in de kolom ADMINISTRATION (YEAR_ENTERED) de waarde 1776 staat:
SELECT
FROM
WHERE
OR
*
STATE
(ADMIN_ENTERED IS NULL AND YEAR_ENTERED <> 1776)
(ADMIN_ENTERED IS NOT NULL AND YEAR_ENTERED = 1776)
3. Bij alle rijen in de tabel ELECTION met eenzelfde waarde in de kolom ELECTION_YEAR moeten òf in de
kolom WINNER_LOSER_INDIC alleen NULL-waarden voorkomen òf er moet in precies één rij een ' W' en in
de andere rijen steeds een ' L' zijn ingevuld (N.B. ' of een ' W' of een ' L' ' wordt reeds bewaakt):
SELECT
FROM
WHERE
GROUP BY
HAVING
*
ELECTION
WINNER_LOSER_INDIC = 'W'
ELECTION_YEAR
COUNT (*) > 1
4. Tevens eisen we dat deze waarden ' W' of ' L' slechts mogen zijn ingevuld voor zeker verkiezings
jaar, als voor
dat verkiezingsjaar ook de uitslag in de kolom VOTES is vastgelegd:
SELECT
FROM
WHERE
*
ELECTION
VOTES IS NULL
AND
WINNER_LOSER_INDIC IS NOT NULL
5. In de kolom PRES_MARRIAGE (MAR_YEAR) ingevulde waarden moeten groter of gelijk zijn dan de in
de kolom PRESIDENT (BIRTH_YR) voor dezelfde president ingevulde waarde + 18:
SELECT
FROM
WHERE
*
PRES_MARRIAGE PM
MAR_YEAR < ( SELECT
FROM
WHERE
BIRTH_YR + 18
PRESIDENT
PRES_NAME = PM.PRES_NAME)
6. Een in de kolom PRES_MARRIAGE (PR_AGE) ingevulde waarde moet gelijk zijn aan of 1 meer zijn dan
het verschil van voor die president ingevulde waarden in de kolommen PRES_MARRIAGE (MAR_YEAR) en
PRESIDENT (BIRTH_YR):
SELECT
FROM
WHERE
OR
*
PRES_MARRIAGE PM
PR_AGE <>
(
PR_AGE + 1 <>
(
SELECT
FROM
WHERE
SELECT
FROM
WHERE
MAR_YEAR - BIRTH_YR
PRESIDENT P, PRES_MARRIAGE PM
P.PRES_NAME = PM.PRES_NAME)
MAR_YEAR - BIRTH_YR
PRESIDENT P, PRES_MARRIAGE PM
P.PRES_NAME = PM.PRES_NAME )
7. Van alle rijen in de tabel ADMINISTRATION met eenzelfde waarde in de kolom ADMIN_NR, moet
minstens die waardencombinatie in ADMINISTRATION (ADMIN_NR, PRES_NAME) waarvoor de waarde in
de kolom ADMINSTRATION (YEAR_INAUGURATED) minimaal is, ook voorkomen in de kolomcombinatie
ADMIN_PR_VP (ADMIN_NR, PRES_NAME):
SELECT
FROM
WHERE
AND
*
ADMINISTRATION A
YEAR_INAUGURATED = (SELECT
MIN ( YEAR_INAUGURATED )
FROM
ADMINISTRATION
WHERE
ADMIN_NR = A.ADMIN_NR)
NOT EXISTS (SELECT *
FROM ADMIN_PR_VP
WHERE ADMIN_NR = A.ADMIN_NR AND PRES_NAME = A.PRES_NAME)
En zo zijn/blijven er misschien nog wel meer te controleren integriteitsregels over.
7
Collegedictaat IS0-cursus
Integriteits-bewakingsdeel
Feitelijk kunnen we de datadefinitiemogelijkheden van een RDBMS karakteriseren door aan te geven welke van
voorgaande negatieve SELECT’s overb odig zijn omdat de betreffende integriteitsaspecten via CREATEinstructies kunnen worden bewaakt. Voor een RDBMS dat (o.a.) is uitgerust met definitiemogelijkheden voor
domeinen met domeinintegriteitseisen en voor tabelschema' s met níet
-leeg-eisen, primaire sleutels en vreemde
sleutels, blijven vrij weinig negatieve SELECT’s over. Hòe dergelijke negatieve SELECTs dan daadwerkelijk
worden geïmplementeerd, hetzij door ze in de in de applicaties op te nemen (embedded SQL), hetzij in de vorm
van stored procedures, hetzij door tools in te zetten, is een keuze die de gegevensbankbeheerder moet maken.
De kwaliteit van een database-managementsysteem hangt dus o.a. af van de mate waarin het RDBMS ons helpt
om de integriteit van de in de database opgeslagen gegevens te bewaken. Hoe meer ‘automatische’ constraint bewaking er mogelijk is, hoe minder kans op fouten er ook is doordat menselijke ontwerpers of programmeurs
vergeten in bepaalde situatie een speciale beperkingsregel in de gaten te houden en hoe efficiënter (lees:
goedkoper) het bouwproces van het informatiesysteem zal zijn.
We moeten er ons wél ter dege van bewust zijn, dat een fundamentele eis voor het correct ontwerpen en bouwen
van een informatiesysteem bovenal is, dat door een goede informatie-analyse alle samenhang tússen en
beperking(sregels) óp de gegevens naar boven is gekomen.
8