Episodul 3 – Dezvoltarea aplicatiei

Episodul 3 – Dezvoltarea aplicatiei

Pentru dezvoltarea aplicatiei, vom elabora un set minim de specificatii, care ne vor ajuta sa parcurgem impreuna urmatoarele etape, critice, si intilnite in toate etapele dezvoltarii unei baze de date. Inainte de intra in detalii, trebuie spus un lucru important: inainte de a intreprinde orice actiune cu privire la designul unei baze de date trebuie intelese datele si natura datelor care vor fi stocate in aceasta baza de date. Daca datele in sine (ca si semnificatie) sint relativ clare, ramine sa discutam despre natura datelor (sau tipul lor).

OBSERVATIE: Scopul acestui exercitiu nu este de a avea aceasta aplicatie gata in n zile si sa fie mai simpla sau mai complicata. La o prima vedere, poate ca structura pe care am ales-o pare mai complicata decit este nevoie, dar ceea ce as vrea sa se retina din acest exercitiu este logica folosita pentru a ajunge la o structura sau alta, conceptele folosite, metodologia, etc. Am incercat sa materializez anumite concepte abstracte: flexibilitatea aplicatiei pe termen lung, optimizarea datelor, etc. Daca am reusit sau nu, asta ramine la aprecierea fiecaruia din voi.

Dintre celelalte aspecte care trebuie luate in calcul la designul unei baze de date, cele mai importante ar fi urmatoarele:

1. 
   Securitatea datelor – aplicatia rulind pe local si datele nefiind critice, aspectul securizarii accesului la date nu-l vom mai discuta. Oricum, securitatea datelor este un subiect atit de vast, incit ar putea face obiectul unui tutorial separat (si, probabil, ca asa va si fi la un moment dat)
   
2. 
   Utilizarea preponderenta a bazei de date (pentru actualizari sau pentru rapoarte), dpdv al performantelor
   
3. 
   Constringeri de spatiu / resurse hardware
   
4. 
   Arhitectura aplicatiei (aplicatie distribuita, web, baza de date distribuita, etc)
   

Nu vom intra in amanunte pe aceste subiecte, din lipsa de spatiu. Dar acestea nu trebuie uitate de nici un dezvoltator / consultant cind propune / dezvolta o solutie pentru clientii sai.

Ca si date de pornire, pentru aspectele mai sus mentionate, vom presupune urmatoarele:

1. 
   Nu se doreste securizarea datelor, datele nefiind confindentiale sau critice
   
2. 
   Baza de date va fi o baza de date de uz personal, deci performanta nu este un criteriu critic
   
3. 
   Baza de date va ajunge la o dimensiune suportabila pe un calculator de uz personal
   
4. 
   Baza de date va fi utilizata local, pe un calculator de uz personal, interfata de exploatare va fi dezvoltata folosind facilitatile oferite de MS-Access 2003
   

Cerintele aplicatiei

Ca si minimum, aplicatia trebuie sa permita stocarea in baza de date a urmatoarelor detalii despre CD / DVD-uri:

 Titlul discului

 Standard disc (audio, mp3, divx, dvd, etc)

 Anul de aparitie

 Casa de discuri / casa de “filme” (Paramount, etc)

 Artist (in cazul audio, mp3 sau unde e cazul)

 Gen muzical / gen film

 Disc propriu (propriu vs imprumutat)

 Data cumpararii / imprumutarii

 Data returnarii

 Persoana (care ne-a imprumutat sau careia i-am imprumutat)

In acelasi timp, dorim ca aplicatia sa ne permita sa stim ce CD-uri / DVD-uri am imprumutat, de la cine si cind trebuie returnate; in aceeasi ordine de idei, dorim sa stim ce CD-uri / DVD-uri avem imprumtate, cui si pina cind.

Natura datelor
Dupa cum se observa din descrierea cerintelor de mai sus, majoritatea datelor folosite sint de tip data calendaristica sau de tip caracter. Natura datelor afecteaza si tipul de data pe care urmeaza sa-l folosim in SQL Server. Foarte pe scurt, SQL Server permite ca pentru acelasi cimp sa avem mai multe optiuni dintre care sa alegem. Astfel, pentru un cimp numeric, in functie de valorile pe care le determinam ca vor fi stocate in acel cimp, putem opta intre mai multe feluri de date numerice: tinyint, smallint, int, bigint, numeric si real (cu zecimale si nivele de precizie), bit, etc. Mai multe detalii despre data types se gasesc in SQL Server Books Online, cu cautare pe keyword-ul data types si selectind in lista de optiuni sectiunea Overview.

Ca si concluzie, intelegerea naturii datelor vehiculate si determinarea plajei de valori pe care urmeaza sa o stocam intr-un cimp anume este critica pentru faza de design a bazei de date si ne poate aduce un plus de performanta si o “economie” de spatiu necesar pe disc. O data facut designul unei baze de date cu privire la acest aspect, prea multe modificari nu se mai pot face, fara a afecta negativ partea de dezvoltare a aplicatiei care urmeaza sa foloseasca baza de date.

Aceste tipuri de date reprezinta o caracteristica importanta, in ceea ce priveste spatiul necesar, si anume faptul ca valorile care pot fi stocate in aceste cimpuri pot avea lungimi variabile, aspect deloc de neglijat in cazul cimpurilor care vor stoca date de tip sir de caractere (dar si in cazul cimpurilor cu date de tip intreg, in acest caz insa cistigul de spatiu este mai putin evident). Aceasta facilitate ne permite sa “economisim” spatiu fizic pe disc, in cazul in care acest aspect este o constringere. Ca si best-practice, este bine de stiut acest aspect si indusa obisnuinta folosirii cimpurilor cu lungime variabila. Poate parea un amanunt, dar cind vorbim de baze de date mari, acest “amanunt” devine important.

Sa luam un exemplu, si anume un cimp ale carui date pot avea lungimi diferite, cu un maxim de 50 de caractere si un minim de 3 caractere. O plaja larga de valori (80%) pot avea lungimi de aproximativ 20 – 25 de caractere (vom lua in calcul o medie de 23 de caractere), 5% vor avea lungimea minima (adica 3 caractere) iar restul de 15% vor avea lungimea maxima (adica 50 caractere). Numarul maxim de inregistrari in aceasta tabela a fost determinat la aproximativ 2 milioane de inregistrari. Ca sa se poata vedea mai bine diferenta, vom folosi tabela de mai jos (calculele sint aproximative):

Cimp cu lungime fixa	Cimp cu lungime variabila
2.000.000 x 50 bytes = 100.000.000 bytes = 100Mbytes (aprox)	1.600.000 (80%) x 23 bytes = 36.800.000 bytes = 36.8Mbytes (aprox) 100.000 (5%) x 3 bytes = 300.000 bytes = 0.3Mbytes (aprox) 300.000 (15%) x 50 bytes = 15.000.000 bytes = 15Mbytes (aprox)
Total: 100M	Total: 36.8 + 0.3 + 15 = 52.1M

Dupa cum se poate vedea din tabelul de mai sus, prin simpla folosire a unui tip de data sau a altuia se pot realiza “economii” importante de spatiu. Aceasta economie este obtinuta in mod nativ, datorita modului in care SQL Server trateaza aceste tipuri de date. In exemplul nostru, daca in baza de date avem de stocat 3 bytes, SQL Server va aloca exact spatiul de care e nevoie (3 bytes) in loc sa aloce 50 bytes. Acest tip de data este identificat in SQL Server sub numele de varchar (variable character). Mai multe detalii despre tipurile de date veti gasi studiind SQL Books online, cautind keyword-ul “data types”.

Aceasta economie se traduce in final prin performante imbunatatite, cerinte de administrare mai scazute, cerinte hardware mai reduse. Din aceasta cauza, alegerea unui tip de data sau a altuia, poate avea un impact major pe termen lung, desi la o prima vedere, acest aspect poate parea un amanunt.

Sa continuam si sa discutam putin despre tabelele care vor compune baza noastra de date.

Vom avea nevoie de o tabela care sa contina numele caselor de discuri sau, in cazul DVD-urilor, a caselor de productie filme, cu structura urmatoare:
Cod casa de discuri, numeric, identity
Denumire casa de discuri, varchar(50)

Vom mai avea nevoie de o tabela care sa contina diferitele standarde de discuri (mp3, audio, etc), cu urmatoarea structura:

Cod standard, numeric, identity
Denumire standard, varchar(50)

Vom mai avea nevoie de o tabela care sa contina diferitele genuri muzicale sau de filme (hip-hop, R&B, drama, etc), cu urmatoarea structura:

Cod gen muzical, numeric, identity
Denumire gen muzical, varchar(50)

Vom mai avea nevoie de o tabela care sa contina un fel de agenda cu datele de contact ale persoanelor care ne-au imprumutat discuri sau carora le-am imprumutat noi discuri. Aceasta tabela va trebui sa ne ofere, ca un minim, urmatoarele detalii: numele persoanei, adresa completa, numar de telefon si adresa de email. Pentru acesta, propun urmatoarea structura:

Cod persoana, numeric, identity
Nume persoana, varchar(50)
Adresa, varchar(100)
Numar de telefon, varchar(20)
Adresa email, varchar(30)

Cele de mai sus, sint doar tabelele auxiliare care vor fi folosite de catre aplicatie. Tabela urmatoare contine datele de baza ale aplicatiei, si anume colectia de discuri. In aceasta tabela, vom memora, in loc de denumirea in clar a genurilor muzicale sau a numelor caselor de discuri sau de filme, codul asociat acestora, pentru a permite modificarea facila a acestor detalii in tabelele care contin aceste detalii, fara sa afectam tabela de discuri.

Structura pe care v-o propun pentru aceasta tabela este descrisa in imaginea de mai jos:

Toate tabelele definite pina acuma, le-am definit cu ajutorul Microsoft Visio 2003 for Enterprise Architects. Acest proces poarta numele de “modelare” si are unele avantaje deloc de neglijat. Cel mai important este faptul ca permite modificarea structurii bazei de date pina cind se ajunge la un model stabil, functional, care sa acopere toate nevoile aplicatiei si a clientului. Toate acestea fara sa lucram inca cu SQL Server sau sa scriem o singura linie de cod in Transact-SQL sau MS-Access. Microsoft Visio 2003 for Enterprise Architects (disponibil in pachetul Microsoft Visual Studio 2003 Enterprise Architect) ofera aceasta facilitate si in plus, cind totul este gata, prin conectori ODBC se poate conecta la serverul SQL dorit si sa creeze baza de date conform modelului construit de noi.

Pentru a definitiva modelul bazei de date, vom trece la modelarea relatiilor dintre tabele. Voi lasa schema sa vorbeasca de la sine si vom vedea in lectiile urmatoare cum ne vom folosi de aceste relatii.

Pentru a va usura munca, va voi oferi pentru download scriptul SQL care va va crea baza de date, la finele articolului. Desi, de preferat ar fi, dpdv al scopului acestui tutorial, sa creati baza de date manual, din Enterprise Manager.

Obiectele unei baze de date
Intr-o baza de date, datele sint stocate in tabele. Pe linga aceste tabele, intr-o baza de date SQL mai gasim si alte obiecte, cum ar fi: views (sau vederi), users (userii care au acces la baza de date respectiva), stored procedures (sau proceduri stocate – bucati de cod sau instructiuni Transact-SQL proiectate sa execute anumite operatii bine determinate), etc.

Obiectele unei baze de date se pot vedea in imaginea de mai sus. Imaginea de mai sus afiseaza, de fapt, tipurile de obiecte care pot fi definite in cadrul unei baze de date. Le vom lua, pe scurt, pe rind pe fiecare din ele:

 Diagrams – contine diagramele care reprezinta schematic legaturile dintre tabelele bazei de date

 Tables – contine tabelele care, la rindul lor, contin datele utile ale bazei de date

 Views – contine definitia vederilor (sau a interogarilor salvate) folosite de aplicatie pentru a interoga baza de date

 Stored Procedures – contine definitiile procedurilor stocate folosite pentru a efectua anumite operatii predefinite asupra datelor si / sau a tabelelor din baza de date

 Users – contine utilizatorii care au permisiuni (pe diferite nivele) in cadrul bazei de date curente

 Roles – contine grupurile de utilizatori pre-definiti in cadrul SQL Server si in cadrul bazei de date. Aceste grupuri contin permisiuni predefinite care se pot aplica userilor pentru accesul la date

 Rules – contine regulile de validare a datelor de la nivelul bazei de date. Regulile de validare a datelor se pot implementa pe 2 nivele (cel putin), si in cazul nostru mai specific, la nivelul tabelelor (la nivel declarativ al tabelelor) si la nivelul interfetei utilizator (asa numita validare pe client). Avantajele folosirii validarii la nivelul bazei de date sint in principal la capitolul performanta (clientul nu mai trebuie sa faca validarile, deci va rula mai rapid, validarile pe server merg mai rapid datorita resurselor hardware mai mari, etc).

 Defaults – contine definitiile valorilor implicite definite de utilizator pentru baza de date. Aceste defaults sint folosite la definirea tabelelor la specificarea valorilor implicite care sa fie introduse in tabele cind utilizatorul, prin interfata oferita, nu furnizeaza toate datele pentru a actualiza tabelele. Vom vedea in cadrul exercitiului practic utilitatea acestor defaults.

 User Defined Datatypes – am discutat putin mai sus despre tipurile de date. Aici se pot defini tipuri de date conform cerintelor sau specificului aplicatiei sau a bazei de date.

 User Defined Functions – contine definitiile functiilor definite de dezvoltator / implementator si care functii fac diferite operatii asupra datelor sau a altor obiecte ale bazei de date

 Full-Text Catalogs – contine cataloagele full-text existente in cadrul bazei de date. Aceste cataloage contin referinte sistem cu privire la indecsii full-text. Un exemplu de indecsi full-text si o baza de date care foloseste acest tip de indecsi ar fi o baza de date cu CV-uri, cind se doreste cautarea in cadrul acestor CV-uri dupa cuvinte cheie.

Din pacate, nu avem mai mult spatiu la dispozitie pentru a detalia aceste obiect, dar pentru cei interesati, SQL Server Books Online contine detalii despre aceste tipuri de obiecte.

Noi vom merge mai departe cu exercitiul practic si vom trece la definirea tabelelor bazei de date, folosind SQL Server Enterprise Manager.

Voi exemplifica practic cum se creeaza tabela tbl_Colectie, urmind ca pe baza definitiilor de mai sus pentru restul tabelelor, sa creati tabelele auxiliare. Creerea diagramei va incheia lectia de azi, urmind ca in partea a 4-a sa trecem la lucrul in MS-Access.

Creerea tabelelor folosind Enterprise Manager
Pentru creerea tabelelor, vom incepe prin a creea baza de date. Modul in care SQL Server gestioneaza bazele de date nu face obiectul acestui tutorial, dar foarte pe scurt, lucrurile se intimpla in felul urmator. Fiecare baza de date are 2 componente: fisierele cu date (sau data files) si log-ul operatiilor (sau transaction log). Data files contin tabelele si toate celelalte obiecte dintr-o baza de date. Transaction log-ul contine un istoric al modificarilor efectuate asupra datelor. Dimensiunea acestui istoric variaza la fiecare baza de date, in functie de nevoile specifice ale acestei baze de date.

Ca si functionare, orice modificare se doreste a fi operata asupra datelor, se inregistreaza intii in transaction log (care este o scriere fizica pe disc) si abia apoi trimisa spre executie. SQL Server lucreaza la nivel de tranzactii. Adica o modificare asupra datelor se considera un bloc unitar, care se executa integral cu succes sau nu se executa deloc (un exemplu ar fi un update asupra unor inregistrari urmat de un insert de inregistrari noi, insert care este conditionat de primul update – ori se executa ambele instructiuni cu succes ori deloc). Este “treaba” aplicatiei sa detecteze erorile tranzactionale si sa trateze aceste erori. Acesta este, foarte schematic, modul in care lucreaza SQL Server.

Fiecare din cele 2 componente (data files si transaction log) sint formate din 1 sau mai multe fisiere pe disc. Data files au extensia MDF iar transaction log-ul are extensia LDF.

In cele ce urmeaza, vom creea o baza de date in care vom trece la creerea tabelelor necesare aplicatiei noastre.

Se lanseaza Enterprise Manager, se expandeaza in lista serverul curent si se da click dreapta pe Databases, click pe New Database.

Se completeaza numele bazei de date. Click pe tab-ul Data Files.

Se mareste dimensiunea implicita la 10MB, se lasa restul optiunilor cu valorile implicite si click pe tab-ul Transaction Log.

Se lasa valorile implicite si click pe OK. SQL Server va crea baza de date dupa care ar trebui sa vedem aceasta noua baza de date in Enterprise Manager.

In stinga vedem baza de date ITZone creata anterior, iar in partea dreapta, sint afisate containerele pentru obiectele din aceasta baza de date. Expandind baza de date, aceleasi containere apar si in partea stinga.

Vom trece acum la creerea tabelelor. Pentru exemplificare, vom crea impreuna tabela centrala a aplicatiei, tbl_Colectie. Creerea restului tabelelor o las pentru voi ca exercitiu.

Cu baza de date ITZone selectata si expandata, click dreapta pe containerul Tables si selectata optiunea New Table….

Completate detaliile conform imaginii de mai jos si apoi salvata tabela cu numele tbl_Colectie.

Simbolul cheitei galbene din dreptul cimpului DiscID arata faptul ca acel cimp este index primar in tabela noastra. “Bifele” din coloana “Allow Nulls” specifica faptul ca in acele cimpuri se permite ca utilizatorul sa nu specifice nici o valoare cind se introduc valori noi sau se face un update.

Similar cu acest ecran, se vor crea tabelele auxiliare ale aplicatiei noastre. Descrierile acestora sint in acest tutorial.

Pentru mai multe detalii privitor la fiecare din optiunile disponibile in Table Designer puteti accesa SQL BOL (Books Online).

Pentru comoditate, am inclus scriptul care creeaza baza de date si tabelele necesare. In acelasi script sint creati si toti indecsii necesari ordonarii datelor in forma dorita de noi.

Pentru rularea acestui script, se procedeaza astfel:

Se download-eaza scriptul si se salveaza intr-un folder in care este accesibil pentru SQL Server. Dupa aceasta, se lanseaza SQL Query Analyzer.

Click pe OK

Meniul File, Open… si navigat pina in folderul in care este salvat scriptul si selectat acel script.

Dupa ce scriptul este afisat pe ecran, va trebui modificata calea unde sa fie stocate pe disc fisierele MDF si LDF asociate bazei de date (zona highlight din script).

Modificati textul highlight sa pointeze spre un folder pe calculatorul unde ruleaza SQL Server si apoi click pe butonul Run Script (incercuit in imaginea de mai sus) pentru a rula scriptul.

Dupa rularea scriptului, daca nu au aparut probleme, SQL Query Analyzer va afisa mesajele:

Iar in folderele mentionate in script, vor aparea urmatoarele fisiere:

Dupa rularea scriptului, in Enterprise Manager va aparea noua baza de date si obiectele definite in aceasta.

Se observa tabelele create in urma rularii scriptului.

Apasa aici pentru a downloada scriptul

Concluzie
Desi s-ar mai putea discuta mult la acest capitol, sa nu uitam ca SQL Server este un produs cu o arhitectura complexa si o functionalitate mult peste scopul acestui tutorial. Daca ar fi sa discutam in detaliu despre un anumit subiect asociat SQL Server, am putea lejer sa acaparam tot spatiul de care dispune ITZone. In plus, exista tone de informatie disponibile online atit pe site-ul Microsoft (www.microsoft.com/sql) cit si pe diferite forumuri specializate pe SQL Server. Alte zone de interes online: TechNet, MSDN. Sau, un simplu Google pe “sqlserver” si va va afisa o lista considerabila de resurse online despre SQL Server.

Las ca si exercitiu pentru voi creerea unei diagrame a bazei de date proaspat create de noi. Puteti folosi schema relatiilor dintre tabele prezentata mai sus folosind Visio. Voi publica in partea a 4-a diagrama, pentru ca fiecare din voi sa se poata verifica.