Crearea unei baze de date

Limbajul de definire a datelor(DDL)

Instructiunile INSERT, DELETE, UPDATE DESCRISE in prima parte a capitolului, se ocupau de manipularea datelor din baza de date. Acestea formeaza limbajul de manipulare a datelor (DML) ; instructiunile DML pot modifica datele din baza de date dar nu pot modifica structura acesteia. Niciuna dintre aceste instructiuni nu poate sa creeze sau sa stearga tabele sau coloane dintr-o tabela. Modificarea structurii bazei de date este realizata de un alt grup de instructiuni, numit de obicei Limbaj de Definire a Datelor. Utilizand DDL putem :

Sa cream o noua tabela.

Sa stergem o tabela.

Sa modificam structura unei tabele.

Sa definim o tabela virtuala (vedere).

Sa asiguram controlul accesului la baze de date.

Sa creem indecsi, pentru a face accesul la date mai rapid.

Sa controlam parametrii fizici de stocare a datelor.

Partea principala a DDL este formata din trei actiuniSQL :

CREATE, care defineste si creeaza un obiect al bazei de date.

DROP, care sterge un obiect din baza de date.

ALTER, care modifica definitia unui obiect deja existent al bazei de date.

Crearea unei baze de date

Standardul SQL1 precizeaza limbajul utilizat pentru descrierea structurii bazei de date darn u precizeaza cum anume este creata baza de date; fiecare SGBD are o abordare proprie a modului in care creeaza o baza de date. Multe dintre SGBD-urile existe pe piata au o pereche de instuctiuni CREATE DATABASE / DROP DATABASE utilizate pentru crearea si respectiv stergerea unei baze de date.

Definirea tabelelor

Cea mai importanta stuctura intr-o baza de date relationala este tabela.

Crearea unei tabele (CREATE TABLE)

Instructiunea CREATE TABLE defineste o noua tabela in baza de date. Diferitele clauze ale instructiunii precizeaza elementele din definitia unei tabele. Numele tabelei trebuie sa fie un identificator SQL valid si unic. Tabela nou creata este goala dar este pregatita pentru adaugarea de noi linii, utilizand instructiunea INSERT.

Definirea coloanelor

Coloanele tabelei nou create sunt definite in interiorul instructiunii CREATE TABLE. Definitiile coloanelor sunt incluse intr-o lista cuprinsa intre paranteze rotunde, separate prin virgula. Ordinea coloanelor in lista de definitie va determina ordinea coloanelor din tabela respectiva. In instructiunea CREATE TABLE, pentru fiecare coloana putem specifica:

Numele coloanei, care va fi utilizat pentru a ne referi la coloana respectiva din instructiunile SQL. Fiecare coloana din tabela trebuie sa aiba un nume unic in tabela respectiva dar pot exista coloane din tabele diferite care sa aiba acelasi nume.

Tipul de data al coloanei, care va preciza natura datelor care pot fi stocate in coloana respectiva. Unele tipuri de data, ca VARCHAR si DECIMAL necesita informatii suplimentare, cum ar fi lungimea sau numarul de zecimale. Aceste informatii suplimentare sunt cuprinse intre paranteze rotunde care urmeaza cuvantului cheie reprezentand tipul de data.

Daca o coloana contine, obligatoriu, valori. Clauza NOT NULL previne aparitia valorilor nule intr-o coloana. In absenta acesteia, valorile NULL sunt premise.

O valoare implicita pentru coloana. SGBD utilizeaza aceasta valoare cand o instructiune INSERT nu precizeaza o valoare pentru aceasta coloana.

Standardul SQL2 permite cateva clauze suplimentare, ca parte a definitiei unei coloane, care precizeaza daca o coloana contine valori unice, daca este cheie primara sau straina sau poate restrictiona valorile pe care le poate lua aceasta.

Sa se defineasca tabela OFFICES si coloanele acesteia.

Sa se defineasca tabela ORDERS si coloanele acesteia.

Instructiunea CREATE TABLE pentru o tabela data poate varia de la un SGBD la altul deoarece fiecare SGBD suporta propriul sau set de tipuri de date.

Standardul SQL2 permite specificarea unui domeniu, in locul unui tip de data, in interiorul definitiei unei coloane. Domeniul este o colectie de valori valide ale datelor, definita in interiorul bazei de date si careia i se asociaza un nume. Definitia unui domeniu se bazeaza pe tipurile de date suportate de SGBD dar permite specificarea anumitor restrictii ale valorilor permise pentru datele din domeniul respectiv. In exemplul urmator este prezentata definitia unui domeniu si apoi o alta definitie a tabelei ORDERS astfel incat sa utilizeze domeniul definit in prealabil.

SGBD-ul va verifica automat fiecare linie adaugata pentru a se asigura ca numarul de birou corespunzator acesteia este cuprins in intervalul impus. Domeniile sunt utile in cazul in care aceleasi restrictii se aplica mai multor coloane din tabele diferite. In baza de date folosita ca exemplu, numarul biroului apare in tabelele OFFICES si SALESREPS iar domeniul VALID_OFFICE_ID poate fi utilizat pentru definirea coloanelor din amele tabele.

Valori lipsa si valori implicite

Definitia fiecarei coloane dintr-o tabela ii spune SGBD-ului daca este permisa sau nu lipsa valorilor din coloana respectiva, adica daca se permite sau nu pentru coloana respectiva sa contina valoarea NULL. In majoritatea SGBD-urilor, precum si in standardul SQL, in mod implicit este permis coloanelor sa contina valori NULL. Daca o anumita coloana trebuie sa contina, obligatoriu, o valoare valida atunci in definitia acesteia trebuie inclusa clauza NOT NULL. Standardul SQL si multe dintre produsele SQL permit stabilirea unor valori implicite ale coloanelor. Daca o coloana are o valoare implicita aceasta trebuie precizata la definirea coloanei respective. In continuare este prezentat un exemplu de instructiune CREATE TABLE care contine valori implicite pentru anumite coloane.

Definirea tabelei OFFICES, utilizand valori implicite (sintaxa ANSI/ISO).

In cazul utilizarii acestei definitii a tabelei, la inserarea unei noi linii trebuie precizate obligatoriu numai numarul biroului si orasul. Regiunea implicita este Eastern, managerul biroului este Sam Clark (agentul cu numarul 106), vanzarile sunt zero, iar planul de vanzari este NULL.

Definirea cheilor primare si a cheilor straine

In afara de definirea coloanelor unei tabele, in instructiunea CREATE TABLE se poate preciza cheia primara si cheile straine ale tabelei respective. Aceste functionalitati se realizeaza cu ajutorul clauzelor PRIMARY KEY si respective FOREIGN KEY. Aceste functionalitati sunt incluse in specificatiile ANSI/ISO si majoritatea produselor SQL ofera suport pentru ele.

Clauza PRIMARY KEY precizeaza coloana sau coloanele care formeaza cheia primara a tabelei. Reamintim ca o cheie primara identifica in mod unic liniile dintr-o tabela. In mod implicit, coloanele care fac parte din definitia cheii primare au asociate restrictii de tip valoare unica si NOT NULL.

Clauza FOREIGN KEY precizeaza cheile straine dintr-o tabela si relatiile pe care acestea le creeaza cu alte tabele (tabelele parinte). In cadrul acestei clauze se specifica:

Coloana sau coloanele care formeaza cheia straina; toate coloanele care formeaza cheia straina trebuie sa faca parte din tabela care urmeaza a fi creata.

Tabela care este referita de cheia straina. Acesta este tabela parinte din relatie; tabela in curs de definire este tabela copil.

Un nume pentru relatia dintre tabela copil si cea parinte (numele relatiei este optional). Acest nume nu este utilizat in nici o instructiune SQL de manipulare a bazelor de date dar poate apare in mesajele de eroare emise de sistem si este necesar daca dorim sa stergem cheia straina.

Modul in care SGBD-ul trebuie sa trateze valorile NULL din una sau mai multe coloane ale cheii straine.

O regula optionala de stergere (DELETE) in cadrul relatiei (CASCADE, SET NULL, SET DEFAULT sau NO ACTION), care va determina ce actiuni vor fi efectuate la stergerea unei linii din tabela parinte.

O regula optionala de modificare (UPDATE), care determina actiunile efectuate la modificarea valorilor cheii primare din tabela parinte.

O conditie optionala de validare, care sa restrictioneze datele din tabela respectiva astfel incat aceasta sa contina numai linii care satisfac conditia respectiva.

In continuare este prezentat un exemplu de instructiune CREATE TABLE pentru tabela ORDERS, in care sunt include definitii ale cheii primare precum si ale cheilor straine

Figura 5.X ne arata relatiile definite de aceasta instructiune si numele asociate acestora. In general este recomandat sa atribuim nume relatiilor deoarece ele ne ajuta sa intelegem mai bine relatiile create intre tabele prin intermediul cheilor straine. De exemplu, fiecare comanda este efectuata de un client al carui numar apare in coloana CUST a tabelei ORDERS. Relatia creata de aceasta coloana a fost denumita PLACEDBY.

Figura 5.24 Stabilirea relatiilor intre tabele, in cadrul instructiunii CREATE TABLE

Cand SGBD-ul proceseaza instructiunea CREATE TABLE, verifica fiecare definitie    a unei chei straine. SGBD-ul se asigura ca atat cheia straina cat si cheia primara la care aceasta face referire au acelasi numar de coloane iar coloanele respective au acelasi tip de data. Tabela parinte trebuie sa fie deja creata pentru ca aceasta verificare sa reuseasca. Observam ca in cadrul clauzei FOREIGN KEY sunt specificate regulile de stergere si modificare. Daca nu este specificata nici o actiune referentiala, SGBD-ul va considera implicit optiunea NO ACTION.

Daca dorim sa cream doua sau mai multe tabele din cadrul unui ciclu referential (cum sunt tabelele OFFICES si SALESREPS din baza de date utilizata ca exemplu) nu putem include o definitie a cheii straine in prima instructiune CREATE TABLE deoarece tabela referita nu exista inca. Va trebui sa cream mai intai prima tabela fara definitia cheii straine si sa-i adaugam ulterior acestei cheia straina, utilizand instructiunea ALTER TABLE.

Restrictii de tip valoare unica

Standardul SQL2 permite specificarea restrictiilor de tip valoare unica in instructiunea CREATE TABLE, utilizand clauza UNIQUE. Mai jos este prezentata o varianta a instructiunii CREATE TABLE pentru tabela OFFICES care contine o clauza UNIQUE pentru coloana CITY.

Restrictii de validare (check)

Un alt tip de restrictie, check, poate fi specificata in instructiunea CREATE TABLE. O restrictie de validare contine o conditie (similara conditiei de selectie din instructiunea SELECT) care este verificata la fiecare actualizare a tabelei (la oricare operatie INSERT, UPDATE sau DELETE). Daca rezultatul evaluarii conditiei este TRUE atunci actualizarea este permisa; in caz contrar actualizarea este respinsa si este semnalata o eroare. Urmatorul exemplu este o instructiune CREATE TABLE pentru tabela OFFICES, cu o conditie de validare extrem de simpla, care garantaeza ca planul de vanzari al unui birou este mai mare de $0.00.

Optional, putem atribui un nume restrictiei ; acesta va fi utilizat de SGBD atunci cand semnaleaza incalcarea restrictiei respective. Mai jos este prezentat un exemplu de restrictie de validare asociata tabelei SALESREPS care asigura respectarea regulii :  agentilor de vanzari angajati dupa 1 Ianuarie 1988 nu le pot fi stabilite cote de vanzari mai mari de $300.000,00 . Aceasta restrictie poarta numele de QUOTA_CAP.

Stergerea unei tabele (DROP TABLE)

Putem sterge o tabela din baza de date utilizand instructiunea DROP TABLE in care se specifica numele tabelei ce urmeaza a fi stearsa. In urmatorul exemplu, tabela CUSTOMERS este inlocuita cu doua noi tabele, CUST_INFO si ACCOUNT_INFO.

Cand instructiunea DROP TABLE sterge o tabela din baza de date, definitia si continutul acesteia se pierd. Nu exista nici o posibilitate de recuperare. Datorita consecintelor sale, instructiunea DROP TABLE trebuie utilizata cu precautie.

Standardul SQL2 precizeaza ca instructiunea DROP TABLE trebuie sa contina fie o clauza CASCADE fie o clauza RESTRICT care sa precizeze efectul pe care il va avea stergerea tabelei respective asupra altor obiecte ale bazei de date (cum sunt vederile) care depind de aceasta. Majoritatea produselor comerciale SQL accepta instructiunea DROP TABLE fara nici o clauza.

Modificarea definitiei unei tabele (ALTER TABLE)

Modificarea definitiei unei tabele se realizeaza cu ajutorul instructiunii ALTER TABLE. Aceasta permite:

Adaugarea unei coloane intr-o tabela.

Stergerea unei coloane dintr-o tabela.

Modificarea valorii implicite a unei coloane.

Adaugarea sau stergerea cheii primare a unei tabele.

Adaugarea sau stergerea unei chei straine dintr-o tabela.

Adaugarea sau stergerea unei restrictii de tip valoare unica    a unei tabele.

Adaugarea sau stergerea unei restrictii de validare    a unei tabele.

Standardul SQL2 restrictioneaza fiecare instructiune ALTER TABLE la o singura modificare a unei tabele. De exemplu, pentru a adauga o noua coloana si pentru a defini o noua cheie straina sunt necesare doua instructiuni ALTER TABLE. Cateva SGBD-uri permit realizarea mai multor modificari intr-o singura instructiune ALTER TABLE.

Adaugarea unei coloane

Cea mai frecventa utilizare a instructiunii ALTER TABLE este pentru adaugarea unei coloane noi la o tabela deja existenta. Definirea unei coloane in instructiunea ALTER TABLE este similara celei din instructiunea CREATE TABLE si functioneaza in acelasi mod. Noua coloana este adaugata la sfarsitul definitiei tabelei si este situata cel mai la dreapta. Implicit, SGBD-urile asociaza valoarea NULL pentru coloana nou adaugata in toate liniile deja existente. Daca aceasta coloana are asociata o clauza NOT NULL va trebui sa aiba specificata si o valoare implicita. SGBD-ul va adauga, pentru coloana respectiva, valoarea implicita in toate liniile existente.

Sa se aduge un nume de contact si un numar de telefon pentru fiecare client.

Pentru fiecare produs, sa se adauge cantitatea minima permisa.

In primul exemplu, noua coloana va avea valoarea NULL pentru clientii deja existenti. In al doilea exemplu, coloana MIN_QTY va avea valoarea zero ( 0 ) pentru produsele deja existente.

Initial, instructiunea ALTER TABLE nu permitea adaugarea decat a definitiei unei coloane intr-o tabela si semnificatia clauzei ADDera evidenta. Ulterior, s-a permis adaugarea de noi chei primare, chei straine si alte tipuri de restrictii iar clauza ADD pentru aceste tipuri de obiecte trebuie sa precizeze tipul obiectului la care se refera. Pentru compatibilitate cu aceste noi clauze ADD/DROP, standardul SQL2 include, optional, cuvantul cheie COLUMN dupa cuvantul cheie ADD, in cazul adaugarii unei noi coloane. Cu aceasta completare, exemplul precedent devine:

Stergerea unei coloane

Instructiunea ALTER TABLE poate fi utilizata pentru a sterge una sau mai multe coloane dintr-o tabela.

Sa se stearga coloana HIRE_DATE din tabela SALESREPS.

Standardul SQL impune utilizarea unor instructiuni separate ALTER TABLE pentru stergerea mai multor coloane dar unele SGBD-uri permit stergerea mai multor coloane dintr-o tabela intr-o singura instructiune ALTER TABLE.

Mentionam ca stergerea unei coloane poate ridica aceleasi probleme privind integritatea ca si in cazul operatiilor de actualizare. De exemplu, daca stergem o coloana care este cheie primara referita de o cheie straina, atunci coloana reprezentand cheia straina va deveni invalida. Probleme similare apar daca incercam sa stergem o coloana care este referita intr-o clauza check sau este folosita in definitia unei vederi.

Standardul SQL2 trateaza aceste caracteristici in acelasi mod in care trateaza problemele de integritate ridicate de instructiunile DELETE si UPDATE. Putem specifica una din urmatoarele reguli de stergere:

RESTRICT; daca vreunul dintre obiectele bazei de date (chei straine, restrictii etc.) depind de coloana care urmeaza sa fie stearsa atunci instructiunea ALTER TABLE esueaza si coloana nu poate fi stearsa.

CASCADE; toate obiectele din baza de date (chei straine, restrictii etc.) care depind de coloana respectiva vor fi, de asemenea, sterse. Efectul stergerii in cascada poate produce modificari dramatice in baza de date si, de aceea, trebuie utilizat cu precautie.

Se recomanda stergerea explicita a cheilor straine si a celorlalte restrictiilor dependente, prin utilizarea instructiunii DROP corespunzatoare, inainte de stergerea unei coloane.

Modificarea cheilor primare si a cheilor straine

O alta utilizare frecventa a instructiunii ALTER TABLE este pentru a modifica sau a adauga o cheie primara sau o cheie straina la definitia unei tabele.

Spre deosebire de coloanele unei tabele, cheile primare si cheile straine pot fi atat adaugate cat si sterse cu ajutorul instructiunii ALTER TABLE. Clauzele care permit adaugarea unei chei primare sau straine sunt aceleasi ca in cazul instructiunii CREATE TABLE si lucreaza in acelasi mod. Mentionam ca putem sterge o cheie straina numai daca relatiei introduse de aceasta i se asociaza un nume.

Sa se modifice tabela OFFICES astfel incat coloana REGION sa devina cheie straina care refera cheia primara din tabela nou creata REGION.

In continuare este prezentat un exemplu de instructiune ALTER TABLE care modifica o cheie primara. Mentionam ca, in acest caz, trebuie sterse eventualele cheile straine care faceau referire la cheia primara in cauza.

Sa se modifice cheia primara a tabelei OFFICES.

Indecsi (CREATE/DROP INDEX)

Una dintre structurile fizice de stocare furnizate de majoritatea SGBD-urilor bazate pe SQL o constituie indexul. Un index este o structura care permite accesul rapid la liniile unei tabele, pe baza valorilor unei sau mai multor coloane. Figura 5.x ne arata tabela PRODUCTS si doi indecsi care au fost creati pentru aceasta. Unul dintre indecsi permite accesul rapid pe baza valorilor coloanei DESCRIPTION; celalalt permite accesul pe baza valorilor cheii primare, care este constituita din coloanele MFR_ID si PRODUCT_ID.

Figura 5.25 Doi indecsi asociati tabelei PRODUCTS

Indecsii stocheaza valorile datelor si pointeri catre liniile in care apar aceste valori. Intr-un index valorile datelor sunt asezate in ordine crescatoare sau descrescatoare si, in consecinta, SGBD-ul poate gasi repede o anumita valoare. Apoi utilizeaza pointerul asociat acesteia pentru a localiza linia care contine valoarea respectiva. Prezenta sau absenta unui index nu este vizibila utilizatorului care acceseaza o tabela. De exemplu, sa consideram urmatoarea interogare SQL:

Sa se afiseze stocul si pretul produsului Size 4 Widgets.

Instructiunea nu precizeaza daca exista sau nu un index corespunzator coloanei DESCRIPTION. SGBD-ul va procesa interogarea in ambele situatii. Daca nu exista un index corespunzator coloanei DESCRIPTION, SGBD-ul va fi nevoit sa proceseze interogarea prin parcurgerea secventiala a tabelei PRODUCTS, linie cu linie, examinand valoarea coloanei DESCRIPTION din fiecare linie. Pentru a determina toate liniile care satisfac conditia de selectie, va trebui sa examineze toate liniile din tabela. Pentru o tabela cu mii sau milioane de inregistrari scanarea tabelei poate dura cateva minute sau chiar ore.

Prin utilizarea unui index pentru coloana DESCRIPTION, SGBD-ul poate localiza datele cerute cu mult mai putin efort. Va cauta in index, pentru a determina valoarea ceruta ("Size 4 Widget") si apoi va utiliza pointerul asociat acesteia pentru a gasi linia corespunzatoare din tabela. Cautarea intr-un index este foarte rapida deoarece indexul este ordonat iar liniile acestuia sunt foarte mici. Trecerea de la index la liniile corespunzatoare din tabela este, de asemenea, foarte rapida deoarece indexul ii spune SGBD-ului unde sunt localizate pe disc liniile respective.

Asa cum se arata si in acest exemplu, avantajul utilizarii indecsilor consta in cresterea vitezei de executie a instructiunilor SQL care au conditii de selectie care se refera la coloanele dupa care se face indexarea. Unul dintre dezavantajele indecsilor este necesitatea unui spatiu de stocare suplimentar pe disc. Un alt dezavantaj il constituie faptul ca un index trebuie actualizat de fiecare data cand este adaugata o noua linie in tabela sau este actualizata valoarea coloanei indexate dintr-o linie deja existenta. Aceasta impune instructiuni INSERT si UPDATE suplimentare pentru tabela respectiva.

Se recomanda crearea de indecsi corespunzatoare coloanelor care sunt utilizate frecvent in conditii de selectie. De asemenea, se recomanda utilizarea indecsilor in cazul tabelelor care sunt frecvent interogate si nu in cazul tabelelor care sunt frecvent actualizate. Majoritatea SGBD-urilor creeaza automat un index pentru coloanele care alcatuiesc cheia primara a unei tabele.

Pentru baza de date utilizata ca exemplu, in afara indecsilor corespunzatori cheilor primare ale tabelelor, putem defini urmatorii indecsi pentru urmatoarele coloane:

Coloana COMPANY din tabela CUSTOMER, daca datele despre clienti sunt accesate frecvent pe baza denumirilor companiilor.

Coloana NAME din tabela SALESREPS, daca datele referitoare la agentii de vanzari sunt accesate adesea pe baza numelor acestora.

Coloana REP din tabela ORDERS, in cazul in care comenzile sunt accesate frecvent pe baza agentilor de vanzari care le-au primit.

Coloana CUST din tabela ORDERS, in cazul in care comenzile sunt accesate frecvent pe baza clientilor care le-au efectuat.

Coloanele MFR si PRODUCT, impreuna, daca accesarea comenzilor se face adesea pe baza produselor comandate.

Standardul SQL2 nu face precizari asupra indecsilor sau a modului in care sunt acestia creati. Majoritatea sistemelor SQL permit insa crearea indecsilor, prin utilizarea instructiunii CREATE INDEX. Instructiunea permite asocierea unui nume indexului respectiv si precizarea tabelei pentru care este creat acesta. De asemenea, instructiunea precizeaza coloanele dupa care se face indexarea precum si ordinea in care vor fi sortate liniile (ascendent sau descendent). Instructiunea CREATE INDEX poate contine optiunea UNIQUE, care precizeaza daca pentru combinatia de coloane dupa care se face indexarea sunt permise sau nu valorile duplicat.

Urmatorul exemplu creeaza un index pentru tabela ORDERS, pe baza coloanelor MFR si PRODUCT iar aceasta combinatie de coloane trebuie sa contina valori unice pentru fiecare dintre liniile tabelei.

Sa se creeze un index pentru tabela ORDERS.

Instructiunea DROP INDEX sterge un index din baza de date.

Sa se stearga indexul creat in exemplul anterior.