Tietokantaohjelmointi 2019 SQL DDL DML ja DCL osa

Tietokantaohjelmointi 2019 SQL DDL, DML ja DCL – osa 1 Kati Iltanen ja Marko Junkkari Kalvot perustuvat kirjoihin Elmasri, Navathe: Fundamentals of Database Systems, Silberschatz ja Korth, Sudarshan: Database System Concepts ja Postgre. SQL: n manuaaleihin. Näihin kalvoihin liittyvät kysymykset ja kommentit Katille: Kati. Iltanen@staff. uta. fi

TIKO: n SQL-osuus § Osaksi kertausta Tietokantojen perusteet -kurssilta. § Tarkoitus sekä syventää että laajentaa SQL: n tietämystä. § Käytössä oleva esimerkkitietokanta on pohjautuu Elmasrin ja Navathen Fundamentals of Database Systems -kirjassa käytettyyn esimerkkikantaan. § § § Sama kanta on käytössä TKP-kurssilla. Esimerkkikanta annetaan kurssin verkkosivuilla. SQL: ää käydään läpi myös myöhemmillä luentokerroilla. 2

Johdanto § SQL on relaatiotietokannan hallintaan ja käyttöön kehitetty korkean tason kieli. § SQL: n komennot voidaan luokitella kolmeen osaan: 1. tietokannan määrittely (data definition language, DDL) 2. tietokannan manipulointi (data manipulation language, DML) 3. tietokannan hallinta (data control language, DCL). § SQL: n taulu (table) – relaatiomallin relaatio § SQL-taulussa voi olla duplikaattirivejä. § SQL: n sarake (column) – relaatiomallin attribuutti 3

Tietokannan määrittely § SQL sisältää komennot tietokannan eri objektien määrittelyyn. Esim. § § § taulut kaaviot näkymät indeksit eheysrajoitukset 4

Kaaviot (schema) § Useat relaatiotietokantajärjestelmät ja SQL tukevat kaavioiden (schema) käyttöä. § Kaavio on kokoelma tauluja. § Tavallisesti käyttäjällä on oletuskaavio, jonka nimeä ei tarvitse mainita esimerkiksi taulujen luontilauseissa tai kyselyissä. § § Postgre. SQL: ssä on olemassa oletusarvoisesti public-niminen kaavio, joka on oletusarvoisesti oletuskaaviona. Jos halutaan viitata toisen kaavion tauluihin, voidaan käyttää pistenotaatiota: kaavio. taulu § Jos taulu on luotu ’yritys’-nimiseen kaavioon, niin tyontekija-taulun sukunimi-sarakkeeseen viitataan merkinnällä yritys. tyontekija. sukunimi 5

Kaavion luonti: CREATE SCHEMA § Kaavioita luodaan CREATE SCHEMA kaavionimi -komennolla. § Olemassa oleva kaavio voidaan asettaa oletuskaavioksi SET SEARCH_PATH TO -komennolla. § Tämän jälkeen kaavion nimeä ei enää tarvitse erikseen mainita siihen viitattaessa. § § Huom: Oletuskaavion asetus vaihtelee tietokantajärjestelmästä riippuen Alla oleva esimerkki luo ’yritys’-nimisen kaavio ja asettaa sen oletuskaavioksi. CREATE SCHEMA yritys; SET SEARCH_PATH TO yritys; § SHOW SEARCH_PATH -komento näyttää hakupolun. 6

Kaavion poisto: DROP SCHEMA § Kaavio poistetaan DROP SCHEMA -komennolla: DROP SCHEMA yritys; § Taulut on poistettava ensin DROP SCHEMA yritys CASCADE; § Poistaa kaavion tauluineen 7

Taulun luominen § Taulu luodaan CREATE TABLE -lauseella, joka määrittelee § § § taulun nimen taulun sarakkeiden nimet minkä tyyppistä tietoa taulun sarakkeisiin voidaan tallettaa (yleensä) pääavaimen mahdollisesti avaimia, vierasavaimia ja muita rajoitteita mahdollisia oletusarvoja 8

Taulun luominen: Rajoitteita § Pääavain määritellään PRIMARY KEY -avainsanoilla. § § Avain määritellään UNIQUE-avainsanalla. § § yksilöivä sarake tai sarakeyhdistelmä Viiteavain määritellään FOREIGN KEY -avainsanoilla § § yksilöivä sarake tai sarakeyhdistelmä, jonka kaikki arvot ovat jonkin taulun pääavaimen tai avaimen arvoja tai tyhjäarvoja Tyhjäarvot (NULL-arvot) kielletään NOT NULL -määreellä. 9

Taulun luominen CREATE TABLE tyontekija ( ttnro INT, etunimi VARCHAR(15) NOT NULL, sukunimi VARCHAR(20) NOT NULL, saika DATE NOT NULL, kotikunta VARCHAR(20) NOT NULL, palkka NUMERIC(8, 2), puhelin VARCHAR(15), osastonro INT NOT NULL, esimiesnro INT, PRIMARY KEY (ttnro), FOREIGN KEY (osastonro) REFERENCES osasto, FOREIGN KEY (esimiesnro) REFERENCES tyontekija); CREATE TABLE osasto ( onro INT, onimi VARCHAR(15) NOT NULL, johtajanro INT NOT NULL, aloituspvm DATE, PRIMARY KEY (onro), UNIQUE (onimi), FOREIGN KEY(johtajanro) REFERENCES tyontekija); 10

Taulun luominen: Rajoitteita § CHECK-määreellä voidaan asettaa rajoitteita sarakkeeseen tallennettaville arvoille. § Lisäys- tai muutosoperaatiossa annettavien arvojen on tuotettava totuusarvo tosi tai tuntematon, jotta operaatio suoritetaan. CREATE TABLE tyontekija ( ttnro INT CHECK (ttnro > 0), etunimi VARCHAR(15) NOT NULL CHECK(etunimi <> ''), sukunimi VARCHAR(20) NOT NULL CHECK(sukunimi <> ''), saika DATE NOT NULL, kotikunta VARCHAR(20) NOT NULL, palkka NUMERIC(8, 2), puhelin VARCHAR(15), osastonro INT NOT NULL, esimiesnro INT, PRIMARY KEY (ttnro), FOREIGN KEY (osastonro) REFERENCES osasto, FOREIGN KEY (esimiesnro) REFERENCES tyontekija); 11

Taulun luominen: Rajoitteita § CHECK-määre voidaan sijoittaa luontilauseessa sarakemäärittelyiden jälkeen. § Tällöin CHECK-määreessä voidaan viitata ko. taulun useisiin sarakkeisiin. CREATE TABLE osasto ( onro INT, onimi VARCHAR(15) NOT NULL, johtajanro INT NOT NULL, aloituspvm DATE DEFAULT '2018 -01 -15', PRIMARY KEY (onro), UNIQUE (onimi), FOREIGN KEY(johtajanro) REFERENCES tyontekija, CHECK (onro > 0 AND onimi <> '')); 12

Taulun luominen: Oletusarvo § Sarakkeen määrittelyn yhteydessä voidaan antaa sarakkeelle oletusarvo DEFAULT-avainsanalla. § Oletusarvoa käytetään lisäysoperaatiossa, jossa sarakkeelle ei ole annettu arvoa. CREATE TABLE osasto ( onro INT, onimi VARCHAR(15) NOT NULL, johtajanro INT NOT NULL, aloituspvm DATE DEFAULT '2018 -10 -15', PRIMARY KEY (onro), UNIQUE (onimi), FOREIGN KEY(johtajanro) REFERENCES tyontekija, CHECK (onro > 0 AND onimi <> '')); 13

Taulun luominen § Taulun rakenteen voi tarkistaa Postgre. SQL-komennolla d taulunnimi. § d tyontekija 14

Taulun poistaminen § Taulun voi poistaa DROP TABLE -lauseella. § poistaa taulun määrittelyn ja varsinaisen tietosisällön DROP TABLE taulu; DROP TABLE tyontekija; DROP TABLE taulu CASCADE; - Poistaa taulun, vaikka taulu olisi viittausten kohteena 15

Taulun rakenteen muuttaminen § Taulun rakennetta voi muuttaa ALTER TABLE -lauseella. § § lisätä tai poistaa sarakkeita vaihtaa sarakkeiden nimiä ja tietotyyppejä muuttaa taulun rajoitteita Mahdollisuudet muuttaa taulun rakennetta vaihtelevat tietokannanhallintajärjestelmittäin. § Postgre. SQL on joustava muutosten suhteen. Esim. : Sarakkeen lisäys Sarakkeen poisto ALTER TABLE taulu ADD sarake tietotyyppi ALTER TABLE taulu DROP sarake ALTER TABLE tyontekija ADD lempivari VARCHAR(20); ALTER TABLE tyontekija DROP lempivari; 16

Taulun rakenteen muuttaminen Viiteavaimen määrittely Viiteavainmäärittelyn poisto ALTER TABLE tyontekija ADD FOREIGN KEY (osastonro) REFERENCES osasto; ALTER TABLE tyontekija DROP CONSTRAINT tyontekija_osastonro_fkey; Table "tkp. tyontekija" Column | Type | Modifiers ------+------------+-----ttnro | integer | not null etunimi | character varying(15) | not null sukunimi | character varying(20) | not null saika | date | not null kotikunta | character varying(20) | not null palkka | numeric(8, 2) | puhelin | character varying(15) | osastonro | integer | not null esimiesnro | integer | Indexes: "tyontekija_pkey" PRIMARY KEY, btree (ttnro) Foreign-key constraints: "tyontekija_esimiesnro_fkey" FOREIGN KEY (esimiesnro) REFERENCES tyontekija(ttnro) "tyontekija_osastonro_fkey" FOREIGN KEY (osastonro) REFERENCES osasto(onro) Referenced by: TABLE "huollettava" CONSTRAINT "huollettava_huoltajanro_fkey" FOREIGN KEY (huoltajanro) REFERENCES tyontekija(ttnro) TABLE "osallistuu" CONSTRAINT "osallistuu_ttnro_fkey" FOREIGN KEY (ttnro) REFERENCES tyontekija(ttnro) TABLE "osasto" CONSTRAINT "osasto_johtajanro_fkey" FOREIGN KEY (johtajanro) REFERENCES tyontekija(ttnro) TABLE "tyontekija" CONSTRAINT "tyontekija_esimiesnro_fkey" FOREIGN KEY (esimiesnro) REFERENCES tyontekija(ttnro) 17

Rivien lisääminen § Rivejä (varsinaisia tietoja, dataa) lisätään tauluun INSERTlauseella. INSERT INTO tyontekija VALUES (12, 'Pekka', 'Puro', '1985 -01 -09', 'Tampere', 3000. 00, NULL, 5, 33); INSERT INTO tyontekija(ttnro, etunimi, sukunimi, saika, kotikunta, palkka, osastonro, esimiesnro) VALUES (12, 'Pekka', 'Puro', '1985 -01 -09', 'Tampere', 3000. 00, 5, 33); § Yhdellä INSERT INTO -lauseella voidaan lisätä useita rivejä INSERT INTO tyontekija VALUES (88, 'Jukka', 'Susi', '1957 -11 -10', 'Tampere', 5500. 00, '444 1234’, 1, NULL ), (12, 'Pekka', 'Puro', '1985 -01 -09', 'Tampere', 3000. 00, NULL, 5, 33); 18

Rivien muuttaminen § Rivejä muutetaan UPDATE-lauseella. § Muutettavat rivit rajataan WHERE-osan ehdolla. UPDATE tyontekija SET sukunimi = 'Myrsky', kotikunta = 'Orivesi' WHERE ttnro = 99; UPDATE tyontekija SET palkka = 4050 WHERE palkka = 4000. 50; 19

Rivien poistaminen § Rivien poistaminen tapahtuu DELETE-lauseella. § Poistettavat rivit rajataan WHERE-osan ehdolla. DELETE FROM tyontekija; DELETE FROM tyontekija WHERE ttnro > 20; 20

Viite-eheys: Postgre. SQL: n toimintasäännöt § Toimintasäännöt Postgre. SQL: ssä § NO ACTION § § § RESTRICT § § Muutokset ja poistot vyörytetään viitanneisiin riveihin. SET NULL § § Jos viitteen kohde katoaa, operaatio estetään. CASCADE § § oletussääntö Jos viitteen kohde katoaa, operaatio estetään. Jos viitteen kohde katoaa, asetetaan viitannut arvo tyhjäarvoksi (viiteavainsarakkeen arvoksi tyhjäarvo). SET DEFAULT § Jos viitteen kohde katoaa, asetetaan viitannut arvo oletusarvoksi (viiteavainsarakkeen arvoksi oletusarvo). 21

Viite-eheys: Postgre. SQL: n toimintasäännöt NO ACTION CREATE TABLE tyontekija ( §Jos viittauksen kohde katoaisi ttnro INT, operaation seurauksena, etunimi VARCHAR(15) NOT NULL, operaatio estetään (ei siis tehdä sukunimi VARCHAR(20) NOT NULL, muutosta tai poistoa) saika DATE NOT NULL, kotikunta VARCHAR(20) NOT NULL, palkka NUMERIC(8, 2), puhelin VARCHAR(15), osastonro INT NOT NULL, esimiesnro INT, PRIMARY KEY (ttnro), FOREIGN KEY (osastonro) REFERENCES osasto(onro), FOREIGN KEY (esimiesnro) REFERENCES tyontekija(ttnro)); Kun viiteavain määritellään, on NO ACTION -toimintasääntö oletusarvoisesti voimassa muutos- ja poisto-operaatioille. 22

Viite-eheys: Postgre. SQL: n toimintasäännöt NO ACTION Table "public. tyontekija" Column | Type | Modifiers ------+------------+-----ttnro | integer | not null etunimi | character varying(15) | not null sukunimi | character varying(20) | not null saika | date | not null kotikunta | character varying(20) | not null palkka | numeric(8, 2) | puhelin | character varying(15) | osastonro | integer | not null esimiesnro | integer | Indexes: "tyontekija_pkey" PRIMARY KEY, btree (ttnro) Foreign-key constraints: "tyontekija_esimiesnro_fkey" FOREIGN KEY (esimiesnro) REFERENCES tyontekija(ttnro) "tyontekija_osastonro_fkey" FOREIGN KEY (osastonro) REFERENCES osasto(onro) Referenced by: TABLE "osallistuu" CONSTRAINT "osallistuu_ttnro_fkey" FOREIGN KEY (ttnro) REFERENCES tyontekija(ttnro) TABLE "tyontekija" CONSTRAINT "tyontekija_esimiesnro_fkey" FOREIGN KEY (esimiesnro) REFERENCES tyontekija(ttnro) 23

Viite-eheys: Postgre. SQL: n toimintasäännöt Ao. lauseiden suoritus ei onnistu, seurauksena virheilmoitus NO ACTION § UPDATE osasto SET onro = 500 WHERE onro = 5; Viittauksen kohteena olevaa DELETE FROM osasto WHERE onro = 5; arvoa ei voi muuttaa eikä riviä poistaa § § osasto onro tyontekija onimi ttnro etunimi … osastonro esimiesnro 1 Pääkonttori 88 Jukka 1 4 Hallinto 33 Ville 5 88 5 Tutkimus 12 Pekka 5 33 98 Jenni 4 88 99 Alli 4 98 Oletetaan, että tietokannassa on vain osasto- ja tyontekija-taulut. 24

Viite-eheys: Postgre. SQL: n toimintasäännöt NO ACTION (Oletetaan, että tietokannassa on vain osasto- ja tyontekija-taulut. ) tyontekija ttnro etunimi … osastonro esimiesnro 88 Jukka 1 33 Ville 5 88 12 Pekka 5 33 98 Jenni 4 88 99 Alli 4 98 UPDATE tyontekija SET ttnro = 120 WHERE ttnro = 12; ttnro etunimi … DELETE FROM tyontekija WHERE ttnro = 99; osastonro esimiesnro 88 Jukka 1 33 Ville 5 88 120 Pekka 5 33 98 Jenni 4 88 Työntekijänumerot 12 ja 99 eivät ole viittauksen kohteena, joten arvon 12 päivittäminen arvoksi 120 sekä ttnro 99 rivin poistaminen onnistuvat. 25

Viite-eheys: Postgre. SQL: n toimintasäännöt CASCADE CREATE TABLE tyontekija ( ttnro INT, etunimi VARCHAR(15) NOT NULL, sukunimi VARCHAR(20) NOT NULL, saika DATE NOT NULL, kotikunta VARCHAR(20) NOT NULL, palkka NUMERIC(8, 2), puhelin VARCHAR(15), osastonro INT NOT NULL, esimiesnro INT, PRIMARY KEY (ttnro), FOREIGN KEY(osastonro) REFERENCES osasto(onro) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (esimiesnro) REFERENCES tyontekija(ttnro) ON DELETE CASCADE ON UPDATE CASCADE); Oletetaan, että tietokannassa on vain osasto- ja tyontekija-taulut. 26

Viite-eheys: Postgre. SQL: n toimintasäännöt CASCADE Table “public. tyontekija" Column | Type | Modifiers ------+------------+-----ttnro | integer | not null etunimi | character varying(15) | not null sukunimi | character varying(20) | not null saika | date | not null kotikunta | character varying(20) | not null palkka | numeric(8, 2) | puhelin | character varying(15) | osastonro | integer | not null esimiesnro | integer | Indexes: "tyontekija_pkey" PRIMARY KEY, btree (ttnro) Foreign-key constraints: "tyontekija_esimiesnro_fkey" FOREIGN KEY (esimiesnro) REFERENCES tyontekija(ttnro) ON UPDATE CASCADE ON DELETE CASCADE "tyontekija_osastonro_fkey" FOREIGN KEY (osastonro) REFERENCES osasto(onro) ON UPDATE CASCADE ON DELETE CASCADE Referenced by: TABLE "tyontekija" CONSTRAINT "tyontekija_esimiesnro_fkey" FOREIGN KEY (esimiesnro) REFERENCES tyontekija(ttnro) ON UPDATE CASCADE ON DELETE CASCADE 27

Viite-eheys: Postgre. SQL: n toimintasäännöt ON UPDATE CASCADE Kun viittauksen kohteena olevaa arvoa muutetaan, tehdään vastaava muutos päivitettyyn riviin viitanneisiin riveihin. osasto tyontekija § onro onimi ttnro etunimi … osastonro esimiesnro 1 Pääkonttori 88 Jukka 1 4 Hallinto 33 Ville 5 88 5 Tutkimus 12 Pekka 5 33 98 Jenni 4 88 99 Alli 4 98 UPDATE osasto SET onro = 500 WHERE onro = 5; Taulut UPDATE-lauseen suorituksen jälkeen onro onimi ttnro etunimi … osastonro esimiesnro 1 Pääkonttori 88 Jukka 1 4 Hallinto 33 Ville 500 88 500 Tutkimus 12 Pekka 500 33 98 Jenni 4 88 99 Alli 4 98 28

Viite-eheys: Postgre. SQL: n toimintasäännöt ON DELETE CASCADE § Kun viittauksen kohteena oleva rivi poistetaan, poistetaan myös siihen viitanneet rivit. osasto onro tyontekija onimi ttnro etunimi … osastonro esimiesnro 1 Pääkonttori 88 Jukka 1 4 Hallinto 33 Ville 5 88 5 Tutkimus 12 Pekka 5 33 98 Jenni 4 88 99 Alli 4 98 DELETE FROM osasto WHERE onro = 5; Taulut DELETE-lauseen suorituksen jälkeen onro onimi ttnro etunimi … osastonro esimiesnro 1 Pääkonttori 88 Jukka 1 4 Hallinto 98 Jenni 4 88 99 Alli 4 98 29

Näkymät § Joskus tiettyihin tauluihin kohdistuu paljon samoja kyselyitä. § Kyselyn määrittely voi olla joskus työlästä, vaikeaa ja virhealtista. § Kysely voidaan määritellä näkymäksi, jolloin jatkossa voidaan käyttää näkymän nimeä kyselyn sijasta. § Näkymät (views) ovat virtuaalisia tauluja. § § § eivät itse (välttämättä) varastoi tietoa esittävät perustauluista tai aiemmin määritellyistä näkymistä johdettua tietoa Perustaulut (base tables) § varsinaisia tietokannan tauluja, joihin tieto on fyysisesti tallennettu 30

Näkymät § Näkymät käyttäytyvät kyselyissä samoin kuin varsinaiset perustaulutkin. § Näkymien päivittäminen on ongelmallisempaa: § aina ei voida yksikäsitteisesti päätellä, miten näkymään kohdistettu päivitys pitäisi kohdistaa perustauluihin. 31

Näkymät § Näkymiä luodaan CREATE VIEW –komennolla. CREATE VIEW <näkymänimi> AS <kysely> § § missä kysely on normaali SQL-kysely Luodaan näkymä, joka sisältää työntekijöiden työntekijänumerot, etu- ja sukunimet, projektien nimet ja projekteissa tehtävien tuntien määrät. CREATE VIEW tunnit_projekteissa AS SELECT tyontekija. ttnro, etunimi, sukunimi, pnimi, tunnit FROM tyontekija, osallistuu, projekti WHERE tyontekija. ttnro = osallistuu. ttnro AND osallistuu. pnro = projekti. pnro; 32

Näkymät § Näkymän nimeä voidaan käyttää kyselyissä samalla tavoin kuin taulun nimeä. SELECT * FROM tunnit_projekteissa; ttnro | etunimi | sukunimi | pnimi | tunnit -------+----------+-------12 | Pekka | Puro | Tuote X | 32. 5 12 | Pekka | Puro | Tuote Y | 7. 5 33 | Ville | Viima | Tuote Y | 10. 0 33 | Ville | Viima | Tuote Z | 10. 0 99 | Alli | Kivi | Tuote Z | 30. 0 99 | Alli | Kivi | Tuote X | 10. 0 98 | Jenni | Joki | Tuote Y | 15. 0 33 | Ville | Viima | Uudet edut | 1. 0 33 | Ville | Viima | TYKY-liikunta | 2. 0 98 | Jenni | Joki | Uudet edut | 4. 0 98 | Jenni | Joki | TYKY-liikunta | 6. 0 33

Näkymät § Näkymän nimeä voidaan käyttää kyselyissä samalla tavoin kuin taulun nimeä. SELECT DISTINCT etunimi, sukunimi, ttnro FROM tunnit_projekteissa WHERE pnimi LIKE 'Tuote%'; etunimi | sukunimi | ttnro ---------+------Alli | Kivi | 99 Jenni | Joki | 98 Pekka | Puro | 12 Ville | Viima | 33 34

Näkymät § Määritellään toinenkin näkymä CREATE VIEW osasto_info(osasto, tt_lkm, palkat_yht) AS SELECT onimi, COUNT(*), SUM(palkka) FROM osasto, tyontekija WHERE onro = osastonro GROUP BY onimi; SELECT * FROM osasto_info; osasto | tt_lkm | palkat_yht -------+-----------Hallinto | 2 | 6800. 00 Tutkimus | 2 | 7000. 50 Pääkonttori | 1 | 5500. 00 35

Näkymät § Tietokannanhallintajärjestelmä huolehtii, että näkymät ovat ajan tasalla. § § Perustauluihin tehdyt päivitykset heijastuvat siis näkymiin. Näkymiä voidaan poistaa DROP VIEW -komennolla. DROP VIEW näkymänimi DROP VIEW tunnit_projekteissa; § Näkymän poistaminen ei koskaan poista tietoa perustauluista, joista näkymä on luotu. 36

Näkymät § Näkymien päivittäminen voi olla ongelmallista. § Aina ei voida yksikäsitteisesti päätellä, miten näkymään kohdistettu päivitys pitäisi kohdistaa perustauluihin. § Yhteen perustauluun perustuvaa näkymää voidaan yleensä päivittää, jos näkymän sarakkeet sisältävät § § § tarvittavat avaimet muun perustaulussa vaaditun tiedon Jotta näkymä olisi päivitettävissä, on näkymän määrittelyn loppuun lisättävä WITH CHECK OPTION -määre. § WITH CHECK OPTION -määrettä ei ole toteutettu kaikissa tietokannanhallintajärjestelmissä tai versioissa. 37

Näkymät § Näkymät, jotka perustuvat useisiin tauluihin ja joissa käytetään liitosehtoja, eivät ole yleensä päivitettävissä. § Näkymät, joissa käytetään ryhmittely- ja aggregointifunktioita eivät ole päivitettävissä. § Tietokannanhallintajärjestelmä ei välttämättä tue minkäänlaisia päivityksiä näkymien kautta. § Asiasta kiinnostuneille löytyy lisätietoa kirjasta Elmasri, Navathe: Fundamentals of Database Systems § § § SQL – The Relational Database Standard Views View Implementation and View Update 38

Näkymät § Kyselyjen helpottamisen lisäksi näkymiä käytetään § § lukuoikeuksien antamisessa tietoriippumattomuuden toteuttamiseksi § § Sovellusohjelmaa ei tarvitse muuttaa, vaikka tietokantaan tehtäisiin laajojakin muutoksia. Sovellusohjelmat käyttävät näkymiä eivätkä ole tällöin riippuvaisia perustauluista. 39

Metadata § Tietokannanhallintajärjestelmä pitää kirjaa kaikista tauluista, sarakkeista ym. tietokantaobjekteista. § Tämä metadata on tallennettu tauluihin, joiden kokoelmaa kutsutaan systeemihakemistoksi (system catalog). § Tauluihin voidaan tehdä kyselyjä normaaliin tapaan. § SQL: n standardin INFORMATION SCHEMAssa on määritelty, mitä tietoja pitäisi tallentaa mihinkäkin systeemitauluun. § Kaikki järjestelmät eivät kuitenkaan noudata tätä määrittelyä ja eri järjestelmillä on omia menetelmiään metatiedon selville saamiseksi. 40

Metadata § Postgre. SQL: ssä metadata on information_schema-nimisessä kaaviossa. § Postgre. SQL: ssä metadataa voi tarkastella § psql: n komennoilla, esim. § § d näyttää kaikki kaavion taulut d taulunnimi näyttää taulun metadataa dn näyttää kaaviot näkymiin kohdistuvilla kyselyillä, esim. § § information_schemata; -- kaaviot information_schema. tables; -- taulut information_schema. table_constraints; -- rajoitteet information_schema. referential_constraints; -- viiteavainrajoitteet 41

Metadata Joitakin schemata-näkymän sarakkeita ja rivejä schema_name | schema_owner ----------+-------information_schema | postgres pg_catalog | postgres public | postgres exam | tkp h 2 | tkp h 3 | tkp h 4 | tkp l 3 | tkp tentti 1 | tkp tentti 2 | tkp tentti 3 | tkp tentti 4 | tkp Joitakin tables-näkymän sarakkeita ja rivejä table_schema | table_name | table_type ----------+--------------------+------tiko 17 | tilaus | BASE TABLE tiko 17 | tilaustuote | BASE TABLE tiko 17 | tuote_era | BASE TABLE tiko 17 | tyontekija | BASE TABLE tkp 17 h 3 | kategoria | BASE TABLE tkp 17 h 3 | planeetta | BASE TABLE tkp 17 h 3 | tuote_kategoria | BASE TABLE tkp 17 h 3 | tuotemerkki | BASE TABLE 42

Metadata Joitakin referential_constraints-näkymän sarakkeita ja rivejä constraint_schema | constraint_name | unique_constraint_schema | unique_constraint_name -------------------+---------------------+------------l 3 | osallistuu_pnro_fkey | l 3 | projekti_pkey l 3 | osallistuu_ttnro_fkey | l 3 | tyontekija_pkey l 3 | tyontekija_esimiesnro_fkey | l 3 | tyontekija_pkey l 3 | tyontekija_osastonro_fkey | l 3 | osasto_pkey 43