Conf. univ. dr. Nicoleta IACOB [612856]
1
UNIVERSITATEA SPIRU HARET
FACULTATEA DE INGINERIE, INFORMATICĂ ȘI GEOGRAFIE
Baze de date
Conf. univ. dr. Nicoleta IACOB
Cuprins
Baze de date ………………………………………………………………………………………………………………………….. 1
1. Noțiuni introductive în teoria bazelor de date …………………………………………………………………………. 5
1.1. Noțiunile de bază de date, sistem de gestiune a bazei de date …………………………………………. 5
1.2. Noțiunile de entitate, relație, atribut ……………………………………………………………………………. 5
1.3. Construirea de diagrame entitate-relație ………………………………………………………………………. 7
1.4. Tipuri de relații între entități ………………………………………………………………………………………. 8
2. Baze de date relaționale ……………………………………………………………………………………………………….. 8
2.1. Noțiunile de bază de date relațională, sistem de gestiune a bazelor de date relaționale …………… 8
2.2. Regulile lui Codd ………………………………………………………………………………………………………….. 9
2.3. Componentele bazelor de date relaționale: ……………………………………………………………………… 10
1) Structura relațională a datelor …………………………………………………………………………………… 11
2) Operatorii modelului relațional ………………………………………………………………………………… 11
3) Restricții de integritate ale modelului relațional …………………………………………………………. 13
2.4. Tipuri de constrângeri de integritate ………………………………………………………………………………. 14
3. Proiectarea bazelor de date relaționale …………………………………………………………………………………. 14
3.1. Formele normale: FN1; FN2; FN3 ………………………………………………………………………………… 15
4. Limbajul SQL (Structured Query Language) ………………………………………………………………………… 21
4.1. Structura lexicală a limbajului SQL ………………………………………………………………………….. 21
4.2. Operatori SQL ……………………………………………………………………………………………………….. 21
4.3. Funcții definite în SQL ……………………………………………………………………………………………. 22
4.4. Tipuri de date …………………………………………………………………………………………………………. 23
2
4.5. Categorii de instrucțiuni SQL …………………………………………………………………………………… 24
5. Limbajul de definire a datelor (LDD) ………………………………………………………………………………….. 24
5.1. Comenzi (CREATE, ALTER, DROP) …………………………………………………………………………… 25
6. Limbajul de manipulare a datelor (LMD) …………………………………………………………………………….. 29
6.1. Interogarea datelor (Comanda SELECT) ……………………………………………………………………….. 30
6.2. Adăugarea de noi tupluri (Comanda INSERT) ……………………………………………………………….. 39
6.3. Modificarea tuplurilor din tabele (Comanda UPDATE) …………………………………………………… 42
6.4. Ștergerea tuplurilor din tabele (Comanda DELETE) ……………………………………………………….. 44
7. Limbajul de control al datelor (LCD) …………………………………………………………………………………… 45
7.1. Asigurarea confidențialității și securității datelor …………………………………………………………….. 46
7.2. Reluarea unor acțiuni în cazul unei defecțiuni ………………………………………………………………… 46
7.3. Garantarea coerenței datelor în cazul prelucrării concurente …………………………………………….. 47
8. Exerciții de fixare a noțiunilor …………………………………………………………………………………………….. 49
Procedura de instalare Oracle XE (Oracle Database 11g Express Edition) și Apex (Oracle
Application Express) pe Windows ………………………………………………………………………………………. 49
Crearea unui user ………………………………………………………………………………………………………………. 51
APLICAȚII REZOLVATE ………………………………………………………………………………………………… 52
DDL (Data Definition Language) ……………………………………………………………………………………….. 52
Crearea tabelelor ………………………………………………………………………………………………………………. 52
Schema curenta ………………………………………………………………………………………………………………… 54
Adaugarea constrangerilor …………………………………………………………………………………………………. 54
DML (Data Manipulation Language): INSERT, UPDATE, DELETE ……………………………………… 55
Introducerea valorilor in tabele …………………………………………………………………………………………… 55
Inserare de valori folosind variabile de substitutie – ampersand unic (&) …………………………………. 55
Crearea unui script pentru manipularea datelor ……………………………………………………………………… 56
Copierea inregistrarilor dintr-un alt tabel ……………………………………………………………………………… 56
Modificarea tabelelor ………………………………………………………………………………………………………… 57
Tipuri de constrangeri ……………………………………………………………………………………………………….. 57
Instructiunnea SELECT …………………………………………………………………………………………………….. 57
3
Tipuri de operatori: ……………………………………………………………………………………………………………. 58
Definirea alias-urilor pentru coloane ……………………………………………………………………………………. 58
Functia generala nvl ………………………………………………………………………………………………………….. 58
Operatorul de concatenare (||) ……………………………………………………………………………………………… 58
Randuri duplicate ……………………………………………………………………………………………………………… 58
Eliminarea randurilor duplicate …………………………………………………………………………………………… 58
Ordonarea inregsitrarilor ……………………………………………………………………………………………………. 58
Sortarea dupa aliasul numelui coloanei ………………………………………………………………………………… 59
Sortarea dupa pozitia coloanei ……………………………………………………………………………………………. 59
Clauza WHERE ………………………………………………………………………………………………………………… 59
Negarea comparatiei ………………………………………………………………………………………………………….. 60
Conditii multiple ………………………………………………………………………………………………………………. 60
Functii de tip caracter ………………………………………………………………………………………………………… 61
Functii de tip numeric: ………………………………………………………………………………………………………. 62
Functii de tip data calendaristica …………………………………………………………………………………………. 62
Functii de conversie …………………………………………………………………………………………………………… 63
Functii care accepta orice tip de tip de date ………………………………………………………………………….. 63
Functii de grup …………………………………………………………………………………………………………………. 64
Instructiunea CASE …………………………………………………………………………………………………………… 64
Gruparea datelor ……………………………………………………………………………………………………………….. 65
Interogari din mai multe tabele …………………………………………………………………………………………… 65
Selectii din mai multe tabele folosind alias-uri la tabele …………………………………………………………. 65
Subinterogari ……………………………………………………………………………………………………………………. 66
Interogarea datelor din mai multe relatii folosind tipuri de asocieri …………………………………………. 68
Modificarea tuplurilor din tabele (comanda UPDATE) ………………………………………………………….. 69
Comanda DELETE ……………………………………………………………………………………………………………. 70
Stergerea inregistrarilor folosind valori dintr-un alt tabel ……………………………………………………….. 70
Procesarea tranzactiilor ……………………………………………………………………………………………………… 71
Folosirea atributului %type ………………………………………………………………………………………………… 72
4
Variabile de legatura …………………………………………………………………………………………………………. 72
Crearea si stergerea vederilor/vizualizarilor ………………………………………………………………………….. 73
Definirea generatorului de numere de secventa …………………………………………………………………….. 74
Index ……………………………………………………………………………………………………………………………….. 74
Sinonim …………………………………………………………………………………………………………………………… 75
Operatii pe tabele structurate arborescent …………………………………………………………………………….. 75
APLICATII PROPUSE ……………………………………………………………………………………………………… 75
Bibliografie ………………………………………………………………………………………………………………………….. 76
5
1. Noțiuni introductive în teoria bazelor de date
1.1. Noțiunile de bază de date, sistem de gestiune a bazei de date
O bază de date (BD) :
• reprezintă un ansamblu structurat de fișiere care grupează datele prelucrate în aplicații
informatice ale unei persoane, grup de persoane, instituții etc.;
• este definită ca o colecție de date aflate în interdependență, împreună cu descrierea datelor și a
relațiilor dintre ele.
Sistemul de gestiune a bazei de date (SGBD) este:
• un sistem complex de programe care asigură interfața între o bază de date și utilizatorii
acesteia;
• software-ul bazei de date care asigură:
o definirea structurii bazei de date;
o încărcarea datelor în baza de date;
o accesul la baza de date (interogare, actualizare);
o întreținerea bazei de date (refolosirea spațiilor goale, refacerea bazei de date în cazul
unor incidente);
o reorganizarea bazei de date (restructurarea și modificarea strategiei de acces);
o securitatea datelor.
1.2. Noțiunile de entitate, relație, atribut
Cele trei concepte de bază utilizate în organizarea bazei de date sunt:
• entitatea;
• atributul;
• valoarea.
Prin entitate se înțelege un obiect concret sau abstract reprezentat prin proprietățile sale. O proprietate
a unui obiect poate fi exprimată prin perechea (ATRIBUT, VALOARE).
Entitatea poate fi persoană, loc, concept, activitate etc. Prin urmare, ea poate fi un obiect cu existență
fizică, reală sau poate fi un obiect cu existență conceptuală, abstractă.
Exemplu: În exemplul “Produsul X are culoarea Y”, atributul este „culoarea”, iar valoarea este
reprezentată litera„Y”.
O entitate poate fi:
• dependentă (slabă), existența sa depinzând de altă entitate;
• independentă (tare), caz în care ea nu depinde de existența altei entități.
Observații privind entitățile:
• entitățile devin tabele în modelele relaționale;
• în general, entitățile se scriu cu litere mari;
• entitățile sunt substantive , dar nu orice substantiv este o entitate. Trebuie ignorate
substantivele nerelevante;
• cheia primară identifică unic o entitate și face distincție între valori diferite ale entității.
Aceasta trebuie să fie unică și cunoscută la orice moment. Cheia primară trebuie să fie
controlată de administratorul bazei, să nu conțină informații descriptive, să fie simplă, fără
6
ambiguități, să fie stabilă, să fie familiară utilizatorului astfel încât acesta să o poată folosi
cu ușurință;
• pentru fiecare entitate este obligatoriu să se dea o descriere detaliată;
• nu pot exista, în aceeași diagramă, două entități cu același nume, sau o aceeași entitate cu
nume diferite.
Relația este o comunicare între două sau mai multe entități. Gradul unei relații este dat de numărul de
entități participante într-o relație (de exemplu, relație binară, ternară, cvadruplă, n-ară).
Existența unei relații este subordonată existenței entităților pe care le leagă.
O relație în care aceeași entitate participă mai mult decât o dată în diferite roluri definește o relație
recursivă. Uneori, aceste relații sunt numite unare.
Observații privind relațiile:
• în modelul relațional, relațiile devin tabele speciale sau coloane speciale care referă chei
primare;
• relațiile sunt verbe, dar nu orice verb este o relație;
• pentru fiecare relație este important să se dea o descriere detaliată;
• într-o relație, tuplurile trebuie să fie distincte;
• în aceeași diagramă pot exista relații diferite cu același nume. În acest caz, ele sunt diferențiate
de către entitățile care sunt asociate prin relația respectivă;
• cardinalul relației este numărul tuplurilor dintr-o relație. Pentru fiecare relație trebuie stabilită
cardinalitatea (maximă și minimă) relației.
Asupra entităților participante într-o relație pot fi impuse constrângeri care trebuie să reflecte
restricțiile care există în lumea reală asupra relațiilor. O clasă de constrângeri, numite constrângeri de
cardinalitate, este definită de numărul de înregistrări posibile pentru fiecare entitate participantă (raport
de cardinalitate). Cel mai întâlnit tip de relații este cel binar, iar în acest caz rapoartele de cardinalitate
sunt, în general, one-to-one (1:1), one-to-many (1:M) sau many-to-many (N:M).
• 1:1 – legătura de tip “una-la-una” (one-to-one) – este asocierea prin care unui element
(entitate) din mulțimea E1 îi corespunde un singur element din mulțimea E2 și reciproc;
• 1:M – legătura de tip “una-la-multe” (one-to-many) – este asocierea prin care unui element
din mulțimea E1 îi corespund unul sau mai multe elemente din mulțimea E2, dar unui
element din E2 îi corespunde un singur element în mulțimea E1;
• N:M – legătura de tip “multe-la-multe” (many-to-many) – este asocierea prin care unui
element din mulțimea E1 îi corespund unul sau mai multe elemente din mulțimea E2 și
reciproc.
O relație se poate memora printr-un tabel de forma:
R A1 … Aj .. Am
r1 a11 … a1j … a1m
… … … … … …
ri ai1 … aij … aim
… … … … … …
rn an1 … anj … anm
7
unde liniile din acest tabel formează elementele relației, sau tupluri, înregistrări, care în general sunt
distincte, coloanele A1, A2, …, A m formează o mulțime de atribute, iar a 11, …, a nm sunt valoari pentru
fiecare din atributele A 1, Am.
Atributul este o proprietate descriptivă a unei entități sau a unei relații (de exemplu, denumire,
unitate_de_masura a unui produs, sunt atribute ale entității PRODUS).
Atributele pot fi:
• simple (de exemplu, prețul unitar al unui produs);
• compuse (de exemplu, denumirea produsului);
• cu valori multiple (de exemplu, limbile în care este tradus un produs);
• derivate (de exemplu, numărul de zile rămase pănă la expirare se obține din data expirării).
Atributele sunt utile atunci când într-o relație un domeniu apare de mai multe ori. Prin numele dat
fiecărei coloane (atribut), se diferențiază coloanele care conțin valori ale aceluiași domeniu, eliminând
dependența față de ordine.
Observații privind atributele:
• atributul reprezintă coloana unei tabele de date, caracterizată printr-un nume;
• trebuie făcută distincție între atribut, care uzual devine coloană în modelele relaționale și
valoarea acestuia, care devine valoare în coloane;
• atributele sunt substantive , dar nu orice substantiv este atribut;
• fiecărui atribut trebuie să i se dea o descriere completă în specificațiile modelului (exemple,
contraexemple, caracteristici);
• pentru fiecare atribut trebuie specificat numele, tipul fizic (integer, float, char etc.), valori
posibile, valori implicite, reguli de validare, constrângeri, tipuri compuse;
• atributele pot caracteriza o clasă de entități, nu doar o entitate.
1.3. Construirea de diagrame entitate-relație
Prin tehnica entiate-relație (denumită și entitate-asociere) se construiește o diagramă entiate-relație
(notată E-R) prin parcurgerea următorilor pași:
a) identificarea entităților (componentelor) din sistemul proiectului;
b) identificarea asocierilor (relațiilor) dintre entități și calificarea lor;
c) identificarea atributelor corespunzătoare entităților;
d) stabilirea atributelor de identificare a entităților.
a) Identificarea entităților. Prin convenție, entitățile sunt substantive, se scriu cu litere mari și se
reprezintă prin dreptunghiuri . Într-o diagramă nu pot exista două entități cu același nume, sau o
aceeași entitate cu nume diferite.
b) Identificarea asocierilor dintre entități și calificarea lor. Între majoritatea componentelor (adică a
entităților) unui sistem economic se stabilesc legături (asocieri).
Observații privind diagramele entitate-relație:
• legăturile se reprezintă prin arce neorientate între entități;
• fiecărei legături i se acordă un nume plasat la mijlocul arcului și simbolizat printr-un romb
(semnificația legăturii);
• numerele simbolizate deasupra arcelor se numesc cardinalități și reprezintă tipul legăturii;
• cardinalitatea asocierilor exprimă numărul minim și maxim de realizări ale unei entități cu
cealaltă entitate asociată. Maximele unei cardinalități sunt cunoscute și sub denumirea de
8
grad de asociere , iar minimele unei cardinalități reprezintă obligativitatea participării
entităților la asociere.
1.4. Tipuri de relații între entități
Asocierea dintre entități se face în funcție de:
• cardinalitatea asocierii. În funcție de maxima cardinalității (gradul de asociere), se cunosc trei
tipuri de asocieri:
o una-la-una (1-1 sau one to one);
o una-la-multe (1-M sau one to many);
o multe-la-multe (N-M sau many to many).
• numărul de entități distincte care participă la asociere. Se cunosc trei tipuri de asocieri:
o binare (între două entități distincte);
o recursive (asocieri ale entităților cu ele însele);
o complexe (între mai mult de două entități distincte).
c) Identificarea atributelor entităților și a asocierilor dintre entități. Atributele unei entități reprezintă
proprietăți ale acestora. Atributele sunt substantive , iar pentru fiecare atribut i se va preciza tipul fizic
(integer, float, char, string etc.)
d) Stabilirea atributelor de identificare a entităților. Un atribut de identificare (numit cheie primară),
reprezintă un atribut care se caracterizează prin unicitatea valorii sale pentru fiecare instanță a entității.
În cadrul diagramei entitate-asociere, un atribut de identificare se marchează prin subliniere sau prin
marcarea cu simbolul # plasat la sfârșitul numelui acestuia.
Pentru ca un atribut să fie atribut de identificare, acesta trebuie să satisfacă unele cerințe:
• oferă o identificare unică în cadrul entității;
• este ușor de utilizat;
• este scurt (de cele mai multe ori, atributul de identificare apare și în alte entități, drept cheie
externă).
Pentru o entitate pot exista mai multe atribute de identificare, numite atribute (chei) candidate . Dacă
există mai mulți candidați cheie, se va selecta unul, preferându-se acela cu valori mai scurte și mai
puțin volatile.
2. Baze de date relaționale
2.1. Noțiunile de bază de date relațională, sistem de gestiune a bazelor de date relaționale
O bază de date relațională (BDR) reprezintă un ansamblu de relații, prin care se reprezintă datele și
legăturile dintre ele.
În cadrul bazei de date relaționale, datele sunt organizate sub forma unor tablouri bidimensionale
(tabele) de date, numite relații. Asocierile dintre relații se reprezintă prin atributele de legătură. În
cazul legăturilor de tip „una-la-multe”, aceste atribute figurează într-una dintre relațiile implicate în
asociere. În cazul legăturilor de tip „multe-la-multe”, atributele sunt situate într-o relație distinctă,
construită special pentru explicarea legăturilor între relații.
9
Prin sistem de gestiune a bazelor de date relaționale (SGBDR) se înțelege un SGBD care utilizează
drept concepție de organizare a datelor modelul relațional.
Definirea unui SGBDR impune o detaliere a caracteristicilor pe care trebuie să le prezinte un SGBD
pentru a putea fi considerat relațional. În acest sens, Codd a formulat (în 1985) 13 reguli, care exprimă
cerințele pe care trebuie să le satisfacă un SGBD.
2.2. Regulile lui Codd
Regulile lui Codd pentru SGBD-urile relaționale:
R0: Gestionarea datelor la nivel de relație. Sistemul trebuie să gestioneze BD numai prin mecanisme
relaționale.
R1: Reprezentarea logică a datelor. Într-o bază de date relaționată, informația este reprezentată la nivel
logic sub forma unor tabele (relații). Acest lucru înseamnă că toate datele trebuie să fie memorate și
prelucrate în același mod.
R2: Garantarea accesului la date. Orice dată din baza de date relaționată trebuie să poată fi accesată
prin specificarea numelui relației (tabelei), a valorii cheii primare și numelui atributului (coloană).
R3: Valorile nule:
• sistemul trebuie să permită declararea și manipularea sistematică a valorilor NULL (semnifică
lipsa unor date);
• valorile NULL diferă de șirurile de caractere „spațiu”, șirurile vide de caractere.
• valorile NULL sunt deosebit de importante în implementarea restricțiilor de integritate:
integritatea entităților; integritatea referențială .
R4: Metadatele. Utilizatorii autorizați trebuie să poată aplica asupra descrierii bazei de date aceleași
operații ca și asupra datelor obișnuite.
R5: Facilitățile limbajelor utilizate:
• trebuie să existe cel puțin un limbaj care să exprime oricare din următoarele operații:
o definirea relațiilor;
o să vizualizeze datele;
o să regăsească informația;
o să poată reactualiza informația;
o să verifice și să corecteze datele de intrare etc.
• în general, toate implementările SQL respectă această regulă.
R6: Actualizarea tabelelor virtuale:
• toate tabelele/relațiile virtuale trebuie să poată fi actualizate;
• nu toate tabelele virtuale sunt teoretic actualizate.
Exemplu: Fie tabela de bază PRODUS, cu următoarea schemă PRODUS (Denumire_produs:D1,
Cantitate:D2, Pret_unitar:D3), cu ajutorul tabelei PRODUS este definită o tabelă virtuală FACTURA,
cu schema: FACTURA (Denumire_produs:D1, Cantitate:D2, Pret_unitar:D3, Valoare:D4). Valorile
atributului „Valoare” se calculează astfel:
Valoare=Cantitate*Pret_unitar
10
Presupunem că se dorește schimbarea prețului unitar la un anumit produs, această schimbare trebuie
efectuată în tabela de bază PRODUS, atributul „Pret_unitar” din tabela virtuală FACTURA, fiind
actualizabil, întrucât actualizarea se poate propaga spre tabela de bază.
Presupunem că se dorește schimbarea valorii (Valoare) la un anumit produs:
modificarea de la tabela virtuală spre tabela de bază nu mai este posibilă, atributul „Valoare”
nu este actualizabil, deoarece schimbarea valorii (Valoare) se poate datora schimbării
cantității (Cantitate) și/sau a prețului unitar (Pret_unirar);
astfel trebuie să existe un mecanism prin care să se poată determina dacă anumite vizualizări
pot fi modificate sau nu.
Majoritatea implementărilor SQL îndeplinesc această cerință.
R7: Actualizările în baza de date (inserările, modificările și ștergerile din baza de date):
• un SGBDR nu trebuie să oblige utilizatorul să caute într-o relație, tuplu cu tuplu, pentru a
regăsi informația dorită;
• această regulă exprimă cerința ca în operațiile prin care se schimbă conținutul bazei de date să
se lucreze la un moment dat pe o întreagă relație.
R8: Independența fizică a datelor:
• o schimbare a structurii fizice a datelor nu trebuie să blocheze funcționarea programelor de
aplicații;
• într-un SGBDR trebuie să se separe aspectul fizic al datelor (stocare sau acces la date) de
aspectul logic al datelor.
R9: Independența logică a datelor. Schimbarea relațiilor bazei de date nu trebuie să afecteze
programele de aplicație.
R10: Restricțiile de integritate. Restricțiile de integritate trebuie să fie definite într-un limbaj
relațional, nu în programul de aplicație.
R11: Distribuirea geografică a datelor. Distribuirea datelor pe mai multe calculatoare dintr-o rețea de
comunicații de date nu trebuie să afecteze programele de aplicație.
R12: Prelucrarea datelor la nivelul de bază. Dacă sistemul posedă un limbaj de bază orientat pe
prelucrarea de tupluri și nu pe prelucrarea relațiilor, acest limbaj nu trebuie să fie utilizat pentru a evita
restricțiile de integritate (se introduc inconsistențe).
Clasificarea regulilor lui Codd
În funcție de tipul de cerințe pe care le exprimă, regulile sunt grupate în 5 categorii:
1) reguli de bază: R0 și R12;
2) reguli structurale: R1 și R6;
3) reguli privind integritatea datelor: R3 și R10;
4) reguli privind manipularea datelor: R2, R4, R5, R7;
5) reguli privind independența datelor: R8, R9, R11.
2.3. Componentele bazelor de date relaționale:
Orice model de date, conform unei sugestii a lui Codd, trebuie să se bazeze pe trei componente:
1) structurile de date. O bază de date relațională (BDR) reprezintă un ansamblu de relații, prin
care se reprezintă date și legăturile dintre ele. Structurile sunt definite de un limbaj de definire
11
a datelor (Data Definition Language). Datele în modelul relațional sunt structurate în relații
(tabele) bidimensionale;
2) operatorii de manipulare a datelor. Relațiile pot fi manipulate utilizând un limbaj de
manipularea datelor (Data Manipulation Language). În modelul relațional, limbajul folosește
operatori relaționali bazați pe conceptul algebrei relaționale. În afară de acesta, există limbaje
echivalente algebrei relaționale, cum ar fi calculul relațional orientat pe tuplu și calculul
relațional orientat pe domeniu ;
3) constrângerile de integritate. Prin integritatea datelor se subînțelege că datele rămân stabile, în
siguranță și corecte. Integritatea în modelul relațional este menținută de constrângeri interne
care nu sunt cunoscute utilizatorului.
1) Structura relațională a datelor
Prezentarea structurii relaționale a datelor impune definirea noțiunilor de: domeniu, tabelă (relație),
atribut, tuplu, cheie și schema tabelei.
Domeniul este un ansamblu de valori caracterizat printr-un nume. El poate fi explicit sau implicit.
Tabela/relația este un subansamblu al produsului cartezian al mai multor domenii, caracterizat printr-
un nume, prin care se definesc atributele ce aparțin aceleași clase de entități.
Atributul este coloana unei tabele, caracterizată printr-un nume.
Cheia este un atribut sau un ansamblu de atribute care au rolul de a identifica un tuplu dintr-o tabelă.
Tipuri de chei: primare/alternate, simple/comune, externe.
Tuplul este linia dintr-o tabelă și nu are nume. Ordinea liniilor (tuplurilor) și coloanelor (atributelor)
dintr-o tabelă nu trebuie să prezinte nici-o importanță.
Schema tabelei este formată din numele tabelei, urmat între paranteze rotunde de lista atributelor, iar
pentru fiecare atribut se precizează domeniul asociat.
Schema bazei de date poate fi reprezentată printr-o diagramă de structură în care sunt puse în evidență
și legăturile dintre tabele. Definirea legăturilor dintre tabele se face logic construind asocieri între
tabele cu ajutorul unor atribute de legătură. Atributele implicate în realizarea legăturilor se găsesc fie
în tabelele asociate, fie în tabele distincte construite special pentru legături. Atributul din tabela inițială
se numește cheie externă , iar cel din tabela finală este cheie primară . Legăturile posibile sunt 1:1,
1:M, N:M. Potențial, orice tabelă se poate lega cu orice tabelă, după orice atribute.
Legăturile se stabilesc la momentul descrierii datelor prin limbaje de descriere a datelor (LDD), cu
ajutorul restricțiilor de integritate. Practic, se stabilesc și legături dinamice la momentul execuției.
2) Operatorii modelului relațional
Operatorii modelului relațional sunt operatorii din:
a) algebra relațională;
b) calcul relațional: orientat pe tuplu; orientat pe domeniu
a) Algebra relațională este o colecție de operații formale aplicate asupra tabelelor (relațiilor), și a
fost concepută de E.F. Codd. Operațiile sunt aplicate în expresiile algebrice relaționale care sunt
cereri de regăsire. Acestea sunt compuse din operatorii relaționali și operanzi. Operanzii sunt
întotdeauna tabele (una sau mai multe). Rezultatul evaluării unei expresii relaționale este format
dintr-o singură tabelă.
12
Algebra relațională are cel puțin puterea de regăsire a calcului relațional. O expresie din calculul
relațional se poate transforma într-una echivalentă din algebra relațională și invers.
Codd a introdus 6 operatori de bază (reuniunea, diferența, produsul cartezian, selecția, proiecția,
joncțiunea) și 2 operatori derivați (intersecția și diviziunea). Ulterior au fost introduși și alți operatori
derivați (speciali). În acest context, operatorii din algebra relațională pot fi grupați în două categorii:
operatori pe mulțimi și operatori speciali.
Fie R1, R2, R3 – relații (tabele).
Operatorii pe mulțimi sunt:
• reuniunea. R3 = R1 ∪ R2, unde R3 va conține tupluri din R1 sau R2 luate o singură dată;
• diferența. R3 = R1 \ R2, unde R3 va conține tupluri din R1 care nu se regăsesc în R2;
• produsul cartezian . R3 = R1 × R2, unde R3 va conține tupluri construite din perechi (x1,x2),
cu x1∈R1 și x2∈R2;
• intersecția. R3 = R1 ∩ R2, unde R3 va conține tupluri care se găsesc în R1 și R2 în același
timp etc.
Operatorii relaționali speciali sunt:
• selecția. Din R1 se obține o subtabelă R2, care va conține o submulțime din tuplurile inițiale
din R1 ce satisfac un predicat (o condiție). Numărul de atribute din R2 este egal cu
numărul de atribute din R1. Numărul de tupluri din R2 este mai mic decât numărul de
tupluri din R1;
• proiecția. Din R1 se obține o subtabelă R2, care va conține o submulțime din atributele inițiale
din R1 și fără tupluri duplicate. Numărul de atribute din R2 este mai mic decât numărul de
atribute din R1;
• joncțiunea este o derivație a produsului cartezian, ce presupune utilizarea unui calificator care
să permită compararea valorilor unor atribute din R1 și R2, iar rezultatul în R3. R1 și R2
trebuie să aibă unul sau mai multe atribute comune care au valori comune.
b) Calculul relațional se bazează pe calculul predicatelor de ordinul întâi și a fost propus de E.F.
Codd. Predicatul este o relație care se stabilește între anumite elemente și care poate fi confirmată sau
nu. Predicatul de ordinul 1 este o relație care are drept argumente variabile care nu sunt predicate.
Variabila poate fi de tip tuplu (valorile sunt dintr-un tuplu al unei tabele) sau domeniu (valorile sunt
dintr-un domeniu al unei tabele). Cuantificatorii (operatorii) utilizați în calculul relațional sunt:
universal ( ∀) și existențial ( ∃).
Construcția de bază în calculul relațional este expresia relațională de calcul tuplu sau domeniu.
Expresia relațională de calcul este formată din:
• operația de efectuat;
• variabile (tuplu respectiv domeniu);
• condiții (de comparație, de existență);
• formule bine definite (operanzi-constante, variabile, funcții, predicate, operatori);
• cuantificatori.
Pentru implementarea acestor operatori există comenzi specifice în limbajele de manipulare a datelor
(LMD) din sistemele de gestiune a bazelor de date relaționale (SGBDR). Aceste comenzi sunt utilizate
în operații de regăsire (interogare).
13
După tehnica folosită la manipulare, LMD sunt bazate pe:
• calculul relațional (QUEL în Ingres, ALPHA propus de Codd);
• algebra relațională (ISBL, RDMS);
• transformare (SQL, SQUARE);
• grafică (QBE, QBF).
Transformarea oferă o putere de regăsire echivalentă cu cea din calculul și algebra relațională. Se
bazează pe transformarea (mapping) unui atribut sau grup de atribute într-un atribut dorit prin
intermediul unor relații. Rezultatul este o relație (tabelă) care se poate utiliza într-o altă transformare.
Grafica oferă interactivitate mare pentru construirea cererilor de regăsire. Utilizatorul specifică cererea
alegând sau completând un ecran structurat grafic. Poate fi folosită de către toate categoriile de
utilizatori în informatică.
Algebra relațională este prin definiție neprocedurală (descriptivă), iar calculul relațional permite o
manieră de căutare mixtă (procedurală/neprocedurală).
3) Restricții de integritate ale modelului relațional
Restricțiile de integritate ale modelului relațional reprezintă cerințe pe care trebuie să le îndeplinească
datele din cadrul bazei de date pentru a putea fi considerate corecte și coerente în raport cu lumea reală
pe care o reflectă. Dacă o bază de date nu respectă aceste cerințe, ea nu poate fi utilizată cu un maxim
de eficiență.
Restricțiile sunt de două tipuri: restricții de integritate structurală și restricții de integritate de
comportament.
Restricții de integritate structurale, care se definesc prin egalitatea sau inegalitatea unor valori din
cadrul relațiilor. Acestea sunt:
• restricția de unicitate a cheii – cheia primară trebuie să fie unică și minimală;
• restricția de integritate a referirii . Într-o tabelă t1 care referă o tabelă t2, valorile cheii externe
trebuie să figureze printre valorile cheii primare din t2 sau să ia valoarea NULL
(neprecizat);
• restricția de integritate a entității . Într-o tabelă, atributele din cheia primară nu trebuie să ia
valoarea NULL.
Cele trei restricții de mai sus sunt minimale.
Pe lângă acestea, există o serie de alte restricții structurale care se referă la dependențele dintre date:
funcționale, multivaloare, joncțiune etc. (sunt luate în considerare la tehnicile de proiectare a bazelor
de date relaționale – BDR).
Restricții de integritate de comportament – sunt cele care se definesc prin comportamentul datelor și
țin cont de valorile din BDR. Acestea sunt:
• restricția de domeniu . Domeniul corespunzător unui atribut dintr-o tabelă trebuie să se
încadreze între anumite valori;
• restricții temporale . Valorile anumitor atribute se compară cu niște valori temporale (rezultate
din calcule etc.).
Restricțiile de comportament fiind foarte generale se gestionează fie la momentul descrierii datelor (de
exemplu prin clauza CHECK), fie în afara modelului la momentul execuției.
14
2.4. Tipuri de constrângeri de integritate
Restricțiile de integritate suportate de Oracle sunt:
• NOT NULL – nu permite valori NULL în coloanele unei tabele;
• UNIQUE – nu sunt permise valori duplicat în coloanele unei tabele;
• PRIMARY KEY – nu permite valori duplicate sau NULL în coloana sau coloanele definite
astfel;
• FOREIGN KEY – presupune ca fiecare valoare din coloana sau setul de coloane definit astfel
să aibă o valoare corespondentă identică în tabela de legătură, tabelă în care coloana
corespondentă este definită cu restricția UNIQUE sau PRIMARY KEY;
• CHECK – elimină valorile care nu satisfac anumite cerințe (condiții) logice.
Termenul de cheie (keys) este folosit pentru definirea câtorva categorii de constrângeri, și sunt :
PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, REFERENCED KEY.
3. Proiectarea bazelor de date relaționale
Proiectarea BDR se realizează prin proiectarea schemelor BDR și proiectarea modulelor funcționale
specializate.
A. Proiectarea schemelor BDR
Schemele bazei de date sunt: conceptuală, externă și internă.
a) Proiectarea schemei conceptuale pornește de la identificarea setului de date necesar sistemului.
Aceste date sunt apoi integrate și structurate într-o schemă (exemplu: pentru BDR relaționale cea mai
utilizată tehnică este normalizarea). Pentru acest lucru se parcurg pașii:
• stabilirea schemei conceptuale inițiale – care se deduce din modelul entitate-asociere. Pentru
acest lucru, se transformă fiecare entitate din model într-o colecție de date (fișier), iar
pentru fiecare asociere se definesc cheile aferente. Dacă rezultă colecții izolate, acestea se
vor lega de alte colecții prin chei rezultând asocieri (1:1, 1:M, N:M);
• ameliorarea progresivă a schemei conceptuale – prin eliminarea unor anomalii (exemplu: cele
cinci forme normale pentru BDR relaționale);
• stabilirea schemei conceptuale finale – trebuie să asigure un echilibru între cerințele de
actualizare și performanțele de exploatare (exemplu: o formă normală superioară asigură
performanțe de actualizare, dar timpul de răspuns va fi mai mare).
Tehnica de normalizare
Tehnica de normalizare este utilizată în activitatea de proiectare a structurii BDR și constă în
eliminarea unor anomalii (neajunsuri) de actualizare din structură.
Anomaliile de actualizare sunt situații nedorite care pot fi generate de anumite tabele în procesul
proiectării lor:
• anomalia de ștergere – stergând un tuplu dintr-o tabelă, pe lângă informațiile care trebuie
șterse, se pierd și informațiile utile existente în tuplul respectiv;
• anomaliile de adăugare – nu pot fi incluse noi informații necesare într-o tabelă, deoarece nu se
cunosc și alte informații utile (de exemplu valorile pentru cheie);
• anomalia de modificare – este dificil de modificat o valoare a unui atribut atunci când ea apare
în mai multe tupluri.
15
Normalizarea este o teorie construită în jurul conceptului de forme normale (FN), care ameliorează
structura BDR prin înlăturarea treptată a unor neajunsuri și prin imprimarea unor facilități sporite
privind manipularea datelor.
Normalizarea utilizează ca metodă descompunerea (top-down) unei tabele în două sau mai multe
tabele, păstrând informații (atribute) de legătură.
Codd a definit inițial 3 forme normale, notate prin FN1, FN2 și FN3. Întrucât într-o primă formulare,
definiția FN3 ridică ceva probleme, Codd și Boyce au elaborat o nouă variantă, cunoscută sub numele
de Boyce-Codd Normal Form (BCNF) . Astfel, BCNF este reprezentată separat în majoritatea
lucrărilor. R. Fagin a tratat cazurile FN4 și FN5.
O relație este într-o formă normală dacă satisface o mulțime de constrângeri.
Normalizarea bazei de date relaționale poate fi imaginată ca un proces prin care pornindu-se de la
relația inițială/universală R se realizează descompunerea succesivă a acesteia în subrelații, aplicând
operatorul de proiecție. Relația R poate fi ulterior reconstruită din cele n relații obținute în urma
normalizării, prin operații de joncțiune.
3.1. Formele normale: FN1; FN2; FN3
Prima formă normală (FN1)
FN1 este strâns legată de noțiunea de atomicitate a atributelor unei relații. Astfel, aducerea unei relații
în FN1 presupune introducerea noțiunilor de:
• atribut simplu (atribut atomic) – atribut care nu mai poate fi descompus în alte atribute, în caz
contrar, atributul este compus (atribut neatomic);
• atribut compus ;
• grupuri repetitive de atribute – atribut (grup de atribute) dintr-o relație care apare cu valori
multiple pentru o singură apariție a cheii primare a relației nenormalizate.
Aducerea unei relații universale în FN1
FN1 este tratată în general cu superficialitate, deoarece principala cerință – atomicitatea valorilor –
este ușor de îndeplinit (cel puțin la prima vedere).
Dintre toate formele normale, doar FN1 are caracter de obligativitate. Se spune că o bază de date este
normalizată dacă toate relațiile se află măcar în FN1.
O relație este în FN1 dacă domeniile pe care sunt definite atributele relației sunt constituite numai din
valori atomice. Un tuplu nu trebuie să conțină atribute sau grupuri de atribute repetitive.
Aducerea relațiilor în FN1 presupune eliminarea atributelor compuse și a celor repetitive.
Se cunosc trei soluții pentru determinarea grupurilor repetitive :
1) eliminarea grupurilor repetitive pe orizontală (în relația R inițială, în locul atributelor compuse
se trec componentele acestora, ca atribute simple);
2) eliminarea grupurilor repetitive prin adăugarea de tupluri;
3) eliminarea grupurilor repetitive prin construirea de noi relații.
Primele două metode generează relații stufoase prin duplicarea forțată a unor atribute, respectiv
tupluri, creându-se astfel redundanțe masive cu multiple anomalii de actualizare.
16
Metoda a treia presupune eliminarea grupurilor repetitive prin construirea de noi relații, ceea ce
generează o structură ce oferă cel mai mic volum de redundanță.
Exemplu: Fie relația nenormalizată (primară) FACTURI. Să se stabilească o structură de tabele care să
permită stocarea informațiilor conținute în document (factură) și obținerea unor situații sintetice
privind evidența sumelor facturate pe produse, pe clienți, pe anumite perioade de timp.
Relația FACTURI nenormalizată
În cazul în care o factură conține mai multe produse, relația de mai sus va avea grupurile repetitive:
„cod_produs”, „denumire_produs”, „data_expirarii”, „cantitate”, „pret_unitar”, „valoare”,
„valoare_tva”.
Etapele de aducere a unei relații în FN1 sunt:
I. se construiește câte o relație pentru fiecare grup repetitiv;
II. în schema fiecărei noi relații obținute la pasul 1 se introduce și cheia primară a relației R
nenormalizată;
III. cheia primară a fiecărei noi relații va fi compusă din atributele chei ale relației R, plus unul
sau mai multe atribute proprii.
Exemplu: Deoarece o factură poate avea unul sau mai multe produse înscrise pe aceasta, informațiile
legate de produse vor fi separate într-o altă tabelă. Aplicând etapele de aducere în FN1, se obțin două
relații.
FACTURI
nr_factura#
data_factura
nume_client
adresa_client
telefon_client
email_client
banca_client
nr_cont_client
delegat
cod_produs
denumire_produs
unitate_de_masura
data_expirarii
cantitate
pret_unitar
valoare
valoare_tva
total_valoare_factura
total_valoare_tva
17
Relația FACTURI adusă în forma normală FN1
Observații:
• Câmpul „adresa_client” cuprinde informații despre județul, localitatea, strada și numărul
domicililului clientului. Dacă se consideră că este de interes o evidență a sumelor
factorizate pe județe sau localități, se vor pune în locul câmpului „adresa_client” trei
câmpuri distincte: „judet_client”, „localitate_client”, „adresa_client”, ușurând în acest fel
interogările;
• Între tabela FACTURI și tabela LINII_FACTURI există o relație de „una-la-multe”, adică
unui număr unic de factură îi pot corespunde unul sau mai multe produse care sunt
memorate ca înregistrări în tabela LINII_FACTURI. Cheia primară în această tabelă este
o cheie compusă, formată din două câmpuri: „nr_factura” și „cod_produs”.
Însă, eliminarea grupurilor repetitive, adică aducerea unei relații în FN1, nu rezolvă complet problema
normalizării.
A doua formă normală (FN2)
FN2 este strâns legată de noțiunea de dependență funcțională (DF) .
O relație se află în a doua formă normală (FN2) dacă:
1. se află în forma normală FN1 și
2. fiecare atribut, care nu este cheie, este dependent de întreaga cheie primară.
Etapele de aducere a unei relații de la FN1 la FN2 sunt:
I. se identifică posibila cheie primară a relației aflate în FN1;
II. se identifică toate dependențele dintre atributele relației, cu excepția acelora în care sursa este
un atribut component al cheii primare;
III. se identifică toate dependențele care au ca sursă un atribut sau subansamblu de atribute din
cheia primară;
IV. pentru fiecare atribut (sau subansamblu) al cheii de la pasul III se creează o relație care va
avea cheia primară atributul (subansamblul) respectiv, iar celelalte atribute vor fi cele care
apar ca destinație în dependențele de la etapa III.
Exemplu: Relația care conține date redundante (de exemplu, modificarea denumirii unui produs atrage
după sine modificarea în fiecare tuplu în care apare acest produs) este relația LINII_FACTURI. Se
observă că nu există nici o dependență funcțională între atributele necomponente ale cheii. În schimb,
toate atributele care nu intră în alcătuirea cheii compuse sunt dependente de aceasta, iar DF dintre FACTURI
nr_factura#
data_factura
nume_client
adresa_client
telefon_client
banca_client
nr_cont_client
delegat
toal_valoare_factura
toal_valoare_tva
LINII_FACTURI
nr_factura#
cod_produs#
denumire_produs
unitate_de_masura
data_expirarii
cantitate
pret_unitar
valoare
valoare_tva
18
atributul component al cheii primare sunt: cod_produs –> denumire_produs, cod_produs –>
unitate_de_masura, data_expirarii. Ca urmare se formează încă două relații.
Relația FACTURI în a doua formă normală FN2
Chiar dacă au fost eliminate o parte din redundanțe, mai rămân și alte redundanțe ce se vor elimina
aplicând alte forme normale.
A treia formă normală (FN3)
O relație este în forma normală trei (FN3) dacă:
1. se găsește în FN2 și
2. fiecare atribut care nu este cheie (nu participă la o cheie) depinde direct de cheia primară. A
treia regulă de normalizare cere ca toate câmpurile din tabele să fie independente între ele.
Exemplu: În relația FACTURI se observă că atributul „nume_client” determină în mod unic atributele
„adresa_client”, „telefon_client”, „email_client”, „banca_client” și „nr_cont_client”. Deci pentru
atributul „nume_client” se construiește o relație CLIENTI în care cheia primară va fi acest atribut, iar
celelalte atribute vor fi „adresa_client”, „telefon_client”, „email_client”, „banca_client” și
„nr_cont_client”. Câmpurile „valoare” și „valoare_tva” depind de câmpurile „cantitate”, „pret_unitar”,
și de un procent fix de TVA. Fiind câmpuri ce se pot calcula în orice moment, ele vor fi eliminate din
tabelă LINII FACTURI, deoarece constituie informație memorată redundant.
Relația FACTURI în a treia forma normală FN3
Etapele de aducere a unei relații de la FN2 la FN3 sunt:
I. se identifică toate atributele ce nu fac parte din cheia primară și sunt surse ale unor dependențe
funcționale; FACTURI
nr_factura#
data_factura
nume_client
adresa_client
telefon_client
email_client
banca_client
nr_cont_client
delegat
toal_valoare_factura
toal_valoare_tva LINII_FACTURI
nr_factura#
cod_produs#
cantitate
pret_unitar
valoare
valoare_tva
PRODUSE
cod_produs#
denumire_produs
unitate_de_masura
data_expirarii
FACTURI
nr_factura#
data_factura
nume_client
delegat
toal_valoare_factura
toal_valoare_tva LINII_FACTURI
nr_factura#
cod_produs#
cantitate
pret_unitar
PRODUSE
cod_produs#
denumire_produs
unitate_de_masura
data_expirarii
CLIENTI
nume_client#
adresa_client
telefon_client
email_client
banca_client
nr_cont_client
19
II. pentru aceste atribute, se construiește câte o relație în care cheia primară va fi atributul
respectiv, iar celelalte atribute, destinațiile din DF considerate;
III. din relația de la care s-a pornit se elimină atributele destinație din DF identificată la pasul I,
păstrându-se atributele surse.
Observații:
• Această a treia formă normală mai poate suferi o serie de rafinări pentru a putea obține o
structură performantă de tabele ale bazei de date. De exemplu, se observă că
„nume_client” este un câmp în care este înscris un text destul de lung format dintr-o
succesiune de litere, semne speciale (punct, virgulă, cratimă), spații, numere. Ordonarea și
regăsirea informațiilor după astfel de câmpuri este lentă și mai greoaie decât după câmpuri
numerice. Din acest motiv se poate introduce un nou atribut „cod_client” care să fie
numeric și care să fie cheia primară de identificare pentru fiecare client;
• O altă observație care poate fi făcută în legătură cu tabelele aflate în cea de a treia formă
normală este aceea că „total_valoare_factura” este un câmp care ar trebui să conțină
informații sintetice obținute prin însumarea valorii tuturor ofertelor aflate pe o factură.
Este de preferat ca astfel de câmpuri să fie calculate în rapoarte sau interogări și să nu fie
memorate în tabelele bazei de date.
Verificarea aplicării corecte a procesului de normalizare se realizează astfel încât uniunea acestor
relații să producă relația inițială, cu alte cuvinte, descompunerea este fără pierderi.
Celelalte forme normale se întâlnesc mai rar în practică. Aceste forme nu sunt respectate, în general,
pentru că beneficiile de eficiență pe care le aduc nu compensează costul și munca de care este nevoie
pentru a le respecta.
Forma normală Boyce Codd (FNBC)
O definiție mai riguroasă pentru FN3 a fost dată prin forma intermediară BCNF (Boyce Codd Normal
Form):
• o tabelă este în BCNF dacă fiecare determinant este un candidat cheie. Determinantul este un
atribut elementar sau compus față de care alte atribute sunt complet dependente
funcțional.
A patra formă normală (FN4)
O tabelă este în FN4 dacă și numai dacă:
• este în FN3 și
• nu conține două sau mai multe dependențe multivaloare. Într-o tabelă T, fie A, B, C trei
atribute. În tabela T se menține dependența multivaloare A dacă și numai dacă mulțimea
valorilor lui B ce corespunde unei perechi de date (A,C), depinde numai de o valoare a lui
A și este independentă de valorile lui C. FACTURI
nr_factura#
data_factura
nume_client
delegat
LINII_FACTURI
nr_factura#
cod_produs#
cantitate
pret_unitar
PRODUSE
cod_produs#
denumire_produs
unitate_de_masur
a
data_expirarii
CLIENTI
cod_client#
nume_client
adresa_client
telefon_client
email_client
banca_client
nr_cont_client
20
A cincea formă normală (FN5) (numită și forma normală proiecție-uniune)
O tabelă este în FN5 dacă și numai dacă:
• este în FN4 și
• orice dependență de uniune a lui R este o consecință a unei chei candidat a lui R. În tabela T
(A,B,C) se menține dependența joncțiune (AB,AC) dacă și numai dacă T menține
dependența multivaloare A –>> B sau C.
Fiecare dintre cele 6 forme normale este mai restrictivă ca predecesoarea sa. Astfel, de exemplu, o
schemă de relație aflată în forma normală trei este și în forma normală doi, așa cum se reprezintă în
figura de mai jos:
Forme normale
Scopul formelor normale este acela de a elimina redundanțele din cadrul relațiilor prin descompunerea
acestora în două sau mai multe relații, fără însă a pierde informație, ceea ce înseamnă faptul că este
posibilă, în orice moment, revenirea la relația originară doar pe baza relațiilor obținute din
descompunere.
Dependența multivaloare este caz particular al dependenței joncțiune. Dependența funcțională este caz
particular al dependenței multivaloare.
b) Proiectarea schemei externe are rolul de a specifica viziunea fiecărui utilizator asupra BDR. Pentru
acest lucru, din schema conceptuală se identifică datele necesare fiecărei viziuni. Datele obținute se
structurează logic în subscheme ținând cont de facilitățile de utilizare și de cerințele utilizator. Schema
externă devine operațională prin construirea unor vizualizări (view) cu SGBD-ul și acordarea
drepturilor de acces. Datele într-o vizualizare pot proveni din una sau mai multe colecții și nu ocupă
spațiul fizic.
c) Proiectarea schemei interne presupune stabilirea structurilor de memorare fizică a datelor și
definirea căilor de acces la date. Acestea sunt specifice fie SGBD-ului (scheme de alocare), fie
sistemului de operare. Proiectarea schemei interne înseamnă estimarea spațiului fizic pentru BDR,
definirea unui model fizic de alocare (trebuie văzut dacă SGBD-ul permite explicit acest lucru) și
definirea unor indecși pentru accesul direct, după cheie, la date.
B. Proiectarea modulelor funcționale specializate
Proiectarea modulelor funcționale ține cont de concepția generală a BDR, precum și de schemele
proiectate anterior. În acest sens, se proiectează fluxul informațional, modulele de încărcare și
manipulare a datelor, interfețele specializate, integrarea elementelor proiectate cu organizarea și
funcționarea BDR. FN 5FN 4FNBCFN 3FN 2FN 1
21
Realizarea componentelor logice . Componentele logice ale unei BD sunt programele de aplicație
dezvoltate, în cea mai mare parte, în SGBD-ul ales. Programele se realizează conform modulelor
funcționale proiectate în etapa anterioară. Componentele logice țin cont de ieșiri, intrări, prelucrări și
de colecțiile de date. În paralel cu dezvoltarea programelor de aplicații se întocmesc și diferite
documentații (tehnică, de exploatare, de prezentare).
Punerea în funcțiune și exploatarea . Se testează funcțiile BDR mai întâi cu date de test, apoi cu date
reale. Se încarcă datele în BDR și se efectuează procedurile de manipulare, de către beneficiar cu
asistența proiectantului. Se definitivează documentațiile aplicației. Se intră în exploatare curentă de
către beneficiar conform documentației.
Dezvoltarea sistemului . Imediat după darea în exploatare a BDR, în mod continuu, pot exista factori
perturbatori care generează schimbări în BDR. Factorii pot fi: organizatorici, datorați progresului
tehnic, rezultați din cerințele noi ale beneficiarului, din schimbarea metodologiilor etc.
4. Limbajul SQL (Structured Query Language)
Limbajul SQL (Structured Query Language) este limbajul utilizat de majoritatea sistemelor de baze de
date relaționale (SGBDR) pentru definirea și manipularea datelor.
4.1. Structura lexicală a limbajului SQL
Elementele unei instrucțiuni (statement) sunt:
• cuvinte cheie (key words) – dintre care fac parte comenzile (SELECT, UPDATE, INSERT
etc), operatorii (AND, OR, NOT, LIKE), clauzele (WHERE, SET, VALUES etc);
• identificatori (identifier) – sunt elementele care denumesc tabela, coloana sau alt obiect al BD.
SQL face diferența între literele mari și mici, deci este „case-sensitive”; identificatorul
care conține ghilimele se numește identificator delimitat ;
• constante (literali) – reprezintă șiruri de caractere (‘ ‘), numere întregi, numere reale (ex. 3.5;
4.; .001; 5e2), constanta NULL, constante de tip logic (1 pentru TRUE și 0 pentru
FALSE);
• caractere speciale – cum ar fi (;) care semnifică terminarea comenzilor; (.) care semnifică
virgula zecimală; sau (*) care simbolizează operatorul de înmulțire.
4.2. Operatori SQL
SQL are următorii operatori:
• operatori aritmetici binari :
+
–
*
% modulo
^ ridicarea la putere
& AND orientat pe biți
| OR orientat pe biți
# XOR orientat pe biți
<< deplasare la stânga
>> deplasare la dreapta
22
• operatori binari de comparație:
<
>
<=
>=
=
<> sau != diferit
• operatori aritmetici mari:
@ valoarea absolută
! factorial
!! factorial, operator postfix
~ NOT orientat pe biți
• operatori de comparație:
A BETWEEN min AND max (compară A cu două valori: min și max)
A IN (v1,…,vn) compară A cu o listă de valori
A IS NULL
A IS NOT NULL
A LIKE model_șir
• operatori logici:
Operatorii logici sunt legați prin cuvintele cheie AND, OR, NOT și returnează o valoare
logică TRUE, FALSE sau NULL.
• operatori relaționali:
UNION (reuniune)
INTERSECT (intersecție)
MINUS (diferență)
4.3. Funcții definite în SQL
Categorii de funcții SQL:
• funcții pe un sigur rând (funcții scalare) – realizează operații asupra unui singur rând și
returnează un singur rezultat pentru fiecare rând. Funcțiile pe un sigur rând cuprind
următoarele tipuri de funcții:
o funcții de tip caracter – acceptă argumente de tip caracter și întorc rezultate de tip
caracter (CHR, CONCAT, INITCAP, LOWER, LPAD, LTRIM, REPLACE, RPAD,
RTRIM, SUBSTR, UPPER etc.) sau numeric (ASCII, INSTR, LENGTH);
o funcții de tip numeric (de calcul trigonometric : sin, cos, tg, ctg etc.; de calcul al
logaritmului : ln, log, lg; de calcul al puterilor : pow; de rotunjire : floor, ceil etc.) –
acceptă argumente de tip numeric și întorc rezultate de tip numeric;
o funcții de tip dată calendaristică (ADD_MONTHS, LAST_DAY, MONTHS_
BETWEEN, NEXT_DAY, SYSDATE etc.) – acceptă argumente de tip dată
calendaristică și întorc rezultate de tip dată calendaristică cu excepția funcției
MONTH_BEETWEEN care întoarce o valoare numerică;
o funcții de conversie (TO_CHAR, TO_NUMBER, TO_DATE, CAST) – fac conversia
dintr-un tip de dată în altul;
o funcții generale : NVL, DECODE.
• funcții pe mai mutle rânduri (funcții de grup) – lucrează cu grupuri de rânduri pentru a returna
un singur rezultat pentru fiecare grup. Aceste funcții sunt cunoscute cu denumirea de
funcții de grup . Toate funcțiile de grup, mai puțin COUNT(*) ignoră valorile NULL.
Majoritatea funcțiilor de grup acceptă opțiunile: DISTINCT (determină luarea în calcul
23
numai a valorilor distincte ale rândurilor din cadrul grupului) și ALL (este implicit și
determină luarea în calcul a tuturor valorilor grupului de rânduri).
Funcțiile agregat – calculează un rezultat din mai multe linii ale unui tabel (funcții de totalizare):
o COUNT (furnizează numărul de linii ale unui rezultat);
o SUM (execută suma tuturor valorilor dintr-o coloană);
o MAX (returnează valoarea cea mai mare dintr-o coloană);
o MIN (returnează valoarea cea mai mică dintr-o coloană);
o AVG (calculează media valorilor dintr-o coloană).
Aceste funcții vor fi folosite în instrucțiunea SELECT.
Funcțiile scalare – primesc unul sau mai multe argumente și returnează valoarea calculată sau NULL
în caz de eroare. Argumentele funcțiilor pot fi constante sau valori ale atributelor specificate prin
numele coloanelor corespunzătoare.
4.4. Tipuri de date
În limbajul SQL sunt definite mai multe tipuri de date: numeric, șir de caractere, șir de biți, dată
(calendaristică), timp.
Denumirile tipurilor de date precum și limitele acestora diferă de la un SGBD la altul, dar în general,
sunt destul de asemănătoare.
• Tipul numeric include:
numere întregi :
o INTEGER sau INT reprezentat pe 4 octeți;
o SMALLINT reprezentat pe 2 octeți;
numere reale reprezentate în virgulă flotantă, cu diferite precizii:
o FLOAT reprezentat pe 4 octeți;
o REAL reprezentat pe 8 octeți;
o DOUBLE [PRECISION] reprezentat pe 8 octeți;
numere zecimale reprezentate cu precizia dorită:
o tipul NUMERIC sau DECIMAL, cu forma numeric[(p,s)], unde p este numărul total
de cifre afișate, iar s este numărul de cifre după punctul zecimal.
• Tipul șir de caractere
o CHARACTER (n) sau CHAR (n) definesc șiruri de caractere cu lungime fixă;
o CHARACTER VARYING sau VARCHAR (n) definește șirul de caractere cu
lungime variabilă.
Asemănarea dintre cele două tipuri prezentate mai sus este aceea că ambele reprezintă șiruri de
maxim n caractere, iar deosebirea este aceea că pentru șiruri cu număr de caractere mai mic ca n,
CHAR (n) completează șirul cu spații albe până la n caractere, iar VARCHAR (n) memorează
numai atâtea caractere câte are șirul dat.
• Tipul șiruri de biți
o BIT(n) definește secvențe de cifre binare (care pot lua valoarea 0 sau 1) de lungime
finită n;
o BIT VARYING (n) definește secvențe de lungime variabilă, cu limita maximă n.
24
• Tipuri pentru data calendaristică și timp
o DATE permite memorarea datelor calendaristice în formatul yyyy-mm-dd;
o TIME permite memorarea timpului, folosind trei câmpuri hh:mm:ss;
o TIMESTAMP(p) permite memorarea combinată a datei calendaristice și a timpului,
cu precizia p pentru câmpul SECOND (al secundelor); valoarea implicită a lui p este
6;
o INTERVAL este utilizat pentru memorarea intervalelor de timp.
Tipurile de date sunt „case-insensitive”, deci nu țin cont de caracterele mari sau mici.
4.5. Categorii de instrucțiuni SQL
În funcție de tipul acțiunii pe care o realizează, instrucțiunile SQL se împart în mai multe categorii.
Datorită importanței pe care o au comenzile componente, unele dintre aceste categorii sunt evidențiate
ca limbaje relaționale în cadrul SQL, și anume:
• limbajul de definire a datelor (LDD sau DDL – Data Definition Language);
• limbajul de interogare a datelor (LQD sau DQL – Data Query Language);
• limbajul de prelucrare a datelor (LMD sau DML – Data Manipulation Language);
• limbajul de control al datelor (LCD sau DCL – Data Control Language).
Pe lângă comenzile care alcătuiesc aceste limbaje, SQL cuprinde:
• instrucțiuni pentru controlul sesiunii;
• instrucțiuni pentru controlul sistemului;
• instrucțiuni SQL încapsulate.
5. Limbajul de definire a datelor (LDD)
Limbajele relaționale de definire a datelor oferă următoarele facilități utilizatorilor:
• facilități de descriere a datelor la nivel conceptual . În vederea descrierii datelor la nivel
conceptual, limbajele relaționale conțin o serie de comenzi, și anume:
o crearea unei BD (dicționarul BD): CREATE DATABASE;
o ștergerea unei BD: DROP DATABASE;
o crearea tabelelor de bază: CREATE TABLE;
o ștergerea tabelelor de bază: DROP TABLE;
o crearea de sinonime: CREATE SYNONYM;
o ștergerea sinonimelor: DROP SYNONYM;
o actualizarea structurii unei tabele: ALTER TABLE cu opțiunile ADD, MODIFY,
DROP;
o adăugarea restricțiilor de integritate: ASSERT ON. În Oracle restricțiile de integritate
sunt: NULL, CHECK, pe cheie (PRIMARY, UNIQUE, REFERENTIAL).
• facilități de descriere a datelor la nivel logic . Pentru descrierea datelor la nivel logic,
limbajele relaționale dispun de o serie de comenzi, precum:
o crearea tabelelor virtuale: CREATE VIEW;
o ștergerea tabelelor virtuale: DROP VIEW;
o acordarea drepturilor de acces la BD:
GRANT CONNECT – conectarea la BD a unui utilizator;
GRANT drepturi – acordarea unor drepturi de acces (pentru regăsire,
actualizare etc.).
25
o retragerea drepturilor de acces la BD:
REVOKE drepturi – retragerea unor drepturi;
REVOKE CONNECT – deconectarea unui utilizator de la BD.
facilități de descriere a datelor la nivel fizic . Pentru definirea unor caracteristici legate de
organizarea la nivel fizic a datelor din baza de date, limbajele relaționale dispun de o serie
de comenzi, și anume:
o crearea indecșilor: CREATE INDEX;
o ștergerea indecșilor: DROP INDEX;
o controlul alocării spațiului fizic al BD:
CREATE SPACE – creează un model de alocare a spațiului fizic pentru o
BD;
ALTER SPACE – actualizează modelul de alocare a spațiului fizic;
DROP SPACE – șterge un model de alocare a spațiului fizic.
o regruparea fizică a datelor dintr-o BD (clustere):
CREATE CLUSTER – creează un cluster dintr-o BD;
ALTER CLUSTER– actualizează un cluster;
DROP CLUSTER – șterge un cluster.
5.1. Comenzi (CREATE, ALTER, DROP)
Limbajul de definire a datelor (a schemei unei BD) include instrucțiuni ce permit:
crearea schemei bazei de date;
adăugarea relațiilor la schema bazei;
ștergerea unor relații existente;
adăugarea de noi atribute relațiilor existente;
optimizarea bazei de date (index, grup, declanșator);
definirea structurii fizice și logice a unei BD;
restricții cu privire la utilizarea structurii.
Comenzi pentru crearea unei baze de date
CREATE DATABASE nume_baza;
Comenzi pentru suprimarea unei baze de date
DROP DATABASE nume_baza;
Această comandă distruge BD cu numele nume_baza .
Comenzi pentru crearea relațiilor de bază
În cadrul acestor comenzi se precizează numele relației, precum și numele și tipul atributelor.
CREATE TABLE nume_tabela (atribute);
Crearea unei relații indicând cheia la nivel de coloană
Exemplu: Să se creeze relația PRODUSE(cod_produs, denumire_proddus,
unitate_de_masura, data_expirarii).
CREATE TABLE PRODUSE
(cod_produs VARCHAR(5) PRIMARY KEY ,
denumire_proddus VARCHAR(30),
unitate_de_masura VARCHAR(2),
data_expirarii DATE);
26
Crearea unei relații indicând cheile la nivel de tabel
Exemplu: Să se creeze relația LINII_FACTURI(nr_factura, cod_produs, cantitate,
PRET_UNIRAR).
CREATE TABLE LINII_FACTURI
(nr_factura VARCHAR(5),
cod_produs CHAR (5),
cantitate REAL,
pret_unitar REAL,
PRIMARY KEY (nr_factura, cod_produs),
FOREIGN KEY (cod_produs)
REFERENCES PRODUSE (cod_produs));
Dacă cheia primară are mai mult de o coloană atunci cheile trebuie indicate la nivel de tabel.
Crearea unui tabel prin copiere
Exemplu: Să se creeze relația PRODUSE_PAINE(cod_produs, denumire_proddus,
unitate_de_masura, data_expirarii), utilizând copierea datelor din relația PRODUSE .
CREATE TABLE PRODUSE_PAINE
SELECT cod_produs, denumire_proddus, unitate_de_masura,
data_expirarii
FROM PRODUSE
WHERE denumire_proddus LIKE 'PAINE';
Comenzi pentru suprimarea unei relații de bază
DROP TABLE nume_tabela;
Comanda SQL distruge relația nume_tabela.
Comenzi pentru schimbarea numelui unei relații
RENAME nume_tabela TO nume_tabela_nou;
Exemplu: Să se modifice numele relației PRODUSE_PAINE în PROD_PAINE , apoi să se suprime
relația PROD_PAINE .
RENAME TABLE PRODUSE_PAINE TO PROD_PAINE;
DROP TABLE PROD_PAINE;
Comenzi pentru modificarea structurii unei relații
Prin modificarea structurii unei relații se înțelege:
• extinderea schemei relației prin adăugarea de noi atribute;
• restrângerea schemei unei relații prin suprimarea unor atribute;
• modificarea numelui și/sau tipului unui atribut din cadrul relației.
Unele limbaje relaționale (QBE) admit toate aceste tipuri de modificări în schema unei relații, iar
altele (SQL sau QUEL) numai o parte.
ALTER TABLE nume_tabel …
Adăugarea unui atribut cu ajutorul opțiunii ADD
Exemplu: Să se adauge atributul „TOTAL_VALOARE_FACTURA” la relația LINII_FACTURI.
ALTER TABLE LINII_FACTURI
ADD (TOTAL_VALOARE_FACTURA REAL);
Modificarea unui atribut cu ajutorul opțiunii MODIFY
Exemplu: Să se modifice forma prețului unitar din relația LINII_FACTURI.
27
ALTER TABLE LINII_FACTURI
MODIFY pret_unitar DECIMAL (10,2);
Comenzi pentru declararea restricțiilor de integritate (a constrângerilor)
Constrângerea este un mecanism care asigură că valorile unei coloane sau a unei mulțimi de coloane
satisfac o condiție declarată. Unei constrângeri i se poate da un nume unic. Dacă nu se specifică un
nume explicit atunci sistemul automat îi atribuie un nume de forma SYS_Cn, unde n reprezintă
numărul constrângerii. Constrângerile pot fi șterse, pot fi adăugate, pot fi activate sau dezactivate, dar
nu pot fi modificate.
Prin comanda CREATE TABLE pot fi specificate anumite restricții (constrângeri) prin care se
exprimă o condiție care trebuie respectată de toate tuplurile uneia sau mai multor relații. Acestea pot fi
definite cu ajutorul comenzii:
ALTER TABLE
Constrângerile declarative pot fi:
• constrângeri de domeniu , care definesc valorile luate de un atribut:
o DEFAULT
o NOT NULL
o UNIQUE
o CHECK
• constrângeri de integritate a entității , care precizează cheia primară:
o PRIMARY KEY
• constrângeri de integritate referențială , care asigură corespondența între cheile primare și
cheile externe corespunzătoare:
o FOREIGN KEY
Fiecărei restricții i se poate da un nume, lucru util atunci când, la un moment dat (salvări, restaurări,
încărcarea BD) se dorește dezactivarea uneia sau mai multora dintre acestea. Astfel se prefigurează
numele fiecărei restricții cu tipul său:
• pk_(PRIMARY KEY) – pentru cheile primare;
• un_(UNIQUE) – pentru cheile alternative – care impune respectarea unicității valorilor;
• nn_(NOT NULL) – pentru atributele obligatorii;
• ck_(CHECK) – pentru reguli de validare la nivel de atribut;
• fk_(FOREIGN KEY) – pentru cheile străine.
Exemplu: Să se realizeze constrângerea de cheie primară, de cheie externă și constrângerea de
domeniu pentru relația LINII_FACTURI .
CREATE TABLE LINII_FACTURI
(nr_factura VARCHAR(5) NOT NULL ,
CONSTRAINT pk_nr PRIMARY KEY (nr_factura) ,
cod_produs CHAR (5),
CONSTRAINT FOREIGN KEY fk_co(cod_produs)
REFERENCES PRODUSE (cod_produs),
cantitate REAL,
PRET_UNIRAR REAL);
Observații:
• Liniile ce nu respectă constrângerea sunt depuse automat într-un tabel special;
• Constrângerile previn ștergerea unui tabel dacă există dependențe;
• Constrângerile pot fi activate sau dezactivate în funcție de necesități.
• Constrângerile pot fi create o dată cu tabelul sau după ce acesta a fost creat.
28
Modificarea unei restricții de integritate
ALTER TABLE nume_tabela
MODIFY(nume_atribut TIP_CONSTRÂNGERE);
Exemplu: Să se modifice una din constrângerile din exemplul de mai sus:
ALTER TABLE LINII_FACTURI
MODIFY PRET_UNIRAR REAL NOT NULL ;
Activarea și/sau dezactivarea unei constrângeri
Activarea sau dezactivarea unei constrângeri se realizează cu ajutorul opțiunilor ENABLE sau
DISABLE .
Exemplu: Să se dezactiveze, apoi să se activeze constrângerea de cheie primară din relația
LINII_FACTURI .
ALTER TABLE LINII_FACTURI
ADD (CONSTRAINT pk_nr PRIMARY KEY (nr_factura) DISABLE );
ALTER TABLE LINII_FACTURI ENABLE (CONSTRAINT pk_nr);
Suprimarea unei constrângeri cu ajutorul opțiunii DROP
ALTER TABLE nume_tabela DROP PRIMARY KEY;
Exemplu: Să se suprime restricția de cheie primară (pentru atributul „ nr_factura ”) din tabela
LINII_FACTURI .
ALTER TABLE LINII_FACTURI DROP PRIMARY KEY;
Adăugarea unei constrângeri cu ajutorul opțiunii ADD
ALTER TABLE nume_tabela ADD CONSTRAINT …;
Exemplu: Să se adauge restricția de cheie primară „nr_factura” pentru relația LINII_FACTURI.
ALTER TABLE LINII_FACTURI
ADD CONSTRAINT pk_nr PRIMARY KEY (nr_factura);
Observații:
• Comanda ALTER TABLE realizează modificarea structurii tabelului (la nivel de coloană sau
la nivel de tabel), dar nu modificarea conținutului acestuia;
• Constrângerile pot fi adăugate (ADD CONSTRAINT), șterse (DROP CONSTRAINT),
activate (ENABLE) sau dezactivate (DISABLE), dar nu pot fi modificate;
• Dacă există o cheie externă care referă o cheie primară și dacă se încearcă ștergerea cheii
primare, această ștergere nu se poate realiza (tabelele sunt legate prin declarația de cheie
externă). Ștergerea este totuși permisă dacă în comanda ALTER apare opțiunea
CASCADE, care determină și ștergerea cheilor externe ce referă cheia primară urmărind
sintaxa:
ALTER TABLE Nume_tabela
DROP PRIMARY KEY CASACDE ;
29
6. Limbajul de manipulare a datelor (LMD)
Instrucțiunile LMD (sau DML, Data Manipulation Language) sunt utile pentru interogarea și
prelucrarea datelor din obiectele unei scheme. Aceste instrucțiuni permit:
• interogarea bazei de date (SELECT);
• adăugarea de înregistrări în tabele (sub forma rândurilor din tabele) sau vizualizări (INSERT);
• modificarea valorilor unor coloane din înregistrările existente în tabele sau vizualizări
(UPDATE);
• suprimarea de înregistrări din tabele sau vizualizări (DELETE).
O colecție de comenzi LMD care formează o unitate logică de lucru se numește tranzacție.
Instrucțiunile LMD individuale afectează datele dintr-un singur tabel. Este posibil ca într-o
instrucțiune LMD să se refere și o vizualizare care conține date din mai multe tabele (adică o
vizualizare care conține o uniune de tabele), dar, în acest caz, instrucțiunea LMD poate referi numai
coloane dintr-un singur tabel al vizualizării. Cu alte cuvinte, atunci când o instrucțiune LMD folosește
o vizualizare, toate coloanele vizualizării referite în instrucțiunea LMD trebuie să corespundă unor
coloane dintr-un singur tabel fizic al bazei de date.
Sistemul SGBD nu va efectua în baza de date nici o modificare care încalcă una din restricții.
La formarea instrucțiunilor LMD, trebuie să se țină seama de următoarele aspecte referitoare la
restricțiile tabelului modificat:
• restricții de tip cheie primară . Atunci când se inserează un nou rând într-un tabel, cheia
primară a noului rând trebuie să fie unică în întregul tabel. Când se modifică valoarea unei
chei primare (ceea ce se întâmplă rareori), noua valoare trebuie să fie unică în întregul
tabel;
• restricții de unicitate . Ca și în cazul cheilor primare, coloanele pe care a fost definită o
restricție de unicitate trebuie sa aibă valori unice în întregul tabel;
• restricții NOT NULL . O valoare nula (NULL) este o modalitate specială prin care sistemul
SGBD tratează valoarea unei coloane pentru a indica faptul că valoarea coloanei
respective nu este cunoscută. O valoare nulă nu este același lucru cu un spațiu liber, un șir
vid sau valoarea zero – este o valoare specială care nu este egală cu nimic altceva.
În cazul instrucțiunilor INSERT, trebuie specificate valori pentru toate coloanele cu
restricții NOT NULL.
În cazul instrucțiunilor UPDATE nu se pot înlocui valorile unei coloane cu valori nule
dacă pe coloana respectivă este definită o restricție NOT NULL.
Dacă instrucțiunea LMD referă o vizualizare, nu poate fi folosită într-o instrucțiune
INSERT dacă una dintre coloanele tabelului cu o restricție NOT NULL (obligatorii)
lipsește din definirea vizualizării;
• restricții referențiale . Nu se poate insera sau actualiza valoarea unei chei externe decât dacă
există deja rândul părinte corespondent care conține valoarea cheii în coloana cheii
primare. În sens invers, nu se poate șterge un rând părinte dacă există rânduri subordonate
care referă valoarea din rândul părinte, decât dacă restricția a fost definită cu opțiunea
ON DELETE CASCADE . În general inserările în tabele trebuie făcute ierarhic (mai întâi
rândurile părinte, apoi rândurile copii), iar ștergerile trebuie făcute în ordine inversă
(copiii înaintea părinților);
• restrictii de verificare (CHECK). O instructiune INSERT sau UPDATE nu poate stoca într-o
coloană o valoare care încalcă o restricție CHECK definită pentru coloana respectivă.
30
Actualizarea datelor se referă la adăugarea unor noi rânduri într-o tabelă (cu instrucțiunea INSERT), la
modificarea valorilor uneia sau mai multor valori dintr-un rând (cu comanda UPDATE) și la ștergerea
unui rând dintr-o tabelă (cu comanda DELETE).
6.1. Interogarea datelor (Comanda SELECT)
Comanda fundamentală a standardului SQL este SELECT, aceasta permițând interogarea unei baze de
date.
Componentele interogării se numesc clause.
Sintaxa generală a comenzii SELECT este următoarea:
SELECT [ALL/DISTINCT/UNIQUE] listă de selecție
FROM listă de relații (tabele)
WHERE condiție de căutare asupra liniilor
GROUP BY listă de atribute care permit partiționarea
HAVING condiție asupra partițiilor
ORDER BY listă de atribute;
Clauzele SELECT și FROM sunt obligatorii. SELECT specifică datele care se selectează, iar clauza
FROM specifică relațiile din care se selectează. Restul clauzelor sunt opționale.
Exemplul 1: Să se selecteze toate produsele împreună cu toate datele acestora existente în baza de
date.
SELECT * FROM PRODUSE;
Exemplul 2: Să se selecteze toate produsele care expiră în data de 2017-07-03.
SELECT * FROM PRODUSE
WHERE data_expirarii=’2017-07-03’;
Interogarea datelor folosind operatorii IS și IS NOT
Exemplu: Să se selecteze numele tuturor clienților care au completată adresa, apoi să se afișeze
numele tuturor persoanelor care nu au numărul de telefon completat.
SELECT nume_client FROM CLIENTI
WHERE adresa_client IS NOT NULL;
SELECT nume_client FROM CLIENTI
WHERE telefon_client IS NULL;
Interogarea datelor folosind operatorii logici AND, OR, NOT
Sintaxa pentru interogarea care utilizează un operator logic este:
condiție1 AND condiție 2;
condiție1 OR condiție 2;
NOT condiție;
Exemplu: Să se determine numărul facturii și codul produselor pentru produsele cu o cantitate mai
mare de 300 și cu un preț unitar mai mare sau egal ca 100.
SELECT cod_produs, nr_factura FROM LINII_FACTURI
WHERE cantitate>’300’ AND pret_unitar>=’100’;
31
Interogarea datelor folosind operatorul IN
SELECT valoare_câmp IN (valoare1, valoare2,…);
Această sintaxă a operatorului IN este similară cu următoarea listă de disjuncții:
Valoare_câmp=valoare1 OR valoare_câmp=valoare2 OR …;
Exemplu: Să se selecteze numărul facturii și codul produselor pentru produsele cu prețul unitar de 70,
80, 90.
SELECT * FROM LINII_FACTURI
WHERE pret_unitar IN (70.00,80.00,90.00);
Interogarea datelor folosind DISTINCT
Pentru a selecta seturi de valori distincte, adică eliminarea valorilor duplicat, în SQL se folosește
sintaxa DISTINCT, micșorând astfel setul de date.
Sintaxa acestei comenzi este:
SELECT DISTINCT nume_câmp1, nume_câmp2,…
FROM nume_tabela
WHERE comenzi;
sau
SELECT DISTINCT *
FROM nume_tabela;
Sintaxa DISTINCT se referă la o înregistrare care poate cuprinde unul sau mai multe câmpuri.
Exemplu: Să se afișeze toate datele distincte despre produse.
SELECT DISTINCT denumire_produs FROM PRODUSE;
Interogarea datelor folosind operatorul LIKE
Se cunosc mai multe modalități de utilizare a expresiei LIKE, și anume:
• pentru o expresie care începe cu o anumită literă, de exemplu litera ‘A’: LIKE ‘A%’;
• pentru o expresie care se termină cu o anumită literă, de exemplu litera ‘A’: LIKE ‘%A’;
• pentru o expresie care include o anumită literă, de exemplu litera ‘A’: LIKE ‘%A%’;
Exemplu: Să se selecteze numele adresa și emailul tuturor persoanelor din București care au adresă de
email pe gmail sau personal.
SELECT nume_client, adresa_client, email_client
FROM CLIENTI
WHERE adresa_client LIKE ‘%BUCUREȘTI %’ AND
(email_client LIKE ‘%gmail%’ OR email_client LIKE’%personal%’);
Interogarea datelor folosind operatorul BETWEEN
Operatorul se utilizează în combinație cu două valori între care se află valoarea la care se referă
operatorul.
Sintaxa este:
val BETWEEN minim AND maxim;
sau
val>=min AND val<=max;
32
Cele trei expresii val, min, max pot fi de tip numeric (numeric, decimal, int, smalint etc.) sau de tip
dată calendaristică.
Exemplu: Să se selecteze codurile tuturor facturilor înregistrate în perioada 1 ianuarie 2017 și 1 mai
2017.
SELECT nr_factura
FROM FACTURI
WHERE data_factura BETWEEN ‘2017-01-01’ AND ‘2017-05-01’;
Interogarea datelor folosind funcțiile calendaristice YEAR, DAY, MONTH
Funcțiile YEAR, DAY, MONTH rețin dintr-un câmp de tip dată calendaristică anul, ziua, respectiv luna.
Exemplu: Să se vizualizeze codurile tuturor facturilor înregistrate în luna mai.
SELECT nr_factura
FROM FACTURI
WHERE MONTH(data_factura)=05;
Interogarea datelor folosind ordonarea
Datele se pot ordona după orice câmp. Ordonarea se poate face atât crescător cât și descrescător.
Sintaxa pentru interogarea
• ordonată crescător este:
ORDER BY nume_câmp (ASC);
• ordonată descrescător este:
ORDER BY nume_câmp (DESC);
Dacă ORDER BY nu este urmat de ASC sau DESC, ordonarea se face implicit crescător.
Exemplu: Să se vizualizeze lista clienților în ordine alfabetică.
SELECT nume_client
FROM CLIENTI
ORDER BY nume_client;
Interogarea datelor din mai multe tabele
Atunci când este necesară obținerea de informații din mai multe tabele se utilizează condiții de join. În
acest fel liniile dintr-un tabel pot fi puse în legătură cu cele din alt tabel conform valorilor comune ale
unor coloane. Interogarea datelor din mai multe relații este strâns legată de noțiunea de cheie primară,
cheie secundară, restricții de integritate, asocieri între relații.
Exemplu: Să se afișeze facturile, numele și adresa clienților corespunzătoare facturilor.
SELECT FACTURI.nr_factura, CLIENTI.nume_client,
CLIENTI.adresa_client
FROM FACTURI, CLIENTI
WHERE FACTURI.nume_client = CLIENTI.nume_client;
Observații:
• Atunci când în clauza FROM a unei comenzi SELECT apar mai multe tabele se realizează
produsul cartezian al acestora. De aceea numărul de linii rezultat crește considerabil, fiind
necesară restricționarea acestora cu o clauza WHERE. Se utilizează sintaxa:
nume_tabel.nume_câmp
Clauza FROM specifică două relații ( FACTURI și CLIENTI ).
33
Clauza SELECT cuprinde valori din relația FACTURI și din relația CLIENTI , prin
urmare trebuie definite câmpurile în funcție de tabela din care fac parte.
• Clauza WHERE include condiții care exprimă o egalitate între valorile identificatorului
nume_câmp a relației nume_tabel și a celei ale referinței la acest identificator în tabela
referită.
Tipuri de asocieri pentru relații
Rolul unei relații este de a modela entități, între relații există aceleași tipuri de asocieri ca și între
entități, și anume asocieri una-la-una, una-la-multe, multe-la-multe .
• Asocieri de la una-la-una
Două relații stochează informații în asocierea una-la-una dacă unei înregistrări din relația A îi
corespunde (sau nu) o singură înregistrare din B.
Acest tip de asociere este utilizată mai rar. Există, totuși, cazuri în care este necesară și utilă stabilirea
unei astfel de relații.
Exemplu:
Asociere de tip 1:1
• Asocieri de la una-la-multe
O relație A se află într-o asociere de una-la-multe cu o relație B dacă fiecărei înregistrări din A îi
corespund una sau mai multe înregistrări din relația B. Unei înregistrări din relația B nu îi corespunde
decât maxim o înregistrare din relația A.
Sunt utilizate următoarele denumiri:
• B este relația copil, sau relația care referă la A, sau relația cheie străină;
• A este relația părinte (master), sau relația referită, sau relația cheie primară.
Exemplu:
Asociere de tip 1:M
Observație: Relația A are cheia primară „ nr_factura”, iar relația B are atributul „ cod_produs” cheie
externă.
LINII_FACTURI
nr_factura#
cod_produs#
cantitate
pret_unitar
PRODUSE
cod_produs#
denumire_produs
unitate_de_masura
data_expirarii
FACTURI
nr_factura#
data_factura
nume_client
delegat
LINII_FACTURI
nr_factura#
cod_produs#
cantitate
pret_unitar
34
• Asocieri de la mai multe-la-multe
O relație A se află în asociere de tipul multe-la-multe cu o relație B dacă unei înregistrări din relația A
îi pot corespunde mai multe înregistrări din relația B și unei înregistrări din relația B îi pot corespunde
mai multe înregistrări din relația A.
O asociere N la M nu se definește direct, asocierea construindu-se cu ajutorul unei relații de joncțiune.
În această relație se păstrează legătura între cele două relații, precum și informațiile necesare.
Exemplu:
A 1:M B 1:M C
Asociere de tip N:M
Observație: În exemplul de mai sus, relația LINII_FACTURI realizează joncțiunea între relațiile
FACTURI și PRODUSE, stocând informațiile privind nr_factura, cod_produs, cantitate, pret_unitar
etc.
Astfel, asocierea N la M este vizualizată sub forma a două relații de 1 la M.
Interogarea datelor din mai multe relații folosind aliasuri (sau pseudonime)
Un alias este o redenumire fie a unui câmp, fie a unei relații. Aliasurile sunt utilizate la eliminarea
rescrierii complete a denumirii unei relații sau a unui câmp, redenumindu-le într-un mod simplificat.
Sintaxa utilizată este:
nume_relație/camp AS nume_nou;
sau
nume_relație/camp nume_nou;
Există posibilitatea de a utiliza aliasuri pentru tabelele din clauza FROM și utilizarea lor în cadrul
comenzii SELECT respective (alias.coloana). Această identificare (prin 'tabel.coloana' sau
'alias.coloana') este obligatorie atunci când se face referință la o coloană ce apare în mai mult de un
tabel din clauza FROM.
Exemplu: Să se afișeze facturile, numele și adresa clienților corespunzătoare facturilor, folosind
aliasuri.
SELECT F.nr_factura, C.nume_client, C.adresa_client
FROM FACTURI F, CLIENTI C
WHERE F.nume_client = C.nume_client;
Observație: În cazul în care un atribut apare doar într-o relație dintre cele menționate în listă, nu este
obligatorie precizarea relației (adică a aliasului) din care face parte atributul respectiv.
Interogarea datelor din mai multe relații folosind tipuri de asocieri
Tipurile de asocieri utilizate în interogarea mai multor relații sunt: FACTURI
nr_factura#
data_factura
nume_client
delegat
LINII_FACTURI
nr_factura#
cod_produs#
cantitate
pret_unitar
PRODUSE
cod_produs#
denumire_produs
unitate_de_masura
data_expirarii
35
1) INNER JOIN (joncțiunea internă);
2) LEFT OUTER JOIN (semijoncțiunea la stânga);
3) RIGHT OUTER JOIN (semijoncțiunea la dreapta);
1) INNER JOIN (joncțiunea internă) . Sintaxa
SELECT …FROM tabel_A INNER JOIN tabel_B ON (condiții de join)
selectează toate informațiile din relațiile A și B care corespund condițiilor de asociere.
Exemplu: Selectați numărul facturii și denumirea produselor fiecărei facturi folosind operația de join,
apoi utilizând clauza WHERE.
SELECT F.nr_factura, P.cod_produs, P.denumire_produs
FROM LINII_FACTURI F INNER JOIN PRODUSE P
ON (F.cod_produs =P.cod_produs);
SELECT F.nr_factura, P.denumire_produs
FROM LINII_FACTURI F, PRODUSE P
WHERE F.cod_produs =P.cod_produs;
Observații:
• Rezultatul este același. Valorile NULL vor fi ignorate;
• Sintaxei SELECT-FROM-INNER JOIN i se pot adăuga și alte condiții, neincluse în condițiile
de join, dacă acestea se referă la alte câmpuri decât cele care participă la join.
2) LEFT OUTER JOIN (semijoncțiunea la stânga). Sintaxa
SELECT …FROM tabel_A LEFT OUTER JOIN tabel_B
ON (condiții de join)
selectează toate informațiile din A, pe care le completează cu informații din B, în măsura în care
satisfac condițiile de join; acolo unde nu vor exista informații din B, acestea vor fi completate cu
NULL.
Exemplu: Selectați toate facturile. Dacă există informații despre aceste facturi, afișați și aceste
informații.
SELECT *
FROM LINII_FACTURI F LEFT OUTER JOIN PRODUSE P
ON (F.cod_produs =P.cod_produs);
Observație: Ordinea în care se scrie denumirea relației în sintaxa LEFT OUTER JOIN este foarte
importantă. Astfel, relația din stânga este relația primară, adică relația pentru care se dorește returnarea
tuturor informațiilor; relația din dreapta este relația secundară, adică informațiile din ea sunt necesare
doar în măsura în care se potrivesc condițiilor de asociere. Astfel se explică și denumirea de asociere
de la stânga spre exterior.
3) RIGHT OUTER JOIN (semijoncțiunea la dreapta). Sintaxa
SELECT …FROM tabel_A RIGHT OUTER JOIN tabel_B
ON (condiții de join)
selectează toate informațiile din B, pe care le completează cu informații din A, în măsura în care
satisfac condițiile de join; acolo unde nu vor exista informații din A, acestea vor fi completate cu
NULL.
36
Exemplu: Selectați toate facturile și produsele corespunzătoare, inclusiv facturile fără niciun produs.
SELECT *
FROM PRODUSE P RIGHT OUTER JOIN LINII_FACTURI F
ON (P.cod_produs =F.cod_produs);
Observație: Sintaxa RIGHT OUTER JOIN este utilizată mai rar; de obicei se utilizează sintaxa LEFT
OUTER JOIN.
Interogarea datelor din mai multe relații folosind instrucțiunea UNION
Sintaxa interogării datelor din mai multe relații folosind instrucțiunea UNION este:
SELECT Câmp 1, Câmp 2, …, Câmp n
FROM Tabel 1
UNION (ALL)
SELECT Câmp 1A, Câmp 2A,…, Câmp nA
FROM Tabel 2
și returnează înregistrări distincte dacă este folosită instrucțiunea UNION, și toate înregistrările dacă
se folosește UNION ALL. Astfel operatorul UNION elimină duplicatele, iar UNION ALL
vizualizează toate înregistrările, inclusiv duplicatele.
Pentru a utiliza această interogare, trebuie să se țină seama de două cerințe: domeniile Câmp 1A, Câmp
2A,…, Câmp nA și Câmp 1, Câmp 2, …, Câmp n trebuie să fie respectiv aceleași și, numărul de
câmpuri din fiecare interogare trebuie să coincidă.
Operatorul UNION se folosește atunci când între relații nu există o asociere directă.
Interogarea datelor mai multor relații folosind operatorul de concatenare a două șiruri de caractere
Rolul operatorului de concatenare a două șiruri de caractere este de a uni două șiruri de caractere într-
unul singur. Este utilizat în toate SGBD-urile, cu mici modificări ale simbolului: în SQL se folosește
simbolul ‚+’, în Oracle simbolul ‚||’ etc.
Se pot concatena o constantă cu un câmp, sau două câmpuri. Câmpurile trebuie să fie de tip text.
Sintaxa pentru concatenarea a două câmpuri este
CONCAT(Câmp1, Câmp2)
sau inserând virgula, spațiu sau oricare marcaj de delimitare
CONCAT(Câmp1,’,’, Câmp2)
sau
CONCAT(Câmp1,’ ’, Câmp2)
concatenează cele două constante într-una singură ’ Câmp1 Câmp2’.
Observație: Concatenarea prezintă dezavantajul afișării câmpurilor NULL.
Interogarea datelor folosind funcțiile totalizatoare:
• MAX
• MIN
• COUNT
• SUM
• AVG
37
• Interogarea datelor folosind funcția MAX
Sintaxa:
SELECT MAX (Nume_câmp) FROM Nume_tabela
returnează un număr egal cu valoarea maximă a câmpului Nume_câmp din relația Nume_tabela,
valorile NULL fiind ignorate.
Exemplu: Selectați cea mai recentă factură din tabela FACTURI, fără a da un nume rezultatului, apoi
cu nume pentru câmpul rezultat.
SELECT MAX (data_factura) FROM FACTURI ;
SELECT MAX (data_factura) AS data_ultimei_înregistrari
FROM FACTURI ;
• Interogarea datelor folosind funcția MIN
Funcția MIN este o funcție similară cu funcția MAX, cu ajutorul căreia se poate determina valoarea
cea mai mică dintr-un câmp.
Atât funcția MIN cât și funcția MAX se pot aplica doar pentru tipurile de date numeric sau dată
calendaristică.
• Interogarea datelor folosind funcția COUNT
Sintaxa:
• SELECT COUNT (*) FROM Nume_tabela – returnează un număr egal cu numărul de
înregistrări ale tabelei Nume_tabela;
• SELECT COUNT (Nume_câmp) FROM Nume_tabela – returnează un număr egal cu
numărul de valori nenule ale câmpului Nume_câmp din tabela Nume_tabela. Sunt
ignorate valorile NULL;
• SELECT COUNT (DISTINCT Nume_câmp) FROM Nume_tabela – returnează un
număr egal cu numărul de valori distincte nenule ale câmpului Nume_câmp din tabela
Nume_tabela. Sunt ignorate valorile NULL.
Exemplu: Precizați numărul de produse.
SELECT COUNT (cod_produs) AS nr_produselor FROM PRODUSE;
• Interogarea datelor folosind funcția SUM
Sintaxa:
• SELECT SUM (Nume_câmp) FROM Nume_tabela – returnează un număr egal cu
suma tuturor valorilor câmpului Nume_câmp din relația Nume_Tabela. Sunt ignorate
valorile NULL;
• SUM (DISTINCT Nume_câmp) FROM Nume_tabela – returnează un număr egal cu
suma valorilor distincte ale câmpului Nume_câmp din relația Nume_Tabela.
Funcția SUM se aplică acelor câmpuri care au domeniul de valori de tipul FLOAT, DECIMAL,
NUMERIC, INT etc. și nu are sens pentru câmpuri de tip text.
Exemplu: Precizați suma tuturor încasărilor existente pe facturile emise.
SELECT SUM (DISTINCT total_valoare_factura) FROM FACTURI;
38
• Interogarea datelor folosind funcția AVG
Sintaxa:
AVG (nume_câmp) FROM Nume_tabela
returnează un număr egal cu media aritmetică a tuturor valorilor câmpului Nume_câmp din relația
Nume_tabela. Valorile NULL sunt ignorate.
Funcția AVG se utilizează doar pentru date de tip numeric: INT, FLOAT, NUMERIC.
Exemplu: Selectați media prețurilor produselor.
SELECT AVG (pret_unitar) FROM LINII_FACTURI;
Interogarea datelor folosind instrucțiunea GROUP BY
Prin instrucțiunea GROUP BY se grupează datele după fiecare produs în parte.
Exemplu: Selectați fiecare produs în parte grupându-le crescător și precizați cantitatea vândută din
fiecare tip.
SELECT P.denumire_produs, SUM(F.cantitate) AS suma
FROM PRODUSE P, LINII_FACTURI F
WHERE F.cod_produs= P.cod_produs
GROUP BY P.cod_produs;
Observație: Menționarea clauzelor SELECT, FROM, WHERE, GROUP BY, ORDER BY în această
ordine este obligatorie. Greșeala frecventă care duce la apariția unor mesaje de eroare este aceea a
introducerii unor câmpuri după care se grupează în clauza SELECT și neintroducerea lor în clauza
GROUP BY.
Funcțiile de agregare se pot folosi ca extensii ale clauzei GROUP BY:
• ROLLUP – permite instrucțiunii SELECT să calculeze niveluri de subtotal multiple peste un
grup de dimensiuni;
• CUBE – generează toate subtotalurile care pot fi calculate dintr-un CUBE pe dimensiunile
specificate;
• GROUPING;
• GROUPING SET.
Interogarea datelor folosind instrucțiunea HAVING
Instrucțiunea HAVING se utilizează numai în combinație cu instrucțiunea GROUP BY. Clauza
HAVING este utilizată când se dorește filtrarea datelor grupate conform unor criterii. Aceste criterii
presupun compararea unor valori obținute prin apelarea unor funcții totalizatoare. Aceste tipuri de
comparări presupun gruparea datelor. Din această cauză, HAVING cere obligatoriu clauza GROUP
BY.
Exemplu: Selectați produsele grupate după cod care au prețul unitar cuprins între 500 și 3000.
SELECT P.denumire_produs, P.cod_produs, F.pret_unitar
FROM PRODUSE P, LINII_FACTURI F
WHERE F.cod_produs= P.cod_produs
GROUP BY P.cod_produs
HAVING F.PRET_UNIRAR BETWEEN 500 AND 3000;
39
6.2. Adăugarea de noi tupluri (Comanda INSERT)
În vederea adăugării unor rânduri noi într-o tabelă sau într-o vizualizare se utilizează comanda
INSERT. Instrucțiunea are două forme de bază: una în care valorile coloanelor sunt specificate chiar în
instrucțiune și alta în care valorile sunt selectate dintr-un tabel sau o vizualizare, folosind o
subinterogare.
Inserarea unui singur rând de date folosind clauza Values
Instrucțiunea INSERT care folosește o clauză VALUES poate crea un singur rând la fiecare rulare,
deoarece valorile pentru rândul de date respectiv sunt specificate chiar în instrucțiune.
Sintaxa generală a instrucțiunii este următoarea:
INSERT INTO nume_tabel_sau_vizualizare[(lista_de_coloane)]
VALUES (lista_de_valori);
sau
INSERT INTO nume_tabel/nume_view [(col1[, col2[,…]])]
VALUES (expresia1[, expresia2[,…]]) / subcerere;
• expresia1, expresia2,… reprezintă expresii a căror evaluare este atribuită coloanelor precizate
(se inserează o linie);
• subcerere, reprezintă o interogare (se inserează una sau mai multe linii).
Exemplu:
INSERT INTO PRODUSE (cod_produs, denumire_produs,
unitate_de_masura, data_expirarii)
VALUES (50, ‘PAINE’, ‘KG’,’2017-05-15’);
Clauza VALUES specifică valorile ce vor fi introduse în tabel sau vizualizare. Pentru a insera mai
multe linii prin aceeași instrucțiune INSERT, în locul acestei clauze se va preciza o subcerere.
Dacă nu se mai cunoaște ordinea de declarare a coloanelor se folosește comanda DESCRIBE care va
afișa lista coloanelor definite pentru tabela respectivă, tipul și lungimea lor.
Se pot remarcă următoarele:
• lista de coloane este opțională, dar dacă este inclusă trebuie să fie încadrată între paranteze
rotunde;
• dacă lista de coloane este omisă, trebuie specificată o valoare pentru fiecare coloană din tabel,
în ordinea în care sunt definite coloanele în tabel. Este bine ca întotdeauna să se includă
lista de coloane, deoarece omiterea acesteia face ca instrucțiunea INSERT să fie
dependentă de definiția tabelului. Dacă o coloană este modificată sau în tabel este
adăugată o nouă coloană, chiar și opțională, probabil instrucțiunea INSERT va eșua la
următoarea rulare;
• dacă lista de coloane este specificată, lista de valori trebuie să conțină o valoare pentru fiecare
coloană din listă, în aceeași ordine. Cu alte cuvinte, între lista de coloane și lista de valori
trebuie să existe o corespondență una-la-una . Orice coloană care lipsește din listă va primi
o valoare nulă, presupunund că valorile nule sunt acceptate în coloana respectivă;
• în clauza VALUES, valorile de tip caracter și dată calendaristică trebuie incluse între
apostrofuri. Nu se recomandă includerea între apostrofuri a valorilor numerice, întrucât
aceasta ar determina conversii implicite la tipul NUMBER;
• pentru introducerea de valori speciale în tabel pot fi utilizate funcții;
• adăugarea unei linii care va conține valori NULL se poate realiza în mod implicit, prin
omiterea numelui coloanei din lista de coloane. Exemplu:
40
INSERT INTO PRODUSE (cod_produs, denumire_produs,
unitate_de_masura)
VALUES (50, ‘PAINE’, ‘KG’);
sau explicit, prin specificarea în lista de valori a cuvântului cheie NULL sau a șirului vid (‘’)
în cazul șirurilor de caractere sau datelor calendaristice. Exemplu:
INSERT INTO PRODUSE (cod_produs, denumire_produs,
unitate_de_masura, data_expirarii)
VALUES (50, ‘PAINE’, ‘KG’, NULL);
Inserări masive folosind instrucțiunea SELECT internă
Așa cum se observă, este nevoie de foarte mult cod pentru a insera în tabel un singur rând de date
folosind o instrucțiune INSERT cu clauza VALUES.
O altă soluție, care poate fi folosită pentru a insera rânduri multiple într-un tabel, este forma care
folosește o instrucțiune SELECT internă. Această formă este utilă și pentru stabilirea următoarei valori
disponibile pentru o cheie primară cu valori secvențiale. De asemenea, poate fi folosită atunci când se
creează un tabel temporar pentru testare, care va fi populat cu toate datele dintr-un alt tabel.
Sintaxa generală a instrucțiunii este:
INSERT INTO nume_tabel_sau_vizualizare[(lista_de_coloane)]
SELECT instructiune_select;
Se remarcă următoarele:
• lista de coloane este opțională, dar dacă este inclusă trebuie să fie încadrată între paranteze
rotunde;
• dacă lista de coloane este omisă, instrucțiunea SELECT internă trebuie să furnizeze o valoare
pentru fiecare coloană din tabel, în ordinea în care sunt definite coloanele în tabel. Este
bine ca întotdeauna să se includă lista de coloane, deoarece omiterea acesteia face ca
instrucțiunea INSERT să fie dependentă de definiția tabelului. Dacă o coloană este
modificată sau în tabel este adăugată o nouă coloană, chiar și opțională, probabil
instrucțiunea INSERT va eșua la următoarea rulare;
• dacă lista de coloane este specificată, instrucțiunea SELECT internă trebuie să furnizeze o
valoare pentru fiecare coloană din lista de valori, în aceeași ordine. Cu alte cuvinte, între
lista de coloane și setul de rezultate al instrucțiunii SELECT trebuie să existe o
corespondență una-la-una. Orice coloană care lipsește din listă va primi o valoare nulă,
presupunând că valorile nule sunt acceptate în coloana respectivă;
• cuvântul cheie NULL poate fi folosit în instrucțiunea SELECT pentru specificarea unei valori
nule pentru o coloană.
Inserarea unor valori specifice de tip dată calendaristică
Formatul DD-MON-YY este de obicei folosit pentru a insera o valoare de tip dată calendaristică. Cu
acest format, secolul este implicit cel curent. Deoarece data conține de asemenea informații despre
timp, ora implicită este 00:00:00.
Dacă o dată calendaristică necesită specificarea altui secol sau oră, trebuie folosită funcția TO_DATE .
Exemplu:
INSERT INTO PRODUSE (cod_produs, denumire_produs,
unitate_de_masura, data_expirarii)
VALUES (50, ‘PAINE’, ‘KG’, TO_DATE(‘FEB-3 , 2017’, 'MON-DD,
YYYY’));
41
Inserare de valori folosind variabile de substituție
• Crearea unui script interactiv prin folosirea variabilelor de substituție SQL*Plus
Exemplu:
INSERT INTO PRODUSE (cod_produs, denumire_produs, unitate_de_masura)
VALUES (‘&cod_produs’, ‘&denumire_produs’, ‘&unitate_de_masura’)
• Crearea unui script pentru manipularea datelor
Comanda împreună cu variabilele de substituție pot fi salvate într-un fișier și acesta poate fi executat.
De fiecare dată când se execută fișierul sunt cerute valori noi pentru variabile. Prin intermediul
comenzii SQL*Plus ACCEPT, mesajele afișate la cererea introducerii valorilor pot fi modificate.
• ACCEPT – memoreaza valoarea într-o variabilă;
• PROMPT – afișează textul specificat.
Exemplu:
ACCEPT cod_produs PROMPT 'Introduceti codul produsului:'
ACCEPT denumire_produs PROMPT 'Introduceti denumirea produsului:'
INSERT INTO PRODUSE (cod_produs, denumire_produs)
VALUES (‘&cod_produs’, ‘&denumire_produs’)
Exemplul înregistrează informația pentru un produs, în tabelul PRODUSE. Utilizatorului îi sunt cerute
codul produsului și denumirea produsului, folosind mesajele de prompt stabilite în ACCEPT.
Parametrul de substitutie SQL*Plus nu trebuie precedat de & când este referit într-o comanda
ACCEPT. Pentru a continua o comandă SQL*PLUS pe linia următoare se folosește o linie (-).
Copierea înregistrarilor dintr-un alt tabel
Comanda INSERT poate fi folosită pentru a adăuga înregistrări într-un tabel, valorile pentru câmpuri
fiind extrase dintr-un tabel existent. Pentru aceasta se folosește, în locul clauzei VALUES, o
subinterogare (Nu se mai folosește clauza VALUES).
Sintaxa este următoarea:
INSERT INTO nume_tabel [ coloana ( , coloana ) ]
Subinterogare;
Numărul și tipul câmpurilor (coloanelor) din lista specificată în comanda INSERT trebuie să
corespundă numărului și tipului valorilor din subinterogare.
Exemplu:
INSERT INTO PRODUSE (cod_produs, denumire_produs,
unitate_de_masura)
SELECT cod_produs, denumire_produs, unitate_de_masura
FROM PRODUSE
WHERE denumire_produs = 'PAINE';
Inserări multitabel
O inserare multitabel presupune introducerea de linii calculate pe baza rezultatelor unei subcereri, în
unul sau mai multe tabele. Acest tip de inserare, introdus de Oracle, este util în mediul data
warehouse. Astfel, datele extrase dintr-un sistem sursă, pot fi transformate utilizând instrucțiuni
INSERT multitabel, spre a fi încărcate în obiectele bazei de date.
42
Sintaxa clauzei inserare_multi_tabel este următoarea:
INSERT ALL INTO …[VALUES…] [INTO…[VALUES…] …]
| inserare_condiționată| subcerere
Clauza inserare_condiționată are forma următoare:
[ALL | FIRST]
WHEN condiție THEN INTO…[VALUES…]
[INTO…[VALUES…] …]
[WHEN condiție THEN INTO…[VALUES…]
[INTO…[VALUES…] …] …]
[ELSE INTO…[VALUES…]
[INTO…[VALUES…] …] …]
Pentru a efectua o inserare multitabel necondiționată, sistemul va executa câte o instrucțiune
INSERT…INTO pentru fiecare linie returnată de subcerere.
Utilizând clauza inserare_condiționată, decizia inserării unei linii depinde de condiția specificată prin
intermediul opțiunii WHEN. Expresiile prezente în aceste condiții trebuie să facă referință la coloane
returnate de subcerere. O instrucțiune de inserare multitabel poate conține maxim 127 clauze WHEN.
Specificarea opțiunii ALL determină evaluarea tuturor condițiilor din clauzele WHEN. Pentru cele a
căror valoare este TRUE, se inserează înregistrarea specificată în opțiunea INTO corespunzătoare.
Opțiunea FIRST determină inserarea corespunzătoare primei clauze WHEN a cărei condiție este
evaluată TRUE. Toate celelalte clauze WHEN sunt ignorate.
Dacă nici o condiție din clauzele WHEN nu este TRUE, atunci sistemul execută clauza INTO
corespunzătoare opțiunii ELSE, iar dacă aceasta nu există, nu efectuează nici o acțiune.
Inserările multitabel pot fi efectuate numai asupra tabelelor, nu și asupra vizualizărilor. De asemenea,
acest tip de inserare nu se poate efectua asupra tabelelor distante. Subcererea dintr-o instrucțiune
corespunzătoare unei inserări multitabel nu poate utiliza o secvență.
6.3. Modificarea tuplurilor din tabele (Comanda UPDATE)
În funcție de momentul în care se dorește realizarea modificărilor asupra bazei de date, utilizatorul
poate folosi una din următoarele comenzi:
• SET AUTOCOMMIT IMM[EDIATE] (schimbările se efectuează imediat);
• SET AUTOCOMMIT OFF (schimbările sunt păstrate într-un buffer).
La execuția comenzii COMMIT se permanentizează schimbările efectuate, iar la execuția comenzii
ROLLBACK se renunță la schimbările realizate.
Instrucțiunea UPDATE este folosită pentru actualizarea datelor din coloanele unui tabel (sau ale unei
vizualizări).
Valorile câmpurilor care trebuie modificate pot fi furnizate explicit sau pot fi obținute în urma unei
cereri SQL.
Sintaxa generală a instrucțiunii UPDATE:
43
UPDATE nume_tabel_sau_vizualizare
SET nume_coloana = expresie[,nume_coloana = expresie…]
[ WHERE conditie];
Sau mai general, expresia poate fi o cerere:
UPDATE nume_tabel_sau_vizualizare
SET (coloana1[,coloana2[,…]]) = (subinterogare) / coloana =
exprresie /(interogare)
[WHERE conditie]
Dacă este nevoie, se pot modifica mai multe înregistrări simultan.
Observații:
• Pentru a se putea executa instrucțiunea UPDATE, utilizatorul care o lansează în execuție
trebuie să aibă acest privilegiu;
• Dacă nu este specificată clauza WHERE se vor modifica toate liniile;
• Cererea trebuie să furnizeze un număr de valori corespunzător numărului de coloane din
paranteza care precede caracterul de egalitate.
Se remarcă următoarele:
• clauza SET conține o listă cu una sau mai multe coloane, împreună cu o expresie care
specifică noua valoare pentru fiecare coloană. Aceasta este o listă de perechi
(NUME,VALOARE), separate prin virgule, cu un operator de egalitate între fiecare
NUME și VALOARE;
• expresia poate fi o constantă, un alt nume de coloană sau orice altă expresie pe care SQL o
poate transforma într-o singură valoare, care poate fi apoi atribuită coloanei respective;
• clauza WHERE conține o expresie care limitează rândurile actualizate. Dacă această clauză
este omisă, motorul SQL va încerca să actualizeze toate rândurile din tabel sau din
vizualizare.
Oracle permite utilizarea valorii implicite DEFAULT în comenzile INSERT și UPDATE. Unei
coloane i se atribuie valoarea implicită definită la crearea sau modificarea structurii tabelului dacă:
• nu se precizează nici o valoare;
• dacă se precizează cuvântul cheie DEFAULT în comenzile INSERT sau UPDATE.
Dacă nu este definită nici o valoare implicită pentru coloana respectivă, sistemul îi atribuie valoarea
NULL. Cuvântul cheie DEFAULT nu poate fi specificat la actualizarea vizualizărilor.
Cazurile în care instrucțiunea UPDATE nu poate fi executată sunt similare celor în care eșuează
instrucțiunea INSERT.
Notă: În general, se folosește cheia primară pentru a identifica o singură înregistrare. Folosirea altor
coloane poate determina modificarea mai multor înregistrări.
De exemplu, identificarea unei singure înregistrări în tabelul PRODUSE prin denumire poate fi
periculoasă, deoarece pot exista mai multe produse cu aceeași denumire.
Comanda UPDATE modifică anumite înregistrări dacă este specificată clauza WHERE.
Exemplul următor mărește prețul unitar cu 25% pentru produsul cu codul 50.
UPDATE LINII_FACTURI
44
SET pret_unitar = pret_unitar * 1.25
WHERE cod_produs = 50;
Actualizarea înregistrarilor folosind subinterogări după mai multe câmpuri
Exemplul următor mărește prețul unitar cu 15% pentru produsele care au prețul unitar identic cu cel al
produsului cu codul 50.
UPDATE LINII_FACTURI
SET pret_unitar = pret_unitar * 1.15
WHERE pret_unitar IN
(SELECT pret_unitar
FROM LINII_FACTURI
WHERE cod_produs = 50);
Modificarea înregistrarilor folosind subinterogări după mai multe câmpuri (folosind valori dintr-un
alt tabel)
În clauza SET a unei comenzi UPDATE pot fi implementate subinterogări după mai multe câmpuri.
UPDATE nume_tabel
SET (coloana, coloana, …) =
( SELECT coloana, coloana,
FROM nume_tabel
WHERE conditie)
WHERE conditie;
Dacă se încercă atribuirea unei valori unui câmp care are legată de o constrângere de integritate, va
rezulta o eroare.
6.4. Ștergerea tuplurilor din tabele (Comanda DELETE)
Instrucțiunea DELETE șterge unul sau mai multe rânduri dintr-un tabel. Instrucțiunea poate să
folosească și o vizualizare, dar numai dacă aceasta se bazează pe un singur tabel (ceea ce înseamnă că
instrucțiunile DELETE nu pot fi folosite pentru vizualizări care conțin uniuni). În instrucțiunile
DELETE nu sunt referite niciodată coloane, doarece instrucțiunea șterge rânduri întregi de date,
inclusiv toate valorile datelor (toate coloanele) din rândurile afectate. Dacă se șterge o singură valoare
din rândurile existente, se folosește instrucțiunea UPDATE pentru a înlocui valorile respective cu
valori nule (presupunând că valorile nule sunt permise în acele coloane).
Sintaxa generală a instrucțiunii DELETE este:
DELETE FROM nume_tabel_sau_vizualizare [ AS alias]
[ WHERE conditie ];
Se remarcă următoarele:
• comanda DELETE nu șterge structura tabelului;
• clauza WHERE este opțională. Totuși, este folosită aproape întotdeauna, deoarece o
instrucțiune DELETE fără o clauză WHERE încearcă să șteargă toate rândurile din tabel.
În clauza WHERE pot fi folosite și subcereri;
• atunci când este inclusă, clauza WHERE specifică rândurile care urmează să fie șterse. Orice
rând pentru care condiția WHERE este evaluată ca adevărată este șters din tabel;
• nu se pot șterge rânduri dacă se încalcă o restricție referențială. În general, rândurile
subordonate trebuie șterse înaintea rândurilor părinte;
• pentru a șterge linii identificate cu ajutorul valorilor din alte tabele, se utilizează subcereri;
45
• comanda nu poate fi folosită pentru ștergerea valorilor unui câmp individual. Acest lucru se
poate realiza cu ajutorul comenzii UPDATE.
Dacă se încearcă ștergerea unei înregistrări care conține o valoare implicată într-o constrângere de
integritate, atunci va fi returnată o eroare.
În cazul în care constrângerea de integritate referențială a fost definită utilizând opțiunea ON DELETE
CASCADE, atunci instrucțiunea DELETE va șterge atât liniile indicate, cât și liniile „copil“ din
tabelele corespunzătoare.
Ștergerile accidentale pot fi omise, restaurându-se valorile inițiale prin comanda AUTOCOMMIT
OFF.
Exemplu: Stergeți toate produsele care expiră înainte de 1 Ianuarie, 2017. Ștergerile să nu fie efectuate
imediat ci ulterior.
SET AUTOCOMMIT OFF
DELETE FROM PRODUSE
WHERE data_expirarii > TO_DATE('01-01-17', 'DD-MM-YY');
Ștergerea înregistrărilor folosind valori dintr-un alt tabel
Pot fi folosite subinterogări pentru a șterge înregistrări dintr-un tabel, folosind informațiile din altul.
Exemplul de mai jos șterge toate produsele cu denumirea PAINE. Subinterogarea caută în tabelul
PRODUSE codul produsului PAINE, apoi furnizează codul interogării principale, care șterge
înregistrările din LINII_PRODUSE pe baza acestuia.
DELETE FROM LINII_PRODUSE
WHERE cod_produs =
( SELECT cod_produs
FROM PRODUSE
WHERE denumire_produs = 'PAINE');
Încălcarea constrângerii de integritate
Dacă se încerarcă ștergerea unei înregistrări care conține un câmp cu o valoare legată de o
constrângere de integritate, se obține o eroare.
În exemplul de mai jos se încearcă ștergerea produsului cu codul 10 din tabelul PRODUSE, dar
aceasta provoacă o eroare, deoarece codul produsului este folosit ca și cheie externă în tabelul
LINII_FACTURI.
Dacă înregistrarea părinte, care se încearcă să se șteargă, are înregistrări fii, atunci se primește un
mesaj de eroare: child record found violation ORA – 02292 .
DELETE FROM PRODUSE
WHERE cod_produs = 50;
7. Limbajul de control al datelor (LCD)
Limbajul pentru controlul datelor ( LCD – Data Control Language ) include instrucțiuni SQL care
permit administratorilor să controleze accesul la datele din baza de date și folosirea diferitelor
privilegii ale sistemului DBMS, cum ar fi privilegiul de oprire și pornire a bazei de date.
46
Controlul unei baze de date cu ajutorul SQL-ului se referă la:
• asigurarea confidentialității și securității datelor;
• organizarea fizică a datelor;
• realizarea unor performanțe;
• reluarea unor acțiuni în cazul unei defecțiuni;
• garantarea coerenței datelor în cazul prelucrării concurente.
Sistemul de gestiune trebuie:
• să pună la dispoziția unui număr mare de utilizatori o mulțime coerentă de date;
• să garanteze coerența datelor în cazul manipulării simultane de către diferiți utilizatori.
Comenzile de control la dispoziția administratorului (DBA) – GRANT, REVOKE – sunt utilizate pentru
a da sau a lua drepturi de acces (la comenzi LMD, deci la operarea unor modificări a bazei de date).
Sintaxa:
GRANT
[Privilegii]
ON
TO
Utilizator IDENTIFIED BY ’DenumireParola’;
REVOKE
[Privilegii]
ON
TO
Utilizator IDENTIFIED BY ’DenumireParola’;
7.1. Asigurarea confidențialității și securității datelor
Sistemul de gestiune trebuie să asigure securitatea fizică și logică a informației și să garanteze că
numai utilizatorii autorizați pot efectua operații corecte asupra bazei de date. Pentru acestea, există
mecanisme care permit identificarea și autentificarea utilizatorilor și există proceduri de acces
autorizat care depind de date și de utilizator, cum ar fi:
conturi pentru utilizatori, cu parolă folosită pentru autentificare;
grupuri, roluri, privilegiile și profilurile – acestea permit nu numai constrângeri ci și stabilirea
unei politici de securitate. Pentru a accesa un obiect, un utilizator trebuie să aibă privilegiile
necesare. Privilegiile pot fi acordate direct unui utilizator sau pot fi grupate în roluri, care la
rândul lor pot fi acordate utilizatorului.
Exemplu: Un forum de discuții are utilizatori grupați pe roluri ca: administrator, moderator,
membru. Fiecare rol poate avea privilegii diferite: administratorul poate configura baza de
date (modifică schema, adaugă tabele, configurează interfața); moderatorul poate valida,
modifica, șterge postările membrilor; membrii pot adăuga înregistrări.
7.2. Reluarea unor acțiuni în cazul unei defecțiuni
Reluarea unor acțiuni în cazul apariției unei defecțiuni hard sau soft presupune recuperarea ultimei
stări coerente a bazei de date. În funcție de defecțiunea care a determinat întreruperea lucrului,
restaurarea bazei de date se realizează automat de SGBD sau manual, adică necesită intervenție
umană.
47
Salvarea bazei de date se realizează conform unei strategii existând combinațiile:
copii ale bazei de date și copii ale jurnalelor acestora;
jurnale ale tranzacțiilor;
jurnale ale imaginii înregistrărilor din baza de date.
Copiile bazei de date – pot fi realizate automat de sistem la anumite intervale de timp sau la comanda
administratorului bazei de date, ori de câte ori este nevoie și de obicei pe un alt suport magnetic decât
cele pe care rezidă baza de date. Aceste copii pot fi utilizate doar în situația în care prelucrările
efectuate între momentul realizării copiilor și cel al apariției unei defecțiuni pot fi reluate. Acest lucru
este posibil doar dacă prelucrările sunt efectuate într-o secvență cunoscută iar timpul necesar pentru
reprocesarea nu este foarte mare. Durata mare de execuție pentru astfel de copii face ca anumite
SGBD-uri să recurgă la copii ale jurnalelor bazei de date. Volumul datelor care vor fi copiate în acest
caz va fi mai mic, iar procesul de restaurare va implica într-o măsură mai mică intervenția umană.
Jurnalul tranzacțiilor – este un fișier special întreținut de SGBD, în care sunt memorate informațiile
despre tranzacțiile efectuate asupra bazei de date, cum sunt:
identificatorul sau codul tranzacției;
momentul începerii execuției tranzacției;
numărul terminalului sau identificatorul utilizatorului care a inițiat tranzacția;
datele introduse;
înregistrările modificate și tipul modificării.
Jurnalul imaginilor înregistrărilor din baza de date – se deosebește de jurnalul tranzacțiilor prin aceea
că el nu conține descrierea operațiilor efectuate asupra bazei de date, ci efectul acestora.
7.3. Garantarea coerenței datelor în cazul prelucrării concurente
Sistemul de gestiune a bazelor de date asigură accesul concurent al mai multor utilizatori la baza de
date. Fiecare utilizator trebuie să aibă o vedere validă și consistentă asupra bazei de date, incluzând și
modificările făcute de alți utilizatori; în același timp, procesarea incorectă a datelor trebuie evitată,
pentru a nu afecta consistența datelor sau integritatea acestora.
În funcție de complexitatea operației de acces concurent, problema gestionării concurenței se
complică:
acces concurent a mai multor utilizatori numai pentru consultarea datelor;
acces concurent a mai multor utilizatori cu unul dintre ei modificând datele;
acces concurent a mai multor utilizatori cu mai mulți dintre ei modificând datele.
Pentru ultimele două, se utilizează blocarea datelor (primul utilizator care le accesează, le blochează).
Cu cât dimensiunea datelor blocate este mai mică, cu atât gestionarea accesului concurențial este mai
eficientă.
Coerența este asigurată cu ajutorul conceptului de tranzacție. Tranzacția este unitatea logică de lucru
constând din una sau mai multe instrucțiuni SQL, care trebuie să fie executate atomic (ori se execută
toate, ori nu se execută nici una!), asigurând astfel trecerea BD dintr-o stare coerentă în altă stare
coerentă. Dacă toate operațiile ce constituie tranzacția sunt executate și devin efective, spunem că
tranzacția este validată (COMMIT), iar modificările (INSERT, DELETE, UPDATE) aduse de
48
tranzacție devin definitive (modificările sunt înregistrate și sunt vizibile tuturor utilizatorilor). Din
acest punct prima instrucțiune SQL executabilă va genera automat începutul unei noi tranzacții.
Dacă dintr-un motiv sau altul (neverificarea condițiilor, accesul imposibil) o operație a tranzacției nu a
fost executată spunem că tranzacția a fost anulată (ROLLBACK). Modificările aduse de toate
operațiile tranzacției anulate sunt și ele anulate și se revine la starea bazei de date de dinaintea
tranzacției anulate. Executarea unei instrucțiuni ROLLBACK presupune terminarea tranzacției curente
și începerea unei noi tranzacții.
Este posibil ca o tranzacție să fie descompusă în subtranzacții, astfel încât dacă este necesar să se
anuleze doar parțial unele operații.
Controlul tranzacțiilor constă în:
• definirea începutului și sfârșitului unei tranzacții;
• validarea sau anularea acesteia;
• o eventuală descompunere în subtranzacții.
Limbajul pentru controlul datelor (LCD) permite salvarea informației, realizarea fizică a modificărilor
în baza de date, rezolvarea unor probleme de concurență.
Limbajul conține următoarele instrucțiuni:
• SET AUTO[COMMIT] {ON | OFF} – Dacă se folosește utilitarul SQL*Plus, există
posibilitatea ca după fiecare comandă LMD să aibă loc o permanentizare automată a datelor
(un COMMIT implicit).
Dacă este setată pe ON, fiecare comandă LMD individuală duce la salvarea modificărilor,
imediat ce este executată. Nu se mai poate reveni la situația dinainte (un rollback nu mai este
posibil).
Dacă este setată pe OFF, COMMIT poate fi dată explicit. De asemeni, COMMIT este
executată odată cu o comanda LDD sau la ieșirea din SQL*Plus;
• ROLLBACK [TO [SAVEPOINT] savepoint] – permite restaurarea unei stări
anterioare a bazei de date.
Dacă nu se specifică nici un SAVEPOINT, toate modificările făcute în tranzacția curentă sunt
anulate, iar dacă se specifică un anumit savepoint, atunci doar modificările de la acel
savepoint până în momentul respectiv sunt anulate;
• ROLLBACK TO SAVEPOINT name – șterge savepoint-ul și toate schimbările de după el
(temporare);
• SAVEPOINT name – folosită în conjuncție cu instrucțiunea ROLLBACK, pentru definirea
unor puncte de salvare în fluxul programului. Punctele de salvare pot fi considerate ca
niște etichete care referă o submulțime a schimbărilor dintr-o tranzacție, marcând efectiv
un punct de salvare pentru tranzacția curentă. În acest mod este posibilă împărțirea
tranzacției în subtranzacții.
Punctele de salvare nu sunt obiecte ale schemei, prin urmare, nu sunt referite în dicționarul
datelor. Server-ul Oracle implementează un punct de salvare implicit pe care îl mută
automat după ultima comandă LMD executată.
Dacă este creat un punct de salvare având același nume cu unul creat anterior, cel definit
anterior este șters automat.
SAVEPOINT savepoint;
Starea datelor înainte de COMMIT sau ROLLBACK este următoarea:
• starea anterioară a datelor poate fi recuperată;
• utilizatorul curent poate vizualiza rezultatele operațiilor LMD prin interogări asupra tabelelor;
49
• alți utilizatori nu pot vizualiza rezultatele comenzilor LMD făcute de utilizatorul curent (read
consistency);
• înregistrările (liniile) afectate sunt blocate și, prin urmare, alți utilizatori nu pot face schimbări
în datele acestor înregistrări.
Execuția unei comenzi COMMIT implică anumite modificări:
• toate schimbările (INSERT, DELETE, UPDATE) din baza de date făcute după anterioara
comandă COMMIT sau ROLLBACK sunt definitive. Comanda se referă numai la
schimbările făcute de utilizatorul care dă comanda COMMIT;
• toate punctele de salvare vor fi șterse;
• starea anterioară a datelor este pierdută definitiv;
• toți utilizatorii pot vizualiza rezultatele;
• blocările asupra liniilor afectate sunt eliberate; liniile pot fi folosite de alți utilizatori pentru a
face schimbări în date.
Execuția unei comenzi ROLLBACK implică anumite modificări:
• anulează tranzacția în curs și toate modificările de date făcute după ultima comandă
COMMIT;
• sunt eliberate blocările liniilor implicate;
• nu șterge un tabel creat prin CREATE TABLE. Eliminarea tabelului se poate realiza doar prin
comanda DROP TABLE.
8. Exerciții de fixare a noțiunilor
Procedura de instalare Oracle XE (Oracle Database 11g Express Edition) și
Apex (Oracle Application Express) pe Windows
Oracle XE este o versiune de bază de date ideală pentru training și inițiere în tehnologia bazelor de
date. Este gratuită și poate fi instalată pe orice mașina (fizică sau virtuală) singurele ei limitari fiind
cele legate de spațiul de stocare (poate folosi până la 11 GB), respectiv memorie și processor (până la
1 Gb RAM și 1 procesor/mașină). Suportul este asigurat pe Oracle Discussion Forum, o comunitate
monitorizată de Oracle și alți experți în domeniu. Pașii preliminari sunt:
1. Se descarcă Oracle XE în funcție de sistemul de operare pe care se va instala (32 bit/64 bit):
http://www.oracle.com/technetwork/database/database-technologies/express-
edition/downloads/index.html
2. Se descarcă Apex:
http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html
3. Se instalează Oracle XE, singurele input-uri așteptate de la utilizator fiind:
a. calea unde va fi instalat (default pe drive-ul C)
b. parola pentru administratorul bazei de date (sysdba)
Datorită faptului că Oracle XE conține o versiune mai veche de Apex, aceasta trebuie
upgradată cu versiunea descarcată la pasul 2. Pașii de upgrade sunt următorii:
50
1. Se dezarhivează versiunea descarcată la pasul 2 în directorul C:\apex
2. Se deschide un command prompt și se navighează în calea de mai sus:
cd C:\apex
3. Folosind SQLPLUS ne conectăm la baza de date instalată anterior:
{Command prompt} C:\apex> sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter Password: SYS_Password // parola de sysdba setată anterior
4. Se instalează Apex:
SQL> @apexins SYSAUX SYSAUX TEMP /i/
5. Se loghează din nou în SQL Plus și se execută comanda:
{Command prompt} C:\apex> sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter Password: SYS_Password
SQL> @apxldimg.sql C:\
6. Se introduce parola pentru apex-ul nou instalat:
SQL> @apxchpwd
Enter password for Application Express ADMIN account.
7. Pentru a accesa Apex-ul nou instalat se accesează link-ul:
http://localhost:8080/apex/apex_admin
în Username, enter ADMIN
în Password, enter the password entered în Step 6
51
ORACLE SQL DEVELOPER
Crearea unui user
– USER SQL
CREATE USER PROF IDENTIFIED BY 123456
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
– QUOTAS
– ROLES
GRANT "CONNECT" TO PROF WITH ADMIN OPTION;
GRANT "RESOURCE" TO PROF WITH ADMIN OPTION;
ALTER USER PROF DEFAULT ROLE "CONNECT","RESOURCE";
– SYSTEM PRIVILEGES
GRANT CREATE TRIGGER TO PROF WITH ADMIN OPTION;
GRANT CREATE TABLE TO PROF WITH ADMIN OPTION;
GRANT CREATE VIEW TO PROF WITH ADMIN OPTION;
GRANT CREATE SEQUENCE TO PROF WITH ADMIN OPTION;
GRANT CREATE SYNONYM TO PROF WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO PROF WITH ADMIN OPTION;
GRANT CREATE PROCEDURE TO PROF WITH ADMIN OPTION;
52
APLICAȚII REZOLVATE
Fie baza de date H-R cu diagrama reprezentată în figura următoare:
Să se creeze tabelele din figura de mai sus în care sa fie precizate restricțiile de integritate:
DDL (Data Definition Language)
Crearea tabelelor
create table prof.emp –contine angajatii unei firme
(empno number(6,0) constraint emp_empno_pk
primary key, –cheia primara
firstname varchar2(25),
lastname varchar2(25) constraint
emp_lastname_nn not null,
cnp varchar(13),
phone varchar2(20),
email char(50) constraint emp_email_nn not
null,
adr varchar2(60) default 'necunoscuta',
jobid varchar2(10) constraint emp_jobid_nn
not null,
hiredate date constraint
emp_hiredate_nn not null, –data angajarii pe postul actual
mgr number(6,0), –cod manager
sal number(9,2),
comm number(9,2), –comision
deptno number(4,0)
);
53
Vizualizarea campurilor
describe prof.emp;
create table prof.regions
(regionid number(4,0) constraint
regions_regionid_pk primary key,
region varchar2(40) –denumirea regiunii
);
describe prof.regions;
create table prof.countries
(countryid char(3) constraint
countries_countryid_pk primary key,
country varchar2(40), –denumire
regionid number(3,0) constraint
countries_regionid_fk
references
prof.regions (regionid) –cheie straina
);
describe prof.countries;
create table prof.locations
(locid number(4,0) constraint
locations_locid_pk primary key,
street varchar2(40),
postalcode varchar2 (12),
city varchar2(20) constraint
locations_city_nn not null,
stateprovince varchar2(25) default
'bucharest', –judetul
countryid char(3) constraint
locations_countryid_fk
references
prof.countries (countryid)
);
describe prof.locations;
create table prof.dept –descrie departamentele din care fac parte
angajatii
(deptno number(4,0) constraint dept_deptno_pk
primary key,
dname varchar2(40) constraint dept_dname_uk
unique, –cheie unica
locid number(4,0) constraint dept_locid_fk
references
prof.locations(locid)
);
describe prof.dept;
54
create table prof.jobs
(jobid varchar2(10) constraint jobs_jobid_pk
primary key,
job varchar2(40) constraint jobs_job_nn not
null, –denumire job
minsal number(9,2),
maxsal number(9,2)
);
describe prof.jobs;
create table prof.jobgrades
(gradelevel varchar2(3),
lowestsal number(9,2),
highestsal number(9,2)
);
describe prof.jobgrades;
create table prof.jobhistory
(empno number(6,0) constraint
jobhistory_empno_fk
references prof.emp(empno)
constraint
jobhistory_empno_nn not null,
startdate date constraint
jobhistory_startdate_nn not null,
enddate date constraint
jobhistory_enddate_nn not null,
jobid varchar2(10) constraint
jobhistory_jobid_fk
references
prof.jobs(jobid)
constraint
jobhistory_jobid_nn not null,
deptno number(4,0) constraint
jobhistory_deptno_fk
references
prof.dept(deptno)
);
describe prof.jobhistory;
Schema curenta
alter session set current_schema=prof;
Adaugarea constrangerilor
alter table emp
add constraint emp_deptno_fk foreign key(deptno)
references prof.dept(deptno);
alter table emp
add constraint emp_jobid_fk foreign key(jobid)
references prof.jobs(jobid);
55
DML (Data Manipulation Language): INSERT, UPDATE, DELETE
Introducerea valorilor in tabele
–introducerea valorilor in tabelul jobgrade
insert into jobgrades values('a',1000,2999);
…………….
select * from jobgrades;
– introducerea valorilor in tabelul job
insert into jobs values('ad_pres' , 'president',2000,40000);
……………………
select * from jobs;
– introducerea valorilor in tabelul region
insert into regions values (1,'europe');
……………………………
select * from regions;
– introducerea valorilor in tabelul countries
insert into countries values('ca','canada',2);
……………………………
select * from countries;
– introducerea valorilor in tabelul location
insert into locations values(1,'2014 jabberwocky
rd','26192','southlake','texas','us');
–sau
insert into prof.locations(locid, street, postalcode, city,
stateprovince, countryid) values (7,'ghica
13','o71','bucharest','bucharest','ro');
–sau
insert into prof.locations (locid,street,postalcode,city, countryid)
values(6,'fabricii 46g','o73','bucharest','ro');
…………….
select * from locations;
– introducerea valorilor in tabelul dept
insert into dept values(10,'adminstration',1);
…………………………………
select * from dept;
– sau
Inserare de valori folosind variabile de substitutie – ampersand unic (&)
Enter value for department_id: 120
Enter value for department_name: oracle
Enter value for location_id: 1
insert into dept(deptno, dname, locid) values (&department_id,
'&department_name',&location_id);
56
Crearea unui script pentru manipularea datelor
Prin intermediul comenzii sql*plus ACCEPT, mesajele afisate la cererea introducerii valorilor pot fi
modificate.
• accept – memoreaza valoarea intr-o variabila;
• prompt – afiseaza textul specificat.
accept deptno prompt 'Introduceti codul departamentului:'
accept dname prompt 'Introduceti denumirea departamentului:'
accept locin prompt 'Introduceti codul localitatii:'
insert into dept (deptno, dname, locid) values (&deptno, '&dname',
&locid);
select * from dept;
–sau
–sa se adauge in tabela dept datele pentru noul departament (cu
date introduse de la tastatura).
prompt Sa se adauge in dept datele pentru:
insert into dept (deptno, dname,locid)
values(&deptno,'&dname',&locid);
select* from dept;
– introducerea valorilor in tabelul emp
insert into emp values (7839, 'KINGA', 'R','1821110222233',
'0757000000', 'n.iacob@gmail.com', 'BUCURESTI', 'ad_pres',
to_date('20-01-2017','dd-mm-yyyy'), null, 1500, 300, 10);
insert into emp values (7900, 'JAMES','B', '1931117222233',
'0757022000', 'n.iacob.mi@spiruharet.ro', 'BUCURESTI','mk_rep',
to_date('30-10-2017','dd-mm-yyyy'), 7839, 8950, 2000, 10);
……………………
select * from emp;
– introducerea valorilor in tabelul jobhistory
insert into jobhistory values (7839,to_date('13-01-2005','dd-mm-
yyyy'),to_date('24-07-2017','dd-mm-yyyy'),'it_prog',10);
………………
select * from jobhistory;
Copierea inregistrarilor dintr-un alt tabel
Numarul si tipul campurilor (coloanelor) din lista specificata in comanda insert trebuie sa corespunda
numarului si tipului valorilor din subinterogare.
create table managers
(id number(4,0),
name varchar2(30),
salary number(7,2));
insert into managers (id, name, salary)
select empno, firstname, sal from emp
where mgr is null;
select * from managers;
57
Modificarea tabelelor
–adaugati o noua coloana tabelului emp care sa contina comentarii
alter table emp
add (comments varchar2(80));
describe prof.emp;
–modificati dimensiunea coloanei firstname la 30 si pe cea a salariului la 12 cu 3 zecimale
alter table emp
modify (firstname varchar2(30), sal number(12,3));
describe prof.emp;
–eliminati coloana comments din tabelul emp
alter table emp
drop column comments;
describe prof.emp;
–redenumiti tabelul emp cu ang
rename emp to ang;
describe prof.ang;
–eliminati tabelul ang
drop table ang;
Tipuri de constrangeri
Constrangerile de tip CHECK se pot implementa la nivel de coloana doar daca nu refera o alta
coloana a tabelului
–sa se adauge restrictia de validare: sal>0
alter table emp
add (constraint emp_sal_chk check(sal>0);
Constrangerea de tip NOT NULL se poate declara doar la nivel de coloana
Adaugarea unei chei primare (PRIMARY KEY)
alter table emp
add constraint emp_empno_pk primary key(empno);
Adaugati o constrangere de cheie externa (foreign key) pe deptno din emp
alter table emp
add constraint emp_deptno_fk foreign key(deptno)
references dept(deptno);
Adaugarea unei chei unice (UNIQUE)
alter table dept
add constraint dept_dname_uk unique(dname);
Instructiunnea SELECT
Sintaxa generala
select [all/distinct/unique] lista de selectie (coloane, alias)
from lista de relatii (tabele)
where conditie de cautare asupra liniilor
58
group by lista de atribute care permit partitionarea
having conditie asupra partitiilor
order by lista de atribute [asc|desc];
–afisati numele angajatilor
select firstname from emp;
–afisati codul, numele si salariul angajatilor
select empno, firstname, sal from emp;
–afisati date despre angajati
select * from emp;
Tipuri de operatori:
operatori aritmetici : +, -, * , …
operatori de comparatie : <, <=, >, >=, =, <>, between, in, is, like…
operatori logici : and, or, not
operatori relationali : union, intersec, minus
–afisati salariul anual
select empno, sal, sal * 12 from emp;
Definirea alias-urilor pentru coloane . Cuvantul cheie "as" poate lipsi!
–sa se afiseze codul angajatilor si salariul anual
select empno cod_ang, sal, sal*12 as sal_anual from emp;
Functia generala nvl (expr1, expr2) forteaza functiile grup sa includa valori nulle.
select firstname, sal, sal*12, comm, sal*12 + nvl(comm,0)
venituri_anuale from emp;
Operatorul de concatenare (||)
–sa se afiseze numele fiecarui angajat si codul sefului direct superior
select firstname ||' lucreaza pentru managerul cu codul: ' || mgr
angajat_sef from emp;
Randuri duplicate
select deptno from emp;
Eliminarea randurilor duplicate
select distinct deptno from emp;
Ordonarea inregsitrarilor
–afisati angajatii in ordine alfabetica
select firstname
from emp
order by firstname;
59
–sa se afiseze numele si salariul tuturor angajatilor. Rezultatele sa se afiseze crescator dupa
numarul departamentului si descrescator dupa salariu:
select deptno, firstname, sal
from emp
order by deptno, sal desc;
Sortarea dupa aliasul numelui coloanei
–afisati veniturile anuale in ordine descrescatoare
select firstname, sal*12 + nvl(comm,0) venituri_anuale
from emp
order by venituri_anuale desc;
Sortarea dupa pozitia coloanei
select empno, firstname, sal, sal*12
from emp
order by 2;
Clauza WHERE
–ulterior se poate adauga la linia de stare (o selectie explicita, prin introducerea codului
corespunzator managerului).
select *
from emp
where mgr=&cod_manager; –7698
–sau, o selectie implicita prin specificarea direct a codului departamentului:
–afisati subordonatii managerului cu codul 7698
select *
from emp
where mgr=7698;
–afisati numele salariatilor angajati inainte de 01-jan-2017 pe job-ul actual
select empno, firstname,hiredate
from emp
where hiredate <= '01-jan-2017';
–afisati numele persoanelor angajate intre '02-apr-2015' si '08-dec-2016'
select firstname,hiredate
from emp
where hiredate between '02-apr-2015' and '08-dec-2016';
Operatorul in – egal cu oricare din elementele listei
–afisati date depre angajatii din departamentele 10, 20, 30
select firstname, deptno, jobid
from emp
where deptno in (10, 20, 30);
–afisati managerii
select firstname, deptno
from emp
where mgr is null;
60
–afisati numele care incep cu litera s, procent (%)
select *
from emp
where firstname like 's%';
–afisati numele care contin exact 5 caractere, caracterul underscore (_)
select firstname
from emp
where firstname like '_____';
–sa se selecteze numele, adresa si emailul tuturor persoanelor din bucuresti care au adresa de
email pe gmail sau spiruharet.
select firstname, adr, email
from emp
where adr like '%bucuresti%' and (email like '%gmail%' or email like
'%spiruharet%');
Negarea comparatiei
–afisati date depre angajatii care nu fac parte din departamentele 10 si 30
select *
from emp
where deptno not in(10,30);
–afisati numele care nu incep cu litera s
select *
from emp
where firstname not like 's%';
–afisati angajatii care au primit prima
select *
from emp
where comm is not null;
–afisati numele persoanelor care nu s-au angajat in perioada '02-apr-2016' si '08-dec-2016'
select firstname,hiredate
from emp
where hiredate not between '02-apr-2016' and '08-dec-2016';
Conditii multiple
–afisati salariatii din departamentul 10 care au salariul cel putin 1000
select *
from emp
where deptno = 10 and sal >= 1000;
–
select *
from emp
where deptno = 20 and jobid='ac_mgr' or jobid ='ad_vp' ;
61
–
select *
from emp
where deptno = 20 and (jobid='ac_mgr' or jobid ='ad_vp');
Functii de tip caracter – accepta argumente de tip caracter si intorc rezultate de tip caracter
(chr, concat, initcap, lower, upper, lpad & rpad, ltrim & rtrim, translate, replace, substr etc.) sau
numeric (ascii, instr, length).
–afisati toate persoanele al caror nume contine litera a (cu litere mici) pe orice pozitie
select *
from emp
where lower(firstname) like '%a%';
–sa se afiseze departamentele din alte localitati decat Bucuresti (cu litere mari).
select *
from locations
where upper (city) <> 'bucuresti' ;
–prima litera mare, restul mici
select initcap(city) from locations;
–lpad(string,len,pstring)
–rpad(string,len,pstring)
–completatii cu spatii la stanga
select lpad(city,10,'-') from locations;
–-completatii cu spatii la dreapta
select rpad(city,10,'-') from locations;
–substr(string,pos,len)
–extrageti din coloanal CNP din tabelum emp, ziua, luna si anul nasterii
select firstname as numele, substr(cnp,6,2) as ziua, substr(cnp,4,2)
luna, substr(cnp,2,2) anul_nasterii
from emp;
–instr(string,search)
–instr(string,search,pos,n)
–in numele localitatilor, determinati pozitia de inceput pe care se gaseste subsirul tt
select city, instr(city,'tt') from locations;
–ltrim(string,rem)
–rtrim(string,rem)
–
select ltrim(' tech', ' ') from dual;
–
select rtrim('tech ', ' ') from dual;
–length(string)
–sa se afiseze lungimea atributului nume din tabela emp
select firstname, length (firstname) lungime_nume from emp;
62
–translate(string,from,to)
–in denumirea localitatilor inlocuiti litera o cu m
select city, translate(city,'o','m') from locations;
–replace(string,search,replace)
–replace(string,search)
–in denumirea localitatilor inlocuiti subsirul th cu g
select city, replace(city,'th','g') from locations;
Functii de tip numeric: mod, abs, sqrt,exp, de calcul trigonometric (sin, sinh, tan, tanh, cos,
cosh, ctg etc.; de calcul al logaritmului: ln, log, lg; de calcul al puterilor: pow; de rotunjire: floor, ceil,
round, trunc, etc.) – accepta argumente de tip numeric si intorc rezultate de tip numeric.
–round(number,n)
–afisati media primelor rotunjita la 2 zecimale
select avg(nvl(comm,0)), round (avg(nvl(comm,0)),2)from emp;
–trunc(number,n)
select avg(nvl(comm,0)),trunc (avg(nvl(comm,0)),1) from emp;
Functia trunc(data1,'char') gaseste prima zi a lunii care e continuta in data1, daca char = 'month',
sau gaseste prima zi a anului care contine data1 daca char= 'year'.
select sysdate data_curenta,
trunc (sysdate, 'month') prima_zi_luna,
trunc (sysdate,'year') prima_zi_an
from sys.dual;
–ceil(number)
–floor(number)
select avg(nvl(comm,0)), ceil(avg(nvl(comm,0))) from emp;
–
select avg(nvl(comm,0)), floor(avg(nvl(comm,0))) from emp;
Functii de tip data calendaristica (add_months, last_day, months_ between, next_day,
round, trunc, sysdate etc.) – accepta argumente de tip data calendaristica si intorc rezultate de tip data
calendaristica cu exceptia functiei month_between care intoarce o valoare numerica;
–afisaza data din sistem
select sysdate from sys.dual;
–months_between(date1,date2)
select firstname, hiredate,
round(months_between(sysdate,hiredate),0) from emp;
–add_months(date,mon)
select firstname, hiredate, add_months(hiredate,2) from emp;
63
–next_day(date,day)
–sa se afiseze urmatoarea zi de sambata (dupa data curenta–>sysdate)
select next_day (sysdate,'saturday') urmatoarea_sambata from dual;
–last_day(date)
select firstname, hiredate, last_day(hiredate) from emp;
–round(date,what),unde what = month sau year
–sa se afiseze angajatii care au cuvantul “james” in nume impreuna cu vechimea acestora.
Vechimea se va afisa in doua moduri: rotunjita in ani si in ani cu luni.
select firstname,
round ((sysdate-hiredate)/365, 0) ani,
round ((sysdate-hiredate)/365, 1) ani_cu_luni
from emp
where firstname like '%james%';
–sa se afiseze numele angajatilor si data implinirii limitei de varsta pentru pensionare (65 de
ani) precum si numarul de luni ramase pana la pensionare (65 ani*12 luni).
select firstname, datan,
add_months (datan, 65*12) data_pensionare,
round (months_between(add_months(datan,65*12),sysdate),2)
luni_ramase_pensionare
from emp;
Functii de conversie : to_char, to_number, to_date – fac conversia dintr-un tip de data in altul;
–to_char(number)
–to_char(number,format)
–to_char(date)
–to_char(date,format)
– afisati data si ora din sistem
select to_char(sysdate,'dd month yyyy hh24:mi:ss') from dual;
–to_date(string)
–to_date(string,format)
–
select to_date(hiredate,'dd-mon-yyyy') from emp;
Functii care accepta orice tip de tip de date : nvl, greatest & least, decode
–
select nvl(comm,0)from emp;
–greatest(value1,value2, …)
–least(value1,value2, …)
–afisati cea mai mare valoare din sirul de numere 10,20,50,40
select greatest(10,20,50,40) from dual;
–afisati cea mai mica valoare din sirul de numere 10,20,50,40
select least(10,20,50,40) from dual;
64
Functia generala decode evalueaza o expresie intr-un mod similar structurii if-then-else.
decode( value,
, search1, result1
[, search2, result2 . . .]
, default)
Sa se evalueze valoarea lui jobid astfel:
– daca jobid este it_prog, sporul de salariu este de 10%;
– daca jobid este ac_cccount, sporul de salariu este de 15%
– iar daca jobid este ac_mgr, sporul de salariu este de 20%.
– pentru celelalte functii salariile nu se modifica.
select jobid, sal,
decode(jobid,
'it_prog', sal*1.1,
'ac_cccount', sal*1.15,
'ac_mgr', sal*1.20,
sal) revista_salarii
from emp;
Functii de grup : avg, count, max, min, stddev, sum, varianve
–sa se afiseze numarul de inregistrari din tabela dept
select count (*) nr_inreg from dept;
–
select count(distinct jobid) from emp;
–sa se afiseze salariul de baza mediu, minim si maxim pentru toti salariatii cu codul cuprins
intre 7000 si 7500 .
select sum(sal), round (avg (sal),2) mediu, min (sal) minim, max
(sal) maxim
from emp
where empno between 7000 and 7500;
Instructiunea CASE
–sa se specifice daca un angajat dat are salariu mic, mediu, bun sau excellent dupa cum este mai
mic decat 5000, cuprins intre [5000,10.000), cuprins intre [10.000,15.000] sau mai mare decat
15.000.
select firstname, sal,
(case
when sal <5000 then 'mic'
when sal <10000 then 'mediu'
when sal <=15000 then 'bun'
else 'excelent'
end) as calificare
from emp;
65
Gruparea datelor
Functiile de agregare se pot folosi ca extensii ale clauzei group by:
• rollup – permite instructiunii select sa calculeze niveluri de subtotal multiple peste un grup de
dimensiuni;
• cube – genereaza toate subtotalurile care pot fi calculate dintr-un cube pe dimensiunile
specificate;
• grouping;
• grouping set.
–in departamentul 30 afisati media salariilor pentru fiecare job, daca aceasta este mai mare de
3700
select jobid, round(avg(sal),2)
from emp
where deptno = 30
group by jobid
having round(avg(sal),2) > 3700;
Interogari din mai multe tabele
select emp.firstname,dept.dname
from emp,dept
where dept.deptno = emp.deptno;
Selectii din mai multe tabele folosind alias-uri la tabele
select e.firstname,d.dname
from emp e,dept d
where e.deptno = d.deptno;
–
select firstname, e.deptno, dname, jobid
from emp e, dept d
where e.deptno = d.deptno and jobid = 'ac_mgr';
–listeaza numele tuturor angajatiilor impreuna cu numele managerului lor:
select e1.firstname, e2.firstname
from emp e1, emp e2
where e1.mgr = e2.empno;
–
select e.firstname, d.dname, d.deptno
from emp e, dept d
where e.deptno(+) = d.deptno;
Union
–afisaja job-urile din ambele departamente (10 si 30)
select jobid, deptno from emp
where deptno=10
union
select jobid, deptno from emp
where deptno=30;
66
Intersect
select deptno from dept
intersect
select deptno from emp;
Minus
select deptno from dept
minus
select deptno from emp;
Subinterogari
select column(s)
from table(s)
where column(s) = (select column(s)
from table(s)
where condition(s) );
–afisati numele angajatilor cu salariul mimim
select firstname,jobid,sal
from emp
where sal =
(select min(sal)
from emp);
–afisati angajatii care lucreaza pe acelasi post cu angajatul care are numarul 7369, si au salariul
mai mic ca cel al angajatului cu numarul 7782.
select firstname, jobid, sal
from emp
where jobid=
(select jobid
from emp
where empno=7369)
and sal<
(select sal
from emp
where empno = 7782);
–afisati codul firmei cu cei mai multi angajatii:
select deptno , count(*) nr_ang
from emp
group by deptno
having count(*) =
( select max(count(*))
from emp
group by deptno );
–sa se afiseze numele angajatilor care au cel mai mic salariu din departamentul din care fac
parte.
select firstname,sal,deptno
from emp
where (deptno,sal) in
67
(select deptno,min(sal)
from emp
group by deptno);
–afisati angajatii cara nu au mai putin de 3 luni de la data angajarii
select firstname,hiredate
from emp
where hiredate not in
(select hiredate
from emp
where months_between(sysdate,hiredate) <= 3);
–sa se afiseze codul si numele angajatilor care au primele doua salarii cele mai mari.
select empno, firstname, sal
from
(select *
from emp
order by sal desc)
where rownum < 3;
Operatorii any/some, all
Operatorul any – compara valoarea cu fiecare valoare returnata de subinterogare luata separat
–afisati toti angajatii care lucreaza in departamentul 10 si care castiga cel putin la fel ca
angajatii din departamentul 30
select firstname,sal,deptno
from emp
where sal >= any
(select sal
from emp
where deptno = 30)
and deptno = 10;
Operatorul all – compara valoarea cu toate valorile returnate de subinterogare
–afisati toti angajatii ale caror salarii sunt mai mici decat salariile medii la nivel de
departamente.
select empno, firstname, jobid, sal, deptno
from emp
where sal < all
(select round(avg(sal),2)
from emp
group by deptno);
–sa se gaseasca postul avand cel mai scazut salariu mediu .
select jobid, avg(sal)
from emp
group by jobid
having avg(sal) =
(select min(avg(sal))
from emp
group by jobid);
68
–afisati toti angajatii care lucreaza in departament cu managerul lor
select empno,firstname,jobid,mgr,deptno
from emp e1
where deptno in
(select deptno
from emp e
where e.empno = e1.mgr);
Folosind subinterogari in clauza from afisati numele angajatilor care realizeaza mai mult decat
media salariilor din departamentul in care lucreaza.
select e.firstname , e.sal , e.deptno , d.salavg
from emp e ,
(select deptno , round(avg(sal),2) salavg
from emp
group by deptno ) d
where e.deptno = d.deptno and e.sal > d.salavg;
–sa se afiseze, pentru fiecare angajat in parte, ce procent reprezinta salariul din totalul de
salarii de pe departament .
select e.firstname,e.deptno, round(e.proc/d.total*100,2) procent
from
(select firstname, deptno, sal proc
from emp
group by firstname, deptno, sal) e,
(select deptno,sum (sal) total
from emp
group by deptno) d
where e.deptno=d.deptno ;
Interogarea datelor din mai multe relatii folosind tipuri de asocieri :
1) inner join (jonctiunea interna);
2) left outer join (semijonctiunea la stanga);
3) right outer join (semijonctiunea la dreapta).
inner join (jonctiunea interna). Sintaxa
select …from tabel_a inner join tabel_b on (conditii de join)
– selecteaza toate informatiile din relatiile a si b care corespund conditiilor de asociere.
left outer join (semijonctiunea la stanga) .
select …from tabel_a left outer join tabel_b on (conditii de join)
– selecteaza toate informatiile din a, pe care le completeaza cu informatii din b, in masura in care
satisfac conditiile de join; acolo unde nu vor exista informatii din b, acestea vor fi completate cu null.
–
select e.firstname, d.dname
from emp e left outer join dept d
on (e.deptno = d.deptno);
right outer join (semijonctiunea la dreapta) . Sintaxa
select …from tabel_a right outer join tabel_b on (conditii de
join)
69
– selecteaza toate informatiile din b, pe care le completeaza cu informatii din a, in masura in care
satisfac conditiile de join; acolo unde nu vor exista informatii din a, acestea vor fi completate cu null.
–
select e.firstname, d.dname
from dept d right outer join emp e
on (e.deptno = d.deptno);
–
select e.firstname, d.deptno
from emp e full outer join dept d
on (e.deptno = d.deptno);
Modificarea tuplurilor din tabele (comanda UPDATE)
In functie de momentul in care se doreste realizarea modificarilor asupra bazei de date, utilizatorul
poate folosi una din urmatoarele comenzi:
• set autocommit imm[ediate] (schimbarile se efectueaza imediat);
• set autocommit off (schimbarile sunt pastrate intr-un buffer).
La executia comenzii commit se permanentizeaza schimbarile efectuate, iar la executia comenzii
rollback se renunta la schimbarile realizate.
update table [alias]_or view
set column [,column…] =
{expression,subquery}
[where condition];
–majorati salariile cu 15% pentru angajatii din departamentele 10 si 30.
update emp
set sal = sal *1.15
where deptno in (10,30);
select * from emp;
–transferati angajatul cu numarul 7782 la departamentul 20 .
update emp
set deptno = 20
where empno = 7782;
select * from emp;
–majorati salariul cu 15% pentru persoanele care au salariul identic cu cel al angajatului cu
codul 7788.
update emp
set sal=sal * 1.15
where sal in
(select sal
from emp
where empno = 7788);
select firstname,empno,sal from emp;
–modificati pentru angajatul numarul 7782 departamentul si slujba astfel incat sa coincida cu
cele ale angajatului numarul 7499 .
update emp
set (jobid, deptno) =
70
(select jobid, deptno
from emp
where empno = 7499)
where empno = 7782;
select * from emp;
–modificati toate numerele de telefon din judetul maramures, astfel ca prefixul sa nu mai fie
0262 ci 0362, din baza de date emp .
update emp
set phone=concat('0362', substr(phone, 5))
where substr(phone,1,4)='0262';
select firstname,phone from emp;
–adaugati o noua coloana tabelului emp care sa contina salnet
alter table emp
add (salnet number(9,2));
describe prof.emp;
–sa se calculeze salariul net
update emp
set salnet=(case nvl(comm,0)
when 0 then sal
else sal+nvl(comm,0) –sal+sal*nvl(comm,0)/100
end);
select firstname,sal,nvl(comm,0), salnet from emp;
Comanda DELETE
delete [from] table
[where condition];
–stergeti departamentul cu numarul 10 din tabelul dept
delete from dept
where deptno = 10;
select * from dept;
–stergeti toate persoanele angajate dupa data de 10 iulie 1987. Stergerile sa nu fie efectuate
imediat ci ulterior.
set autocommit off
delete from emp
where hiredate > to_date('10-07-1987', 'dd-mm-yyyy');
select * from emp;
Stergerea inregistrarilor folosind valori dintr-un alt tabel
–stergeti toti subordonatii managerului JONES . Subinterogarea cauta in tabelul emp codul numele
managerului, apoi furnizeaza codul interogarii principale, care sterge inregistrarile din tabelul emp pe
baza acestuia.
delete from emp
where mgr =
(select empno
from emp
where firstname = 'jones');
71
Procesarea tranzactiilor
commit [work];
–
update emp
set sal = sal * 1.25;
commit;
select firstname, sal
from emp;
rollback [work];
rollback [work] to savepoint_name;
–
update emp
set sal = sal * 1.05
where deptno in
(select deptno
from dept
where locid = '1');
rollback;
select firstname, sal from emp;
savepoint savepoint_name;
–
update dept
set dname = upper(dname);
savepoint done_dept;
update emp
set sal = sal * 1.1;
rollback to done_dept;
commit;
select * from dept;
select * from emp;
–sa se stearga inregistrari din tabelul emp in functie de conditia introdusa: hiredate <
to_date('10-07-1987', 'dd-mm-yyyy')
accept p_conditie prompt 'introduceti conditia de stergere: '
begin
delete from emp
where &p_conditie;
end;
/
select * from emp;
–sa se afiseze pentru toti angajatii valoarea comisionului introdus de la tastatura
accept comision prompt 'introduceti comisionul: '
select firstname, sal, sal*&comision/1000 valoare_comision
from emp;
72
Folosirea atributului %type
Putem defini tipul unei variabile in functie de tipul altei variabile sau a unei coloane de tabela.
–sa se creeze un bloc anonim in care se declara o variabila v_jobid de tip jobid (%type) a carei
valoare va fi codul jobului salariatului cu codul 7698
set serveroutput on
declare
v_jobid emp.jobid%type;
begin
select jobid into v_jobid
from emp
where empno=7698;
dbms_output.put_line('jobul este '|| v_jobid);
end;
/
–sa se specifice daca un angajat dat are salariu mare, mediu sau mic dupa cum este mai mare
decat 5000, cuprins intre 2000 si 5000 sau mai mic decat 2000
define p_empno = 7698;
declare
v_empno emp.empno%type := &p_empno;
v_sal emp.sal%type;
v_comentariu varchar2(10);
begin
select sal into v_sal
from emp
where empno = v_empno;
if v_sal < 2000 then v_comentariu := 'mic';
elsif v_sal between 2000 and 5000 then v_comentariu :=
'mediu';
else v_comentariu:= 'mare';
end if;
dbms_output.put_line('salariatul avand codul '|| v_empno ||'
are salariu '|| v_sal || ' considerat '|| v_comentariu);
end;
/
select * from emp;
Variabile de legatura – se utilizeaza pentru transmiterea valorii unor variabile din pl/sql catre
sql*plus. Referirea la o variabila de legatura se face in pl/sql prin prefixarea acestei variabile cu
caracterul „ : ”. In sql*plus, pentru declararea acestui tip de variabila se foloseste comanda
VARIABLE, iar valoarea variabilei de legatura va fi tiparita utilizand comanda PRINT.
–
variable v_jobid varchar2(35)
begin
select job into :v_jobid
from emp
where empno=7698;
–dbms_output.put_line('jobul este '|| :v_jobid);
end;
/
print v_jobid
73
–afisarea salariului maxim al angajatilor din tabelul emp utilizand variabile de legatura
variable g_max_sal number
declare
v_max_sal number;
begin
select max(sal) into v_max_sal
from emp;
:g_max_sal :=v_max_sal;
end;
/
print g_max_sal
–afisarea salariului total al angajatilor din departamentul 10 din tabelul emp folosind variabile
de legatura:
variable g_sal number
declare
v_sum_sal emp.sal%type;
v_deptno number not null :=10;
begin
select sum(sal)into v_sum_sal– functie grup
from emp
where deptno=v_deptno;
:g_sal:=v_sum_sal;
end;
/
print g_sal
Crearea si stergerea vederilor/vizualizarilor
create [or replace] view [{force | noforce}] view
view_name[(alias_name[, alias_name…])]
as subquery
[with {check option | read only} constraint constraint_name];
create [or replace] view <view-name> [(<column(s)>)] as
<select-statement> [with check option [constraint <name>]];
–Sa se creeze vizualizarea v_emp care sa contina codul si numele salariatilor din tabelul emp.
–sa se afiseze continutul acesteia.
–sa se insereze o noua inregistrare in aceasta vizualizare.
–sa se analizeze structura si continutul vizualizarii.
–sa se stearga vizualizarea v_emp
create view v_emp(empno,firstname)
as
select empno,firstname
from emp;
insert into v_emp values (7777, 'ionescu');
desc v_emp;
select * from v_emp;
drop v_emp;
74
–sa se creeze vizualizarea emp_dept care sa contina codul si numele departamentului, numarul
de angajati din departamentul respectiv si suma alocata pentru plata salariilor.
create view emp_dept (deptno,dname,nr_ang,val_sal)
as
select e.deptno,dname,count(*) nr_ang,sum(sal) val_sal
from emp e, dept d
where d.deptno = e.deptno
group by e.deptno,dname;
select * from emp_dept;
–
drop view emp_dept;
Definirea generatorului de numere de secventa :
create sequence sequence_name
increment by n
start with val_start;
–Sa se creeze o secventa secv care incepe cu valoarea 10 si se termina cu valoarea 10000, pasul
de incrementare 10 si nu cicleaza. Acesta secventa secv se va folosi ulterior pentru generarea
automata de numere unice pentru campul empno din tabela emp. Se vor genera succesiv,
crescator, numerele cuprinse intre 10 si 10000.
create sequence secv –nume secventa
increment by 10 –pasul de incrementare
start with 10 –valoarea de pornire a secventei
maxvalue 10000 –valoarea maxima a secventei
nocycle; –secventa finita
–sa se modifice toate liniile din tabelul emp, regenerand codul angajatilor a.i. sa utilizeze
secventa secv. Sa se anuleze modificarile
update emp
set empno=secv.nextval;
rollback;
–sa se afiseze valoarea curenta a secventei
select secv.currval val from dual;
–sa se modifice pasul secventei secv de la 1 la 10000
alter sequence secv
increment by 10000;
–sa se sterga secventa secv .
drop sequence secv;
Index
create [unique] index index_name
on table_name
(column [,column . . .]);
75
–sa se creeze un index nou pe atributul empno din tabela emp .
create unique index emp_idx01
on prof.emp
(empno);
–
create unique index emp_idx02
on prof.emp
(empno,hiredate);
Sinonim
create synonym <name> for <user>.<table> ;
–
create synonym ang
for prof.emp;
select * from ang;
–sa se creeze un sinonim public pentru tabela emp din schema de obiecte
create public synonym prod for emp;
Operatii pe tabele structurate arborescent :
Sa se afiseze datele salariatilor subordonati direct salariatului JONES (sau…empno=7566).
Returneaza nivelul liniilor rezultat ale unei cereri ierarhice.
– 1 pentru radacina,
– 2 pentru subdirector (copil) al radacinii etc.
Se utilizeaza in comanda select si cluza connect by
select level,empno,firstname,job, mgr,deptno
from emp
where level=2
connect by prior empno=mgr
start with firstname = 'jones';
–start with empno=7566;
APLICATII PROPUSE
Se consideră entitățile INSTITUTIE și FUNCTIONAR care au următoarele atribute:
INSTITUTIE (COD, DENUMIRE, ADRESA)
FUNCTIONAR (ID, NUME, PRENUME, FUNCTIE, SALARIU, PRIMA, ID_SEF, COD)
Știind că într-o instituție pot lucra mai mulți funcționari, iar un funcționar lucrează într-o singură
instituție, se cere să se scrie următoarele interogări:
1. crearea celor două tabele cu impunerea constrângerilor aferente și inserarea înregistrărilor în
ambele tabele în conformitate cu cerințele problemei;
2. stabilind un impozit de 18% din salariu, să se afișeze funcționarii în ordine crescătoare după
impozit;
76
3. să se specifice dacă un funcționar dat are prima mare, medie sau mică după cum este mai mare
decât 4500, cuprinsă între 1500 și 4500 sau mai mică decât 1500;
4. realizați o creștere a salariilor funcționarilor după funcție (considerăm trei funcții: SECRETAR,
CONTABIL și ADMINISTRATOR, și creșteri corespunzătoare de 10%, 15% și respectiv 20%),
folosind subprograme;
5. afișați funcționarii care nu sunt în instituția cu codul 20 (presupunând existența unei instituții cu
COD=20);
6. afișați pentru fiecare funcție salariul mediu;
7. după excluderea funcționarilor cu salariile mai mici decât 2000, să se afișeze funcțiile pentru care
media salariilor este mai mare decât 2600;
8. pentru fiecare funcție să se afișeze funcționarii cu salariile maxime;
9. afișați funcționarii care câștigă mai mult decât salariul maxim al funcționarilor care aparțin
instituției cu DENUMIRE=”Oracle”;
10. ștergeți instituția cu codul 20 (presupunând existența unei instituții cu COD=20);
11. să se gasească funcția cu cel mai scăzut salariu mediu;
12. afișați salariului total al angajaților din instituția cu codul 10, folosind variabile de legatură;
13. afișați funcționarii care lucrează pe aceeași funcție cu funcționarul care are ID=7369, și au salariul
mai mare ca cel al funcționarului cu ID=7876;
14. afișați funcționarii care nu au subalterni.
Bibliografie
• Bâscă, O., Baze de date, Editura All, 1997;
• Cârstoiu, D., Baze de date, Editura Matrix ROM, 2009;
• Connolly T., Begg C., Database Systems. A Practical Approach to Design, Implementation,
and Management, Ed. Addison Wesley, 2005
(http://www.palinfonet.com/download/software2/database%20systems.pdf);
• Fusaru D., Arhitectura bazelor de date-mediul SQL, Editura Fundației România de Mâine,
2002;
• Popa M., Baze de date (Fundamente, exemple, teste de verificare), Editura Fundației România
de Mâine, București 2006;
• Popescu I., Modelarea bazelor de date, Editura Tehnică, 2002;
• Popescu M, Baze de date, Editura Renaissance, București, 2010;
• ***, Informatică pentru examenul de licență, Universitatea SPIRU HARET, 2017;
• ORACLE, https://www.oracle.com/index.html.
Copyright Notice
© Licențiada.org respectă drepturile de proprietate intelectuală și așteaptă ca toți utilizatorii să facă același lucru. Dacă consideri că un conținut de pe site încalcă drepturile tale de autor, te rugăm să trimiți o notificare DMCA.
Acest articol: Conf. univ. dr. Nicoleta IACOB [612856] (ID: 612856)
Dacă considerați că acest conținut vă încalcă drepturile de autor, vă rugăm să depuneți o cerere pe pagina noastră Copyright Takedown.