Academia de Studii Economice Facultatea de Cibernetică, Statistică şi Informatică Economică
SISTEME DE BAZE DE DATE Proiect Baze de Date Spatiale
Indrumător: Indrumător: Lect.Univ. Dr. Anda Belciu Student: Stan Ştefan Ştefan Antoniu Master E-Business
1. Descrierea bazei de date si schema conceptuala
Tabelele create formează o bază de date care ajută la monitorizarea activităţii activităţii celor mai mari si importante spitale din Bucuresti. Baza de date este formată din formată din trei tabele: SPITALE, SP ITALE, DOCTORI si PACIENTI. Legăturile Legăturile dintre aceste tabele sunt realizate prin mai multe chei externe.
2. Datele spatiale despre spitale A- Spitalul Universitar de Urgenta = 44.437089, 26.072075 C-Spitalul Universitar de Urgenta Elias = 44.466131, 26.072933 D- Spitalul Clinic de Urgenta Floreasca = 44.454247, 26.101257 E- Spitalul Clinic Coltea = 44.435006, 26.10366 H- Spitalul “Grigore Alexandrescu” = 44.452286, 26.090271 I- Spitalul Militar Central = 44.44383, 26.074135 J- Spitalul Clinic “Sfanta Maria” = 44.457065, 26.07877 a- Spitalul Sfantul Spiridon, Iasi = 47.169133,27.582911 b- Spitalul de Oncologie, Cluj = 46.763692,23.587107 c- Spitalul Judetean, Timisoara = 45.738192,21.242084
3. Crearea tabelelor
CREATE TABLE "SPITALE" ("COD_SPITAL" NUMBER PRIMARY KEY, "DENUMIRE" VARCHAR2(30), "STRADA" VARCHAR2(60), "NUMAR" number(4), "NR_ANGAJATI" number(4), "LATITUDINE" NUMBER(15,12), "LONGITUDINE" NUMBER(15,12), "GEOM" "SDO_GEOMETRY" );
CREATE TABLE "DOCTORI" ("COD_DOCTOR" NUMBER PRIMARY KEY, "NUME" VARCHAR2(40), "PRENUME" VARCHAR2(60), "VECHIME" number(4), "SPECIALIZARE" number(4), "COD_SPITAL_A" NUMBER(4), CONSTRAINT TS_fk1 FOREIGN KEY(COD_SPITAL_A) REFERENCES SPITALE(COD_SPITAL));
CREATE TABLE "PACIENTI" ("COD_PACIENT" NUMBER PRIMARY KEY, "NUME" VARCHAR2(40), "PRENUME" VARCHAR2(60), "DATA_CONSULTATIEI" DATE, "SPECIALIZARE" number(4), "COD_SPITAL_C" NUMBER(4), "COD_DOCTOR_C" NUMBER(4), CONSTRAINT TS_fk2 FOREIGN KEY(COD_SPITAL_C) REFERENCES SPITALE(COD_SPITAL), CONSTRAINT TS_fk3 FOREIGN KEY(COD_DOCTOR_C) REFERENCES DOCTORI(COD_DOCTOR));
4. Adaugarea datelor spatiale
SPITALE
INSERT INTO SPITALE (COD_SPITAL,TIP_SPITAL,STRADA,NUMAR,NR_ANGAJATI,LATITUDINE,LONGITUDINE,GEOM) values (1,'S. Clinic Coltea','Bvd. Ion C. Bratianu',1,342,44.435006,26.10366, MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(26.10366,44.435006,NULL),NULL,N ULL)); INSERT INTO SPITALE (COD_SPITAL,DENUMIRE,STRADA,NUMAR,NR_ANGAJATI,LATITUDINE,LONGITUDINE,GEOM) values (2,'S. Universitar de urgenta','Splaiul Independentei',169,783,44.437089,26.072075, MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(26.072075,44.437089,NULL),NULL,N ULL)); INSERT INTO SPITALE (COD_SPITAL,DENUMIRE,STRADA,NUMAR,NR_ANGAJATI,LATITUDINE,LONGITUDINE,GEOM) values (3,'S. Militar Central','Calea Plevnei',88,578,44.44383,26.074135, MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(26.074135,44.44383,NULL),NULL,N ULL)); INSERT INTO SPITALE (COD_SPITAL,DENUMIRE,STRADA,NUMAR,NR_ANGAJATI,LATITUDINE,LONGITUDINE,GEOM) values (4,'S. Grigore Alexandrescu','Bvd Iancu de Hunedoara',31,389,44.452286,26.090271, MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(26.090271,44.452286,NULL),NULL,N ULL)); INSERT INTO SPITALE (COD_SPITAL,DENUMIRE,STRADA,NUMAR,NR_ANGAJATI,LATITUDINE,LONGITUDINE,GEOM) values (5,'S.de Urgenta Floreasca','Calea Floreasca',8,642,44.454247,26.101257, MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(26.101257,44.454247,NULL),NULL,N ULL)); INSERT INTO SPITALE (COD_SPITAL,DENUMIRE,STRADA,NUMAR,NR_ANGAJATI,LATITUDINE,LONGITUDINE,GEOM) values (6,'S. Clinic Sfanta Maria','Bvd Ion Mihalache',38,254,44.457065,26.07877, MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(26.07877,44.457065,NULL),NULL,N ULL));
INSERT INTO SPITALE (COD_SPITAL,DENUMIRE,STRADA,NUMAR,NR_ANGAJATI,LATITUDINE,LONGITUDINE,GEOM) values (7,'S.U. de urgenta Elias','Bvd Marasti',17,396,44.466131,26.072933, MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(26.072933,44.466131,NULL),NULL,N ULL)); INSERT INTO SPITALE (COD_SPITAL,DENUMIRE,STRADA,NUMAR,NR_ANGAJATI,LATITUDINE,LONGITUDINE,GEOM) values (8,'S.Sfantul Spiridon, Iasi','Bvd. Independentei',1,290,47.169133,27.582911, MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(27.582911,47.169133,NULL),NULL,N ULL)); INSERT INTO SPITALE (COD_SPITAL,DENUMIRE,STRADA,NUMAR,NR_ANGAJATI,LATITUDINE,LONGITUDINE,GEOM) values (9,'S. de Oncologie, Cluj','Ion Creanga',35,359,46.763692,23.587107 , MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(23.587107,46.763692,NULL),NULL,N ULL)); INSERT INTO SPITALE (COD_SPITAL,DENUMIRE,STRADA,NUMAR,NR_ANGAJATI,LATITUDINE,LONGITUDINE,GEOM) values (10,'S. Judetean, Timisoara','Bvd Dr Iosif Bulbuca',1,603,45.738192,21.242084 , MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(21.242084 ,45.738192,NULL),NULL,NULL));
INSERT INTO DOCTORI (COD_DOCTOR,NUME,PRENUME,VECHIME,SPECIALIZARE,COD_SPITAL_A) values (101,'Bacalbasa','Ion',27,11,3);
INSERT INTO PACIENTI (COD_PACIENT,NUME,PRENUME,DATA_CONSULTATIEI,COD_SPITAL_C,COD_DOCTOR_C) values (702,'Neacsu','Bianca','13-sep-12',2,102);
5. - Actualizarea tabelei de metadate spaţiale INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ( 'SPITALE', 'geom', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('Longitude', -180, 180, 0.5), SDO_DIM_ELEMENT('Latitude', -90, 90, 0.5) ), 8307 );
6 - Crearea indecşilor spaţiali
CREATE INDEX spitale_spatial_idx ON SPITALE(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
7-
Vizualizarea datelor spaţiale în Map View
8 Interogari a) Aflati distanta (in metri) dintre spitalul “Grigore Alexandrescu” si spitalul Universitar de Urgenta din Bucuresti. SELECT SDO_GEOM.SDO_DISTANCE(s1.geom,s2.geom, 0.005) distanta FROM spitale s1, spitale s2 WHERE s1.COD_SPITAL='4' and s2.COD_SPITAL='2';
b) Aflati distanta (in metri) dintre spitalul clinic “Coltea” si spitalul judetean din Timisoara. SELECT SDO_GEOM.SDO_DISTANCE(s1.geom,s2.geom, 0.005) distanta FROM spitale s1, spitale s2 WHERE s1.COD_SPITAL='1' and s2.COD_SPITAL='10';
c) Afisati o zona tampon de 10 km in jurul spitalului de oncologie din Cluj. SELECT s.denumire, SDO_GEOM.SDO_BUFFER(s.geom, m.diminfo, 10,'unit=km') FROM spitale s, user_sdo_geom_metadata m WHERE s.cod_spital=9
d) Afisati distanta dintre toate spitalele DECLARE CURSOR c is SELECT * FROM spitale; k number:=0; BEGIN FOR i in c LOOP dbms_output.put_line('Distanta de la ' || i.denumire ||' la '); FOR r in (SELECT s.denumire,sdo_geom.sdo_distance(i.geom,s.geom,0.5) distanta FROM spitale s WHERE i.cod_spital != s.cod_spital) LOOP dbms_output.put(r.denumire||' este de '); dbms_output.put(round(r.distanta) || ' metri'); dbms_output.put_line(' '); END LOOP; dbms_output.put_line(' '); END LOOP; END;
e) Afisati toate spitalele care au mai mult de 500 de angajati
f) Afisati toate spitalele aflate la o distanta mai mica de 3 km fata de spitalul cu codul 4 . SELECT s.denumire, s.geom FROM spitale s WHERE SDO_WITHIN_DISTANCE (s.geom,(SELECT geom FROM spitale WHERE cod_spital=4), 'distance=2 unit=km') = 'TRUE';
g) Aflati distanta de la spitalul de urgenta Floreasca la toate spitalele din baza de date DECLARE CURSOR a is SELECT * FROM spitale; x number; BEGIN for i in a loop SELECT sdo_geom.sdo_distance(i.geom, s.geom, 0.005, 'Unit = km') INTO x FROM spitale s WHERE s.cod_spital = 5; dbms_output.put_line('Distanta de la Floreasca la spitalul ' || i.cod_spital || ' este ' || round(x)); end loop; END;
