Academia de Studii EconomiceFacultatea de Cibernetică, Statistică şi Informatică Economică

SISTEME DE BAZE DE DATE Proiect Baze de Date Spatiale

Indrumător: Lect.Univ. Dr. Anda Belciu Student: Stan Ş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 celor mai mari si importante spitale din Bucuresti. Baza de date este formată din trei tabele: SPITALE, DOCTORI si PACIENTI. 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.582911b- Spitalul de Oncologie, Cluj = 46.763692,23.587107c- 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,NULL));

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,NULL));

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,NULL));

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,NULL));

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,NULL));

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,NULL));

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,NULL));

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,NULL));

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,NULL));

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;BEGINFOR i in c LOOPdbms_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) LOOPdbms_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 sWHERE 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;BEGINfor i in a loopSELECT 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;