PROIECT
Baze de date
-GESTIUNEA UNEI REPREZENTANTE AUTO-
GESTIUNEA UNEI REPREZENTANTE AUTO
Baza de date creata contine informatii despre clientii reprezentantei, variantele de masini disponibile, piesele de schimb pentru respectivele masini, service si mai ales despre vanzarile atat de masini cat si de piese.
Tabelul VARINATE contine modelul, marimea motorului masinii, puterea- in cai putere- , tipul de combustibil pe care functioneaza masina si pretul acesteia. Cheia primara a acestui tabel este ID_VAR. Este unica deoarece fiecare varianta este diferita, daca nu ca model, ca putere a motorului sau a tipului de carburant folosit. De aceea am si ales numele modelelor de mai multe ori pentru a se putea observa ca marimea motorului si carburantul difera.
Tabelul CLIENT contine date despre client precum ID_CLIENT, care este si cheia primara a acestuia, nume si prenume. Ulterior apare si coloana varsta prin adaugare.
Tabelul PIESE contine informatii despre accesorii ce se potrivesc la toate cele 3 modele de masini. Cheia primara este ID_PIESA; aceasta este unica, fiecare din cele 5 tipuri de accesorii avand un cod diferit chiar daca id 2 si id 3 sunt jante , ele sunt diferite, de exemplu unele din aliaj si unele din fier. Tabelul contine atat 151g61b denumirea piesei cat si pretul acesteia; pretul ne va ajuta atunci cand se va face totalul vanzarilor.
Tabelul SERVICE contine informatii despre clientul care a adus masina- asta insemnand numele si prenumele clientului cat si idul variantei masinii, legatura care se face prin tabelul vanzari. Tabelul mai contine data intrarii masinii in service ,ulterior adaugata, coloana cu data iesirii masinii respective din service si mai ales numarul de comanda de service, adica un fel de ordine in care vor fi preluate masinile pentru a li se face modificarile sau reparatiile dorite; aceasta este si cheia primara din tabel: ID_SERV.
Tabelul VANZARI contine cele mai multe informatii: idul variantei de masina cumparate, idul piesei cumparat si bineinteles id clientrului care le-a cumparat, precum si data achizitiei acestora si numarul comenzii sau facturii notat cu NR_COM fiind si cheia primara.
VANZARI are 3 chei externe: ID_VAR care il leaga de tabelul VARINATE, ID_CLIENT care face legatura cu tabelul CLIENT si ID_PIESA care indica idul piesei din tabelul PIESE. Am pus si o verificare la acest tabel si anume: atunci cand id_var nu este nula atunci id_piesa este nula si invers. Aceasta conditie trebuie sa fie indeplinita deoarece numarul de vanzare este unic si fiecare dintre bunurile vandute sunt separate. Asa ca atunci cand se va vinde o masina, bineinteles numai aceasta este in numarul respectiv de vanzare, deci nu trebuie sa fie completata si coloana id_piesa in acelasi loc, si invers cand avem o vanzare de piesa id_var va fi nula pe acea pozitie. Astfel se poate tine mai usor evidenta bunurilor vandute.
Toate legaturile din acest tabel de la cheia primara la cheia externa sunt de forma 1:N.
prompt Creating table VARIANTE
create table VARIANTE
ID_VAR NUMBER(4) not null,
MODEL VARCHAR2(40) not null,
MOTOR NUMBER(12),
PUTERE NUMBER(30),
CARBURANT VARCHAR2(12),
PRET NUMBER(25)
);
alter table VARIANTE
add constraint ID_VAR_PK primary key (ID_VAR);
alter table VARIANTE
add constraint ID_VAR_NN
check ('ID_VAR' IS NOT NULL);
prompt Creating table CLIENT
create table CLIENT
ID_CLIENT NUMBER(8),
NUME VARCHAR2(25) not null,
PRENUME VARCHAR2(25)
);
alter table CLIENT
add constraint ID_CLIENT_PK primary key (ID_CLIENT);
alter table CLIENT
add constraint ID_CLIENT_NN
check ('ID_CLIENT' IS NOT NULL);
prompt Creating table SERVICE
create table SERVICE
ID_SERV NUMBER(5) ,
ID_CLIENT NUMBER(5) not null,
DATA_INTRARE DATE
alter table SERVICE
add constraint ID_SERV_PK primary key (ID_SERV);
alter table SERVICE
add constraint ID_SERV_NN
check ('ID_SERV' IS NOT NULL);
alter SERVICE
add constraint ID_CLIENT_FK FOREIGN KEY(ID_CLIENT)
references CLIENT(ID_CLIENT);
prompt Creating table PIESE
create table PIESE
ID_PIESA NUMBER(5) ,
DENUMIRE VARCHAR2(15),
PRETP NUMBER(10)
alter table PIESE
add constraint ID_PIESA_PK primary key (ID_PIESA);
alter table PIESE
add constraint ID_PIESA_NN
check ('ID_PIESA' IS NOT NULL);
prompt Creating table VANZARI
create table VANZARI
NR_COM NUMBER(10) not null ,
ID_VAR NUMBER(16),
ID_PIESA NUMBER(6),
CANTITATE NUMBER(5),
ID_CLIENT NUMBER(8),
DATA_ACHIZ DATE
alter table VANZARI
add constraint NR_COM_PK primary key (NR_COM);
alter table VANZARI
add constraint NR_COM_NN
check ('NR_COM' IS NOT NULL);
alter table VANZARI
add constraint ID_VAR_FK FOREIGN KEY(ID_VAR)
references VARIANTE(ID_VAR);
alter table VANZARI
add constraint ID_PIESA_FK FOREIGN KEY(ID _PIESA)
references PIESE(ID_PIESA);
alter table VANZARI
add constraint ID_CLIENT_FK FOREIGN KEY(ID_CLIENT)
references CLIENT(ID_CLIENT);
CHECK(ID_VAR IS NOT NULL AND ID_PIESA IS NULL) OR
(ID_VAR IS NULL AND ID_PIESA IS NOT NULL)
prompt ====Populare====
prompt ====CLIENT=====
insert into CLIENT (ID_CLIENT, NUME, PRENUME)
values (173, 'ema', 'paslaru');
insert into CLIENT (ID_CLIENT, NUME, PRENUME)
values (182, 'mihai', 'polexe');
insert into CLIENT (ID_CLIENT, NUME, PRENUME)
values (123, 'benga', 'alexandru');
insert into CLIENT (ID_CLIENT, NUME, PRENUME)
values (251, 'dragut', 'catalin');
insert into CLIENT (ID_CLIENT, NUME, PRENUME)
values (125, 'samira', 'tataru');
insert into CLIENT (ID_CLIENT, NUME, PRENUME)
values (247, 'olteanu', 'andrei');
insert into CLIENT (ID_CLIENT, NUME, PRENUME)
values (152, 'olteanu', 'florin');
commit;
prompt ======VARIANTE=======
insert into VARIANTE (ID_VAR,MODEL, MOTOR, PUTERE, CARBURANT , PRET)
values (53, 'audi A3', 1.5, 110, ‚BENZINA’, 17.500);
insert into VARIANTE (ID_VAR,MODEL, MOTOR, PUTERE, CARBURANT , PRET)
values (56, 'audi A3', 2.0, 140, ‚MOTORINA’, 25.000);
insert into VARIANTE (ID_VAR,MODEL, MOTOR, PUTERE, CARBURANT , PRET)
values (83, 'audi A6 S-line', 3.8, 250, ‚BENZINA’, 50.000);
insert into VARIANTE (ID_VAR,MODEL, MOTOR, PUTERE, CARBURANT , PRET)
values (86, 'audi A6', 3.2, 160, ‚MOTORINA’, 47.000);
insert into VARIANTE (ID_VAR,MODEL, MOTOR, PUTERE, CARBURANT , PRET)
values (76, 'audi A4', 2.5, 135, ‚MOTORINA’, 23.500);
commit;
prompt========PIESE=========
insert into PIESE (ID_PIESA, DENUMIRE, PRETP)
values (1, 'PROIECTOR', 300 );
insert into PIESE (ID_PIESA, DENUMIRE, PRETP)
values (2, 'JANTE1', 400 );
insert into PIESE (ID_PIESA, DENUMIRE, PRETP)
values (3, 'JANTE2', 200 );
insert into PIESE (ID_PIESA, DENUMIRE, PRETP)
values (4, 'PRAG', 15 );
insert into PIESE (ID_PIESA, DENUMIRE, PRETP)
values (5, 'HUSE', 250 );
COMMIT;
prompt========SERVICE=========
insert into SERVICE (ID_SERV, ID_CLIENT, DATA_INTRARE)
values (0821,251 , to_date('17-12-2006', 'dd-mm-yyyy') );
insert into SERVICE (ID_SERV, ID_CLIENT, DATA_INTRARE)
values (0827,152 , to_date('23-08-2007', 'dd-mm-yyyy') );
insert into SERVICE (ID_SERV, ID_CLIENT, DATA_INTRARE)
values (0832,123 , to_date('15-11-2007', 'dd-mm-yyyy') );
insert into SERVICE (ID_SERV, ID_CLIENT, DATA_INTRARE)
values (0850,251 , to_date('24-07-2007', 'dd-mm-yyyy') );
insert into SERVICE (ID_SERV, ID_CLIENT, DATA_INTRARE)
values (0856, 182, to_date('08-04-2006', 'dd-mm-yyyy') );
COMMIT;
prompt==========VANZARI=========
insert into VANZARI (NR_COM, ID_VAR, ID_PIESA, CANTITATE, ID_CLIENT, DATA_ACHIZ)
values (5080,86 ,’-’ ,1 , 251,to_date('23-05-2006', 'dd-mm-yyyy') );
insert into VANZARI (NR_COM, ID_VAR, ID_PIESA, CANTITATE, ID_CLIENT, DATA_ACHIZ)
values (5053,53 ,’-’ ,1 , 182,to_date('27-11-2005', 'dd-mm-yyyy') );
insert into VANZARI (NR_COM, ID_VAR, ID_PIESA, CANTITATE, ID_CLIENT, DATA_ACHIZ)
values (5098,56 ,’-’ ,1 , 123,to_date('13-04-2007', 'dd-mm-yyyy') );
insert into VANZARI (NR_COM, ID_VAR, ID_PIESA, CANTITATE, ID_CLIENT, DATA_ACHIZ)
values (5083,83 ,’-’ ,1 , 173,to_date('17-10-2006', 'dd-mm-yyyy') );
insert into VANZARI (NR_COM, ID_VAR, ID_PIESA, CANTITATE, ID_CLIENT, DATA_ACHIZ)
values (5089,56 ,’-’ ,1 , 125,to_date('18-01-2007', 'dd-mm-yyyy') );
insert into VANZARI (NR_COM, ID_VAR, ID_PIESA, CANTITATE, ID_CLIENT, DATA_ACHIZ)
values (5030,76 ,’-’ ,1 , 247,to_date('09-08-2005', 'dd-mm-yyyy') );
insert into VANZARI (NR_COM, ID_VAR, ID_PIESA, CANTITATE, ID_CLIENT, DATA_ACHIZ)
values (5093,83 ,’-’ ,1 , 152,to_date('14-02-2007', 'dd-mm-yyyy') );
insert into VANZARI (NR_COM, ID_VAR, ID_PIESA, CANTITATE, ID_CLIENT, DATA_ACHIZ)
values (5009,’-’ ,1 ,2 , 247,to_date('26-01-2007’, 'dd-mm-yyyy') );
insert into VANZARI (NR_COM, ID_VAR, ID_PIESA, CANTITATE, ID_CLIENT, DATA_ACHIZ)
values (5006,’-’ ,3 ,4 , 251,to_date('17-12-2006’, 'dd-mm-yyyy') );
insert into VANZARI (NR_COM, ID_VAR, ID_PIESA, CANTITATE, ID_CLIENT, DATA_ACHIZ)
values (5008,’-’ ,2 ,4 , 125,to_date('17-07-2007’, 'dd-mm-yyyy') );
insert into VANZARI (NR_COM, ID_VAR, ID_PIESA, CANTITATE, ID_CLIENT, DATA_ACHIZ)
values (5005,’-’ ,5 ,1 , 251,to_date('24-07-2007’, 'dd-mm-yyyy') );
insert into VANZARI (NR_COM, ID_VAR, ID_PIESA, CANTITATE, ID_CLIENT, DATA_ACHIZ)
values (5002,’-’ ,4 ,4 , 123,to_date('15-11-2007’, 'dd-mm-yyyy') );
commit;
DLL
alter table CLIENT add varsta number(4);
alter table SERVICE add data_iesire date;
insert into SERVICE (data_iesire)
values (to_date('21-12-2006', 'dd-mm-yyyy') );
insert into SERVICE (data_iesire)
values (to_date('25-08-2007', 'dd-mm-yyyy') );
insert into SERVICE (data_iesire)
values (to_date('10-04-2006', 'dd-mm-yyyy') );
insert into SERVICE (data_iesire)
values (to_date('17-11-2007', 'dd-mm-yyyy') );
insert into SERVICE (data_iesire)
values (to_date('05-09-2007', 'dd-mm-yyyy') );
create table CLIENT2 as select * from CLIENT;
Drop table CLIENT2;
DML
--Sa se afiseze totalul vanzarilor din 2007.
select sum(cantitate*pret) from VANZARI v,VARINATE a,PIESE p
where v.id_var=a.id_var and p.id_piesa=v.id_piesa and extract(year from data_achiz)=2007;
--Sa se afiseze totalul vanzarilor de masini.
select sum(pret) from VANZARI v, VARIANTE a
where a.id_var=v.id_var and check(v.id_var is not null and v.id_piesa is null);
--Sa se afiseze clientii a caror masina a stat mai mult de 1 luna in service.
select c.id_client, nume, prenume from CLIENT c,SERVICE s
where c.id_client-s.id_client and to_char(data_iesire, ’dd-mm-yyyy’) –to_char(data_intrare, ’dd-mm-yyyy’)<30 ;
--Sa se afiseze denumirea pieselor care au fost cumparate pt audi A3 2.0
select denumire,pretp from PIESE p,VARIANTE a, VANZARI v
where p.id_piesa=v.id_piesa and a.id_var=v.id_var
group by pretp having upper(model) like ‚audi A3%’ and cilindrii=2.0;
--Sa se afiseze modelul de masina pe care il detine clientul 125.
select model, motor, pret from VARINATE
where id_var=(select id_var from VANZARI where id_client=125);
--Sa se schimbe varsta tuturor detinatorilor de audi A6 cu varsta clientului ---detinator de audi A4.
update CLIENT
set varsta=(select varsta from CLIENT where id_client=(select c.id_client from CLIENT c,VANZARI v,VARIANTE a where c.id_client=v.id_client and a.id_var=v.id_var and upper(model) like ‚%audi A4%’));
--Sa se stearga coloana varsta din tabela CLIENT
alter table CLIENT
drop column varsta;
--Sa se afiseze numele si prenumele clientilor a caror masina nu a fost
--niciodata in service.
select nume, prenume from CLIENT
where id_client is not in (select id_client from SERVICE);
--Sa se afiseze numele si tipul de carburant al masinii al tuturor clientilor si
--sa se ordoneze dupa nume .
select nume, carburant as „Tip combustibil” from CLIENT c, Vanzari v, VARIANTE a where c.id_client=v.id_client and a.id_var=v.id_var
order by nume;
select model || motor from VARIANTE a , VANZARI v where a.id_var=v.id_var and extract(year from data_achiz)>=2006;
update CLIENT
set nume=’Dumitru’ and prenume=’Cristian’
where id_client=152;
delete from SERVICE where id_client=(select id_client from VANZARI where id_var=53);
select nume as „client”, model || motor from VARIANTE a, VANZARI v, CLIENT c where a.id_var=v.id_var and c.id_client=v.id_client and putere>150;
|
