ebSkola

10. tēma / 10.3

10.3 Tabulu projektēšana un saites

Stundas uzdevums: Projektēt vairākas saistītas tabulas, izmantojot primārās un ārējās atslēgas.

SR 2.4.1. Analizē problēmu un veic dekompozīciju SR 2.4.6. Plāno un dokumentē izstrādes procesu

70 min plāns: Teorija un paraugs (~10 min) · 1. uzdevums (~15 min) - izveido otro tabulu ar ārējo atslēgu · 2. uzdevums (~25 min) - pārbaudi, ko dara ārējā atslēga · 3. uzdevums (~20 min) - novērs datu dublēšanos. Papildu uzdevumu sāc tikai tad, ja pārējie trīs ir gatavi.

Pirms sāc: atver savu datubāzi spele. Šajā stundā no vienas tabulas taisīsi divas, kas saistītas savā starpā.

Teorija: Primārās un ārējās atslēgas

Datubāze ir tikai tik laba, cik labi tā ir projektēta. Galvenie principi:

  • Primary Key (PK) - viens lauks, kas unikāli identificē rindu (parasti id SERIAL).
  • Foreign Key (FK) - lauks vienā tabulā, kas norāda uz citas tabulas PK.
  • Normalizācija - neatkārto vienus un tos pašus datus vairākās tabulās.
-- Spēlētāji un viņu spēles (1 → daudzi)
CREATE TABLE speletaji (
    id SERIAL PRIMARY KEY,
    vards TEXT NOT NULL UNIQUE
);

CREATE TABLE speles (
    id SERIAL PRIMARY KEY,
    speletajs_id INTEGER NOT NULL REFERENCES speletaji(id) ON DELETE CASCADE,
    punkti INTEGER NOT NULL,
    spelets TIMESTAMP DEFAULT NOW()
);

ON DELETE CASCADE - ja izdzēš spēlētāju, automātiski tiek izdzēstas arī viņa spēles.

Praktiskie uzdevumi

1. uzdevums -

Izveido otro tabulu ar ārējo atslēgu

Beigās katra spēle zinās, kuram spēlētājam tā pieder.

  1. Izveido tabulu CREATE TABLE speles (.
  2. Pievieno lauku id SERIAL PRIMARY KEY.
  3. Pievieno lauku speletajs_id INTEGER NOT NULL REFERENCES speletaji(id).
  4. Pievieno lauku punkti INTEGER NOT NULL CHECK (punkti >= 0).
  5. Pievieno lauku spelets TIMESTAMP DEFAULT NOW().
  6. Izpildi skriptu un pārbaudi tabulu ar \d speles.

Gatavs, kad: tabula speles eksistē, un speletajs_id ir atzīmēts kā ārējā atslēga.

2. uzdevums -

Pārbaudi, ko dara ārējā atslēga

Beigās tu redzēsi, ka datubāze neļauj izveidot spēli bez spēlētāja.

  1. Atrodi viena spēlētāja id ar SELECT id, vards FROM speletaji;.
  2. Ievadi spēli šim spēlētājam ar INSERT.
  3. Mēģini ievadīt spēli ar speletajs_id = 9999.
  4. Pieraksti, kā sauc kļūdu, kas to neļāva.
  5. Mēģini izdzēst spēlētāju, kuram ir spēles.
  6. Pieraksti, kāpēc arī tas neizdodas.
  7. Pievieno ārējai atslēgai ON DELETE CASCADE un mēģini vēlreiz.

Gatavs, kad: spēli neizdodas piesaistīt neesošam spēlētājam, bet ar ON DELETE CASCADE spēlētāja dzēšana aizvāc arī viņa spēles.

3. uzdevums -

Novērs datu dublēšanos

Beigās viens un tas pats dati vairs neatkārtosies divās vietās.

  1. Pieraksti, kas notiktu, ja spēlētāja vārdu glabātu arī tabulā speles.
  2. Pieraksti, cik vietās būtu jālabo vārds, ja spēlētājs to nomainītu.
  3. Izpildi vaicājumu, kas parāda spēles kopā ar spēlētāja vārdu no otras tabulas.
  4. Pārliecinies, ka vārds tabulā speles nav glabāts.
  5. Nomaini viena spēlētāja vārdu ar UPDATE.
  6. Izpildi vaicājumu vēlreiz un pārbaudi, ka jaunais vārds parādās visur.
  7. Pieraksti vienu secinājumu: ko nozīmē neatkārtot datus.

Gatavs, kad: nomainot vārdu vienā vietā, tas mainās visos rezultātos, jo tas glabājas tikai vienā tabulā.

Papildu uzdevums - Pievieno trešo tabulu

Ja pamatdarbs ir gatavs, sadali spēles pa veidiem.

  1. Izveido tabulu speles_veidi ar id un nosaukums.
  2. Ievadi tajā trīs spēļu veidus.
  3. Pievieno tabulai speles lauku veids_id ar ārējo atslēgu.
  4. Atjaunini esošās spēles ar kādu veidu.
  5. Pārbaudi, ka nevar ievadīt neesošu veidu.

Gatavs, kad: katrai spēlei ir veids, un neesoša veida ievade tiek noraidīta.

Biežākās kļūdas

  • FK norāda uz neeksistējošu rindu: Vispirms ievadi vecāku tabulā, tad bērnu.
  • Aizmirsts CASCADE: Bez tā nevarēsi dzēst vecāku rindas, kamēr ir bērnu rindas.
  • Daudzi-pret-daudziem bez saites tabulas: NEKAD nelieto FK masīvu - vienmēr trešo tabulu.

Koda piemērs

CREATE TABLE speletaji (
    id SERIAL PRIMARY KEY,
    vards TEXT NOT NULL UNIQUE
);

CREATE TABLE speles (
    id SERIAL PRIMARY KEY,
    speletajs_id INTEGER NOT NULL REFERENCES speletaji(id) ON DELETE CASCADE,
    punkti INTEGER NOT NULL CHECK (punkti >= 0),
    spelets TIMESTAMP DEFAULT NOW()
);

INSERT INTO speletaji (vards) VALUES ('Anna'), ('Jānis');
INSERT INTO speles (speletajs_id, punkti) VALUES (1, 120), (1, 85), (2, 200);
Tabulu shēma izveidota.
3 spēļu rezultāti pievienoti.