1Proiectarea Bazelor de Date

Cornelia TUDORIE

Proiectarea Bazelor de Date -Cornelia TUDORIE 2

Proiectarea Bazelor de Date - Cuprins

A. Modelul Relaional.B. Bazele teoretice ale limbajelor relaionaleC. Limbajul SQL. Lucrul cu tabele. D. Sisteme de baze de date. Performana.E. Obiecte ale sistemului de baze de date. Performana n

utilizareF. Programe pentru baze de date. Performana n programare.G. Protecia datelor.H. Sisteme Informatice. Proiectarea sistemelor de baze de date.

Proiectarea Bazelor de Date -Cornelia TUDORIE 3

A. Modelul Relaional

Baz de dateansamblu structurat de date,nregistrate pe suporturi accesibile calculatorului, n scopul satisfacerii simultan, a mai multor cereridin partea utilizatorilor, n mod selectiv, i n timp optim

Baz de date relaional baz de date a crei schem esteun ansamblu de scheme de relaii (tabele), cu legturi logice ntre ele

Proiectarea Bazelor de Date -Cornelia TUDORIE 4

A. Modelul Relaional

Baz de date relaional (exemplu) :

DISTRIBUITOR [ numed, adresad ]FILM [ codf, titlu, durata, categ, numed ]CASETA [ codc, codf, nrimp ]CLIENT [ nrcl, nume, adresa, datan ]IMPRUMUT [ codc, nrcl, dataret ]REZERVARE [ nrcl, codf, datarez ]

2Proiectarea Bazelor de Date -Cornelia TUDORIE 5

B. Bazele teoretice ale limbajelor relaionale

Algebra relaional. Limbaje algebrice.

Calculul relaional. Limbaje predicative.

Proiectarea Bazelor de Date -Cornelia TUDORIE 6

C. Limbajul SQL. Lucrul cu tabele.

Limbajul de interogare.

Limbajul de manipulare.

Limbajul de descriere.

Proiectarea Bazelor de Date -Cornelia TUDORIE 7

D. Sisteme de baze de date. Performana.

Sistem informatic :- model al unui sistem real

- conine reprezentri informatice ale obiectelor i proceselor reale

- pentru a urmri evoluia sistemului real, a lua decizii,..

- faciliteaz controlul i coordonarea sistemului real

Proiectarea Bazelor de Date -Cornelia TUDORIE 8

D. Sisteme de baze de date. Performana.

Sistem de baze de date :

- sistem integrat de date memorate i organizate sub un control centralizat

integrare: structuri de date intercorelate

control centralizat : SGBD, cu ajutorul schemei

3Proiectarea Bazelor de Date -Cornelia TUDORIE 9

D. Sisteme de baze de date. Performana.

Schema :

- descrierea datelor, obiectelor i a accesului la ele

SGBD :

- ansamblu coordonat de programe ce permitedescrierea, memorarea, manipularea, prelucrarea ansamblurilorde date ce constituie baze de date

Proiectarea Bazelor de Date -Cornelia TUDORIE 10

Rolul SGBD

21

3

4567

8

91011

Legend

informaii de control

date

consultare scheme

Proiectarea Bazelor de Date -Cornelia TUDORIE 11

Avantaje

coeren (se reduce redundana sau este strict controlat + restricii de integritate) independena datelor fa de programe accesibilitate (limbaje de interogare de nivel nalt) partajarea datelor protecia datelor

Proiectarea Bazelor de Date -Cornelia TUDORIE 12

Dezavantaje

costuri (hardware, software, instruire, personal) probleme de securitate i integritate (din partajarea datelor) dificultatea modelrii datelor

4Proiectarea Bazelor de Date -Cornelia TUDORIE 13

Obiective

Performana la nivel de: utilizare

programare

proiectare

administrare

Se urmrete: minimizarea

timpului de rspuns

gestiunea optim a spaiului de stocare

asigurarea integritii datelor

Proiectarea Bazelor de Date -Cornelia TUDORIE 14

Proiectarea Bazelor de Date - Cuprins

A. Modelul Relaional.B. Bazele teoretice ale limbajelor relaionaleC. Limbajul SQL. Lucrul cu tabele. D. Sisteme de baze de date. Performana.E. Obiecte ale sistemului de baze de date. Performana n

utilizareF. Programe pentru baze de date. Performana n programare.G. Protecia datelor.H. Sisteme Informatice. Proiectarea sistemelor de baze de date.

Proiectarea Bazelor de Date -Cornelia TUDORIE 15

E. Obiecte ale sistemului de baze de date. Performana n utilizare.

1. Stocarea informaiilor n baza de date2. Obiectele bazei de date:

1. Tabele2. Indeci3. Tabele virtuale4. Sinonime5. Secvene

3. Gestiunea eficient a spaiului de stocare

4. Optimizarea cererilor

Proiectarea Bazelor de Date -Cornelia TUDORIE 16

E. 1. Stocarea informaiilor n baza de date

Structura Bazei de Date

Nivel conceptual Diagrama entitate-legtur

Nivel logic Tabele, ... i alte obiecte logice

Nivel fizic Obiecte fizice (fiiere)

5Proiectarea Bazelor de Date -Cornelia TUDORIE 17

E. 1. Stocarea informaiilor n baza de date

Structura conceptual a Bazei de Date DISTRIBUITOR

FILM

CASETA

CLIENT

IMPRUMUT

REZERVARE

numed

adresadcodf

datarez

dataret

titlu

durata

categ

codc

nume

nrcl

adresa

datan

nrimp

Proiectarea Bazelor de Date -Cornelia TUDORIE 18

E. 1. Stocarea informaiilor n baza de date

Structura logic a Bazei de Date

DISTRIBUITOR [ numed, adresad ]FILM [ codf, titlu, durata, categ, numed ]CASETA [ codc, codf, nrimp ] + restricii de integritateCLIENT [ nrcl, nume, adresa, datan ] indeci, IMPRUMUT [ codc, nrcl, dataret ] tabele virtuale, ...REZERVARE [ nrcl, codf, datarez ]

Proiectarea Bazelor de Date -Cornelia TUDORIE 19

E. 1. Stocarea informaiilor n baza de date

Organizarea logic a Bazei de Date

User tablesUser tables Data Data dictionarydictionary

ServerServer

Proiectarea Bazelor de Date -Cornelia TUDORIE 20

E. 1. Stocarea informaiilor n baza de date

Organizarea fizic a Bazei de Date

Control files

Redo logfiles

Data filesPasswordfile

Parameterfile

Archivedlog files

Database

6Proiectarea Bazelor de Date -Cornelia TUDORIE 21

E. 1. Stocarea informaiilor n baza de date

Componente de date Oracle

Database

Componente logice de stocare

Tablespace Data file

O/S BlockOracleblock

Segment

Extent

Obiectpartitionat

Obiectnepartitionat

Nivel logic Componente fizice de stocare

ocupa

se gaseste in

se gaseste in

Proiectarea Bazelor de Date -Cornelia TUDORIE 22

E. 1. Stocarea informaiilor n baza de date

Tipuri de segmente

TableTable Table Table partitionpartition

IndexIndex

Proiectarea Bazelor de Date -Cornelia TUDORIE 23

E. 1. Stocarea informaiilor n baza de date

Tipuri de segmente

RollbackRollbacksegmentsegment

TemporaryTemporarysegmentsegment

Proiectarea Bazelor de Date -Cornelia TUDORIE 24

E. 1. Stocarea informaiilor n baza de date

Blocul de baza de date (DataBase Block) Cea mai mic unitate de stocare Const n unul sau mai multe blocuri ale SO Stabilit la crearea bazei de date prin parametrul DB_BLOCK_SIZE

7Proiectarea Bazelor de Date -Cornelia TUDORIE 25

E. 1. Stocarea informaiilor n baza de date

Continutul unui bloc de baza de date

HeaderHeader

Free spaceFree space

DataData

Proiectarea Bazelor de Date -Cornelia TUDORIE 26

E. 1. Stocarea informaiilor n baza de date

Parametrii pentru utilizarea spaiuluin blocul de baz de date

PCTFREEPCTFREE

PCTUSEDPCTUSED

Proiectarea Bazelor de Date -Cornelia TUDORIE 27

E. 1. Stocarea informaiilor n baza de date

Utilizarea spatiului in blocul de baza de date

InsertsInserts

InsertsInserts

InsertsInserts

InsertsInserts

1 2

3 4

PCTFREE=20PCTFREE=20 PCTUSED=40PCTUSED=40

80%80%

80%80%40%40%

Proiectarea Bazelor de Date -Cornelia TUDORIE 28

Structura unui bloc

BlocBloc de de baza de dbaza de datatee

HHeadereader de rndde rndLungimea cLungimea cololoaneioaneiValoarea cValoarea cololoaneioanei

8Proiectarea Bazelor de Date -Cornelia TUDORIE 29

Migrarea rndurilor

nainte denainte deUUpdatepdate

DupDupUUpdatepdate

Proiectarea Bazelor de Date -Cornelia TUDORIE 30

E. 1. Stocarea informaiilor n baza de date

Informaii n dicionarul de date(pentru administrator)

TablespacesDBA_TABLESPACES

SegmentsDBA_SEGMENTS

Data filesDBA_DATA_FILES

Free extentsDBA_FREE_SPACE

Used extentsDBA_EXTENTS

Proiectarea Bazelor de Date -Cornelia TUDORIE 31

E. Obiecte ale sistemului de baze de date. Performana n utilizare.

1. Stocarea informaiilor n baza de date2. Obiectele bazei de date:

1. Tabele2. Indeci3. Tabele virtuale4. Sinonime5. Secvene

3. Gestiunea eficient a spaiului de stocare

4. Optimizarea cererilor

Proiectarea Bazelor de Date -Cornelia TUDORIE 32

Tabela (relaia) - componenta principal a bazei de date relaionale - structur pe linii (rnduri sau nregistrri)

i coloane (atribute sau cmpuri)Elemente importante:

- cheia primar- cheie strin- cheie secundar

Operaii:- creare

- interogare- actualizare : adugare, modificare, tergere de rnduri- tergere

E. 2. 1. Obiectele bazei de date: Tabele

9Proiectarea Bazelor de Date -Cornelia TUDORIE 33

Creare de tabele

1. Crearea unei tabele de sine stttoare2. Crearea unei tabele pe baza altora, deja existente.

Proiectarea Bazelor de Date -Cornelia TUDORIE 34

Crearea unei tabele de sine stttoare

CREATE TABLE [schema.] tabela(coloan tip [, coloan tip, ...] )[TABLESPACE tablespace ][PCTFREE integer ] [PCTUSED integer ][STORAGE ( INITIAL integer [ { K | M } ]

NEXT integer [ { K | M } ]PCTINCREASE integerMINEXTENTS integerMAXEXTENTS integer )] ;

schema numele proprietarului tabeleiTABLESPACE numele spaiului de stocarePCTFREE procentul de spaiu liber a blocului rezervat pentru actualizriPCTUSED procentul minim de ocupare permanent a unui bloc STORAGE indicaii de alocare a spaiului (dimensiunea spaiului iniial, a celei de a doua extensii, procentul de cretere ncepnd de la a treia extensie, numrul minim i maxim de extensii)

Proiectarea Bazelor de Date -Cornelia TUDORIE 35

Crearea unei tabele de sine stttoare

CREATE TABLE employees(empno NUMBER(4), last_name VARCHAR2(30)deptno NUMBER(2))

TABLESPACE data01 PCTFREE 20 PCTUSED 50STORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50);

CREATE TABLE employees(empno NUMBER(4), last_name VARCHAR2(30)deptno NUMBER(2))

TABLESPACE data01 PCTFREE 20 PCTUSED 50STORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50);

Exemplu

Proiectarea Bazelor de Date -Cornelia TUDORIE 36

Modificarea parametrilor de stocare

ALTER TABLE scott.employeesPCTFREE 30 PCTUSED 50STORAGE( NEXT 200KMINEXTENTS 2MAXEXTENTS 100);

ALTER TABLE scott.employeesPCTFREE 30 PCTUSED 50STORAGE( NEXT 200KMINEXTENTS 2MAXEXTENTS 100);

ALTER TABLE [schema.] tabela[PCTFREE integer ] [PCTUSED integer ][STORAGE ( INITIAL integer [ K | M ]

NEXT integer [ K | M ]PCTINCREASE integerMINEXTENTS integerMAXEXTENTS integer ) ] ;

10

Proiectarea Bazelor de Date -Cornelia TUDORIE 37

Dealocarea spaiului neutilizat

Free space after deleteFree space after deleteUnused blockUnused block

High water High water markmark

BeforeBeforedeallocationdeallocation

Used blockUsed block

ALTER TABLE scott.employeesDEALLOCATE UNUSED;

ALTER TABLE scott.employeesDEALLOCATE UNUSED;

AfterAfterdeallocationdeallocation

Proiectarea Bazelor de Date -Cornelia TUDORIE 38

Crearea unei tabele de sine stttoare

CREATE TABLE [schema.] tabela(coloan tip [, coloan tip, ...] )[TABLESPACE tablespace ][PCTFREE integer ] [PCTUSED integer ][STORAGE ( INITIAL integer [ K | M ]

NEXT integer [ K | M ]PCTINCREASE integerMINEXTENTS integerMAXEXTENTS integer ) ] ;

n acelai timp, se pot defini i restricii de integritate (CONSTRAINTS)

Proiectarea Bazelor de Date -Cornelia TUDORIE 39

Creare de tabele

1. Crearea unei tabele de sine stttoare2. Crearea unei tabele pe baza altora, deja existente

Proiectarea Bazelor de Date -Cornelia TUDORIE 40

Crearea unei tabele pe baza altora

CREATE TABLE [schema.] tabela[ (coloan [, coloan, ...] ) ]

[TABLESPACE tablespace ][PCTFREE integer ] [PCTUSED integer ][STORAGE ( INITIAL integer [ { K | M } ]

NEXT integer [ { K | M } ]PCTINCREASE integerMINEXTENTS integerMAXEXTENTS integer ) ]

AS subinterogare;

11

Proiectarea Bazelor de Date -Cornelia TUDORIE 41

Obinerea de informaii asupra tabelelor

Dicionarul de dateDicionarul de date ( Data dictionary ) furnizeaz informaii despre :

structuri logice sau fizice

nume, definiii, spaii alocate obiectelor

restricii de integritate

monitorizare Proiectarea Bazelor de Date -

Cornelia TUDORIE 42

Obinerea de informaii asupra tabelelor

Dicionarul de date

USER_USER_xxxxxx

ALL_ALL_xxxxxx

DBA_DBA_xxxxxx

Obiecte aflate n proprietatea utilizatorului

Obiecte care pot fi accesate de utilizator

Obiecte din toat baza de date

Proiectarea Bazelor de Date -Cornelia TUDORIE 43

Obinerea de informaii asupra tabelelor

Views Description

dba_usersdba_sys_privsdba_roles

user_tablesuser_objectsuser_lobsuser_tab_columnsuser_constraints

Informaii generale

Informaii relativ la obiectele utilizatorului:tabele, coloane, restricii, alte obiecte

dictionarydict_columns

DicDicionarul de date : exempleionarul de date : exemple

Informaii de administrare

Proiectarea Bazelor de Date -Cornelia TUDORIE 44

Obinerea de informaii asupra tabelelor

Disponibile pentru utilizator : DICTIONARY USER_USERS USER_TABLES ALL_TABLES USER_VIEWS USER_TAB_COLUMNS SESSION_PRIVS SESSION_ROLES . . . . . . .

12

Proiectarea Bazelor de Date -Cornelia TUDORIE 45

Obinerea de informaii asupra tabelelor

SQL> SELECT * FROM dictionary WHERE table_name LIKE '%TABLE%;

TABLE_NAME COMMENTS---------- ----------------------------------

ALL_ALL_TABLES Description of all object and relational tables accessible to the user

ALL_NESTED_TABLES Description of nested tables in tables accessible to the user

ALL_OBJECT_TABLES Description of all object tables accessible to the user

ALL_TABLES Description of relational tablesaccessible to the user

.

15 rows selected.

SQL> SELECT * FROM dictionary WHERE table_name LIKE '%TABLE%;

TABLE_NAME COMMENTS---------- ----------------------------------

ALL_ALL_TABLES Description of all object and relational tables accessible to the user

ALL_NESTED_TABLES Description of nested tables in tables accessible to the user

ALL_OBJECT_TABLES Description of all object tables accessible to the user

ALL_TABLES Description of relational tablesaccessible to the user

.

15 rows selected.

Exemplu

Proiectarea Bazelor de Date -Cornelia TUDORIE 46

Obinerea de informaii asupra tabelelor

SQL> describe user_tablesName Null? Type------------------------------- -------- ----

TABLE_NAME NOT NULL VARCHAR2(30)TABLESPACE_NAME VARCHAR2(30)PCT_FREE NUMBER

PCT_USED NUMBER

INI_TRANS NUMBER

MAX_TRANS NUMBERINITIAL_EXTENT NUMBER

NEXT_EXTENT NUMBER

MIN_EXTENTS NUMBER

......

SQL> describe user_tablesName Null? Type------------------------------- -------- ----

TABLE_NAME NOT NULL VARCHAR2(30)TABLESPACE_NAME VARCHAR2(30)PCT_FREE NUMBER

PCT_USED NUMBER

INI_TRANS NUMBER

MAX_TRANS NUMBERINITIAL_EXTENT NUMBER

NEXT_EXTENT NUMBER

MIN_EXTENTS NUMBER

......

Exemplu

Proiectarea Bazelor de Date -Cornelia TUDORIE 47

Obinerea de informaii asupra tabelelor

SQL> select table_name,tablespace_name,pct_free,pct_used,2 initial_extent,next_extent from user_tables3 where table_name='PROD';

TABLE_NAME TABLESPACE_NAME PCT_FREE PCT_USED INITIAL_EXTENT NEXT_EXTENT

---------- --------------- --------- --------- -------------- -----------

PROD DATA01 10 40 10240 10240

SQL> select table_name,tablespace_name,pct_free,pct_used,2 initial_extent,next_extent from user_tables3 where table_name='PROD';

TABLE_NAME TABLESPACE_NAME PCT_FREE PCT_USED INITIAL_EXTENT NEXT_EXTENT

---------- --------------- --------- --------- -------------- -----------

PROD DATA01 10 40 10240 10240

Exemplu

Proiectarea Bazelor de Date -Cornelia TUDORIE 48

Obinerea de informaii asupra tabelelor

Comanda ANALYZEComanda ANALYZE genereaz statistici referitoare la stocare, pe baza ntregii tabele, sau a unei poriuni

ANALYZE TABLE [schema.] tabelaCOMPUTE STATISTICS ;

ANALYZE TABLE [schema.] tabelaESTIMATE STATISTICS [ SAMPLE integer { ROWS | PERCENT } ] ;

sau

13

Proiectarea Bazelor de Date -Cornelia TUDORIE 49

Obinerea de informaii asupra tabelelor

SQL> ANALYZE TABLE prod ESTIMATE STATISTICS;

Table analyzed.

SQL> SELECT num_rows, avg_space, sample_size, 2 avg_row_len, chain_cnt3 FROM user_tables WHERE table_name='PROD';

NUM_ROWS AVG_SPACE SAMPLE_SIZE AVG_ROW_LEN CHAIN_CNT--------- --------- ----------- ----------- ---------

9 1718 1064 24 0

SQL> ANALYZE TABLE prod ESTIMATE STATISTICS;

Table analyzed.

SQL> SELECT num_rows, avg_space, sample_size, 2 avg_row_len, chain_cnt3 FROM user_tables WHERE table_name='PROD';

NUM_ROWS AVG_SPACE SAMPLE_SIZE AVG_ROW_LEN CHAIN_CNT--------- --------- ----------- ----------- ---------

9 1718 1064 24 0

Exemplu

Proiectarea Bazelor de Date -Cornelia TUDORIE 50

Comentarii la tabele i coloane

COMMENT ON TABLE tabelaIS 'text' ;

COMMENT ON COLUMN tabela.coloanaIS 'text' ;

sau

Proiectarea Bazelor de Date -Cornelia TUDORIE 51

Comentarii la tabele i coloane

COMMENT TABLE tabelaIS 'text' ;

COMMENT COLUMN tabela.coloanaIS 'text' ;

sau

SQL> COMMENT ON TABLE prod IS 'Nomenclatorul de produse';

Comment created.

SQL> SELECT table_name, comments FROM user_tab_comments2 WHERE table_name='PROD';

TABLE_NAME COMMENTS

---------- -------------------------------------------

PROD Nomenclatorul de produse

SQL> COMMENT ON TABLE prod IS 'Nomenclatorul de produse';

Comment created.

SQL> SELECT table_name, comments FROM user_tab_comments2 WHERE table_name='PROD';

TABLE_NAME COMMENTS

---------- -------------------------------------------

PROD Nomenclatorul de produse

Exemplu

Proiectarea Bazelor de Date -Cornelia TUDORIE 52

Comentarii la tabele i coloane

COMMENT TABLE tabelaIS 'text' ;

COMMENT COLUMN tabela.coloanaIS 'text' ;

sau

SQL> COMMENT ON COLUMN prod.den IS 'Denumirea produsului';

Comment created.SQL> SELECT table_name, column_name, comments 2 FROM user_col_comments WHERE table_name='PROD;

TABLE_NAME COLUMN_NAME COMMENTS

---------- ----------- -----------------------------

PROD CODP

PROD DEN Denumirea produsuluiPROD CULOARE

PROD STOC

PROD PRET

SQL> COMMENT ON COLUMN prod.den IS 'Denumirea produsului';

Comment created.SQL> SELECT table_name, column_name, comments 2 FROM user_col_comments WHERE table_name='PROD;

TABLE_NAME COLUMN_NAME COMMENTS

---------- ----------- -----------------------------

PROD CODP

PROD DEN Denumirea produsuluiPROD CULOARE

PROD STOC

PROD PRET

Exemplu

14

Proiectarea Bazelor de Date -Cornelia TUDORIE 53

Golirea unei tabele

TRUNCATE TABLE [schema.] tabela[ { DROP | REUSE } STORAGE ] ;

Proiectarea Bazelor de Date -Cornelia TUDORIE 54

tergerea unei tabele

DROP TABLE [schema.] tabela[ CASCADE CONSTRAINTS ] ;

Proiectarea Bazelor de Date -Cornelia TUDORIE 55

E. Obiecte ale sistemului de baze de date. Performana n utilizare.

1. Stocarea informaiilor n baza de date2. Obiectele bazei de date:

1. Tabele2. Indeci3. Tabele virtuale4. Sinonime5. Secvene

3. Gestiunea eficient a spaiului de stocare

4. Optimizarea cererilor