Ventascib

if db_id('VENTASCIB') is not null

begin

use master

drop database VENTASCIB

end

go

CREATE DATABASE VENTASCIB

GO

USE VENTASCIB

go

SET DATEFORMAT YMD

CREATE TABLE TB_DISTRITO(

COD_DIS CHAR(5)NOT NULL PRIMARY KEY,

NOM_DIS VARCHAR(50)

)

CREATE TABLE TB_VENDEDOR(

COD_VEN CHAR(3)NOT NULL PRIMARY KEY ,

NOM_VEN VARCHAR(20)NOT NULL,

APE_VEN VARCHAR(20)NOT NULL,

SUELDO_VEN MONEY NOT NULL,

FEC_ING DATE NOT NULL,

TIP_VEN VARCHAR(10)NOT NULL,

COD_DIS CHAR(5)NOT NULL REFERENCES TB_DISTRITO

)

CREATE TABLE TB_CLIENTE(

COD_CLI CHAR (5)NOT NULL PRIMARY KEY,

RAZ_SOC_CLI CHAR(30)NOT NULL,

DIR_CLI VARCHAR(100)NOT NULL,

TLF_CLI CHAR(9)NOT NULL,

RUC_CLI CHAR(11) NULL,

COD_DIS CHAR(5)NOT NULL REFERENCES TB_DISTRITO,

FEC_REG DATE NOT NULL,

TIP_CLI VARCHAR(10) NOT NULL,

CONTACTO VARCHAR(30) NOT NULL

)

CREATE TABLE TB_PROVEEDOR(

COD_PRV CHAR(5)NOT NULL PRIMARY KEY,

RAZ_SOC_PRV VARCHAR(80)NOT NULL,

DIR_PRV VARCHAR(100)NOT NULL,

TEL_PRV CHAR(15) NULL,

COD_DIS CHAR(5)NOT NULL REFERENCES TB_DISTRITO,

REP_VEN VARCHAR(80) NOT NULL

)

CREATE TABLE TB_FACTURA(

NUM_FAC VARCHAR(12)NOT NULL PRIMARY KEY,

FEC_FAC DATE NOT NULL,

COD_CLI CHAR(5)NOT NULL REFERENCES TB_CLIENTE,

FEC_CAN DATE NOT NULL,

EST_FAC VARCHAR(10)NOT NULL,

COD_VEN CHAR(3)NOT NULL REFERENCES TB_VENDEDOR,

PORC_IGV DECIMAL NOT NULL

)

CREATE TABLE TB_ORDEN_COMPRA(

NUM_OCO CHAR(5)NOT NULL PRIMARY KEY,

FEC_OCO DATE NOT NULL,

COD_PRV CHAR(5)NOT NULL REFERENCES TB_PROVEEDOR,

FEC_ATE DATE NOT NULL,

EST_OCO CHAR(1)NOT NULL

)

CREATE TABLE TB_PRODUCTO(

COD_PRO CHAR(5)NOT NULL PRIMARY KEY,

DES_PRO VARCHAR(50)NOT NULL,

PRE_PRO MONEY NOT NULL,

STK_ACT INT NOT NULL,

STK_MIN INT NOT NULL,

UNI_MED VARCHAR(30)NOT NULL,

LIN_PRO VARCHAR(30)NOT NULL,

IMPORTADO VARCHAR(10)NOT NULL

)

CREATE TABLE TB_DETALLE_FACTURA(

NUM_FAC VARCHAR(12)NOT NULL REFERENCES TB_FACTURA,

COD_PRO CHAR(5)NOT NULL REFERENCES TB_PRODUCTO,

CAN_VEN INT NOT NULL,

PRE_VEN MONEY NOT NULL

PRIMARY KEY (NUM_FAC,COD_PRO)

)

CREATE TABLE TB_DETALLE_COMPRA(

NUM_OCO CHAR(5)NOT NULL REFERENCES TB_ORDEN_COMPRA,

COD_PRO CHAR(5)NOT NULL REFERENCES TB_PRODUCTO,

CAN_PED INT NOT NULL

PRIMARY KEY (NUM_OCO,COD_PRO)

)

CREATE TABLE TB_ABASTECIMIENTO(

COD_PRV CHAR(5)NOT NULL REFERENCES TB_PROVEEDOR,

COD_PRO CHAR(5)NOT NULL REFERENCES TB_PRODUCTO,

PRE_ABA MONEY NOT NULL

PRIMARY KEY (COD_PRV,COD_PRO)

)

GO

INSERT INTO TB_DISTRITO VALUES('D01','Surco')

INSERT INTO TB_DISTRITO VALUES('D02','Jesús María')

INSERT INTO TB_DISTRITO VALUES('D03','San Isidro')

INSERT INTO TB_DISTRITO VALUES('D04','La Molina')

INSERT INTO TB_DISTRITO VALUES('D05','San Miguel')

INSERT INTO TB_DISTRITO VALUES('D06','Miraflores')

INSERT INTO TB_DISTRITO VALUES('D07','Barranco')

INSERT INTO TB_DISTRITO VALUES('D08','Chorrillos')

INSERT INTO TB_DISTRITO VALUES('D09','San Borja')

INSERT INTO TB_DISTRITO VALUES('D10','Lince')

INSERT INTO TB_DISTRITO VALUES('D11','Breña')

INSERT INTO TB_DISTRITO VALUES('D12','Magdalena')

INSERT INTO TB_DISTRITO VALUES('D13','Rimac')

INSERT INTO TB_DISTRITO VALUES('D14','Surquillo')

INSERT INTO TB_DISTRITO VALUES('D15','Pueblo Libre')

INSERT INTO TB_DISTRITO VALUES('D16','Bellavista')

INSERT INTO TB_DISTRITO VALUES('D17','Callao')

INSERT INTO TB_DISTRITO VALUES('D18','San Martin de Porres')

INSERT INTO TB_DISTRITO VALUES('D19','Santa Anita')

INSERT INTO TB_DISTRITO VALUES('D20','Los Olivos')

INSERT INTO TB_DISTRITO VALUES('D21','Independencia')

INSERT INTO TB_DISTRITO VALUES('D22','Lima - Cercado')

INSERT INTO TB_DISTRITO VALUES('D24','San Luis')

INSERT INTO TB_DISTRITO VALUES('D25','El Agustino')

INSERT INTO TB_DISTRITO VALUES('D26','San Juan de Lurigancho')

INSERT INTO TB_DISTRITO VALUES('D27','Ate - Vitarte')

INSERT INTO TB_DISTRITO VALUES('D28','San Juan de Miraflores')

INSERT INTO TB_DISTRITO VALUES('D29','Carmen de la Legua')

INSERT INTO TB_DISTRITO VALUES('D30','Comas')

INSERT INTO TB_DISTRITO VALUES('D31','Villa María del Triunfo')

INSERT INTO TB_DISTRITO VALUES('D32','Villa el Salvador')

INSERT INTO TB_DISTRITO VALUES('D33','La Perla')

INSERT INTO TB_DISTRITO VALUES('D34','Ventanilla')

INSERT INTO TB_DISTRITO VALUES('D35','Puente Piedra')

INSERT INTO TB_DISTRITO VALUES('D36','Carabayllo')

INSERT INTO TB_DISTRITO VALUES('D37','Santa Maria')

INSERT INTO TB_DISTRITO VALUES('D38','San Guchito')

INSERT INTO TB_DISTRITO VALUES('D45','La Punta')

SELECT* FROM TB_DISTRITO

go

INSERT INTO TB_VENDEDOR VALUES('V01','JUANA','ALVA',1000,'07/12/2003','1','D08')

INSERT INTO TB_VENDEDOR VALUES('V02','JUAN','SOTO',1200,'10/03/2002','2','D03')

INSERT INTO TB_VENDEDOR VALUES('V03','CARLOS','AREVALO',1500,'02/10/1992','2','D09')

INSERT INTO TB_VENDEDOR VALUES('V04','CESAR','OJEDA',850,'08/11/1999','1','D01')

INSERT INTO TB_VENDEDOR VALUES('V05','JULIO','VEGA',1500,'11/05/1997','1','D01')

...