Sa recapitulam putin structura bazei de date Psihologie.

Diagrama cu toate obiectele bazei de date Psihologie arata astfel:

Tipuri de relatii

Dupa cum se vede avem 5 tabele. Vom descrie, pe scurt, relatiile. Intre tabelele Localitati si Institutii exista o relatie de la 1 la n, sau one to many, sau de la 1 la mai multi, sau de la 1 la infinit ( ). Aceasta inseamna ca unei localitati ii corespund una sau mai multe institutii dar nu si invers. Adica o institutie este dintr-o singura localitate. Campul LocalitateId din tabela Localitati este cheia primara iar campul LocalitateId din tabela Institutii este cheia straina. Aceasta constrangere ne asigura ca in tabela Institutii nu putem selecta o localitate inainte de a exista in tabela Localitati. Astfel de relatii mai exista si intre tabelele Institutii si Subiecti, respectiv intre tabelele Localitati si Subiecti. Un alt gen de relatie ( neevidentiat aici) este acela de relatie de la unu la unu. In aceasta situatie fiecarei inregistrari dintr-un tabel ii corespunde exact o inregistrare din celalalt tabel. Iar ultimul tip de relatie este relatia de la n la n, sau de la mai multi la mai multi ( sau many to many ). Acest gen de relatie exista intre tabelele Subiecti si Teste. Astfel, unui subiect ii pot corespunde mai multe teste. Pe de alta parte un test poate fi efectuat de mai multi subiecti. Aceasta relatie nu poate fi realizata direct, ca in cazul relatiilor de la 1 la n. Acest tip de relatii se stabilesc prin intermediul unei tabele de legatura. In cazul nostru tabela de legatura este tabela TesteXSubiecti care este astfel denumita pentru a sugera un fel de produs cartesian intre tabelele Teste si Subiecti. Astfel, in aceasta tabela se stocheaza astfel informatia: In ziua X ( campul Data ), Subiectul Y ( stocat in campul SubiectId ) a efectuat testul Z ( informatie inmagazinata in campul TestId ) si a obtinut nota a (informatie reflectata in campul Nota). Astfel acest tabel de legatura ( sau de jonctiune) stocheaza informatiile utile ale bazei de date ( evolutia notelor subiectilor la diversele teste).

Inserarea datelor in tabele

Avem aceasta baza de date dar aceasta are un impediment. Ea este o baza de date goala. Fireste ca scopul unei baze de date este aceea de a fi umpluta cu date. Pasul urmator este umplerea tabelelor cu un set de date.

Cum inseram date in tabele?

Date se pot insera in tabele atat din Enterprise Manager cat si din SQL Query Analizer. Sa inseram pentru inceput cateva localitati. Vom folosi pentru aceasta Enterprise Manager.

Inseram Localitatea Bucuresti cu judetul B. Intram in Enterprise Manager si ne pozitionam cu mouse-ul pe tabela Localitati -> Click Dreapta -> Open Table -> Return all rows. Ni se deschide tabela in modul actualizare si introducem valorile 1 pentru LocalitateId, Bucuresti pentru denumire si B pentru Judet. Ulterior putem introduce valorile 2, Pitesti si Arges.

Mai departe sa introducem doua teste. Inchidem tabela Localitati si ne pozitionam pe tabela Teste -> Click Dreapta -> Open Table -> Return all rows. Se deschide tabela Teste. Sa stabilim pentru testul 'Atentie Cifre ' id-ul 1. Observam ca daca incercam sa editam valoarea 1 in campul TestId ne apare mesajul 'Cannot edit this cell'. Nu putem edita campul TestId. De ce? Pentru a vedea motivul inchidem fereastra de actualizare date. Ne pozitionam din nou pe tabela Teste -> Click Dreapta -> Design table. In ecranul de proiectare al tabelelor urmarim campul TestId. Observam ca are setat Valoarea Identity = Yes si valorile Identity Seed = 1, respectiv Identity Increment = 1. Aceasta inseamna ca acest camp este un camp care se genereaza automat de catre SQL Server. Cheia primara se genereaza automat incepand de la valoarea 1 ( Identity Seed ) si, pe masura ce introducem alte teste, id-ul se va mari cu 1 ( Identity Increment). Prin urmare, pentru a introduce teste trecem direct, cu tab, la valoarea efectiva a testului, TestId fiind generat automat de program. Revenim la optiunea Teste -> Click Dreapta -> Open Table -> Return all rows. Apasam tab si scriem, in campul DenumireTest Atentie - Cifre. Ulterior introduce testul Reactie Simpla. Avem astfel aceste doua teste si doua localitati introduse.

Vom introduce doua institutii. De data aceasta vom utiliza SQL Query Analizer pentru a introduce laboratorul de Psihologie nr. 1 din Bucuresti. Pentru aceasta deschidem SQL Query Analizer si ne pozitionam pe baza curenta Psihologie. Instructiunea de inserare este: INSERT INTO ( . ) VALUES ( . )

INSERT INTO Institutii ( InstitutieId, Denumire, LocalitateId, Profil, Adresa, Telefon )

VALUES ( 1, 'Laboratorul de Psihologie Nr. 1', 1, 'Psihologie', 'Strada Feeriei Generale, nr. 12 bis', '8989989')

Scriem aceasta fraza INSERT in editorul SQL Query Analizer si, apasand tasta F5, rulam instructiunea care va avea ca efect inserarea acestei institutii in tabela de institutii. Va aparea mesajul (1 row(s) affected) care ne asigura ca inserarea s-a produs cu succes. Sa observam ca in campul LocalitateId am introdus valoarea 1 care corespunde in tabela Localitati orasului Bucuresti. In tabela Localitati avem doua orase cu id-urile 1 si 2. Incercati sa realizati urmatoarea fraza INSERT.

INSERT INTO Institutii( InstitutieId, Denumire, LocalitateId, Profil, Adresa, Telefon )

VALUES ( 1, 'Laboratorul de Psihologie Nr. 1', 3, 'Psihologie', 'Strada Feeriei Generale, nr. 12 bis', '8989989')

Vom primi urmatorul mesaj de eroare:

Server: Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__Institutii__InstitutieId'. Cannot insert duplicate key in object 'Institutii'.

The statement has been terminated.

Inserarea nu a putut fi efectuata. Motivul este urmatorul: constrangerea dintre tabelele Localitati si Institutii. Nu exista in tabela Localitati localitatea care sa-i corespunda campului LocalitateId = 3. Este ca si cum am incerca sa inseram in tabela Instituiii o localitate care nu exista, deoarece din perespectiva bazei de date localitatile care exista sunt cele din tabele Localitati. Daca nu am fi realizat constrangerea am fi putut face inserarea dar atunci ce sens ar mai fi avut tabela Localitati? Aceasta este una din cele mai frecvente erori care poate apare inclusiv in sarcina unui implementator. Constrangerile sunt verificate inainte de inserare si mesajele sunt elocvente atunci cand aceste constrangeri nu sunt respectate. Ce se poate face? Daca se are in vedere, sa zicem, o alta localitate Ploiesti, intai se introduce in tabela Localitati Ploiesti cu id-ul 3 si apoi se face insertul respectiv.

Puteti popula cu date mai departe. Pentru usurarea sarcinii am realizat un script care face o initializare a bazei de date. Ce inseamna o initializare? Un set de instructiuni ( de regula instructiuni INSERT ) prin care se introduc niste valori initiale. Acest script va popula cu cateva valori cele 5 tabele. Rulati in SQL Query Analizer scriptul Initializarea bazei de date Psihologie.sql

Acum avem suficiente date in baza de date pentru a le putea vizualiza si, eventual interoga aceste date conform unor criterii stabilite.

Cum vizualizam si interogam datele?

Acum cand avem ceva date in baza noastra ( care poate fi extinsa si considerabil imbunatatita, lucru de dorit dealtfel) am putea sa ne punem anumite intrebari cum ar fi:

toti subiectii cu numele si orasele

toti subiectii din Iasi

toti subiectii care lucreaza la o institutie in Bucuresti in ordinea crescatoare a numelor acestora.

Toate notele subiectilor din Iasi intr-un interval.

Mediile generale ale subiectilor etc.

Aceste intrebari presupun selectarea datelor astfel incat sa satisfaca cerintele noastre si ele se numesc interogari ale bazei de date. Instructiunea de baza utilizata este instructiunea SELECT. Alte cuvinte cheie extrem de simple ( ca fiind cuvinte uzuale de limba engleza ) sunt WHERE, AND, OR, ORDER BY, GROUP BY, FROM.

Interogarile reprezinta, intr-un fel, partea cea mai simpla dar si cea mai dificila a SQL - ului. Oricum, in cursul procesului de implementare sigur veti avea nevoie sa vizualizati anumite seturi de date. Iar aceste vizualizari se pot face, de asemenea, atat in Enterprise Manager cat si in Query Analizer. Recomandarea mea este sa utilizati SQL Query Analizer mai ales cand interogarile nu sunt foarte simple.

Selectia tuturor subiectilor din Iasi din Enterprise Manager

Intram pe tabela Subiecti -> click dreapta -> Open table -> Return All Rows. In acest moment vizualizam toate inregistrarile din tabela Subiecti. Pentru a rafina selectia dupa criteriile suplimentare trebuie sa avem posibilitatea de a modifica fraza SELECT.

Pentru aceasta, selectam optiunea SQL ( al patrulea buton de sus ). In acest moment va aparea un mic editor de SQL.

In acest editor se pot realiza fraze select. Se observa fraza care selecteaza toti subiectii : Select * FROM Subiecti. * inseamna toate inregistrarile. Sintaxa este SELECT * FROM Tabelul X. Ceea ce inseamna vizualizarea tuturor inregistrarilor din tabela X. Daca vrem doar numele si prenumele subiectilor din orasul Iasi scriem fraza urmatoare: SELECT S.NumeSubiect, S.PrenumeSubiect FROM Subiecti S, Localitati L WHERE S.LocalitateId = L.LocalitateId AND L.Denumire = 'IASI' Pentru a obtine subiectii din Iasi deschidem Sql Query Analizer avand grija sa ne pozitionam pe baza de date Psihologie. Scriem aceasta fraza SELECT si, apasand tasta F5, ne va aparea un singur subiect din Iasi si anume John Stallone.

Aceeasi fraza SQL poate fi realizata si in Enterprise Manager, apasand pe butonul SQL.

Se observa ca sintaxa este simpla si apropiata de sintaxa comuna. Ce, De unde si in ce conditii, exprimate prin cuvintele cheie Select, from, Where ( eventual and ). Selectia s-a facut din doua tabele deoarece conditia de Iasi ne ducea la tabela Localitati si, preluand Id-ul de-acolo luam din tabela Subiecti toti subiectii cu campul LocalitateId egal cu cel gasit in tabela Localitati. Se poate lucra in ambele editoare la fel de bine, totusi editorul SQL Query Analizer avand facilitati suplimentare si fiind special construit pentru aceasta.

O descriere mai detaliata a frazelor Select si a interogarilor vom incerca sa facem intr-o etapa urmatoare. Pana atunci, acest script realizeaza mai multe exemple si este indicat sa il parcurgeti cu atentie: Selectia datelor.sql

Modificarea datelor

O alta operatie necesara este aceea a modificarii datelor. Pentru aceasta exista instructiunea UPDATE. Similar, o modificare se poate realiza atat din SQL Enterprise Manager cat si din QueryAnalizer. Sa presupunem ca noi consideram putin probabila existenta cetateanului John Stallone in Iasi si vrem sa-i modificam denumirea in Gigi Popescu. Astfel Numele va deveni din John Popescu si prenumele din Stallone Gigi. Intram in SQL Query Analizer si, pozitionandu-ne pe baza de date Psihologie, scriem urmatoarea fraza UPDATE:

UPDATE Subiecti

SET NumeSubiect = 'Popescu', PrenumeSubiect = 'Gigi'

WHERE NumeSubiect = 'John' AND PrenumeSubiect = 'Stallone'

Sintaxa generala este UPDATE . SET . WHERE . si sugereaza modificare ( update tabela . ) , ce anume se modifica si cu ce valori noi SET . si care anume valori se modifica ( WHERE . ).

Sa modificam acum nota lui Popescu la testul Atentie Cifre unde acesta a luat nota 5 si s-a dus la marire, luand nota 8. Acest update este mai dificil. Haideti intai sa ne convingem ca nota lui Popescu este intr-adevar 5 la testul Atentie Cifre. Pentru aceasta sa intram in Enterprise Manager pe tabela Subiecti. Alegem SQL si apoi alegem optiunea Add table.

Adaugam, pe rand, tabelele Teste si TesteXSubiecti. Alegand tabelele, se realizeaza automat fraza SELECT si se poate rula interogarea apasand pe !. Ulterior se va vedea setul de date rezultat in urma rularii interogarii. Se adauga conditiile ca DenumireTest sa fie Atentie Cifre si ca NumeSubiect sa fie Popescu si prenumele Vasile. Dar in acest caz ar fi mult mai precis selectat numele dupa SubiectId = 2 si testul dupa TestId = 1. Selectand doar numele, nota, data si data notei avem setul de date dorit.

Se observa nota lui Popescu si anume 5. Prin urmare nu ii vom modifica nota lui Vasilescu ( sa spunem ) care este un elev stralucit si o nota de 8 i-ar strica palmaresul. Deoarece acesta este riscul unor update-uri facute la repezeala si pe fuga: pot strica datele. Pentru a fi siguri ca vom modifica nota lui Popescu si nu a lui Vasilescu am facut intai selectul si apoi il vom transforma foarte simplu intr-un update.

Prin inlocuirea frazei select cu fraza update urmata de instructiunea set putem fi siguri ca vom face exact update-ul dorit. Trebuie sa fim constienti ca instructiunile UPDATE sunt instructiuni care trebuie realizate cu mare grija. Si aceste instructiuni le voi trata intr-un script separat: Modificarea datelor.sql

Stergerea datelor

Desi nimeni nu o doreste, exista situatii in care suntem nevoiti sa mai si stergem, din diverse motive. Instructiunea de stergere este simpla

delete . from . where

Presupunem ca lucram in SQL Query Analizer.

De exemplu, daca vrem sa stergem toti subiectii ar trebui sa executam instructiunea DELETE Subiecti. Executia instructiunii va genera un mesaj de eroare:

Acesta este un mesaj frecvent intampinat la stergerea unor date. El apare in situatia cand vrem sa stergem din tabele care au corespondenti de la 1 la n catre alte tabele. In tabela Subiecti exista corespondenti in tabela TesteXSubiecti. Fireste ca nu putem sterge un subiect care are note. Notele cui raman? Intai trebuie sterse notele, deci tabela TesteXSubiecti si abia apoi stearsa tabela Subiecti. Prin urmare pentru a putea sterge subiectii trebuie sterse notele. Corect este astfel:

DELETE TesteXSubiecti

DELETE Subiecti

Instructiunile de stergere sunt simple dar periculoase. Trebuie sa fim foarte atenti deoarece nu putem reveni si datele, odata sterse, sunt pierdute si nu pot fi recuperate. Prin urmare, instructiunea DELETE trebuie efectuata cu mare bagare de seama.

Sa presupunem ca vrem sa stergem testele cu note sub 5 deoarece consideram ca aici nu exista corigenti. Primul lucru punctam tabela de unde stergem (TesteXSubiecti). Apoi incercam intai, similar ca la update, sa selectam acesti subiecti pentru a vedea cine nu a invatat si la ce. Aceasta fraza SELECT afiseaza notele si subiectii ce trebuie sterse :

Prin urmare, notele lui Niculaie si Ion trebuie sterse. Cum este cel mai simplu acum sa le stergem?. Dupa campul KeyId care este cheia primara a tabelei TesteXSubiecti, prin urmare este unica. Inregistrarile 34 si 36 sunt cele care trebuie sterse. Instructiunea de stergere este atunci urmatoarea:

Se vede ca au fost afectate doua inregistrari. Sunt exact cele doua inregistrari selectate anterior care acum au fost sterse. Daca din greseala era uitata instructiunea WHERE atunci era sters tot tabelul TesteXSubiecti.

Pentru cei ce doresc sa aprofundeze voi da alte exemple in scriptul urmator :

Stergerea datelor.sql

Prin urmare, am parcurs in acest capitol cele patru tipuri de instructiuni de baza, care formeaza dealtfel si standardul ANSI SQL. Este vorba despre instructiunea SELECT si cele de modificare UPDATE, INSERT si DELETE.