Proiect Baze de Date - Nota 8-9 - ASE CSIE [PDF]

Zitiervorschau

PROIECT -BAZE DE DATE-

Baza de date creată se ocupă de gestiunea informaţiilor legate de activitatea unui atelier auto. Pentru aceasta am creat 6 tabele: o Mecanici o Autoturisme o Clienti_auto o Programari o Defectiuni o Inregistrari

MECANICI #cod_mecanic *nume *prenume *nr_telefon *adresa

Tabela MECANICI conţine informaţii despre angajatii ce efectueaza reparatii la atelierul auto. Pentru fiecare din ei, se păstrează informaţii despre nume, prenume, adresă si nr_telefon( câmpuri obligatorii) fiind identificaţi printr-un număr unic cu rol de cheie primară.

DEFECTIUNI #cod_defect *nume_defect Tabela DEFECTIUNI cuprinde date despre defectiunile constatate la autoturisme , acestea fiind identificate prin cod_defect(primary key) şi nume_defec(care nu poate avea valoarea NULL).

AUTOTURISME #cod_auto *marca *model tonaj an_fabric

Tabela AUTOTURISME stochează date despre marca, modelul, tonajul si anul fabricatiei autovehiculelor. Cheia primară a acestei tabele este cod_auto.

CLIENTI-AUTO #cod_client *nume *prenume *telefon *adresa *e-mail

Aceasta tabela contine informatii despre clientii atelierului auto avand ca si cheie primara cod_client.

PROGRAMARI #cod_progamare #cod_mecanic #cod_auto *data_prog

Tabela PROGRAMARI are drept cheie primară câmpul cod_progamare iar cele 2 campuri cod_mecanic si cod_auto sunt chei externe din tabela MECANICI respective AUTOTURISME. Se mai păstrează informaţii despre data progamarii ce are restricţie NOT NULL.

INREGISTRARI #cod_auto #cod_defect *durata_efecturare *costuri

Tabela INREGISTRARI conţine cheia externa cod_auto din tabela AUTOTURISME si cheia externa cod_defect din tabela DEFECTIUNI. Deasemenea tabela mai contine informatii referitoare la durata de efectuare a reparatiei si respective costurile.

Comenzi LDD Creare tabel MECANICI create table mecanici (cod_mecanic number(5) constraint PKey_mecanic primary key, nume varchar2(20) not null, prenume varchar2(20), adresa varchar2(15), telefon varchar2(10) CONSTRAINT tel_ck check (telefon like '0%')); Creare tabel AUTOTURISME create table autoturisme (cod_auto number(5) constraint pk_auto primary key, marca varchar2(25) not null, model_auto varchar2(25), tonaj number(5,2), an_fabric number(4) CONSTRAINT anfabric_CK check (an_fabric between 1900 and 2010)); Creare tabel CLIENTI_AUTO create table clienti_auto (cod_client number(5) constraint pk_clientii primary key, nume varchar2(20) not null, prenume varchar2(20) not null, telefon varchar2(10) CONSTRAINT tl_ck check (telefon like '0%'), email varchar2(20), adresa varchar2(20));

Creare tabel PROGRAMARI

create table programari (cod_programare number(5)constraint pk_codprogr primary key, cod_mecanic number(5), cod_auto number(5), data_prog date, CONSTRAINT FKmecanic FOREIGN KEY (cod_mecanic) REFERENCES mecanici(cod_mecanic), CONSTRAINT FKautoturism FOREIGN KEY (cod_auto) REFERENCES autoturisme(cod_auto)); Creare tabel DEFECTIUNI create table defectiuni (cod_defect number(5) constraint pk_defect primary key, nume_defect varchar2(20)); Creare tabel INREGISTRARI create table inregistrari (cod_auto number(5), constraint fk_inreg FOREIGN KEY (cod_auto) REFERENCES autoturisme(cod_auto), cod_defect number(5), constraint fk_defecti FOREIGN KEY (cod_defect) REFERENCES defectiuni(cod_defect), durata_efectuare number(3), costuri number(5)); ALTER,DROP 1.Să se adauge o restrictive conform careia numarul de telefon sa fie unic. alter table mecanici add constraint uq_mec unique (telefon); 2.Sa se şteargă tabela MECANICI. Drop table mecanici 3. Sa se modifice numele defectiunilor din tabela DEFECTIUNI alter table defectiuni Modify(nume_defect varchar2(40)); 4. Sa se schimbe numele tabelei CLIENTI_AUTO cu CLIENTI alter table clienti_auto rename to clienti;

Comenzi LMD insert into mecanici values ('102', 'Lazar','Viorel','Bucuresti','0731284321'); insert into mecanici values ('103', 'Bogatu','Lucian','Pitesti','0768003452'); insert into mecanici values ('104', 'Zamfirei','Andrei','Zalau','0726134413'); insert into mecanici values ('105', 'Balan','Miroslav','Chisinau','0324178875'); insert into mecanici values ('106', 'Popescu','Marian','Bucuresti','0734278983'); ---------------------------------------------------------------------------------------------------------------insert into autoturisme values ('202', 'BMW','X5','3','2009'); insert into autoturisme values ('203', 'Ford','F-150','4','2002'); insert into autoturisme values ('204', 'Dacia','Logan','2','2004'); insert into autoturisme values ('205', 'Mercedes','CLK-200','3','2000'); insert into autoturisme values ('206', 'Buick','Enclave','4','2008'); ----------------------------------------------------------------------------------------------------------------insert into programari values ('302', '102','202','27-02-2010'); insert into programari values ('303', '103','203','12-03-2010'); insert into programari values ('304', '104','204','17-03-2010'); insert into programari values ('305', '105','205','30-04-2010'); insert into programari values ('306', '106','206','19-09-2010'); ------------------------------------------------------------------------------------------------------------insert into clienti_auto values ('402', 'Firicel','Ion','0734276343','[email protected]','Braila'); insert into clienti_auto values ('403', 'Mocanu','Diana','0743101202','[email protected]','Bucuresti'); insert into clienti_auto values ('404', 'Maneca','Nicoleta','0722677888','[email protected]','Bucuresti'); insert into clienti_auto values

('405', 'Sarpe','Mihai','0766212329','[email protected]','Timisoara'); insert into clienti_auto values ('406', 'Loghin','Irina','0744222454','[email protected]','Braila'); ----------------------------------------------------------------------------------------------------------------insert into inregistrari values ('202', '502','12','1000'); insert into inregistrari values ('203', '503','45','4000'); insert into inregistrari values ('204', '504','22','900'); insert into inregistrari values ('205', '505','7','150'); insert into inregistrari values ('206', '506','3','700'); ----------------------------------------------------------------------------------------------------------------i insert into defectiuni values ('502','arbore cotit'); insert into defectiuni values ('503','transmisie uzata'); insert into defectiuni values ('504','planetare tocite'); insert into defectiuni values ('505','schimabare ulei'); insert into defectiuni values ('506','directie fata');

Tabelele create şi populate arată astfel:

1.Să se modifice numele mecanicului cu, codul 102 in ‘Andronoiu’. update mecanici set nume='Andronoiu' where cod_mecanic= 102;

2.Să se modifice numărul de telefon al clientului cu numele începând cu litera F. update clienti_auto set telefon='0720341203' where nume like'F%';

3.Să se modifice data programarii pentru marca BMW update programari set data_prog =to_date('10.10.2011','dd.mm.yyyy') where cod_auto=(select cod_auto from autoturisme where marca like 'BMW');

4. Să se modifice durata de efectuare la 50 de zile pentru defectiunea “ rbore cotit” update inregistrari set durata_efectuare=50 where cod_defect=(select cod_defect from defectiuni where nume_defect like 'arbore cotit');

5.Pentru autoturismul cu cod auto 206 modificati an_fabric ca fiind cea mai veche dată. Update autoturisme set an_fabric=( select min(an_fabric) from autoturisme ) where cod_auto=206;

6. Stergeţi din tabela clienti_auto clientii al caror nume incep cu litera F delete from clienti_auto where nume like 'F%';

7.Să se şteargă programarile mecanicului Lazar delete from programari where cod_mecanic =(select cod_mecanic from mecanici where lower(nume) like 'lazar');

8.Să se şteargă din tabela inregistrari autoturismul cu costurile cele mai mari delete from inregistrari where costuri=(select max(costuri) from inregistrari);

9.Să se şteargă toate înregistrarile din tabela defectiuni. Delete from defectiuni;

Interogări 1.Să se afişeze numele şi prenumele mecanicilor care au lucrat la autoturismul marca BMW select prenume ||' '||nume as Nume_Mecanic from mecanici m, programari p, autoturisme a where m.cod_mecanic=p.cod_mecanic and a.cod_auto=p.cod_auto and upper(marca) like 'BMW';

2.Să se afişeze durata de efectuare si costurile pentru autoturismele care au avut ca defectiune schimbare ulei si anul fabricatiei sa fie dupa anul 1999 select marca, durata_efectuare, costuri from autoturisme a, inregistrari i,defectiuni d where a.cod_auto=i.cod_auto and d.cod_defect=i.cod_defect and lower(nume_defect) like 'BMW' and an_fabric>1999;

3. Sa se afiseze mecanici care nu au programare. select nume, prenume from mecanici m, programari p where m.cod_mecanic=p.cod_mecanic(+);

4. Sa afiseze numele defectiunilor care au codul intre 502 si 505 si durata de efectuare a reparatiilor mai mica de 45 de zile select nume_defect from defectiuni where cod_defect between 502 and 505 intersect select nume_defect from defectiuni d, inregistrari i where d.cod_defect=i.cod_defect and durata_efectuare2004;

11.Sa se afiseze numele prenurmele si telefonul mecanicului a carui prenume incepe cu litera L select prenume, nume, telefon from mecanici where prenume like('L%')

12.Să se afişeze mecanici care au autoturismele programate între 17.03.2010 şi data curentă. select cod_mecanic,nume from mecanici where cod_mecanic in (select cod_mecanic from programari where data_prog>to_date('17-03-2010','dd-mm-yyyy'));

13 Sa se afiseze costurile totale de reparatii cat si cea mai scumpa reparatie al atelierului.

select prenume,nume,email_ select sum(costuri) as cost_total, max(costuri) as cost_max from autoturisme x, inregistrari i where x.cod_auto=i.cod_auto;

14. Sa se afiseze modelul de autoturism cu codul de programare 306 select model_auto from autoturisme x, programari p where x.cod_auto=p.cod_auto and cod_programare=306;

15. Să se afişeze atat inregistrrarile cat si defectiunile aferente select * from inregistrari x, defectiuni d where x.cod_defect=d.cod_defect ;

Creare tabele virtuale 1.Creaţi o tabelă virtuală care să cuprindă instrumentele folosite după data de 10.02.1999. Create or replace view info_i as select id_instrument,nume_instrument from instrumente where id_instrument in (select id_instrument from evidenta_instrumente where data_utilizare>to_date('10.02.1999','dd.mm.yyyy')) ;

2.Creaţi o tabelă virtuală care să cuprindă si mecanicii care nu au programare Create or replace view mec_prog as select nume, prenume from mecanici m, programari p where m.cod_mecanic=p.cod_mecanic(+);

3.Creaţi o tabelă virtuală care să cuprindă toate informaţiile despre autoturisme. Create or replace view autoturism as Select * from autoturisme;

4.Să se creeze un index după coloana costuri din tabela inregistrari. Create index titlu_index on inregistrari(costuri);

5.Să se creeze un index după coloana data_prog din tabela programari. Create index data_index on programari(data_prog);

6.Să se creeze sinonim pentru tabela mecanici. Create synonym mecanic for mecanici;

7.Să se creeze sinonim pentru tabela inregistrari. Create synonym register for inregistrari ;

8. Să se creeze o secvenţă pentru asigurarea unicităţii cheii primare din tabela autoturisme.

Create sequence seq_auto start with 13 Increment by 1 Maxvalue 500 nocycle;