CAPITOLUL 12

Crearea si Definirea de structuri tabelare

In acest capitol, discutam aspectele logice ale tabelelor si coloanelor si commenzile necesare pentru a construi tabele cu Constrangeri de Integri- tate. Acesta este primul din doua capitole care acopera subsetul comenzilor SQL cunoscut ca Limbajul de Definire a Datelor(DDL).

Structuri de date ORACLE

In acest curs vom discuta aspectele logice de creare a tabelelor.Consi- deratiile fizice sunt acoperite in celelalte cursuri.Desi tabelele pot fi create fara a da informatii de asezare fizice, ar trebui notat ca un utili- zator ORACLE trebuie sa fi primit privilegiul de CREATE TABLE de catre admi- nistratorul bazei de date si sa aiba alocat ceva spatiu de tabela pentru a crea tabele.

In general, structurile de date ORACLE pot fi rezumate dupa cum urmeaza.

Tabelele pot fi create oricand, chiar cu utilizatori folosind baza de date.
Nu este necesar sa specificati dimensiunea nici unei tabele. Aceasta este definita ultima prin cat spatiu a fost alocat bazei de date ca un intreg. Oricum, este important sa estimati cat de mult spatiu va utiliza o tabela.
Structurile pot fi modificate online.
Tabelele pot capata automat mai mult spatiu daca dimensiunea i- nitiala este umpluta.
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, si deasemenea sa inregistreze informatii in Dictionarul de Date (acesta este discutat mai tarziu).
Denumirea unei tabele

Numele pe care-l alegeti pentru o tabela trebuie sa urmeze regulile standard pentru numirea unui obiect al unei baze de date ORACLE.
1. Numele trebuie sa inceapa cu o litera, A-Z sau a-z.
2. Poate contine litere, numerale si caracterele speciale underscore (_). Caracterele $ si # sunt deasemenea legale, dar folosirea lor este descurajata.
3. Numele este acelasi indiferent daca sunt folosite litere mari sau mici, de exemplu, EMP, emp, si eMp sunt toate aceeasi tabela.
4. Poate fi de maxim 30 caractere in lungime.
5. Numele nu trebuie sa duplice numele altui obiect din contul dumneavoastra.
6. Numele nu trebuie sa fie un cuvant rezervat SQL.

    NUME                       VALID ?
    ----                       -------

    EMP85                       da

    85EMP                       nu; nu incepe cu o litera

    FIXED_ASSETS                da

    FIXED ASSETS                nu; contine un blank

    UP DATE                     nu; cuvant rezervat SQL

Ar trebui sa folositi nume de descriere pentru tabele si alte obiecte ale bazei de date. Folositi acelasi nume sa descrie aceeasi entitate in doua tabele diferite. De exemplu, coloana cu numarul departamentului este numita DEPTNO in ambele EMP si DEPT.

Crearea unei tabele

Creati o noua tabela folosind comanda CREATE TABLE. Una dintre cele mai simple forme a acestei comenzi este cand informatia de baza pentru fiecare coloana este definita impreuna cu tipul ei de data si dimensiunea.

  Sintaxa: CREATE TABLE nume tabela
           (nume coloana tip(dimensiune),
            nume coloana tip(dimensiune),
            ...);

  Exemplu: CREATE TABLE DEPT
           (DEPTNO NUMBER(2),
           DNAME   VARCHAR2(12),
           LOC     VARCHAR2(12));

Numele coloanelor intr-o tabela trebuie sa fie unice.

Tipurile coloanelor

Cand creati o tabela trebuie sa specificati fiecare tip de data al co- loanei. Tabela de mai jos arata cele mai importante tipuri de date.

Tipul de data poate fi urmat de unul sau mai multe numere in paranteze care dau informatii despre latimea coloanei. Latimea coloanei determina la- timea maxima pe care valorile in coloana pot s-o aiba. VARCHAR2 trebuie sa aiba o dimensiune, dar cele implicite sunt disponibile.

Tabela de mai jos arata tipurile de date principale in ORACLE7.

   Tip de date                        Descriere
   -----------                        ---------

   VARCHAR2(w)                    Sir de caractere de lungime maxim w. Lungi-
                                  mea maxima este de 2000 caractere.

   CHAR(w)                        Sir de lungime fixa w. Lungimea implicita
                                  este 1. Lungimea maxima este 255.

   NUMBER                         Numere in virgula mobila cu precizie : 38
                                  de cifre semnificative.

   NUMBER(w)                      Numere intregi de precizie w.

   NUMBER(w,s)                    Numere cu precizia w si scala s. Precizia
                                  reprezinta numarul maxim de cifre semnifi-
                                  cative permise care nu pot depasi 38. Scala
                                  este numarul de pozitii zecimale inregistra-
                                  te in dreapta punctului.

   DATE                           Valorile datei din 1 Ianuarie 4712 inainte
                                  de Hristos pana in 31 Decembrie 4712 dupa
                                  Hristos. Informatia de timp este deasemenea
                                  stocata.

   LONG                           Sir de caractere de lungime variabila de
                                  lungime 2 Gb, sau 2 la puterea 31 minus 1.
                                  Este permisa o singura coloana de tipul LONG
                                  pe tabela.

   RAW si                         Echivalent cu VARCHAR2 si respectiv LONG,
   LONG RAW                       dar folosit pentru a stoca date binare ca
                                  imagini grafice sau sunete digitizate.

NOTE:

Tipul de date VARCHAR2 al Oracle7 este sinonim cu versiunea 6 a tipu- rilor de date 'VARCHAR' si 'CHAR'.
ORACLE foloseste semantica prin comparatie non-padded daca o valoare VARCHAR2 este folosita intr-o comparatie(vezi Capitolul 2 pentru de- talii).
Daca o valoare scrisa intr-o coloana depaseste scala coloanei, atunci va apare o rotunjire.

Tabela de mai jos arata exemple de specificatii de coloane:

  NUMBER(4)                    Poate contine toate numerele pana la 4 cifre.

  NUMBER(8,3)                  Poate contine pana la 8 cifre, dintre care 3
                               pot fi in dreapta punctului zecimal.

  VARCHAR2(1000)               Valorile pot contine pana la 1000 caractere.

  CHAR(80)                     Siruri de caractere de lungime fixa egala cu
                               80. Valorile mai scurte sunt inlocuite cu
                               blank-uri.

Crearea tabelei EMP

Tabela noastra demonstrativa, EMP, ar fi putut fi creata de o comanda CREATE TABLE ca mai jos:


      CREATE TABLE EMP
      (EMPNO       NUMBER(4) NOT NULL,
      ENAME        VARCHAR2(10),
      JOB          VARCHAR2(10),
      MGR          NUMBER(4),
      HIREDATE     DATE,
      SAL          NUMBER (7,2),
      COMM         NUMBER (7,2),
      DEPTNO       NUMBER (2) NOT NULL);

Comanda DESCRIBE a SQL*PLUS-ului poate fi folosita pentru a lista detal ale coloanelor create intr-o tabela:


                                    DESCRIBE EMP

Constrangerea NOT NULL

In exemplul de mai sus, veti observa ca definitiile pentru coloanele EMPNO si DEPTNO sunt urmate de NOT NULL. Aceasta ne asigura ca valoril nule sunt permise pentru aceste coloane, de cand aceste coloane servesc ca chei pentru operatii pe aceasta tabela. Coloanele fara constrangerea NOT NULL permit valori nule.

NOT NULL este una dintre constrangerile de integritate care pot fi defi- nite.

OPTIUNEA DEFAULT

Unei coloane ii poate fi data o valoare implicita prin optiunea DEFAULT. Aceasta previne aparitia de null-uri (sau erori, daca NOT NULL este specifi- cata) daca o linie este inserata fara o valoare din coloana.Valorile implici- te pot fi literali, o expresie, dar nu numele altei coloane. Functii ca SYSDATE si USER sunt valide.

De exemplu:


         HIREDATE DATE DEFAULT SYSDATE,
         SAL NUMBER (7,2) DEFAULT 0

Constrangeri de integritate

Oracle permite constrangerilor de integritate sa fie definite pentru ta- bele si coloanelor sa forteze reguli sigure, inauntrul unei tabele sau intre tabele.

Constrangerile sunt folosite:

de serverul Oracle7 sa forteze reguli la nivelul tabelei oricand este inserata o linie, actualizata sau stearsa din acea tabela. Constrange- rea trebuie sa fie satisfacuta pentru ca operatiile sa reuseasca.
pentru a preveni stergerea unei tabele daca sunt posesiuni din alte tabele.
prin unelte sigure Oracle, ca Oracle Forms, pentru a furniza reguli pentru utilizarea intr-o aplicatie.

Constrangerile sunt clasate dupa cum urmeaza:

Constrangeri de tabela
Acestea pot referi una sau mai multe coloane si sunt definite SEPARAT de definitiile coloanelor din tabela.: Constrangeri de coloana
Acestea refera o singura coloana si sunt definite INAUNTRUL specificatiei pentru coloana posesoare.

Constrangerile pot fi adaugate unei tabele dupa crearea ei si deasemenea temporar dezactivate (vezi comanda ALTER TABLE in capitolul urmator).Toate detaliile despre constrangeri sunt stocate in Dictionarul de Date. Fiecarei constrangeri ii este repartizat un nume. Iti este mai usor sa suplimentezi una tu singur, astfel ca poate fi mai usor referita mao tarziu, dar daca nu, atunci un nume este generat automat pe forma:

        SYS_Cn

unde n este un numar unic. Cuvantul cheie CONSTRAINT iti permite sa numesti o noua constrangere tu insuti.

Tipuri de constrangeri

Puteti defini urmatoarele tipuri de constrangeri:

NULL/NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY(integritatea de referinta)
CHECK

Constrangerea UNIQUE

Aceasta desemneaza o coloana sau o combinatie de coloane ca o cheie uni- ca.Doua linii in aceeasi tabela nu pot avea aceeasi valoare pentru aceasta cheie.NULL-urile sunt permise daca cheia unica este bazata pe o singura co- loana.

Sintaxa constrangerii de tabela :

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

Sintaxa constrangerii de coloana :

       [CONSTRAINT nume constrangere] UNIQUE

De exemplu, pentru a va asigura ca nu sunt 2 nume de departamente identice la o singura locatie:


       CREATE TABLE DEPT
       (DEPTNO NUMBER, DNAME VARCHAR2(9),
       LOC VARCHAR2(10),
       CONSTRAINT UNQ_DEPT_LOC UNIQUE(DNAME,LOC))

In exemplul de mai sus, constrangerea UNQ_DEPT_LOC este o constrangere de ta- bela. Notati ca virgula precede detaliile. O constrangere unica il face pe ORACLE sa creeze un singur index pentru a manui regula. Indecsii sunt discu- tati mai tarziu.

Constrangere de cheie primara

Ca si la cheile unice, o cheie primara forteaza unicitatea unei coloane sau combinatii de coloane implicate si un index unic este creat pentru a con- duce aceasta. Totusi poate fi o singura cheie primara pe o tabela, si aceasta este cunoscuta ca fiind cheia definitiva prin care liniile in tabela sunt i- dentificate individul. NULL-urile nu sunt permise in coloanele de chei prima- re.

Sintaxa constrangerii de tabela :

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

Sintaxa constrangerii de coloana :

       [CONSTRAINT nume constrangere] PRIMARY KEY

Notati ca aceeasi combinatie de coloane nu poate fi folosita si pentru o cheie primara si pentru una unica. Urmatorul exempludefineste DEPTNO ca o cheie primara folosind o constrangere de coloana:


         CREATE TABLE DEPT
         (DEPTNO NUMBER(2) CONSTRAINT DEPT_PRIM PRIMARY KEY, ...)

Constrangere de cheie externa

Cheile externe furnizeaza reguli de integritate de referinta inauntrul unei tabele sau intre tabele. O cheie exeterna este folosita intr-o relatie cu fiecare cheie primara sau unica oriunde si poate fi folosita, de exemplu, pentru a preveni stergerea unui departament in DEPT daca angajatii exista cu acelasi numar de departament in EMP.

Sintaxa constrangerii de tabela :

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

Sintaxa constrangerii de coloana :

       [CONSTRAINT nume constrangere] REFERENCES tabela (Coloana)

Notati ca, cuvintele 'FOREIGN KEY' nu sunt folosite versiunea constrangerii de coloana a sintaxei.

                      ------------
  Exemplul 1          |   DEPT   |  (parinte)
                      ------------
                           |
                           |
                          /|\    DEPTNO
                      ------------
                      |   EMP    |   (fiu)
                      ------------

Pentru a stabili relatia dintre EMP si DEPT astfel incat EMP.DEPTNO este che- ia externa, si fiecare angajat trebuie sa aiba un numar valid de departament care este cunoscut in DEPT:


     CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)
     REFERENCES DEPT(DEPTNO)

Optiunea ON DELETE CASCADE

Ca rezultat al constrangerii de tabela de mai sus, (care ar fi putut la fel de bine sa fie definita ca o constrangere de coloana), un departament in DEPT nu ar fi putut fi sters daca liniile exista in EMP cu aceeasi valoare DEPTNO. Alternativ, puteti cere ca angajatii corespunzatori sa fie stersi a- utomat daca departamentul parinte in DEPT este sters. Aceasta este realizata adaugand clauza ON DELETE CASCADE.


     CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)
     REFERENCES DEPT(DEPTNO) ON DELETE CASCADE



 Exemplul 2          --------------   EMPNO
                     |    EMP     | . .
                     --------------     .
                           \|/           .
                            .            .
                             .          .
                      MGR     .       .
                                .  .

 Pentru a va asigura ca fiecarei linii de angajat in EMP ii este dat un numar
 de manager (MGR) pentru un angajat existent valid:

        CREATE TABLE EMP
        (EMPNO NUMBER(4) PRIMARY KEY, ...
        MGR NUMBER(4) CONSTRAINT EMP_MGR
        REFERENCES EMP(EMPNO), ...


               Constrangerea de verificare (CHECK)

      Constrangerea CHECK defineste explicit o conditie pe care fiecare linie
 trebuie sa o satisfaca(sau sa o faca necunoscuta datorita unui NULL). Condi-
 tia poate folosi aceleasi constructii ca acelea intr-o restrictie de cerere,
 cu urmatoarele exceptii:
 

         subcererile nu sunt permise
        
 referirile la pseudo-coloane ca SYSDATE nu sunt permise
 

 Sintaxa:
          [CONSTRAINT nume constrangere] CHECK (conditie)


          Alte optiuni ale constrangerilor
  DISABLE                Adaugand DISABLE unei definitii de constrangere
                         inseamna ca ORACLE nu o forteaza. Constrangerea poate
                         fi inca citita de uneltele ORACLE pentru a construi
                         reguli intr-o aplicatie si puteti face posibila con-
                         strangerea mai tarziu prin comanda ALTER TABLE.
     
     CREATE TABLE EMP
     ( . . . . . ,
     ENAME VARCHAR2(10) CONSTRAINT CHK_UPP_NAM CHECK(ENAME-UPPER(ENAME))
     DISABLE,
     . . . . . . . ) ;
    
  EXCEPTIONS             Identifica o tabela existenta unde este plasata
  INTO nume tabela       informatia despre liniile care incalca constrangerea.
   
   CREATE TABLE EMP
     ( . . . . . ,
     ENAME VARCHAR2(10) CONSTRAINT CHK_UPP_NAM CHECK(ENAME-UPPER(ENAME))
     EXCEPTIONS INTO CON_VIOLATE,
     . . . . . . . ) ;
   


 In final, notati ca tabelele referite intr-o constrangere trebuie sa existe
 in aceeasi baza de date. Daca ele apartin unui utilizator diferit atunci po-
 sesorul trebuie specificat ca un prefix.

 Iata un exemplu complet al constructiei tabelei EMP cu constrangeri :

   CREATE TABLE EMP

   (EMPNO NUMBER(4)    CONSTRAINT EMP_PRIM PRIMARY KEY,

   ENAME VARCHAR2(10)  CONSTRAINT ENAME_CONS
                       CHECK(ENAME=UPPER(ENAME)),

   JOB VARCHAR2(10),

   MGR NUMBER(4)       CONSTRAINT EMP_MGR
                       REFERENCES EMP(EMPNO),

   HIREDATE DATE DEFAULT SYSDATE,

   SAL NUMBER(7,2)     CONSTRAINT SAL_CONS
                       NOT NULL,

   COMM NUMBER(7,2),

   DEPTNO NUMBER(2)    CONSTRAINT DEPTNO_CONS
                       NOT NULL,

   CONSTRAINT          EMP_DEPT FOREIGN KEY (DEPTNO)
                       REFERENCES DEPT(DEPTNO))


       Crearea unei tabele cu linii din alta tabela

      Exista o a doua forma a declaratiei CREATE TABLE in care tabela este
 creata cu linii potrivite, derivate din alta tabela:
        CREATE TABLE DEPT
        [(nume-coloana ,. . . .)]
        AS SELECT declaratie


       Tabela va fi creata cu coloane specificate si linii recuperate din de-
        claratia SELECT inserata.
      
 Numai constrangerile NULL/NOT NULL sunt mostenite din tabela selecta-
        ta.
      
 Daca toata coloanele in declaratia SELECT au nume bine definite (nu
        sunt expresii s.a.m.d.) specificatiile coloanei pot fi omise.
      
 Daca sunt date specificatiile coloanei, atunci numarul de coloane tre-
        buie sa fie egal cu numarul de articole in lista SELECT.

 Pentru a crea o tabela DEPT30 care tine numerele angajatilor, nume, job-uri
 si salariile angajatilor din departamentul 30, introduceti:

      CREATE TABLE DEPT30
      AS
      SELECT       EMPNO,ENAME,JOB,SAL
      FROM         EMP
      WHERE        DEPTNO = 30;

      Table created.

 Pentru a vedea descrierea lui DEPT30, introduceti:

     DESC DEPT30



 Pentru a crea o tabela tinand numele angajatului, salariul si detalii de
 grad, introduceti:

    CREATE TABLE EMP_SALS
    (NAME,SALARY,GRADE)
    AS
    SELECT       ENAME, SAL, GRADE
    FROM         EMP, SALGRADE
    WHERE        EMP.SAL BETWEEN LOSAL AND HISAL;

    Table created.

    DESC EMP_SALS;


 Pentru a afisa continutul tabelei EMP SALS, introduceti:

         SELECT      *
         FROM        EMP_SALS;
,/pre>


 CAPITOLUL 12   Exercitii - Crearea de tabele si de constrangeri

 Atelier

  Creati o tabela numita PROJECTS, cu coloanele specificate ca mai jos.Pe
    langa aceasta , definiti PROJID ca, coloana de CHEIE PRIMARA , si asigura-
    ti-va ca datele P_END_DATE nu sunt mai recente decat datele P_START_DATE.


 
 Creati o a doua tabela, ASSIGNMENTS, ca cea de mai jos. Definiti-i coloana
    PROJID ca o cheie externa care refera tabela PROJECTS. Coloana EMPNO a ta-
    belei dumneavoastra este o viitoare cheie externa a lui EMP. Aceste doua
    coloane nu ar trebui sa permita valori NULL (PROJID si EMPNO).


    Sunt alte constrangeri pe care ar trebui sa le angajam in aceste tabele.
    Acestea vor fi adaugate intr-un viitor atelier.

 
 Folositi comanda DESCRIBE pentru a verifica definitiile coloanelor. (Ne
    vom uita la constrangeri mai tarziu).



 CAPITOLUL 12 -Solutii

 Fiecare din constrangerile aratate mai jos ar fi putut fi definite ca orica-
 re constrangere de tabela sau constrangere de coloana.

  
      CREATE TABLE PROJECTS
      ( PROJID           NUMBER(4) CONSTRAINT PROJ_PRIM PRIMARY KEY,
      P_DESC             VARCHAR2(20),
      P_START_DATE       DATE,
      P_END_DATE         DATE,
      BUDGET_AMOUNT      NUMBER(7,2),
      MAX_NO_STAFF       NUMBER(2),
      CONSTRAINT  P_DATE_RULE CHECK(P_START_DATE <= P_END_DATE))
      
 
 
        CREATE TABLE ASSIGNMENTS
      (PROJID            NUMBER(4) NOT NULL REFERENCES PROJECTS (PROJID),
      EMPNO              NUMBER(4) NOT NULL REFERENCES EMP (EMPNO),
      A_START_DATE       DATE,
      A_END_DATE         DATE,
      BILL_RATE          NUMBER(4,2),
      ASSIGN_TYPE        VARCHAR2(2))

CAPITOLUL 12

Crearea si Definirea de structuri tabelare

Structuri de date ORACLE

Limbajul de Definire a Datelor (LDD)

Denumirea unei tabele

Crearea unei tabele

Tipurile coloanelor

NOTE:

Crearea tabelei EMP

Constrangerea NOT NULL

OPTIUNEA DEFAULT

Constrangeri de integritate

Tipuri de constrangeri

Constrangerea UNIQUE

Constrangere de cheie primara

Constrangere de cheie externa

Optiunea ON DELETE CASCADE

Constrangerea de verificare (CHECK)

Alte optiuni ale constrangerilor

Crearea unei tabele cu linii din alta tabela

CAPITOLUL 12 Exercitii - Crearea de tabele si de constrangeri

Atelier

CAPITOLUL 12 -Solutii