Baze de Date- Indrumar

1

UNIVERSITATEA TRANSILVANIA DIN BRAŞOV

FACULTATEA DE ŞTIINŢE ECONOMICE

Prof.dr. DORIN LIXĂNDROIU Lector dr. RADU LIXĂNDROIU

BAZE DE DATE

RELAŢIONALE

� introducere în baze de date

� algebra relaţională (AR)

� SQL Access

� interogări în AR şi Access 2007

� normalizarea relaţiilor

Note de curs

Anul universitar 2008-2009

2

CAP.1. BAZE DE DATE

1.1. ORGANIZAREA DATELOR (OD)

(Ce presupune organizarea datelor?)

● definirea, structurarea, ordonarea şi gruparea datelor în colecţii de date omogene; ● stabilirea relaţiilor între date, între elementele unei colecţii, între colecţii de date; ● stocarea datelor pe suport informational. 1.2. OBIECTIVELE ORGANIZĂRII DATELOR

(De ce este necesară organizarea datelor?)

● minimizarea timpului de acces la date; ● economie de memorie internă şi externă; ● asigurarea unicităţii datelor; ● sistemul OD trebuie să reflecte cât mai fidel toate legăturile dintre obiectele, fenomenele, procesele economice pe care aceste date le reprezintă; ● asigurarea flexibilităţii datelor. 1.3. ETAPE ALE EVOLUTIEI TEHNICILOR DE ORGANIZARE ŞI

PRELUCRARE A DATELOR

● Prima etapă – se adaptează tipurile de organizare a datelor existente în sistemele de prelucrare manuală la condiţiile tehnice impuse de calculator. - apare fişierul (în general cu organizare secvenţială) - utilizarea benzilor magnetice - prelucrarea pe loturi (batch processing) ● A doua etapă – este marcată de separarea dintre structura logică de date şi structura fizică. Rezultă independenţa fizică a datelor. - se utilizează fişiere secvenţial-indexate şi fişiere cu acces direct - suport extern de memorare discul magnetic

- se asigură independenţa aplicaţiilor de modificările echipamentelor hardware (banda, disc, etc.)

- apar primele facilităţi simple de protecţie a datelor

Caracteristică comună a primelor două etape: fiecare aplicaţie lucrează cu propriile fişiere fără a avea nici o legătură cu fişierele utilizate de alte aplicaţii. Inconveniente:

- redundanţa datelor = > probleme în operaţiile de actualizare - absenţa unor legături logice între datele din grupuri diferite de fişiere = >

număr mare de fişiere, timp mare de prelucrare - flexibilitate redusă a sistemului la apariţia unei noi aplicaţii.

● A treia etapă – este definită de apariţia fişierelor integrate.

- se reduce redundanţa datelor, aceleaşi date fizice pot fi utilizate în comun de către mai multe aplicaţii - rezultă o structură logică unitară.

3

Notă. Structura integrată constituie originea noţiunii de model conceptual (modelul ce contine descrierile tuturor datelor şi a legăturilor dintre ele). ● A patra etapă – este etapa bazelor de date.

1.4. SISTEMUL BAZAT PE FISIERE INDEPENDENTE (file based)

O colectie de programe de aplicaţie care efectuează servicii pentru utilizatorii finali. Fiecare program defineşte şi gestionează propriile date. Caracteristici:

● datele sunt descrise independent în toate fişierele în care apar ● fiecare fişier de date este descris în toate programele care îl accesează ● nu există control al accesului şi manipulării datelor, în afara celui impus prin programele de aplicaţie. Dezavantajele tratării bazate pe fisiere:

● redundanţa şi inconsistenţa datelor ● dificultatea accesului ● izolarea datelor ● complexitatea deosebită a actualizărilor ● probleme de securitate ● probleme legate de integritatea datelor ● costul ridicat ● dificultatea de a obţine răspunsuri rapide la probleme ad-hoc simple ● inflexibilitatea faţă de schimbările ulterioare din sistemul informational. 1.5. BAZE DE DATE

DATA - înregistrarea unei observaţii, obiect, fenomen, imagine, sunet sau text, într-o formă convenabilă unei prelucrări, interpretări sau transmiteri prin mijloacele informaticii. INFORMAŢIA - semnificaţia ce poate fi ataşată sau poate fi dedusă dintr-un ansamblu de date pe baza asociaţiilor dintre acestea. BAZA DE DATE – o colecţie de date operaţionale înregistrate pe suport adresabil, aflate

în interdependenţă logică, împreună cu descrierea datelor şi a relaţiilor dintre ele şi care

sunt prelucrate în aplicaţiile informatice ale unei organizaţii. Baza de date permite

operaţii de introducere, ştergere, actualizare şi interogare a datelor.

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

4

- cu o redundanţă minimă şi controlată, - independente de programul de aplicaţie, - direct accesibile după mai multe criterii, - simultan accesibile de către mai mulţi 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 - dicţionarul datelor (DD) - mijloacele hardware - personalul (ABD, analişti, programatori, utilizatori finali). Cerinţele minimale care se impun unei baze de date:

- furnizarea în timp util a informaţiilor solicitate - costuri minime în prelucrarea şi întreţinerea informaţiei

- capacitatea de a satisface, cu aceleaşi date necesităţile informaţionale ale unui număr mare de utilizatori - flexibilitate - posibilitatea de adaptare la cerinţe noi, de a da răspunsuri la interogări neprevăzute iniţial

- asigurarea unei redundanţe minime a datelor - sincronizare – exploatarea simultană a datelor de către mai mulţi utilizatori

- confidenţialitate – asigurarea securităţii datelor prin mecanisme de protecţie împotriva accesului neautorizat

- integritate – facilităţi de validare şi recuperare a datelor deteriorate accidental - compatibilitate şi expandabilitate – posibilitatea de valorificare a eforturilor anterioare şi anticiparea nevoilor viitoare - permisivitate – prin ierarhizarea datelor după criteriul frecvenţei acceselor, sau reorganizări care să crească performanţele BD.

Database Administration (ABD) - Administratorul bazei de date este responsabil cu realizarea fizică a BD, care include proiectarea, implementarea, exploatarea şi întreţinerea acesteia, securitatea, acordarea drepturilor de acces şi controlul integrităţii. 1.6. SISTEMUL DE GESTIUNE AL BAZELOR DE DATE ( SGBD) Database Management System (DBMS) SGBD - un ansamblu de programe (produs software) care permite definirea,actualizarea

şi consultarea datelor din baza dedate.

Funcţiile unui SGBD: - definirea datelor (DDL – Data Definition Language) permite definirea conceptuală a

datelor, fără referire la modul de memorare - manipularea datelor (DML – Data Manipulation Language şi / sau interfaţa cu limbaje

de programare) permite specificarea operaţiilor de introducere, actualizare, ştergere şi interogare a datelor

5

- controlul integrităţii datelor - accesul concurent (folosirea simultană a datelor de mai mulţi utilizatori) - confidenţialitatea informaţiilor din BD - securitatea în funcţionare (DCL – Data Control Language)

Arhitectura funcţională a unui SGBD

Interfaţă de generaţia a 4-a

Pascal

Visual Fox

Oracle

...

Definire de date

Manipulare

Confidenţialitate

Securitate

Interfeţe

S

G

B

D

Sistem de exploatare

Baze de date Baze de date

Utilizator

6

Obiectivele unui SGBD:

- asigurarea independenţei datelor - asigurarea unor facilităţi sporite de utilizare a datelor - asigurarea unei redundanţe minime şi controlate a datelor din BD (reducerea

redundanţelor se face prin identificarea informaţiilor comune) - securitatea şi confidenţialitatea datelor - partajabilitatea datelor - integritatea datelor Există mai multe nivele de reprezentare (abstractizare şi percepţie) a datelor în baza de date: � Nivelul conceptual – este dat de viziunea adminstratorului bazei de date asupra

datelor. 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 aplicaţiile care vor fi

dezvoltate (independenţă logică); - rezultatul la acest nivel este schema conceptuală.

� Nivelul logic – este dat de viziunea programatorului de aplicaţii asupra datelor. Principalele aspecte la acest nivel: - programatorul de aplicaţii realizează programele pentru descrierea şi manipularea

datelor; - programele implementează structura externă (logică) a datelor; - structura externă este dedusă din schema conceptuală;

- viziunea programatorului de aplicaţii este independentă de suportul tehnic de informaţie (independenţa fizică).

� Nivelul fizic (intern) – este dat de viziunea programatorului (inginerului) de sistem asupra datelor. Principalele aspecte la acest nivel: - programatorul de sistem realizează structura internă (fizică); - structura internă corespunde descrierii datelor pe supotul fizic de informaţie; - structura internă este dedusă din cea externă; - implementarea schemei interne se face cu ajutorul sistemului de gestiune a

fişierelor din SGBD şi/sau din sistemul de operare, prin gestiunea fizică a perifericelor.

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

7

Nivele de reprezentare a datelor

Domeniu de aplicaţie

Parte a domeniului obiect de studiu

Entitate-asociere, semantică relaţională, funcţională etc.

Relaţională

În funcţie de SGBD (tabel, înregistrare, segment, etc.)

Formalisme

SGBD

Baza de date

Scheme externe Schema conceptuală

Schema logică

Schema fizică

CONCEPTUAL

LOGIC

FIZIC

Programe

Factori cantitativi

Constrângeri ale SGBD-ului

8

Într-un raport al ANSI / SPARC (American National Standards Institute / Standards

Planning And Requirements Committee) se disting cel puţin trei nivele de reprezentare a datelor (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 de facilităţi, incluse în Data Manipulation Language (DML). Acţiunea se exprimă sub forma unei fraze a limbajului, care este evaluată şi executată de SGBD. Interfeţele sunt alte forme de comunicare care permit SGBD-ului să transmită date către alte limbaje de programare (Pascal, C, C++, Cobol etc.). Aceste interfeţe permit accesul şi manipularea datelor dintr-o bază de date plecând de la un program scris într-un limbaj de programare clasic (procedural).

� Integritatea datelor. Conceptul de integritate a datelor este relativ la calitatea

informaţiei înregistrate. Constrângerile de integritate sunt specificate în definirea schemei bazei de date.

� Accesul concurent. Datele dintr-o bază de date pot fi accesate concurent de mai mulţi utilizatori. SGBD-ul trebuie să ofere mecanisme de gestiune a conflictelor de acces.

� Confidenţialitate. Punerea în comun a datelor pentru mai mulţi utilizatori impune

problema confidenţialităţii. Confidenţialitatea este asigurată prin nume de utilizator şi parolă care generează drepturi de acces diferenţiate.

� Securitatea în funcţionare. SGBD-ul trebuie să ofere mecanisme care să permită

repunerea rapidă a bazei de date în stare operaţională, în caz de incident hardware sau software. Aceste mecanisme sunt bazate pe înregistrarea operaţiunilor realizate asupra bazei 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 abordări ale celor două concepte. Modul lor de tratare este departe de a fi unitar. În unele lucrări se consideră:

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

Alţi autori extind noţiunea de bancă de date şi consideră banca de date formată din :

B. Banca de date := baza de date + hardware + SGBD + programele de aplicaţii + utilizatorii În cartea L’art des bases de données, autorii Miranda S.M., Busta J.M. fac distincţia între cele două concepte:

9

C. Baza de date conţine date primare care sunt exploatate cu ajutorul unui SGBD. În cazul unei interogări, sistemul de gestiune al bazei de date furnizează direct răspunsul. Banca de date date conţine date referenţiale şi accesul este asigurat cu ajutorul unui sistem documentar (SD). Sistemul documentar permite o direcţionare către un text (carte, articol, ...) şi după consultare se obţine răspunsul la interogarea formulată. Exemplu [Miranda, 1988]: Dacă se consideră atributul – “stare de sănătate” având mai multe valori, printre care {...., nebun, ...} şi formulăm interogarea: “Care a fost starea de sănătate a preşedintelui Wilson (SUA) între 1914-1918 ?” Răspunsul dat de banca de date va fi de genul: Istoria contemporană a SUA, pag. 52, ed.

1980, iar răspunsul efectiv se obţine în urma consultării lucrării indicate. În cazul unei baze de date răspunsul va fi direct valoarea atributului “stare de sănătate”: nebun.

1.8. MODELE DE REPREZENTARE A DATELOR

Tipologia SGBD-urilor este în general funcţie de tipurile de structuri ale datelor pe care le suportă. Dintre modelele cele mai întâlnite amintim [Ionescu, 2004]: � modelul ierarhic

� modelul reţea

� modelul relaţional

� modelul orientat-obiect

� modelul obiect relaţional

Modelul ierarhic (Hierarchical Model) – acesta a fost primul model folosit pentru dezvoltarea bazelor de date. Modelul permite reprezentarea claselor sau ansamblelor de obiecte printr-o structură ierarhică de înregistrări. Relaţiile de tip "tată-fiu" între clase sunt de tip 1:N. Ansamblul claselor se constituie într-un arbore direcţionat în care nodurile sunt tipurile de înregistrări, iar arcele sunt tipurile de legături. Modelul reţea (Network Model) – utilizează o structură de graf pentru definirea schemei conceptuale a bazei de date. Nodurile grafului sunt tipurile de entităţi, iar muchiile reprezintă legăturile dintre tipurile de entităţi. Relaţiile sunt de tipul M:N şi se reprezintă fără duplicarea înregistrărilor, fiecare înregistrare putând fi referită de mai multe înregistrări. Acest model este în prezent rar folosit pentru baze de date generale, care implică operaţii de interogare. Aplicarea modelului reţea se întâlneşte în bazele de date grafice utilizate în modelarea realităţii virtuale. Modelul relaţional (Relational Model) – premite vizualizarea unei baze de date ca un ansamblu de tabele bidimensionale. Modelul se bazează pe noţiunea de relaţie din matematică, care corespunde unei mulţimi de entităţi de acelaşi tip. Limbajele relaţionale de manipulare a datelor sunt limbaje neprocedurale – utilizatorul, de exemplu, formulează interogarea fără să indice procedura (algoritmul) de rezolvare. SGBD-urile relaţionale oferă un limbaj de programare unanim recunoscut şi acceptat, limbajul SQL,

10

bazat pe algebra relaţională. Pentru limbajul SQL au fost emise mai multe standarde de către International Standardization Office (ISO).

Modelul orientat-obiect (Object Model) – este un concept unificator în informatică, fiind aplicabil î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. Au o utilizare limitată, mult mai redusă decât cea a sistemelor de baze de date relaţionale. Pentru bazele de date orientate obiect există un limbaj standard standard de interogare OQL (Object Query Language).

Modelul obiect relaţional (Object Relational Model) – este considerat următorul mare val în dezvoltarea şi întreţinerea bazelor de date. Construcţia se poate realiza dezvoltând sistemul relaţional prin adăugarea caracteristicilor obiectuale necesare sau pornind de la un sistem orientat obiect şi adăugând caracteristicile relaţionale. 1.9. SCURTĂ ISTORIE A BAZELOR DE DATE

1961 - apariţia sistemului IDS (Integrated Data Storage, General Electric). Terminologia introdusă (tipuri de înregistrări şi tipuri set) va fi utilizată în modelul reţea prezentat la "Conference On DAta SYstems and Languages Data Base Task Group" (CODASYL DBTG). 1965-1970 - dezvoltarea sistemelor de gestiune a fişierelor generalizate. IBM a dezvoltat modelul iererhic şi sistemul IMS (Information Management System). În acceasşi perioadă apare IMS DB/DC (DataBase/DataCom) care suportă modelul reţea. În anii 70, domeniul se dezvoltă foarte mult, ajungând să fie disciplină universitară şi de cercetare. Astfel apar numeroase produse comerciale care implementează propunerile raportului CODASYL DBTGi: ISD II (HoneyWell), DMS1100 (UNIVAC), DMS II

(Burroughs), etc. 1970 - apare modelul relaţional de date 1971 - publicarea raportului CODASYL DBTG

1972 – prima conferinţă internaţională organizată de ACM SIGMOD (Association of

Computing Machinery, Special Interest Group on Management Of Data) 1975 - prima conferinţă internaţională VLDB (Very Large Data Base); publicarea raportului ANSI-SPARC 1976 - publicarea modelului Entitate - Asociere 1975 - 1980 - dezvoltarea sistemelor relaţionale experimentale: SYSTEM-R (IBM) şi INGRES (Berkeley, University of California) 1980 - .... apariţia şi comercializarea a numeroase SGBD-uri relaţionale ce au înlocuit SGBD-urile ierarhce şi reţea. SGBD-urile pot fi utilizate pe microcalculatoare şi se realizează sisteme din generaţia a patra cu instrumente şi interfeţe multiple. 1990 - .... se dezvoltă numeroase produse a căror complexitate creşte, la un preţ tot mai scăzut: PowerBuilder (SYSBASE), Oracle Developer, VB (Microsoft), etc. Se dezvoltă modelul client-server, ce se foloseşte în tot mai multe aplicaţii economice. Se dezvoltă şi produse sotftware ca Excel/ Access (Microsoft) pentru scop personal de o complexitate mai scăzută.

11

1990-1995 - apar baze de date pentru Internet. Sunt utilizate conceptele client-server, şi astfel complexitatea Internet-ului creşte exponenţial. 2000 - pentru aplicaţiile pe Internet apar o serie de instrumente cum ar fi Active Server

Pages, Java Servlets, JDBC, Enterprise Java Beans, ColdFusion, Dream Weaver, Oracle

Developer 2000, Apache, MySQL. Se dezvoltă procesarea tranzacţiilor online (OLTP)

precum şi procesarea analitică de tip OLAP. După 2000 - se dezvoltă aplicaţii pe arhitectura client-server pentru PDA-uri, tranzacţii cu POS-uri, telefoane mobile. Companiile cele mai reprezentative din domeniu rămân: IBM, Microsoft şi Oracle.

1.10. BAZE DE DATE RELAŢIONALE

Conceptul de baze de date relaţionale (BDR) apare în lucrarea lui E.F.CODD (IBM), A

Relational Model for Large Shared Data Banks, (1970). Modelul de date relaţional a fost perfecţionat în anii următori de C.J. DATE, R. BOYCE, R. FAGIN, W.W. ARMSTRONG. Caracteristicile modelului relaţional:

� datele sunt percepute de utilizatori ca tabele � simplitate şi precizie în definirea elementelor de bază (relaţii, atribute, domenii) � operatorii relaţionali generează un tabel rezultat din tabelele operanzi

� restricţiile de integritate, normalizarea relaţiilor, controlul concurenţei permit crearea structurii datelor şi a prelucrării lor într-un mod consistent şi asigură integritatea şi protecţia acestora.

Definiţia 1.1. BDR este un ansamblu organizat de tabele (relaţii) împreună cu legăturile dintre ele. Avantajele BDR faţă de fişiere (sisteme file-based) [Velicanu, 2003]:

CRITERIU BDR FIŞIERE

Independenţa datelor logică şi fizică fizică Nivele de structurare conceptual, logic, fizic logic şi fizic Deschidere şi portabilitate mare mică Reprezentarea şi utilizarea datelor

simplificată prin model complicată

Structura datelor se păstrează în dicţionarul datelor în programe E.F.CODD formulează 13 reguli pentru evaluarea performanţelor unui sistem de gestiune

a bazelor de date relaţionale (SGBDR). Aceste reguli exprimă cerinţele maximale ca un SGBDR să fie relaţional. Condiţiile minimale ca un SGBD să fie relaţional, pot fi formulate astfel: � să implementeze modelul de date relaţional prin DDL (Data Definition Language) şi

DML (Data Manipulation Language)

� să implementeze un limbaj de interogare relaţional

12

Arhitectura funcţională a unui SGBD relaţional

Elementele de bază utilizate pentru a descrie datele în modelul relaţional din punct de vedere formal, uzual sau fizic sunt:

Formal Uzual Fizic

relaţie tablou fişier tuplu linie articol atribut coloană câmp domeniu tip de dată tip de dată

Interfaţa utilizatorului

Gestiunea vederilor

Integritatea semantică

Autorizarea accesului

Optimizarea cererilor

Gestiunea planurilor de execuţie

Controlul execuţiei

Executarea operatorilor algebrici

Gestiunea buffer-ului

Mecanisme de acces

Gestiunea accesului concurent

Jurnalizarea

Control

Tratarea cererilor

Gestiunea accesului

Securitate

R E Z U L T A T E

13

Principalele concepte utilizate în bazele de date relaţionale

Definiţia 1.2. Domeniul este un ansamblu de valori care poate fi definit explicit prin enumerarea tuturor valorilor sau implicit prin precizarea proprietăţilor pe care le au valorile domeniului respectiv.

Exemplu:

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

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

plan disciplina Baze de date; - domeniul D2 este definit implicit prin specificarea proprietăţilor 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 ∈∈=×××

Fiecărui domeniu i se asociază un atribut : ( ) iiii DAf,DA:f ∈→

Definiţia 1.3. Relaţia poate fi definită ca o mulţime de tupluri ce aparţine produsului cartezian n21 D...DD ××× , astfel spus n21 D...DDR ×××⊆ .

Relaţia se poate memora într-o tabelă bidimensională:

R A1 A2 ..... An

t1 a11 a12 a1n

t2 a21 a22 a2n

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

tm am1 am2 ..... amn

Liniile tabelului formează elementele relaţiei numite şi tupluri.

Notăm: tuplul i prin >=< in2i1ii a,...,a,at .

Schema relaţiei (schema relaţională) este un element invariant în timp şi este dată de mulţimea numelor atributelor corespunzătoare unei relaţii. Pentru fiecare atribut se precizează domeniul asociat. Notăm schema unei relaţii cu: [ ]nn2211 D:A,...,D:A,D:AR

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

Schema bazei de date relaţionale este dată de mulţimea tuturor schemelor relaţionale corespunzătoare unei aplicaţii, iar conţinutul curent al relaţiilor la un moment dat se numeşte bază de date relaţională. Cardinalul unei relaţii este dat de numărul de tupluri din relaţie. Gradul unei relaţii (aritatea relaţiei) este dat de numărul de atribute din relaţie.

14

Relaţia virtuală (relaţie derivată, viziune) este definită implicit pe baza altor relaţii, prin intermediul unei expresii relaţionale. Stabilirea efectivă a tuplurilor care compun relaţia virtuală se realizează prin evaluarea expresiei relaţionale în momentul în care utilizatorul apelează la această relaţie. Domenii compatibile cu reuniunea – domeniile au acelaşi grad (aritate) şi atributele corespondente iau valori în aceleaşi domenii.

Relaţia se prezintă ca o mulţime de tupluri. Logic, această mulţime nu poate conţine elemente identice, cu alte cuvinte, relaţia nu poate avea tupluri duplicate. Necesitatea identificării unui tuplu a condus la noţiunea de cheie.

Definiţia 1.3. Cheia unei relaţii reprezintă ansamblul minimal de atribute cu rol de identificare unică a tuplurilor dintr-o relaţie. Într-o relaţie pot exista mai multe atribute / combinaţii de atribute cu rol de identificare unică a tuplurilor, există deci mai mulţi candidaţi cheie. Dintre aceştia ABD-ul alege cheia primară, celelalte devin chei

secundare sau alternante. Orice relaţie are cel puţin o cheie.

Cheia simplă este cheia formată dintr-un singur atribut, iar cheia compusă este formată din mai multe atribute.

Domeniul primar este domeniul pe care este definită cheia primară. Cheia externă este un atribut /grup de atribute dintr-o relaţie, ale cărui valori sunt definite pe domeniul primar al altei relaţii. Relaţia primară. O relaţie RP este primară, dacă există o altă relaţie R, legată semantic de ea, care are drept cheie externă, cheia primară a relaţiei considerate (RP). Exemple.

1. Fie relaţia STUDENT [nr_matricol, nume, facultate, grupa, sectia, CNP, adresa] Atributele nr_matricol şi CNP au rol de identificare unică a tuplurilor din relaţie; reprezintă candidaţi cheie. Alegem drept cheie primară atributul nr_matricol, care are domeniul format din 4 caractere numerice şi este mai uşor de operat. Atributul CNP, format din 13 caractere numerice devine cheie secundară (alternantă). 2. Se consideră relaţiile:

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]

� relaţia PRODUSE are cheia primară - cod_produs;

15

� relaţia CONTRACTE are cheia primară - nr_contract, iar atributele cod_produs şi cod_client sunt chei externe ;

� relaţia CLIENTI are cheia primară - cod_client, iar atributul CUI este cheie

secundară; � PRODUSE şi CLIENTI sunt relaţii primare.

1.11. RESTRICŢIILE DE INTEGRITATE MINIMALE

Restricţiile de integritate minimale sunt reguli pe care trebuie să le satisfacă datele din baza de date.

A. Integritatea domeniului – constă din controlul sintactic şi semantic al unei date oarecare şi se referă la definiţia tipului domeniului. De exemplu, în cazul unui domeniu definit explicit prin enumerarea valorilor, trebuie să ne asigurăm că valorile atributului respectiv fac parte din mulţimea enumerată. Sau, în cazul unui domeniu definit implicit, se poate verifica dacă numărul unei facturi aparţine unui interval dat.

B. Integritatea entităţii – se referă la restricţii asupra cheii primare. Aceasta trebuie să fie unică şi nenulă (atributele cheii primare trebuie să fie diferite de valoarea null) .

C. Integritatea referirii – impune ca valorile cheii externe să figureze printre valorile

cheii primare din relaţia referită (relaţia primară).

16

CAP.2. ALGEBRA RELAŢIONALĂ

Interogarea (query) este operaţia prin care se obţin datele dorite dintr-o bază de date, selectate conform unor criterii. Deoarece această operaţie este cea mai importantă operaţie, limbajele de manipulare a datelor sunt denumite şi limbaje de interogare. Pentru formularea conceptuală a interogărilor în bazele de date relaţionale s-au dezvoltat două limbaje abstracte de interogare: algebra relaţională şi calculul relaţional.

Algebra relaţională (relational algebra), introdusă de Codd în 1970, defineşte cadrul formal al limbajelor relaţionale pentru baze de date. Algebra relaţională introduce o colecţie de operatori algebrici care se aplică relaţiilor (tabelelor). Fiecare operaţie din algebra relaţională are drept operanzi una sau mai multe relaţii, iar rezultatul este tot o relaţie. Această uniformitate (proprietatea algebrică de închidere) permite aplicarea de combinaţii de operatori relaţiilor. Prin analogie cu un compilator care, plecând de la un program sursă produce un program

executabil, rezultatul compilării unei interogări (cereri) de către un SGBD relaţional este o expresie algebrică care va fi evaluată. În cadrul modelului relaţional se consideră limbaje relaţionale complete numai acele limbaje de interogare care permit implementarea tuturor operaţiilor prevăzute de unul din limbajele abstracte de interogare. Limbajele de interogare reale implementate în sistemele de baze de date relaţionale sunt limbaje definite pe baza limbajelor abstracte de interogare. Cunoaşterea limbajului abstract de interogare bazat pe algebra relaţională este obligatorie pentru înţelegerea aprofundată a modului de execuţie a interogărilor. Algebra relaţională conţine două tipuri de operaţii:

� operaţii pe mulţimi: reuniunea, intersecţia, diferenţa şi produsul cartezian. Pentru a determina reuniunea, intersecţia şi diferenţa a două relaţii, acestea trebuie să fie compatibile cu reuniunea (trebuie să aibă acelaşi număr de atribute şi atributele corespondente să fie definite pe domenii compatibile, adică să aibă formatul datelor identic).

� operaţii relaţionale: selecţia, proiecţia, joncţiunea şi diviziunea. Operaţii de bază - reprezintă un ansamblu minimal de operaţii, în sensul că niciuna din operaţii nu poate fi exprimată ca o combinaţie a celorlalte operaţii. Pentru algebra relaţională există mai multe ansambluri minimale. În continuare prezentarea va considera ca operaţii de bază: trei operaţii pe mulţimi: reuniunea, diferenţa, produsul cartezian şi două operaţii relaţionale unare: selecţia şi proiecţia. Operatorii de intersecţie, joncţiune şi diviziune pot fi obţinuţi din cei cinci operatori de bază.

Notă. În definirea operatorilor algebrei relaţionale vom nota cu: t – un tuplu din relaţia R

t(A) – subtuplu din R relativ la atributul A.

17

Fiecare operator al algebrei relaţionale va fi descris prin signatură. Signatura indică numărul şi tipul operanzilor, precum şi tipul rezultatului.

2.1. OPERATORII ALGEBREI RELAŢIONALE

1. UNION – reuniunea a două relaţii R1 şi R2, compatibile cu reuniunea, este dată de mulţimea tuplurilor care aparţin fie relaţiei R1, fie relaţiei R2, fie ambelor relaţii. Tuplurile care aparţin ambelor relaţii se introduc în reuniune o singură dată, adică nu se duplică.

Signatura: latieRelatieRelatieRe →×

{ }21213 RtsauRt|tRRR ∈∈== U

R3

R1 R2

Exemplu.

R1 A B

a b c d x y

R2 A B

d f x y h r c d

R3 A B

a b c d x y d f h r

Sintaxa:

UNION (R1, R2) - rezultatul este o relaţie. 2. DIFFERENCE – diferenţa a două relaţii R1 şi R2, compatibile cu reuniunea, este dată

de mulţimea tuplurilor care aparţin relaţiei R1 şi nu aparţin relaţiei R2..

U

18


{ }21213 RtsiRt|tRRR ∉∈=−=

R3

R1 R2

Exemplu.

R1 A B

a b c d x y

R2 A B

d f x y h r c d

R3 A B

a b

Sintaxa:

DIFFERENCE (R1, R2) - rezultatul este o relaţie. 3. INTERSECT – intersecţia a două relaţii R1 şi R2, compatibile cu reuniunea, este dată

de mulţimea tuplurilor care aparţin atât relaţiei R1, cât şi relaţiei R2.


{ }21213 RtsiRt|tRRR ∈∈== I

R3

_

19

R1 R2

Exemplu.

R1 A B

a b c d x y

R2 A B

d f x y h r c d

R3 A B

c d x y

Sintaxa:

INTERSECT (R1, R2) - rezultatul este o relaţie. Observaţie. Intersecţia poate fi exprimată cu ajutorul operaţiei de diferenţă (operaţie de bază): ( ) ( )RSSSRRSR −−=−−=I 4. PRODUCT – produsul cartezian a două relaţii R1 şi R2, produce o nouă relaţie care are

ca atribute, reuniunea atributelor din cele două relaţii (atributele comune vor fi luate separat, calificările fiind făcute cu numele relaţiei), iar fiecare element din R1 se combină (concatenează) cu fiecare element din R2.


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

R3

I

R1 R2

20

Exemplu.

R1 A B

a b c d x y

R2 A

a d

R3 R1. A B R2.A

a b a c d a x y a a b d c d d x y d

Sintaxa:

PRODUCT (R1, R2) - rezultatul este o relaţie. 5. SELECT – este o operaţie unară de restricţie care selectează din tuplurile relaţiei R,

acele tupluri care satisfac o condiţie specificată. Condiţia este o expresie logică (predicat) specificată asupra atributelor relaţiei R. Condiţia poate cuprinde nume de atribute, constante, operatori logici (and, or, not), operatori aritmetici de comparare (<, >, =, ≤, ≥, ≠).

Signatura: latieReicalogExpresielatieRe →×

( ) ( ){ }αθσ AtsiRt|tRS conditie ∈==

unde: t(A) θ α defineşte condiţia de selecţie (θ este un operator aritmetic de comparare, iar α un tuplu care poate fi înlocuit de un atribut sau valoarea unui atribut). S

R

Exemplu.

condiţie

21

( )RS 'x'B== σ

R A B C

x y z t x a z x b c u w

S A B C

t x a z x b

Sintaxa:

SELECT (R; condiţie) - rezultatul este o relaţie. Observaţii.

1. Această operaţie nu trebuie confundată cu instrucţiunea SELECT, care este instrucţiunea generală de interogare din limbajele de manipulare a datelor.

2. În termenii limbajului de interogare SQL, operaţia de selecţie realizează o decupare pe orizontală a tabelei operand R.

3. Cardinalul relaţiei rezultat S este mai mic sau egal decât cardinalul relaţiei R.

Egalitatea poate apare în situaţia în care condiţia este adevărată pentru toate tuplurile din relaţie.

6. PROJECT - este o operaţie unară de restricţie prin care se selectează din relaţia R,

numai acele atribute specificate explicit în cadrul operaţiei. Relaţia rezultată P va avea ca atribute submulţimea 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

22

x u x z x y x z x z y y x t x

x x z y x x z y x x

x x z y

Sintaxa:

PROJECT (R; lista atribute) - rezultatul este o relaţie. Observaţii.

1. Dacă în lista atributelor de proiecţie există o cheie a relaţiei operand R, atunci relaţia rezultat are toate tuplurile distincte, adică relaţiile R şi P vor avea acelaşi cardinal.

2. Dacă în lista atributelor de proiecţie nu există o cheie a relaţiei operand R, atunci în relaţia rezultat P pot apare tupluri duplicate care vor fi eliminate. În exemplul prezentat după eliminarea tuplurilor duplicate din relaţia intermediară P’ , s-a obţinut relaţia P care conţine două tupluri distincte.

3. Relaţia rezultat P are gradul k, dat de numărul atributelor din listă. 4. În termenii limbajului de interogare SQL, operaţia de proiecţie realizează o

decupare pe verticală a tabelei operand R.

7. JOIN – este o operaţie definită pe două relaţii R1 şi R2. Relaţia rezultat R3 va fi construită prin concatenarea unor tupluri din R1 cu tupluri din R2 care satisfac o anumită condiţie (condiţia de joncţiune - θ) specificată explicit în cadrul relaţiei. Condiţia de joncţiune - θ este o expresie logică (predicat) specificată asupra atributelor relaţiilor R1 şi R2. Condiţia de joncţiune - θ poate cuprinde nume de atribute, constante, operatori logici (and, or, not), operatori aritmetici de comparare (<, >, =, ≤, ≥, ≠).

Signatura: latieReresieexplatieRelatieRe →×× θ Fie relaţiile ]B,A[R 11 = şi ]C,B[R 22 = , unde B1 şi B2 sunt atribute definite pe acelaşi domeniu. Atunci: ( ) ( ){ }2121213 BtBtsiRRt|tRRR θθ ×∈=⊗=

Observaţie. Joncţiunea se poate exprima în funcţie de operaţiile de bază: produs

cartezian şi selecţie astfel: ( )2121 RRRR ×=⊗ θθ σ

23

Exemplu.

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

R1 A B C

a x c b y c d z g

R2 D E A

0 11 a 1 13 a 3 11 a 4 11 d 6 12 d 7 13 c

R3 R1. A B C D E R2. A

b y c 0 11 a b y c 1 13 a b y c 3 11 a d z g 0 11 a d z g 1 13 a d z g 3 11 a d z g 7 13 c

Sintaxa:

THETA - JOIN (R1, R2; θ - expresie) - rezultatul este o relaţie. În continuare se prezintă patru forme ale operaţiei de joncţiune. 7.1. EQUI – JOIN – este un caz particular al lui THETA – JOIN , când θ este egalitate. Exemplu.

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

R1 A B C

a x c b y c d z a

R2 D E A

0 11 a 1 13 a 3 11 a 4 11 d 6 12 d 7 13 c

R3 R1. A B C D E R2. A

a x c 0 11 a a x c 1 13 a a x c 3 11 a d z a 4 11 d d z a 6 12 d

Sintaxa:

EQUI - JOIN (R1, R2; θ - expresie) - rezultatul este o relaţie.

R1 R2

R3

θ

24

7.2. NATURAL – JOIN – este o joncţiune pe egalitate (EQUI – JOIN) pentru toate

atributele cu acelaşi nume din cele două relaţii, urmată de o proiecţie pe reuniunea atributelor celor două relaţii.

În cazul EQUI – JOIN schema relaţiei rezultat conţine toate atributele celor doi operanzi şi rezultă că în fiecare tuplu al relaţiei rezultat vor exista cel puţin două valori egale. Introducerea joncţiunii naturale va elimina această redundanţă.

Schema relaţiei rezultat R3 se obţine prin reuniunea atributelor celor două relaţii R1 şi

R2 (atributele cu acelaşi nume se iau o singură dată), iar extensia relaţiei R3 va conţine tuplurile obţinute prin concatenarea tuplurilor din R1 cu tupluri din R2, care au aceleaşi valori pentru atributele cu acelaşi nume.

Joncţiunea naturală este joncţiunea cea mai utilizată în practică şi poate fi definită cu

ajutorul operaţiilor de bază: proiecţie, selecţie şi produs cartezian. Dacă se notează cu: θ - condiţia de egalitate între valorile atributelor din intersecţia schemelor relaţiilor

R1 şi R2 (coloanele comune), atr - reuniunea atributelor celor două scheme (atributele cu acelaşi nume se iau o

singură dată), atunci:

( )( )21atr213 RRRRR ×∏=⊗= θσ

Exemple.

A. Se dau relaţiile: R1[A, B, C] şi R2 [B, C, D] ( ) ( )C.RC.RandB.RB.R 2121 ===θ atr = A, B, C, D

R1 A B C

a b c d b c b b f c a d

R2 B C D

b c d b c e a d b

R3 A B C D

a b c d a b c e d b c d d b c e c a d b

B. Se dau relaţiile: R1 [A, B, C] şi R2 [D, E, A] A.RA.R 21 ==θ atr = A, B, C, D, E

25

R1 A B C

a x c b y c d z a

R2 D E A

0 11 a 1 13 a 3 11 a 4 11 d 6 12 d 7 13 c

R3 A B C D E

a x c 0 11 a x c 1 13 a x c 3 11 d z a 4 11 d z a 6 12

Sintaxa:

NATURAL - JOIN (R1, R2; atribut(e) joncţiune

*) - rezultatul este o relaţie.

* pentru mărirea clarităţii va apare atributul / atributele de joncţiune. Observaţie.

Selecţia este un caz particular de joncţiune naturală a unei relaţii cu o relaţie constantă.

Înţelegem prin relaţie constantă o relaţie care are un singur tuplu, eventual redus la o singură valoare. 7.3. SEMI – JOIN - este joncţiunea dintre două relaţii R1 şi R2 , urmată de o proiecţie pe

atributele relaţiei R1. Semi – joncţiunea conservă atributele unei relaţii participante la joncţiune (R1). Semi – joncţiunea mai poate fi privită ca o generalizare a operaţiei de selecţie, rezultatul fiind o selecţie asupra relaţiei R1 , realizată pe baza valorilor din R2 ale atributului de joncţiune.

Exemplu.

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

R1 A B C

a x c b y c d z a

R2 D E A

0 11 a 1 13 a 3 11 a 4 11 d 6 12 d 7 13 c

R3 A B C

a x c a x c a x c d z a d z a

R1 R2

R3

θ

26

Sintaxa:

SEMI - JOIN (R1, R2; θ - expresie) - rezultatul este o relaţie. Observaţie.

Considerăm joncţiunea naturală dintre ]B,A[R 11 = şi ]C,B[R 22 = , unde B1 şi B2 sunt atributele de joncţiune. � Dacă B1 = B2 = Φ , atunci joncţiunea corespunde produsului cartezian. � Dacă A = C = Φ , atunci joncţiunea corespunde intersecţiei. � Dacă A = Φ sau C = Φ (dar nu amândouă), atunci operaţia este o semi-

joncţiune.

7.4. OUTER – JOIN. Joncţiunea dintre două relaţii R1 şi R2 poate conduce la pierdere de tupluri, dacă relaţiile participante la joncţiune nu au proiecţii identice pe atributul de joncţiune, adică nu au aceleaşi valori în relaţiile care se joncţionează. Relaţia rezultat R3 conţine joncţiunea naturală dintre R1 şi R2, la care se adaugă tuplurile din R1 şi R2, care nu au participat la joncţiune. În aceste tupluri se va atribui valorea null pentru atributele relaţiei corespondente.

Exemplu.

2ext13 RRR ⊗=

R1 A B C

a x c b y c

d z a

R2 D E A

0 11 a 1 13 a 3 11 a 4 11 d 6 12 d 7 13 c

R3 A B C D E

a x c 0 11 a x c 1 13 a x c 3 11 d z a 4 11 d z a 6 12 b y c null null

c null null 7 13

R1 R2

R3

ext

27

Observaţii. În urma joncţiunii naturale se pierd informaţiile din tuplurile < b, y, c > din R1 şi < 7, 13, c > din R2. Aceste tupluri se adaugă în cazul joncţiunii externe şi se completează cu null pe atributele relaţiei corespondente. Sintaxa:

OUTER - JOIN (R1, R2; atribut(e) joncţiune) - rezultatul este o relaţie. 8. DIVISION - este o operaţie definită pe două relaţii care au schema R1 [A1,

A2,..., An] şi R2 [Ap+1, Ap+2,..., An]. Relaţia rezultat 213 RRR ÷= are schema R3 [A1,

A2,..., Ap] şi este formată din toate tuplurile care, concatenate cu fiecare tuplu din R2 , dau întotdeauna un tuplu din R1.

Notăm: ATR1 = {A1, A2,..., Ap+1, Ap+2,..., An} ATR2 = {Ap+1, Ap+2,..., An}

Definiţia 1. 21 RRt ÷∈ dacă 1122 RtRt ∈∃∈∀ , astfel încât ( ) tt1ATRATR 21

=∏ −

şi ( ) 21ATR tt2

=∏ .

Definiţia 2. Diviziunea se poate exprima în funcţie de operaţiile de bază: produs

cartezian, diferenţă şi proiecţie astfel:

( ) ( )( )( )121ATRATRATRATR1ATRATR21 RRRRRR212121

−×∏∏−∏=÷ −−−

Signatura: latieRelatieRelatieRe →× R3

R1 R2

Problemă (Exemplu de diviziune).

Fie relaţia R1 [K, P] unde atributul K are ca valori codurile angajaţilor unui institut de cercetare, iar atributul P conţine codurile proiectelor în derulare. Un cercetător poate lucra la unul sau mai multe proiecte. Să se determine codurile angajaţilor angrenaţi simultan în proiectele P3 şi P4.

28

Rezolvare.

Construim relaţia R2 [P] care va conţine două tupluri: <P3> şi <P4>.

Codurile angajaţilor care lucrează la proiectele P3 şi P4 sunt date de rezultatul diviziunii

21 RR ÷ .

R1 K P

17 P1 17 P2 17 P3

17 P4

29 P1 29 P3 53 P3

53 P4

80 P3

R2 P

P3 P4

Calculăm diviziunea conform definiţiei 2: 2121 QQRR −=÷ Pasul 1. Calculăm ( )1ATRATR1 RQ

21 −∏= Pasul 2. Calculăm 21 RQS ×=

Q1 K

17 29 53 80

S K P

17 P3 29 P3 53 P3 80 P3 17 P4 29 P4

53 P4 80 P4

Pasul 3. Calculăm 1RST −= Pasul 4. Calculăm ( )TQ21 ATRATR2 −∏=

T K P

29 P4 80 P4

T K

29 80

Pasul 5. Calculăm 2121 QQRR −=÷

29

R1:R2 K

17 53

Rezultatul interogării: angajaţii cu codul <17> şi <53> lucrează simultan în proiectele <P3> şi <P4>.

Sintaxa:

DIVISION (R1, R2) - rezultatul este o relaţie. 2.2 OPERAŢII DE CALCUL

La operaţiile descrise anterior se pot adăuga operaţii de calcul pe relaţii. Aceste operaţii sunt justificate de numeroasele interogări (cereri) care necesită operaţii de calcul. Operaţiile de calcul sunt implementate în toate limbajele de interogare. Aceşti operatori de calcul formează deci o extensie a operatorilor de bază şi nu pot fi exprimaţi cu ajutorul acestora. 1. COUNT - este o operaţie care permite numărarea tuplurilor dintr-o relaţie (liniilor dintr-o tabelă) care au aceeaşi valoare pe atributul considerat (sau aceleaşi valori pe atributele considerate). Relaţia rezultantă va conţine numai atributul (atributele) de regrupare Xi , iar tuplurile vor fi formate din valorile distincte şi numărul de apariţii. Notăm: )R(COUNTT

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

T

R

Operatorul COUNT

Count...

30

Exemplu.

R A B C

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

CountB(R) B Count

n 2 m 2 o 1 p 1

CountB,C(R) B C Count

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

Dacă nu este precizat niciun atribut de regrupare, operaţia COUNT va determina numărul de tupluri din relaţie:

Count(R) Count

6

Sintaxa:

COUNT (R; X1, X2, ..., Xn) - rezultatul este o relaţie;

COUNT (R) - rezultatul este un număr (poate fi interpretat şi ca o relaţie cu un singur atribut şi un singur tuplu, care are ca valoare numărul de linii din tabelă).

2. SUM – este o operaţie care permite efectuarea sumei valorilor atributului Y pentru fiecare din valorile diferite ale atributelor de regrupare X1,...,Xn . Atributul Y trebuie să fie numeric. Notăm: )Y,R(SUMT


Dacă nu este precizat niciun atribut de regrupare, operaţia SUM va determina suma valorilor atributului Y. T

R

Operatorul SUM

Sum...

31

Exemplu.

R A B C

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

SumB(R,C) B Sum

n 34 m 30 o 14 p 13

Sum(R,C) Sum

91

Sintaxa:

SUM (R, Y; X1, X2, ..., Xn) - rezultatul este o relaţie;

SUM (R, Y) - rezultatul este un număr (poate fi interpretat şi ca o relaţie cu un singur atribut şi un singur tuplu, care are ca valoare suma valorilor atributului Y din toate liniile tabelei).

3. MEAN – este o operaţie care permite efectuarea mediei aritmetice a valorilor atributului Y pentru fiecare din valorile diferite ale atributelor de regrupare X1,...,Xn .

Atributul Y trebuie să fie numeric. Notăm: )Y,R(MEANT


Dacă nu este precizat niciun atribut de regrupare, operaţia MEAN va determina media aritmetică a valorilor atributului Y din toată relaţia. T

R

Mean..

..m...

32

Operatorul MEAN

Exemplu.

R A B C

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

MeanB(R,C) B Sum

n 15 o 9 p 17

Mean(R,C) Sum

15

Sintaxa:

MEAN (R, Y; X1, X2, ..., Xn) - rezultatul este o relaţie;

MEAN (R, Y) - rezultatul este un număr (poate fi interpretat şi ca o relaţie cu un singur atribut şi un singur tuplu, care are ca valoare media aritmetică a valorilor atributului Y din toate liniile tabelei).

4. MAX şi MIN - este o operaţie care permite determinarea valorii maxime / minime a atributului Y pentru fiecare din valorile diferite ale atributelor de regrupare X1,...,Xn .

Atributul Y trebuie să fie numeric. Notăm: )Y,R(MAXT


)Y,R(MINT


Dacă nu este precizat niciun atribut de regrupare, operaţia MAX (MIN) va determina maximul (minimul) valorilor atributului Y din toată relaţia.

T

33

R

Operatorul MAX (MIN)

Exemplu.

R A B C

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

MinB(R,C) B Min

n 13 o 9 p 10

Max(R,C) Max

21

Sintaxa:

MAX (R, Y; X1, X2, ..., Xn) - rezultatul este o relaţie;

MAX (R, Y) - rezultatul este un număr (poate fi interpretat şi ca o relaţie cu un singur atribut şi un singur tuplu, care are ca valoare maximul valorilor atributului Y din toate liniile tabelei).

Observaţie. Pentru operaţia MIN sintaxa este analoagă.

Max...

34

CAP. 3. LIMBAJUL SQL

SQL (Structured Query Language - Limbaj Structurat de Interogare) este un limbaj de programare neprocedural specific bazelor de date. Limbajul SQL este standardizat ANSI-ISO (fiind cel mai popular limbaj de manipulare a bazelor de date relaţionale) şi poate fi utilizat în: MySQL, SQL Server, MS Access,

Oracle, DB2, etc. Pe lângă versiunile standardizate ale limbajului SQL, există şi o mulţime de dialecte şi variante caracteristice diferitelor SGBD-uri. Limbajul SQL permite:

• manipularea structurii bazelor de date • manipularea datelor conţinute

Principalele instucţiuni de definire a datelor sunt:

• CREATE TABLE • ALTER TABLE • DROP TABLE

3.1. Instrucţiunea CREATE TABLE

Instrucţiunea CREATE TABLE este utilizată pentru a crea o nouă tabelă. Această opţiune este utilizată cu precădere dacă mediul de lucru nu posedă instrumente pentru crearea şi modificarea tabelelor într-o manieră mai facilă, aşa cum are spre exemplu Microsoft Access. Sintaxa generală a instrucţiunii CREATE TABLE este: CREATE TABLE nume_tabela (c1 d1 [constrângeri_coloană],

c2 d2 [constrângeri_coloană], ... cn dn [constrângeri_coloană], [constrângeri_coloană])

unde: c1, c2, ... , cn - reprezintă coloanele tabelei d1, d2, ... , dn - reprezintă domeniile fiecărui câmp Exemplul 1. Dacă dorim crearea unei tabele cu numele angajati1, cu următoarele câmpuri:

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 afişat pentru exemplul 1

3.2. Instrucţiunea ALTER TABLE

Pentru modificarea strcturii unei tabele se utilizează instrucţiunea ALTER TABLE. Această instrucţiune permite adăugarea sau ştergerea unor câmpuri, modificarea domeniilor unor câmpuri, precum şi adăugarea sau ştergerea unor constrângeri ale tabelei. Instrucţiunea ALTER TABLE are următoarele sintaxe: - pentru adăugarea unui nou câmp:

ALTER TABLE nume_tabela ADD nume_coloană domeniu - pentru ştergerea unui câmp

ALTER TABLE nume_tabela DROP nume_coloană - pentru modificarea constrângerilor unu câmp

ALTER TABLE nume_tabela ALTER COLUMN nume_coloană domeniu

Exemplul 2. Dacă dorim adăugarea în tabela angajati1 a unui nou câmp numit bonus de tip integer vom avea:

ALTER TABLE angajati1 ADD bonus int

36


Exemplul 3. Dacă dorim, în tabela angajati1, modificarea câmpului numit bonus din integer în tip de caractere cu lungimea maxima de 10 caractere vom avea:

ALTER TABLE angajati1 ALTER COLUMN bonus varchar(10)


Exemplul 4. Dacă dorim ştergerea din tabela angajati1 a câmpului numit bonus vom avea:

ALTER TABLE angajati1 DROP bonus

37


3.3. Instrucţiunea DROP TABLE

Pentru ştergerea unei tabele se utilizează instrucţiunea DROP TABLE. Această instrucţiune va face ştergerea efectivă a întregii tabele cu toate datele conţinute. Sintaxa generală a instrucţiunii DROP TABLE este:

DROP TABLE nume_tabela Exemplul 5. Dacă dorim ştergerea tabelei angajati1 vom avea:

DROP TABLE angajati1 Principalele instrucţiuni de manipulare a datelor sunt:

• SELECT • INSERT • UPDATE • DELETE

3.4. Instrucţiunea SELECT

Instrucţiunea SELECT este instrucţiunea de interogare a datelor din limbajul SQL. Utilizarea acestei instrucţiuni generează o tabelă virtuală, numită vedere (query). În query se regăsesc toate informaţiile dorite din unul sau mai multe tabele ale bazei de date. Sintaxa generală a instrucţiunii SELECT este: SELECT [DISTINCT] c1, c2, ... , cn [FROM t1, t2, ... , tm] [WHERE condiţie] [clauze secundare] unde: c1, c2, ... , cn - sunt coloanele dorite din tabelele specficate în clauza FROM t1, t2, ... , tm - sunt tabelele din care se face selecţia

38

Rezultatul selecţiei este format din coloanele c1, c2, ... , cn cu datele rezultate din produsul cartezian al tabelelor t1, t2, ... , tm pentru care se respectă eventuala condiţie specificată în clauza WHERE. � Clauza SELECT defineşte coloanele tabelei rezultat. � Clauza FROM indică unu sau mai multe tabele ce conţin datele dorite. � Clauza WHERE definşte condiţia sau condiţiile ce trebuie îndepline de datele din

clauza SELECT. Între clauzele secundare amintim: ORDER BY, GROUP BY, HAVING. Exemplele următoare vor fi construite pentru tabela ANGAJATI:

Tabela ANGAJATI

Exemplul 6. Dacă dorim afişarea tuturor datelor din tabelă vom avea: SELECT cod_sal, nume, adresa, localitate, sal_brut, cod_dep, data_angajarii FROM angajati sau SELECT * FROM salariati Observaţie. În acest al doilea caz, simblul * înlocuieşte toate câmpurile din tabelă. Exemplul 7. Dacă dorim afişarea tuturor angajaţilor din localitatea Brasov ce au salariul mai mare de 1200 vom avea: SELECT cod_sal, nume, localitate, sal_brut FROM angajati WHERE localitate="brasov" AND salariu_brut>1200

39


Exemplul 8. Dacă dorim afişarea tuturor persoanelor angajate după data de 01.01.2009 vom avea: SELECT cod_sal, nume, data_angajarii FROM angajati WHERE data_angajarii>=#01-01-2009#


În clauza SELECT se pot utiliza următoarele funcţii agregat:

• COUNT (numără 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 afişarea numărului de angajaţi vom avea: SELECT count(*) FROM angajati


40

Exemplul 10. Dacă dorim afişarea mediei aritmetice a salariului brut vom avea: SELECT AVG(sal_brut) FROM angajati


Instrucţiunea SELECT poate să nu conţină nici clauza FROM, dacă datele nu sunt conţinute de nicio tabelă. În acest caz, instrucţiunea SELECT conţine o listă de expresii pe care le calculează. Exemplul 11. Dacă dorim afişarea rezultatului produsului 50x25 vom avea: SELECT 50*25


Dacă se doreşte, în tabela rezultat se pot redenumi coloanele, sau se pot denumi anumite expresii, utilizând clauza AS. Exemplul 12. Dacă dorim afişarea rezultatului produsului 50x25, iar numele tabelei să fie REZULTAT vom avea: SELECT 50*25 AS rezultat


Clauza FROM este obligatorie, dacă în clauza SELECT se doreşte afişarea unor coloane din tabele. Dacă se doreşte selectarea unor coloane din tabele diferite, acestea vor fi toate enumerate în clauza FROM, despărţite prin virgulă. În cazul în care un câmp apare în mai mult de o tabelă, atunci pentru a se cunoaşte din ce tabelă se doreşte respectivul câmp, la clauza FROM se va specifica şi numele tabelei de forma: nume_tabel.nume_câmp

41

În clauza WHERE se impun toate condiţiile necesare pentru datele din tabela rezultat. În clauza WHERE se pot utiliza şi operatorii logici (AND, OR, NOT) şi paranteze. Exemplul 13. Dacă dorim afişarea angajaţilor din Brasov sau Predeal vom avea: SELECT nume, localitate FROM angajati WHERE localitate="brasov" or localitate="predeal"


Clauza ORDER BY face ordonarea liniilor din tabela rezultat după coloana ce urmează clauzei. Implicit, ordonarea se face în ordine crescătoare sau alfabetică dacă tipul câmpului după care se face ordonarea este de tip text. În cazul în care se doreşte ordonare invers lexicografică (descrescătoare), atunci numele coloanei trebuie urmat de cuvântul DESC. Exemplul 14. Dacă dorim afişarea angajaţilor în ordine descrescătoare vom avea: SELECT nume FROM angajati ORDER BY nume DESC


42

Clauza GROUP BY se utilizează pentru gruparea rezultatelor funcţiilor agregat, în funcţie de valoarea unei sau mai multor coloane. Clauza GROUP BY se utilizează la sfârşitul instrucţiunii, fiind urmată de câmpul pentru care se face gruparea rezultatelor funcţiei agregat. Exemplul 15. Dacă dorim numărul de angajaţi din fiecare localitate vom avea: SELECT COUNT(*) AS numar, localitate FROM angajati GROUP BY localitate


Clauza HAVING se utilizează în loc de clauza WHERE, atunci când în instrucţiune se utilizează funcţii agregat. Clauza HAVING este asemănătoare clauzei WHERE, adică introduce o condiţie pe care trebuie să o respecte liniile din rezultat şi în plus permite utilizarea funcţiilor agregat în expresia condiţională. Exemplul 16. Dacă dorim numărul de angajaţi din Brasov si Predeal vom avea: SELECT COUNT(*) AS numar, localitate FROM angajati GROUP BY localitate HAVING localitate="brasov" OR localitate="predeal"


Subinterogările reprezintă instrucţiuni SELECT în alte interogări de tip SELECT. Numim această tehnică imbricare. Astfel, instrucţiunile SELECT se pot imbrica pe mai multe niveluri, o instrucţiune având ca argument rezultatul unei alte instrucţiuni, numită şi subinterogare.

43

Exemplul 17. Dacă dorim numele angajatului care are salariul egal cu salariul maxim vom avea: SELECT nume, sal_brut FROM angajati WHERE sal_brut IN (SELECT MAX(sal_brut) FROM angajati)


Clauza IN şi NOT IN specifică dacă valorile unui câmp aparţin unei mulţimi precizate. Această mulţime poate fi formată prin enumerarea elementelor sau printr-o subinterogare. Exemplul 18. Dacă dorim numele angajaţilor din Brasov, Bucuresti şi Predeal vom avea: SELECT nume, localitate FROM angajati WHERE localitate IN ("brasov", "bucuresti", "predeal")


3.5. Instrucţiunea INSERT

Instrucţiunea INSERT este utilizată pentru introducerea datelor în tabelă. Instrucţiunea INSERT are următoarea 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 corespunzătoare coloanelor c1, c2, ... ,cn Observaţie. Între valori şi numele coloanelor trebuie să existe o corespondenţă directă.

44

Exemplul 19. Dacă dorim introducerea în tabela ANGAJAŢI a unui nou angajat cu datele: 18, EMILIA, str. O.Goga nr. 3, bucuresti, 1300, prod, 05.05.2009 vom avea: INSERT INTO TABLE angajati VALUES (18, "EMILIA", "str. O.Goga nr. 3", "bucuresti", 1300, " prod", #5/5/2009#);


Dacă se doreşte introducerea datelor în altă ordine decât cea implicită a coloanelor din tabelă, sau nu se cunoaşte această ordine, trebuie specificată ordinea câmpurilor după numele tabelei. Exemplul 20. Dacă dorim introducerea în tabela ANGAJAŢI a unui nou angajat cu datele: 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#);


45

3.6. Instrucţiunea UPDATE

Instrucţiunea UPDATE permite modificarea valorilor din coloanele unei tabele pentru anumite condiţii. Sintaxa generală este: UPDATE nume_tabel SET c1=e1 [c2=e2 , ... ,n] [WHERE condiţie] Clauza WHERE impune ca actualizarea valorilor să se facă doar asupra liniilor care îndeplinesc o serie de condiţii. Dacă lipseşte, se vor modifica toate liniile din tabelă. Exemplul 21. Dacă dorim modificarea tuturor salariilor angajaţilor din departamentul "prod" la valoarea de 1000 lei vom avea: UPDATE angajati SET sal_brut=1000 WHERE cod_dep="prod"


Valoarea poate fi schimbată şi cu valoarea unei expresii calculate. De exemplu: Exemplul 22. Dacă dorim modificarea tuturor salariilor angajaţilor din departamentul "conta" în sensul creşterii cu 15% vom avea: UPDATE angajati SET sal_brut=sal_brut*115/100 WHERE cod_dep="conta"

46


3.7. Instrucţiunea DELETE

Instrucţiunea DELETE este utilizată pentru ştergerea uneia sau mai multor linii dintr-o tabelă. Sintaxa instrucţiunii DELETE este: DELETE FROM nume_tabela [WHERE condiţie] Utilizând această instrucţiune se vor şterge toate liniile care îndeplinesc condiţia specificată în clauza WHERE. Dacă este omisă clauza WHERE se vor şterge toate liniile din tabelă. Exemplul 23. Dacă dorim ştergerea tuturor angajaţilor din departamentul "conta" vom avea: DELETE FROM angajati WHERE cod_dep="conta"

47


48

CAP. 4. NORMALIZAREA RELAŢIILOR

1. INTRODUCERE

În activitatea de modelare a bazelor de date problema care se pune este de a stabili mulţimea de relaţii care realizează o reprezentare fidelă a schemei conceptuale, evitând incoerenţa, redundanţa şi pierderile de informaţii. Relaţiile (tabelele) unei baze de date se pot stabili în mai multe moduri şi de aceea este necesar să existe criterii de evaluare a calităţii relaţiilor, pentru ca acestea să asigure integritatea datelor şi posibilităţi de

interogare performante. Teoria normalizării se bazează pe observaţia că anumite relaţii au posibilităţi mai bune de actualizare şi interogare decât alte relaţii echivalente (care conţin aceleaşi informaţii). Normalizarea relaţiilor permite obţinerea unei baze de date în care să nu se manifeste anomalii de actualizare sau stocare. Pentru a înţelege nevoia de normalizare să considerăm relaţia R care conţine informaţii legate de furnizori (cod_funizor, nume_furnizor, localitate, cod_loc) şi de produsele care le oferă (cod_produs, denumire, um, cantitate). Un furnizor poate oferi mai multe produse, iar un produs poate fi oferit de mai mulţi furnizori. R [cod_funizor, cod_produs, nume_furnizor, localitate, cod_loc, denumire, um, cantitate] Cheia relaţiei R este (cod_funizor, cod_produs). cod_furnizor cod_produs nume_furnizor localit cod_loc denumire um cant

F1 P13 Alfa SRL Brasov 5000 xyz kg 200 F2 P17 Beta SRL Cluj 3000 abc mp 600 F3 P13 Gama SRL Sinaia 2555 xyz kg 800 F1 P17 Alfa SRL Brasov 5000 abc mp 400 F2 P29 Beta SRL Cluj 3000 efg litru 600

Observăm că datele despre fiecare furnizor (nume_furnizor, localitate, cod_loc) apar în fiecare tuplu în care se prezintă un produs oferit de un anumit furnizor. Analog, datele generale despre fiecare produs ( denumire, um) apar în fiecare tuplu în care un furnizor oferă respectivul produs. Aceste redundanţe conduc la creşterea spaţiului de memorare şi la anomalii de actualizare a relaţiei. Anomalii de inserare – nu se pot introduce datele generale despre un furnizor (nume_furnizor, localitate, cod_loc), dacă nu există cel puţin un produs pe care acesta să-l ofere. O altă anomalie de inserare – nu putem introduce informaţiile generale despre un produs ( denumire, um), dacă nu există un furnizor care să-l ofere. Aceste anomalii apar datorită restricţiei de integritate care impun ca într-o relaţie atributele cheie nu pot să aibă valoarea null.

49

Anomalii de ştergere – dacă se şterg toate informaţiile legate de un furnizor, de exemplu firma nu mai lucrează cu furnizorul F2, atunci tuplurile cu cheile <F2, P17> şi <F2,

P29> vor fi şterse. Se pierd astfel informaţiile generale legate de produsul P29,

(denumire, um). Anomalii de actualizare – orice modificare a unei informaţii generale legate de un furnizor trebuie să se propage în toate tuplurile în care apare acel furnizor. Aceasta măreşte timpul de actualizare şi creşte riscul de incoerenţă al datelor. Acelaşi tip de anomalie apare şi în cazul modificării unei informaţii generale despre un produs. Teoria normalizării are la bază analiza dependenţelor dintre atributele care sunt la originea fenomenelor de redundanţă şi propune două scheme de modelare a bazelor de date relaţionale fără anomalii sau pierderi de informaţii [Popescu, 1996]:

� schema descompunerii – schema relaţiei universale (relaţia universală este relaţia care conţine toate atributele care modelează sistemul real cercetat) se descompune prin proiecţii succesive în subrelaţii; descompunerea se opreşte când continuarea ar conduce la pierderi de informaţii; procesul de descompunere este reversibil, ceea ce garantează că relaţia de plecare (universală) poate fi regăsită prin utilizarea operatorului de joncţiune şi astfel nicio informaţie nu a fost pierdută;

� schema sintezei – porneşte de la o mulţime de atribute independente; pe baza proprietăţilor de semantică şi legături între atribute se compun relaţii care să evite eventualele anomalii.

Procesul de ameliorare a schemei conceptuale trebuie să satisfacă următoarele cerinţe:

� să asigure conservarea datelor, adică în schema conceptuală finală trebuie să regăsim toate datele din cadrul schemei iniţiale;

� să asigure conservarea dependenţelor dintre date, adică în schema conceptuală finală fiecare dependenţă trebuie să aibă determinantul şi determinatul în schema aceleiaşi relaţii;

� să reprezinte o descompunere minimală a relaţiilor iniţiale, adică niciuna din relaţiile care compun schema finală nu trebuie conţinută într-o altă relaţie din această schemă.

Pentru ca informaţiile dintr-o bază de date să fie prelucrate cât mai simplu este necesar ca relaţiile să verifice anumite condiţii, altfel spus să aibă un anumit grad de normalizare. Forma normală (Normal Form) a unei relaţii presupune anumite condiţii pe care trebuie să le îndeplinească valorile atributelor şi dependenţele funcţionale definite pe aceea relaţie. E.F.Codd a definit primele trei forme normale (1NF, 2NF, 3NF). Ulterior a fost definită mai complet 3NF şi a primit numele de forma normală Boyce-Codd (BCNF). Formele normale superioare, definite de R. Fagin, se referă la dependenţele multivaloare (4NF) şi dependenţele de joncţiune (5NF). De remarcat că BCNF, 4NF şi 5NF corespund definiţiei unice: orice determinant al unei dependenţe este o cheie. Diferenţa este dată de faptul că

50

în cazul BCNF este vorba de dependenţa funcţională, în cazul 4NF de dependenţa

multivaloare, iar în cazul 5NF de dependenţa de joncţiune [Fotache, 2005]. În continuare ne limităm prezentarea la primele trei forme normale definite de Codd, considerate în multe lucrări de specialitate a fi suficiente pentru proiectarea corectă a bazelor de date. 2. DEPENDENŢE FUNCŢIONALE

Dependenţa funcţională reprezintă dependenţa dintre date prin care se poate identifica un atribut sau grup de atribute prin intermediul altui atribut sau grup de atribute.

Definiţia 4.1. Dependenţa funcţională. Dată o relaţie R, spunem că un atribut sau un grup de atribute Y depinde funcţional de un atribut sau grup de atribute X, dacă pentru fiecare valoare a lui X se asociază o singură valoare a lui Y în orice tuplu din R. Formal: ( ) ( ) ( ) ( )2Y1Y2X1X21 tttt:avemRt,t ∏=∏⇒∏=∏∈∀ sau echivalent: pentru orice tupluri <x, y>, <x’, y’> din R, x = x’ → y = y’. Vom spune că “X determină pe Y” sau “Y depinde funcţional de X” şi vom nota

X → Y . Atributul (grupul de atribute) X se numeşte determinant, iar atributul (grupul de atribute) Y se numeşte determinat, adică: determinant → determinat.

În cazul exemplului prezentat în introducere identificăm următoarele dependenţe funcţionale: (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

51

Tipuri de dependenţe funcţionale

Definiţia 4.2. Dependenţa funcţională trivială.

O dependenţă funcţională X → Y este trivială dacă XY ⊆ . Definiţia 4.3. Dependenţa funcţională parţială (dfp).

O dependenţă funcţională X → Y este parţială dacă Y1X.i.aX1X →⊂∃ Vom numi dependenţa funcţională X1 → Y, dependenţă argument dfp.

Exemplu.

Dependenţa funcţională: (cod_furnizor, cod_produs) → nume_furnizor este parţială deoarece se manifestă şi dependenţa argument dfp: cod_furnizor → nume_furnizor Definiţia 4.4. Dependenţa funcţională completă (totală) (dfc).

O dependenţă funcţională X → Y este completă (totală) dacă nu există Y1X.i.aX1X →⊂

Definiţia 4.5. Dependenţa funcţională tranzitivă (dft).

O dependenţă funcţională X → Y este tranzitivă dacă se manifestă concomitent dependenţele funcţionale: Z → X şi Z → Y.

Exemplu.

Dependenţa funcţională: cod_loc → localitate este tranzitivă deoarece se manifestă concomitent şi dependenţele: cod_furnizor → localitate cod_furnizor → cod_loc

Observaţie.

Cheia unei relaţii poate fi definită cu ajutorul dependenţelor funcţionale astfel: X este o cheie pentru relaţia R [X, Y] dacă Y depinde funcţional de X adică: X → Y.

Cheia X este minimală dacă dependenţa funcţională X → Y este completă.

3. PRIMA FORMĂ NORMALĂ (1NF)

Definiţia 4.6. O relaţie este în prima formă normală notată (1NF), dacă fiecare din atributele sale are un domeniu atomic (monovaloare). O relaţie în 1NF nu conţine grupuri repetitive.

Observaţie.

Noţiunea de grup repetitiv (mulţime de valori) nu există în modelul relaţional. O relaţie nenormalizată poate fi transformată într-o relaţie 1NF, înlocuind atributul compus prin atributele simple corespunzătoare (spargerea relaţiei) sau duplicând tuplele de atâtea ori câte valori există pentru un atribut dat (spargerea grupului repetitiv). Exemple de relaţii nenormalizate:

52

a) relaţie în care un atribut este o relaţie ZBOR [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) Observăm că atributul compus AVION din relaţia ZBOR este de fapt o relaţie cu două atribute tip_nava şi capacitate (număr de locuri).

Rezultatul trecerii în 1NF prin spargerea relaţiei AVION este: ZBOR 1 NR_ZBOR TIP NAVA CAPCITATE

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

b) relaţie în care un atribut este un ansamblu de valori CATALOG [Nume, Note]

CATALOG NUME NOTE

Einstein 8, 6 Freud 7, 9, 5 Rezultatul trecerii în 1NF, în situaţia că numărul maxim de note este cunoscut, prin spargerea relaţiei NOTE este: CATALOG 1 NUME NOTA 1 NOTA 2 NOTA 3

Einstein 8 6 null Freud 7 9 5 În cazul spargerii grupului repetitiv NOTE se obţine relaţia: CATALOG 2 NUME NOTA

Einstein 8 Einstein 6 Freud 7 Freud 9 Freud 5 Teorema 1 (de eliminare a grupurilor repetitive)

53

Dacă R [A1, A2, ..., An] este o relaţie în care Am+1, Am+2, ..., An formează un grup repetitiv, şi {A1, A2, ..., Ap} cu p < m este o cheie primară, atunci relaţia R se poate descompune în două relaţii fără grupuri repetitive şi pierdere de informaţii, astfel: R1 [A1, A2, ..., Am] = ( )R

m21 A,...,A,A∏

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

n1mp21 A,...,A,A,...,A,A +∏

Algoritmul 1NF - de aducere a unei relaţii nenormalizate în 1NF

(eliminarea atributelor compuse şi repetitive)

Pasul 1. Se trec în relaţie în locul atributelor compuse componentele acestora ca atribute simple.

Pasul 2. Se trec grupurile de atribute repetitive, fiecare într-o nouă relaţie. Pasul 3. Se introduce în schema fiecărei noi relaţii create la Pasul 2 cheia primară a

relaţiei din care a fost extras grupul repetitiv. Pasul 4. Se stabileşte cheia primară a fiecărei noi relaţii create la Pasul 2. Aceasta va fi

compusă din cheia introdusă la Pasul 3 (cheia primară iniţială) precum şi din unul sau mai multe atribute proprii relaţiei.

Exemplu.

Pentru gestionarea cărţilor dintr-o bibliotecă se consideră relaţia: CARTE [ cota, nume_autori, titlul, editura, an_apariţie, ISBN, cuvinte_cheie]

Cota Nume autori Titlul Editura An

apar.

ISBN Cuvinte-cheie

C104 Ionescu M Popescu F Georgescu L

Baze de date

Economica 2009 978-973-8204-41-7

proiectarea bazelor de date, SQL Server 2008, Oracle

C289 Marinescu A Sisteme informatice

Polirom 2007 978-973-1978-89-5

analiza SI, proiectarea SI, implementarea SI, auditul SI

În relaţia CARTE (nenormalizată) există două grupuri de atribute repetitive: nume_autori şi cuvinte_cheie care crează mari greutăţi în stocarea informaţiilor şi realizarea interogărilor. Alegem drept cheie primară atributul COTA. Aplicarea Algoritmului 1NF conduce la următoarele relaţii: CARTE 1 [ cota, titlul, editura, an_apariţie, ISBN] AUTORI [ cota, nume autori] CUVINTE CHEIE [ cota, cuvinte cheie]

54

CARTE 1 Cota Titlul Editura An

aparitie

ISBN

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

C104 Ionescu M C104 Popescu F C104 Georgescu L C289 Marinescu A CUVINTE CHEIE Cota Cuvinte cheie

C104 proiectarea bazelor de date C104 SQL Server 2008 C104 Oracle C289 analiza SI C289 proiectarea SI C289 implementarea SI C289 auditul SI 4. A DOUA FORMĂ NORMALĂ (2NF)

Definiţia 4.7. O relaţie este în a doua formă normală notată (2NF), dacă relaţia este în (1NF) şi oricare dintre atributele care nu aparţin cheii primare este complet dependent funcţional de cheie. Observaţie. O relaţie în 2NF nu conţine dependenţe funcţionale parţiale între atributele cheie şi celelate atribute. Exemplu. Redundanţe care apar în cazul unei relaţii 1NF, care nu este 2NF. Fie R [A, B, C, D] în care cheia primară este (A, B) şi se manifestă dependenţele: (A, B) → C (A, B) → D B → C

A B C D

a1 b1 c1 d1 a2 b1 c1 d2 a3 b2 c3 d2 a4 b2 c3 d3

55

Teorema 2 ( de descompunere fără pierdere de informaţie)

Fie R [A1, A2, ..., An] o relaţie în 1NF şi K = {A1, A2, ..., Ap} cu p < n este o cheie

primară. Presupunem că există { } ∅=∩=∈ K,A,...,A,AA n21 ββ , adică β este un

atribut noncheie şi α → β cu K⊂α ( β este complet dependent funcţional de o submulţime strictă de atribute din cheie). Atunci dependenţa α → β se poate elimina descompunând relaţia R în următoarele două relaţii: [ ] ( )RR1 βαβα ∪∏=∪

[ ] ( )RAR A2 ββ −∏=−

Observaţie. Conform teoremei de mai sus, relaţia R [A, B, C, D] din exemplul precedent, în care se manifestă dependenţa parţială (A, B) → C se descompune fără pierdere de informaţie în: R1 [ B, C] şi R2 [A, B, D] Algoritmul 2NF - de aducere a unei relaţii 1NF în 2NF

(eliminarea dependenţelor funcţionale parţiale)

Pasul 1. Pentru fiecare dependenţă funcţională argument dfp se crează o nouă relaţie, cu schema constituită din determinantul şi determinatul acestei dependenţe. Dacă există mai multe dependenţe funcţionale argument dfp cu acelaşi determinant se va crea o singură relaţie formată din determinant luat o singură dată şi determinaţii dependenţelor considerate.

Pasul 2. Din relaţia iniţială se elimină atributul / atributele care formează determinatul dependenţelor funcţionale argument dfp.

Pasul 3. Se stabileşte cheia primară a fiecărei noi relaţii create la Pasul 1. Aceasta va fi formată din determinantul dependenţei funcţionale argument dfp.

Aplicaţie.

Pentru evidenţa autoturismelor închiriate de o firmă clienţilor se consideră relaţia: 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.2009 C145 Lungu M Libertatii 14, Predeal BV 19 XIX Ford Focus 17.04.2009 C679 Tudor A Armoniei 23, Iasi CJ 12 XII Audi A6 23.05.2009 C089 Stan D Sadoveanu 45, Cluj CT 07 VII Opel Astra 07.04.2009 C445 Bondescu I Caragiale 66, Arad BV 61 LXI VW Golf 26.04.2009

56

Cheia relaţiei este (nr_client, nr_auto), iar dependeţele funcţionale 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

Observaţie.

- relaţia AUTO este în forma normală 1; - dependenţa funcţională (1) este parţială deoarece se manifestă şi d.f.argument dfp (5); - dependenţa funcţională (2) este parţială deoarece se manifestă şi d.f.argument dfp (6); - dependenţa funcţională (3) este parţială deoarece se manifestă şi d.f.argument dfp (7). Aplicarea Algoritmului 2NF - de aducere a unei relaţii 1NF în 2NF bazat pe Teorema 2, conduce la spargerea relaţiei AUTO, în trei relaţii în 2NF: EVIDENTA [ nr_client, nr_auto, data ]

CLIENT [ nr_client, nume_client, adresa ] AUTO [ nr_auto, marca ] EVIDENŢA Nr client Nr auto Data

C234 BV 21 XXI 22.05.2009 C145 BV 19 XIX 17.04.2009 C679 CJ 12 XII 23.05.2009 C089 CT 07 VII 07.04.2009 C445 BV 61 LXI 26.04.2009 CLIENT Nr client Nume client Adresa

C234 Smith A Castelului 12, Brasov C145 Lungu M Libertatii 14, Predeal C679 Tudor A Armoniei 23, Iasi C089 Stan D Sadoveanu 45, Cluj C445 Bondescu I Caragiale 66, Arad AUTO

Nr auto Marca

BV 21 XXI Logan 1.4 BV 19 XIX Ford Focus CJ 12 XII Audi A6 CT 07 VII Opel Astra BV 61 LXI VW Golf

57

5. A TREIA FORMĂ NORMALĂ (3NF)

Definiţia 4.8. O relaţie este în a treia formă normală notată (3NF), dacă relaţia este în (2NF) şi oricare dintre atributele care nu aparţin cheii primare nu depinde tranzitiv de cheie. Observaţie. O altă exprimare: orice atribut ce nu aparţine cheii primare depinde direct de cheie. Fie R o relaţie, K cheia primară şi presupunem că β este un atribut ce depinde tranzitiv de cheie. Aceasta înseamnă că există un atribut α, astfel încât există dependenţele funcţionale: K → α şi α → β Deoarece relaţia R este în 2NF rezultă că β este complet dependent funcţional de cheia relaţiei şi deci ∅=∩αK , adică α este un atribut noncheie.

Exemplu. Redundanţe care apar în cazul unei relaţii 2NF, care nu este 3NF. Fie R [A, B, C] în care cheia primară este A şi se manifestă dependenţele: A → B A → C B → C

A B C a1 b1 c1 a2 b1 c1 a3 b2 c2 a4 b2 c2

Teorema 3 - Casey – Delobel (de descompunere fără pierdere de informaţie)

Fie R [A1, A2, ..., An] o relaţie în 2NF şi K este o cheie primară. Dacă există atributul { } ∅=∩=∈ K,A,...,A,AA n21 ββ , şi α → β cu K∉α (β depinde tranzitiv de cheie),

atunci dependenţa α → β se poate elimina descompunând relaţia R în următoarele două relaţii: [ ] ( )RR1 βαβα ∪∏=∪

[ ] ( )RAR A2 ββ −∏=−

Observaţie. Conform teoremei de mai sus, relaţia R [A, B, C] din exemplul precedent, în care se manifestă dependenţa tranzitivă B → C se descompune fără pierdere de informaţie în: R1 [B, C] şi R2 [ A, B]

58

Algoritmul 3NF - de aducere a unei relaţii 2NF în 3NF

(eliminarea dependenţelor funcţionale tranzitive)

Pasul 1. Pentru fiecare dependenţă funcţională tranzitivă din cadrul relaţiei considerate, se crează o nouă relaţie, formată din atributele implicate în această dependenţă.

Pasul 2. Se stabileşte cheia primară a fiecărei noi relaţii create la Pasul 1.

Pasul 3. Se introduc în relaţia iniţială în locul atributelor transferate la Pasul 1, cheile primare detrminate la Pasul 2.

Aplicaţie.

Pentru evidenţa rezultatelor examenului de licenţă, se consideră relaţia: 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 MKTS 5678 Popescu V MK 9.30 Teodorescu N MKTS 7890 Georgescu D CIG 9.70 Oancea C FBC 4567 Constantinescu FB 9.60 Cristea D FBC 3456 Marinescu H CIG 9.20 Andreescu M MNIE Cheia relaţiei este nr_matricol, iar dependeţele funcţionale 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

Observaţie.

- relaţia EXAMEN este în 2NF, deoarece toate valorile atributelor sunt atomice, nu avem atribute repetitive (1NF) şi nu există dependenţe funcţionale parţiale (2NF). - dependenţele (4) şi (6) arată ca atributul catedra depinde tranzitiv de cheia primară a relaţiei. Aplicarea Algoritmului 3NF - de aducere a unei relaţii 2NF în 3NF bazat pe Teorema 3, conduce la spargerea relaţiei EXAMEN, în două relaţii în 3NF:

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

59

REZULTAT Nr matricol Nume student Program Nota Prof coord

2345 Ionescu M ECTS 9.45 Zamfir R 5678 Popescu V MK 9.30 Teodorescu N 7890 Georgescu D CIG 9.70 Oancea C 4567 Constantinescu FB 9.60 Cristea D 3456 Marinescu H CIG 9.20 Andreescu M PROFESOR

Prof coord Catedra

Zamfir R MKTS Teodorescu N MKTS Oancea C FBC Cristea D FBC Andreescu M MNIE

Rezumat.

1NF toate atributele sunt atomice şi nu există atribute repetitive 2NF 1NF + orice atribut noncheie este complet dependent funcţional de cheie

(nu există dependenţe funcţionale parţiale) 3NF 2NF + atributele care nu aparţin cheii nu depind tranzitiv de cheie

(nu există dependenţe funcţionale tranzitive / nu există dependenţe funcţionale între atributele noncheie)

60

CAP. 5. APLICAŢII

5.1. FIRMA DE COMERCIALIZARE PRODUSE ELECTRONICE

Relaţii cu furnizorii şi clienţii unei firme de comercializare produse electronice sunt gestionate de urmăroarele şase relaţii (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 următoarele precizări: - pe o factură emisă pot exista mai multe produse; informaţiile despre aceste produse le

găsim în relaţia (tabela) DETALII_FACTURA; - sub fiecare schemă a relaţiei este indicat numele prescurtat al relaţiei cu care se va

opera în procedurile de interogare. Cheile primare pentru relaţiile (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 independent unul de altul şi este posibil să primim două facturi cu acelaşi număr factură de la furnizori diferiţi;

- 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 interogările formulate se vor prezenta rezolvările utilizând operaţiile algebrei

relaţionale şi limbajul SQL din Microsoft Access. Pentru o parte din interogări se va da şi rezolvarea cu ajutorul construcţiei arborelui de interogare.

61

P1. Cum se numesc furnizorii din Braşov ?

Lista: |cod_furnizor | nume_furnizor | CIF |

Algebră relaţională R1 = SELECT (FURNIZ; localitate = “Brasov”) R2 = PROJECT (R1; cod_furnizor, nume_furnizor, CIF) Access

SELECT cod_furnizor, nume_furnizor, CIF FROM furniz WHERE localitate="Brasov";

Arborele de interogare

P2. Lista cu numerele şi valorile facturilor primite, ce au fost întocmite după 1.03.2009 şi au o valoare mai mare de 500 lei? Algebră relaţională R1 = SELECT (FACTP; (data_factura > 1.03.2009) and (valoare>500)) R2 = PROJECT (R1; nr_factura, valoare) Access

SELECT nr_factura, valoare FROM factp WHERE data_factura>#3/1/2009# AND valoare>500;

cod_furnizor, nume_furnizor, CIF

FURNIZ

localitate=”Brasov”

R1

R2

62

P3. Care sunt localităţile în care firma îşi are partenerii de afaceri? Câţi clienţi există în fiecare localitate?

Algebră relaţională R1 = PROJECT (FURNIZ; localitate) R2 = PROJECT (CLI; localitate) R3 = UNION (R1, R2) Q1 = COUNT (CLI; localitate) Q2 = PROJECT (Q1; localitate, count)

Access

SELECT distinct localitate FROM furniz UNION (SELECT localitate FROM cli);

SELECT count(*), localitate FROM cli GROUP BY localitate;

Dacă dorim să vedem câţi parteneri comerciali avem in fiecare localitate vom avea: SELECT Count(*) AS nr, total.localitate FROM [SELECT cod_furnizor AS cod_partener, localitate FROM furniz UNION (SELECT cod_client AS cod_partener, localitate FROM cli)]. AS total GROUP BY total.localitate;

P4. Lista facturilor primite în acest an. Lista: |nr_factura | data_factura | cod_furnizor | nume_furnizor |

Algebră relaţională R1 = 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) Access SELECT nr_factura, data_factura, factp.cod_furnizor, nume_furnizor FROM furniz, factp WHERE factp.cod_furnizor=furniz.cod_furnizor AND data_factura>#1/1/2009#; Sau: SELECT nr_factura, data_factura, factp.cod_furnizor, nume_furnizor FROM furniz, factp WHERE factp.cod_furnizor=furniz.cod_furnizor AND

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

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ă relaţională 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) Access SELECT nr_factura, factp.cod_furnizor, nume_furnizor FROM factp, furniz WHERE furniz.cod_furnizor=factp.cod_furnizor AND data_factura=#3/4/2009#; Sau: SELECT nr_factura, factp.cod_furnizor, nume_furnizor FROM factp INNER JOIN furniz ON furniz.cod_furnizor=factp.cod_furnizor WHERE data_factura=#3/4/2009#;

P6. În ce localităţi se găsesc clienţii care au cumpărat produsul ‘XYZ’? Algebră relaţională R1 = 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) Access SELECT DISTINCT localitate FROM cli, prod, facte, factd WHERE facte.cod_client=cli.cod_client

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

64

Arborele de interogare

P7. În ce localităţi s-a vândut produsul ‘XYZ’ în perioada 15.04.2009 – 30.04.2009 ?

Algebră relaţională R1 = SELECT (PROD; denumire = ‘XYZ’) R2 = NATURAL JOIN (R1, FACTD; cod_produs) R3 = SELECT (FACTE; (data_factura>14.04.2009) and (data_factura<1.05.2009)) R4 = NATURAL JOIN (R2, R3; nr_factura) R5 = NATURAL JOIN (R4, CLI; cod_client) R6 = PROJECT (R5; localitate) Access SELECT DISTINCT localitate FROM cli, prod, facte, factd WHERE facte.cod_client=cli.cod_client

AND facte.nr_factura=factd.nr_factura

PROD

denumire = “XYZ”

R1

cod_produs

FACTD

nr factura

FACT

cod client

CLI

R2

R3

localitate

R5

R4

65

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

Sau: SELECT DISTINCT localitate FROM cli, prod, facte, factd WHERE facte.cod_client=cli.cod_client

AND facte.nr_factura=factd.nr_factura AND factd.cod_produs=prod.cod_produs AND prod.denumire="xyz" AND (data_factura>=#4/15/2009# AND data_factura<=#4/30/2009#);

P8. În care din facturile emise se înregistrează vânzarea concomitentă a tuturor produselor firmei ?

Algebră relaţională R1 = PROJECT (FACD; nr_factura, cod_produs) R2 = PROJECT (PROD; cod_produs) R3 = DIVISION (R1, R2) Access SELECT count(*) AS nr_produse, nr_factura FROM factd GROUP BY nr_factura HAVING count(*)=(SELECT count(*) FROM prod);

P9. În care din facturile emise după 1 ianuarie 2009 se înregistrează vânzarea concomitentă a tuturor produselor firmei ?

Algebră relaţională R1 = NATURAL JOIN (FACTE, FACTD; nr_factura) R2 = SELECT (R1; data_factura>1.01.2009) R3 = PROJECT (R2; nr_factura, cod_produs) R4 = PROJECT (PROD; cod_produs) R5 = DIVISION (R3, R4)

Access

SELECT nr_factura, data_factura FROM facte WHERE data_factura>=#01-01-2009# AND nr_factura IN

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

66

P10. Care sunt numerele facturilor emise, în care s-a consemnat vânzarea simultană a produselor ‘XYZ’ şi ‘ABC’ ?

Algebră relaţională R1 = 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 2 R1 = PROJECT (FACTD; nr_factura, cod_produs) R2 = SELECT (PROD; (denumire = ‘XYZ’) or (denumire = ‘ABC’)) R3 = PROJECT (R2; cod_produs) R4 = DIVISION (R1, R3) Access SELECT DISTINCT facte.nr_factura FROM cli, prod, facte, factd WHERE facte.cod_client=cli.cod_client

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

Sau: SELECT DISTINCT facte.nr_factura FROM cli, prod, facte, factd WHERE facte.cod_client=cli.cod_client

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

Sau: SELECT DISTINCT facte.nr_factura FROM cli, facte, factd,

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

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

67

P11. Care este valoarea totală a facturilor emise în luna mai 2009 pentru fiecare client? Lista: |cod_client | nume_client | valoare totala | Algebră relaţională R1 = 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) Access SELECT tmp.total, tmp.cod_client, nume_client FROM cli,

[SELECT sum(valoare) AS total, facte.cod_client FROM facte WHERE year(data_factura)=2009 AND month(data_factura)=5 GROUP BY facte.cod_client]. AS tmp

WHERE tmp.cod_client=cli.cod_client; Arborele de interogare

FACTE

01.05.09≤data factura≤31.05.09

R1

SUM valoare/cod client CLI

R2

cod client

R3

cod client, nume client, sum

R4

68

P12. Care este valoarea totală a facturilor emise în luna mai 2009 pentru fiecare client persoană fizică? Lista: |cod_client | nume_client | valoare totala | Algebră relaţională R1 = SELECT (FACTE; (data_factura ≥ 01.05.2009) and (data_factura ≤ 31.05.2009)) R2 = SUM (R1, valoare; cod_client) R3 = SELECT (CLI; tip_client = ‘F’) R4 = NATURAL JOIN (R2, R3; cod_client) R5 = PROJECT (R4; cod_client, nume_client, sum) Access SELECT tmp.total, tmp.cod_client, nume_client FROM cli,

[SELECT sum(valoare) AS total, cod_client FROM facte WHERE year(data_factura)=2009 and month(data_factura)=5 and cod_client IN (SELECT cod_client FROM cli WHERE tip_client="f") GROUP BY cod_client]. AS tmp

WHERE tmp.cod_client=cli.cod_client; P13. Care sunt valorile totale TVA colectat şi TVA deductibil în luna mai 2009? Algebră relaţională R1 = SELECT (FACTE; (data_factura ≥ 01.05.2009) and (data_factura ≤ 31.05.2009)) R2 = SUM (R1, tva_colectat) Q1 = SELECT (FACTP; (data_factura ≥ 01.05.2009) and (data_factura ≤ 31.05.2009)) Q2 = SUM (Q1, tva_deductibil) Access (SELECT sum(factp.valoare*19/100) AS tva FROM factp) UNION (SELECT sum(facte.valoare*19/100) AS tva FROM facte); P14. Care este valoarea maximă a facturilor emise în luna mai 2009 pentru fiecare client persoană juridică? Lista: |cod_client | nume_client | valoare maximă factură | Algebră relaţională R1 = SELECT (FACTE; (data_factura ≥ 01.05.2009) and (data_factura ≤ 31.05.2009)) R2 = MAX (R1, valoare; cod_client) R3 = SELECT (CLI; tip_client = ‘J’) R4 = NATURAL JOIN (R2, R3; cod_client) R5 = PROJECT (R4; cod_client, nume_client, max)

69

Access SELECT tmp.total, tmp.cod_client, nume_client FROM cli,

[SELECT MAX(valoare) AS total, cod_client FROM facte WHERE cod_client IN (SELECT cod_client FROM cli WHERE tip_client="j") AND YEAR(data_factura)=2009 AND MONTH(data_factura)=5 GROUP BY cod_client]. AS tmp

WHERE tmp.cod_client=cli.cod_client;

70

BIBLIOGRAFIE

1. AIRINEI D., Depozite de date, Editura Polirom, Iaşi, 2002. 2. BÂSCĂ O., Baze de date, Editura ALL, Bucureşti, 1997. 3. CONNOLLY Th., ş.a., Baze de date. Proiectare, Implementare, Gestionare,

Editura Teora, Bucureşti, 2001. 4. CONNOLLY Th., ş.a., Database Systems, Addison-Wesley, 2002. 5. DATE C.J., Baze de date, Editura Plus, Bucureşti, 2005. 6. DOLLINGER R., Baze de date şi gestiunea tranzacţiilor, 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ă, Bucureşti, 1999. 10. FOTACHE M., Baze de date relaţionale, Editura Junimea, Iaşi, 1997. 11. FOTACHE M., SQL, Editura Polirom, Iaşi, 2001. 12. FOTACHE M., Proiectarea bazelor de date, Editura Polirom, 2005. 13. Grupul BDASEIG, Baze de date. Fundamente teoretice şi practice,

Editura Infomega, Bucureşti, 2002. 14. HERNANDEZ M., Proiectarea bazelor de date, Ed. Teora, 2003. 15. HORGA M., ş.a., Limbajul SQL în Oracle şi Visual FoxPro,

Editura Bibliotheca, Târgovişte, 2007. 16. IONESCU F., Baze de date relaţionale şi aplicaţii, Editura Tehnică,

Bucureşti, 2004. 17. LUNGU I., ş.a., Baze de date. Organizare, proiectare şi implementare,

Editura ALL Educational, Bucureşti, 1995. 18. LUNGU I., ş.a., Baze de date. Sistemul ORACLE, Ed. Economică,

Bucureşti, 2002. 19. LUNGU I., Baze de date ORACLE. Limbajul SQL, Editura ASE,

Bucureşti, 2005. 20. MEIER A., Introduction pratique aux bases de données relationnelles,

Ed. Springer France, 2006 21. MIRANDA S.M., BUSTA J.M., L’art des bases de données, Ed. Eyrolles,

Paris, 1988. 22. PASCU C., ş.a., Totul despre SQL, Editura Tehnică, Bucureşti, 1994. 23. POPA GHE., ş.a., Sisteme de gestiune a bazelor de date, Editura Cison,

Bucureşti, 1996. 24. POPESCU I., Baze de date relaţionale, Editura. Universităţii din Bucureşti,

Bucureşti, 1996. 25. POPESCU I., ORACLE 8. Prelucrarea avansată a informaţiei, Ed. Tehnică,

Bucureşti,1999. 26. POPESCU I., Modelarea bazelor de date, Editura Tehnică, Bucureşti, 2001. 27. POPESCU I., ş.a., Programare avansată în ORACLE 9i, Editura Tehnică,

Bucureşti, 2004.

71

28. REBOUL G., Informatique de gestion. Analyse et modèle relationnel, Ed. Dunod, Paris, 1997.

29. STANCIU A., ş.a., Baze de date. Introducere în SQL SERVER 2008, Editura Infomega, Bucureşti, 2008.

30. STANCIU A., ş.a., Baze de date Access 2007, Editura Infomega, Bucureşti, 2009. 31. ŞTEFAN V., Tehnologii orientate obiect pentru baze de date relaţionale,

Editura Infomega, Bucureşti, 2006. 32. ŢÎMBULEA L., Structuri de date şi baze de date, Universitatea Babeş-Bolyai

Cluj Napoca, 1992. 33. VELICANU M., ş.a., Sisteme de gestiune a bazelor de date, Editura Petrion,

Bucureşti, 2000. 34. VELICANU M., ş.a., Sisteme de baze de date. Teorie şi practică, Editura Petrion,

Bucureşti, 2003.

Baze de Date- Indrumar

Documents

Transcript of Baze de Date- Indrumar