Modelul relational - Modelul entitati-legaturi (Cheie primara, cheie externa)

Modelul relational

Ce este modelul relational? O caracterizare ar putea fi urmatoarea: modelul relational este o tehnica de descriere a datelor cu ajutorul tabelelor, si o tehnica de manipulare a acestora cu ajutorul unor operatori specifici calculului relational si / sau algebrei relationale, concretizati prin utilizarea unor limbaje specifice - cum ar fi QbE (Query by Example) sau SQL (Structured Query Language). Modelul relational este un model abstract care are la baza fundamente matematice solide (teoria algebrica a relatiilor). Principiile modelului relational au fost descrise prin 1969-1970 de catre E. F. Codd, cercetator la IBM. El a fost primul care a inteles ca rigoarea matematica poate fi folosita pentru a introduce principii solide in domeniul gestiunii bazelor de date - domeniu care era foarte deficitar la acest capitol.

Modelul entitati-legaturi

O arhitectura clasica a unei baze de date este organizata pe trei nivele: nivelul extern sau utilizator, nivelul conceptual si nivelul intern. Pentru a descrie nivelul conceptual folosim un model abstract numit model entitati-legaturi.

Elementul fundamental al acestui model este notiunea de entitate: acest termen generic desemneaza un obiect care face parte dintr-o clasa (multime) de entitati, toate aceste obiecte fiind similare ca structura, dar care pot fi deosebite prin proprietati specifice (pe care le vom numi in continuare atribute). Deoarece definitia este destul de vaga (imprecisa) vom da doua exemple preluate dintr-o problema (simplificata) de evidenta a facturilor, si dintr-o alta problema de evidenta a personalului:

- multimea clientilor unui magazin formeaza o clasa de entitati; toti acesti clienti au niste atribute: cod personal, nume, localitate (de domiciliu), adresa; fiecare client este o entitate identificabila prin codul personal al acestuia;

- multimea produselor aflate in evidenta magazinului formeaza o alta clasa de entitati; toate aceste produse pot fi caracterizate prin: cod produs, denumire, pret unitar; fiecare produs este o entitate identificabila prin codul acestuia;

- multimea departamentelor unei firme; orice departament se caracterizeaza prin: cod, denumire, sef;

- multimea angajatilor unei firme: fiecare angajat se caracterizeaza prin: numar legitimatie, nume, departamentul la care lucreaza.

Un alt element care caracterizeaza acest model este notiunea de legatura. Aceasta legatura (asociere) intre mai multe clase de entitati E₁, , E_n (nu neaparat distincte) presupune existenta unei multimi de valori (e₁, , e_n), unde e_i este multimea valorilor atributelor unei entitati din clasa E_i. In practica de cele mai multe ori intilnim legaturi intre doua clase de entitati (n=2); acestea se numesc legaturi binare.

Un exemplu concret preluat din evidenta personalului:

(101,'Personal',5001,5001,'Petrescu',101)

Primele trei valori sint preluate din clasa de entitati Departamente, urmatoarele trei din Personal. Se observa usor ca aceasta multime de valori este un element al produsului cartezian al celor doua multimi: Departamente si Personal. Dupa ce eliminam valorile care se repeta (aceasta operatie este numita proiectie) obtinem:

(101,'Personal',5001,'Petrescu')

Sa clasificam in continuare legaturile binare dupa cardinalitatea acestora (cite entitati din fiecare clasa intra in cadrul legaturii):

- legaturi de tip 1:1 (one-to-one); observam o astfel de legatura in cazul unei evidente a personalului, care indica faptul ca un departament este condus de un sef (un departament nu poate fi condus de mai multi sefi, o persoana nu poate conduce mai multe departamente);

- legaturi de tip 1:n (one-to-many); in evidenta personalului remarcam o astfel de legatura intre angajatii unui departament si departamentul in cauza (la un departament lucreaza mai multi angajati, un angajat nu poate lucra in cadrul mai multor departamente); la evidenta facturilor remarcam o legatura intre Facturi si Clienti (unui client i se pot intocmi mai multe facturi; nu se poate intocmi o aceeasi factura pentru mai multi clienti);

- legaturi de tip m:n (many-to-many); o astfel de legatura exista intre Clienti si Produse: un client poate cumpara mai multe produse, si in acelasi timp mai multi clienti pot cumpara un acelasi sortiment de produse.

Dupa ce vor fi prezentate fundamentele modelului relational, vom prezenta si tehnici de implementare a acestor tipuri de legaturi.

Domeniu, atribut, relatie

Un domeniu este o multime de valori scalare (atomice - care nu pot fi descompuse) de acelasi tip. Exemple: multimea codurilor personale, multimea numelor de clienti, multimea numelor de localitati s.a. Cu aceste valori se pot efectua mai multe operatii:

- cu majoritatea acestor valori se pot face comparatii;

- cu unele valori se pot face si alte operatii: o cantitate poate fi inmultita cu un pret (rezultind o valoare), un pret poate fi inmultit cu o valoare (in cazul unei reasezari) s.a.m.d.

Un atribut al unei clase de entitati este o caracteristica (proprietate) care ia valori intr-un anumit domeniu. Exemple:

- atributele clasei de entitati CLIENTI sint: cod client, nume client, localitate, adresa;

- atributele clasei de entitati PRODUSE sint: cod produs, denumire produs, pret unitar.

O relatie (in sens algebric) este o submultime a unui produs cartezian D₁ D₂ D_n, unde D_i sint domenii de valori (nu neaparat distincte). Aceasta a fost definitia folosita mult timp si pentru notiunea de relatie in domeniul bazelor de date - modelul relational. Unul dintre cei mai prestigiosi autori de lucrari in acest domeniu, C. J. Date propune o noua definitie pentru notiunea de relatie (in domeniul bazelor de date): aceasta se compune din doua parti:

- antetul relatiei, care este o multime de atribute definite fiecare pe cite un domeniu de valori (aceste domenii nu sint neaparat distincte):

- corpul relatiei, care este o multime de tuple (un tuplu este o generalizare a notiunii de cuplu); fiecare tuplu este definit ca o multimi de valori ale atributelor definite in cadrul antetului:

Valoarea m (numarul de atribute ale relatiei) reprezinta gradul relatiei, si valoarea n (numarul de tuple) reprezinta cardinalitatea relatiei.

Din definitia de mai sus a notiunii de relatie rezulta urmatoarele proprietati:

- atributele nu sint ordonate (ordinea lor nu este semnificativa) - rezulta din faptul ca antetul este definit ca o multime de atribute (intr-o multime ordinea elementelor nu este semnificativa);

- atributele sint distincte (chiar daca pot exista doua atribute definite pe acelasi domeniu) - elementele unei multimi sint distincte;

- in cadrul corpului relatiei tuplele nu sint ordonate - corpul relatiei este definit ca o multime de tuple;

- orice atribut are doar valori atomice; aceasta proprietate poate fi enuntata si astfel: la intersectia dintre o linie si o coloana se afla intotdeauna o singura valoare, si niciodata o colectie de valori; rezulta ca o relatie nu contine grupuri repetitive; spunem in acest caz ca relatia se afla in prima forma normala;

- nu exista tuple duplicate (deoarece corpul unei relatii este o multime de tuple).

Daca o linie din tabela Clienti contine valorile:

(80001, 'Ionescu', 'Bucuresti', 'Str Libertatii')

toate aceste valori se afla intr-o anumita relatie: ele definesc identitatea unui client, care are cod personal 80001, are numele 'Ionescu', domiciliaza in localitatea 'Bucuresti' la adresa 'Str Libertatii'.

Prezentam in continuare o corespondenta intre termenii formali (definiti mai sus) si termenii informali (cei folositi in mod curent in exploatarea bazelor de date relationale):

Relatie    Tabela

Tuplu Linie / inregistrare

Cardinalitate Numar de linii

Atribut    Coloana / Cimp

Grad Numar de coloane

Domeniu    Multime de valori valide

Corespondenta nu trebuie considerata ca o echivalenta, deoarece exista citeva deosebiri de care trebuie sa tinem seama (este de altfel una dintre diferentele pe care le sesizam intre teorie si practica):

- notiunea de relatie este o notiune teoretica, pe cind o tabela este un obiect concret, care are o anumita reprezentare in calculator - sub forma unui tablou bidimensional;

- intr-o relatie ordinea atributelor si a tuplelor nu este semnificativa; intr-o tabela exista o ordonare atit a coloanelor - data de ordinea acestora la crearea tabelei, cit si a liniilor - data de ordinea in care acestea au fost introduse, sau de ordinea unei chei care induce o anumita ordonare a liniilor in cadrul tabelei;

- o relatie este formata intotdeauna din tuple distincte; in multe cazuri o tabela poate avea linii duplicate.

Cheie primara, cheie externa

O cheie candidata a unei relatii R este o multime K de atribute cu urmatoarele proprietati:

- identificare unica: nu exista doua tuple distincte in R care sa aiba aceeasi valoare pentru setul de atribute K; cu alte cuvinte, multimea K de atribute identifica in mod unic fiecare tuplu al relatiei R;

- nereductibilitate: nu exista o submultime proprie a lui K (distincta de K) care sa aiba proprietatea de identificare unica.

O cheie este simpla daca este formata dintr-un singur atribut, si este compusa in caz contrar.

O relatie poate avea mai multe chei candidate; una dintre acestea se alege pentru a fi folosita in aplicatii ca si cheie de identificare a tuplelor. Cheia candidata folosita in acest scop se numeste cheie primara; de obicei se folosesc in acest scop acele chei care reprezinta coduri ale inregistrarilor memorate in baza de date. Un motiv important pentru a justifica o astfel de alegere este urmatorul: valorile de tip cod ocupa foarte putin spatiu in comparatie cu informatiile pe care le identifica, fapt care conduce la o economie importanta de spatiu in cazul crearii unui index, precum si la accelerarea regasirii informatiilor in cazul in care se fac corelatii intre tabele.

Sa analizam unul din exemplele prezentate:

- tabela Clienti are cheia primara Codcl, tabela Produse are cheia primara Codpr, tabela Facturi are cheia primara Nrfact; in toate aceste cazuri se verifica usor cele doua proprietati ale cheii primare - atit identificarea unica cit si ireductibilitatea sint evidente;

- tabela Detalii are cheia primara compusa din atributele Nrfact si Codpr; ireductibilitatea este evidenta si in acest caz, deoarece este posibil sa avem mai multe linii cu aceeasi valoare pentru Nrfact (cind un client cumpara mai multe produse cu aceeasi factura), si de asemenea este posibil sa avem mai multe linii cu aceeasi valoare pentru Codpr (cind mai multi clienti cumpara acelasi tip de marfa).

In continuare se defineste notiunea de cheie externa (sau cheie straina) in conexiune cu notiunea de cheie candidata. Sa facem precizarea ca in majoritatea situatiilor practice apar legaturi intre tabele care se materializeaza prin coincidenta valorilor cheilor primare si externe.

Fie R₂ o relatie. O cheie externa din R₂ este o multime Ek de atribute cu urmatoarele proprietati:

- exista o tabela R₁ (care poate sa coincida sau nu cu R₂) care are o cheie candidata Pk;

- fiecare valoare a setului Ek din R₂ coincide cu o valoare a setului Pk din R₁.

Sa analizam din nou unul din exemplele prezentate:

- tabela Facturi are o cheie externa Codcl, care este cheie primara in tabela Clienti

- tabela Detalii are doua chei externe: Nrfact care este cheie primara in tabela Facturi, si Codpr care este cheie primara in tabela Produse; am remarcat mai sus ca cele doua chei externe formeaza impreuna cheia primara a tabelei Detalii

Sa facem citeva precizari:

- prin definitie, fiecare valoare a unei chei externe trebuie sa se regaseasca printre multimea valorilor cheii candidate corespondente; reciproca nu este obligatorie: de exemplu, putem sa inregistram informatii in tabela Clienti despre un potential client, chiar daca acesta inca nu cumpara nimic; putem de asemenea sa inregistram informatii despre un produs pe care il aducem in magazin, si care inca nu este cumparat de nici un client;

- o cheie externa este simpla daca si numai daca cheia candidata corespondenta este simpla, si este compusa daca si numai daca cheia candidata corespondenta este compusa;

- fiecare atribut component al unei chei externe trebuie sa fie definit pe acelasi domeniu al componentei corespondente din cheia candidata;

- o valoare a unei chei externe reprezinta o referinta catre un tuplu care contine aceeasi valoare pentru cheia candidata corespondenta; se pune astfel problema integritatii referintei: o baza de date nu trebuie sa contina valori invalide pentru chei externe, altfel spus daca B refera pe A atunci A trebuie sa existe.

Ce se intimpla daca stergem din tabela Clienti un client pentru care avem citeva linii in tabela Facturi? Daca operatia de stergere se efectueaza fara nici un control, dupa stergere nu mai este respectata regula integritatii referintei. Pentru a preveni asemenea situatii, in tabela Facturi precizam la creare:

[ON DELETE option] [ON UPDATE option]

unde optiunea poate fi:

RESTRICTED daca dorim ca operatia ceruta pentru tabela Clienti sa fie respinsa;

CASCADES daca dorim ca operatia ceruta pentru tabela Clienti sa provoace operatii (actualizari sau stergeri) 'in cascada' - se actualizeaza (sau se sterg) toate liniile afectate din tabela Facturi; in acest al doilea caz se cere o atentie deosebita, deoarece o stergere a unei linii din tabela Facturi poate afecta linii din tabela Detalii

Cheile externe se folosesc pentru a implementa legaturile dintre entitati. Legaturile de tip one-to-one si one-to-many se implementeaza introducind in una din tabele o cheie externa, care va face legatura cu cheia primara din tabela corespondenta. O legatura de tip many-to-many se implementeaza introducind o tabela suplimentara care are o cheie primara compusa, fiecare element al cheii primare fiind o cheie externa. Cele doua exemple sint (speram) concludente in acest sens.

Valori NULL

Exista unele situatii cind regula integritatii referintei nu poate fi respectata. Sa consideram urmatorul exemplu foarte simplu: o baza de date pentru evidenta personalului compusa din doua tabele:

Departamente Coddep Dendep Sefdep

Angajati Nrleg Numeang Dep

Observam ca tabela Departamente are ca si cheie primara atributul Coddep, si cheie externa Sefdep, care este cheie primara in tabela Angajati. De asemenea tabela Angajati are ca si cheie primara atributul Nrleg, si cheie externa Dep, care este cheie primara in tabela Departamente. In momentul crearii celor doua tabele nu exista nici o informatie inregistrata. Daca dorim sa introducem informatii despre un departament operatia de inserare va fi respinsa deoarece nu avem informatiile despre seful departamentului respectiv. Daca dorim sa introducem informatii despre seful unui departament operatia de inserare va fi de asemenea respinsa deoarece nu avem informatiile despre departamentul respectiv.

Aceasta problema este rezolvata prin introducerea notiunii de valoare NULL; semnificatia acestei valori este valoare necunoscuta sau informatie absenta. In concordanta cu aceasta noua notiune regulile de integritate se completeaza astfel:

- integritatea entitatii: orice atribut al unei chei primare nu poate avea valoarea NULL;

- integritatea referintei: orice valoare a unei chei externe este fie NULL fie coincide cu o valoare a unei chei candidate corespondente.

Problema enuntata mai sus se poate rezolva in felul urmator:

- mai intii se introduc informatiile despre departament, dar fara sa se precizeze informatia despre seful departamentului;

- in continuare se introduc informatiile despre seful departamentului, si in acest moment se poate introduce si informatia despre departamentul la care acesta lucreaza;

- in final se actualizeaza informatia absenta din tabela Departamente

Crearea unei baze de date relationale in SQL

O baza de date relationala este o baza de date care este vazuta de utilizator ca o colectie de relatii (tabele) normalizate (aduse cel putin in forma intii normala).

Inainte de a prezenta instructiunile SQL pentru definirea unei baze de date sa facem citeva precizari. Numele limbajului (prescurtare de la Structured Query Language - limbaj structurat de interogare) este impropriu dupa cum se poate usor observa. Acest limbaj ne permite atit definirea datelor, cit si manipularea acestora: inserare, modificare, stergere, consultare (interogare). Orice operatie realizata de o instructiune SQL opereaza cu tabele SQL. O tabela SQL, spre deosebire de relatii, poate avea linii duplicate. De asemenea tabelele SQL au o ordine precizata a coloanelor (de la stinga la dreapta), in schimb ordinea liniilor nu este semnificativa. O tabela SQL se creeaza cu ajutorul unei comenzi CREATE TABLE:

CREATE TABLE Nume-tabela (Coloana Reprezentare [Valoare-implicita] [Restrictii], );

Reprezentare. Standardul SQL precizeaza mai multe tipuri de date scalare (detalii privind modul de reprezentare trebuie cautate in documentatia SGBD-ului care se foloseste in mod concret), unele dintre acestea fiind prezentate in continuare:

Char(n) - o coloana de siruri de maximum n caractere;

Integer - o coloana de valori intregi pozitive sau negative (de regula intregi pe 4 octeti);

Smallint - o coloana de valori intregi mici (de regula intregi pe 2 octeti);

Float(p) - o coloana de valori reprezentate in virgula mobila;

Decimal(p,q) - o coloana de valori zecimale, unde p este numarul total de pozitii pe care se reprezinta numarul, si q este numarul de cifre ale partii zecimale (punctul zecimal nu se reprezinta in mod explicit)

Date - o coloana de valori de tip data calendaristica, pentru reprezentarea carora se foloseste un format specific SGBD-ului folosit;

Time - o coloana de valori de tip ora exacta.

Valoare implicita. Pentru fiecare coloana se poate preciza o valoare implicita (care trebuie sa se incadreze in tipul de date definit la reprezentare), in cazul in care la inserarea unei linii in tabela nu este precizata o valoare pentru coloana respectiva. Daca nu a fost precizata o valoare implicita la creare si nici la inserare, atunci coloana respectiva va primi o valoare NULL, dar numai daca o valoare NULL este permisa. O valoare implicita se precizeaza astfel:

DEFAULT = valoare

Restrictii. O restrictie poate fi de tipul urmator:

NOT NULL - coloana respectiva nu poate primi valori NULL;

CHECK (conditie) - se precizeaza o conditie care va fi verificata in momentul inserarii unei linii in tabela, sau in momentul actualizarii unei linii: de exemplu

CHECK (PRETUNIT > 0)

restrictie de tip cheie candidata:

UNIQUE (Lista-de-coloane);

restrictie de tip cheie primara:

PRIMARY KEY (Lista-de-coloane);

restrictie de tip cheie externa:

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 pina acum pot fi urmatoarele:

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), CHECK (PRETUNIT > 0));

CREATE TABLE Facturi (Nrfact Integer, Dataf DATE, Codcl Integer, PRIMARY KEY (Nrfact), FOREIGN KEY (Codcl) REFERENCES Clienti);

CREATE TABLE Detalii (Nrfact Integer, Codpr Integer, Cant Integer, PRIMARY KEY (Nrfact, Codpr), FOREIGN KEY (Nrfact) REFERENCES Facturi, FOREIGN KEY (Codpr) REFERENCES Produse);

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

ALTER TABLE Nume-tabela ADD/MODIFY/DROP Nume-coloana Specificatii

Este posibil sa adaugam o coloana noua in tabela, sa stergem o coloana existenta din tabela, sau sa modificam specificatiile (de tip, de restrictii etc) ale unei coloane existente.

Incarcarea si actualizarea datelor

Introducerea datelor. Instructiunea Insert are sintaxa urmatoare:

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

VALUES (Lista-valori);

Datele pot fi introduse in mai multe moduri:

- se precizeaza lista coloanelor care vor primi valori, si in acest caz celelalte coloane vor primi fie valoarea NULL fie valoarea implicita specificata la crearea tabelei; lista valorilor corespunde cu lista coloanelor; exemplu:

INSERT INTO Departamente (Coddep, Dendep)

VALUES (101, 'Personal');

- nu se precizeaza lista coloanelor, caz in care trebuie specificate valorile in lista in conformitate cu lista coloanelor precizata la crearea tabelei; lista valorilor trebuie sa fie completa (eventual cu argumente absente precizate explicit); exemplu:

INSERT INTO Departamente VALUES (101, 'Personal',);

INSERT INTO Angajati VALUES (5001, 'Petrescu',101);

Actualizarea datelor. Instructiunea Update are sintaxa urmatoare:

UPDATE Nume-tabela

SET Coloana = Valoare

[WHERE Conditie];

Sint actualizate liniile din tabela pentru care este verificata conditia specificata. Atentie: daca nu s-a specificat nici o conditie sint actualizate toate liniile tabelei. Exemplu:

UPDATE Departamente

SET Sefdep = 10

WHERE Coddep=100;

Stergerea datelor. Instructiunea DELETE are sintaxa urmatoare:

DELETE FROM Nume-tabela

[WHERE Conditie];

Sint sterse liniile din tabela pentru care este verificata conditia specificata. Atentie: daca nu s-a specificat nici o conditie sint sterse toate liniile tabelei. Exemplu:

DELETE FROM Departamente

WHERE Departament='Personal';

Un model entitati-legaturi pentru evidenta personalului.

Am pus in evidenta cele doua tipuri de legaturi

existente intre Departamente si Personal.

Un exemplu de baza de date pentru evidenta personalului

DEPARTAMENTE PERSONAL

Coddep Dendep Sefdep Nrleg Nume Depart

100 Conducere 5000 5000 Popescu 100

101 Personal 5001 5001 Petrescu 101

102 Productie 5002 5002 Ionescu 102

5005 Carmen 100

5006 Adam 102

5007 Barbu 101

Un model entitati-legaturi pentru evidenta facturilor.

Am pus in evidenta, ca in exemplul de mai sus, pentru

fiecare clasa de entitati cheile primare si cheile externe.

Un exemplu de baza de date pentru evidenta facturilor

CLIENTI PRODUSE

Codcl Nume Localit Adresa Codpr Denpr Pretunit

Ionescu Bucuresti Str Libertatii 90001 Radio 1500

Vasilescu Cluj Aleea Carpati 90002 Casetofon 2500

Moldovan Tg-Mures Str Mihai Viteazu 90003 Televizor 3600

90004 Mixer 1200

90005 Picup 900

FACTURI DETALII

Nrfact Dataf Codcl Nrfact Codpr Cant

12.01.98 80001 00001 90001 2

15.02.98 80002 00001 90002 1

25.02.98 80001 00002 90001 1

07.03.98 80003 00002 90002 2

00002 90003 1

00003 90003 1

00004 90002 1

00004 90004 1