Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
as
DROP TABLE IF EXISTS ENVIO_NACIONAL; DROP TABLE IF EXISTS NACIONAL; DROP TABLE IF EXISTS INTERNACIONAL; DROP TABLE IF EXISTS PAQUETE; DROP TABLE IF EXISTS CONDUCTOR_CAMION; DROP TABLE IF EXISTS CONDUCTOR; DROP TABLE IF EXISTS CAMION; DROP TABLE IF EXISTS RUTA; DROP TABLE IF EXISTS CIUDAD; DROP TABLE IF EXISTS AEROLINEA; CREATE TABLE AEROLINEA( id_Aerolinea integer, Nombre_aerolinea varchar(50) NOT NULL, CONSTRAINT id_Aerolinea_PK PRIMARY KEY (id_Aerolinea) ); CREATE TABLE CIUDAD( id_Ciudad integer, Nombre_Ciudad varchar(50) NOT NULL, CONSTRAINT id_Ciudad_PK PRIMARY KEY (id_Ciudad) ); CREATE TABLE RUTA( id_Ruta integer, Nombre_Ruta varchar(50) NOT NULL, CONSTRAINT id_Ruta_PK PRIMARY KEY (id_Ruta) ); CREATE TABLE CAMION ( Placa varchar(8) NOT NULL, Ciudad_Resguardo varchar(50) NOT NULL, Carga_Maxima float NOT NULL, Carga_Minima float NOT NULL, CONSTRAINT Placa_PK PRIMARY KEY (Placa) ); CREATE TABLE CONDUCTOR ( id_Conductor integer, RFC varchar(15) NOT NULL, id_Ruta integer NOT NULL, Direccion varchar(50) NOT NULL, Nombre_Conductor varchar(50) NOT NULL, CONSTRAINT idConductor_PK PRIMARY KEY (id_Conductor) ); CREATE TABLE CONDUCTOR_CAMION ( Placa varchar(8) NOT NULL, id_Conductor integer NOT NULL, Fecha DATE NOT NULL, CONSTRAINT UK_Placa_idConductor_Fecha UNIQUE(Placa,id_Conductor,Fecha) ); CREATE TABLE PAQUETE ( id_Paquete integer, Direccion varchar(100) NOT NULL, Peso_kg float NOT NULL, Destinatorio varchar(50) NOT NULL, Tipo_paquete varchar(1) NOT NULL, CONSTRAINT CHK_Tipo_paquete CHECK (Tipo_paquete LIKE ('I') OR Tipo_paquete LIKE ('N')), CONSTRAINT id_Paquete_PK PRIMARY KEY (id_Paquete) ); CREATE TABLE INTERNACIONAL ( id_Paquete_Internacional varchar(5) NOT NULL, id_Paquete integer NOT NULL, id_Ciudad integer NOT NULL, id_Aerolinea integer NOT NULL, Fecha_Entrega DATE NOT NULL, CONSTRAINT CHK_id_Paquete_Internacional CHECK (id_Paquete_Internacional LIKE '_____'), CONSTRAINT id_Paquete_Internacional_PK PRIMARY KEY (id_Paquete_Internacional) ); CREATE TABLE NACIONAL ( id_Paquete_Nacional varchar(5) NOT NULL, id_Paquete integer NOT NULL, id_Conductor integer, RFC varchar(15), id_Ruta integer, Ciudad_Destino varchar(50) NOT NULL, CONSTRAINT CHK_id_Paquete_Nacional CHECK (id_Paquete_Nacional LIKE '_____'), CONSTRAINT id_Paquete_Nacional_PK PRIMARY KEY (id_Paquete_Nacional) ); CREATE TABLE ENVIO_NACIONAL ( id_Paquete_Nacional varchar(5) NOT NULL, Ciudad_Destino varchar(50) NOT NULL, Num_Paquetes integer NULL DEFAULT 0, CONSTRAINT id_Paquete_E_PK PRIMARY KEY (id_Paquete_Nacional) ); -- Llaves Foraneas para la tabla Conductor Camión ALTER TABLE CONDUCTOR_CAMION ADD CONSTRAINT Placa_FK FOREIGN KEY (Placa) REFERENCES CAMION ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CONDUCTOR_CAMION ADD CONSTRAINT idConductor_C_FK FOREIGN KEY (id_Conductor) REFERENCES CONDUCTOR ON UPDATE CASCADE ON DELETE CASCADE; -- Llaves Forenaeas para la tabla Internacional ALTER TABLE INTERNACIONAL ADD CONSTRAINT id_Paquete_I_FK FOREIGN KEY (id_Paquete) REFERENCES PAQUETE ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE INTERNACIONAL ADD CONSTRAINT id_Ciudad_FK FOREIGN KEY (id_Ciudad) REFERENCES CIUDAD ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE INTERNACIONAL ADD CONSTRAINT id_Aerolinea_FK FOREIGN KEY (id_Aerolinea) REFERENCES AEROLINEA ON UPDATE CASCADE ON DELETE CASCADE; -- Llaves Foreneas para la tabla Nacional ALTER TABLE NACIONAL ADD CONSTRAINT id_Paquete_N_FK FOREIGN KEY (id_Paquete) REFERENCES PAQUETE ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE NACIONAL ADD CONSTRAINT id_Conductor_N_FK FOREIGN KEY (id_Conductor) REFERENCES CONDUCTOR ON UPDATE CASCADE ON DELETE CASCADE; -- Llaves Foreneas para envío Nacional /*ALTER TABLE ENVIO_NACIONAL ADD CONSTRAINT id_Paquete_Nacional_E_FK FOREIGN KEY (id_Paquete_Nacional) REFERENCES NACIONAL ON UPDATE CASCADE ON DELETE CASCADE;*/ -- Llaves Foraneas para la tabla Conductor ALTER TABLE CONDUCTOR ADD CONSTRAINT id_Ruta_C_FK FOREIGN KEY (id_Ruta) REFERENCES RUTA; SELECT * FROM CONDUCTOR_CAMION
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
Movie Rating SQL Analysis Project
SQL Movie-Rating Query Exercises
hell
PostgreSQL: Inner Join
statistics
Assignment 1(SET A)
Assignment 1(SET A)
Game Play Analysis (511,512,534,550)
Select rows with no intersection on join table and check by date
Many to many
Please log in to post a comment.