INTRODUCERE N BAZE DE DATE RELAIONALE:

20ndrumar de laborator pentru baze de date

21Laboratorul 1: Introducere n baze de date relaionale: Modelul Oracle

1

INTRODUCERE N BAZE DE DATE RELAIONALE: MODELUL ORACLEScopul lucrrii: Pregtirea studenilor pentru realizarea unei baze de date n mediul ORACLE. Se studiaz noiunile de baz ale modelelor de baz relaionale, tipurile de date, crearea tabelelor i constrngerile dintr-o baz de date din mediul ORACLE.1. OBSERVAII TEORETICEModelul de date cel mai vechi care a stat la baza realizrii unor SGBD-uri este modelul ierarhic. Intensiunea sau modelul conceptual al bazei de date ierarhice este reprezentat printr-o diagram numit arbore de definiie ierarhic. Modelului ierarhic i-a urmat modelul reea a crui intensiune este o diagram ce reprezint un graf. Odat cu dezvoltarea tehnicii de calcul a aprut un nou model de date numit model relaional a crui baze au fost puse de cercettorul american E.F. Codd. Acest model st la baza realizrii mai multor SGBD-uri performante.

1.1. BAZE DE DATE RELAIONALEO baz de date (BD) este o colecie de date relaionate. Datele coninute ntr-o baz de date pot fi vizualizate sub forma unor tabele. Deci, o baz de date este construit din una sau mai multe tabele. Tabele sunt matrice bidimensionale, construite din coloane (cmpuri) i linii (nregistrri sau tuple). Baza de date

Tabelele bazei de dateTabelul 1Cmp1

Cmp2

Cmp3

nregistrarea1

nregistrarea2

nregistrarea3

Tabelul 2Cmp1

Cmp2

Cmp3

Cmp4

nregistrarea1

nregistrarea2

Modul de stocare a datelor definete tipul bazei de date. Oracle folosete modelul relaional de baze de date.O baz de date este relaional dac are: structur (tabele accesibile utilizatorilor), operaii (aciuni de manipulare a datelor) i reguli (legi de acces la date i relaii ntre tabele).O baz de date relaional prezint urmtoarele avantaje:

permite interogri utiliznd SQL ; suport accesul simultan la date a mai multor utilizatori ;

structur logic independent de modul de stocare fizic al datelor.

Modelul de bazei de date Oracle nu permite vizualizarea tabelelor bazei de date n mod direct, aa cum o face de exemplu FoxPro. Vizualizarea tabelelor se face prin comenzi SQL de interogare i manipulare a bazei de date. Comenzile SQL se execut cu ajutorul unor utilitare de linie de comand precum SQL*Plus i SQLWorksheet. Rezultatul unei interogri SQL este prezentat sunt forma unui tabel.O baz de date Oracle este o colecie de date fizice, structuri logice de memorie i procese server. O instan a bazei de date Oracle este o parte a memoriei care este alocat pentru rularea bazei de date i a proceselor din fundal. Sistemul de baze de date Oracle este proiectat pe modelul de date relaional i utilizeaz limbajul de interogare SQL pentru regsirea datelor.Modul de stocare a unei baze de date Oracle folosete structuri logice de stocare a bazei de date, diferite i independente de structurile fizice de stocare.Poate cel mai important element al unei baze de date Oracle l constituie Dicionarul de date al bazei de date. Dicionarul de date este creat automat la instalarea bazei de date i ca parte integrant a bazei dedate este actualizat permanent. Acesta cuprinde: obiectele bazei de date (tabele, vederi, indeci, proceduri i funcii etc.), userii i privilegiile acestora, gradul de utilizare a bazei de date i alocarea logic a spaiului de stocare a bazei de date Oracle. Dicionarul bazei de date Oracle (DDO) este read-only i este proprietatea utilizatorului SYS. DDO este compus din: tabele de baz (informaii despre baza de date, accesate direct de mediul Oracle, stocate n format intern, rareori accesate de utilizatori) ; vederi accesibile utilizatorilor (decodifica pentru utilizator informaiile din tabelele de baz. Majoritatea utilizatorilor au acces la aceste vederi i nu la tabelele de baz. Accesul se realizeaz cu comanda all_views).Modelul relaional presupune legturi ntre tabelele bazei de date. Astfel, legturile ntre tabelele unei baze de date pot fi One to Many (1- la mai multe) i Many to Many (mai multe- la mai multe). Aceste legturi sunt date de cmpurile tabelelor ca n exemplul de mai jos.Transformarea unei asocieri sau legturi ntre dou relaii de tipul many to many se despart n dou relaii de tipul one to many:Many to Many: mai muli autori pot edita mai multe cri, One to Many: acelai autor poate scrie mai multe cri, o carte poate include mai muli autori.n cadrul unor tabele exist noiunile de cheie primar (Primary key), cheie strin (Foreign key) i cheie unic (Unique Key). Oracle le numete i constrngeri deoarece impun o anumit manipulare a datelor coninute n tabele. Mai multe despre constrngeri n cadrul discuiei despre SQL.Tabela Cri

Primary Key: carte_id are un cod unicCarte_idNume_cartenrexPre($)

ntreg_11Poezii2520

ntreg_22Poeme3015

ntreg_33Amintiri din copilrie2510

Tabela Autori

Relaii logice stabilite prin Foreign Key: Cartea 3 are ca autori 14 si 12, iar 4 are autorul 3.Carte_idNume_autorAutor_id

ntreg_13Ion14

ntreg_23 Pop12

ntreg_34George3

1.2. ARHITECTURA SIMPLIFICAT A SISTEMULUI ORACLEDei este proiectat pentru servere cu sistem de operare NT sau UNIX, Oracle poate fi instalat i pe calculatoare personale cu o mare varietate de sisteme de operare. Daca lucrai ntr-o reea atunci serverul Oracle va avea instalat Oracle Server i staiile de lucru Oracle Client. Dac lucrai pe un calculator personal atunci trebuie s avei instalat Oracle Personal Server pentru sistemul dvs. de operare. O copie a acestuia o putei obine de la http://www.oracle.com/Arhitectura simplificat: utilitare pentru dezvoltarea de aplicaiiCASE DesignerSQL*PlusDEVELOPER 2000DESIGNER 2000Precompilatoare

CaseAidedSystemEngineering SQL*Plus este un interpretor in linie de comanda, produs de Oracle, care ofer o fereastra de interogare a bazei de date Oracle. SQL*Plus permite interaciunea cu o baza de date prin scrierea de blocuri de cod SQL i PL/SQL.Este folosit pentru aplicaii bazate pe formulare cu acces la baze de date. Cuprinde:Oracle Forms, Oracle Reports, Oracle GraphicsEste folosit pentru modelarea unui model de activitate, analiz i proiectare. Cuprinde:Process ModellerSystem ModellerSystem DesignerGeneratorsRepository

AdministratorPrecompilatoarele C, Cobol, Pascal etc. permit reutilizarea codului din aceste aplicaii si combinarea lui cu cod de interfa pentru serverul Oracle.

Sistemul ORACLEOracle folosete dou structuri de memorie principale:

1. SGA - System Global Area care conine datele i controleaz informaiile pentru o instan Oracle.

2. PGA - Program Global Area care conine datele i controleaz informaiile pentru un proces server.Pornirea bazei de date Oracle:1. Pornirea unei instane (sinonim cu pornirea bazei de date, Oracle aloc o poriune a memoriei partajate - SGA) ;2. Montarea bazei de date (proces de asociere a bazei de date cu o instan pornit anterior) ;3. Deschiderea bazei de date (punerea bazei de date la dispoziia utilizatorilor pentru operaiuni de baz de date).

Oprirea bazei de date Oracle:1. nchiderea bazei de date (scrierea datelor n fiierele de date) ;2. Demontarea bazei de date (disocierea de o instan a bazei de date) ;3. Oprirea bazei de date (eliminarea SGA din memorie).Accesarea bazei de date OracleUn utilizator Oracle este orice persoan care posed un cont utilizator nregistrat n serverul Oracle i creia i-au fost acordate privilegiile necesare accesului la date. Accesarea se face prin aplicaii specifice: SQL*NET (mediu client / server) ; SQL*Plus (mediu de interogare a bazei de date) ; Instrumente Oracle (Developer 2000) ; Instruciuni SQL coninute n alte limbaje (C++, Java).Structuri logice de stocare a bazei de date OracleO baz de date Oracle este format dintr-o structura logic i o structur fizic, independente una fa de cealalt. Separarea celor 2 structuri permite o mai bun gestionare a bazei de date. Structurile logice de stocare dicteaz modul de utilizare al spaiului fizic al bazei de date.Structurile logice sunt formate din : spaii tabel ; segmente ; extinderi ; blocuri ; fiiere de date.Structura fizic conine: fiierele de date ; fiierele de control ; fiierele jurnal.Spaiile tabel sunt uniti logice ale bazei de date Oracle. Datele dintr-o baz de date Oracle sunt stocate logic n spaiul tabel, iar fizic n fiiere de date, asociate spaiului tabel. Se recomand separarea datelor sistem fa de datele utilizatorului. Spaiul tabel System este creat automat i conine: dicionarul de date ;

segmentele de revenire ;

proceduri, declanatori i pachete (uniti PL/SQL).

Un spaiu tabel poate fi: activ (online): accesibil tuturor utilizatorilor ; inactiv (offline): inaccesibil total sau parial.Dezactivarea spaiilor tabel poate avea ca scop: un back-up al bazei de date ;

interzicerea accesului la o parte a bazei de date ; interzicerea accesului la un grup de tabele pt. actualizarea sau modificarea unei aplicaii.Un spaiu tabel dezactivat nu poate fi citit sau manipulat dect de Oracle. Instruciunile SQL de regsire a datelor, care se refer la un spaiu tabel dezactivat nu pot fi executate. Dac exist un segment de revenire care face referire la un spaiu tabel, acesta poate fi dezactivat numai dup ce segmentul a fost rezolvat.Fiierele de date si spaiile tabelFiecare spaiu tabel conine cel puin un fiier fizic de date. Fiierul asociat cu un spaiu tabel conine datele pentru spaiul tabel respectiv. Fiierele de date sunt asociate cu un singur spaiu tabel i o singur baza de date. Fiierul de date iniial este destinat pstrrii spaiului tabel System. Dimensiunea sa iniial este de cel puin 2KB. La crearea spaiului tabel i a fiierului de date acestea sunt goale, ns posed spaiu alocat (se creeaz segmente de date) pentru datele bazei de date. Cnd este creat un segment sau dimensiunea lui crete, Oracle aloc extinderi pentru segmentul respectiv. Un obiect logic de stocare nu corespunde unui anumit fiier de date. Un fiier de date este depozitarul datelor oricrui obiect dintr-un spaiu tabel.Pentru a crea un nou spaiu tabel (privilegiul create tablespace) putei folosi utilitarul SQL*DBA sau putei lansa comanda urmtoare n SQL*Plus:

create tablespace nume_sp_tabeldatafile "/nume_fis_date" size 10Mdefault storage(initial 50knext 40kminextents 10maxextents 50pctincrease 0)offline;

Pentru a aduga un nou fiier de date (privilegiul alter tablespace):alter tablespace nume_sp_tabeladd datafile ''/fis_date_nou' size 5M;Oracle pune la dispoziie o serie de vederi pentru a afla informaii despre structurile logice de stocare: dba_tablespaces, user_tablespaces, dba_data_files, user_extends, user_segments, dba_extends.

Exemplu:

select tablespace_name, initial_extent, next_extent, max_extents, pct_increasefrom sys.dba_tablespaces;

SegmenteUn segment este format din extinderi. Spaiul alocat segmentelor nu este eliberat dect atunci cnd segmentul este abandonat sau trunchiat.

1.3. CREAREA UNEI BAZE DE DATEAtunci cnd se ncepe un proiect pentru o baz de date, prima etap n administrarea datelor va fi ntotdeauna crearea bazei de date. Acest proces poate varia de la elementar la complicat, n funcie de necesitile dumneavoastr i de sistemul de gestiune a bazelor de date pe care l-ai ales. Multe dintre sistemele moderne (inclusiv Personal Oracle) conin instrumente grafice care v permit s construii complet baza de date cu o singur apsare a butonului mouse-ului. Nu vrem s spunem c aceast metod nu este de folos sau c nu v economisete timpul de lucru, dar sunt multe avantaje ale nvrii instruciunilor SQL care se afl n spatele acestor clic-uri cu butonul mouse-ului.

Fiierul script conine programul SQL necesar pentru reconstrucia complet a bazei de date sau a bazelor de date, adesea incluznd toate obiectele din baza de date, cum ar fi indeci, proceduri rezidente i declanatori (triggers). Valoarea acestei operai se va vedea n timpul dezvoltrii aplicaiei, dac se face continuu modificri la baza de date fundamental i cnd se reconstruiete baza de date cu ultimele modificri aduse. Evident, folosirea instrumentelor grafice de fiecare dat cnd este necesar s executai acest proces poate deveni mare consumatoare de timp. Algoritm pentru crearea unei baze de date:

1 Salvarea bazelor existente. Pentru fiecare baz existent trebuie salvate fiierele de iniializare, fiierele de date, fiierele de control i de reluare.2 Crearea unui nou fiier de iniializare. Pornirea unei instane se face utiliznd un fiier de iniializare. Pentru a crea un nou fiier de iniializare se face o copie la un fiier de iniializare deja existent, de exemplu init.ora.3 Editarea noului fiier de iniializare. Copia fiierului trebuie adaptat la noile cerine; pentru aceasta se fac urmtoarele modificri asupra parametrilor din fiier: DB_NAME- nume local al bazei de date ;

DB_DOMAIN - numele logic al domeniului n reea ;

CONTROL_FILES - numele i localizarea fiierelor de control ;

INIT_SQL_FILES - numele fiierelor SQL care vor fi executate n

momentul crerii bazei de date pentru construirea dicionarului de date i a

altor fiiere de iniializare4 Editarea tuturor fiierelor de iniializare existente. Dac mai exist i alte baze de date, trebuie ca fiierele lor de iniializare s fie reactualizate specificnd parametrii CONTROL_FILES, DB_NAME, DB_DOMAIN.5 Oprirea i repornirea sistemului Oracle. Dac o baz de date este deschis, ea trebuie oprit, utiliznd comanda shutdown. Apoi se conecteaz utiliznd privilegiul INTERNAL i se repornete o nou instan, dar fr a deschide baza de date:STARTUP NOMOUNT PFILE=numele_de_initializare.6. Crearea bazei de date. Sintaxa tipic pentru crearea bazei de date este instruciunea CREATE DATABASE, care arat astfel:

CREATE DATABASE nume_baza_de_date; iar modificarea ei se face prin comanda alter database nume_baza_ de_date;

Administratorul bazei de date sau utilizatorii care au privilegiul DBA sunt singurii care pot s porneasc sau s opreasc baza de date.

Proiectarea unei baze de dateProiectarea corect a unei baze de date este extrem de important pentru succesul aplicaiei dumneavoastr, normalizarea este procesul de spargere a datelor dumneavoastr n componente separate n vederea reducerii duplicatelor. Sunt mai multe niveluri de normalizare numite forme de normale (FN) n numr de cinci. Relaia de incluziune ntre formele normale este urmtoarea: FN1FN2FN3FN4FN5Formele normale de la 1 la 3 elimin dependenele funcionale dintre atributele unei scheme de relaie, forma normal 4 elimin dependenele multivalorice dintre atribute iar forma normal 5 elimin dependenele de cuplare dintre atribute. Normalizarea relaiilor poate fi un proces extrem de dificil. Numeroasele instrumente de proiectare a unei baze de date disponibile pe pia permit s se planifice proiectul dup un model logic, ntre factorii care pot influena proiectul unei baze de date, fiind inclui urmtorii:

Securitatea ; Spaiul de disc disponibil ; Viteza de cutare i de regsire a datelor ; Viteza actualizrilor bazei de date ; Viteza de realizare a unor jonciuni multiple n vederea regsirii datelor ; Suportul fiierelor temporare de ctre SGBDR.Evident, spaiul de disc este ntotdeauna un factor ce influeneaz baza de date. Dei poate credei acest lucru, n era stocrilor de ordinul a mai muli giga-octeti, spaiul de disc se poate s nu se afle pe lista consideraiilor, amintii-v c timpul de regsire a nregistrrilor este direct proporional cu dimensiunea bazei de date. Dac vi s-a dat sarcina de proiectare a structurii unei baze de date, avei anse ca multe dintre datele de care dispunei s se repete.

Adesea, poate apare problema invers. Se poate s v fi dorit s normalizai complet proiectul tabelelor cu baza de date i s facei astfel nct s avei creat un grup de tabele.

Dei teoretic v-ai obinuit cu arta proiectrii bazelor de date, adevrul este c orice operaii de interogare efectuate asupra acestei baze de date pot fi executate, dar ntr-o perioad mai lung de timp. Bazele de date proiectate prin aceast metod sunt uneori mai greu de ntreinut, deoarece este foarte greu s ne dm seama ce a vrut s fac proiectantul la momentul respectiv. Acest fapt evideniaz importana documentarii att a programului, ct i a proiectului, astfel nct ali utilizatori care lucreaz dup dumneavoastr (sau lucreaz cu dumneavoastr) pot s-i fac o idee asupra a ceea ce v-ai gndit n timp ce ai creat structura bazei de date. n termenii proiectantului de baze de date, aceast documentaie este cunoscut ca dicionar de date.Crearea unui dicionar de date

Dicionarul de date este cea mai important form de documentaie pentru proiectantul de baze de date. Acest dicionar este folosit pentru:

Descrierea scopului bazei de date si a utilizatorilor ; Realizeaz documentaia bazei de date.

Aceasta poate nsemna oricare dintre urmtoarele specificaii:

Pe ce dispozitiv a fost creat dimensiunea prestabilit a bazei de date sau dimensiunea fiierului jurnal (folosit pentru a memora informaii despre operaiile efectuate cu baze de date n anumite SGBDR) ; Includerea codului surs SQL pentru oricare fiier script de instalare sau dezinstalare a unei baze de date. Aceast operaie include documentaia de utilizare a instrumentelor pentru import/export ; Asigurarea descrierii amnunite a fiecrui tabel din baza de date i a scopurilor acestora n contextul utilizrii lor ; Documentarea structurii interne a fiecrui tabel. Aceasta poate include toate cmpurile i tipurile lor de date, cu comentarii, toi indecii i toate vederile ; Includerea codului surs SQL pentru toate procedurile rezidente i pentru toi declanatorii ; Asigurarea unei descrieri a cerinelor bazei de date, cum ar fi folosirea valorilor unice sau a valorilor NOT NULL. De asemenea, ar trebui menionat dac aceste constrngeri sunt forate la nivelul SGBDR sau dac programatorul de baze de date trebuie s verifice aceste constrngeri n cadrul codului surs.Multe instrumente CASE (Computer Aided Software Engineering - Inginerie software cu ajutorul calculatorului) ajut programatorul n crearea acestui dicionar de date. De exemplu, n pachetul de programe Microsoft Access exist un instrument Database Documentor care permite o descriere detaliat a fiecrui obiect al bazei de date.

1.4. STRUCTURI DE DATE ORACLEn aceast lucrare vom discuta aspectele logice de creare a tabelelor. Consideraiile fizice sunt acoperite n celelalte lucrri. Dei tabelele pot fi create fr a da informaii de aezare fizic, ar trebui notat c un utilizator ORACLE trebuie s fi primit privilegiul de CREATE TABLE de ctre administratorul bazei de date i s aib alocat ceva spaiu de tabel pentru a crea tabele.n general, structurile de date ORACLE pot fi rezumate dup cum urmeaz:

Tabelele pot fi create oricnd, chiar cu utilizatori folosind baza de date ; Nu este necesar s specificai dimensiunea nici unei tabele. Aceasta este definit ultima prin ct spaiu a fost alocat bazei de date ca un ntreg. Oricum, este important s estimai ct de mult spaiu va utiliza o tabel ; Structurile pot fi modificate online ; Tabelele pot capt automat mai mult spaiu dac dimensiunea iniial este umplut.1.4.1. Crearea i Definirea de structuri tabelaren acest paragraf, se studiaz aspectele logice ale tabelelor i coloanelor i comenzile necesare pentru a construi tabele cu constrngeri de integritate. Acesta se realizeaz cu ajutorul subsetului de comenzi SQL cunoscut ca Limbajul de Definire a Datelor (DDL).

1.4.1.1. Crearea cmpurilor cheie

mpreun cu crearea documentaiei pentru proiectul bazei de date, cel mai important obiectiv n proiectarea bazei de date este separarea structurii de tabele astfel nct fiecare tabel s aib o cheie primar i o cheie extern. Cheia primar ar trebui s fie proiectat astfel nct s ndeplineasc urmtoarele cerine:

Fiecare nregistrare este unic n cadrul unui tabel (nici o nregistrare din tabel nu are toate coloanele identice cu alt nregistrare).

Pentru ca o nregistrare s fie unic, este necesar ca toate coloanele s fie unice. (Aceasta nseamn c este ideal ca datele dintr-o coloan s nu se mai repete nicieri n tabel.)Referitor la a dou cerin, coloana care conine date unice n ntregul tabel este cunoscut sub numele de cmpul cheii primare.

Un cmp al cheii externe este un cmp folosit pentru legarea unui tabel de cheia primara sau externa a unui alt tabel.

1.5. LIMBAJUL DE DEFINIRE A DATELOR (LDD)LDD este un subset al comenzilor SQL folosit pentru a crea, modifica sau muta structurile bazei de date ORACLE, i de asemenea s nregistreze informaii n Dicionarul de Date (acesta este discutat mai trziu). 1.5.1 DENUMIREA UNEI TABELENumele pe care-l alegei pentru o tabel trebuie s urmeze regulile standard pentru numirea unui obiect al unei baze de date ORACLE.

Numele trebuie s nceap cu o liter, A-Z sau a-z. Poate conine litere, numerale i caracterele speciale underscore (_). Caracterele $ i # sunt de asemenea legale, dar folosirea lor este descurajat.Numele este acelai indiferent dac sunt folosite litere mari sau mici, de exemplu, ANG, ang, i aNg sunt toate aceeai tabel. Poate fi de maxim 30 caractere n lungime.Numele nu trebuie s duplice numele altui obiect din contul dumneavoastr i de asemeni, nu trebuie s fie un cuvnt rezervat SQL.

NUME

VALID ?

Carte85

da

85Carte

nu;// nu ncepe cu o literGen_literar

da

Gen literar

nu;// conine un blankUPDATE

nu;// cuvnt rezervat SQL

Ar trebui s folosii nume de descriere pentru tabele i alte obiecte ale bazei de date. Folosii acelai nume s descrie aceeai entitate n dou tabele diferite. De exemplu, coloana cu codul crii este numit Carte_ID n ambele tabele, CARTI i AUTORI_CARTI.1.5.2 TIPURI DE DATECnd creai o tabel trebuie s specificai fiecare tip de dat al coloanei. Tabelul de mai jos arat cele mai importante tipuri de date.Tipul de dat poate fi urmat de unul sau mai multe numere n paranteze care dau informaii despre limea coloanei. Limea coloanei determin limea maxim pe care valorile n coloan pot s-o aib. VARCHAR2 trebuie s aib o dimensiune, dar sunt disponibile i cele implicite.Tabela de mai jos arat tipurile de date principale n ORACLE.Tip de date

Descriere

VARCHAR2(w)

ir de caractere de lungime maxim w. Lungimea

maxim este de 2000 caractere.CHAR(w)

ir de lungime fix w. Lungimea implicit este 1.

Lungimea maxim este 255.

NUMBER

Numere in virgula mobila cu precizie : 38 de cifre

semnificative.

NUMBER(w)

Numere ntregi de precizie w.

NUMBER(w, s)

Numere cu precizia w si scala s. Precizia reprezint

numrul maxim de cifre semnificative permise, care

nu pot depi 38. Scala este numrul de poziii

zecimale nregistrate n dreapta punctului.

DATE

Valorile datei din 1 Ianuarie 4712 nainte de Hristos

pana n 31 decembrie 4712 dup Hristos. Informaia

de timp este de asemenea stocat.

LONG

ir de caractere de lungime variabil de lungime

2Gb. Este permis o singur coloan de tipul LONG

pe tabel.

RAW i LONG RAW Echivalent cu VARCHAR2 si respectiv LONG dar

folosit pentru a stoca date binare ca imagini grafice

sau sunete digitizate.

ROWID

Stocheaz un triplet care este constituit din rnd,

blocul de dat, i fiierul de date. Fiecare rnd

dintr-un tabel are asociat un ROWID.

Tipuri de date non-OracleTipurile de date convertite n Oracle

DECIMALNUMBER

INTERGER, INTNUMBER (38)

SMALLINTNUMBER (38)

FLOATNUMBER

REALNUMBER

DOUBLENUMBER

Not: Tipul de date VARCHAR2 din Oracle8i este sinonim cu versiunea a-6-a a tipurilor de date 'VARCHAR' i 'CHAR'.Dac o valoare scris ntr-o coloan depete scala coloanei, atunci va apare o rotunjire.Tabela de mai jos arat exemple de specificaii de coloane:

NUMBER(4)

Poate conine toate numerele pana la 4 cifre.

NUMBER(8, 3)

Poate conine pn la 8 cifre, dintre care 3 pot fi n

dreapta punctului zecimal.

VARCHAR2(1000)

Valorile pot conine pn la 1000 caractere.

CHAR(80)

iruri de caractere de lungime fix egal cu 80.

Valorile mai scurte sunt nlocuite cu blank-uri.

Alte tipuri de date:

VARRAY este un set de variabile puse intr-un sir (vector). Fiecare element are un index care corespunde poziiei lui in vector. Numrul elementelor variaz, si de aceea se numete VARRAY, sau un vector variabil. Pentru a crea un vector trebuie s i declari valoarea maxim, pentru c aceasta poate varia. Pentru a crea un tip vector (array) se folosete sintaxa urmtoare:

Exemplu:CREATE TYPE cost AS ARRAY (20) OF NUMBER (12,2);

Aceasta sintax va crea un vector de 20 de elemente, fiecare fiind un numr de 12 cifre din care dou sunt dup virgul.

REF este de asemenea un tip de dat nou n Oracle 8; el este un pointer spre un obiect (cmp) i este folosit n principal s stocheze identificatorul unui obiect, i s permit dup aceea selectarea acelui obiect.Sintaxa este urmtoarea:

SELECT nume_carte d

FROM carti d

WHERE d.carte_id = 1;

n acest exemplu, d se comport ca o referin ctre carti care s permit accesul mai uor la acel tabel.LOB se refera la un grup mai mare de obiecte;

Sunt diferite tipuri ale tipului LOB:BLOB un cmp nestructurat de date binar; el poate fi un fiier video sau poz ;

CLOB un cmp mare care este constituit din caractere bit (single-byte); acestea pot fi ca un document ;

NCLOB este similar cu CLOB, dar constituit din caractere bit sau multi-bit din setul de caractere naional;

BFILE este un pointer ctre un fiier stocat n afara bazei de date.

Comanda DESCRIBE a SQL*PLUS-ului poate fi folosit pentru a lista detaliile coloanelor create ntr-o tabel:

DESCRIBE CARTI;

1.5.3 CREAREA UNEI TABELE

Creai o nou tabel folosind comanda CREATE TABLE. Una dintre cele mai simple forme a acestei comenzi este cnd informaia de baz pentru fiecare coloan este definit mpreun cu tipul ei de dat i dimensiunea.Sintaxa:

CREATE TABLE nume tabela

(nume coloana tip(dimensiune),

nume coloana tip(dimensiune),

...); 1.5.4 TIPURI DE CONSTRNGERI

Constrngeri de integritateOracle permite constrngerilor de integritate s fie definite pentru tabele i coloanelor s foreze reguli sigure, n interiorul unei tabele sau ntre tabele.

Constrngerile sunt folosite de serverul Oracle: s foreze reguli la nivelul tabelei oricnd este inserat o linie, actualizat sau tears din acea tabel. Constrngerea trebuie s fie satisfcut pentru ca operaiile s reueasc ; pentru a preveni tergerea unei tabele dac sunt posesiuni din alte tabele ; prin unelte sigure Oracle, ca Oracle Forms, pentru a furniza reguli pentru utilizarea ntr-o aplicaie.

Constrngerile sunt clasate dup cum urmeaz:1. Constrngeri de tabel

Acestea pot referi una sau mai multe coloane i sunt definite SEPARAT de definiiile coloanelor din tabel.

2. Constrngeri de coloan

Acestea refer o singur coloan i sunt definite N INTERIORUL specificaiei pentru coloana posesoare.Constrngerile pot fi adugate unei tabele dup crearea ei, i de asemenea temporar dezactivate (vezi comanda ALTER TABLE n capitolul urmtor). Toate detaliile despre constrngeri sunt stocate n Dicionarul de Date. Fiecrei constrngeri i este repartizat un nume. i este mai uor s suplimentezi una tu singur, astfel c poate fi mai uor referit mai trziu, dar dac nu, atunci un nume este generat automat pe forma:

SYS_Cn,

unde n este un numr unic.

Cuvntul cheie CONSTRAINT i permite s numeti o nou constrngere. Putei defini urmtoarele tipuri de constrngeri:

NULL/NOT NULL

UNIQUE

PRIMARY KEY

FOREIGN KEY (integritatea de referin)

CHECK.Constrngerea NOT NULLn exemplul din cadrul desfurrii lucrrii, vei observa c definiiile pentru coloanele Carti_ID i Autori_ID sunt urmate de NOT NULL. Aceasta ne asigur c valorile nule nu sunt permise pentru aceste coloane, de cnd aceste coloane servesc ca i chei pentru operaii pe aceast tabel. Coloanele fr constrngerea NOT NULL permit valori nule.

NOT NULL este una dintre constrngerile de integritate care pot fi definite.

Opiunea DEFAULTUnei coloane i poate fi dat o valoare implicit prin opiunea DEFAULT. Aceasta previne apariia de null-uri (sau erori, dac NOT NULL este specificat) dac o linie este inserat fr o valoare din coloan. Valorile implicite pot fi literali, o expresie, dar nu numele altei coloane. Funcii ca SYSDATE i USER sunt valide.

De exemplu: Data_aparitie DATE DEFAULT SYSDATE,

Pret NUMBER (7, 2) DEFAULT 0

Constrngerea UNIQUEAceasta desemneaz o coloan sau o combinaie de coloane ca o cheie unic. Dou linii n aceeai tabel nu pot avea aceeai valoare pentru aceast cheie. NULL-urile sunt permise dac cheia unic este bazat pe o singura coloan.

Sintaxa constrngerii de tabel:

[CONSTRAINT nume constrangere] UNIQUE (Coloana, Coloana, ...)

Sintaxa constrngerii de coloan:

[CONSTRAINT nume constrangere] UNIQUE

O constrngere unic face ca n ORACLE s se creeze un singur index pentru a mnui regula. Indecii sunt discutai mai trziu.

Constrngere de cheie primarCa i la cheile unice, o cheie primar foreaz unicitatea unei coloane sau combinaii de coloane implicate, i un index unic este creat pentru a conduce la aceasta. Totui, poate fi o singur cheie primar pe o tabel, i aceasta este cunoscut ca fiind cheia definitiv prin care liniile n tabel sunt identificate individual. NULL-urile nu sunt permise n coloanele de chei primare.

Sintaxa constrngerii de tabel:

[CONSTRAINT nume constrangere] PRIMARY KEY (Coloana, Coloana, ...)

Sintaxa constrngerii de coloan:

[CONSTRAINT nume constrangere] PRIMARY KEY

Notai c aceeai combinaie de coloane nu poate fi folosit i pentru o cheie primar i pentru una unic.

Urmtorul exemplu definete Carte_ID ca o cheie primar folosind o constrngere de coloan:

CREATE TABLE CARTI

(Carti_ID NUMBER(3) CONSTRAINT CARTI_PK PRIMARY KEY, ...)

Constrngere de cheie extern

Cheile externe furnizeaz reguli de integritate de referin n interiorul unei tabele sau ntre tabele. O cheie extern este folosit ntr-o relaie cu fiecare cheie primar sau unic, i poate fi folosit, de exemplu, pentru a preveni tergerea unui autor n AUTORI dac autorii exist cu acelai cod de autor n AUTORI_CARTI.

Sintaxa constrngerii de tabel:

[CONSTRAINT nume constrangere] FOREIGN KEY (Coloana, Coloana, ...)

REFERENCES tabela (Coloana, Coloana, ...);

Sintaxa constrngerii de coloan:

[CONSTRAINT nume constrangere] REFERENCES tabela (Coloana);

Not: Cuvntul 'FOREIGN KEY' nu este folosit n versiunea constrngerii de coloan a sintaxei.

Exemplul

AUTORI | (printe)

|

|

/|\ Autori_ID

| AUTORI_CARTI

| (fiu)

Pentru a stabili relaia dintre AUTORI i AUTORI_CARTI se realizeaz o cheie strin cu ajutorul creia se face legtura dintre tabele. Autori_ID este cheia extern n tabela AUTORI_CARTI, i fiecare carte trebuie s aib un numr valid de autor care este cunoscut n tabela AUTORI:

CONSTRAINT FK_Autori_ID FOREIGN KEY (Autori_ID)

REFERENCES AUTORI(Autori_ID);

Opiunea ON DELETE CASCADE

Ca rezultat al constrngerii de tabel de mai sus (care ar fi putut la fel de bine s fie definit ca o constrngere de coloan), un Autor_ID n AUTORI nu ar fi putut fi ters dac existau n AUTORI_CARTI linii cu aceeai valoare Autori_ID. Alternativ, putei cere ca, crile corespunztoare s fie terse automat dac autorul printe n AUTORI este ters. Aceasta este realizat adugnd clauza ON DELETE CASCADE.

CONSTRAINT FK_Autor_ID FOREIGN KEY(Autor_ID)

REFERENCES AUTORI(Autori_ID) ON DELETE CASCADEExemplul

Autori_ID

|

AUTORI_CARTI

\|/

.

.

1(Carte_ID)

Pentru a v asigura c fiecrei linii de carte n CARTI i este dat un numr de cod (CARTE_ID) pentru o carte existent valid:

CREATE TABLE CARTI

(Carti_ID NUMBER(3) PRIMARY KEY, ...);

Constrngerea de verificare (CHECK)Constrngerea CHECK definete explicit o condiie pe care fiecare linie trebuie s o satisfac (sau s o fac necunoscut datorit unui NULL). Condiia poate folosi aceleai construcii ca acelea ntr-o restricie de cerere, cu urmtoarele excepii:

subcererile nu sunt permise ;

referirile la pseudo-coloane ca SYSDATE nu sunt permise.

Sintax:

[CONSTRAINT nume constrangere] CHECK (conditie)

Alte opiuni ale constrngerilorDISABLE

Adugnd DISABLE unei definiii de constrngere nseamn c ORACLE nu o foreaz. Constrngerea poate fi nc citit de uneltele ORACLE pentru a construi reguli ntr-o aplicaie i putei face posibil constrngerea mai trziu prin comanda ALTER TABLE.

CREATE TABLE CARTI ( . . . . . ,

Nume_carte VARCHAR2(20) CONSTRAINT CHK_UPP_NAM CHECK(Nume-

UPPER(Nume_carte))DISABLE,

. . . . . . . ) ;

EXCEPTIONS

Identific o tabel existent, unde este plasat

INTO nume tabela

informaia despre liniile care ncalc constrngerea.

CREATE TABLE CARTI ( . . . . . ,

Nume_carte VARCHAR2(20) CONSTRAINT CHK_UPP_NAM CHECK (Nume-

UPPER (Nume_carte))

EXCEPTIONS INTO CON_VIOLATE,

. . . . . . . ) ;

1.5.5. CREAREA UNEI TABELE CU LINII DIN ALT TABEL

Exist o a doua form a declaraiei CREATE TABLE n care tabela este creat cu linii potrivite, derivate din alta tabel:

CREATE TABLE CARTI

[(nume-coloana , . . . .)]

AS SELECT declaratie Tabela va fi creat cu coloane specificate i linii recuperate din declaraia SELECT inserat.

Numai constrngerile NULL/NOT NULL sunt motenite din tabela selectat.

Dac toate coloanele n declaraia SELECT au nume bine definite (nu sunt expresii .a.m.d.) specificaiile coloanei pot fi omise.

Dac sunt date specificaiile coloanei, atunci numrul de coloane trebuie s fie egal cu numrul de articole din lista SELECT.

3. DESFURAREA LUCRRII3.1. CONECTAREA LA BAZA DE DATE

Analog exemplului de mai jos studentul de la fiecare calculator i realizeaz propria baz de date dintr-un domeniu dorit. Cu acest exemplu se va parcurge desfurarea lucrrilor de laborator urmtoare 2,3,4,5,6. In final studentul va fi pregtit pentru crearea si interogarea unei baze de date n mediul ORACLE. 3.1.1. CREAREA BAZEI DE DATE BIBLIOTEC Pentru o bun nelegere a crerii unei baze de date lum un exemplu dintr-un domeniu cunoscut tuturor Biblioteca. S presupunem c avem urmtoarea baz de date BIBLIOTECA avnd tabelele: CARTI, AUTORI, GEN_LITERAR, CATEGORII_PUBLICATII, EDITURI, IMPRUMUTA.Formatul acestor tabele este ilustrat n Tabelul 1.

Tabelul 1. Structura tabelelor din baza de date BIBLIOTECA

CARTI

Carte_ID

number(3),

Nume

varchar2(30),

Pret

number(7,2),

Editia

number(3),

Data_aparitiei

date;

AUTORIAutori_ID

number(3),

Nume_autori

varchar2(20),

Prenume_autori

varchar2(20);

GEN_LITERAR

Gen_ID

number(3),

Nume_gen

varchar2(20);

CATEGORII_PUBLICATII

Categorie_ID

number(3),

Nume_categorie

varchar2(20);

EDITURI

Editura_ID

number(3),

Nume_editura

varchar2(30),

Adresa

varchar2(75);

IMPRUMUTA

Carte_id

number(3),

Cititor_id

number(3),

Dataim

date,

Dataef

date,

Datares

date; CITITOR

Cod_id

number(3),

Nume_cititor

varchar2(30),Prenume_cititor

varchar2(30),

Varsta

number(3)

Adresa

varchar2(30);

Pentru a vedea structura unui tabel folosii comanda

describe(desc) nume_tabela

exemplu: desc cititor

3.2. CONSTRNGERI

Din examinarea acestor tabele ne punem urmtoarele ntrebri: Care cmpuri sunt chei primare? Care sunt chei externe?

Cheia primar din tabelul CARTI este cmpul Carte_ID. Acest cmp ar trebui s nu fie duplicat deoarece avei numai un singur cod cu aceast carte (n realitate, vei avea probabil un numr de verificare sau o dat calendaristic pentru a face nregistrarea cu adevrat unic, dar acum s presupunem c este valabil cmpul Carte_ID). Cmpul Autori_ID este cheia primar pentru tabelul AUTORI, Categorie_ID este cheie primar pentru tabelul CATEGORII_PUBLICATII, Editura_ID este cheie primar pentru tabelul EDITURI, iar cmpul Gen_ID este cheia primar pentru tabelul GEN_LITERAR.

Cheile externe din acest exemplu sunt, de asemenea, uor de gsit.

Cmpul Gen_ID din tabelul GEN_LITERAR este utilizat pentru jonciunea tabelului GEN_LITERAR cu tabelul CARTI. Cmpul Categorie_ID din tabelul CATEGORII_PUBLICATII este folosit pentru jonciunea tabelului CATEGORII_PUBLICATII cu tabelul CARTI. De asemenea cmpul Cod_ID i cu cmpul Carte_ID se gsesc ntr-un tabel de legtur numit CITITOR_CARTE deoarece un cititor a mprumutat mai multe cri, iar o carte poate fi mprumutat de mai muli cititori. Analog i pentru tabelele CARTI, respectiv EDITURI, unde Carti_ID i Edituri_ID se gsesc ntr-un tabel intermediar numit EDITURI_CARTI.

Pentru a terge o nregistrare din tabelul Autori_Carti trebuie ca, crile corespunztoare s fie terse automat dac cartea printe n CARTI este tears. Pentru a permite acest lucru se definete n sintaxa constrngerii de cheie extern, noiunea ON DELETE CASCADE:CONSTRAINT FK_Autor_ID FOREIGN KEY(Autor_ID)

REFERENCES AUTORI(Autori_ID) ON DELETE CASCADE

n final, notai c tabelele referite ntr-o constrngere trebuie s existe n aceeai baz de date. Dac ele aparin unui utilizator diferit atunci posesorul trebuie specificat ca un prefix.Iat un exemplu complet al construciei tabelei CARTI cu constrngeri:

CREATE TABLE CARTI

(

Carti_ID

NUMBER(3) CONSTRAINT CARTI _PRIM PRIMARY KEY,

Nume

VARCHAR2(20) CONSTRAINT Nume_CONS

CHECK(Nume-UPPER(Nume)),

Pret

NUMBER (7,2),

Data_aparitieDATE DEFAULT SYSDATE,

Editia

NUMBER(3),

Gen_ID

NUMBER(3) CONSTRAINT Gen_FK REFERENCES

GEN_LITERAR(Gen_ID),

Categorie_IDNUMBER(3) CONSTRAINT Categ_FK REFERENCES

CATEGORII_PUBLICATII(Categorie_ID) );

Not:

Numele coloanelor ntr-o tabel trebuie sa fie unice. Cheile strine se creeaz numai dup ce s-au definit cheile primare.

Un alt exemplu, pentru a v asigura c nu sunt dou nume de cri identice la o singur locaie:

CREATE TABLE CARTI

(Carte_ID NUMBER,

Nume_Carte VARCHAR2(30),

Pret NUMBER(7,2),

CONSTRAINT UNQ_CARTE_ID UNIQUE(Nume_carte,carte_id)

)

n exemplul de mai sus, constrngerea UNQ_CARTE_ID este o constrngere de tabel.

Not: virgula precede detaliile.3.3. CREAREA UNEI TABELE CU LINII DIN ALT TABELPentru a crea o tabel EDITURI care ine codul editurilor, numele, adresa editurilor, introducei:CREATE TABLE EDITURI2

AS SELECT Editura_ID, Nume_editura,Adresa

FROM EDITURI

WHERE Editura_ID = 2;

Table created.

Pentru a vedea descrierea lui EDITURI2, introducei:

DESC EDITURI2 ;

Pentru a crea o tabel innd numele editurii, adresa i alte detalii, introducei:

CREATE TABLE ED_DET (Nume, Adresa, Oferta)

AS SELECT Ed_Nume, Adresa, Oferta

FROM EDITURIWHERE EDITURI.OFERTA BETWEEN MinPret AND MaximPret;

Table created.

DESC ED_DET;

Pentru a afia coninutul tabelei ED_DET, introducei:

SELECT * FROM ED_DET;

Nota: cmpul Oferta va trebui creat n tabelul EDITURI4. NTREBRI I EXERCIIII. Definii noiunea de constrngere. Dai exemple de tipuri de constrngeri. Care este importana folosirii constrngerii?II. Proiectai o baz de date n care s respectai constrngerile de integritate i tipurile de date.Trebuie avut n vedere:

1. Crearea tabelelor cu tipurile de date respective ;2. Tipurile de constrngeri menionate mai sus ;3. ncercai s obinei accesul la tabelele altui utilizator ;4. Artai ce tabele sunt la ceilali utilizatori ;5. Copiai structura i coninutul unui tabel care aparine unui utilizator ;

6. Creai un dicionar de date personal pe care s-l completai pe parcursul laboratorului.Not: baza de date pe care o alegei poate fi una din opiunile enumerate:

o baz de date despre componente de calculatoare (n care s specificai numele de componente, compatibilitatea, sisteme de plat etc.) ; o baz de date despre componente electronice (parametrii componentei respective, link-ul de la care poate fi gsit documentaia despre componenta respectiv etc.) ; o baz de date n care s inei evidenta angajailor dintr-o firm (numele angajatului, departamentul n care lucreaz, mod de plat, funcia, proiecte realizate etc.) ; o baz de date despre o magazie de CD (numele artitilor, piesele, genul de muzic etc).

_1138861133.unknown

_1138861176.unknown