Proiect - Baze de Date

1.Tema Proiectului

Se considera o firma ce ofera servicii de Internet, Telefonie si Televiziune. Se doreste crearea unei baze de date care sa tina evidenta clientilor, a serviciilor oferite de firma, serviciile contractate de clienti si a agentilor din cadrul firmei. Schema bazei de date:

2. Creare tabele, creare indecsi, creare sinonime, adaugare restrictii de integritate, modificare structura tabele

prompt

prompt ====================

prompt Creare tabela JUDETE

prompt ====================

create table judete

(

cod_judet varchar2(2) primary key,

nume_judet varchar2(30) not null

);

prompt

prompt =====================

prompt Creare tabela CLIENTI

prompt =====================

create table clienti

(

id_client number(8) primary key,

nume_client varchar2(20) not null,

prenume_client varchar2(20) not null,

cnp varchar2(13) not null,

adresa varchar2(100) not null,

cod_judet varchar2(2) not null,

telefon varchar2(20) not null

);

prompt

prompt ======================

prompt Creare tabela SERVICII

prompt ======================

create table servicii

(

cod_serviciu varchar2(8) primary key,

denumire_serviciu varchar2(20) not null,

pret number(8,2) not null

);

prompt

prompt ====================

prompt Creare tabela AGENTI

prompt ====================

create table agenti

(

id_agent number(8) primary key,

nume_agent varchar2(20) not null,

prenume_agent varchar2(20) not null,

data_angajare date,

bonus number(2,2)

);

prompt

prompt =======================

prompt Creare tabela CONTRACTE

prompt =======================

create table contracte

(

id_contract number(8) primary key,

id_client number(8) not null references clienti(id_client) on delete cascade,

id_agent number(8) references agenti(id_agent) on delete set null,

cod_serviciu varchar2(8) not null references servicii(cod_serviciu) on delete cascade,

data_inceput date not null,

data_sfarsit date

);

prompt

prompt ======================================

prompt Adaugarea unui camp in tabela clienti

prompt =====================================

alter table clienti add (email varchar2(40));

prompt

prompt ==============================================================================

prompt Campul denumire_servicii trebuie sa permita introducerea mai multor caractere.

prompt ==============================================================================

alter table servicii modify (denumire_serviciu varchar2(50));

prompt

prompt ====================

prompt Creare secvente

prompt ====================

create sequence seq_id_client start with 1 increment by 1;

create sequence seq_id_contract start with 1 increment by 1;

create sequence seq_id_agent start with 1 increment by 1;

prompt

prompt ==================================

prompt Adaugare restrictii de integritate

prompt ==================================

alter table clienti add constraint cnp_valid check(length(cnp)=13 and substr(cnp,1,1) in ('1','2'));

alter table clienti add constraint cnp_UK unique (cnp);

alter table clienti add constraint cod_judet_FK foreign key(cod_judet) references judete(cod_judet);

alter table clienti add constraint email_UK unique (email);

alter table agenti add constraint bonus_valid check(bonus>=0);

prompt

prompt ====================

prompt Adaugare indecsi

prompt ====================

create index contracte_data_inceput_IDX on contracte(data_inceput);

create index agenti_nume_agent_IDX on agenti(nume_agent);

prompt

prompt ====================

prompt Creare sinonime

prompt ====================

create synonym clienti_firma for clienti;

create synonym agenti_firma for agenti;