baze de date
-
Upload
adrian-petcu -
Category
Documents
-
view
71 -
download
0
description
Transcript of baze de date
-
Proictarea Bazelor de DateProictarea Bazelor de Date
Curs
Specializarea Ingineria Informaiei - anul IV-A
Facultatea de Electronica, Telecomunicatii si Tehnologia Informatiei
Prof. Felicia IonescuProf. Felicia Ionescu
http://info.tech.pub.ro/~fionescu/
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 2
BibliografieBibliografie
Felicia Ionescu, Baze de date relationale si aplicatii, Editura Tehnica, Bucureti, 2004
C. J. Date, An Introduction to Database Systems, 8th Edition, 2004 R. Elmasri and S. B. Navathe, Fundamentals of Database Systems,
Fourth Edition, 2004
J. Ullman, J. Windom, A First Course In Database Systems, Prentice Hall, 1997
Kevin Kline, Daniel Kline, SQL In A Nutshell, OReilly, 2001 Oracle 11g Documentation -
http://www.oracle.com/technetwork/database/enterprise-edition/documentation/index.html
MySQL Documentation - http://dev.mysql.com/doc/ PostgreSQL Documentation - http://www.postgresql.org/docs/manuals/ Microsoft SQL Server Books Online MSDN - Academic Alliance
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 3
Capitolul 1: IntroducereCapitolul 1: Introducere
Definiii baze de date, sisteme de baze de date Componentele sistemelor de baze de date Arhitectura interna a sistemelor de baze de date Avantajele oferite de sistemele de baze de date Clasificari ale sistemelor de baze de date
Clasificare dupa modelul de date
Clasificare dupa numarul de utilizatori
Clasificare dupa numarul de statii pe care este memorata baza de date
Modelarea datelor Modele conceptuale de nivel inalt
Modele specifice de date
Evolutia sistemelor de baze de date
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 4
Sisteme de baze de date Sisteme de baze de date
Bazele de date se folosesc in aproape toate domeniile de activitate actuale: Activitati bancare si comerciale (depozite bancare, vanzari produse)
Productie (gestiunea stocurilor, gestiunea financiar-contabila, salarizare etc.)
Evidenta populatiei, taxe si impozite
Servicii (servicii medicale, rezervari bilete de calatorie etc.)
Definitie (in sens larg): O baza de date (database) este o colecie de date corelate din punct de vedere logic, care reflecta un anumit aspect al lumii reale i este destinata unui anumit grup de utilizatori. In acest sens pot fi considerate ca fiind baze de date: Fise de evidenta (mentinute manual)
Fisiere de documente sau foi de calcul tabelar (Microsoft Word, Microsoft Excel)
Baze de date mentinute computerizat
Definitie (in sens restrans): O baz de date este o colecie de date creat i meninut computerizat, care permite operaii de: Introducere (insert)
Stergere (delete)
Actualizare (update)
Interogare (query)
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 5
Componentele unui Sistem de Baze de Date (1)Componentele unui Sistem de Baze de Date (1)
Un sistem de baze de date (Database System) este un sistem computerizat de meninere a evidenei unei anumite activiti, folosind baze de date
Componentele unui sistem de baze de date sunt: hardware, software, utilizatori si date persistente
Hardware: Sistemele de baze de date sunt instalate pe calculatoare de uz general
Bazele de date sunt memorate fizic ca fisiere pe discuri magnetice (hard-discuri)
Software: Sisteme de operare, biblioteci, instrumente de dezvoltare, interfete
Sistemul de Gestiune a Bazelor de Date (SGBD) (Database Management System DBMS) - recepioneaz cererile utilizatorilor de acces la baza de date, le interpreteaz, execut operaiile corespunztoare i returneaz rezultatul
Aplicatii de baze de date: (Database Applications) sunt programe care ofer anumite utilizari ale unei baze de date
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 6
Componentele unui Sistem de Baze de Date (2)Componentele unui Sistem de Baze de Date (2)
Utilizatori: Programatori de aplicatii
Utilizatori finali
Administratorul bazei de date
Analisti si proiectanti ai bazelor de date
Datele persistente sunt memorate in fisiere pe hard-disk Limbaje conceptuale pentru lucrul cu bazele de date:
Limbaje pentru Definirea Datelor(LDD) (Data Definition Languages DDL)
Limbaje pentru Manipularea Datelor (LMD) (Data Manipulation Languages DML)
SGBD
Baza de date
Utilizatorfinal
Program aplicaie
Date
Date Date
Date
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 7
Arhitectura interna a unuiArhitectura interna a unui Sistem de BDSistem de BD Arhitectura pe 3 niveluri relativ independente: nivelul intern, nivelul
conceptual i nivelul extern (Standard ANSI/X3/SPARC -1975)
Schema descrierea datelor pe un anumit nivel: schema interna, conceptuala si scheme externe (vedere utilizator)
Corespondente intre niveluri (mappings)
Schema intern
Schema conceptual
Vedere utilizator #1
Vedere utilizator #2
Vedere utilizator #nNivelul extern
SGBD
Nivelul conceptual
Nivelul intern
Datememorate
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 8
Avantaje oferite de Sistemele de BDAvantaje oferite de Sistemele de BD
Compactitate ridicat a datelor Reprezentarea unor asocieri complexe intre date Timp de dezvoltare a bazelor de date redus Viteza mare de actualizare si regasire a datelor Redundanta controlata a datelor (si cat mai scazuta) Flexibilitate, mentinerea datelor actualizate la zi Independenta datelor fata de suportul hardware utilizat Securitatea datelor: autentificarea utilizatorilor si autorizarea accesului Impunerea de restrictii (constrangeri) de integritate la introducerea si
actualizarea datelor
Mentinerea integritatii datelor in caz de defecte: salvare si refacere Posibilitatea de partajare a datelor intre mai multe categorii de utilizatori Posibilitatea de introducere a standardelor
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 9
Clasificarea Sistemelor de Baze de Date (1)Clasificarea Sistemelor de Baze de Date (1)
Clasificare dupa modelul de date: Modelul ierarhic de date
Modelul de date retea
Modelul relational
Modelul obiect-orientat
Modelul obiect-relational
Clasificare dupa numarul de utilizatori Sisteme mono-utilizator
Sisteme multi-utilizator
Clasificare dupa numarul de statii pe care este memorata baza de date: Baze de date centralizate
Baze de date distribuite
Arhitectura client-server: Server (back-end): SGBD-ul si baza de date
Client (front-end): program (programe) de aplicatie
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 10
Clasificarea Sistemelor de Baze de Date (2)Clasificarea Sistemelor de Baze de Date (2)
Sisteme de baze de date centralizate: a- mono-utilizator; b- multi-utilizator
AplicaieClient
SGBD
BD
a
Server
Reeade comunicaie
AplicaieClient
AplicaieClient
AplicaieClient
SGBD
BD
Server
b
Reeade comunicaie
AplicaieClient
AplicaieClient
AplicaieClient
SGBD
BD
Server
SGBD
BD
Server
Sistem de baze de date distribuit
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 11
Modelarea datelorModelarea datelor Un model este o abstractizare a unui sistem:
capteaz cele mai importante trsturi caracteristice ale sistemului (concepte)
conceptele trebuie sa fie relevante din punct de vedere al scopului pentru care se definete modelul respectiv
Tehnica de identificare a trsturilor caracteristice eseniale ale unui sistem se numete abstractizare
Un model de date stabilete regulile de organizare i interpretare a unei colecii de date.
n proiectarea bazelor de date se folosesc 2 categorii de modele: Modele conceptuale de nivel nalt (modelul Entitate-Asociere, modelul
Entitate-Asociere Extins) descriu concis colectiile de date care modeleaz activitatea dorit fr s detalieze modul de reprezentare sau de prelucrare a datelor - schem conceptual de nivel nalt
Modele specifice (modelul ierarhic, modelul reea, modelul relaional, etc.) -descriu reprezentarea mulimilor de entiti i a asocierilor dintre acestea prin structuri de date specifice implement[rii - schem conceptual (logic)
Trecerea de la modelul conceptual de nivel nalt la un model de date specific proiectare logic a bazei de date.
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 12
Modelul EntitateModelul Entitate--AsociereAsociere Modelul Entitate-Asociere (Entity-Relationship Model) defineste multimile
de entiti i asocierile dintre ele, dar nu impune nici un mod specific de structurare i prelucrare (gestiune) a datelor; Introdus n 1976 de P.S. Chen
O entitate (entity) este orice exista in realitatea obiectiva si poate fi identificat n mod distinctiv Exemple: o persoana, o planta, o activitate, un concept etc.
Un atribut (attribute) este o proprietate care descrie un anumit aspect al unei entiti Exemple: persoanele au nume, prenume, adresa etc.
Tip de entitate (entity type): se refera la entittile similare, care pot fi descrise prin aceleasi atribute Exemple: tipul persoana, tipul planta
Multime de entitati (entities set): colecia tuturor entitilor de acelai tip dintr-o baz de date constituie o mulime de entiti Exemple: multimea tuturor persoanelor, multimea tuturor plantelor
O entitate este o instanta a unui tip de entitate si un element al multimii de entitati de acel tip
In exprimarea curenta, adeseori nu se face diferentierea dintre entitate, tip de entitate si multime de entitati, dar diferenta este evidenta
Asemanare cu modelul obiect: tip de entitate - clasa; entitate - obiect
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 13
AsocieriAsocieri O asociere (relationship) este o legtur (coresponden) ntre entiti din
dou sau mai multe mulimi de entiti; asocierile pot avea atribute Tipul asocierii (relationship type) se refera la asocierile similare, care pot
fi definite intre entitati din 2 sau mai multe multimi de entitati Multime de asocieri (relationship set): multimea asocierilor de acelasi tip O asociere este o instanta a unui tip de asociere si un element al multimii
de asocieri de acel tip
In exprimarea curenta, adeseori nu se face diferentierea dintre asociere, tip de asociere si multime de asocieri, dar diferenta este evidenta
Gradul unui (tip de) asociere (degree): numrul de (mulimi de) entiti asociate; dupa grad, asocierile pot fi: binare (de gradul 2, ntre 2 mulimi de entiti) majoritatea asocierilor multiple (ntre k mulimi de entiti, k > 2)
Categorii (tipuri) de asocieri binare - dup numrul elementelor din fiecare dintre cele dou mulimi puse n coresponden: unul-la-unul (one-to-one) 1:1; exemplu: sot-sotie unul-la-multe (one-to-many) 1:N; exemplu: parinte-fii multe-la-unul (many-to-one) N:1; exemplu: fii-parinte multe-la-multe (many-to-many) M:N; exemplu: profesori-studenti
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 14
Categorii de asocieri binare (1)Categorii de asocieri binare (1)
Asocieri binare intre multimile de entitati A si B
unul-la-unul 1:1 unul-la-multe- 1:N
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 15
Categorii de asocieri binare (2)Categorii de asocieri binare (2)
Asocieri binare intre multimile de entitati A si B
multe-la-multe- M:Nmulte-la-unul- N:1
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 16
Cardinalitatea asocierilorCardinalitatea asocierilor
Cardinalitatea (multiplicitatea) unei asocieri fa de o mulime de entiti(cardinality, multiplicity) este numrul maxim de elemente din acea mulime care pot fi asociate cu un element din alt mulime a asocierii Exemplu: asocierea unul-la-multe dintre mulimile A i B prezint multiplicitatea
1 fa de mulimea A i multiplicitatea N (se nelege o valoare oarecare N > 1) fa de mulimea B
Raport de cardinalitate (cardinality ratio): raportul dintre valorile cardinalitilor unei asocieri fa de dou din mulimile de entiti asociate Exemple pentru asocieri binare: 1:1, 1:N, N:1, M:N
Asocierile multiple (k-are, k > 2) prezint cte un raport de cardinalitate pentru fiecare pereche de mulimi de entiti pe care le asociaz.
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 17
Diagrama EntitateDiagrama Entitate--Asociere Asociere Diagrama Entitate-Asociere (Entity-Relationship Diagram) reprezint
grafic modelul Entitate-Asociere prin mulimile de entiti i asocierile dintre acestea
Multimi (tipuri) de entitati: Puternice (de sine statatoare)
Slabe (depind de alte multimi de entitati)
Notatii:
A B Asociere binar 1:Nntre 2 tipuri de entiti1 N
Tip entitate Tip de entitate puternic
Tip entitate Tip de entitate slab
Nume atribut Atribut
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 18
Exemplu de diagrama EntitateExemplu de diagrama Entitate--Asociere (1)Asociere (1) Multimi de entitati puternice:
SECTII (Numar, Nume, Buget)
ANGAJATI (Nume, Prenume, DataNasterii, Adresa, Functie, Salariu)
PROIECTE (Denumire, DataInceperii, Termen, Buget)
Multimi de entitati slabe: DEPENDENTI (Nume, Prenume, DataNasterii, GradRud)
ANGAJATISECTII1 N
DEPENDENTI
1
N
PROIECTE
M
N
LucreazaIntretin
Numr Buget Nume
Denumire BugetGradRudenie
Salariu
Cuprind
Nume
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 19
Exemplu de diagrama EntitateExemplu de diagrama Entitate--Asociere (2)Asociere (2) Asocieri:
Asocierea SECTII - ANGAJATI - 1:N Asocierea ANGAJATI - PROIECTE - M:N Asocierea ANGAJATI - DEPENDENTI - 1:N
Raportul de cardinalitate al unei asocieri este stabilit de proiectant astfel nct s reflecte ct mai corect modul de organizare a activitii modelate
Modul de stabilire a tipurilor de entiti i a asocierilor nu este unic: aceeai funcionalitate se poate obine printr-o varietate de diagrame E-A
O mulime de entiti se denumeste printr-un substantiv, iar o asociere se denumeste (de regul) printr-un verb, deoarece o asociere reprezint o interaciune ntre entiti
Modelul E-A nu precizeaz modul cum sunt realizate asocierile ntre mulimile de entiti: acest aspect depinde de modelul de date specializat utilizat pentru definirea bazei de date
Exemple: n modelul ierarhic asocierile sunt realizate explicit, prin pointeri de la o entitate la entitile asociate; n modelul relaional asocierile se realizeaz prin egalitatea valorilor unor atribute comune ale multimilor de entiti (chei)
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 20
Modelul EntitateModelul Entitate--Asociere ExtinsAsociere Extins Modelul Entitate-Asociere Extins (Enhanced Entity-Relationship Model)
permite definirea de subtipuri ale unui tip de entiti, care motenesc atribute de la tipul de entitate respectiv
Crearea ierarhiilor: specializare si generalizare Tipurile i a subtipurile formeaza ierarhii de tipuri de entiti complexe,
organizate pe mai multe niveluri
Diagrama Entitate-Asociere Extinsa
ANGAJAT
TEHNICIANSECRETARA
Nume Prenume Salariu
VitezaRedactare Calificare
DataNasterii Adresa
INGINER
Specialitate
d
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 21
Modelul de date ierarhicModelul de date ierarhic
Modelul ierarhic (Hierarchical Model): baza de date se reprezinta printr-o structur ierarhic de nregistrri (records) conectate prin legturi (links). A fost primul model folosit pentru dezvoltarea bazelor de date Cel mai cunoscut SGBD ierarhic: sistemul IMS (Information Management
System) dezvoltat de IBM n programul de cercetri Apollo, n perioada anilor 1960
O nregistrare de date n modelul ierarhic este o instan a unui tip de nregistrare (record type) i const dintr-o colecie de cmpuri (fields), fiecare cmp coninnd valoarea unui atribut.
Un tip de legtur n modelul ierarhic: tip de asociere cu raportul de cardinalitate 1:N (printe-fiu) ntre dou tipuri de nregistrri
Schema conceptual a unei baze de date n modelul ierarhic se reprezint printr-un numr oarecare de scheme ierarhice
O schem ierarhic este un arbore direcionat, reprezentat pe mai multe niveluri, n care nodurile sunt tipuri de nregistrri, iar arcele sunt tipuri de legturi
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 22
Baze de date ierarhiceBaze de date ierarhice
Numai legturi de tipul printe-fiu, care corespund asocierilor 1:1 i 1:N din modelul E-A
Asocierile M:N se pot reprezenta prin multiplicarea nregistrrilor de tip fiu, atunci cnd sunt referite de mai multe nregistrri de tip printe mare redundan a datelor
Avantaje: simplitatea i eficiena de calcul Deficiente:
nu exista separare intre descrierea logica si fizica a datelor interogarile trebuie s fie prevzute explicit in structura datelor
Utilizari actuale - aplicatii specializate, baze de date XML
f1 f2 f3
FACULTATI
p1 p2 p3
PROFESORI
s1 s2 s3
STUDENTI
s1 s2 s4
FACULTATI
PROFESORI
STUDENTI
FACULTATI
PROFESORI
STUDENTI
1
N
M
N
(a) Diagrama E-A (b) Schema ierarhica (c) Arbori de instantiare a datelor
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 23
Modelul de date reteaModelul de date retea Modelul reea (Network Model) folosete o structur de graf pentru
definirea schemei conceptuale a bazei de date
Modelele ierarhic si retea modele pre-relationale Standardizat n 1971, de o comisie DBTG (Database Task Group). Sisteme de gestiune comerciale in modelul retea: IDS II (Honeywell),
UNISYS (Burroughs), IDMS (Computer Associates)
Nodurile grafului sunt tipuri de entiti (nregistrri - records), iar muchiile reprezint asocierile (legturile-links) dintre tipurile de entiti
Asocierile M:N se reprezint fr duplicarea nregistrrilor, fiecare nregistrare putnd fi referit de mai multe nregistrri, ceea ce elimin (micoreaz) redundana
Dezavantaje: aceleasi ca si la modelul ierarhic, la care se adauga complexitatea mare in reprezentarea datelor
Actualmente modelul retea: este rar utilizat pentru baze de date de uz general se utilizeaza pentru aplicaii specializate
de ex, pentru baze de date grafice (scene virtuale)
f1 f2 f3
FACULTATI
p1 p2 p3
PROFESORI
s1 s2 s3
STUDENTI
s4
Modelul retea
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 24
Modelul de date relationalModelul de date relational Modelul relaional (Relational Model) se bazeaz pe noiunea de relaie
(relation) din matematic, care corespunde unei mulimi de entiti
Fundamentat de E.F. Codd (IBM), prin lucrarea "Un Model Relaional de Date pentru Bnci Mari de Date Partajate" (1970)
Dezvoltare extraordinara a sistemelor de gestiune a bazelor de date relationale, datorit simplitii i a fundamentrii matematice a modelului
Alte lucrri ale cercetatorilor C.J. Date, P. Chen, R. Boyce, J.D. Ullman, R. Fagin, W. Armstrong, M. Stonebraker etc. au perfecionat modelul relaional
Primul Sistem de Gestiune a Bazelor de Date Relaionale (SGBDR) a fost prototipul System R (IBM, 1970)
Dup aceasta numeroase companii au realizat sisteme de gestiune relaionale: Oracle, Microsoft, Ingres, Sybase, IBM, Informix
SGBDR folosesc limbajul SQL (Structured Query Language), pentru care au fost emise mai multe standarde ANSI (American National Standardization Institute) si ISO (International Standardization Office)
Majoritatea SGBD-urilor relaionale actuale implementeaz versiunea SQL2 (SQL92) sau versiuni ulterioare (SQL-1999, SQL-2003, SQL-2006)
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 25
Modelul obiectModelul obiect--orientatorientat Modelul obiect (Object Model) este un concept unificator Necesar in domenii n care se manipuleaz date de tipuri complexe:
proiectarea sistemelor de calcul: programare, hardware, interfete proiectarea asistat de calculator (CAD-CAM) sisteme de informaii geografice fizic, biologie, medicin i altele
Strategii pentru dezvoltarea sistemelor de gestiune a bazelor de date obiect-orientate (SGBDOO): Extinderea unui limbaj de programare obiect-orientat cu capaciti de
administrare a obiectelor persistente: sistemul GemStone (extinde Java si C++)
Extinderea unui limbaj de programare relaional cu capaciti de orientare spre obiecte. Exemplu: limbajul OQL (Object Query Language) (sau Object SQL), Exist mai multe astfel de sisteme, cum sunt: Ontos, Versant, O2.
Dezvoltarea unui limbaj obiect-orientat pentru baze de date complet nou: SIM (Semantic Information Manager).
Dificultati: Complexitate in dezvoltare a bazei de date i a aplicaiilor Interogarile trebuie s fie prevzute explicit in structura datelor
Utilizare SGBDOO: cam 5% din sistemele de gestiune actuale
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 26
Modelul obiectModelul obiect--relationalrelational
Modelul obiect-relaional (Object-Relational Model) reprezint extinderea modelului relaional cu caracteristici ale modelului obiect
Modelul obiect-relaional pstreaz structurarea datelor n relaii, si, in plus: permite definirea unor noi tipuri de date, ca domenii ale atributelor permite extinderea tipurilor de date prin motenire
Sistemele de gestiune a bazelor de date obiect-relaionale (SGBDOR) se realizeaz prin extinderea sistemelor relaionale, de regula n mod gradat, adugndu-se de la o versiune la alta ct mai multe caracteristici posibile ale modelului obiect
Aceasta abordare asigur rularea n continuare a aplicaiilor relaionale existente n noile versiuni de sisteme SGBDOR, ceea ce permite productorilor s-i pstreze clienii i domeniile de utilizare
Limbajele de programare pentru SGBDOR sunt implementri de standarde mai recente ale limbajului SQL: SQL3 (SQL-1999), SQL-2003, SQL-2006
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 27
Complexitatea datelor si a interogarilorComplexitatea datelor si a interogarilor
Clasificare propusa de M. Stonebraker (1996)
SGBDR SGBDOR
Sisteme de fiiere SGBDOO
Complexitatea datelor
Complexitatea interogrilor
SGBDR prelucreaz tipuri simple de date, dar permit interogri complexe SGBDOO prelucreaz tipuri de date complexe, dar n care rezolvarea
interogrilor este destul de dificil
SGBDOR permit prelucrarea datelor complexe i rezolvarea interogrilor complexe; sistemele de baze de date obiect-relaionale sunt considerate sisteme de baze de date universale
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 28
Evolutia sistemelor de baze de dateEvolutia sistemelor de baze de date1960 Modele prerelationale: ierarhic si retea
Primele produse de baze de date (DBOM, IMS, IDS, Total, IDMS)Standarde Codasyl
1970 Modelul relational prototipuri de SGBDRLucrari teoretice asupra modelului relationalArhitectura interna pe 3 niveluri a bazelor de date (ANSI and Codasyl)Modelul Entitate-Asociere
1980 Dezvoltarea SGBDR comercialePrimul standard SQL (1986 - ANSI, ISO) Baze de date distribuite
1990 Arhitectura client/server a sistemelor de baze de date (two-tier arch.)Baze de date obiect-orientateBaze de date obiect-relationaleStandarde SQL: SQL 92, SQL 99
2000 Arhitectura pe 3 niveluri a sistemelor de baze de date (three-tier arch.)Baze de date in sistemul WWW (World Wide Web)
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 29
Sisteme de Gestiune a Bazelor de dateSisteme de Gestiune a Bazelor de date
Sisteme ComercialeOracle ($$$$)DB2 (IBM) ($$$)SQL Server (Microsoft) ($$)
Sisteme Open SourcePostgreSQLMySQL
-
Prof. Felicia Ionescu Cap.1 - Introducere in Baze de date 30
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 1
Capitolul 2: Baze de date relaCapitolul 2: Baze de date relaionaleionale
Relaii, atribute, domenii; schema relaiei Reprezentarea relaiilor prin tabele Limbajul SQL:
Convenii lexicale Expresii, operatori, functii Instructiuni de definire a datelor: CREATE, ALTER, DROP Instructiuni de manipulare a datelor: SELECT, INSERT, UPDATE, DELETE
Constrngerile de integritate ale relaiilor Constrngeri de domeniu Constrngeri de tuplu: cheia primar chei secundare Constrngeri de integritate referenial chei strine
Indexarea relaiilor Indexul primar Indexuri secundare
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 2
RelaRelaii ii Atribute Atribute DomeniiDomenii Modelul relaional: E.F.Codd, 1970 IBM O baz de date relaional este compus dintr-o mulime finit de relaii
fiecare relaie reprezinta o mulime (tip) de entitati sau o mulime (tip) de asocieri fiecare relaie este unica intr-o baza de date o relaie se defineste prin intermediul atributelor sale
Atributele unei relaii corespund atributelor tipului de entitate sau de asociere pe care l reprezint relaia respectiv fiecare atribut are un nume (Ai) i un domeniu de definiie D(Ai) pentru o entitate data, un atribut poate lua o singur valoare (scalar)
Atributele pot fi: simple (un element) sau compuse (o submulime de atribute) Domeniu: o mulime de valori D = {di | i = 1,, n }, definit printr-o specificare
de tip, unde: D este numele domeniului di este un element al domeniului care satisface anumite constrngeri Elementele domeniilor sunt atomice (indivizibile) O valoare speciala, null, poate apartine oricarui domeniu (inseamna lipsa de
informatie sau valoare necunoscuta)
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 3
Schema relaSchema relaieiiei Schema relaiei: descriere a unei relaii (tipul, intensiunea relaiei) Schema relaiei: R(A1,A2,...Ai,...An), unde:
R este numele schemei relaiei lista ordonat a atributelor sale A1,A2,...Ai,..An fiecare atribut Ai definit pe domeniul su de definiie, D(Ai) Gradul relaiei: numrul de atribute ale schemei acelei relaii (n) Exemplu: STUDENTI (Nume, Prenume, DataNasterii, Adresa, Facultatea)
O relaie r definita prin schema R(A1,A2,...Ai,...An) este: o mulime finita de n-tupluri t tuplul t este o list ordonat de n valori: t = , unde 1 i n vi este o valoare a atributului Ai, vi D(Ai)
Relaia r(R): r este variabila, instanta a schemei (tipului) R Valoarea variabilei: starea sau extensiunea relaiei Numarul de tupluri ale unei relaii: cardinalitatea relaiei Fiecare tuplu este unic intr-o relaie (nu exista tupluri duplicat) Corespondenta: relaiemulime de entitati (sau de asocieri); tuplu entitate
n mod curent: se foloseste R atat pentru schema cat i pentru relaia insasi
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 4
Reprezentarea relaReprezentarea relaiilor prin tabeleiilor prin tabele Un tabel (table) = reprezentarea grafic a unei relaii; compus din:
Numele tabelului - identic cu numele relaiei Coloanele corespund atributelor relaiei Capul tabelului- contine numele atributelor (coloanelor) schema relaiei O mulime de linii, fiecare linie corespunznd unui tuplu starea relaiei Valori ale atributelor fiecarui tuplu
Exemplu: Tabelul care reprezinta relaia (starea relaiei) STUDENTI
Nume Prenume DataNasterii Adresa Facultatea
Anghelescu Octavian 1999 Bucuresti ETTI
Beldiman Cristina 1998 Bucuresti ETTI
Boeru Marius 1999 null ETTI
Numele Coloane - Atribute
STUDENTI
Linii - tupluri
Tabelul sugereaz ordonarea atributelor (coloanelor) i a tuplurilor (liniilor) ceea ce nu corespunde modelului matematic (relaie = mulime de tupluri)
Valori atribute
Capul tabelului
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 5
AfiAfiarea tabelelorarea tabelelor SGBD-urile ofer instrumnente de proiectare i afisare a tabelelor
De exemplu, afiarea tabelului Employees din baza de date Northwind folosind toolset-ul SQL Query Analyser din Microsoft SQL Server
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 6
Ordonarea valorilor atributelor Ordonarea valorilor atributelor n tuplurin tupluri
Din punct de vedere logic, ordinea valorilor atributelor ntr-un tuplu nu conteaza; aceast structurare poate fi exprimat prin urmtoarele definiii:
Schema relaiei: R = {A1,A2, ...Ai,...An} (o mulime de atribute) Relaia r(R): o mulime de n-tupluri t, unde:
fiecare tuplu t este o mulime de n perechi ordonate , unde 1 i n, t = {,,..., ...} vi este valoarea atributului Ai, vi D(Ai)
Observaii asupra celor dou definiii: A doua definiie a relaiei este mult mai general decat prima definitie Prima definiie simplific notaiile i corespunde reprezentrii prin tabel a relaiei
i de aceea va fi folosit n continuare destul de frecvent
n implementrile reale, exist o anumit ordine a valorilor atributelor memorate n fiiere, dar aceasta nu este relevant din punct de vedere logic
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 7
Limbajul SQLLimbajul SQL Limbajul IBM Sequel dezvoltat ca parte a proiectului System R la IBM San
Jose Research Laboratory (1970)
Redenumit Structured Query Language (SQL) Standarde SQL - ANSI i ISO:Anul Denumire Caracteristici
1986 SQL-86 Publicat de ANSI (SQL1); ratificat de ISO n 1987
1989 SQL-89 Revizii minore
1992 SQL-92 Revizii majore, redenumit SQL2
1999 SQL-1999 Redenumit SQL3, adauga unele caracteristici obiect-relaionale
2003 SQL-2003 Adauga unele trasaturi referitoare la limbajul XML
2006 SQL-2006 Utilizare SQL n conjunctie cu XML
Fiecare SGBDR implementeaz un dialect al limbajului SQL, ceea ce micoreaz gradul de portabilitate a aplicaiilor
n diferitele implementri ale limbajului SQL pot s lipseasc unele comenzi prevzute n standard, dar pot exista extensii specifice SGBD-ului respectiv
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 8
Caracteristicile generale ale limbajului SQLCaracteristicile generale ale limbajului SQL
Limbajul SQL foloseste reprezentarea prin tabele a relaiilor, reprezentare care este mai simpl i mai intuitiv (foloseste termenii tabel, linie, coloan)
Limbajul SQL cuprinde: Componenta de descriere a datelor (Limbaj de Descriere a Datelor LDD) Componenta de manipulare a datelor (Limbaj de Manipulare a Datelor LMD) Alte componente: controlul tranzactiilor, controlul securitatii, protectia datelor etc.
Limbajul SQL2 este un limbaj neprocedural: o instruciune SQL2 specific ce informaii trebuie s fie setate sau obinute, nu
modul (procedura) n care se opereaz
limbajul SQL2 nu conine instruciuni de control al fluxului execuiei (instruciuni ca for, while, if, etc)
Standardul SQL3 prevede instructiuni de control i crearea de tipuri definite de utilizator, fiind implementat n SGBD-urile obiect-relaionale
Pentru aplicaiile de baze de date, s-au dezvoltat extensii procedurale ale limbajului SQL, biblioteci i interfee de programare care integreaz instruciunile SQL
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 9
Structura lexicala a limbajului SQLStructura lexicala a limbajului SQL
O instruciune SQL (statement) este o secven de elemente - de regula terminat cu semnul punct i virgul (;)
Fiecare instruciune SQL conine o comand SQL (command), care specific ce aciune se efectueaz, urmat de alte elemente, care specific operaii, clauze, parametri etc. Exemplu: SELECT * FROM ANGAJATI;
Elementele (tokens) instruciunilor SQL cuvnte cheie (key words): CREATE, INSERT, SELECT , WHERE, FROM etc. identificatori (identifiers):
simpli - numai caractere alfa-numerice i underscore(_): ANGAJATI, Nume, Prenume etc. delimitati (quoted) - pot contine orice caracter, foloseste ghilimele : Nume, Prenume etc.
constante (literali): 1000, 100.5, Ionescu, NULL caractere speciale: *, ., ;
Spaiile albe (whitespaces) separa elementele: spaiu, linie nou, tab O instructiune se poate scrie pe una sau mai multe linii, iar ntr-o linie se pot
introduce una sau mai multe instructiuni Limbajul SQL este case-insensitive (nu deosebeste literele mici de cele mari)
cu exceptia identificatorilor delimitati (quoted) care sunt case-sensitive
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 10
Expresii Expresii i operatori i operatori n limbajul SQLn limbajul SQL O expresie SQL const dintr-unul sau mai muli operanzi, operatori i
paranteze Parantezele se pot folosi pentru a preciza o anumit ordine a operaiilor, dac
aceasta este diferit de ordinea implicit data de precedenta operatorilor.
Un operand poate fi: numele unei coloane n acest caz se foloseste valoarea memorata n acea
colona intr-una sau mai multe linii ale tabelului
o constant (literal) valoarea returnat de o functie
Un operator SQL este exprimat prin: unul sau mai mai multe caractere speciale; exemple: +, -, *, /, %,
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 11
Operatori SQLOperatori SQL
A B A and B A or B A not A
true true true true true
false
null
true false false true
false
true
nulltrue null null true
false false false false
false null false null
null null null null
Operatorii de comparaie returneaz valori logice: true (1), dac condiia este ndeplinit false (0) dac condiia nu este ndeplinit null dac ambii operanzi au valoarea null
Operatorii logici (NOT, AND, OR): se aplic unor valori logice trivalente (cu 3 valori: true (1), false (0) i null - lipsa de
informatie)
returneaz o valoare logic trivalent
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 12
FuncFuncii SQLii SQL Funcii SQL: funcii agregat i funcii scalare. Funciile agregat calculeaz un rezultat din mai multe linii ale unui tabel
Aceste funcii vor fi detaliate ulterior, la descrierea instruciunii SELECT Funciile scalare:
Primesc unul sau mai multe argumente i returneaz valoarea calculat sau NULL n caz de eroare
Argumentele funciilor pot fi constante (literale) sau valori ale atributelor specificate prin numele coloanelor corespunzatoare
Tipuri de funcii scalare SQL: Funcii de calcul trigonometric (sin, cos, tan etc.), funcii de calcul al logaritmului
(ln, log), al puterii (power), funcii de rotunjire (floor, ceil), etc.
Funcii pentru manipularea irurilor de caractere: concat, replace, upper etc. Funcii pentru data calendaristic i timp: add_months, next_day, last_day etc. Funcii de conversie: to_number, to_char etc.
Funciile scalare se folosesc n expresii, care pot s apar n diferite clauze ale instruciunilor SQL
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 13
Tipuri de date SQL (1)Tipuri de date SQL (1)
Tipuri de date SQL2: numeric, iruri de caractere, iruri de bii, data (calendaristic), timp
Tipul numeric: numere ntregi: integer sau int (4 octei), smallint (2 octei) numere reale reprezentate n virgul flotanta: float (4 octei), real i double
[precision] (8 octei)
numere zecimale reprezentate cu precizia dorit (tipul numeric sau decimal, memorate ca ir de caractere): numeric[(p,s)] (sau decimal [(p,s)]), unde p (precizia) este numrul total de cifre, iar s (scara) este numrul de cifre dup punctul zecimal
Siruri de caractere: character(n), prescurtat, char(n) - ir de caractere de lungime fix (n) character varying(n), prescurtat varchar(n) - ir de caractere de lungime variabil,
maximum n
Siruri de bii - secvene de cifre binare (care pot lua valoarea 0 sau 1): bit(n)) - sir de biti de lungime fix (n) bit varying(n) sir de biti lungime variabil, maxim n
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 14
Tipuri de date SQL (2)Tipuri de date SQL (2) Tipurile SQL pentru data calendaristic i timp sunt: date, time, timestamp,
interval: Tipul date: memorarea datelor calendaristice prin utilizarea a trei cmpuri (year,
month, day), n formatul yyyy-mm-dd; se admit numai date valide
Tipul time: memorarea timpului, folosind trei cmpuri (hour, minute, second) n formatul HH:MM:SS; se admit numai valori valide
Tipul timestamp(p): memorarea combinat a datei calendaristice i a timpului, cu precizia p pentru cmpul second. Valoarea implicit a lui p este 6
Tipul interval este utilizat pentru memorarea intervalelor de timp Variante de tipuri de date SQL specifice n diferite sisteme SGBD; Exemple:
SGBD Microsoft SQL Server: tinyint - numr ntreg pe 1 octet SGBD Oracle: varchar2 - ir de caractere de lungime variabil
Standardul SQL2 nu suport tipuri de date i operaii definite de utilizator Standardul SQL3 suport tipuri de date i operaii definite de utilizator, care
sunt caracteristice ale modelului de date obiect-relaional
Actualmente, productorii de sisteme de baze de date relaionale introduc treptat diferite caracteristici ale modelului obiect-relaional cuprinse n SQL3
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 15
Domenii SQLDomenii SQL
n SQL2 domeniile atributelor se specific pe baza tipurilor de date predefinite ale limbajului SQL, deci nu corespund ntru totul noiunii de domeniu relaional
Standardul SQL2 prevede comanda CREATE DOMAIN, care definete un domeniu pe baza unui tip predefinit SQL2 i cu unele constrngeri
Standardul SQL3 prevede comanda CREATE TYPE care creaz tipuri definite de utilizator (user-defined types)
n SGBD-urile actuale sunt implementate diferite versiuni din standarde: n SQL Server se pot crea domenii ale atributelor cu comanda SQL CREATE
DOMAIN
n Oracle (8i, 9i, 10g,11g) se pot crea tipuri de date noi, folosind comanda CREATE TYPE, care permite gruparea sub un anumit nume a mai multor atribute i operatii
n PostgreSQL de asemenea se pot crea tipuri de date noi, folosind comanda CREATE TYPE
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 16
Conventii de notatieConventii de notatie Pentru prezentarea limbajului SQL i a altor limbaje, biblioteci i interfete
[ ] (paranteze drepte) Element opional al instruciunii
{ } (acolade) Element obligatoriu al instruciunii
| (bar vertical) Separ elementele din parantezele drepte sau acolade; numai unul dintre acestea se poate introduce n instruciunea respectiv
[ , . . . n] Elementul precedent poate fi repetat de n ori; elementele repetate sunt separate prin virgul
element1, . . . . . . .elementn
List de n elemente de acelai tip; elementele repetate sunt separate prin virgul
lista_elemente List de elemente de acelai tip separate prin virgul
Caracterele folosite pentru a specifica o anumit convenie sintactic (paranteze, bara vertical, virgula, etc.) nu apar n instruciunile propriu-zise
Listele de elemente (compuse din mai multe elemente separate prin virgul) vor fi exprimate folosind una cele trei din construciile de mai sus, care se potrivete cel mai bine instruciunii respective
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 17
InstrucInstruciuni SQL iuni SQL Componenta de definire a datelor din SQL (LDD - Limbajul de Definire a Datelor):
Crearea (CREATE), modificarea (ALTER) i distrugerea (DROP) obiectelor bazei de date
Obiectele bazei de date sunt: tabele de baz (TABLE), tabele vedere (VIEW), indexuri (INDEX), proceduri (PROCEDURE), trigere (TRIGGER), utilizatori (USER)
Exemple de comenzi SQL de definire a datelor:CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE USER
CREATE FUNCTION, CREATE TRIGGER, CREATE PROCEDURE
ALTER TABLE, ALTER VIEW, ALTER FUNCTION, ALTER PROCEDURE
DROP TABLE, DROP VIEW, DROP INDEX, DROP USER
DROP FUNCTION, DROP PROCEDURE, DROP TRIGGER
Componenta de manipulare a datelor din limbajul SQL (Limbajul de Manipulare a Datelor - LMD) conine comenzile: SELECT, INSERT, UPDATE i DELETE
Instructiunile SQL se transmit SGBD-ului: de ctre diferite programe client (client grafic, linie de comanda, program executabil) SGBD-ul compileaz i execut instructiunea SQL returneaz un rspuns (rezultatul operaiei sau un cod de eroare)
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 18
Crearea tabelelor Crearea tabelelor
Instruciunea CREATE TABLE are urmtoarea sintax:CREATE TABLE nume_tabel (
col1 domeniu1 [constrngeri_coloana],col2 domeniu2 [constrngeri_coloana],. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . coln domeniun [constrngeri_coloana],
[constrngeri_tabel] );
Constrngerile impuse fiecrei coloane (atribut), ca i constrngerile de tabel, sunt opionale i vor fi discutate n sectiunea urmtoare. Exemplu:
CREATE TABLE ANGAJATI (Nume varchar(20),Prenume varchar(20),DataNasterii date,Adresa varchar(50),Functia varchar(20),Salariu numeric);
Instruciunea CREATE TABLE definete att tipul relaiei ct i o variabil relaie de acel tip care iniial este vid (nu conine nici un tuplu)
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 19
Crearea vederilorCrearea vederilor
Tabelele create cu instruciunea CREATE TABLE: se numesc i tabele de baz (base tables) ele sunt memorate n fiierele bazei de date i pot fi accesate pentru introducerea,
modificarea i regsirea (interogarea) datelor
Un tabel vedere (view) este un tabel virtual care: nu este memorat fizic n fiiere reprezint o selecie (dup un anumit criteriu) a datelor memorate n unul sau mai
multe tabele de baz
Un tabel vedere se creeaza cu instruciunea SQL: CREATE VIEW nume_vedere AS (SELECT...);
Formatul comenzii SELECT va fi descris n capitolul urmtor Datele (valorile atributelor) sunt memorate o singur dat, n tabelele de baz,
dar pot fi accesate att prin tabelele de baz ct i prin tabelele vederi
Un tabel vedere este ntotdeauna actualizat ("la zi"), adic orice modificare efectuat n tabelele de baz se regsete imediat n orice tabel vedere creat pe baza acestora
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 20
Modificarea Modificarea i stergerea tabelelor i stergerea tabelelor i a vederilori a vederilor
Comanda de modificare a tabelelor (ALTER TABLE) permite: adugarea sau tergerea unor atribute modificarea domeniilor unor atribute adugarea, modificarea sau tergerea unor constrngeri ale tabelului
Pentru adugare unei coloane ntr-un tabel se folosete clauza ADD, urmata de numele coloanei i numele domeniului (tipul SQL) atributului corespunztor. Exemplu:
ALTER TABLE ANGAJATI ADD DataAngajarii date;
Pentru tergerea unei coloane dintr-un tabel se folosete clauza DROP, urmata de numele coloanei care se va sterge. Exemplu:
ALTER TABLE ANGAJATI DROP DataAngajarii;
Instruciunile de tergere a tabelelor de baz i a vederilor sunt: DROP TABLE nume_tabel;
DROP VIEW nume_vedere;
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 21
InstrucInstruciunea SELECTiunea SELECT SELECT - instruciune de interogare, prin care se regsesc informaiile din
unul sau mai multe tabele ale bazei de date dupa un criteriu (conditie) dat
Sintaxa general:SELECT [DISTINCT] lista_coloane
[FROM lista_tabele]
[WHERE conditie]
[clauze_secundare];
SELECT returneaza un tabel cu coloanele din lista_coloane ale acelor linii (tupluri) ale produsului cartezian al tabelelor din lista_tabele
pentru care expresia logic conditie este adevrat (are valoarea TRUE).
Instructiunea SELECT are urmatoarele seciuni (clauze): Clauza SELECT definete lista de coloane a tabelului rezultat Clauza FROM indic lista de tabele din care se selecteaz rezultatul Clauza WHERE definete condiia pe care trebuie s o ndeplineasc fiecare
linie a tabelului rezultat
Clauze secundare (ORDER BY, GROUP BY, HAVING): permit ordonri sau grupri ale tuplurilor (liniilor) rezultate
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 22
Clauza SELECTClauza SELECT Clauza SELECT specific:
lista coloanelor unor tabele (date n lista_tabele) expresii care vor fi calculate i afiate
Exemple:SELECT ID, Name, CountryCode, District from city;
SELECT 3*4, cos(45), floor(12.45);
Eliminarea liniilor duplicat cu parametrul DISTINCT. Exemplu:SELECT [DISTINCT] CountryCode FROM city;
Selectarea tuturor coloanelor produsului cartezian al tabelelor date - cu caracterul * ca i lista_coloane. Exemplu:
SELECT * FROM city;
n clauza SELECT se pot redenumi tabelele i coloanele tabelelor sau se pot specifica nume pentru expresii, folosind urmtoarea sintax:
SELECT nume1 [AS] noul_nume1 [,...n] FROM lista_tabele [alte_clauze];
SELECT ID, Name Oras, CountryCode Cod Tara FROM city;
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 23
Clauzele FROM Clauzele FROM i WHEREi WHERE
Clauza FROM specific lista_tabele din care se selecteaz rezultatul Numele coloanelor din lista_coloane (clauza SELECT) trebuie s fie distincte Dac nu sunt distincte, se calific unele coloane cu numele tabelului caruia i
aparin - folosind operatorul punct (.). De exemplu:SELECT ANGAJATI.Nume, SECTII.Nume FROM ANGAJATI, SECTII;
Clauza WHERE specifica conditia pe care trebuie sa o ndeplinesca rezultatul: conditia este o expresie logic compusa din valori logice, operatori logici (NOT, AND,
OR) i paranteze
o valoare logic se obtine ca rezultat al comparaiei ntre doi operanzi folosind un operator de comparaie
un operand poate fi un atribut (nume de coloan), o constant, valoarea unei expresii aritmetice sau o valoare returnat de o funcie
operatorii de comparaie pot fi operatori aritmetici sau operatori SQL de comparaie Exemple:
SELECT * FROM city WHERE Population > 1000;
SELECT * FROM city WHERE (Population > 200000) AND (CountryCode=ROM);
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 24
Clauze secundareClauze secundare funcfuncii agregatii agregat Clauzele secundare sunt: ORDER BY, GROUP BY, HAVING Clauza ORDER BY specific numele atributului dup care se face ordonarea
liniilor tabelului rezultat SELECT * FROM city order by CountryCode;
Ordonarea n ordine cresctoare: parametrul ASC (implicit); n ordine descrescatoare: DESC. Exemplu:
SELECT * FROM city order by CountryCode DESC;
Clauzele GROUP BY i HAVING se folosesc mpreun cu funciile agregat Funciile agregat definite n limbajul SQL2 sunt urmtoarele:
Functia Valoarea returnata
COUNT Numarul de linii al tabelului rezultat
SUM Suma valorilor din coloana dat ca argument
MAX Valoarea maxima din coloana dat ca argument
MIN Valoarea minima din coloana dat ca argument
AVG Valoarea medie din coloana dat ca argument
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 25
FuncFuncii agregatii agregat
Exemple de funcii agregat fr clauze secundare: SELECT COUNT(*) FROM city; -- returneaza numarul de linii din tabel
SELECT COUNT(col) FROM city; -- return nr de valori dif de null din acea col.
SELECT MAX(Population) FROM city;
SELECT MIN(Population) FROM city;
SELECT AVG(Population) FROM city;
Clauza GROUP BY se folosete pentru gruparea rezultatelor funciilor agregat dupa valoarea uneia sau mai multor coloane. Exemplu:
SELECT CountryCode, AVG(Population) FROM city GROUP BY CountryCode;
Clauza HAVING nlocuiete clauza WHERE atunci cnd n condiia care trebuie s fie ndeplinit se folosesc funcii agregat. Exemplu:
SELECT CountryCode, AVG(Population) FROM city GROUP BY CountryCode HAVING AVG(Population) >800000;
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 26
InstrucInstruciunea INSERTiunea INSERT Instruciunea INSERT se folosete pentru introducerea datelor n tabele i are
urmtoarea sintax:INSERT INTO nume_tabel (col1,col2,...coln) VALUES(val1,val2,...valn);
ntre valori i numele de coloane trebuie s existe o coresponden pozitional. De exemplu:
INSERT INTO SECTII (Numar, Nume, Buget) VALUES (1,Productie, 40000);
Lista de coloane poate s lipseasc dac se introduc valori n toate coloanele tabelului i n aceast situatie: ordinea valorilor introduse trebuie s respecte ordinea coloanelor tabelului ordinea coloanelor provine din ordinea de definire a atributelor prin instruciunea
CREATE TABLE, precum i din operaiile ulterioare de alterare a tabelului ordinea coloanelor se poate afla prin instruciunea DESCRIBE nume_tabel.
De exemplu, introducerea unei linii cu toate valorile n tabelul ANGAJATI (IdAngajat,Nume,Prenume,DataNasterii,Adresa,Functia,Salariu)
se poate face cu instruciunea:INSERT INTO ANGAJATI
VALUES(100,Mihailescu, Mihai,1950-04-05,Craiova,Inginer, 3000);
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 27
InstrucInstruciunile UPDATE iunile UPDATE i DELETEi DELETE
Instruciunea UPDATE permite actualizarea valorilor coloanelor (atributelor) din una sau mai multe linii ale unui tabel i are sintaxa:
UPDATE nume_tabel SET col1 = expr1 [, . . . n] [WHERE conditie];
Clauza WHERE: actualizarea valorilor se efectueaza numai asupra acelor linii care ndeplinesc condiia dat. Exemplu:
UPDATE ANGAJATI SET Adresa = Bucuresti WHERE Nume = Popescu;
Dac este omis clauza WHERE, vor fi modificate valorile coloanelor din toate liniile tabelului.
Instruciunea DELETE permite tergerea uneia sau mai multor linii dintr-un tabel i are sintaxa:
DELETE FROM nume_tabel [WHERE conditie];
Din tabel se terg acele linii care ndeplinesc condiia dat n clauza WHERE. Dac este omis clauza WHERE, vor fi sterse toate liniile din tabel.
Exemplu: DELETE FROM ANGAJATI WHERE Nume =Ionescu;
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 28
Constrngeri de integritate (1)Constrngeri de integritate (1) Constrngerile de integritate (integrity constraints) sunt reguli care se impun
pentru ca datele memorate s corespund ct mai bine celor din realitate: se definesc la proiectarea bazei de date trebuie s fie respectate de orice stare a acesteia
Clasificare dup locul unde se definesc: constrngeri de coloana i constrngeri de tabel
Clasificare dup numrul de relaii implicate: constrngeri intra-relaie i constrngeri inter-relaii.
Constrngerile intra-relaie - reguli care se impun n cadrul unei singure relaii; sunt de trei categorii: Constrngeri de domeniu - condiii ce se impun valorilor domeniilor atributelor Constrngeri de tuplu - condiii ce se impun tuplurilor unei relaii prin chei (primare
sau secundare)
Constrngeri impuse prin dependene de date (dependene funcionale, multivalorice sau de jonciune); acestea sunt constrngeri intre valorile atributelor dintr-o relaie
Constrngerile inter-relaii - reguli care se impun ntre dou sau mai multe relaii; asigura integritarea referenial prin intermediul cheilor strine
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 29
Constrngeri de integritate (2)Constrngeri de integritate (2) Clasificare din punct de vedere al modului de definire i de verificare a
respectrii constrngerilor: inerente, implicite i explicite.
Constrngerile inerente sunt cele ale modelului de date nsui, care nu trebuie s fie definite deoarece sunt incluse n sistemul de gestiune De exemplu: n modelul relaional constrngerea ca valoarea fiecrui atribut s
fie atomic (indivizibil) este o constrngere inerent
Constrngerile implicite sunt reguli specifice fiecrui sistem de gestiune; acestea se definesc de ctre proiectant, iar sistemul de gestiune le verific i le impune automat Exemple: connstrngerile de domeniu, constrngerile de tuplu i constrngerile
de integritate referenial sunt constrngeri implicite.
Constrngerile explicite sunt constrngeri suplimentare, specifice bazei de date respective; proiectantul definete constrngerile explicite precum i procedurile de verificare ale accestora (funcii, proceduri stocate, triggere) Exemple: dependenele de date care nu sunt determinate de cheile relaiilor
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 30
Constrngeri de domeniu (1)Constrngeri de domeniu (1) Constrngerile de domeniu: constrngerea NOT NULL, constrngerea de
valoare implicit (DEFAULT), constrngerea de verificare (CHECK) Constrngerea NOT NULL nsemna c atributul respectiv nu poate lua
valoarea NULL n nici un tuplu al relaiei. Valoarea NULL a unui atribut ntr-un tuplu semnific faptul c valoarea acelui
atribut nu este cunoscut pentru acel tuplu. Exemple: nu se cunoaste deloc data de nastere a unei personalitati istorice; nu se cunoate valoarea unui atribut n momentul inserarii tuplului, dar aceasta va fi
cunoscuta i completat ulterior
La crearea unui tabel opiunea NULL este implicit (nu se specific nimic), sau se poate introduce explicit; optiunea NOT NULL se introduce explicit.
Optiunile NULL i NOT NULL se introduc ca i constrngeri de coloana n instructiunea SQL CREATE TABLE. Exemplu:
CREATE TABLE ANGAJATI (Nume varchar(20) NOT NULL,Prenume varchar(20) NOT NULL,DataNasterii date NULL,Functie varchar(20),Salariu numeric);
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 31
Constrngeri de domeniu (2)Constrngeri de domeniu (2) Constrangerea de valoare implicit a unui atribut (DEFAULT): dac la
inserarea unui tuplu nu se specific valoarea unui atribut, atunci: atributul primete valoarea implicit (DEFAULT), dac a fost definit atributul primete valoarea NULL, dac nu a fost definit valoare implicit, dar sunt
admise valori NULL se genereaz o eroare, dac nu a fost definit o valoare implicit i nici nu sunt
admise valori NULL. Exemplu:CREATE TABLE STUDENTI (
Nume varchar (20) NOT NULL,Prenume varchar (20) NOT NULL,Tara varchar (20) DEFAULT Romania ) ;
Constrngerea de verificare (CHECK) pentru verificarea valorilor atributelor printr-o conditie care trebuie sa ia valoarea TRUE.
Se introduce ca o constrangere de tabel n instructiunea CREATE TABLE:[CONSTRAINT nume_constrangere] CHECK (conditie); Exemplu:CREATE TABLE ANGAJATI (
Nume varchar(20) NOT NULL,Prenume varchar(20) NOT NULL,Salariu numeric,CONSTRAINT Verificare_Salariu CHECK (Salariu >= 1500 ));
MySql 5.0 nu face verificarea CHECK, chiar daca admite acest cuvnt cheie
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 32
Constrngeri de tuplu Constrngeri de tuplu O relaie = mulime de tupluri tuplurile unei relaii trebuie s fie distincte (nu
pot exista dou sau mai multe tupluri identice) Pentru ca tuplurile unei relaii s fie distincte se folosete cte o cheie primar
(primary key) n fiecare relaie O cheie primar PK a unei relaii este un atribut (simplu sau compus) al acelei
relaii care are proprietatea de unicitate, adic fiecare valoare a cheii primare este unic n acea relaie. Aceasta nseamn c: Nu exist dou tupluri distincte (diferite) care s aib aceeai valoare a cheii primare
(sau combinaie de valori) pentru orice stare a relaiei, adic:ti[PK] tj[PK] dac i j, unde ti i tj sunt 2 tupuri diferite ale relaiei
Proprietatea de unicitate a cheii primare este o constrngere de integritate a tuplurilor: fiecare tuplu poate fi identificat n mod precis i se pstreaz integritatea acestuia, dac se cunoate valoarea cheii sale primare
Cheia primar este o constrngere implicit: se definete de proiectant la crearea tabelului i este verificat de SGBD (s nu existe duplicate, etc)
Cheia primar mai are urmtoarele restricii: Este ireductibil: nu exist o submulime proprie nevid a cheii PK care s aib
proprietatea de unicitate Nici o valoare a atributelor cheii primare nu poate fi modificat prin operaii de
actualizare (UPDATE) Nu se admit valori de NULL pentru nici unul dintre atributele cheii primare
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 33
CheCheii primarprimare naturale e naturale i artificiale (1)i artificiale (1)
Se pot defini chei primare naturale sau chei primare artificile, cu condiia ca acestea s ndeplineasc condiia de unicitate
O cheie primar natural este un atribut (simplu sau compus) al relaiei: reprezint o proprietate a tipului de entitate (sau asociere) reprezntat de acea relaie are n mod natural valori unice: nu exist dou tupluri cu aceeai valoare a cheii
primare, deoarece nu exist dou entiti cu acceai valoare a proprietii respective
O cheie primar artificial este un atribut (de obicei simplu) care nu reprezint o proprietate a tipului de entitate sau asociere reprezentat de relaie, ci se adaug n schema relaiei special pentru identificarea unic a tuplurilor
Exemplu:ANGAJATI (IdAngajat, CNP, Nume, Prenume, DataNasterii, Adresa, Functia, Salariu):
IdAngajat este o cheie primar artificial Ar putea fi definite i chei primare naturale prin atribute simple sau compuse care au
proprietatea de unicitate n anumite condiii: atributul simplu {CNP} valabil numai pentru persoanele din Romania atributul compus {Nume, Prenume, DataNasterii, Adresa}
Din motive de eficien a operaiilor de identificare a tuplurilor, se prefer chei primare cu un numr ct mai mic de atribute (atribut simplu)
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 34
CheCheii primarprimare naturale e naturale i artificiale (2)i artificiale (2) Modul de asigurare a unicitii valorii cheii primare artificiale depinde de
sistemul SGBD folosit. De exemplu:
n Microsoft SQL Server se pot obine valori unice ale cheii primare folosind parametrul IDENTITY, care asigur incrementarea valorii atributului cheii la introducerea fiecrei linii noi
n sistemele Oracle se pot genera chei artificiale folosind obiecte SEQUENCE; un obiect SEQUENCE geneaza un numar unic la fiecare apel al metodei NEXTVAL
n MySQL, se foloseste parametrul AUTO_INCREMENT pentru generareanumerelor unice pentru cheile primare.
SGBD-urile interzic introducerea liniilor (tuplurilor) care au valori identice ale cheilor primare
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 35
Definirea cDefinirea cheheiiii primarprimare e
Cheia primar se defineste prin instructiunea CREATE TABLE ca o constrngere de tabel sau ca o constrngere de coloan
Definirea cheii primare ca o constrngere de tabel: [CONSTRAINT nume_constr] PRIMARY KEY (lista_atribute)
Exemplu:CREATE TABLE SECTII (
IdSectie int,Nume varchar(50) NOT NULL,Buget numeric,CONSTRAINT PK PRIMARY KEY (IdSectie)
);
Dac cheia primar este simpl (format dintr-un singur atribut), ea se poate specifica i ca o constrngere de coloan; exemplu:
CREATE TABLE ANGAJATI (IdAngajat int PRIMARY KEY AUTO_INCREMENT,Nume varchar(20) NOT NULL,Prenume varchar(20) NOT NULL,DataNasterii Date,Adresa varchar(50),Salariu numeric) ;
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 36
Superchei, chei candidatSuperchei, chei candidatee
O supercheie (superkey) este o submulime SK de atribute ale unei relaii care prezint proprietatea de unicitate (orice combinaie de valori ale atributelor supercheii este unic pentru orice stare a relaiei) Dac se cunoate valoarea (combinaia de valori ale atributelor) supercheii, atunci
acel tuplu poate fi identificat n mod unic
Orice relaie are cel puin o supercheie, care este mulimea tuturor atributelor sale O cheie candidat (candidate key) este o supercheie ireductibil:
Unicitate: nu exist dou tupluri diferite ale relaiei care s conin aceeai combinaie de valori ale atributelor cheii CK;
Ireductibilitate: nu exist nici o submulime proprie, nevid a cheii CK care s aib proprietatea de unicitate
O cheie candidat poate fi simpl (un atribut), sau compus (mai multe atribute) Exemplu: ANGAJATI (IdAngajat, CNP, Nume, Prenume, DataNasterii, Adresa,
Functia, Salariu)
SK1 = {IdAngajat, CNP, Nume, Prenume, DataNasterii, Adresa, Functia, Salariu}
SK2 = {CNP, Nume, Prenume, DataNasterii, Adresa}; SK3 = {IdAngajat, CNP}
CK1= {Nume, Prenume, DataNasterii, Adresa}; CK2 = {CNP}; CK3 ={IdAngajat}
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 37
Chei candidate, primare Chei candidate, primare i i secundare secundare Atunci cnd exist mai multe chei candidate (cu proprietile de unicitate i
ireductibilitate), una dintre ele se alege ca i cheie primar, iar celelalte chei se pot defini ca i chei secundare
Cheia primar este o cheie candidat aleas (desemnat) de proiectant la definirea tabelului
O cheie secundar (alternativ, unic) (secondary, alternate, unique key) este o cheie candidat definit de proiectant Cheile secundare se definesc n instruciunea CREATE TABLE folosind specificatorul
UNIQUE [KEY] n loc de PRIMARY KEY
Alegerea cheii primare dintre mai multe chei candidate este arbitrar, dar, din motive de eficien, se alege cheia cu cel mai mic numr de atribute
Cheile secundare se deosebesc de cele primare prin: Pot fi modificate prin instruciuni UPDATE, dac se respect proprietatea de unicitate Cheile secundare compuse admit valori NULL pentru unele din atributele lor
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 38
ConstrConstrngeri interngeri inter--relarelaiiii Asocierile (relaionships) 1: N ntre dou mulimi de entiti (din modelul
Entitate-Asociere) se realizeaz n modelul relaional prin chei strine
Exemplu: Pentru a realiza asocierea 1: N dintre relaiile SECTII i ANGAJATI, se adaug n relaia ANGAJATI cheia strin IdSectie, care reprezint identificatorul (numrul) seciei n care lucreaz angajatul respectiv:
ANGAJATI (IdAngajat, Nume, Prenume, DataNasterii, Adresa, Salariu, IdSectie)
IdSectie Nume Buget
1 Productie 400000
2 Proiectare 300000
3 Cercetare 200000
4 Documentare 100000
SECTII
IdAngajat Nume Prenume DataNasterii Adresa Functia Salariul IdSectie
1 Ionescu Ion 1960.01.05 Bucuresti inginer
tehnician
secretara
inginer
4000 1
2 Popa Petre 1965.02.97 Bucuresti 3200 1
3 Carol Ana 1961.03.06 Bucuresti 2000 2
4 Marin Radu 1970.03.98 Bucuresti 4000 3
ANGAJATI
ANGAJATISECTIIN1
Diagrama E-A
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 39
Cheia strinCheia strin Fie dou relaii R1 i R2, ntre care exista o asociere cu raportul 1: N.
O cheie strin (foreign key) este o submulime FK de atribute ale relaiei R2 care refer cheia CK din relaia R1 i satisface urmtoarele condiii: atributele cheii strine FK sunt definite pe domenii compatibile cu cele ale atributelor
cheii candidate CK a relaiei R1 valorile atributelor FK ntr-un tuplu din relaia R2, fie sunt identice cu valorile atributelor
CK ale unui tuplu oarecare din starea curent a relaiei R1, fie sunt NULL Dou domenii sunt compatibile dac ele sunt compatibile din punct de vedere al
tipului de date i compatibile semantic (are sens s fie comparate) n limbajul SQL verificarea domeniilor se rezum la verificarea tipurilor de date, iar
compatibiltatea semantic trebuie s fie asigurat de proiectant Cheia strin reprezint o constrngere referenial ntre cele 2 relaii
Relaia referit (R1) relaie printe, relaia care refer (R2) relaie fiu Cheia strin se specific n comanda CREATE TABLE sau ALTER TABLE:
[CONSTRAINT nume_constr] FOREIGN KEY (cheie_strina) REFERENCES relaia_referita (cheie_candidata)
Exemplu:CREATE TABLE ANGAJATI (
IdAngajat int PRIMARY KEY, Nume varchar(20) NOT NULL, Prenume varchar(20) NOT NULL,IdSecie int,CONSTRAINT FK FOREIGN KEY (IdSectie) REFERENCES SECTII(IdSectie));
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 40
Mentinerea integritMentinerea integritii referenii refereniale a relaiale a relaiilor (1)iilor (1)
Integritatea referenial (referential integrity) este proprietatea bazei de date prin care orice cheie strin: fie are o valoare care se regsete printre valorile cheii candidate referite fie are valoarea NULL
Pentru meninerea integritii refereniale trebuie s fie inpuse restrictii operaiilor de modificare a strii relaiilor (INSERT, DELETE, UPDATE)
Restriciile care se impun operaiilor de modificare a relaiilor depind de rolul relaiei (relaie care refer, relaie referit, sau poate avea ambele roluri)
Operaia INSERT: ntr-o relaie care nu refer alt relaie, inserarea se poate face fr restricii ntr-o relaie care refer (care conine o cheie strin): SGBD-ul permite introducerea
unui tuplu nou numai dac: (a) valoarea cheii strine a tuplului nou este NULL sau (b) exist o valoare a cheii referite egal cu valoarea cheii strine a tuplului nou
Operatia DELETE: ntr-o relaie care nu este referit tergerea se poate face fr restricii ntr-o relaie referit se admite: tergere restricionat, tergere n cascad,
anularea (SET NULL) a cheilor strine care refereau tuplul ters
tergerea restricionat interzice tergerea unui tuplu din relaia referit dac acesta este referit de un tuplu din relaia care o refer
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 41
Mentinerea integritMentinerea integritii referenii refereniale a relaiale a relaiilor (2)iilor (2) tergerea n cascad permite tergerea unui tuplu din relaia referit; dac
tuplul ters era referit de unul sau mai multe tupluri, atunci se terg i acestea din relaia care o refer; dac tuplurile terse din relaia care refer sunt, la rndul lor referite de alte tupluri din alte relaii, atunci trebuie s fie terse i acestea, .a.m.d.; se execut deci o tegere n cascad
Operaia UPDATE este o tergere urmat de o introducere, deci restriciile de actualizare reprezint combinaia restriciilor de introducere i de tergere
n limbajul SQL se specific opiunile ON DELETE i ON UPDATE constrngerii de cheie strin; valorile posibile ale acestor opiuni sunt: RESTRICT tergerea restricionat (este valoare implicit) CASCADE tergerea n cascad; SET NULL anularea cheilor strine care refereau tuplul ters NO ACTION se admit valori care nu respect integritatea relaional
Exemplu:CREATE TABLE ANGAJATI (
IdAngajat int PRIMARY KEY,Nume varchar (20) NOT NULL, . . . . . . . . . . . . . . . . . . . . . . . .
Sectie int,CONSTRAINT FK FOREIGN KEY (Sectie) REFERENCES SECTII (IdSectii) ,
ON DELETE CASCADE ON UPDATE RESTRICT);
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 42
Indexarea relaIndexarea relaiiloriilor (1)(1) Timpul de execuie a operatiilor asupra datelor din relaii depinde de modul de
reprezentare a mulimii de elemente (tupluri) ale relaiilor
Operaia de cutare a unui element ntr-o mulime se execut mai rapid dac elementele mulimii sunt reprezentate printr-o colecie ordonat, cum sunt liste, arbori, tabele de dispersie (hash table). De exemplu:
Timpul de cutare a unui element ntr-o mulime neordonat de N elemente este proportional cu N: TC = k1 * N = O(N)
Timpul de cutare al unui element memorat ntr-o structur arbore binar de cutare ordonat dup valoarea etichetei (cheii) de ordonare este TC = k2* log N = O(log N)
Un arbore binar ordonat complet cu d niveluri: pe nivelul 0 are 20 = 1 nod
pe nivelul 1 are 21 = 2 noduri
pe nivelul j are 2j noduri
Nr. total noduri N = 20 + 21 + 2j + 2d-1 = 2d 1 d = log (N + 1)
Pentru cutare se parcurg max d pai, deci timpul de cutare TC = k2* log N = O(log N)
4
62
3 5 71
Nivelul 0
Nivelul 1
Nivelul 2
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 43
Indexarea relaIndexarea relaiiloriilor (2)(2) Rezult c, dei o relaie nu presupune ordonarea tuplurilor sale, pentru
accelerarea operaiei de cutare (SELECT) a unui tuplu se folosesc colecii ordonate
i celelate operaii (INSERT, UPDATE, DELETE) se execut mai rapid n colecii ordonate Pentru inserarea unui tuplu se verific mai nti s nu existe deja un tuplu cu aceeai
valoare a cheii
Pentru modificarea unui tuplu se caut mai nti tuplul dorit, apoi se fac modificrile Pentru tergere se caut mai nti tuplul dorit i apoi se terge
Un index al unei relaii este o structur auxiliar, memorat n baza de date,care permite accesul rapid la tuplurile relaiei prin ordonarea acestora
Structuri folosite n indexare: arbori binari de cutare, arbori BTREE, arbori RTREE, tabele de dispersie (HASH) etc.
Exista dou categorii de indexuri ale unei relaii: un index primar, care determin localizarea tuplurilor n fiierele bazei de date zero, unul sau mai multe indexuri secundare, care nu modific localizarea tuplurilor,
dar sunt folosii pentru regsirea rapid a tuplurilor dup valorile unor atribute
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 44
Indexul primarIndexul primar (1)(1) Indexul primar (primary index) se definete pe cheia primar a relaiei Fiecare element al indexului primar corespunde unui tuplu al relaiei i
elementele sunt ordonate dup valoarea cheii primar PK
De exemplu, pentru o structur arbore binar ordonat a indexului primar al unei relaii cu ckeia primar PK i atributele (A, B, C, ...), un element (nod) Ni este memorat la adresa Li pe hard-disk i conine: Valoarea cheii primare a tuplului (pki), care este i eticheta de ordonare a arborelui Valorile celorlalte atribute ale tuplului (ai, bi, ci, ...) Adresele fiilor (Lj, Lk) (locaiile de memorare pe hard-disk a nodurilor fii)
(pki) (ai, bi, ci, ...)(Li)
(Lj) (Lk)
(Lj) (Lk)
(4)(Marin, .)
(2)(Popa, .) (6)(Ionescu,.)
(1)(Ionescu,..) (3)(Carol,...) (5)(Ene,.) (7)(Dobre,.)
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 45
Indexul primar Indexul primar (2)(2) Structura indexului primar este memorat mpreun cu tuplurile relaiei Operaiile de interogare care se fac dup indexul primar (cheia primar) se
execut eficient, fiind o cutare ntr-o mulime ordonat dup acea valoare Exemplu: Care sunt funcia i salariul angajatului cu cheia primara 3?Se caut nodul arborelui care are valoarea etichetei de ordonare (care este i cheia
primar a relaiei) egal cu valoarea dat (3)
Dup gsirea nodului se extrag valorile atributelor tuplului memorat n acel nod
Sunt necesari maximum d (log N) pai de cutare (N este nr total de tupluri ale relaiei)
Operatiile de interogare care se fac dup valoarea altor atribute (dect indexul primar) se execut mult mai ineficient, fiind o cutare ntr-o mulime neordonatdup acea valoare Exemplu: Care sunt funcia i salariul angajatului cu numele Dobre ?Pentru cutare se vor parcurge pe rnd toate tuplurile relaiei (memorate n nodurile
arborelui - exist astfel de algoritmi de parcurgere) pentru a gsi tuplul (sau tuplurile) cu valoarea atributului Nume egal cu Dobre
Sunt necesari maximum N pai (N este nr total de tupluri ale relaiei)
Pentru rezolvarea mai eficient a unor astfel de interogri se definesc indexuri secundare pe acele atribute care intervin n clauza WHERE din interogri
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 46
Indexuri secundare (1)Indexuri secundare (1) Un index secundar pe un atribut al unei relaii (secondary index) este o
structur ordonat dup valoarea acelui atribut; un element al unui index secundar conine: valoarea atributului indexat (care este etichet de ordonare) adresa (sau adresele) tuplurilor care conin acea valoare a atributului respectiv
Sunt dou categorii de indexuri secundare: unice (UNIQUE) i normale Un index secundar UNIQUE este definit pe un atribut A (simplu sau compus) al
relaiei care ia valori unice (cum este o cheie unic - secundar sau alternativ) Un element (nod) al indexului este compus din valoarea ai atributului indexat A i
adresa (Li) a unui singur tuplu care are acea valoare a atributului A
Dac relaia are N tupluri, indexul va avea M = N elemente Index secundar normal (care nu este unic - nu are o denumire specific) este
definit pe un atribut A care nu ia valori unice (nu este cheie unic) Un element (nod) al indexului este compus din valoarea ai a atributului indexat A i
lista (Li1 , Li2 , ) a adreselor (pe hard-disk) a tuplurilor ti1, ti2, care au valoarea aia atributului A
Dac relaia are N tupluri, indexul va avea M N elemente Pentru o structur arbore binar a indexului, fiecare nod mai conine i adresele
nodurilor fii (stnga, dreapta) (nereprezentate n figura urmtoare)
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 47
IIndexuri secundarendexuri secundare (2)(2) Exemplu: indexul secundar (cu structur arbore binar) definit pe atributul Nume al
relaiei ANGAJATI, al crei index primar este cel dat n figura precedent La interogarea Care sunt functia i salariul angajailor cu numele Dobre ? se
parcurge indexul secundar definit pe atributul Nume i se afl adresa unui singur tuplu (L7)
La interogarea Care sunt functia i salariul angajatilor cu numele Ionescu ? se parcurge indexul secundar definit pe atributul Nume i se afl adresele tuplurilor (L1 i L6) care au valoarea atributului Nume egal cu Ionescu
Dac indexul are o structur arbore binar ordonat, se vor executa max (log N) pai Un index secundar nu modific adresa de memorare a unui tuplu (care se afl n
indexul primar), dar conine informaii pentru gsirea rapid a unui tuplu dup valoarea acestui index
(Ionescu) (L1, L6)
(Dobre) (L7)
(Popa) (L2)(Carol) (L3) (Ene) (L5)
(Marin) (L4)
-
Prof. Felicia Ionescu Cap. 2 - Baze de date relationale 48
Indexuri secundare (Indexuri secundare (33)) Un index secundar se poate crea cu comanda CREATE TABLE (ca o
costrngere de tabel), cu ALTER TABLE sau cu CREATE INDEX; ex.:CREATE [optiuni] INDEX nume_index ON nume_tabel (lista_atribute_index);
Una din opiunile care se pot introduce n CREATE INDEX este UNIQUE n general, sistemele SGBD adaug:
Un index secundar UNIQUE pentru fiecare cheie candidat (definit prin constrngerea UNIQUE KEY)
Un index secundar normal pentru fiecare cheie strin; un astfel de index secundar ajut la gsirea rapid a tuturor tuplurilor asociate cu o valoare a cheii strine (Care sunt angajaii care lucreaz n secia cu numrul (identificatorul IdSectie) 1?
n sistemele SGBD avansate (obiect-relaionale), pot exista i indexuri secundare speciale, cum sunt Indexuri spaiale (indexarea obiectelor reprezentate n spaiul bi sau tridimensional) Indexuri de context (indexarea textelor) Indexuri XML (indexarea documentelor XML)
Indexurile secundare au avantaje i dezavantaje: Avantaje: accelereaz operaiile de interogare care se fac dup valoarea indexului Dezavantaje: ocup spaiu de memorie i consum timp la actualizarea relaiilor
n general, se recomand utilizarea unui numr ct mai mic de indexuri secundare, definite pe atributele care intervin cel mai frecvent n interogri
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
1
Capitolul 3: Interogarea bazelor de dateCapitolul 3: Interogarea bazelor de date
Limbaje de interogare Algebra relationala si calculul relational Operatiile pe multimi ale algebrei relationale
Reuniunea Intersecia Diferenta Produsul Cartesian
Operatiile speciale ale algebrei relationale Selectia Proiectia Jonctiunea Diviziunea
Interogarea bazelor de date Interogarea intr-o singura relatie Interogarea in doua sau mai multe relatii
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
2
Limbaje de interogareLimbaje de interogare Interogarea (query): operaia prin care se obin informatiile dorite dintr-o
baz de date, selectate conform unui anumit criteriu (condiie); Limbaje de interogare: abstracte si concrete (reale - implementari in diferite
SGBD-uri)
Limbaje de interogare abstracte: algebra relationala si calculul relational Algebra relationala (relational algebra) - const dintr-o mulime de operaii
care au ca operanzi relaii, iar rezultatul este tot o relaie Calculul relaional (relational calculus) - bazat pe calculul predicatelor -
exprim o interogare definind rezultatul dorit ca expresie de calcul relaional Calculul relational al tuplurilor foloseste variabile de tuplu (variabile
definite pe mulimea tuplurilor unei relaii) Calculul relational al domeniilor foloseste variabile de domeniu (variabile
definite pe domenii de definiie ale atributelor) Cele trei limbaje formale sunt echivalente din punct de vedere al interogarilor Limbajele de interogare reale sunt definite pe baza unuia sau altuia din
limbajele de interogare abstracte, sau pe o combinaie a acestora. De exemplu, limbajul SQL2 este n cea mai mare parte bazat pe algebra
relaional, dar mai conine i construcii derivate din calculul relaional.
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
3
Algebra relaAlgebra relaionalional Algebra relaional (relational algebra) - interogrile sunt expresii compuse
din operatii care au ca operanzi relatii si rezultatul este o relatie Operatiile algebrei relationale: operatii pe multimi si operatii speciale, la care
se adauga operatia de redenumire (rename) a atributelor (E.Codd) Operatiile relationale pe multimi acioneaz asupra relaiilor vzute ca mulimi
(de tupluri), fr a lua n consideraie compoziia fiecrui tuplu; acestea sunt: Reuniunea Intersecia Diferena Produsul cartezian
Operaiile relaionale speciale iau n consideraie compoziia tuplurilor, formate din valori ale atributelor relaiilor; acestea sunt: Restricia Proiecia Jonciunea Diviziunea
Proprietatea de nchidere: operanzii (unul sau doi operanzi) sunt relaii, rezultatul este o relaie; aceast proprietate permite operaii imbricate: proiecia unei jonciuni etc.
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
4
OperaOperaia de ia de rreuniuneeuniune Reuniunea (union) a dou relaii compatibile r(R) i s(S):
q = r s = { t | t r or t s} Pentru ca r si s sa fie compatibile, trebuie ca:
r si s s aiba acelasi grad (acelasi numar de atribute) Atributele corespondente (n ordine pozitional) s fie compatibile
Tuplurile care aparin ambelor relaii se introduc n relaia rezultat o singur dat (nu se duplic)
Relatia rezultat are un numar de tupluri (cardinalitatea) mai mic sau egal cu suma numerelor de tupluri ale celor doi operanzi
Exemplu:
r s
A B
1
2
1
A B
2
3
s
r
A B
1
2
1
3
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
5
OperaOperaiile de iile de iintersecntersecie ie i i ddifereniferen Intersectia (set-intersection) a dou relaii compatibile r(R) i s(S):
q = r s = { t | t r and t s}Exemplu:
A B
121
A B
11
r
r - s
A B
2r s
Diferena (set-difference) a dou relaii compatibile r(R) i s(S):q = r - s = { t | t r and t s}
Exemplu:A B
121
A B
23
rs
A B
23
s
Reuniunea si intersectia sunt comutative si asociative (r s = s r ; r (s t) = (r s) t); diferenta nu este nici nici comutativa, nici asociativa
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
6
Operatia de Operatia de pprodus rodus CCartesianartesian Produsul Cartesian (Cartesian-Product) a dou relaii r(R) i s(S):
q = r x s = { tp | t r and p s}, Q = R S Se presupune c multimile R si S sunt disjuncte, adica R S = Daca atributele din R si S nu sunt disjuncte, atunci (unele):
se pot redenumi (RENAME nume_atribut AS noul_nume_atribut) sau se pot califica cu numele relatiei careia ii apartin (folosind operatorul punct)
Tuplurile relaiei rezultat se obtin prin concatenarea valorilor atributelor fiecrui tuplu din prima relaie cu valorile atributelor tuturor tuplurilor din a doua relaie
Relaia rezultat are numrul de tupluri (cardinalitatea) egal cu produsul numarului de tupluri ale relatiilor operand
Exemplu:A B
1
2r
C D
10102010
E
aabb
s
A B
11112222
C D
1010201010102010
E
aabbaabb
r x s
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
7
Exprimarea operatiilor pe multimi in SQL (1)Exprimarea operatiilor pe multimi in SQL (1) Reuniunea:
SELECT lista_coloane1 FROM tabel1 [WHERE condiie1]UNIONSELECT lista_coloane2 FROM tabel2 [WHERE condiie2];
Exemplu (MySQL, Intreprindere): SELECT Nume, Prenume, Adresa FROM FURNIZORIUNIONSELECT Nume, Prenume, Adresa FROM CLIENTI;Afiseaza numele tuturor furnizorilor si al clientilor, precum si adresa acestora
Intersectia:SELECT lista_coloane1 FROM tabel1 [WHERE condiie1]INTERSECTSELECT lista_coloane2 FROM tabel2 [WHERE condiie2];
Diferenta:SELECT lista_coloane1 FROM tabel1 [WHERE condiie1]MINUSSELECT lista_coloane2 FROM tabel2 [WHERE condiie2];
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
8
Exprimarea operatiilor pe multimi in SQL (2)Exprimarea operatiilor pe multimi in SQL (2)
Produsul Cartesian:SELECT * from R, S;
SELECT lista_col_R, lista_col_S from R, S;
Exemplu (MySQL, Intreprindere):
SELECT * FROM ANGAJATI, SECTII;
SELECT IdAngajat, Nume, Prenume, DataNasterii, Adresa, Functia,
Salariu, ANGAJATI.IdSectie, SECTII.IdSectie, Denumire, Buget
FROM ANGAJATI, SECTII;
Produsul Cartesian este implementat in toate SGBD-urile (instructiunea SQL SELECT)
In sistemul Oracle sunt implementate toate operatiile pe multimi In alte SGBD-uri nu sunt implementate toate operaiile pe mulimi;
in SQL Server 2000 si in MySQL 5.0 nu sunt implementate operaiile INTERSECT i MINUS
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
9
OperaOperaia de ia de sselecelecie ie Seleca (sau restricia select, restriction) ntr-o relatie r(R) - definita astfel:
p(r) = {t | t r and p(t)}unde p, predicatul selectiei, este o expresie logic compus din termeni conectati prin
operatorii and, or not (i, eventual, paranteze)
Fiecare termen este o valoare logic obinut ca rezultat al unei operaii de comparaie de forma:
op sau op , unde
op este un operator de comparatie aritmetic (=, , >, . 10000000;
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
10
Operatia de Operatia de pproiecroiecieie Proiectia (project) pe atributele A1, A2, .. Ak intr-o relatie r(R) se noteaz:
A1, A2, Ak (r), unde A1 R, A2 R, Ak R Rezultatul este o relatie cu k atribute, cele din lista data Daca {A1, A2, Ak} nu contine o supercheie, pot sa apara tupluri duplicat;
teoretic, tuplurile duplicat se elimina, dat fiind ca rezultatul este o multime
Exemplu:A B C
10
20
30
40
1
1
1
2
A C
1
1
1
2
=
A C
1
1
2
A,C (r)r
In limbajul SQL proiectia se exprima astfel:SELECT DISTINCT A1, A2, Ak FROM R
Daca nu se introduce parametrul DISTINCT, nu se elimina tuplurile duplicat
Exemplu (MySQL, WORLD): SELECT DISTINCT CountryCode FROM City;
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
11
OperaOperaia de joncia de jonciune naturaliune natural (1)(1) Jonciunea natural (natural join) combin tuplurile din doua relatii Fie multimile de atribute: A = {A1,A2,...Am} , B= {B1,B2,...Bn}, C={C1,C2,Ck}
si doua relatii r(R) si s(S), unde:
R ={A, B}, S = {B, C} deci atributele R S = B = {B1, B2,...Bn} sunt comune celor dou relaii
Jonciunea natural este o relatie q = r s, care se obine n felul urmtor: se calculeaz produsul cartesian al celor doua relatii: p = r x s, P = {A, R.B, S.B, C}; din tuplurile produsului cartesian se selecteza acele tupluri care au valori egale
pentru atributele comune (B1, B2,...Bn): R.B = S.B, adic R.B1=S.B1, R.B2=S.B2,..
se face proiectia rezultatului pe multimea de atribute R S = {A, B, C} Schema relatiei rezultat este Q = R S = {A, B, C}
q = r s = A,B,C (r.B1=s.B1 AND r.B2=s.B2 AND r.Bn = s.Bn) (r x s) Atributele comune R.B si S.B trebuie s fie compatibile n cele doua relatii;
dac sunt compatibile, ele se consider identice chiar dac au denumiri diferite, si n reuniunea atributelor R S se introduc o singur dat
Cazul cel mai frecvent de jonctiune naturala: intre doua relatii asociate (1: N), atributul comun fiind cheia strain cheia primar (candidat) referit
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
12
Operatia de jonctiune naturala (2)Operatia de jonctiune naturala (2) Exemplul 1: r s = A,B,C,D,E (r.D = s.D ) (r x s)
A B
12412
C D
aabab
D
abcde
E
sr
A B
12412
C D
aabab
E
r s In SQL trebuie sa fie introduse explicit lista atributelor rezultatului si conditiile
de egalitate ale atributelor comune: SELECT A,B,C,R.D,E FROM R, S WHERE R.D = S.D;
Exemplul 2: ANGAJATI SECTII; cheia straina: ANGAJATI.IdSectie SELECT IdAngajat, ANGAJATI.Nume, Prenume, DataNasterii, Adresa, Salariu,
ANGAJATI.IdSectie, SECTII.Nume, Buget FROM ANGAJATI, SECTII WHERE ANGAJATI.IdSectie=SECTII.IdSectie;
Exemplul 3:(MySQL-WORLD) city country; cheia straina: city.countryCodeSELECT ID, city.Name Oras, CountryCode 'Cod Tara', city.Population, country.Name
Tara, Continent from city, country where city.countryCode=country.CODE order by country.Name;
Daca nu se afiseaza toate atributele jonciunii, nseamna ca s-a combinat cu o proiecie
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
13
JJonconciuniiuni interne interne i externei externe Jonciunea natural se mai numete i jonciune intern i se mai poate
exprima in SQL cu cuvintele cheie INNER JOIN
Exemplu de jonciune (combinat cu o proiecie si o selectie)(MySQL world)SELECT city.Name Oras, Code 'Cod Tara', country.Name Tara, Continent
FROM city INNER JOIN country ON CountryCode=CodeWHERE Continent='Antarctica' OR Continent = 'Europe' ORDER BY Continent;
Jonciunea extern introduce n plus toate liniile care exit n prima relaie (pentru LEFT OUTER JOIN) sau n cea de-a doua relaie (pentru RIGHT OUTER JOIN) i pentru care nu exist linii n ceallalt relaie care s ndeplineasc condiia de join; exemplu:
SELECT city.Name Oras, Code 'Cod Tara', country.Name Tara, Continent FROM city RIGHT OUTER JOIN country ON CountryCode=CodeWHERE Continent='Antarctica' OR Continent = 'Europe' ORDER BY Continent;
Se vor afia si rile care nu au nici un oras nscris n tabelul city.
-
Prof. Felicia Ionescu Cap. 3 - Interogarea bazelor de date
14
OperaOperaia de diviziuneia de diviziune Fie relaiile r(R) si s(S), unde:
R = {A, B} unde A={A1,A2,..Am}, B={ B1,B2,..Bn} S = {B}
Relaia q = r s are schema Q = R S = {A} si: r s = { t | t R-S (r) u s ( tu r ) }
unde tu inseamna concatenarea tuplurilor t si u
n limbajul SQL, diviziunea se exprim printr-o instruciune SELECT, introducnd explicit toate conditiile impuse valorilor atributelor
Exemplu:A B
1231213
r
B
12
s
A
r s