SCRIP DE LA BASE DE DATOS

SCRIP DE LA BASE DE DATOS
drop database office_people;
create database office_people;
use office_people;

create table administradores (
tipodoc varchar (20) not null,
numdoc varchar (12) not null primary key,
nombres varchar (30) not null,
apellidos varchar (40) not null,
direccion varchar (70) not null,
telefono varchar (30) not null,
login varchar (20) not null,
password varchar (30) not null);

insert into administradores values ('c.c.','92031611','Luis Alberto','Sierra Rodriguez','Cl12B22-15','3114253465','92031611','serrasazuis');


create table articulos (
codigo int (25) not null primary key,
nombre varchar (200) not null,
marca varchar (50) not null,
cantidad float not null,
valorcosto int (8) not null,
menudeo varchar (15) not null);


insert into articulos values ('0025','lapicero azul','BEIFA','38','350','no');

insert into articulos values ('0023','lapicero azul','FABER CASTELL','38','350','no');

insert into articulos values ('0029','lapicero ROJO','KILOMETRICO','38','350','no');

insert into articulos values ('0013','lapicero ROJO','FABER CASTELL','38','350','no');

insert into articulos values ('0012','lapicero NEGRO','KILOMETRICO','38','350','no');

insert into articulos values ('0011','lapicero NEGRO','FABER CASTELL','38','350','no');

insert into articulos values ('0010','memoria USB 1GB','Sony','15','12000','no');

insert into articulos values ('0045','cuaderno rayado 100hojas','Norma','14','650','no');

insert into articulos values ('0040','block sin raya','El cid','5','1150','si');

insert into articulos values ('0041','block rayado','Norma','15','750','si');

insert into articulos values ('0042','cd','Princo','6','450','no');

insert into articulos values ('920012','cartulina blanca','Papelcard','6','1200','si');

insert into Articulos values('370125','cuaderno pasta dura','Eco','12','1500','no');

insert into Articulos values('143592','cuaderno empastado','EL CID','15','2500','no');

insert into Articulos values('133877','Marcador negro 0.5mm','Bic','7','750','no');

insert into Articulos values('466125','Lapicero azul','Kilometrico','15','1000','no');

insert into Articulos values('126090','Lapicero azul','Faber-Castell','5','750','no');

insert into Articulos values('107757','Lapicero negro','Faber-Castell','25','750','no');

insert into Articulos values('109367','Lapicero rojo','Bic','10','1000','no');

insert into Articulos values('122000','Lapiz negro','Mongol#2','9','225','no');

insert into Articulos values('145899','Memoria USB 2GB','SanDisk','10','15000','no');

insert into Articulos values('125067','Memoria USB 4GB','imation','8','45900','no');

insert into Articulos values('136857','Cinta magica de 18mm * 33mm con dispensador','Studmark','6','2200','no');

insert into Articulos values('132544','Blocks sin raya','Norma','12','1200','no');

insert into Articulos values('23209','Cartulina bristol negra 1/4*5unidades','INPAPELES','10','2100','si');

insert into Articulos values('107326','Pincel redondo 00','Gioto','7','2000','no');

insert into Articulos values('101175','Pincel redondo para delinear ','Gioto','4','950','no');

insert into Articulos values('51165','Cartulina azul','PAPELCARD','8','2000','si');

insert into Articulos values('114362','Borrador nata','Orion','20','200','no');

insert into Articulos values('86381','sacapunta metalica','Eco','10','200','no');




create table vendedores (
tipodoc varchar (20) not null,
numdoc varchar (12) not null primary key,
nombres varchar (30) not null,
apellidos varchar (40) not null,
direccion varchar (70) not null,
telefono varchar (15) not null,
login varchar (20) not null,
password varchar (70) not null);


insert into vendedores values ('c.c.','92456789','carlos andres','sierra hernandez','cll 4 n°9-56 barrio el palmar corozal','3114253465','xxx','sena');

create table proveedores (
nit varchar (15) not null primary key,
nombre varchar (50) not null,
direccion varchar (70) not null,
telefono varchar (10) not null);

insert into proveedores values ('9031100','Papeleria santafe','kra 14 n°8-24','3104256782');

insert into proveedores values ('64546550-4','Papeleria Tauro','kra 14 n°9-26','3129807654');

insert into proveedores values ('23566550-4','Distri Center','kra 14 n°9-27','3134256782');

insert into proveedores values ('64989550-4','papeleria santafe','kra 14 n°7-14','3164256782');


create table deudores (
tipodoc varchar (20) not null,
numdoc varchar (12) not null primary key,
nombres varchar (30) not null,
apellidos varchar (40) not null,
direccion varchar (70) not null,
telefono varchar (10) not null,
saldo int (8) not null);


insert into deudores values ('C.C.','23856766','yessenia','estrada','cll 13 n° 8-56','2805643',0);

insert into deudores values ('C.E.','45432543','fernando','gutierrez','cll 34 n° 7-35','3114279683',10000);

insert into deudores values ('C.C.','90897765','jair','castro','cll 78 n° 4-34','312434786',5000);

create table encargado_administrador(
tipo varchar (30) not null,
numdoc varchar (12) not null primary key);


create table encargado_vendedor(
tipo varchar (30) not null,
numdoc varchar (12) not null primary key);

create table encargados(
tipo varchar (30) not null,
numdoc varchar (12) not null primary key);


insert into encargados values('administrador','92031611');
insert into encargados values('vendedor','92456789');


create table ventas_creditos(
codigo varchar (10) not null primary key,
fecha date not null,
coddeudor varchar (12) not null,
plazo date not null,
codencargado varchar (12) not null,
valortotal float (8) not null,
estado varchar (20) not null,
foreign key(coddeudor) references deudores(numdoc) on update cascade on delete cascade,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);


insert into ventas_creditos values('21101','2010-01-05','90897765','2010-02-05','92456789','10000','credito');

insert into ventas_creditos values('21102','2010-01-10','23856766','2010-02-10','92456789','42700','credito');

insert into ventas_creditos values('21103','2010-01-20','45432543','2010-02-20','92456789','53000','credito');

create table detalle_ventacredito(
codarticulo int(25) not null,
cantidad float (3) not null,
vunitario int (8) not null,
codventacred varchar (10) not null,
foreign key(codarticulo) references articulos(codigo) on update cascade on delete cascade,
foreign key(codventacred) references ventas_creditos(codigo) on update cascade on delete cascade);

insert into detalle_ventacredito values('0012','2','750','21101');

insert into detalle_ventacredito values('86381','2','300','21101');

insert into detalle_ventacredito values('920012','0.50','2400','21101');

insert into detalle_ventacredito values('370125','3','2000','21101');

insert into detalle_ventacredito values('0029','1','700','21101');

insert into detalle_ventacredito values('45','5','1300','21102');

insert into detalle_ventacredito values('10','1','20000','21102');

insert into detalle_ventacredito values('132544','1','2400','21102');

insert into detalle_ventacredito values('23','2','1000','21102');

insert into detalle_ventacredito values('114362','1','300','21102');

insert into detalle_ventacredito values('136857','1','3500','21102');

insert into detalle_ventacredito values('42','3','1000','21102');

insert into detalle_ventacredito values('143592','1','5000','21102');

insert into detalle_ventacredito values('125067','1','50000','21103');

insert into detalle_ventacredito values('107326','1','2500','21103');

insert into detalle_ventacredito values('23','1','1000','21103');

insert into detalle_ventacredito values('122000','1','500','21103');

create table compras (
codigo varchar (10) not null primary key,
fecha date not null,
codproveedor varchar (15) not null,
valortotal int (8) not null,
foreign key(codproveedor) references proveedores(nit) on update cascade on delete cascade);


insert into compras values ('31400','2010-01-10','23566550-4','120000');



create table detalle_compra(
codarticulo int (25) not null,
cantidad int (3) not null,
vunitario int (7) not null,
codcompra varchar (7) not null,
foreign key(codarticulo) references articulos(codigo) on update cascade on delete cascade,
foreign key(codcompra) references compras(codigo) on update cascade on delete cascade);


insert into detalle_compra values ('0010','10','12000','31400');

insert into detalle_compra values ('51165','5','1200','31400');


create table venta_contados(
codigo varchar (10) not null primary key,
fecha date not null,
codencargado varchar (12) not null,
descuento float (3) not null,
valortotal int (8) not null,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);

insert into venta_contados values('22200','2010-01-26','92456789','0','20700');

insert into venta_contados values('22201','2010-01-26','92456789','0','1300');

insert into venta_contados values('22202','2010-01-26','92456789','0','2000');

insert into venta_contados values('22203','2010-01-26','92031611','5','115235');

insert into venta_contados values('22204','2010-01-26','92031611','0','300');

insert into venta_contados values('22205','2010-01-26','92031611','0','3900');

insert into venta_contados values('22206','2010-01-26','92031611','0','1000');

insert into venta_contados values('22207','2010-01-26','92456789','0','22000');

insert into venta_contados values('22208','2010-01-26','92456789','0','300');

insert into venta_contados values('22209','2010-01-26','92456789','0','600');

create table detalle_venta(
codarticulo int(25) not null,
cantidad float (4) not null,
vunitario int (8) not null,
codventa varchar (10) not null,
foreign key(codarticulo) references articulos (codigo) on update cascade on delete cascade,
foreign key(codventa) references venta_contados(codigo) on update cascade on delete cascade);

insert into detalle_venta values('0010','1','20000','22200');

insert into detalle_venta values('12','1','700','22200');

insert into detalle_venta values('0045','1','1300','22201');

insert into detalle_venta values('51165','0.5','1200','22202');

insert into detalle_venta values('122000','1','500','22202');

insert into detalle_venta values('114362','1','300','22202');

insert into detalle_venta values('0010','2','20000','22203');

insert into detalle_venta values('0025','1','1000','22203');

insert into detalle_venta values('133877','1','1500','22203');

insert into detalle_venta values('143592','4','5000','22203');

insert into detalle_venta values('125067','1','50000','22203');

insert into detalle_venta values('51165','1','2400','22203');

insert into detalle_venta values('132544','1','2400','22203');

insert into detalle_venta values('370125','2','2000','22203');

insert into detalle_venta values('86381','1','300','22204');

insert into detalle_venta values('51165','1','2400','22205');

insert into detalle_venta values('133877','1','1500','22205');

insert into detalle_venta values('23','1','1000','22206');

insert into detalle_venta values('10','1','20000','22207');

insert into detalle_venta values('0011','2','1000','22207');

insert into detalle_venta values('114362','1','300','22208');

insert into detalle_venta values('114362','2','300','22209');

create table donacion_articulos(
codigo varchar (10) not null primary key,
fecha date not null,
beneficiario varchar (50) not null,
codencargado varchar (12) not null,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);

insert into donacion_articulos values ('7581','2009-10-11','jose ramires','92031611');

create table detalle_donacionarticulos(
codarticulo int (25) not null,
cantidad float (3) not null,
coddonacion varchar (10) not null,
foreign key(codarticulo) references articulos(codigo) on update cascade on delete cascade,
foreign key(coddonacion) references donacion_articulos(codigo) on update cascade on delete cascade);

insert into detalle_donacionarticulos values('466125','1','7581');

create table danos(
codigo varchar (10) not null primary key,
fecha date not null,
codencargado varchar (12) not null,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);

insert into danos values('77700','2009-12-22','92456789');

create table detalle_dano(
codarticulo int (25) not null,
cantidad float (3) not null,
coddano varchar (10) not null,
foreign key(codarticulo) references articulos(codigo) on update cascade on delete cascade,
foreign key(coddano) references danos(codigo) on update cascade on delete cascade);

insert into detalle_dano values('133877','1','77700');

create table perdidas_articulo(
codigo varchar (10) not null primary key,
fecha date not null,
codencargado varchar (12) not null,
valortotal int (10) not null,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);


create table detalle_perdida(
codarticulo int (25) not null,
cantidad float (3) not null,
vunitario int (8) not null,
codperdida varchar (10) not null,
foreign key(codarticulo) references articulos(codigo) on update cascade on delete cascade,
foreign key(codperdida) references perdidas_articulo(codigo) on update cascade on delete cascade);


create table ventas_servicios(
codigo varchar (10) not null primary key,
fecha date not null,
valortotal int (8) not null,
codencargado varchar (12) not null,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);

insert into ventas_servicios values('3101','2010-01-26','1050','92031611');

insert into ventas_servicios values('3102','2010-01-26','200','92031611');

insert into ventas_servicios values('3103','2010-01-26','15000','92031611');

insert into ventas_servicios values('3104','2010-01-26','450','92031611');

insert into ventas_servicios values('3105','2010-01-26','500','92456789');

insert into ventas_servicios values('3106','2010-01-26','2000','92456789');

insert into ventas_servicios values('3107','2010-01-26','200','92456789');

insert into ventas_servicios values('3108','2010-01-26','400','92456789');


create table detalle_ventaservicios(
concepto varchar (45) not null,
cantidad int (4) not null,
vunitario int (10) not null,
codservicio varchar (10) not null,
foreign key(codservicio) references ventas_servicios(codigo) on update cascade on delete cascade);

insert into detalle_ventaservicios values('fotocopias','6','100','3101');

insert into detalle_ventaservicios values('llamada celular','3','150','3101');

insert into detalle_ventaservicios values('llamada celular','1','200','3102');

insert into detalle_ventaservicios values('recarga celular','1','15000','3103');

insert into detalle_ventaservicios values('llamada celular','3','150','3104');

insert into detalle_ventaservicios values('fotocopias','5','100','3105');

insert into detalle_ventaservicios values('llamada celular','10','200','3106');

insert into detalle_ventaservicios values('llamada celular','1','200','3107');

insert into detalle_ventaservicios values('llamada celular','2','200','3108');



create table venta_menudeo(
codigo int (10) not null primary key,
fecha date not null,
codencargado varchar (12) not null,
valortotal int (10) not null,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);


create table detallesdeventasmenudeo(
codventamenudeo int (10) not null,
codarticulo int (25) not null,
vunitario int (10) not null,
foreign key (codventamenudeo) references venta_menudeo(codigo) on delete cascade on update cascade,
foreign key (codarticulo) references articulos (codigo) on delete cascade on update cascade
);



create table articulos_enmalestado(
codigo varchar (10) not null primary key,
fecha date not null,
codencargado varchar (12) not null,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);

insert into articulos_enmalestado values('92125','2010-01-12','92031611');

create table detalle_malestado(
codarticulo int (25) not null,
cantidad float (3) not null,
codmalestado varchar (10) not null,
foreign key(codarticulo) references articulos(codigo) on update cascade on delete cascade,
foreign key(codmalestado) references articulos_enmalestado(codigo) on update cascade on delete cascade);

insert into detalle_malestado values('133877','2','92125');

create table abonos(
codigo varchar (10) not null Primary key,
fecha date not null,
codeudor varchar (12) not null,
codcredito varchar (10) not null,
valorcredito float (8) not null,
valorabono float (8) not null,
deudaactual float (8) not null,
codencargado varchar (12) not null,
foreign key(codeudor) references deudores(numdoc) on update cascade on delete cascade,
foreign key(codcredito) references ventas_creditos(codigo) on update cascade on delete cascade,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);


create table prestamo_paraproveedor(
codigo varchar (10) not null primary key,
fecha date not null,
codproveedor varchar (15) not null,
codencargado varchar (12) not null,
foreign key(codproveedor) references proveedores(nit) on update cascade on delete cascade,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);

insert into prestamo_paraproveedor values('66333','2010-01-11','9031100','92031611');

create table detalle_prestamoprov(
codarticulo int (25) not null,
cantidad float (4) not null,
codprestamo varchar (10) not null,
foreign key(codarticulo) references articulos(codigo) on update cascade on delete cascade,
foreign key(codprestamo) references prestamo_paraproveedor(codigo) on update cascade on delete cascade);

insert into detalle_prestamoprov values('23','5','66333');

create table devoluciones_de_proveedor(
codigo varchar (10) not null primary key,
fecha date not null,
codproveedor varchar (15) not null,
codprestamo varchar (10) not null,
codencargado varchar (12) not null,
foreign key(codproveedor) references proveedores(nit) on update cascade on delete cascade,
foreign key(codprestamo) references prestamo_paraproveedor(codigo) on update cascade on delete cascade,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);

create table detalle_devolucion(
codarticulo int (25) not null,
cantidad float (4) not null,
coddevolucion varchar (10) not null,
foreign key(codarticulo) references articulos(codigo) on update cascade on delete cascade,
foreign key(coddevolucion) references devoluciones_de_proveedor(codigo) on update cascade on delete cascade);


create table utilizacion_articulos(
codigo varchar (10) not null primary key,
fecha date not null,
codencargado varchar (12) not null,
valortotal int (10) not null,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);

insert into utilizacion_articulos values('7110','2010-01-26','92456789','1000');

create table detalle_utilizacion(
codarticulo int (25) not null,
cantidad float (3) not null,
vunitario int (10) not null,
codutilizac varchar (10) not null,
foreign key(codarticulo) references articulos(codigo) on update cascade on delete cascade,
foreign key(codutilizac) references utilizacion_articulos(codigo) on update cascade on delete cascade);

insert into detalle_utilizacion values('23','1','1000','7110');

create table prestamos_deproveedor(
codigo varchar (10) not null primary key,
fecha date not null,
codproveedor varchar (15) not null,
codencargado varchar (12) not null,
foreign key(codproveedor) references proveedores(nit) on update cascade on delete cascade,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);

insert into prestamos_deproveedor values('91337','2010-01-06','9031100','92031611');

create table detalle_prestamodeprov(
codarticulo int (25) not null,
cantidad float (4) not null,
codprestamo varchar (10) not null,
foreign key(codarticulo) references articulos(codigo) on update cascade on delete cascade,
foreign key(codprestamo) references prestamos_deproveedor(codigo) on update cascade on delete cascade);

insert into detalle_prestamodeprov values('23','5','91337');

create table devoluciones_paraproveedor(
codigo varchar (10) not null primary key,
fecha date not null,
codproveedor varchar (15) not null,
codiprestamo varchar (10) not null,
codencargado varchar (12) not null,
foreign key(codproveedor) references proveedores(nit) on update cascade on delete cascade,
foreign key(codiprestamo) references prestamos_deproveedor(codigo) on update cascade on delete cascade,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);

insert into devoluciones_paraproveedor values('331333','2010-01-10','9031100','91337','92031611');

create table detalle_devpprov(
codarticulo int (25) not null,
cantidad float (4) not null,
coddevol varchar (10) not null,
foreign key(codarticulo) references articulos(codigo) on update cascade on delete cascade,
foreign key(coddevol) references devoluciones_paraproveedor(codigo) on update cascade on delete cascade);

insert into detalle_devpprov values('23','5','331333');

create table perdida_dinero(
codigo varchar (10) not null primary key,
fecha date not null,
valor int (10) not null,
concepto varchar (70) not null,
codencargado varchar (12) not null,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);

create table gasto_dinero(
codigo varchar (10) not null primary key,
fecha date not null,
valor int (10) not null,
concepto varchar (70) not null,
codencargado varchar (12) not null,
foreign key(codencargado) references encargados(numdoc) on update cascade on delete cascade);

insert into gasto_dinero values('2442','2010-01-03','100000','pago de alquiler ','92031611');

create table donacion_dinero(
codigo varchar (10) not null primary key,
fecha date not null,
valor int (10) not null,
concepto varchar (70) not null
);

insert into donacion_dinero values('001','2009-11-21','2000','proaguinaldos');