logistics

--Tablas

--Entra con SQLDeveloper con LOGISTICS_DEV y crea las siguientes tablas:


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

create sequence insurance_seq start with 1 increment by 1 nocache;

create table insurance(
insuranceid integer default insurance_seq.nextval primary key,
description varchar2 (30),
renewal date check(renewal <= timestamp)
);


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


--TRUCK
--plate char(10) pk
--model varchar2(30),
--insuranceid integer not null fk
create table truck(
plate char(10) primary key,
model varchar2(30),
insuranceid integer not null
);
--    Crear claves primarias y extranjeras
--    Introduce unos registros de ejemplo.

alter table insurance
add constraint fk_truck_insurance
foreign key (insuranceid)
references insurance(insuranceid);
--DRIVER
--driverid integer primary key,
--name varchar2(30),
--license char(15),
--birthdate date

create sequence driver_seq start with 1 increment by 1 nocache;

create table driver(
driverid integer default driver_seq.nextval primary key,
name varchar2(30),
license char(15),
birthdate date check ( birthdate < timestamp)
);
--    Crear claves primarias
--    Crear trigger de autonumérico
--    Check donde renewal sea menor que la fecha de hoy
--    Introduce unos registros de ejemplo.

--Tanto DELIVERY COMO PACKAGE deben formar parte de un cluster
--DELIVERY
--deliveryid integer primary key,
--origin varchar(30),
--destiny varchar(40),
--departure timestamp,
--arrival timestamp,
--driverid integer fk,
--truckid integer fk
CREATE CLUSTER CLUSTER_DELIVERY
 (deliverycode NUMBER(4))
SIZE 512
STORAGE (initial 100K next 50K);

CREATE INDEX IDX_CLUSTERDELIVERY ON CLUSTER CLUSTER_DELIVERY;

create sequence delivery_seq start with 1 increment by 1 nocache;

create table delivery(
  deliveryid integer default delivery_seq.nextval primary key,
  origin varchar2 (30),
  destiny varchar2 (40),
  departure timestamp,
  arrival timestamp,
  driverid integer,
  truckid varchar2 (30)
)

cluster CLUSTER_DELIVERY (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.

alter table delivery
add constraint fk_delivery_driver
foreign key (driverid)
references driver(driverid);


alter table delivery
add constraint fk_delivery_truck
foreign key (truckid)
references truck(truckid);



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

create sequence customer_seq start with 1 increment by 1 nocache;

create table customer(
  customerid integer default customer_seq.nextval primary key,
  name varchar2(40),
  address varchar2(40) default 'unknow',
  email varchar2(100)
);

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

--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,
deliveryid integer
)

cluster cluster_delivery(packageid);

alter table package
add constraint fk_package_customer
foreign key (customerid)
references customer(customerid);

alter table package
add constraint fk_package_delivery
foreign key (deliveryid)
references delivery(deliveryid);