DAM - DAW - MP 0484 Bases de Dades

La pràctica del BANC - Entorn

El sistema bancari és un dels que més protecció ha de tenir, de manera que no podem deixar a qualsevol programador realitzar tasques directes sobre les taules de la base de dades.

Avui dia, la majoria d'aplicacions treballen amb crides API per tal d'independitzar les aplicacions del SGBD, de minimitzar els errors en programació i maximitzar la seguretat del sistema.

En aquesta pràctica crearem un entorn molt petit i bàsic per gestionar Comptes Bancaris.

A partir de tres taules simples intentarem explicar i veure el que podem fer amb procediments, funcions i triggers.

Preparem l’entorn

I el primer que cal fer és preparar l’entorn amb

• una base de dades bancdb

• un usuari administrador per la base de dades del banc adminbanc

• un usuari simple que només pugui operar amb els comptes però a través de procediments i funcions userbanc

Per això començarem creant la base de dades, els usuaris i donant-los permisos a cada un d’ells.

Recordeu que per a crear la base de dades i els usuari hem de connectar-nos amb l'usuari root- DBA.

Creació de la base de dades i dels usuaris pel Banc

CREATE DATABASE bancdb;
CREATE USER adminbanc@localhost IDENTIFIED BY 'adminbanc';
GRANT ALL PRIVILEGES on bancdb.* TO adminbanc@localhost;
CREATE USER userbanc@localhost IDENTIFIED BY 'userbanc';
GRANT EXECUTE on bancdb.* TO userbanc@localhost;

Fixeu-vos com l’usuari userbanc no pot veure el contingut de les taules, només té el permís EXECUTE que li permet executar funcions i procediments emmagatzemats.

Treballarem amb tres taules simples, la d’oficina, la de compte bancari i la dels moviments de cada compte bancari.

La taula compte serà una entitat feble d'oficina i la taula moviment serà una entitat feble de compte.

Tindrem dades redundants que mirarem d’anar gestionant de forma automàtica. Això ens servirà per a poder practicar la programació de triggers.

Creació de taules

La definició de les taules serà la següent:

Creació de les taules del Banc

USE bancdb;
DROP TABLE IF EXISTS MOVIMENT;
DROP TABLE IF EXISTS COMPTE;
DROP TABLE IF EXISTS OFICINA;

CREATE TABLE OFICINA (
    cOficina INT(4) AUTO_INCREMENT,
    dOficina VARCHAR(50) NOT NULL,
    adrOficina VARCHAR(50) NOT NULL,
    darrerCompte INT(11) NOT NULL DEFAULT 0,
    saldoOficina DECIMAL(20,2) NOT NULL DEFAULT 0,
    CONSTRAINT PK_OFICINA PRIMARY KEY (cOficina)
); 
CREATE INDEX I_OFICINA_D_OFICINA on OFICINA(dOficina);

INSERT INTO oficina(dOficina, adrOficina) VALUES
    ('Una', 'Avda. Montilivi'),
    ('Dues', 'Pl. Catalunya'),
    ('Tres', 'C. Amunt');

CREATE TABLE COMPTE (
    cOficina INT(4) NOT NULL,
    cCompte BIGINT(10) NOT NULL,
    numeroOperacionsAdd INT(11) NOT NULL DEFAULT 0,
    numeroOperacionsSub INT(11) NOT NULL DEFAULT 0,
    saldoCompte DECIMAL(20,2) NOT NULL DEFAULT 0,
    CONSTRAINT PK_COMPTE PRIMARY KEY (cOficina, cCompte),
    CONSTRAINT FK_COMPTE_OFICINA
        FOREIGN KEY (cOficina) 
        REFERENCES Oficina(cOficina) 
);

CREATE TABLE MOVIMENT (
    cOficina INT(4) NOT NULL,
    cCompte BIGINT(10) NOT NULL,
    numeroMoviment INT(11) NOT NULL,
    import DECIMAL(20,2) NOT NULL,
    moment DATE DEFAULT CURRENT_TIMESTAMP,
    tipusMoviment VARCHAR(50) NULL,
    CONSTRAINT PK_MOVIMENT
        PRIMARY KEY (cOficina, cCompte, numeroMoviment),
    CONSTRAINT FK_MOVIMENT_COMPTE
        FOREIGN KEY (cOficina, cCompte) 
        REFERENCES Compte(cOficina, cCompte)
);

• L’usuari adminbanc serà el que podrà crear les funcions, procediments, triggers, etc

• L’usuari userbanc només tindrà accés als procediments i funcions que programem.

Hem creat tres oficines ja que aquesta no és feina de userbanc i per tant hem decidit que les oficines les crearem amb INSERT amb l’usuari adminbanc.