Ejercicio 3 empresa logística

Empresa de Logística

Crea un tablespace llamado LOGISTICS

Crea tres usuarios cuyo default tablespace sea ese

• LOGISTICS_USER con rol de connect
• LOGISTICS_DEV con rol de connect,resources
• LOGISTICS_ADM con rol de sysdba

Tablas

Entra con SQLDeveloper con LOGISTICS_DEV y crea las siguientes tablas:

INSURANCE
insuranceid integer pk,
description varchar2(30),
renewal date

• Crear claves primarias
• Crear trigger de autonumérico
• Check donde renewal sea mayor que la fecha de hoy
• Introduce unos registros de ejemplo.

-----------------------------------------------------------------------------------------------------

create table INSURANCE(
insuranceid integer primary key NOT NULL,
description varchar2(30),
renewal date
--constraint fecha_mayor_ck check (renewal>sysdate)
);

CREATE SEQUENCE IncrINSURANCE
  MINVALUE 1
  INCREMENT BY 1
  START WITH 1
  nomaxvalue
;

Create Trigger TriggerINSURANCE
  before insert on INSURANCE
  for each row
  begin
    Select IncrINSURANCE.nextval into:new.insuranceid from dual;
  end;
/

-----------------------------------------------------------------------------------------------------

TRUCK
plate char(10) pk
model varchar2(30),
insuranceid integer not null fk

• Crear claves primarias y extranjeras
• crear trigger de autonumérico --PARA QUE UN TRIGGER EN UN CHAR.... SE HACE Y YA ESTA
• Introduce unos registros de ejemplo.

-----------------------------------------------------------------------------------------------------

create table TRUCK (
plate char(10) primary key not null,
model varchar2(30),
insuranceid integer not null
);

alter table TRUCK
add constraint FK_Truck_insuranceid
foreign key (insuranceID)
references insurance(insuranceid);

-----------------------------------------------------------------------------------------------------

DRIVER
driverid integer primary key,
name varchar2(30),
license char(15),
birthdate date

• Crear claves primarias
• Crear trigger de autonumérico
• Check donde renewal sea menor que la fecha de hoy
• Introduce unos registros de ejemplo.

----------------------------------------------------------------------------------------------------

create table DRIVER(
driverid integer primary key,
name varchar2(30),
license char(15),
birthdate date
--constraint fecha_mayor_ck check (birthdate>sysdate) -- NO FUNCIONA!!!
);
CREATE SEQUENCE Incrdriver
  MINVALUE 1
  INCREMENT BY 1
  START WITH 1
  nomaxvalue
;
Create Trigger TriggerDRIVERID
  before insert on DRIVER
  for each row
  begin
    Select IncrDRIVER.nextval into:new.DRIVERID from dual;
  end;
/

-----------------------------------------------------------------------------------------------------

Tanto DELIVERY COMO PACKAGE deben formar parte de un cluster

-----------------------------------------------------------------------------------------------------

CREATE CLUSTER CLUSTER_TRANSPORTE
 (TRANSCODE NUMBER(4))
SIZE 512
STORAGE (initial 100K next 50K);

-----------------------------------------------------------------------------------------------------

DELIVERY

deliveryid integer primary key,

origin varchar(30),

destiny varchar(40),

departure timestamp,

arrival timestamp,

driverid integer fk,

truckid integer fk


cluster CLUSTER_TRANSPORTE(deliveryid );

• Crear claves primarias y extranjeras
• Check donde arrival sea mayor que la fecha de hoy
• Crear trigger de autonumérico
• Introduce unos registros de ejemplo.

-----------------------------------------------------------------------------------------------------

create table DELIVERY(
deliveryid integer primary key,
origin varchar(30),
destiny varchar(40),
departure timestamp,
arrival timestamp,
driverid integer, --fk
truckid integer --fk
)
CLUSTER CLUSTER_TRANSPORTE (deliveryid);
alter table DELIVERY
add constraint FK_DELIVERY_DRIVERid
foreign key (DRIVERID)
references DRIVER(DRIVERid);
alter table DELIVERY
add constraint FK_DELIVERY_TRUCKid
foreign key (PLATE)
references TRUCK(PLATE);
-----------------------------------------------------------------------------------------------------

CUSTOMER
customerid integer pk,
name varchar2(40) not null,
address varchar2(40) default 'unknown',
email varchar2(100)

• Crear claves primarias
• Crear trigger de autonumérico
• Introduce unos registros de ejemplo.

-----------------------------------------------------------------------------------------------------

CREATE TABLE CUSTOMER(
customerid integer primary key,
name varchar2(40) not null,
address varchar2(40) default 'unknown',
email varchar2(100)
);
CREATE SEQUENCE Incrcustomer
  MINVALUE 1
  INCREMENT BY 1
  START WITH 1
  nomaxvalue
;
Create Trigger TriggerCUSTOMER
  before insert on CUSTOMER
  for each row
  begin
    Select Incrcustomer.nextval into:new.customerid from dual;
  end;-----------------------------------------------------------------------------------------------------

PACKAGE
packageid integer pk,
description varchar2(40),
withcare char(1) default 'Y',
customerid integer fk,
deliveryid integer fk

• Crear claves primarias y extranjeras
• Crear trigger de autonumérico
• Introduce unos registros de ejemplo.

-----------------------------------------------------------------------------------------------------

create table PACKAGE(
packageid integer primary key,
description varchar2(40),
withcare char(1) default 'Y',
customerid integer, -- fk,
deliveryid integer -- fk
);
CLUSTER CLUSTER_TRANSPORTE (packageid);
alter table package
add constraint FK_PACKAGE_CUSTOMERID
foreign key (CUSTOMERID)
references CUSTOMER(CUSTOMERid);
alter table PACKAGE
add constraint FK_PACKAGE_DELIVERYID
foreign key (DELIVERYID)
references DELIVERY(DELIVERYID);
-----------------------------------------------------------------------------------------------------

Sinónimos

Por cada tabla crea un sinónimo para que se puedan manejar
las tablas con el prefijo LOGISTICS.
-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

Consultas

• Crea una consulta que muestre todos los nombres de camiones.
• Crea una consulta que muestre el total de paquetes que se han mandado.
• Crea una consulta que muestre los nombres de los conductores que han hecho algún envío.
• Crea una consulta que solicite un código de conductor y muestre los camiones que ha utilizado en un envío
• Crea una consulta que solicite un envío y muestre los paquetes que van en ese envío.
• Crea una consulta que muestre la suma de paquetes que hay en cada envío.
• Crea una consulta que solicite un código de paquete y muestre el nombre del conductor que lo transportaba
• Crea una consulta que dado un código de envío elimine todos los paquetes que forman parte de el.