ditr  vom, Ejercicios de Derecho Administrativo. Universidad Carlos III de Madrid (UC3M)
criskgl
criskgl

ditr vom, Ejercicios de Derecho Administrativo. Universidad Carlos III de Madrid (UC3M)

PDF (149 KB)
20 páginas
2Número de visitas
Descripción
Asignatura: Derecho administrativo, Profesor: peep ray, Carrera: Ingenieria Informática + ADE, Universidad: UC3M
20 Puntos
Puntos necesarios para descargar
este documento
Descarga el documento
Vista previa3 páginas / 20
Esta solo es una vista previa
3 páginas mostradas de 20 páginas totales
Descarga el documento
Esta solo es una vista previa
3 páginas mostradas de 20 páginas totales
Descarga el documento
Esta solo es una vista previa
3 páginas mostradas de 20 páginas totales
Descarga el documento
Esta solo es una vista previa
3 páginas mostradas de 20 páginas totales
Descarga el documento

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');

1.2 LISTADO DE CARRETERAS POR VELOCIDAD ESTABLECIDA

select road, (L_TRAM/L_ROAD)*s_rad +

((L_ROAD- L_TRAM)/L_ROAD)*s_road as V_PROD, direction from(

select road, direction,

(CASE

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

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

END) L_ROAD,

(CASE

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

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

END) L_TRAM, speedlim AS s_rad,

(CASE

WHEN road is not NULL THEN (select speed_limit from roads where name = road )

END) s_road

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') =

TO_CHAR(add_months(sysdate,-13), 'MM-YYYY')) t1,

(select sum(amount) as y from tickets where

TO_CHAR(obs1_date , 'MM-YYYY') =

TO_CHAR(add_months(sysdate,-1), 'MM-YYYY')) t2;

7

2

Vistas

2.1 Nueva multa

create or replace view nueva_multa as

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

from (observations inner join roads

on observations.road = roads.name

and observations.speed < roads.speed_limit/2);

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

select road, abs(INIT) as INIT,

(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(

(select road, -km_point as INIT, -lead(km_point)

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

speedlim, direction

from radars where direction = 'DES'

UNION ALL

select road, km_point as INIT, lead(km_point)

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

speedlim, direction

from radars where direction = 'ASC')

RIGHT JOIN

(select * from roads)

on road = name and speedlim < speed_limit

);

2.4 Avispado

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

from observations group by nplate, road order by road)

right join

(select * from roads)

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

• Temporalidad: After

• Granularidad: Sentencia y Por fila(trigger compuesto)

• Acción: Se inserta una nueva multa.

create or replace procedure calc_tick

(nPlatein VARCHAR2, odatetimein TIMESTAMP,

roadin VARCHAR2,

km_pointin NUMBER,

directionin VARCHAR2,

speedin NUMBER,

obs_ant_nplate OUT observations%rowtype,

obs_ant_rad OUT observations%rowtype,

cuant1 OUT number, cuant2 OUT number,

cuant3 OUT number)

is

horas number;

speedTramo number;

time_min_tramo number;

max_speed_road int;

max_speed int;

diff number(10,3);

11

distance number;

pragma autonomous_transaction;

begin

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

select * INTO obs_ant_nplate from(

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

from observations where nplate = nPlatein)

where rank = 1);

--------OBSERVACION ANTERIOR MISMO RADAR-------

select * INTO obs_ant_rad from(

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

from observations where road = roadin and

direction = directionin and km_point = km_pointin)

where rank = 1);

------SANCION DE VELOCIDAD---------------------

select speedlim into max_speed from radars

where roadin = road

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

where roadin = road

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)

THEN select speed_limit into max_speed_road from roads where name = roadin;

time_min_tramo :=

(5/max_speed)+((distance-5)/max_speed_road);

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 +

extract( hour from (odatetimein-obs_ant_rad.odatetime))*3600 +

extract( minute from (odatetimein-obs_ant_rad.odatetime))*60 +

extract( second from (odatetimein-obs_ant_rad.odatetime));

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

obs_ant_rad.nplate := NULL;

obs_ant_rad.road := NULL;

obs_ant_rad.km_point := NULL;

obs_ant_rad.direction := NULL;

obs_ant_rad.odatetime := NULL;

obs_ant_rad.speed := NULL;

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;

obs_ant_rad observations%rowtype;

platein VARCHAR2(7);

odatetimein TIMESTAMP;

roadin VARCHAR2(5);

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;

roadin := :NEW.road;

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

obs_ant_rad := functions.obs_ant_radar(platein, odatetimein);

select speedlim into speedmax from radars

where road = roadin and direction = directionin

and km_point = km_pointin;

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

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

odatetimein);

cuant3 := functions.CuantDist(platein, obs_ant_rad.odatetime,

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

where nplate = obs_ant_rad.nplate;

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',

obs_ant_rad.nplate , obs_ant_rad.odatetime , date_insert,

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

• Temporalidad:Before

• Granularidad: Por fila

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

• Temporalidad:Before

• Granularidad: Por fila

-------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

3.4.1 Restricción de edad

Características

• Tabla a la que se asocia: conductores

• Eventos:Inserción

• Temporalidad:Before

• Granularidad: Por fila

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

CREATE OR REPLACE trigger restriction_edad

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;

3.4.2 Restricción de velocidad de radar

Características

• Tabla a la que se asocia: conductores

• Eventos:Inserción

21

• Temporalidad:Before

• Granularidad: Por fila

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

CREATE OR REPLACE trigger restriction_rad

BEFORE INSERT ON radars

FOR EACH ROW

DECLARE

speedlim_road NUMBER(3,0);

BEGIN

select speed_limit INTO speedlim_road from roads where name = :NEW.road;

IF(:NEW.speedlim > speedlim_road) THEN

Raise_Application_Error ( -20100, 'VELOCIDAD INVALIDA');

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 Perfil Administrativo

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!
Esta solo es una vista previa
3 páginas mostradas de 20 páginas totales
Descarga el documento