BDR [1]

7/28/2019 BDR [1]

1/71

1

UNIVERSITATEA TRANSILVANIA DIN BRAOVFACULTATEA DE TIINE ECONOMICE

Prof.dr. DORIN LIXNDROIU Lector dr. RADU LIXNDROIU

BAZE DE DATERELAIONALE

introducere n baze de datealgebra relaional (AR)

SQL Access interogri n AR i Access 2007normalizarea relaiilor

Note de curs

Anul universitar 2008-2009

7/28/2019 BDR [1]

2/71

2

CAP.1. BAZE DE DATE

1.1. ORGANIZAREA DATELOR (OD)(Ce presupune organizarea datelor?)

definirea, structurarea, ordonarea i gruparea datelor n colecii de date omogene;

stabilirea relaiilor ntre date, ntre elementele unei colecii, ntre colecii de date; stocarea datelor pe suport informational.

1.2. OBIECTIVELE ORGANIZRII DATELOR(De ce este necesar organizarea datelor?) minimizarea timpului de acces la date; economie de memorie interni extern; asigurarea unicitii datelor; sistemul OD trebuie s reflecte ct mai fidel toate legturile dintre obiectele,fenomenele, procesele economice pe care aceste date le reprezint; asigurarea flexibilitii datelor.

1.3. ETAPE ALE EVOLUTIEI TEHNICILOR DE ORGANIZARE IPRELUCRARE A DATELOR

Prima etap se adapteaz tipurile de organizare a datelor existente n sistemele deprelucrare manual la condiiile tehnice impuse de calculator.

- apare fiierul (n general cu organizare secvenial)- utilizarea benzilor magnetice- prelucrarea pe loturi (batch processing)

A doua etap este marcat de separarea dintre structura logic de date i structurafizic. Rezult independena fizic a datelor.

- se utilizeaz fiiere secvenial-indexate i fiiere cu acces direct- suport extern de memorare discul magnetic- se asigur independena aplicaiilor de modificrile echipamentelor hardware(banda, disc, etc.)- apar primele faciliti simple de protecie a datelor

Caracteristic comun a primelor dou etape: fiecare aplicaie lucreaz cu propriilefiiere fr a avea nici o legtur cu fiierele utilizate de alte aplicaii.Inconveniente:

- redundana datelor = > probleme n operaiile de actualizare- absena unor legturi logice ntre datele din grupuri diferite de fiiere = >

numr mare de fiiere, timp mare de prelucrare- flexibilitate redus a sistemului la apariia unei noi aplicaii.

A treia etap este definit de apariia fiierelor integrate.- se reduce redundana datelor, aceleai date fizice pot fi utilizate n comun de ctremai multe aplicaii- rezult o structur logic unitar.

7/28/2019 BDR [1]

3/71

3

Not. Structura integrat constituie originea noiunii de model conceptual (modelul cecontine descrierile tuturor datelor i a legturilor dintre ele).

A patra etap este etapa bazelor de date.

1.4. SISTEMUL BAZAT PE FISIERE INDEPENDENTE (file based)

O colectie de programe de aplicaie care efectueaz servicii pentru utilizatorii finali.Fiecare program definete i gestioneaz propriile date.

Caracteristici:

datele sunt descrise independent n toate fiierele n care apar fiecare fiier de date este descris n toate programele care l acceseaz nu exist control al accesului i manipulrii datelor, n afara celui impus prinprogramele de aplicaie.

Dezavantajele tratrii bazate pe fisiere: redundana i inconsistena datelor dificultatea accesului izolarea datelor complexitatea deosebit a actualizrilor probleme de securitate probleme legate de integritatea datelor costul ridicat dificultatea de a obine rspunsuri rapide la probleme ad-hoc simple inflexibilitatea fa de schimbrile ulterioare din sistemul informational.

1.5. BAZE DE DATE

DATA - nregistrarea unei observaii, obiect, fenomen, imagine, sunet sau text, ntr-oform convenabil unei prelucrri, interpretri sau transmiteri prin mijloaceleinformaticii.

INFORMAIA- semnificaia ce poate fi ataat sau poate fi dedus dintr-un ansamblu dedate pe baza asociaiilor dintre acestea.

BAZA DE DATE o colecie de date operaionale nregistrate pe suport adresabil, aflaten interdependen logic, mpreun cu descrierea datelori a relaiilor dintre ele i care

sunt prelucrate n aplicaiile informatice ale unei organizaii. Baza de date permite

operaii de introducere, tergere, actualizare i interogare a datelor.

BAZA DE DATE este un ansamblu de date:- structurate,- coerente,- persistente,

7/28/2019 BDR [1]

4/71

4

- cu o redundan minimi controlat,- independente de programul de aplicaie,- direct accesibile dup mai multe criterii,- simultan accesibile de ctre mai muli utilizatori.

ARHITECTURA UNUI SISTEM DE BAZE DE DATE (Database System)- baza de date propriu-zis n care se memoreaz datele- sistemul de gestiune al bazei de date (SGBD)- metabaza de date - dicionarul datelor (DD)- mijloacele hardware- personalul (ABD, analiti, programatori, utilizatori finali).

Cerinele minimale care se impun unei baze de date:-furnizarea n timp util a informaiilorsolicitate- costuri minime n prelucrarea i ntreinerea informaiei- capacitatea de a satisface, cu aceleai date necesitile informaionale ale unui

numr mare de utilizatori- flexibilitate - posibilitatea de adaptare la cerine noi, de a da rspunsuri lainterogri neprevzute iniial- asigurarea unei redundane minime a datelor- sincronizare exploatarea simultan a datelor de ctre mai muli utilizatori- confidenialitate asigurarea securitii datelor prin mecanisme de proteciempotriva accesului neautorizat- integritate faciliti de validare i recuperare a datelor deteriorate accidental- compatibilitate i expandabilitate posibilitatea de valorificare a eforturiloranterioare i anticiparea nevoilor viitoare- permisivitate prin ierarhizarea datelor dup criteriul frecvenei acceselor, saureorganizri care s creasc performanele BD.

Database Administration (ABD) - Administratorul bazei de date este responsabil curealizarea fizic a BD, care include proiectarea, implementarea, exploatarea intreinerea acesteia, securitatea, acordarea drepturilor de acces i controlul integritii.

1.6. SISTEMUL DE GESTIUNE AL BAZELOR DE DATE ( SGBD)Database Management System (DBMS)

SGBD - un ansamblu de programe (produs software) care permite definirea,actualizareai consultarea datelor din baza dedate.

Funciile unui SGBD:- definirea datelor (DDL Data Definition Language) permite definirea conceptual a

datelor, fr referire la modul de memorare- manipularea datelor (DML Data Manipulation Language i / sau interfaa cu limbaje

de programare) permite specificarea operaiilor de introducere, actualizare, tergerei interogare a datelor

7/28/2019 BDR [1]

5/71

5

- controlul integritii datelor- accesul concurent (folosirea simultan a datelor de mai muli utilizatori)- confidenialitatea informaiilor din BD- securitatea n funcionare (DCL Data Control Language)

Arhitectura funcional a unui SGBD

Interfa de generaia a 4-a

Pascal

Visual Fox

Oracle...

Definire de date

Manipulare

Confidenialitate

Securitate

Interfee

S

G

B

D

Sistem de exploatare

Baze de date Baze de date

Utilizator

7/28/2019 BDR [1]

6/71

6

Obiectivele unui SGBD:

- asigurarea independenei datelor- asigurarea unor faciliti sporite de utilizare a datelor- asigurarea unei redundane minime i controlate a datelor din BD (reducerea

redundanelor se face prin identificarea informaiilor comune)

- securitatea i confidenialitatea datelor- partajabilitatea datelor- integritatea datelor

Exist mai multe nivele de reprezentare (abstractizare i percepie) a datelor n baza dedate:

Nivelul conceptual este dat de viziunea adminstratorului bazei de date asupradatelor. Principalele aspecte la acest nivel:- cu instrumentele oferite de SGBD, administratorul bazei de date realizeaz

structura conceptual a BD;

- viziunea adminstratorului bazei de date este independent de aplicaiile care vor fidezvoltate (independen logic);- rezultatul la acest nivel este schema conceptual.

Nivelul logic este dat de viziuneaprogramatorului de aplicaii asupra datelor.Principalele aspecte la acest nivel:- programatorul de aplicaii realizeaz programele pentru descrierea i manipularea

datelor;- programele implementeazstructura extern (logic) a datelor;- structura extern este dedus din schema conceptual;- viziunea programatorului de aplicaii este independent de suportul tehnic de

informaie (independena fizic).

Nivelul fizic (intern) este dat de viziunea programatorului (inginerului) de sistemasupra datelor. Principalele aspecte la acest nivel:- programatorul de sistem realizeazstructura intern (fizic);- structura intern corespunde descrierii datelor pe supotul fizic de informaie;- structura intern este dedus din cea extern;- implementarea schemei interne se face cu ajutorul sistemului de gestiune a

fiierelor din SGBD i/sau din sistemul de operare, prin gestiunea fizic aperifericelor.

- Nivelul virtual (extern) este dat de viziunea utilizatorului final asupra sistemului.

7/28/2019 BDR [1]

7/71

7

Nivele de reprezentare a datelor

Domeniu de aplicaie

Parte a domeniuluiobiect de studiu

Entitate-asociere,semanticrelaional,funcional etc.

Relaional

n funcie de SGBD(tabel, nregistrare,segment, etc.)

Formalisme

SGBD

Baza de date

Scheme externeSchema conceptual

Schema logic

Schema fizic

CONCEPTUAL

LOGIC

FIZIC

Programe

Factoricantitativi

Constrngeri aleSGBD-ului

7/28/2019 BDR [1]

8/71

8

ntr-un raport al ANSI / SPARC (American National Standards Institute / StandardsPlanning And Requirements Committee) se disting cel puin trei nivele de reprezentare adatelor (conceptual, logic i fizic). Pentru fiecare nivel se asociaz o schem.

Comentarii.

Manipularea datelor presupune instrumente i mecanisme ce permit comunicarea:baz de date - utilizatori. Pentru manipularea datelor, SGBD-urile ofer o serie defaciliti, incluse n Data Manipulation Language (DML). Aciunea se exprim subforma unei fraze a limbajului, care este evaluati executat de SGBD.Interfeele sunt alte forme de comunicare care permit SGBD-ului s transmit datectre alte limbaje de programare (Pascal, C, C++, Cobol etc.). Aceste interfee permitaccesul i manipularea datelor dintr-o baz de date plecnd de la un program scrisntr-un limbaj de programare clasic (procedural).

Integritatea datelor. Conceptul de integritate a datelor este relativ la calitateainformaiei nregistrate. Constrngerile de integritate sunt specificate n definirea

schemei bazei de date. Accesul concurent. Datele dintr-o baz de date pot fi accesate concurentde mai muli

utilizatori. SGBD-ul trebuie s ofere mecanisme de gestiune a conflictelor de acces.

Confidenialitate. Punerea n comun a datelor pentru mai muli utilizatori impuneproblema confidenialitii. Confidenialitatea este asigurat prin nume de utilizator iparol care genereaz drepturi de acces difereniate.

Securitatea n funcionare. SGBD-ul trebuie s ofere mecanisme care s permitrepunerea rapid a bazei de date n stare operaional, n caz de incident hardware sausoftware. Aceste mecanisme sunt bazate pe nregistrarea operaiunilor realizate asuprabazei de date i reexecutarea lor automat n caz de incident.

1.7. BANCA DE DATE BAZA DE DATE

Exist n literatura de specialitate mai multe abordri ale celor dou concepte. Modul lorde tratare este departe de a fi unitar. n unele lucrri se consider:

A. Banca de date := Baza de date + SGBD

Ali autori extind noiunea de banc de datei considerbanca de date format din :

B. Banca de date := baza de date + hardware + SGBD + programele de aplicaii +utilizatorii

n carteaLart des bases de donnes, autoriiMiranda S.M., Busta J.M. fac distincia ntrecele dou concepte:

7/28/2019 BDR [1]

9/71

9

C. Baza de date conine date primare care sunt exploatate cu ajutorul unui SGBD. ncazul unei interogri, sistemul de gestiune al bazei de date furnizeaz direct rspunsul.Banca de date date conine date refereniale i accesul este asigurat cu ajutorul unuisistem documentar(SD). Sistemul documentarpermite o direcionare ctre un text (carte,articol, ...) i dup consultare se obine rspunsul la interogarea formulat.

Exemplu [Miranda, 1988]:Dac se consider atributul stare de sntate avnd maimulte valori, printre care {...., nebun, ...} i formulm interogarea:Care a fost starea de sntate a preedintelui Wilson (SUA) ntre 1914-1918 ?Rspunsul dat de banca de date va fi de genul:Istoria contemporan a SUA, pag. 52, ed.1980, iar rspunsul efectiv se obine n urma consultrii lucrrii indicate. n cazul uneibaze de date rspunsul va fi direct valoarea atributului stare de sntate: nebun.

1.8. MODELE DE REPREZENTARE A DATELOR

Tipologia SGBD-urilor este n general funcie de tipurile de structuri ale datelor pe care lesuport.Dintre modelele cele mai ntlnite amintim [Ionescu, 2004]:

modelul ierarhic modelul reea modelul relaional modelul orientat-obiect modelul obiect relaionalModelul ierarhic (Hierarchical Model) acesta a fost primul model folosit pentrudezvoltarea bazelor de date. Modelul permite reprezentarea claselor sau ansamblelor deobiecte printr-o structur ierarhic de nregistrri. Relaiile de tip "tat-fiu" ntre clasesunt de tip 1:N. Ansamblul claselor se constituie ntr-un arbore direcionat n carenodurile sunt tipurile de nregistrri, iar arcele sunt tipurile de legturi.

Modelul reea (Network Model) utilizeaz o structur de graf pentru definirea schemeiconceptuale a bazei de date. Nodurile grafului sunt tipurile de entiti, iar muchiilereprezint legturile dintre tipurile de entiti. Relaiile sunt de tipul M:N i se reprezintfr duplicarea nregistrrilor, fiecare nregistrare putnd fi referit de mai multenregistrri. Acest model este n prezent rar folosit pentru baze de date generale, careimplic operaii de interogare. Aplicarea modelului reea se ntlnete n bazele de dategrafice utilizate n modelarea realitii virtuale.

Modelul relaional (Relational Model) premite vizualizarea unei baze de date ca unansamblu de tabele bidimensionale. Modelul se bazeaz pe noiunea de relaie dinmatematic, care corespunde unei mulimi de entiti de acelai tip. Limbajele relaionalede manipulare a datelor sunt limbaje neprocedurale utilizatorul, de exemplu,formuleaz interogarea fr s indice procedura (algoritmul) de rezolvare. SGBD-urilerelaionale ofer un limbaj de programare unanim recunoscut i acceptat, limbajul SQL,

7/28/2019 BDR [1]

10/71

10

bazat pe algebra relaional. Pentru limbajul SQL au fost emise mai multe standarde dectreInternational Standardization Office (ISO).

Modelul orientat-obiect (Object Model) este un concept unificator n informatic, fiindaplicabil n programare, n proiectare hardware-ului, a bazelor de date, etc. Sistemele de

baze de date orientate obiect se bazeaz pe limbajele de programare orientate obiect. Auo utilizare limitat, mult mai redus dect cea a sistemelor de baze de date relaionale.Pentru bazele de date orientate obiect exist un limbaj standard standard de interogareOQL (Object Query Language).

Modelul obiect relaional (Object Relational Model) este considerat urmtorul mare valn dezvoltarea i ntreinerea bazelor de date. Construcia se poate realiza dezvoltndsistemul relaional prin adugarea caracteristicilor obiectuale necesare sau pornind de laun sistem orientat obiect i adugnd caracteristicile relaionale.

1.9. SCURT ISTORIE A BAZELOR DE DATE

1961 - apariia sistemuluiIDS (Integrated Data Storage, General Electric). Terminologiaintrodus (tipuri de nregistrri i tipuri set) va fi utilizat n modelul reea prezentat la"Conference On DAta SYstems and Languages Data Base Task Group" (CODASYLDBTG).1965-1970 - dezvoltarea sistemelor de gestiune a fiierelor generalizate.IBMa dezvoltatmodelul iererhic i sistemulIMS (Information Management System). n acceasi perioadapareIMS DB/DC (DataBase/DataCom) care suport modelul reea.n anii 70, domeniul se dezvolt foarte mult, ajungnd s fie disciplin universitari decercetare. Astfel apar numeroase produse comerciale care implementeaz propunerileraportului CODASYL DBTGi: ISD II (HoneyWell), DMS1100 (UNIVAC), DMS II(Burroughs), etc.1970 - apare modelul relaional de date1971 - publicarea raportului CODASYL DBTG1972 prima conferin internaional organizat de ACM SIGMOD (Association ofComputing Machinery, Special Interest Group on Management Of Data)1975 - prima conferin internaional VLDB (Very Large Data Base); publicarearaportuluiANSI-SPARC1976 - publicarea modeluluiEntitate - Asociere1975 - 1980 - dezvoltarea sistemelor relaionale experimentale: SYSTEM-R (IBM) iINGRES (Berkeley, University of California)1980 - .... apariia i comercializarea a numeroase SGBD-uri relaionale ce au nlocuitSGBD-urile ierarhce i reea. SGBD-urile pot fi utilizate pe microcalculatoare i serealizeaz sisteme din generaia a patra cu instrumente i interfee multiple.1990 - .... se dezvolt numeroase produse a cror complexitate crete, la un pre tot maisczut: PowerBuilder (SYSBASE), Oracle Developer, VB (Microsoft), etc. Se dezvoltmodelul client-server, ce se folosete n tot mai multe aplicaii economice. Se dezvoltiproduse sotftware ca Excel/ Access (Microsoft) pentru scop personal de o complexitatemai sczut.

7/28/2019 BDR [1]

11/71

11

1990-1995 - apar baze de date pentru Internet. Sunt utilizate conceptele client-server, iastfel complexitatea Internet-ului crete exponenial.2000 - pentru aplicaiile pe Internetapar o serie de instrumente cum ar fi Active ServerPages, Java Servlets, JDBC, Enterprise Java Beans, ColdFusion, Dream Weaver, Oracle

Developer 2000, Apache, MySQL. Se dezvolt procesarea tranzaciilor online (OLTP)

precum i procesarea analitic de tip OLAP.Dup 2000 - se dezvolt aplicaii pe arhitectura client-server pentru PDA-uri, tranzaciicu POS-uri, telefoane mobile. Companiile cele mai reprezentative din domeniu rmn:IBM, Microsofti Oracle.

1.10. BAZE DE DATE RELAIONALE

Conceptul de baze de date relaionale (BDR) apare n lucrarea lui E.F.CODD (IBM), ARelational Model for Large Shared Data Banks, (1970). Modelul de date relaional a fostperfecionat n anii urmtori de C.J. DATE, R. BOYCE, R. FAGIN, W.W. ARMSTRONG.

Caracteristicile modelului relaional: datele sunt percepute de utilizatori ca tabele simplitate i precizie n definirea elementelor de baz (relaii, atribute, domenii) operatorii relaionali genereaz un tabel rezultatdin tabelele operanzi restriciile de integritate, normalizarea relaiilor, controlul concurenei permit crearea

structurii datelor i a prelucrrii lor ntr-un mod consistent i asigur integritatea iprotecia acestora.

Definiia 1.1. BDR este un ansamblu organizat de tabele (relaii)mpreun cu legturiledintre ele.

Avantajele BDR fa de fiiere (sistemefile-based) [Velicanu, 2003]:

CRITERIU BDR FIIEREIndependena datelor logici fizic fizicNivele de structurare conceptual, logic, fizic logic i fizicDeschidere i portabilitate mare micReprezentarea i utilizareadatelor

simplificat prin model complicat

Structura datelor se pstreaz n dicionarul datelor n programe

E.F.CODD formuleaz13 reguli pentru evaluarea performanelor unui sistem de gestiune

a bazelor de date relaionale(SGBDR). Aceste reguli exprim cerinele maximale ca unSGBDR s fie relaional.

Condiiile minimale ca un SGBD s fie relaional, pot fi formulate astfel: s implementeze modelul de date relaional prinDDL (Data Definition Language) i

DML (Data Manipulation Language)

s implementeze un limbaj de interogare relaional

7/28/2019 BDR [1]

12/71

12

Arhitectura funcional a unui SGBD relaional

Elementele de baz utilizate pentru a descrie datele n modelul relaional din punct devedere formal, uzual sau fizic sunt:

Formal Uzual Fizic

relaie tablou fiiertuplu linie articolatribut coloan cmpdomeniu tip de dat tip de dat

Interfaa utilizatorului

Gestiunea vederilor

Integritatea semantic

Autorizarea accesului

Optimizarea cererilor

Gestiunea planurilorde execuie

Controlul execuiei

Executareaoperatorilor algebrici

Gestiunea buffer-ului

Mecanisme de acces

Gestiunea accesuluiconcurent

Jurnalizarea

Control

Tratareacererilor

Gestiunea

accesului

Securitate

REZULTATE

7/28/2019 BDR [1]

13/71

13

Principalele concepte utilizate n bazele de date relaionale

Definiia 1.2. Domeniul este un ansamblu de valori care poate fi definit explicit prinenumerarea tuturor valorilor sau implicit prin precizarea proprietilor pe care le auvalorile domeniului respectiv.

Exemplu:

D1 = {MK, ECTS, FB, CIG, IE, MN}[ ]{ }100,0x,Nx|xD2 =

D3 = {0, 9, 19}- domeniulD1 este definit explicit prin enumerarea programelor de studii care au n

plan disciplinaBaze de date;- domeniulD2 este definit implicit prin specificarea proprietilor care pot fi luate

de valorile domeniului;- domeniul D3 este definit explicit prin enumerarea valorilor posibile (n procente)

ale cotelor de TVA.

( ){ }nn11n21n21 DV,...,DVunde,V,...,V,VD...DD =

Fiecrui domeniu i se asociaz un atribut: ( ) iiii DAf,DA:f

Definiia 1.3. Relaia poate fi definit ca o mulime de tupluri ce aparine produsuluicartezian n21 D...DD , astfel spus n21 D...DDR .

Relaia se poate memora ntr-o tabel bidimensional:

R A1 A2 ..... Ant1 a11 a12 a1n

t2 a21 a22 a2n

... ... ... ...

tm am1 am2 ..... amn

Liniile tabelului formeaz elementele relaiei numite i tupluri.Notm: tuplul i prin >=< in2i1ii a,...,a,at .

Schema relaiei (schema relaional) este un element invariant n timp i este dat demulimea numelor atributelor corespunztoare unei relaii. Pentru fiecare atribut seprecizeaz domeniul asociat.Notm schema unei relaii cu: [ ]nn2211 D:A,...,D:A,D:AR

sau pe scurt: [ ]n21 A,...,A,AR .

Schema bazei de date relaionale este dat de mulimea tuturor schemelor relaionalecorespunztoare unei aplicaii, iar coninutul curent al relaiilor la un moment dat senumete baz de date relaional.

Cardinalul unei relaii este dat de numrul de tupluri din relaie.Gradul unei relaii (aritatea relaiei) este dat de numrul de atribute din relaie.

7/28/2019 BDR [1]

14/71

14

Relaia virtual (relaie derivat, viziune) este definit implicit pe baza altor relaii, prinintermediul unei expresii relaionale. Stabilirea efectiv a tuplurilor care compun relaiavirtual se realizeaz prin evaluarea expresiei relaionale n momentul n care utilizatorulapeleaz la aceast relaie.

Domenii compatibile cu reuniunea domeniile au acelai grad (aritate) i atributelecorespondente iau valori n aceleai domenii.

Relaia se prezint ca o mulime de tupluri. Logic, aceast mulime nu poate conineelemente identice, cu alte cuvinte, relaia nu poate avea tupluri duplicate. Necesitateaidentificrii unui tuplu a condus la noiunea de cheie.

Definiia 1.3. Cheia unei relaii reprezint ansamblul minimal de atribute cu rol deidentificare unic a tuplurilor dintr-o relaie. ntr-o relaie pot exista mai multe atribute /combinaii de atribute cu rol de identificare unic a tuplurilor, exist deci mai mulicandidai cheie. Dintre acetia ABD-ul alege cheia primar, celelalte devin chei

secundare sau alternante. Orice relaie are cel puin o cheie.Cheia simpleste cheia format dintr-un singur atribut, iar cheia compus este formatdin mai multe atribute.

Domeniul primareste domeniul pe care este definit cheia primar.

Cheia externeste un atribut /grup de atribute dintr-o relaie, ale crui valori sunt definitepe domeniul primaral altei relaii.

Relaia primar. O relaieRP esteprimar, dac exist o alt relaieR, legat semanticde ea, care are drept cheie extern, cheia primar a relaiei considerate (RP).

Exemple.

1. Fie relaia STUDENT [nr_matricol, nume, facultate, grupa, sectia, CNP, adresa]Atributele nr_matricol i CNP au rol de identificare unic a tuplurilor din relaie;reprezint candidai cheie. Alegem drept cheie primar atributul nr_matricol, care aredomeniul format din 4 caractere numerice i este mai uor de operat. Atributul CNP,format din 13 caractere numerice devine cheie secundar (alternant).

2. Se consider relaiile:PRODUSE [cod_produs, denumire, um]CONTRACTE [nr_contract, cod_produs, cod_client, data, cantitate, pret_unitar]CLIENTI [cod_client, nume, CUI, adresa, cont, tel, email]

relaia PRODUSE are cheia primar - cod_produs;

7/28/2019 BDR [1]

15/71

15

relaia CONTRACTE are cheia primar - nr_contract, iaratributele cod_produsicod_clientsunt chei externe ;

relaia CLIENTI are cheia primar - cod_client, iar atributul CUI este cheiesecundar;

PRODUSE i CLIENTI sunt relaii primare.

1.11. RESTRICIILE DE INTEGRITATE MINIMALE

Restriciile de integritate minimale sunt reguli pe care trebuie s le satisfac datele dinbaza de date.

A.Integritatea domeniului const din controlul sintactic i semantic al unei dateoarecare i se refer la definiia tipului domeniului. De exemplu, n cazul unuidomeniu definit explicit prin enumerarea valorilor, trebuie s ne asigurm cvalorile atributului respectiv fac parte din mulimea enumerat. Sau, n cazul unui

domeniu definit implicit, se poate verifica dac numrul unei facturi aparine unuiinterval dat.

B.Integritatea entitii se refer la restricii asupra cheii primare. Aceasta trebuies fie unici nenul(atributele cheii primare trebuie s fie diferite de valoareanull) .

C.Integritatea referirii impune ca valorile cheii externe s figureze printre valorilecheii primare din relaia referit (relaia primar).

7/28/2019 BDR [1]

16/71

16

CAP.2. ALGEBRA RELAIONAL

Interogarea (query) este operaia prin care se obin datele dorite dintr-o baz de date,selectate conform unor criterii. Deoarece aceast operaie este cea mai importantoperaie, limbajele de manipulare a datelor sunt denumite i limbaje de interogare.

Pentru formularea conceptual a interogrilor n bazele de date relaionale s-au dezvoltatdou limbaje abstracte de interogare: algebra relaionali calculul relaional.

Algebra relaional (relational algebra), introdus de Codd n 1970, definete cadrulformal al limbajelor relaionale pentru baze de date. Algebra relaional introduce ocolecie de operatori algebrici care se aplic relaiilor (tabelelor). Fiecare operaie dinalgebra relaional are drept operanzi una sau mai multe relaii, iar rezultatul este tot orelaie. Aceast uniformitate (proprietatea algebric de nchidere) permite aplicarea decombinaii de operatori relaiilor.

Prin analogie cu un compilatorcare, plecnd de la unprogram surs produce unprogramexecutabil, rezultatul compilrii unei interogri (cereri) de ctre un SGBD relaional esteo expresie algebric care va fi evaluat. n cadrul modelului relaional se considerlimbaje relaionale complete numai acele limbaje de interogare care permitimplementarea tuturor operaiilor prevzute de unul din limbajele abstracte de interogare.Limbajele de interogare reale implementate n sistemele de baze de date relaionale suntlimbaje definite pe baza limbajelor abstracte de interogare.

Cunoaterea limbajului abstract de interogare bazat pe algebra relaional esteobligatorie pentru nelegerea aprofundat a modului de execuie a interogrilor.

Algebra relaionalconine dou tipuri de operaii: operaii pe mulimi: reuniunea, intersecia, diferena i produsul cartezian. Pentru

a determina reuniunea, intersecia i diferena a dou relaii, acestea trebuie s fiecompatibile cu reuniunea (trebuie s aib acelai numr de atribute i atributelecorespondente s fie definite pe domenii compatibile, adic s aib formatuldatelor identic).

operaii relaionale: selecia, proiecia, jonciunea i diviziunea.Operaii de baz - reprezint un ansamblu minimal de operaii, n sensul c niciuna dinoperaii nu poate fi exprimat ca o combinaie a celorlalte operaii. Pentru algebrarelaional exist mai multe ansambluri minimale. n continuare prezentarea va consideraca operaii de baz: trei operaii pe mulimi: reuniunea, diferena,produsul cartezian idou operaii relaionale unare: selecia i proiecia. Operatorii de intersecie, jonciuneidiviziune pot fi obinui din cei cinci operatori de baz.

Not. n definirea operatorilor algebrei relaionale vom nota cu:t un tuplu din relaiaRt(A) subtuplu dinR relativ la atributulA.

7/28/2019 BDR [1]

17/71

17

Fiecare operator al algebrei relaionale va fi descris prin signatur. Signatura indicnumrul i tipul operanzilor, precum i tipul rezultatului.

2.1. OPERATORII ALGEBREI RELAIONALE

1.

UNION reuniunea a dou relaii R1 i R2, compatibile cu reuniunea, este dat demulimea tuplurilor care aparin fie relaiei R1, fie relaiei R2, fie ambelor relaii.Tuplurile care aparin ambelor relaii se introduc n reuniune o singur dat, adic nuse duplic.

Signatura: latieRelatieRelatieRe

{ }21213 RtsauRt|tRRR == U

R3

R1 R2

Exemplu.

R1 A Ba bc dx y

R2 A Bd fx yh rc d

R3 A Ba bc dx yd fh r

Sintaxa:

UNION (R1, R2) - rezultatul este o relaie.

2. DIFFERENCE diferena a dou relaiiR1iR2, compatibile cu reuniunea, este datde mulimea tuplurilor care aparin relaieiR1i nu aparin relaieiR2..

U

7/28/2019 BDR [1]

18/71

18


{ }21213 RtsiRt|tRRR ==

R3

R1 R2

Exemplu.

R1 A Ba bc dx y

R2 A Bd fx yh rc d

R3 A Ba b

Sintaxa:

DIFFERENCE (R1, R2) - rezultatul este o relaie.

3. INTERSECT intersecia a dou relaiiR1iR2, compatibile cu reuniunea, este datde mulimea tuplurilor care aparin att relaieiR1, ct i relaieiR2.


{ }21213 RtsiRt|tRRR == I

R3

_

7/28/2019 BDR [1]

19/71

19

R1 R2

Exemplu.

R1 A Ba bc dx y

R2 A Bd fx yh rc d

R3 A Bc dx y

Sintaxa:

INTERSECT (R1, R2) - rezultatul este o relaie.

Observaie. Intersecia poate fi exprimat cu ajutorul operaiei de diferen(operaie debaz):

( ) ( )RSSSRRSR ==I

4. PRODUCTprodusul cartezian a dou relaii R1iR2, produce o nou relaie care areca atribute, reuniunea atributelor din cele dou relaii (atributele comune vor fi luateseparat, calificrile fiind fcute cu numele relaiei), iar fiecare element din R1 secombin (concateneaz) cu fiecare element dinR2.


( ){ }221121213 RtsiRt|t,tRRR ==

R3

I

R1 R2

7/28/2019 BDR [1]

20/71

20

Exemplu.

R1 A Ba bc dx y

R2 Aad

R3 R1. A B R2.Aa b ac d ax y aa b dc d dx y d

Sintaxa:

PRODUCT (R1, R2) - rezultatul este o relaie.

5. SELECT este o operaie unar de restricie care selecteaz din tuplurile relaiei R,acele tupluri care satisfac o condiie specificat. Condiia este o expresie logic(predicat) specificat asupra atributelor relaiei R. Condiia poate cuprinde nume deatribute, constante, operatori logici (and, or, not), operatori aritmetici de comparare(, =, , , ).

Signatura: latieReicalogExpresielatieRe

( ) ( ){ } AtsiRt|tRS conditie == unde: t(A) definete condiia de selecie ( este un operator aritmetic decomparare, iar un tuplu care poate fi nlocuit de un atribut sau valoarea unuiatribut).

S

R

Exemplu.

condiie

7/28/2019 BDR [1]

21/71

21

( )RS 'x'B==

R A B Cx y zt x a

z x bc u w

S A B Ct x az x b

Sintaxa:

SELECT (R; condiie) - rezultatul este o relaie.

Observaii.1. Aceast operaie nu trebuie confundat cu instruciunea SELECT, care este

instruciunea general de interogare din limbajele de manipulare a datelor.2. n termenii limbajului de interogare SQL, operaia de selecie realizeaz o

decupare pe orizontal a tabelei operand R.3. Cardinalul relaiei rezultat S este mai mic sau egal dect cardinalul relaiei R.Egalitatea poate apare n situaia n care condiia este adevrat pentru toatetuplurile din relaie.

6. PROJECT - este o operaie unar de restricie prin care se selecteaz din relaia R,numai acele atribute specificate explicit n cadrul operaiei. Relaia rezultat P vaavea ca atribute submulimea selectat.

Signatura: latieReatributeListalatieRe

Fie ]A,...,A,A[R n21= i { } { }n21k1 A,...,A,AA,...,A =

( ) ( ){ }k1 a,...,atRP ==

P

R

Exemplu.

( )RP C,A=

R A B C P A C P A C

lista atribute

7/28/2019 BDR [1]

22/71

22

x u xz x yx z xz y yx t x

x xz yx xz yx x

x xz y

Sintaxa:

PROJECT (R; lista atribute) - rezultatul este o relaie.

Observaii.

1. Dac n lista atributelor de proiecie exist o cheie a relaiei operand R, atuncirelaia rezultat are toate tuplurile distincte, adic relaiileRi P vor avea acelaicardinal.

2. Dac n lista atributelor de proiecie nu exist o cheie a relaiei operandR, atuncin relaia rezultat P pot apare tupluri duplicate care vor fi eliminate. n exemplulprezentat dup eliminarea tuplurilor duplicate din relaia intermediar P , s-aobinut relaia P care conine dou tupluri distincte.

3. Relaia rezultat P are gradul k, dat de numrul atributelor din list.4. n termenii limbajului de interogare SQL, operaia de proiecie realizeaz o

decupare pe vertical a tabelei operand R.

7. JOIN este o operaie definit pe dou relaii R1 i R2. Relaia rezultat R3 va ficonstruit prin concatenarea unor tupluri din R1 cu tupluri din R2 care satisfac o

anumit condiie (condiia de jonciune - ) specificat explicit n cadrul relaiei.Condiia de jonciune - este o expresie logic (predicat) specificat asupraatributelor relaiilor R1 i R2. Condiia de jonciune - poate cuprinde nume deatribute, constante, operatori logici (and, or, not), operatori aritmetici de comparare(, =, , , ).

Signatura: latieReresieexplatieRelatieRe

Fie relaiile ]B,A[R 11 = i ]C,B[R 22 = , undeB1iB2 sunt atribute definite peacelai domeniu. Atunci:

( ) ( ){ }2121213 BtBtsiRRt|tRRR ==

Observaie. Jonciunea se poate exprima n funcie de operaiile de baz: produscarteziani selecie astfel:

( )2121 RRRR =

7/28/2019 BDR [1]

23/71

23

Exemplu.

213 RRR = unde :R1.A > R2.A

R1 A B Ca x cb y cd z g

R2 D E A0 11 a1 13 a3 11 a

4 11 d6 12 d7 13 c

R3 R1. A B C D E R2. Ab y c 0 11 ab y c 1 13 ab y c 3 11 a

d z g 0 11 ad z g 1 13 ad z g 3 11 ad z g 7 13 c

Sintaxa:

THETA - JOIN (R1, R2; - expresie) - rezultatul este o relaie.

n continuare se prezint patru forme ale operaiei de jonciune.

7.1. EQUI JOIN este un caz particular al lui THETA JOIN, cnd este egalitate.

Exemplu.

213 RRR = unde :R1.A = R2.A

R1 A B Ca x cb y c

d z a

R2 D E A0 11 a1 13 a

3 11 a4 11 d6 12 d7 13 c

R3 R1. A B C D E R2. Aa x c 0 11 aa x c 1 13 a

a x c 3 11 ad z a 4 11 dd z a 6 12 d

Sintaxa:

EQUI - JOIN (R1, R2; - expresie) - rezultatul este o relaie.

R1 R2

R3

7/28/2019 BDR [1]

24/71

24

7.2. NATURAL JOIN este o jonciune pe egalitate (EQUI JOIN) pentru toate

atributele cu acelai nume din cele dou relaii, urmat de o proiecie pe reuniuneaatributelor celor dou relaii.n cazul EQUI JOIN schema relaiei rezultat conine toate atributele celor doioperanzi i rezult c n fiecare tuplu al relaiei rezultat vor exista cel puin douvalori egale. Introducereajonciunii naturale va elimina aceast redundan.

Schema relaiei rezultatR3 se obine prin reuniunea atributelor celor dou relaiiR1iR2 (atributele cu acelai nume se iau o singur dat), iar extensia relaieiR3 va coninetuplurile obinute prin concatenarea tuplurilor din R1 cu tupluri din R2, care auaceleai valori pentru atributele cu acelai nume.

Jonciunea natural este jonciunea cea mai utilizat n practici poate fi definit cuajutorul operaiilor de baz:proiecie, seleciei produs cartezian.Dac se noteaz cu: - condiia de egalitate ntre valorile atributelor din intersecia schemelor relaiilor

R1 iR2(coloanele comune),atr- reuniunea atributelor celor dou scheme (atributele cu acelai nume se iau o

singur dat),atunci:

( )( )21atr213 RRRRR ==

Exemple.

A. Se dau relaiile: R1[A, B, C]iR2 [B, C, D]( ) ( )C.RC.RandB.RB.R 2121 ===

atr = A, B, C, D

R1 A B Ca b cd b cb b fc a d

R2 B C Db c db c ea d b

R3 A B C Da b c da b c ed b c dd b c e

c a d b

B. Se dau relaiile: R1 [A, B, C]iR2 [D, E, A]A.RA.R 21 ==

atr = A, B, C, D, E

7/28/2019 BDR [1]

25/71

25

R1 A B Ca x cb y cd z a

R2 D E A0 11 a1 13 a3 11 a4 11 d6 12 d7 13 c

R3 A B C D Ea x c 0 11a x c 1 13a x c 3 11d z a 4 11d z a 6 12

Sintaxa:

NATURAL - JOIN (R1, R2; atribut(e) jonciune*)- rezultatul este o relaie.

* pentru mrirea claritii va apare atributul / atributele de jonciune.

Observaie.

Selecia este un caz particular dejonciune natural a unei relaii cu o relaie constant.nelegem prin relaie constant o relaie care are un singur tuplu, eventual redus la osingur valoare.

7.3. SEMI JOIN- este jonciunea dintre dou relaiiR1iR2 , urmat de o proiecie peatributele relaieiR1. Semi jonciunea conserv atributele unei relaii participante lajonciune (R1). Semi jonciunea mai poate fi privit ca o generalizare a operaiei deselecie, rezultatul fiind o selecie asupra relaieiR1 , realizat pe baza valorilor dinR2ale atributului de jonciune.

Exemplu.

213

RRR

= unde :R1.A = R2.A

R1 A B Ca x cb y cd z a

R2 D E A0 11 a1 13 a3 11 a4 11 d6 12 d7 13 c

R3 A B Ca x ca x ca x cd z ad z a

R1 R2

R3

7/28/2019 BDR [1]

26/71

26

Sintaxa:

SEMI - JOIN (R1, R2; - expresie) - rezultatul este o relaie.

Observaie.Considerm jonciunea natural dintre ]B,A[R 11 = i ]C,B[R 22 = , unde B1iB2 sunt atributele de jonciune.

DacB1 = B2 = , atunci jonciunea corespundeprodusului cartezian. DacA = C = , atunci jonciunea corespunde interseciei. Dac A = sau C = (dar nu amndou), atunci operaia este o semi-

jonciune.

7.4. OUTER JOIN. Jonciunea dintre dou relaiiR1iR2 poate conduce la pierdere de

tupluri, dac relaiile participante la jonciune nu au proiecii identice pe atributul dejonciune, adic nu au aceleai valori n relaiile care se joncioneaz. Relaia rezultatR3 coninejonciunea naturaldintreR1iR2, la care se adaug tuplurile dinR1iR2,care nu au participat la jonciune. n aceste tupluri se va atribui valorea null pentruatributele relaiei corespondente.

Exemplu.

2ext13 RRR =

R1 A B Ca x cb y c

d z a

R2 D E A0 11 a1 13 a3 11 a4 11 d6 12 d7 13 c

R3 A B C D Ea x c 0 11a x c 1 13a x c 3 11d z a 4 11d z a 6 12b y c null null

c null null 7 13

R1 R2

R3

ext

7/28/2019 BDR [1]

27/71

27

Observaii. n urmajonciunii naturale se pierd informaiile din tuplurile < b, y, c > dinR1 i < 7, 13, c > din R2. Aceste tupluri se adaug n cazul jonciunii externe i secompleteaz cu null pe atributele relaiei corespondente.

Sintaxa:

OUTER - JOIN (R1, R2; atribut(e) jonciune) - rezultatul este o relaie.

8. DIVISION- este o operaie definit pe dou relaii care au schema R1 [A1,A2,..., An] i R2 [Ap+1, Ap+2,..., An]. Relaia rezultat 213 RRR = are schema R3 [A1,

A2,..., Ap]i este format din toate tuplurile care, concatenate cu fiecare tuplu dinR2 ,dau ntotdeauna un tuplu dinR1.

Notm: ATR1= {A1, A2,..., Ap+1, Ap+2,..., An}ATR2= {Ap+1, Ap+2,..., An}

Definiia 1. 21 RRt dac 1122 RtRt , astfel nct ( ) tt1ATRATR 21 =

i ( ) 21ATR tt2 = .

Definiia 2. Diviziunea se poate exprima n funcie de operaiile de baz: produscartezian, difereniproiecie astfel:

( ) ( ) 121ATRATRATRATR1ATRATR21 RRRRRR 212121 =


R3

R1 R2

Problem (Exemplu de diviziune).

Fie relaia R1 [K, P]unde atributul Kare ca valoricodurile angajailor unui institut decercetare, iar atributul P conine codurile proiectelor n derulare. Un cercettor poatelucra la unul sau mai multe proiecte. S se determine codurile angajailor angrenaisimultan n proiectele P3i P4.

7/28/2019 BDR [1]

28/71

28

Rezolvare.

Construim relaiaR2[P]care va conine dou tupluri: i .Codurile angajailor care lucreaz la proiectele P3i P4 sunt date de rezultatul diviziunii

21 RR .

R1 K P17 P117 P217 P3

17 P4

29 P129 P353 P3

53 P480 P3

R2 PP3P4

Calculm diviziunea conform definiiei 2: 2121 QQRR =

Pasul 1. Calculm ( )1ATRATR1 RQ 21 = Pasul 2. Calculm 21 RQS =

Q1 K1729

5380

S K P17 P329 P3

53 P380 P317 P429 P4

53 P480 P4

Pasul 3. Calculm 1RST = Pasul 4. Calculm ( )TQ 21 ATRATR2 =

T K P29 P480 P4

T K2980

Pasul 5. Calculm 2121 QQRR =

7/28/2019 BDR [1]

29/71

29

R1:R2 K1753

Rezultatul interogrii: angajaii cu codul i lucreaz simultan n proiectelei .

Sintaxa:

DIVISION (R1, R2) - rezultatul este o relaie.

2.2 OPERA

II DE CALCUL

La operaiile descrise anterior se pot aduga operaii de calcul pe relaii. Aceste operaiisunt justificate de numeroasele interogri (cereri) care necesit operaii de calcul.Operaiile de calcul sunt implementate n toate limbajele de interogare. Aceti operatoride calcul formeaz deci o extensie a operatorilor de bazi nu pot fi exprimai cu ajutorulacestora.

1. COUNT - este o operaie care permite numrarea tuplurilor dintr-o relaie (liniilordintr-o tabel) care au aceeai valoare pe atributul considerat (sau aceleai valori peatributele considerate). Relaia rezultant va conine numai atributul (atributele) deregrupareXi, iar tuplurile vor fi formate din valorile distincte i numrul de apariii.

Notm:)R(COUNTT

n1 X,...,X= , unde X1,...,Xn sunt atributele de regrupare.

T

R

Operatorul COUNT

Count...

7/28/2019 BDR [1]

30/71

30

Exemplu.

R A B C

a n 17b o 14c n 17d p 13e m 20f m 10

CountB(R) B Count

n 2m 2o 1p 1

CountB,C(R) B C Count

n 17 2m 20 1m 10 1o 14 1p 13 1

Dac nu este precizat niciun atribut de regrupare, operaia COUNTva determina numrulde tupluri din relaie:

Count(R) Count6

Sintaxa:

COUNT (R; X1, X2, ..., Xn) - rezultatul este o relaie;

COUNT (R) - rezultatul este un numr (poate fi interpretat i ca o relaie cu unsingur atribut i un singur tuplu, care are ca valoare numrul de linii din tabel).

2. SUM este o operaie care permite efectuarea sumei valorilor atributului Y pentrufiecare din valorile diferite ale atributelor de regrupareX1,...,Xn . Atributul Ytrebuie s fienumeric.

Notm:)Y,R(SUMT


Dac nu este precizat niciun atribut de regrupare, operaia SUM va determina sumavalorilor atributului Y.

T

R

Operatorul SUM

Sum...

7/28/2019 BDR [1]

31/71

31

Exemplu.

R A B Ca n 17b o 14c n 17d p 13e m 20f m 10

SumB(R,C) B Sumn 34m 30o 14p 13

Sum(R,C) Sum91

Sintaxa:

SUM (R, Y; X1, X2, ..., Xn) - rezultatul este o relaie;

SUM (R, Y) - rezultatul este un numr (poate fi interpretat i ca o relaie cu unsingur atribut i un singur tuplu, care are ca valoare suma valorilor atributului Ydin toate liniile tabelei).

3. MEAN este o operaie care permite efectuarea mediei aritmetice a valoriloratributului Y pentru fiecare din valorile diferite ale atributelor de regrupare X1,...,Xn .

Atributul Ytrebuie s fie numeric.

Notm:)Y,R(MEANT


Dac nu este precizat niciun atribut de regrupare, operaia MEANva determina mediaaritmetic a valorilor atributului Y din toat relaia.

T

R

Mean..

7/28/2019 BDR [1]

32/71

32

OperatorulMEAN

Exemplu.

R A B Ca n 17b o 9c p 21d n 13e p 20

f p 10

MeanB(R,C) B Sumn 15o 9p 17

Mean(R,C) Sum15

Sintaxa:

MEAN (R, Y; X1, X2, ..., Xn) - rezultatul este o relaie;

MEAN (R, Y) - rezultatul este un numr (poate fi interpretat i ca o relaiecu un singur atribut i un singur tuplu, care are ca valoare media aritmetic avalorilor atributului Y din toate liniile tabelei).

4. MAXi MIN- este o operaie care permite determinarea valorii maxime / minime aatributului Y pentru fiecare din valorile diferite ale atributelor de regrupare X1,...,Xn .Atributul Ytrebuie s fie numeric.

Notm:)Y,R(MAXT


)Y,R(MINTn1 X,...,X

= , unde X1,...,Xn sunt atributele de regrupare.

Dac nu este precizat niciun atribut de regrupare, operaia MAX (MIN) va determinamaximul (minimul) valorilor atributului Y din toat relaia.

T

7/28/2019 BDR [1]

33/71

33

R

OperatorulMAX (MIN)

Exemplu.

R A B C

a n 17b o 9c p 21d n 13e p 20f p 10

MinB(R,C) B Min

n 13o 9p 10

Max(R,C) Max

21

Sintaxa:

MAX (R, Y; X1, X2, ..., Xn) - rezultatul este o relaie;

MAX (R, Y) - rezultatul este un numr (poate fi interpretat i ca o relaie cu unsingur atribut i un singur tuplu, care are ca valoare maximul valorilor atributuluiY din toate liniile tabelei).

Observaie. Pentru operaiaMIN sintaxa este analoag.

Max...

7/28/2019 BDR [1]

34/71

34

CAP. 3. LIMBAJUL SQL

SQL(Structured Query Language - Limbaj Structurat de Interogare) este un limbaj deprogramare neprocedural specific bazelor de date.

Limbajul SQL este standardizatANSI-ISO (fiind cel mai popular limbaj de manipulare abazelor de date relaionale) i poate fi utilizat n: MySQL, SQL Server, MS Access,Oracle, DB2, etc.

Pe lng versiunile standardizate ale limbajului SQL, exist i o mulime de dialecte ivariante caracteristice diferitelor SGBD-uri.

Limbajul SQL permite: manipularea structurii bazelor de date manipularea datelor coninute

Principalele instuciuni de definire a datelor sunt: CREATE TABLE ALTER TABLE DROP TABLE

3.1. Instruciunea CREATE TABLE

Instruciunea CREATE TABLE este utilizat pentru a crea o nou tabel. Aceastopiune este utilizat cu precdere dac mediul de lucru nu posed instrumente pentrucrearea i modificarea tabelelor ntr-o manier mai facil, aa cum are spre exemplu

Microsoft Access.

Sintaxa general a instruciunii CREATE TABLE este:

CREATE TABLE nume_tabela (c1 d1 [constrngeri_coloan],c2 d2 [constrngeri_coloan],...cn dn [constrngeri_coloan],[constrngeri_coloan])

unde: c1, c2, ... , cn - reprezint coloanele tabelei

d1, d2, ... , dn - reprezint domeniile fiecrui cmp

Exemplul 1. Dac dorim crearea unei tabele cu numele angajati1, cu urmtoarelecmpuri:

7/28/2019 BDR [1]

35/71

35

vom avea:

CREATE TABLE angajati1 (cod_sal int, nume varchar(250), adresa varchar (250),localitate varchar(250), sal_brut int, cod_dep varchar (10),data_angajarii date)

Rezultatul afiat pentru exemplul 1

3.2. Instruciunea ALTER TABLE

Pentru modificarea strcturii unei tabele se utilizeaz instruciunea ALTER TABLE.Aceast instruciune permite adugarea sau tergerea unor cmpuri, modificareadomeniilor unor cmpuri, precum i adugarea sau tergerea unor constrngeri ale

tabelei.Instruciunea ALTER TABLE are urmtoarele sintaxe:- pentru adugarea unui nou cmp:

ALTER TABLE nume_tabela ADD nume_coloan domeniu

- pentru tergerea unui cmp

ALTER TABLE nume_tabela DROP nume_coloan

- pentru modificarea constrngerilor unu cmpALTER TABLE nume_tabelaALTER COLUMN nume_coloan domeniu

Exemplul 2. Dac dorim adugarea n tabela angajati1 a unui nou cmp numit bonus detip integervom avea:

ALTER TABLE angajati1 ADD bonus int

7/28/2019 BDR [1]

36/71

7/28/2019 BDR [1]

37/71

37


3.3. Instruciunea DROP TABLE

Pentru tergerea unei tabele se utilizeaz instruciunea DROP TABLE. Aceastinstruciune va face tergerea efectiv a ntregii tabele cu toate datele coninute. Sintaxageneral a instruciunii DROP TABLE este:

DROP TABLE nume_tabela

Exemplul 5. Dac dorim tergerea tabelei angajati1 vom avea:

DROP TABLE angajati1

Principalele instruciuni de manipulare a datelor sunt: SELECT INSERT

UPDATE DELETE

3.4. Instruciunea SELECT

Instruciunea SELECT este instruciunea de interogare a datelor din limbajul SQL.Utilizarea acestei instruciuni genereaz o tabel virtual, numitvedere (query). n queryse regsesc toate informaiile dorite din unul sau mai multe tabele ale bazei de date.

Sintaxa general a instruciunii SELECT este:

SELECT [DISTINCT] c1, c2, ... , cn[FROM t1, t2, ... , tm] [WHERE condiie] [clauze secundare]

unde: c1, c2, ... , cn - sunt coloanele dorite din tabelele specficate n clauza FROMt1, t2, ... , tm - sunt tabelele din care se face selecia

7/28/2019 BDR [1]

38/71

38

Rezultatul seleciei este format din coloanele c1, c2, ... , cn cu datele rezultate dinprodusul cartezian al tabelelor t1, t2, ... , tm pentru care se respect eventuala condiiespecificat n clauza WHERE.

Clauza SELECT definete coloanele tabelei rezultat.

Clauza FROM indic unu sau mai multe tabele ce conin datele dorite. Clauza WHERE definte condiia sau condiiile ce trebuie ndepline de datele dinclauza SELECT.

ntre clauzele secundare amintim: ORDER BY, GROUP BY, HAVING.

Exemplele urmtoare vor fi construite pentru tabela ANGAJATI:

Tabela ANGAJATI

Exemplul 6. Dac dorim afiarea tuturor datelor din tabel vom avea:

SELECT cod_sal, nume, adresa, localitate, sal_brut, cod_dep, data_angajariiFROM angajati

sau

SELECT * FROM salariati

Observaie. n acest al doilea caz, simblul * nlocuiete toate cmpurile din tabel.

Exemplul 7.Dac dorim afiarea tuturor angajailor din localitatea Brasov ce au salariulmai mare de 1200 vom avea:

SELECT cod_sal, nume, localitate, sal_brutFROM angajatiWHERE localitate="brasov" AND salariu_brut>1200

7/28/2019 BDR [1]

39/71

39


Exemplul 8. Dac dorim afiarea tuturor persoanelor angajate dup data de 01.01.2009vom avea:

SELECT cod_sal, nume, data_angajariiFROM angajatiWHERE data_angajarii>=#01-01-2009#


n clauza SELECT se pot utiliza urmtoarele funcii agregat: COUNT (numr liniile din tabela rezultat) SUM (calculeaz suma valorilor dintr-o coloan) MAX (returneaz valoarea maxim dintr-o coloan) MIN (returneaz valoarea minim dintr-o coloan) AVG (returneaz media aritmetic a valorilor dintr-o coloan)

Exemplul 9. Dac dorim afiarea numrului de angajai vom avea:

SELECT count(*)FROM angajati


7/28/2019 BDR [1]

40/71

40

Exemplul 10. Dac dorim afiarea mediei aritmetice a salariului brut vom avea:

SELECT AVG(sal_brut)FROM angajati


Instruciunea SELECT poate s nu conin nici clauza FROM, dac datele nu suntconinute de nicio tabel. n acest caz, instruciunea SELECT conine o list de expresiipe care le calculeaz.

Exemplul 11. Dac dorim afiarea rezultatului produsului 50x25 vom avea:

SELECT 50*25


Dac se dorete, n tabela rezultat se pot redenumi coloanele, sau se pot denumi anumiteexpresii, utiliznd clauza AS.

Exemplul 12.Dac dorim afiarea rezultatului produsului 50x25, iar numele tabelei s fieREZULTAT vom avea:

SELECT 50*25 AS rezultat


Clauza FROM este obligatorie, dac n clauza SELECT se dorete afiarea unor coloanedin tabele. Dac se dorete selectarea unor coloane din tabele diferite, acestea vor fi toateenumerate n clauza FROM, desprite prin virgul. n cazul n care un cmp apare n maimult de o tabel, atunci pentru a se cunoate din ce tabel se dorete respectivul cmp, laclauza FROM se va specifica i numele tabelei de forma:

nume_tabel.nume_cmp

7/28/2019 BDR [1]

41/71

41

n clauza WHERE se impun toate condiiile necesare pentru datele din tabela rezultat. nclauza WHERE se pot utiliza i operatorii logici (AND, OR, NOT) i paranteze.

Exemplul 13.Dac dorim afiarea angajailor din Brasov sau Predeal vom avea:

SELECT nume, localitateFROM angajatiWHERE localitate="brasov" or localitate="predeal"


Clauza ORDER BY face ordonarea liniilor din tabela rezultat dup coloana ce urmeazclauzei. Implicit, ordonarea se face n ordine cresctoare sau alfabetic dac tipulcmpului dup care se face ordonarea este de tip text. n cazul n care se dore te ordonareinvers lexicografic (descresctoare), atunci numele coloanei trebuie urmat de cuvntulDESC.

Exemplul 14.Dac dorim afiarea angajailor n ordine descresctoare vom avea:

SELECT numeFROM angajatiORDER BY nume DESC


7/28/2019 BDR [1]

42/71

42

Clauza GROUP BY se utilizeaz pentru gruparea rezultatelor funciilor agregat, nfuncie de valoarea unei sau mai multor coloane. Clauza GROUP BY se utilizeaz lasfritul instruciunii, fiind urmat de cmpul pentru care se face gruparea rezultatelorfunciei agregat.

Exemplul 15. Dac dorim numrul de angajai din fiecare localitate vom avea:SELECT COUNT(*) AS numar, localitateFROM angajatiGROUP BY localitate


Clauza HAVING se utilizeaz n loc de clauza WHERE, atunci cnd n instruciune seutilizeaz funcii agregat. Clauza HAVING este asemntoare clauzei WHERE, adicintroduce o condiie pe care trebuie s o respecte liniile din rezultat i n plus permiteutilizarea funciilor agregat n expresia condiional.

Exemplul 16. Dac dorim numrul de angajai din Brasov si Predeal vom avea:

SELECT COUNT(*) AS numar, localitate

FROM angajatiGROUP BY localitateHAVING localitate="brasov" OR localitate="predeal"


Subinterogrile reprezint instruciuni SELECT n alte interogri de tip SELECT.Numim aceast tehnic imbricare. Astfel, instruciunile SELECT se pot imbrica pe maimulte niveluri, o instruciune avnd ca argument rezultatul unei alte instruciuni, numiti subinterogare.

7/28/2019 BDR [1]

43/71

43

Exemplul 17. Dac dorim numele angajatului care are salariul egal cu salariul maximvom avea:

SELECT nume, sal_brutFROM angajati

WHERE sal_brut IN(SELECT MAX(sal_brut) FROM angajati)


Clauza IN i NOT IN specific dac valorile unui cmp aparin unei mulimi precizate.Aceast mulime poate fi format prin enumerarea elementelor sau printr-o subinterogare.

Exemplul 18. Dac dorim numele angajailor din Brasov, Bucuresti i Predeal vom avea:

SELECT nume, localitateFROM angajatiWHERE localitate IN ("brasov", "bucuresti", "predeal")


3.5. Instruciunea INSERT

Instruciunea INSERT este utilizat pentru introducerea datelor n tabel. Instruciunea

INSERT are urmtoarea sintax:

INSERT INTO nume_tabela (c1,c2,..., cn) VALUES (v1,v2,..., vn)unde: c1, c2, ... ,cn - reprezint coloanele din tabel n care se vor introduce datele

v1, v2, ... ,vn - reprezint valorile corespunztoare coloanelor c1, c2, ... ,cn

Observaie. ntre valori i numele coloanelor trebuie s existe o coresponden direct.

7/28/2019 BDR [1]

44/71

44

Exemplul 19. Dac dorim introducerea n tabela ANGAJAI a unui nou angajat cudatele: 18, EMILIA, str. O.Goga nr. 3, bucuresti, 1300, prod, 05.05.2009 vom avea:

INSERT INTO TABLE angajatiVALUES (18, "EMILIA", "str. O.Goga nr. 3", "bucuresti", 1300, " prod", #5/5/2009#);


Dac se dorete introducerea datelor n alt ordine dect cea implicit a coloanelor dintabel, sau nu se cunoate aceast ordine, trebuie specificat ordinea cmpurilor dupnumele tabelei.

Exemplul 20. Dac dorim introducerea n tabela ANGAJAI a unui nou angajat cudatele: 19, str. Agriselor, constanta, raluca, 1300, prod, 10.08..2009 vom avea:

INSERT INTO angajati ( cod_sal, adresa, localitate, nume, sal_brut, cod_dep,

data_angajarii )VALUES (19, "str. Agriselor nr. 3", "constanta", "RALUCA", 1300, " prod",#10/8/2009#);


7/28/2019 BDR [1]

45/71

45

3.6. Instruciunea UPDATE

Instruciunea UPDATE permite modificarea valorilor din coloanele unei tabele pentruanumite condiii. Sintaxa general este:

UPDATE nume_tabel SET c1=e1 [c2=e2 , ... ,n] [WHERE condiie]Clauza WHERE impune ca actualizarea valorilor s se fac doar asupra liniilor carendeplinesc o serie de condiii. Dac lipsete, se vor modifica toate liniile din tabel.

Exemplul 21. Dac dorim modificarea tuturor salariilor angajailor din departamentul"prod" la valoarea de 1000 lei vom avea:

UPDATE angajatiSET sal_brut=1000 WHERE cod_dep="prod"


Valoarea poate fi schimbati cu valoarea unei expresii calculate. De exemplu:

Exemplul 22. Dac dorim modificarea tuturor salariilor angajailor din departamentul"conta"n sensul creterii cu 15% vom avea:

UPDATE angajatiSET sal_brut=sal_brut*115/100 WHERE cod_dep="conta"

7/28/2019 BDR [1]

46/71

46


3.7. Instruciunea DELETE

Instruciunea DELETE este utilizat pentru tergerea uneia sau mai multor linii dintr-otabel.Sintaxa instruciunii DELETE este:

DELETE FROM nume_tabela [WHERE condiie]

Utiliznd aceast instruciune se vor terge toate liniile care ndeplinesc condiiaspecificat n clauza WHERE. Dac este omis clauza WHERE se vor terge toate liniiledin tabel.

Exemplul 23. Dac dorim tergerea tuturor angajailor din departamentul "conta" vomavea:

DELETE FROM angajatiWHERE cod_dep="conta"

7/28/2019 BDR [1]

47/71

47


7/28/2019 BDR [1]

48/71

48

CAP. 4. NORMALIZAREA RELAIILOR

1. INTRODUCEREn activitatea de modelare a bazelor de date problema care se pune este de a stabilimulimea de relaii care realizeaz o reprezentare fidel a schemei conceptuale, evitndincoerena, redundanai pierderile de informaii. Relaiile (tabelele) unei baze de datese pot stabili n mai multe moduri i de aceea este necesar s existe criterii de evaluare acalitii relaiilor, pentru ca acestea s asigure integritatea datelor i posibiliti deinterogare performante.

Teoria normalizrii se bazeaz pe observaia c anumite relaii au posibiliti mai bune deactualizare i interogare dect alte relaii echivalente (care conin aceleai informaii).Normalizarea relaiilor permite obinerea unei baze de date n care s nu se manifesteanomalii de actualizare sau stocare.

Pentru a nelege nevoia de normalizare s considerm relaia R care conine informaiilegate de furnizori (cod_funizor, nume_furnizor, localitate, cod_loc)i de produsele carele ofer (cod_produs, denumire, um, cantitate). Un furnizor poate oferi mai multeproduse, iar un produs poate fi oferit de mai muli furnizori.

R [cod_funizor, cod_produs, nume_furnizor, localitate, cod_loc, denumire, um, cantitate]

Cheia relaieiR este (cod_funizor, cod_produs).

cod_furnizor cod_produs nume_furnizor localit cod_loc denumire um cantF1 P13 Alfa SRL Brasov 5000 xyz kg 200F2 P17 Beta SRL Cluj 3000 abc mp 600F3 P13 Gama SRL Sinaia 2555 xyz kg 800F1 P17 Alfa SRL Brasov 5000 abc mp 400F2 P29 Beta SRL Cluj 3000 efg litru 600

Observm c datele despre fiecare furnizor (nume_furnizor, localitate, cod_loc) apar nfiecare tuplu n care se prezint un produs oferit de un anumit furnizor. Analog, datelegenerale despre fiecare produs ( denumire, um) apar n fiecare tuplu n care un furnizorofer respectivul produs. Aceste redundane conduc la creterea spaiului de memorare ila anomalii de actualizare a relaiei.

Anomalii de inserare nu se pot introduce datele generale despre un furnizor(nume_furnizor, localitate, cod_loc), dac nu exist cel puin un produs pe care acestas-l ofere. O alt anomalie de inserare nu putem introduce informaiile generale despreun produs ( denumire, um), dac nu exist un furnizor care s-l ofere. Aceste anomaliiapar datoritrestriciei de integritate care impun ca ntr-o relaie atributele cheie nu pots aib valoarea null.

7/28/2019 BDR [1]

49/71

49

Anomalii de tergere dac se terg toate informaiile legate de un furnizor, de exemplufirma nu mai lucreaz cu furnizorul F2, atunci tuplurile cu cheile i vor fi terse. Se pierd astfel informaiile generale legate de produsul P29,(denumire, um).

Anomalii de actualizare orice modificare a unei informaii generale legate de unfurnizor trebuie s se propage n toate tuplurile n care apare acel furnizor. Aceastamrete timpul de actualizare i crete riscul de incoeren al datelor. Acelai tip deanomalie apare i n cazul modificrii unei informaii generale despre un produs.

Teoria normalizrii are la baz analiza dependenelor dintre atributele care sunt laoriginea fenomenelor de redundani propune dou scheme de modelare a bazelor dedate relaionale fr anomalii sau pierderi de informaii [Popescu, 1996]:

schema descompunerii schema relaiei universale (relaia universal esterelaia care conine toate atributele care modeleaz sistemul real cercetat) se

descompune prin proiecii succesive n subrelaii; descompunerea se opretecnd continuarea ar conduce la pierderi de informaii; procesul de descompunereeste reversibil, ceea ce garanteaz c relaia de plecare (universal) poate firegsit prin utilizarea operatorului de jonciune i astfel nicio informaie nu afost pierdut;

schema sintezei pornete de la o mulime de atribute independente; pe bazaproprietilor de semantic i legturi ntre atribute se compun relaii care sevite eventualele anomalii.

Procesul de ameliorare a schemei conceptuale trebuie s satisfac urmtoarele cerine: s asigure conservarea datelor, adic n schema conceptual final trebuie s

regsim toate datele din cadrul schemei iniiale; s asigure conservarea dependenelordintre date, adic n schema conceptual

final fiecare dependen trebuie s aib determinantul i determinatul nschema aceleiai relaii;

s reprezinte o descompunere minimal a relaiilor iniiale, adic niciuna dinrelaiile care compun schema final nu trebuie coninut ntr-o alt relaie dinaceast schem.

Pentru ca informaiile dintr-o baz de date s fie prelucrate ct mai simplu este necesar carelaiile s verifice anumite condiii, altfel spus s aib un anumit grad de normalizare.Forma normal(Normal Form) a unei relaii presupune anumite condiii pe care trebuies le ndeplineasc valorile atributelor i dependenele funcionale definite pe aceearelaie.

E.F.Codd a definit primele trei forme normale (1NF, 2NF, 3NF). Ulterior a fost definitmai complet 3NF i a primit numele de forma normal Boyce-Codd (BCNF). Formelenormale superioare, definite de R. Fagin, se refer la dependenele multivaloare (4NF) idependenele de jonciune (5NF). De remarcat c BCNF, 4NF i 5NF corespund definiieiunice: orice determinant al unei dependene este o cheie. Diferena este dat de faptul c

7/28/2019 BDR [1]

50/71

50

n cazul BCNF este vorba de dependena funcional, n cazul 4NF de dependenamultivaloare, iar n cazul 5NF de dependena de jonciune [Fotache, 2005].

n continuare ne limitm prezentarea la primele trei forme normale definite de Codd,considerate n multe lucrri de specialitate a fi suficiente pentru proiectarea corect a

bazelor de date.

2. DEPENDENE FUNCIONALEDependena funcional reprezint dependena dintre date prin care se poate identifica unatribut sau grup de atribute prin intermediul altui atribut sau grup de atribute.

Definiia 4.1. Dependena funcional. Dat o relaieR, spunem c un atribut sau un grupde atribute Ydepinde funcional de un atribut sau grup de atributeX, dac pentru fiecarevaloare a luiXse asociaz o singur valoare a lui Yn orice tuplu dinR.

Formal: ( ) ( ) ( ) ( )2Y1Y2X1X21 tttt:avemRt,t == sau echivalent: pentru orice tupluri , dinR, x = x y = y.

Vom spune cX determin pe Y sau Y depinde funcional de Xi vom notaX Y.

Atributul (grupul de atribute)Xse numete determinant, iar atributul (grupul de atribute)Yse numete determinat, adic:

determinant determinat.

n cazul exemplului prezentat n introducere identificm urmtoarele dependenefuncionale:

(1) (cod_furnizor, cod_produs) nume_furnizor(2) (cod_furnizor, cod_produs) localitate(3) (cod_furnizor, cod_produs) cod_loc(4) (cod_furnizor, cod_produs) denumire(5) (cod_furnizor, cod_produs) um(6) (cod_furnizor, cod_produs) cantitate(7) cod_furnizor nume_furnizor(8) cod_furnizor localitate

(9) cod_furnizor cod_loc(10) cod_produs denumire(11) cod_produs um(12) cod_loc localitate

7/28/2019 BDR [1]

51/71

51

Tipuri de dependene funcionale

Definiia 4.2. Dependena funcional trivial.

O dependen funcionalX Yeste trivial dac XY .

Definiia 4.3. Dependena funcional parial (dfp).O dependen funcionalX Yesteparial dac Y1X.i.aX1X Vom numi dependena funcionalX1 Y, dependen argument dfp.Exemplu.

Dependena funcional: (cod_furnizor, cod_produs) nume_furnizoresteparialdeoarece se manifesti dependena argument dfp:cod_furnizor nume_furnizor

Definiia 4.4. Dependena funcional complet (total) (dfc).

O dependen funcional X Y este complet (total) dac nu existY1X.i.aX1X

Definiia 4.5. Dependena funcional tranzitiv (dft).O dependen funcional X Y este tranzitiv dac se manifest concomitentdependenele funcionale:Z XiZ Y.

Exemplu.

Dependena funcional: cod_loc localitateeste tranzitivdeoarece se manifest concomitent i dependenele:

cod_furnizor localitatecod_furnizor cod_loc

Observaie.Cheia unei relaii poate fi definit cu ajutorul dependenelor funcionale astfel:Xeste o cheie pentru relaiaR [X, Y]dacYdepinde funcional deX adic: X Y.Cheia X este minimaldac dependena funcionalX Y este complet.

3. PRIMA FORM NORMAL (1NF)Definiia 4.6. O relaie este n prima form normal notat (1NF), dac fiecare dinatributele sale are un domeniu atomic (monovaloare). O relaie n 1NFnu conine grupurirepetitive.

Observaie.

Noiunea de grup repetitiv (mulime de valori) nu exist n modelul relaional.

O relaie nenormalizat poate fi transformat ntr-o relaie 1NF, nlocuind atributulcompus prin atributele simple corespunztoare (spargerea relaiei) sau duplicnd tuplelede attea ori cte valori exist pentru un atribut dat (spargerea grupului repetitiv).Exemple de relaii nenormalizate:

7/28/2019 BDR [1]

52/71

52

a) relaie n care un atribut este o relaieZBOR [NR_ZBOR, AVION] cu AVION [tip_nava, capacitate]

ZBOR NR_ZBOR AVION

102 (B707, 150)107 (B737, 180)108 (AIRB320, 250)109 (B707, 150)110 (B747, 300)

Observm c atributul compus AVIONdin relaia ZBOR este de fapt o relaie cu douatribute tip_nava i capacitate (numr de locuri).Rezultatul trecerii n 1NFprin spargerea relaieiAVIONeste:

ZBOR 1 NR_ZBOR TIP NAVA CAPCITATE

102 B707 150107 B737 180108 AIRB320 250109 B707 150110 B747 300

b) relaie n care un atribut este un ansamblu de valoriCATALOG [Nume, Note]

CATALOG NUME NOTE

Einstein 8, 6

Freud 7, 9, 5Rezultatul trecerii n 1NF, n situaia c numrul maxim de note este cunoscut, prinspargerea relaieiNOTEeste:

CATALOG 1 NUME NOTA 1 NOTA 2 NOTA 3

Einstein 8 6 nullFreud 7 9 5

n cazul spargerii grupului repetitiv NOTEse obine relaia:

CATALOG 2 NUME NOTA

Einstein 8Einstein 6Freud 7Freud 9Freud 5

Teorema 1 (de eliminare a grupurilor repetitive)

7/28/2019 BDR [1]

53/71

53

DacR [A1, A2, ..., An]este o relaie n careAm+1, Am+2, ..., An formeaz un grup repetitiv,i {A1, A2, ..., Ap}cup < m este o cheie primar, atunci relaiaR se poate descompune ndou relaii fr grupuri repetitive i pierdere de informaii, astfel:

R1 [A1, A2, ..., Am] = ( )Rm21 A,...,A,A

R2 [A1, A2, ..., Ap, Am+1,..., An] = ( )Rn1mp21 A,...,A,A,...,A,A +

Algoritmul 1NF - de aducere a unei relaii nenormalizate n 1NF

(eliminarea atributelor compuse i repetitive)

Pasul 1. Se trec n relaie n locul atributelor compuse componentele acestora ca atributesimple.

Pasul 2. Se trec grupurile de atribute repetitive, fiecare ntr-o nou relaie.Pasul 3. Se introduce n schema fiecrei noi relaii create la Pasul 2 cheia primar a

relaiei din care a fost extras grupul repetitiv.Pasul 4. Se stabilete cheia primar a fiecrei noi relaii create la Pasul2. Aceasta va ficompus din cheia introdus la Pasul 3 (cheia primar iniial) precum i dinunul sau mai multe atribute proprii relaiei.

Exemplu.

Pentru gestionarea crilor dintr-o bibliotec se consider relaia:

CARTE [ cota, nume_autori, titlul, editura, an_apariie, ISBN, cuvinte_cheie]

Cota Nume autori Titlul Editura An

apar.

ISBN Cuvinte-cheie

C104 Ionescu MPopescu FGeorgescu L

Baze dedate

Economica 2009 978-973-8204-41-7

proiectarea bazelorde date,SQL Server 2008,Oracle

C289 Marinescu A Sistemeinformatice

Polirom 2007 978-973-1978-89-5

analiza SI,proiectarea SI,implementarea SI,auditul SI

n relaia CARTE (nenormalizat) exist dou grupuri de atribute repetitive: nume_autorii cuvinte_cheie care creaz mari greuti n stocarea informaiilor i realizarea

interogrilor. Alegem drept cheie primar atributul COTA. Aplicarea Algoritmului 1NFconduce la urmtoarele relaii:

CARTE 1 [ cota, titlul, editura, an_apariie, ISBN]AUTORI [ cota, nume autori]CUVINTE CHEIE [ cota, cuvinte cheie]

7/28/2019 BDR [1]

54/71

54

CARTE 1Cota Titlul Editura An

aparitie

ISBN

C104 Baze de date Economica 2009 978-973-8204-41-7C289 Sisteme informatice Polirom 2007 978-973-1978-89-5

AUTORICota Nume autori

C104 Ionescu MC104 Popescu FC104 Georgescu LC289 Marinescu A

CUVINTE CHEIECota Cuvinte cheie

C104 proiectarea bazelor de dateC104 SQL Server 2008

C104 OracleC289 analiza SIC289 proiectarea SIC289 implementarea SIC289 auditul SI

4. A DOUA FORM NORMAL (2NF)Definiia 4.7. O relaie este n a doua form normalnotat(2NF), dac relaia este n(1NF) i oricare dintre atributele care nu aparin cheii primare este complet dependentfuncional de cheie.

Observaie. O relaie n 2NFnu conine dependene funcionale pariale ntre atributelecheie i celelate atribute.

Exemplu. Redundane care apar n cazul unei relaii 1NF, care nu este 2NF.

FieR [A, B, C, D] n care cheia primar este (A, B)i se manifest dependenele:

(A, B) C (A, B) D B C

A B C D

a1 b1 c1 d1a2 b1 c1 d2a3 b2 c3 d2a4 b2 c3 d3

7/28/2019 BDR [1]

55/71

55

Teorema 2 ( de descompunere fr pierdere de informaie)

Fie R [A1, A2, ..., An] o relaie n 1NF i K = {A1, A2, ..., Ap} cu p < n este o cheieprimar. Presupunem c exist { } == K,A,...,A,AA n21 , adic este unatribut noncheie i cu K ( este complet dependent funcional de osubmulime strict de atribute din cheie). Atunci dependena se poate elimina

descompunnd relaiaRn urmtoarele dou relaii:

[ ] ( )RR1 =

[ ] ( )RAR A2 =

Observaie. Conform teoremei de mai sus, relaiaR [A, B, C, D] din exemplul precedent,n care se manifest dependena parial (A, B) C se descompune fr pierdere deinformaie n:

R1[ B, C] i R2[A, B, D]

Algoritmul 2NF - de aducere a unei relaii 1NF n 2NF

(eliminarea dependenelor funcionale pariale)

Pasul 1. Pentru fiecare dependen funcional argument dfp se creaz o nou relaie, cuschema constituit din determinantul i determinatul acestei dependene. Dacexist mai multe dependene funcionale argument dfp cu acelai determinant seva crea o singur relaie format din determinant luat o singur dat ideterminaii dependenelor considerate.

Pasul 2. Din relaia iniial se elimin atributul / atributele care formeaz determinatuldependenelor funcionale argument dfp.Pasul 3. Se stabilete cheia primar a fiecrei noi relaii create la Pasul1. Aceasta va fi

format din determinantul dependenei funcionale argumentdfp.

Aplicaie.Pentru evidena autoturismelor nchiriate de o firm clienilor se consider relaia:

AUTO [ nr_client, nume_client, adresa, nr_auto, marca, data ]

Nr client Nume client Adresa Nr auto Marca Data

C234 Smith A Castelului 12, Brasov BV 21 XXI Logan 1.4 22.05.2009C145 Lungu M Libertatii 14, Predeal BV 19 XIX Ford Focus 17.04.2009C679 Tudor A Armoniei 23, Iasi CJ 12 XII Audi A6 23.05.2009C089 Stan D Sadoveanu 45, Cluj CT 07 VII Opel Astra 07.04.2009C445 Bondescu I Caragiale 66, Arad BV 61 LXI VW Golf 26.04.2009

7/28/2019 BDR [1]

56/71

56

Cheia relaiei este (nr_client, nr_auto), iar dependeele funcionale care se manifest sunt:

(1) (nr_client, nr_auto) nume_client(2) (nr_client, nr_auto) adresa(3) (nr_client, nr_auto) marca

(4) (nr_client, nr_auto) data(5) nr_client nume_client(6) nr_client adresa(7) nr_auto marca

Observaie.

- relaia AUTO este n forma normal 1;- dependena funcional(1) este parial deoarece se manifesti d.f.argument dfp (5);- dependena funcional (2) este parial deoarece se manifesti d.f.argument dfp (6);- dependena funcional (3) este parial deoarece se manifesti d.f.argument dfp (7).AplicareaAlgoritmului 2NF - de aducere a unei relaii 1NF n 2NFbazat pe Teorema 2,conduce la spargerea relaiei AUTO, n trei relaii n 2NF:

EVIDENTA [ nr_client, nr_auto, data ]CLIENT [ nr_client, nume_client, adresa ]AUTO [ nr_auto, marca ]

EVIDENANr client Nr auto Data

C234 BV 21 XXI 22.05.2009C145 BV 19 XIX 17.04.2009C679 CJ 12 XII 23.05.2009

C089 CT 07 VII 07.04.2009C445 BV 61 LXI 26.04.2009

CLIENTNr client Nume client Adresa

C234 Smith A Castelului 12, BrasovC145 Lungu M Libertatii 14, PredealC679 Tudor A Armoniei 23, IasiC089 Stan D Sadoveanu 45, ClujC445 Bondescu I Caragiale 66, Arad

AUTONr auto MarcaBV 21 XXI Logan 1.4BV 19 XIX Ford FocusCJ 12 XII Audi A6CT 07 VII Opel AstraBV 61 LXI VW Golf

7/28/2019 BDR [1]

57/71

57

5. A TREIA FORM NORMAL (3NF)Definiia 4.8. O relaie este n a treia form normalnotat (3NF), dac relaia este n(2NF) i oricare dintre atributele care nu aparin cheii primare nu depinde tranzitiv decheie.

Observaie. O alt exprimare: orice atribut ce nu aparine cheii primare depinde direct decheie.

FieR o relaie, Kcheia primari presupunem cesteun atribut ce depinde tranzitiv decheie. Aceasta nseamn c exist un atribut , astfel nct exist dependenelefuncionale:

K i Deoarece relaia R este n 2NFrezult ceste complet dependent funcional de cheiarelaiei i deci =K , adiceste un atribut noncheie.

Exemplu. Redundane care apar n cazul unei relaii 2NF, care nu este 3NF.FieR [A, B, C] n care cheia primar esteA i se manifest dependenele:

AB AC B C

A B Ca1 b1 c1a2 b1 c1a3 b2 c2a4 b2 c2

Teorema 3 - Casey Delobel (de descompunere fr pierdere de informaie)Fie R [A1, A2, ..., An] o relaie n 2NF i Keste o cheie primar. Dac exist atributul

{ } == K,A,...,A,AA n21 , i cu K (depinde tranzitiv de cheie),atunci dependena se poate elimina descompunnd relaiaR n urmtoarele dourelaii:

[ ] ( )RR1 =

[ ] ( )RAR A2 =

Observaie. Conform teoremei de mai sus, relaiaR [A, B, C] din exemplul precedent, ncare se manifest dependena tranzitiv B C se descompune fr pierdere deinformaie n:

R1[B, C] i R2[ A, B]

7/28/2019 BDR [1]

58/71

58

Algoritmul 3NF - de aducere a unei relaii 2NF n 3NF

(eliminarea dependenelor funcionale tranzitive)

Pasul 1. Pentru fiecare dependen funcional tranzitivdin cadrul relaiei considerate,se creaz o nou relaie, format din atributele implicate n aceast dependen.

Pasul 2. Se stabilete cheia primar a fiecrei noi relaii create la Pasul1.Pasul 3. Se introduc n relaia iniial n locul atributelor transferate la Pasul 1, cheileprimare detrminate la Pasul 2.

Aplicaie.

Pentru evidena rezultatelor examenului de licen, se consider relaia:

EXAMEN [nr_matricol, nume_student, program_studiu, nota, prof_coordonator, catedra]

Nr matricol Nume student Program Nota Prof coord Catedra

2345 Ionescu M ECTS 9.45 Zamfir R MKTS5678 Popescu V MK 9.30 Teodorescu N MKTS7890 Georgescu D CIG 9.70 Oancea C FBC4567 Constantinescu FB 9.60 Cristea D FBC3456 Marinescu H CIG 9.20 Andreescu M MNIE

Cheia relaiei este nr_matricol, iar dependeele funcionale care se manifest sunt:

(1) nr_matricol nume_student(2) nr_matricol program_studii(3) nr_matricol nota(4) nr_matricol prof_coordonator(5) nr_matricol catedra

(6) prof_coordonator catedra

Observaie.- relaia EXAMEN este n 2NF, deoarece toate valorile atributelor sunt atomice, nu avematribute repetitive (1NF)i nu exist dependene funcionale pariale (2NF).- dependenele (4) i (6) arat ca atributul catedra depinde tranzitiv de cheia primar arelaiei.

AplicareaAlgoritmului 3NF - de aducere a unei relaii 2NF n 3NFbazat pe Teorema 3,conduce la spargerea relaiei EXAMEN, n dou relaii n 3NF:

REZULTAT [nr_matricol, nume_student, program_studiu, nota, prof_coordonator]PROFESOR [prof_coordonator, catedra]

7/28/2019 BDR [1]

59/71

59

REZULTATNr matricol Nume student Program Nota Prof coord

2345 Ionescu M ECTS 9.45 Zamfir R5678 Popescu V MK 9.30 Teodorescu N7890 Georgescu D CIG 9.70 Oancea C4567 Constantinescu FB 9.60 Cristea D3456 Marinescu H CIG 9.20 Andreescu M

PROFESORProf coord Catedra

Zamfir R MKTSTeodorescu N MKTSOancea C FBCCristea D FBCAndreescu M MNIE

Rezumat.

1NF toate atributele sunt atomice i nu exist atribute repetitive2NF 1NF + orice atribut noncheie este complet dependent funcional de cheie

(nu exist dependene funcionale pariale)3NF 2NF + atributele care nu aparin cheii nu depind tranzitiv de cheie

(nu exist dependene funcionale tranzitive /nu exist dependene funcionale ntre atributele noncheie)

7/28/2019 BDR [1]

60/71

60

CAP. 5. APLICAII

5.1. FIRMA DE COMERCIALIZARE PRODUSE ELECTRONICE

Relaii cu furnizorii i clienii unei firme de comercializare produse electronice sunt

gestionate de urmroarele ase relaii (tabele):FURNIZORI [cod_furnizor, nume_furnizor, CUI, adresa, localitate, cont, tel, email](FURNIZ)

FACTURI_PRIMITE [nr_factura, cod_furnizor, data_factura, valoare, tva_deductibil](FACTP)

CLIENTI [cod_client, tip_client, nume_client, adresa, localitate](CLI)

PRODUSE [ cod_produs, denumire, um, grupa](PROD)

FACTURI_EMISE [nr_factura, data_factura, cod_client, valoare, tva_colectat](FACTE)

DETALII_FACTURA [nr_factura, cod_produs, cantitate, pret_unitar](FACTD)

Facem urmtoarele precizri:- pe o factur emis pot exista mai multe produse; informaiile despre aceste produse le

gsim n relaia (tabela) DETALII_FACTURA;- sub fiecare schem a relaiei este indicat numele prescurtat al relaiei cu care se va

opera n procedurile de interogare.

Cheile primare pentru relaiile (tabelele) definite sunt:- pentru FURNIZORI cheia primar este (cod_furnizor), CIF poate fi cheia secundar

sau alternant;- pentru FACTURI_PRIMITE cheia primar este compus din dou atribute

(nr_factura, cod_furnizor), deoarece furnizorii numeroteaz facturile independentunul de altul i este posibil s primim dou facturi cu acelai numr factur de lafurnizori diferii;

- pentru CLIENTI cheia primar este (cod_client);- pentru PRODUSE cheia primar este (cod_produs);- pentru FACTURI_EMISE cheia primar este (nr_factura);- pentru DETALII_FACTURA cheia primar este (nr_factura, cod_produs).

Pentru interogrile formulate se vor prezenta rezolvrile utiliznd operaiile algebreirelaionale i limbajul SQL dinMicrosoft Access. Pentru o parte din interogri se va da irezolvarea cu ajutorul construciei arborelui de interogare.

7/28/2019 BDR [1]

61/71

61

P1. Cum se numesc furnizorii din Braov ?

Lista: |cod_furnizor | nume_furnizor | CIF |

Algebr relaional

R1 = SELECT(FURNIZ; localitate = Brasov)R2 = PROJECT(R1; cod_furnizor, nume_furnizor, CIF)

AccessSELECT cod_furnizor, nume_furnizor, CIFFROM furnizWHERE localitate="Brasov";

Arborele de interogare

P2. Lista cu numerele i valorile facturilor primite, ce au fost ntocmite dup 1.03.2009i au o valoare mai mare de 500 lei?

Algebr relaionalR1 = SELECT(FACTP; (data_factura > 1.03.2009) and (valoare>500))R2 = PROJECT(R1; nr_factura, valoare)

Access

SELECT nr_factura, valoareFROM factpWHERE data_factura>#3/1/2009# AND valoare>500;

cod_furnizor,nume_furnizor, CIF

FURNIZ

localitate=Brasov

R1

R2

7/28/2019 BDR [1]

62/71

62

P3. Care sunt localitile n care firma i are partenerii de afaceri?Ci clieni exist n fiecare localitate?

Algebr relaionalR1 = PROJECT(FURNIZ; localitate)

R2 = PROJECT(CLI; localitate)R3 = UNION(R1, R2)Q1 = COUNT(CLI; localitate)Q2 = PROJECT(Q1; localitate, count)

AccessSELECT distinct localitateFROM furnizUNION (SELECT localitate FROM cli);

SELECT count(*), localitate

FROM cliGROUP BY localitate;

Dac dorim s vedem ci parteneri comerciali avem in fiecare localitate vom avea:

SELECT Count(*) AS nr, total.localitateFROM [SELECT cod_furnizor AS cod_partener, localitate FROM furnizUNION (SELECT cod_client AS cod_partener, localitate FROM cli)]. AS totalGROUP BY total.localitate;

P4. Lista facturilor primite n acest an.

Lista: |nr_factura | data_factura | cod_furnizor | nume_furnizor |

Algebr relaionalR1 = SELECT(FACTP; data_factura > 1.01.2009)R2 =NATURAL JOIN(R1, FURNIZ; cod_furnizor)R3 = PROJECT(R2; nr_factura, data_factura, cod_furnizor, nume_furnizor)

AccessSELECT nr_factura, data_factura, factp.cod_furnizor, nume_furnizorFROM furniz, factpWHERE factp.cod_furnizor=furniz.cod_furnizor AND data_factura>#1/1/2009#;Sau:SELECT nr_factura, data_factura, factp.cod_furnizor, nume_furnizorFROM furniz, factpWHERE factp.cod_furnizor=furniz.cod_furnizor AND

YEAR(data_factura)=YEAR(DATE());

7/28/2019 BDR [1]

63/71

63

P5. De la care furnizori s-au primit facturi ntocmite n data de 4.03.2009?

Lista: |nr_factura | cod_furnizor | nume_furnizor |

Algebr relaional

R1 = SELECT(FACTP; data_factura = 4.03.2009)R2 =NATURAL JOIN(R1, FURNIZ; cod_furnizor)R3 = PROJECT(R2; nr_factura, cod_furnizor, nume_furnizor)

Sau:Q1 =NATURAL JOIN(FACTP, FURNIZ; cod_furnizor)Q2 = SELECT(Q1; data_factura = 4.03.2009)Q3 = PROJECT(Q2; nr_factura, cod_furnizor, nume_furnizor)

AccessSELECT nr_factura, factp.cod_furnizor, nume_furnizor

FROM factp, furnizWHERE furniz.cod_furnizor=factp.cod_furnizor AND data_factura=#3/4/2009#;

Sau:

SELECT nr_factura, factp.cod_furnizor, nume_furnizorFROM factpINNER JOIN furniz ON furniz.cod_furnizor=factp.cod_furnizorWHERE data_factura=#3/4/2009#;

P6. n ce localit

i se gsesc clien

ii care au cump

rat produsul XYZ?

Algebr relaionalR1 = SELECT(PROD; denumire = XYZ)R2 =NATURAL JOIN(R1, FACTD; cod_produs)R3 =NATURAL JOIN(R2, FACTE; nr_factura)R4 =NATURAL JOIN(R3, CLI; cod_client)R5 = PROJECT(R4; localitate)

AccessSELECT DISTINCT localitateFROM cli, prod, facte, factdWHERE facte.cod_client=cli.cod_client

AND facte.nr_factura=factd.nr_facturaAND factd.cod_produs=prod.cod_produsAND prod.denumire="xyz";

7/28/2019 BDR [1]

64/71

64


P7. n ce localiti s-a vndut produsul XYZ n perioada 15.04.2009 30.04.2009 ?

Algebr relaionalR1 = SELECT(PROD; denumire = XYZ)R2 = NATURAL JOIN(R1, FACTD; cod_produs)R3 = SELECT(FACTE; (data_factura>14.04.2009) and (data_factura

7/28/2019 BDR [1]

65/71

65

AND factd.cod_produs=prod.cod_produsAND prod.denumire="xyz"AND data_factura BETWEEN #4/15/2009# AND #4/30/2009#;

Sau:

SELECT DISTINCT localitateFROM cli, prod, facte, factdWHERE facte.cod_client=cli.cod_client

AND facte.nr_factura=factd.nr_facturaAND factd.cod_produs=prod.cod_produsAND prod.denumire="xyz"AND (data_factura>=#4/15/2009# AND data_factura1.01.2009)R3 = PROJECT(R2; nr_factura, cod_produs)R4 = PROJECT(PROD; cod_produs)R5 = DIVISION(R3, R4)

AccessSELECT nr_factura, data_facturaFROM facteWHERE data_factura>=#01-01-2009# AND nr_factura IN

(SELECT factd.nr_factura FROM factd, facte GROUP BY factd.nr_facturaHAVING count(*)=(SELECT COUNT(*) FROM prod));

7/28/2019 BDR [1]

66/71

66

P10. Care sunt numerele facturilor emise, n care s-a consemnat vnzarea simultan aproduselor XYZ i ABC ?

Algebr relaionalR1 = SELECT(PROD; denumire = XYZ)

R2 = NATURAL JOIN(R1, FACTD; cod_produs)R3 = PROJECT(R2; nr_factura)

R4 = SELECT(PROD; denumire = ABC)R5 = NATURAL JOIN(R4, FACTD; cod_produs)

R6 = PROJECT(R5; nr_factura)

R7 = INTERSECT (R3, R6)

Varianta 2R1 = PROJECT(FACTD; nr_factura, cod_produs)

R2 = SELECT(PROD; (denumire = XYZ) or (denumire = ABC))R3 = PROJECT(R2; cod_produs)R4 = DIVISION(R1, R3)

AccessSELECT DISTINCT facte.nr_facturaFROM cli, prod, facte, factdWHERE facte.cod_client=cli.cod_client

AND facte.nr_factura=factd.nr_facturaAND factd.cod_produs=prod.cod_produsAND (prod.denumire="xyz" OR prod.denumire="abc");

Sau:

SELECT DISTINCT facte.nr_facturaFROM cli, prod, facte, factdWHERE facte.cod_client=cli.cod_client

AND facte.nr_factura=factd.nr_facturaAND factd.cod_produs=prod.cod_produsAND prod.denumire IN ("xyz","abc");

Sau:SELECT DISTINCT facte.nr_facturaFROM cli, facte, factd,

[SELECT * FROM prod WHERE prod.denumire IN ("xyz","abc")]. AS tmpWHERE facte.cod_client=cli.cod_client

AND facte.nr_factura=factd.nr_facturaAND factd.cod_produs=prod.cod_produs;

7/28/2019 BDR [1]

67/71

67

P11. Care este valoarea total a facturilor emise n luna mai 2009 pentru fiecareclient?

Lista: |cod_client | nume_client | valoare totala |

Algebr relaionalR1 = SELECT(FACTE; (data_factura 01.05.2009) and (data_factura 31.05.2009))R2 = SUM(R1, valoare; cod_client)R3 = NATURAL JOIN(R2, CLI; cod_client)R4 = PROJECT(R3; cod_client, nume_client, sum)

AccessSELECT tmp.total, tmp.cod_client, nume_clientFROM cli,

[SELECT sum(valoare) AS total, facte.cod_client FROM facteWHERE year(data_factura)=2009 AND month(data_factura)=5

GROUP BY facte.cod_client]. AS tmpWHERE tmp.cod_client=cli.cod_client;


FACTE

01.05.09data factura31.05.09

R1

valoare/cod clientCLI

R2

cod client

R3

cod client,nume client, sum

R4

7/28/2019 BDR [1]

68/71

7/28/2019 BDR [1]

69/71

69

AccessSELECT tmp.total, tmp.cod_client, nume_clientFROM cli,

[SELECT MAX(valoare) AS total, cod_client FROM facte WHERE cod_clientIN (SELECT cod_client FROM cli WHERE tip_client="j")

AND YEAR(data_factura)=2009AND MONTH(data_factura)=5 GROUP BY cod_client]. AS tmpWHERE tmp.cod_client=cli.cod_client;

7/28/2019 BDR [1]

70/71

70

BIBLIOGRAFIE

1. AIRINEI D.,Depozite de date, Editura Polirom, Iai, 2002.2. BSC O.,Baze de date, Editura ALL, Bucureti, 1997.3. CONNOLLY Th., .a.,Baze de date. Proiectare, Implementare, Gestionare,

Editura Teora, Bucureti, 2001.4. CONNOLLY Th., .a.,Database Systems, Addison-Wesley, 2002.5. DATE C.J.,Baze de date, Editura Plus, Bucureti, 2005.6. DOLLINGER R.,Baze de datei gestiunea tranzaciilor, Editura Albastr,

Cluj Napoca, 1998.7. DOLLINGER R., Utilizarea sistemului SQL Server, Editura Albastr,

Cluj Napoca, 2001.8. EAGLESTONE B., .a., Web Database Systems, Mc Graw Hill Book Company,

Londra, 2001.9. FLORESCU V., .a.,Baze de date, Editura Economic, Bucureti, 1999.10.FOTACHE M.,Baze de date relaionale, Editura Junimea, Iai, 1997.11.

FOTACHE M., SQL, Editura Polirom, Iai, 2001.12.FOTACHE M., Proiectarea bazelor de date, Editura Polirom, 2005.

13.Grupul BDASEIG,Baze de date. Fundamente teoreticei practice,Editura Infomega, Bucureti, 2002.

14.HERNANDEZ M., Proiectarea bazelor de date, Ed. Teora, 2003.15.HORGA M., .a.,Limbajul SQL

Date post:	03-Apr-2018
Category:	Documents
Upload:	aurica-morar
View:	236 times
Download:	0 times

BDR [1]

Documents