Arvestustöö - Maksim Tsikvasvili Portfoolio

Lehe Sisukord

Käesolev postitus käsitleb Andmebaasi Arvestustöö raames tehtud tegevusi ja loodud lahendusi. Alustame andmebaasi loomisest ja tabelite seadistamisest.

CREATE DATABASE kool;

Tabelite loomine ja seostamine

Loomata on Opetaja tabel.

CREATE TABLE Opetaja(
    OpetajaID INT PRIMARY KEY AUTO_INCREMENT,
    OpetajaNimi VARCHAR(60) UNIQUE,
    Aine VARCHAR(60)
);

Seejärel loome Klass tabeli, lisades ka võõrvõtme seose (foreign key).

CREATE TABLE Klass(
    KlassID INT PRIMARY KEY AUTO_INCREMENT,
    KlassNimi VARCHAR(60) UNIQUE,
    OpetajaID INT,
    OpilasteArv INT,
    FOREIGN KEY (OpetajaID) REFERENCES Opetaja(OpetajaID)
);

Viimasena loome Opilane tabeli koos võõrvõtmega, mis viitab Klass tabelile.

CREATE TABLE Opilane(
    OpilaneID INT PRIMARY KEY AUTO_INCREMENT,
    OpilaneNimi VARCHAR(60) UNIQUE,
    KlassID INT,
    FOREIGN KEY (KlassID) REFERENCES Klass(KlassID)
);

Kasutaja loomine ja õiguste haldamine andmebaasis

Loome uue kasutaja nimega ‘test’ ja määrame talle parooli.

CREATE USER 'test'@'localhost'
IDENTIFIED VIA mysql_native_password
USING '1111';

Anname kasutajale ‘opilaneNimi’ õigused andmete vaatamiseks ja lisamiseks “kool” andmebaasi tabelites Klass, Opetaja ja Opilane.

GRANT SELECT, INSERT ON `kool`.`klass` TO 'test'@'localhost';
GRANT SELECT, INSERT ON `kool`.`opetaja` TO 'test'@'localhost';
GRANT SELECT, INSERT ON `kool`.`opilane` TO 'test'@'localhost';

Kuvatakse, mida kasutaja opilaneNimi näeb pärast õiguste andmist.

Logi tabeli ja triggerite loomine

Loo tabel logi, et jälgida andmebaasi tegevusi.

CREATE TABLE logi(
    id INT PRIMARY KEY AUTO_INCREMENT,
    kasutaja VARCHAR(80),
    kuupaev DATETIME,
    tegevus VARCHAR(90),
    andmed TEXT
);

Loome triggeri, mis logib andmete kustutamise tabelis klass.

DELIMITER //
CREATE TRIGGER tr_klass_delete
AFTER DELETE ON Klass
FOR EACH ROW
BEGIN
    INSERT INTO logi(kasutaja, kuupaev, tegevus, andmed)
    VALUES (USER(), NOW(), 'Klassi kustutamine', CONCAT('Kustutatud klass: ', OLD.KlassNimi, ', ID: ', OLD.KlassID));
END;
//
DELIMITER ;

See trigger jälgib andmete lisamist tabelisse klass ja logib selle “logi” tabelisse.

DELIMITER //
CREATE TRIGGER tr_klass_insert
AFTER INSERT ON Klass
FOR EACH ROW
BEGIN
    INSERT INTO logi(kasutaja, kuupaev, tegevus, andmed)
    VALUES (USER(), NOW(), 'Klassi lisamine', CONCAT('Lisatud klass: ', NEW.KlassNimi, ', ID: ', NEW.KlassID));
END;
//
DELIMITER ;

Triggerite töö kontrollimine

Kontrollime, et andmed luuakse tabelisse logi, kui lisame midagi klass tabelisse.

Teeme näidisandmed:

Opetaja:

INSERT INTO `opetaja` (`OpetajaNimi`, `Aine`)
VALUES ('Mari Speek', 'Sotsiaalained');

Klass:

INSERT INTO `klass` (`KlassNimi`, `OpetajaID`, `OpilasteArv`)
VALUES ('TARpv24', 2, 15);

Logi tabel pärast lisamist:

Kontrollime, et andmed luuakse tabelisse logi, kui kustutame midagi klass tabelist.

DELETE FROM klass
WHERE KlassNimi = 'TARpv24';

Logi tabel pärast kustutamist:

Kasutaja õiguste piiramine

Kontrollime, et kasutaja opilaneNimi ei saa teha tegevusi, mis talle ei ole lubatud, näiteks andmete muutmine või uute tabelite loomine.

UPDATE opetaja
SET OpetajaNimi = 'Alina'
WHERE OpetajaID = 2;

Katsutakse uuendada õpetaja nime ilma vajalike õigusteta

CREATE TABLE test(
    testID INT PRIMARY KEY AUTO_INCREMENT,
    kiri VARCHAR(200)
);

Katsutakse luua tabelit ilma vajalike õigusteta

Andmebaasi protseduuri loomine

Loome protseduuri, mis kuvab kõik klassid ja nende õpilased, kes on seotud kindla õpetajaga.

Lisame uue õpilase protseduuri testimiseks:

INSERT INTO `opilane` (`OpilaneNimi`, `KlassID`)
VALUES ('Maksim', 5);

Protseduuri definitsioon:

DELIMITER //
CREATE PROCEDURE GetKlassidJaOpilasedByOpetaja(IN opetaja_id INT)
BEGIN
    SELECT
        o.OpetajaNimi,
        k.KlassNimi,
        p.OpilaneNimi
    FROM Opetaja o
    LEFT JOIN Klass k ON o.OpetajaID = k.OpetajaID
    LEFT JOIN Opilane p ON k.KlassID = p.KlassID
    WHERE o.OpetajaID = opetaja_id;
END //
DELIMITER ;

Protseduuri 'GetKlassidJaOpilasedByOpetaja' loomine

Protseduuri käivitamine ja tulemus:

CALL GetKlassidJaOpilasedByOpetaja(2);

Protseduuri 'GetKlassidJaOpilasedByOpetaja' tulemus

Andmebaasi transaktsioon ja SAVEPOINT

Loome transaktsiooni, mis võimaldab sisestatud andmed vajadusel tagasi võtta, kasutades SAVEPOINT’i. See on oluline osa andmebaasi arvestustööst andmete terviklikkuse tagamiseks.

START TRANSACTION;

SAVEPOINT esimene;

INSERT INTO Opetaja (OpetajaNimi, Aine)
VALUES ('Tanne Kiik', 'Matemaatika');

SELECT * FROM Opetaja;
ROLLBACK TO esimene;
SELECT * FROM Opetaja;

COMMIT;

Enne ROLLBACK’i, näeme lisatud õpetajat:

Pärast ROLLBACK’i, on lisatud õpetaja kadunud:

Lisa oma andmebaasi tegevus: KlassiStatistika vaade

Lisame vaate nimega KlassiStatistika, mis kuvab iga õpetaja õpetatavad klassid koos õpilaste arvuga. See on kasulik funktsioon andmebaasi arvestustöö kontekstis.

See vaade on äärmiselt kasulik, sest see näitab koheselt, kui palju õpilasi igas klassis on, aidates kaasa kooli ressursside planeerimisele ja ülevaate saamisele.

CREATE VIEW KlassiStatistika AS
SELECT
  o.OpetajaNimi AS Opetaja,
  k.KlassNimi AS Klass,
  COUNT(p.OpilaneID) AS OpilasteKokku
FROM Opetaja o
LEFT JOIN Klass k ON o.OpetajaID = k.OpetajaID
LEFT JOIN Opilane p ON k.KlassID = p.KlassID
GROUP BY o.OpetajaNimi, k.KlassNimi;

SELECT * FROM `KlassiStatistika`;

Miks LEFT JOIN? Kasutame LEFT JOIN-i selleks, et kuvada ka õpetajad, kellel hetkel ühtegi klassi pole. See tagab tervikliku ülevaate kõikidest õpetajatest ja nende seostest klassidega, isegi kui seos puudub.

Loodame, et see Andmebaasi Arvestustöö ülevaade oli kasulik! Kui teil on küsimusi andmebaasi haldamise kohta, võtke julgelt ühendust.