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