Proiect 7 BD PDF

Preview

Summary

Proiectul 7 Baze de Date, tematica: firma de paza...

Description

FACULTATEA DE AUTOMATICĂ ȘI CALCULATOARE DEPARTAMENTUL DE AUTOMATICĂ ȘI INFORMATICĂ APLICATĂ

Proiect Nr. 7

Monitorizarea evenimentelor unei firme de pază

Temă la disciplina:

Baze de Date

*Nume* Ingineria Sistemelor Anul II Grupa 6.2

Enunț:

An Universitar 2018-2019 Semestrul II

Se consideră o aplicație pentru monitorizarea evenimentelor unei firme de pază. Baza de date Oracle va conține urmatoarele informații:  Nume client  CNP  Adresa  Eveniment (descriere)  De plată  Data producerii evenimentului  Agentul care a constatat evenimentul  Timpul de reactie la eveniment (în cât timp a ajuns agentul la fața locului)  Timp mediu de reație pe agent. Știind că Nume client are maxim 14 caractere, CNP are exact 13 caractere, adresa are maxim 10 caractere, eveniment are maxim 20 caractere, agentul este de maxim 10 caractere, de plată este un număr cu 2 zecimale, pozitiv, timpul de reacție este pozitiv ți exprimat în secunde, se cere:

1. Să se realizeze proiectarea bazei de date aferente (structura de tabele, structura de coloane a fiecărei tabele, constrângeri). Am decis să utilizez o structura cu două tabele relaționate prin câmpul CNP, o tabelă intitulată Clienți iar a doua, Evenimente. Tabela Clienți conține câmpurile Nume, CNP (cheie primară, căci trebuie să fie unic si diferit de null), Adresă (cu constrângerea NOT NULL, clientul trebuie să aiba o adresă) si De plată (cu valoarea implicită 0). Tabela Evenimente cuprinde câmpurile Eveniment, Agent (numele acestuia), CNP Client (cu constrângere de tip FOREIGN KEY), Dată eveniment și Timpul de reacție. Timpul mediu de reacție nu a fost inclus în câmpurile tabelei deoarece se poate calcula foarte ușor în cadrul unei interogări. În plus, dacă acel camp era introdus în tabela Evenimente, valorile s-ar fi repetat, căci există posibilitatea ca același agent să acționeze în mai multe evenimente, ceea ce ar fi creat redundanță. Pentru a evita redundanța, s-ar fi putut crea o tabelă cu agenții companiei, dar asta ar fi complicat introducerea datelor, căci timpul mediu de reacție trebuia recalculat, acest lucru încetinind lucrul cu baza de date. Toate câmpurile respectă constrângerile din cerință.

2. Să se scrie comenzile SQL pentru tabelele proiectate la punctul anterior. CREATE TABLE Clienti ( Nume varchar(14) NOT NULL, CNP char(13) PRIMARY KEY, Adresa varchar(10) NOT NULL, De_plata number(5,2) DEFAULT 0 CHECK ( De_plata>=0 ) ); CREATE TABLE Evenimente ( Eveniment varchar(20) NOT NULL, Agent varchar(10) NOT NULL, CNP_Client char(13) NOT NULL, Data_eveniment date NOT NULL, Timp_reactie number(5) NOT NULL,

FOREIGN KEY(CNP_Client) REFERENCES Clienti(CNP) ON DELETE CASCADE );

Rezultat:

3. Să se scrie comenzile SQL pentru popularea bazei de date cu urmatoarele produse, cu 4 clienți, 2 având câte 3 evenimente, unul un eveniment și altul nici un eveniment.Evenimentele sunt toate constatate de agenți diferiți. INSERT INTO Clienti(Nume,CNP,Adresa) VALUES ('Ion','1970413423371','Timisoara'); INSERT INTO Clienti(Nume,CNP,Adresa) VALUES ('Adi','1960216107056','Arad'); INSERT INTO Clienti(Nume,CNP,Adresa) VALUES ('Andrei','1940827210915','Timisoara'); INSERT INTO Clienti(Nume,CNP,Adresa) VALUES ('Vali','1970111110715','Lugoj'); INSERT INTO Evenimente VALUES ('Jaf armat','Cristi','1970413423371','12.04.2019',100); INSERT INTO Evenimente VALUES ('Agresiune fizica','Alin','1970413423371','01.04.2019',150); INSERT INTO Evenimente VALUES ('Tentativa de omor','Mihai','1970413423371','03.03.2019',210); INSERT INTO Evenimente VALUES ('Spargere','Liviu','1940827210915','11.04.2019',310); INSERT INTO Evenimente VALUES ('Incaierare','Attila','1940827210915','05.04.2019',60); INSERT INTO Evenimente VALUES ('Furt','Marian','1940827210915','09.02.2019',200); INSERT INTO Evenimente VALUES ('Jaf armat','Claudiu','1970111110715','01.01.2019',60); Am făcut incă două inserturi suplimentare, pentru a putea arăta funcționalitatea unor cerințe din cele ce urmează: INSERT INTO Evenimente VALUES ('Jaf','Mihai','1970413423371','02.03.2019',200); INSERT INTO Evenimente VALUES ('Port arma alba','Ali','1970413423371','02.03.2016',150);

Rezultat:

4. Să se scrie o procedură care să permită adaugarea unui eveniment - procedura va fi apelată cu parametrii: CNP, eveniment, data, agent și timp de reacție. Procedura va actualiza și suma de plata a clientului, știind ca un eveniment pe an este gratuit, și ca restul costă 10 daca timpul a fost sub 300 secunde și 5 altfel. CREATE OR REPLACE PROCEDURE Eveniment_nou(CNP_cl char,Eveniment varchar,Data_eveniment date,Agent varchar,Timp_react number) IS suma_plata number(2); numar_even integer; BEGIN INSERT INTO Evenimente VALUES (Eveniment,Agent,CNP_cl,Data_eveniment,Timp_react); SELECT COUNT(Eveniment) INTO numar_even FROM Evenimente WHERE CNP_Client=CNP_cl AND TRUNC(Evenimente.Data_eveniment,'yyyy')=TRUNC(Data_eveniment,'yyyy') GROUP BY CNP_Client; if(numar_even>1) THEN if(Timp_react0) THEN if(:NEW.Timp_reactie...