• Author / Uploaded
• KEVIN GUSTAVO ANGLAS VALERIO

Citation preview

UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS

BASE DE DATOS 3 PRÁCTICA CALIFICADA Diseño de un DataMart Profesor:  Espinoza Domínguez Robert Elías Ciclo:  Séptimo Ciclo Integrantes:      

Anglas Valerio Kevin Gustavo Chávez Guerrero Gabriel Alexander Contreras Ruiz Daniel Gonzales Duran Jefferson Ervyn Hermenegildo Flores Franco Jossep Montellanos LLamoca Daniel

2020-I Caso de Estudio de Clínica Odontológica Se requiere implementar una solución de Inteligencia de Negocios para la clínica odontológica “SONRIE”, en un plazo de cinco meses. En el plan estratégico se definieron los siguientes objetivos:

 

Objetivo 01: Mejorar el margen de utilidad de cada servicio en por lo menos 20% respecto al año anterior. Objetivo 02: reducir el costo por servicio en 15% respecto al año anterior.

La clínica cuenta con tres locales de atención (San Borja, San Isidro y Miraflores), identificados por un número, nombre, su dirección dada por la ciudad donde se ubica, la calle y el número. En cada local se realizan un conjunto de servicios-tratamientos y existen varios consultorios que se identifican por un número dentro del local y en cada consultorio existe cierto equipamiento. Dicho equipamiento se identifica globalmente mediante un código, descripción (torno, laser, etc.), marca, modelo, un número de serie. Los odontólogos que trabajan en la clínica se identifican por su nombre y Nro. Colegiatura. De ellos se conoce su especialidad principal dentro de la odontología y los diferentes servicios (tratamientos) que pueden realizar. Interesa mantener la historia clínica de cada cliente afiliado o no, la cual consiste, por un lado, en un registro de cada consulta indicando la fecha de consulta, el odontólogo y el local y por otro en un registro de todos los servicios-tratamientos que se le han realizado. De cada tratamiento interesa saber: fecha de inicio, si el tratamiento ha sido finalizado o no y la identificación del odontólogo que lo realizó. La enfermera genera una orden de pago cuando el cliente desea realizar un pago parcial o total del servicio-tratamiento realizado, finalmente el cliente se dirige a Tesorería a realizar el pago donde el cajero registra la transacción. El problema es la oportunidad en la generación y obtención de la información, no se conoce el nivel de ventas y costos por tratamiento (servicio), el margen de utilidad que deja cada doctor y servicio o por local, se requiere un sistema de gestión para tomar decisiones rápidas y oportunas.

Para este trabajo utilizamos el software SQL SERVER Y EXCEL

A continuación, mostraremos la implementación en SQL SERVER, Abrir el DATA MART

Diagrama de base de datos generado en SQL SERVER

A continuación, mostraremos la implementación en EXCEL, Abrir el Excel de la carpeta

1. Elabore los cuadros y/o tablas necesarias para determiner las dimensiones, la tabla de hechos y las medidas para esta solución de inteligencia de negocios.

DIMENSIONES

TABLAS DE HECHOS MEDIDAS

      

Sucursal Paciente Doctor Servicio Consultorio Fecha Equipamiento

 Tratamiento  Cantidad de la venta  Costo de la venta  Precio de la venta

2. Elabore el diagrama de entidad y relación para la solución de inteligencia de negocios.

3. Elabore el diagrama multidimensional para la solución de inteligencia de negocios.

4. Implemente en el SQL Server el diagrama físico para la solución de inteligencia de negocios.

PASO 1: CREACION Y USO DE LA BASE DE DATOS

create database Clinica_Odontologica; use Clinica_Odontologica;

PASO 2: CREACION DE LAS TABLAS (ENTIDADES) /*TABLAS PARA LA BD*/

CREATE TABLE SUCURSAL /*DISTRITO DE LA SUCURSAL*/ ( idSucursal int primary key, distrito varchar(40) NOT NULL, direccion varchar(100) NOT NULL ); CREATE TABLE DOCTOR ( idDoctor int primary key, apellidos varchar(20) NOT NULL, nombre varchar(20) NOT NULL, numColegiatura int NOT NULL, especialidad varchar(20) ); CREATE TABLE CONSULTORIO /*EN UNA SUCURSAL HAY VARIOS CONSULTORIOS */ ( idConsultorio int primary key, idSucursal int NOT NULL , idDoctor int NOT NULL, CONSTRAINT fk_Sucursal FOREIGN KEY (idSucursal) REFERENCES SUCURSAL(idSucursal), CONSTRAINT fk_DOC FOREIGN KEY (idDoctor) REFERENCES DOCTOR(idDoctor) ); CREATE TABLE EQUIPAMIENTO /*EN UN CONSULTORIO AL VARIOS EQUIPOS */ ( idEquipamiento int primary key, idConsultorio int , serie varchar(25) NOT NULL,/*HERRAMIENTA PARA ENDODONCIA,CIRUGIA(sacar muelas y otros) ...ETC*/ descripcion varchar(25), precio float, CONSTRAINT fk_Consultorio FOREIGN KEY (idConsultorio) REFERENCES CONSULTORIO(idConsultorio) ); CREATE TABLE SERVICIO/*SERVICIOS BRINDADOS */ ( idServicio int primary key, nombre varchar(20) NOT NULL ); CREATE TABLE PACIENTE/*PARA CREAR HISTORIAL DE CADA PACIENTE PEDIDO EN EL CASO*/ ( idPaciente int primary key, DNI varchar(10), apellidos varchar(20) NOT NULL, nombre varchar(20) NOT NULL, ); CREATE TABLE FECHA/*PARA REGISTRAR EN LOS PAGOS DEL TRATAMIENTO YA QUE SE

PUEDE HACER PAGOS PARCIALES ,FECHA DE INICIO TRATAMIENTO .... BUSCAR POR DIA MES AÑO etc...*/ ( idFecha int primary key, dia int NOT NULL, mes int NOT NULL, anio int NOT NULL, Nombre_mes varchar(20), Fecha date ); CREATE TABLE TRATAMIENTO ( idTratamiento int primary key, idFecha int NOT NULL, idServicio int NOT NULL, idConsultorio int NOT NULL, idPaciente int NOT NULL, finalizo int,/*booleano 0 false 1 true*/ Total float NOT NULL,/**/ Costo float NOT NULL, cantidad int , CONSTRAINT fk_Fecha FOREIGN KEY (idFecha) REFERENCES FECHA(idFecha), CONSTRAINT fk_Servicio FOREIGN KEY (idServicio) REFERENCES SERVICIO(idServicio), CONSTRAINT fk_Consultorios FOREIGN KEY (idConsultorio) REFERENCES CONSULTORIO(idConsultorio), CONSTRAINT fk_Paciente FOREIGN KEY (idPaciente) REFERENCES PACIENTE(idPaciente) ); CREATE TABLE PAGO/*PAGO DE TRATAMIENTO INCLUIDO EL COSTO DE HERRAMIENTAS UTILIZADAS*/ ( idPago int primary key, /*NUMERO DE BOLETA DE PAGO*/ idTratamiento int, precio int, idFecha int, CONSTRAINT fk_Fechas FOREIGN KEY (idFecha) REFERENCES FECHA(idFecha), CONSTRAINT fk_Tratamiento FOREIGN KEY (idTratamiento) REFERENCES TRATAMIENTO(idTratamiento) );

5. Cargue los datos en el SQL Server para poner en práctica su solución.

Para mostrar el historial se mostrarán todos los tratamientos en una tabla de excel. PASO 3: INSERCION DE DATOS INSERT INTO SUCURSAL VALUES (1,'San Borja','Avenida Guardia Civil, 335'); INSERT INTO SUCURSAL VALUES (2,'San Isidro','Av. Petit Thouars 3270'); INSERT INTO SUCURSAL VALUES (3,'Miraflores','Carlos Tenaud 251'); /*DOCTOR*/ /*INSERTANDO POR ESPECIALIDADES (ENDODONCIA) */ INSERT INTO DOCTOR VALUES (1,'LICAPA HINFANZON','HASLER',11200006,'CIRUGIA BUCAL'); INSERT INTO DOCTOR VALUES (2,'MAMANI TURPO','RENZO',13200002,'CIRUGIA BUCAL');

/*agregar mas*/ /*INSERTANDO POR ESPECIALIDADES (CIRUGIA BUCAL) */ INSERT INTO DOCTOR VALUES (3,'HUAREZ AREVALO','DIEGO',12200004,'ENDODONCIA'); INSERT INTO DOCTOR VALUES (4,'MALLMA TRUJILLO','RONNY',14200005,'ENDODONCIA'); /*agregar mas*/ /*INSERTANDO POR ESPECIALIDADES (ORTODONCIA) */ INSERT INTO DOCTOR VALUES (5,'CASTRO PEÑA','GABRIELA',15200007,'ORTODONCIA'); INSERT INTO DOCTOR VALUES (6,'RAMIREZ LERMA','MILAGROS',15200008,'ORTODONCIA'); /*CONSULTORIOS*/ /*SAN BORJA 1*/ INSERT INTO CONSULTORIO VALUES (1,1,1) ; INSERT INTO CONSULTORIO VALUES (2,1,2); /*INSERT INTO CONSULTORIO VALUES (3,3,3);*/ /*SAN ISIDRO 2*/ INSERT INTO CONSULTORIO VALUES (3,2,3); INSERT INTO CONSULTORIO VALUES (4,2,4); /*INSERT INTO CONSULTORIO VALUES (6,2,6);*/ /*MIRAFLORES 3*/ INSERT INTO CONSULTORIO VALUES (5,3,5); INSERT INTO CONSULTORIO VALUES (6,3,6); /*INSERT INTO CONSULTORIO VALUES (9,3,3);*/ /*SERVICIOS*/ INSERT INTO SERVICIO VALUES (1,'ENDODONCIA'); INSERT INTO SERVICIO VALUES (2,'CIRUGIA BUCAL');

INSERT INTO SERVICIO VALUES (3,'ORTODONCIA');

/*EQUIPAMIENTO*/ /*3 equipamientos por consultorio*/

/*Consultorio 1 s*/ INSERT INTO Equipamiento INSERT INTO Equipamiento INSERT INTO Equipamiento /*Consultorio 2 s*/ INSERT INTO Equipamiento INSERT INTO Equipamiento INSERT INTO Equipamiento /*Consultorio 3 s*/ INSERT INTO Equipamiento INSERT INTO Equipamiento INSERT INTO Equipamiento /*Consultorio 4 */ INSERT INTO Equipamiento Quirugicos',3.20); INSERT INTO Equipamiento INSERT INTO Equipamiento /*Consultorio 5 s*/ INSERT INTO Equipamiento INSERT INTO Equipamiento INSERT INTO Equipamiento /*Consultorio 6 s*/ INSERT INTO Equipamiento INSERT INTO Equipamiento INSERT INTO Equipamiento

/*FECHAS*/ /*ENERO*/ INSERT INTO INSERT INTO INSERT INTO /*FEBRERO*/ INSERT INTO INSERT INTO INSERT INTO /*MARZO*/ INSERT INTO INSERT INTO

VALUES (1,1,'ENDODONCIA','Guantes Quirugicos',3.20); VALUES (2,1,'ENDODONCIA','Servilletas',2.50); VALUES (3,1,'ENDODONCIA','Babero',4.50); VALUES (4,2,'ENDODONCIA','Anastesia',3.20); VALUES (5,2,'ENDODONCIA','Desinfectante',5.60); VALUES (6,2,'ENDODONCIA','Guantes Quirugicos',3.20); VALUES (7,3,'CIRUGIA BUCAL','mascarilla',3.50); VALUES (8,3,'CIRUGIA BUCAL','hilo dental',3.20); VALUES (9,3,'CIRUGIA BUCAL','Guantes Quirugicos',3.20); VALUES (10,4,'CIRUGIA BUCAL','Guantes VALUES (11,4,'CIRUGIA BUCAL','Babero',4.50); VALUES (12,4,'CIRUGIA BUCAL','Anastesia',3.20); VALUES (13,5,'ORTODONCIA','Babero',4.50); VALUES (14,5,'ORTODONCIA','Guantes Quirugicos',3.20); VALUES (15,5,'ORTODONCIA','Mascarilla',1.5); VALUES (16,6,'ORTODONCIA','Anastesia',3.20); VALUES (17,6,'ORTODONCIA','mascarilla',3.50); VALUES (18,6,'ORTODONCIA','Guantes Quirugicos',3.20);

FECHA VALUES (1,2,1,18,'Enero','20180102 00:00:00 AM'); FECHA VALUES (2,4,1,18,'Enero','20180104 00:00:00 AM'); FECHA VALUES (3,13,1,18,'Enero','20180113 00:00:00 AM'); FECHA VALUES (4,6,2,18,'Febrero','20180206 00:00:00 AM'); FECHA VALUES (5,11,2,18,'Febrero','20180211 00:00:00 AM'); FECHA VALUES (6,21,2,18,'Febrero','20180221 00:00:00 AM'); FECHA VALUES (7,09,3,18,'Marzo','20180309 00:00:00 AM'); FECHA VALUES (8,13,3,18,'Marzo','20180313 00:00:00 AM');

INSERT INTO /*ABRIL*/ INSERT INTO INSERT INTO INSERT INTO /*MAYO*/ INSERT INTO INSERT INTO INSERT INTO /*JUNIO*/ INSERT INTO INSERT INTO INSERT INTO /*JULIO*/ INSERT INTO INSERT INTO INSERT INTO

FECHA VALUES (9,19,3,18,'Marzo','20180319 00:00:00 AM'); FECHA VALUES (10,15,4,18,'Abril','20180415 00:00:00 AM'); FECHA VALUES (11,19,4,18,'Abril','20180419 00:00:00 AM'); FECHA VALUES (12,25,4,18,'Abril','20180425 00:00:00 AM'); FECHA VALUES (13,11,4,18,'Mayo','20180511 00:00:00 AM'); FECHA VALUES (14,18,4,18,'Mayo','20180518 00:00:00 AM'); FECHA VALUES (15,23,4,18,'Mayo','20180523 00:00:00 AM'); FECHA VALUES (16,17,4,18,'Junio','20180617 00:00:00 AM'); FECHA VALUES (17,20,4,18,'Junio','20180620 00:00:00 AM'); FECHA VALUES (18,23,4,18,'Junio','20180623 00:00:00 AM'); FECHA VALUES (19,13,4,18,'Julio','20180713 00:00:00 AM'); FECHA VALUES (20,19,4,18,'Julio','20180719 00:00:00 AM'); FECHA VALUES (21,29,4,18,'Julio','20180729 00:00:00 AM');

/*PACIENTES*/ INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE ANGEL'); INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE ALBERTO'); INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE INSERT INTO PACIENTE

VALUES VALUES VALUES VALUES VALUES

(1,'75524986','GONZALES DEL RIO','GABRIELA'); (2,'72335692','SIHUEN MORI','KATTY'); (3,'71356571','GOMEZ PEREIRA','JUAN JOSE'); (4,'75489223','FLORES HERMEREGILDO','FRANCO'); (5,'35524986','MORENO RONQUILLO','MIGUEL

VALUES VALUES VALUES VALUES VALUES VALUES

(6,'30235692','FUENTES DAVILA','PATRICIO'); (7,'31256170','GOMEZ RONCAL','JOSE'); (8,'37989210','MAMANI MAMANI','PEDRO'); (9,'65421086','DE LA CRUZ PEÑA','ESTEFANY'); (10,'62235652','GONZALES PRIETO','KARLA'); (11,'61203571','PEREIRA FUENTES','JUAN

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(12,'17289321','GONZALES AYBAR','JEFFRY'); (13,'10524986','HUATUCO PRIETO','DAVID'); (14,'13235609','GUILLEN RUIZ','ROSA'); (15,'41256570','FLORES QUISPE','MARIA'); (16,'47989203','GONZALES GONZALES','DAVID'); (17,'42235610','FUERTES CRUZ','KATTY'); (18,'56156574','CASTRO TRUJILLO','LUCAS'); (19,'27989253','PEÑA GOMEZ','PATRICK'); (20,'21253270','MEJIA CUELLAR','JEFFERSON'); (21,'27911923','BERNABE GOMEZ','JAVIER');

/*TRATAMIENTO*/ INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO INSERT INTO TRATAMIENTO

/*PAGOS REALIZADOS*/ INSERT INTO PAGO INSERT INTO PAGO INSERT INTO PAGO INSERT INTO PAGO INSERT INTO PAGO INSERT INTO PAGO INSERT INTO PAGO INSERT INTO PAGO INSERT INTO PAGO INSERT INTO PAGO INSERT INTO PAGO INSERT INTO PAGO

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1,1,1,1,1,0,600,100,3); (2,2,2,2,2,1,800,250,3); (3,3,3,3,3,1,900,300,2); (4,4,1,4,4,0,1200,200,3); (5,5,2,5,5,1,1300,300,3); (6,6,3,6,6,0,500,100,2); (7,7,1,1,7,1,750,150,2); (8,8,2,2,8,0,2000,100,10); (9,9,3,3,9,1,600,150,3); (10,1,1,4,10,1,1100,500,1); (11,2,2,5,11,0,980,300,3); (12,3,3,6,12,1,1300,150,6); (13,4,1,1,13,1,1000,150,4); (14,5,2,2,14,1,600,200,2); (15,6,3,3,15,0,1300,150,3); (16,7,1,4,16,0,500,150,2); (17,8,2,5,17,1,1340,350,2); (18,9,3,6,18,1,222,18,4); (19,1,1,1,19,1,850,120,4); (20,2,2,2,20,0,900,300,1); (21,3,3,3,21,1,900,120,5);

VALUES(1,1,300,1); VALUES(2,2,750,2); VALUES(3,3,600,3); VALUES(4,4,600,4); VALUES(5,5,900,5); VALUES(6,6,200,6); VALUES(7,7,300,7); VALUES(8,8,1000,8); VALUES(9,9,450,9); VALUES(10,10,500,10); VALUES(11,11,900,11); VALUES(12,12,900,12);

INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO

PAGO PAGO PAGO PAGO PAGO PAGO PAGO PAGO PAGO

VALUES(13,13,600,13); VALUES(14,14,400,14); VALUES(15,15,450,15); VALUES(16,16,300,16); VALUES(17,17,700,17); VALUES(18,18,72,18); VALUES(19,19,480,19); VALUES(20,20,300,20); VALUES(21,21,1600,21);

6. Elabore los dashboards necesarios con Powe Pivot. TOTAL DE VENTAS POR TRATAMIENTO 9000 7920

8000 7000 6000

6000

5722

5000

1 - ENDODONCIA 2 - CIRUGIA BUCAL 3 - ORTODONCIA

4000 3000 2000 1000 0

Total

COSTOS POR TRATAMIENTO

6000 4950

5000

4000 3272

3080

1 - ENDODONCIA 2 - CIRUGIA BUCAL 3 - ORTODONCIA

3000

2000

1000

0

Total

UTILIDAD DE DOCTOR POR TRATAMIENTO 2000

1850

1800 1600

1600

1520 1400

1400 1200

1120

1000

1 - ENDODONCIA 2 - CIRUGIA BUCAL 3 - ORTODONCIA

850

800 600 400 200 0

DIEGO

GABRIELA

HASLER

MILAGROS

RENZO

RONNY

UTILIDAD POR SUCURSAL 2000

1850

1800 1600

1600

1520 1400

1400 1200

1120

1000

1 - ENDODONCIA 2 - CIRUGIA BUCAL 3 - ORTODONCIA

850

800 600 400 200 0

Miraflores

San Borja

San Isidro

UTILIDAD DE CADA DOCTOR POR CADA SUCURSALES 2000 1850 1800 1600

1600

1520 1400

1400 1200

1120

1000 850 800 600 400 200 0

CONCLUSION

1 - ENDODONCIA 2 - CIRUGIA BUCAL 3 - ORTODONCIA

El presente trabajo aprendimos a crear una base de datos en el software SQL SERVER, además de crear tablas para la solución del problema. Por otro lado, se elaboró la inserción de datos para ver el funcionamiento de la clínica Odontológica. También este software nos permite la creación o generación del diagrama de base de datos el cual pudimos observar en el presente informe. Así mismo elaboramos unos DashBoards gracias la Power Pivot que nos brinda el programa de Microsoft Excel, y gracias a esto pudimos observar comportamientos de clínica como la utilidad de cada doctor en las diferentes sucursales, cono también la utilidad que percibe cada doctor por los tratamientos que brinda, entre otros análisis que plasmamos en el presente trabajo.