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')

...