+ All Categories
Home > Documents > Database Project

Database Project

Date post: 27-Dec-2015
Category:
Upload: mihai-nitescu
View: 62 times
Download: 2 times
Share this document with a friend
75
Universitatea “Politehnica” din Bucuresti Facultatea de Electronica, Telecomunicatii si Tehnologia Informatiei Proiect Baze de date pentru telecomunicatii
Transcript
Page 1: Database Project

Universitatea “Politehnica” din BucurestiFacultatea de Electronica, Telecomunicatii si Tehnologia Informatiei

Proiect Baze de date pentru telecomunicatii

Student: Profesor Coordonator: Tudor Mihnea Galatchi Dan

- Bucuresti 2011 –

Page 2: Database Project

Cuprins

CAPITOLUL 1 – INTRODUCERE IN BAZE DE DATE.............................................5

MODELUL RELATIONAL DE BD.................................................................................5ARHITECTURA INTERNA A SISTEMELOR DE BAZA DE DATE...............................................6MODELAREA DATELOR............................................................................................6PROIECTAREA BAZEI DE DATE...................................................................................9PREZENTARE GENERALA A LIMBAJULUI SQL..............................................................11

CAPITOLUL 2 – CREAREA SI ADMINISTRAREA TABELELOR.............................12

MODUL DE LUCRU...............................................................................................12CREAREA SI ADMINISTRAREA TABELELOR..................................................................16CONSTRÂNGERILE................................................................................................23CONSTRÂNGERILE DE DOMENIU..............................................................................24STERGEREA TABELELOR.........................................................................................32CHEI RELATIONALE...............................................................................................33CHEI PRIMARE.....................................................................................................37CHEI STRAINE......................................................................................................42VEDERI..............................................................................................................48

Page 3: Database Project

Capitolul 1 – Introducere in Baze de Date

Sistemele de baze de date sunt o componenta esentiala a vietii de zi cu zi în societateamoderna. În cursul unei zile, majoritatea persoanelor desfasoara activitati care implica interactiunea cu o baza de date: depunerea sau extragerea unor sume de bani din banca, rezervarea biletelor de tren sau avion, cautarea unei referinte într-o biblioteca computerizata, cumpararea unor produse etc.

Bazele de date pot avea dimensiuni (numar de înregistrari) extrem de variate, de la câteva zeci de înregistrari (de exemplu, baza de date pentru o agenda cu numere de telefon) sau pot ajunge la zeci de milioane de înregistrari (de exemplu, baza de date de plata pentru plata taxelor si a impozitelor).

Utilizatorii unei baze de date au posibilitatea sa efectueze mai multe categorii de operatii asupra datelor memorate: • Introducerea de noi date (insert); • Stergerea unora din datele existente (delete); • Actualizarea datelor memorate (update); • Interogarea bazei de date (query) pentru a regasi anumite informatii, selectate dupa uncriteriu ales.

În sensul cel mai larg, o baza de date (database) este o colectie de date corelate din punct de vedere logic, care reflecta un anumit aspect al lumii reale si este destinata unui anumit grup de utilizatori. În acest sens, bazele de date pot fi create si mentinute manual (de exemplu, fisele de evidenta a cartilor dintr-o biblioteca, asa cum erau folosite cu ani în urma) sau computerizat, asa cum este majoritatea bazelor de date folosite în momentul de fata. O definitie într-un sens mai restrâns a unei baze de date este urmatoarea: O baza de date (database) este o colectie de date creata si mentinuta computerizat, carepermite operatii de introducere, stergere, actualizare si interogare a datelor.

Simple colectii de fise (documente pe hârtie) sau fisiere de date, care contin înregistrari de date, dar nu permit operatii de interogare, nu sunt considerate baze de date. De exempu, datele memorate în fisiere pe disc de un instrument de calcul tabelar (ca Microsoft Excel) sau documentele memorate de un editor de text (ca Microsoft Word) nu sunt considerate baze de date.

Modelul relational de BD

Exista trei modele uzuale de implementare de BD: ierarhic, retea sau relational. Fiecare sebazeaza pe conceptul de date stocate ca set sau multime de înregistrari. Imaginati-va un set de fise, de exemplu. Modelele ierarhic si retea se bazeaza pe parcurgerea legaturilor dintre date pentru a lucra cu baza de date; de regula sunt utilizate pentru sisteme-cadru generale. O baza de date este deci o colectie autodescrisa de înregistrari integrate. Aceastacaracteristica BD este cunoscuta si ca independenta program - date.

Sistemele de gestionare a bazelor de date relationale (SGBDR) au cunoscut o larga raspândire, datorita modelului simplu, relational de date pe care- l utilizeaza:

5

Page 4: Database Project

Datele se prezinta sub forma unei colectii (unui set) de relatii Fiecare relatie are forma unui tabel (cea mai importanta componenta a unei BD) Rândurile (înregistrarile) tabelului reprezinta entitati Coloanele (câmpurile) tabelului sunt atribute/proprietati ale acestor entitati

Fiecare tabel are un set de atribute, care împreuna reprezinta o “cheie” care defineste fiecare entitate în mod unic. De exemplu, o companie de telecomunicatii poate avea în baza sa de date un tabel cu clientii sai, cu un rând/înregistrare pentru fiecare client. Ce atribute ar fi de interes? Depinde, desigur, de scopul pentru care a fost creat tabelul, si atributele sunt stabilite la momentul configurarii bazei de date. Ca exemplu aplicatia poate fi un stat de plata, deci va fi nevoie, în afara de nume, ID client si de informatii referitoare la tipul de abonament si servicii oferite.

O BD relationala contine entitatile, atributele si relatiile logice dintre acestea, reprezentate printr-o diagrama entitate – relatie (ER).

Arhitectura interna a sistemelor de baza de date

Arhitectura interna a unui sistem de baze de date propusa prin standardul ANSI/X3/SPARC (1975) contine trei niveluri functionale: nivelul extern, nivelul conceptual si nivelul intern (fig. 1.2). Nivelul extern este o colectie de scheme externe, care sunt vederi ale diferitelor grupuri de utilizatori, existând câte o vedere individuala a datelor pentru fiecare grup; nivelul conceptual contine schema conceptuala (logica) a bazei de date, iar nivelul intern contine schema interna (fizica) a bazei de date. O schema externa (vedere utilizator) (external schema, user’s view) contine o subschema conceptuala a bazei de date, mai precis descrierea datelor care sunt folosite de acel grup de utilizatori. Schema conceptuala a bazei de date (conceptual schema) corespunde unei reprezentari unice (pentru toti utilizatorii) si abstracte a datelor, descriind ce date sunt stocate în baza de date si care sunt asocierile dintre acestea. Schema interna (fizica) a bazei de date (internal schema) specifica modul de reprezentare a datelor pe suportul fizic. Un sistem de baze de date suporta o schema interna, o schema conceptuala si mai multe scheme externe; toate aceste scheme sunt descrieri diferite ale aceleiasi colectii de date, care exista doar în nivelul intern.

Modelarea datelor

Un model este o abstractizare a unui sistem, care capteaza cele mai importante trasaturi caracteristice ale sistemului (concepte), relevante din punct de vedere al scopului pentru care se defineste modelul respectiv. Tehnica de identificare a trasaturilor caracteristice esentiale ale unui sistem se numeste abstractizare.

6

Page 5: Database Project

Un model de date stabileste regulile de organizare si interpretare a unei colectii de date. În proiectarea bazelor de date se folosesc, de regula, mai multe modele de date, care se pot clasifica în doua categorii: modele conceptuale de nivel înalt si modele specializate. Un model conceptual de nivel înalt al datelor contine o descriere concisa a colectiilor de date care modeleaza activitatea dorita (numita schema conceptuala de nivel înalt), fara sa detalieze modul de reprezentare sau de prelucrare a datelor. Modelele specializate de date (cum sunt: modelul ierarhic, modelul retea, modelul relational, etc.) impun anumite structuri speciale de reprezentare a multimilor de entitati si a asocierilor dintre acestea, structuri pe baza carora sunt dezvoltate sistemele de gestiune a bazelor de date. Într-un astfel de model de date, o baza de date este reprezentata printr-o schema conceptuala (logica) specifica. Trecerea de la modelul conceptual de nivel înalt la un model de date specific reprezinta etapa de proiectare logica a bazei de date care asigura corespondenta dintre schema conceptuala de nivel înalt a bazei de date si schema conceptuala specifica modelului de date respectiv.

Modelul relational (Relational Model) se bazeaza pe notiunea de relatie (relation) din matematica, care corespunde unei multimi de entitati de acelasi tip. Modelul de date relational a fost propus de cercetatorul E.F. Codd de la compania IBM, care a publicat în anul 1970 lucrarea "Un model Relational de Date pentru Banci Mari de Date Partajate"

Cel mai utilizat model conceptual de nivel înalt este modelul Entitate-Asociere (E-A) sau Entitate-Relatie care reprezinta schema conceptuala de nivel înalt a bazei de date prin multimi de entitati si asocieri dintre acestea. Dezvoltarea acestui model, astfel încât sa permita extinderea tipurilor de entitati, este cunosuta sub numele de model Entitate-Asociere Extins (E-AE). Proiectarea modelului E sau al modelului E-AE este, de regula, una din primele etape în proiectarea bazelor de date, etapa numita proiectarea schemei conceptuale. Primul Sistem de Gestiune a Bazelor de Date Relationale (SGBDR) a fost prototipul System R, dezvoltat la compania IBM în anii 1970, dupa care numeroase companii au realizat sisteme de gestiune relationale (Oracle, Microsoft, Ingres, Sybase, etc.) iar aplicatiile de baze de date relationale au capatat o amploare deosebita. Pe lânga avantajul unui model de date precis si simplu, sistemele de baze de date relationale mai beneficiaza si de un limbaj de programare unanim recunoscut si acceptat, limbajul SQL (Structured Query Language), pentru care au fost emise mai multe standarde de catre ISO (International Standardization Office). Majoritatea SGBD- urilor relationale actuale implementeaza versiunea SQL92 (sau SQL2).

Pentru intelegerea functionarii unui sistem de baze de date trebuie defintie si intelese urmatoarele concepte de baza:

O entitate (entity) este „orice poate fi identificat în mod distinctiv"; o entitate se refera la un aspect al realitatii obiective care poate fi deosebit de restul universului si poate reprezenta un obiect fizic, o activitate, un concept, etc. Orice entitate este descrisa prin atributele sale. Un atribut (attribute) este o proprietate care descrie un anumit aspect al unei entitati. Atributele prin care este descrisa o entitate se aleg pe baza criteriului relevantei relativ la

7

Page 6: Database Project

domeniul de interes pentru care se defineste modelul respectiv, astfel încât sa asigure diferentierea acelei entitati fata de restul universului. Toate entitatile similare, care pot fi descrise prin aceleasi atribute, apartin unui acelasi tip de entitate (entity type), iar colectia tuturor entitatilor de acelasi tip dintr-o baza de date constituie o multime de entitati (entities set). În general, în modelul E-A se foloseste aceeasi denumire atât pentru un tip de entitate cât si pentru multimea entitatilor de acel tip. De exemplu, tipul de entitate „angajat” (al unei institutii) reprezinta orice persoana angajata a institutiei, care are o anumita functie si primeste un anumit salariu. Acest tip de entitate poate fi descris prin mai multe atribute, dintre care o parte sunt atribute de identificare a persoanei (Nume,Prenume,DataNasterii,Adresa), iar altele sunt atribute legate de activitatea acesteia în institutia respectiva (Functie,Salariu). Prin analogie cu modelul obiect, se poate spune ca un tip de entitate corespunde unei clase, o entitate este o instanta a unui tip de entitate si corespunde unui obiect, iar multimea entitatilor de un tip dat corespunde multimii obiectelor (instantelor) unei clase.

O asociere (relationship - relatie) este o corespondenta între entitati din doua sau mai multe multimi de entitati. Gradul unei asocieri este dat de numarul de multimi de entitati asociate. Asocierile pot fi binare (de gradul 2, între 2 multimi de entitati) sau multiple (între k multimi de entitati, k > 2). Asocierile binare sunt, la rândul lor, de trei categorii, dupa numarul elementelor din fiecare dintre cele doua multimi puse în corespondenta de asocierea respectiva (fig. 1.5). Fiind date doua multimi de entitati, E1 si E2 , se definesc urmatoarele categorii de asocieri binare: • Asocierea “unul-la-unul” (one-to-one) este asocierea prin care unui element (entitate) din multimea E1 îi corespunde un singur element din multimea E2 , si reciproc; se noteaza cu 1:1. • Asocierea „unul-la-multe” (one-to-many) este asocierea prin care unui element din multimea E1 îi corespund unul sau mai multe elemente din multimea E2 , dar unui element din E2 îi corespunde un singur element în multimea E1 ; se noteaza cu 1:N. • Asocierea „multe-la-multe” (many-to-many) este asocierea prin care unui element din multimea E1 îi corespund unul sau mai multe elemente din multimea E2 si reciproc; se noteaza cu M:N. Cardinalitatea (multiplicitatea) unei asocieri fata de o multime de entitati (cardinality, multiplicity) este numarul maxim de elemente din acea multime care pot fi asociate cu un element din alta multime a asocierii.

În proiectarea bazelor de date se considera doua categorii de entitati: entitati normale (puternice, obisnuite - regular entities) si entitati slabe (dependente - weak entities). Entitatile normale au o existenta proprie în cadrul modelului, în timp ce entitatile slabe nu pot exista decât daca exista o entitate normala (puternica) cu care sunt asociate. De exemplu, o entitate „dependent” poate sa reprezinte o un numar ce reprezinta vechimea unui client in retea (aceasta se calculeaza din momentul in care clientul intra face parte din reteaua operatorului respectiv). O entitate „client” este o entitate puternica, deoarece ea exista în mod normal în modelul activitatii operatorului de telefonie mobila în timp ce o entitate “dependent” este o

8

Page 7: Database Project

entitate slaba: nu se va calcula vechimea in retea decat din momentul in care clientul va opta pentru unul din abonamentele de telefonie disponibile).În proiectarea bazelor de date se definesc asocieri între multimile de entitati componente, pentru a reprezenta anumite aspecte ale realitatii pe care baza de date o modeleaza.

Proiectarea bazei de date

Structura unei BD (entităţile, atributele, relaţiile) este determinată în timpul proiectării BD. Abordarea proiectării unei BD este diferită de cea a sistemelor pe bază de fişiere, unde totul era dictat de nevoile aplicative ale departamentelor individuale. În cazul BD trebuie de considerat întâi datele apoi aplicaţiile. Această schimbare a modului de tratare se numeşte schimbare de paradigmă.

Pentru a intelege pe deplin intregul proces de proiectare a unei baze de date trebuie lamurite anumite aspecte, dupa cum urmeaza.

Poziţiile persoanelor din mediul BD

Se pot identifica patru tipuri distincte de persoane implicate în mediul SGBD: - administratorii de date şi de BD; - proiectanţii de BD; - programatorii de aplicaţii; - utilizatorii finali. Administratorii de date şi de BD BD şi SGBD sunt resurse comune care trebuie gestionate ca orice resursă. Sarcinile administratorului de date (DA = data administrator): - responsabil cu gestionarea resurselor de date: planificarea, elaborarea şi întreţinerea strategiilor şi procedurilor bazei de date - responsabil cu proiectarea conceptuală/logică a BD - consultarea şi îndrumarea managerilor superiori cu privire la direcţia de dezvoltare a BD, a.î BD să sprijine obiectivele generale ale organizaţiei. Administratorul de baze de date (DBA – data base administrator) este responsabil cu realizarea fizică a BD, având următoarele sarcini: - proiectarea şi implementarea BD, - securitatea şi controlul integrităţii BD, - întreţinerea sistemului operaţional, - asigurarea de performanţe satisfăcătoare pentru aplicaţii şi utilizatori; Rolul DBA este mai tehnic şi necesită cunoaşterea în detaliu a SGBD şi a mediului acestuia.

Proiectanţii de BD Există proiectanţi de BD logice şi proiectanţi de BD fizice.

Proiectanţii de BD logice: “ce anume?”

9

Page 8: Database Project

- se ocupă cu identificarea datelor (entităţi şi atribute) şi relaţiilor dintre acestea, şi de constrângerile asupra celor care vor fi stocate în BD; - trebuie să cunoască foarte bine datele organizaţiei şi a regulilor de operare ale organizaţiei;- trebuie să implice toţi posibilii utilizatori ai BD în modelul creat. Etape de proiectare a BD logice: a) proiectarea conceptuală a BD, independent de detaliile de implementare (de ex. SGBD utilizat, programele de aplicaţie, limbajele de programare etc.) b) proiectarea logică a BD, care se bazează pe un anumit model, de ex. relaţional, ierarhic, în reţea, orientat pe obiecte. Proiectanţii de BD fizice (“Cum anume?”) preia modelul logic şi stabileşte realizarea fizică: - transpunerea modelului logic de date într-un set de tabele şi constrângeri privind integritatea; - selectarea de structuri de stocare şi metode de acces specifice, a.î. să se obţină performanţe bune ale datelor in activităţile legate de BD; - măsuri referitoare la securitatea datelor. Proiectarea fizică a unei BD trebuie să ţină cont de SGBDul avut în vedere; proiectantul trebuie să cunoască funcţionalitatea acestui SGBD şi avantajele şi dezavantajele fiecărei variante de implementare a BD. Strategia de stocare aleasă trebuie să ţină cont de modul de utilizare. Programatorii de aplicaţii Odată realizată BD trebuie implementate programele de aplicaţie ce conferă funcţionalitatea cerută de utilizatorii finali. Aceasta este sarcina programatorilor de aplicaţii. Fiecare program conţine instrucţiuni care îi cere SGBD să efectueze o operaţie oarecare în BD (extragere, inserare, reactualizare, ştergere de date). Programele pot fi scrise într-un limbaj de generaţia a treia sau a patra. Utilizatorii finali Sunt clienţii pentru care a fost proiectată, implementată şi este întreţinută BD, pentru a le satisface necesităţile informaţionale. Utilizatorii simpli nu sunt conştienţi de SGBD. Accesează BD prin programe de aplicaţie speciale, simplificatoare. Ei folosesc comenzi simple, opţiuni din meniu. Utilizatorul simplu nu ştie nimic despre BD sau SGBD (de ex. vânzătorul care citeşte codul de bare pentru a afla preţul unui produs. Există totuşi un program care citeşte codul de bare, caută preţul articolului respectiv în BD, modifică câmpul cu stocul de articole, afişează preţul la casă). Utilizatorii sofisticaţi sunt familiarizaţi cu structura BD şi facilităţile SGBD. Pot utiliza un limbaj de interogare de nivel înalt (SQL). Pot scrie programe de aplicaţie pentru uz personal.

Scurta istorie a limbajului SQL

Istoria limbajului SQL incepe intr-un laborator IBM in San Jose, California, unde a fost dezvoltat la sfarsitul anilor ’70. Initialele acronimului SQL semnifica Structured Query Language (Limbaj de Interogari Structurate), iar limbajul in sine este deseori denumit “sequel”. A fost initial dezvoltat pentru sistemul DB2 al IBM, un sistem relational de

10

Page 9: Database Project

management a bazelor de date (RDBMS). SQL este un limbaj non-procedural, in contrast cu generatia a treia de limbaje procedurale cum ar fi COBOL si C.Caracteristica care diferentiaza un DBMS fata de un RDBMS este faptul ca RDBMS ofera un limbaj de baze de date orientat pe seturi. Acest lucru inseamna ca limbajul SQL proceseaza seturi de date in grupuri.Doua organizatii de standardizare, American National Standards Institute (ANSI) si International Standards Organization (ISO), promoveaza in prezent limbajul SQL. Standardul ANSI-82 este standardul pentru SQL folosit in aceasta carte. Desi aceste entitati pregatesc standarde pentru a fi adoptate de proiectanti pentru sisteme de baze de date, toate variantele de baze de date difera de standardul ANSI mai mult sau mai putin. In plus, majoritatea sistemelor furnizeaza extensii particulare pentru SQL care extind limbajul spre unul cu adevarat procedural. Am folosit diverse sisteme RDBMS pentru exemplele din aceasta carte pentru a oferi o idee asupra sistemelor de baze de date uzuale.

Prezentare generala a limbajului SQL

Limbajul SQL este limbajul considerat standard pentru manipularea si recuperarea datelor din bazele de date relationale. SQL permite unui utilizator sau administrator de baze de date sa faca urmatoarele lucruri:

Modificarea structurii unei baze de date Schimbarea setarilor de securitate la nivel de sistem Adaugarea drepturilor de utilizator la baze de date sau tabele Interogarea unei baze de date pentru informatii Actualizarea continutului unei baze de date

Cea mai utilizata declaratie in SQL este declaratia SELECT, care recupereaza datele din baza de date si le returneaza la utilizator. Exemplul tabelului ANGAJATI ilustreaza un exemplu tipic de folosirea a declaratiei SELECT. De asemenea, limbajul SQL ofera declaratii pentru crearea de baze de date noi, tabele, campuri si indecsi, precum si declaratii pentru inserarea si stergerea inregistrarilor. ANSI SQL recomanda un grup principal de functii pentru manipularea datelor. Multe sisteme de baze de date au si unelte pentru asigurarea integritatii datelor si impunerea securitatii care permit programatorilor sa opreasca executarea unui grup de comenzi daca exista o anumita conditie.

Cel mai popular model de stocare a datelor este sistemul relational de baze de date, care a fost conceput de Dr. E. F. Codd in 1970, in lucrarea lui, “A Relational Model of Data for Large Shared Data Banks”. Limbajul SQL a evoluat sa utilizeze conceptele modelului relational.

Majoritatea bazelor de date au o relatie “parinte/copil”, astfel incat un nod parinte contine indicatori de fisiere catre copiii lui.

Aceasta metoda are mai cateva avantaje si multe dezavantaje. Un avantaj este ca structura fizica a datelor pe un disc devine neimportanta. Un programator doar stocheaza indicicatori catre urmatoarea locatie, iar astfel datele pot fi accesate. De asemenea, datele pot fi adaugate si sterse cu usurinta. Insa, grupuri diferite de informatie nu pot fi asociate cu usurinta pentru a crea informatie noua. Formatul datelor pe disc nu pot fi schimbate in mod arbitrar dupa ce baza de date a fost creata. Pentru a face acest lucru este necesara crearea unei structuri noi de baza de date.

11

Page 10: Database Project

Idea lui Codd pentru un RDBMS foloseste concepte matematice de algebra relationala pentru a imparti datele in seturi si subseturi asociate.Deoarece informatia poate fi grupata in seturi distincte, Dr. Codd a organizat sistemul de baza de date folosind acest concept. In modelul relational, datele sunt separate in seturi care se aseamana cu structura unui tabel. Aceasta structura este formata din elemente individuale de date denumite coloane sau campuri. Un singur set de grupuri de campuri este cunoscut sub numele de rand sau inregistrare.

Oracle Database 10g Express Edition

Aplicatia propusa pentru realizarea unei baze de date pentru un operator de telefonie mobila va fi Oracle Database 10g Express Edition.

Baza de date Oracle Database 10g Express Edition (Oracle Database XE) este o versiune gratuită, downloadabilă. Oracle Database XE este uşor de instalat şi uşor de întreţinut. Cu Oracle Database XE puteţi folosi Database Home Page, o interfaţă intuitivă de tip browser, pentru administrarea bazei de date, crearea tabelelor, a vizualizărilor, a obiectelor, import şi export a datelor, a interogărilor şi scripurilor SQL, precum şi generarea raporturilor.Oracle Database XE include Oracle HTML DB 2.1, un mediu de dezvoltare grafic pentru crearea aplicaţiilor Web centrate pe baze de date.

Capitolul 2 – Crearea si administrarea tabelelor

Modul de lucru

Toate activităţile de administrare se desfăşoară prin intermediul ferestrei de administrare. Pentru accesarea acesteia, accesaţi Start> All Programs> Oracle Database 10g Express Edition> Go To Database Homepage

12

Page 11: Database Project

În această fereastră vom desfăşura toate activităţile de gestionare şi administrare a bazei de date. Mai intâi este necesară logarea folosind userul system şi a parolei pe care am setat-o la instalare (oracle).

Procesul de instalare crează un utilizator system. Acest utilizator este considerat administrator deoarece are acces DBA. Administratorul îşi poate crea propriul user şi parolă pentru a crea conturi de utilizatori atunci când va fi necesar. Accesaţi Administration> Database Users> Create User.

13

Page 12: Database Project

Vom crea un cont de administrator numit generic mihnea şi parolă oracle. Se vor completa câmpurile cu userul şi parola de mai sus, apoi se va selecta căsuţa DBA, care va indica că userul alexandru are drepturi de administrator, iar apoi se va crea userul folosind Create

14

Page 13: Database Project

Mai departe, vom crea 10 utilizatori diferiţi fără drepturi de administrator. Vom denumi aceşti utilizatori telecom_i, cu i=1...10.

La final, lista userilor va fi următoarea:

15

Page 14: Database Project

Contul de administrator este cel care va gestiona sistemul, inclusiv crearea de useri şi administrarea conturilor utilizatorilor. Aceşti utilizatori telecom_i, cu i=1-10, pot indeplini fiecare roluri diferite in gestionarea bazei de date. De exemplu, fiecare poate fi responsabil cu crearea tabelelor, introducerea datelor, etc.

Crearea si administrarea tabelelor

Scopul bazelor de date relaţionale este acela de a memora informaţii care modelează entităţi din realitate, dar şi relaţiile matematice care apar între mulţimile de entităţi. O bază de date relaţională are la bază mai multe “relaţii”. O relaţie reprezintă o mulţime de entităţi sau o mulţime de asocieri între entităţi. Relaţia este definită de atribute şi domeniile de definiţie ale acestora.Înainte de a decide care este structura unei baze de date (adică a se stabili concret ce tabele vor fi necesare şi ce coloane vor avea acestea), este necesar să se analizeze problema pentru care se doreşte crearea unui model. Trebuie ca structura folosită să modeleze suficient de detaliat entităţile implicate şi, de asemenea, să asigure faptul că baza de date nu va memora informaţii dublate din motive de eficienţă a utilizării spaţiului de stocare şi nu numai. Deşi spaţiul disponibil în sine poate nu este o problemă, o bază de date de dimensiuni foarte mari se va comporta cu siguranţă suboptimal. Operaţiile efectuate asupra acesteia vor dura mai mult timp, în special dacă nu există niciun fel de optimizări de tipul indecşilor.

Ne vom loga astfel cu contul de administrator, respectiv mihnea cu parola oracle:

Pentru a crea o bază de date, se foloseşte instrucţiunea CREATE DATABASE. Baza de date este necesară pentru a putea crea tabele. Pentru această temă, vom crea o baza de date numita generic MihneaDB.

16

Page 15: Database Project

Pentru aceasta vom intra in sectiunea Home a paginii de start a userusului SYSTEM si vom accesa SQL>SQL Commands>Enter Command.

Fereastra unde se vor introduce liniile de comanda va arata in felul urmator:

Pentru a crea o bază de date, se foloseşte instrucţiunea CREATE DATABASE. Baza de date este necesară pentru a putea crea tabele.Pentru această temă, se va folosi baza de date creată la instalarea Oracle. Dacă baza de date nu ar fi fost creată în timpul procesului de instalare, pentru crearea ei s-ar fi folosit o instrucţiune de forma:

CREATE DATABASE MihneaDB;

17

Page 16: Database Project

Cum baza de date a fost deja creata, vom primi urmatorul mesaj:

Aceasta este o formă simplistă a instrucţiunii, însă se pot folosi parametri pentru a specifica spaţiul alocat pe disc. Condiţia esenţială este ca utilizatorul care crează baza de date să aibă drepturi de acces care să îi permită acest lucru.

Aspectul cu adevărat important este designul bazei de date. Se doreşte normalizarea relaţiilor pentru a evita existenţa informaţiilor dublate în baza de date. De asemenea, trebuie impuse constrângeri asupra valorilor pe care atributele relaţiilor le pot lua pentru a asigura funcţionarea corectă a aplicaţiei.

Pentru fiecare structură sau funcţie/procedură de pe baza de date este nevoie să se documenteze codul pentru a permite lucrul în echipă şi pentru a uşura efortul depus la modificări ulterioare. Documentaţia este cunoscută ca “data dictionary” şi are ca scop descrierea bazei de date şi a celor care o vor utiliza, informaţii legate de dimensiunea bazei de date sau a logului, scripturi pentru instalare/import/export, descrierea detaliată a structurii fiecărei tabele, codul sursă pentru procedurile stocate/triggeri, constrângeri de unicitate sau de domeniu de definiţie etc.

Pentru operatorul de telefonie mobila vom crea doua tabele: una pentru abonati si una pentru clientii pre-pay.

Un tabel este reprezentarea practică a noţiunii teoretice de relaţie (relaţia este o noţiune abstractă, cu sensul de mulţime matematică). Se poate spune că un tabel este format din :

- Numele său (ajută la identificarea entităţilor reale pe care le modelează);- Coloane (corespund valorilor atributelor relaţiilor);- Capul tabelului (header-ul tabelului, cuprinzând numele atributelor relaţiei; se poate

spune că acesta corespunde schemei relaţiei);- Tupluri (reprezintă înregistrări sau linii din tabelă, adică valori particulare pentru

atributele relaţiei).

18

Page 17: Database Project

Sistemul de gestiune de baze de date Oracle are un număr considerabil de tipuri de date ”built-in” (cu diferite caracteristici) care se pot folosi pentru definirea tipurilor de date asociate coloanelor din tabele: Varchar2, Nvarchar2(n), Number(p, s), Long, Date, Binary_float, Binary_double, Timestamp(p), Interval year(p) to month, Timestamp(p) with local time zone, Raw(n) etc.

De asemenea trebuie clarificate si urmatoarele concepte utilizate in cele ce urmeaza:

Relatie: o relatie este un tabel cu coloane si rânduri. Atribut: Un atribut este o coloana a unei relatii, cu o anumita denumire. Domeniu: Un domeniu este multimea de valori permise pentru unul sau mai multe atribute. Pentru fiecare atribut se defineste în mod central denumirea domeniului, sensul acestuia si domeniul de definitie. Ca urmare sistemul va evita operatiile incorecte semantic. (De ex. compararea unui numar de telefon cu nr. de casa, desi ambele sunt siruri de caractere; însa: nr. luni este legal de înmultit cu salariile, desi primele sunt caractere text, iar celelalte sunt valori monetare).Tuplu: Un tuplu este un rând dintr-o relatie. Intensitatea unei relatii: structura tabelului, specificarea domeniilor si a altor restrictii referitoare la valorile posibile. Intensitatea este de regula fixa/fixata. Extensia (starea) unei relatii: tuplurile, care se modifica în timp. Grad: Gradul unei relatii reprezinta numarul de atribute pe care îl contine. O relatie cu doua atribute, de exemplu, se numeste binara. Cardinalitate: cardinalitatea unei relatii este numarul de tupluri continute de aceasta. Baza de date relationala: Un set de relatii normalizate. O BDR consta din relatii structurate adecvat.Baza de date relationala: Un set de relatii normalizate. O BDR consta din relatii structurate adecvat. Null reprezinta valoarea unui atribut care este curent necunoscuta sau nu este aplicabila tuplului respectiv. Valoarea logica este “necunoscut”. Un Null nu este acelasi lucru cu o valoare numerica = 0 sau cu un sir de text “spatii” (zero string); acestea sunt valori, un Null însemnând însa absenta unei valori.

Numele tabelelor se dă în conformitate cu datele pe care le vor conţine. Trebuie ca tabelele să aibă nume diferite dacă sunt în aceeaşi schemă. În caz contrar, baza de date va genera o eroare, specificând că numele este deja asociat unui obiect existent în baza de date. Astfel, vom numi cele doua tabele abonati respectiv clienti-prepay.

Comanda SQL pentru crearea tabelei pentru abonati este urmatoarea:

create table abonati (nume varchar(20) not null, cod_client number(2) not null, numar_telefon varchar(10) not null, serviciu varchar(50) not null, cost_serviciu varchar(10) not null, stare_plati varchar(10) not null, vechime_retea varchar(10) not null)

19

Page 18: Database Project

S-a creat o tabelă cu numele ”abonati” care are următoarele coloane :

- Nume: corespunzator tuplurilor ce vor contine numele si prenumele clientilor cu abonament

- Cod_client: este un identificator unic pentru fiecare client in parte- Numar_telefon: evident, numarul de telefon asociat abonatului- Serviciu : reprezinta unul sau mai multe servicii disponibile utilizatorului (voce, sms,

mms, video call, internet, roaming)- Cost_serviciu: reprezinta costul total al serviciilor incluse in abonamentul clientului- Stare_plati: acest camp evidentiaza daca abonatul este cu plata la zi a facturilor sau

este restantier.- Vechime_retea: perioada de timp de cand clientul detine abonamentu de telefonie

mobile

Initial tabela creata nu are nicio inregistrare. Pentru verificarea continutului tabelei se utilizeaza sintaxa:

select * from abonati;

20

Page 19: Database Project

Am folosit tipul varchar pentru a memora numele, serviciile utilizate, costul serviciilor, statusul platilor (zi sau restantier) si vechime in retea deoarece acestea nu au o dimensiune standard pentru toţi abonaţii (nu putem spune că numele tuturor abonaţilor are acelaţi număr de litere; de asemenea, numărul de cifre ale numărului de telefon poate diferi în funcţie de operator). Folosind varchar(n), se limitează numărul maxim de caractere la n, însă, dacă un abonat are numele mai mic de n caractere, spaţiul ocupat efectiv de câmpul corespunzător din tuplul respectiv este exact numărul de caractere al numelui. O altă metodă de a crea o tabelă este pornind de la o tabelă deja existentă. Astfel, se creează o tabelă cu o structură identică celei a tabelei deja existentă. Mai mult, noua tabelă va conţine şi datele care existau în tabela veche la momentul creării sale şi care corespund unei eventuale condiţii specificate. Toate campurile create nu trebuie sa fie goale (fara continut) si de aceea am impus constrangerea not null.

Pentru a popula o tabelă, se execută instrucţiuni de tip insert, specificându-se tabela, coloanele din tabelă care vor fi completate şi valorile pentru coloanele respective. Inserarea de campuri (tupluri) in tabela se face utilizand sintaxa:

insert all into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Mirea Cristian', '1', '0743568963', 'voce, sms, internet','10 euro', 'zi', '1 an') into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Tache Viorel', '2', '0745632147', 'voce, sms, mms', '7 euro', 'zi', '6 luni')into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Modoran Laurentiu', '3', '0745665893', 'voce, sms, video call', '11 euro', 'zi', '2 ani')into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Carbunaru Gabriel', '4', '0742315698', 'voce, sms, mms, video call, internet, roaming', '20 euro', 'zi', '15 luni')into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Ionita Gabriel', '5', '0743256165', 'voce, sms, roaming', '9 euro', 'restantier', '1 an')into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Polojan Marian', '6', '0745896321', 'voce, sms', '5 euro', 'restantier', '3 ani')into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Surugiu Mircea', '7', '0741568954', 'voce, sms, internet', '10 euro', 'zi', '4 ani')into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Nedelcu Dan', '8', '0744448954',

21

Page 20: Database Project

'voce, sms, internet, roaming', '15 euro', 'zi', '2 ani')into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Stan Laurentiu', '9', '0742358954', 'voce, sms, roaming', '9 euro', 'zi', '2 ani')select 1 from dual

Verificam continutul tabelei:

select * from abonati;

Se observă că inserarea a fost făcută cu succes. Instrucţiunea insert all aşteaptă o instrucţiune select la final, dar, întrucât datele care au fost introduse în tabela abonati nu existau într-o altă tabelă, s-a folosit ”select 1 from dual”. Obiectul ”dual” este o tabelă fictivă(”dummy-table”) creată de Oracle odată cu crearea bazei de date şi a dicţionarului. Aceasta are o singură coloană, ”DUMMY”, şi conţine un singur tuplu, ”X”. La execuţia unui select din dual, se va returna un singur tuplu. Această tabelă este folosită pentru cazurile în care se doreşte execuţia unui cod SQL care nu are asociată logic o tabelă (în exemplul de mai sus, am folosit-o doar

22

Page 21: Database Project

pentru că ”insert all” aştepta o instrucţiune select). Tabela ”dual” se află în schema SYS, dar poate fi apelată din orice altă schemă.

Constrângerile

Deoarece structura bazelor de date trebuie să modeleze realitatea, datele conţinute de acestea trebuie să se încadreze în anumite mulţimi de valori. Aceste constrângeri sunt definite ca nişte reguli la proiectarea bazei de date şi trebuie respectate întotdeauna.

Se pot diferenţia două tipuri de constrângeri din punctul de vedere al locului unde sunt definite : intra-relaţie şi inter-relaţie. Cele intra-relaţie se referă la o singură relaţie (sau tabelă, în practică) şi se împart în constrângeri de domeniu, constrângeri de tuplu etc. Cele inter-relaţie se referă la una sau mai multe tabele, fiind modelate prin cheile străine şi asigurând asocierea corectă a relaţiilor.

Constrângerile de domeniu sunt impuse valorilor atributelor pentru ca acestea să corespundă realităţii. Acestea afectează coloanele şi, din acest motiv, se mai numesc şi constrângeri de coloană. Există constrângeri care se pot aplica pe orice tip de coloană (de exemplu, DEFAULT care setează valoarea implicită a atributelor relaţiei sau NOT NULL care constrânge coloana să aibă valori valide).

Constrângerile de tuplu (cheie primară şi chei secundare) afectează tabela în sensul că aceasta nu trebuie să conţină rânduri duplicate. De asemenea, se pot impune constrângeri asupra combinaţiilor de valori ale atributelor tuplurilor. Nu trebuie să existe tupluri distincte în care să existe aceleaşi combinaţii de valori ale anumitor atribute.

Constrângere 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

23

Page 22: Database Project

Constrângerile declarative pot fi: - constrângeri de domeniu, care definesc valorile luate de un atribut: DEFAULT NOT NULL UNIQUE CHECK

- constrângeri de integritate a entităţii care precizează cheia primară

PRIMARY KEY - constrângeri de integritate referenţială care asigură corespondenţa între cheile primare

şi cheile externe corespunzătoare 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 nn_(NOT NULL) pentru atributele obligatorii ck_(CHECK) pentru reguli de validare la nivel de atribut fk_(FOREIGN KEY) pentru cheile străine.

Asupra definitiilor pentru cheie primara, cheie straina vom reveni intr-o sectiune ulterioara.

Constrângerile de domeniu

La crearea tabelei ”abonati” s-au specificat constrângeri de domeniu prin specificarea ”not null” după declararea tuturor coloanelor. ”Null” este o valoare generală, asociată tuturor tipurilor de date dintr-o bază de date care modelează lipsa unei valori a câmpului respectiv.

Mai pot fi impuse constrângeri care să asigure că modelarea realităţii este cât mai fidelă. De exemplu, pentru tabela abonaţi, se observă că există un câmp care modelează numarul de telefon al abonatului. Acesta este unic pentru fiecare abonat in parte. Se poate impune o constrângere de tip ”check” care să se asigure că orice valoare introdusă pentru coloana numar_telefon sa fie formata strict din 10 cifre.

24

Page 23: Database Project

alter table abonati add constraint telefon check(length(numar_telefon)=10)

Vom incerca apoi sa inseram un tuplu in tabela ”abonati” care are campul ’numar_telefon’ mai lung de 10 caractere.

insert into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Agache Irina', '1', '074356823963', 'voce, sms, internet','10 euro', 'zi', '1 an')

Pentru crearea tabelei ce va contine informatii despre clientii pre-pay se vor urma aceiasi pasi.

In linia de comanda SQL se va crea tabela clienti_prepay:

create table clienti_prepay ( numar_telefon varchar(10) not null, serviciu varchar(50) not null, suma_disponibila varchar(10) not null, vechime_retea varchar(10) not null)

25

Page 24: Database Project

Initial tabela este nepopulata:

Select * from clienti_prepay;

26

Page 25: Database Project

Popularea tabelei se face cu sintaxa:

insert all into clienti_prepay(numar_telefon, serviciu, suma_disponibila, vechime_retea) values ('0743554213', 'voce, sms, internet','50 euro', '1 an') into clienti_prepay(numar_telefon, serviciu, suma_disponibila, vechime_retea) values ('0744556889', 'voce, sms, mms', '7 euro', '6 luni')into clienti_prepay(numar_telefon, serviciu, suma_disponibila, vechime_retea) values ('0742115445', 'voce, sms, video call', '15 euro', '2 ani')into clienti_prepay(numar_telefon, serviciu, suma_disponibila, vechime_retea) values ('0748551221', 'voce, sms, mms, video call, internet, roaming', '20 euro', '15 luni')into clienti_prepay(numar_telefon, serviciu, suma_disponibila, vechime_retea) values ('0741236547', 'voce, sms, roaming', '100 euro', '1 an')into clienti_prepay(numar_telefon, serviciu, suma_disponibila, vechime_retea) values ('0744888999', 'voce, sms', '5 euro', '3 ani')into clienti_prepay(numar_telefon, serviciu, suma_disponibila, vechime_retea) values ('0742312562', 'voce, sms, internet', '10 euro', '4 ani')into clienti_prepay(numar_telefon, serviciu, suma_disponibila, vechime_retea) values ('0745366998', 'voce, sms, internet', '25 euro', '4 ani')into clienti_prepay(numar_telefon, serviciu, suma_disponibila, vechime_retea) values ('0742352562', 'voce, sms, internet', '35 euro', '4 ani')select 1 from dual

27

Page 26: Database Project

Dupa popularea tabelei, afisam continutul acesteia:

Select * from clienti_prepay

Modificarea structurii tabelelor

De multe ori, este nevoie să se adauge una sau mai multe coloane la o tabelă deja existentă. Adăugarea nu afectează în niciun fel coloanele deja existente ale tabelei respective. În cazul în care se specifică o valoare implicită pentru coloana nou adăugată, toate tuplurile existente în tabelă se vor completa cu valoarea implicită pentru coloana nou adăugată.

Vom presupune că la tabela ”abonati” se doreşte introducerea unor informaţii suplimentare cu privire la vechimea acestuia in retea pe baza carora se vor calcula bonusurile aferente: astfel un client la intrarea in retea este client Basic, dupa 1 an in retea devine client Silver, dupa 2 ani devine client Gold iar pentru o vechime mai mare de 3 ani va fi client Platinum. Pentru aceasta, se va adăuga o coloană, Bonus la tabela ”abonati”. Pentru a exemplifica, se va seta ca valoare implicită ”Basic” şi se va adăuga şi constrângerea not null.

alter table abonati add Bonus varchar(200) default 'Basic' not null;

28

Page 27: Database Project

Verificam modificarile aparute in tabela:

select * from abonati;

Se vede că toate tuplurile existente s-au completat cu valoarea ”Basic” pentru câmpul corespunzător coloanei nou adăugate.

Există şi posibilitatea de a modifica o coloană dintr-o tabelă (se schimbă, de exemplu, dimensiunea coloanei respective). Trebuie ţinut cont de faptul că micşorarea dimensiunii unei coloane nu este permisă în cazul în care tabela respectivă conţine un tuplu în care valoarea corespunzătoare coloanei respective are o dimensiune mai mare decât noua dimensiune care se doreşte.Se va încerca reducerea numărului de caractere al coloanei ”adresa” la maxim 6 caractere. În acest caz, baza de date va genera o eroare deoarece toate tuplurile din tabelă conţin valoarea implicită, ”Basic”, a coloanei ”Bonus” (lungimea este de 5 caractere, adică mai mare decât noua dimensiune care se dorea setată).

29

Page 28: Database Project

alter table abonati modify bonus varchar(3);

Pentru exemplificarea unei redimensionări reuşite, se presupune că se doreşte micşorarea dimensiunii coloanei ”Bonus” la 10 caractere.

alter table abonati modify Bonus varchar(10) default 'Basic';

De asemenea se poate reiinoi continutul unui camp deja existent. Vom seta astfel valorile atributului Bonus pentru clientii care sunt in retea de cel putin un an sa fie Silver.

update abonati set Bonus ='Silver' where vechime_retea like '%1an%';

30

Page 29: Database Project

Verificam rezultatul modificarii:

select * from abonati;

Există şi cazuri în care se doreşte eliminarea uneia sau mai multor coloane dintr-o tabelă. Se exemplifică prin ştergerea coloanei ”Bonus” din tabela ”abonati”.

alter table abonati drop column Bonus;

31

Page 30: Database Project

Verificam acum modificarile aparute in tabela:

select * from abonati

Se observa lipsa coloanei Bonus din tabela fara ca celelalte intrari sa fie modificate.

Stergerea tabelelor

Pentru ştergerea unei tabele se execută o instrucţiune de tip drop. Pentru exemplificare, se va şterge tabela ”abonati” creata anterior.

drop table abonati;

Mesajul de notificare indica faptul ca stergerea tabelei s-a realizat cu succes.

32

Page 31: Database Project

Proprietatile relatiilor

- fiecare relatie are o denumire, diferita de toate celelalte denumiri de relatii;- fiecare celula a relatiei contine exact o valoare atomica (singulara); este ilegala trecerea de mai multe valori într-o celula; - fiecare atribut are o denumire distincta; - toate valorile unui atribut apartin aceluiasi domeniu; - ordinea atributelor nu are nici o importanta; - fiecare tuplu este distinct; nu exista dubluri ale tuplurilor; - teoretic, ordinea tuplurilor nu are nici o importanta (în practica poate afecta eficienta accesarii tuplurilor). Aceste proprietati rezulta din proprietatile relatiilor matematice: - din moment ce relatia este o multime, ordinea elementelor nu are importanta; deci ordinea tuplurilor nu are importanta; - într-o multime nu se repeta nici un element; deci nu exista tupluri duble.

Chei relationale

Trebuie sa existe posibilitatea de identificare unica a unui tuplu dintr-o relatie, prin valorile atributelor sale.

Supercheia: Este un atribut sau un set de atribute care identifica în mod unic un tuplu din interiorul unei relatii. O supercheie poate contine si atribute care nu sunt necesare identificarii unice a tuplului.

Cheia candidat: este o supercheie minima, pentru care nici o submultime nu este supercheie în cadrul relatiei respective. O cheie poate include mai multe atribute, caz în care se numeste cheie combinata. O cheie candidat este unica (în fiecare tuplu al relatiei R, valorile cheii identifica acel tuplu în mod unic) si ireductibila (nici o submultime a che ii candidat nu este unica).

Cheia primara: este cheia candidat care este selectata [din toate cheile candidat identificate] pentru a identifica în mod unic tuplurile din cadrul unei relatii. Cheile candidat neselectate se numesc chei alternative.

Cheie straina: Un atribut sau o multime de atribute din cadrul unei relatii, care se potrivesc cu o cheie candidat din alta relatie. De exemplu o cheie straina dintr-o relatie poate (spunem ca tinteste) coincide cu cheia primara din alta relatie. (Spunem ca tinteste cheia primara din alta relatie).

Atributele comune joaca un rol important în manipularea datelor. O supercheie este o submulţime de atribute ale unei relaţii care are proprietatea că orice

combinaţie a valorilor atributelor este unică în relaţia respectivă. Cu alte cuvinte, o combinaţie a valorilor atributelor respective identifică în mod unic un tuplu.

33

Page 32: Database Project

În exemplul considerat mai sus, se poate spune că o combinaţie a atributelor ”nume”, ”cod_client” si ”numar_telefon” identifică unic un abonat. Se poate defini în acest sens o constrângere de unicitate pe tabelă.

alter table abonati add constraint nume_cod_telefon_unic unique (nume, cod_client, numar_telefon);

Astfel fiecare inregistrare trebuie sa contina nume de client, cod de abonat si numar de telefon unice. Introducerea unei intregistrari care are cel putin una din aceste campuri duplicate va genera un mesaj de eroare.

Introducem astfel prima linie din tabel inca o data:

insert into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Mirea Cristian', '1', '0743568963', 'voce, sms, internet','10 euro', 'zi', '1 an')

Si vom obtine mesajul de eroare care ne atentioneaza ca o intrare identica exista deja in tabela, incalcandu-se astfel constrangerea impusa.

O cheie candidată este o supercheie ireductibilă (nu există o submulţime a atributelor supercheii care să aibă proprietatea că identifică în mod unic un tuplu). Această cheie poate fi formată din unul sau mai multe atribute şi condiţia de unicitate reprezintă o constrângere de integritate impusă asupra modelului respectiv.

34

Page 33: Database Project

Astfel, in cazul tabelei abonati poate fi considerata o singura cheie candidata: numar_telefon.. Aceasta este supercheie ireductibila: identifica in mod unic un abonat si fiind formata dintr-un singur atribut este ireductibila.

Atributul cod_client nu poate fi considerata cheie candidat deoarece aceasta poate identifica in mod unic un abonat dar acesta ar putea avea mai multe tipuri de abonamente deci nu ar identifica in mod unic un tuplu din relatie. Daca in schimb cod_abonat ar identifica in mod unic tipul de abonament ar putea fi mai multi clienti care ar folosi acelasi tip de abonament si iarasi acest atribut nu ar identifica un singur tuplu din relatie, prin urmare nu poate fi considerat o cheie canditat.

Se poate deci aplica o constrangere de unicitate pentru fiecare din cheile candidate existente:

alter table abonati add constraint telefon_unic unique(numar_telefon);

La introducerea unui client cu numar de telefon identic cu unul din numerele de telefon deja existente in tabela sa va primi mesaj de eroare doarece numarul de telefon introdus corespunde unui alt client:

insert into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Ban Adrian', '1', '0743568963', 'voce','10 euro', 'zi', '1 an')

35

Page 34: Database Project

Constrangerile pot fi eliminate utilizand urmatoarea sintaxa:

alter table abonati drop constraint nume_cod_telefon_unic;

Pentru tabela clienti_prepay exista o singura cheie canditat posibilia si anume coloana numar_telefon. Aceasta identifica in mod unic fiecare linie de tabel intrucat nu exista alte linii cu acelasi numar de telefon inscris in campul corespunzator. Pe de alta parte pot exista mai multi clienti care sa beneficieze de aceleasi servicii sau sa aibe disponibila la un moment dat aceeasi suma.

Astfel putem aplica o constrangere de unicitate pentru campurile coloanei numar_telefon.

alter table clienti_prepay add constraint telefon_unic_prepay unique(numar_telefon);

36

Page 35: Database Project

Incercarea de a introduce o linie cu numar de telefon deja existent in tabela va esua primindu-se un mesaj de eroare:

insert into clienti_prepay(numar_telefon, serviciu, suma_disponibila, vechime_retea) values ('0742345445', 'voce', '2 euro', ' 3 ani')

Chei primare

Cheile primare pot fi naturale (formate din unul sau mai multe atribute existente în relaţie) sau artificiale (construite special pentru identificarea unică a tuplurilor; de obicei, cheile artificiale sunt formate dintr-un singur atribut al relaţiei). Pentru relatia abonati cheia primara naturala este numarul de telefon. Acesta este unic pentru fiecare abonat in parte si este formata dintr-un singur atribut. O alta varianta ar putea fi atributul cod_abonat care la fel este unic pentru fiecare abonat in parte si de asemenea ar fi o cheie primara naturala dar in cazul in care un client are mai mult de un abonament atributul nu ar identifica un singur tuplu ci mai multe. Codul de abonat este unic pentru fiecare client in parte...dar nu si pentru fiecare tip de abonament care il foloseste.

Exista de asemenea posibilitatea de a alege atributele nume si numar_telefon. Daca numele poate fi acelasi pentru mai multi abonati, numarul de telefon este unic si astfel s-ar identifica cu usurinta un singur tuplu din relatie.

Vom alege astfel drept cheie primara pentru tabela abonati atributul numar_telefon. Aceasta se implementeaza utilizand sintaxa:

37

Page 36: Database Project

alter table abonati add constraint cheie_primara_abonati primary key(numar_telefon)

Daca incercam sa introducem un tuplu a carui valoare din campul numar_telefon este 0742315698 vom primi un mesaj de eroare datorita faptului ca atributul numar_telefon a fost setat ca si cheie primara deci nu vom putea introduce tupluri cu acest camp duplicat. Mesajul de eroare primit va specifica numele constrangerii incalcate.

insert into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Balan Dan', '1', '0742315698', 'voce','10 euro', 'zi', '1 an')

In cazul relatiei clienti_prepay exista o singura cheie candidat si anume atributul numar_telefon. Astfel acest atribut va fi ales drept cheie primara pentru aceasta relatie.

Vom aplica constrangerea de cheie primara:

alter table clienti_prepay add constraint cheie_primara_prepay primary key(numar_telefon)

38

Page 37: Database Project

La introducerea unui tuplu ce contine o valoare duplicat al campului numar_telefon se va primi un mesaj de eroare:

insert into clienti_prepay(numar_telefon, serviciu, suma_disponibila, vechime_retea) values ('0741236547', 'voce', '2 euro', ' 3 ani')

Pana acum am ales drept cheie primara atributul numar_telefon pentru ambele tabele. Acest atribut trebuie cunoscut de fiecare data cand se introduc date despre un nou abonat. Exista cazuri cand cheia primara si valorile acesteia nu trebuie cunoscute de catre utilizator atunci cand se introduc date despre un nou client, si aceata din motive de securitate. Sa presupunem ca atributul cod_client din tabela abonati este ales cheie primara. In cazul de fata este eficient ca utilizatorul să nu fie nevoit să ştie de existenţa campului cod_client şi, mai ales, să nu fie nevoit să ştie ce valoare trebuie să specifice în instrucţiunile de tip insert. De aceea, se poate implementa un sistem în care valorile cheii primare să se incrementeze automat.

Pentru aceasta, este necesar să se definească o secvenţă de numere pentru care se specifică valoarea de început, incrementul şi, eventual, valoarea maximă. După definirea secvenţei, se creează un trigger pe tabelă înainte de inserare. Acesta selectează din secvenţa creată următoarea valoare pe care o va folosi pentru valoarea cheii primare a noului tuplu introdus prin insert. Astfel, înainte de inserarea oricărei linii noi în tabelă, se execută triggerul. Acesta selectează următoarea valoare a secvenţei şi o foloseşte pentru completarea câmpului cod_client a tuplului care se introduce.

39

Page 38: Database Project

O secvenţă este un obiect al bazei de date care memorează o secvenţă de numere. Acest lucru este util atunci când se doreşte definirea unei chei primare şi setarea proprietăţii de autoincrement a coloanei respective.Un trigger este o procedură stocată specială în sensul că este declanşată (executată) automat în urma unei alte acţiuni ce a fost făcută asupra tabelei (de exemplu, după inserarea unui tuplu, înainte de inserarea unui tuplu sau înainte/după executarea unui update, se poate dori executarea automată a unei instrucţiuni suplimentares).În urma definirii secvenţei şi a trigger-ului, nu mai este necesar să se specifice valori pentru cod_client atunci când se face o operaţie de insert în tabela respectivă.

Setam atributul cod_client drept cheie primara:

alter table abonati add constraint cheie_primara primary key(cod_client);

Crearea secventei se face astfel:

create sequence secventa_cod_client start with 10 increment by 1 nomaxvalue;

40

Page 39: Database Project

Apoi creem triggerul dupa cum urmeaza:

create or replace trigger trigger_abonatibefore insert on ABONATIfor each rowbeginselect secventa_cod_client.nextval into :new.cod_client from dual;end;

Dupa crearea triggerului introducem un nou rand in tabela fara a mentiona in campurile completate si cod_client:

insert into abonati(nume, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea) values ('Tofan Dan', '0743566963', 'voce, sms, internet','10 euro', 'restantier', '14 luni')

41

Page 40: Database Project

Vom observa ca valoarea campului cod_client va fi incrementata automat cu 1:

select * from abonati;

Chei straine

O cheie străină reprezintă o constrângere referenţială care asigură modelarea corectă a asocierilor 1:N între două sau mai multe relaţii. Considerând două relaţii, R1 şi R2, se numeşte cheie străină o submulţime de atribute ale lui R2 care referă R1 respectând două condiţii :

- Atributele cheii străine sunt definite pe domenii compatibile cu cele ale atributelor unei chei candidate din R1;

- Valorile atributelor cheii străine într-un tuplu din relaţia R2 sunt identice cu valorile cheii atributelor cheii candidate din R1 ale unui tuplu din R1 sau iau valoarea NULL (aceasta este proprietatea de integritate referenţială).

Reamintim faptul ca o asociere este totală dacă toate entităţile au obligativitatea să participe la asociere, adică minima cardinalităţii este mai mare decât zero. Printr-o asociere parţială se înţelege o asociere în care nu există obligativitatea participării la această asociere a tuturor entităţilor vizate, ci numai a unora dintre ele sau a nici uneia. Asocierea parţială se caracterizează prin faptul că minima cardinalităţii ataşată unei entităţi este zero. Observaţii

- minima cardinalităţii este zero, are drept rezultat lipsa obligativităţii participării partenerului la această asociere;

- minima cardinalităţii este mai mare decât zero, are drept rezultat obligativitatea participării.

42

Page 41: Database Project

In cazul de fata, cele doua entitati ABONATI si CLIENTI_PREPAY sunt independente una de cealalta. Astfel singura asociere posibilia este cea partiala de tip multi-la-multi, deoarece exista posibilitatea ca un abonat sa detina una sau mai multe cartele prepay pe langa abonamentul deja exsistent dar nu este obligatoriu ca fiecare abonat sa aiba si cartela prepay. Drept urmare minima cardinalitatii acestei asocieri atat in cazul entitatii abonati cat si in cazul entitatii clienti_prepay este 0 iar maxima este ‚multi’.In cazul entitatii Bonus adaugata ulterior exsista o relatie de tip 1 la 1 cu entitatea abonati intrucat fiecare abonat, indiferent de vechimea in retea are asociat un plan de loialitate in functie de care i se acorda anumite beneficii (cum ar fi minutele de loialitate). Planele de loialitate descrise de atributele entitatii bonus corespund tuturor tipuri de clienti in functie de vechimea acestora, astfel nu exista posibilitatea ca un client sa nu fie inclus in unul din aceste plane de loialitate dar in acelasi timp vechimea in retea corespunde unui singur plan de acordare a bonusurilor.

O restrictie de integritate referentiala apare atunci când o relatie face referinta la o alta relatie. Când doua tabele (relatii), T1 si T2, prezinta atributul sau grupul de atribute notat CH, care pentru Tl este cheie primara, iar pentru T2 - cheie straina, daca în T2 se interzice aparitia de valori nenule ale CH care nu exista în niciun tuplu din T1, se spune ca între T2 ~i T1 s-a instituit o restrictie referentiala.

Observatii

• Pentru multi utilizatori si profesionisti ai bazelor de date, denumirea de "relational" desemneaza faptul ca o baza de date este alcatuita din tabele puse în legatura prin intermediul cheilor straine. Aceasta este, de fapt, a doua acceptiune a termenului de BDR, prima, cea “clasica”, avâ nd în vedere perceptia fiecarei linii dintr-o tabela ca o relatie între clase de valori.

• Majoritatea SGBD-urilor prezinta mecanisme de declarare si gestionare automata a restrictiilor referentiale, prin actualizari în cascada si interzicerea valorilor care ar indica aceste restrictii.

• Respectarea restrictiilor referentiale este una dintre cele mai complicate sarcini pentru dezvoltatorii de aplicatii ce utilizeaza baze de date. Din acest punct de vedere, tentatia este de a "sparge" baza de date în cât mai putine tabele cu putinta, altfel spus, de a avea relatii cât mai "corpolente". Gradul de fragmentare a bazei tine de normalizarea bazei de date, care, ca parte a procesului de proiectare a BD, se bazeaza pe dependentele func tionale, multivaloare si de jonctiune între atribute.

In cazul nostru, cele doua tabele, abonati si respectiv clienti prepay sunt tabele independente. Chiar daca la prima vedere cele doua contin campuri comune acestea reprezinta relatii cu proprietati diferite. Diferenta esentiala intre aceste tabele este faptul ca atributele relatiilor pe care le reprezinta sunt caracteristice unor entitati complet diferite, respectiv abonati (clienti care au optat pentru un abonament de telefonie cu costuri fixe lunar de exemplu) si clienti prepay (clienti care doresc o administrare dinamica a costurilor lunare in functie de necesitatile din perioada respectiva). Dupa modul cum au fost construite cele doua tabele nu avem atribute dintr-o tabela care sa refere la elemente din celelalte tabele si astfel nu putem alege o cheie straina.

43

Page 42: Database Project

Pentru a putea exemplifica crearea unei chei străine, este necesar să se mai creeze cel puţin încă o tabelă.

Vom crea astfel o tabela Bonusuri care va contine informatii detaliate despre bonusurile de loialitate ale abonatilor in functie de vechimea in retea.

create table bonusuri (Denumire varchar (10) not null, Minute_gratuite varchar (10) not null, Vechime_necesara varchar (10) not null )

Vom popula tabela creata:

insert allinto bonusuri (Denumire, minute_gratuite, Vechime_necesara) values ('Basic', '50', '1 an')into bonusuri (Denumire, minute_gratuite, Vechime_necesara) values ('Silver', '100', '2 ani')into bonusuri (Denumire, minute_gratuite, Vechime_necesara) values ('Gold', '150', '3 ani')into bonusuri (Denumire, minute_gratuite, Vechime_necesara) values ('Platinum', '200', '4 an')select 1 from dual;

44

Page 43: Database Project

Si o vom afisa:

select * from bonusuri;

Aplicam constrangerea de cheie primara atributului Denumire

alter table bonusuri add constraint cheie_primara primary key(Denumire);

45

Page 44: Database Project

Astfel, coloana Bonusuri din tabela ‚abonat’ contine doar numele pachetului de bonusuri de loialitate care il primeste fiecare abonat in functie de vechimea in retea, fara a fi nevoie sa se cunoasca in detaliu toate celelalte informatii cu privire la pachetul de bonusuri.

Se doreşte definirea cheii străine în tabela ”abonati” care să refere coloana ”denumire” din tabela ”bonusuri”. Pentru aceasta este necesar ca toate valorile din coloana ”Bonus” din tabela ”abonati” să conţină strict valori regăsite în coloana ”Denumire” din tabela ”Bonusuri” la care se va face referinţă.

alter table abonati add constraint bonus_fk foreign key(Bonus)references bonusuri(Denumire);

La inserarea unui nou tuplu (sau la execuţia unui update asupra valorii coloanei ”Bonus” a unui tuplu) în tabela ”abonati”, se va verifica dacă valoarea furnizată pentru ”Bonus” se regăseşte printre valorile coloanei ”Denumire” din tabela ”bonusuri”. În cazul în care valoarea furnizată nu este regăsită în valorile existente în coloana ”Denumire” din tabela ”Bonusuri”, se va genera o eroare şi operaţia de inserare va eşua.

46

Page 45: Database Project

insert into abonati(nume, cod_client, numar_telefon, serviciu, cost_serviciu, stare_plati, vechime_retea, bonus) values ('Agache Irina', '1', '0744333555', 'voce, sms, internet','10 euro', 'zi', '1 an', ’Argint’)

Pentru a evidentia utilitatea cheii straine vom apela situatia urmatoare. Se poate dori

extragerea de informaţii legate de pachetul de bonusuri unui abonat, cunoscând doar denumirea acestuia. În acest caz, se poate executa o instrucţiune de tip join pentru a returna informaţiile legate de abonatul respectiv. O astfel de instrucţiune poate avea următoarea formă:

select ABONATI.nume, ABONATI.numar_telefon, ABONATI.serviciu, ABONATI.cost_serviciu, ABONATI.Bonus, bonusuri.minute_gratuitefrom abonati join bonusurion abonati.bonus=bonusuri.denumirewhere abonati.bonus='Silver';

Una dintre caracteristicile extrem de puternice ale limbajului SQL este capacitatea acestuia de a aduna şi de a manipula datele conţinute în câteva tabele. Fără această

47

Page 46: Database Project

caracteristică ar trebui să stocăm toate datele necesare fiecărei aplicaţii într-un tabel. Fără tabele comune ar trebui să stocăm aceeaşi informaţie în câteva tabele.

Declaraţia JOIN din limbajul SQL permite proiectarea unor tabele mai mici ce sunt mai uşor de întreţinut decât tabelele mai mari.

Ca definiţie simplă, unirea este un mecanism folosit pentru asocierea tabelelor într-o instrucţiune SELECT. Utilizând o sintaxă specială, mai multe tabele pot fi unite astfel încât să fie returnat un singur set de rezultate, iar unirea asociază din mers liniile corectate din fiecare tabel. Astfel, se pot realiza diferite tipuri de unire.

Unirea interioară este operaţia utilizată frecvent în aplicaţii şi poate fi privită ca tipul de unire implicit. Rezultatul acestei operaţii constă în crearea unui nou tabel prin combinarea valorilor coloanelor a două tabele (A şi B), bazată pe o anumită condiţie. Interogarea compară fiecare rând al tabelului A cu fiecare rând al tabelului B pentru a găsi toate perechile de rânduri care satisfac condiţia. Atunci când este satisfăcută condiţia, valorile coloanelor pentru fiecare pereche de rânduri ale tabelelor A şi B sunt combinate, rezultând un singur rând. Rezultatul unirii poate fi definit ca efectul calculării mai întâi al produsului cartezian (sau unire încrucişată) al tuturor înregistrărilor din tabel (combinând fiecare înregistrare din tabelul A cu fiecare înregistrare din tabelul B) şi apoi întorcând toate înregistrările care satisfac condiţia. Implementările reale SQL utilizează în mod normal alte metode cum ar fi unirea Hash sau unirea de tip sortare – îmbinare, deoarece calculul produsului cartezian este foarte ineficient.

In exemplul de mai sus a fost utilizata unirea in mod explicit. Acest tip de unire foloseşte cuvântul – cheie JOIN pentru a specifica tabelul pe care dorim să îl unim şi cuvântul - cheie ON ce menţionează condiţia pentru realizarea unirii.

Vederi

Vederea externa este structura BD asa cum apare ea unui anumit utilizator. În modelul relational o vedere este o relatie virtuala, o relatie care nu este de fapt de sine statatoare, ci este derivata în mod dinamic din una sau mai multe relatii de baza.

Relatie de baza: O relatie cu o anumita denumire, corespunzatoare unei entitati din schema conceptuala, ale carei tupluri sunt stocate fizic în BD.

Vedere: Rezultatul dinamic al uneia sau mai multor operatii relationale care actioneaza asupra relatiilor de baza, pentru a obtine o alta relatie. O vedere este o relatie virtuala, care în realitate nu exista în BD, ci este produsa în momentul respectiv, la cererea unui utilizator.

Continutul unei vederi este definit ca o interogare asupra unei sau mai multor relatii de baza. Vederile sunt dinamice, adica modificarile în relatiile de baza sunt reflectate imediat în vederi. Si invers, modificarile permise operate asupra vederii se transmit relatiilor de baza.

Scopul vederilor

Mecanismul de vedere este util pentru ca:

- furnizeaza un mecanism de securitate puternic; - permite utilizatorilor accesarea datelor în mod personalizat; aceleasi date pot fi

vizualizate simultan, în moduri diferite, de catre diversi utilizatori;

48

Page 47: Database Project

- poate simplifica operatiile complexe asupra relatiilor de baza.

Reactualizarea vederilor

Exista restrictii referitor la modificarile care pot fi efectuate prin intermediul vederilor. Conditiile de care depinde reactualizarea datelor prin intermediul vederilor sunt: - sunt permise reactualizarile prin intermediul unor vederi definite prin utilizarea unei interogari simple a unei singure relatii de baza, si care interogare contine fie cheia primara, fie cheia candidat a acesteia; - nu sunt permise reactualizarile prin vederi care implica relatii de baza multiple; - nu sunt permise reactualizarile prin vederi care implica operatia de acumulare sau de grupare.

O vedere este deseori numita si o tabela virtuala. Vederile sunt create utilizand instructiunea CREATE VIEW. Dupa ce vederea a fost creata, se pot folosi urmatoarele comenzi SQL pentru a ne referi la acea vedere:

SELECT INSERT INPUT UPDATE DELETE

Se pot folosi vederi sau tabele virtuale pentru a incapsula query-uri complexe. Dupa crearea unei vederi asupra unui set de date, acea vedere poate fi tratata ca orice alta tabela. Insa, sunt aplicate restrictii speciale in ceea ce priveste modificarea datelor din acele vederi. Cand datele dintr-o tabela se modifica, ceea ce se obtine interogand o vedere de asemenea se modifica. Vederile nu ocupa spatiu in memoria fizica, astfel diferentiindu-se de tabele.

Sintaxa pentru instructiunea CREATE VIEW este:CREATE VIEW <numele_vederii> [(coloana1, coloana2...)] ASSELECT <numele_tabelei numele_coloanei>FROM <numele_tabelei>

Desi sintaxa poate parea dificil de inteles, ea va fi exemplificata in cele ce urmeaza tocmai pentru a ilustra diferitele situatii de utilizare dar si avantajele vederilor. Setul de instructiuni prezentate mai sus au ca efect crearea unei vederi al carui nume va fi specificat in campul “numele_vederii” si care cuprinde mai multe coloane al caror nume poate fi de asemenea specificat. O instructiune SELECT va determina campurile si tipurile de date.

Cel mai simplu exemplu de utilizare a unei vederi este instructiunea select.

create view ved_simpla asselect * from abonati;

49

Page 48: Database Project

Aceasta instructiune nu face decat sa creeze o tabela identica cu tabela abonati deja existenta:

select * from ved_simpla

Un alt exemplu este acela de a evidentia toate campurile tabelei abonati ale caror inregistrari contin doar abonati care au activ serviciul de roaming:

create view abonati_roaming asselect * from abonatiwhere serviciu like '%roaming%';

50

Page 49: Database Project

Dupa crearea vederii putem observa doar abonatii in roaming.

select * from abonati_roaming;

Acelasi lucru il putem face si pentru tabela clienti_prepay:

create view prepay_roaming asselect * from clienti_prepaywhere serviciu like '%roaming%';

51

Page 50: Database Project

Iar continutul vederii va arata astfel:

Utilizand cele doua vederi create putem afisa care sunt clientii care folosesc serviciul de roaming indiferent daca sunt abonati sau utilizeaza cartele prepay. Instructiunea UNION este foarte utila pentru unirea datelor din doua tabele.

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.

52

Page 51: Database Project

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âmap 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ă. Dupa cum am exemplificat si anterior tabelele abonati si clienti_prepay nu pot fi asociate una cu cealalta.

Aplicam deci, sintaxa urmatoare pentru a afisa toti clientii care folosesc serviciul de roaming:

select nume, numar_telefon, serviciu, cost_serviciu from abonati_roamingunionselect '-', numar_telefon, serviciu, '-' from prepay_roaming;

Sintaxa instructiunii UNION obliga utilizator sa foloseasca in selectie acelasi numar de coloane din fiecare tabel in parte. Avand in vedere ca pentru clientii prepay numele acestora nu este disponibil in tabel vom completa selectia cu un camp gol (’-’) pentru a respecta numarul de coloane selectate din tabela aboanti. De asemenea coloana cost_serviciu este disponibila doar pentru clientii care utilizeaza aboament lunar.

Putem extrage de exemplu din tabela abonati doar acei clienti care platesc pentru serviciile utilizate peste 10 euro. Reamintim ca atunci cand a fost creata tabela abonati, campul cost_serviciu a fost declarat de tip varchar(10), in ideea ca toate randurile acestei coloane vor

53

Page 52: Database Project

contine atat date numerice cat si caractere (care in cazul nostru reprezinta moneda in care se calculeaza costul serviciilor utilizate).

Practic, pentru a selecta doar abonatii care platesc lunar peste 10 euro trebuie analizate doar caracterele alfanumerice din campul cost_serviciu si comparate cu valoarea 10 pentru a selecta doar campurile care ne intereseaza.

Pentru aceasta am apelat la instructiunea CAST care ne ofera posibilitatea conversiei unui tip de date in alt tip.

Formatul instructiunii CAST este urmatorul:

CAST ( expresie AS tip_data ( lungime ) ) unde:

Expresie, reprezinta orice instructiune simpla sau compusa care descrie datele ale carui tip se doreste a fi schimbatTip_data reprezinta tipul in care se doreste sa se efectueze conversia. Campul lungime reprezinta dimensiunea maxima a datelor din noul tip, acesta este optional.Vom crea urmatoarea vedere:

create view suma asselect * from abonatiwhere cast(substr (abonati.cost_serviciu,1,2) as number)>10;

Aici se remarca utilizarea functiei SUBSTR() pentru selectia primelor 2 caractere ale campului cost_serviciu pentru a fi convertite in date de tip numeric. Sintaxa functiei utilizate este:

Substr(sir,poz_initiala,poz_finala) unde poz_initiala reprezinta pozitia primului caracter selectat iar poz_finala reprezinta pozitia ultimului caracter selectat.

54

Page 53: Database Project

Rezultatul afisarii vederii este cel asteptat:

Intr-un mod asemanator putem afisa, de exemplu toti clientii care au o vechime mai mare de 2 ani in retea. Pentru aceasta vom crea o vedere pentru clentii care utilizeaza abonament si una pentru clientii care utilizeaza cartele prepay:

55

Page 54: Database Project

Creem vederea pentru tabela abonati:

create view vechime_abonati asselect * from abonatiwhere cast (substr(abonati.vechime_retea,1,1) as number)>2;

56

Page 55: Database Project

Apoi creem vederea pentru tabela clienti_prepay

create view vechime_prepay asselect * from clienti_prepaywhere cast (substr(clienti_prepay.vechime_retea,1,1) as number)>2;

Cele doua vederi vor avea urmatorul output:

Abonatii cu o vechime mai mare de 2 ani

57

Page 56: Database Project

Clientii prepay cu o vechime mai mare de 2 ani

Daca dorim sa afisam toti clientii cu o vechime mai mare de 2 ani efectuam UNION asupra celor doua tabele selectand coloanele relevante pentru utilizator:

select nume, numar_telefon, serviciu, vechime_retea from vechime_abonatiunionselect '-', numar_telefon, serviciu, vechime_retea from vechime_prepay

Se observa lipsa datelor din campul nume pentru clientii care utilizeaza cartele prepay.

58

Page 57: Database Project

59


Recommended