LIMBAJ DE DEFINIRE A DATELOR

SQL (Structured Query Language) in versiunea initiala System R.1970 este un limbaj standardizat (ANSI-1992) de manipulare de date si cuprinde instructiuni ce pot fi impartite in urmatoarele grupe:

DDL (Data Definition Language) - grup de instructiuni SQL care formeaza limbajul de definire a datelor, adica a schemelor de relatii, a schemelor de relatii virtuale, a integritatii si a autorizarilor;

DML (Data Manipulation Language) - grup de instructiuni SQL care formeaza limbajul de manipulare interactiva a datelor si care permite inserari, modificari si stergeri;

QL (Query Language)- grup de instructiuni SQL care compun limbajul de interogare ce ne permite sa exprimam cererile in general prIn cuvantul SELECT;

DCL (Data Control Language) - grup de instuctini de comanda si control (de exemplu EXIT care inchide o sesiune de lucru,COMITT si ROLBACK de terminare a tranzactiilor).

Concepte de baza

Vom descrie SQL limbajul de definire a datelor si modul in care definitiile sunt generate de sistem. Ne vom limita la functiile de definire a schemelor conceptuale si fizice (numite interne). Definitia schemelor externe (view) este abordata in capitolul 1. Limbajele de definire a schemelor conceptuale si interne pot fi separate sau cuprinse intr -un limbaj uniform. Definitia connceptuala a datelor este determinata de modelul care sta la baza SGBD-ului. Datele conceptuale pot fi definite cu ajutorul urmatoarelor enitati: atribute, domenii, tupluri, relatii, ierarhii de relatii si baze de date. in definitia relatiei este inclusa definitia schemei (listei de atribute) si a restrictiilor de integritate.

Reamintim ca  schema bazei de date descrie relatiile, atributele care le compun, domeniile in care atributele iau valori, restrictiile de integritate, drepturile de utilizare a relatiilor si a schemelor virtuale si a detaliilor relative la implementarea fizica a relatiilor.

Limbajul de definire a datelor (DDL) cuprinde instructiuni ce permit realizarea urmatoarelor actiuni:

crearea, adaugarea si stergerea de scheme de relatii;

adaugarea si stergerea de atribute n/din shemele de relatii ale BD;

crearea, adaugarea si stergerea de restrictii de utilizare a structurilor de date si de integritate;

definirea structurilor logice si fizice ale BD.

O baza de date relationala de tipul SQL contine urmatoarele obiecte: tabele(relatii), relatii virtuale(view), sinonime (nume alternative ale unui tabel sau view), indecsi, (utilizati in optimizarea accesului), cataloage(care contIn relatiile dintr-o BD) etc. Comenzile SQL formeaza un sistem de gestiune a BD. Luam in continuare comenziile pentru definire si actualizare a fiecarui tip de structura.

Create database

Aceasta instructiune creaza o baza de date care devine BD curenta. Informix OL creaza un tabel catalog ce contine dictionarul de date, care descrie structura BD intr-un dbspace specificat. Daca acesta nu este specificat, atunci acesta se creaza in dbspace-ul radacina. Instructiunea are forma urmatoare:

CREATE DATABASE nume_baza [in dbspace]

[WITH [BUFFERS] LOG / LOGMODE ANSI ]

Daca se specfica WITH cu o forma LOG inseamna ca se doreste utilizarea jurnalului de tranzactii si se pot utiliza instructiunile asociate (COMMIT, ROLLBACK, SETLOG etc).

In exemplul urmator se creaza o BD simpla de masini:

CREATE DATABASE masini

Ea poate fi activata prin comanda ;

START DATABASE masini

CONNECT TO

Pentru a termina conexiunea curenta se utilizeza fraza DISCONNECT CURRENT. Toate bazele activate anterior vor fi dezactivate. Urmatoarul exemplu creaza o BD numita cont si un nume de fisier pentru fisierul-log.daca se specifica numai un nume de fisier, atunci fisierul log este creat in fisie rul curent.

CREATE DATABASE cont WITH LOG in '/CONTACT/F200/jurnalact'

CREATE DATABASE LINK

Creaza o legatura cu o BD aflata la distanta. Exemlpul urmator creaza o legatura numita lk care face referire la BD user parola clt123 cu localizarea specificata de 'excont'

CREATE DATABASE LINK lk CONNECT TO user IDENTIFIED BY clt123 USING 'excont'

Comanda CREATE CLUSTER creaza un grup de relatii care au cel putin un atribut comun. 

Comanda urmatoare creaza un index pentru o relatie sau grup de relatii.

CREATE INDEX nume_index ON nume_relatie(atribut)

Exemplul urmator creaza indexul icod pentru atributul COD al relatiei (tabelului) reper.

CREATE INDEX icod ON reper(COD

Inchiderea unei BD poate fi facuta prin utilizarea comenzii STOP DATABASE.

Stergerea unei BD poate fi facuta cu ajutorul comenzii:

DROP DATABASE <nume_baza>

In SQL server

CREATE DATABASE BDStud

ON

( NAME = 'BDStud_dat',

FILENAME = 'c: BDStud_dat.mdf',

SIZE = 10,

MAXSIZE = 50,

FILEGROWTH = 5 )

LOG ON

( NAME = 'BDStud_log',

FILENAME = 'c: BDStud_log.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB )

Pentru lucra cu baza de date creata se da comanda USE

USE BDStud

CREATE TABLE

Tabelele reprezinta structurile de date fundamentale in toate SGBD-urile relationale ca ORACLE, INFORMIX, DB2, PARADOX, FOXPRO, INGRES etc. Un tabel (relatie) poate fi privit ca o matrice bidimensionala, in care o coloana(atribut) contine date de un singur tip despre o anumita entitate. De exemplu, o coloana poate fi numele unei persoane, alta varsta, alta salariu etc.

O linie (tuplu) este o colectie de date din fiecare coloana care in ansamblu reperezinta o entitate. De exemplu, un tabel este o lista de profesori, un plan de invatamant, o lista de masini etc.

Cand se proiecteaza un tabel trebuie avut in vedere urmatoarele aspecte:

sa se foloseasca nume sugestive pentru tabel si atributele care-l compun;

sa se aleaga tipul adecvat pentru fiecare coloana;

sa se determine restrictiile de integritate care exista intre date;

sa se determine o structura normalizata a tabelului,daca este posibil

sa se stabileasca daca o coloana poate sa ia sau nu valoarea NULL

Un tabel de baza este un tabel real, adica care exista fizic, in sensul ca in el sunt memorate inregistrari fizice si posibil pointeri la unul sau mai multe fisiere VSAM ce reprezinta in mod direct o tabela memorata. DIn contra, o vedere(view) este un tabel virtual, adica nu este memorat fizic, dar apare si e utilizat ca si cum ar fi.

Un tabel poate avea cel mult un index de grupare(CLUSTER) si mai multi indecsi (noncluster). Sistemele relationale mai suporta si alte structuri: B-arbori, partitii hassing etc. Ele pot fi create in orice moment si acest lucru este valabil si pentru indecsi.

Integritatea domeniului

O relatie de grad n mai poate fi privita si ca o submultime a produsului cartezian a n domenii D₁xD₂x.xD_n nu toate distincte in mod necesar.

Valorile care apar in coloana i a relatiei trebiue sa fie si n coloana D_i. Coloanele sunt nume de date (nume de atribute) care sunt referite la intrebari.

Integritatea unui domeniu define]te corectitudinea valorilor care-l compun.

Oricarui atribut al unei relatii ii coresunde un domeniu de baza (domeniu activ) format din multimea valorilor de pe coloana atributului respectiv din relatie. Astfel, orice atribut A al unei scheme pentru o relatie r are un domeniu de baza D si orice valoare candidata pentru r.A trebuie sa fie in D. Integritatea referentiala defineste legaturile intre relatiile r₁ si K un atribut sau grup de atribute care constituie o cheie pentru o alta relatie r₂. Atunci, in orice moment r₁.K trebuie sa fie egala cu valoarea unei chei primare continuta intr-un tuplu din r₂.

Instructiunea CREATE TABLE este utilizata pentru a crea: schema (structura de date) a unui tabel nou, restrictiile de integritate (vezi CHECK, CONSTRAINT), restrictii referentiale (vezi PRIMARY KEY, FOREIGN KEY). Aceasta are forma:

CREATE TABLE nume_tabel

definitie-coloana restictii-niv-coloana]

[,definitie-coloana restictii-niv-coloana])

Definitie-coloana

Nume-coloana tip-data [DEFAULT valoare] [NOT NULL] [restrictii-niv-coloana

Restictii-niv-coloana

UNIQUE/DISTINCT/PRIMARY KEY(nume-col,)/FOREIGN KEY(nume-col..)

Clauza-references/clauza-check/[ CONSTRAINT nume-restictie]

Numele tabelului trebuie sa fie unic in BD. Numele tabelelor temporare, al tabelelor virtuale sa fie diferit de cele ale tabelelor existente in BD.

Privilegii asupra tabelelor

Implicit toti utilizatorii care au garantat privilegiul CONNECT la o BD au toate privilegiile la noul tabel (exceptand ALTER si REFERENCES). Acordarea de acces privilegiat al unor utilizatori particulari se realizeaza cu instructiunea GRANT.

Pentru a restrictiona accesul se utilizeaza instructiunea REVOKE.

Definirea restrictiilor

Cand se creaza un tabel trebuie ca numele coloanelor sa fie unice. Oricarei coloane trebuie atasat un tip si optional una sau mai multe restrictii. Se indica care coloana are o valoare implicita si ce date trebue introduse in coloana si verificate. Restrictiile se pot defini la nivel de coloana sau de tabel. Daca definim restrictiile la nivel de coloana, nu putem avea coloane multiple. Valoarea implicita este inserata in coloana cand nu este specificata o valoare implicita si daca coloana permite null implicit este null. Nu se pot proiecta valori implicite pentru coloane seriale, iar pentru coloane de tip text si BYTE li se pot atribui implicit numai valoarea null. Tipul coloanei trebuie sa concorde cu tipul valorii implicite. Exemple de utilizare de valori implicite:

CREATE TABLE cont

( NR_ACCES  INTEGER DEFAULT 0001,

TIP_ACCES CHAR(1) DEFAULT 'A',

DESCRIERE_ACCES CHAR(20) DEFAULT 'cont nou',

IDENTIFICATOR_ACCES CHAR(8) DEFAULT USER )

Daca intr-o coloana este specificata restrictia NOT NULL, atunci trebuie sa se introduca obligatoriu o valoare in acea coloana, cand se insereaza o linie sau se actualizeaza linia pentru coloana repsectiva, altfel serverul BD returneaza o eroare.

CREATE TABLE articol

( NR_ART INTEGER,

COD_FABR CHAR(4) NOT NULL,

DESCRIERE CHAR(20))

Forma generala a unei restrictii(constrangeri):

UNIQUE

NOT NULL DISABLED WITH

PRIMARY KEY [CONSTRAINT nume_restr] ENABLED ERROR

REFERENCES FILTERING WITHOUT

CHECK

Urmatorul tabel creaza un tabel cu 2 restrictii: o restrictie de cheie primara numita kp si una de unicitate numita unic:

CREATE TABLE cont

(NR_ACCES INTEGER PRIMARY KEY CONSTRAINT kp,

COD_ACCES INTEGER UNIQUE CONTRAINT UNIQUE

DESCRIERE CHAR(25))

Putem seta restrictiile in modul urmator: DISABLED (inactive) sau ENABLED (active) in timpul operatiilor de inserare, stergere sau actualizare. Cand se opteaza FILTERING, liniile care nu verifica restrictiile sunt scrise intr-un tabel de diagnoza daca avem optiunea WITH ERROR sau WITHOUT ERROR c nd se returneaz utilizatorului erorile.

Clauza REFERENCES

Clauza REFERENCES este utilizata pentru a stabili legaturi referentiale intre doua tabele sau in acelasi tabel si are forma:

REFERENCES nume_tabel(NUME_COL[,NUME_COL])[ON DELETE CASCADE].

Nume_tabel este numele tabelului referit. Tabelul referit este diferit de (sau acelasi cu) tabelul care refera. Tabelul referit ca si tabelul care refera trebuie sa fie in aceiasi baza de date. Coloana referita trebuie sa contina restrictia UNIQUE sau cheie primara.

Tipul de data al coloanei referite trebuie sa fie acelasi cu tipul coloanei care refera cu exceptia ca daca coloana referita este seriala atunci coloana care refera poate fi INTEGER

Daca coloanele care refera iau valori notnull atunci acestea formeaza o cheie externa (FOREIGN KEY

O legatura dintre coloanele referite si care refera este numita legatura parinte-copil unde parintele este coloana referita (PRIMARY KEY), iar copilul este coloana (sau grupul de coloane) care refera (FOREIGN KEY). Aceste legaturi parinte-copil sunt stabilite printr-o restrictie (constrangere) referentiala. O restrictie referentiala poate fi stabilita si in interiorul aceluiasi tabel. De exemplu, daca consideram un tabel cu salariatii unei inteprinderi, acesta contine marca care identifica in mod unic orice salariat printr-un numar. Coloana NR_MASINA este o cheie externa (COPIL) ce contine codul (NUAMRUL MASINII) ce face referinta la MARCA care trebuie sa aiba o legatura one-to-one intre coloanele care refera si coloanele care sunt referite. Cu alte cuvinte,daca PK este o multime de coloane, atunci FK trebuie sa fie tot o multime de coloane.

EXEMPLE DE CREAREA DE TABELE

CREATE TABLE Student

Nr-leg  varchar(4) PRIMARY KEY

Nume  varchar(20),

Prenume  varchar(20),

Sex   char(1) DEFAULT 'M' CHECK (Sex IN ('M','m','F','f')),

Data_nasteri  smalldatetime,

Stare_civila char(1) CHECK (Stare_civila IN ('C','N','D'))

Grupa   varchar(3)

CREATE TABLE Discipline

Cod_diciplina   varchar(3) PRIMARY KEY

Denumire   varchar(20),

Sem   varchar(2) CHECK (Sem IN ('I','II')),

Anul  varchar(1) CHECK (Anul BETWEEN '1' and '3')

CREATE TABLE Catalog

NrLeg   varchar(5) NOT NULL

FOREIGN KEY REFERENCES Student(NrLeg)

ON DELETE CASCADE ON UPDATE CASCADE

Cod_discplina  varchar(3) NOT NULL

FOREIGN KEY REFERENCES Dicipline(Cod_disciplina), 

Nota   Numeric(2),

Data   smalldatetime

Crearea de restrictii in tabele

CREATE TABLE Student

(

NrLeg varchar(5) PRIMARY KEY

Nume   varchar(20),

Prenume varchar(20),

Sex char(1)   DEFAULT 'M' CONSTRAINT so CHECK (Sex IN ('M','m','F','f'

Data_nasteri smalldatetime,

Stare_civila   char(1) CONSTRAINT s1 CHECK (Stare_civila IN ('C','N'))

)

CREATE TABLE Discipline

Cod_disciplina varchar(3) PRIMARY KEY

Denumire   varchar(20),

Semestru   varchar(2) CHECK (Sem IN ('I','II')),

Anul   varchar(1) CHECK (Anul Between '1' And '4')

CREATE TABLE Catalog

(NrLeg varchar(5) NOT NULL CONSTRAINT rt0 FOREIGN KEY REFERENCES Student(NrLeg) ON DELETE CASCADE ON UPDATE CASCADE

Cod_disciplina varchar(3) NOT NULL CONSTRAINT rt1 FOREIGN KEY

REFERENCES Dicipline(Cod_disciplina),

Nota  Numeric(2),

Data smalldatetime)

MODIFICAREA STRUCTURII (SCHEMEI SI RESTRICTIILOR)UNUI TABEL

Adaugarea unei noi coloane

ALTER TABLE Student ADD grupa varchar(4)

Modificarea tipului unei coloane

ALTER TABLE Student ALTER COLUMN grupa numeric(4)

Stergerea unei coloane

ALTER TABLE Student DROP COLUMN grupa

Adaugarea unei coloane noi asupra careia se aplica o restictie

ALTER TABLE Student ADD NrLeg_sot VARCHAR(5) NULL CONSTRAINT sot_sotie DEFAULT 'xxx'

Stergerea de tabele

Stergerea completa a tabelelor se face cu ajutorul instructiunii DROP TABLE.

Exemple:

DROP TABLE Catalog,

DROP TABLE Student,

DROP TABLE Dicipline.