Tietokannan rakenteen määritys (DDL)

Video on koostettu Toni Taipaluksen kevään 2023 luentonauhoitteista. Videolla mainitut viittaukset demoihin tai tenttiin eivät pidä paikkaansa keväällä 2024. Ajankohtaiset tiedot kurssin suorittamisesta löytyvät etusivulta.

Luku tekstinä

# luku-4.4

4.4 DDL

Tietokannan rakenteen määrittäminen (Data Definition Language, DDL; kutsutaan joskus myös nimellä Schema Manipulation Language, SML) tapahtuu SQL:ssä mm. komennoilla CREATE, ALTER ja DROP. Tarkastellaan ennen rakenteen määrittämistä tietokantaympäristön yleistä rakennetta.

Kuvio 4.3: Tietokantaympäristön yleinen rakenne.
Kuvio 4.3: Tietokantaympäristön yleinen rakenne.

Kuviossa esiintyvissä käsitteissä on suuriakin tuotekohtaisia eroja. Tietokannanhallintajärjestelmän palvelinprosessiperhe (jotkin tuotteet käyttävät tästä nimeä tietokantainstanssi) on käynnissä laitteella. Tuotteesta riippuen palvelinprosesseja voi olla käynnissä useita.

Palvelinprosessi pitää yllä katalogeja, jotka ovat laitteen massamuistissa. Palvelinprosessi puolestaan sijaitsee keskusmuistissa. Palvelinprosessin lopettaminen ei poista massamuistissa sijaitsevaa dataa.

Katalogit sisältävät ns. skeemoja, jotka ovat kokoelmia erilaisia tietokantaobjekteja kuten tauluja, näkymiä, triggereitä ja proseduureja. Skeeman voi käsittää nimiavaruudeksi. Jokaisessa katalogissa on erityinen, automaattisesti luotu skeema, jonka avulla tietokannanhallintajärjestelmä pitää yllä metadataa katalogin kaikkien skeemojen kaikkien tietokantaobjektien rakenteesta. SQL-standardin mukaan metadatan sisältävät järjestelmätaulut ovat skeemassa INFORMATION_SCHEMA.

Olet kokeillut SQLite-tietokannanhallintajärjestelmän hallintakomentoa .tables alaluvun 4.3 alussa. Hallintakomento on itse asiassa alias järjestelmätaulua koskevalle SELECT-lauseelle:

SELECT tbl_name 
FROM maindotsql=ite_master 
WHERE type LIKE 'table';

4.4.1 Tietotyypit

SQL-standardi määrittää sarakkeille erilaisia tietotyyppivaihtoehtoja. SQL-kieli on vahvasti tyypittävä, mikä tarkoittaa, että jokaiselle taulun sarakkeelle on määritettävä tietotyyppi kun taulu luodaan. Yleiset, standardin mukaisia tietotyyppejä on lueteltu alla olevassa taulukossa.

Tietotyyppi Selite Käyttöesimerkki Esimerkkisarake
CHAR, CHARACTER Määrätyn mittainen merkkijono CHAR(10) henkilötunnus
VARCHAR, CHARACTER VARYING Vaihtelevan mittainen merkkijono VARCHAR(20) etunimi
INTEGER, INT kokonaisluku INT syntymävuosi
NUMERIC (desimaali)luku NUMERIC(7,2) kuukausipalkka
BOOLEAN totuusarvo BOOLEAN tilaus_vahvistettu
DATE päivämäärä DATE tilauspvm
TIMESTAMP aikaleima TIMESTAMP maksuaika
ARRAY lista INTEGER[7] lottonumerot
TEXT tekstimuotoinen sarake TEXT arvostelu

Mainittujen tietotyyppien lisäksi eri tuotteissa on lisäksi muita tietotyyppejä, kuten tietotyypit suuren binääridatan kuten teksti, kuvat, audio ja video tallentamiseen sekä tietotyypit paikkatiedolle.

4.4.2 Taulujen hallinta

Tauluja hallitaan komennoilla CREATE, ALTER ja DROP. Ennen komentojen syntaksiin tutustumista muistellaan luvussa 2 käsiteltyjä, relaatioihin liittyviä termejä, mutta tällä kertaa SQL:n näkökulmasta:

  • Taululla on otsake (header). Otsake sisältää sarakkeiden nimet niiden luontijärjestyksessä.
  • Taululla on asteluku (degree). Asteluku kertoo sarakkeiden määrän.
  • Taululla on kardinaalisuus. Taulun kardinaalisuus on taulun rivien lukumäärä.
  • Sarakkeella on kardinaalisuus. Sarakkeen kardinaalisuus on sen sisältämien erilaisten arvojen lukumäärä.
astun asnimi kaup tyyppi mpiiri
a123 Virtanen Mikkeli y i
a125 Kojo & Lipas Kouvola y i
a134 Liekki Tampere y l
# monitaulut

4.4.2.1 Taulujen luominen

Taulu luodaan komennolla CREATE TABLE, ja sen yleinen syntaksi on seuraava.

CREATE TABLE taulu (
	sarake tietotyyppi[, 
	sarake tietotyyppi]*
);

Esimerkiksi harjoitustietokannan tuote-taulu voitaisiin luoda seuraavalla SQL-lauseella.

# sqlHelp_sqlite_ddl1

Tehtävä: muokkaa yllä olevaa SQL-lausetta niin, että se luo taulun nimeltä osasto. Taulussa on sarakkeet osastotunnus, osoite, pinta-ala (kahden desimaalin tarkkuudella) ja onko_toiminnassa, joka on BOOLEAN-tietotyyppiä. Lisää tauluun rivejä.

4.4.2.1.1 Perusavain

Perusavain (primary key, PK) on sarake tai sarakejoukko joka yksilöi taulun rivit, toisin sanoen perusavaimen ansiosta jokainen taulun rivi on erilainen. Taululle voidaan määrittää perusavain joko taulun luontikomennolla tai myöhemmin muokkaamalla taulua. Taulun luontikomennolla perusavaimen lisääminen muuttaa syntaksia seuraavalla tavalla:

CREATE TABLE taulu (
	sarake tietotyyppi [PRIMARY KEY]?[, 
	sarake tietotyyppi]*
);

Esimerkiksi

CREATE TABLE tuote (
	tuotetun CHAR(5) PRIMARY KEY,
	tuotenimi VARCHAR(20)
);

Kuten relaatiolla, myös taululla voi olla vain yksi perusavain. Jos perusavain koostuu useammasta kuin yhdestä sarakkeesta, se täytyy luoda sarakkeiden määrityksen jälkeen:

CREATE TABLE taulu (
	sarake tietotyyppi[, 
	sarake tietotyyppi]*[,
	PRIMARY KEY (sarake[, sarake]*)]?
);

Esimerkiksi

CREATE TABLE tilaus (
	tuotetun CHAR(5),
	astun CHAR(5),
	maara INT,
	PRIMARY KEY (tuotetun, astun)
);

Perusavaimen valintaa käsitellään tarkemmin luvussa 5.

4.4.2.1.2 Viiteavain

Viiteavain (foreign key, FK) on perusavaimen ohella tärkein relaatiotietokannan eheyspiirre. Viiteavain on sarake tai sarakejoukko, joka yksilöi rivin toisessa taulussa. Tavallisesti viiteavain viittaa toisen taulun perusavaimeen tai sen osaan. Sitä taulua, jonka perusavaimeen viiteavain viittaa, sanotaan taulujen välisen suhteen isäntätauluksi ja viittaavaa taulua renkitauluksi. Viiteavain rajoittaa viittaavan taulun sarakkeen arvojoukon isäntätaulun viitatun sarakkeen arvoihin. Viiteavainmääritys ei ole pakollinen, jotta taulujen välille voidaan muodostaa liitos SQL-lauseella.

Kuvio 4.4: Viite- ja perusavaimet kolmessa taulussa.
Kuvio 4.4: Viite- ja perusavaimet kolmessa taulussa.

Viitatun ja viittaavan sarakkeen tulee olla samaa tietotyyppiä. SQL-kielessä viiteavaimet määritetään joko taulun luonnin yhteydessä CREATE TABLE-käskyllä tai muokkaamalla jo olemassa olevaa taulua. Yleinen syntaksi on seuraava.

CREATE TABLE taulu (
	sarake tietotyyppi[, 
	sarake tietotyyppi]*[,
	PRIMARY KEY (sarake[, sarake]*)]?[,
	FOREIGN KEY (sarake[, sarake]*) REFERENCES isäntätaulu (sarake[, sarake]*)
	  [ON UPDATE [RESTRICT|SET NULL|CASCADE|SET DEFAULT]]?
	  [ON DELETE [RESTRICT|SET NULL|CASCADE|SET DEFAULT]]?
	]*
);

Jossa siis CREATE TABLE-käskyllä luotava taulu tulee olemaan suhteen renkitaulu. FOREIGN KEY-lisämäärettä seuraa sulkujen sisällä pilkkulista, jossa luetellaan renkitaulun viittaavat sarakkeet, esimerkiksi:

CREATE TABLE tilausrivi (
	asiakastunnus CHAR(10),
	tuotetunnus CHAR(10),
	maara INT,
	PRIMARY KEY (asiakastunnus, tuotetunnus),
	FOREIGN KEY (asiakastunnus) REFERENCES asiakas (asiakastunnus)
	  ON UPDATE RESTRICT
	  ON DELETE SET NULL,
	FOREIGN KEY (tuotetunnus) REFERENCES tuote (tuotetunnus)
	  ON UPDATE RESTRICT
	  ON DELETE SET DEFAULT
);

Yllä olevat lisämääreet kertovat tietokannanhallintajärjestelmälle, miten toimitaan kun isäntätaulun viitatun sarakkeen arvoissa tapahtuu muutoksia tai rivejä poistetaan:

  • RESTRICT estää sarakkeen muutokset tai rivin poiston isäntätaulussa.
  • SET NULL asettaa sarakkeen arvon tyhjäarvoksi renkitaulussa.
  • CASCADE vyöryttää isäntätaulussa tapahtuneet muutokset renkitaulun sarakkeeseen.
  • SET DEFAULT asettaa renkitaulun sarakkeen arvoksi oletusarvon.

Tehtävä: Luo yllä olevan kuvion mukaiset taulut (kurssi, opettaja, kurssitoteutus) viite-eheyksineen. Alustava pohja SQL-lauseille on annettu alla. Lisää tauluihin rivejä.

# sqlHelp_ddlpkfk
4.4.2.1.2 Muut eheysrajoitteet

Sarakkeille voidaan asettaa eheysrajoitteita perus- ja viiteavaimen lisäksi. Yleisimmät, SQL-standardin määrittämät eheysrajoitteet ovat seuraavat:

  • DEFAULT määritetään sarakkeen oletusarvo.
  • NOT NULL kielletään tyhjäarvot sarakkeessa.
  • UNIQUE sarakkeen arvojen täytyy olla yksilöiviä.
  • CHECK voidaan määrittää hyväksyttävät arvot ehtolausekkeella.

Esimerkiksi:

CREATE TABLE asiakas (
	asiakastunnus CHAR(10) PRIMARY KEY,
	etunimi VARCHAR(20) NOT NULL,
	sukunimi VARCHAR(30) NOT NULL,
	email VARCHAR(50) UNIQUE,
	asiakkuus VARCHAR(10) DEFAULT 'perus',
	puhelin VARCHAR(20) CHECK (puhelin LIKE '+358%'),
	bonushyvitys DECIMAL(5,2) CHECK (bonushyvitys > 0)
);
# moniddl2

4.4.2.2 Taulujen muokkaaminen

Taulujen rakennetta muokataan DDL-komennolla ALTER TABLE. Komennolla voidaan esimerkiksi:

  • Muuttaa sarakkeen tietotyyppiä.
  • Muuttaa sarakkeen nimeä.
  • Poistaa tai luoda uusi sarake.
  • Poistaa tai luoda eheyspiirteitä.

Luodaan alla esimerkiksi (PostgreSQL) ensin osa taulun sarakkeista tietotyyppeineen ja lisätään vasta sen jälkeen eheyspiirteet ja kolmas sarake:

CREATE TABLE tilausrivi (
	asiakastunnus CHAR(10),
	tuotetunnus CHAR(10)
);

ALTER TABLE tilausrivi
ADD PRIMARY KEY (asiakastunnus, tuotetunnus);

ALTER TABLE tilausrivi
ADD FOREIGN KEY (asiakastunnus) REFERENCES asiakas (asiakastunnus);

ALTER TABLE tilausrivi
ADD COLUMN maara INTEGER;

ALTER-käskyn toteutuksissa on suuriakin tuotekohtaisia eroja. Yleisenä piirteenä voidaan pitää ADD,DROP ja SET -avainsanoja.

4.4.2.3 Taulujen poistaminen

Taulu ja kaikki sen sisältämä data poistetaan komennolla DROP jota seuraa taulun nimi. Jos tauluun viitataan viiteavaimilla, ne voivat estää poisto-operaation.

DROP TABLE tilausrivi;

4.4.3 Muiden tietokantaobjektien hallinta

Muita tietokantaobjekteja kuten näkymiä, triggereitä ja proseduureja voidaan myös hallita komennoilla CREATE, ALTER ja DROP. Tarkastellaan seuraavaksi yksinkertaisia esimerkkejä eri tietokantaobjektien luomisesta.

Näkymät (view) ovat SELECT-lauseen tuloksista luotuja aputauluja. Ne eivät kuitenkaan sisällä dataa, vaan ovat nimensä mukaisesti reaaliaikainen näkymä datasta. Näkymän voi käsittää tallennetuksi SELECT-lauseeksi, jolla saavutetaan mm. yksinkertaisemmat kyselyt sovellusohjelmassa (ts. tehdään hakulause näkymään, ei tauluihin), yleisimpien ad hoc -kyselyiden tallentaminen ja hienojakoisempi oikeuksienhallinta.

# sqlHelp_sqlite_view1

Yllä olevan lauseen avulla luodaan näkymä, joka sisältää SELECT-lauseensa mukaisen näkymän dataan. Näkymän sisältöä voidaan tarkastella edelleen SELECT-lauseella, esim. SELECT * FROM itäiset_asiakkaat;. SELECT-lause, jolla näkymä luodaan voi olla miten monimutkainen tahansa.

Triggerit (trigger) ovat ohjelmakoodia tai SQL:ää, joka suoritetaan automaattisesti jonkin ehdon täyttyessä. SQL-standardi määrittää, että triggerin käynnistymisen saa aikaan joko tauluun tai näkymään kohdistuva SELECT-, INSERT-, UPDATE- tai DELETE-käsky. Alla on esimerkki triggeristä (Oracle), joka sallii tyontekija-tauluun kohdistuvat poisto-operaatiot, mutta tallentaa lokiin vanhat arvot sekä poisto-operaation suoritusajan ja suorittajan.

CREATE TRIGGER tyontekija_bd
BEFORE DELETE ON tyontekija FOR EACH ROW
DECLARE
	kayttaja VARCHAR2(12);
BEGIN
	SELECT user 
	INTO kayttaja
	FROM dual;
	
	INSERT INTO tyontekija_loki (nimi, palkka, ostun, poistopvm, poistaja)
	VALUES (:old.nimi, :old.palkka, :old.ostun, SYSDATE, kayttaja);
END;
/

Proseduurit ja funktiot ovat tietokantaan tallennettua ohjelmakoodia, joka suoritetaan kutsuttaessa. Niiden käyttökohteet voivat olla melkein mitä tahansa, kohdealueesta ja sen tarpeista riippuen. SQL-standardi määrittää funktion rutiiniksi, joka voi palauttaa yhden arvon. Proseduuri puolestaan on rutiini, joka voi tuottaa useita tulosteparametreja. Proseduureilla ja funktioilla saadaan tavallisesti luotua monimutkaisempaa toiminnallisuutta kuin triggereillä, sillä niiden toiminnallisuus ei rajoitu SQL:ään. Eri tuotteilla on erilaisia tapoja ja kieliä hallita proseduureja ja funktioita, esimerkiksi:

  • Oracle Database: PL/SQL tai Java.
  • SQL Server: T/SQL tai .NET-kielet.
  • PostgreSQL: PL/pgSQL, pl/python, pl/perl jne.

Triggereitä, proseduureja tai funktioita ei käsitellä kurssilla tämän tarkemmin.

4.4.4 Indeksit

Indeksit eli hakemistot ovat tietorakenteita, jotka voivat tehdä hakuoperaatioista nopeampia. SQL-standardi ei määritä viitekehystä indekseille, joten eri tuotteissa on hyvinkin erilaisia indeksointitoteutuksia. Tarkastellaan seuraavaksi yleisellä tasolla relaatiotietokannanhallintajärjestelmiin vakiintuneita indeksointitapoja.

4.4.4.1 Rakenne

Vakiintunut indeksityyppi relaatiotietokannoissa on ns. B+-puu (B+-tree). Sen mukaisesti indeksin rakenne on puuta muistuttava ja jokaiseen ei-lehtisolmuun sijoitetaan n puuosoitinta (pointer) ja n-1 avainarvoa. Avainarvo kertoo, mistä hakulauseen ehdoissa määritettyä tauluriviä etsitään: jos etsittävä arvo on avainarvoa pienempi, seurataan avainarvon vasemmalta puolelta löytyvää puuosoitinta. Jos taas etsittävä arvo on yhtä suuri tai suurempi kuin avainarvo, seurataan avainarvon oikealta puolelta löytyvää puuosoitinta päätyen lopulta puun lehtisolmuun, jossa on viittaus levylle.

Kuvio 4.5: B+-puuindeksin rakenne. Indeksoitu sarake voisi olla esimerkiksi henkilön ikä.
Kuvio 4.5: B+-puuindeksin rakenne. Indeksoitu sarake voisi olla esimerkiksi henkilön ikä.

Indeksipuun lehtisolmuissa on avainarvoja, levyosoitteita sekä lohko-osoittimia. Levyosoitteet viittaavat siihen fyysiseen kohtaan levyllä, johon indeksoidun taulun rivi on tallennettu. Lohko-osoitin viittaa indeksin seuraavaan lehtisolmuun. Avainarvot ovat sarakkeiden arvoja indeksoidusta sarakkeesta.

Oletetaan hakulause SELECT * FROM henkilö WHERE ikä = 38; ja oletetaan indeksin olevan yllä olevan kuvion mukainen. Tietokannanhallintajärjestelmä seuraa indeksiä aloittaen juurisolmusta. Juurisolmusta seurataan avainarvon 23 oikealta puolelta löytyvää puuosoitinta, koska 38 > 23. Seuraavan tason solmusta seurataan avainarvon 34 oikealta puolelta löytyvää puuosoitinta, koska 38 > 34. Lehtisolmusta seurataan levyosoitinta levyosoitteeseen, jonne kyseinen rivi on tallennettu ja luetaan rivi muistiin.

4.4.4.2 Toiminta

B+-puu pyritään tavallisesti pitämään muistissa lehtisolmuja lukuun ottamatta. Kuvitellaan taulu tuote, jossa on sarakkeet tuotetunnus, tuotenimi, ja hinta. Taulun perusavain on tuotetunnus-sarake ja taulussa on 10 000 riviä. Oletetaan yksinkertainen SQL-hakulause:

SELECT tuotetunnus, hinta
FROM tuote
WHERE tuotetunnus = 5200;

Ilman indeksiä tietokannanhallintajärjestelmä joutuu lukemaan läpi pahimmassa tapauksessa taulun kaikki 10 000 riviä ja vertaamaan tuotetunnus-sarakkeen arvoa haluttuun arvoon 5200. Jos oletetaan yksinkertaistettuna, että yhden lohkon lukeminen levyltä kestää 10 ms, ja yksi rivi mahtuu yhteen lohkoon, koko taulun lukeva operaatio kestää 10 000 riviä * 10 ms = 100 sekuntia.

Jos taas tuotetunnus-sarake on indeksoitu, mutta indeksi ei ole muistissa, joutuu tietokannanhallintajärjestelmä tekemään n+1 levylukua: rivin luvun levyltä sekä n lukua indeksiin, missä n on indeksipuun korkeus.

Indeksit määritetään SQL-rajapinnan kautta DDL-lauseilla. Alla on esitetty yksinkertaisia esimerkkejä indeksien määrittämisestä.

CREATE INDEX asiakas_idx
ON asiakas (etunimi, sukunimi);

DROP INDEX asiakas_idx;

These are the current permissions for this document; please modify if needed. You can always modify these permissions from the manage page.