Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt
You are on page 1/ 6
CREATE TABLE PROVINCIAS(
ID_PRO NVARCHAR(10) NOT NULL PRIMARY KEY,
NOM_PRO NVARCHAR(20) NOT NULL, REG_PRO NVARCHAR(20) NOT NULL CHECK(REG_PRO IN ('COSTA','SIERRA','ORIENTE')) )
CREATE TABLE CIUDADES(
ID_CIU NVARCHAR(10) NOT NULL PRIMARY KEY, NOM_CIU NVARCHAR(20) NOT NULL, CIU_PRO_PER NVARCHAR(10) NOT NULL, CONSTRAINT CIU_PRO_PER FOREIGN KEY (CIU_PRO_PER) REFERENCES PROVINCIAS(ID_PRO) )
CREATE TABLE CARGOS(
ID_CAR NVARCHAR(10) NOT NULL PRIMARY KEY, NOM_CAR NVARCHAR(20) NOT NULL CHECK(NOM_CAR IN ('ADMINISTRADOR','EMPLEADO')), SUE_CAR INT NOT NULL )
CREATE TABLE BODEGAS(
ID_BOD NVARCHAR(10) NOT NULL PRIMARY KEY, NOM_BOD NVARCHAR(20) NOT NULL, TEL_BOD NVARCHAR(20) NOT NULL, CIU_BOD_PER NVARCHAR(10) NOT NULL, CONSTRAINT CIU_BOD_PER FOREIGN KEY (CIU_BOD_PER) REFERENCES CIUDADES(ID_CIU) )
CREATE TABLE USUARIOS(
ID_USU NVARCHAR(10) NOT NULL PRIMARY KEY, NOM_USU NVARCHAR(10) NOT NULL, APE_USU NVARCHAR(10) NOT NULL, TEL_USU NVARCHAR(10) NOT NULL, CEL_USU NVARCHAR(10) NOT NULL, FOTO_USU IMAGE, COD_CIU_PER NVARCHAR(10) NOT NULL, COD_CAR_PER NVARCHAR(10) NOT NULL, COD_BOD_PER NVARCHAR(10) NOT NULL, COD_USU_PER NVARCHAR(10) NOT NULL, CONSTRAINT COD_CIU_PER FOREIGN KEY (COD_CIU_PER) REFERENCES CIUDADES(ID_CIU), CONSTRAINT COD_CAR_PER FOREIGN KEY (COD_CAR_PER) REFERENCES CARGOS(ID_CAR), CONSTRAINT COD_BOD_PER FOREIGN KEY (COD_BOD_PER) REFERENCES BODEGAS(ID_BOD), CONSTRAINT COD_USU_PER FOREIGN KEY (COD_USU_PER) REFERENCES USUARIOS(ID_USU) )
CREATE TABLE PROVEEDORES(
ID_PRO NVARCHAR(10) NOT NULL PRIMARY KEY, NOM_PRO NVARCHAR(15) NOT NULL, APE_PRO NVARCHAR(15) NOT NULL, TEL_PRO NVARCHAR(10) NOT NULL, CEL_PRO NVARCHAR(10) NOT NULL, PROV_CIU_PER NVARCHAR(10) NOT NULL, CONSTRAINT PROV_CIU_PER FOREIGN KEY (PROV_CIU_PER) REFERENCES CIUDADES(ID_CIU) )
CREATE TABLE PRODUCTOS_PROVEEDORES(
ID_PRO NVARCHAR(10) NOT NULL PRIMARY KEY, NOM_PRO NVARCHAR(15) NOT NULL, TIPO_PRO NVARCHAR(15) NOT NULL CHECK(TIPO_PRO IN ('VEGETAL','FRUTA')), CAN_QUITALES_PRO INT NOT NULL, PRE_PRO INT NOT NULL, ID_PRODUCTOR_PER NVARCHAR(10) NOT NULL, CONSTRAINT ID_PRODUCTOR_PER FOREIGN KEY (ID_PRODUCTOR_PER) REFERENCES PROVEEDORES(ID_PRO) )
CREATE TABLE CLIENTES(
ID_CLI NVARCHAR(10) NOT NULL PRIMARY KEY, NOM_CLI NVARCHAR(15) NOT NULL, APE_CLI NVARCHAR(15) NOT NULL, TEL_CLI NVARCHAR(10) NOT NULL, CEL_CLI NVARCHAR(10) NOT NULL, CLI_CIU_PER NVARCHAR(10) NOT NULL, CONSTRAINT CLI_CIU_PER FOREIGN KEY (CLI_CIU_PER) REFERENCES CIUDADES(ID_CIU) )
CREATE TABLE MAESTRO_COMPRAS(
NUM_COM NVARCHAR(10) NOT NULL PRIMARY KEY, FEC_COM DATE NOT NULL, PRO_COM_PER NVARCHAR(10) NOT NULL, CONSTRAINT PRO_COM_PER FOREIGN KEY (PRO_COM_PER) REFERENCES PROVEEDORES(ID_PRO) )
CREATE TABLE DETALLE_COMPRAS(
ID_COM INT NOT NULL PRIMARY KEY IDENTITY, PRO_COM NVARCHAR(10) NOT NULL, CAN_PRO_COM INT NOT NULL, NUM_COM_PER NVARCHAR(10) NOT NULL, CONSTRAINT NUM_COM_PER FOREIGN KEY (NUM_COM_PER) REFERENCES MAESTRO_COMPRAS(NUM_COM) )
CREATE TABLE MAESTRO_ENVIOS(
NUM_ENV NVARCHAR(10) NOT NULL PRIMARY KEY, FEC_ENV DATE NOT NULL, BOD_ENV NVARCHAR(10) NOT NULL, CONSTRAINT BOD_ENV FOREIGN KEY (BOD_ENV) REFERENCES BODEGAS(ID_BOD) )
--------REFERENCIA---------------' CREATE TABLE TOTAL_PRODUCTOS( ID_PRO_TOT NVARCHAR(10) NOT NULL PRIMARY KEY, NOM_TOT_PRO NVARCHAR(10) NOT NULL, CAN_TOT_PRO NVARCHAR(10) NOT NULL )
CREATE TABLE DETALLE_ENVIOS(
ID_ENV INT NOT NULL PRIMARY KEY IDENTITY, ID_PRO_ENV NVARCHAR(10) NOT NULL, CAN_PRO_ENV INT NOT NULL, NUM_EVI_PER NVARCHAR(10) NOT NULL, CONSTRAINT ID_PRO_ENV FOREIGN KEY (ID_PRO_ENV) REFERENCES TOTAL_PRODUCTOS(ID_PRO_TOT), CONSTRAINT NUM_EVI_PER FOREIGN KEY (NUM_EVI_PER) REFERENCES MAESTRO_ENVIOS(NUM_ENV) )
CREATE TABLE PRODUCTO_BODEGA(
ID_PRO_BOD NVARCHAR(10) NOT NULL PRIMARY KEY, NOM_PRO_BOD NVARCHAR(10) NOT NULL, CAN_PRO_BOD SMALLINT NOT NULL, BOD_PRO_PER NVARCHAR(10) NOT NULL, PRE_UNI_PRO_BOD INT NOT NULL, --CAMPO CALCULADO 20% CONSTRAINT BOD_PRO_PER FOREIGN KEY (BOD_PRO_PER) REFERENCES BODEGAS(ID_BOD) )
CREATE TABLE MAESTRO_VENTAS(
NUM_VEN NVARCHAR(10) NOT NULL PRIMARY KEY, CLI_VEN_PER NVARCHAR(10) NOT NULL, USU_VEN_PER NVARCHAR(10) NOT NULL, FEC_VEN DATE NOT NULL, CONSTRAINT CLI_VEN_PER FOREIGN KEY (CLI_VEN_PER) REFERENCES CLIENTES(ID_CLI), CONSTRAINT USU_VEN_PER FOREIGN KEY (USU_VEN_PER) REFERENCES USUARIOS(ID_USU) )
CREATE TABLE DETALLE_VENTAS(
ID_VEN INT NOT NULL PRIMARY KEY IDENTITY, PRO_VEN NVARCHAR(10) NOT NULL, CAN_VEN SMALLINT NOT NULL, NUM_VEN_PER NVARCHAR(10) NOT NULL, CONSTRAINT PRO_VEN FOREIGN KEY (PRO_VEN) REFERENCES PRODUCTO_BODEGA(ID_PRO_BOD), CONSTRAINT NUM_VEN_PER FOREIGN KEY (NUM_VEN_PER) REFERENCES MAESTRO_VENTAS(NUM_VEN) )
INSERT INTO PROVINCIAS(ID_PRO, NOM_PRO,
REG_PRO)VALUES('PR01','TUNGURAHUA','SIERRA'); INSERT INTO PROVINCIAS(ID_PRO, NOM_PRO, REG_PRO)VALUES('PR02','GUAYAS','COSTA'); INSERT INTO PROVINCIAS(ID_PRO, NOM_PRO, REG_PRO)VALUES('PR03','NAPO','ORIENTE');
INSERT INTO CIUDADES(ID_CIU, NOM_CIU, CIU_PRO_PER)VALUES('C01','AMBATO','PR01');
INSERT INTO CIUDADES(ID_CIU, NOM_CIU, CIU_PRO_PER)VALUES('C02','QUITO','PR01'); INSERT INTO CIUDADES(ID_CIU, NOM_CIU, CIU_PRO_PER)VALUES('C03','GUAYAQUIL','PR02'); INSERT INTO CIUDADES(ID_CIU, NOM_CIU, CIU_PRO_PER)VALUES('C04','PUYO','PR03');
INSERT INTO CARGOS(ID_CAR, NOM_CAR, SUE_CAR)VALUES('ADMIN','ADMINISTRADOR','1500');
INSERT INTO CARGOS(ID_CAR, NOM_CAR, SUE_CAR)VALUES('EMP','EMPLEADO','1000');
INSERT INTO BODEGAS(ID_BOD, NOM_BOD, TEL_BOD, CIU_BOD_PER)VALUES('BOD01','BODEGA-
NORTE','045746345','C02'); INSERT INTO BODEGAS(ID_BOD, NOM_BOD, TEL_BOD, CIU_BOD_PER)VALUES('BOD02','BODEGA- SUR','045746342','C02'); INSERT INTO BODEGAS(ID_BOD, NOM_BOD, TEL_BOD, CIU_BOD_PER)VALUES('BOD03','BODEGA- PUERTO','055111345','C03'); INSERT INTO BODEGAS(ID_BOD, NOM_BOD, TEL_BOD, CIU_BOD_PER)VALUES('BOD04','BODEGA- A01','035556345','C01'); INSERT INTO BODEGAS(ID_BOD, NOM_BOD, TEL_BOD, CIU_BOD_PER)VALUES('BOD05','BODEGA- P01','081246345','C04');
INSERT INTO USUARIOS(ID_USU, NOM_USU, APE_USU, TEL_USU, CEL_USU, FOTO_USU,
