1

Baze de date

Note de curs

Florin Ostafi, Claudiu Lefter

2

Cuprins 1. Introducere

1.1 InformaŃii şi date 1.2 Sisteme de gestiune a bazelor de date 1.3 Modele de date 1.4 Limbaje şi utilizatori 1.5 Avantaje şi dezavantaje ale sistemelor de gestiune a bazelor de date

Partea I. Baze de date relaŃionale 2. Modelul relaŃional

2.1 Structura modelului relaŃional 2.2 Constrângeri de integritate

3. Algebra relaŃională 3.1 Reuniune, intersecŃie, diferenŃă 3.2 OperaŃia de redenumire (ρ) 3.3 Operatorul de selecŃie (σ) 3.4 Operatorul de proiecŃie (π) 3.5 Operatorul joncŃiune – „join” 3.6 Interogări în algebra relaŃională 3.7 EchivalenŃa expresiilor algebrice 3.8 Valori NULL în algebra relaŃională 3.9 Vederi

4. Calculul relaŃional 4.1 Calculul relaŃional pe domenii 4.2 Calculul relaŃional pe tupluri

5. SQL 5.1 Definirea datelor în SQL 5.2 Interogări în SQL 5.3 Modificarea datelor în SQL 5.4 Alte definiŃii de date în SQL 5.5 Controlul accesului la baza de date 5.6 Utilizarea SQL în limbajele de programare

Partea a II-a. Proiectarea bazelor de date 6. Tehnici de proiectare şi modele

6.1. Procesul de proiectare a bazei de date 6.2. Modelul Entitate – RelaŃie (Entity – Relantionship model)

7. Proiectarea conceptuală 7.1 Extragerea şi analiza cerinŃelor 7.2 Strategii de proiectare 7.3 Calitatea unei scheme conceptuale. 7.4 Metodă de abordare a proiectării conceptuală

8. Proiectarea logică 8.1 Analiza performanŃelor schemei E-R 8.2 Restructurarea schemei E-R

8.3 Translarea în modelul relaŃional

3

Capitolul 1. Introducere 1.1 InformaŃii şi date

În cadrul fiecărei activităŃi disponibilitatea informaŃiei şi capacitatea de a o gestiona în mod eficient sunt esenŃiale. Din acest motiv, fiecare organizaŃie dispune de un sistem informaŃional care gestionează informaŃia necesară realizării funcŃiilor respectivei organizaŃii. Răspândirea tehnicii de calcul în aproape toate activităŃile umane generează o continuă creştere în computerizarea sistemelor informaŃionale.

În sistemele bazate pe tehnică de calcul, informaŃia este înregistrată sub formă de date, care necesită o interpretare pentru a furniza informaŃii. Nu se poate da o definiŃie exactă a conceptului de dată, precum şi a diferenŃei dintre dată şi informaŃie. Ce putem spune este ca data nu are nicio semnificaŃie, dar odată interpretată şi corelată convenabil, ea furnizează informaŃii ce îmbogăŃesc cunoaşterea asupra lumii înconjurătoare.

Exemplu

Şirul de caractere Popescu Ion şi numărul 123456 scrise pe o foaie de hârtie sunt două date care nu au nicio semnificaŃie. Dacă hârtia este trimisă ca răspuns la întrebarea „Cine este şeful departamentului de cercetare şi care este numărul său de telefon?” atunci datele pot fi interpretate şi utilizate pentru îmbogăŃirea cunoştinŃelor cu informaŃia „persoana Popescu Ion este şeful departamentului de cercetare şi numărul său de telefon este 123456”. Având introdusă noŃiunea de dată, ne putem îndrepta spre conceptul de bază de date,

elementul principal al cursului. Se pot da mai multe definiŃii ale unei baze de date, cea mai generală dintre ele fiind: o

bază de date este o colecŃie de date utilizată pentru a reprezenta informaŃiile de interes pentru un sistem informaŃional.

1.2 Sisteme de gestiune a bazelor de date Primele sisteme software dedicate gestiunii datelor au apărut la sfârşitul anilor ’60. În

lipsa software-ului specific, gestiunea se realiza cu ajutorul limbajelor tradiŃionale de programare cum ar fi C şi FORTRAN sau, mai recent, prin intermediul limbajelor orientate obiect (C++, Java).

Abordarea convenŃională a gestiunii datelor exploata prezenŃa fişierelor pentru stocarea permanentă a datelor. Sistemele de gestiune a bazelor de date bazate pe fişiere au constituit o încercare de început de a computeriza sistemul de îndosariere manual, în scopul de a accesa mai eficient datele stocate.

Un fişier este un set de înregistrări, care conŃin date între care există relaŃii logice. Structura fizică şi stocarea fişierelor de date şi a înregistrărilor sunt definite în cadrul aplicaŃiei. Un fişier permite stocarea şi căutarea datelor, dar furnizează doar un mecanism simplu de acces şi partajare. Astfel, procedurile scrise într-un limbaj de programare sunt complet autonome, fiecare definind şi utilizând unul sau mai multe fişiere „private”. Datele ce prezintă interes pentru mai multe programe sunt multiplicate de atâtea ori câte programe utilizator există, introducând în acest fel redundanŃa şi inconsistenŃa datelor.

4

Limitările sistemelor de gestiune a bazelor de date bazate pe fişiere - separarea şi izolarea datelor - dublarea datelor - dependenŃa de date - incompatibilitatea fişierelor - interogarea / proliferarea fixă a programelor aplicaŃie

Separarea şi izolarea datelor Atunci când datele sunt izolate în fişiere separate, procesul de combinare a datelor devine mai complicat.

Exemplu Fişiere disponibile

- fişier cu informaŃii despre proprietăŃile disponibile pentru închiriere - fişier cu informaŃii despre chiriaşi

CerinŃă: listă a tuturor caselor care îndeplinesc pretenŃiile unui potenŃial chiriaş

OperaŃii care trebuie efectuate: - se caută chiriaşii care preferă tipul „casă” - se caută proprietăŃile de tip „casă” care satisfac cerinŃele chiriaşilor

Programatorul de aplicaŃii trebuie să realizeze sincronizarea a două fişiere, pentru a fi sigur ca datele extrase sunt corecte. Această dificultate este amplificată dacă se cer date din mai mult de două fişiere.

Dublarea datelor

După cum am precizat anterior, datele ce prezintă interes pentru mai multe aplicaŃii sunt multiplicate de atâtea ori câte aplicaŃii utilizator există. Dublarea datelor poate duce la alterarea integrităŃii acestora, atunci când modificarea datelor redundante se face doar în fişierele accesate de o aplicaŃie utilizator.

DependenŃa de date

Structura fizică şi stocarea fişierelor de date şi a înregistrărilor sunt definite în codul aplicaŃiei. Din acest motiv, efectuarea de modificări în structura existentă este dificilă. Spre exemplu, dacă se modifică dimensiunea unui câmp din structura unui fişier, programatorul trebuie să identifice toate programele ce accesează fişierul respectiv, să le modifice şi să le testeze din nou, operaŃii consumatoare de timp şi supuse apariŃiei erorilor.

Formate de fişiere incompatibile

Deoarece structura fişierelor este încorporată în programele aplicaŃiei, ea este dependentă de limbajul în care sunt scrise acestea. Structura unui fişier poate fi diferită în urma generării lui de către limbaje de programare diferite. Incompatibilitatea directă a unor astfel de fişiere face dificil ă prelucrarea lor simultană.

Interogarea / proliferarea fixă a programelor aplicaŃie

Sistemele bazate pe fişiere sunt în mare măsură dependente de programatorul de aplicaŃii, neexistând facilităŃi pentru realizarea interogărilor neplanificate. Pe de altă parte au proliferat fişierele şi programele aplicaŃie, fapt ce a dus în cele din urmă la programe inadecvate sau ineficiente pentru îndeplinirea cerinŃelor utilizatorilor, cu o documentaŃie limitată şi dificil de întreŃinut.

5

Motivul pentru care au fost create bazele de date a fost, în cea mai mare parte, depăşirea inconvenientelor prezentate anterior. DefiniŃie. Un sistem de gestiune a bazelor de date (SGBD) este un sistem software capabil să

gestioneze colecŃii de date mari, partajate şi persistente şi să asigure corectitudinea şi securitatea datelor. De asemenea, ca orice produs software, un sistem de gestiune a bazelor de date trebuie să fie eficient şi să producă rezultatul scontat.

DefiniŃie. O bază de date (BD) este o colecŃie de date gestionate de un sistem de gestiune a

bazelor de date. Sistemele de gestiune a bazelor de date şi bazele de date au o serie de caracteristici printre care cele enumerate şi dezvoltate mai jos:

• Bazele de date pot fi mari, în sensul că pot conŃine mii de miliarde de octeŃi şi în general depăşesc memoria principală disponibilă. Prin urmare, un SGBD trebuie să gestioneze datele în memoria secundară. Această proprietate impune de fapt unui SGBD să poată gestiona datele fără să fie limitat de dimensiunea lor, singura limitare fiind dată de capacitatea dispozitivelor de stocare.

• Bazele de date sunt partajate, în sensul că diverse aplicaŃii şi utilizatori trebuie să aibă posibilitatea de a obŃine accesul la datele de interes. Accesul partajat la date al mai multor utilizatori ce operează simultan este garantat de SGBD prin utilizarea unui mecanism special denumit „controlul concurenŃei”.

• Bazele de date sunt persistente - BD au o durată medie de viaŃă ce nu este limitată de o singură execuŃie a programelor utilizator, pe când datele gestionate de un program în memoria principală au o durată de viaŃă cuprinsă între începutul şi sfârşitul execuŃiei programului, nefiind persistente.

• SGBD asigură corectitudinea datelor – are capacitatea de a conserva conŃinutul BD (sau cel puŃin permite reconstituirea acesteia) în cazul unor defecŃiuni hardware sau erori software. Pentru a îndeplini această cerinŃă, SGBD-urile pun la dispoziŃie funcŃii specifice de backup şi recuperare a datelor.

• SGBD asigură securitatea datelor – fiecare utilizator, recunoscut prin intermediul unui nume de utilizator specific accesului la SGBD, poate efectua numai anumite operaŃii asupra datelor. Acest lucru se realizează cu ajutorul unui mecanism de autorizare.

• SGBD-urile trebuie să fie eficiente – ele trebuie să finalizeze operaŃiile utilizând cantitatea adecvată de resurse (timp şi spaŃiu) pentru fiecare utilizator. Această caracteristică se bazează atât pe tehnicile utilizate în implementarea SGBD-ului, cât şi pe modul de proiectare a produsului respectiv.

• SGBD-urile măresc productivitatea – capacitatea sistemului cu BD de a conduce la realizarea scopurilor utilizatorilor. Această definiŃie este generică şi nu corespunde unei anumite funcŃii a SGBD-urilor, dat fiind că un SGBD pune la dispoziŃia utilizatorilor diverse servicii şi funcŃii. Sarcina proiectării unei BD şi a aplicaŃiilor ce o utilizează vizează garantarea unei bune productivităŃi a întregului sistem.

1.3 Modele de date Un model de date este o combinaŃie de elemente destinată organizării datelor. Fiecare

model de date pune la dispoziŃia utilizatorilor mecanisme de structurare, similare constructorilor type din limbajele de programare, care permit definirea unor tipuri noi de date pe baza tipurilor elementare, predefinite.

6

Modelul de date relaŃional, cel mai răspândit model de date, pune la dispoziŃie

constructorul relaŃie, oferind astfel posibilitatea organizarea datelor sub forma unei colecŃii de înregistrări având structură fixă. O relaŃie se reprezintă sub forma unui tabel, în care liniile coincid cu înregistrările, iar coloanele corespund câmpurilor înregistrării; ordinea în care apar liniile şi coloanele nu este relevantă.

Exemplu Datele referitoare la cursurile universitare şi titularii lor, precum şi inserarea cursurilor în prospectul diverselor specializări pot fi organizate cu ajutorul a două relaŃii (tabele) CURSURI şi PROSPECT:

CURSURI Curs Titular Baze de date ReŃele de calculatoare Tehnici de programare

Popescu Ionescu Anton

PROSPECT Specializare Curs An

Automatică Automatică Calculatoare Calculatoare Calculatoare

ReŃele de calculatoare Baze de date Tehnici de programare ReŃele de calculatoare Baze de date

4 3 3 4 4

Modelul relaŃional a fost propus la începutul anilor ’70, iar sistemele reale bazate pe

modelul relaŃional au apărut la începutul anilor ’80.

Pe lângă modelul relaŃional au mai fost definite alte trei modele:

• Modelul de date ierarhic – utilizează structuri de tip arbore şi ierarhie şi a fost definit în faza de început a dezvoltării SGBD-urilor (anii ’60). Este utilizat şi acum în multe sisteme din motive de continuitate.

• Modelul de date reŃea – cunoscut şi sub numele CODASYL (Conference of Data Systems Languages) utilizează grafuri pentru organizarea datelor; a fost dezvoltat în anii ’70.

• Modelul de date obiect – a apărut în anii ’80 şi rezolvă unele limitări ale modelului relaŃional; extinde în domeniul bazelor de date paradigma programării orientate obiect.

Modelele prezentate anterior sunt denumite „logice”, pentru a sublinia faptul că, deşi

structurile utilizate sunt abstracte, ele reflectă o organizare particulară (arbore, graf, tabel, obiect).

Alte modele de date, cunoscute sub numele de modele conceptuale, au fost introduse pentru a descrie datele într-o manieră independentă de modelul logic. Spre deosebire de modelele logice, cele conceptuale nu sunt disponibile în SGBD-urile comerciale. Modelele conceptuale sunt utilizate în faza preliminară a proiectării bazelor de date, pentru o analiză cât mai bună a aplicaŃiei, fără implicaŃii de implementare. Un exemplu de model conceptual este modelul Entitate - RelaŃie, ce va fi prezentat în cadrul acestui curs.

1.3.1 Scheme şi instanŃe Într-o bază de date există o parte formată din caracteristici ale datelor care nu se modifică

în timp, numită schema bazei de date şi o parte care se modifică în timp, numită instanŃă (starea) bazei de date, alcătuită din valorile actuale din baza de date.

7

Cele două relaŃii din exemplul anterior au o structură fixă; relaŃia CURSURI are două coloane (atribute) care fac referire la cursuri, respectiv titulari.

Schema unei relaŃii conŃine numele relaŃiei, urmat de numele atributelor:

CURSURI(Curs, Titular) InstanŃa unei relaŃii este formată dintr-o colecŃie de linii ale relaŃiei respective, care variază în timp. În cazul relaŃiei CURSURI, instanŃa acestei relaŃii este dată de următoarele trei perechi:

Baze de date ReŃele de calculatoare Tehnici de programare

Popescu Ionescu Anton

1.3.2 Nivele de abstractizare în SGBD-uri Arhitectura unui SGBD este împărŃită pe trei nivele, cunoscute sub numele de nivelul logic, intern şi extern; fiecare nivel are asociată o schemă.

• Schema logică reprezintă o descriere a întregii baze de date prin prisma modelului logic adoptat de SGBD (relaŃional, ierarhic, reŃea sau obiect)

• Schema internă descrie implementarea schemei logice prin prisma structurilor de stocare fizică. De exemplu, o relaŃie poate fi organizată la nivel fizic sub forma unui fişier secvenŃial sau a unui fişier secvenŃial cu indici.

• Schema externă reprezintă descrierea unei porŃiuni a bazei de date prin prisma modelului logic. O schemă externă poate oferi o organizare diferită a datelor cu scopul de a reflecta un punct de vedere al unui anumit utilizator sau grup de utilizatori. În acest fel este posibil ca unei singure scheme logice să-i fie asociate diverse scheme externe: fiecare schemă externă va furniza o anumită vedere asupra bazei de date. În majoritatea sistemelor, nivelul extern nu apare explicit, dar este posibilă definirea unor relaŃii derivate, numite vederi.

Exemplu Un student de la Automatică este interesat de cursurile oferite în cadrul acestei specializări. Această informaŃie este prezentă în relaŃia AUTOMATICĂ, derivată din relaŃia PROSPECT. AUTOMATIC Ă Specializare Curs An Automatică Automatică

ReŃele de calculatoare Baze de date

4 3

Schemelor externe li se pot asocia mecanisme pentru autorizarea accesului, în sensul că

un utilizator poate fi autorizat să manipuleze doar datele descrise prin prisma schemei externe asociată.

1.3.3 IndependenŃa datelor Arhitectura multi-nivel descrisă anterior garantează independenŃa datelor, o caracteristică

majoră a SGBD-urilor. Această proprietate permite utilizatorilor şi programelor să facă referire la date la un nivel înalt de abstractizare, ignorând detaliile de implementare. IndependenŃa datelor prezintă două aspecte: independenŃa fizică şi cea logică.

8

IndependenŃa fizică permite interacŃiunea cu SGBD-ul independent de aspectele fizice ale datelor. Este posibilă modificarea organizării fi şierelor ce implementează relaŃiile sau modificarea alocării fizice a fişierelor la dispozitivele de stocare fără a influenŃa descrierile de nivel înalt ale datelor şi programele ce utilizează acele date.

IndependenŃa logică garantează că interacŃiunea cu nivelul extern al bazei de date este independentă de nivelul logic. Spre exemplu, se poate modifica o schemă externă fără a fi necesară modificarea schemei logice. De asemenea, se poate modifica nivelul logic, păstrând intactă schema externă de interes pentru un anume utilizator.

Este de subliniat faptul că accesul la baza de date se face numai prin intermediul nivelului extern (care poate coincide cu nivelul logic). SGBD-ul este cel care traduce operaŃiile în termenii nivelelor corespunzătoare.

1.4 Limbaje şi utilizatori SGBD-urile oferă o gamă largă de limbaje pentru gestiunea datelor şi implică, pe

parcursul ciclului lor de viaŃă, un spectru larg de utilizatori. łinând cont de distincŃia dintre scheme şi instanŃe putem face o diferenŃiere similară între

limbajele bazei de date.

• limbajul de definire al datelor (LDD) – utilizat pentru definirea schemelor logice, externe şi fizice precum şi pentru definirea autorizărilor de acces;

• limbajul de manipulare al datelor (LMD) – utilizat pentru interogarea şi modificarea instanŃelor unei baze de date.

Remarcă. Unele limbaje (SQL) oferă facilităŃilor ambelor limbaje (LDD şi LMD) într-o formă integrată.

În funcŃie de modul de exploatare a bazei de date, utilizatorii se pot împărŃi în:

• administratorul bazei de date (DBA) – persoana responsabilă de proiectarea, controlul şi administrarea bazei de date. DBA-ul mediază între cerinŃele diverse, adesea conflictuale, exprimate de utilizatori, asigurând controlul partajat asupra datelor. În particular, el este responsabil pentru garantarea serviciilor, asigurând corectitudinea sistemului şi gestionând autorizările de acces la date.

• programatorii de aplicaŃii – definesc şi creează programele ce accesează baza de date. Ei utilizează LMD sau alte unelte pentru generarea interfeŃelor cu baza de date.

• utilizatorii obişnuiŃi – cei care utilizează efectiv baza de date. Aceştia pot fi împărŃiŃi la rândul lor în două categorii:

- utilizatori finali – utilizează tranzacŃii (spre exemplu programe ce realizează activităŃi frecvente şi predefinite

- utilizatori ocazionali – capabili să utilizeze limbaje interactive pentru a accesa baza de date, formulând interogări (sau actualizări) de diferite tipuri. Ei pot fi specialişti în limbajul pe care îl utilizează şi interacŃionează frecvent cu baza de date. Termenul „ocazional” se referă la faptul că interogările nu sunt predefinite.

1.5 Avantaje şi dezavantaje ale sistemelor de gestiune a bazelor de date Vom încheia această introducere rezumând caracteristicile esenŃiale ale bazelor de date

şi ale sistemelor de gestiune ale bazelor de date şi punctând avantajele şi dezavantajele acestora.

9

• SGBD-urile permit datelor să fie considerate drept resurse comune ale organizaŃiei, disponibile tuturor membrilor autorizaŃi.

• Baza de date furnizează un model standardizat şi precis al acelei părŃi a lumii reale care prezintă interes pentru organizaŃie, model folosit în aplicaŃiile existente şi care, cu extensiile necesare, poate fi folosit în aplicaŃii viitoare.

• SGBD-urile oferă posibilitatea unui control centralizat al datelor.

• Partajarea bazelor de date permite reducerea redundanŃei şi inconsistenŃei datelor.

• IndependenŃa datelor, caracteristica fundamentală a SGBD-urilor, favorizează dezvoltarea de aplicaŃii mai flexibile şi mai uşor de modificat.

Utilizarea SGBD-urilor are şi câteva aspecte negative, cum ar fi:

• SGBD-urile sunt produse scumpe, complexe şi foarte diferite de multe alte unelte software. Introducerea lor necesită un efort financiar atât direct (costul produsului) cât şi indirect (resurse hardware şi software, pregătirea personalului etc.).

• SGBD-urile furnizează, în forma standard, un set de servicii care au un anumit cost. În cazurile în care câteva dintre aceste servicii nu sunt neapărat necesare, e dificilă extragerea serviciilor necesare la un moment dat şi acest fapt poate duce la ineficienŃă.

În concluzie, putem spune că pot exista situaŃii în care folosirea SGBD-urilor nu este necesară şi anume cazurile în care există un singur utilizator (sau mai mulŃi, dar care nu au nevoie de acces concurent la date). Oricum, dezvoltarea actuală a SGBD-urilor a dus la sisteme mai eficiente şi mai fezabile, şi la arhitecturi din ce în ce mai ieftine, crescând astfel posibilitatea dezvoltării aplicaŃiilor cu SGBD-uri.

10

Partea I Baze de date relaŃionale

Capitolul 2. Modelul relaŃional Majoritatea sistemelor cu baze de date se bazează pe modelul relaŃional care a fost propus de E.F. Codd în 1970 într-o publicaŃie ştiinŃifică, cu scopul de a furniza o bază pentru independenŃa datelor. Adoptarea modelului relaŃional ca standard nu a fost imediată, acest lucru fiind motivat de nivelul înalt de abstractizare. Deşi primele prototipuri de sisteme relaŃionale au fost create încă din anii ’70, primele sisteme relaŃionale au apărut pe piaŃă în anii ’80.

2.1 Structura modelului relaŃional Conceptele ce stau la baza modelului relaŃional sunt cele de relaŃie şi tabel, care diferă ca

şi noŃiuni dar sunt strâns legate. NoŃiunea de relaŃie este formală, venind din domeniul matematicii, mai exact din domeniul teoriei mulŃimilor, în timp ce noŃiunea de tabel este o noŃiune simplă şi intuitivă. DefiniŃie. Fie date mulŃimile D1 şi D2. Produsul cartezian dintre D1 şi D2 (notat 1 2D D× ) se

defineşte ca fiind mulŃimea perechilor ordonate ( , )1 2v v , 1 1v D∈ , 2 2v D∈ .

DefiniŃie. O relaŃie pe mulŃimile D1 şi D2 (numite domeniile relaŃiei) este o submulŃime a produsului cartezian 1 2D D× .

ObservaŃii .

DefiniŃiile de mai sus nu specifică în clar dacă mulŃimile D1 şi D2 sunt finite sau nu. În practică relaŃiile trebuie să fie finite deoarece bazele de date trebuie stocate în sisteme computerizate de dimensiuni finite.

În acelaşi timp însă este de dorit ca domeniile să aibă dimensiuni infinite, astfel încât să putem presupune existenŃa unei valori care nu e prezentă în baza de date.

Din acest motiv, vom presupune acolo unde este necesar, că bazele de date sunt alcătuite din relaŃii finite, definite pe domenii infinite. DefiniŃiile de mai sus pot fi generalizate. Fie n 0> mulŃimi , ,...,1 2 nD D D , nu neapărat

distincte. • Produsul cartezian ...1 2 nD D D× × × este dat de mulŃimea n-tuplurilor , ,...,1 2 nv v v , unde

, ,i iv D i 1 n∈ = .

• O relaŃie matematică pe domeniile , ,...,1 2 nD D D este o submulŃime a produsului

cartezian ...1 2 nD D D× × × .

Gradul produsului cartezian şi al relaŃiei (dat de numărul componentelor produsului

cartezian) este n. Cardinalitatea relaŃiei este dată de numărul n-tuplurilor. Deoarece o relaŃie este de fapt o mulŃime, putem face următoarele observaŃii:

• între n-tupluri nu există o ordine predefinită; • n-tuplurile unei relaŃii sunt distincte unele de celelalte; din acest motiv, un tabel

reprezintă o relaŃie doar dacă liniile sale sunt diferite între ele. Pe fiecare n-tuplu se defineşte o relaŃie de ordine adică fiecare componenta i a fiecărui

tuplu corespunde domeniului i. Această ordonare între domeniile unei relaŃii reprezintă o

11

caracteristică nesatisfăcătoare a conceptului de relaŃie (aşa cum este definit în matematică) din punctul de vedere al posibilităŃii organizării şi utilizării datelor (vezi figura 2.1).

Real Madrid Liverpool Real Madrid Roma

Liverpool Milan Roma Milan

3 2 1 0

1 0 2 1

Fig. 2.1 Exemplu de relaŃie în care semnificaŃia datelor este dată de poziŃia lor în cadrul tuplului

Pentru înlăturarea acestui inconvenient a fost introdusă o notaŃie non-poziŃională, prin asocierea de nume domeniilor unei relaŃii, referite drept atribute. Atributele descriu rolurile jucate de domenii (vezi figura 2.2).

EchipaGazda EchipaOaspete GoluriGazde GoluriOaspeti Real Madrid Liverpool Real Madrid Roma

Liverpool Milan Roma Milan

3 2 1 0

1 0 2 1

Fig. 2.2 RelaŃie cu atribute

Pentru formalizarea conceptelor introduse anterior, definim funcŃia :DOM →X D care asociază fiecărui atribut A∈ X un domeniu ( )DOM A ∈ D .

DefiniŃie. Un tuplu definit pe o mulŃime de atribute X este o funcŃie t care asociază fiecărui atribut A∈ X o valoare din domeniul ( )DOM A .

DefiniŃie. O relaŃie pe o mulŃime de atribute X este o mulŃime de tupluri definite pe X .

NotaŃie. Fie t un tuplu definit pe o mulŃime de atribute X şi A∈ X un atribut. Prin t[A] se notează valoarea tuplului t în domeniul ( )DOM A .

Exemplu

Fie t primul tuplu din relaŃia prezentată în figura 2.2. În acest caz, putem spune că

t[EchipaOaspete]=Liverpool Aceeaşi notaŃie va fi utilizată şi în cazul unor mulŃimi de atribute.

Exemplu t[EchipaOaspete, GoluriOaspeti]=Liverpool,1

O relaŃie poate fi utilizată pentru organizarea într-o manieră relevantă a datelor necesare unei aplicaŃii. De obicei, nu este suficientă o singură relaŃie şi de aceea bazele de date sunt formate din mai multe relaŃii, ale căror tupluri conŃin valori comune atunci când acest lucru este necesar pentru stabilirea unor corespondenŃe (vezi figura 2.3).

STUDENTI NrInreg Nume Prenume DataNastere 276545 Ionescu Maria 25/11/1980 485745 Popescu Ana 23/04/1981 200768 Georgescu Paul 12/02/1981 587614 Luca Radu 10/10/1980 937653 Maftei Lucian 01/12/1980

12

EXAMENE

Student Nota Curs

276545 8 01 276545 9 04 937653 9 01 200768 9 04

CURSURI

Cod Denumire Titular 01 Fizica Melinte 03 Chimie Mardare 04 Chimie Dascalu

Fig. 2.3 Exemplu de bază de date relaŃională

DefiniŃie. Schema unei relaŃii este formată din numele relaŃiei R şi o mulŃime de atribute

1 2 nX { A ,A ,...,A }= şi se notează R(X). Fiecărui atribut îi este asociat un domeniu.

DefiniŃie. Schema bazei de date este formată dintr-o mulŃime de scheme de relaŃii:

1 1 2 2 n n{ R ( X ),R ( X ),...,R ( X )}=R

DefiniŃie. InstanŃa unei relaŃii (pe scurt – relaŃia) având schema R(X) este dată de mulŃimea r a tuplurilor definite pe mulŃimea de atribute X.

DefiniŃie. InstanŃa bazei de date (pe scurt – baza de date) având schema

1 1 2 2 n n{ R ( X ),R ( X ),...,R ( X )}=R este mulŃimea 1 2 nr { r ,r ,...,r }= de relaŃii în care fiecare

ir , i 1,n= este o relaŃie definită pe schema i iR ( X ).

Exemplu Schema bazei de date din figura 2.3 este

R = {STUDENTI(NrInreg, Nume, Prenume, DataNastere), EXAMENE(Student, Nota, Curs),

CURSURI(Cod, Denumire, Titular)}

Structura modelului relaŃional este simplă şi puternică. În acelaşi timp însă impune u anumit grad de rigiditate, prin aceea ca informaŃiile trebuie reprezentate sub forma unor tupluri omogene de date; în particular, putem reprezenta în cadrul unei relaŃii doar tupluri ce corespund schemei relaŃiei. În practică există cazuri în care datele disponibile nu corespund cu exactitate formatului ales.

Exemplu

Se consideră schema PERSOANĂ(Nume, Prenume, Adresă, Telefon). Pot exista tupluri pentru care valoarea atributului Telefon nu este disponibilă

Pentru rezolvarea problemei indisponibilităŃii valorilor, s-a extins conceptul de relaŃie în sensul includerii posibilităŃii ca fiecare atribut al unui tuplu să poată lua fie valori din domeniul asociat, fie o valoare specială, denumită valoare null. Valoarea null indică absenŃa

13

informaŃiei şi este o valoare suplimentară, ce nu este conŃinută de domeniu. La definirea unei relaŃii se pot specifica acele atribute care acceptă valori null.

2.2 Constrângeri de integritate

În multe cazuri ne confruntăm cu situaŃii când nu orice mulŃime de tupluri în cadrul unei scheme reprezintă informaŃii corecte pentru aplicaŃie. Pentru înlăturarea unor astfel de situaŃii (de incorectitudine a informaŃiilor) a fost introdus conceptul de constrângere de integritate, ca fiind o proprietate ce trebuie satisfăcută de toate instanŃele corecte ale bazei de date. O constrângere poate fi privită ca un predicat ce asociază valoarea adevărat sau fals fiecărei instanŃe. Se pot defini mai multe constrângeri pentru o bază de date şi vom considera corecte (sau legale) acele instanŃe care satisfac toate constrângerile impuse.

În funcŃie de elementele bazei de date ce intervin într-o constrângere de integritate, acestea se pot clasifica astfel:

1. constrângeri intra-relaŃionale - sunt definite pe o singură relaŃie a bazei de date şi pot fi de două tipuri:

a. constrângeri la nivel de tuplu – pot fi evaluate pe fiecare tuplu, independent de celelalte tupluri;

b. constrângeri la nivel de domeniu (la nivel de valoare) – impun restricŃii asupra domeniului unui atribut.

2. constrângeri inter-relaŃionale – implică mai multe relaŃii

Exemplu

Fie baza de date din figura următoare

STUDENTI NrInreg Nume Prenume DataNastere 200768 Georgescu Paul 12/02/1981 937653 Maftei Lucian 10/10/1980 937653 Luca Radu 01/12/1980

EXAMENE Student Nota Promovat Curs

200768 11 DA 05 937653 4 DA 01 937653 6 DA 04 276545 7 DA 01

CURSURI

Cod Denumire Titular 01 Fizica Melinte 03 Chimie Mardare 04 Chimie Dascalu

Fig.2.4 Bază de date cu informaŃii incorecte

14

• constrângeri la nivel de tuplu:

- în primul tuplu al relaŃiei EXAMENE avem ca rezultat la un examen nota 11;

- în al doilea tuplu al relaŃiei EXAMENE un student este considerat promovat la examen deşi nota sa este 4.

• constrângeri la nivel de domeniu

- pentru atributul Notă din relaŃia EXAMENE, numai valorile cuprinse între 1 şi 10 sunt permise;

- ultimele două tupluri ale relaŃiei STUDENTI conŃin informaŃii pentru doi studenŃi diferiŃi dar cu acelaşi număr de înregistrare, identificarea studenŃilor fiind astfel ambiguă.

• constrângeri inter-relaŃionale

- al patrulea tuplu al relaŃiei EXAMENE are, pentru atributul Student, o valoare care nu apare printre numerele de înregistrare din relaŃia STUDENTI;

- primul tuplu al relaŃiei EXAMENE are, pentru atributul Curs, o valoare care nu apare printre codurile cursurilor din relaŃia CURSURI.

În continuare vom examina constrângerile pe tuplu, constrângerile de chei (cele mai importante constrângeri intra-relaŃionale) şi constrângerile de referinŃă (cele mai importante constrângeri inter-relaŃionale).

Constrângeri pe tuplu

Constrângerile pe tuplu reprezintă condiŃii impuse valorilor fiecărui tuplu, independent de celelalte tupluri.

O sintaxă posibilă pentru aceste constrângeri permite definirea unor expresii booleene (folosind conectorii AND, OR şi NOT) ale căror atomi compară valorile atributelor implicate în constrângeri sau expresii aritmetice folosind valorile atributelor.

Exemplu

Expresia ce descrie prima constrângere de tuplu încălcată în exemplul prezentat în figura 2.4:

(Nota ≥ 1) AND (Nota ≤ 10)

Expresia ce descrie a doua constrângere de tuplu violată în exemplul prezentat în figura 2.4:

(Promovat = ’DA’) AND (Nota ≥ 5)

DefiniŃia permite construirea unor expresii de complexitate ridicată, singura condiŃie fiind ca aceste expresii să utilizeze valorile unui singur tuplu.

Exemplu

Fie dată schema

PLATĂ(Dată, Sumă, Deduceri, Net)

15

Se poate defini o constrângere care impune condiŃia ca suma netă să fie egală cu diferenŃa dintre suma totală şi deduceri:

Net = Sumă - Deduceri

Constrângeri de chei

O cheie este o mulŃime de atribute ce ajută la identificarea în mod unic a tuplurilor unei relaŃii.

DefiniŃie. O mulŃime de atribute K este o super-cheie a relaŃiei r dacă r nu conŃine două tupluri distincte t1 şi t2 astfel încât t1[K] = t 2[K] .

DefiniŃie. O mulŃime K de atribute este o cheie a relaŃiei r dacă mulŃimea K este o super-cheie minimală (i.e. nu există altă super-cheie K’ a lui r astfel încât K ' K⊂ ).

Exemplu

Se consideră următoarea relaŃie

STUDENT NrInreg Nume Prenume DataNastere Facultate 284328 Ionescu Maria 29/04/59 AC 296328 Ionescu Ana 29/04/59 TCM 587614 Ionescu Lucian 01/05/61 Textile 934856 Popescu Lucian 01/05/61 AC 965536 Popescu Lucian 05/03/58 TCM

Fig.2.5 Exemplu de relaŃie pentru evidenŃierea cheilor

• MulŃimea {NrInreg} este super-cheie; este de asemenea şi super-cheie minimală, deoarece conŃine un singur atribut, deci {NrInreg} este o cheie. • MulŃimea {Nume, Prenume, DataNastere} este o super-cheie; deoarece nici una din submulŃimi nu este super-cheie, {Nume, Prenume, DataNastere} este o cheie. • MulŃimea {NrInreg, Facultate} este super-cheie, dar nu este o super-cheie minimală, deoarece submulŃimea {NrInreg} este ea însăşi super-cheie minimală. Deci {NrInreg, Facultate} nu este o cheie. • Deoarece nu există două tupluri cu aceleaşi valori pentru atributele Nume şi Facultate, putem afirma că mulŃimea {Nume, Facultate} este o super-cheie. Deoarece există tupluri având aceeaşi valoare atât pentru atributul Nume cât şi pentru atributul Facultate, mulŃimea {Nume, Facultate} este o cheie. MulŃimea {Nume, Facultate} identifică în mod unic tuplurile acestei relaŃii. Apare însă întrebarea: Putem afirma acest lucru în cazul general ? Răspunsul este, în mod evident, că nu, deoarece pot exista studenŃi cu acelaşi nume şi care au terminat aceeaşi facultate. Spunem că, în acest caz, mulŃimea {Nume, Facultate} este, prin şansă, cheie a relaŃiei. Pentru schema STUDENTI (NrInreg, Nume, Prenume, DataNastere, Facultate) se pot stabili două constrângeri ce impun următoarele chei:

NrInreg Nume, Prenume, DataNastere

RelaŃia din figura 2.5 satisface ambele constrângeri de chei anterioare.

16

Faptul că pentru orice relaŃie se poate stabili cel puŃin o cheie garantează accesul la toate datele din baza de date şi identificarea lor unică. Mai mult decât atât, permite stabilirea unor legături între datele conŃinute de diverse relaŃii.

Exemplu

Se consideră baza de date din figura 2.3. RelaŃia EXAMENE face referire la studenŃii din relaŃia STUDENTI prin intermediul atributului NrInreg şi la cursurile din relaŃia CURSURI prin atributul Cod. NrInreg este cheia relaŃiei STUDENTI iar Cod este cheia relaŃiei CURSURI. Se observă faptul că valorile atributelor cheie sunt utilizate pentru referirea conŃinutului altor relaŃii.

O cheie primară este cheia prin intermediul căreia se realizează referinŃe între relaŃii.

Alte constrângeri impuse cheii unei relaŃii vizează utilizarea valorilor null pentru atributele ce compun cheia. Mai exact, este interzisă utilizarea valorilor null pentru cheile primare, în timp ce ele pot fi admise pentru celelalte chei.

În practică pot apărea situaŃii în care nu există atribute ale căror valori să fie disponibile pentru o cheie primară. În aceste cazuri se va introduce un atribut suplimentar care va fi generat şi asociat fiecărui tuplu în momentul inserării în relaŃia corespunzătoare.

Constrângeri de referinŃă

O constrângere de referinŃă (sau cheie străină) între o mulŃime de atribute X ale relaŃiei R1 şi altă relaŃie R2 este satisfăcută dacă valorile fiecărui tuplu din R1 corespunzătoare mulŃimii X se regăsesc printre valorile cheii primare a relaŃiei R2. Se întâlnesc două situaŃii:

a) Cheia relaŃiei R2 este unică şi conŃine un singur atribut B.

Constrângerea de referinŃă între mulŃimea X (formată din atributul A) şi relaŃia R2 este satisfăcută dacă

1 1t R∀ ∈ cu 1t [ A] NULL≠ , 2 2t R∃ ∈ astfel încât 1 2t [ A] t [ B ]= .

b) Cheia relaŃiei R2 este unică şi coincide cu o mulŃime K de atribute.

Pentru stabilirea unei constrângeri între X şi R2 trebuie specificată o anumită ordine pentru ambele mulŃimi (X şi K). Indicând atributele în ordine, 1 2 pX A A ...A= , 1 2 pK B B ...B= ,

constrângerea este satisfăcută dacă

1 1t R∀ ∈ cu 1 it [ A ] NULL≠ , i 1, p= , 2 2t R∃ ∈ astfel încât 1 i 2 it [ A ] t [ B ]= , i 1, p= .

Exemplu

Se consideră baza de date din figura următoare

CONTRAVENTIE Cod Data Cadru Judet NrInmat 143256 25/10/92 567 IS 02 AAA 987554 26/10/92 456 IS 02 AAA 987557 26/10/92 456 IS 03 BBB 630876 15/10/92 456 VS 03 BBB 539856 12/10/92 567 VS 03 BBB

CADRE NrInreg Nume Prenume 567 Dascalescu Eugen 456 Ionescu George 638 Popescu Dan

17

AUTOVEHICUL NrInmat Judet Proprietar Adresa 03 BBB IS Maftei Eduard Nicolina 30 01 CCC IS Maftei Eduard Nicolina 30 02 AAA IS Luca Marian Primaverii 4 03 BBB VS Melinte Dan Primaverii 17

Fig.2.6 Bază de date cu constrângeri de referinŃă

Se definesc următoarele constrângeri de referinŃă: - între atributul Cadru al relaŃiei CONTRAVENłIE şi relaŃia CADRE; - între atributele NrInmat şi Judet ale relaŃiei CONTRAVENłIE şi relaŃia

AUTOVEHICUL, ordinea atributelor în cheia relaŃiei AUTOVEHICUL fiind NrInmat , Judet.

Se poate observa că baza de date din figura 2.6 respectă ambele constrângeri.

În cazul b) discuŃia despre ordinea atributelor poate părea excesivă, dat fiind că se poate obŃine o corespondenŃă prin intermediul numelor atributelor. În general, nu ne putem baza pe o astfel de corespondenŃă (prin intermediul numelor atributelor) şi va trebui să recurgem la impunerea unei anumite ordini a atributelor.

Exemplu

Să presupunem că dorim să includem într-o relaŃie numărul de înregistrare şi judeŃele celor două autovehicule implicate într-un accident. O schemă posibilă ar fi:

ACCIDENT(Cod, Judet1, NrInmat1, Judet2, NrInmat2,...)

CorespondenŃa implicată de constrângerea de referinŃă cu relaŃia AUTOVEHICUL nu va putea fi stabilită prin numele atributelor, deoarece cele din relaŃia ACCIDENT diferă de numele atributelor cheii relaŃiei AUTOVEHICUL. Va trebui să specificăm că referinŃa asociază Judet1 la Judet şi NrInmat1 la NrInmat , respectiv Judet2 la Judet şi NrInmat2 la NrInmat .

Exemplu Baza de date din figura următoare nu respectă cele două constrângeri menŃionate anterior.

ACCIDENT Cod Jud1 NrInmat1 Jud2 NrInmat2 … 6207 IS 03 BBB BC 02 DDD … 6974 BC 02 AAA BC 02 DDD …

AUTOVEHICUL NrInmat Judet Proprietar Adresa 01 CCC IS Maftei Eduard Nicolina 30 02 AAA BC Luca Marian Primaverii 4 03 BBB IS Melinte Dan Primaverii 17

Fig.2.7 Bază de date care nu satisface constrângerile de referinŃă

ObservaŃii

• în cazul relaŃiilor cu mai multe chei, se va alege o cheie primară şi toate referirile trebuie redirectate spre ea.

18

• există sisteme de gestiune a bazelor de date care nu permit indicarea în mod explicit a unei chei primare. In aceste cazuri, constrângerea de referinŃă trebuie să indice explicit atributele ce formează cheia la care se face referire. Probleme propuse

1. Se consideră baza de date din figura următoare:

PACIENT

Cod Nume Prenume A102 Popescu Maria B372 Popa Mihai

B543 Ionescu Ioana

B444 Ionescu Radu

S555 Vasile Ion

INTERNARE

Pacient Intrare Iesire Sectie A102 2/05/2004 9/05/2004 A A102 2/12/2004 2/01/2005 A

S555 5/10/2004 3/12/2004 B

B444 1/12/2004 1/01/2005 B

S555 5/10/2004 1/112004 A

MEDIC

Numar Nume Prenume Sectie 203 Dumitrescu Mihai A 574 Georgescu Stefan B

461 Barbu Petre B

530 Petrescu George C

405 Filipescu George A

501 Barbulescu Stefan A

SECTIE

Cod Nume Specialist A Chirurgie 203 B Pediatrie 574

C Medicala 530

PuneŃi în evidenŃă cheile şi constrângerile de referinŃă din baza de date prezentată. PrecizaŃi dacă respectivele constrângeri sunt satisfăcute de toate bazele de date care au aceeaşi schemă cu baza de date din figură. PrecizaŃi care atribute pot admite valori null.

2. Se consideră următoare informaŃii referitoare la managementul împrumuturilor dintr-o bibliotecă personală: proprietarul împrumută cărŃi prietenilor, care se înregistrează prin intermediul numelor (astfel încât să se evite repetiŃiile); o carte este referită prin titlul său (nu există două cărŃi cu acelaşi titlu); când clientul împrumută o carte, se înregistrează data împrumutului şi proprietarul fixează data returnării;

DefiniŃi o schema relaŃională pentru reprezentarea informaŃiilor de mai sus, precizând domeniile adecvate pentru atribute şi o instanŃă sub formă tabelară. PrecizaŃi cheia sau cheile relaŃiei.

3. ReprezentaŃi prin intermediul unei relaŃii sau a mai multora informaŃiile conŃinute în mersul trenurilor dintr-o staŃie: numărul trenului, ora sosirii, ora plecării, punctul de plecare, destinaŃia finală, tipul de tren şi opririle de pe parcurs.

4. DefiniŃi o schemă a unei baze de date în care se organizează informaŃiile referitoare la o companie care are angajaŃi (fiecare cu codul numeric personal, nume, prenume şi data naşterii) şi subsidiari (fiecare cu cod, ramură şi director, care este angajat). Fiecare angajat lucrează pentru un subsidiar. IndicaŃi cheile şi constrângerile de referinŃă ale schemei. ArătaŃi o instanŃă a bazei de date şi verificaŃi dacă se respectă constrângerile.

19

Capitolul 3. Algebra relaŃională InformaŃiile de interes pentru aplicaŃiile de gestiune a datelor se pot reprezenta prin intermediul relaŃiilor. Limbajele pentru specificarea operaŃiilor necesare interogării şi actualizării datelor reprezintă o componentă esenŃială pentru fiecare model de date. O actualizare poate fi văzută ca o funcŃie care, pornind de la o bază de date produce o altă bază de date (fără a-i modifica schema). O interogare poate fi privită ca fiind o funcŃie care returnează o relaŃie, plecând de la o bază de date. În continuare vom vedea mai întâi care sunt fundamentele limbajelor de interogare şi actualizare şi apoi vom studia limbajele ce sunt recunoscute de sistemele de gestiune a bazelor de date comerciale. Algebra relaŃională este un limbaj procedural în care funcŃiile pentru extragerea datelor sunt specificate prin descrierea procedurii ce trebuie urmată în sensul obŃinerii unui rezultat. Algebra relaŃională se bazează pe o colecŃie de operatori ce sunt aplicaŃi relaŃiilor, producând relaŃii. Pentru formularea unor interogări complexe se vor construi expresii ce implică mai mulŃi operatori. Operatorii algebrici relaŃionali se împart în următoarele clase:

• Operatori clasici din teoria mulŃimilor – reuniune, intersecŃie, diferenŃă;

• Operatori de redenumire, selecŃie, proiecŃie;

• Operatorul join (joncŃiune), împreună cu variantele sale – joncŃiune naturală, produs cartezian, theta joncŃiune şi joncŃiune externă.

3.1 Reuniune, intersecŃie, diferenŃă După cum am mai precizat anterior, relaŃiile sunt mulŃimi de tupluri omogene şi de aceea

are sens definirea operatorilor clasici de reuniune, intersecŃie şi diferenŃă. În cele ce urmează, vom permite aplicarea acestor operatori doar asupra perechilor de relaŃii definite pe aceleaşi atribute.

Reuniunea a două relaŃii r1(X) şi r2(X) este relaŃia notată 1 2r r∪ care conŃine tupluri ce

aparŃin fie lui r1 fie lui r2, fie ambelor relaŃii r1 şi r2.

IntersecŃia dintre relaŃiile r1(X) şi r2(X) este relaŃia notată 1 2r r∩ formată din tuplurile

comune relaŃiilor r1 şi r2.

DiferenŃa dintre relaŃiile r1(X) şi r2(X) este relaŃia notată 1 2r r− care conŃine tuplurile din

r1 care nu se regăsesc în r2.

3.2 OperaŃia de redenumire (ρρρρ) Limitările impuse operatorilor standard din teoria mulŃimilor pot fi restrictive în anumite

situaŃii. Exemplu

Să considerăm cele două relaŃii din figura 3.1

20

TATA_COPIL Tata Copil Adam Dan Adam Marian Radu Cristi Radu Catalin

MAMA_COPIL Mama Copil Eva Dan Eva Lucian Maria Cristi Carmen Catalin

TATA_COPIL ∪∪∪∪ MAMA_COPIL ??

Figura 3.1 Reuniune logică, dar incorectă

Ar avea sens executarea unei reuniuni între cele două relaŃii cu scopul de a obŃine toate perechile ’părinte – copil’ din baza de date. Acest lucru nu este însă posibil deoarece atributul denumit de noi din instinct ’părinte’ are numele ’tata’ într-o relaŃie şi numele ’mama’ în cealaltă relaŃie. Pentru a rezolva problema prezentată în exemplul anterior se introduce un operator

specific algebrei relaŃionale, al cărui scop este de a adapta numele atributelor atunci când este necesară aplicarea unui operator din teoria mulŃimilor. Acest operator este operatorul de redenumire, numele lui indicând faptul că modifică numele atributelor lăsând intact conŃinutul relaŃiei.

Exemplu

TATA_COPIL Tata Copil Adam Dan Adam Marian Radu Cristi Radu Catalin

Parinte Tata←ρ (TATA_COPIL)

Parinte Copil Adam Dan Adam Marian Radu Cristi Cristi Iacob

Fig.3.2 Exemplu de redenumire

Operatorul de redenumire modifică numele atributului Tata din relaŃia TATA_COPIL în Parinte.

DefiniŃie. Fie r o relaŃie definită pe mulŃimea X de atribute şi fie Y o altă mulŃime de atribute având aceeaşi cardinalitate ca şi X. Fie secvenŃele ordonate 1 2 kA A ...A şi 1 2 kB B ...B formate din

atributele mulŃimii X respectiv Y. Redenumirea

A A ...A B B ...B1 2 k 1 2 k( r )←ρ

conŃine pentru fiecare tuplu t din r un tuplu t’ definit pe Y astfel încât ' i it [ B ] t[ A ]= , i 1,k= .

În practică, cele două secvenŃe 1 2 kA A ...A şi 1 2 kB B ...B vor conŃine doar atributele ce

urmează a fi redenumite şi noile lor nume. 3.3 Operatorul de selecŃie (σσσσ) Rezultatul unei selecŃii conŃine tuplurile relaŃiei operand ce satisfac condiŃia asociată

relaŃiei. \

21

Exemplu Se consideră relaŃia ANGAJAT din figura 3.3.

ANGAJAT Nume Prenume Varsta Salariu Ionescu Maria 25 2000 Popescu Lucia 40 3000 Diaconescu Nicu 36 4500 Ionescu Marin 40 3900

Virsta 30 Salariu 4000< >σ ∨∨∨∨ ( ANGAJAT )

Nume Prenume Varsta Salariu Ionescu Maria 25 2000 Diaconescu Nicu 36 4500

Fig.3.3 Exemplu de selecŃie După cum se poate observa, sunt selectate tuplurile din relaŃia ANGAJAT care îndeplinesc condiŃia: Varsta < 30 sau Salariu > 4000.

DefiniŃii .

Fiind dată o relaŃie r definită pe o mulŃime de atribute X, spunem că o formulă propoziŃională F definită pe X este o formulă compusă din condiŃii atomice de tipul A Bθ sau A cθ legate prin conectorii ∨ (OR), ∧ (AND) şi ¬ (NOT), în care:

• θ este un operator de comparaŃie ( , , , , ,= ≠ < ≤ > ≥ );

• A şi B sunt atribute din X compatibile (i.e. conŃin valori pentru care are sens comparaŃia);

• c este o constantă compatibilă cu domeniul atributului A.

Fiind dată formula propoziŃională F şi tuplul t, se defineşte valoarea de adevăr a lui F pe t:

• A Bθ este adevărată dacă şi numai dacă t[ A] este în relaŃia θ cu t[ B ] ;

• A cθ este adevărată dacă şi numai dacă t[ A] este în relaŃia θ cu c;

• 1 2F F∨ , 1 2F F∧ şi 1F¬ au semnificaŃia uzuală.

O selecŃie F ( r )σ produce o relaŃie care are aceleaşi atribute ca relaŃia r şi care conŃine

acele tupluri din r pentru care F este adevărată.

3.4 Operatorul de proiecŃie (ππππ)

DefiniŃie. Fie dată relaŃia r definită pe mulŃimea de atribute X şi o submulŃime Y a lui X. ProiecŃia relaŃiei r pe Y (notată Y( r )π ) reprezintă mulŃimea tuplurilor definite pe Y obŃinute din

tuplurile lui r luând în considerare numai valorile corespunzătoare atributelor din Y:

Y( r ) { t[Y ] / t r }π = ∈

În general rezultatul unei proiecŃii conŃine un număr de tupluri cel mult egal cu numărul de tupluri din operand.

În figurile 3.4, 3.5 sunt prezentate situaŃiile în care numărul de tupluri ale rezultatului este egal, respectiv mai mic decât numărul de tupluri ale operandului.

22

Exemplu

ANGAJAT Nume Prenume Varsta Salariu Ionescu Maria 25 2000 Popescu Lucia 40 3000 Diaconescu Nicu 36 4500 Ionescu Marin 45 3900

Varsta , Salariuπ (ANGAJAT)

Varsta Salariu 25 2000 40 3000 36 4500 45 3900

Fig.3.4 Exemplu de proiecŃie cu acelaşi număr de tupluri ca şi operandul

ANGAJAT Nume Prenume Departament Sef Ionescu Maria Vanzari Luca Popescu Lucia Vanzari Luca Diaconescu Maria Personal Damian Ionescu Marin Personal Damian

SeftDepartamen,π (ANGAJAT)

Departament Sef Vanzari Luca Personal Damian

Fig.3.5 Exemplu de proiecŃie cu mai puŃine tupluri decât operandul

PropoziŃie. Y( r )π conŃine acelaşi număr de tupluri ca şi r dacă şi numai dacă Y este o super-

cheie pentru r. DemonstraŃie.

• Fie Y super-cheie pentru r. Rezultă că r nu conŃine perechi de tupluri egale pe Y, deci fiecare tuplu va contribui la rezultatul proiecŃiei.

• Dacă proiecŃia are acelaşi număr de tupluri ca şi operandul rezultă că fiecare tuplu din r a contribuit la proiecŃie cu valori diferite. Aceasta înseamnă că r nu conŃine perechi de tupluri egale pe Y, deci Y este o super-cheie.

3.5 Operatorul joncŃiune – „join” Operatorul joncŃiune permite realizarea unei conexiuni între datele conŃinute de diverse

relaŃii. Există două versiuni principale ale acestui operator, care, oricum, se pot obŃine una din cealaltă.

JoncŃiunea naturală (⊳⊲ ) – „natural join” JoncŃiunea naturală corelează datele din relaŃii diferite pe baza valorilor egale asociate

atributelor cu acelaşi nume. DefiniŃie. Fie r1(X1) şi r2(X2) două relaŃii. JoncŃiunea naturală r1⊳⊲ r2 este o relaŃie definită pe X1X2 (reuniunea dintre X1 şi X2) astfel încât:

1 2 1 2 1 1 2 2 1 1 2 2r r { t definit pe X X / t r , t r , astfel incat t[ X ] t sit[ X ] t }= ∃ ∈ ∃ ∈ = =⊳⊲

Pe scurt putem scrie

1 2 1 2 1 1 2 2r r { t definit pe X X / t[ X ] r si t[ X ] r }= ∈ ∈⊳⊲

DefiniŃia confirmă faptul că tuplurile relaŃiei rezultat provin din combinarea tuplurilor din operanzi având valori egale pentru atributele comune. Notăm 1,2 1 2X X X= ∩ mulŃimea

atributelor comune. CondiŃiile 1 1t[ X ] t= şi 2 2t[ X ] t= implică (deoarece 1,2 1X X⊆ şi

1,2 2X X⊆ ) 1,2 1 1,2t[ X ] t [ X ]= şi 1,2 2 1,2t[ X ] t [ X ]= , deci 1 1,2 2 1,2t [ X ] t [ X ]= . Fie n gradul

23

joncŃiunii naturale dintre relaŃiile r1 şi r2, n1 gradul relaŃiei r1 şi n2 gradul relaŃiei r2; atunci

1 2n n n≤ + .

Exemplu

CONTRAVENTIE Cod Data Cadru Judet NrInmat 143256 25/10/92 567 IS 02 AAA 987554 26/10/92 456 IS 02 AAA 987557 26/10/92 456 IS 03 BBB 630876 15/10/92 456 VS 03 BBB 539856 12/10/92 567 VS 03 BBB

AUTOVEHICUL NrInmat Judet Proprietar Adresa 03 BBB IS Maftei Eduard Nicolina 30 01 CCC IS Maftei Eduard Nicolina 30 02 AAA IS Luca Marian Primaverii 4 03 BBB VS Melinte Dan Primaverii 17

CONTRAVENTIE ⊳⊲ AUTOVEHICUL

Cod Data Cadru Judet NrInmat Proprietar Adresa 143256 25/10/92 567 IS 02 AAA Luca Marian Primaverii 4 987554 26/10/92 456 IS 02 AAA Luca Marian Primaverii 4 987557 26/10/92 456 IS 03 BBB Maftei Eduard Nicolina 30 630876 15/10/92 456 VS 03 BBB Melinte Dan Primaverii 17 539856 12/10/92 567 VS 03 BBB Melinte Dan Primaverii 17

Fig.3.5 RelaŃiile CONTRAVENTIE şi AUTOVEHICUL şi joncŃiunea lor naturală

Se observă faptul că joncŃiunea naturală a celor două relaŃii s-a obŃinut prin combinarea fiecărui tuplu din CONTRAVENłIE cu exact un tuplu din AUTOVEHICUL:

• cu cel mult unul deoarece atributele Judet şi NrInmat formează o cheie a relaŃiei AUTOVEHICUL;

• cu cel puŃin unul datorită constrângerii de referinŃă dintre atributele Judet şi NrInmat din CONTRAVENTII şi relaŃia AUTOVEHICUL.

DefiniŃie. Fie r1(X1) şi r2(X2) două relaŃii. Spunem că joncŃiunea naturală 1 2r r⊳⊲ este completă dacă

1 1 1 2 1 1t r , t r r , astfel incat t[ X ] t∀ ∈ ∃ ∈ =⊳⊲ şi

2 2 1 2 2 2t r , t' r r , astfel incat t'[ X ] t∀ ∈ ∃ ∈ =⊳⊲ .

Exemplu

r 1 Angajat Departament Ionescu vanzari Balint productie Baltag productie

r 2 Departament Sef productie Manole vanzari Burlacu

r 1 ⊳⊲ r 2

Angajat Departament Sef Ionescu vanzari Burlacu Balint productie Manole Baltag productie Manole

Fig.3.6 Exemplu de joncŃiune naturală completă

24

Notă. Există posibilitatea ca în cazul unei joncŃiuni rezultatul să nu conŃină nici un tuplu. În cealaltă extremă se află situaŃia când fiecare tuplu al unui operand se poate combina cu fiecare tuplu din cel de-al doilea operand. În acest caz cardinalitatea relaŃiei rezultat este dată de produsul cardinalităŃilor celor doi operanzi. Rezumând cazurile expuse putem spune că joncŃiunea dintre r1 şi r2 conŃine un număr de tupluri cuprins între 0 şi 1 2r r× . Mai mult decât atât:

• dacă joncŃiunea este completă atunci |1 2 1 2| r r | max(| r ,| r |)≥⊳⊲ ;

• dacă 1 2X X∩ conŃine o cheie pentru r2 atunci |1 2 1| r r | | r≤⊳⊲ ;

• dacă 1 2X X∩ este cheie primară pentru r2 şi există constrângeri de referinŃă între

1 2X X∩ din r1 şi r2 atunci |1 2 1| r r | | r=⊳⊲

JoncŃiunea externă – „outer join” Faptul că operatorul de joncŃiune exclude tuplurile unei relaŃii ce nu au corespondent în

celălalt operand este avantajos în unele situaŃii şi dezavantajos în altele. În acest sens a fost introdus operatorul de joncŃiune externă, care asigură prezenŃa în relaŃia rezultat a tuturor tuplurilor unei relaŃii, acestea fiind completate cu valori NULL atunci când nu au corespondent în cealaltă relaŃie.

Acest operator are trei variante : • joncŃiune externă la stânga – extinde doar tuplurile primului operand; • joncŃiune externă la dreapta – extinde doar tuplurile celui de-al doilea operand; • joncŃiune externă completă – extinde toate tuplurile.

Exemplu

r 1

Angajat Departament Ionescu vanzari Balint productie Luca productie

r 2

Departament Sef productie Manole achizitii Burlacu

r 1 ⊳⊲ LEFT r 2

Angajat Departament Sef Ionescu vanzari null Balint productie Manole Luca productie Manole

r 1 ⊳⊲ RIGHT r 2

Angajat Departament Sef Balint productie Manole Luca productie Manole null achizitii Burlacu

r 1 ⊳⊲ FULL r 2

Angajat Departament Sef Ionescu vanzari null Balint productie Manole Luca productie Manole null achizitii Burlacu

Fig.3.7 Exemple de joncŃiuni externe

25

JoncŃiune n-ară, intersecŃie şi produs cartezian

ProprietăŃi ale joncŃiunii naturale:

• comutativitatea - 1 2 2 1r r r r=⊳⊲ ⊳⊲ ;

• asociativitate - 1 2 3 1 2 3( r r ) r r ( r r )=⊳⊲ ⊳⊲ ⊳⊲ ⊳⊲ .

łinând cont de aceste proprietăŃi putem scrie următoarele secvenŃe de joncŃiuni fără

paranteze: n

1 2 n i 1 ir r ... r sau r=⊳⊲ ⊳⊲ ⊳⊲ ⊳⊲ - joncŃiune n-ară.

Până in acest moment nu s-au făcut nici un fel de precizări asupra mulŃimilor X1 şi X2 de atribute. Cele două mulŃimi pot fi egale sau disjuncte.

Dacă X1 = X2 atunci joncŃiunea coincide cu intersecŃia:

1 1 2 2 1 1 2 2r ( X ) r ( X ) r ( X ) r ( X )= ∩⊳⊲

Într-adevăr, din definiŃie, 1 2r r⊳⊲ conŃine tuplurile t definite pe 1 2 1X X X∪ = astfel

încât 1 1t[ X ] r∈ şi 2 2t[ X ] r∈ . Deoarece 1 2X X= rezultă că 1 2t r r∈ ∩ , care este tocmai

definiŃia intersecŃiei.

Dacă 1 2X X∩ = φ , rezultatul joncŃiunii este definit pe reuniunea 1 2X X şi fiecare tuplu

va fi derivat din două tupluri, câte unul din fiecare operand. CondiŃia ca tuplurile să aibă aceleaşi valori în atributele comune va fi satisfăcută întotdeauna. În acest caz joncŃiunea devine produs cartezian.

Theta – joncŃiune şi echi – joncŃiune În general produsul cartezian nu prezintă interes deoarece combină tuplurile celor doi

operanzi într-o manieră lipsită de semnificaŃie (vezi figura 3.8).

Exemplu

ANGAJAT Angajat Proiect Ionescu A Balint A Balint B

PROIECT Cod Nume A Venus B Marte

ANGAJAT ⊳⊲PROIECT

Angajat Proiect Cod Nume Ionescu A A Venus Balint A A Venus Balint B A Venus Ionescu A B Marte Balint A B Marte Balint B B Marte

Figura 3.8 Produs cartezian

Din această cauză a fost introdu un alt operator, cel de theta – joncŃiune, care este de fapt

un produs cartezian urmat de o selecŃie (vezi figura 3.9):

1 F 2 F 1 2r r ( r r )= σ⊳⊲ ⊳⊲ ,

unde F este condiŃia de selecŃie.

26

Exemplu

ANGAJAT Angajat Proiect Ionescu A Balint A Balint B

PROIECT Cod Nume A Venus B Marte

P r o i e c t = C o dP r o i e c t = C o dP r o i e c t = C o dP r o i e c t = C o dσσσσ

(ANGAJAT ⊳⊲PROIECT)

Angajat Proiect Cod Nume Ionescu A A Venus Balint A A Venus Balint B B Marte

Figura 3.9 Produs cartezian urmat de o selecŃie

DefiniŃie. O theta – joncŃiune în care condiŃia de selecŃie F este o conjuncŃie de atomi de egalitate, fiecare atom implicând câte un atribut din fiecare operand se numeşte echi – joncŃiune. A treia relaŃie din figura 3.9 a fost obŃinută ca rezultat al unei echi – joncŃiuni. Din punct de vedere practic, theta şi echi – joncŃiunea au o importanŃă deosebită. Aceasta deoarece majoritatea sistemelor de gestiune a bazelor de date nu exploatează avantajele numelor atributelor în combinarea relaŃiilor. De fapt, interogările SQL corespund echi – joncŃiunilor, în timp ce joncŃiunea naturală a devenit disponibilă doar în versiunile recente de SQL. Este de notat faptul că joncŃiunea naturală poate fi înlocuită cu succesiunea redenumire, echi – joncŃiune şi proiecŃie.

Exemplu Fie r1(ABC) şi r2(BCD) două relaŃii. 1 2r r⊳⊲ poate fi exprimată cu ajutorul operatorilor

mai sus amintiŃi astfel: 1) se redenumesc atributele în scopul obŃinerii unor relaŃii cu scheme disjuncte

B',C' B,C 2( r )←ρ ;

2) se aplică operatorul de echi – joncŃiune, impunând condiŃia de egalitate atributelor redenumite

1 B B' C C' B',C' B,C 2r ( ( r ))= ∧ = ←ρ⊳⊲

3) se aplică operatorul de proiecŃie pentru a elimina atributele „duplicat”

A,B,C,D 1 B B' C C' B',C' B,C 2( r ( ( r )))= ∧ = ←π ρ⊳⊲

3.6 Interogări în algebra relaŃională O interogare poate fi definită ca fiind o funcŃie care, aplicată asupra unei instanŃe a unei baze de date, produce o relaŃie. Mai exact, dată o schemă R a unei baze de date, o interogare este o funcŃie care, pentru fiecare instanŃă r a lui R, produce o relaŃie definită pe o mulŃime X de atribute. În algebra relaŃională, interogările unei scheme R de baze de date sunt formulate cu ajutorul unor expresii, ale căror atomi sunt relaŃii din R.

Exemple Se consideră o bază de date formată din relaŃiile:

ANGAJAT (NrInreg, Nume, Varsta, Salariu)

SUPERVIZOR (NrSup, NrAng)

27

În figura 3.10 este dată o bază de date definită pe această schemă.

ANGAJAT NrInreg Nume Varsta Salariu 101 Maria Ionescu 34 40 103 Maria Balint 23 35 104 Lucia Popescu 38 61 105 Nicu Luca 44 38 210 Marcel Burlacu 49 60 231 Alin Lupu 50 60 252 Nicu Luca 44 70 301 Andrei Popa 34 70 375 Maria Ionescu 50 65

SUPERVIZOR NrSup NrAng 210 101 210 103 210 104 231 105 301 210 301 231 375 252

Fig.3.10 Bază de date pentru exemplificarea interogărilor în algebra relaŃională

1) Să se găsească numerele de înregistrare pentru supervizorii acelor angajaŃi ce câştigă mai mult de 40.

NrSup NrAng NrInreg Salariu 40( SUPERVIZOR ( ( ANGAJAT )))= >π σ⊳⊲

Rezultatul interogării se poate observa în figura 3.11.a

2) Să se găsească numele şi salariile pentru supervizorii acelor angajaŃi ce câştigă mai mult de 40.

Fiecare tuplu a rezultatului este construit pe baza a trei tupluri, primul din ANGAJAT (despre angajaŃii care câştigă mai mult de 40), al doilea din SUPERVIZOR (care furnizează numărul supervizorului angajatului în chestiune) şi al treilea din ANGAJAT (cu informaŃii privind pe supervizori). Intuitiv, soluŃia necesită joncŃiunea relaŃiei ANGAJAT cu rezultatul expresiei precedente, dar este necesară o precizare. În general, supervizorii şi angajaŃii nu sunt aceiaşi şi astfel cele două tupluri din ANGAJAT care contribuie la tuplul rezultat sunt diferite. JoncŃiunea trebuie precedată deci de o redenumire.

NumeSup,SalariuSup NrInregSup,NumeSup,SalariuSup,VarstaSup NrInreg ,Nume,Salariu ,Varsta

NrInregSup NrSup

NrAng NrInreg Salariu 40

( ( ANGAJAT )

( SUPERVIZOR ( ( ANGAJAT ))))

←

=

= >

π ρ

σ

⊳⊲

⊳⊲

Rezultatul interogării se poate observa în figura 3.11.b.

3) Să se găsească numerele de înregistrare şi numele supervizorilor ce au numai subalterni ce câştigă mai mult de 40.

Vom proceda în felul următor: se găsesc toŃi supervizorii pentru care toŃi subalternii câştigă maxim 40; apoi aplicăm operatorul de diferenŃă între submulŃimea supervizorilor şi mulŃimea obŃinută la pasul anterior.

NrInreg ,Nume NrInreg NrSup NrSup

NrSup NrAng NrInreg Salariu 40

( ANGAJAT ( ( SUPERVIZOR )

( SUPERVIZOR ( ( ANGAJAT )))))

=

= ≤

π π −

π σ

⊳⊲

⊳⊲

NrSup 210 301 375

Nume Salariu Marcel Burlacu 60 Andrei Popa 70 Maria Ionescu 65

NrInreg Nume 301 Andrei Popa 375 Maria Ionescu

a) b) c)

Fig. 3.11 Rezultatele interogărilor asupra bazei de date din Fig. 3.10

Probleme propuse. 1) Să se găsească numărul de înregistrare, numele şi vârsta pentru supervizorii cu

salariu mai mare de 40.

28

2) Să se găsească angajaŃii ce câştigă mai mult decât supervizorii lor, incluzând în rezultat numărul de înregistrare, numele şi salariul atât pentru angajaŃi cât şi pentru supervizori.

3.7 EchivalenŃa expresiilor algebrice Algebra relaŃională permite formularea unor expresii echivalente între ele, adică expresii

ce produc acelaşi rezultat. DefiniŃie. Spunem că expresiile E1 şi E2 sunt echivalente pentru schema R (cu notaŃia 1 R 2E E≡ )

dacă 1 2E ( r ) E ( r )= , pentru orice instanŃă r în R.

DefiniŃie. Spunem că expresiile E1 şi E2 sunt echivalente absolut (cu notaŃia 1 2E E≡ ) dacă sunt

echivalente pentru orice schema R.

DistincŃia între cele două cazuri este dată de faptul că atributele operanzilor nu sunt specificate în expresii (în particular în operaŃiile de joncŃiune naturală).

Exemplu

EchivalenŃă absolută: AB A 0 A 0 AB( ( R )) ( ( R ))> >π σ ≡ σ π

EchivalenŃă: AB 1 AC 2 R ABC 1 2( R ) ( R ) ( R R )π π ≡ π⊳⊲ ⊳⊲

În ultimul caz, echivalenŃa este validă doar dacă în schema R, intersecŃia dintre mulŃimile de atribute ale R1 şi R2 este atributul A. Dacă ar mai exista alt atribut B comun celor două mulŃimi, prima joncŃiune ar opera pe A, în timp ce a doua ar opera pe A şi B.

EchivalenŃa expresiilor în algebra relaŃională joacă un rol important în optimizarea cererilor. Cererile SQL sunt translate în domeniul algebrei relaŃionale şi se evaluează costul acestor cereri. Costul unei cereri este definit în termenii dimensiunii rezultatului intermediar şi final. Când sunt în discuŃie mai multe expresii echivalente va fi selectată cea având costul cel mai mic. În acest context se utilizează transformări de echivalenŃă, acestea substituind o expresie cu o alta echivalentă. Prezintă interes acele transformări care reduc dimensiunea relaŃiilor intermediare sau pregătesc o expresie în vederea aplicării unor astfel de transformări:

• atomizarea selecŃiilor

1 2 1 2F F F F( E ) ( ( E ))∧σ ≡ σ σ .

• cascadarea proiecŃiilor – o proiecŃie poate fi transformată într-o cascadă de proiecŃii care elimină atribute în diverse faze

X X XY( E ) ( ( E ))π ≡ π π ,

dacă E este definită pe o mulŃime de atribute ce include mulŃimile X şi Y. • anticiparea selecŃiei în raport cu joncŃiunea

F 1 2 1 F 2( E E ) E ( E )σ ≡ σ⊳⊲ ⊳⊲ ,

dacă F face referire doar la atribute din E2. • anticiparea proiecŃiei în raport cu joncŃiunea. Fie E1 şi E2 două expresii definite pe X1 şi

X2. Dacă 2 2Y X⊂ şi 2 1 2Y X X⊇ ∩ atunci

1 2 2X Y 1 2 1 Y 2( E E ) E ( E )π ≡ π⊳⊲ ⊳⊲

29

Combinând această regulă cu cascadarea proiecŃiilor, obŃinem echivalenŃa pentru theta – joncŃiune:

1 2Y 1 F 2 Y Y 1 F Y 2( E E ) ( ( E ) ( E ))π ≡ π π π⊳⊲ ⊳⊲ ,

unde X1 – atributele expresiei E1,

X2 - atributele expresiei E2,

1 1J X⊆ , 2 2J X⊆ - submulŃimi de atribute implicate în F,

1 1 1Y ( X Y ) J= ∩ ∪ , 2 2 2Y ( X Y ) J= ∩ ∪ .

• combinarea selecŃiei şi a produsului cartezian pentru a forma theta – joncŃiunea

F 1 2 1 F 2( E E ) E Eσ ≡⊳⊲ ⊳⊲

• distributivitatea selecŃiei în raport cu reuniunea

F 1 2 F 1 F 2( E E ) ( E ) ( E )σ ∪ ≡ σ ∪ σ

• distributivitatea selecŃiei în raport cu diferenŃa

F 1 2 F 1 F 2( E E ) ( E ) ( E )σ − ≡ σ − σ

• distributivitatea proiecŃiei în raport cu reuniunea

X 1 2 X 1 X 2( E E ) ( E ) ( E )π ∪ ≡ π ∪ π

Notă. ProiecŃia nu este distributivă în raport cu diferenŃa

• 1 2 1 2F F F F( R ) ( R ) ( R )∨σ ≡ σ ∪ σ

• 1 2 1 2 1 2F F F F F F( R ) ( R ) ( R ) ( R ) ( R )∧σ ≡ σ ∩ σ ≡ σ σ⊳⊲

• 1 2 1 2F ( F ) F F( R ) ( R ) ( R )∧ ¬σ ≡ σ − σ

Sunt valabile comutativitatea şi asociativitatea tuturor operatorilor binari (excluzând diferenŃa) şi distributivitatea joncŃiunii în raport cu reuniunea:

• 1 2 1 2E ( E E ) ( E E ) ( E E )∪ ≡ ∪⊳⊲ ⊳⊲ ⊳⊲

În final, trebuie să fim conştienŃi că prezenŃa unor rezultate intermediare vide (fără nici un tuplu) face posibilă simplificarea expresiilor într-un mod natural. Este de subliniat faptul că o joncŃiune (sau produsul cartezian) în care unul din operatori este o relaŃie vidă, produce un rezultat vid.

3.8 Valori NULL în algebra relaŃională În cele discutate anterior am presupus că expresiile algebrice sunt aplicate unor relaŃii ce

nu conŃin valori NULL. łinând cont de importanŃa valorilor NULL în aplicaŃii, vom vedea care este impactul lor asupra limbajelor de interogare şi actualizare a datelor.

Exemplu. Să considerăm relaŃia din figura următoare

PERSOANA Nume Varsta Salariu Ionescu 35 500 Popescu 27 600 Popa NULL 500

Figura 3.12 RelaŃie cu valori NULL

30

şi selecŃia Varsta 30( PERSOANA)>σ .

Primul tuplu va contribui la rezultatul selecŃiei, iar al doilea tuplu nu. Ce putem spune despre al treilea tuplu (valoarea NULL având semnificaŃia unei informaŃii pe care o ignoram)?

În raport cu interogările prezentate anterior vom utiliza o logică trivalentă în locul celei bivalente. În această logică o formulă poate avea valorile de adevăr TRUE (T), FALSE (F) sau UNKNOWN (U). Rezultatul unei condiŃii atomice va avea valoarea de adevăr UNKNOWN dacă cel puŃin un termen al comparaŃiei are valoarea NULL. Revenind la exemplul nostru, expresia va produce o relaŃie formată din primul tuplu, pentru care valoarea de adevăr a expresiei a fost TRUE. Tabelele de adevăr în logica trivalentă pentru conectorii not, and şi or sunt:

not F T U U T F

and T U F T T U F U U U F F F F F

or T U F T T T T U T U U F T U F

Este de notat că această logică trivalentă pentru operatorii algebrici prezintă unele dezavantaje.

Exemplu Să considerăm expresia

Varsta 30 Varsta 30( PERSOANA) ( PERSOANA )> ≤σ ∪ σ . Logic, această expresie ar trebui să returneze relaŃia PERSOANA. Pe de altă parte, dacă cele două subexpresii sunt evaluate separat, al treilea tuplu va produce un rezultat necunoscut pentru fiecare subexpresie, deci şi pentru reuniune. Numai prin intermediul unei evaluări globale (abordare care nu este practică în cazul expresiilor complexe) putem ajunge la concluzia că un astfel de tuplu trebuie să apară în rezultat.

Cea mai bună metodă practică de a depăşi astfel de dificultăŃi este de a trata valorile NULL din punct de vedere pur sintactic. În acest sens sunt introduse două noi condiŃii atomice de selecŃie pentru a verifica dacă o valoare este specificată sau este NULL:

• A is NULL este adevărată pentru tuplul t dacă t[A] = NULL şi falsă în rest;

• A is NOT NULL este adevărată pentru tuplul t dacă t[A] ≠ NULL şi falsă în rest.

Exemplu

Varsta 30( PERSOANA)>σ . returnează persoanele cu vârsta peste 30 de ani;

Varsta 30 Varsta IS NULL( PERSOANA )> ∨σ - returnează persoanele care au sau care pot avea

peste 30 de ani Această abordare este utilizată în versiunile recente de SQL care suportă logica trivalentă. 3.9 Vederi

În cele prezentate anterior am văzut că se pot construi reprezentări diferite ale datelor, reprezentări ce vor fi disponibile utilizatorilor. Acest lucru este posibil în modelul relaŃional prin intermediul relaŃiilor derivate, al căror conŃinut este definit pe baza conŃinutului altor relaŃii. Într-o bază de date relaŃională există relaŃii de bază, al căror conŃinut este autonom şi stocat în baza de date şi relaŃii derivate.

Există două tipuri principale de relaŃii derivate:

• vederi materiale - relaŃii derivate stocate în baza de date;

31

• relaŃii virtuale (numite pur şi simplu vederi) – relaŃii definite prin intermediul unor funcŃii (expresii ale limbajului de interogare) care nu sunt stocate în baza de date, dar pot fi folosite în interogări ca şi cum ar exista fizic.

În general, vederile materiale oferă un avantaj când numărul cererilor de interogare este mai mare decât operaŃiile de actualizare ale relaŃiei pe care se bazează vederea. Deoarece nu se pot specifica tehnici generale de păstrare a consistenŃei între relaŃiile de bază şi vederile materiale, majoritatea sistemelor comerciale oferă mecanisme numai pentru relaŃiile virtuale (vederi).

Vederile sunt definite în sistemele relaŃionale ca fiind expresii ale unui limbaj de interogare.

Exemplu

Să considerăm baza de date formată din relaŃiile

1 2 3R ( ABC ),R ( DEF ),R (GH ),

şi vederea definită ca produsul cartezian urmat de o selecŃie

A D 1 2R ( R R )>= σ ⊳⊲ .

Pe baza acestei scheme, interogarea

B G 3( R R )=σ ⊳⊲

va fi executată prin înlocuirea lui R cu definiŃia sa:

B G A D 1 2 3( ( R R ) R )= >σ σ ⊳⊲ ⊳⊲ .

Utilizarea vederilor poate fi utilă din mai multe motive:

• un utilizator interesat numai de o porŃiune din baza de date poate evita contactul cu componentele ce nu prezintă interes;

Exemplu Într-o bază de date cu două relaŃii având schemele

ANGAJAT (NrAngajat, Departament) MANAGER (Departament, NrSupervizor),

un utilizator interesat doar de angajaŃi şi de supervizorii lor ar putea fi ajutat de existenŃa unei vederi definită astfel:

NrAngajat ,NrSupervizor( ANGAJAT MANAGER )π ⊳⊲ .

• expresii extrem de complexe pot fi definite cu ajutorul vederilor, existând anumite avantaje în cazul subexpresiilor repetitive;

• vederile permit implementarea unui mecanism de protecŃie a datelor din baza de date;

• în cazul restructurării unei baze de date, este convenabilă definirea unor vederi corespunzătoare relaŃiilor ce nu mai există după restructurare. În acest caz, aplicaŃiile deja scrise ce fac referire la versiunea anterioară a schemei pot fi utilizate pe noua schemă fără a necesita modificări.

Exemplu Presupunem că schema R( ABC ) este înlocuită de schemele 1 2R ( AB ),R ( BC ). Putem defini vederea

1 2R R R= ⊳⊲ şi astfel nu va trebui să modificăm aplicaŃiile ce fac referire la R. Dacă B este o cheie pentru R2 atunci prezenŃa vederii este complet transparentă.

32

Din punct de vedere al interogărilor, vederile pot fi tratate în aceeaşi manieră ca şi relaŃiile. Nu acelaşi lucru se întâmplă în cazul operaŃiilor de actualizare. De fapt nu se poate da o semantică pentru actualizarea vederilor.

Exemplu Fie vederea definită anterior

NrAngajat ,NrSupervizor( ANGAJAT MANAGER )π ⊳⊲ .

Să presupunem că dorim să inserăm un tuplu în această vedere; de fapt dorim să inserăm două tupluri în relaŃiile de bază care să genereze noul tuplu din vedere. Acest lucru nu este posibil deoarece tuplul din vedere nu implică atributul Departament, atribut care este utilizat în realizarea legăturii dintre cele două relaŃii de bază. În general, problema actualizării vederilor este complexă, toate sistemele impunând limitări severe în ceea ce priveşte actualizarea vederilor.

Probleme propuse

1. Se consideră baza de date alcătuită din relaŃiile următoare:

FILME (NumarFilm , Titlu, Regizor, An, CostProductie) ARTISTI (NumarActor , Nume, Prenume, Sex, DataNastere, Nationalitate)

ROLURI ( NumarFilm , NumarActor , Personaj)

a). RealizaŃi o bază de date cu relaŃiile de mai sus pentru care joncŃiunile între diverse relaŃii sunt complete. b). Presupunând că există două constrângeri de referinŃă între relaŃia ROLURI şi celelalte două relaŃii, precizaŃi cazurile posibile de joncŃiuni incomplete. c). ArătaŃi un produs cartezian care implică relaŃii din baza de date prezentată. d). RealizaŃi o bază de date pentru care una (sau mai multe) dintre joncŃiuni este (sunt) vidă (vide). 2. Se consideră baza de date de la exemplul 1. ExprimaŃi următoarele interogări în algebra

relaŃională: a). Să se găsească titlurile filmelor în care joacă Henry Fonda. b). Să se găsească titlurile filmelor pentru care regizorul este şi actor. c). Să se găsească actorii care au interpretat două personaje în acelaşi film; precizaŃi titlul filmului, numele şi prenumele actorului şi numele personajelor interpretate. d). Să se găsească titlurile filmelor în care actorii sunt toŃi de acelaşi sex. 3. Se consideră baza de date care conŃine următoarele relaŃii:

CURSURI (Numar, Facultate, TitluCurs, Titular) STUDENTI (Numar , Nume, Prenume, Facultate)

TITULARI (Numar , Nume, Prenume) EXAMENE (Student, Curs, Nota, Data)

PLAN_INVATAMANT (Student , Curs, An)

FormulaŃi în algebra relaŃională interogările care produc: a). studenŃii care au obŃinut nota 10 la cel puŃin un examen, precizând pentru fiecare numele, prenumele şi data când au obŃinut prima notă de 10; b). pentru fiecare curs de la Automatică, studenŃii care au trecut examenul în ultima sesiune; c). studenŃii care au trecut toate examenele cerute de planul de învăŃământ; d). pentru fiecare curs de la Electronică, studenŃii care au obŃinut cea mai mare notă;

33

e). numele şi prenumele studenŃilor care au luat examenul la un curs al cărui titular are acelaşi nume ca şi studentul. 4. Pentru baza de date cu relaŃiile:

ORASE (Nume, Judet, NumarLocuitori) TRAVERSARI (Oras , Râu)

RÂURI (Râu, Lungime), formulaŃi următoarele interogări în algebra relaŃională: a). găsiŃi numele, judeŃele şi numărul de locuitori pentru oraşele care au mai mult de 50000 de locuitori şi sunt traversate de Siret sau Mureş; b). găsiŃi oraşele care sunt traversate de cel puŃin două râuri, precizând numele oraşului şi numele celui mai lung râu care-l traversează.

34

Capitolul 4. Calculul relaŃional Calculul relaŃional este o familie de limbaje de interogare, bazate pe calculul cu predicate de ordinul întâi. Aceste limbaje sunt declarative, interogarea fiind specificată prin proprietăŃile rezultatului, contând mai puŃin procedura ce trebuie urmată pentru a o obŃine. Spre deosebire de calculul relaŃional, algebra relaŃională este un limbaj procedural, deoarece expresiile specifică pas cu pas construirea rezultatului.

Există mai multe versiuni de calcul relaŃional, dar în cele ce urmează vom discuta doar despre două dintre aceste versiuni şi anume:

• Calculul relaŃional pe domenii, care prezintă caracteristicile de bază ale acestor limbaje;

• Calculul relaŃional pe tupluri, care formează bazele multor constructori disponibili pentru interogarea în SQL.

4.1 Calculul relaŃional pe domenii

Expresiile din calculul relaŃional au forma

1 1 k k{ A : x , ..., A : x | f },

unde

• 1 kA ..., A - atribute distincte;

• 1 kx ..., x - variabile ( pentru simplitate le vom considera distincte)

• f - formulă construită pe baza următoarelor reguli:

a) există două tipuri de formule atomice:

- 1 1 p pR( A : x , ..., A : x ), unde 1 pR( A , ..., A ) este o schemă relaŃională şi 1 px ..., x

variabile;

- x yθ sau x cθ , cu x şi y variabile, c constantă şi θ operator de comparaŃie ( , , , , ,= ≠ ≤ < ≥ > ).

b) dacă f1 şi f2 sunt formule, atunci1 2f f∨ , 1 2f f∧ şi 1f¬ sunt formule.

c) dacă f este o formulă şi x variabilă ce apare în f atunci x( f )∃ şi x( f )∀ sunt formule ( ∃ este cuantificatorul existenŃial iar ∀ cuantificatorul universal).

Lista de perechi 1 1 k kA : x , ..., A : x se numeşte listă Ńintă deoarece defineşte structura

rezultatului. Rezultatul va consta din relaŃia definită pe 1 kA ..., A care conŃine tupluri ale căror

valori, substituite cu 1 kx ..., x , asigură că formula f este adevărată.

Valoarea de adevăr a unei formule f se stabileşte pe baza următoarelor reguli: • formula atomică 1 1 p pR( A : x , ..., A : x ) este adevărată pentru acele valori ale lui 1 px ..., x

ce formează un tuplu în R; • formula atomică x yθ este adevărată pentru acele valori ale lui x şi y astfel încât valoarea

lui x se află în relaŃia θ cu valoarea lui y; în acelaşi mod se defineşte valoarea de adevăr a formulei atomice x cθ ;

• semnificaŃia conectorilor , ,∨ ∧ ¬ este cea uzuală;

• x( f )∃ este adevărată dacă există cel puŃin o valoare a lui x pentru care formula f este adevărată;

35

• x( f )∀ este adevărată dacă formula f este adevărată pentru toate valorile posibile ale lui x. Vom exemplifica în continuare modul de exprimare a interogărilor în calculul pe

domenii.

Exemple Se consideră baza de date cu schema:

ANGAJAT (NrInreg, Nume, Varsta, Salariu)

SUPERVIZOR (NrSup, NrAng).

4) Să se găsească numerele de înregistrare, numele, vârsta şi salariul pentru toŃi angajaŃii ce câştigă mai mult de 40.

În algebra relaŃională:

Salariu 40( ANGAJAT )>σ .

În calculul relaŃional pe domenii:

{ NrInreg : m,Nume : n,Varsta : v,Salariu : s | ANGAJAT( m,n,v,s ) s 40 }∧ >

5) Să se găsească numerele de înregistrare pentru supervizorii acelor angajaŃi ce câştigă mai mult de 40.

În algebra relaŃională:

NrSup NrAng NrInreg Salariu 40( SUPERVIZOR ( ( ANGAJAT )))= >π σ⊳⊲

În calculul relaŃional pe domenii:

{ NrSup : n | ANGAJAT( NrInreg : m,Nume : u,Varsta: v,Salariu : s )

SUPERVIZOR( NrSup : n,NrAng : m ) s 40 }

∧∧ >

Variabila m, comună ambelor condiŃii atomice, realizează corespondenŃa între tuplurile specificate în joncŃiune. Se pot utiliza cuantificatori existenŃiali pentru toate variabilele ce nu apar în lista Ńintă, dar acest lucru ar complica formularea cererii.

Dacă într-o expresie este necesară implicarea unor tupluri diferite ale aceleaşi relaŃii este suficientă impunerea mai multor condiŃii asupra aceluiaşi predicat din formulă, utilizând variabile diferite. Să considerăm interogarea:

6) Să se găsească numele şi salariile pentru supervizorii acelor angajaŃi ce câştigă mai mult de 40.

În algebra relaŃională:

NumeSup,SalariuSup NrInregSup,NumeSup,SalariuSup,VarstaSup NrInreg ,Nume,Salariu ,Varsta

NrInregSup NrSup

NrAng NrInreg Salariu 40

( ( ANGAJAT )

( SUPERVIZOR ( ( ANGAJAT ))))

←

=

= ≥

π ρ

σ

⊳⊲

⊳⊲

În calculul relaŃional pe domenii

Vom cere ca, pentru fiecare tuplu din rezultat să existe trei tupluri: un tuplu corespunde unui angajat ce câştigă mai mult de 40, al doilea tuplu indică supervizorul său şi ultimul furnizează informaŃii detaliate despre supervizor:

36

{ NumeSup : ns,SalariuSup : ss | ANGAJAT( NrInreg : ni,Nume : u,Varsta : v,Salariu : s ) s 40

SUPERVIZOR( NrSup : n,NrAng : ni )

ANGAJAT( NrInreg : n,Nume : ns,Varsta : q,Salariu : ss )}

∧ > ∧∧

4) Să se găsească numerele de înregistrare şi numele supervizorilor ce au numai subalterni ce câştigă mai mult de 40.

În algebra relaŃională:

NrInreg ,Nume NrInreg NrSup

NrSup

NrSup NrAng NrInreg Salariu 40

( ANGAJAT

( ( SUPERVIZOR )

( SUPERVIZOR ( ( ANGAJAT )))))

=

= ≤

π

π −

π σ

⊳⊲

⊳⊲

În calculul relaŃional pe domenii

{ NrInreg : n,Nume : u | ANGAJAT( NrInreg : n,Nume : u,Varsta : v,Salariu : s )

SUPERVIZOR( NrSup : n,NrAng : m )

m'( n'( v'( s'( ANGAJAT( NrInreg : m',Nume : n',Varsta : v',Salariu : s')

SUPERVIZOR( NrSup : n,NrAng : m') s' 40 ))))}

∧∧

¬∃ ∃ ∃ ∃ ∧∧ ≤

O alternativă la această expresie se poate obŃine prin utilizarea cuantificatorului universal:

{ NrInreg : n,Nume : u | ANGAJAT( NrInreg : n,Nume : u,Varsta : v,Salariu : s )

SUPERVIZOR( NrSup : n,NrAng : m )

m'( n'( v'( s'( ( ANGAJAT( NrInreg : m',Nume : n',Varsta : v',Salariu : s')

SUPERVIZOR( NrSup : n,NrAng : m')) s' 40 ))))}

∧∧

∀ ∀ ∀ ∀ ¬ ∧∨ >

Această expresie selectează supervizorul n dacă pentru orice tuplu de valori m',n',v',s' corespunzător angajaŃilor lui n, s' 40> .

ObservaŃii . Structura f g¬ ∨ corespunde condiŃiei „dacă f atunci g” deoarece este adevărată în toate cazurile cu excepŃia cazului când f este adevărată şi g falsă.

De notat că legile lui de Morgan

( f g ) ( f ) ( g )¬ ∧ = ¬ ∨ ¬ ,

( f g ) ( f ) ( g )¬ ∨ = ¬ ∧ ¬ ,

sunt valabile şi pentru cuantificatori:

x( f ) ( x( ( f )))∃ ≡ ¬ ∀ ¬

x( f ) ( x( ( f )))∀ ≡ ¬ ∃ ¬

Dezavantaje ale calculului relaŃional pe domenii

Deşi natura declarativă a calculului relaŃional pe domenii prezintă unele aspecte de interes, există unele limitări ce au importanŃă din punct de vedere practic.

De exemplu, expresia

1 1 2 2 1 1 2 2{ A : x ,A : x | R( A : x ) x x }∧ =

produce ca rezultat o relaŃie pe A1 şi A2 formată din tupluri ale căror valori pe A1 apar în relaŃia R, şi valoarea pe A2 poate fi orice valoare din domeniu (deoarece condiŃia 2 2x x= este

întotdeauna adevărată). În particular, dacă domeniul se schimbă, spre exemplu de la întreg între 0 şi 99 la întreg între 0 şi 999, răspunsul interogării se schimbă de asemenea. Dacă domeniul este infinit, atunci şi rezultatul este infinit, ceea ce este de neacceptat.

O observaŃie similară poate fi făcută şi referitor la expresia

37

1 1 1 1{ A : x | R( A : x )}¬

al cărei rezultat este o relaŃie ce conŃine acele valori din domeniul A1 care nu apar în relaŃia R. DefiniŃie. O expresie a unui limbaj de interogare este independentă de domeniu dacă rezultatul său în fiecare instanŃă a bazei de date nu variază dacă se modifică domeniul pe care se evaluează expresia. DefiniŃie. Un limbaj este independent de domeniu dacă toate expresiile lui sunt independente de domeniu. Pe baza acestor definiŃii putem afirma că algebra relaŃională este independentă de domeniu (construind rezultatul pe baza relaŃiilor din baza de date, fără a face referire la domeniile atributelor), în timp ce calculul relaŃional pe domenii este dependent de domeniu. Spunem ca două limbaje de programare sunt echivalente dacă pentru orice expresie dintr-un limbaj există o expresie echivalentă în cel de-al doilea limbaj şi invers. Algebra relaŃională şi calculul pe domenii nu sunt echivalente, deoarece calculul pe domenii permite formularea unor expresii ce sunt dependente de domeniu. Totuşi această echivalenŃă se poate obŃine dacă limităm calculul pe domenii la o submulŃime de expresii independente de domeniu. Un alt dezavantaj al calculului relaŃional pe domenii este dat de numărul mare de variabile cerute într-o expresie, de obicei o variabilă pentru fiecare atribut. De asemenea, ori de câte ori se impune o operaŃie de cuantificare, cuantificatorii sunt şi ei multiplicaŃi. Singurul limbaj bazat, într-o anumită măsură, pe calculul pe domenii este QBE (Query by Example). În încercarea de a depăşi limit ările calculului pe domenii a fost propusă o variantă a calculului relaŃional, în care variabilele reprezintă tupluri. 4.2 Calculul relaŃional pe tupluri Expresiile din calculul pe tupluri au forma

{T | L | f } ,

unde • L este lista gamei de valori , în care sunt enumerate variabilele libere din formula

f, împreună cu gamele de variaŃie corespunzătoare; de fapt L este o listă de elemente de tipul x(R) , cu x variabilă şi R relaŃie; dacă x(R) este în lista gamei de valori, atunci x va lua valori numai din R.

• T este lista Ńintă, compusă din elemente de tipul Y:x.Z (sau mai simplu x.Z ca abreviere pentru Z:x.Z), cu x variabilă şi Y, Z secvenŃe de atribute; atributele din Z trebuie să apară în schema relaŃiei ce constituie gama de valori a lui x; vom scrie x.* ca abreviere pentru X:x.X, unde gama de valori a variabilei x este o relaŃie definită pe atributele X;

• f este o formulă constituită din: - atomi de tipul x.A cθ sau 1 1 2 2x .A x .Aθ care compară valoarea lui x

corespunzătoare atributului A cu constanta c şi valoarea lui x1 pe atributul A1 cu cea a lui x2 pe atributul A2;

- conectori ( , ,∧ ∨ ¬ ); - cuantificatori:

� x( R )( f )∃ - există un tuplu x în relaŃia R ce satisface f; � x( R )( f )∀ - orice tuplu x din R satisface f.

Notă. În timp ce se introduce variabila x, o declaraŃie a gamei de valori de forma x(R) specifică faptul că x poate lua ca valori numai tupluri din relaŃia R. De aceea, acest limbaj nu necesită

38

formularea unor condiŃii atomice ca în calculul pe domenii care să indice că un tuplu aparŃine unei anumite relaŃii. În continuare vor fi prezentate câteva exemple de interogări formulate în calculul relaŃional pe tupluri.

Exemple Se consideră baza de date cu schema:

ANGAJAT (NrInreg, Nume, Varsta, Salariu)

SUPERVIZOR (NrSup, NrAng).

1) Să se găsească numerele de înregistrare, numele, vârsta şi salariul pentru toŃi angajaŃii ce câştigă mai mult de 40.

{ e.* | e( ANGAJAT )| e.Salariu 40 }>

2) Să se găsească numerele de înregistrare pentru supervizorii acelor angajaŃi ce câştigă mai mult de 40.

{ s.NrSup | e( ANGAJAT ),s( SUPERVIZOR )| e.NrInreg s.NrAng e.Salariu 40 }= ∧ >

3) Să se găsească numele şi salariile pentru supervizorii acelor angajaŃi ce câştigă mai mult de 40.

{ NumeS,SalariuS : e' .( Nume,Salariu ) | e'( ANGAJAT ),e( ANGAJAT ),s( SUPERVIZOR )|

e' .NrInreg s.NrSup s.NrAng e.NrInreg e.Salariu 40 }= ∧ = ∧ >

4) Să se găsească numerele de înregistrare şi numele supervizorilor ce au numai subalterni ce câştigă mai mult de 40.

{ e.( NrInreg,Nume )| e( ANGAJAT ),s( SUPERVIZOR )|

e.NrInreg s.NrSup e'( ANGAJAT )( s'( SUPERVIZOR )

( ( s.NrSup s' .NrSup s' .NrAng e' .NrInreg )

e' .Salariu 40 ))}

= ∧ ∀ ∀¬ = ∧ = ∨

>

O alternativă la această expresie se poate obŃine prin utilizarea cuantificatorului existenŃial:

{ e.( NrInreg,Nume )| e( ANGAJAT ),s( SUPERVIZOR )|

e.NrInreg s.NrSup ( e'( ANGAJAT )( s'( SUPERVIZOR )

( s.NrSup s' .NrSup s' .NrAng e' .NrInreg e' .Salariu 40 )))}

= ∧ ¬ ∃ ∃= ∧ = ∧ ≤

ObservaŃii . Nu orice interogare din algebra relaŃională sau din calculul relaŃional pe domenii se poate exprima în calculul relaŃional pe tupluri. Spre exemplu, interogările care în algebra relaŃională necesită operatorul reuniune nu pot fi exprimate prin calculul pe tupluri. După cum se va vedea în capitolul următor, SQL pune la dispoziŃie posibilitatea construirii în mod explicit a reuniunii, deoarece aspectele declarative ale SQL se bazează pe calculul pe tupluri cu declaraŃia gamei de valori. Probleme propuse

5. Se consideră baza de date alcătuită din relaŃiile următoare:

FILME (NumarFilm , Titlu, Regizor, An, CostProductie) ARTISTI (NumarActor , Nume, Prenume, Sex, DataNastere, Nationalitate)

39

ROLURI ( NumarFilm , NumarActor , Personaj)

ExprimaŃi următoarele interogări în calculul relaŃional pe domenii şi în calculul relaŃional pe tupluri: a). Să se găsească titlurile filmelor în care joacă Henry Fonda. b). Să se găsească titlurile filmelor pentru care regizorul este şi actor. c). Să se găsească actorii care au interpretat două personaje în acelaşi film; precizaŃi titlul filmului, numele şi prenumele actorului şi numele personajelor interpretate. d). Să se găsească titlurile filmelor în care actorii sunt toŃi de acelaşi sex. 6. Se consideră baza de date care conŃine următoarele relaŃii:

CURSURI (Numar, Facultate, TitluCurs, Titular) STUDENTI (Numar , Nume, Prenume, Facultate)

TITULARI (Numar , Nume, Prenume) EXAMENE (Student, Curs, Nota, Data)

PLAN_INVATAMANT (Student , Curs, An)

FormulaŃi în calculul relaŃional pe domenii şi în calculul relaŃional pe tupluri interogările care produc: a). studenŃii care au obŃinut nota 10 la cel puŃin un examen, precizând pentru fiecare numele, prenumele şi data când au obŃinut prima notă de 10; b). pentru fiecare curs de la Automatică, studenŃii care au trecut examenul în ultima sesiune; c). studenŃii care au trecut toate examenele cerute de planul de învăŃământ; d). pentru fiecare curs de la Electronică, studenŃii care au obŃinut cea mai mare notă; e). numele şi prenumele studenŃilor care au luat examenul la un curs al cărui titular are acelaşi nume ca şi studentul. 7. Pentru baza de date cu relaŃiile:

ORASE (Nume, Judet, NumarLocuitori) TRAVERSARI (Oras , Râu)

RÂURI (Râu, Lungime), formulaŃi următoarele interogări în calculul relaŃional pe domenii şi în calculul relaŃional pe tupluri: a). găsiŃi numele, judeŃele şi numărul de locuitori pentru oraşele care au mai mult de 50000 de locuitori şi sunt traversate de Siret sau Mureş; b). găsiŃi oraşele care sunt traversate de cel puŃin două râuri, precizând numele oraşului şi numele celui mai lung râu care-l traversează.

40

Capitolul 5. SQL SQL este un acronim pentru Structured Query Language şi a fost dezvoltat pentru sistemul de gestiune a bazelor de date System R, creat de IBM Research Laboratory, San Jose, California la sfârşitul anilor ’70. SQL a fost standardizat şi a devenit limbajul de referinŃă pentru bazele de date relaŃionale. SQL nu este numai un limbaj de interogare. El conŃine proprietăŃile unui limbaj de definire a datelor, LDD (comenzi pentru definirea unei scheme a unei baze de date relaŃionale) şi proprietăŃile unui limbaj de manipulare a datelor, LMD (comenzi pentru modificarea şi interogarea unei instanŃe a unei baze de date relaŃionale). De-a lungul timpului au existat mai multe versiuni de SQL, prima definiŃie a unui standard pentru SQL fiind promulgată în 1986 de ANSI (the American National Standards Institute). Acest prim standard conŃinea multe din elementele de bază pentru formularea interogărilor, oferind în acelaşi timp un suport limitat pentru definirea schemei şi manipularea ei. În 1989 a apărut versiunea SQL-89, care adaugă la versiunea anterioară definiŃia integrităŃii de referinŃă. Următoarea versiune, compatibilă în cea mai mare parte cu versiunea anterioară, dar care conŃinea un număr mare de caracteristici noi, a fost publicată în 1992, fiind cunoscută sub numele de SQL-2. SQL-3 este cea mai recentă versiune de SQL, compatibilă în totalitate cu SQL-2, dar nu a fost încă adoptat ca standard. 5.1 Definirea datelor în SQL În această secŃiune vom ilustra utilizarea SQL pentru definirea schemei unei baze de date. Înainte de aceasta se prezintă notaŃiile folosite în sintaxa limbajului. Cuvintele cheie vor fi scrise cu caractere normale iar variabilele cu caractere italice. De asemenea:

• parantezele unghiulare <***> marchează termenii; • parantezele pătrate [***] indică faptul că termenii delimitaŃi sunt opŃionali (pot să nu

apară sau să apară doar o singură dată); • acoladele {***} indică faptul că termenul din interior poate să nu apară sau poate fi

repetat de un număr arbitrar de ori; • barele verticale indică faptul că unul dintre termenii delimitaŃi de acestea trebuie să apară.

Parantezele rotunde trebuie privite întotdeauna ca şi cuvinte cheie ale SQL. 5.1.1 Domenii elementare SQL pune la dispoziŃie şase familii de domenii elementare, care pot fi utilizate pentru

definirea domeniilor asociate atributelor schemei. 1) Caracter Domeniul caracter permite reprezentarea caracterelor sau a şirurilor de caractere.

Lungimea şirurilor poate fi fixă sau variabilă. În cazul şirurilor de lungime variabilă trebuie specificată lungimea maximă. Pentru fiecare schemă este specificat un set de caractere implicit (latin, chirilic, grecesc etc.). În cazul în care este necesară folosirea a mai mult de un set de caractere se specifică acest lucru pentru fiecare domeniu.

Sintaxa este:

character [varying] [(Lungime)] [character set NumeSetCaracter]

Dacă lungimea nu este specificată, domeniul reprezintă un singur caracter. Exemplu - şir de caractere de lungime variabilă, cu lungimea maximă de 1000 caractere, setul de caractere grecesc

character varying (1000) character set Greek

41

2) Bit Acest domeniu, introdus in SQL-2, este utilizat pentru atribute ce pot avea doar două

valori: 0 sau 1. Se foloseşte pentru reprezentarea atributelor de tip flag (specifică dacă un obiect are sau nu o anumită proprietate). SQL pune la dispoziŃie de asemenea domeniul „şir de biŃi”, lungimea şirului fiind specificată ca parametru. Şirurile de biŃi sunt utile pentru reprezentarea unui grup de proprietăŃi.

Sintaxa este:

bit [varying] [(Lungime)]

Exemplu - şir de biŃi de lungime variabilă, cu lungimea maximă de 100 caractere

bit varying (100)

3) Domenii numerice exacte Această familie conŃine domenii ce permit reprezentarea valorilor exacte, de tip întreg

sau cu parte fracŃionară. SQL pune la dispoziŃie patru domenii numerice diferite:

• numeric [(Precizie [,Scală])]

• decimal [(Precizie [,Scală])]

• integer

• smallint

Domeniile numeric şi decimal reprezintă numere în baza 10. Parametrul Precizie specifică numărul total de digiŃi, iar parametrul Scală indică numărul de digiŃi folosiŃi pentru partea fracŃionară.

Exemplu

decimal (4) – valori între -9999 şi +9999;

numeric (6,3) – valori între -999,999 şi +999,999.

DiferenŃa dintre domeniile numeric şi decimal constă în faptul că domeniul numeric are

exact precizia indicată, în timp ce precizia domeniului decimal trebuie luată drept cerinŃa minimă.

Dacă precizia nu este specificată, sistemul utilizează valoarea implicită. Dacă scala nu este specificată, se presupune a fi zero.

Domeniile integer şi smallint pot fi utilizate când nu este nevoie de parte fracŃionară. 4) Domenii numerice aproximative Aceste domenii permit descrierea numerelor reale, prin intermediul reprezentării în

virgulă mobilă, unde fiecare număr corespunde unei perechi mantisă – exponent. Mantisa este o valoarea fracŃionară iar exponentul este un întreg. Valoarea aproximativă a unui număr real se obŃine înmulŃind mantisa cu puterea lui 10 specificată prin exponent.

Exemplu

160.17E16 0.17 10→ ⋅ ; 0.17 – mantisă; 16 – exponent; valoare = mantisă ⋅ 10exponent

SQL pune la dispoziŃie următoarele domenii numerice aproximative:

42

• float [(Precizie)]

• double precision

• real

Pentru domeniul float se poate furniza, ca parametru, numărul de digiŃi dedicaŃi pentru reprezentarea mantisei (parametrul Precizie). Domeniul double precision reprezintă numere cu o precizie ridicată faŃă de domeniul real.

5) Dată calendaristică şi timp Această familie de domenii a fost introdusă în SQL-2 cu scopul de a oferi un suport

pentru gestiunea informaŃiilor temporale:

• date

• time [(Precizie)] [with time zone]

• timestamp [(Precizie)] [with time zone]

Fiecare domeniu poate fi structurat pe câmpuri. Domeniul date pune la dispoziŃie câmpurile year, month şi day, domeniul time câmpurile hour, minute şi second iar domeniul timestamp pune la dispoziŃie toate câmpurile celor două domenii amintite anterior.

Pentru domeniile time şi timestamp se poate specifica numărul de poziŃii zecimale utilizate în reprezentarea fracŃiunilor de secundă (parametrul Precizie).

Dacă parametrul Precizie este omis, domeniul time va folosi precizie 0 (rezoluŃie la nivel de secundă) iar domeniul timestamp va folosi o precizie de 6 (rezoluŃie la nivel de microsecundă).

Dacă este specificată opŃiunea with time zone, va fi posibilă accesarea a două câmpuri suplimentare: timezone_hour şi timezone_minute (reprezintă diferenŃa dintre timpul local şi timpul Greenwich).

6) Intervale temporale Această familie de domenii oferă posibilitatea reprezentării intervalelor de timp (de

exemplu durata unei acŃiuni). Sintaxa acestor domenii este:

Interval PrimaUnitateDeTimp [to UltimaUnitateDeTimp],

unde PrimaUnitateDeTimp şi UltimaUnitateDeTimp definesc unităŃile de măsură ce trebuie folosite. Grupul unităŃilor de măsură este împărŃit în două grupuri distincte: year şi month pe de o parte şi unităŃile de la day la second pe de altă parte. Această separare are loc deoarece este imposibilă compararea exactă a zilelor şi a lunilor (deoarece o lună poate avea între 28 şi 31 zile). Prima unitate de timp poate fi însoŃită de precizie, care va reprezenta numărul de digiŃi zecimali utilizaŃi pentru reprezentare. Dacă cea mai mică unitate este second, putem specifica numărul de poziŃii zecimale utilizate (precizia). Dacă a doua unitate de timp este şi prima (deci singura) atunci primul parametru reprezintă numărul de poziŃii zecimale semnificative, iar cel de-al doilea poate reprezenta numărul de poziŃii zecimale pentru partea fracŃionară. Dacă precizia nu este specificată, se foloseşte valoarea implicită 2.

Exemplu

interval year(5) to month – permite reprezentarea intervalelor până la 99999 ani şi 11 luni interval day(4) to second(6) – permite reprezentarea intervalelor până la 9999zile, 23 ore, 59 minute şi 59,999999 secunde (precizie de 1 milionime de secundă)

43

5.1.2 Definirea schemei bazei de date SQL permite definirea unei scheme de baze de date ca o colecŃie de obiecte, fiecare

schemă conŃine o mulŃime de domenii, tabele, indici, aserŃii, vederi şi privilegii şi este definită cu ajutorul următoarei sintaxe:

create schema [NumeSchemă] [[authorization] Autorizare] {DefiniŃieElementeDinSchemă}.

Termenul Autorizare reprezintă numele utilizatorului proprietar al schemei; dacă este omis se consideră că utilizatorul care a executat comanda este proprietarul schemei. Dacă NumeSchemă este omis va fi adoptat ca nume al schemei numele utilizatorului ce a executat comanda. După comanda create schema se pot defini obiectele din schema respectivă.

În continuare ne vom ocupa de tabele şi domenii, urmând ca mai târziu să revenim asupra celorlalte obiecte al unei scheme.

5.1.3 Definirea tabelelor Un tabel SQL este format dintr-o mulŃime ordonată de atribute şi o mulŃime, posibil

vidă, de constrângeri. Sintaxa ce permite definirea unui tabel este:

create table NumeTabel (NumeAtribut Domeniu [ValoareImplicită] [Constrângeri]

{, NumeAtribut Domeniu [ValoareImplicită] [Constrângeri]} [,AlteConstrângeri]

)

Fiecare tabel este definit prin furnizarea numelui său (NumeTabel) şi a definiŃiei atributelor sale; fiecare atribut are la rândul său un nume (NumeAtribut), un domeniu şi o posibilă mulŃime de constrângeri ce trebuie satisfăcute de valorile atributului. După ce au fost definite toate atributele, se pot defini alte constrângeri ce implică mai multe atribute. IniŃial tabelul nu conŃine înregistrări, proprietarul deŃinând toate privilegiile asupra tabelului, adică drepturi de a accesa şi de a modifica datele din tabel.

Exemplu

create table Departament ( NumeDept char(20) primary key,

Adresa char(50), Oraş char(20)

)

5.1.4 Domenii utilizator La definirea tabelelor, pe lângă domeniile predefinite pot fi utilizate şi domenii definite

explicit de utilizator. Comanda SQL pentru definirea unui domeniu utilizator pe baza unui domeniu predefinit este:

create domain NumeDomeniu as DomeniuElementar [ValoareImplicită] [Constrângeri]

Un domeniu este caracterizat deci de un nume, de un domeniu elementar (predefinit sau un alt domeniu utilizator), de o posibilă valoare implicită şi de o mulŃime, posibil vidă, de constrângeri (condiŃii ce trebuie îndeplinite de valorile legale din domeniu).

SQL-2 nu dispune de constructori de domeniu de tip structură sau vector. Această limitare este dată de conceptul de model relaŃional de date, model ce impune ca toate atributele să fie definite pe domenii elementare.

44

DeclaraŃia domeniilor asociază un nume de domeniu cu o mulŃime de constrângeri. Acest lucru este util atunci când trebuie să repetăm aceeaşi definiŃie de domeniu în mai multe tabele.

5.1.5 Valori implicite de domeniu Termenul ValoareImplicită din definiŃia domeniilor şi a tabelelor indică valoarea ce va fi

considerată pentru atributul asociat în cazul inserării unei linii ce nu specifică o valoare pentru acel atribut. Dacă este omisă specificarea unei valori implicite, atunci se va utiliza valoarea NULL ca valoare implicită.

Sintaxa pentru specificarea unei valori implicite este:

default <ValoareGenerică | user | NULL>,

unde • ValoareGenerică este o valoare compatibilă cu domeniul asociat; • opŃiunea user setează ca valoare implicită numele de login al utilizatorului ce a

executat comanda de actualizare a tabelului.

Dacă un atribut are asociat un domeniu cu valoare implicită, dar se specifică explicit o altă valoare implicită pentru atribut, atunci va avea câştig de cauză valoarea implicită a atributului.

Exemplu

NumărCopii smallint default 0 – dacă se inserează o linie şi nu se specifică valoarea pentru

acest atribut, atunci acestui atribut i se va atribui valoarea 0. 5.1.6 Constrângeri intra-relaŃionale În timpul definirii atât a domeniilor cât şi a tabelelor există posibilitatea de a defini

constrângeri. Acestea sunt proprietăŃi ce trebuie verificate de fiecare instanŃă a bazei de date. Constrângerile, după cum am mai precizat într-un capitol anterior, se împart în constrângeri intra-relaŃionale (implică o singură relaŃie) şi constrângeri inter-relaŃionale (iau în considerare mai multe relaŃii).

Cele mai simple constrângeri intra-relaŃionale sunt: not NULL, unique şi primary key. Not Null . Această constrângere indică faptul că valoarea NULL nu este admisă ca valoare pentru atributul afectat de constrângere. În acest caz valoarea atributului trebuie să fie specificată la inserare. Este posibilă inserarea unei linii fără a specifica valoarea unui atribut cu constrângere not NULL doar dacă pentru atributul respectiv s-a definit o valoare implicită diferită de valoarea NULL. Specificarea acestei constrângeri se face prin adăugarea cuvintelor cheie not NULL la definirea atributului.

Exemplu

Nume character(20) not NULL Unique. O constrângere de tip unique se aplică unui atribut (sau unei mulŃimi de atribute) dintr-un tabel şi impune ca atributul, respectiv mulŃimea de atribute să formeze o (super) cheie. Se impune astfel ca linii diferite să nu conŃină aceleaşi valori. ExcepŃie face valoarea NULL, care poate apărea în diverse linii fără a încălca constrângerea. Aceasta se datorează faptului că fiecare valoare NULL reprezintă o valoare necunoscută diferită de ce a altei valori NULL.

45

Există două moduri de definire a acestei constrângeri. Prima variantă se utilizează doar în cazul în care constrângerea implică un singur atribut şi constă în adăugarea cuvântului cheie unique la definirea atributului.

Exemplu

NrInreg numeric(4) unique A doua variantă se aplică în cazul în care constrângerea implică mai multe atribute şi

constă în utilizarea clauzei unique (Atribut{, Atribut}) după definirea atributelor. Exemplu

Nume character(20) not NULL, Prenume character(20) not NULL, Unique (Nume, Prenume)

Primary key. Această constrângere poate fi specificată o singură dată pentru un tabel şi poate fi definită pe un singur atribut sau pe o mulŃime de atribute. DefiniŃia unei astfel de constrângeri implică definirea implicită a unor constrângeri not NULL pentru atributul (atributele) cheii primare.

Exemplu

Nume character(20), Prenume character(20), Dept character(15), Salariu numeric(9) default 0, primary key (Nume, Prenume)

5.1.7 Constrângeri inter-relaŃionale După cum am mai precizat într-o secŃiune anterioară, cele mai importante constrângeri inter-relaŃionale sunt constrângerile de integritate referenŃială. ConstrucŃia utilizată de SQL pentru definirea acestora este constrângerea de tip foreign key. Această constrângere impune ca pentru fiecare linie dintr-un tabel (numit tabel intern), valoarea corespunzătoare unui atribut, diferită de NULL, să se regăsească printre valorile unui atribut din liniile unui alt tabel (numit tabel extern). Singura cerinŃă impusă de sintaxă este ca atributul referit din tabelul extern să fie subiectul unei constrângeri unique. Această cerinŃă este satisfăcută dacă atributul în cauză formează o cheie primară pentru tabelul extern. Constrângerile de referinŃă pot fi definite în două moduri.

În cazul în care în constrângere este implicat un singur atribut se utilizează construcŃia sintactică references, care indică tabelul extern şi atributul asociat.

Exemplu

Create table Angajati

( NrInreg numeric(6) primary key, Nume character(20) not NULL, Prenume character(20) not NULL, Dept character(15)

references Departament (NumeDept), Salariu numeric(9) default 0, Oras character(15), unique (Nume, Prenume) )

46

Dacă legătura se face între o mulŃime de atribute se va utiliza construcŃia foreign key, plasată după definirea tuturor atributelor. Această construcŃie listează mai întâi atributele constrânse din tabelul intern, urmate de numele tabelului extern şi de numele atributelor referite.

Exemplu

Create table Angajati

( NrInreg numeric(6) primary key, Nume character(20) not NULL, Prenume character(20) not NULL, Dept character(15)

references Departament (NumeDept), Salariu numeric(9) default 0, Oras character(15), unique (Nume, Prenume), foreign key (Nume, Prenume) references DatePersonale (Nume, Prenume) )

În cazul constrângerilor discutate până acum sistemul va rejecta (generând un mesaj de

eroare) orice operaŃie de actualizare ce violează constrângerea. Pentru constrângerea de referinŃă SQL permite utilizatorului selecŃia acŃiunii ce va fi executată în cazul violării constrângerii.

Vom ilustra acest aspect considerând exemplul anterior. Să considerăm constrângerea de tip cheie externă asupra atributului Dept în tabelul

ANGAJATI. Constrângerea poate fi violată operând atât asupra liniilor din tabelul intern ANGAJATI cât şi asupra liniilor din tabelul extern DEPARTAMENT. Există doar două căi de a încălca constrângerea prin modificarea conŃinutului tabelului intern: prin inserarea unei linii invalide sau prin modificarea atributului Dept. În aceste cazuri nu este oferit un suport special, operaŃiile fiind pur şi simplu rejectate.

Pe de altă parte, se oferă opŃiuni variate de reacŃie la încălcarea constrângerii de referinŃă determinată de modificarea tabelului extern. Această diferenŃă faŃă de cazul anterior este dată de importanŃa tabelului extern care, din punctul de vedere al aplicaŃiei, reprezintă tabelul principal (master). Tabelul intern (slave) trebuie să se adapteze la schimbările produse în tabelul extern.

OperaŃiile asupra tabelului extern care pot produce violări ale constrângerii de referinŃă sunt modificarea valorilor atributelor referite şi ştergerea de înregistrări (în exemplul anterior modificarea atributului NumeDept respectiv ştergerea de înregistrări din tabelul DEPARTAMENT).

Tipul reacŃiei la astfel de încălcări ale constrângerii diferă în funcŃie de comanda ce a generat violarea constrângerii.

În cazul operaŃiilor de actualizare este posibilă reacŃia în unul din modurile următoare: • cascade: noua valoare pentru atributul din tabelul extern va fi atribuită tuturor liniilor

corespunzătoare din tabelul intern; • set NULL: valoarea NULL este asignată atributului din tabelul intern în locul valorii

modificate din tabelul extern; • set default: atributului din tabelul intern îi va fi asignată valoarea implicită în locul

valorii modificate în tabelul extern; • no action: operaŃia de actualizare este rejectată. OpŃiunile disponibile în cazul încălcării constrângerii de referinŃă prin ştergerea de

înregistrări din tabelul extern sunt:

47

• cascade: vor fi şterse toate liniile din tabelul intern corespunzătoare liniei şterse din tabelul extern;

• set NULL: se asignează valoarea NULL atributului din tabelul intern în locul valorii şterse din tabelul extern;

• set default: atributul din tabelul intern va primi valoarea implicită în locul valorii şterse din tabelul extern;

• no action: operaŃia de ştergere este rejectată.

Specificarea modului de reacŃie în cazul violării unei constrângeri de referinŃă se face imediat după definirea constrângerii prin sintaxa:

on <delete | update> <cascade | set NULL | set default | no action>

Exemplu

Create table Angajati

( NrInreg numeric(6), Nume character(20) not NULL, Prenume character(20) not NULL, Dept character(15) Salariu numeric(9) default 0, Oras character(15), primary key (Nr Inreg), foreign key (Dept) references Departament (NumeDept) on delete set NULL on update cascade, unique (Nume, Prenume) )

5.1.8 Actualizarea schemei unei relaŃii Comenzile definite în SQL pentru manipularea schemelor unei baze de date sunt alter şi

drop.

Comanda alter. Această comandă permite modificarea domeniilor şi schemelor de tabele şi poate avea o varietate de forme:

alter domain NumeDomeniu <set default ValoareImplicită | drop default | add constraint DefiniŃieConstrângere | drop constraint NumeConstrângere>

alter table NumeTabel <alter column NumeAtribut

<set default ValoareImplicită | drop default> | add constraint DefiniŃieConstrângere | drop constraint NumeConstrângere | add column DefiniŃieAtribut | drop column NumeAtribut>

Prin utilizarea celor două comenzi se pot opera următoarele modificări asupra domeniilor şi tabelelor:

• adăugare / eliminare constrângeri;

• modificare valoare implicită;

48

• adăugare / eliminare atribute din schema unui tabel.

Notă. În momentul definirii unei noi constrângeri, datele din tabel trebuie să satisfacă acea constrângere. În caz contrar definiŃia constrângerii nu va fi validată. Comanda drop. Această comandă permite eliminarea datelor de tip schemă, domeniu, tabel, vedere sau aserŃie (constrângere ce nu este asociată unui anumit tabel). Sintaxa comenzii drop este:

drop <schema | domain | table | view | assertion> NumeComponentă [<restrict | cascade>]

OpŃiunea restrict specifică faptul că această componentă nu va fi validată în situaŃia în care componenta nu este vidă sau este referită în definiŃia altei componente. Astfel o schemă nu va fi eliminată dacă ea conŃine tabele sau alte componente; un domeniu nu va fi eliminat dacă apare în definiŃia unui tabel ş.a.m.d. Această opŃiune este implicită.

Folosind opŃiunea cascade componenta este eliminată împreună cu toate componentele dependente. Eliminarea unei scheme care nu este vidă va conduce la eliminarea tuturor obiectelor care intră în componenŃa sa. Ştergerea unui tabel folosind această opŃiune implică ştergerea tuturor liniilor din tabel. OpŃiunea cascade trebuie utilizată cu mare atenŃie deoarece în cazul în care există dependenŃe care nu au fost luate în calcul, rezultatul poate fi diferit de cel scontat. Multe din sistemele comerciale oferă posibilitatea testării rezultatului comenzii drop cu opŃiunea cascade înainte de execuŃia efectivă a comenzii.

5.2 Interogări în SQL Cererile de interogare exprimate în SQL prezintă un aspect declarativ deoarece sunt specificate proprietăŃile rezultatului şi nu modul de obŃinere (SQL urmăreşte principiile calculului relaŃional). Cererile SQL sunt pasate pentru execuŃie optimizatorului de cereri. Optimizatorul de cereri este o componentă a sistemului de gestiune a bazelor de date care analizează cererea, selectează o strategie de execuŃie şi formulează o cerere echivalentă în limbajul procedural intern al sistemului de gestiune a bazelor de date. 5.2.1 Interogări simple

Interogarea unei baze de date poate fi exprimată în SQL prin intermediul instrucŃiunii select, care are sintaxa:

select ExprAtribut [[as] Alias ] {, ExprAtribut [[as] Alias]} from NumeTabel [[as] Alias ] {, NumeTabel [[as] Alias]} [where Conditie]

O cerere SQL va lua în considerare doar liniile ce aparŃin produsului cartezian al tabelelor listate în clauza from şi va stabili liniile ce satisfac condiŃia exprimată în clauza where. Rezultatul execuŃiei unei cereri SQL este un tabel, având câte o linie pentru fiecare linie selectată de clauza where şi ale cărui coloane rezultă din evaluarea expresiilor ExprAtribut ce apar în clauza select (lista Ńintă). Fiecare coloană poate fi redenumită cu ajutorul unui Alias ce urmează imediat după expresie. Tabelele pot fi de asemenea redenumite prin intermediul unui Alias.

Exemplu Se consideră o bază de date care conŃine tabelele

ANGAJATI (Nume, Prenume, Dept, Birou, Salariu), DEPARTAMENT (Dept, Adresa, Oras),

49

cu precizarea ca salariul înregistrat este anual.

ANGAJATI Nume Prenume Dept Birou Salariu Ionescu Maria Administratie 10 45 Popescu Ion Productie 20 36 Popa Stefan Administratie 20 40 Dumitrescu Vasile Distributie 16 45 Ionescu Ion Planificare 14 80 Manole Radu Planificare 7 73 Luca Doru Administratie 75 40 Vasile Alina Productie 20 46

DEPARTAMENT Dept Adresa Oras Administratie Independentei Iasi Productie Primaverii Bucuresti Distributie Central Focsani Planificare Nicolina Iasi Cercetare Trandafirului Cluj

Fig. 5.1 ConŃinutul tabelelor ANGAJATI şi DEPARTAMENT Interogarea 1: Să se găsească salariile angajaŃilor cu numele Ionescu.

select Salariu as SalariuAnual from Angajati where Nume = ’Ionescu’

Rezultatul acestei interogări se poate observa în figura 5.2

Salariu

45 80

Fig. 5.2. Rezultatul interogării 1

Lista Ńintă. Lista Ńintă specifică elementele schemei tabelelor rezultat. Caracterul special * poate să apară în lista Ńintă şi reprezintă selecŃia tuturor atributelor tabelelor precizate în clauza from.

Exemplu Interogarea 2: Să se găsească toate informaŃiile referitoare la angajatul cu numele Ionescu.

select * from Angajati where Nume = ’Ionescu’

Rezultatul acestei interogări se poate observa în figura 5.3

Nume Prenume Dept Birou Salariu Ionescu Maria Administratie 10 45 Ionescu Ion Planificare 14 80

Fig. 5.3 Rezultatul interogării 2

50

Lista Ńintă poate conŃine expresii ce utilizează valorile atributelor din fiecare linie selectată.

Exemplu Interogarea 3: GăsiŃi salariul lunar al angajaŃilor cu numele Popescu. Rezultatul este prezentat în figura 5.4.

select Salariu/12 as SalariuLunar from Angajati where Nume = ’Popescu’

SalariuLunar

3.00

Fig. 5.4 Rezultatul interogării 3 Clauza from. Dacă o interogare implică înregistrări din mai multe tabele, argumentul din clauza from va reprezenta o listă de tabele. CondiŃiile din clauza where sunt aplicate în acest caz produsului cartezian al acestor tabele; se poate specifica o joncŃiune prin indicarea explicită a comparaŃiilor între atribute din tabele diferite.

Exemplu Interogarea 4: Să se găsească numele angajaŃilor şi oraşele în care aceştia lucrează.

select Angajati.Nume, Angajati.Prenume, Departament.Oras from Angajati, Departament where Angajati.Dept = Departament.Dept

Rezultatul este prezentat în figura 5.5.

Nume Prenume Oras Ionescu Maria Iasi Popescu Ion Bucuresti Popa Stefan Iasi Dumitrescu Vasile Focsani Ionescu Ion Iasi Manole Radu Iasi Luca Doru Iasi Vasile Alina Bucuresti

Fig. 5.5 Rezultatul interogării 4 În interogarea precedentă s-a folosit operatorul punct (’.’) pentru identificarea tabelului din care se extrag atributele. Folosirea acestei construcŃii este necesară în cazul în care tabelele din clauza from au atribute cu acelaşi nume, pentru a distinge între referinŃele la atribute omonime. În cazul în care nu există posibilitatea apariŃiei unei ambiguităŃi se poate specifica atributul fără a preciza tabelul căruia îi aparŃine. Într-o interogare se pot utiliza alias-uri pentru tabele cu scopul de a scurta referinŃa la acestea.

51

Exemplu

Interogarea 4 se poate exprima astfel

select a.Nume, a.Prenume, d.Oras from Angajati a, Departament d where a.Dept = d.Dept

Clauza where. CondiŃia din clauza where este o expresie booleană formată prin combinarea predicatelor simple cu operatorii and, or şi not. Fiecare predicat simplu utilizează operatorii de comparaŃie (=, >, >=, <, <=, <>) şi are, într-un membru, o expresie formată din valori ale atributelor dintr-o linie şi în celălalt membru o valoarea constantă sau o altă expresie. Prioritar este operatorul not, dar nu se introduce o precedenŃă între and, şi or. Dacă într-o expresie se folosesc ambii operatori and şi or este indicată specificarea precedenŃei prin utilizarea parantezelor.

Exemplu

Interogarea 5: Să se găsească prenumele angajaŃilor cu numele Ionescu care lucrează în departamentele Administratie sau Productie.

select Prenume from Angajati where Nume = ’Ionescu’ and (Dept = ’Administratie’ or Dept = ’Productie’)

Rezultatul este prezentat în figura 5.6.

Prenume Maria

Fig. 5.6 Rezultatul interogării 5 Pentru compararea şirurilor de caractere SQL pune la dispoziŃie operatorul like. Acest

operator compară un şir cu alt şir, specificat parŃial cu ajutorul caracterelor speciale ’_’ şi ’% ’. Caracterul ’_’ substituie un caracter oarecare iar ’% ’ substituie un şir oarecare de caractere, posibil vid.

Exemplu ComparaŃia like ’ab%ba_’ va fi satisfăcută de toate şirurile de caractere ce încep cu secvenŃa ab şi au în componenŃă perechea ba înainte de ultimul caracter.

Gestiunea valorilor NULL. Pentru selecŃia atributelor având valori NULL SQL prevede predicatul is NULL care este adevărat doar dacă atributul are valoarea NULL. Predicatul is not NULL este adevărat în caz contrar celui prezentat anterior. Sintaxa este:

Atribut is [not] NULL

Duplicate. O diferenŃă majoră între SQL şi algebra, respectiv calculul relaŃional constă în gestiunea înregistrărilor duplicat. În algebra relaŃională şi în calculul relaŃional un tabel este văzut ca o relaŃie matematică, mai exact ca o mulŃime de tupluri distincte între ele. În SQL un tabel poate avea mai multe linii ce conŃin aceleaşi valori pentru toate atributele (duplicate). Dacă se doreşte emularea comportării din algebra relaŃională în SQL ar trebui eliminate toate duplicatele la fiecare execuŃie a unei operaŃii de proiecŃie. Deoarece operaŃia de eliminare a duplicatelor este consumatoare de timp şi adesea nu este necesară, executarea acestei operaŃii este lăsată la latitudinea persoanei ce implementează interogarea.

52

Eliminarea duplicatelor este specificată prin cuvântul cheie distinct, plasat imediat după select:

select [<distinct | [all]>]

OpŃiunea all indică faptul că vor fi păstrate toate înregistrările din rezultat (deci inclusiv duplicatele) şi este opŃiunea implicită.

Exemplu Se consideră tabelul PERSOANA (Cod, Nume, Prenume, Oras) prezentat în figura următoare:

PERSOANA Cod Nume Prenume Oras IS001122 Ionescu Maria Iasi BC012345 Popescu Ion Bucuresti IS123456 Ionescu Vasile Iasi SV342345 Ionescu Radu Suceava

Fig. 5.7 ConŃinutul tabelei PERSOANA Interogarea 6: Să se găsească oraşele în care locuiesc persoanele cu numele Ionescu.

select Oras from Persoana where Nume = ’Ionescu’

Interogarea 7: Să se găsească oraşele în care locuiesc persoanele cu numele Ionescu, fiecare oraş apărând o singură dată.

select distinct Oras from Persoana where Nume = ’Ionescu’

Prin executarea celor două interogări se obŃin rezultatele din figura 5.8

Oras Iasi Iasi Suceava

Oras Iasi Suceava

Fig. 5.8 Rezultatele interogărilor 6 şi 7

JoncŃiuni . SQL-2 introduce o sintaxă alternativă pentru specificarea joncŃiunilor, fiind astfel posibilă realizarea unei distincŃii între condiŃiile ce reprezintă condiŃii de joncŃiune şi cele ce reprezintă selecŃii de linii.

Sintaxa propusă este:

select ExprAtribut [[as] Alias] {, ExprAtribut [[as] Alias]} from NumeTabel [[as] Alias] {[TipJonctiune] join NumeTabel [[as] Alias] on CondJonctiune} [where AlteConditii]

53

În acest fel, condiŃia de joncŃiune este mutată din clauza where în clauza from. Parametrul TipJonctiune specifică tipul joncŃiunii: inner, left sau full. Inner join corespunde theta-joncŃiunii din algebra relaŃională.

Exemplu

Interogarea 4 se poate rescrie sub forma

select Nume, Prenume, Oras from Angajati a inner join Departament d on a.Dept = d.Dept

În cazul unei joncŃiuni, liniile dintr-un tabel ce nu au linii corespondente în celălalt tabel

vor fi eliminate din rezultat. Pentru a forŃa apariŃia unor astfel de linii în rezultat se poate apela la joncŃiunea externă, cu cele trei variante:

• left join – furnizează acelaşi rezultat ca şi inner join, dar include liniile tabelului ce apare în stânga joncŃiunii pentru care nu există linii corespondente în tabelul din dreapta;

• right join – păstrează liniile tabelului din dreapta ce nu au corespondent în tabelul din stânga;

• full join – furnizează acelaşi rezultat ca şi inner join, suplimentat cu liniile excluse din ambele tabele.

Exemplu Se consideră o bază de date care conŃine tabelele SOFERI (Nume, Prenume, ID) şi AUTOVEHICULE (NrInreg, Marca, Model, ID) prezentate în figura 5.9.

SOFERI Nume Prenume ID Ionescu Maria VR 001Y Popescu Ion PZ 111B Popa Stefan AP 222C

AUTOVEHICULE NrInreg Marca Model ID IS01AAA BMW 323 VR 001Y SV02BBB BMW Z3 VR 001Y IS02CCC Lancia Delta PZ 111B IS01EFD BMW 316 MI 222C

Fig. 5.9 ConŃinutul tabelelor SOFERI şi AUTOVEHICULE Interogarea 8: Să se găsească şoferii ce deŃin autovehicule, incluzând şi şoferii fără autovehicule.

select Nume, Prenume, Soferi.ID, NrInreg, Marca, Model from Soferi left join Autovehicule on

(Soferi.ID = Autovehicule.ID) Rezultatele sunt prezentate în figura 5.10

Nume Prenume ID NrInreg Marca Model Ionescu Maria VR 001Y IS01AAA BMW 323 Ionescu Maria VR 001Y SV02BBB BMW Z3 Popescu Ion PZ 111B IS02CCC Lancia Delta Popa Stefan AP 222C NULL NULL NULL

Fig. 5.10 Rezultatul interogării 8

54

Interogarea 9: Să se găsească toŃi şoferii şi toate maşinile împreună cu posibilele relaŃii între ele.

select Nume, Prenume, Soferi.ID, NrInreg, Marca, Model from Soferi full join Autovehicule on

(Soferi.ID = Autovehicule.ID) Rezultatele sunt prezentate în figura 5.11

Nume Prenume ID NrInreg Marca Model Ionescu Maria VR 001Y IS01AAA BMW 323 Ionescu Maria VR 001Y SV02BBB BMW Z3 Popescu Ion PZ 111B IS02CCC Lancia Delta Popa Stefan AP 222C NULL NULL NULL NULL NULL NULL IS01EFD BMW 316

Fig. 5.11 Rezultatul interogării 9

Unele implementări de SQL specifică joncŃiunea externă prin adăugarea unui caracter special sau a unei secvenŃe de caractere (* sau (+)) la atributele implicate în condiŃia de joncŃiune.

Exemplu Interogarea 8 se poate exprima sub forma

select Nume, Prenume, Soferi.ID, NrInreg, Marca, Model from Soferi , Autovehicule where Soferi.ID * = Autovehicule.ID

SQL-2 oferă posibilitatea realizării joncŃiunii naturale (joncŃiune pe baza atributelor cu

acelaşi nume) a două tabele prin utilizarea cuvântului cheie natural în faŃa tipului joncŃiunii. Exemplu Interogarea 9 se poate exprima sub forma

select Nume, Prenume, Soferi.ID, NrInreg, Marca, Model from Soferi natural full join Autovehicule

În mod normal, joncŃiunea naturală nu este disponibilă în sistemele comerciale. Motivele

acestei excluderi sunt : - comportarea unei interogări se poate modifica în mod semnificativ ca rezultat al unei

mici modificări a schemei; - joncŃiunea naturală impune analizarea completă a schemelor tabelelor implicate, cu

scopul de a înŃelege condiŃia de joncŃiune. Utilizarea variabilelor. Am văzut mai înainte cum se pot asocia nume alternative, numite alias-uri, tabelelor din clauza from şi avantajele folosirii acestora. Prin folosirea alias-urilor se poate referi un tabel de mai multe ori, într-un mod similar operatorului de redenumire ρ din algebra relaŃională. De fiecare dată când este introdus un alias, se declară o variabilă de tip tabel care are ca valoare conŃinutul tabelului pentru care se introduce alias-ul. Când un tabel apare doar o singură dată în interogare, nu este nici o diferenŃă în a

55

interpreta alias-ul ca pseudonim sau ca o nouă variabilă. Când tabelul apare de mai multe ori este esenŃial să privim alias-ul ca o nouă variabilă.

Exemplu Interogarea 10: Se consideră baza de date din figura 5.1. Să se găsească toŃi angajaŃii ce au acelaşi nume (dar prenume diferite) cu un angajat care lucrează în departamentul ProducŃie.

select a1.Nume, a1.Prenume from Angajati a1, Angajati a2 where a1.Nume = a2.Nume and a1.Prenume <> a2.Prenume and a2.Dept = ’Productie’

Utilizarea alias-urilor de tabel are importanŃă din următoarele puncte de vedere:

• se evită necesitatea scrierii întregului nume al tabelului ori de câte ori este cerut acest lucru;

• se poate face referire de mai multe ori la acelaşi tabel; introducerea unui alias are semnificaŃia declarării unei variabile de tip tabel, ce are acelaşi conŃinut cu tabelul al cărui alias este;

• se pot specifica cererile imbricate.

Ordonarea. În general, rezultatul unei interogări conŃine linii, aranjate într-o ordine oarecare. Dacă se doreşte impunerea unei ordonări după un anumit criteriu asupra liniilor returnate de o interogare se va utiliza clauza order by. Sintaxa este:

order by Atribut [asc | desc] {, Atribut [asc | desc]}

Exemplu Se consideră baza de date din figura 5.9.

Interogarea 11: ExtrageŃi conŃinutul tabelului AUTOVEHICULE în ordine descendentă după Marcă şi Model.

select * from Autovehicule order by Marca desc, Model desc

Rezultatul este prezentat în figura următoare

NrInreg Marca Model ID IS02CCC Lancia Delta PZ 111B SV02BBB BMW Z3 VR 001Y IS01AAA BMW 323 VR 001Y IS01EFD BMW 316 MI 222C

Fig. 5.12 Rezultatul interogării 11

56

5.2.2 Interogări agregate Operatorii agregaŃi constituie una din cele mai importante extensii ale SQL în comparaŃie

cu algebra relaŃională. În algebra relaŃională toate condiŃiile sunt evaluate pentru un singur tuplu la un moment

dat. Adesea apare necesitatea evaluării unor proprietăŃi ce depind de o mulŃime de tupluri (de exemplu aflarea numărului de angajaŃi ce lucrează în departamentul ProducŃie).

Operatorii agregaŃi sunt:

• count. Operatorul count are următoarea sintaxă:

count ( < * | [distinct | all] ListaAtribute>)

OpŃiunea * returnează numărul de linii din rezultat. OpŃiunea distinct returnează numărul valorilor distincte pentru lista de atribute din

rezultat. OpŃiunea all returnează numărul liniilor ce conŃin valori diferite de NULL pentru lista

de atribute. Dacă se specifică un atribut fără un distinct sau all se consideră opŃiunea implicită all.

Exemplu Se consideră baza de date din figura 5.1

Interogarea 12: Să se găsească numărul angajaŃilor din departamentul Productie.

select count (*) from Angajati where Dept = ’Productie’

Interogarea 13: Să se găsească numărul valorilor distincte pentru atributul Salariu pentru toŃi angajaŃii din tabela ANGAJAłI.

select count (distinct Salariu) from Angajati

Interogarea 14: Să se găsească numărul de linii din tabelul ANGAJATI care au valori diferite de NULL pentru atributul Salariu.

select count (all Salariu) from Angajati

• sum, max, min, avg. Aceşti operatori au următoarea sintaxă:

<sum | max | min | avg> ([distinct | all] ExprAtribut)

Aceşti operatori se aplică liniilor selectate de clauza where a interogării şi au următoarele semnificaŃii:

- sum returnează suma valorilor deŃinute de expresia atribut;

- max şi min returnează valoarea maximă, respectiv minimă;

- avg returnează media valorilor expresiei atribut.

Expresia atribut ExprAtribut poate fi un atribut sau o expresie. Operatorii sum şi avg acceptă ca argument expresii ce reprezintă valori numerice sau

intervale de timp. FuncŃiile min si max necesită definirea unei ordini în expresia atribut, fiind aplicabile

şi asupra şirurilor de caractere şi momentelor de timp.

57

Cuvintele cheie distinct şi all au semnificaŃiile discutate deja la operatorul count.

Exemplu Interogarea 15: Să se găsească salariul maxim, mediu şi minim pentru toŃi angajaŃii din tabela ANGAJAłI.

select max(Salariu), avg(Salariu), min(Salariu) from Angajati

Interogarea 16: Să se găsească salariul maxim pentru angajaŃii care lucrează într-un departament din Iasi.

select max(Salariu) from Angajati a, Departament d where a.Dept = d.Dept and Oras = ’Iasi’

5.2.3 Interogări group by

FuncŃiile agregat prezentate operează pe toate liniile returnate de interogare. În cazul în care se doreşte utilizarea funcŃiilor agregat pe o submulŃime a liniilor selectate SQL pune la dispoziŃie clauza group by. Această clauză specifică modul în care va fi împărŃit tabelul în submulŃime de linii. Clauza acceptă ca argument o mulŃime X de atribute, iar interogarea va opera separat pe fiecare mulŃime de linii ce posedă aceleaşi valori pentru X. Pentru a înŃelege mai bine semnificaŃia clauzei group by să analizăm următorul exemplu:

Exemplu Se consideră tabela ANGAJAłI din figura 5.1

Interogarea 17: Să se găsească suma salariilor angajaŃilor din acelaşi departament.

select Dept, sum(Salariu) from Angajati group by Dept

Într-o primă fază interogarea este executată fără a Ńine cont de clauza group by. De fapt se execută interogarea

select Dept, Salariu from Angajati

Rezultatul acestei interogări este prezentat în figura 5.13.

Dept Salariu Administratie 45 Productie 36 Administratie 40 Distributie 45 Planificare 80 Planificare 73 Administratie 40 Productie 46

Fig. 5.13 ProiecŃia pe atributele Dept şi Salariu a tabelei ANGAJAłI

Tabelul rezultat este apoi împărŃit în mulŃimi ce au aceeaşi valoare pentru atributele listate în clauza group by. Rezultatul acestei grupări este dat în figura 5.14.

58

Dept Salariu Administratie 45 Administratie 40 Administratie 40 Productie 36 Productie 46 Distributie 45 Planificare 80 Planificare 73

Fig. 5.14 Regrupare în acord cu valorile atributului Dept Odată stabilite grupurile de linii, funcŃia agregat se aplică fiecărui grup în parte. Rezultatul final al interogării este tabelul din figura 5.15.

Dept Salariu Administratie 125 Productie 82 Distributie 45 Planificare 153

Fig. 5.15 Rezultatul final al interogării

Sintaxa SQL impune restricŃia ca, ori de câte ori este utilizată clauza group by, atributele ce pot apărea în clauza select să fie o submulŃime a atributelor din clauza group by. Motivul acestei restricŃii va fi prezentat prin următorul exemplu.

Exemplu Se consideră tabelul ANGAJATI din figura 5.1. Fie interogarea

select Birou from Angajati group by Dept

Această interogare este incorectă, deoarece pentru aceeaşi valoare a atributului Dept, atributul Birou deŃine mai multe valori. După ce liniile au fost grupate după atributul Dept, fiecare grup trebuie să corespundă unei singure linii în tabelul returnat de interogare. Interogarea corectă este

select Birou from Angajati group by Dept, Birou

Pentru a lua în considerare doar grupurile de linii ce satisfac anumite condiŃii trebuie utilizată clauza having. Dacă aceste condiŃii pot fi verificate la nivel de linie, atunci este suficientă utilizarea predicatelor corespunzătoare ca argument al clauzei where. Clauza having conŃine condiŃii ce trebuie aplicate la terminarea execuŃiei interogării ce utilizează clauza group by. Fiecare submulŃime de linii va participa la formarea rezultatului doar dacă satisface condiŃia din clauza having. Sintaxa permite ca în clauza having să apară expresii booleene, formate din predicate simple şi operatori booleeni. Predicatele simple pot fi:

• comparaŃii între rezultatul evaluării unei funcŃii agregat şi o expresie generică • comparaŃii între atribute ce formează clauza group by şi o expresie generică.

Se recomandă ca în clauza having să apară doar predicatele ce implică o funcŃie agregat şi restul predicatelor să fie incluse în contextul clauzei where.

59

Exemplu

Se consideră tabelul ANGAJATI din figura 5.1.

Interogarea 18: Să se găsească departamentele în care salariul mediu al angajaŃilor din biroul 20 este mai mare ca 25.

select Dept from Angajati where Birou = ’20’ group by Dept having avg(Salariu) > 25

Forma completă a unei instrucŃiuni select este

InterogareSQL ::= select ListaTinta from ListaTabele [where Conditie] [group by ListaAtributeGrupare] [having ConditieAgregata] [order by ListaAtributeOrdonare]

5.2.4 Interogări cu operatori din teoria mulŃimilor SQL pune la dispoziŃie operatori din teoria mulŃimilor, cum ar fi operatorii de reuniune

(union), intersecŃie (intersect) şi diferenŃă (except sau minus). Este de notat că orice interogare ce utilizează operatorii de intersecŃie şi diferenŃă poate fi exprimată cu ajutorul interogărilor imbricate. Sintaxa pentru utilizarea operatorilor din teoria mulŃimilor este:

InterogareSQL {<union | intersect | except> [all] InterogareSQL}

Operatorii din teoria mulŃimilor presupun eliminarea duplicatelor ca opŃiunea implicită. Dacă se doreşte utilizarea acestor operatori cu menŃinerea duplicatelor este suficientă specificarea opŃiunii all. ObservaŃie. SQL nu impune ca schemele pe care se execută operaŃiile să fie identice (spre deosebire de algebra relaŃională), ci doar ca atributele să aibă domenii compatibile. CorespondenŃa între atribute nu se bazează pe nume, ci pe poziŃia atributelor. Dacă atributele au nume diferite, rezultatul va prelua numele de atribute din primul operand.

Exemple Se consideră tabelul ANGAJATI din figura 5.1.

a) Să se găsească numele şi prenumele tuturor angajaŃilor.

select Prenume as NumeAngajat from Angajati union select Nume from Angajati

b) Să se găsească numele de angajaŃi care sunt şi prenume

select Prenume as NumeAngajat from Angajati intersect select Nume

60

from Angajati

c) Să se găsească numele de angajaŃi care nu sunt şi prenume

select Prenume as NumeAngajat from Angajati except select Nume from Angajati

NumeAngajat Ionescu Popescu Popa Dumitrescu Manole Luca Vasile Maria Ion Stefan Radu Doru Alina

a)

NumeAngajat Vasile

b)

NumeAngajat Ionescu Popescu Popa Dumitrescu Manole Luca

c)

Fig 5.16 Rezultatul interogărilor a), b) şi c) 5.2.5 Interogări imbricate Până acum toate interogările formulate conŃineau în clauza where o condiŃie compusă, în care fiecare predicat reprezintă o comparaŃie între două valori. Se pot defini predicate cu structură complexă, în care o expresie valorică poate fi comparată cu rezultatul execuŃiei unei interogări SQL. Interogarea utilizată în comparaŃie se defineşte în interiorul predicatului din clauza where şi se numeşte interogare imbricată.

În general, primul operand al unei comparaŃii de genul celei amintite anterior este un atribut, în timp ce în celălalt membru avem o mulŃime de valori (rezultatul interogării). Pentru a rezolva această problemă a eterogenităŃii termenilor comparaŃiei, SQL pune la dispoziŃie cuvintele cheie any şi all pentru a extinde operatorii de comparaŃie.

Cuvântul cheie any specifică faptul că linia este validă dacă valoarea atributului se află în relaŃie cu cel puŃin o valoare returnată de interogarea imbricată.

Cuvântul cheie all specifică faptul că linia este validă dacă valoarea atributului se află în relaŃie cu toate valorile returnate de interogare.

Sintaxa cere ca domeniul elementelor returnate de interogarea imbricată să fie compatibil cu atributul cu care se face comparaŃia.

Exemplu

Se consideră tabelele ANGAJATI şi DEPARTAMENT prezentate în figura 5.1.

Interogarea 19: Să se găsească angajaŃii ce lucrează într-un departament din Iaşi.

select Nume, Prenume from Angajati where Dept = any (select Dept from Departament where Oras = ’Iasi’)

ObservaŃie. Această interogare poate fi rezolvată prin realizarea unei joncŃiuni între cele două tabele.

61

Interogarea 20: Să se găsească departamentele în care nu lucrează nici un angajat cu numele Ionescu.

select Dept from Departament where Dept <> all (select Dept from Angajati where Nume = ’Ionescu’)

Această interogare poate fi exprimată cu ajutorul operatorului diferenŃă:

select Dept from Departament except select Dept from Angajati where Nume = ’Ionescu’

SQL pune la dispoziŃie doi operatori pentru a reprezenta apartenenŃa la o mulŃime şi

negaŃia sa: in şi not in. Aceşti operatori sunt echivalenŃi cu = any, respectiv <> all. Să mai facem observaŃia că funcŃiile agregat max şi min pot fi utilizate în interogările

imbricate.

Exemplu

Se consideră tabelele ANGAJATI şi DEPARTAMENT prezentate în figura 5.1.

Interogarea 21: Să se găsească departamentele în care lucrează angajaŃii ce câştigă cel mai mare salariu.

select Dept from Angajati where Salariu = any (select max(Salariu) from Angajati)

sau

select Dept from Angajati where Salariu >= all (select Salariu from Angajati)

ObservaŃii .

• Deşi cele două interogări sunt echivalente, este indicată folosirea funcŃiilor agregat deoarece sunt mai concludente şi se execută mai eficient.

• În cazul primei interogări nu există nici o diferenŃa dacă în loc de operatorul any se foloseşte operatorul all (deoarece interogarea internă are ca rezultat o singură linie).

Pentru a înŃelege mecanismul rezolvării interogărilor ce conŃin interogări imbricate se

pleacă de la presupunerea că interogarea imbricată se execută înaintea analizei liniilor din interogarea externă. Rezultatul interogării poate fi salvat într-o variabilă temporară şi predicatul interogării externe poate fi evaluat cu ajutorul rezultatului temporar.

Uneori interogarea imbricată face referire la contextul interogării în care este imbricată; acest lucru are loc prin intermediul unei variabile definită în interogarea externă şi utilizată în cea internă. Un astfel de mecanism este cunoscut sub numele de transferul legăturilor dintr-un

62

context în altul. În acest caz, noua interpretare pentru interogările imbricate este următoarea: pentru fiecare linie din interogarea externă se evaluează mai întâi interogarea imbricată şi apoi se evaluează predicatul din interogarea externă.

Există o restricŃie privind vizibilitatea variabilelor SQL. O variabilă poate fi utilizată doar în interiorul interogării în care este definită sau în interogarea imbricată din interogarea în care este definită. Dacă o interogare conŃine interogări imbricate pe acelaşi nivel, variabilele declarate în clauza from a unei interogări nu pot fi utilizate în contextul celeilalte interogări.

Vom exemplifica semnificaŃia interogărilor imbricate complexe împreună cu descrierea unui operatorului logic exists. Acest operator acceptă ca parametru o interogare imbricată şi returnează valoarea adevărat doar dacă interogarea nu produce un rezultat vid.

Exemplu

Se consideră relaŃia PERSOANA din figura 5.7.

Interogarea 22: Să se găsească persoanele care au acelaşi nume şi prenume, dar coduri diferite.

select * from Persoana P where exists (select * from Persoana P1 where P1.Nume = P.Nume and P1.Prenume = P.Prenume and P1.Cod <> P.Cod )

În acest caz nu se poate executa interogarea imbricată înaintea evaluării interogării externe, dat fiind că interogarea imbricată nu este definită până când nu se asignează o valoare variabilei P. Este necesar în schimb să se evalueze interogarea imbricată pentru fiecare linie produsă în cadrul interogării externe. În exemplul prezentat vor fi examinate mai întâi liniile variabilei P una câte una. Pentru fiecare din aceste linii va fi executată interogarea imbricată. Această interogare poate fi formulată realizând o joncŃiune a tabelei PERSOANA cu ea însăşi.

O altă cale de a formula interogarea din exemplul anterior este prin folosirea constructorului de tuplu, reprezentat de o pereche de paranteze rotunde care marchează lista de atribute.

Exemplu

Se consideră relaŃia PERSOANA din figura 5.7.

Interogarea 23: Să se găsească toate persoanele ce nu au omonime.

select * from Persoana P where (Nume, Prenume) not in (select Nume, Prenume from Persoana P1 where P1.Cod <> P.Cod )

Sistemele comerciale nu rezolvă întotdeauna interogările imbricate prin scanarea

tabelului extern şi producerea unei interogări pentru fiecare linie din relaŃie. În schimb încearcă să proceseze cât mai multe interogări într-o manieră orientată pe mulŃimi, cu scopul de a manevra cantităŃi mari de date prin cât mai puŃine operaŃii posibile.

63

5.3 Modificarea datelor în SQL Limbajele de manipulare a datelor includ instrucŃiuni pentru interogarea şi modificarea

bazei de date. Pentru modificarea conŃinutului unei baze de date SQL pune la dispoziŃie instrucŃiunile insert, delete şi update.

5.3.1 Inserări în baza de date Sintaxa instrucŃiunii insert este:

insert into NumeTabel [(ListaAtribute)] <values (ListaValori) | InterogareSQL>

Prima variantă permite inserarea unei singure linii în tabele. Argumentul clauzei values reprezintă valorile atributelor pentru linia inserată.

Exemplu

insert into Departament (Dept, Oras) values (’ProducŃie’, ’Suceava’)

A doua variantă permite adăugarea unei mulŃimi de linii, care sunt extrase mai întâi din

baza de date. Exemplu

insert into DepartamenteIasi (select Dept, Adresa from Departament where Oras = ’Iasi’)

Comanda anterioară inserează în tabelul DepartamenteIasi liniile din tabelul Departament care au Iasi ca valoare a atributului Oras. Dacă valorile pentru anumite atribute nu sunt specificate în momentul inserării, se vor

asigna valori implicite sau valori NULL în cazul în care nu sunt definite valori implicite. CorespondenŃa între atributele tabelului şi valorile ce urmează a fi inserate este dictată de

ordinea în care termenii apar în definiŃia tabelului. Astfel primul element din ListaValori (în cazul primei variante) sau primul element din lista Ńintă (în cazul celei de-a doua variante) trebuie să corespundă primului element ce apare în ListaAtribute (sau în definiŃia tabelului dacă ListaAtribute este omisă) şi aşa mai departe pentru celelalte atribute.

5.3.2 Ştergerea înregistrărilor Sintaxa instrucŃiunii delete este:

delete from NumeTabel [where Conditie]

Dacă nu se specifică clauza where se vor şterge toate înregistrările din tabel. În cazul în care instrucŃiunea delete conŃine clauza where se vor şterge doar acele înregistrări ce satisfac condiŃia precizată. CondiŃia poate conŃine şi interogări imbricate ce fac referire la conŃinutul altor tabele. În cazul în care există constrângeri de referinŃă cu opŃiunea cascade în care tabelul este referit ştergerea unor linii din tabel poate duce la ştergerea unor linii aparŃinând altor tabele.

Exemplu

delete from Departament where Dept not in (select Dept from Angajati)

Comanda de mai sus şterge departamentele fără angajaŃi.

64

Este de notat diferenŃa dintre comanda delete şi comanda drop definită în secŃiunea 5.1.8.

O comandă de genul

delete from Departament

şterge toate liniile din tabelul DEPARTAMENT, şi posibil toate liniile tabelelor care sunt legate prin constrângeri de referinŃă de acesta, dacă opŃiunea cascade este precizată ca eveniment la ştergere. Schema bazei de date rămâne neschimbată, comanda modificând doar instanŃa bazei de date. Comanda

drop table Departament cascade

are acelaşi efect ca şi comanda anterioară, dar în acest caz schema bazei de date se modifică, tabelul DEPARTAMENT fiind şters, la fel ca şi vederile sau tabelele care se referă la el în definiŃiile lor. Comanda

drop table Departament restrict

eşuează dacă există linii în tabelul DEPARTAMENT. 5.3.3 Actualizarea înregistrărilor Sintaxa instrucŃiunii update este

update NumeTabel set Atribut = <Expresie | InterogareSQL | NULL | default> {, Atribut = <Expresie | InterogareSQL | NULL | default>} [where Conditie]

InstrucŃiunea update face posibilă actualizarea unuia sau a mai multor atribute din liniile tabelului NumeTabel ce satisfac o posibilă Conditie. Dacă nu apare clauza where se vor actualiza toate liniile din tabel. Noua valoare ce va fi asignată unui atribut poate fi:

• rezultatul evaluării unei expresii, definită pe atributele din tabel;

• rezultatul unei interogări SQL;

• valoarea NULL;

• valoarea implicită a domeniului de definiŃie.

Exemplu

Comanda

update Angajati set Salariu = Salariu * 1.1 where Dept = ’Administratie’

produce o creştere cu 10% a salariilor angajaŃilor din departamentul Administratie.

Natura SQL, care este orientat pe operaŃiile cu mulŃimi, trebuie luată în considerare când se scriu comenzi de actualizare.

Să presupunem că se doreşte modificarea salariilor angajaŃilor astfel: creşterea salariilor sub 30 mii cu 10% şi a salariilor peste 30 mii cu 15%. O cale de a face actualizarea este execuŃia următoarelor comenzi:

update Angajati set Salariu = Salariu * 1.1 where Salariu <= 30

update Angajati set Salariu = Salariu * 1.15 where Salariu > 30

65

Să presupunem că avem un angajat care câştigă 28 mii, deci satisface condiŃia din prima comanda de actualizare şi atributul Salariu va fi setat la 30.8 mii. În acest moment, linia satisface de asemenea şi condiŃia celei de-a doua actualizări, deci salariul va fi modificat din nou. Creşterea pentru salariatul respectiv va fi de 26.5%.

Această problemă particulară poate fi rezolvată prin inversarea celor două operaŃii de actualizare.

În situaŃiile mai complexe soluŃia ar putea să necesite introducerea unor actualizări intermediare sau utilizarea unui limbaj de programare de înalt nivel care foloseşte cursori. Această tehnică va fi prezentată în lucrările de laborator. 5.4 Alte definiŃii de date în SQL Având descrise în acest moment interogările în SQL se poate completa prezentarea

componentelor unei scheme a unei baze de date cu clauza check, a aserŃiilor şi a primitivelor pentru definirea vederilor.

5.4.1 Constrângerea de integritate de tip check Pentru specificarea altor constrângeri decât cele discutate până acum, SQL-2 a introdus

constrângerea check, care are următoarea sintaxă:

check (CondiŃie)

CondiŃiile ce pot fi utilizate sunt cele ce pot apărea în clauza where a unei interogări SQL. CondiŃia impusă trebuie verificată întotdeauna pentru a menŃine corectitudinea bazei de date. În acest fel pot fi specificate toate constrângerile pe tuplu discutate anterior, deoarece condiŃia din constrângerea check poate face referire la alte atribute.

Pentru exemplificarea acestei construcŃii vom redefini schema tabelului ANGAJATI din secŃiunea 5.1.7:

Exemplu

create table Angajati

(NrInreg character(6) check ( NrInreg is not NULL and 1 = (select count(*) from Angajati a where NrInreg = a.NrInreg)), Nume character(20) not NULL check ( Nume is not NULL), Prenume character(20) check ( Prenume is not NULL and 1 = (select count(*) from Angajati a where Prenume = a.Prenume and Nume = a.Nume)), Dept character(15) check (Dept in (select Dept from Departament)))

ObservaŃii

• constrângerile predefinite permit o reprezentare compactă şi mai uşor de citit;

• prin utilizarea clauzei check se pierde posibilitatea specificării unei reacŃii în cazul încălcării constrângerii;

• când se utilizează constrângeri predefinite, sistemul le recunoaşte imediat şi le poate verifica mult mai eficient.

66

5.4.2 AserŃii AserŃiile reprezintă constrângeri ce nu sunt asociate unei anumite linii sau unui anumit

tabel în particular şi fac parte din schema bazei de date. AserŃiile permit definirea tuturor constrângerilor prezentate şi, în plus, permit definirea unor constrângeri care nu pot fi exprimate altfel (de exemplu constrângeri între mai multe tabele, constrângeri ce impun ca un tabel să aibă o anumită cardinalitate). AserŃiile au un nume şi pot fi şterse cu ajutorul comenzii drop.

Sintaxa ce permite definiŃia aserŃiilor este:

create assertion NumeAsertie check (Conditie)

Exemplu

create assertion CelPutinUnAngajat check ( 1<= (select count(*) from Angajati)) Această constrângere impune ca tabelul ANGAJATI sa aibă cel puŃin o înregistrare.

Constrângerile de integritate check sau aserŃie pot fi imediate sau întârziate.

Constrângerile imediate sunt verificate după fiecare modificare a bazei de date, în timp ce constrângerile întârziate sunt verificate la sfârşitul unei secvenŃe de modificări a bazei de date, numită tranzacŃie. Încălcarea unei constrângeri imediate de o instrucŃiune reface starea bazei de date din momentul anterior execuŃiei instrucŃiunii. Dacă o constrângere întârziată este încălcată se va reface starea bazei de date din momentul anterior începerii tranzacŃiei.

În interiorul unui program se poate seta tipul unei constrângeri la imediată sau întârziată cu ajutorul comenzilor

set constraints [NumeConstrangere] immediate sau

set constraints [NumeConstrangere] deferred. 5.4.3 Vederi În capitolul 3 vederile au fost introduse ca fiind tabele ‚virtuale’ al căror conŃinut depinde

de conŃinutul altor tabele din baza de date. În SQL vederile sunt definite prin asocierea unui nume şi a unei liste de atribute cu rezultatul execuŃiei unei interogări.

O vedere se defineşte folosind comanda:

create view NumeVedere [(ListaAtribute)] as InterogareSQL [with [<local | cascaded>] check option]

Interogarea SQL şi schema vederii trebuie să aibă acelaşi număr de atribute. Exemplu

Să se definească vederea AngajatiAdmin care va conŃine toŃi angajaŃii din departamentul AdministraŃie şi care au salariul mai mare ca 10.

create view AngajatiAdmin (NrInreg, Nume, Prenume, Salariu) as select NrInreg, Nume, Prenume, Salariu from Angajati where Dept = ’Administratie’ and Salariu > 10

În cazul anumitor vederi se pot efectua operaŃii de actualizare, dar aceste operaŃii trebuie

translate în instrucŃiuni de modificare a tabelelor ce stau la baza vederii. Nu întotdeauna se pot

67

găsi soluŃii de modificare a tabelelor de bază, mai ales în situaŃiile în care vederea se defineşte pe baza unei joncŃiuni între mai multe tabele.

În general sistemele comerciale permit modificarea unei vederi doar dacă este definită pe un singur tabel; alte sisteme cer ca atributele vederii să conŃină măcar o cheie primară a tabelului de bază.

Clauza check option specifică faptul că operaŃiile de actualizare se pot face numai asupra liniilor ce aparŃin vederii şi după actualizare liniile continuă să aparŃină vederii. Când o vedere este definită pe baza altor vederi, opŃiunile local sau cascaded specifică dacă ştergerea liniilor se face la nivel local sau trebuie propagată la toate vederile de care depinde vederea în cauză. OpŃiunea implicită este cascaded.

Exemplu

Să se definească vederea AngajatiAdmin1, bazată pe vederea AngajatiAdmin, care va conŃine toŃi angajaŃii din departamentul AdministraŃie şi care au salariul între 10 şi 50.

create view AngajatiAdmin1 as select * from AngajatiAdmin where Salariu < 50 with check option

Încercarea de a da valoarea 8 atributului Salariu nu va fi acceptată de definiŃia curentă a vederii, dar ar fi fost validată dacă check option ar fi fost definită ca local. Încercarea de a modifica valoarea atributului Salariu pentru o linie din vedere la valoarea 60 nu ar fi validată nici cu opŃiunea local. Vederile pot fi utilizate în SQL pentru formularea unor interogări care altfel ar fi

imposibil de exprimat. În general, vederile pot fi considerate ca fiind unelte ce măresc posibilitatea creării interogărilor imbricate.

Exemplu

Interogarea 24: Să se găsească departamentul cu cel mai mare buget alocat salariilor.

create view BugetSalarii (Dept, SalariuTotal) as select Dept, sum(Salariu) from Angajati group by Dept

select Dept from BugetSalarii where SalariuTotal = (select max(SalariuTotal) from BugetSalarii)

5.5 Controlul accesului la baza de date Mecanismele de protecŃie a datelor reprezintă un aspect important al aplicaŃiilor moderne

ce lucrează cu baze de date. În acest sens, administratorul bazei de date are posibilitatea de a alege şi de a implementa politici adecvate de control al accesului la baza de date.

SQL a fost proiectat astfel încât fiecare utilizator poate fi identificat în două moduri: ca utilizator al sistemului de operare şi ca utilizator al bazei de date.

5.5.1 Resurse şi privilegii Resursele protejate de sistem sunt în general tabelele, dar pot fi protejate şi alte

componente, cum ar fi atributele unor tabele, vederi sau domenii.

68

Ca regulă generală, utilizatorul care creează o resursă este proprietarul ei şi este autorizat să efectueze orice operaŃie asupra acelei resurse. Din cauza acestei limitări, SQL pune la dispoziŃie mecanisme de organizare ce permit administratorului să specifice acele resurse la care utilizatorii au acces şi cele la care nu au acces. Prin intermediul acestor mecanisme utilizatorii dispun de privilegii de acces la resursele sistemului.

Fiecare privilegiu de acces este caracterizat de:

• resursa la care face referire;

• utilizatorul ce acordă privilegiul;

• utilizatorul ce primeşte privilegiul;

• operaŃia permisă asupra resursei;

• posibilitatea acordării privilegiului altor utilizatori.

În momentul creării unei resurse, sistemul acordă, în mod automat, toate privilegiile asupra resursei creatorului său. Există un utilizator predefinit, _system, asociat administratorului bazei de date, ce deŃine toate privilegiile asupra tuturor resurselor.

Tipurile de privilegii disponibile sunt:

1) insert – permite inserarea unui obiect nou în resursă (aplicabil numai tabelelor şi vederilor);

2) update – permite modificarea valorii unui obiect (poate fi utilizat cu tabele, vederi şi atribute);

3) delete – permite eliminarea unui obiect din resursă (doar tabele sau vederi);

4) select – permite utilizatorului să citească resursa cu scopul de a o utiliza în interogări (tabele, vederi sau atribute);

5) references – permite crearea unei referinŃe către o resursă în contextul definirii unui tabel. Poate fi asociat cu tabele sau atribute. Acordarea acestui privilegiu asupra unei resurse poate conduce la limitarea posibilităŃii de modificare a resursei. Să considerăm că utilizatorul Paul este proprietarul tabelului DEPARTAMENT, iar utilizatorul Ştefan deŃine privilegiul de referinŃă. Ştefan are posibilitatea să definească o constrângere de tip foreign key pe tabelul său ANGAJATI, referind resursa indicată de privilegiu (de exemplu cheia tabelului DEPARTAMENT). Dacă Ştefan adoptă o politică no action la definirea constrângerii, Paul va fi pus în situaŃia de a nu putea şterge sau modifica linii din tabelul său dacă aceste operaŃii au ca efect încălcarea constrângerii.

6) usage – se aplică domeniilor şi permite utilizarea lor, spre exemplu, pentru definirea schemei unui tabel.

Privilegiul de a efectua operaŃiile de drop sau alter nu poate fi acordat. Acest tip de privilegiu este deŃinut doar de proprietarul resursei.

Privilegiile se acordă sau se revocă cu ajutorul instrucŃiunilor grant şi revoke. 5.5.2 Comenzi pentru acordarea şi revocarea privilegiilor Sintaxa comenzii de acordare de privilegii este:

grant Privilegii on Resursă to Utilizatori [with grant option]

Această instrucŃiune permite acordarea de Privilegii asupra Resursei către Utilizatori.

69

Exemplu

grant select on Departament to Stefan

Clauza with grant option indică posibilitatea propagării privilegiului către alŃi utilizatori.

Se pot utiliza cuvintele cheie all privileges pentru acordarea tuturor privilegiilor. Exemplu

grant all privileges on Departament to Stefan, Paul

Sintaxa comenzii de revocare de privilegii este:

revoke Privilegii on Resursă from Utilizatori [restrict | cascade]

Printre privilegiile ce pot fi revocate unui utilizator se găseşte şi privilegiul grant option, ce derivă din utilizarea opŃiunii with grant option. Revocarea privilegiilor poate fi făcută doar de utilizator care, într-o primă fază, a acordat aceste privilegii. OpŃiunea restrict împiedică execuŃia instrucŃiunii revoke dacă retragerea privilegiului are ca efect o retragere în lanŃ de privilegii. O astfel de comportare poate apărea în situaŃia în care utilizatorul a primit privilegiul cu opŃiunea with grant option şi a propagat privilegiul către alŃi utilizatori. OpŃiunea cascade în schimb va avea ca rezultat revocarea tuturor privilegiilor din lanŃ şi în plus va elimina toate obiectele din baza de date ce au fost construite pe baza acestor privilegii.

5.6 Utilizarea SQL în limbajele de programare Accesul la informaŃiile conŃinute în baza de date se face cel mai adesea prin intermediul

aplicaŃiilor integrate într-un sistem informaŃional, în timp ce utilizarea directă a interpretorului SQL este rezervată experŃilor. Utilizarea aplicaŃiilor dedicate pentru a accesa informaŃiile dintr-o bază de date este justificată de o serie de factori:

• accesul la informaŃii este cerut adesea de o aplicaŃie şi nu direct de utilizator; • pentru utilizatori accesul trebuie să fie simplu şi predictibil. Din acest motiv este utilă

reducerea complexităŃii accesului la baza de date prin construirea unei aplicaŃii cu o interfaŃă simplă;

• prezentarea datelor oferite de către sistem poate fi nepotrivită pentru cererile utilizatorilor, în timp ce o aplicaŃie specială nu este restricŃionată şi poate pune la dispoziŃie o reprezentare adecvată a cerinŃelor.

Există numeroase unelte ce pot fi folosite pentru crearea aplicaŃiilor de baze de date. O piaŃă în plină dezvoltarea este aceea a limbajelor de generaŃia a patra (4GLs), unelte de dezvoltare sofisticate care fac posibilă dezvoltarea de aplicaŃii de management al bazelor de date cu minimum de efort. Mai mult, există numeroase produse care nu sunt legate de o bază de date în particular, ci sunt capabile de a manageria dialogul cu sistemele relaŃionale prin intermediul standardului SQL. Aceste produse oferă posibilitatea definirii efective a schemelor bazelor de date şi construirii unor interfeŃe complexe.

O altă metodă de a scrie aplicaŃii este aceea a utilizării limbajelor de programare de nivel înalt. Analiza se va concentra pe această metodă deoarece are încă o importanŃă considerabilă şi datorită lipsei unei abordări unitare pentru limbajele de generaŃia a patra.

5.6.1 Probleme de integrare Pentru a utiliza instrucŃiuni SQL în interiorul unui limbaj procedural, instrucŃiunile SQL

trebuie încapsulate. Din punct de vedere al implementării este necesară punerea la dispoziŃie a

70

unui compilator de limbaj de nivel înalt cu un preprocesor. Acest preprocesor este capabil să detecteze apelurile către serviciile sistemelor de gestiune a bazelor de date şi să le introducă într-un mediu de execuŃie a interogărilor, care include şi un optimizator al acestor interogări. Această soluŃie oferă avantajele portabilităŃii şi abstractizării care caracterizează deja limbajele standard cum ar fi SQL. La execuŃie, programul începe un dialog cu baza de date, trimiŃând interogarea direct către sistem.

O problemă care apare constă în faptul că limbajele de programare accesează elementele unui tablou prin scanare linie cu linie, utilizând pentru aceasta o abordarea orientată pe tuplu. Spre deosebire de acestea, SQL e un limbaj orientat pe mulŃime, care acŃionează asupra întregului tabel. De asemenea, rezultatul unei interogări SQL este un tabel.

Există două posibilităŃi de a rezolva această problemă. Prima constă în utilizarea limbajelor de programare care au disponibile construcŃii de date puternice. Această soluŃie a devenit mai interesantă odată cu dezvoltarea limbajelor de programare orientate obiect, care sunt caracterizate de mecanisme puternice de definire a tipurilor de date. Dezavantajele ar fi că multe aplicaŃii sunt construite pe limbaje ce nu au această capabilitate. De asemenea, o altă dificultate care apare este lipsa unor standarde acceptate de toate sistemele.

O a doua strategie, mult mai utilizată, este standardizată şi nu necesită extensii complicate ale limbajelor de programare existente. Această soluŃie se bazează pe cursori.

5.6.2 Cursori Cursorul este un mecanism care permite accesarea liniilor unui tabel una câte una.

Cursorul se defineşte utilizând o interogare. Sintaxa pentru definirea unui cursor este: declare NumeCursor [scroll] cursor for InterogareSQL [for <read only | update [of Atribut {, Atribut}]>] Comanda declare cursor defineşte un cursor asociat cu o interogare particulară a bazei de

date. OpŃiunea scroll specifică dacă permitem cursorului să pointeze la oricare din liniile rezultatului interogării. OpŃiunea for update indică posibilitatea folosirii cursorului pentru o actualizare a atributelor specificate.

Un cursor se deschide prin intermediul comenzii: open NumeCursor Când se aplică această comandă de deschidere a cursorului, se execută interogarea iar

rezultatul interogării poate fi accesat prin intermediul comenzii fetch: fetch [Positie from] NumeCursor into ListaVariabile Comanda fetch preia o linie din cursor şi întoarce valorile acesteia în variabile ale

programului din ListaVariabile. ListaVariabile trebuie să includă câte o variabilă pentru fiecare element din lista Ńintă a interogării, astfel încât fiecare element din ListaVariabile să fie compatibil din punct de vedere al tipului cu domeniile elementelor din lista Ńintă. Un concept important este acela de linie curentă, care reprezintă ultima linie utilizată într-o operaŃie fetch. Parametrul Pozitie este utilizat pentru setarea liniei curente. Acest parametru poate lua una din valorile următoare:

• next – linia următoare devine linie curentă;

• first – prima linie a rezultatului interogării devine linie curentă;

• last - ultima linie a rezultatului interogării devine linie curentă;

71

• absolute Expresie – linia de pe poziŃia i a rezultatului interogării devine linie curentă, unde i este rezultatul evaluării expresiei, care trebuie să fie de tip integer;

• relative Expresie - linia de pe poziŃia i, plecând de la linia curentă, a rezultatului interogării devine linie curentă; i are semnificaŃia de mai sus.

Aceste valori ale parametrului Pozitie pot fi folosite doar dacă este activată opŃiunea scroll. În caz contrar, valoarea implicită a parametrului este next.

Comenzile update şi delete permit modificarea bazei de date prin intermediul cursorilor. Spre exemplu, sintaxa comenzii update este este:

update NumeTabel set Atribut = <Expresie | NULL | default> {, Atribut = <Expresie | NULL | default>} where current of NumeCursor

Singura modificare cerută de sintaxa comenzilor update şi delete constă în posibilitatea

utilizării în clauza where a cuvintelor cheie current of NumeCursor, care indică linia curentă (cea care trebuie actualizată sau ştearsă). Comenzile de modificare pot fi folosite doar în cazul în care cursorul permite accesul la o linie curentă a tabelului şi nu sunt aplicabile când interogarea asociată cursorului necesită o joncŃiune între diferite tabele.

Comanda close închide cursorul şi comunică sistemului că rezultatul interogării nu mai

este necesar. În acest moment spaŃiul de memorie necesar memorării rezultatului interogării este dealocat. Sintaxa aceste comenzi este:

close NumeCursor

Exemplu

declare CursorAngajati scroll cursor for select Nume, Prenume, Salariu from Angajati where Salariu < 100 and Salariu > 40 Cursorul CursorAngajati este alocat unei interogări ce permite obŃinerea datelor referitoare la angajaŃii care câştigă între 40 şi 100. Să considerăm în continuare un exemplu simplu de program în C care utilizează cursori.

Comenzile SQL sunt precedate de caracterul ’$’ iar variabilele programului sunt precedate de caracterul ’:’. Se utilizează şi o variabilă predefinită, sqlcode, care conŃine valoarea 0 dacă ultima comandă a fost executată cu succes şi o valoare diferită de 0 dacă apare o eroare; scopul este de a detecta dacă asupra liniilor cursorului a fost executată comanda fetch.

Exemplu

void AfiseazăSalariiDepartament (char NumeDept[ ]) { char Nume[20], Prenume[20]; long int Salariu;

$ declare DeptAng cursor for select Nume, Prenume, Salariu from Angajati where Dept = :NumeDept;

72

$ open DeptAng $ fetch DeptAng into :Nume, :Prenume, :Salariu; printf (”Departament %s\n”, NumeDept); while (sqlcode == 0) { printf (”Numele Angajatului: %s %s”, Nume, Prenume); printf (”Salariu: %d\n”, Salariu); $ fetch DeptAng into :Nume, :Prenume, :Salariu; } $ close cursor DeptAng; }

În cazul în care rezultatul interogării constă într-un singur tuplu (interogări scalare) nu

mai este necesară definirea unui cursor pentru interfaŃarea cu limbajul de programare. Se poate stabili direct căror variabile din program li se atribuie valorile rezultatului interogării prin utilizarea clauzei into. Sintaxa instrucŃiunii select se extinde în modul următor:

InterogareSQL ::= select ListaTinta [into ListaVariabile] from ListaTabele [where Conditie] [group by ListaAtributeGrupare] [having ConditieAgregata] [order by ListaAtributeOrdonare]

Exemplu

$ select Nume, Prenume into :NumeAng, : PrenumeAng from Angajati where ID = :IDAng

5.6.3 SQL Dinamic În practică există numeroase situaŃii în care este necesar să se permită utilizatorului să

formuleze interogări arbitrare asupra bazei de date. Dacă interogările au o structură predefinită şi partea care variază este reprezentată doar de

parametrii interogării atunci se pot construi aplicaŃii folosind cursori, după cum s-a văzut în secŃiunea anterioară. Există cazuri în care interogările trebuie să fie mai flexibile. Aceste interogări diferă nu doar prin parametrii folosiŃi ci şi prin structură şi mulŃimea de tabele luată în considerare. Mecanismele prezentate în secŃiunea anterioară nu mai funcŃionează în acest context. Această familie de mecanisme este cunoscută sub numele de SQL static. Există şi o familie alternativă de comenzi cunoscută sub numele de SQL dinamic. Aceste comenzi fac posibilă alcătuirea unui program ce execută comenzi SQL construite când programul rulează; ele necesită un suport special din partea sistemului.

Problema principală care apare este aceea a transferului parametrilor între comenzile SQL şi program. Deoarece o astfel de comandă este arbitrară, programul nu are nici o cale de a recunoaşte, în momentul compilării, care sunt parametrii de intrare şi de ieşire ai comenzii. Această informaŃie este necesară programului pentru a putea organiza intern interogarea.

Utilizarea SQL dinamic modifică modul de interacŃiune cu sistemul. În SQL static, comenzile pot fi procesate de către compilator, care analizează structura comenzii şi construieşte o translaŃie spre limbajul intern al sistemului. În SQL dinamic nu este necesară translaŃia şi optimizarea comenzilor de fiecare dată, acestea putând fi executate imediat. Acest fapt aduce

73

avantaje considerabile din punct de vedere al performanŃei. Spre exemplu, dacă o comandă este îndeplinită în mod repetat, cu această soluŃie translaŃia se face doar o dată, pe când interacŃiunea cu maşina la fiecare execuŃie separată a comenzii poate necesita propria fază de translaŃie.

SQL dinamic permite două tipuri de interacŃiuni. Interogarea poate fi îndeplinită imediat, aceasta însemnând că execuŃia interogării se face imediat după analiză, sau gestiunea interogării are loc în două faze, analiză şi execuŃie. Comanda de execuŃie imediată. Comanda execute immediate necesită execuŃia unei instrucŃiuni SQL fie directe, fie conŃinută într-o variabilă de tip şir de caractere. Sintaxa este:

execute immediate InterogareSQL

Această metodă poate fi folosită doar pentru instrucŃiunile care nu necesită parametri de intrare sau ieşire.

Exemplu

execute immediate ”delete from Departament where Dept = ’Administratie’ ”; Într-un program în C se poate scrie: SQLString = ”delete from Departament where Dept = ’Administratie’ ”; ... $ execute immediate :SQLString

În schimb, când o instrucŃiune este executată de mai multe ori sau când programul trebuie să realizeze un schimb de parametri de intrare/ieşire cu interogarea, se poate distinge între cele două faze. Faza de pregătire . Comanda prepare analizează şi optimizează instrucŃiunea SQL şi o transformă într-un limbaj procedural intern al sistemului de gestiune a bazelor de date. Sintaxa este:

prepare NumeComanda from InstructiuneSQL

InstrucŃiunea SQL poate conŃine parametri de intrare.

Exemplu

prepare :InstructiuneSQL from ”select Oras from Departament where Dept = ?” În acest caz, translaŃia interogării corespunde variabilei InstructiuneSQl , cu un parametru de intrare care corespunde numelui departamentului care trebuie selectat de către interogare

În momentul în care o instrucŃiune SQl pregătită nu mai este necesară se poate dealoca memoria utilizând comanda deallocate prepare, care are sintaxa:

deallocate prepare NumeComanda

De exemplu, pentru a dealoca instrucŃiunea anterioară se foloseşte comanda

deallocate prepare :InstructiuneSQL

Faza de execuŃie. Pentru a executa o instrucŃiune pregătită cu comanda prepare, se foloseşte comanda execute, cu următoarea sintaxă:

74

execute NumeComanda [into ListaTinta] [using ListaParametri]

Lista Ńintă conŃine lista parametrilor în care trebuie scris rezultatul execuŃiei instrucŃiunii (această parte este opŃională în cazul în care comanda SQL nu are parametri de ieşire). Lista de parametri specifică ce valori trebuie luate de parametrii variabili din listă (această parte se omite de asemenea dacă în comanda SQL nu există parametri de intrare).

Exemplu

execute :InstructiuneSQL into :oras using :departament

Dacă presupunem că şirul ProducŃie este atribuit variabilei departament, efectul comenzii este executarea interogării:

select Oras from Departament where Dept = ’Productie’

iar rezultatul se memorează în variabila oras.

Utilizarea cursorilor în SQL dinamic . Utilizarea cursorilor în SQL dinamic este similară cu folosirea acestora în SQL static. Există doar două diferenŃe. Prima este că identificatorul interogării este atribuit cursorului în locul interogării însăşi. Cea de-a doua constă în faptul că acele comenzi pentru utilizarea cursorilor permit specificarea clauzelor into şi using, care la rândul lor permit specificarea eventualilor parametri de intrare sau ieşire.

Exemplu

prepare :InstructiuneSQL from :SQLString declare CursorPrg cursor from :InstructiuneSQL open CursorPrg using :VariabilaPrg

5.6.4 Proceduri Standardul SQL permite definirea procedurilor, cunoscute sub numele de proceduri stocate deoarece sunt uzual stocate în baza de date drept componente ale schemei. Odată ce o procedură este definită, ea este tratată ca o comandă SQL predefinită.

Exemplu

Să considerăm următoarea procedură SQL, care actualizează numele oraşului în care se găseşte un departament.

procedure AtribuieOras (:Dep char(20), :Oras char(20)) update Departament set Oras = :Oras where Dept = :Dept;

Procedura este apelată dând valori parametrilor. Următorul exemplu arată cum se face apelul procedurii de mai sus în interiorul unui program în C, folosind două variabile, NumeDept şi NumeOras.

Exemplu

$ AtribuieOras (:NumeDept, :NumeOras);

Standardul SQL-2 este limitat la definirea procedurilor formate dintr-o singură comanda SQL. Multe sisteme au înlăturat această limitare. Există sisteme care permit doar secvenŃe de comenzi în interiorul unei proceduri, şi sisteme care permit utilizarea structurilor de control, a

75

declaraŃiilor de variabile locale şi a apelului programelor externe. SQL-3 extinde aceste aspecte şi pune la dispoziŃie o sintaxă bogată pentru definirea procedurilor. Următorul exemplu arată o procedură ne-standard compusă dintr-o secvenŃă de două instrucŃiuni SQL.

Exemplu

procedure SchimbaToateOrasele (:OrasNou char(20), :OrasVechi char(20)) begin update Departament set Oras = :OrasNou where Oras = :OrasVechi; update Angajati

set Oras = :OrasNou where Oras = :OrasVechi; end; Procedura dă atributului Oras valoarea variabilei :OrasNou, pentru toate liniile tabelelor Departament şi Angajati în care atributul Oras este egal cu valoarea variabilei :OrasVechi.

Una din extensiile uzuale puse la dispoziŃie de sistemele actuale este structura de control if-then-else.

Exemplu

procedure SchimbaOras (:NumeDept char(20), :OrasNou char(20)) begin if not exists (select * from Departament where Dept = :NumeDept) insert into EroareDept values (:NumeDept) else update Departament

set Oras = :OrasNou where Dept = :NumeDept;

end if; end; Procedura dă atributului Oras în care se găseşte departamentul :NumeDept valoarea variabilei :OrasNou; dacă departamentul nu există, se introduce o linie în tabelul EroareDept.

După cum am indicat mai sus, există sisteme comerciale care oferă extensii procedurale puternice ale SQL. Există de asemenea posibilitatea de a scrie întreaga aplicaŃie în aceste extensii ale SQL. Următorul program este scris în PL/SQL, extensia furnizată de sistemul relaŃional ORACLE SERVER.

Exemplu

procedure Debit (ContClient char(5), Retragere integer) is SumaVeche integer; SumaNoua integer; SumaMinima integer;

begin select Suma, Limita into SumaVeche, SumaMinima

from ContBanca where NumarCont = ContClient for update of Suma;

SumaNoua := SumaVeche – Retragere; if SumaNoua > Limita then

76

update ContBanca set Suma = SumaNoua where NumarCont = ContClient;

else insert into LimitaDepasita values (ContClient, Retragere, sysdate);

end if; end Debit; Procedura permite retragerea sumei Retragere din contul cu codul ContClient, dacă în cont sunt suficienŃi bani. Se utilizează variabilele locale (SumaVeche, SumaNoua, SumaMinima) şi o structură de control if-then-else.

77

Partea a II-a Proiectarea bazelor de date

Capitolul 6. Tehnici de proiectare şi modele

În capitolele precedente s-au analizat modele de baze de date şi limbaje, presupunând în cele mai multe cazuri că există o bază de date cu care utilizatorii pot interacŃiona. În acest capitol se va pune problema proiectării unei baze de date conform cerinŃelor utilizatorilor. Proiectarea unei baze de date presupune definirea structurii, caracteristicilor şi conŃinutului acesteia. Utilizarea unor tehnici corespunzătoare este esenŃială pentru crearea unui produs de calitate bună.

6.1. Procesul de proiectare a bazei de date

6.1.1. Ciclul de viaŃă al informaŃiilor din sistem

Proiectarea bazei de date este doar una din activităŃile dezvoltării unui sistem

informaŃional în interiorul unei organizaŃii. În figura 1 este prezentat ciclul de viaŃă a unui sistem informaŃional.

Figura 1. Ciclul de viaŃă al unui sistem informaŃional

Studiul de fezabilitate serveşte definirii cât mai precise a costurilor diferitelor soluŃii posibile şi stabilirii priorităŃilor în crearea componentelor sistemului.

Extragerea şi analiza cerinŃelor constă în definirea şi studiul proprietăŃilor şi funcŃionalităŃii sistemului informaŃional. Această etapă presupune interacŃiunea cu clienŃii pentru

78

extragerea cerinŃelor. Rezultatul este o descriere (informală) completă a datelor care intervin şi a operaŃiilor care acŃionează asupra datelor respective. Se stabilesc de asemenea necesităŃile hardware şi software ale sistemului.

Proiectarea se împarte în general în două taskuri: proiectarea bazei de date şi proiectarea operaŃională. La început se stabilesc structura şi organizarea datelor, iar apoi se definesc caracteristicile programelor. Cei doi paşi sunt complementari şi se pot parcurge simultan sau consecutiv. Descrierea datelor şi a programelor rezultate din această etapă este formală şi se referă la modele specifice.

Implementarea constă în crearea sistemului informaŃional în concordanŃă cu structura şi caracteristicile precizate în etapa de proiectare. Se construieşte baza de date şi se scrie codul programelor.

Validarea şi testarea constau în verificarea funcŃionării şi a calităŃii sistemului informaŃional. Testele trebuie să conŃină, pe cât posibil, toate condiŃiile de operare posibile.

Operarea este activitatea în care sistemul informaŃional funcŃionează îndeplinind scopurile pentru care a fost creat. Presupunând ca nu există erori majore sau că nu este schimbată funcŃionalitatea sistemului, această activitate constă doar în managementul şi întreŃinerea sistemului.

În practică aceste etape nu sunt în general secvenŃiale, deoarece în timpul uneia dintre activităŃile menŃionate este necesară revenirea la o etapă anterioară. Mai mult, câteodată este necesară introducerea unei noi activităŃi – prototipizarea – care constă în utilizarea unor unelte software specifice pentru crearea rapidă a unei versiuni simplificate a sistemului informaŃional cu ajutorul căreia se testează funcŃionalitatea acestuia. Prototipul poate fi prezentat clienŃilor pentru a verifica dacă au fost extrase şi modelate corect cerinŃele acestora.

6.1.2. Metodologii pentru proiectarea bazelor de date O abordare structurată a proiectării bazelor de date constă în parcurgerea următoarelor etape:

• descompunerea activităŃii de proiectare în paşi succesivi, independenŃi unul de altul;

• o serie de strategii care trebuie urmate şi câteva criterii care permit o alegere în cazul în care există mai multe soluŃii;

• câteva modele de referinŃă pentru a descrie intrările şi ieşirile diferitelor faze. ProprietăŃile care trebuie garantate de o anumită metodologie sunt:

• generalitatea – privitoare la aplicaŃiile şi sistemele pe care rulează ( şi astfel posibilitatea utilizării independent de o aplicaŃie specifică şi de un sistem disponibil);

• calitatea produsului, care presupune acurateŃe şi eficienŃă;

• uşurinŃa utilizării atât a strategiei cât şi a modelelor de referinŃă. Metodologiile din domeniul proiectării bazelor de date se împart în trei faze (vezi figura

2).

79

Figura 2. Fazele proiectării bazelor de date Proiectarea conceptuală. Scopul acestei faze este de a reprezenta cerinŃele informale ale aplicaŃiei în termenii descrierii complete şi formale dar independent de criteriul folosit pentru reprezentare în sistemul de management al bazei de date. Rezultatul acestei faze este schema conceptuală şi se referă la un model de date conceptual (care permite descrierea organizării datelor la un nivel înalt de abstractizare fără a lua în considerare aspectele de implementare). În această fază, proiectantul trebuie să încerce să reprezinte conŃinutul bazei de date fără a lua în considerare mijloacele prin care informaŃiile respective vor fi implementate în sistem sau eficienŃa programelor ce utilizează aceste informaŃii. Proiectarea logică reprezintă translarea schemei conceptuale într-un model de date disponibil pentru sistemul de gestiune al bazei de date. Rezultatul acestei faze, numit schemă logică, se referă la un model de date logic (care permite reprezentarea datelor într-o formă independentă încă de detaliile fizice deşi sistemul de gestiune al bazei de date folosit pentru implementare poate fi unul ce suportă acest model de date). În această fază proiectantul trebuie să ia în considerare criterii de optimizare. Se utilizează frecvent tehnici formale pentru verificarea calităŃii schemei logice. În cazul unui model de date relaŃional, tehnica folosită este aceea a normalizării .

80

Proiectarea fizică. În această fază, schema logică se completează cu detalii de implementare fizică (organizarea fişierelor şi indecşi) puse la dispoziŃie de SGBD. Rezultatul acestei faze se numeşte schema fizică şi se referă la un model de date fizic. Acest model depinde de sistemul de management al bazei de date şi ia în considerare criteriile pentru organizarea fizică a datelor. Să precizăm ce fel de cerinŃe ale aplicaŃiei sunt utilizate în cele trei faze ale proiectării bazelor de date. Se poate face o distincŃie între cerinŃele de date care constau în conŃinutul bazei de date şi cerinŃele operaŃionale care constau în utilizarea bazei de date de clienŃi sau programatori. În proiectarea conceptuală, cerinŃele de date furnizează majoritatea informaŃiilor, în timp ce cerinŃele operaŃionale se folosesc doar pentru a verifica dacă schema conceptuală este completă. În proiectarea logică pe de altă parte, schema conceptuală, care este furnizată ca intrare, rezumă cerinŃele de date; în schimb, cerinŃele operaŃionale se folosesc pentru a obŃine schema logică. În proiectarea fizică schema logică şi cerinŃele operaŃionale se folosesc pentru optimizarea performanŃelor sistemului. În această fază trebuie luate în considerare caracteristicile SGBD-ului utilizat. Rezultatul procesului de proiectare a bazei de date nu este dat doar de schema fizică, ci şi de schema conceptuală şi cea logică. Schema conceptuală furnizează o reprezentare de nivel înalt a bazei de date care poate fi extrem de folositoare pentru documentare. Schema logică furnizează o descriere a conŃinutului bazei de date şi, lăsând la o parte aspectele de implementare, este utilă pentru realizarea interogărilor şi modificărilor bazei de date. 6.2. Modelul Entitate – RelaŃie (Entity – Relantionship model) Modelul Entitate – RelaŃie (E–R) este un model de date conceptual ce pune la dispoziŃie o serie de construcŃii capabile să descrie datele necesare unei aplicaŃii într-o manieră uşor de înŃeles şi independentă de criteriile de gestiune şi organizare a datelor din sistem.

ConstrucŃiile modelului E-R definesc scheme ce descriu modul de organizare şi dictează care apariŃii de date (valori pe care baza de date le poate stoca la diferite momente de timp) sunt legale.

ConstrucŃiile modelului E-R sunt prezentate în figura 3. ConstrucŃiile de bază ale modelului E-R vor fi prezentate în continuare.

EntităŃi – reprezintă clase de obiecte ce au proprietăŃi comune şi existenŃă autonomă. NotaŃia grafică pentru entitate se poate observa în figura 4. ORAŞ, DEPARTAMENT, ANGAJAT, VÂNZĂRI sunt exemple de entităŃi într-o aplicaŃie pentru o organizaŃie comercială.

O apariŃie a unei entităŃi este un obiect al clasei reprezentate de entitatea respectivă. Spre exemplu, oraşele Iaşi şi Bucureşti sunt apariŃii ale entităŃii ORAS.

ObservaŃie. O apariŃie a unei entităŃi nu este o valoare ce identifică un obiect (de exemplu numele unui angajat) ci este chiar obiectul însuşi. O consecinŃă a acestui fapt este aceea că o apariŃie a unei entităŃi are o existenŃă (şi o identitate) independent de proprietăŃile asociate (în cazul unui angajat, angajatul există indiferent dacă are un nume, un prenume, o vârstă etc.). În acest sens modelul entitate relaŃie este diferit de modelul relaŃional, unde nu este posibilă reprezentarea unui obiect fără a-i cunoaşte proprietăŃile.

Într-o schemă, fiecare entitate are un nume unic.

81

Entitate

RelaŃie

Atribut simplu

Atribut compus

Cardinalitatea unei relaŃii

Cardinalitatea unui atribut

Identificatori interni

Identificator extern

Generalizare

SubmulŃime

Figura 3. ConstrucŃii ale modelului entitate-relaŃie

Figura 4. Exemple de entităŃi

RelaŃii – reprezintă legături logice între două sau mai multe entităŃi. REZIDENłĂ este un exemplu de relaŃie care poate exista între entităŃile ORAŞ şi ANGAJAT.

m1,M1 m2,M2

m,M

82

O apariŃie a unei relaŃii este un n-tuplu format din apariŃii ale entităŃilor, câte o apariŃie pentru fiecare entitate implicată. În figura 5 este prezentat un exemplu conŃinând apariŃii ale relaŃiei EXAMEN între entităŃile STUDENT şi CURS.

Figura 5. Exemple de apariŃii ale relaŃiei EXAMEN Într-o schemă entitate relaŃie, fiecare relaŃie are un nume unic şi este reprezentată grafic prin simbolul conŃinând numele relaŃiei.

Figura 6. Exemple de relaŃii După cum se poate observa în figura 6, între aceleaşi entităŃi pot exista mai multe relaŃii. Pentru numele relaŃiilor este indicată folosirea substantivelor în locul verbelor, pentru a evita sugerarea unei „direcŃii”. Un aspect important al relaŃiilor constă în aceea că mulŃimea apariŃiilor unei relaŃii este o relaŃie matematică între mulŃimile de apariŃii ale entităŃilor implicate (este o submulŃime a produsului cartezian al celor două mulŃimi de apariŃii ale entităŃilor implicate). Astfel este asigurat faptul că nici un n-tuplu nu va apărea de două ori pentru apariŃiile unei relaŃii. Acest aspect are consecinŃe importante: spre exemplu, relaŃia EXAMEN din figura 6 nu are capacitatea de a raporta faptul că un student oarecare dă un examen de mai multe ori (deoarece aceasta ar trebui să producă perechi identice).În acest caz, examenul trebuie reprezentat de o entitate legată de entităŃile STUDENT şi CURS prin intermediul a două relaŃii binare.

Sunt posibile relaŃiile recursive care reprezintă relaŃii între o entitate şi ea însăşi.

83

Figura 7. Exemple de relaŃii recursive În figura 7 relaŃia recursivă COLEG a entităŃii ANGAJAT conectează perechi de oameni care lucrează împreună. Spre deosebire de prima relaŃie, relaŃia SUCCESIUNE a entităŃii SUVERAN nu este simetrică. În acest caz, este necesar să se asocieze identificatori liniilor din relaŃia recursivă. Există relaŃii care implică mai mult de două entităŃi. În exemplul din figura 8 o apariŃie a relaŃiei FURNIZEAZĂ descrie faptul că o anumită firmă furnizează un anumit produs unui departament.

Figura 8. Exemplu de relaŃie ce implică mai multe entităŃi Atribute – descriu proprietăŃile elementare ale entităŃilor sau relaŃiilor.

Spre exemplu, Nume, Salariu, Vârstă sunt atribute pentru entitatea ANGAJAT iar Dată, Notă sunt atribute pentru relaŃia EXAMEN (vezi figura 9).

Un atribut asociază fiecărei apariŃii a unei entităŃi (sau relaŃie) o valoare aparŃinând unei mulŃimi denumite domeniul atributului. Domeniul conŃine valori admisibile pentru atribut. De exemplu domeniul pentru atributul Nume poate fi orice şir de caractere de lungime 20, iar domeniul pentru atributul Vârstă poate fi orice număr între 18 şi 60. Domeniile nu sunt reprezentate grafic, ele fiind de obicei descrise în documentaŃia asociată. Atributele pot fi simple sau compuse.

Un atribut compus este o mulŃime de atribute ale aceleaşi entităŃi sau relaŃii ale căror înŃelesuri sau utilizări sunt strâns conectate. Spre exemplu, atributele Strada, NumărCasă, CodPoştal ale entităŃii PERSOANĂ pot fi grupate pentru a forma atributul compus Adresă. În figura 11 este prezentat un exemplu de schemă Entitate-RelaŃie cu toate componentele discutate până în acest moment.

84

Figura 9. Schemă E-R cu relaŃii, entităŃi şi atribute

Figura 10. Exemplu de entitate cu un atribut compus

Figura 11. Exemplu de schemă Entitate-RelaŃie

85

Cardinalitatea unei relaŃii – este specificată pentru fiecare entitate participantă la relaŃie şi descrie numărul minim, respectiv maxim de apariŃii ale relaŃiei la care poate participa o apariŃie a entităŃii. Într-o schemă E-R cardinalităŃile minime şi maxime ale participărilor entităŃilor în relaŃii sunt specificate în paranteze, ca în figura 12.

Figura 12. Cardinalitatea unei relaŃii într-un model E-R În principiu este posibil să atribuim orice valoare întreagă mai mare ca 0 cardinalităŃii unei relaŃii, singura cerinŃă fiind ca valoarea minimă a cardinalităŃii să fie mai mică decât cea maximă. În majoritatea cazurilor este suficientă utilizarea a trei valori: 0, 1 sau N (N este o valoare întreagă mai mare ca 1):

• cardinalitatea minimă 0 – participarea la relaŃie este opŃională;

• cardinalitatea minimă 1 – participarea la relaŃie este obligatorie;

• cardinalitatea maximă 1 – fiecare apariŃie a entităŃii este asociată cel mult unei singure apariŃii a relaŃiei;

• cardinalitatea maximă N – fiecare apariŃie a entităŃii este asociată unui număr arbitrar de apariŃii ale relaŃiei.

În funcŃie de cardinalităŃile maxime ale entităŃilor implicate într-o relaŃie, relaŃiile se

împart în: • relaŃii unu-la-unu - definesc o corespondenŃă unu la unu între entităŃi (v. figura 13a

– o comandă are o singură factură)

Figura 13. Exemple de cardinalităŃi ale relaŃiilor • relaŃii unu-la-mai-mulŃi - cardinalitatea maximă a unei entităŃi este 1, iar

cardinalitatea maximă a celeilalte entităŃi este N (v. figura 13b – o persoană poate fi rezidentă într-un singur oraş, în timp ce fiecare oraş poate avea mai mulŃi rezidenŃi)

86

• relaŃii mai mulŃi-la-mai mulŃi - cardinalităŃile maxime sunt N pentru ambele entităŃi (v. figura 13c).

În ceea ce priveşte cardinalitatea minimă, să precizăm faptul că este rar cazul în care

participarea este obligatorie pentru toate entităŃile implicate. Aceasta se întâmplă deoarece atunci când se adaugă o nouă apariŃie a entităŃii, adesea apariŃiile corespunzătoare ale altor entităŃi legate de aceasta nu sunt cunoscute încă sau nu există. Spre exemplu, să considerăm schema din figura 13a. Când se primeşte o nouă comandă, nu există încă o factură şi deci nu este posibilă construirea unei apariŃii pentru relaŃia ONORARE care conŃine noua comandă.

În relaŃiile n-are, entităŃile implicate au aproape întotdeauna cardinalitatea maximă egală cu N. Când o entitate este implicată într-o relaŃie n-ară cu cardinalitatea maximă egală cu 1, înseamnă că una din apariŃiile sale poate fi legată de o singură apariŃie a relaŃiei, şi deci la un singur n-tuplu al apariŃiilor unei alte entităŃi implicate în relaŃie. Această înseamnă de fapt ca este posibilă înlocuirea relaŃie n-are cu n relaŃii binare unu-la-mai mulŃi care leagă astfel o entitate cu altele. Cardinalitatea atributelor –descrie numărul minim, respectiv maxim de valori ale atributelor asociate fiecărei apariŃii a unei entităŃi sau relaŃii.

În general, cardinalitatea unui atribut este (1,1) şi este omisă pe schemă. În acest caz atributul este o funcŃie ce asociază o singură valoare fiecărei apariŃii a entităŃii (relaŃiei).Valoarea unor atribute poate fi null sau pot exista valori diferite ale atributului asociate unei apariŃii a entităŃii. Aceste situaŃii pot fi reprezentate prin alocarea cardinalităŃii minime egală cu 0 (în primul caz) respectiv cu cardinalitatea egală cu N (în al doilea caz).

Figura 14. Exemplu de atribute cu cardinalitate În figura 14 este prezentat un exemplu de atribute cu cardinalitate. O persoană are un nume şi doar unul, poate avea sau nu permis de conducere (dacă are, este unic) şi poate avea mai multe maşini, la fel de bine ca nici una. Într-un mod similar participării unei apariŃii a unei entităŃi într-o relaŃie, putem spune că un atribut cu cardinalitatea minimă egală cu zero este opŃional pentru entitatea asociată (sau relaŃia asociată) şi este obligatoriu în cazul în care cardinalitatea minimă este unu. De asemenea, spunem că un atribut este multivaloare dacă are cardinalitatea maximă N. Atributele multivaloare trebuie folosite cu precauŃie deoarece ele reprezintă situaŃii care pot fi modelate, câteodată, prin entităŃi adiŃionale legate prin relaŃii unu-la-unu sau mai mulŃi-la-mai mulŃi cu entităŃile la care se referă. Spre exemplu să presupunem că avem atributul multivaloare Calificări pentru entitatea PERSOANĂ din figura 14 (o persoană poate avea mai multe calificări). Calificarea este, de asemenea, un concept ce poate fi atribuit mai multor persoane; din acest motiv, este naturală modelarea acestui concept cu ajutorul unei entităŃi CALIFICARE legată de PERSOANĂ cu o relaŃie mai mulŃi-la-mai mulŃi.

Identificatori – sunt specifici fiecărei entităŃi din schemă şi descriu conceptele (atributele şi/sau entităŃile) schemei ce permit identificarea unică a apariŃiilor acelei entităŃi. Identificatorii se clasifică în:

87

• identificator intern – format din unul sau mai multe atribute ale entităŃii. Este cunoscut sub numele de cheie. Spre exemplu, entitatea AUTOMOBIL cu atributele Model, NrÎnmat, Culoare are ca identificator intern atributul NrÎnmat, presupunând ca nu există două automobile cu acelaşi număr de înmatriculare. Pentru entitatea PERSOANĂ cu atributele DatăNaştere, Nume, Prenume şi Adresă, un identificator intern poate fi format din mulŃimea atributelor Nume, Prenume şi DatăNaştere. În figura 15 se pot observa notaŃiile pentru identificatorii interni. Se poate observa diferenŃa între un identificator intern format dintr-un singur atribut (figura 15a) şi un identificator intern format din mai multe atribute (figura 15b).

Figura 15. Exemple de identificatori interni

• identificator extern. Există cazuri când atribute ale unei entităŃi nu sunt suficiente pentru a identifica în mod unic apariŃiile entităŃii. Să considerăm entitatea STUDENT din figura 16. Schema descrie studenŃii înscrişi la diverse universităŃi, doi studenŃi din universităŃi diferite putând avea acelaşi număr de înregistrare. În acest caz, pentru a putea identifica un student în mod unic avem nevoie atât de numărul său de înregistrare, cât şi de universitatea de care acesta aparŃine. Un identificator corect pentru entitatea student este format din atributul NrÎnreg şi entitatea UNIVERSITATE. Un astfel de identificator se numeşte identificator extern. Se observă că identificarea este posibilă prin relaŃia obligatorie unu-la-mai mulŃi dintre entităŃile UNIVERSITATE şi STUDENT, care asociază fiecare student cu o singură universitate. Astfel, o entitate E poate fi identificată prin alte entităŃi doar dacă fiecare astfel de entitate este implicată într-o relaŃie în care E participă cu cardinalitatea (1,1)

Figura 16. Exemplu de identificator extern ObservaŃii.

• un identificator poate implica unul sau mai multe atribute, cu condiŃia ca fiecare dintre ele să aibă cardinalitate (1,1);

• un identificator extern poate implica una sau mai multe entităŃi, cu condiŃia ca fiecare dintre ele să fie într-o relaŃie în care entitatea de identificat participă cu cardinalitatea (1,1);

• un identificator extern poate implica o entitate care este la rândul său identificată extern, atâta timp cât nu se generează cicluri;

88

• fiecare entitate trebuie să aibă un identificator (intern sau extern) dar poate avea mai mult de unul. Dacă există mai mult de un identificator atunci atributele şi entităŃile implicate într-o identificare pot fi opŃionale (cardinalitatea minimă egală cu zero).

În acest moment schema din figura 11 poate fi reexaminată, introducând cardinalităŃi şi

identificatori. Schema rezultată este prezentată în figura 17.

Figura 17. Schema din figura 11 completată cu identificatori şi cardinalităŃi Se poate observa că numele unui oraş identifică o filială a companiei. Aceasta înseamnă că există doar o filială în oraşul respectiv. Un departament este identificat de nume şi de filiala a cărei parte este (putem deduce din cardinalitate că o filială are mai multe departamente dar fiecare departament aparŃine unei singure filiale. Un departament are cel puŃin un număr de telefon. Un angajat (identificat printr-un cod) poate aparŃine unui singur departament (dar există posibilitatea să nu aparŃină nici unui departament –cazul în care este nou angajat spre exemplu) şi poate conduce cel mult un departament. În plus, fiecare departament are un singur manager şi unul sau mai mulŃi angajaŃi. Mai mulŃi angajaŃi (dar cel puŃin unul) pot lucra la un proiect (identificat prin numele său) şi fiecare angajat lucrează în general la mai multe proiecte (existând posibilitatea să nu lucreze la nici unul). De asemenea, data livrării proiectului poate să nu fie precizată. Generalizări – reprezintă legături logice între o entitate părinte E şi una sau mai multe entităŃi copii, E1, ..., En; entitatea E este mai generală, în sensul că E1, ..., En sunt cazuri particulare ale lui E. În această situaŃie spunem că E este o generalizare a lui E1, ..., En, iar E1, ..., En sunt particularizări ale entităŃii E. Spre exemplu, PERSOANĂ este o generalizare pentru BĂRBAT şi FEMEIE. ProprietăŃi.

• fiecare apariŃie a unei entităŃi copil este apariŃie a entităŃii părinte;

• fiecare proprietate a entităŃii părinte (atribut, identificator, relaŃie, generalizare) este de asemenea o proprietate a entităŃii copil. Spre exemplu, dacă entitatea PERSOANĂ are atributele Nume şi Vârstă, atunci entităŃile BĂRBAT şi FEMEIE au de asemenea aceste atribute. Mai mult, identificatorul pentru PERSOANĂ este de asemenea un

89

identificator valid pentru entităŃile BĂRBAT şi FEMEIE. Această proprietate se numeşte moştenire.

Generalizarea se reprezintă grafic ca în figura 18. Se observă că pentru entităŃile copii nu se reprezintă proprietăŃile moştenite.

Figura 18. Exemplu de generalizare Generalizările pot fi clasificate în modul următor:

• o generalizare este totală dacă fiecare apariŃie a entităŃii părinte este de asemenea o apariŃie a unei entităŃi copil; în caz contrar este generalizare parŃială.

• o generalizare este exclusivă dacă fiecare apariŃie a entităŃii părinte este cel mult o apariŃie a unei entităŃi copil; în caz contrar generalizarea este suprapusă.

Exemple.

Generalizarea PERSOANĂ pentru BĂRBAT şi FEMEIE este totală (persoanele pot fi numai bărbaŃi sau femei) şi exclusivă (o persoană este fie bărbat fie femeie).

Generalizarea VEHICUL pentru AUTOMOBIL şi BICICLETĂ este parŃială (există şi alte tipuri de vehicule) şi exclusivă.

Generalizarea PERSOANĂ pentru STUDENT şi ANGAJAT este parŃială şi suprapusă (există studenŃi care sunt şi angajaŃi).

Generalizarea suprapusă poate fi transformată uşor într-o generalizare exclusivă prin adăugare uneia sau mai multor entităŃi copil pentru reprezentarea entităŃilor ca sunt „intersecŃia” între entităŃile ce se suprapun. În ultimul exemplu prezentat se poate adăuga entitatea STUDENTANGAJAT pentru a obŃine o generalizare exclusivă.

Generalizările totale se reprezintă în mod obişnuit cu o săgeată plină, ca în figura 19.

În general, o entitate poate fi implicată în mai multe generalizări diferite. Există generalizări cu mai multe nivele, cunoscute sub numele de ierarhii. De asemenea, o generalizare poate avea un singură entitate copil, cunoscută sub numele de submulŃime. În figura 19 este prezentată o ierarhie. RelaŃia dintre entităŃile MANAGER PROIECT şi ANALIST este un exemplu de submulŃime.

90

Figura 19. Exemplu de ierarhie a generalizărilor între entităŃi ObservaŃii finale asupra modelului E-R

Modelul E-R este realizat pe baza a două construcŃii de bază: entitate şi relaŃie;

• o entitate poate participa în mai multe relaŃii sau în nici una;

• o relaŃie implică două sau mai multe entităŃi;

• participarea unei entităŃi într-o relaŃie are o cardinalitate minimă şi una maximă; Modelul E-R mai are construcŃiile atribut şi generalizare;

• un atribut are un nume şi o cardinalitate minimă şi una maximă şi aparŃine unui concept de bază (entitate sau relaŃie);

• atributele compuse sunt specializări ale atributelor şi sunt formate din unul sau mai multe atribute;

• o generalizare are o entitate părinte şi una (cazul submulŃimilor) sau mai multe entităŃi copil;

• o entitate poate fi părinte sau copil în mai multe generalizări (de asemenea în nici una);

Este esenŃială folosirea de nume diferite în cazul construcŃiilor de bază pentru a evita ambiguităŃile. Două atribute pot avea acelaşi nume dacă aparŃin unor construcŃii de bază diferite

Există anumite restricŃii în folosirea unor construcŃii. Spre exemplu o ierarhie a unei generalizări nu poate conŃine cicluri. De asemenea cardinalitatea minimă trebuie să fie mai mică decât cea maximă.

Schemele E-R furnizează o reprezentare abstractă a datelor unei aplicaŃii şi pot fi utilizate nu numai pentru proiectarea bazelor de date:

• se pot folosi pentru documentaŃii deoarece pot fi înŃelese uşor de către nespecialişti;

91

• pot fi utilizate pentru descrierea datelor dintr-un sistem informaŃional existent deja (spre exemplu pentru integrarea cu alte baze de date);

• se mai pot folosi de asemenea în cazul modificării cerinŃelor clienŃilor.

6.3 DocumentaŃia pentru schemele E-R

O schemă E-R este adeseori insuficientă pentru a descrie toate aspectele unei aplicaŃii în detaliu. În primul rând într-o schemă E-R sunt precizate doar numele conceptelor, acest fapt fiind insuficient pentru a explica semnificaŃia acestora. Spre exemplu, în figura 17, nu este clar dacă entitatea PROIECT se referă la un proiect intern al companiei sau la un proiect extern la care compania respectivă este parte. Mai mult, dacă schema este complexă, se poate întâmpla să nu poată fi reprezentate toate proprietăŃile conceptelor care apar într-un mod inteligibil. În schema din figura 17 ar fi greu de introdus şi alte atribute pentru ANGAJAT fără a reduce accesibilitatea schemei. Pe de altă parte, este imposibilă reprezentarea unor proprietăŃi ale datelor prin intermediul schemelor E-R. În exemplul din figura 17 să presupunem că un angajat poate fi manager doar în departamentul de care aparŃine. Această proprietate nu poate fi exprimată direct în schemă deoarece se referă la două concepte independente (management şi membru) descrie prin două relaŃii şi nu există construcŃii care să permită corelarea a două relaŃii. Un alt exemplu care nu se poate implementa direct pe schema E-R ar fi acela în care se impune ca un angajat să nu poată avea un salariu mai mare decât managerul departamentului de care aparŃine. Ambele proprietăŃi menŃionate sunt de tipul constrângere de integritate. Modelul E-R nu furnizează mijloace potrivite pentru reprezentarea constrângerilor complexe impuse datelor. Din motivele precizate mai sus, o schemă E-R trebuie însoŃită de o documentaŃie care să faciliteze interpretarea schemei şi descrierea proprietăŃilor care nu pot fi exprimate direct prin construcŃiile puse la dispoziŃie de modelul E-R. Reguli de operare Regulile de operare reprezintă una din uneltele utilizate de analişti pentru a descrie proprietăŃile unei aplicaŃii care nu pot fi exprimate direct cu ajutorul modelului conceptual. Această abordare permite specificarea regulilor unei aplicaŃii. Spre exemplu, faptul că un angajat nu poate câştiga mai mult decât managerul său este o regulă de operare.

În particular, o regulă de operare poate fi:

• descrierea unui concept relevant al aplicaŃiei sau mai degrabă o definire precisă a entităŃilor, atributelor sau relaŃiilor unui model E-R;

• o constrângere de integritate aplicată datelor aplicaŃiei;

• o derivare, sau mai precis un concept care poate obŃinut pe baza unei deducŃii sau a unui calcul matematic din alte concepte ale schemei (spre exemplu un atribut Cost care poate fi obŃinut ca sumă a atributelor Net şi Taxe);

Pentru regulile din prima categorie precizată anterior nu este posibilă definirea unei sintaxe exacte şi de aceea se folosesc propoziŃii exprimate în limbaj natural. Aceste reguli sunt formulate în termenii vocabularului.

Regulile ce descriu constrângeri de integritate şi derivări folosesc definiŃii formale.

92

Constrângerile de integritate pot fi exprimate sub forma aserŃiilor care sunt declaraŃii ce trebuie satisfăcute întotdeauna de baza de date. Pentru claritate, unele declaraŃii trebuie să fie „atomice”.

O notaŃie de tipul if <condiŃie> then <acŃiune> nu este potrivită pentru a exprima o regulă.

O structură mai potrivită pentru a exprima o regulă de operare sub forma unei aserŃii poate fi:

<concept> trebuie/nu trebuie <expresie a conceptelor>

unde conceptele pot corespunde fie unui concept a schemei E-R la care se referă fie unui concept derivat. Spre exemplu, pentru a exprima constrângerile pentru schema din figura 17, se utilizează următoarele reguli de operare:

(RO1) managerul departamentului trebuie să aparŃină departamentului;

(RO2) un angajat nu trebuie să aibă salariul mai mare decât managerul departamentului căruia îi aparŃine;

(RO3) un departament din filială Iaşi trebuie manageriat de un angajat cu mai mult de 10 ani vechime în companie

Regulile de operare care descriu derivări pot fi exprimate prin specificarea operaŃiilor

(matematice sau de alt fel) care permit obŃinerea conceptelor derivate. O structură posibilă este următoarea:

<concept> este obŃinut prin <operaŃii asupra conceptelor>

Dacă în exemplul tratat până acum entitatea DEPARTAMENT are un atribut NumărDeAngajaŃi, se poate introduce o regulă de forma:

(RO4) numărul angajaŃilor dintr-un departament este obŃinut prin numărarea angajaŃilor care aparŃin departamentului respectiv;

În momentul în care schema conceptuală este translată într-o bază de date (fazele logice şi fizice ale proiectării) se pot implementa prin diferite metode reguli de operare ne-descriptive pentru a garanta consistenŃa datelor astfel încât acestea să respecte proprietăŃile impuse. Se pot aborda următoarele căi:

• utilizarea SQL pentru definirea schemei logice a bazei de date prin intermediul constrângerilor predefinite sau a aserŃiilor;

• utilizarea declanşatorilor sau a regulilor active;

• utilizarea manipulărilor SQL potrivite invocate din interiorul unui program.

Tehnici de realizare a documentaŃiei DocumentaŃia pentru conceptele variate reprezentate într-o schemă pot fi organizate uşor

sub forma unui dicŃionar de date. Acesta este format din două tabele:

• primul tabel descrie entităŃile din schemă: numele lor, definiŃii informale în limbaj natural, lista tuturor atributelor (cu o descriere a acestora) şi identificatorii posibili;

• al doilea tabel descrie relaŃiile: numele lor, descriere informală, lista atributelor (cu descrieri posibile) şi lista entităŃilor implicate împreună cu cardinalităŃile de participare la relaŃie.

93

Un exemplu de dicŃionar de date pentru schema din figura 17 este prezentat în figura 20. Este de notat faptul că dicŃionarul de date poate fi utilizat şi pentru documentarea unor constrângeri impuse datelor şi altor forme de reguli de operare.

Se mai poate alcătui un tabel în care se listează regulile organizate după tip. Unele reguli pot fi exprimate în formele precizate în secŃiunea anterioară. Să reamintim faptul că este importantă reprezentarea tuturor regulilor care descriu constrângeri ce nu sunt exprimate în schemă, dar poate fi de asemenea folositoare reprezentarea regulilor deja reprezentate în schemă. Un exemplu de documentaŃie de acest tip referitoare la schema din figura 17 este prezentat în figura 21.

Entitate Descriere Atribute Identificator ANGAJAT AngajaŃi care lucrează în

companie CNP, Nume, Prenume, Vârstă

CNP

PROIECT Proiectele companiei la care lucrează angajaŃii

Nume, Buget, DatăLivrare

Nume

DEPARTAMENT Departamentele filialei companiei

Telefon, Nume Nume, FILIALĂ

FILIALĂ Filiala companiei într-un oraş

Oraş, Adresă (Număr, Stradă, CodPoştal)

Oraş

RelaŃie Descriere EntităŃi implicate Atribute MANAGEMENT Asociază un manager cu

un departament Angajat (0,1) Departament (1,1)

MEMBRU Asociază un angajat cu un departament

Angajat (0,1) Departament (1,N)

DatăStart

PARTICIPARE Asociază angajaŃii cu proiectele

Angajat (0,N) Proiect (1,N)

DatăStart

COMPOZIłIE Asociază un departament cu o filială

Departament (1,1) Filială (1,N)

Figura 20. DicŃionarul de date pentru schema din figura 17 Constrângeri (RO1) managerul departamentului trebuie să aparŃină departamentului; (RO2) un angajat nu trebuie să aibă salariul mai mare decât managerul departamentului

căruia îi aparŃine; (RO3) un departament din filială Iaşi trebuie manageriat de un angajat cu mai mult de 10 ani

vechime în companie (RO4) un angajat care nu aparŃine unui departament nu trebuie să participe la nici un proiect. Derivări (RO5) bugetul unui proiect este obŃinut prin înmulŃirea sumei salariilor angajaŃilor ce

lucrează la el cu 3

Figura 21. Reguli de operare pentru schema din figura 17 Probleme propuse.

1. Se consideră schema E-R din figura 22.

94

a) CorectaŃi schema, luând în considerare proprietăŃile fundamentale ale generalizărilor.

b) Schema reprezintă doar femeile care muncesc; modificaŃi schema astfel încât să reprezinte toŃi muncitorii, bărbaŃi şi femei.

c) Atributul JudeŃ poate fi privit ca o sub-proprietate a atributului łară; restructuraŃi schema în acest sens.

2. AdăugaŃi cardinalităŃile minime şi maxime şi identificatorii pentru schema obŃinută după rezolvarea problemei 1; specificaŃi dacă există constrângeri de integritate pe schemă care nu pot fi exprimate prin modelul entitate-relaŃie.

3. ReprezentaŃi următoarele informaŃii printr-o schemă entitate-relaŃie:

o companie produce CD-uri cu un cod şi un titlu; pe fiecare CD au fost înregistraŃi unul sau mai mulŃi cântăreŃi, fiecare având un nume şi o adresă şi câŃiva dintre aceştia şi un nume de scenă.

4. CompletaŃi schema din problema 3 cu informaŃii care vi se par că lipsesc.

5. CreaŃi o schemă E-R pentru a reprezenta următoarele concepte, utilizând, dacă este cazul, construcŃii de tip generalizare. IndicaŃi atributele entităŃilor implicate şi tipul generalizărilor, rezolvând eventualele suprapuneri.

AngajaŃii unei companii se împart în manageri, programatori, analişti, şefi de proiect şi secretare. Există analişti care sunt de asemenea programatori. Şefii de proiect trebuie să fie manageri. Fiecare angajat are un cod, nume şi prenume. Fiecare categorie de angajaŃi are un salariu de bază. Fiecare angajat (în afară de manageri) are fixat un număr de ore de muncă.

Figura 22. Schema E-R pentru problema 1.

95

Capitolul 7. Proiectarea conceptuală Proiectarea conceptuală a bazelor de date constă în construirea unei scheme Entitate-RelaŃie care furnizează o descriere optimă a cerinŃelor clienŃilor. ConstrucŃia schemei este un proces iterativ, aceasta suferind o serie de transformări şi corecŃii. În acest capitol se vor descrie strategii pentru dezvoltarea unei scheme conceptuale. 7.1 Extragerea şi analiza cerinŃelor Prin extragerea cerinŃelor se înŃelege identificarea completă a problemelor pe care aplicaŃia trebuie să le rezolve şi a caracteristicilor aplicaŃiei. CerinŃele sunt transformate în specificaŃii care în general sunt exprimate în limbaj natural şi din acest motiv pot fi ambigue şi dezorganizate. Analiza cerinŃelor presupune clarificarea şi organizarea specificaŃiilor cerinŃelor. CerinŃele pot proveni din mai multe surse, cum ar fi:

• Utilizatori ai aplicaŃiei. În acest caz, informaŃia este obŃinută prin interviuri sau prin intermediul unor documente specifice scrise special pentru acest scop.

• DocumentaŃie existentă referitoare la problema de rezolvat: reguli interne, proceduri de operare etc. Sunt necesare colectarea şi selecŃia. Responsabilitatea revine proiectantului.

• Posibile aplicaŃii anterioare care trebuie să fie înlocuite sau cu care noua aplicaŃie trebuie să interacŃioneze.

Exemplu Se cere proiectarea unei baze de date pentru o companie ce organizează cursuri de instruire şi pentru care s-au colectat specificaŃiile prezentate în figura 1. Datele au fost extrase prin interviuri cu angajaŃii companiei.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Dorim crearea unei baze de date pentru o companie care face cursuri de instruire Pentru aceasta trebuie să stocăm date despre instruiŃi şi instructori. Pentru fiecare participant la curs (în jur de 5000), identificaŃi prin cod, vrem să stocăm codul numeric personal, numele, vârsta, sexul, locul naşterii, numele angajatorului, adresa şi numărul de telefon, angajatorii anteriori (şi perioada angajării), cursurile urmate (există circa 200 de cursuri) şi aprecierea finală la fiecare curs. Avem nevoie de asemenea să reprezentăm seminariile la care fiecare participant este aşteptat în prezent şi, pentru fiecare zi, locurile şi orele la care clasele sunt ocupate. Fiecare curs are un cod şi un titlu şi fiecare curs poate fi organizat de oricâte ori. Fiecărei organizări a unui curs particular îi spunem „ediŃie” a cursului. Pentru fiecare ediŃie vom reprezenta data de start, data de sfârşit şi numărul participanŃilor. Dacă un instruit este dintr-o profesie liberală trebuie cunoscut domeniul de expertiză şi dacă este necesar titlul său. Pentru oricine care lucrează la companie, vom stoca nivelul şi poziŃia deŃinută. Pentru fiecare instructor (circa 300) vom preciza numele, vârsta, locul naşterii, ediŃia cursului predat, cursurile predate în trecut şi cursurile pe care un titular este calificat să le Ńină. Se stochează numerele de telefon ale tuturor instructorilor. Un instructor poate fi angajat permanent al companiei de training sau poate fi angajat temporar.

Figura 1. Exemplu de cerinŃe exprimate în limbaj natural Este evident că cerinŃele au ambiguităŃi. Spre exemplu există participanŃi sau instruiŃi, titulari sau instructori, cursuri sau seminarii.

96

Vom stabili câteva reguli pentru scrierea specificaŃiilor mai precis şi fără ambiguităŃi: • Se alege un nivel potrivit de abstractizare. Se evită termenii prea generali sau prea

specifici. În cazul nostru, termenul perioadă (linia 5), titlu (linia 14) şi apreciere (linia 6) pot fi specificaŃi mai precis (dată start şi dată sfârşit, titlu profesional şi notă).

• Se standardizează structura propoziŃiei. Spre exemplu „pentru <concept> păstrăm <proprietăŃi>”.

• Se evită frazele complexe (angajat este preferat lui oricine care lucrează pentru o companie – linia 15)

• Se identifică sinonimele şi omonimele (ex.: titular şi instructor, participant curs şi instruit, respectiv loc care înseamnă locul de naştere cât şi locul unde se Ńin orele). Pentru sinonime se foloseşte un singur termen ir pentru omonime se caută alŃi termeni.

• Se marchează explicit referinŃele. AbsenŃa referinŃelor dintre termeni duce la concepte ambigue (ex.: la linia 5 adresa şi numărul de telefon se referă la angajat sau angajator?)

• Se construieşte un vocabular. Pentru fiecare termen, vocabularul conŃine o scurtă descriere, sinonime posibile şi referinŃe la alŃi termeni conŃinuŃi de vocabular cu care este în legătură logică. Un exemplu de vocabular este cel prezentat în figura 2.

Termen Descriere Sinonime Legături Instruit Participant la curs.

Poate fi angajat sau să aibă o profesie liberală.

Participant Curs, Companie

Instructor Titular curs. Poate fi angajat temporar.

Titular Curs

Curs Curs oferit. Poate avea mai multe ediŃii

Seminar Instructor, Instruit

Companie Compania unde este angjat participantul sau unde a fost angajat.

Instruit

Figura 2. Exemplu de vocabular În acest moment se pot rescrie specificaŃiile folosind cele menŃionate mai sus şi se încearcă gruparea cerinŃelor ca în figura 3. După specificarea datelor trebuie specificate operaŃiile care trebuie executate asupra acestor date. În cazul nostru operaŃiile ar putea fi:

• OperaŃia 1: se inserează un nou instruit incluzând datele despre el (se realizează de aproximativ 40 de ori pe zi)

• OperaŃia 2: se atribuie un instruit unei ediŃii a unui curs (de 50 de ori pe zi) • OperaŃia 3: se inserează un nou instructor, incluzând toate datele şi cursurile pe care

acesta este calificat să le predea (de două ori pe zi) • OperaŃia 4: se atribuie un instructor calificat pentru o ediŃie a unui curs (de 15 ori pe

zi) • OperaŃia 5. se afişează toate informaŃiile despre o ediŃie anterioară a cursului: titlu,

orar, număr de instruiŃi (de 10 ori pe zi) • OperaŃia 6: afişează toate cursurile disponibile, cu informaŃii despre instructorii

calificaŃi să le Ńină (de 20 de ori pe zi) • OperaŃia 7: pentru fiecare instructor, se găsesc instruiŃii pentru toate cursurile pe care

le Ńine sau le-a Ńinut(de 5 ori pe săptămână)

97

• OperaŃia 8: se realizează o analiză statistică a tuturor instruiŃilor cu toate informaŃiile despre ei, despre ediŃiile cursurilor pe care le-au urmat şi notele obŃinute (de 10 ori pe lună)

CerinŃe generale

Se doreşte crearea unei baze de date pentru o companie care derulează cursuri de instruire. Se doreşte păstrarea datelor pentru instruiŃi şi instructori.

CerinŃe referitoare la instruiŃi

Pentru fiecare instruit (în jur de 5000), identificat de un cod, se va păstra codul numeric personal, nume, vârstă, sex, oraşul de naştere, angajatorul curent, angajatorul precedent (cu data de start şi data de sfârşit a perioadei în care a fost angajat), ediŃiile cursurilor pe care instruitul le urmează în prezent şi cele pe care le-a urmat împreună cu nota obŃinută.

CerinŃe referitoare la angajatorii instrui Ńilor

Pentru fiecare angajator a unui instruit se va păstra numele, adresa şi numărul de telefon

CerinŃe referitoare la cursuri Pentru fiecare curs (în jur de 200) trebuie stocat numele şi codul. Fiecare organizare a unui curs anume se numeşte ediŃie a cursului. Pentru fiecare ediŃie se va stoca data de start, data de sfârşit şi numărul participanŃilor. Pentru ediŃia curentă se vor păstra datele, sălile de clasă, şi momentele în care clasa este ocupată.

CerinŃe referitoare la tipuri specifice de instruiŃi

Pentru un instruit care practică o profesie liberală (liber profesionist), se va păstra domeniul de expertiză şi eventual titlul profesional. Pentru un instruit care este angajat, se vor păstra nivelul şi poziŃia ocupată.

CerinŃe referitoare la instructori

Pentru fiecare instructor (în jur de 300), se vor păstra numele, vârsta, oraş de naştere toate numerele de telefon, ediŃiile cursurilor predate în prezent şi în trecut, şi cursurile pe care calificat să le predea. Instructorii pot fi angajaŃi permanenŃi ai companiei de training sau pot fi angajaŃi temporar

7.2 Strategii de proiectare Pentru dezvoltarea unei scheme conceptuale pe baza specificaŃiilor impuse se pot aplica strategii de proiectare din alte domenii, strategii ce vor fi descrise în continuare. Strategia top-down (se sus în jos) Schema conceptuală este obŃinută printr-o serie de rafinări succesive ale schemei iniŃiale ce descrie toate cerinŃele prin intermediul câtorva concepte abstracte.

Procedura este descrisă în figura 4. Fiecare nivel reprezentat conŃine o schemă ce descrie informaŃii diverse la diferite grade de detaliu.

Trecerea de la un nivel la altul se face cu ajutorul unor transformări numite primitive de transformare de sus în jos (v. figura 5). Primitivele operează pe un singur concept al schemei şi îl transformă într-o structură de complexitate mai ridicată, capabilă să descrie conceptul iniŃial în detaliu.

98

Transformarea T1 – se aplică atunci când o entitate descrie două concepte logice diferite legate unele de altele. Spre exemplu în aplicaŃia descrisă în secŃiunea anterioară se poate începe cu entitatea CURS. Acest concept pare prea abstract, putând face deosebirea între TIPCURS (care are un cod şi un titlu) şi EDIłIECURS (care are o dată de start şi una de sfârşit). Aceste două entităŃi pot fi legate prin relaŃia TIP.

Transformarea T2 – este aplicată atunci când o entitate este alcătuită din sub-entităŃi. În aplicaŃia noastră această transformare are loc atunci când ne dăm seama că printre cei instruiŃi se poate distinge între ANGAJAT şi LIBERPROFESIONIST.

Transformarea T3 – se aplică atunci când o relaŃie descrie două sau mai multe concepte diferite legând aceleaşi entităŃi. Spre exemplu, în relaŃia PREDARE între instructori şi cursuri, PREDARECURENTĂ poate fi separată de PREDAREANTERIOARĂ.

Figura 4. Strategia top-down

99

Transformare Concept iniŃial Rezultat

T1 De la o entitate la două entităŃi

şi relaŃia dintre ele

T2 De la o entitate la o

generalizare

T3 De la o relaŃie la relaŃii

multiple

T4 De la o relaŃie la o entitate cu

relaŃii

T5 Adăugarea atributelor la o

entitate

T6 Adăugarea atributelor la o

relaŃie

Figura 5. Primitive de transformare top-down

Transformarea T4 – se aplică atunci când o relaŃie descrie un concept cu existenŃă autonomă. Spre exemplu, dacă relaŃia CONTRACT între o entitate CONSULTANT şi o entitate COMPANIE are multe atribute, atunci ea este mai bine reprezentată printr-o entitate legată de altele prin intermediul unor relaŃii binare.

Transformarea T5 – se aplică pentru adăugarea unor proprietăŃi (atribute) entităŃilor. Această se întâmplă spre exemplu atunci când rafinăm entitatea INSTRUIT prin adăugarea atributelor CNP, Nume, Vârstă, Sex şi OraşDeNaştere.

Transformarea T6 – se aplică atunci când se adaugă proprietăŃi la o relaŃie, într-o manieră similară transformării T5.

Avantajul acestei strategii este acela că proiectantul poate începe cu o reprezentare completă a cerinŃelor, chiar dacă lipsesc unele detalii. Dezavantajul ar fi că este necesară o viziune globală asupra tuturor conceptelor, ceea ce este dificil de realizat în cazul aplicaŃiilor complexe.

100

Strategia bottom-up (de jos în sus) În această strategie specificaŃiile ini Ńiale sunt descompuse în componente până când fiecare componentă descrie un fragment elementar al specificaŃiilor (v. figura 6). În acest punct, componentele sunt reprezentate prin scheme conceptuale simple. Aceste scheme vor fi combinate pentru a se obŃine schema finală. Şi în acest caz se utilizează transformări elementare (primitive de transformare de sus în jos – figura 7). Aceste primitive introduc în schemă concepte noi care nu au fost prezente până în acel moment, capabile să descrie aspecte ale aplicaŃiei care nu au fost luate în considerare.

Transformarea T1 – se aplică atunci când se identifică în specificaŃii o clasă de obiecte cu proprietăŃi comune. În aplicaŃia descrisă anterior se poate identifica entitatea CLASĂ (ce păstrează o anumită clasă la un anumit moment).

Transformarea T2 – se aplică atunci când se identifică în specificaŃii o legătură logică între două entităŃi. În aplicaŃia noastră se poate identifica relaŃia CALIFICARE între entităŃile INSTRUCTOR şi CURS.

Transformarea T3 – se aplică atunci când se identifică în specificaŃii o generalizare între entităŃi. Spre exemplu, entitatea INSTRUCTOR este o generalizare a entităŃilor PERMANENT şi TEMPORAR.

Transformarea T4 – se aplică atunci când se identifică în specificaŃii o entitate care poate fi privită ca o agregare a unor serii de atribute. Spre exemplu se identifică entitatea INSTRUIT cu proprietăŃile CNP, Nume, Vârstă, Sex şi OraşDeNaştere.

Transformarea T4 – se aplică atunci când o relaŃie poate fi privită ca o agregare a unor atribute.

Figura 6. Strategia bottom-up

101

Transformare Concept iniŃial Rezultat

T1 Generarea unei entităŃi

T2 Generarea unei relaŃii

T3 Generarea unei generalizări

T4 Agregarea unor atribute pe o

entitate

T5 Agregarea unor atribute pe o

relaŃie

Figura 7. Primitive de transformare bottom-up

Avantajul acestei strategii este acela că permite descompunerea problemei în componente simple care pot fi uşor identificate şi astfel procesul de proiectare poate fi atribuit mai multor proiectanŃi dacă este necesar. Dezavantajul ar fi că este necesară integrarea mai multor scheme conceptuale. Strategia inside-out (din interior spre exterior) Această strategie poate fi privită ca o particularizare a strategiei de jos în sus. Se începe cu câteva concepte importante şi apoi pe baza acestora, proiectarea se extinde radial. Cu alte cuvinte se reprezintă mai întâi conceptele cele mai apropiate de conceptele iniŃiale şi apoi procesul de proiectare se mută spre conceptele mai depărtate prin intermediul navigării prin specificaŃii. Avantajul acestei strategii constă în eliminarea paşilor de integrare din strategia de jos în sus. Pe de altă parte este necesară examinarea, din timp în timp, a tuturor specificaŃiilor căutând concepte ce nu au fost reprezentate încă. Un exemplu al dezvoltării din interior spre exterior se poate observa în figura 8, care se referă la exemplul din figura 17 a capitolului 6. Ariile indicate prezintă o dezvoltare cronologică posibilă a schemei.

102

Figura 8. Exemplu de strategie inside-out Strategia mixtă Se poate adopta o strategie mixtă care combină avantajele strategiilor top-down, bottom-up şi inside-out. ProiectanŃii descompun cerinŃele în componente conform strategiei bottom-up, dar nu se dezvoltă componentele separat. În acelaşi timp se defineşte o schemă cadru care conŃine, la nivel abstract, principalele componente ale aplicaŃiei. Schema cadru furnizează o viziune sintetică asupra procesului de proiectare şi uşurează integrarea schemelor dezvoltate separat. Spre exemplu, pentru exemplul prezentat în secŃiunile anterioare o schemă cadru posibilă este prezentată în figura 9.

Figura 9. Schema cadru pentru procesul de instruire într-o companie Din acest punct se pot examina separat conceptele principale prin rafinări graduale (urmând paşii strategiei top-down) sau se pot extinde componentele cu concepte care nu au fost încă reprezentate (conform strategiei bottom-up). 7.3 Calitatea unei scheme conceptuale. ProprietăŃile utilizate pentru a stabili calitatea unei scheme sunt: Corectitudinea schemei. O schemă conceptuală este corectă dacă utilizează corect construcŃiile puse la dispoziŃie de modelul conceptual. Se pot defini două tipuri de erori:

103

• erorile sintactice marchează utilizarea ilegală a unei construcŃii (ex.: generalizarea dintre relaŃii în detrimentul entităŃilor).

• erorile semantice marchează utilizarea unei construcŃii care nu-şi urmăreşte definiŃia (ex.: utilizarea unei relaŃii pentru a descrie faptul că o entitate este o specializare a altei entităŃi).

Caracterul complet al schemei. O schemă conceptuală este completă dacă include concepte ce reprezintă toate cerinŃele de date şi care permit execuŃia tuturor operaŃiilor incluse în cerinŃele operaŃionale.

Accesibilitatea schemei. O schemă conceptuală este accesibilă când reprezintă cerinŃele într-un mod natural şi uşor de înŃeles.

Minimalitatea schemei. O schemă este minimală când toate specificaŃiile datelor sunt reprezentate doar o singură dată în schemă. O schemă nu este minimală când apar redundanŃele – concepte derivate din altele. O sursă tipică de redundanŃe este prezenŃa ciclurilor determinate de prezenŃa relaŃiilor şi/sau generalizărilor. Câteodată redundanŃele sunt necesare din motive de proiectare, aceste situaŃii fiind precizate în documentaŃie. 7.4 Metodă de abordare a proiectării conceptuală În practică se aplică foarte rar o singură strategie de proiectare conceptuală. Independent de strategia aleasă, apare necesitatea modificării schemei utilizând transformări top-down (prin care se rafinează conceptele deja prezentate) şi transformări bottom-up (prin care se adaugă concepte noi).

Etapele ce trebuie parcurse pentru realizarea unei scheme conceptuale sunt:

1. Analiza cerinŃelor

• Construirea unui vocabular

• Analiza cerinŃelor şi eliminarea ambiguităŃilor

• Gruparea cerinŃelor

2. Etapa de bază

• Identificarea celor mai relevante concepte şi reprezentarea lor într-o schemă cadru

3. Descompunerea (folosită dacă este potrivită sau necesară)

• Descompunerea cerinŃelor cu referire la conceptele prezentate în schema cadru

4. Etapa iterativă (se repetă pentru toate schemele până când fiecare specificaŃie este reprezentată)

• Rafinarea conceptelor pe baza cerinŃelor

• Adăugarea de concepte noi care descriu părŃi ale cerinŃelor nereprezentate încă

5. Integrarea

• Integrarea sub-schemelor într-o schemă generală Ńinând cont de schema cadru

6. Analiza calităŃii

• Verificarea corectitudinii şi realizarea restructurărilor necesare

104

• Verificarea caracterului complet al schemei şi realizarea restructurărilor necesare

• Verificarea minimalităŃii, listarea redundanŃelor şi dacă este necesar realizarea restructurărilor necesare

• Verificarea accesibilităŃii şi realizarea restructurărilor necesare dacă este necesar

7.5 Exemplu de proiectare conceptuală

Se consideră exemplul unui proces de instruire din cadrul unei companii despre care am mai discutat şi în secŃiunile anterioare. Am indicat o schemă cadru posibilă în figura 9. Din acest punct se poate decide analizarea separată a specificaŃiilor pentru instruiŃi, cursuri şi instructori şi de a aplica o strategie inside-out pentru fiecare.

Ne vom referi întâi la instruiŃi. Se pot identifica două tipuri: angajaŃi şi liber profesionişti. Aceste entităŃi se reprezintă ca specializări ale entităŃii INSTRUIT; generalizarea este totală. Este necesară reprezentarea angajatorilor instruiŃilor . Aceasta se poate face introducând entitatea ANGAJATOR care este legată printr-o relaŃie de ANGAJAT. Este necesară de asemenea distincŃia între conceptele angajare actuală şi anterioară. Decidem să divizăm relaŃia în două relaŃii: A NGAJAREANTERIOARĂ şi ANGAJAREACTUALĂ. Prima are o dată de start şi una de sfârşit şi este legată de entitatea INSTRUIT (deoarece şi liber profesioniştii se poate să fi fost angajaŃi). A doua relaŃie are doar dată de start şi este legată de entitatea ANGAJAT. Adăugând atribute entităŃilor şi relaŃiilor, cardinalităŃi pentru relaŃii şi identificatori ai entităŃilor se obŃine schema din figura 10.

Figura 10. Rafinarea unei porŃiuni a schemei cadru Se observă că entitatea INSTRUIT are doi identificatori (Cod şi CNP). Atributul TitluProfesional este opŃional.

105

Pentru instructori se disting cazurile în care aceştia sunt fie angajaŃi permanenŃi ai companiei, fie angajaŃi temporar. Se realizează astfel o generalizare totală, cu entitatea părinte INSTRUCTOR. Se adaugă atributele precizate în specificaŃii: Nume, Vârstă, OraşDeNaştere şi Telefon. Se observă cu nu se poate stabili un identificator pe baza acestor atribute. Din acest motiv, se decide folosirea CNP-ului instructorului chiar dacă nu este cerut în specificaŃii. Schema rezultată este prezentată în figura 11.

Figura 11. Rafinarea unei alte porŃiuni a schemei cadru

În ceea ce priveşte entitatea CURS, există două concepte distincte legate: un concept abstract al cursului (cu nume şi cod) şi ediŃia cursului (cu dată de start, dată de sfârşit şi numărul de instruiŃi). Vom reprezenta aceste concepte cu două entităŃi distincte legate prin relaŃia TIP. Clasele unui curs se pot descrie printr-o entitate legată de ediŃiile cursurilor prin relaŃia COMPOZIłIE. Se adaugă apoi atribute, cardinalităŃi şi identificatori. O clasă este identificată prin sală, timp şi dată. Pentru ediŃiile unui curs, presupunem că două ediŃii ale aceluiaşi curs nu pot începe în aceeaşi zi şi astfel un identificator pentru entitatea EDIłIECURS este format din atributul DatăStart şi entitatea CURS. Schema rezultată este prezentată în figura 12.

Figura 12. Rafinarea unei alte părŃi a schemei cadru

Schema finală este obŃinută prin integrarea schemelor obŃinute până în acest punct. Între schemele referitoare la instructori şi cursuri. În schema cadru aceste părŃi sunt legate prin relaŃia PREDARE. Această relaŃie trebuie rafinată. Se identifică trei legături diferite între instructori şi cursuri: predare curentă, predare anterioară şi calificare. Aceste legături se reprezintă prin intermediul a trei relaŃii: primele două leagă entităŃile INSTRUCTOR şi EDIłIECURS, iar a treia leagă INSTRUCTOR de CURS. În aceste moment se poate face integrarea. łinând cont de schema cadru, trebuie clarificată relaŃia între cursuri şi instruiŃi. Apar două cazuri: prezenŃă curentă şi şi prezenŃă anterioară. Deci definim două relaŃii între entităŃile INSTRUIT şi EDIłIECURS. Pentru o prezenŃă anterioară interesează nota finală. Se obŃine în final schema din figura 13.

106

Schema finală E-R pentru o companie de instruire În acest moment se începe verificarea schemei obŃinute. Se verifică dacă schema este completă prin întoarcerea la specificaŃii şi verificarea dacă toate datele sunt reprezentate şi toate operaŃiile pot fi efectuate. Spre exemplu, să considerăm operaŃia 7, în care se cer instruiŃii pentru toate cursurile Ńinute de un instructor. Datele pentru această operaŃie se găsesc pe schemă în felul următor: se pleacă de la entitatea INSTRUCTOR, se trece prin relaŃiile PREDARECURENTĂ şi PREDAREANTERIOARĂ, entitatea EDIłIECURS, relaŃiile PREZENłĂCURENTĂ şi PREZENłĂANTERIOARĂ şi apoi se ajunge la entitatea INSTRUIT. Cu privire la minimalitate, să notăm că există o redundanŃă în schemă: atributul NrParticipanŃi al entităŃii EDIłIECURS se poate obŃine prin numărarea numărului de instanŃe ale entităŃii INSTRUIT care sunt legate de ediŃia respectivă. Se va discuta despre eliminarea sau menŃinerea acestei redundanŃe în capitolul următor, referitor la proiectarea logică. Trebuie menŃionat în final că schema trebuie să aibă o documentaŃie potrivită. Este importantă descrierea restricŃiilor posibile care nu sunt exprimate în schemă, sub forma regulilor de operare. Un exemplu ar fi precizarea că un instructor predă un curs doar dacă este calificat să o facă. Probleme propuse 1. Se doreşte crearea unei baze de date prin care să se gestioneze împrumuturile dintr-o

bibliotecă. SpecificaŃiile obŃinute prin interviu sunt următoarele:

Un cititor care utilizează biblioteca are un card pe care este memorat un cod, numele şi adresa. Utilizatorul face o cerere pentru împrumutul unor cărŃi catalogate în bibliotecă. Fiecare carte are un titlu şi o listă a autorilor şi pot exista mai multe exemplare pentru fiecare carte. Fiecare carte din bibliotecă este identificată printr-un cod. Urmând cererea, se consultă o arhivă a cărŃilor disponibile (aceasta conŃine care nu sunt împrumutate în prezent). Dacă este disponibilă cartea, se caută cartea pe rafturi. Odată ce cartea este găsită este dată cititorului. Textul este apoi clasificată ca fiind împrumutată. După ce cartea este citită, este returnată, pusă înapoi pe raft şi re-clasificată ca disponibilă. Pentru fiecare împrumut se înregistrează orele şi datele luării şi returnării cărŃii.

107

Să se analizeze specificaŃiile, să se filtreze ambiguităŃile şi să se grupeze după tip. Să se acorde o atenŃie deosebită diferenŃei dintre conceptul de carte şi de copie a unei cărŃi. IdentificaŃi legăturile logice dintre grupurile de specificaŃii obŃinute.

2. Să se reprezinte specificaŃiile din exerciŃiul precedent folosind o schemă entitate-relaŃie. 3. Să se realizeze o schemă entitate-relaŃie care descrie datele unei aplicaŃii referitoare la un lanŃ de târguri auto. Interesează:

• târgurile, cu nume (identificator), adresă şi număr de telefon

• automobilele, cu număr de înregistrare (identificator), model (şir de caractere) şi proprietar

• clienŃii (proprietarii de automobile) cu cod numeric personal, nume, prenume şi telefon; în plus fiecare client poate fi proprietarul mai multor automobile

• service-ul realizat la târg, cu număr (unic pentru un târg anume) dată de start şi de sfârşit, componentele înlocuite (cu cantităŃi) şi numărul orelor de muncă

• piesele de schimb disponibile cu cod, nume şi cost pe bucată PrecizaŃi cardinalităŃile relaŃiilor şi (cel puŃin) un identificator pentru fiecare entitate

108

Capitolul 8. Proiectarea logică Scopul proiectării logice este de a construi o schemă logică ce reprezintă corect şi eficient toate informaŃiile descrise într-o schemă entitate-relaŃie. Translatarea din schema conceptuală în cea logică nu este un proces simplu deoarece, în primul rând, nu toate construcŃiile modelului E-R pot fi translatate natural într-un model relaŃional. Spre exemplu, dacă o entitate poate fi reprezentată simplu printr-o relaŃie, pentru o generalizare există mai multe variante. În al doilea rând, scopul proiectării conceptuale este de reprezenta datele la un nivel înalt de abstractizare, fără a Ńine cont de detaliile de implementare. Proiectarea logică trebuie să ia în considerare performanŃele impuse produsului final. Din acest motiv, proiectarea logică presupune parcurgerea următoarelor etape:

• Restructurarea schemei E-R – fază independentă de modelul logic ales şi care se bazează pe criterii de optimizare a schemei şi de simplificare a următoarei etape;

• Translarea în modelul logic –care Ńine cont de un anumit model logic (modelul relaŃional spre exemplu) şi poate include alte optimizări, bazate pe caracteristicile modelului logic.

La intrarea primei etape există schema conceptuală şi estimarea încărcării bazei de date

(cantitatea de date şi cerinŃele operaŃionale). Rezultatul acestei etape este schema E-R restructurată, care nu mai este o schemă E-R propriu-zisă deoarece utilizează aspecte de implementare pentru reprezentarea datelor.

Această schemă şi modelul logic ales formează intrarea celei de-a doua etape, care va produce schema logică a bazei de date, împreună cu constrângerile de integritate aferente şi documentaŃia.

Figura 1. Proiectarea logică a unei baze de date 8.1 Analiza performanŃelor schemei E-R O schemă E-R poate fi modificată astfel încât să se optimizeze unii indicatori de

performanŃă:

109

• costul unei operaŃii – evaluat în funcŃie de numărul de apariŃii ale entităŃilor şi relaŃiilor ce sunt parcurse pentru a executa o operaŃie asupra unei baze de date;

• cerinŃa de stocare – evaluată în funcŃie de numărul de octeŃi necesari stocării datelor descrise de schemă.

Pentru a evalua aceşti indicatori avem nevoie de următoarele informaŃii:

• volumul de date – numărul de apariŃii ale fiecărei entităŃi şi relaŃii din schemă; – dimensiunea fiecărui atribut.

• caracteristicile operaŃiilor : – tipul operaŃiei (interactivă sau batch); – frecvenŃa operaŃiei (numărul mediu de execuŃii într-un anumit interval de timp); – datele implicate (entităŃi şi/sau relaŃii).

Să luăm ca exemplu schema din figura următoare:

Figura 2. Exemplu de schemă E-R OperaŃiile tipice pentru această schemă pot fi:

• operaŃia 1: atribuie un angajat unui proiect;

• operaŃia 2: găseşte datele pentru un angajat, pentru departamentul în care lucrează acesta şi pentru proiectele în care este implicat;

• operaŃia 3: găseşte datele pentru toŃi angajaŃii unui anumit departament;

• operaŃia 4: pentru fiecare filială, găseşte departamentele, cu numele managerilor şi lista angajaŃilor din fiecare departament.

Volumul datelor şi caracteristicile generale ale operaŃiilor pot fi descrise prin utilizarea unor tabele, ca în figura următoare:

110

Tabelul volumelor Concept Tip Volum Filială E 10 Tabelul opera Ńiilor Departament E 80 Opera Ńie Tip Frecven Ńă Angajat E 2000 Op 1 I 50/zi Proiect E 500 Op 2 I 100/zi CompoziŃie R 80 Op 3 I 10/zi Membru R 1900 Op 4 B 2/săptămână Management R 80 Participare R 6000

Figura 3. Exemplu de tabele ale volumelor şi operaŃiilor În tabelul volumelor se prezintă toate conceptele schemei (entităŃi şi relaŃii) cu volumul lor estimat. În tabelul operaŃiilor se înscriu, pentru fiecare operaŃie, frecvenŃa aşteptată şi un simbol care arată dacă operaŃia este interactivă (I) sau batch (B). În tabelul volumelor, numărul apariŃiilor unei relaŃii depinde de doi parametri:

• volumul entităŃilor implicate în relaŃie

• de câte ori o apariŃie a acestor entităŃi participă, în medie, într-o apariŃie a relaŃiei; acest parametru depinde de cardinalităŃile relaŃiei.

Spre exemplu, numărul de apariŃii ale relaŃiei COMPOZIłIE este egal cu numărul departamentelor, deoarece cardinalitatea relaŃiei indică faptul că fiecare departament aparŃine doar unei filiale. Numărul de apariŃii ale relaŃiei MEMBRU este mai mic decât numărul angajaŃilor, deoarece există angajaŃi care nu aparŃin nici unui departament. Dacă un angajat este implicat în medie în trei proiecte avem 6000 de apariŃii ale relaŃiei PARTICIPARE (şi deci 6000/500=12 angajaŃi în medie pentru fiecare proiect).

Pentru fiecare operaŃie se pot descrie datele implicate prin intermediul schemei de navigare care constă în fragmente ale schemei E-R relevante pentru operaŃie. Pe această schemă este utilă desenarea „căilor logice” care trebuie urmate pentru a accesa informaŃiile cerute. Un exemplu de schemă de navigare este prezentat în figura 4, referitoare la operaŃia 2.

Figura 4. Exemplu de schemă de navigare Acum se poate estima costul unei operaŃii prin numărarea accesărilor apariŃiilor entităŃilor şi relaŃiilor necesare efectuării unei operaŃii. Ca exemplu ne vom referi tot la operaŃia 2. łinând cont de schema de navigare trebuie să accesăm întâi o apariŃie a entităŃii A NGAJAT pentru a accesa o apariŃie a relaŃiei MEMBRU şi, prin intermediul acesteia, a unei apariŃii a entităŃii DEPARTAMENT. Pentru a obŃine datele referitoare la un proiect la care angajatul lucrează trebuie să accesăm în medie trei apariŃii ale relaŃiei

111

PARTICIPARE (deoarece am presupus că un angajat lucrează în medie la trei proiecte). Apoi, prin această relaŃie accesăm în medie trei apariŃii ale entităŃii PROIECT. Aceste informaŃii pot fi sumate în tabelul accesărilor din figura 5. În ultima coloană a acestui tabel se menŃionează tipul accesului (R pentru citire, W pentru scriere)

Tabelul acces ărilor

Concept Tip Acces ări Tip Angajat Entitate 1 R Membru RelaŃie 1 R Departament Entitate 1 R Participare RelaŃie 3 R Proiect Entitate 3 R

Figura 5. Tabelul accesărilor pentru operaŃia 2 8.2 Restructurarea schemei E-R Etapele restructurării unei scheme E- R sunt precizate în figura 6.

Figura 6. Etapele restructurării schemei E-R

• analiza redundanŃelor - decide ştergerea sau păstrarea unor redundanŃe din schema E-R;

• eliminarea generalizărilor - înlocuieşte toate generalizările cu alte construcŃii; • partiŃionarea/combinarea entităŃilor şi relaŃiilor – decide dacă este convenabilă

partiŃionarea unui concept în mai multe sau unirea mai multor concepte separate în unul singur;

• selecŃia identificatorilor primari – alege un identificator pentru acele entităŃi care au mai mult de un identificator.

Analiza redundanŃelor Într-o schemă conceptuală o redundanŃă corespunde unei informaŃii ce poate fi derivată din alte date. Cele mai frecvente exemple sunt:

112

• atribute ale căror valori pot fi derivate, pentru fiecare apariŃie a unei entităŃi/relaŃii, din valorile altor atribute pentru aceeaşi apariŃie. În exemplul de mai jos, fiecare atribut poate fi dedus din celelalte două prin operaŃii aritmetice

• atribute ce pot fi derivate din alte atribute aparŃinând altor entităŃi/relaŃii, de obicei prin intermediul funcŃiilor agregat. În exemplul următor, atributul ValoareTotala a entităŃii A CHIZITIE poate fi calculat din valorile atributului Pret a entităŃii PRODUS prin sumarea preŃurilor produselor achiziŃionate, după cum se specifică în relaŃia COMPUNERE.

• atribute ce pot fi derivate din operaŃii de numărare a apariŃiilor. În exemplul de mai jos, atributul NumarLocuitori poate fi obŃinut prin numărarea apariŃiilor relaŃiei REZIDENłĂ în care apare un anumit oraş.

• relaŃii ce pot fi derivate din alte relaŃii în prezenŃa ciclurilor. RelaŃia INVATARE dintre studenŃi şi profesori poate fi derivată din relaŃiile PREZENłĂ şi ATRIBUIRE. PrezenŃa ciclurilor nu generează în mod automat redundanŃe. Spre exemplu, dacă se înlocuieşte relaŃia INVATARE cu relaŃia SUPERVIZARE reprezentând legătura dintre studenŃi şi supervizori, atunci schema nu mai este redundantă.

PrezenŃa informaŃiilor derivate într-o bază de date prezintă un avantaj şi totodată dezavantaje. Avantajul este reducerea numărului de accesări necesare obŃinerii informaŃiilor derivate. Dezavantajele ar fi:

• creşterea cererii de stocare;

• necesitatea efectuării de operaŃii suplimentare pentru actualizarea informaŃiilor derivate.

113

Decizia menŃinerii sau eliminării unei redundanŃe poate fi luată prin compararea costului operaŃiilor ce implică informaŃia redundantă şi capacitatea de stocare necesară în cazul prezenŃei, respectiv absenŃei redundanŃei. Exemplu

Să considerăm exemplul referitor la persoane şi oraşe

pentru care se definesc următoarele operaŃii:

• operaŃia 1: adaugă o persoană nouă cu oraşul de rezidenŃă al acesteia;

• operaŃia 2: afişează toate datele referitoare la oraş (inclusiv numărul de locuitori). Presupunem de asemenea că încărcarea bazei de date este dată în tabelele de mai jos.

Volumul relaŃiei REZIDENłĂ este egal cu volumul entităŃii PERSOANA deoarece cardinalităŃile indică faptul că fiecare persoană are rezidenŃa într-un singur oraş.

Tabelul volumelor Tabelul opera Ńiilor Concept Tip Volum Opera Ńie Tip Frecven Ńă Oraş E 200 Op 1 I 500/zi Persoană E 1000000 Op 2 I 2/zi RezidenŃă R 1000000

1. Să facem o evaluare a performanŃelor în cazul prezenŃei redundanŃei (atributul NumarLocuitori). Presupunem că pentru stocarea numărului de locuitori dintr-un oraş sunt necesari 4 octeŃi. Astfel, datele redundante necesită 4 x 200 = 800 octeŃi (nesemnificativ). Să estimăm acum costul operaŃiilor . Pentru operaŃia 1:

• 1 operaŃie de scriere în entitatea PERSOANĂ (adăugare persoană); • 1 operaŃie de scriere în relaŃia REZIDENłĂ (adăugare pereche persoană-oraş); • 1 operaŃie de citire din entitatea ORAŞ (găsire oraş); • 1 operaŃie de scriere în entitatea ORAŞ ( actualizare număr de locuitori).

Tabelul acces ărilor (în prezen Ńa redundan Ńelor) OperaŃia 1

Concept Tip Acces ări Tip Persoana E 1 W RezidenŃă R 1 W Oraş E 1 R Oraş E 1 W

OperaŃia 1 necesită 3 x 500 = 1500 accesări în scriere / zi 1 x 500 = 500 accesări în citire / zi Pentru operaŃia 2:

• 1 operaŃie de citire din entitatea ORAŞ

Tabelul acces ărilor (în prezen Ńa redundan Ńelor) OperaŃia 2

Concept Tip Acces ări Tip Oraş E 1 R

OperaŃia 2 necesită 1 x 2 = 2 accesări în scriere / zi (neglijabil)

114

Presupunând că accesul în scriere costă de două ori mai mult decât accesul în citire, numărul de accesări / zi în prezenŃa redundanŃelor este 2 x 1500 + 500 = 3500. 2. Să facem o evaluare a performanŃelor în cazul absenŃei redundanŃei. Vom evalua costul operaŃiilor. Pentru operaŃia 1:

• 1 operaŃie de scriere în PERSOANA; • 1 operaŃie de scriere în REZIDENłĂ.

Tabelul acces ărilor (în absen Ńa redundan Ńelor) OperaŃia 1

Concept Tip Acces ări Tip Persoana E 1 W RezidenŃă R 1 W

OperaŃia 1 necesită 2 x 500 = 1000 accesări în scriere / zi Pentru operaŃia 2:

• 1 operaŃie de citire din ORAŞ (neglijabil) • în medie 5000 de operaŃii de citire din REZIDENłĂ pentru a determina numărul de

locuitori (5000 = 1000000 persoane / 200 oraşe)

Tabelul acces ărilor (în absen Ńaredundan Ńelor) OperaŃia 2

Concept Tip Acces ări Tip Oraş E 1 R RezidenŃă R 5000 R

OperaŃia 2 necesită 2 x 5000 = 10000 accesări în citire / zi

Numărul de accesări / zi în absenŃa redundanŃelor este de 2 x 1000 + 10000 = 12000.

Se observă că în al doilea caz sunt necesare 8500 accesări / zi în plus pentru a „salva” mai puŃin de un kilo-octet. Putem trage concluzia că este mai convenabil să menŃinem redundanŃa în cazul acestei probleme.

Eliminarea generalizărilor

Deoarece modelul relaŃional nu permite reprezentarea directă a generalizărilor din modelul E-R apare necesitatea transformării acestor construcŃii în alte construcŃii ce pot fi translate cu uşurinŃă. Există trei posibilităŃi de reprezentare a unei generalizări cu ajutorul entităŃilor şi relaŃiilor. Pentru înŃelegerea acestor moduri de transformare să considerăm schema din figura 7.

Figura 7. Exemplu de schema cu generalizare

1. Înglobarea entităŃilor copil în entitatea părinte

115

EntităŃile E1 şi E2 sunt eliminate şi proprietăŃile acestora sunt adăugate proprietăŃii părinte E0. Se adaugă entităŃii părinte atributul Atype pentru a distinge tipul (E1 sau E2) unei apariŃii a lui E0. A11 şi A21 pot avea valoarea NULL pentru unele apariŃii ale entităŃii E0. RelaŃia R2 va avea cardinalitatea minimă 0 pentru E0 deoarece apariŃiile lui E2 formează o submulŃime a apariŃiilor entităŃii E0.

2. Înglobarea entităŃii părinte în entităŃile copil

Entitatea părinte E0 este eliminată şi, prin proprietatea de moştenire, atributele sale,

identificatorii şi relaŃiile în care era implicată sunt adăugate entităŃilor copil E1 şi E2. RelaŃiile R11 şi R12 reprezintă restricŃia relaŃiei R1 pe apariŃiile entităŃii E1, respectiv E2.

3. Substituirea unei generalizări cu relaŃii

Generalizarea este transformată în două relaŃii „una la unu” ce leagă părintele de copiii E1 şi E2. Nu există transfer de atribute sau relaŃii şi entităŃile E1 şi E2 sunt identificate extern prin entitatea E0. În noua schemă apar constrângeri în plus: nici o apariŃie a lui E0 nu poate participa

116

în ambele relaŃii RG1 şi RG2.; mai mult, dacă generalizarea este completă, fiecare apariŃie a lui E0 trebuie să participe în exact una din relaŃiile RG1 şi RG2.

Pentru a alege una din cele trei variante trebuie avute în vedere următoarele aspecte:

• Prima opŃiune este convenabilă atunci când operaŃiile implică apariŃii şi atribute ale E0, E1 şi E2 într-un mod asemănător. În acest caz, deşi se stochează valori NULL, alegerea asigură mai puŃine accesări în comparaŃie cu celelalte variante în care apariŃiile şi atributele sunt distribuite celorlalte entităŃi.

• A doua opŃiune este posibilă doar dacă generalizarea este totală, altfel apariŃiile lui E0 care nu sunt apariŃii nici ale lui E1 nici ale lui E2 nu vor putea fi reprezentate. Această metodă este utilă când există operaŃii care se referă doar la apariŃiile lui E1 sau ale lui E2 şi astfel se face distincŃia între aceste entităŃi. Nu mai există în principiu atribute ce pot lua valori NULL. Numărul de accesări este mai redus în comparaŃie cu a treia metodă, deoarece nu este necesară accesarea lui E0 pentru a accesa atribute ale E1 şi E2.

• A treia metodă este utilă atunci când generalizarea nu este totală şi operaŃiile se referă fie la apariŃiile şi atributele lui E1(E2) fie ale lui E0 şi astfel se face distincŃia între entităŃile părinte şi copil. Stocarea este mai mică în raport cu prima metodă datorită absenŃei valorilor NULL dar creşte numărul accesărilor pentru a păstra consistenŃa apariŃiilor.

OpŃiunile prezentate nu sunt singurele posibile. O variantă ar fi cea din figura 8. În acest

caz, pe baza consideraŃiilor anterioare, s-a decis înglobarea E0 şi E1 şi lăsarea separată a lui E2. Atributul Atype este adăugat pentru a distinge apariŃiile lui E0 de cele ale E1.

Figura 8. Restructurare posibilă a schemei din figura 7

PartiŃionarea / combinarea entităŃilor şi relaŃiilor Creşterea eficienŃei accesului la datele dintr-o bază de date se poate realiza conform următorului principiu: operaŃiile de acces sunt reduse prin separarea atributelor aceluiaşi concept (entitate sau relaŃie) ce sunt accesate de operaŃii diferite, respectiv prin combinarea atributelor aparŃinând unor concepte diferite ce sunt accesate de aceleaşi operaŃii .

117

PartiŃionarea entităŃilor Un exemplu de partiŃionare a entităŃilor este prezentat în figura 9: entitatea ANGAJAT este substituită prin două entităŃi legate printr-o relaŃie 1:1. Una din entităŃi descrie informaŃii despre statutul unui angajat, iar cealaltă descrie informaŃii personale ale unui angajat. Acest tip de restructurare este utilă doar dacă operaŃiile ce implică frecvent entitatea originală necesită, pentru un angajat, ori numai informaŃii personale, ori numai informaŃii legate de angajare.

Figura 9. Exemplu de partiŃionare a entităŃilor Acest exemplu este un exemplu de partiŃionare verticală, în sensul că un concept este divizat Ńinând cont de atributele sale. Avantajul partiŃionării verticale este generarea unor noi entităŃi cu număr mai mic de atribute decât entitatea originală. Prin urmare, noile entităŃi pot fi translate în structuri fizice din care se poate extrage un volum mare de date printr-un singur acces. Este posibilă de asemenea şi partiŃionarea orizontală, în care divizarea se face în raport cu apariŃiile entităŃii. De exemplu, pot exista operaŃii asociate analiştilor şi operaŃii asociate angajaŃilor din departamentul vânzări. În acest caz se dovedeşte utilă partiŃionarea entităŃii ANGAJAT în două entităŃi distincte, ANALIST şi VÂNZARE, având aceleaşi atribute ca entitatea originală. PartiŃionarea orizontală are ca efect secundar introducerea de duplicate pentru relaŃiile în care participă entitatea originală. Acest fenomen poate avea un impact negativ asupra performanŃelor bazei de date. Ştergerea atributelor multi-valoare Acest tip de restructurare este necesar deoarece modelul relaŃional nu permite reprezentarea directă a atributelor multi-valoare. Un exemplu este prezentat în figura 10.

Figura 10. Exemplu de ştergere a atributelor multi-valoare

118

Entitatea AGENTIE este separată în două entităŃi: una având acelaşi nume şi aceleaşi atribute cu entitatea originală, în afară de atributul multi-valoare (Telefon) şi o entitate TELEFON cu atributul Numar. EntităŃile sunt legate de o relaŃie 1:N. Evident, dacă atributul este opŃional, atunci cardinalitatea minimă pentru entitatea TELEFON trebuie să fie 0. Combinarea entităŃilor Combinarea entităŃilor este operaŃia inversă partiŃionării. Un exemplu de combinare a entităŃilor este ilustrat în figura 11. EntităŃile PERSOANA şi APARTAMENT aflate în relaŃia PROPRIETAR sunt combinate într-o singură entitate ce deŃine atributele ambelor entităŃi. Această restructurare poate fi sugerată de faptul că majoritatea operaŃiilor frecvente asupra entităŃii PERSOANA necesită informaŃii în legătură cu apartamentul deŃinut de persoana respectivă. Prin urmare se doreşte evitarea accesărilor necesare extragerii datelor prin intermediul relaŃiei PROPRIETAR. Dezavantajul acestei restructurări constă în posibilitatea apariŃiei valorilor NULL în noua entitate PERSOANA deoarece cardinalitatea entităŃii originale PERSOANA indică faptul că există persoane ce nu deŃin un apartament.

Figura 11. Exemplu de combinare a entităŃilor OperaŃia de combinare se efectuează în general asupra relaŃiilor 1:1 şi mai rar asupra relaŃiilor 1:N sau N:N. Motivul constă în apariŃia redundanŃelor în atributele non-cheie ale entităŃii având cardinalitatea N. Alte tipuri de partiŃionare şi combinare OperaŃiile de partiŃionare şi combinare pot fi aplicate şi relaŃiilor din următoarele motive:

• pentru a separa apariŃiile unei relaŃii ce sunt întotdeauna accesate separat;

• pentru a uni două (sau mai multe) relaŃii între aceleaşi entităŃi într-o singură relaŃie, atunci când apariŃiile lor sunt accesate întotdeauna împreună.

Un exemplu de partiŃionare a unei relaŃii este prezentat în figura 12, în care se face distincŃie între jucătorii prezenŃi ai unei echipe de fotbal şi cei din trecut.

119

Figura 12. Exemplu de partiŃionare a unei relaŃii

SelecŃia identificatorilor primari În cazul în care există entităŃi ce deŃin mai mulŃi identificatori apare necesitatea stabilirii acelor atribute ce formează identificatorul primar. Identificatorul primar va avea drept corespondent în modelul relaŃional o cheie primară. Criteriile de alegere a unui identificator primar sunt:

• Atributele ce pot deŃine valori NULL nu pot forma un identificator primar. • Este indicat ca un identificator primar să conŃină un atribut sau cât mai puŃine

atribute. Avantajele acestei alegeri ar fi:

- indecşi de dimensiune redusă (un index este o structură auxiliară pentru acces rapid la date);

- spaŃiu redus pentru crearea legăturilor logice între relaŃii; - sunt facilitate operaŃiile de joncŃiune.

• Un identificator intern este de preferat unuia extern, care poate implica mai multe entităŃi şi aceasta din cauză că identificatorii externi sunt translaŃi în chei ce conŃin identificatorii tuturor entităŃilor implicate în identificatorul extern.

• Este de preferat un identificator ce este utilizat de majoritatea operaŃiilor pentru a accesa apariŃiile entităŃii. În acest fel operaŃiile vor fi executate eficient, fiind avantajate de indecşii construiŃi automat de sistemul de gestiune a bazei de date.

Dacă în această etapă nu există nici un identificator care să satisfacă criteriile anterioare,

se va introduce un atribut suplimentar în entitate, atribut ce va fi utilizat exclusiv pentru identificarea apariŃiilor entităŃii. 8.3 Translarea în modelul relaŃional Translarea în modelul relaŃional este a doua etapă a proiectării logice. Plecând de la o schemă E-R se construieşte schema relaŃională echivalentă (echivalentă din punct de vedere al capacităŃii de a reprezenta informaŃii echivalente). łinând cont de faza de restructurare a schemei E-R, translarea va pleca de la o schemă E-R simplificată, ce nu conŃine generalizări sau atribute multi-valoare şi are numai identificatori primari.

120

EntităŃi şi relaŃii N:N Se consideră schema din figura 13.

Figura 13. Schemă E-R cu o relaŃie N:N Translarea în modelul relaŃional se face urmând paşii:

• Fiecare entitate va fi translată într-o relaŃie M-R(a modelului relaŃional) cu acelaşi nume şi având aceleaşi atribute ca şi entitatea; cheia fiecărei relaŃii este dată de identificatorul entităŃii corespunzătoare.

• Fiecare relaŃie E-R dintre entităŃi va avea drept corespondent o relaŃie M-R cu acelaşi nume şi cu atribute atributele relaŃiei E-R şi identificatorii entităŃilor implicate; cheia relaŃiei M-R va conŃine identificatorii entităŃilor.

Dacă entităŃile sau relaŃiile E-R au atribute opŃionale, atributele corespunzătoare din modelul relaŃional pot avea valoarea NULL.

Se obŃine referitor la exemplul din figura 13:

ANGAJAT(Numar , Nume, Salariu) PROIECT( Cod, Nume, Buget) PARTICIPARE(Numar , Cod, DataStart)

În cazul în care se doreşte mărirea clarităŃii schemei obŃinute se pot opera redenumiri de atribute:

PARTICIPARE(Angajat , Proiect, DataStart)

Domeniul atributului Angajat este o mulŃime de numere de înregistrare pentru angajaŃi, iar domeniul pentru atributul Proiect este o mulŃime de coduri de proiecte. De asemenea trebuie impuse constrângeri de referinŃă între aceste atribute şi relaŃiile ANGAJAT, respectiv PROIECT. OperaŃia de redenumire este esenŃială în cazul relaŃiilor recursive. Să considerăm schema din figura 14.

Figura 14. Schemă E-R cu relaŃie recursivă Prin translare se obŃine:

121

PRODUS(Cod, Nume, Pret) COMPUNERE(Componenta, Subcomponenta, Cantitate)

Atributele Componenta şi Subcomponenta au ca domeniu mulŃimea de coduri ale produselor. Trebuie impuse constrângeri de referinŃă între Componenta şi PRODUS şi Subcomponentă şi PRODUS.

Translarea relaŃiilor E-R ce implică mai mult de două entităŃi este similară cazului relaŃiilor E-R între două entităŃi (relaŃii binare). Ca exemplu este dată schema din figura 15. Schema este translată în:

FURNIZOR(IDFurnizor , NumeFurnizor) PRODUS(Cod, Tip) DEPARTAMENT(Nume, Telefon) LIVRARE(Furnizor , Produs, Departament, Cantitate)

Figura 15. Schemă E-R cu o relaŃie ternară Se impun constrângeri de referinŃă între atributele Furnizor, Produs şi Departament ale relaŃiei M-R LIVRARE şi relaŃiile M-R FURNIZOR, PRODUS şi DEPARTAMENT. Pentru acest ultim tip de translare trebuie verificat dacă identificatorii entităŃilor, luaŃi împreună, nu formează o cheie, sau mai degrabă o super-cheie redundantă pentru relaŃia M-R care reprezintă relaŃia E-R din schemă. Aceasta se poate întâmpla, spre exemplu, dacă există un singur furnizor care livrează un produs dat unui departament dat. Este de notat faptul că rămâne validă cardinalitatea deoarece acest furnizor poate livra mai multe produse acestui departament sau altor departamente. În acest caz, cheia relaŃiei M-R LIVRARE trebuie să fie formată doar din atributele Produs şi Departament, deoarece, fiind dat un produs şi un departament, furnizorul este determinat în mod unic. RelaŃii 1:N Se consideră schema din figura 16.

Figura 16. Schemă E-R cu o relaŃie 1:N Utilizând regula de translare a relaŃiilor N:N se obŃine:

122

JUCATOR(Nume, DataNastere, Pozitie) ECHIPA(Nume, Oras, CuloriEchipa) CONTRACT(NumeJucator, DataNastereJucator, Echipa, Salariu)

Cheia relaŃiei CONTRACT este formată numai din identificatorul entităŃii JUCATOR deoarece cardinalităŃile indică faptul că fiecare jucător are contract la o singură echipă. Se observă că relaŃiile JUCATOR şi CONTRACT au aceeaşi cheie. Acest lucru indică posibilitatea combinării celor două relaŃii în una singură, fără să existe pericolul introducerii de redundanŃe. Această combinare este posibilă datorită corespondenŃei 1:1 între instanŃele celor două relaŃii. Astfel, pentru schema din figura 16 se preferă translarea:

JUCATOR(Nume, DataNastere, Pozitie, Echipa, Salariu) ECHIPA(Nume, Oras, CuloriEchipa) Trebuie impusă o constrângere de referinŃă între atributul Echipa şi relaŃia ECHIPA. Să considerăm cazul în care entitatea JUCATOR participă opŃional la relaŃia CONTRACT (pot exista jucători care nu au contract cu nici o echipă). Ambele translări prezentate anterior sunt valide. Ce-a de-a doua prezintă dezavantajul că pot exista valori NULL în relaŃia JUCATOR pentru atributele Echipa şi Salariu.

Regulă: entitatea ce participă la o relaŃie E-R cu cardinalitatea maximă 1 este translată într-o relaŃie M-R ce include identificatorii celorlalte entităŃi implicate în relaŃia E-R şi posibilele atribute ale relaŃiei E-R. Astfel nu mai este nevoie de o relaŃie M-R separată pentru relaŃia din schema E-R.

Ca exemplu, să presupunem că entitatea PRODUS participă în relaŃia din figura 15 cu cardinalităŃile minimă şi maximă 1. Aceasta înseamnă că pentru fiecare produs există un singur furnizor care livrează produsul respectiv şi un singur departament deservit. Schema este translatată sub forma:

FURNIZOR(IDFurnizor , NumeFurnizor) DEPARTAMENT(Nume, Telefon) PRODUS(Cod, Tip, Furnizor, Departament, Cantitate)

Se impun constrângeri de referinŃă între atributul Furnizor al relaŃie PRODUS şi relaŃia FURNIZOR şi între atributul Departament al relaŃiei PRODUS şi relaŃia DEPARTAMENT. EntităŃi cu identificatori externi Să considerăm exemplul din figura 17.

Figura 17. Schemă E-R cu identificator extern

Schema relaŃională este:

STUDENT(NrInregistrare , Universitate, Nume, AnInregistrare) UNIVERSITATE(Nume , Oras, Adresa)

123

Există o constrângere de referinŃă între atributul Universitate şi relaŃia UNIVERSITATE. Este de notat faptul că, prin reprezentarea identificatorului extern, a fost reprezentată şi relaŃia dintre entităŃi. Acest tip de translare este valid indiferent de cardinalitatea cu care participă celelalte entităŃi la relaŃie. RelaŃii 1:1 Pentru relaŃiile 1:1 există mai multe posibilităŃi de translare. Să considerăm relaŃia din figura 18, cu participare obligatorie din partea celor două entităŃi implicate.

Figura 18. Schemă E-R cu o relaŃie 1:1 Există două posibilităŃi valide de translare: SEF(Numar, Nume, Salariu, Departament, DataStart) DEPARTAMENT(Nume, Telefon, Filiala) cu constrângere de referinŃă între atributul Departament al relaŃiei SEF şi relaŃia DEPARTAMENT, sau SEF(Numar, Nume, Salariu) DEPARTAMENT(Nume, Telefon, Filiala, Sef, DataStart) cu constrângere de referinŃă între atributul Sef al relaŃiei DEPARTAMENT şi relaŃia SEF. Pe lângă aceste două soluŃii, există posibilitatea obŃinerii unei singure relaŃii, incluzând toate atributele din schema E-R. Motivul pentru care această soluŃie va fi eliminată este următorul: dacă după faza de restructurare (care precede faza de translare) schema E-R conŃine două entităŃi legate printr-o relaŃie 1.1, este de dorit ca în faza de translare cele două concepte să rămână separate. Să considerăm cazul unei relaŃii 1:1 cu participarea opŃională a uneia dintre entităŃi, ca în schema din figura 19.

Figura 19. Schema E-R cu o relaŃie 1:1 cu participare opŃională

SoluŃia adoptată este: ANGAJAT(Numar , Nume, Salariu)

124

DEPARTAMENT(Nume, Telefon, Filiala, Sef, DataStart) cu constrângere de referinŃă între atributul Sef al relaŃiei DEPARTAMENT şi relaŃia ANGAJAT. Această opŃiune este preferabilă uneia în care relaŃia E-R este reprezentată în relaŃia M-R ANGAJAT prin numele departamentului manageriat, deoarece, pentru acest atribut, ar putea exista valori NULL. Să considerăm cazul în care ambele entităŃi au participări opŃionale. Să presupunem că în schema din figura 19 pot exista departamente fără şef (cardinalitatea minimă a entităŃii DEPARTAMENT este 0). În acest caz se obŃine prin translare: ANGAJAT(Numar , Nume, Salariu) DEPARTAMENT(Nume, Telefon, Filiala) MANAGEMENT(Sef , Departament, DataStart) Se observă că se poate lua drept cheie a relaŃiei MANAGEMENT atributul Departament. Se impun constrângeri de referinŃă între atributele Sef şi Departament ale relaŃiei MANAGEMENT şi relaŃiile ANGAJAT şi DEPARTAMENT. Această soluŃie are avantajul că atributele ce implementează relaŃia din schema E-R nu pot avea valori NULL. Dezavantajul soluŃiei constă în relaŃia nouă introdusă. Această soluŃie este recomandată în cazul în care numărul de apariŃii ale relaŃiei este mic în comparaŃie cu numărul de apariŃii ale entităŃilor implicate în relaŃie.

Exemplu de translare a unei scheme complexe cu obŃinerea unui număr minim de

relaŃii Să considerăm schema E-R din figura 20.

Figura 20. Schemă entitate relaŃie Într-o primă fază se translează fiecare entitate într-o relaŃie din modelul relaŃional. Translarea entităŃilor cu identificatori interni este imediată:

125

E3(A31, A32) E4(A41, A42) E5(A51, A52) E6(A61, A62, A63)

Al doilea pas constă în translarea entităŃilor cu identificatori externi. Se obŃin următoarele relaŃii M-R:

E1(A11, A51, A12) E2(A21, A11, A51, A22)

Este de notat modul în care E2 preia atributul A11 din E1 şi tranzitiv, atributul A51 din E5 care, împreună, identifică E1. Trebuie impuse constrângeri de referinŃă adecvate (de exemplu între atributul A51 din E1 şi relaŃia E5). În al treilea pas se translează relaŃiile din schema E-R. RelaŃiile R1 şi R6 au fost deja translate datorită identificatorilor externi ai entităŃilor E1, respectiv E2. Presupunem că vrem să obŃinem un număr minim de relaŃii M-R în schema finală. Astfel:

• Pentru a transla R3, vom introduce, cu redenumiri adecvate, atributele care identifică E6 printre cele ale lui E5, precum şi atributul AR3 al lui R3. Astfel introducem A61R3, A62R3 şi AR3 în E5.

• Similar, pentru R4, introducem A61R4 şi A62R4 în E5.

• Similar, pentru R5, introducem A61R5, A62R5 şi AR5 în E5.

Se obŃine

E5(A51, A52, A61R3, A62R3, AR3, A61R4, A62R4, A61R5, A62R5, AR5)

Cheia relaŃiei M-R E5 este formată doar din A51 deoarece entitatea E5 participă cu cardinalitatea maximă 1 la relaŃiile E-R R3, R4 şi R5.

Redenumirea atributelor entităŃii E6 este obligatorie pentru a face distincŃia între utilizări ale aceloraşi atribute în reprezentarea unor relaŃii diferite (spre exemplu A61R3 care reprezintă R3 şi A61R4 care reprezintă R4).

În final se translează singura relaŃie N:N, şi anume R2:

R2(A21, A11, A51, A31, A41, AR21, AR22)

Schema relaŃională care se obŃine în final este: E1(A11, A51, A12)

E2(A21, A11, A51, A22) E3(A31, A32)

E4(A41, A42) E5(A51, A52, A61R3, A62R3, AR3, A61R4, A62R4, A61R5, A62R5, AR5) E6(A61, A62, A63) R2(A21, A11, A51, A31, A41, AR21, AR22) Tabel rezumat pentru translarea din modelul entitate-relaŃie în modelul relaŃional În figura 21 sunt prezentate, pe scurt, posibilităŃi de translare din modelul E-R în modelul

relaŃional Simbolurile X şi Y indică o cardinalitate permisă. Asteriscul indică posibilitatea prezenŃei

valorilor NULL iar linia de subliniere întreruptă marchează o cheie alternativă celei marcate prin linie continuă.

126

Tip Schema iniŃială Translare posibilă

RelaŃie binară N:N

RelaŃie ternară N:N

RelaŃie 1:N cu participare obligatorie

RelaŃie 1:N cu participare opŃională

sau

RelaŃie cu identificatori externi

RelaŃie 1:1 cu participare obligatorie pentru ambele entităŃi

sau

RelaŃie 1:1 cu participare opŃională pentru o entitate

127

RelaŃie 1:1 cu participare opŃională pentru ambele entităŃi

sau

sau

Figura 21. Translări din modelul E-R în modelul relaŃional

Documentarea schemelor logice Rezultatul proiectării logice nu constă doar în schema bazei de date ci presupune şi o documentaŃie asociată. O parte a documentaŃiei obŃinute în faza proiectării conceptuale poate fi „moştenită” de schema logică. În particular, dacă numele conceptelor din schema E-R sunt refolosite pentru construirea schemei relaŃionale, regulile de operare definite anterior pot fi folosite pentru documentarea acesteia din urmă. DocumentaŃia schemei relaŃionale trebuie completată cu constrângerile de referinŃă.

Pentru aceasta se adoptă o notaŃie grafică simplă atât pentru relaŃii cât şi pentru constrângerile dintre acestea. Un exemplu referitor la schema din figura 13 este dat în figura 22. Cheile relaŃiilor se scriu îngroşat, săgeŃile descriu constrângerile de referinŃă iar asteriscul asociat indică posibilitatea ca atributul respectiv să aibă valori NULL.

Figura 22. Reprezentarea grafică a translării schemei din figura 13.

În acest mod se pot păstra informaŃii despre relaŃiile din modelul E-R, permiŃându-se reconstrucŃia informaŃiilor reprezentate prin relaŃia originală. În exemplul din figura 22, proiectele în care angajaŃii sunt implicaŃi pot fi regăsite prin intermediul relaŃiei PARTICIPARE.

În figura 23 este prezentat un alt exemplu referitor la schema din figura 16.

Figura 23. Reprezentarea grafică a translării schemei din figura 16.

128

Este de remarcat faptul că această metodă permite de asemenea reprezentarea explicită a relaŃiilor din schema iniŃială E-R cărora, în schema relaŃională echivalentă, nu le corespunde nici o relaŃie (spre exemplu relaŃia CONTRACT din modelul E-R din figura 16).

În figura 24 este reprezentată schema relaŃională corespunzătoare exemplului anterior de translare a unei scheme complexe cu obŃinerea unui număr minim de relaŃii (figura 20). În acest mod se pot identifica uşor legăturile logice dintre diferitele relaŃii care apar.

Figura 24. Reprezentarea grafică a schemei relaŃionale

corespunzătoare schemei E-R din figura 20

8.4 Exemplu de proiectare logică Să considerăm exemplul din capitolul anterior (proiectarea conceptuală) referitor la o companie de instruire. Schema conceptuală este prezentată în figura 25.

Figura 25. Schema E-R pentru o companie de instruire

129

OperaŃiile care au loc asupra datelor descrise prin schema anterioară sunt:

• OperaŃia 1: se inserează un nou instruit incluzând datele despre el;

• OperaŃia 2: se atribuie un instruit unei ediŃii a unui curs;

• OperaŃia 3: se inserează un nou instructor, incluzând toate datele şi cursurile pe care acesta este calificat să le predea;

• OperaŃia 4: se atribuie un instructor calificat pentru o ediŃie a unui curs;

• OperaŃia 5. se afişează toate informaŃiile despre o ediŃie anterioară a cursului: titlu, orar, număr de instruiŃi;

• OperaŃia 6: afişează toate cursurile disponibile, cu informaŃii despre instructorii calificaŃi să le Ńină;

• OperaŃia 7: pentru fiecare instructor, se găsesc instruiŃii pentru toate cursurile pe care le Ńine sau le-a Ńinut;

• OperaŃia 8: se realizează o analiză statistică a tuturor instruiŃilor cu toate informaŃiile despre ei, despre ediŃiile cursurilor pe care le-au urmat şi notele obŃinute.

Faza de restructurare Încărcarea bazei de date este prezentată în figura 26.

Tabelul volumelor Concept Tip Volum Clasă E 8000 Tabelul opera Ńiilor EdiŃieCurs E 1000 Opera Ńie Tip Frecven Ńă Curs E 200 Op 1 I 40/zi Instructor E 300 Op 2 I 50/zi Temporar E 250 Op 3 I 2/zi Permanent E 50 Op 4 I 15/zi Instruit E 5000 Op 5 I 10/zi Angajat E 4000 Op 6 I 20/zi LiberProfesionist E 1000 Op 7 I 5/zi Angajator E 8000 Op 8 B 10/lună PrezenŃăAnterioară R 10000 PrezenŃăCurentă R 500 CompoziŃie R 8000 Tip R 1000 PredareAnterioară R 900 PredareCurentă R 100 Calificare R 500 AngajareActuală R 4000 AngajareAnterioară R 10000

Figura 26. Tabelele volumelor şi operaŃiilor pentru schema din figura 25

Analiza redundanŃelor. În schemă există doar o dată redundantă şi anume atributul NrParticipanŃi în EDIłIECURS, care poate fi derivat din relaŃiile PREZENłĂCURENTĂ şi PREZENłĂANTERIOARĂ. Cererea de stocare este de 4 x 1000 = 4000 octeŃi, presupunând că sunt necesari patru octeŃi pentru ca fiecare apariŃie a entităŃii EDIłIECURS să stocheze numărul de participanŃi. OperaŃiile care implică această informaŃie sunt 2, 5 şi 8. Ultima operaŃie poate fi lăsată deoparte deoarece este o operaŃie cu o frecvenŃă mică, care se execută în modul batch. Vom evalua costul operaŃiilor 2 şi 5 în prezenŃa şi în absenŃa redundanŃei. Se poate deduce din tabelul volumelor că fiecare ediŃie a unui curs are, în medie, 8 clase şi 10 participanŃi. Pornind de la aceste date se poate obŃine uşor tabelul accesărilor prezentat în figura 27.

130

Accesări în prezenŃa redundanŃei Accesări în absenŃa redundanŃei

OperaŃia 2 OperaŃia 2 Concept Tip Accesări Tip Concept Tip Accesări Tip

Instruit E 1 R Instruit E 1 R PrezenŃăCurentă R 1 W PrezenŃăCurentă R 1 W EdiŃieCurs E 1 R EdiŃieCurs E 1 W OperaŃia 5

OperaŃia 5 Concept Tip Accesări Tip Concept Tip Accesări Tip EdiŃieCurs E 1 R

EdiŃieCurs E 1 R Tip R 1 R Tip R 1 R Curs E 1 R Curs E 1 R CompoziŃie R 8 R CompoziŃie R 8 R Clasă E 8 R Clasă E 8 R PrezenŃăAnterioară R 10 R

Figura 27. Tabelele accesărilor pentru schema din figura 25 Se obŃine:

• În prezenŃa redundanŃei: pentru operaŃia 2 avem 2 x 50 = 100 accesări în citire / zi şi tot atâtea accesări în scriere / zi, în timp ce, pentru operaŃia 5, avem 19 x 10 = 190 accesări în citire / zi dintr-un total de 490 accesări / zi (s-a considerat că accesul în scriere costă de două ori mai mult decât accesul în citire);

• În absenŃa redundanŃei: pentru operaŃia 2 avem 50 de accesări în citire / zi şi tot atâtea în scriere, în timp ce pentru operaŃia 5 avem 29 x 10 = 290 accesări în citire / zi, dintr-un total de 440 accesări / zi (s-a considerat că accesul în scriere costă de două ori mai mult decât accesul în citire).

Se observă că în prezenŃa redundanŃei, apar dezavantaje atât în ceea ce priveşte cererea de

stocare cât şi timpul de acces. Din acest motiv se va renunŃa la atributul NrParticipanŃi al entităŃii EDIłIECURS.

Eliminarea generalizărilor . În schemă există două generalizări: una referitoare la

instructori şi una referitoare la instruiŃi. În ceea ce priveşte instructorii, să remarcăm că operaŃiile relevante (3,4,6 şi 7) nu fac

distincŃia între instructorii temporari şi cei angajaŃi permanent de companie. Mai mult, entităŃile corespunzătoare nu au atribute specifice acestora. Din aceste motive, vom şterge entităŃile copil ale generalizării şi vom adăuga atributul Tip entităŃii INSTRUCTOR. Acest atribut are domeniul format din simbolurile T (pentru temporar) şi P (pentru permanent).

În ceea ce priveşte instruiŃii, să observăm că şi în acest caz, operaŃiile relevante (1, 2 şi 8) nu fac o diferenŃa majoră între diversele tipuri de apariŃii. EntităŃile copil au ambele atribute specifice. Se pot lăsa entităŃile ANGAJAT şi LIBERPROFESIONIST adăugând două relaŃii 1:1 între aceste entităŃi şi entitatea INSTRUIT. În acest fel se evită atribute cu posibile valori NULL pentru entitatea părinte şi se poate reduce dimensiunea relaŃiilor.

Rezultatul eliminării generalizărilor se poate vedea în figura 28. PartiŃionarea / combinarea conceptelor. Din analiza datelor şi operaŃiilor se pot

identifica mai multe restructurări potenŃiale. Prima se referă la entitatea EDIłIECURS. OperaŃia 5 şi relaŃiile PREDAREANTERIOARĂ şi PREZENłĂANTERIOARĂ se referă doar la ediŃiile anterioare ale cursului. Astfel, pentru a face operaŃia anterioară mai eficientă, se poate descompune entitatea orizontal pentru a distinge între ediŃiile curente şi cele anterioare ale cursului.

131

Dezavantajul acestei alegeri este acela că relaŃiile COMPOZIłIE şi TIP se vor duplica. Pe de altă parte, operaŃiile 7 şi 8 nu fac o distincŃie majoră între ediŃiile curente şi anterioare ale unui curs şi ar putea fi mai costisitoare dacă necesită vizitarea a două entităŃi distincte. Din aceste motive, nu vom partiŃiona entitatea EDIłIECURS.

Două restructurări posibile ar putea avea loc prin combinarea relaŃiilor PREDAREANTERIOARĂ ŞI PREDARECURENTĂ şi respectiv a relaŃiilor PREZENłĂANTERIOARĂ ŞI PREZENłĂCURENTĂ. În ambele cazuri avem de-a face cu două concepte similare între care câteva operaŃii (7 şi 8) nu fac diferenŃa. Combinarea acestor relaŃii mai poate duce la un avantaj şi anume că nu mai este necesar transferul apariŃiilor de la o relaŃie la alta la sfârşitul unei ediŃii a unui curs. Un dezavantaj ar fi acela că atributul Notă, care nu apare la o ediŃie curentă a cursului va avea valori NULL. Din tabelul de volume se observă că numărul de apariŃii ale relaŃiei PREZENłĂCURENTĂ este de 500. Presupunând că avem nevoie de 4 octeŃi pentru a stoca notele, cererea de stocare va fi de doar aproximativ 2 KocteŃi. Decidem deci să combinăm cele două perechi de relaŃii după cum se poate vedea în figura 28. Trebuie adăugată o constrângere care nu poate fi reprezentată direct pe schemă şi anume aceea ca un instructor să nu poată preda mai mult de o ediŃie a unui curs în aceeaşi perioadă. Similar, un participant nu poate fi prezent la mai mult de o ediŃie a unui curs la un anumit moment.

În final, este necesară eliminarea atributului multi-valoare Telefon din entitatea INSTRUCTOR. Pentru aceasta se introduce o nouă entitate TELEFON legată printr-o relaŃie 1:N de entitatea INSTRUCTOR (din care se elimină atributul respectiv).

SelecŃia identificatorilor primari. Doar entitatea INSTRUIT are doi identificatori: CNP-ul şi codul intern. Este preferabil să se aleagă al doilea identificator deoarece CNP-ul poate necesita câŃiva octeŃi pentru stocare în timp ce codul, care trebuie să distingă între 5000 de apariŃii (vezi tabelul volumelor) nu necesită mai mult de doi octeŃi.

Entitatea EDIłIECURS este identificată prin atributul DatăStart şi prin entitatea CURS. Într-o reprezentare relaŃională, acest identificator trebuie utilizat pentru a implementa două relaŃii (PREZENłĂ şi PREDARE). Se poate observa că fiecare curs are un cod şi că numărul mediu de ediŃii pentru un curs este 5. Aceasta înseamnă că este convenabil să adăugăm un atribut Cod cu domeniul small integer pentru a identifica o ediŃie a unui curs, în locul identificatorului extern.

Schema rezultată în urma restructurării poate fi observată în figura 28.

Figura 28. Schema E-R din figura 25 după faza de restructurare

132

Translarea în modelul relaŃional Folosind tehnicile de translare prezentate anterior, schema din figura 28 poate fi translată în următoarea schemă relaŃională: EDIłIECURS(Cod, DatăStart, DatăSfârşit, Curs, Instructor) CLASĂ(Timp, Sală, Dată, EdiŃie) INSTRUCTOR(CNP, Nume, Vârstă, OraşDeNaştere, Tip) TELEFON(Număr, Instructor)

CURS(Cod, Nume) CALIFICARE(Curs, Instructor) INSTRUIT(Cod, CNP, Nume, Vârstă, OraşDeNaştere, Sex) PREZENłĂ(Instruit, EdiŃie, Note*) ANGAJATOR(Nume, Adresă, Telefon) ANGAJAREANTERIOARĂ(Instruit, Angajator, DatăStart, DatăSfârşit) LIBERPROFESIONIST(Instruit, Expertiză, TitluProfesional*) ANGAJAT(Instruit, Nivel, PoziŃie, Angajator, DatăStart)

Schema logică va fi completată cu o documentaŃie care va descrie, printre altele, toate constrângerile de referinŃă care există între relaŃii. Aceasta se poate face utilizând notaŃiile grafice prezentate anterior.

Bibliografie 1. Database Systems - concepts, languages & architectures, P. Atzeni, S. Ceri, S. Paraboschi, R. Torlone, Ed. McGraw - Hill, 2000 2. Database Systems - the complete book, H. Garcia-Molina, J.D. Ullman, J. Widom, Ed. Prentice Hall, 2002 3. Baze de date - proiectare, implementare, gestionare, T. Connolly, C. Begg, A. Strachan, Ed. Teora, 2001 4. Baze de date , O. Bâscă, Ed. All, 1997 5. PL/SQL , T. Luers, T. Atwood, J. Gennick, Ed. Teora, 2001 6. Sisteme de gestiune a bazelor de date – aplicaŃii Oracle, I. Lungu, M. Velicanu, C. Bodea, C. IoniŃă, Ed. All, 1998

Bibliografie 1. Database Systems - concepts, languages & architectures, P. Atzeni, S. Ceri, S. Paraboschi, R. Torlone, Ed. McGraw - Hill, 2000 2. Database Systems - the complete book, H. Garcia-Molina, J.D. Ullman, J. Widom, Ed. Prentice Hall, 2002 3. Baze de date - proiectare, implementare, gestionare, T. Connolly, C. Begg, A. Strachan, Ed. Teora, 2001 4. Baze de date , O. Bâscă, Ed. All, 1997 5. PL/SQL , T. Luers, T. Atwood, J. Gennick, Ed. Teora, 2001 6. Sisteme de gestiune a bazelor de date – aplicaŃii Oracle, I. Lungu, M. Velicanu, C. Bodea, C. IoniŃă, Ed. All, 1998