Laborator 2
Proiectarea unei baze de date
Introducere
Bazele de date sunt indispensabile in majoritatea aplicatiilor de astazi. Succesul unei aplicatii depinde in mare masura de modul in care sunt organizate si folosite datele. Aceasta organizare trebuie sa permita accesul cat mai facil si eficient la informatiile stocate. Astfel proiectarea bazei de date este strans legata de modelul "business" al aplicatiei. Astfel obiectele gestionate de aplicatie trebuie sa aiba entitati corespunzatoare in baza de date. De asemenea, relatiile dintre obiecte se regasesc si in baza de date.
Formularea cerintelor
Dorim spre exemplu sa tinem gestiunea resurselor umane si a activitatilor unei companii. Fiecare angajat face parte dintr-o echipa. Fiecare echipa are un sef. Fiecare proiect presupune un buget, o data de inceput si un termen limita de realizare si poate fi atribuit unei singure echipe. In cadrul unui proiect exista mai multe sarcini care pot fi atribuite unora sau mai multor membri. Fiecare sarcina este atribuita la o anumita data unuia sau mai multor membri si are un termen limita. O sarcina poate fi inceputa, in curs sau terminata (sau mai bine un stadiu de realizare? e.g. 10%, 20%).
Abstractizarea
sau stabilirea entitatilor si a relatiilor
Aceasta faza consta in extragerea unui model pornind de la cerinte si presupune urmatoarele etape.
Identificarea subiectelor (entitatilor) care vor deveni tabele in baza de date. Entitatile reprezinta clase de obiecte in general. Instantele acestora (deci contextul particular) reprezinta inregistrarile. Entitatile pot avea mai multe proprietati (atribute) care trebuie de asemeni identificate plecand de la cerinte. Pentru fiecare atribut trebuie stabilit si domeniul de valori posibile (tipul de date), daca atributul are o valoare implicita sau daca este obligatoriu (adica valoarea sa poate fi necunoscuta sau neaplicabila pentru anumite instante). Fiecare entitate trebuie sa contina unul sau mai multe atribute care identifica unic o instanta, numit cheie primara (primary key).
EMP
UID
NAME
JOB
HIREDATE
TEAM
TID
NAME
LEADER
PROJECT
PID
NAME
BUDGET
DEADLINE
TASK
TaID
NAME
BEGINDATE
ENDDATE
STATUS
Stabilirea relatiilor dintre entitati. O relatie este o asociere intre doua entitati. Asocierea este de regula asimetrica.
Stabilirea tipului de relatii intre entitati. Exista mai multe tipuri de relatii, clasificate dupa multiplicitate:
  1. •one-to many. Unei instante a primei entitati ii pot corespunde mai multe instante din cea de-a doua. Reciproca nu este valabila.
  2. •many-to-many Unei instante a primei entitati ii pot corespunde mai multe instante din cea de-a doua si invers. Acest tip de relatie se poate dovedi dificil de identificat si de rezolvat in anumite situatii.
  3. •one-to-one. Unei instante a primei entitati ii poate corespunde o singura instanta din cea de-a doua.
Transcrierea relatiilor dintre entitati. In baza de date relatiile se transcriu tot prin intermediul atributelor si a cheilor.
Astfel, o relatie one-to-many se transcrie printr-o referinta de la capatul many catre capatul end. Astfel capatul many detine o referinta la instanta corespunzatoare a capatului one. Aceasta referinta se numeste cheie straina (foreign key) si poate lua ca valoare o valoare a cheii primare din capatul one.  Atributele care servesc la modelarea relatiilor sunt ilustrate colorat in tabelele de mai jos.
EMP
UID
NAME
JOB
HIREDATE
TEAM
TEAM
TID
NAME
LEADER
PROJECT
PID
NAME
BUDGET
DEADLINE
TEAM
TASK
TaID
NAME
BEGINDATE
ENDDATE
STATUS
PROJECT
Relatiile many-to many devin ele insele entitati care se interpun intre relatiile originale. Astfel noile entitati sunt in relatie  many-to-one cu fiecare din relatiile originale.
TASK_EMP
TaID
EMP
Modelarea efectiva a bazei de date
Se realizeaza prin comenzi SQL (Structured Query Language). SQL este un limbaj specializat in crearea, obtinerea, modificarea si stergerea datelor dintr-o baza de date relationala, ansamblu cunoscut si sub forma CRUD(create, retrieve,update,delete). Comenzile folosite pentru definirea obiectelor din baza de date se numesc DDL (Data Definition Language).
Comenzile SQL DDL
 
CREATE TABLE pentru createa unui tabel
Sintaxa
CREATE TABLE <nume_tabel> (
    <atribut1> tip_date(<dim>) [<restrictie>],
    <atribut2> tip_date(<dim>) [<restrictie>],
    ...)
Este posibil ca odata cu crearea structurii sa importam si datele existente in tabelele existente prin adaugarea clauzei AS SELECT
AS SELECT <atribut1>,<atribut2>,...
FROM <nume_tabel1>
WHERE <predicat>;
Restrictiile <restrictie> asupra atributelor tabelelor (<constraint>) cuprind:
  1. •CONSTRAINT
  2. •DEFAULT
  3. •NOT NULL Impune ca valorile NULL sa nu fie permise pentru coloana. Coloanele fara restrictia NOT NULL pot contine valori NULL, cu semnificatia: valoare necunoscuta, nedefinita sau ne-aplicabila. Aceasta restrictie poate fi specificata numai la nivel de coloana si nu poate fi specificata la nivel de tabela
  4. •UNIQUE O restrictie de integritate UNIQUE impune ca orice valoare intr-o coloana sau multime de coloane  sa fie unica, adica orice doua linii nu pot avea aceeasi valoare in coloana sau in multimea de coloane specificata - cheie unica (unique key), sau cheie unica compusa (composite unique key) pentru multimea de coloane.
  5. •CHECK. Defineste o conditie logica care trebuie indeplinita de fiecare inregistrare.
  6. •PRIMARY KEY. Definirea de chei primare se face cu clauza PRIMARY KEY (<lista atr>)
  7. •FOREIGN KEY permite definirea de chei straine, cu specificarea tabelei parinte si a coloanelor referite in aceasta.
FOREIGN KEY (<lista atr>) REFERENCES <nume_tabel>(<lista atr>) ON DELETE CASCADE
Exemplu
CREATE TABLE emp (
    eid     NUMBER(2),
    name     VARCHAR(15),
    job     VARCHAR(15),
    hiredate DATE,
CONSTRAINT emp_pk PRIMARY KEY(eid)
CONSTRAINT emp_team_fk FOREIGN KEY (team)
REFERENCES team (tid)
) ;
ALTER TABLE pentru modificarea structurii unui tabel (adaugare/stergere/modificare coloane)
Sintaxa
ALTER TABLE <nume_tabel> ADD <nume_coloana> <definitie_coloana>
ALTER TABLE <nume_tabel> MODIFY <nume_coloana> <definitie_coloana>
ALTER TABLE <nume_tabel> DROP <nume_coloana>
Aceasta comanda permite de asemenea si adaugarea/stergerea de restrictii.
Exemplu
ALTER TABLE emp
ADD CONSTRAINT emp_team_fk
    FOREIGN KEY (team) REFERENCES team (tid) ;
DROP TABLE pentru stergerea unui tabel
Sintaxa
DROP TABLE <nume_tabel> [CASCADE CONSTRAINTS]
Clauza CASCADE CONSTRAINTS este foarte utila in cazul in care tabelul de sters este tabel parinte. In acest caz, se sterg toate cheile externe care refera tabelul, apoi est esters si acesta.
Exercitiu
Sa se creeze baza de date anterioara folosind comenzi DDL
Comenzile SQL DML (Data Manipulation Language)

INSERT - permite inserarea uneia sau mai multor inregistrari intr-un tabel.
Sintaxa INSERT INTO <nume_tablou> (<nume_coloana1>, [<nume_coloana2>, ... ]) VALUES (<valoare1>, [<valoare2>, ...])  
UPDATE - permite modificarea uneia sau mai multor inregistrari dintr-un tabel.
Sintaxa UPDATE <nume_tablou> SET <nume_coloana> = <valoare> [, <nume_coloana> = <valoare> ...] [WHERE <conditie>];  
DELETE - permite stergerea uneia sau mai multor inregistrari dintr-un tabel.
Sintaxa DELETE FROM <nume_tablou> [WHERE <conditie>]  
Exercitiu
Avand baza de date anterioara, sa se introduca in tabelele create inregistrarile de mai jos. Se va folosi comanda SQL INSERT.
EMP
EID
NAME
JOB
HIREDATE
TEAM
1
John Smith
developer PHP
01-01-2005
2
2
Natasha Waters
manager
10-11-1999
1
3
Valery Mitchels
tester
05-09-2003
1
4
Steven Philips
manager
09-11-2001
2
5
Thomas Dow
developer JAVA
11-12-2004
1
6
William Vix
developer JAVA
11-20-2001
1
TEAM
TID
NAME
LEADER
1
research
2
2
webs
4
PROJECT
PID
NAME
BUDGET
DEADLINE
TEAM
1
company’s website
20000
12-20-2006
2
2
intranet system
33000
10-20-2006
1
TASK
TaID
NAME
BEGINDATE
ENDDATE
STATUS
PROJECT
1
general specifications
01-01-2006
01-02-2006
finished
2
2
database design
02-03-2006
02-10-2006
finished
1
3
development
02-12-2006
07-30-2006
finished
1
4
development
02-05-2006
10-30-2006
in progress
2
5
testing
10-09-2006
10-19-2006
not started
2
TASK_EMP
TaID
EMP
1
1
2
4
3
1
4
6
4
5
5
3
 
Mediul SQLPlus
 
Este un program simplu care furnizeaza interfata la linie de comanda cu serverul Oracle.
Spre deosebire de alte shell-uri, nu are history.
Permite introducerea a trei tipuri de comenzi:
  1. •comenzi SQL: pentru a fi executate imediat, trebuie incheiate cu caracterul “;”
  2. •comenzi specifice SQLPlus
  3. •comenzi Oracle PL/SQL
 
Comenzi SQLPlus
help <item>
Afisarea structurii unui tabel:
describe <nume_tabel>
Afisarea tabelelor utilizatorului
select TABLE_NAME from USER_TABLES;
Salvarea comenzilor intr-un fisier:
describe studenti
save studenti.sql
Lansarea in executie a comenzilor din fisier
@studenti.sql
start studenti sql
Redirectarea output-ului unei comenzi:
spool studenti.out
describe studenti
spool off
Personalizare output
set heading on | off
set feedback on | off
set timing on | off
Secvente escape
& folosit pentru a inlocui un string cu sirul de caractere primit de la linia ce comanda.
select &nume from studenti;
Enter value for nume: *
=>  se executa select * from studenti;
Daca dorim sa utilizam caracterul &, folosim secvente escape:
set escape \
select * from studenti where obs like ‘%\&%’