Proiect Baze de Date

UNIVERSITATEA “POLITEHNICA” TIMISOARA

FACULTATEA DE AUTOMATICA SI CALCULATOARE

Proiect Baze de Date

Proiectul 2

Student : 242f56c

Proiectul numarul 2

Enuntul proiectului :

Se considera o aplicatie online care este utilizata pentru realizarea bilantului contabil al unei firme. Aplicatia foloseste pentru stocarea datelor o baza de date Oracle. Stiind ca trebuie stocate informatiile prezentate in tabelele urmatoare si

ca informatiile trebuie sa respecte urmatoarele constrangeri: nume companie 50 de caractere, numar cont cinci cifre, descriere 50 de caractere, tip cont 2 caractere, balanta initiala, debitul, creditul si balanta ajustata nu poat avea mai mult de doua zecimale si nu poat fi mai mari decat 1000000, numarul tranzactiei nu poate fi mai mare decat 10000, iar suma tranzactionata pentru un cont intr-o singura tranzactie nu poate fi mai mare decat 10000.

Cerintele proiectului :

1. Sa se realizeze proiectarea bazei de date aferente (structura de tabele, structura de coloane a fiecarei tabele, constrangeri):

i. Proiectare logica a bazei de date (forme normalizate) => structura de tabele;

ii. Dimensionare coloane, constrangeri;

2. Sa se scrie comenzile SQL pentru tabelele proiectate la punctul anterior ;

3. Sa se implementeze un trigger care atunci cand este introdusa o tranzactie automat sa calculeze debitele, creditele si balanta ajustata pentru toate conturile implicate in tranzactie ;

4. Sa se implementeze o functie care sa verifice daca o tranzactia este valida (suma tuturor conturilor dintr-o tranzactie trebuie sa fie zero), functia primeste ca si parametru de intrare numarul tranzactiei pentru care se doreste verificarea ;

5. Sa se afiseze toate tranzactiile in care este folosit un anumit cont ;

6. Sa se afiseze toate tranzactiile care au fost introduse in perioada (01.01.2007-01.06.2007) ;

7. Sa se calculeze suma Balantelor Ajustate pentru toate conturile ;

8. Sa se afiseze toate tranzactiile care implica conturi de un anumit tip ;

9. Sa se stearga un count daca nu exista tranzactii pentru el ;

10. Sa se afiseze contul care apare in cele mai multe tranzactii precum si numarul de tranzactii in care el apare.

Precizam faptul ca vom avea 2 tabele, una va fi tabela ‘parinte’ si cea de-a doua va fi tabela ‘copil’, dupa care se vor introduce o serie de date in tabela parinte, care in cazul nostru este cont, trebuie sa tinem cont si de faptul ca exista unele coloane in care este necesara calcularea datelor in functie de alte coloane ale tabelei parinte, si deasemenea in functie de coloanele tabelei copil care in cazul nostru este tabela tranzactie Avem nevoie si de un trriger, care trebuie creat inaintea popularii tabelei copil (tranzactie) cu date, aceste valori vor putea fi calculate si salvate in acelasi timp in tabela parinte (cont), dupa o inserare in tabela copil (tranzactie).

Trebuie sa avem in vedere faptul ca este vorba despre contabilitatea unei firme, fiecare tranzactie care este introdusa in tabela copil (tranzactie) va fi inserata de doua ori, dar liniile nefiind identice, deoarece se presupune ca o suma extrasa dintr-un cont trebuie depusa intr-un alt cont pentru a fi respectata conditia de validare a acestora.

Acestea fiind spuse pentru verificarea modului de lucru al aplicatiei se vor introduce si date care nu sunt valide, si anume pentru afisarea cu ajutorul unei functii a mesajului corespunzator a unei tranzactii validate, si a unei tranzactii care nu este validata, dupa care cu ajutorul comenzilor existente in SQL, se va opera asupra bazei de date, cu afisarea rezultatelor in scopul verificarii acestora.

Rezolvare :

1. Sa se realizeze proiectarea bazei de date aferente (structura de tabele, structura de coloane a fiecarei tabele, constrangeri):

i. Proiectare logica a bazei de date (forme normalizate) => structura de tabele;

ii. Dimensionare coloane, constrangeri;

i.Crearea tabelei cont

create table cont

(

nume_companie VARCHAR(50),

numar_cont NUMBER(5) PRIMARY KEY CHECK (numar_cont>0),

descriere VARCHAR(50),

tip_cont VARCHAR(2),

balanta_initiala NUMBER(9,2) DEFAULT 0 CHECK (balanta_initiala<=1000000),

debite NUMBER(9,2) DEFAULT 0 CHECK (debite<=1000000),

credite NUMBER(9,2) DEFAULT 0 CHECK (credite<=1000000),

balanta_ajustata NUMBER(9,2) DEFAULT 0 CHECK (balanta_ajustata<=1000000)

ii. crearea tabelei tranzactie:

create table tranzactie

(

numar_tranzactie NUMBER(5) CHECK (numar_tranzactie<=10000),

data DATE DEFAULT SYSDATE,

numar_cont NUMBER(5) NOT NULL,

suma NUMBER(5) CHECK (suma<=10000),

CONSTRAINT A FOREIGN KEY(numar_cont)

REFERENCES cont(numar_cont) ON DELETE CASCADE

);

2. Sa se scrie comenzile SQL pentru tabelele proiectate la punctul anterior , popularea cu date a tabelei parinte (cont) :

insert into cont (nume_companie, numar_cont, descriere, tip_cont, balanta_initiala,

credite, debite, balanta_ajustata) VALUES ('Asirom','1','asigurare','01','25000',

insert into cont (nume_companie, numar_cont, descriere, tip_cont, balanta_initiala,

credite, debite, balanta_ajustata) VALUES ('Unita','2','asigurare','01','45000',

insert into cont (nume_companie, numar_cont, descriere, tip_cont, balanta_initiala,

credite, debite, balanta_ajustata) VALUES ('Banca Transilvania','3','banca','10','85000',

insert into cont (nume_companie, numar_cont, descriere, tip_cont, balanta_initiala,

credite, debite, balanta_ajustata) VALUES ('Audi','4','autoturism','11','30000',

insert into cont (nume_companie, numar_cont, descriere, tip_cont, balanta_initiala,

credite, debite, balanta_ajustata) VALUES ('PetromV','5','petrol','00','50000',

insert into cont (nume_companie, numar_cont, descriere, tip_cont, balanta_initiala,

credite, debite, balanta_ajustata) VALUES ('OMV','6','petrol','00','80000',

insert into cont (nume_companie, numar_cont, descriere, tip_cont, balanta_initiala,

credite, debite, balanta_ajustata) VALUES ('VW','7','autoturism','01','40000',

select * from cont ;

3.Calcularea debitelor, creditelor si a balantei initiale pentru toate conturile atunci cand se introduce o tranzactie:

CREATE OR REPLACE TRIGGER triger1

AFTER INSERT ON tranzactie

FOR EACH ROW

DECLARE

var tranzactie.suma%type;

BEGIN

var:=:new.suma;

IF(var<0) THEN

UPDATE cont SET

credite=credite+var,

balanta_ajustata=balanta_ajustata+var

WHERE cont.numar_cont=:NEW.numar_cont;

ELSE

UPDATE cont SET

debite=debite+var,

balanta_ajustata=balanta_ajustata+var

WHERE cont.numar_cont=:NEW.numar_cont;

END IF;

END;

/

insert into tranzactie values (100,'26-FEB-2006',1,-5000);

insert into tranzactie values (100,'26-MAR-2007',3,5000);

insert into tranzactie values (102,'26-APR-2006',2,-2000);

insert into tranzactie values (102,'12-FEB-2007',4,9000);

insert into tranzactie values (103,'26-DEC-2005',7,-4000);

insert into tranzactie values (103,'22-APR-2008',1,5000);

insert into tranzactie values (103,'26-JUN-2006',1,8000);

insert into tranzactie values (104,'16-FEB-2005',4,-3000);

select * from tranzactie;

select * from cont;

4. Verificam daca o tranzactie este valida, suma tuturor conturilor din ea trebuie sa fie 0, sau este diferita de aceasta valoare :

CREATE OR REPLACE FUNCTION functiev(var NUMBER)

RETURN VARCHAR AS

x NUMBER(7,2);

BEGIN

SELECT SUM(suma) INTO x FROM tranzactie WHERE numar_tranzactie=var;

IF x=0 THEN

RETURN 'Tranzactia este valida!';

ELSIF x<>0 THEN

RETURN 'Tranzactia nu este valida!';

ELSE RETURN 'Tip invalid!';

END IF;

END functiev;

select functiev(2) from dual;

select functiev(100) from dual;

select functiev(103) from dual;

Afisarea tuturor tranzactiilor dintr-un anumit cont :

select numar_tranzactie from tranzactie where numar_cont=2;

5. Afisarea tuturor tranzactiilor care au fost introduse intr-o anumita perioada, fara a se afisa aceeasi tranzactie care a mai aparut anterior:

select distinct numar_tranzactie from tranzactie where data<='01-jun-2007' and

data>='01-jan-2007';

6. Calcularea sumei balantelor ajustate pentru toate conturile :

select sum(balanta_ajustata) from cont;

7. Calcularea sumei balantelor ajustate pentru fiecare cont:

select sum(balanta_ajustata) as suma_balantelor from cont group by numar_cont;

8. Afisarea tuturor tranzactiilor care implica conturi de un anumit tip, fara a se afisa aceeasi tranzactie de mai multe ori:

select distinct numar_tranzactie from tranzactie, cont where cont.numar_cont=

tranzactie.numar_cont and cont.tip_cont='01';

9. Stergerea unui cont pentru care nu exista tranzactii :

delete from cont where numar_cont not in (select numar_cont from tranzactie);

select * from cont;

10. Afisarea contului care apare in cele mai multe tranzactii, si numaul de tranzactii in care apare acesta:

select cont.numar_cont, count(numar_tranzactie)

from tranzactie,cont

where (cont.numar_cont=tranzactie.numar_cont)

group by cont.numar_cont

having count(numar_tranzactie)=

(select max(count(numar_tranzactie)) from tranzactie group by numar_cont);