dbd_u3_ea_joec

Encabezado: EVIDENCIA DE APRENDIZAJE 3. 1

Unidad 3. Evidencia de aprendizaje. Bases de datos en base a un caso.

Por:

Jorge Ricardo Escobar Carrasco.

[email protected]

Diseo de bases de datos.

Universidad abierta y a distancia de Mxico.

mircoles 30 de abril de 2014

Facilitador:

Irma Flores Ros.

[email protected]

EVIDENCIA DE APRENDIZAJE 3. 2

Tabla de contenidoSumario............................................................................................................................................3Unidad 3. Evidencia de aprendizaje. Bases de datos en base a un caso..........................................4Anlisis del caso..............................................................................................................................6

Tablas del caso............................................................................................................................6Clientes...................................................................................................................................6RepresentanteVentas...............................................................................................................6Oficinas...................................................................................................................................6Productos................................................................................................................................6Pedidos...................................................................................................................................7Directores...............................................................................................................................7

Modelo entidad relacin...............................................................................................................7Modelo Relacional...........................................................................................................................8Creacin de las tablas con instrucciones SQL.................................................................................9

// Estructura de tabla para la tabla `Clientes`..............................................................................9// Estructura de tabla para la tabla `Directores`...........................................................................9// Estructura de tabla para la tabla `Oficinas`..............................................................................9// Estructura de tabla para la tabla `Pedidos`.............................................................................10// Estructura de tabla para la tabla `Productos`.........................................................................10// Estructura de tabla para la tabla `RepresentanteVentas`........................................................11// Estructura de tabla para la tabla `RepresentanteVentas`........................................................12Relaciones entre tablas..............................................................................................................12

Filtros para la tabla `Pedidos`...............................................................................................12Filtros para la tabla `RepresentanteVentas`..........................................................................12

Insercin de los registros en las tablas por medio de cdigo SQL................................................13// INSERTAR Oficinas..............................................................................................................13// INSERTAR Directores...........................................................................................................13// INSERTAR Clientes..............................................................................................................13// INSERTAR Productos............................................................................................................14// INSERTAR RepresentantesVentas.........................................................................................14// INSERTAR Pedidos...............................................................................................................14

Consultas de prueba.......................................................................................................................15Conclusin.....................................................................................................................................17Bibliografa....................................................................................................................................18


Sumario.

El propsito de la actividad es realizar una base de datos en SQL y ensayar las operaciones de

creacin de base de datos en base a comandos, realizacin de consultas en base a operadores del

lgebra relacional y conocer el proceso de almacenamientos de las base de datos, las cuales,

como se mencion, van desde el aspecto fsico, hasta el conceptual y lgico.

Una vez estudiado los captulos de la unidad es de suma importancia que se ejercite el

conocimiento, debido a que con ste tipo de actividad, se puede reforzar lo aprendido e

identificar en que punto no quedo tan claro el conocimiento.


Unidad 3. Evidencia de aprendizaje. Bases de datos en base a un caso.

Instrucciones:

1. Lee con atencin el siguiente caso y realiza lo que se te pide en los puntos siguientes.

Observa que despus del caso que te presentan las tablas que tendra las bases de datos y

los atributos de cada una de ellas:

Una pequea empresa distribuidora de productos requiere un sistema de base de datos

que le ayude a controlar el procesamiento de los pedidos, dicha base de datos se llamar

VentasProductos y est compuesta por las siguientes tablas:

Clientes: Contiene una fila por cada uno de los clientes de la empresa. Sus campos son:

nmero de cliente, nombre de la empresa, nombre de la persona de contacto de la

empresa y lmite de crdito.

RepresentanteVentas: Contiene una fila por cada uno de los vendedores de la empresa.

Sus campos son: nmero de empleado, nombre edad, nmero de la oficina en la que

trabaja el vendedor, ttulo, fecha de contratacin, id del director del empleado (todo

empleado tiene un director del cual depende), cuota de ventas previstas y nmero de

ventas realizadas.

Oficinas: Contiene una fila por cada una de las oficinas en las que trabajan los

vendedores. Sus campos son: nmero de oficina, ciudad, regin, ventas realizadas y

objetivo de ventas.

Productos: Contiene una fila por cada producto disponible para ventas. Sus campos son:

id del producto, descripcin, precio y existencia.

Pedidos. Contiene una fila por cada pedido ordenado por un cliente: Por simplicidad, se


supone que cada pedido solo puede ser un solo producto. Sus campos son: nmero, fecha

del pedido, nmero del cliente que hizo el pedido, nmero de empleado que realiz el

pedido, el id del producto que fue pedido, la cantidad y el importe.

1. Crea los modelos para la base de datos del caso presentado (E-R y Relacional).

2. Crea la BD VentasProductos en SQL server 2008, en base a comandos.

3. Crea las tablas que se mencionan en el caso, con sus respectivos atributos, con

instrucciones de SQL.

4. Inserta por lo menos cinco registros a cada tabla por medio de comandos con

instrucciones SQL.

5. Realiza por lo menos diez consultas diferentes de las que se presentaron en la unidad,

utilizando algunos operadores lgicos, y observa lo que sucede. Guarda todas estas

instrucciones en un documento de texto, con el nombre

DBD_U3_INSTRUCCIONESSQL_XXYZ. Enva el archivo a tu Facilitador(a).


Anlisis del caso.

Por lo que se entiende de la oracin la base de datos registra las ventas de productos que

realiza cada vendedor, y estos vendedores estn distribuidos entre varias oficinas.

En este caso, las tablas ya estn definidas. Probablemente necesite agregar alguna entidad

nueva o cambiar los campos de acuerdo a las necesidades. Por ejemplo, cambiare el campo

numero de cliente de la tabla clientes a idCliente y repetir este proceso para estandarizar

los campos llave de las tablas.

En la tabla RepresentanteVentas existe un campo llave secundaria llamado idDirector

para hacerlo coherente con el diseo, agregar la entidad directores y la relacionar con

RepresentanteVentas con una relacin uno a muchos.

Las tablas son las siguientes:

Tablas del caso.

Clientes.

idCliente nombreEmpresa nombreContacto limiteCredito

RepresentanteVentas.

idEmpleado nombreEmpleado edad idOficina titulo fechaContratacion idDirector cuotaPrevista ventasEmpleado

Oficinas.

idOficina ciudad region ventasOficina objetivoVentas

Productos.

idProducto descripcion precio existencias


Pedidos.

idPedido fechaPedido idCliente idProducto cantidad importe

Directores.

idDirector nombreDirector

Modelo entidad relacin.

A continuacin, anexo la figura 1, con el modelo E/R del caso:

En la figura 1, se aprecian las relaciones entre las entidades del diseo de base de datos. A

continuacin, tenemos en la figura 2, el mismo modelo con los atributos anexados.

Figura 1. Modelo E/R sin campos.


Se puede apreciar que ante la aparente complejidad del diseo, an se mantienen las

mismas relaciones entre las entidades. Los atributos subrayados, son llaves primarias.

Modelo Relacional.

En la figura 3, podemos ver como se relacionan las entidades entre si a mayor detalle:

Como se puede apreciar, en el modelo relacional se definen las relaciones de forma

detallada. Ahora solo resta crear la base de datos.

Figura 2. Modelo E/R con atributos anexados.

Figura 3. Modelo relacional del caso.


Creacin de las tablas con instrucciones SQL.

Ahora crear la base de datos con las siguientes instrucciones SQL:

// Estructura de tabla para la tabla `Clientes`

CREATE TABLE IF NOT EXISTS `Clientes` (

ìdCliente` int(10) unsigned NOT NULL AUTO_INCREMENT,

`nombreEmpresa` varchar(50) COLLATE latin1_spanish_ci NOT NULL,

`nombreContacto` varchar(50) COLLATE latin1_spanish_ci NOT NULL,

`limiteCredito` decimal(19,4) NOT NULL,

PRIMARY KEY (ìdCliente`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci

AUTO_INCREMENT=6 ;

// Estructura de tabla para la tabla `Directores`

CREATE TABLE IF NOT EXISTS `Directores` (

ìdDirector` int(10) unsigned NOT NULL AUTO_INCREMENT,

`nombreDirector` varchar(50) COLLATE latin1_spanish_ci NOT NULL,

PRIMARY KEY (ìdDirector`)


AUTO_INCREMENT=6 ;

// Estructura de tabla para la tabla Òficinas`

CREATE TABLE IF NOT EXISTS Òficinas` (

ìdOficina` int(10) unsigned NOT NULL AUTO_INCREMENT,

`ciudad` varchar(50) COLLATE latin1_spanish_ci NOT NULL,


`region` varchar(50) COLLATE latin1_spanish_ci NOT NULL,

`ventasOficina` decimal(19,4) NOT NULL,

òbjetivoVentas` decimal(19,4) NOT NULL,

PRIMARY KEY (ìdOficina`)


AUTO_INCREMENT=6 ;

// Estructura de tabla para la tabla `Pedidos`

CREATE TABLE IF NOT EXISTS `Pedidos` (

ìdPedido` int(10) unsigned NOT NULL AUTO_INCREMENT,

ìdProducto` int(10) unsigned NOT NULL,

ìdCliente` int(10) unsigned NOT NULL,

`fechaPedido` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`cantidad` int(11) NOT NULL,

ìmporte` decimal(19,4) NOT NULL,

PRIMARY KEY (ìdPedido`),

KEY ìdProducto` (ìdProducto`,ìdCliente`),

KEY ìdCliente` (ìdCliente`)


AUTO_INCREMENT=6 ;

// Estructura de tabla para la tabla `Productos`

CREATE TABLE IF NOT EXISTS `Productos` (

ìdProducto` int(10) unsigned NOT NULL AUTO_INCREMENT,


`descripcion` varchar(200) COLLATE latin1_spanish_ci NOT NULL,

`precio` decimal(19,4) NOT NULL,

èxistencias` int(11) NOT NULL,

PRIMARY KEY (ìdProducto`)


AUTO_INCREMENT=6 ;

// Estructura de tabla para la tabla `RepresentanteVentas`

CREATE TABLE IF NOT EXISTS `RepresentanteVentas` (

ìdEmpleado` int(10) unsigned NOT NULL AUTO_INCREMENT,

ìdOficina` int(10) unsigned NOT NULL,

ìdDirector` int(10) unsigned NOT NULL,

`nombreEmpleado` varchar(50) COLLATE latin1_spanish_ci NOT NULL,

èdad` tinyint(4) NOT NULL,

`titulo` varchar(5) COLLATE latin1_spanish_ci NOT NULL,

`fechaContratacion` date NOT NULL,

`cuotaPrevista` decimal(19,4) NOT NULL,

`ventasEmpleado` decimal(19,4) NOT NULL,

PRIMARY KEY (ìdEmpleado`),

KEY ìdOficina` (ìdOficina`,ìdDirector`),

KEY ìdDirector` (ìdDirector`)


AUTO_INCREMENT=6 ;


// Estructura de tabla para la tabla `RepresentanteVentas`

Relaciones entre tablas.

Se deben establecer llaves e indices para realizar las relaciones en la base de datos. Las

relaciones se especifican de la siguiente manera:

Filtros para la tabla `Pedidos`

ALTER TABLE `Pedidos`

ADD CONSTRAINT `Pedidos_ibfk_2` FOREIGN KEY (ìdCliente`) REFERENCES

`Clientes` (ìdCliente`) ON DELETE NO ACTION ON UPDATE NO ACTION,

ADD CONSTRAINT `Pedidos_ibfk_1` FOREIGN KEY (ìdProducto`) REFERENCES

`Productos` (ìdProducto`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Filtros para la tabla `RepresentanteVentas`

ALTER TABLE `RepresentanteVentas`

ADD CONSTRAINT `RepresentanteVentas_ibfk_2` FOREIGN KEY (ìdDirector`)

REFERENCES `Directores` (ìdDirector`) ON DELETE NO ACTION ON UPDATE NO

ACTION,

ADD CONSTRAINT `RepresentanteVentas_ibfk_1` FOREIGN KEY (ìdOficina`)

REFERENCES Òficinas` (ìdOficina`) ON DELETE NO ACTION ON UPDATE NO

ACTION;


Insercin de los registros en las tablas por medio de cdigo SQL.

Ya con las tablas creadas, procedemos a insertar valores en las mismas. He aqu las

sentencias usadas para poblar las tablas con cinco registros cada una.

// INSERTAR Oficinas

INSERT INTO `DBD_U3_EA_JOEC`.Òficinas` (ìdOficina`, `ciudad`, `region`,

`ventasOficina`, òbjetivoVentas`) VALUES (NULL, 'VERACRUZ', 'CENTRO', '', '500000.00'),

(NULL, 'OAXACA', 'SUR', '', '300000.00'), (NULL, 'POZA RICA', NORTE', '', '350000.00'),

(NULL, 'CANCUN', 'OESTE', '', '300000.00'), (NULL, 'COATZACOALCOS', 'CENTRO', '',

'530000.00');

// INSERTAR Directores

INSERT INTO `DBD_U3_EA_JOEC`.`Directores` (ìdDirector`, `nombreDirector`) VALUES

(NULL, 'RAMIRO JIMENEZ'), (NULL, 'FRANCISCO GOMEZ'), (NULL, 'NANCY

HERRERA'), (NULL, 'JOSUE VILLA'), (NULL, 'MARIA DURAN');

// INSERTAR Clientes

INSERT INTO `DBD_U3_EA_JOEC`.`Clientes` (ìdCliente`, `nombreEmpresa`,

`nombreContacto`, `limiteCredito`) VALUES (NULL, 'CSA CONSTRUCTORA', 'MARIO

MARTINEZ', '5000'), (NULL, 'FERRETERIA LA ESPECIAL', 'ANASTASIA HERNANDEZ',

'25000'), (NULL, 'CEMENTOS UNIDOS DEL NORTE', 'FEDERICO CANTU', '60000'),

(NULL, 'LADRILLOS Y CEMENTOS DEL CENTRO', 'MAGDALENA MORIN', '53000'),

(NULL, 'FIERROS Y CONSTRUCCIONES SA', 'MARIA FERNANDEZ', '78000');


// INSERTAR Productos

INSERT INTO `DBD_U3_EA_JOEC`.`Productos` (ìdProducto`, `descripcion`, `precio`,

èxistencias`) VALUES (NULL, 'RUEDA CIRCULAR', '536.00', '783'), (NULL, 'MAQUINA

DEL TIEMPO', '475.5', '1'), (NULL, 'CONJUNTO MUSICAL', '358.5', '21'), (NULL,

'BANDERA DE MEXICO', '1280.60', '58'), (NULL, 'CUBETA CON AGUA', '85.9', '2501');

// INSERTAR RepresentantesVentas

INSERT INTO `DBD_U3_EA_JOEC`.`RepresentanteVentas` (ìdEmpleado`, ìdOficina`,

ìdDirector`, `nombreEmpleado`, èdad`, `titulo`, `fechaContratacion`, `cuotaPrevista`,

`ventasEmpleado`) VALUES (NULL, '1', '1', 'RICARDO', '30', 'SR.', '2008-02-12', '25000',

'15200'), (NULL, '1', '1', 'DIANA', '25', 'LIC.', '2012-12-4', '65000', '78000'), (NULL, '2', '2',

'ANACLETO', '48', 'SR.', '1996-08-21', '10000', '500'), (NULL, '2', '2', 'SELENE', '35', 'ING.',

'2010-03-04', '15000', '32600'), (NULL, '3', '3', 'LUCIA', '21', 'SRA.', '2006-09-11', '25000',

'1500');

// INSERTAR Pedidos

INSERT INTO `Pedidos` (ìdPedido`, ìdProducto`, ìdCliente`, `fechaPedido`, `cantidad`,

ìmporte`) VALUES (1, 1, 1, '2014-04-30 18:13:25', 2, 1072.0000), (2, 3, 1, '2014-04-30

18:13:25', 2, 7170.0000), (3, 4, 1, '2014-04-30 18:13:25', 10, 12806.0000), (4, 5, 2, '2014-04-30

18:13:25', 2500, 2147500.0000), (5, 2, 2, '2014-04-30 18:13:25', 1, 475.5000);


Consultas de prueba.

Para terminar, realizar 10 consultas que obtienen informacin de la base de datos:

Listar el contenido de las tablas: Clientes, Directores, Oficinas, Productos y

RepresentanteVentas.

SELECT * FROM Directores;

SELECT * FROM Clientes WHERE limiteCredito > 55000;

SELECT * FROM Oficinas WHERE `region` NOT LIKE 'CENTRO';

SELECT * FROM Productos WHERE existencias < 40;

SELECT * FROM `RepresentanteVentas` WHERE `ventasEmpleado`>= `cuotaPrevista`;

Listar a los empleados relacionados por oficina.

SELECT Òficinas`.*,`RepresentanteVentas`.* FROM Oficinas JOIN

`DBD_U3_EA_JOEC`.`RepresentanteVentas` ON Òficinas`.ìdOficina` =

`RepresentanteVentas`.ìdOficina`

Listar a los empleados relacionados por director.

SELECT `Directores`.*,`RepresentanteVentas`.* FROM Directores JOIN

`DBD_U3_EA_JOEC`.`RepresentanteVentas` ON `Directores`.ìdDirector` =

`RepresentanteVentas`.ìdDirector`

Listar al empleado y oficina con mayores ventas:

SELECT `nombreEmpleado`, max(`ventasEmpleado`) FROM `RepresentanteVentas`;

SELECT `ciudad`, `region`, max(`ventasOficina`) FROM Òficinas`;

Listado de pedidos :

SELECT `Productos`. `descripcion`, `Productos`. `precio`,`Clientes`. `nombreEmpresa`,


`Clientes`. `nombreContacto`, `Pedidos`.`fechaPedido`,`Pedidos`.`cantidad`,`Pedidos`.ìmporte`

FROM Pedidos JOIN `DBD_U3_EA_JOEC`.`Clientes` ON `Pedidos`.ìdCliente` =

`Clientes`.ìdCliente` JOIN `DBD_U3_EA_JOEC`.`Productos` ON `Pedidos`.ìdProducto` =

`Productos`.ìdProducto`


Conclusin.

A primera vista, el trabajo parece titnico: crear la base de datos con relaciones y datos

que sean coherentes. Pero aunque si es un trabajo laborioso, lo es solo al inicio. Una vez que el

diseo y la estructura de la base de datos ha sido terminada, esta puede funcionar para cualquier

numero de datos que se le introduzcan.

Al final del da, la labor del administrador de bases de datos, consta de dar mantenimiento

y generar consultas para obtener informacin.

Los sistemas gestores de bases de datos, siguen estos principios de diseo y son

herramientas que ayudan a preservar la informacion de forma coherente. Aun cuando desconozco

la mayora de los DBMS, estoy casi seguro que podre aprender a usarlos ya que si siguen el

modelo relacional, es casi seguro que los mecanismos van enfocados a ese modelo y por lo tanto

su aprendizaje no debera ser difcil.


Bibliografa.

UNAD (2014), Ingeniera en desarrollo de software, Programa de la asignatura. Mxico:

Universidad abierta y a distancia de Mxico.

Cuadra D., Castro E., Iglesias A. M., Mrtinez P., Calle F. J., de Pablo C,..., Moreno L. (2008,

abril) Desarrollo de bases de datos: casos prcticos desde el anlisis a la

implementacin. (Primera Edicin), Mxico: Alfaomega.

Prez C. (2008, marzo), MySQL para Windows y Linux, (Segunda edicin), Mxico: Alfaomega.

dbd_u3_ea_joec

Documents