PDF (149 KB)
20 páginas
2Número de visitas
Descripción
20 Puntos
Puntos necesarios para descargar
este documento
Vista previa3 páginas / 20

1

Consultas

1.1 VEHICULOS MAS OBSERVADOS EN EL DIA DE HOY

SELECT nPlate, odatetime from (

SELECT DISTINCT COUNT(*), nPlate, odatetime

FROM OBSERVATIONS GROUP BY nPlate, odatetime)

WHERE ROWNUM <= 10 AND

TO_CHAR(odatetime, 'MM-DD-YYYY') =

TO_CHAR(SYSDATE,'MM-DD-YYYY');

(CASE

WHEN direction = 'DES' THEN MAX(INIT)-MIN(FINAL)

WHEN direction = 'ASC' THEN MAX(FINAL)- MIN(INIT)

(CASE

WHEN direction = 'DES' THEN sum(INIT - FINAL)

WHEN direction = 'ASC' THEN sum(FINAL - INIT)

(CASE

from tramo GROUP BY road, direction, speedlim) ORDER BY V_PROD DESC , road;

5

1.3 PERSONAS QUE NO CONDUCEN NINGUNO DE SUS COCHES

SELECT DISTINCT owner FROM (

(SELECT nPlate, owner FROM VEHICLES)

MINUS

(SELECT nPlate, reg_driver FROM VEHICLES)

MINUS

(SELECT nPlate, driver FROM ASSIGNMENTS)

);

6

1.4 JEFAZOS: PERSONAS CON 3 O MAS COCHES QUE NO CONDUCEN

SELECT DISTINCT count(*), noConduce FROM

(

(

SELECT DISTINCT owner as noConduce FROM

(

(SELECT owner FROM vehicles )

MINUS

(SELECT reg_driver FROM vehicles)

MINUS

(SELECT driver FROM ASSIGNMENTS)

)

) x

JOIN (SELECT owner, nPlate FROM vehicles) y on y.owner = x.noConduce

)

GROUP BY noConduce HAVING count(*) >= 3;

1.5 DIFERENCIA DE INGRESOS

select (t1.x - t2.y) as dif from

(select sum(amount) as x from tickets where

TO_CHAR(obs1_date , 'MM-YYYY') =

(select sum(amount) as y from tickets where

TO_CHAR(obs1_date , 'MM-YYYY') =

7

2

Vistas

2.1 Nueva multa

create or replace view nueva_multa as

select nplate, roads.speed_limit-observations.speed as diff, odatetime

2.2 Proteston

create or replace view proteston as

select mes, debtor from(

select mes, rank() over (partition by mes order by veces desc) rank,

debtor

from (

select mes, count(debtor) as veces, debtor

from(

select obs1_veh, to_char(obs1_date, 'YYYY-MM') AS mes, tipo, debtor

from (

(SELECT obs_veh, obs_date, tik_type, status FROM allegations) a

left JOIN

(select obs1_veh, obs1_date, tik_type as tipo, debtor from tickets) b

on a.obs_veh = b.obs1_veh and a.obs_date = b.obs1_date

and a.tik_type = b.tipo

and a.status = 'R'))

group by mes, debtor order by veces desc)) where rank = 1;

9

2.3 Tramos

create or replace view tramo as

(CASE

WHEN FINAL is NULL THEN abs(INIT+5)

WHEN FINAL - INIT <= 5 THEN abs(FINAL)

WHEN FINAL - INIT > 5 THEN abs(INIT+5)

END) FINAL

, speedlim, direction

from(

over (partition by direction, road order by km_point DESC) as FINAL,

speedlim, direction

from radars where direction = 'DES'

UNION ALL

over (partition by direction, road order by km_point ASC) as FINAL,

speedlim, direction

from radars where direction = 'ASC')

RIGHT JOIN

on road = name and speedlim < speed_limit

);

create or replace view avispado as

select * from(

select p, nplate, road, rank() over (partition by road order by p) rank

from(

select (speed_limit-V_MEDIA)/speed_limit as p, nplate, road

from(

(select nplate, sum(speed)/count(nplate) as V_MEDIA, road

right join

on road = name and V_MEDIA < speed_limit

) order by road, p desc )) where rank <= 10;

10

3

Triggers

3.1 NUEVA MULTA

3.1.1 Características

• Tabla a la que se asocia: Observaciones

• Eventos:Inserción

• Granularidad: Sentencia y Por fila(trigger compuesto)

• Acción: Se inserta una nueva multa.

create or replace procedure calc_tick

(nPlatein VARCHAR2, odatetimein TIMESTAMP,

km_pointin NUMBER,

directionin VARCHAR2,

speedin NUMBER,

obs_ant_nplate OUT observations%rowtype,

cuant1 OUT number, cuant2 OUT number,

cuant3 OUT number)

is

horas number;

speedTramo number;

time_min_tramo number;

max_speed int;

diff number(10,3);

11

distance number;

pragma autonomous_transaction;

begin

---OBSERVACION ANTERIOR MISMO NPLATE---------

select * INTO obs_ant_nplate from(

direction, speed from(

select ROW_NUMBER() over (order by odatetime desc)

as rank, nplate, odatetime, road, km_point, direction, speed

from observations where nplate = nPlatein)

where rank = 1);

select nplate, odatetime, road, km_point, direction, speed from(

select ROW_NUMBER() over (order by odatetime desc)

as rank, nplate, odatetime, road, km_point, direction,

speed

direction = directionin and km_point = km_pointin)

where rank = 1);

select speedlim into max_speed from radars

and km_pointin = km_point

and directionin = direction;

dbms_output.put_line(max_speed);

if (speedin > max_speed) THEN

cuant1 := (speedin - max_speed)*10;

ELSE cuant1 := 0;

end if;

-----SANCION DE TRAMO-----------------------

select speedlim into max_speed from radars

and km_pointin = km_point

and directionin = direction;

12

horas := extract( day from

(odatetimein-obs_ant_nplate.odatetime) )*24 +

extract( hour from

(odatetimein-obs_ant_nplate.odatetime) ) +extract(

minute from (odatetimein-obs_ant_nplate.odatetime) )/60 +

extract( second from (odatetimein-obs_ant_nplate.odatetime) )/3600;

--dbms_output.put_line(horas);

distance := abs(km_pointin - obs_ant_nplate.km_point);

speedTramo := distance/horas;

IF (distance >5)

time_min_tramo :=

IF (horas < time_min_tramo)

THEN cuant2 := (speedTramo - max_speed)*10;

ELSE cuant2 := 0;

END IF;

ELSE

IF (speedTramo > max_speed)

THEN cuant2 := (speedTramo - max_speed)*10;

ELSE cuant2 := 0;

END IF;

END IF;

-----------SANCION DE DISTANCIA----------------------

diff := extract( day from (odatetimein-obs_ant_rad.odatetime))*86400 +

IF (diff < 3.6 and diff > 0)

THEN cuant3:= (3.6 - diff)*100;

ELSE cuant3 := 0;

END IF;

------ EXCEPCIONES----------------------------------

EXCEPTION

when NO_DATA_FOUND THEN

13

end;

14

Nuestro trigger compuesto y solución final:

CREATE OR REPLACE trigger calc_tic

FOR INSERT ON observations

COMPOUND TRIGGER

cuant1 int;

cuant2 int;

cuant3 int;

obs_ant_nplate observations%rowtype;

platein VARCHAR2(7);

odatetimein TIMESTAMP;

km_pointin NUMBER(3);

directionin VARCHAR2(3);

speedin NUMBER(3);

speedmax NUMBER(3,0);

debtor VARCHAR2(9);

comeculos VARCHAR2(9);

date_var VARCHAR2(10);

date_insert date;

AFTER EACH ROW IS

BEGIN

platein := :NEW.nPlate;

odatetimein := :NEW.odatetime;

km_pointin := :NEW.km_point;

directionin := :NEW.direction;

speedin := :NEW.speed;

END AFTER EACH ROW;

AFTER STATEMENT IS

BEGIN

15

obs_ant_nplate := functions.obs_ant_nplate(platein, odatetimein);

select speedlim into speedmax from radars

and km_point = km_pointin;

cuant1 := functions.CuantVelMax2(platein, odatetimein);

cuant2 := functions.CuantTramo(platein, obs_ant_nplate.odatetime,

odatetimein);

odatetimein);

date_var := TO_CHAR(odatetimein, 'MM-DD-YYYY');

date_insert := TO_DATE(date_var, 'MM-DD-YYYY');

IF( cuant2 <> 0 or cuant1 <> 0)

THEN

select owner into debtor from vehicles

where nplate = platein;

END IF;

IF(cuant3 <> 0 )

THEN

select owner into comeculos from vehicles

END IF;

IF (cuant1 <> 0 )

THEN

insert into tickets values(platein, odatetimein , 'S',

NULL, NULL,date_insert, NULL, NULL, cuant1, debtor, 'R' );

dbms_output.put_line('Inserted S type ticket');

16

END IF;

IF (cuant2 <> 0)

THEN

insert into tickets values

(platein, obs_ant_nplate.odatetime, 'T',platein, odatetimein ,

date_insert, NULL, NULL, cuant2, debtor, 'R' );

dbms_output.put_line('Inserted T type ticket');

END IF;

IF (cuant3 <> 0)

THEN

insert into tickets values (platein, odatetimein , 'D',

NULL, NULL, cuant3, comeculos, 'R' );

dbms_output.put_line('Inserted D type ticket');

END IF;

END AFTER STATEMENT;

END;

17

3.2 PROCESAR ALEGACIÓN

3.2.1 Características

• Tabla a la que se asocia: Alegaciones

• Eventos:Inserción

CREATE OR REPLACE trigger allegat

BEFORE INSERT ON ALLEGATIONS

FOR EACH ROW

DECLARE

count1 int;

count2 int;

BEGIN

select count(*) into count1

from ASSIGNMENTS where driver = :NEW.new_debtor;

select count(*) into count2

from ALLEGATIONS

where new_debtor = :New.new_debtor

and obs_veh = :NEW.obs_veh

and obs_date = :NEW.obs_date

and tik_type = :NEW.tik_type;

IF(count1 = 0) THEN

Raise_Application_Error

( -20100,

'NUEVO DEUDOR NO ES CONDUCTOR ASIGNADO: IMPOSIBLE ALEGAR');

END IF;

IF(count1 <> 0 and count2 = 0) THEN

:NEW.status := 'A';

:NEW.exec_date := to_date(SYSDATE, 'DD-MM-YYYY');

END IF;

18

IF(count1 <> 0 and count2 <> 0) THEN

:NEW.status := 'U';

END IF;

END;

3.3 A REY MUERTO

3.3.1 Características

• Tabla a la que se asocia: vehiculos

• Eventos: Modificacion

-------a rey muerto--------------------------

CREATE OR REPLACE TRIGGER rey_mu

BEFORE UPDATE OF reg_driver ON vehicles

FOR EACH ROW

DECLARE

other_driver drivers.dni%TYPE;

BEGIN

IF :NEW.reg_driver is NULL

THEN

select driver into other_driver from(

select * from (

select * from(

(SELECT DRIVER FROM ASSIGNMENTS

WHERE NPLATE = :NEW.nplate) x

LEFT JOIN

(SELECT * FROM DRIVERS) y

ON

x.driver = y.dni

) order by lic_date

) WHERE ROWNUM = 1

19

);

:NEW.reg_driver := other_driver;

END IF;

EXCEPTION

when NO_DATA_FOUND THEN

Raise_Application_Error

( -20100, 'NO HAY CONDUCTOR ALTERNATIVO');

END;

20

3.4 RESTRICCIONES

Características

• Tabla a la que se asocia: conductores

• Eventos:Inserción

• Acción: No permite inserciones de conductores menores de 18 años

BEFORE INSERT ON drivers

FOR EACH ROW

DECLARE

mybirthdate DATE;

age number;

BEGIN

select birthdate into mybirthdate

from persons

where DNI = :NEW.DNI;

age := (SYSDATE - mybirthdate)/365;

IF( age < 18) THEN

Raise_Application_Error ( -20100, 'NO ES MAYOR DE 18');

END IF;

END;

Características

• Tabla a la que se asocia: conductores

• Eventos:Inserción

21

• Acción: No permite inserciones de conductores menores de 18 años

FOR EACH ROW

DECLARE

BEGIN

END IF;

END;

22

4

Diseño externo

4.1 Perfil Relaciones públicas

4.1.1 vistas conductores (con atributos de persona)

CREATE OR REPLACE VIEW conductores

AS

SELECT dni_person, name, surn_1, surn_2, address, town,

mobile, email, birthdate FROM (

(SELECT * FROM DRIVERS) x

LEFT JOIN

(SELECT dni as dni_person , name, surn_1, surn_2,

address, town, mobile, email, birthdate FROM PERSONS) y

ON x.dni = y.dni_person

);

4.1.2 dueños (con atributos de persona)

CREATE OR REPLACE VIEW duenos

AS

SELECT * FROM (

(SELECT owner FROM vehicles) x

LEFT JOIN

(SELECT DNI AS dni_person, name, surn_1, surn_2, address, town, mobile,

email, birthdate FROM PERSONS) y

ON x.owner = y.dni_person

);

23

4.1.3 asignaciones (dni, vehículo, cond-habitual SI/NO)

Implementación en SQL

CREATE OR REPLACE VIEW perfil_rel_publicas_2

AS

SELECT * FROM (

(SELECT owner FROM vehicles) x

LEFT JOIN

(SELECT DNI AS dni_person, name, surn_1, surn_2, address, town,

mobile, email, birthdate FROM PERSONS) y

ON x.owner = y.dni_person

);

4.1.4 Buena gente

Implementación en SQL

CREATE OR REPLACE VIEW buenagente

AS

select dni from persons

minus

select debtor from tickets t

JOIN

allegations a on

(t.obs1_date = a.obs_date

OR t.obs2_date = a.obs_date);

4.2.1 Ultima multa

CREATE OR REPLACE VIEW ultima_multa (nplate, last_ticket)

AS

select t_nPlate, max(t_date) from (SELECT CASE WHEN (tik_type = 'S')

THEN obs1_veh WHEN

(tik_type = 'T' or tik_type = 'D') THEN obs2_veh END AS

t_nPlate, CASE WHEN (tik_type = 'S') THEN obs1_date

WHEN (tik_type = 'T' OR tik_type = 'D') THEN obs2_date END

as t_date FROM TICKETS) group by t_nplate;

24

4.2.2 Impagos

CREATE OR REPLACE VIEW impagos (obs_nplate, obs_date,

tik_type, amount, penalty, total)

AS

select obs1_veh, obs1_date, tik_type, amount,

CASE

WHEN(extract(day FROM pay_date) - extract(day from sent_date) > 20)

then 2* amount

WHEN(extract(day FROM pay_date) - extract(day from sent_date) <= 20)

then 0

END,

amount + CASE

WHEN(extract(day FROM pay_date) - extract(day from sent_date) > 20)

then 2* amount

WHEN(extract(day FROM pay_date) - extract(day from sent_date) <= 20)

then 0

END FROM TICKETS

where state = 'N';

4.2.3 Notificacion

CREATE OR REPLACE VIEW notificacion

AS

SELECT nplate, owner, email, mobile, address FROM (

(SELECT nplate, owner FROM vehicles) x

LEFT JOIN

(SELECT dni as dni_persons, email, mobile, address FROM PERSONS) y

ON x.owner = y.dni_persons

);

25

comentarios (0)
No hay comentarios
¡Escribe tú el primero!