Baza de Date pentru Gestiunea Politelor de Asigurare

Cuprins

Prezentare

Schema logica a bazei de date

Codul sursa pentru generarea bazei de date

Crearea tabelelor

Crearea regulilor de integritate

Crearea cheilor externe

Crearea indecsilor

Crearea secventelor si triggerilor

Crearea vederilor

Popularea bazei de date

Exemple operatii de actualizare

Prezentare

Tema este realizarea unei baze de date care sa reflecte activitatea unei societati de asigurari auto. Baza de date pastreaza evidenta clientilor, a autovehiculelor, a politelor incheiate precum si a agentilor.

Schema logica a bazei de date

Codul sursa pentru generarea bazei de date

Crearea tabelelor

CREATE TABLE UTILIZATORI (COD NUMBER(15, 0) NOT NULL,

NUME VARCHAR2(30) NOT NULL,

ADRESA VARCHAR2(50) NOT NULL,

TEL NUMBER(10, 0) NOT NULL,

TIP VARCHAR2(1) NOT NULL,

NUMEUTILIZATOR VARCHAR2(10) NOT NULL,

PAROLA VARCHAR2(10) NOT NULL

)

CREATE TABLE POLITA (IDPOLITA NUMBER(7, 0) NOT NULL,

IDASIGURAT NUMBER(7, 0) NOT NULL,

SERIE VARCHAR2(2) NOT NULL, NR NUMBER(7, 0) NOT NULL,

CODAGENT NUMBER(15, 0) NOT NULL,

DATASTART DATE NOT NULL,

DATASTOP DATE NOT NULL,

DATAEMITERE DATE NOT NULL,

DOCPLATA VARCHAR2(10) NOT NULL,

TIPPLATA NUMBER(1, 0) NOT NULL,

PRIMA NUMBER(8, 0) NOT NULL,

NRAUTOVEHICUL VARCHAR2(7) NOT NULL,

DESPAGUBIRE NUMBER(9, 0) NOT NULL

)

CREATE TABLE CLIENTI (CODCLIENT NUMBER(15, 0) NOT NULL,

NUME VARCHAR2(35) NOT NULL,

ADRESA VARCHAR2(30) NOT NULL,

TIP VARCHAR2(20) NOT NULL,

TEL NUMBER(10, 0) NOT NULL

)

CREATE TABLE TIPAUTO (CODTIP NUMBER(2, 0) NOT NULL,

DENUMIRE VARCHAR2(40) NOT NULL

)

CREATE TABLE AUTOVEHICUL (NRAUTO VARCHAR2(7) NOT NULL, CODTIP NUMBER(2) NOT NULL,

MARCA VARCHAR2(15) NOT NULL,

MODEL VARCHAR2(15) NOT NULL,

SERIESASIU VARCHAR2(20) NOT NULL,

CAPACITATE NUMBER(5) NOT NULL,

NRLOCURI NUMBER(3) NOT NULL,

MASA NUMBER(5) NOT NULL,

PUTERE NUMBER(3) NOT NULL

)

ALTER TABLE UTILIZATORI ADD (CONSTRAINT UTILIZATORI_BRW_P1 PRIMARY KEY(COD), CONSTRAINT UTILIZATORI_BRW_U1 UNIQUE(NUMEUTILIZATOR)) ENABLE CONSTRAINT UTILIZATORI_BRW_P1 ENABLE CONSTRAINT UTILIZATORI_BRW_U1

ALTER TABLE POLITA ADD (CONSTRAINT POLITA3_BRW_P1 PRIMARY KEY(IDPOLITA)) ENABLE CONSTRAINT POLITA3_BRW_P1

ALTER TABLE CLIENTI ADD (CONSTRAINT CLIENTI3_BRW_P1 PRIMARY KEY(CODCLIENT)) ENABLE CONSTRAINT CLIENTI3_BRW_P1

ALTER TABLE TIPAUTO ADD (CONSTRAINT TIPAUTO_BRW_P2 PRIMARY KEY(CODTIP)) ENABLE CONSTRAINT TIPAUTO_BRW_P2

ALTER TABLE AUTOVEHICUL ADD (CONSTRAINT AUTOVEHICUL3_BRW_P1 PRIMARY KEY(NRAUTO)) ENABLE CONSTRAINT AUTOVEHICUL3_BRW_P1

Crearea regulilor de integritate

ALTER TABLE UTILIZATORI ADD (CONSTRAINT UTILIZATORI_BRW_C5 CHECK(TIP IN ('A', 'I', 'O'))) ENABLE CONSTRAINT UTILIZATORI_BRW_C5

ALTER TABLE CLIENTI ADD (CONSTRAINT CLIENTI3_BRW_C1 CHECK(TIP IN ('F', 'J', 'P', 'H'))) ENABLE CONSTRAINT CLIENTI3_BRW_C1

Crearea cheilor externe

ALTER TABLE POLITA ADD (CONSTRAINT POLITA3_BRW_F1 FOREIGN KEY(IDASIGURAT) REFERENCES CLIENTI(CODCLIENT)) ENABLE CONSTRAINT POLITA3_BRW_F1

ALTER TABLE POLITA ADD (CONSTRAINT POLITA3_BRW_F3 FOREIGN KEY(NRAUTOVEHICUL) REFERENCES AUTOVEHICUL(NRAUTO)) ENABLE CONSTRAINT POLITA3_BRW_F3

ALTER TABLE POLITA ADD (CONSTRAINT POLITA3_BRW_F4 FOREIGN KEY(CODAGENT) REFERENCES UTILIZATORI(COD)) ENABLE CONSTRAINT POLITA3_BRW_F4

ALTER TABLE AUTOVEHICUL ADD (CONSTRAINT AUTOVEHICUL3_BRW_F1 FOREIGN KEY(CODTIP) REFERENCES TIPAUTO(CODTIP)) ENABLE CONSTRAINT AUTOVEHICUL3_BRW_F1

Crearea indecsilor

CREATE UNIQUE INDEX POLITA_BRW_P2 ON POLITA(IDPOLITA)

CREATE UNIQUE INDEX CLIENTI_BRW_P2 ON CLIENTI(CODCLIENT)

CREATE UNIQUE INDEX TIPAUTO_BRW_P2 ON TIPAUTO(CODTIP)

CREATE UNIQUE INDEX AUTOVEHICUL_BRW_P2 ON AUTOVEHICUL(NRAUTO)

Crearea secventelor si triggerilor

CREATE SEQUENCE SEQ_COD_POLITA

INCREMENT BY 1

START WITH 1

MAXVALUE 1000000

NOCACHE

CREATE OR REPLACE TRIGGER GENERARE_CODPOLITA

BEFORE INSERT ON POLITA

FOR EACH ROW

BEGIN

SELECT SEQ_COD_POLITA.NEXTVAL INTO :NEW.IDPOLITA FROM DUAL

END

Crearea vederilor

CREATE OR REPLACE VIEW VIEWPOLITA AS

SELECT P.IDPOLITA, C.NUME, A.MARCA, A.MODEL

FROM POLITA P, CLIENTI C, AUTOVEHICUL A

WHERE P.IDASIGURAT = C.CODCLIENT AND P.NRAUTOVEHICUL = A.NRAUTO

Popularea bazei de date

INSERT INTO CLIENTI VALUES(1, 'Cosmin Ivan', 'Bucuresti', 'F', 7716853)

INSERT INTO CLIENTI VALUES(2, 'Marius Ionescu', 'Bucuresti Mall', 'F', 3224982)

INSERT INTO CLIENTI VALUES (3,'Imred Soft SRL','Str Margelelor nr 3','J',6442344)

INSERT INTO CLIENTI VALUES (4,'Ivan Cosmin','Bld Iuliu Maniu 433','P',2412554)

INSERT INTO CLIENTI VALUES (5,'Dumitru Rodica','Str Ghirlandei nr 22','F',3320124)

INSERT INTO CLIENTI VALUES (6,'Rotri Consult SRL','Str Maciesului nr 87','J',2210354)

INSERT INTO AUTOVEHICUL VALUES('B99PCL', 1, 'Dacia', , 'U11JR100', 1397, 5, 1380, 46)

INSERT INTO AUTOVEHICUL VALUES('B81MGY', 1, 'Trabant', 'S', 'UR100', 300, 4, 980, 10)

INSERT INTO POLITA VALUES(1, 1, 1, 'aa', 10, 1, SYSDATE, SYSDATE, SYSDATE, 'CHIT 101', 1, 500000, 'b99pcl', 100)

Exemple operatii de actualizare

UPDATE CLIENTI SET ADRESA = 'adresa noua' WHERE CODCLIENT = 1;

UPDATE UTILIZATORI SET TEL = ADRESA = 'adresa noua' WHERE COD = ;

UPDATE UTILIZATORI SET DATASTOP = SYSDATE WHERE IDPOLITA = ;

DELETE FROM CLIENTI WHERE CODCLIENT = ;

DELETE FROM UTILIZATORI WHERE COD = ;