Crearea unei baze de date

Orice sistem de gestiune a bazelor de date, precum SQL Server, presupune existenta unui limbaj specific cu ajutorul caruia se gestioneaza bazele de date. Pe de alta parte, toate limbajele au un fundament lingvistic acceptat prin conventie. Astfel, atat Oracle, SQL Server sau DB2 respecta un limbaj standard numit ANSI SQL. Astfel exista un set de instructuni sau un limbaj numit ANSI SQL care este respectat si utilizat de aproape toate sistemele de gestiune de baze de date, prin urmare si de SQL Server. Pe deasupra, fiecare program si-a dezvoltat un set suplimentar de instructiuni specifice. Astfel, limbajul utilizat de SQL Server se numeste Transact SQL iar cel utilizat de Oracle se numeste PL SQL. Acestea sunt diferite dar au in comun sintaxa de baza conferita de standardul ANSI SQL.

Transact SQL este limbajul utilizat de Microsoft pentru administrarea, dezvoltarea si implementarea bazelor de date. El cuprinde atat instructiuni de creare de obiecte ( incepand cu crearea bazelor de date ) dar si instructiuni de interogare a datelor etc.

Pentru o mai buna intelegere a problemei consider ca ar fi util sa ne propunem realizarea unei baze de date test pe baza caruia sa exemplificam. Baza de date va avea scopul de a stoca informatiile referitoare la testarea subiectilor din diverse institutii. Sa denumim baza de date Psihologie.

Primul lucru pe care trebuie sa il facem este sa creem baza de date. O baza de date se poate crea in doua moduri: fie din Enterprise Manager fie din Query Analizer. Vom crea baza de date intai din Enterprise Manager. Apoi o vom sterge pentru a o crea din nou din Query Analizer. Vom descrie, pe scurt, ce fisiere se creeaza si unde sunt stocate acestea in mod implicit.

A)    Crearea bazei de date Psihologie

1)Realizarea bazei de date Psihologie din Enterprise Manager.

Intram in Enterprise Manager pana ajungem la expandarea optiunii Databases. Ne pozitionam cu mouse-ul deasupra optiunii databases si, cu click dreapta vom ajunge la optiunea New Database. Acceptam optiunea New Database si se va deschide un ecran cu numele Database Properties. In primul rand trebuie sa completam numele bazei de date si vom scrie Psihologie. Observam ca avem trei taburi dupa cum urmeaza : General, Data Files si Transaction Log.

In General singurul lucru pe care l-am putea modifica este proprietatea Collation Name unde scrie Server default. Collation este o proprietate a bazei de date care se refera la faptul daca datele de tip text vor fi case sensitive sau case insensitive. Server default inseamna acceptarea collation - ului implicit al serverului pentru baza de date pe care tocmai o creez. De regula acesta nu se modifica. Implicit instalarea unui server de SQL Server propune setarea case insensitive( nu se deosebesc literele mari de cele mici spre deosebire de case sensitive).

In Data Files avem numele conceptual al fisierului ( Psihologie_data ) si Location. Aceasta inseamna calea unde se va crea fisierul de date(C:Program FilesMicrosoft SQL ServerMSSQLdataPsihologie_Data.MDF). Directorul C:Program FilesMicrosoft SQL ServerMSSQLdata este directorul unde SQL Server propune crearea fisierelor atunci cand creem o baza de date. Fisierul va purta implicit denumirea Psihologie_Data.MDF. Initial Size este dimensiunea initiala a fisierului e 1 MB. Alte proprietati se refera la dimensiunea maxima a fisierului, atunci cand se doreste o limitare a acestuia. Optiunea implicita nu limiteaza dimensiunea fisierelor.

In Transaction Log avem informatii similare referitoare la alt fisier, numit fisierul de log. Fisierul se va afla in C:Program FilesMicrosoft SQL ServerMSSQLdataPsihologie_Log.LDF. Se observa ca si acest fisier va fi creat tot in directorul C:Program FilesMicrosoft SQL ServerMSSQLdata. Celelalte proprietati sunt similare.

Apasand butonul de OK vom creea baza de date. Daca aceasta nu apare imediat, ne ducem din nou pe databases si apasand tasta F5 facem un refresh dupa care va aparea si baza Psihologie printre celelalte baze de date.

Fisierele bazei de date

Odata creata baza de date am observat ca s-au creat doua fisiere. Un fisier cu extensia MDF si altul cu extensia LDF. Pentru a vizualiza proprietatile bazei de date Psihologie facem click dreapta pe baza de date Psihologie si selectam optiunea Properties. Aici vedem inca o data cele doua fisiere si, in plus, mai vedem si alte proprietati.

Fisierul cu extensia MDF se mai numeste si fisierul primar si este un fisier de date. Aici se afla datele referitoare la tabelele sistem ale bazei de date. Observam ca initial baza de date contine un set de tabele care sunt cele din baza de date Model si care sunt tabele system. Se mai observa ca acest fisier face parte din grupul de fisiere ( filegroup ) numit primary. Acesta este grupul de fisiere implicit.

Fisierul cu extensia LDF este fisierul de log ( jurnal ). Acesta tine informatii legate de activitatile desfasurate in baza de date.

Initial o baza de date este formata din aceste doua fisiere. Mai exista un alt tip de fisier ( cu extensia NDF ) care se numeste fisier secundar. Acesta este un fisier asemanator cu MDF dar aici nu pot fi stocate decat informatii definite de catre user.  Crearea si gestionarea separata a fisierelor presupune o extrem de buna cunoastere a resurselor si nu este recomandabila decat unor administratori autorizati de SQL Server.

In cazul nostru, baza de date Psihologie odata creata a insemnat crearea acestor doua fisiere. Baza este goala continand doar informatiile minime din Model.

Stergerea unei baze de date din Enterprise Manager

Sa stergem baza de date pentru a o crea din nou din Query Analizer. Facand click dreapta pe baza Psihologie selectam optiunea Delete. Prin aceasta optiune vom sterge baza de date pe care tocmai am creat-o. Fireste ca aceasta inseamna stergerea celor doua fisiere MDF si LDF.

Crearea bazei de date psihologie din SQL Query Analizer

Pentru a crea baza de date deschidem SQL Query Analizer. Orice baza de date se creeaza in master. Pentru aceasta verificam daca suntem in baza de date master (sus, in mijloc, de unde se poate selecta baza de date unde vrem sa lucram avem grija sa fie selectata baza de date master ).

Fiind in master, scriem urmatoarele instructiuni: CreateDatabasePsiho.sql Astfel se vor seta proprietati asemanatoare cu cele din Enterprise Manager. Astfel baza de date are 10 MB ( fisierul de date ) si 5 MB fisierul de log, dimensiunile maxime fiind de 30 MB respectiv 10 MB.

Stergerea bazei de date Psihologie din SQL Query Analizer

Instructiunea de stergere este extrem de simpla, si anume :

Drop database Psihologie

B)    Crearea obiectelor bazei de date Psihologie

Odata creata baza de date urmeaza pasul urmator: crearea tabelelor.

Prima tabela pe care o vom realiza este o tabela cu institutiile de unde provin subiectii. Pentru aceasta trebuie sa ne gandim care sunt atributele unei institutii. Ar fi, sa zicem, numele institutiei, localitatea, profilul, adresa, telefonul, e_mailul. Lista ar putea fi completata si cu alte atribute. Pe langa aceste atribute trebuie sa avem in vedere sa gasim o modalitate de a identifica in mod unic o astfel de institutie. Pentru aceasta cel mai sigur mod este de a genera un camp ( de regula de tip numar intreg ) care va avea tocmai acest rol de a identifica unic o institutie. Pentru aceasta acest camp va fi, in plus, si cheia primara a tabelului.

Revenind, un tabel pastreaza informatii despre anumite seturi de date. Acestea, de cele mai multe ori, trebuie sa fie unice pentru ca nimeni nu are interesul sa tina aceeasi informatie de mai multe ori. Unicitatea este conferita printr-o constrangere. Astfel, datelor din tabel le sunt asociate anumite valori (de regula numerice ) prin care este asigurata atat unicitatea cat si coerenta datelor din tabela respectiva. Aceste valori se numesc generic Id-uri si ele nu apar in fata utilizatorului aplicatiei care lucreaza cu baza de date. Rolul lor este sa pastreze consistenta datelor.

Tipuri de date

Fiecare camp din tabela va fi de un anumit tip. Astfel, de exemplu o varsta a unui individ este un camp de tip intreg. Pentru aceasta exista tipul Int. Numele unei persoane este un camp de tip text. Pentru aceasta exista patru tipuri de campuri si anume varchar, char, nvarchar si nchar. Data nasterii unei persoane este un camp de tip data. In SQL exista tipurile datetime respectiv smalldatetime. Media unui elev este un tip de camp numar real. Pentru aceasta exista tipurile de date float, numeric si decimal. Se pot descoperi foarte usor si celelalte tipuri de date in BooksOnLine.

Crearea tabelelor si a constrangerilor

Construirea unui tabel si a cheii sale primare din Enterprise Manager

Sa realizam pentru inceput un tabel cu localitatile. Informatiile utile ar fi (simplificat ) numele, judetul si id-ul ( cheia tabelei ). Sa construim acest tabel din Enterprise Manager. Pentru aceasta intram in Enterprise Manager si ne pozitionam pe baza de date Psihologie. Ne pozitionam cu mouse-ul pe tables si, click dreapta, selectam optiunea New Table. Automat se deschide fereastra de creare a unui nou tabel in care completam datele dupa cum urmeaza:

In coloana ColumnName scriem numele campului: LocalitateId. Cu tab ne deplasam in coloana Data Type de unde alegem tipul int ( numar intreg ). La optiunea Allows NULL debifam. Optiunea Allows Null este o setare ce atesta daca acest camp este obligatoriu de actualizat sau nu. Avand in vedere ca acesta este un camp care va identifica in mod unic o localitate el este un camp indispensabil si nu se permite neactualizarea acestuia. Cand un camp este lasat necompletat el nu are nici o valoare introdusa de utilizator. In acest caz valoarea este nula ( null ). Mai departe, dupa ce am pus Id-ul trebuie sa il setam ca fiind cheie primara a tabelei ( pentru a ii conferi unicitate). Pentru aceasta selectam inregistrarea, facem click dreapta pe aceasta si setam  optiunea Set Primary Key. Astfel campul LocalitateId este cheie primara a tabelei. Ulterior definim campul Denumire cu tipul varchar (100), tip ales din lista derulanta la data type. Similar, acest camp nu permite null ( debifam optiunea Allows NULL ). Ultimul camp este Judet, de tipul varchar (10) pe care nu il mai debifam. Ceea ce inseamna ca este posibila necompletarea acestui camp, situatie in care va ramane null. Dupa ce am completat aceste trei campuri salvam tabela si o denumim Localitati. Astfel baza de date are o prima tabela care va stoca informatii legate de localitati. Mai mult, acest tabel are deja o cheie primara conferita de un camp intreg (int ), numit LocalitateId. Acest camp are rolul de a identifica in mod unic fiecare inregistrare din tabela Localitati.

Construirea unui tabel si a cheii sale primare din SQL Query Analizer

Sa construim tabela care va tine informatiile despre institutii, tabele pe care o vom denumi Institutii. Pentru aceasta vom deschide SQL Query Analizer si ne vom pozitiona in baza de date Psihologie ( baza curenta, setata de sus, in mijloc ) pentru a fi siguri ca vom crea tabelul unde trebuie. Daca, spre exemplu, baza de date curenta este Northwind sau Model, tabelul de Institutii va fi creat in Northwind sau Model. Prin urmare, primul lucru pe care trebuie sa il facem cand lucram cu SQL Query Analizer este sa setam corect baza de date curenta. Aceasta setare se face simplu, prin selectarea din lista derulanta a bazelor de date a bazei de date care ne intereseaza si in care vrem sa lucram.

Prin urmare, suntem in SQL Query Analizer. Pentru a crea tabela de Institutii vom scrie instructiuni specifice de creare a tabelei. Sintaxa este urmatoarea:

CREATE TABLE Institutii ( InstitutieId INT NOT NULL, Denumire CHAR(200) NOT NULL, LocalitateId INT NOT NULL, Profil VARCHAR(100) NULL, Adresa VARCHAR(255) NULL, Telefon VARCHAR(20) NULL ).

Se observa ca exista un camp care nu confera informatie utila utilizatorilor. Este vorba de campul InstitutieId, camp care este un Id setat pentru a deveni cheia primara a tabelei. Campurile obligatorii ( cele care au setata optiunea NOT NULL ) sunt Denumire si LocalitateId. Aceasta deoarece este evident ca o institutie trebuie sa aiba un nume si este obligatoriu sa stim macar in ce oras se afla institutia respectiva. Celelalte informatii sunt optionale ( necompletarea lor atrage valoarea NULL ). Dupa ce am scris instructiunile acestea trebuie executate. Executia acestora se face in doua moduri: fie prin tastarea tastei F5 ( care inseamna executie), fie prin selectarea din mediul Query -> Execute.

A mai ramas de setat cheia primara a tabelei. Si aceasta se poate stabili din SQL Query Analizer conform sintaxei urmatoare:

ALTER TABLE Institutii

ADD CONSTRAINT PK_Institutie_InstitutieId PRIMARY KEY ( InstitutieId )

Instructiunea ALTER TABLE realizeaza modificarea structurii sau a relatiilor unui tabel deja existent. In acest caz, intai s-a creat tabelul si, ulterior, s-a adaugat cheia primara a acestuia. Se putea crea tabela si stabili in acelasi timp cheia primara a acesteia. Daca am fi procedat astfel, sintaxa era urmatoarea:

CREATE TABLE Institutii ( InstitutieId INT PRIMARY KEY NOT NULL, Denumire CHAR(200) NOT NULL, LocalitateId INT NOT NULL, Profil VARCHAR(100) NULL, Adresa VARCHAR(255) NULL, Telefon VARCHAR(20) NULL ).

Prin urmare, la crearea oricarui obiect se utilizeaza sintaxa CREATE, la fel cum la modificarea oricarui obiect se utilizeaza sintaxa ALTER. De exemplu, daca doream sa stabilim dimensiunea bazei de date la 11 MB utilizam sintaxa urmatoare:

ALTER DATABASE Psihologie

MODIFY FILE

name = Psihologie_data,

Size = 11 MB

Nu vom insista asupra creerii si modificarii de tabele in Query Analizer. Din punctul de vedere al implementarii aceste instructiuni nu sunt prea des folosite. Se pot crea si modifica obiecte din Enterprise Manager fara a necesita cunostinte de Transact SQL. Pentru informatii suplimentare se poate consulta BooksOnLine.

Mai departe se creeaza tabelele si legaturile intre tabele. Sa vedem cum se creeaza o legatura din Enterprise Manager intre doua tabele.

Construirea unei legaturi intre doua tabele

Trebuie sa stabilim o legatura intre tabelele Localitati si Institutii. O institutie se afla intr-o localitate. Informatia despre localitate se afla in tabela de Institutii prin campul LocalitateId. Acest camp este cheie primara in tabela Localitati. De ce este necesara stabilirea acestei legaturi? Deoarece exista o contabilizare a localitatilor in tabela Localitati si, pentru a fi siguri ca se va selecta ca localitate a unei institutii una din localitatile existente in tabela Localitati trebuie realizata o constrangere. Astfel, campul LocalitateId este cheie straina in tabela Institutii.

Cum stabilim efectiv legatura din Enterprise Manager?

Ne ducem in Enterprise Manager la baza de date Psihologie, expandam pana ajungem la lista obiectelor si facem click dreapta pe Diagrams. Apoi selectam optiunea New Database Diagram avand ca scop crearea unei diagrame. Tastam Next si va apare lista cu toate tabelele din baza de date, dintre care o parte sunt tabele sistem si nu ne intereseaza. Selectam tabelele Localitati si Institutii si fie apasand butonul Add fie facand dublu-click mutam tabelele pana la ultimul pas. Apoi apasand Finish diagrama este completata cu aceste doua tabele. Pentru a stabili relatia facem click pe campul LocalitateId din tabela Institutii si, tinand apasat pe mouse ne deplasam pana la campul LocalitateId din tabela Localitati. Cand am ajuns in dreptul campului LocalitateId din tabela Localitati eliberam mouse-ul. Ne va aparea un ecran cu numele Create Relationship. Lasam bifata optiunea 'Enforce relationship for INSERT's and UPDATE's' si apasand OK relatia va fi stabilita. Salvam diagrama si o denumim Localitati. Ni se va cere acceptarea modificarilor si, abia dupa aceasta, relatia va fi permanenta in baza de date.

Ce este o diagrama? Este un instrument grafic de vizualizare a relatiilor intre tabele. Mai mult, dupa cum am constatat, intr-o diagrama se pot stabili relatii intre tabele. O relatie ( constrangere ) se poate stabili si in SQL Query Analizer. Nu vom insista retinand modalitatea extrem de accesibila de stabilire a relatiilor in Enterprise Manager cu ajutorul diagramelor.

Rularea unui script in Query Analizer

Baza de date de test pe care suntem in curs de a o construi este una foarte simpla deoarece scopul este didactic. Mai trebuie adaugate tabelele de Subiecti ( cu descrierea subiectilor), o tabela de teste ( cu denumirea testelor) si o tabela de legatura intre subiecti si teste unde vor fi contabilizate notele subiectilor si datele cand au fost inregistrate aceste note. Nu vom construi acum aceste obiecte impreuna ( desi o puteti face singuri). Pentru generarea bazei de date cu toate tabelele si relatiile intre acestea am pregatit un script.

Ce este un script? Un script este fisier cu extensia SQL. Acest fisier contine un set de instructiuni SQL care realizeaza divese operatiuni ( de exemplu operatiuni de modificare a datelor, de afisare sau creare de obiecte ). Acest script se deschide in Query Analizer. De regula se realizeaza o verificare a instructiunilor din script pentru a se constata corectitudinea acestora. Apoi se mai verifica ca baza de date curenta sa fie cea in care se doresc a fi facute modificarile sau interogarile( in cazul nostru se verifica ca baza de date setata sa fie Psihologie). In final se executa scriptul ( apasand tasta F5).

In cazul nostru vom genera un script care va genera obiectele bazei de date Psihologie. Rulati acest script dupa o vizualizare a acestuia Tabele si Constrangeri.sql

Dupa rularea acestui script se va genera o baza de date care va contine cateva tabele si relatiile dintre acestea. Pentru vizualizarea acestor relatii se recomanda construirea unei diagrame care sa contina toate obiectele bazei de date. Capitolul urmator se va baza pe aceasta baza de date pentru a vedea cum realizam interogari de date, cum introducem sau modificam datele si cum stergem datele.