SGBDAcademia de Studii EconomiceBucureti

Cibernetic, Statistic iInformatic Economic

Disciplina: SGBD

Numele firmei: S.C. APISALECOM S.R.L.

Student: Alecu CristianGrupa 1022Seria A

Profesor: Vlad DiaconiaScurt prezentare a firmeinfiinat n anul 1993, firma S.C. Apisalecom S.R.L. este situat n oraul Comneti, judeul Bacu. Firma proceseaza din anul 2003, odata cu obinerea licenei de fabricaie, produse apicole (miere de albine, polen, propolis, ceara, tinctura de propolis). Principalul produs ambalat este mierea, livrat sub forma a patru sortimente distincte: salcm, tei, munte si poliflor. n acest moment, mierea este ambalat in borcane din sticl avnd coninutul de 400g net. Din anul 2006, am inceput livrrile ctre o serie de firme de produse farmaceutice si cosmetice naturale, a materiei prime sub denumirea: Propolis brut in stare natural. Creterea vnzrilor a fost serioas ajungnd acum principalul lor furnizor de propolis si cear. Acelai propolis prelucrat de noi este vndut en-detail i sub form de tinctur la sticlue de 20ml sau bulgre la pacheele de 10 g. Vnzrile se fac prin firme de distribuie produse naturiste in numr de apte: trei in Bucureti, dou n Vaslui, una n Bacu i una n Galai, acestea avnd ponderea principal a vnzrilor. Materia prim este achiziionat n mare parte de la productorii particulari pe baz de borderouri de achiziie iar plata se face cu O.P. sau numerar. Zona acoperit este destul de larg dar in principal achiziia o facem din Ardeal. O alt parte a materiei prime se obine din stupina proprie. n acest moment deinem un numr de 180 de familii de albine achiziionate prin program SAPARD. Spaiul pe care l deinem n acest moment este de aproximativ 450 de metri patrai i ne permite ambalarea a cca. 1.000 de borcane cu miere pe zi, cu ajutorul a nou persoane angajate. Mierea este subsanta zaharoas pe care o produc albinele prin colectarea nectarului floral i extrafloral sau a unui alt suc luat de pe plantele vii, prin transformarea lui, sub aciunea enzimatic a salivei i a sucului gastric al albinelor. n timpul depozitrii nectarului n celule, apa care este n plus va fi ndeprtat prin ventilare. Mierea conine 20% ap. Pentru a obine 1 litru de miere, sunt necesare 5 kg de nectar. Pentru a obine 1 kg de nectar, este nevoie de 20.000 - 100.000 de zboruri. Un roi de albine (30.000-60.000) poate fabrica 1kg de miere pe zi. 100 de grame de miere, (furnizeaza 335 de calorii) conin 17,2% ap, 38,19% fructoz, 31,28% glucoz, 6,5% zaharoz si 6,83% maltoz i alte dizaharide. Prin comparaie, 100 de grame de zahr conin 0,1% ap si 99.9% zaharoz.

Scopul bazei de datePentru a gestiona cu usurin informaiile legate de produsele i comenzile diferiilor clieni, am creat aceast baz de date, care cuprinde 5 tabele: Angajai, Clieni, Comenzi, Produse i Rnd Comenzi. Pentru crearea tabelelor, am folosit codul de mai jos, cu ajutorul funciei CREATE TABLE: create table PRODUSE(id_produs number(5),denumire_produs varchar2(30) not null,descriere varchar(500),cantitate_produs number(8),pret_lista number(8,2) );

create table RAND_COMENZI( nr_comanda NUMBER(12), id_produs NUMBER(5) not null, pret NUMBER(8,2), cantitate NUMBER(8) );

create table COMENZI(nr_comanda NUMBER(12),data_comanda DATE,id_client NUMBER(5),stare_comanda VARCHAR2(25),id_angajat NUMBER(5) );

create table ANGAJATI(id_angajat NUMBER(5), prenume VARCHAR2(20) not null, nume VARCHAR2(20) not null, email VARCHAR2(25) unique, telefon VARCHAR2(20) unique, data_angajare DATE, salariul NUMBER(8,2) );

create table CLIENTI( id_client NUMBER(5),nume_client VARCHAR2(20),cod_fiscal VARCHAR2(15),strada_client VARCHAR2(30),numar_client NUMBER(5),telefon_client VARCHAR2(15) not null,email_client VARCHAR2(30) not null );

Iar ca restricii, am folosit urmtoarele funcii:

ALTER TABLE PRODUSEADD CONSTRAINT pk_produse PRIMARY KEY(id_produs);ALTER TABLE PRODUSEADD CONSTRAINT ck_produse CHECK(descriere IS NOT NULL);

ALTER TABLE RAND_COMENZIADD CONSTRAINT pk_rand_comenzi PRIMARY KEY(nr_comanda, id_produs);ALTER TABLE RAND_COMENZIADD CONSTRAINT id_produs_fk FOREIGN KEY (id_produs) REFERENCES PRODUSE(id_produs);ALTER TABLE RAND_COMENZIADD CONSTRAINT nr_comanda_fk FOREIGN KEY (nr_comanda) REFERENCES COMENZI(nr_comanda);

ALTER TABLE COMENZIADD CONSTRAINT pk_nr_comanda PRIMARY KEY(nr_comanda);ALTER TABLE COMENZI ADD CONSTRAINT id_angajat_fk FOREIGN KEY(id_angajat)REFERENCES ANGAJATI(id_angajat);ALTER TABLE COMENZIADD CONSTRAINT id_client_fk FOREIGN KEY(id_client)REFERENCES CLIENTI(id_client);

ALTER TABLE ANGAJATIADD CONSTRAINT pk_id_angajat PRIMARY KEY(id_angajat);ALTER TABLE ANGAJATIADD CONSTRAINT salariul_min CHECK(salariul>0);ALTER TABLE ANGAJATIADD CONSTRAINT data_angajare_nn CHECK(data_angajare is not null);

ALTER TABLE CLIENTIADD CONSTRAINT id_client_pk PRIMARY KEY(id_client);ALTER TABLE CLIENTIADD CONSTRAINT nume_client_nn CHECK(nume_client is not null);ALTER TABLE CLIENTI ADD CONSTRAINT email_client_u UNIQUE(email_client);

Pasul urmtor a fost de a insera inregistrri n tabele folosind funcia INSERT:

INSERT INTO PRODUSE VALUES(1,'Miere poliflora', 'Miere de albine.',400,7.19);INSERT INTO PRODUSE VALUES(2,'Miere salcam', 'Miere de albine.',400,11.78);INSERT INTO PRODUSE VALUES(3,'Miere munte', 'Miere de albine.',400,9.92);INSERT INTO PRODUSE VALUES(4,'Miere tei', 'Miere de albine.',400,7.44);INSERT INTO PRODUSE VALUES(5,'Miere salcam', 'Miere de albine.',350,11.16);INSERT INTO PRODUSE VALUES(6,'Polen granule', 'O minune a vietii',100,8.68);INSERT INTO PRODUSE VALUES(7,'Propolis brut', 'Antibiotic natural',10,7.19);INSERT INTO PRODUSE VALUES(8,'Tinctura de propolis', 'Antibiotic natural',20,8.06);INSERT INTO PRODUSE VALUES(9,'Apiinvert', 'Sirop de albine',1000,4.30);INSERT INTO PRODUSE VALUES(10,'Apifonda', 'Hrana pentru albine in stare solida',1000,4.90);INSERT INTO PRODUSE VALUES(11,'Fagure','Fagure din stup.',1000,19.84);INSERT INTO PRODUSE VALUES(12,'Ceara depilatoare', 'Ceara depilatoare naturala.',200,10.54);COMMIT;

INSERT INTO RAND_COMENZI VALUES(1,4,7.44,40);INSERT INTO RAND_COMENZI VALUES(2,3,9.92,160);INSERT INTO RAND_COMENZI VALUES(3,1,7.19,78);INSERT INTO RAND_COMENZI VALUES(4,2,11.78,14);INSERT INTO RAND_COMENZI VALUES(5,7,7.19,900);INSERT INTO RAND_COMENZI VALUES(6,10,4.9,250);INSERT INTO RAND_COMENZI VALUES(7,12,10.54,84);INSERT INTO RAND_COMENZI VALUES(8,11,19.84,190);INSERT INTO RAND_COMENZI VALUES(9,7,7.19,645);INSERT INTO RAND_COMENZI VALUES(10,6,8.68,110);INSERT INTO RAND_COMENZI VALUES(11,9,4.3,480);INSERT INTO RAND_COMENZI VALUES(12,8,8.06,700);INSERT INTO RAND_COMENZI VALUES(13,2,11.78,15);INSERT INTO RAND_COMENZI VALUES(14,5,11.16,50);INSERT INTO RAND_COMENZI VALUES(15,10,4.9,1200);INSERT INTO RAND_COMENZI VALUES(16,11,19.84,90);COMMIT;

INSERT INTO COMENZI VALUES(1,'30.01.2012',3,' finalizata',1);INSERT INTO COMENZI VALUES(2,'14.02.2012',3,' finalizata',2);INSERT INTO COMENZI VALUES(3,'15.02.2012',4,' finalizata',8);INSERT INTO COMENZI VALUES(4,'15.02.2012',1,' anulata',4);INSERT INTO COMENZI VALUES(5,'15.02.2012',2,' finalizata',3);INSERT INTO COMENZI VALUES(6,'16.02.2012',7,' finalizata',7);INSERT INTO COMENZI VALUES(7,'18.02.2012',5,' finalizata',3);INSERT INTO COMENZI VALUES(8,'15.03.2012',6,' anulata',4);INSERT INTO COMENZI VALUES(9,'30.04.2012',7,' finalizata',3);INSERT INTO COMENZI VALUES(10,'04.06.2012',6,' finalizata',2);INSERT INTO COMENZI VALUES(11,'16.07.2012',1,' finalizata',1);INSERT INTO COMENZI VALUES(12,'21.09.2012',4,' finalizata',9);INSERT INTO COMENZI VALUES(13,'02.01.2013',3,' expediata',8);INSERT INTO COMENZI VALUES(14,'03.01.2013',7,' expediata',3);INSERT INTO COMENZI VALUES(15,'04.01.2013',2,' finalizata',1);INSERT INTO COMENZI VALUES(16,'05.01.2013',5,' anulata',5);commit;

INSERT INTO ANGAJATI VALUES(1,'Ion', 'Andrei','ion.andrei@apisalecom.ro','0744128488','02.10.1993',1850);INSERT INTO ANGAJATI VALUES(2,'Stefan', 'Baciu','stefan_b@apisalecom.ro','0742789182','04.03.1994',1900);INSERT INTO ANGAJATI VALUES(3,'Andreea', 'Saulea','saulea.andreea@apisalecom.ro','0723857425','12.11.2000',2480);INSERT INTO ANGAJATI VALUES(4,'Chirtes', 'Stefan','fanelc@apisalecom.ro','0741123456','17.06.1998',2950);INSERT INTO ANGAJATI VALUES(5,'Dorel', 'Ignat','ign_dorel@yahoo.com','0733978544','25.10.2011',1550);INSERT INTO ANGAJATI VALUES(6,'Ioana', 'Badea','ioanab@apisalecom.ro','0765890775','29.07.1997',2800);INSERT INTO ANGAJATI VALUES(7,'Vasile', 'Stoian','stoianvasi@apisalecom.ro','0741886675','10.12.1996',2640);INSERT INTO ANGAJATI VALUES(8,'Miruna', 'Stoilescu','stoilescu.m@apisalecom.ro','0744095518','04.01.1999',3650);INSERT INTO ANGAJATI VALUES(9,'Laura', 'Popescu','popescu_laura12@apisalecom.ro','0743442908','30.03.2004',1900);COMMIT;

INSERT INTO CLIENTI VALUES(1,'S.C. TIS FARMACEUTIC S.R.L.', 'RO 867552',' Industriilor',14,' 021890667','tisfarma@tis.ro');INSERT INTO CLIENTI VALUES(2,'S.C. RADIX S.R.L.', 'RO 779952',' Mihai Viteazul',79,' 0217755','radix.srl@yahoo.com');INSERT INTO CLIENTI VALUES(3,'S.C. PDG S.R.L.', 'RO 1672885',' Soseaua de centura',116,' 0753990012','pdg_group@pdg.ro');INSERT INTO CLIENTI VALUES(4,'APIDAVA S.A.', 'RO 7124124',' Bulevardul Stefan cel Mare',89,' 021867768','apidava@api.com');INSERT INTO CLIENTI VALUES(5,'S.C. BELLA-DONNA S.R.L.', 'RO 234221',' Bulevardul Unirii',357,' 021885647','belladonna_1@yahoo.com');INSERT INTO CLIENTI VALUES(6,'S.C. BROTACEL S.R.L.', 'RO 644921',' Veteranilor',55,' 0765422108','brotacel_srl@brotacel.ro');INSERT INTO CLIENTI VALUES(7,'ONEDIA DISTRIBUTION S.A.', 'RO 855098',' Soseaua de centura',248,' 021664455','onediad@onedia.ro');COMMIT;

Exerciii:1. S se creeze o nou tabel Produse2 care s conin aceleai produse ca i tabela iniial.

set serveroutput onBEGINexecute immediate 'CREATE table PRODUSE2 AS SELECT * FROM PRODUSE';END;/

2. n tabela nou creat (Produse 2 ), s se adauge o coloan cu numele Stoc. Afiai rezultatul.

DECLAREv_sir varchar2(50);BEGINv_sir:='ALTER TABLE PRODUSE2 add (Stoc number (7))';dbms_output.put_line (v_sir);EXECUTE IMMEDIATE v_sir;END;/select * from PRODUSE2;

3. S se afieze numele angajatului cu codul 2.DECLARE v_nume angajati.nume%TYPE;BEGINselect numeinto v_numefrom angajatiwhere id_angajat = 2;dbms_output.put_line('Angajatul :' || v_nume||);END;/

4. S se mreasc cu 20% salariul angajailor care au n prezent salariul mai mic de 2000 de RON.

DECLAREv_procent number:=0.2;v_prag angajati.salariul%type:=200;BEGINupdate angajatiset salariul=salariul*(1+v_procent)where salariul < v_prag;END;/

5. Selectai produsele i preul acestora pentru acelea care au preul mai mic dect preul mediu al produsului cu codul 2 fr a utiliza comanda select. set serveroutput onset autoprint onvariable p_pret number

BEGINselect avg(pret) into :p_pretfrom rand_comenziwhere id_produs = 2;END;/select * from rand_comenzi where pret< :p_pret;

6. S se afieze numrul de comenzi ale angajatului al crui cod este introdus de la tastatur.

DECLAREv_nr_comenzi number(2);BEGINselect count(nr_comanda) into v_nr_comenzi from comenziwhere id_angajat=&id_angajat;dbms_output.put_line ('Angajatul are: '|| v_nr_comenzi||' comenzi');END;/

7. Se d numele unui angajat: Stoilescu. Afiai prenumele i salariul acestui angajat.SET SERVEROUTPUT ONvariable a_salariul numberDEFINE s_nume=Ionescu;DECLARE v_prenume angajati.nume%type;BEGINselect prenume,salariul into v_prenume, :a_salariulfrom angajati where nume='&s_nume';DBMS_OUTPUT.PUT_LINE ('Prenumele angajatului este: '||v_prenume);END;/

Rezolvare: block completedPrenumele angajatului este: Mirunaa_salariul----3650

8. Pentru angajaii cu salariul 1900 de RON, afiai ct ar fi salariul dac s-ar mri cu un procent de 30%. ACCEPT p_sal PROMPT 'Introducei salariul:'ACCEPT p_procent PROMPT 'Introducei procentul:'DECLAREv_sal number:=&p_sal;v_procent number:=&p_procent;BEGINdbms_output.put_line('Salariul ar fi: ' || to_char(nvl(v_sal,0) * (1+nvl(v_procent,0)/100)));END;/

Rezolvare:block completedSalariul ar fi:2470

9. Afiai angajaii cu id-urile n intervalul 1-7 att timp ct salariul acestora este mai mic dect media salariilor. set serveroutput onDECLAREv_sal angajati.salariul%type;v_sal_mediu v_sal%type;i number(4):=1;BEGINselect avg(salariul) into v_sal_mediu from angajati;dbms_output.put_line('Salariul mediu este: '||v_sal_mediu);loopselect salariul into v_sal from angajati where id_angajat=i;dbms_output.put_line('Angajatul cu codul '||i||' are salariul: '||v_sal);i:=i+1;exit when v_sal7;end loop;END; /10. Modificai denumirea produsului cu id-ul 6 n Polen 2. n cazul n care acest produs nu exist, afiai pe ecran mesajul corespunztor.set serveroutput onBEGINupdate produse set denumire_produs='Polen2' where id_produs=6;if SQL%NOTFOUND thendbms_output.put_line('Nu exista produsul cu acest cod');end if;END;/

11. Creai o nou tabel: ANGAJAI_UPD, care s conin id-ul, numele i prenumele primilor 5 angajai din tabela Angajai (folosii un cursor explicit). Afiai pe ecran rezultatul.set serveroutput onCREATE TABLE ANGAJATI_UPD (cod varchar2(7),nume varchar2(30),prenume varchar2(30));

DECLARE v_id angajati.id_angajat%type;v_nume angajati.nume%type;v_prenume angajati.prenume%type;cursor c is select id_angajat, nume, prenume from angajati;BEGINopen c;for i in 1..5 loopfetch c into v_id, v_nume, v_prenume;insert into ANGAJATI_UPD values(v_id, v_nume, v_prenume);end loop;close c;END;/select * from ANGAJATI_UPD

12. S se afieze primele 3 comenzi care au cele mai multe produse comandate. nregistrrile vor fi ordonate descresctor n funcie de numrul produselor.

set serveroutput onDECLAREcursor c_comenzi is select c.nr_comanda, count(r.id_produs) numarfrom comenzi c, rand_comenzi r where c.nr_comanda=r.nr_comandagroup by c.nr_comandaorder by count(r.id_produs) desc;rec_comenzi c_comenzi%rowtype;

BEGINdbms_output.put_line ('Numrul de produse pentru fiecare comand este:');if not c_comenzi%ISOPEN then open c_comenzi;end if;loopfetch c_comenzi into rec_comenzi;exit when c_comenzi%NOTFOUND or c_comenzi%ROWCOUNT>3;dbms_output.put_line ('Comanda '||rec_comenzi.nr_comanda||' are: '||rec_comenzi.numar||' produse');end loop;close c_comenzi;END;/

13. S se afieze produsele a cror cantitate total comandat este mai mare dect o valoare introdus de la tastatur.set serveroutput onDECLAREcursor c_prod (p_val number) isselect p.id_produs, p.denumire_produs, sum(r.cantitate) totalfrom produse p, rand_comenzi rwhere p.id_produs =r.id_produsgroup by p.id_produs, p.denumire_produshaving sum(r.cantitate)>p_valorder by total desc;v_val number(5);rec_prod c_prod%rowtype;

BEGINv_val:=&v_val;dbms_output.put_line('Produsele a cror cantitate vndut este mai mare dect '|| v_val);if not c_prod%ISOPEN thenopen c_prod (v_val);end if;loopfetch c_prod into rec_prod;exit when c_prod%notfound;dbms_output.put_line('Din produsul '||rec_prod.id_produs||', '||rec_prod.denumire_produs||', s-au vandut ' ||rec_prod.total||' unitati');end loop;close c_prod;END;/

14. S se afieze angajatul cu codul 13. S se trateze eroarea aprut n cazul n care nu exist nici un angajat cu acest cod.set serveroutput onDECLAREv_nume varchar2(20);v_prenume varchar2(20);BEGINselect nume, prenume INTO v_nume, v_prenumefrom angajatiwhere id_angajat=&id_angajat;dbms_output.put_line(v_nume ||' '|| v_prenume);EXCEPTIONwhen no_data_found thendbms_output.put_line('Angajatul cu id-ul specificat, nu exist.');END;/

15. S se afieze salariul angajatului cu prenumele Miruna. S se trateze dou exceptii. set serveroutput onDECLAREsal angajati.salariul%type;BEGINselect salariul into sal from angajati where prenume='Miruna';dbms_output.put_line('Miruna are salariul de: '||sal||' RON');EXCEPTIONwhen too_many_rows thendbms_output.put_line('Exist mai muli salariai cu numele Miruna');when no_data_found thendbms_output.put_line('Nu exist angajatul specificat');END;/

16. tergei toate nregistrrile din tabela: PRODUSE2, dup care creai o nou tabel: ERORI care s conin valorile SQLCODE i SQLERRM.create table ERORI(utilizator varchar2(40), cod_eroare number(10), mesaj_eroare varchar2(255));DECLAREcod number;mesaj varchar2(255);del_exception exception;pragma exception_init (del_exception, -2292);BEGINdelete from produse2;EXCEPTIONwhen del_exception thendbms_output.put_line('Produsul nu poate fi ters. ');dbms_output.put_line('Exist comenzi n derulare. ');cod:=SQLCODE;mesaj:=SQLERRM;insert into ERORI values(user, cod, mesaj);END;/

17. S se modifice denumirea unui produs cu id-ul introdus de la tastatur. Tratai cte o excepie dac:-nu exist id-ul specificat;-apare o alt eroare.

DECLAREinvalid_prod EXCEPTION;BEGINupdate produse set denumire_produs='Miere nou'where id_produs=&id_produs;if SQL%NOTFOUND thenraise invalid_prod;end if;EXCEPTIONwhen invalid_prod thendbms_output.put_line ('Nu exist produsul cu id-ul specificat de dumneavoastr');when OTHERS thendbms_output.put_line ('A aprut o eroare. Procesul de actualizare nu se poate realiza.');END;/

18. Folosind o procedur, s se modifice salariul angajatului cu id-ul 6 ( salariul trebuie s devin cu 50% mai mare).create or replace procedure modifica_salariul_procent(p_id_angajat IN angajati.id_angajat%type, procent IN number) isv_salariul angajati.salariul%type;BEGINselect salariul into v_salariul from angajati where id_angajat=p_id_angajat;dbms_output.put_line('Angajatul are salariul de '||v_salariul);update angajatiset salariul=salariul*(1+procent/100)where id_angajat=p_id_angajat;select salariul into v_salariul from angajati where id_angajat=p_id_angajat;dbms_output.put_line('Noul salariu al angajatului este: '||v_salariul);END;/

EXECUTE modifica_salariul_procent(6, 50)Rollback;

19. Calculai salariul mediu al angajailor i returnai-l printr-un parametru de tip OUT.create or replace procedure salariul_mediu (p_salariul_mediu OUT number) isBEGINselect avg (salariul) into p_salariul_mediu from angajati;END;/variable v_salariul_mediu numberexecute salariul_mediu(:v_salariul_mediu)print v_salariul_mediu

20. Realizai un trigger care s nu permit depirea unei limite maxime a salariului unui angajat.

create or replace trigger restrictie_salariul before insert or update on angajati for each rowDECLAREv_sal_max number;BEGIN select max(salariul) into v_sal_max from angajati where id_functie= :new.id_functie;if :new.salariul>v_sal_max then raise_application_error (-20202, 'Nu se poate depsi salariul maxim'); end if;END;/

Pentru verificare:

update angajatiset salariul =10000where id_angajat=7;

21. Creai un trigger care s asigure unicitatea codului unui produs folosind valorile generate de o secven. crate SEQUENCE produse_secvstart with 1increment by 1maxvalue 15nocycle;

create or replace trigger generare_codprodusbefore inserto n produsefor each rowBEGINselect produse_secv.nextval into :new.id_produs from dual;END;/16