• Author / Uploaded
• Javier Angulo Osorio

Citation preview

Laboratorio de Sistemas de Información Administrativos

Página 1

UNIVERSIDAD CATÓLICA DE SANTA MARÍA PROGRAMA PROFESIONAL DE INGENIERÍA DE SISTEMAS

SESIÓN 8:

SISTEMA DE CUENTAS CORRIENTES CLIENTES

I OBJETIVOS  Entender en qué consiste y cómo funciona un sistema de cuentas corrientes  Entender la utilidad de automatizar un sistema de cuentas corrientes  Utilizar sentencias SQL para resolver consultas a un sistema de cuentas corrientes

II TEMAS A TRATAR  Cuentas corrientes clientes  Relaciones entre tablas  Agrupación de datos

III MARCO TEORICO En un sistema de cuentas corrientes, lo que se busca es poder manejar los créditos otorgados a los clientes, se busca tener un control sobre los importes de deuda, las fechas de vencimiento y cancelación, y la gestión de los vendedores para que sus clientes no se atrasen en sus pagos. Un esquema entidad relación de las tablas que forman parte de este sistema, sería como sigue: VENDEDOR

TRANSACCIONES

CLIENTES

UBIGEO

HISTORICA

Un vendedor tiene asignados muchos clientes, un cliente sólo puede tener un vendedor Un ubigeo tiene muchos clientes, un cliente sólo pertenece a un ubigeo Un cliente realiza muchas transacciones, una transacción sólo es realizada por un cliente Las transacciones tienen una tabla histórica, cuya relación es de uno a uno Ing. Mary Ruelas Llerena

Sesión 8

Laboratorio de Sistemas de Información Administrativos

Tabla CLIENTES Nombre del campo Tipo Codcli Carácter Nomcli Carácter Dircli Carácter Ubigeo Carácter Vendedor Carácter Fecultven Fecha Vigente Carácter Tipodoc Carácter Numdoc Carácter Telefono Carácter Calificacion Carácter

Tabla TRANSACCIONES Nombre del campo Tipo Numtrans Carácter Tiptrans Carácter Codcli Carácter Fectra Fecha Fecven Fecha Moneda Carácter Impori Numérico Saldo Numérico Codven Carácter Feccan Fecha Estado Carácter

Observa

Carácter

Tabla HITORICA Nombre del campo Tipo Numtrans Carácter Tiptrans Carácter Codcli Carácter Fectra Fecha Fecven Fecha Moneda Carácter Impori Numérico Saldo Numérico Ing. Mary Ruelas Llerena

Página 2

Longitud Dec Descripción del campo 6 Código del cliente 60 Nombre cliente 45 Dirección cliente 6 Código del ubigeo 4 Código del vendedor 8 Fecha última venta al cliente 1 Indicador de estado, 1=vigente 2 Tipo de documento cliente 15 Número de documento cliente 15 Número telefónico 1 Calificación del cliente. A=Premium, B=preferencial, C=normal, D=nuevo, E=no tan bueno

Longitud 10 3 6 8 8 2 10 10 4 8 1

Dec

2 2

40

Longitud 10 3 6 8 8 2 10 10

Dec

2 2

Descripción del campo Número de la transacción Tipo transacción Código de cliente Fecha de emisión Fecha de vencimiento Código moneda Importe original Saldo a cancelar Código de vendedor Fecha de cancelación Estado de la cuenta. 1=vigente, 2=cancelada, 3=vencida, 4=reprogramada, 5=anulada Observaciones

Descripción del campo Número de la transacción Tipo transacción Código de cliente Fecha de emisión Fecha de vencimiento Código moneda Importe original Saldo a cancelar Sesión 8

Laboratorio de Sistemas de Información Administrativos

Codven Feccan Estado

Carácter Fecha Carácter

4 8 1

Observa

Carácter

40

Página 3

Código de vendedor Fecha de cancelación Estado de la cuenta. 1=vigente, 2=cancelada, 3=vencida, 4=reprogramada, 5=anulada Observaciones

Tabla VENDEDOR Nombre del campo Tipo Longitud Dec Descripción del campo Codven Carácter 4 Código del vendedor Tipven Carácter 1 Tipo de vendedor Nomven Carácter 30 Nombre del vendedor Comision Numérico 10 2 Importe comisión Tabla UBIGEO Nombre del campo Tipo Longitud Dec Descripción del campo Codigo Carácter 6 Código del distrito Departamento Carácter 20 Nombre del departamento Provincia Carácter 20 Nombre de la provincia Distrito Carácter 20 Nombre del distrito

IV ACTIVIDADES 1. Crear las tablas descritas en el marco teórico para un sistema de cuentas corrientes de clientes, como ejemplo se pone la tabla de Clientes CREATE TABLE clientes ( codcli character(6) NOT NULL, nomcli character(60), dircli character(45), ubigeo character(6), vendedor character(4), fecultven date, vigente character(1), tipodoc character(2), numdoc character(15), telefono character(15), calificacion character(1), CONSTRAINT pkcodcli PRIMARY KEY (codcli ) ) 2. Ingresar los siguientes registros en las tablas respectivas: Tabla CLIENTES Ing. Mary Ruelas Llerena

Sesión 8

Laboratorio de Sistemas de Información Administrativos Codcli

Nomcli

094344 Coila Quispe, Julio Cesar 124096 Tito Gonza, Emerson 414689 Idme Zegarra, Eugenia Asunta 231057 Tenorio Gallegos, Irene 324880 Muñiz Calvo, Miguel

Dirci

Ubig vend eo edor

Fecultven

Vige nte

Los rosales E22 Muralla china 317

140 102 140 101

015 4 026 1

14/07/20 14 24/09/20 14

La isla 629 A

140 015 109 4

Puerta Verde X-5 San Martín 1706

Página 4

Tipo doc

numdoc

telefono

calific acion

1 CE

3353886 0 0006428 89

556191 A 9968401 23 C

15/08/20 14

1 DNI

3490221 4

9124325 53 B

140 035 102 7

10/09/20 14

1 DNI

3109797 1

243255 B

140 035 101 7

20/09/20 14

1 DNI

4330188 5

568091 A

1 DNI

Ejemplo:

INSERT INTO clientes(codcli, nomcli, dircli, ubigeo, vendedor, fecultven, vigente, tipodoc, numdoc, telefono, calificacion) values ('094344', ‘Coila Quispe, Julio Cesar’,'Los rosales E22','140102','0154','14/07/2014','1','DN','33538860','556191','A');

Tabla TRANSACCIONES Tipt Numtra Codcli rans ns NC 874976 094344 NC

293849

124096

NC

258674

414689

NC

19837

231057

NC

1876

324880

NC

923459

414689

NC

434

124096

NC

85720

414689

NC

023849

231057

NC

28937

324880

Fectra

Fecven

01/05/201 4 14/06/201 4 25/05/201 4 06/07/201 4 08/08/201 4 29/06/201 4 15/07/201 4 20/08/201 4 06/09/201 4 18/09/201 4

31/05/201 4 14/07/201 4 24/06/201 4 05/08/201 4 07/09/201 4 29/07/201 4 14/08/201 4 19/09/201 4 06/10/201 4 18/10/201 4

Moneda 01

impori

saldo

codven

640 0154

01

6000 1000 0

01

7000

02

9000 1000 0

0 0154 745 0 0357

01 01

0 0261

0 0357

02

4000 1000 0

0 0154

01

2000

01

4000

0 0261 197 0 0154 362 0 0357

01

3000

630 0357

feccan

esta Obser do va

1 10/07/201 4 2 29/06/201 4 2 1 07/09/201 4 2 26/07/201 4 2 19/08/201 4 2 1 5 5

Ejemplo: INSERT INTO transacciones(tiptrans, numtrans, codcli, fectra, fecven, moneda, impori, saldo, codven, feccan, estado, observa) VALUES ('NC','874976','094344','01/05/2014','31/05/2014','01',6000,640,'0154',null,'1',''); Tabla VENDEDOR codven tipven nomven S 0357 Juan Alvarez Federico J 0154 0261

S

Barrios Andrea Moreno

Ing. Mary Ruelas Llerena

comision 294.52 134.55 490.05 Sesión 8

Laboratorio de Sistemas de Información Administrativos

Página 5

Ejemplo: INSERT INTO vendedor(codven, tipven, nomven, comision) VALUES ('0357','S','Juan Alvarez',294.52); Tabla UBIGEO codigo departamento 140101 Arequipa 140102 Arequipa 140109 Arequipa

provincia Arequipa Arequipa Arequipa

Ejemplo: INSERT INTO ubigeo(codigo, departamento, ('140101','Arequipa','Arequipa','Cercado');

distrito Cercado Cayma Miraflores

provincia,

distrito)

VALUES

3. Hacer las sentencias en SQL para obtener la siguiente información: a. Los nombres de los clientes, con su distrito y el nombre del vendedor. nomcli

distrito

vendedor

Coila Quispe, Julio Cesar

Cayma Cercado Miraflores

Federico Barrios

Tito Gonza, Emerson Idme Zegarra, Eugenia Asunta Tenorio Gallegos, Irene Muñiz Calvo, Miguel

Cayma Cercado

Andrea Moreno Federico Barrios Juan Alvarez Juan Alvarez

select a.nomcli, b.distrito, c.nomven from clientes a inner join ubigeo b on a.ubigeo = b.codigo inner join vendedor c on a.vendedor = c.codven b. Total de crédito otorgado por cliente y vendedor Cliente Idme Zegarra, Eugenia Asunta Tenorio Gallegos, Irene Muñiz Calvo, Miguel Tito Gonza, Emerson Coila Quispe, Julio Cesar

Vendedor Federico Barrios Juan Alvarez Juan Alvarez Andrea Moreno Federico Barrios

Importe

saldo 13000 13000 13000

8020 11070 5110

20000

12430

6000

640

select a.nomcli, b.nomven, sum(c.impori) as importe, sum(c.saldo) as saldo from clientes a inner join vendedor b on a.vendedor = b.codven inner join transacciones c on a.codcli = c.codigo and b.codven = c.codven group by a.nombre, b.nomven c. Mostrar las NC canceladas ordenadas por días de atraso (diferencia entre la fecha de cancelación y la de vencimiento)

Ing. Mary Ruelas Llerena

Sesión 8

Laboratorio de Sistemas de Información Administrativos

Página 6

TIPTRANS NUMTRANS CODI FECTRA FECVEN IMPORI FECCAN GO NC 293849 124096 14/06/201 14/07/20 10/07/20 4 14 10000 14 NC 923459 414689 29/06/201 29/07/20 26/07/20 4 14 4000 14 NC 1876 324880 08/08/201 07/09/20 07/09/20 4 14 10000 14 NC 258674 414689 25/05/201 24/06/20 29/06/20 4 14 7000 14 NC 434 124096 15/07/201 14/08/20 19/08/20 4 14 10000 14

ESTADO

2 2 2 2 2

select feccan-fecven as dias_atr,* from transacciones where tiptrans='NC' and estado='2' order by dias_atr d. Mostrar el monto total que ha pagado cada cliente (la diferencia entre el monto original y el saldo) Cliente Idme Zegarra, Eugenia Asunta Tenorio Gallegos, Irene Muñiz Calvo, Miguel Tito Gonza, Emerson Coila Quispe, Julio Cesar

Total 4980 1930 7890 7570 5360

V EJERCICIOS Sobre las tablas creadas en la actividad, generar las sentencias SQL que satisfagan los requerimientos: a. Listado de las notas de crédito otorgadas a cada cliente, mostrando su dirección, teléfono, calificación del cliente, nombre de vendedor, fecha de emisión, saldo y comisión del vendedor. b. Mostrar la descripción del ubigeo y el total de importe original ordenado por monto descendentemente. c. Mostrar el total de deuda de los clientes por cada vendedor, mostrando su nombre. d. Pasar todas las transacciones canceladas de la tabla TRANSACCIONES a la tabla HISTORICA

VI CUESTIONARIO Ing. Mary Ruelas Llerena

Sesión 8

Laboratorio de Sistemas de Información Administrativos

Página 7

1. ¿Cómo se relacionan las diferentes tablas mostradas en el sistema de cuentas corrientes clientes? 2. ¿Cuál es la diferencia entre la tabla de transacciones y la tabla histórica? ¿Cuál es la utilidad de pasar registros de la tabla de transacciones a tablas históricas? 3. ¿Qué beneficios trae llevar un sistema automatizado de cuentas corrientes de clientes y qué mejoras propondría hacerle al sistema practicado en clase?

VII BIBLIOGRAFIA Y REFERENCIAS  MCLEOD, Raymond, “Sistemas de Información Gerencial”, Prentice Hall Hispanoamericana, México, 1998.  DAVIS, Gordon y OLSON, Margrethe. “Sistemas de Información Gerencial”, McGraw-Hill, México, 1990.  SCOTT, GEORGE M., “Principios de Sistemas de Información”, McGraw-Hill, México, 1992.  PRESSMAN, Roger S. “Ingeniería del Software, Un Enfoque Práctico”, McGraw-Hill, España, 1989  MARTIN, James. “Sistemas de Información”, El Ateneo, Argentina, 1991.  MCKEEVER, James M. “Sistemas de Información para la Gerencia”, Limusa, México, 1990.  BARROS, Oscar, “Tecnologías de la Información y su uso en Gestión”, McGraw Hill, Santiago, 1998  LAUDON, Kenneth, Laudon Jane. Administración de los Sistemas de Información.

Organización

y

Tecnología.

México:

Prentice

Hall

Hispanoamericana. 1996  MURDICK, Robert G. “Sistemas de Información Administrativa”. McGraw-Hill, México, 1992.

Ing. Mary Ruelas Llerena

Sesión 8