curs sql.pdf

1

BAZE DE DATErealizate cu

ACCESS 2010Anul universitar 2011-2012

Cerinţe examen� 20% proiect prezentat la laborator� 20% teste în timpul orelor de lab.� 10% teme de control platforma� 50% examen SCRIS� Bibliografie: materiale cursuri, seminarii şi

Baze de date – Access 2007, proiectare şi realizare pas cu pas, Tamaş Ilie, etc., Editura Infomega 2010, Bucureşti

2

Teoria generală a bazelor de date� Datele reprezintă o colecţie de premise, care constau în

cuvinte, numere, imagini, etc.� Informaţia poate fi definită într-o formă generală, ca

fiind totalitatea datelor pentru care se face o referire,adică date accesibile public (dar securizate), înconcordanţă cu interesul pentru organizaţie.

� Cunoştinţele gestionează o experienţă complexă,câştigată din diferite perspective. Totalitatea ideilor,noţiunilor, informaţiilor pe care le are cineva într-undomeniu reprezintă cunoştinţe.

� Învăţarea reprezintă capacitatea de a transmitesistematic cunoştinţe şi deprinderi dintr-un domeniuoarecare, a iniţia pe cineva, a dobândi cunoştinţe prinstudiu, a memora, a câştiga experienţă.3

4

TRECUT VIITOR

Teoria generală a bazelor de date

�� Baza de dateBaza de date este:� Un ansamblu de date structurate� Legate funcţional� Stocate pe suporturi tehnice adresabile� Accesate de mai mulţi utilizatori de o

manieră selectivă şi într-un timp oportun

5

6

Câmpuri calculateÎntregistrare (Tuplu)

Câmpuri (Atribute / Proprietăţi / Rubrici)

Domeniu

Valoare

Baze de date Excel

7

Clienţi1 Facturi

n

8

ClienţiCod_clientCod_client

Nume_client

Adresă

Localitate

ProduseCod_produsCod_produs

Denumire_produs

Preţ_unitar

FacturaNr_factură

Data_factură

Data_scadenţei

Cod_client (CF)

Conţinut FacturăCod_ProdusCod_Produs

Nr_FacturăNr_Factură

Cantitate

9

Modelul Modelul RELAŢIONALRELAŢIONAL

� Un model de organizare bidimensională a datelor în tabele

� Implementează schema relaţională (MRD)� Tabele� Relaţii între tabele� Reguli de validare

� Algebra relaţională (Operatori relaţionali) � Operatori de asamblare (Reuniunea, Intersecţia, Produsul

cartezian, Diferenţa)� Operatori unari (Proiecţia, Selecţia)� Operatori de extensie (Compunerea, Diviziunea)

� Un limbaj standard de gestiune a BDR

10

Modelul Modelul RELAŢIONALRELAŢIONAL�� R(XR(X11,X,X22,....X,....Xnn))

� unde, pentru fiecare element Xi se defineşte un domeniu de valori�� DomeniulDomeniul reprezintă mulţimea valorilor posibile care definesc o

anumită proprietate.�� Atributul Atributul / C/ Câmpul / Proprietate âmpul / Proprietate reprezintă mulţimea valorilor

existente într-o coloană a relaţiei. �� RelaţiileRelaţiile se reprezintă prin tabele care sunt supuse următoarelor

restricţii:� În fiecare coloană, toate valorile sunt de acelaşi tip;� Ordinea liniilor (tuplurilor sau înregistrărilor) nu este predefinită;� Nu sunt admise înregistrări duplicate� Coloanele sunt identificate prin nume distincte (atribute sau proprietăţi)

�Tuplurile unei relaţii se pot identifica de o manieră unică prin intermediul valorilor unuia sau mai multor atribute care joacă rol de CHEIE PRIMARĂ a relaţiei respective

Definiţii ale cheilor existente într-o tabelă� Cheia unei tabele reprezintă un ansamblu minim de atribute

care identifică o înregistrare dintr-o tabelă. � Cheia poate fi formată dintr-un singur atribut şi se numeşte

cheie simplă sau din mai multe atribute şi se numeşte cheie compusă.

� într-o tabelă pot exista mai multe atribute cu proprietatea că pot identifica o înregistrare şi sunt numite chei candidate.

� Cheia primară va identifica în mod unic o înregistrare din tabelă.

� Dacă într-o tabelă un atribut sau ansamblu de atribute ia valori în acelaşi domeniu ca şi cheia primară a unei tabele, atunci acest ansamblu de atribute se numeşte cheie externă.

11

12

Modelul Modelul RELAŢIONALRELAŢIONAL�� CHEIA CHEIA EXTERNĂEXTERNĂ: Fiind două relaţii R1 şi R2, cu

atributele A1 şi A2 chei primare definite pe acelaşi domeniu primar D, se spune că în relaţia R1, A2 este cheie externă dacă, utilizând o parte din valorile ei sau toate, pot fi regăsite tuplurile relaţiei R2

� (altfel spus, un atribut al unei relaţii este cheie externă, dacă se regăseşte pe post de cheie primarăîn altă relaţie)

R1(A1,B1,C1,D1,A2A2) R2(A2,B2,C2,D2)

Relaţii

Chei primare

Cheie externă

13

Modele de date: modelul RELAŢIONALmodelul RELAŢIONAL

R1(A1,B1,C1,D1,A2A2) R2(A2,B2,C2,D2)

Relaţii

Chei primareClienţiCod_clientCod_client

Nume_client

Adresă

Localitate

FacturaNr_facturăNr_factură

Data_factură

Data_scadenţă

Cod_client Cod_client

Cheie externă

Factura(Nr.Factura,Data_f,Data_s,Cod clientCod client)

Clienti(Cod client,Nume_cl,Adresa,Localitate)

14

Dictionar Date+Reguli Gest NORMALIZARE MRD

Normalizarea Bazelor de Date:Procesul de normalizare

NormalizareaNormalizarea este un demers ce conduce la construirea modelului relaţional al bazei de date

Se descompune o tabelă complexă în subtabele mai mici şi mai uşor demanipulat

SCOP: Normalizarea garantează coerenţacoerenţa bazei de date în timpul operaţiilor de actualizare de date, evitând redundanţeleevitând redundanţele

REZULTAT: Un model nedecompozabil ce respectă regulile de definire semantică şi de integritate a datelor

15

� Există o dependenţă funcţională între 2 atribute atunci când cunoscând valoarea luată de către un atribut, se furnizează sistematic valoarea pentru celălalt atribut�� A A --> B> B (B depinde funcţional de A, dacă la orice valoare a lui A

corespunde în orice moment o valoare unică a lui B)�� ExempleExemple de dependenţe funcţionale:

� Cod Produs -> Denumire produs� Cod Produs -> Preţ de referinţă� Număr Comandă -> Dată Comandă� Număr Comandă -> Cod Client -> Nume Client

� -> Cod Fiscal

Dependenţa funcţională : Dependenţele sunt legături logice, ce se stabilesc între

câmpurile modelului relaţional.

Dacă un atribut sau un grup de atribute dintr-un tabel determină funcţional celelalte atribute ale tabelului, rezultă că atributul sau grupul de atribute constituie cheia primară a tabelului

TranzitivitateTranzitivitate:

Dacă A -> B şi B -> C, atunci

A A -->C>C

16

� Reprezentarea grafică a dependenţelor funcţionale

Normalizarea Bazelor de Date:Diagrama dependenţelor funcţionale

Cod Produs Denumire, UM, Preţ de referinţă

NrComandă

Cod Produs

Dată comandă, Cantitate

comandată

17

� Dependenţe Inter-Tabele (1-1)� Când la o valoare a atributului Cheie primară dintr-o tabelă, se

asociază o singură valoare a atributului Cheie primară dintr-o altă tabelă, se poate spune că există o dependenţă funcţională între tabele, exprimată prin dependenţa funcţională dintre atributele chei primare aferente celor două tabele

Normalizarea Bazelor de Date:Dependenţe Inter-Tabele şi Multivaloare

CodProfesor NumeProfesor

� Dependenţe Multivaloare (1-n) (n-m)� În cazul în care la o valoare a atributului Cheie primară dintr-o

tabelă, corespund mai multe valori ale altui atribut dintr-o altă tabelă, se poate spune că există o dependenţă funcţională multivaloare

Nr.factură Cod Produs

CodProfesor Cod Carte

18

� Dicţionar de date şi Reguli de gestiune� Potrivit regulilor de gestiune coroborate cu

analiza informaţională referitoare la aplicaţia de informatizat se declară într-un tablou toate atributele disponibile (Identificatorul + Denumirea în clar)

Normalizarea Bazelor de Date : Formele normale 1, 2, 3

DD+RG

1FN2FN

3FN

�� 1 FN1 FN� O relaţie R este în 1FN atunci când atributele sale nu pot fi

descompuse în unităţi mai mici�� 2 FN2 FN

� O relaţie R este în 2FN, dacă este în 1FN şi toate dependenţele între cheia primară a lui R şi celelalte atribute a lui R sunt elementare (atributele nu depind de o parte din cheie)

�� 3 FN3 FN� O relaţie R este în 3FN, dacă este în 2FN şi dacă sunt eliminate

toate dependenţele funcţionale tranzitive (dacă nu există nici o dependenţă funcţională între atributele non-cheie)

19

� Formalizarea procesului de normalizare este operaţională prin Matricea Dependenţelor Funcţionale: � MDF are pe linie toate atributele dicţionarului de date� MDF are pe coloană toate atributele sau numai atributele

care pot avea rol de cheie primară� Se notează 1 la intersecţia liniei cu coloana pentru care

există dependenţă funcţională� Se notează cu „M” dependenţa multiplă� Se notează 1T dependenţele funcţionale tranzitive

� Liniile care NU AU 1 desemnează fie atribute care nu sunt dependente funcţional de un identificator simplu, fie desemnează atribute parametru

Normalizarea Bazelor de Date:Matricea Dependenţelor Funcţionale

20

�� Inventarierea atributelorInventarierea atributelor (se vor selecta toate atributele referitoare la facturile primite)� Număr factură� Dată factură� Cod furnizor� Denumire furnizor� Adresa, etc.

Normalizarea Bazelor de Date:Exemplul I Etapele procesului de NORMALIZARE

�� Se specifică regulile de gestiune şi algoritmii de calcul asociaţi Se specifică regulile de gestiune şi algoritmii de calcul asociaţi (se vor specifica diversele restricţii şi condiţii impuse datelor)� O factură este emisă de un singur furnizor� Codul materialului este unic� O factură conţine mai multe produse aprovizionate� Furnizorii pot fi numai persoane juridice

� Algoritmii de calcul: (Val_mat_fact=Cant_facturată*Preţ_unitar)

21

�� Se întocmeşte dicţionarul datelorSe întocmeşte dicţionarul datelor

� Un atribut poate fi înscris o singură dată în dicţionar

� Se elimină atributele sinonime (de ex. Cod furnizor = Simbol furnizor)

� Dicţionarul datelor nu admite atribute derivate sau calculate (Valoare, TVA)

Normalizarea Bazelor de Date:Etapele procesului de NORMALIZARE

Nr.crt Atribut În clar1 CodFz Cod Furnizor2 DenFz Denumire Furnizor3 AdrFz Adresă Furnizor4 NrFact Număr Factură5 DataFact Dată Factură

22

�� Se stabilesc Se stabilesc dependenţele funcţionaledependenţele funcţionale între atribute prin între atribute prin MDFMDF

�� Toate atributele sau grupurile de atribute determinante, Toate atributele sau grupurile de atribute determinante, devin devin CHEI CANDIDATECHEI CANDIDATE (posibile chei ale relaţiei). Cheile (posibile chei ale relaţiei). Cheile candidate aparţinând aceleiaşi relaţii sunt caracterizate prin candidate aparţinând aceleiaşi relaţii sunt caracterizate prin dependenţe funcţionale reciproce.dependenţe funcţionale reciproce.

�� Se stabilesc Se stabilesc CHEILE PRIMARECHEILE PRIMARE dintre cheile candidatedintre cheile candidate


CodFz DenFz AdrFz NrFact DataFactCodFz NU 11 11DenFz NUAdrFz NU

NrFact 1 1T 1T NU 11DataFact NU

23

�� Sau se stabilesc dependenţele funcţionale între Sau se stabilesc dependenţele funcţionale între atributeatribute


Număr Factură Dată Factură

Cod Furnizor

Denumire Furnizor

Adresă Furnizor

24

�� Pentru Pentru atributele izolateatributele izolate se vor căuta grupuri se vor căuta grupuri

de atribute ce pot constitui determinanţi ai de atribute ce pot constitui determinanţi ai

acestoraacestora. S. Se vor căuta mai întâi grupuri de chei e vor căuta mai întâi grupuri de chei

primare, apoi grupuri de atribute nonprimare, apoi grupuri de atribute non--cheie, cheie,

iar în final, dacă va fi cazul, se vor adăuga chei iar în final, dacă va fi cazul, se vor adăuga chei

surogat.surogat.

�� Cu fiecare cheie primară şi cu atributele Cu fiecare cheie primară şi cu atributele

determinate direct (determinate direct (fără tranzitivitatefără tranzitivitate) ) se se

formează o nouă relaţieformează o nouă relaţie..

�� Se stabilesc Se stabilesc CHEILE EXTERNECHEILE EXTERNE


25

�� DacăDacă întreîntre douădouă cheichei primareprimare existăexistă oo dependenţădependenţă

multiplămultiplă (reciprocă(reciprocă)),, atunci,atunci, aceastăaceastă dependenţădependenţă

vava generagenera oo relaţierelaţie..

�� CheiaCheia primarăprimară aa acesteiacestei relaţiirelaţii vava fifi formatăformată dindin

celecele douădouă atribute,atribute, care,care, individualindividual vorvor aveaavea şişi rolrol

dede cheichei externeexterne..


FURNIZORFURNIZOR(CodFz, DenFz, AdrFz)

FACTURĂFACTURĂ(NrFact, DataFact, CodFz)

26

Normalizarea Bazelor de Date:EXEMPLUL II: Evidenţa facturilor emise

CLIENTI

PRODUSE

FACTURINrFact,

DataFact

CodCli, Nume,

Adresa,Tel

CodProd, DenProd,

CantFact, Pret

27


NrFact, DataFact, CodCli, Nume, Telefon, Adresa, CodProd, DenProd, CantFact, Pret

FN1FN1

� Descompunerea atributelor compusecompuse ale dicţionarului de date în atribute simpleatribute simple

� Precizarea identificatorului.

�Dicţionarul datelor �(NrFact, DataFact, �CodCli, Nume, Telefon, Strada, Nr, CodPostal, Localitate, Cod Postal, �CodProd, DenProd, CantFact, PretRef, PretFact)

28


�� FN2FN2

� Fiecare atribut non-cheie să

depindă de întreaga cheie

primară (dependenţe

elementare).

CLIENTICLIENTI

FACTURATFACTURAT

FACTURIFACTURI

PRODUSEPRODUSE

FACTURATFACTURAT(NrFact, CodProd, CantFact, PretFact)PRODUSEPRODUSE(CodProd, DenProd, PretRef)

FACTURIFACTURI(NrFact, DataFact, CodCli)

CLIENTICLIENTI (CodCli, Nume, Telefon, Strada, Nr, CodPostal, Localitate)

29


•• FN3FN3Eliminarea dependenţelor tranzitive

CLIENT (CodCli, Nume, Telefon, Fax, Strada, Nr, CodPostal)FACTURA(NrFact, DataFact, CodCli)PRODUS(CodProd, DenProd, PretRef)FACTURAT(NrFact, CodProd, CantFact, PretFact)LOCALIZARELOCALIZARE(CodPostal, Localitate)

curs sql.pdf

Documents