www.agora.ro

Fundamentele bazelor de date

Modelul relațional

Ion Cozac

Ce este modelul relațional? O caracterizare ar putea fi următoarea: modelul relațional este o tehnică de descriere a datelor cu ajutorul tabelelor și o tehnică de manipulare a acestora cu ajutorul unor operatori specifici calculului relațional și/sau algebrei relaționale, concretizați prin utilizarea unor limbaje specifice - cum ar fi QbE (Query by Example) sau SQL (Structured Query Language).

Modelul relațional este un model abstract care are la bază fundamente matematice solide (teoria algebrică a relațiilor). Principiile modelului relațional au fost descrise prin 1969-1970 de către E. F. Codd, cercetător la IBM. El a fost primul care a înțeles că rigoarea matematică poate fi folosită pentru a introduce principii solide în domeniul gestiunii bazelor de date - domeniu care era foarte deficitar la acest capitol.

Modelul entități-legături

În articolul precedent am prezentat o arhitectură clasică a unei baze de date. Aceasta este organizată pe trei nivele:

- nivelul extern sau utilizator;

- nivelul conceptual;

- nivelul intern.

Pentru a descrie nivelul conceptual folosim un model abstract numit model entități-legături.

Elementul fundamental al acestui model este entitatea: acest termen generic desemnează un obiect care face parte dintr-o clasă (mulțime) de entități, toate aceste obiecte sunt similare ca structură, dar pot fi deosebite prin proprietăți specifice (pe care le vom numi în continuare atribute). Deoarece definiția este destul de vagă (imprecisă) vom da câte două exemple preluate dintr-o problemă (simplificată) de evidență a facturilor și dintr-o altă problemă de evidență a personalului. Avem următoarele clase de entități:

1. Mulțimea clienților unui magazin

Fiecare client reprezintă o entitate a acestei clase și are următoarele atribute:

- cod personal;

- nume;

- localitate (de domiciliu);

- adresa.

Fiecare entitate este identificabilă prin atributul cod personal al acesteia.

2. Mulțimea produselor aflate în evidența magazinului

Toate aceste produse pot fi caracterizate prin:

- cod produs;

- denumire;

- preț unitar.

Fiecare produs este o entitate identificabilă prin codul acestuia.

3. Mulțimea departamentelor unei firme

Orice departament se caracterizează prin: cod, denumire, șef.

4. Mulțimea angajaților unei firme

Fiecare angajat se caracterizează prin:

- număr legitimație;

- nume;

- departamentul la care lucrează.

Un alt element care caracterizează acest model este noțiunea de legătură. Legătura (asocierea) între mai multe clase de entități E1, ..., En (nu neapărat distincte) presupune existența unei mulțimi de valori (e1, ..., en), unde ei este mulțimea valorilor atributelor unei entități din clasa Ei. În practică de cele mai multe ori întâlnim legături între două clase de entități (n=2); acestea se numesc legături binare.

Un exemplu concret preluat din evidența personalului:

(101,"Personal",5001,5001,"Petrescu",101)

Primele trei valori sunt preluate din clasa de entități Departamente, următoarele trei din clasa Personal. Se observă ușor că această mulțime de valori este un element al produsului cartezian al celor două mulțimi: Departamente și Personal. După ce eliminăm valorile care se repetă (această operație este numită proiecție) obținem:

(101,"Personal",5001,"Petrescu")

Să clasificăm în continuare legăturile binare după cardinalitatea acestora (câte entități din fiecare clasă intră în cadrul legăturii):

- legături de tip 1:1 (one-to-one); observăm o astfel de legătură în cazul unei evidențe a personalului, care indică faptul că un departament este condus de un șef (un departament nu poate fi condus de mai mulți șefi, o persoană nu poate conduce mai multe departamente);

- legături de tip 1:n (one-to-many); în evidența personalului remarcăm o astfel de legătură între angajații unui departament și departamentul în cauză (la un departament lucrează mai mulți angajați, un angajat nu poate lucra în cadrul mai multor departamente); la evidența facturilor remarcăm o legătură între Facturi și Clienti (unui client i se pot întocmi mai multe facturi; nu se poate întocmi o aceeași factură pentru mai mulți clienți);

- legături de tip m:n (many-to-many); o astfel de legătură există între Clienti și Produse: un client poate cumpăra mai multe produse, și în același timp mai mulți clienți pot cumpăra un același sortiment de produse.

După ce vor fi prezentate fundamentele modelului relațional, vom prezenta și tehnici de implementare a acestor tipuri de legături.

Domeniu, atribut, relație

Un domeniu este o mulțime de valori scalare (atomice - care nu pot fi descompuse) având același tip.

Exemple:

- mulțimea codurilor personale;

- mulțimea numelor de clienți;

- mulțimea numelor de localități etc.

Cu aceste valori se pot efectua mai multe operații:

- cu majoritatea acestor valori se pot face comparații;

- cu unele valori se pot face și alte operații: o cantitate poate fi înmulțită cu un preț (rezultând o valoare), un preț poate fi înmulțit cu o valoare (în cazul unei reașezări) ș.a.m.d.

Un atribut al unei clase de entități este o caracteristică (proprietate) care ia valori într-un anumit domeniu.

Exemple:

- atributele clasei de entități CLIENTI sunt: cod client, nume client, localitate, adresa;

- atributele clasei de entități PRODUSE sunt: cod produs, denumire produs, preț unitar.

O relație (în sens algebric) este o submulțime a unui produs cartezian D1 D2 ... Dn, unde Di sunt domenii de valori (nu neapărat distincte). Aceasta a fost definiția folosită mult timp și pentru noțiunea de relație în domeniul bazelor de date - modelul relațional. Unul dintre cei mai prestigioși autori de lucrări în acest domeniu, C. J. Date (amintit în articolul precedent) propune o nouă definiție pentru noțiunea de relație (în domeniul bazelor de date): aceasta se compune din două părți:

- antetul relației este o mulțime de atribute definite fiecare pe câte un domeniu de valori (aceste domenii nu sunt neapărat distincte):

{A1:D1, A2:D2, ..., Am:Dm};

- corpul relației este o mulțime de tuple (un tuplu este generalizarea noțiunii de cuplu); fiecare tuplu este definit ca o mulțimi de valori ale atributelor definite în cadrul antetului:

{{A1:v11, A2:v12, ..., Am:v1m}, ...,

{A1:vn1, A2:vn1, ..., Am:vnm}}.

Valoarea m (numărul de atribute ale relației) reprezintă gradul relației, iar valoarea n (numărul de tuple) reprezintă cardinalitatea relației.

Din definiția de mai sus a noțiunii de relație rezultă următoarele proprietăți:

1. Atributele nu sunt ordonate (ordinea lor nu este semnificativă).

Această proprietate rezultă din faptul că antetul este definit ca o mulțime de atribute (într-o mulțime ordinea elementelor nu este semnificativă);

2. Atributele sunt distincte (chiar dacă pot exista două atribute definite pe același domeniu), deoarece elementele unei mulțimi sunt distincte;

3. În cadrul corpului relației tuplele nu sunt ordonate - corpul relației este definit ca o mulțime de tuple;

4. Orice atribut are doar valori atomice;

Această proprietate poate fi enunțată și astfel:

La intersecția dintre o linie și o coloană se află întotdeauna o singură valoare, și niciodată o colecție de valori; rezultă că o relație nu conține grupuri repetitive; spunem în acest caz că relația se află în forma întâi normală;

5. Nu există tuple duplicate, deoarece corpul unei relații este o mulțime de tuple.

Dacă o linie din tabela Clienti conține valorile:

(80001, Ionescu, Bucuresti, Str Libertatii)

toate aceste valori se află într-o anumită relație: ele definesc identitatea unui client, care are codul personal 80001, are numele Ionescu, domiciliază în localitatea Bucuresti la adresa Str Libertatii.

Prezentăm în continuare o corespondență între termenii formali (definiți mai sus) și termenii informali (cei folosiți în mod curent în exploatarea bazelor de date relaționale):

Relație Tabelă

Tuplu Linie / înregistrare

Cardinalitate Număr de linii

Atribut Coloană / Câmp

Grad Număr de coloane

Domeniu Mulțime de valori valide

Corespondența nu trebuie considerată ca o echivalență, deoarece există câteva deosebiri de care trebuie să ținem seama (este de altfel una dintre diferențele pe care le sesizăm între teorie și practică).

Relația este o noțiune teoretică. Tabela este un obiect concret, care are o anumită reprezentare în calculator - sub forma unui tablou bidimensional. Într-o relație ordinea atributelor și a tuplelor nu este semnificativă. Într-o tabelă există o ordonare atât a coloanelor - dată de ordinea acestora la crearea tabelei, cât și a liniilor - dată de ordinea în care acestea au fost introduse, sau de ordinea unei chei care induce o anumită ordonare a liniilor în cadrul tabelei. O relație este formată întotdeauna din tuple distincte. În multe cazuri o tabelă poate avea linii duplicate.

Cheie primară, cheie externă

O cheie candidată a unei relații R este o mulțime K de atribute cu următoarele proprietăți:

- identificare unică: nu există două tuple distincte în R care să aibă aceeași valoare pentru setul de atribute K; cu alte cuvinte, mulțimea K de atribute identifică în mod unic fiecare tuplu al relației R;

- nereductibilitate: nu există o submulțime proprie a lui K (distinctă de K) care să aibă proprietatea de identificare unică.

O cheie este simplă dacă este formată dintr-un singur atribut și se numește compusă în caz contrar.

O relație poate avea mai multe chei candidate; una dintre acestea se alege pentru a fi folosită în aplicații ca și cheie de identificare a tuplelor. Cheia candidată folosită în acest scop se numește cheie primară; de obicei se folosesc în acest scop acele chei care reprezintă coduri ale înregistrărilor memorate în baza de date. Un motiv important pentru a justifica o astfel de alegere este următorul: valorile de tip cod ocupă foarte puțin spațiu, în comparație cu informațiile pe care le identifică, fapt care conduce la o economie importantă de spațiu în cazul creării unui index, precum și la accelerarea regăsirii informațiilor în cazul în care se fac corelații între tabele.

Să analizăm unul din exemplele prezentate:

- tabela Clienti are cheia primară Codcl, tabela Produse are cheia primară Codpr, tabela Facturi are cheia primară Nrfact; în toate aceste cazuri se verifică ușor cele două proprietăți ale cheii primare - atât identificarea unică cât și ireductibilitatea sunt evidente;

- tabela Detalii are cheia primară compusă din atributele Nrfact și Codpr; ireductibilitatea este evidentă și în acest caz, deoarece este posibil să avem mai multe linii cu aceeași valoare pentru Nrfact (când un client cumpără mai multe produse cu aceeași factură), și de asemenea este posibil să avem mai multe linii cu aceeași valoare pentru Codpr (când mai mulți clienți cumpără același tip de marfă).

În continuare se definește noțiunea de cheie externă (sau cheie străină) în conexiune cu noțiunea de cheie candidată. Să facem precizarea că în majoritatea situațiilor practice apar legături între tabele care se materializează prin coincidența valorilor cheilor primare și externe.

Fie R2 o relație. O cheie externă din R2 este o mulțime Ek de atribute cu următoarele proprietăți:

- există o tabelă R1 (care poate sau nu să coincidă cu R2) care are o cheie candidată Pk;

- fiecare valoare a setului Ek din R2 coincide cu o valoare a setului Pk din R1.

Să analizăm din nou unul din exemplele prezentate:

- tabela Facturi are o cheie externă Codcl, care este cheie primară în tabela Clienti;

- tabela Detalii are două chei externe: Nrfact care este cheie primară în tabela Facturi, și Codpr care este cheie primară în tabela Produse; am remarcat mai sus că cele două chei externe formează împreună cheia primară a tabelei Detalii.

Să facem câteva precizări:

- Prin definiție, fiecare valoare a unei chei externe trebuie să se regăsească printre mulțimea valorilor cheii candidate corespondente; reciproca nu este obligatorie: de exemplu, putem înregistra informații în tabela Clienti despre un potențial client, chiar dacă acesta încă nu cumpără nimic; putem de asemenea înregistra informații despre un produs pe care îl aducem în magazin chiar dacă el nu a fost încă cumpărat de nici un client;

- O cheie externă este simplă dacă și numai dacă cheia candidată corespondentă este simplă, și este compusă dacă și numai dacă cheia candidată corespondentă este compusă;

- Fiecare atribut component al unei chei externe trebuie să fie definit pe același domeniu al componentei corespondente din cheia candidată;

- O valoare a unei chei externe reprezintă o referință către un tuplu care conține aceeași valoare pentru cheia candidată corespondentă; se pune astfel problema integrității referinței: o bază de date nu trebuie să conțină valori invalide pentru chei externe, altfel spus dacă B referă pe A atunci A trebuie să existe.

Ce se întâmplă dacă ștergem din tabela Clienti un client pentru care avem câteva linii în tabela Facturi? Dacă operația de ștergere se efectuează fără nici un control, după ștergere nu mai este respectată regula integrității referinței. Pentru a preveni asemenea situații, în tabela Facturi precizăm la creare:

[ON DELETE option] [ON UPDATE option]

unde opțiunea poate fi:

- RESTRICTED dacă dorim ca operația cerută pentru tabela Clienti să fie respinsă;

- CASCADES dacă dorim ca operația cerută pentru tabela Clienti să provoace operații (actualizări sau ștergeri) "în cascadă" - se actualizează (sau se șterg) toate liniile afectate din tabela Facturi; în acest al doilea caz se cere o atenție deosebită, deoarece o ștergere a unei linii din tabela Facturi poate afecta linii din tabela Detalii.

Cheile externe se folosesc pentru a implementa legăturile dintre entități. Legăturile de tip one-to-one și one-to-many se implementează introducând în una din tabele o cheie externă, care va face legătura cu cheia primară din tabela corespondentă. O legătură de tip many-to-many

se implementează introducând o tabelă suplimentară care are o cheie primară compusă, fiecare element al cheii primare fiind o cheie externă. Cele două exemple sunt (sperăm) concludente în acest sens.

Valori NULL

Există unele situații când regula integrității referinței nu poate fi respectată. Să considerăm următorul exemplu foarte simplu: o bază de date pentru evidența personalului este compusă din două tabele:

Departamente (Coddep, Dendep, Sefdep)

Angajati (Nrleg, Numeang, Dep)

Observăm că tabela Departamente are ca și cheie primară atributul Coddep, iar cheie externă Sefdep, care este cheie primară în tabela Angajati. De asemenea tabela Angajati are ca și cheie primară atributul Nrleg, și cheie externă Dep, care este cheie primară în tabela Departamente. În momentul creării celor două tabele nu există nici o informație înregistrată. Dacă dorim să introducem informații despre un departament operația de inserare va fi respinsă deoarece nu avem informațiile despre șeful departamentului respectiv. Dacă dorim să introducem informații despre șeful unui departament operația de inserare va fi de asemenea respinsă deoarece nu avem informațiile despre departamentul respectiv.

Această problemă este rezolvată prin introducerea noțiunii de valoare NULL; semnificația acestei valori este valoare necunoscută sau informație absentă. În concordanță cu această nouă noțiune, regulile de integritate se completează astfel:

- integritatea entității: orice atribut al unei chei primare nu poate avea valoarea NULL;

- integritatea referinței: orice valoare a unei chei externe este fie NULL, fie coincide cu o valoare a unei chei candidate corespondente.

Problema enunțată mai sus se poate rezolva în felul următor:

Pasul 1:

se introduc informațiile despre departament, fără a se precizeza informația despre șeful departamentului;

Pasul 2:

se introduc informațiile despre șeful departamentului; în acest moment se poate introduce și informația despre departamentul la care acesta lucrează;

Pasul 3:

se actualizează informația absentă din tabela Departamente.

Crearea unei baze de date relaționale în SQL

O bază de date relațională este o bază de date care este văzută de utilizator ca o colecție de relații (tabele) normalizate (aduse cel puțin în forma întâi normală).

Înainte de a prezenta instrucțiunile SQL (Structured Query Language - Limbaj Structurat de Interogare) pentru definirea unei baze de date să facem câteva precizări:

1. Numele limbajului este impropriu după cum se poate ușor observa. Acest limbaj ne permite atât definirea datelor, cât și manipularea acestora: inserare, modificare, ștergere, consultare (interogare).

2. Orice operație realizată de o instrucțiune SQL operează cu tabele SQL. O tabelă SQL, spre deosebire de relații, poate avea linii duplicate. De asemenea tabelele SQL au o ordine precizată a coloanelor (de la stânga la dreapta), în schimb ordinea liniilor nu este semnificativă. O tabelă SQL se creează cu ajutorul unei comenzi CREATE TABLE:

CREATE TABLE Nume-tabela (Coloana Reprezentare [Valoare-implicită] [Restricții], ...);

Reprezentare. Standardul SQL precizează mai multe tipuri de date scalare (detalii privind modul de reprezentare trebuie căutate în documentația SGBD-ului care se folosește în mod concret), unele dintre acestea fiind prezentate în continuare:

Char (n) - o coloană de șiruri de maximum n caractere;

Integer - o coloană de valori întregi pozitive sau negative (de regulă întregi pe 4 octeți);

Smallint - o coloană de valori întregi mici (de regulă întregi pe 2 octeți);

Float (p) - o coloană de valori reprezentate în virgulă mobilă;

Decimal (p,q) - o coloană de valori zecimale, unde p este numărul total de poziții pe care se reprezintă numărul, și q este numărul de cifre ale părții zecimale (punctul zecimal nu se reprezintă în mod explicit)

Date - o coloană de valori de tip dată calendaristică, pentru reprezentarea cărora se folosește un format specific SGBD-ului folosit;

Time - o coloană de valori de tip oră exactă.

Valoare implicită. Pentru fiecare coloană se poate preciza o valoare implicită (care trebuie să se încadreze în tipul de date definit la reprezentare), în cazul în care la inserarea unei linii în tabelă nu este precizată o valoare pentru coloana respectivă. Dacă nu a fost precizată o valoare implicită la creare și nici la inserare, atunci coloana respectivă va primi o valoare NULL, dar numai dacă o valoare NULL este permisă. O valoare implicită se precizează astfel:

DEFAULT = valoare

Restricții:

Tip de restricție Observație

NOT NULL coloana respectivă nu poate primi valori

NULLCHECK se precizează o condiție care va fi veri-

(condiție) ficată în momentul inserării unei linii în tabelă, sau în momentul actualizării unei linii:

Exemplu:

CHECK (PRETUNIT > 0)

restricție de tip cheie candidată:

UNIQUE (Lista-de-coloane);

restricție de tip cheie primară:

PRIMARY KEY (Lista-de-coloane);

restricție de tip cheie externă:

FOREIGN KEY (Lista-de-coloane)

REFERENCES Nume-tabela

[ON DELETE option] [ON UPDATE option]

Comenzile SQL pentru crearea bazei de date cu care am lucrat până acum pot fi următoarele:

CREATE TABLE Clienti (Codcl Integer,

Nume Char (60), Localit Char (60),

Adresa Char (60), PRIMARY KEY (Codcl));

CREATE TABLE Produse (Codpr Integer,

Denumire Char (60), Pretunit Integer,

PRIMARY KEY (Codpr));

CREATE TABLE Facturi (Nrfact Integer,

Dataf DATE, Codcl Integer,

PRIMARY KEY (Nrfact), FOREIGN KEY (Codcl)

REFERENCES Clienti ON DELETE RESTRICTED

ON UPDATE RESTRICTED);

CREATE TABLE Detalii (Nrfact Integer,

Codpr Integer, Cant Integer,

PRIMARY KEY (Nrfact, Codpr),

FOREIGN KEY (Nrfact) REFERENCES Facturi,

FOREIGN KEY (Codpr) REFERENCES Produse;

După crearea unei tabele, putem crea și un index pentru aceasta:

CREATE INDEX Nume-index ON Nume-tabela;

(Lista-coloane)

Structura unei tabele poate fi modificată ulterior cu ajutorul unei comenzi ALTER TABLE:

ALTER TABLE Nume-tabela ADD/MODIFY/DROP;

Nume-coloana Specificatii ...

Este posibil să adăugăm o coloană nouă în tabelă, să ștergem o coloană existentă din tabelă, sau să modificăm specificațiile (de tip, de restricții etc.) ale unei coloane existente.

Încărcarea și actualizarea datelor

Introducerea datelor

Instrucțiunea Insert are următoarea sintaxă:

INSERT INTO Nume-tabela [ (Lista-coloane)]

VALUES (Lista-valori);

Datele pot fi introduse în mai multe moduri:

- se precizează lista coloanelor care vor primi valori, și în acest caz celelalte coloane vor primi fie valoarea NULL, fie valoarea implicită specificată la crearea tabelei; lista valorilor corespunde listei coloanelor;

Exemple:

INSERT INTO Departamente (Coddep, Dendep)

VALUES (101, 'Personal');

- nu se precizează lista coloanelor, caz în care trebuie specificate valorile în listă în conformitate cu lista coloanelor precizată la crearea tabelei; lista valorilor trebuie să fie completă (eventual cu argumente absente precizate explicit);

INSERT INTO Departamente

VALUES (101, 'Personal',);

INSERT INTO Angajati

VALUES (5001, 'Petrescu',101);

Actualizarea datelor

Instrucțiunea Update are următoarea sintaxă:

UPDATE Nume-tabela

SET Coloana = Valoare

[WHERE Conditie];

Sunt actualizate liniile din tabelă pentru care este verificată condiția specificată.

Atenție:

Dacă nu s-a specificat nici o condiție sunt actualizate toate liniile tabelei.

Exemplu:

UPDATE Departamente

SET Sefdep = 10

WHERE Coddep=100;

Ștergerea datelor

Instrucțiunea DELETE are sintaxa următoare:

DELETE FROM Nume-tabela

[WHERE Condiție];

Sunt șterse liniile din tabelă pentru care este verificată condiția specificată.

Atenție: dacă nu s-a specificat nici o condiție sunt șterse toate liniile tabelei.

Exemplu:

DELETE FROM Departamente

WHERE Departament='Personal';

Ion Cozac este lector la Universitatea "Petru Maior" din Târgu-Mureș și poate fi contactat prin e-mail la adresa: cozac@uttgm.ro

[cuprins]