Limbajul SQL - crearea tabelelor in Visual FoxPro

Limbajul SQL - crearea tabelelor in Visual FoxPro

Prezentare generala

Limbajul SEQUEL (Structured English as Query Language) a fost prezentat pentru prima data ca un limbaj de interogare in anul 1974, de catre Chamberlin si Boyce. In 1980, Chamberlin a schimbat denumirea SEQUEL in SQL (Structured Query Language - limbaj structurat de interogare).

ANSI (Institutul National American pentru Standarde) a publicat in 1986 standardul SQL ANSI 1986, care definea comenzile de baza ale limbajului SQL, inclusiv pentru crearea de tabele si vederi (create table si create view). Limbajul SQL - 86 nu continea optiuni de modificare a structurii sau stergere a obiectelor bazei (alter, drop) si comenzi pentru acordare si revocare de drepturi utilizatorilor (grant si revoke).

Astfel, au fost necesare revizuirea si extinderea limbajului. In 1989 apare limbajul SQL - 89 recunoscut ca fundament al multor SGBD-uri comerciale. Totusi, variantele comerciale ale diferitilor producatori erau incompatibile la nivel de detaliu.

In 1992, apare versiunea SQL - 92 cu multe facilitati incluse, precum:

Jonctiunea externa (outer join)

Modificarea schemei bazei de date (alter, drop, grant, revoke)

Modificari si stergeri referentiale in cascada

Amanarea verificarii restrictiilor

Niveluri de consistenta a tranzactiilor.

In 1999 a fost publicat un standard SQL - 3 care iese din sfera relationalului si a normalizarii relatiilor, asa cum au fost formulate de Codd. Principalele orientari ale limbajului SQL - 3 vizeaza transformarea acestuia intr-un limbaj complet, in vederea definirii si gestionarii obiectelor complexe si persistente. Astfel, au fost introduse:

Mosteniri multiple, polimorfismul, incapsularea, caracteristici preluate din limbajele orientate pe obiecte

Tipuri de date definite de utilizator

Suport pentru sisteme bazate pe gestiunea cunostintelor

Interogari recursive

Instrumente de administrare a datelor.

Tipurile de date definite in standardul SQL - 92 sunt urmatoarele:

SMALLINT pentru numere intregi scurte; ocupa 4 pozitii reprezentate pe 16 biti

INTEGER sau INT pentru numere intregi (9 pozitii reprezentate pe 32 biti)

NUMERIC (p, s) sau DECIMAL (p, s) sau DEC (p, s) pentru memorarea datelor reale, cu un total de p pozitii, dintre care s la partea fractionara

FLOAT pentru date reale reprezentate in virgula mobila (20 pozitii pentru mantisa)

REAL pentru date reale exprimate in virgula mobila, cu precizie mai mica decat FLOAT, dar la nivelul de intrare fiind identice)

DOUBLE PRECISION pentru date reale exprimate in virgula mobila, in dubla precizie (30 pozitii pentru mantisa)

CHAR(n) sau CHARACTER(n) pentru date de tip sir de caractere de lungime n (maxim 240)

VARCHAR(n) sau CHAR VARYING(n) sau CHARACTER VARYING(n) pentru date de tip sir de caractere de lungime variabila, maxim 254 caractere

DATE pentru data calendaristica

TIME pentru ora

TIMESTAMP pentru an, luna, zi, ora, minut, secunda, fractiuni de secunda.

Principalele tipuri de date folosite in Visual FoxPro

CHARACTER(n) sau C(n), LOGICAL sau L, NUMBER(p,s) sau N(p,s), INTEGER sau I, DOUBLE, DATE sau D, DATETIME

Crearea unei baze de date in Visual FoxPro

Crearea unei baze de date necesita cunostinte de administrare strict legate de produsul software de gestiune a bazei de date. Elementele tehnice si fizice caracteristice serverelor de baze de date, cum ar fi: Oracle, SQL Server, DB2, Informix, sunt:

Spatiile tabela (table space) folosite pentru alocarea de spatiu pe disc pentru obiectele bazei

Segmentele de rollback sunt obiecte utilizate de sistem pentru salvarea datelor necesare validarii tranzactiilor

Fisierele de date si procesele sistem asociate sistemelor de gestiune de baze de date.

Comanda de creare a unei baze de date in limbajul SQL - Visual FoxPro este:

CREATE DataBase [nume_baza_de_date | ?].

Argumentul nume_baza_de_date este optional. Lipsa acestui argument determina deschiderea ferestrei de dialog Create, in care se cere introducerea unui nume pentru fisierul baza de date .dbc.

Aplicatie: Sa se creeze o baza de date FIRMA care sa gestioneze informatii despre angajatii din departamentele unei firme, utilizand comenzile SQL si Visual Foxpro.

Comanda de creare a bazei de date FIRMA:

CREATE DATABASE firma

Proiectarea bazei de date

Baza de date FIRMA care gestioneaza informatii despre angajatii departamentelor unei firme contine urmatoarele tabele:

DEPART (departamente), cu detalii despre departamente;

ANG (angajati), cu toti angajatii firmei;

SALGRD (grade salarii), cu detalii despre gradele de salarizare.

Proiectarea bazei de date permite obtinerea schemelor relationale urmatoare:

DEPART(nrdepart#, numedepart, loc)

ANG(codang#, nume, functie, manager, datan, salariu, comision, nrdepart)

SALGRD(grad#, salmin, salmax).

Tabelul DEPART contine urmatoarele campuri:

nrdepart - numar departament, de tip number(2), cheie primara;

numedepart - nume departament, de tip char(14);

loc - localitatea unde se afla departamentul, de tip char(13).

Tabelul ANG contine urmatoarele campuri:

codang - codul angajatului, de tip number(4), cheie primara;

nume - numele angajatului, de tip char(12);

functie - functia angajatului, de tip char(16);

manager - codul managerului angajatului, de tip number(4);

datan - data nasterii, de tip date;

salariu - salariul angajatului, de tip number(12,2);

comision - comisionul angajatului, de tip number(8);

nrdepart - numarul departamentului in care lucreaza angajatul, de tip number(2).

Tabelul SALGRD contine urmatoarele campuri:

grad - categoria de salarizare, de tip number(1);

salmin - salariul minim din categoria respectiva de salarizare, de tip number(12,2);

salmax - salariul maxim din categoria respectiva, de tip number(12,2).

Schema simplificata a bazei de date FIRMA ce contine tabelele si relatiile dintre ele este prezentata in figura 1:

Tabelul DEPART are cheie primara - coloana nrdepart, tabelul ANG are cheie primara - coloana codang si cheie externa - coloana nrdepart, iar tabelul SALGRD are cheie primara - coloana grad.

Crearea tabelelor

Programul care creeaza tabelele, fara a stabili relatiile dintre ele si restrictiile, este urmatorul:

clear

close databases

open database firma.dbc

create table depart( nrdepart n(2) , numedepart c(14), loc c(13))

create table ang( codang n(4), nume c(12), functie c(16),;

manager n(4), datan d, salariu n(12,2),;

comision n(8), nrdepart n(2) )

create table salgrd( grad n(1), salmin n(12,2), salmax n(12,2))

modify database

close all

Dupa executia programului de mai sus, este afisata baza de date in fereastra Database Designer (figura 2):

Adaugarea restrictiilor

In limbajul SQL se pot declara toate tipurile de restrictii pentru o baza de date relationala:

cheie primara - atributul PRIMARY KEY;

unicitate - clauza UNIQUE;

valori obligatorii (nenule) - optiunea NOT NULL; in Visual FoxPro trebuie declarate atributele care pot avea valori NULL;

restrictii utilizator - implementate sub forma regulilor de validare la nivel de camp, inregistrare sau pot fi incluse in declansatoare;

restrictii referentiale - apar datorita existentei cheilor straine (clauza FOREIGN KEY). Restrictiile referentiale pastreaza integritatea bazei de date la executia anumitor operatii, precum: la inserarea unei linii intr-o tabela copil sau modificarea unei chei straine (se verifica daca noile valori se regasesc in liniile tabelului parinte); la stergerea unei linii dintr-o tabela parinte (fie se sterg in cascada toate inregistrarile corespondente din tabelul copil, fie se interzice stergerea).

Exemple:

Adaugarea restrictiei cheie primara pentru coloana nrdepart (tabelul depart):

alter table depart add primary key nrdepart tag nrdepart

Vizualizarea (modificarea) structurii (inclusiv cheia primara) se face folosind comanda modify structure à Table Designer à eticheta Indexes (figura 3):

Adaugarea regulii de validare la nivel de camp: numedepart sa accepte numai litere mari:

ALTER TABLE depart ALTER COLUMN numedepart char(14) ;

CHECK(numedepart = upper(numedepart)) ERROR 'litere mari'

Vizualizarea regulii de validare se face folosind comanda: modify structure à Table Designer à eticheta Fields, selectare camp, sectiunea Field validation, Rule si Message (figura 4):

Adaugarea restrictiei ca loc sa faca parte din lista: Bucuresti, Ploiesti, Brasov, Campina, Arad, Iasi

ALTER TABLE depart ALTER COLUMN loc char(13) ;

CHECK(inlist(loc,'Bucuresti','Ploiesti', 'Brasov', 'Campina', 'Arad',; 'Iasi')) ERROR 'Bucuresti, Ploiesti, Brasov, Campina, Arad, Iasi'

Modificarea regulii de validare se poate realiza in fereastra Table Designer, Fields, selectare camp, Rule, Expression Builder (figura 5):

Adaugarea restrictiei de cheie primara pentru coloana codang (tabelul ang), folosind Table Designer: eticheta Indexes à numele indexului - codang, tipul indexului - primary, ca eticheta - expresia codang (figura 6):

Adaugarea regulii de validare la nivel de camp: nume sa aiba prima litera mare, iar celelalte litere mici si functie sa aiba numai litere mari - in mod vizual (fereastra Table Designer / Fields / Field validation / Expression Builder), figura 7:

   

Adaugarea restrictiei NULL pentru coloanele manager si comision:

ALTER TABLE ang ALTER COLUMN manager null

ALTER TABLE ang ALTER COLUMN comision null

Adaugarea cheii externe pentru tabelul ang, coloana nrdepart - coloana comuna cu coloana nrdepart (tabelul parinte depart):

ALTER TABLE ang ADD FOREIGN KEY nrdepart;

TAG nrdepart REFERENCES depart

Comanda MODIFY DATABASE permite vizualizarea relatiei dintre tabelul parinte ,depart' si tabelul copil ,ang' (figura 8) - coloana nrdepart este cheie primara in tabelul parinte si cheie externa in tabelul copil:

Adaugarea restrictiei referentiale urmatoare: la modificarea cheii primare nrdepart din tabelul depart, sa se modifice in cascada si valorile corespondente din tabelul copil ang: se activeaza fereastra Database Designer, apare meniul Database, se alege optiunea Clean Up DataBase; se foloseste optiunea Edit Referential Integrity (figura 9):

se activeaza eticheta Rules for Updating, apoi se alege optiunea Cascade Updates all related records in the child table with the new key value

Crearea indecsilor in mod vizual, folosind Project Manager

In Visual Foxpro exista urmatoarele tipuri de indecsi:

Primar - pentru declararea cheii primare

Candidat - are aceeasi semnificatie ca si cel primar

Regular - permite duplicarea valorilor, fiind folosit pentru cheile straine sau pentru crearea de indecsi pentru optimizarea accesului la inregistrari

Unique - genereaza in fisierul index doar valori unice ale expresiei de indexare.

Exemplu:

Creati un index pentru tabelul salgrd, coloana grad, in mod vizual:

se deschide baza de date FIRMA folosind Project Manager;

se selecteaza tabelul salgrd

se activeaza butonul Modify, eticheta Indexes si se specifica:

o       numele indexului -grad; tipul Primary,

o       ordinea de sortare - ascendenta si

o       expresia - grad.

Erori care pot apare in cazul folosirii indecsilor

Motorul de baza de date Visual Foxpro nu actualizeaza intotdeauna la timp indecsii, dupa actualizarile efectuate in baza de date[FoBra02]. O solutie este verificarea integritatii bazei de date, astfel:

close all

open database firma

validate database recover && figura 10

Daca un obiect nu este valid si nu este refacut automat, el trebuie sters si reconstruit, apoi se refac restrictiile referentiale.

Crearea completa a tabelelor folosind un program Visual FoxPro

clear

close databases

open database firma.dbc

create table depart( ;

nrdepart number(2) primary key, numedepart char(14) ;

check(numedepart=upper(numedepart));

error 'se accepta numai litere mari', ;

loc char(13) check(inlist(loc,'Bucuresti','Ploiesti',;

'Brasov','Campina','Arad','Iasi'));

ERROR 'se accepta numai;

localitatile din lista Bucuresti, Ploiesti, Brasov, Campina,;

Arad, Iasi')

create table ang( ;

codang number(4) primary key,;

nume char(12) check(nume=proper(nume));

error 'prima litera este majuscula, celelalte - litere mici', ;

functie char(16) check(functie=upper(functie));

error 'se accepta numai litere mari' ,;

manager number(4) null, datan date ,;

salariu number(12,2),;

comision number(8) null, nrdepart number(2),;

foreign key nrdepart tag nrdepart references;

depart tag nrdepart;

)

create table salgrd( grad number(1) primary key,;

salmin number(12,2), salmax number(12,2);

)

modify database

close all   

Dupa executia programului, se creeaza complet baza de date FIRMA (tabelele, restrictiile, indecsii), figura 11:

Observatii:

Baza de date FIRMA contine tabelele: DEPART si ANG in relatie parinte - copil si SALGRD;

tabelul DEPART are cheie primara - nrdepart, ANG are cheie primara - codang, cheie externa - nrdepart si tabelul SALGRD are cheie primara - grad;

pentru afisarea, stergerea sau modificarea unui tabel, se efectueaza click dreapta pe tabelul respectiv pentru alegerea optiunii respective: Browse, Delete, Modify.