Construcția și utilizarea unei baze de date în cadrul unei firme

Stuctura bazei de date utilizata:

DROP TABLE AGENTII CASCADE CONSTRAINTS;

DROP TABLE UNITATI CASCADE CONSTRAINTS;

DROP TABLE CAMERE CASCADE CONSTRAINTS;

DROP TABLE OCUPARE CASCADE CONSTRAINTS;

DROP TABLE CLIENTI CASCADE CONSTRAINTS;

DROP TABLE SERVICII CASCADE CONSTRAINTS;

Create table agentii

(cod_agentie number(2) constraint PK_agentii primary key,

den_agentie varchar2(20) not null,

localitate varchar2(20),

nrtel varchar2(20));

create table unitati

(cod_unitate number(2) constraint PK_unitati primary key,

Den_unitate varchar2(20) not null,

Cod_agentie number(2),

Categorie varchar2(20),

Nr_stele varchar2(5),

Localitate varchar2(20),

Constraint categ_ck check (categorie in ('hotel','motel','pensiune','cabana')),

Constraint stele_ck check (nr_stele in ('*','**','***','****','*****')),

constraint FK_unitati foreign key (cod_agentie) references agentii(cod_agentie));

Create table camere

(Cod_camera number(2) constraint PK_camere primary key,

cod_unitate number(2),

Etaj number(2),

Nr_paturi number(1),

Tel varchar2(2),

Tv varchar2(2),

Pret number(10),

Observatii varchar2(30),

Constraint tel_ck check (tel in('da','nu')),

Constraint tv_ck check(tv in('da','nu')),

constraint FK_camere foreign key (cod_unitate) references unitati(cod_unitate));

Create table ocupare

(cod_contract number(2) constraint PK_ocupare primary key,

Cod_camera number(2),

Data_s date,

Data_p date,

constraint FK_ocupare foreign key (cod_camera) references camere(cod_camera));

Create table clienti

(cod_client number(2) constraint PK_clienti primary key,

Cod_camera number(2),

Nume_client varchar2(30),

Dom_localitate varchar2(25),

Dom_strada varchar2(25),

Cnp varchar2(15),

Tel varchar2(20),

constraint FK_clienti foreign key (cod_camera) references camere(cod_camera));

Create table servicii

(nr_comanda number(2) constraint PK_servicii primary key,

cod_client number(2),

den_serviciu varchar2(20),

pret number(10) ,

constraint FK_servicii foreign key (cod_client) references clienti(cod_client));

prompt se sterg datele din tabele

delete from agentii;

delete from unitati;

delete from camere;

delete from ocupare;

delete from clienti;

delete from servicii;

prompt se introduc datele in tabele

Insert into agentii values(1,'alfa','bucuresti','12345678');

Insert into agentii values(2,'beta','bucuresti','1237654');

Insert into agentii values(3,'x','brasov','87654321');

Insert into agentii values(4,'holiday','timisoara','927651');

Insert into agentii values(5,'hotels','targoviste','345271');

Insert into unitati values(1,'plaza',1,'hotel','****','bucuresti');

Insert into unitati values(2,'marriot',1,'hotel','*****','bucuresti');

Insert into unitati values(3,'sofitel',2,'hotel','***','bucuresti');

Insert into unitati values(4,'intercontinental',1,'motel','***','bucuresti');

Insert into unitati values(5, 'parc',1,'hotel','*****','sinaia');

Insert into unitati values(6,'dunarea',3,'pensiune','**','predeal');

Insert into unitati values(7,'valahia',5,'hotel','*****','targoviste');

Insert into unitati values(8,'timis',4,'hotel','*****','timisora');

Insert into unitati values(9,'cerbul',2,'hotel','*','bucuresti');

Insert into unitati values(10,'lido',3,'motel','**','arad');

Insert into camere values(1,1,1,1,'da','da',500,'');

Insert into camere values(2,1,1,2,'nu','da',300,'');

Insert into camere values(3,1,6,3,'da','da',200,'');

Insert into camere values(4,2,4,2,'nu','nu',100,'');

Insert into camere values(5,3,3,2,'nu','nu',150,'');

Insert into camere values(6,4,2,2,'da','nu',200,'');

Insert into camere values(7,5,5,1,'nu','nu',100,'');

Insert into camere values(8,6,4,1,'da','da',400,'');

Insert into camere values(9,7,3,3,'da','nu',300,'');

Insert into camere values(10,8,2,2,'nu','da',350,'');

Insert into camere values(11,9,1,1,'da','da',550,'');

Insert into camere values(12,10,2,1,'da','nu',200,'');

Insert into camere values(13,9,2,1,'da','da',600,'');

Insert into camere values(14,8,1,2,'nu','nu',140,'');

Insert into camere values(15,7,1,1,'nu','da',330,'');

Insert into camere values(16,6,5,2,'nu','da',200,'');

Insert into camere values(17,5,5,3,'nu','da',500,'');

Insert into camere values(18,4,6,3,'nu','da',127,'');

Insert into camere values(19,3,2,2,'nu','da',400,'');

Insert into camere values(20,2,3,2,'nu','da',200,'');

Insert into camere values(21,3,4,1,'nu','da',300,'');