/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     16/07/2015 9:53:06                           */
/*==============================================================*/


drop table if exists RELATIONSHIP_14;

drop table if exists RELATIONSHIP_15;

drop table if exists RELATIONSHIP_18;

drop table if exists adjudicacion;

drop table if exists asignacion;

drop table if exists ciudad;

drop table if exists costo;

drop table if exists empresa;

drop table if exists licencia;

drop table if exists licitacion;

drop table if exists pais;

drop table if exists pasajero;

drop table if exists propietariovehiculo;

drop table if exists provincia;

drop table if exists resultado;

drop table if exists ruta;

drop table if exists transportista;

drop table if exists usuario;

drop table if exists vehiculo;

drop table if exists ventas;

/*==============================================================*/
/* Table: RELATIONSHIP_14                                       */
/*==============================================================*/
create table RELATIONSHIP_14
(
   serial_veh           int(11) not null,
   serial_rut           int(11) not null,
   fechainicio_horu     date,
   fechafin_horu        date,
   horainicio_horu      time,
   horafin_horu         time,
   observacion_horu     varchar(50),
   primary key (serial_veh, serial_rut)
);

/*==============================================================*/
/* Table: RELATIONSHIP_15                                       */
/*==============================================================*/
create table RELATIONSHIP_15
(
   serial_rut           int(11) not null,
   serial_pas           int(11) not null,
   fecharegistro        date,
   contactoemergencia   varchar(50),
   telefono1emergencia  varchar(10),
   telefono2emergencia  varchar(10),
   observacion          varchar(50),
   primary key (serial_rut, serial_pas)
);

/*==============================================================*/
/* Table: RELATIONSHIP_18                                       */
/*==============================================================*/
create table RELATIONSHIP_18
(
   serial_tra           int(11) not null,
   serial_veh           int(11) not null,
   horainicio_hove      time,
   horafin_hove         time,
   fechainicio_hove     date,
   fechafin_hove        date,
   observacion_hove     varchar(50),
   primary key (serial_tra, serial_veh)
);

/*==============================================================*/
/* Table: adjudicacion                                          */
/*==============================================================*/
create table adjudicacion
(
   serial_adj           int not null,
   serial_lic           int(11) not null,
   serial_cos           int not null,
   porcentaje_adj       int not null,
   total_adj            double not null,
   tipo_adj             varchar(25) not null,
   observacion_adj      varchar(50) not null,
   primary key (serial_adj)
);

/*==============================================================*/
/* Table: asignacion                                            */
/*==============================================================*/
create table asignacion
(
   serial_lic           int(11) not null,
   serial_asi           int not null,
   tipo_asi             varchar(50) not null,
   capacidad_asi        int not null,
   costoporkm_asi       double not null,
   km_asi               double not null,
   primary key (serial_lic, serial_asi)
);

/*==============================================================*/
/* Table: ciudad                                                */
/*==============================================================*/
create table ciudad
(
   serial_ciu           int not null,
   serial_prov          int not null,
   nombre_ciu           varchar(50) not null,
   primary key (serial_ciu)
);

/*==============================================================*/
/* Table: costo                                                 */
/*==============================================================*/
create table costo
(
   serial_lic           int(11) not null,
   serial_cos           int not null,
   sectorinicial_cos    varchar(50) not null,
   sectorfinal_cos      varchar(50) not null,
   costo_cos            double not null,
   horario_cos          varchar(50) not null,
   descripcion_cos      varchar(50) default NULL,
   primary key (serial_lic, serial_cos)
);

/*==============================================================*/
/* Table: empresa                                               */
/*==============================================================*/
create table empresa
(
   serial_emp           int(11) not null auto_increment,
   razonsocial_emp      varchar(50) not null,
   cedularuc_emp        varchar(13) not null,
   actividad_emp        varchar(70) not null,
   direccion_emp        varchar(50) not null,
   telefono_emp         varchar(10) not null,
   personeria_emp       varchar(10) not null,
   primary key (serial_emp),
   unique key AK_CEDRUC_UNICA (cedularuc_emp)
);

/*==============================================================*/
/* Table: licencia                                              */
/*==============================================================*/
create table licencia
(
   serial_lice          int not null,
   nombre_lice          varchar(10) not null,
   descripcion_lice     varchar(150),
   primary key (serial_lice)
);

/*==============================================================*/
/* Table: licitacion                                            */
/*==============================================================*/
create table licitacion
(
   serial_lic           int(11) not null auto_increment,
   serial_emp           int(11) not null,
   nombre_lic           varchar(50) not null,
   valor_lic            float not null,
   fecini_lic           date not null,
   fecfin_lic           date not null,
   dias_lic             int not null,
   numerovehiculos_lic  int not null,
   estado_lic           varchar(25) not null,
   primary key (serial_lic)
);

/*==============================================================*/
/* Table: pais                                                  */
/*==============================================================*/
create table pais
(
   serial_pai           int not null,
   nombre_pai           varchar(25) not null,
   primary key (serial_pai)
);

/*==============================================================*/
/* Table: pasajero                                              */
/*==============================================================*/
create table pasajero
(
   serial_pas           int(11) not null auto_increment,
   apellido_pas         varchar(50) not null,
   nombre_pas           varchar(50) not null,
   cedrulauc_pas        varchar(13) not null,
   direccion_pas        varchar(50) not null,
   telefono_pas         varchar(10) not null,
   celular1_pas         varchar(10) default NULL,
   celular2_pas         varchar(10),
   primary key (serial_pas),
   unique key AK_CEDRUT_UNICO (cedrulauc_pas)
);

/*==============================================================*/
/* Table: propietariovehiculo                                   */
/*==============================================================*/
create table propietariovehiculo
(
   serial_pro           int not null,
   apellido_pro         varchar(50) not null,
   nombre_pro           varchar(50) not null,
   cedula_pro           varchar(10) not null,
   fechanacimiento_pro  date not null,
   direccion_pro        varchar(50) not null,
   celular1_pro         varchar(10),
   celular2_pro         varchar(10),
   telefono_pro         varchar(10),
   primary key (serial_pro)
);

/*==============================================================*/
/* Table: provincia                                             */
/*==============================================================*/
create table provincia
(
   serial_prov          int not null,
   serial_pai           int not null,
   nombre_prov          varchar(50) not null,
   primary key (serial_prov)
);

/*==============================================================*/
/* Table: resultado                                             */
/*==============================================================*/
create table resultado
(
   serial_res           int not null,
   serial_adj           int not null,
   retencion_res        int not null,
   retenciongasto_res   int not null,
   valorfinal_res       double not null,
   excedente_res        double not null,
   observacion_res      varchar(50),
   primary key (serial_res)
);

/*==============================================================*/
/* Table: ruta                                                  */
/*==============================================================*/
create table ruta
(
   serial_rut           int(11) not null auto_increment,
   serial_emp           int(11) not null,
   nombreruta_rut       varchar(25) not null,
   sectorinicial_rut    varchar(50) not null,
   puntoinicial_rut     varchar(50) not null,
   puntosintermedios_rut varchar(150) not null,
   puntofinal_rut       varchar(50) not null,
   sectorfinal_rut      varchar(50) not null,
   distancia_rut        decimal(16,2) not null,
   primary key (serial_rut)
);

/*==============================================================*/
/* Table: transportista                                         */
/*==============================================================*/
create table transportista
(
   serial_tra           int(11) not null auto_increment,
   serial_lice          int not null,
   serial_ciu           int not null,
   nombre_tra           varchar(50) not null,
   apellido_tra         varchar(50) not null,
   cedula_tra           varchar(10) not null,
   fnacimiento_tra      date not null,
   sexo_tra             varchar(10) not null,
   estadocivil_tra      varchar(15) not null,
   direccion_tra        varchar(60) not null,
   celular_tra          varchar(10) not null default NULL,
   celular2_tra         varchar(10),
   celular3_tra         varchar(10),
   telefono_tra         varchar(10) default NULL,
   primary key (serial_tra),
   unique key AK_CEDULA_UNICA (cedula_tra)
);

/*==============================================================*/
/* Table: usuario                                               */
/*==============================================================*/
create table usuario
(
   serial_usu           int(11) not null auto_increment,
   usuario_usu          varchar(20) not null,
   clave_usu            varchar(50) not null,
   estado_usu           varchar(10) not null default 'ACTIVO',
   primary key (serial_usu)
);

/*==============================================================*/
/* Table: vehiculo                                              */
/*==============================================================*/
create table vehiculo
(
   serial_veh           int(11) not null auto_increment,
   serial_pro           int not null,
   placa_veh            varchar(7) not null,
   tipo_veh             varchar(25) not null,
   aniofab_veh          int not null,
   pasajeros_veh        int not null,
   fmatricula_veh       date not null,
   fcaducidadmatricula_veh date not null,
   fultimarevision_veh  date not null,
   marca_veh            varchar(25) not null,
   modelo_veh           varchar(25) not null,
   primary key (serial_veh),
   unique key AK_PLACA_UNICA (placa_veh)
);

/*==============================================================*/
/* Table: ventas                                                */
/*==============================================================*/
create table ventas
(
   serial_ven           int(11) not null auto_increment,
   serial_usu           int(11) not null,
   serial_emp           int(11) not null,
   numero_ven           bigint not null,
   fecha_ven            date not null,
   estado_ven           varchar(25) not null,
   valortotal_ven       double,
   descuento_ven        double,
   iva_ven              double,
   primary key (serial_ven),
   unique key AK_NUMERO_UNICO (numero_ven)
);

alter table RELATIONSHIP_14 add constraint FK_RELATIONSHIP_14 foreign key (serial_rut)
      references ruta (serial_rut) on delete restrict on update restrict;

alter table RELATIONSHIP_14 add constraint FK_RELATIONSHIP_14 foreign key (serial_veh)
      references vehiculo (serial_veh) on delete restrict on update restrict;

alter table RELATIONSHIP_15 add constraint FK_RELATIONSHIP_15 foreign key (serial_pas)
      references pasajero (serial_pas) on delete restrict on update restrict;

alter table RELATIONSHIP_15 add constraint FK_RELATIONSHIP_15 foreign key (serial_rut)
      references ruta (serial_rut) on delete restrict on update restrict;

alter table RELATIONSHIP_18 add constraint FK_RELATIONSHIP_18 foreign key (serial_tra)
      references transportista (serial_tra) on delete restrict on update restrict;

alter table RELATIONSHIP_18 add constraint FK_RELATIONSHIP_18 foreign key (serial_veh)
      references vehiculo (serial_veh) on delete restrict on update restrict;

alter table adjudicacion add constraint FK_RELATIONSHIP_17 foreign key (serial_lic, serial_cos)
      references costo (serial_lic, serial_cos) on delete restrict on update restrict;

alter table asignacion add constraint FK_INHERITANCE_1 foreign key (serial_lic)
      references licitacion (serial_lic) on delete restrict on update restrict;

alter table ciudad add constraint FK_PROVINCIA_TIENE_CIUDADE foreign key (serial_prov)
      references provincia (serial_prov) on delete restrict on update restrict;

alter table costo add constraint FK_INHERITANCE_1 foreign key (serial_lic)
      references licitacion (serial_lic) on delete restrict on update restrict;

alter table licitacion add constraint FK_RELATIONSHIP_20 foreign key (serial_emp)
      references empresa (serial_emp) on delete restrict on update restrict;

alter table provincia add constraint FK_PAIS_TIENE_PROVINCIA foreign key (serial_pai)
      references pais (serial_pai) on delete restrict on update restrict;

alter table resultado add constraint FK_RELATIONSHIP_12 foreign key (serial_adj)
      references adjudicacion (serial_adj) on delete restrict on update restrict;

alter table ruta add constraint FK_RELATIONSHIP_13 foreign key (serial_emp)
      references empresa (serial_emp) on delete restrict on update restrict;

alter table transportista add constraint FK_RELATIONSHIP_16 foreign key (serial_lice)
      references licencia (serial_lice) on delete restrict on update restrict;

alter table transportista add constraint FK_RELATIONSHIP_19 foreign key (serial_ciu)
      references ciudad (serial_ciu) on delete restrict on update restrict;

alter table vehiculo add constraint FK_RELATIONSHIP_22 foreign key (serial_pro)
      references propietariovehiculo (serial_pro) on delete restrict on update restrict;

alter table ventas add constraint FK_RELATIONSHIP_21 foreign key (serial_usu)
      references usuario (serial_usu) on delete restrict on update restrict;

alter table ventas add constraint FK_RELATIONSHIP_7 foreign key (serial_emp)
      references empresa (serial_emp) on delete restrict on update restrict;

