base de datos 7 8 9 2008 v2012 i

BASE DE DATOS 6- Diseo de una Base de Datos El diseo de una base de datos es un proceso complejo que involucra decisiones en diferentes niveles. Esta complejidad se simplifica descomponiendo el proceso en fases, aplicando mtodos y tcnicas especficas. IMPORTANTE: Hasta ahora no se habia indicado cuales son los mtodos y tcnicas de como crear una base de datos desde cero para una empresa u organizacion, si bien hemos manejado SQL Server es solo para conocer y aplicar los conceptos iniciales. Hasta el momento tenemos los principios de como usar un SGBD, no debes confundirte y pensar que ya sabes disear bases de datos, en los ejercicios que se han realizado nos daban una base de datos ya modelada y teniamos que pasarlo a SQL Server As el diseo de la base de datos se descompone en las siguientes fases: Diseo Conceptual Se inicia con la labor de levantamiento de informacin de la organizacin, para luego realizar la especificacin de requerimientos del usuario. El resultado de este trabajo ser el esquema conceptual de la base de datos. Es una descripcin de muy alto nivel de la estructura de la base de datos, independiente del SGBD que se use posteriormente para manipularla. nicamente describe cuales son los diferentes elementos de informacin dentro de una organizacin y como se relacionan estos. No es relevante centrarse en la forma como se estructura su almacenamiento.

Diseo Lgico Empieza el trabajo tomando como base el esquema conceptual y terminado la labor debe generarse el esquema lgico. Un esquema lgico es una descripcin de la estructura de la base de datos que puede ser procesada por un SGBD

Diseo Fsico Parte del esquema lgico para obtenerse luego un esquema fsico. Un esquema fsico es una descripcin de la implantacin de una base de datos en algn medio de almacenamiento; describe las estructuras de almacenamiento y los mtodos usados para tener un acceso efectivo a los datos. Por esta razn un diseo fsico est firmemente atado a un SGBD especfico. El esquema fsico se expresa mediante el uso del lenguaje de definicin de datos del SGBD elegido, as la base de datos puede ser creada y probada.Figura 6.1 Etapas en el diseo de una Base de Datos Recopilacin y edicin: Ing. CARLOS BAYES ANTUNEZ

1

BASE DE DATOS 6.1 Modelo de Datos Modelo de Datos Clasificacin

Es una coleccin de herramientas conceptuales para describir los datos, las relaciones que existen entre ellos, semntica asociada a los datos y restricciones de consistencia.

Modelos de alto nivel o conceptuales: Orientados a la descripcin de estructuras de datos y restricciones de integridad. Se usan fundamentalmente durante la etapa de Anlisis de un problema dado y estn orientados a representar los elementos que intervienen en ese problema y sus relaciones. El ejemplo ms tpico es el Modelo EntidadRelacin. Actualmente tambien se usa los Diagramas UML (Lenguaje Unificado de Modelado).

Modelos de implementacin o lgicos Orientados a las operaciones ms que a la descripcin de una realidad. Usualmente estn implementados en algn Manejador de Base de Datos. El ejemplo ms tpico es el Modelo Relacional, que cuenta con la particularidad de contar tambin con buenas caractersticas conceptuales (Normalizacin de bases de datos).

Modelos de Bajo nivel o fsicos Describen como se almacenan los datos en el computador al representar informacin como los formatos, ordenamientos de los registros que hacen mas eficiente el acceso, etc.

Figura 3.2 Modelo de Datos IMPORTANTE: En el curso trabajaremos de la siguiente manera: Modelo de alto nivel o Conceptuales: Se indicar la parte terica del modelo entidad/relacin y se implementara haciendo uso de la herramienta ERwin, que es un programa de diseo de Base de Datos. Aclaramos que en esta etapa para el modelado E/R se puede usar incluso papel por ser la etapa incial. Adems la herramienta ERwin no es el nico de su tipo ya que existen otros programas similares: Power Designer, Rational Rose, etc. Modelo de Implementacin o Lgicos: Se indicar la parte terica del Modelo relacional: la normalizacin; posteriormente se implementara en SQL Server 6.1.1 Sublenguajes de un Modelo de Datos Un modelo de datos es un lenguaje orientado a describir una Base de Datos, tpicamente, tiene dos sublenguajes: Un Lenguaje de Definicin de Datos o DDL (Data definition Language), orientado a describir de una forma abstracta las estructuras de datos y las restricciones de integridad. Un Lenguaje de Manipulacin de Datos o DML (Data Manipulation Language), orientado a describir las operaciones de manipulacin de los datos.

A la parte del DML orientada a la recuperacin de datos, usualmente se le llama Lenguaje de Consulta o QL (Query Language). (Repase el captulo 2.3) .

Recopilacin y edicin: Ing. CARLOS BAYES ANTUNEZ

2

BASE DE DATOS 6.1.2 Recomendaciones para crear atributos/campos

En lo posible utilizar atributos/campos para cada dato en individual En vez de un campo llamado nombres, crear los campos: Nombre1, Nombre2, ApellidoPat, ApellidoMat Con datos numericos que son el resultado de operaciones con fechas como edad, aos de servicio, etc. , crear campos que almacenen las fechas que intervienen en la operacin y si en la operacin se usa la fecha actual, no es necesario crear un campo para la fecha actual.

En vez de crear un campo llamado edad, crear el campo : fechaNacimiento En vez de crear un campo TiempoDeServicio, crear el campo fechaContratacion

Si ningn campo reune las condiciones de ser clave principal, crear un nuevo campo para la clave y es recomendable que sea de tipo entero y adems que sea identidad.

En las clinicas y/o centros medicos no les sirve el DNI para identificar a los pacientes ya que no todos lo tienen, entonces se crea de forma intencional un campo que los identifique: NumeroHistoriaClinica Int Identity

Evite crear campos que sean el resultado de la operacin de otros campos Se tiene el campo PrecioProducto y el campo CantidadComprada, esta dems crear un campo adicional llamado: MontoTotalCompra (MontoTotalCompra=PrecioProducto*CantidadComprada)

Si existe un dato que ser nico y/o su valor no cambiar (constante), entonces no ser necesario crear un campo para ese dato.

En una tabla que guardar datos de un recibo de compra se crea los campos: PrecioProducto, CantidadComprada pero no se debe crear un campo para el IGV (impuesto general a las ventas), ya que es un valor que sera constante para todos los registros.

Si se desea guardar constantes y/o valores que pocas veces cambiaran su contenido y que se usaran en operaciones con otros campos es mejor tenerlos en una tabla aparte. Ejemplo se crea una tabla adicional llamada Tasas que contiene todos los valores de los impuestos: IGV, AporteSeguroSocial, AporteAFPIntegra.

Dentro de una tabla existen campos que permiten: clasificar o indicar que pertenecen a un grupo y/o categoria, en ciertos casos sera mejor sacar ese campo y crearle su propia tabla. Tenemos la tabla productos, pero un producto puede ser clasificado en diferentes categorias: bebida, producto de limpieza, ropa, etc. Primera solucion es crear una tabla llamada productos con los campos: CodigoProducto, nombreProducto, NombreCategoria , precioUnitario, etc Segunda solucin es crear dos tablas una llamada Productos con los campos: CodigoProducto, nombreProducto, precioUnitario, etc, la otra tabla sera llamada Categorias con los campos: CodigoCategoria, NombreCategoria. Recopilacin y edicin: Ing. CARLOS BAYES ANTUNEZ

3

BASE DE DATOS 6.1.3 Recomendaciones para elegir el Tipo de Dato para un campo Los campos que sern claves principales pueden ser nmeros enteros o caracteres de longitud fija NumeroHistoriaClinica: Int, es correcto NumeroHistoriaClinica: Char(7) es correcto DNI: Char(8), es correcto DNI: Varchar(8), incorrecto Si con un dato se debe hacer operaciones entonces es un nmero Telefono: Char(9), es caracter ya que no multiplicamos ni dividimos nmeros telefnicos Un dato cuyos valores contengan dgitos no necesariamente ser un nmero El "nmero" de DNI si bien esta formado digitos, es un caracter ya que no realizamos operaciones con los nmeros de DNI Si existe un campo que ser clave principal en lo posible que sea numero entero y/o adems identidad Si bien no realizamos operaciones con clave principal como el campo NumeroHistoriaClinica: Int, en realidad al momento de asignar a un paciente su clave esta sera de forma secuencial: el primer paciente sera 1, el siguiente 2, ... notemos que hay una operacion de suma de 1 en 1 y es la unica operacion que se realiza con NumeroHistoriaClinica Si un campo admite solo dos valores o alternativas entonces elegir el tipo de dato bit o lgico. Sexo: Bit EsAlergico: Bit EstaVivo: Bit


4

BASE DE DATOS 7 Modelo Entidad Relacin Generalmente todo modelo tiene una representacin grfica, para el caso de datos el modelo ms popular es el modelo entidad-relacin o diagrama E/R. Se denomina as debido a que precisamente permite representar relaciones entre entidades (objetivo del modelado de datos).

Entidades Modelo Entidad Relacion E-R Representa a la realidad a traves de:

Atributos

Relaciones

Cardinalidad

Llaves Entidades, que son objetos que existen y que es capaces de ser descritos (sustantivo), se distinguen de otros por sus caractersticas, por ejemplo: un libro concreto de un autor, un alumno el cual se distingue de otro por sus caractersticas particulares como lo es el nombre, o el nmero de control asignado al entrar a una institucin educativa, as mismo, un empleado, una materia, etc. Las entidades pueden ser de dos tipos:

Tangibles: Son todos aquellos objetos fsicos que podemos ver, tocar o sentir. Entidades Se clasifican en:

Intangibles: Todos aquellos eventos u objetos conceptuales que no podemos ver, aun sabiendo que existen, por ejemplo: la entidad materia, sabemos que existe, sin embargo, no la podemos visualizar o tocar.

Atributo Realiza una de tres cosas:

Atributos: Es una caracterstica (adjetivo) de una entidad Por ejemplo: - el nombre, direccin telfono, grado, grupo, etc. son atributos de la entidad alumno - Clave, nmero de seguro social, departamento, etc., son atributos de la entidad empleado. - ttulo, ISBN, edicin, nmero de pginas son atributos de la entidad libro. - nombre, apellidos, seudnimo, fecha de nacimiento, son atributos ee la entidad autor

Identificar

Relacionar

Describir


5

BASE DE DATOS Clave o llave Para elegirlo de acuerdo a estas normas: Clave o llave: De entre los atributos habr uno o un conjunto de ellos que no se repite; a este atributo o conjunto de atributos llamado clave o llave de la entidad: Para la entidad persona una clave seria nmero de DNI En toda entidad siempre hay al menos una clave que en el peor de los casos estar formada por todos los atributos de la entidad. Ya que puede haber varias claves y necesitamos elegir una, lo haremos atendiendo a estas normas:

Que sea nica.

Que se tenga pleno conocimiento de ella.Por qu en las empresas se asigna cada cliente un nmero de cliente?

Que sea mnima, ya que ser muy utilizada por el gestor de base de datos.

Relaciones (cardinalidad)

A su vez una entidad se puede asociar o relacionar con ms entidades a travs de relaciones. Relacin: Asociacin entre entidades, sin existencia Relaciones 1-1.- Las entidades que propia en el mundo real que estamos modelando, pero intervienen en la relacin se asocian una necesaria para reflejar las interacciones existentes a una (Ej.: la entidad HOMBRES, la entidad MUJERES y entre ellos la entre entidades. relacin MATRIMONIO). Caractersticas delas relaciones: Grado: Numero de tipos de entidades que participan en la relacin Cardinalidad: Numero de elementos de un tipo Relaciones 1-n.- Una ocurrencia de una que se conectan con un elemento de otro entidad est asociada con muchas (n) de otra (Ej.: la entidad EMPRESA, la (restriccin que se observa en el dominio del entidad TRABAJADOR y entre ellos la problema y que controla las ocurrencias de las relacin TRABAJAR-EN). relaciones)

Relaciones n-n.- Cada ocurrencia, en cualquiera de las dos entidades de la relacin, puede estar asociada con muchas (n) de la otra y viceversa (Ej.: la entidad MEDICO, la entidad PACIENTE y entre ellos la relacin ATENCION).

Diseo de una base de datos Hay cuatro pasos para disear una base de datos usando el modelo Entidad-Relacin: a. Identificar los conjuntos de entidades y los conjuntos de relaciones de inters.

b. Identificar la informacin semntica en los conjuntos de relaciones, tales como si un cierto conjunto de relaciones es una proyeccin 1:N.

c. Definir los dominios y los atributos.

d. Organizar la informacin dentro del Diagrama Entidad-Relacin, y decidir las claves primarias.


6

BASE DE DATOS Pero para entender mejor esto, veamos un ejemplo: Consideremos una empresa que requiere controlar a los vendedores y las ventas que ellos realizan; de este problema determinamos que los objetos o entidades principales a estudiar son el empleado (vendedor) y el artculo (que es el producto en venta), y las caractersticas que los identifican son: Empleados Nombre Puesto Salario DNI Artculos Descripcin costo clave

La relacin entre ambas entidades la podemos establecer como Venta. Para representar un modelo E-R grficamente, se emplean smbolos, los cuales son: 1- Diagrama Entidad / Relacin (notacin tradicional) As nuestro ejemplo anterior quedara representado de la siguiente forma (notacin tradicional):Puesto Salario Descripcin

Nombre

DNI

Clave

Costo

Empleados

N:M Venta

Artculos

2- Diagrama Entidad / Relacin (notacin UML) As nuestro ejemplo anterior quedara representado de la siguiente forma (notacin UML):Empleados DNI Nombre Puesto Salario Artculos

Venta

*E/R Clsico dirige

*

Clave Descripcin Costo

Relacin uno a uno

Profesores

1

1

Departamentos

Notacin UML dirige Profesores 1 1 Departamentos


7

BASE DE DATOS Relacin muchos a uno E/R Clsicopertenece A

Profesores

*

1

Departamentos

Notacin UML perteneceA Profesores

*

1

Departamentos

Relacin muchos a muchos E/R Clsicoensea

Profesores

*

*

Escuelas

Notacin UML ensea Profesores Escuelas

*

*

Cardinalidad mnima en una relacin E/R Clsicoes titular de

Cuentas

0..*

1..*

Clientes

Notacin UML es titular de Clientes

Cuentas

0..*Relacin opcional Un cliente puede o no ser titular de una cuenta

1..*Relacin obligatoria Una cuenta ha de tener un titular como mnimo

Relaciones involutivas Relacion de un tipo consigo mismo . E/R Clsico

Empleados 1

*dirige

1 Personas 1 casada con


8

BASE DE DATOS Notacin UML

Empleados 1

*dirige

1 Personas 1 casada con

Atributos E/R clsico

Notacin UML

Relaciones de especializacin y generalizacin Atributos y claves Las subtipos heredan los atributos de los supertipos: los subtipos poseen todos los atributos del supertipo ms algunos propios. La clave primaria de los subtipos es la clave primaria del supertipo. Profesores

Empleados

Contadores


9

BASE DE DATOS Ejercicio 1 A partir del siguiente enunciado se desea realiza el modelo entidad-relacin: Una empresa vende productos a varios clientes. Se necesita conocer los datos personales de los clientes (nombres, apellidos, DNI, direccin y fecha de nacimiento). Cada producto tiene un nombre y un cdigo, as como un precio unitario. Un cliente puede comprar varios productos a la empresa, y un mismo producto puede ser comprado por varios clientes. Los productos son suministrados por diferentes proveedores. Se debe tener en cuenta que un producto slo puede ser suministrado por un proveedor, y que un proveedor puede suministrar diferentes productos. De cada proveedor se desea conocer el RUC, nombre y direccin. Solucin Si bien en el enunciado nos da para la entidad clientes el atributo llamado apellidos, debemos acostumbrarnos a poner cada apellido como un atributo diferente.ApellidoPat ApellidoMat NombreProd

NombreCli

DireccinCli

CodigoProd

PrecioUnit

0:M FechaNac Clientes

N:M Compra

0:M Productos

1:M 1:1 Proveedores

1:M Suministra

RUC

NombrePro

DireccinPro

Ejercicio 2 A partir del siguiente enunciado se desea realizar el modelo entidad-relacin. Se desea informatizar la gestin de una empresa de transportes que reparte paquetes por el pas. Los encargados de llevar los paquetes son los camioneros, de los que se quiere guardar el DNI, nombre, telfono, direccin, salario, distrito en que vive. De los paquetes transportados interesa conocer el cdigo de paquete, descripcin, destinatario y direccin del destinatario. Un camionero distribuye muchos paquetes, y un paquete slo puede ser distribuido por un camionero. De las provincias a las que llegan los paquetes interesa guardar el cdigo de provincia y el nombre. Un paquete slo puede llegar a una provincia. Sin embargo, a una provincia pueden llegar varios paquetes. De los camiones que llevan los camioneros, interesa conocer la matrcula, modelo, tipo y potencia. Un camionero puede conducir diferentes camiones en fechas diferentes, y un camin puede ser conducido por varios camioneros. Solucin


10

BASE DE DATOSDireccionDest DNIDest telefonoCamionero DireccionCamionero

Destinatario

COdProvincia

DNICamionero

Salario

0:N CodPaquete Paquetes

N:M Reparte

1:M Camioneros

DistritoCamionero

0:M

0:N

1:M Llega 0:M

N:M Maneja

1;1

Provincias

Camiones

CodProvincia

Nombre

Matricula

Modelo

Tipo

Ejercicio 3 Base de datos de un instituto A partir del siguiente enunciado disear el modelo entidad-relacin. Se desea disear la base de datos de un Instituto. En la base de datos se desea guardar los datos de los profesores del Instituto (DNI, nombre, direccin y telfono). Los profesores imparten mdulos, y cada mdulo tiene un cdigo y un nombre. Cada alumno est matriculado en uno o varios mdulos. De cada alumno se desea guardar el n de Expediente, nombre, apellidos y fecha de nacimiento. Los profesores pueden impartir varios mdulos, pero un mdulo slo puede ser impartido por un profesor. Cada mdulo tiene un grupo de alumnos, uno de los cuales es el delegado del grupo. Solucin Aqu tenemos un caso de relacin recursiva en una tabla. Un grupo (varios alumnos) tiene un delegado, y un alumno es delegado de un grupo de alumnos (varios alumnos). Notamos que hay una relacin de uno a varios en la Entidad/tabla Alumnos consigo mismo. El tipo de relacin es de uno a varios.


11

BASE DE DATOSDireccionProf TelefonoProf nombreMod

NombreProf

CodModulo

1:1 DNIProfesor Profesores

N:M Imparte

1:M Modulos

1:N

NroExped 1:M

N:M Tiene

1:MNombre 1:1 ApellidoPat 1:M Representa Alumnos

Ejercicio 4 Concesionario de coches A un concesionario de coches llegan clientes para comprar automviles. De cada coche interesa saber la matrcula, modelo, marca y color. Un cliente puede comprar varios coches en el concesionario. Cuando un cliente compra un coche, se le hace una cha en el concesionario con la siguiente informacin: dni, nombre, apellidos, direccin y telfono. Los coches que el concesionario vende pueden ser nuevos o usados (de segunda mano). De los coches nuevos interesa saber el nmero de unidades que hay en el concesionario. De los coches viejos interesa el nmero de kilmetros que lleva recorridos. El concesionario tambin dispone de un taller en el que los mecnicos reparan los coches que llevan los clientes. Un mecnico repara varios coches a lo largo del da, y un coche puede ser reparado por varios mecnicos. Los mecnicos tienen un dni, nombre, apellidos, fecha de contratacin y salario. Se desea guardar tambin la fecha en la que se repara cada vehculo y el nmero de horas que se tardado en arreglar cada automvil. Solucin


12

BASE DE DATOS

DireccionClient

TelefonoClient

Modelo

NombreCliente

Matricula

1:1 DNICliente Clientes

N:M compra

1:M Coches

DNIMecanico 1:N NombreMEc Mecnicos N:M Revisa

1:M

FEchaCont CochesNuevos CochesUsados

NroUnidades

Kilometraje

Ejercicicio 5 Biblioteca En la biblioteca del centro se manejan fichas de autores y libros. En la ficha de cada autor se tiene el cdigo de autor y el nombre. De cada libro se guarda el cdigo, ttulo, ISBN, editorial y nmero de pgina. Un autor puede escribir varios libros, y un libro puede ser escrito por varios autores. Un libro est formado por ejemplares. Cada ejemplar tiene un cdigo y una localizacin. Un libro tiene muchos ejemplares y un ejemplar pertenece slo a un libro. Los usuarios de la biblioteca del centro tambin disponen de ficha en la biblioteca y sacan ejemplares de ella. De cada usuario se guarda el cdigo, nombre, direccin y telfono. Los ejemplares son prestados a los usuarios. Un usuario puede tomar prestados varios ejemplares, y un ejemplar puede ser prestado a varios usuarios. De cada prstamos interesa guardar la fecha de prstamo y la fecha de devolucin. Solucin


13

BASE DE DATOSTitulo

NombreAutor

CodLibro

1:N CodAutor Autores

N:M Escribe

1:M Libros 1:1

CodUsuario 1:N NombreUsuar Usuarios N:M Prstamo

1:M Tiene 1:M Ejemplares 1:M

TelefonoUsuar

FechaPrestamo

FechaDevolucion CodEjemplar Localizacion


14

BASE DE DATOS Cuestionario 1

Cree el modelo entidad relacin para los siguientes enunciados usando notacin Clsica y notacin UML 1- Empresa de Venta de automoviles A partir del siguiente supuesto disear el modelo entidad-relacin: Se desea disear una base de datos para almacenar y gestionar la informacin empleada por una empresa dedicada a la venta de automviles, teniendo en cuenta los siguientes aspectos: La empresa dispone de una serie de coches para su venta. Se necesita conocer la matrcula, marca y modelo, el color y el precio de venta de cada coche. Los datos que interesa conocer de cada cliente son el NIF, nombre, direccin, ciudad y nmero de telfono: adems, los clientes se diferencian por un cdigo interno de la empresa que se incrementa automticamente cuando un cliente se da de alta en ella. Un cliente puede comprar tantos coches como desee a la empresa. Un coche determinado solo puede ser comprado por un nico cliente. El concesionario tambin se encarga de llevar a cabo las revisiones que se realizan a cada coche. Cada revisin tiene asociado un cdigo que se incrementa automticamente por cada revisin que se haga. De cada revisin se desea saber si se ha hecho cambio de filtro, si se ha hecho cambio de aceite, si se ha hecho cambio de frenos u otros. Los coches pueden pasar varias revisiones en el concesionario. 2- Clinica A partir del siguiente supuesto disear el modelo entidad-relacin: La clnica SAN PATRS necesita llevar un control informatizado de su gestin de pacientes y mdicos. De cada paciente se desea guardar el cdigo, nombre, apellidos, direccin, poblacin, provincia, cdigo postal, telfono y fecha de nacimiento. De cada mdico se desea guardar el cdigo, nombre, apellidos, telfono y especialidad. Se desea llevar el control de cada uno de los ingresos que el paciente hace en el hospital. Cada ingreso que realiza el paciente queda registrado en la base de datos. De cada ingreso se guarda el cdigo de ingreso (que se incrementar automticamente cada vez que el paciente realice un ingreso), el nmero de habitacin y cama en la que el paciente realiza el ingreso y la fecha de ingreso. Un mdico puede atender varios ingresos, pero el ingreso de un paciente solo puede ser atendido por un nico mdico. Un paciente puede realizar varios ingresos en el hospital. 3- Tienda Informtica Se desea informatizar la gestin de una tienda informtica. La tienda dispone de una serie de productos que se pueden vender a los clientes. De cada producto informtico se desea guardar el cdigo, descripcin, precio y nmero de existencias. De cada cliente se desea guardar el cdigo, nombre, apellidos, direccin y nmero de telfono. Un cliente puede comprar varios productos en la tienda y un mismo producto puede ser comprado por varios clientes. Cada vez que se compre un artculo quedar registrada la compra en la base de datos junto con la fecha en la que se ha comprado el artculo. La tienda tiene contactos con varios proveedores que son los que suministran los productos. Un mismo producto puede ser suministrado por varios proveedores. De cada proveedor se desea guardar el cdigo, nombre, apellidos, direccin, provincia y nmero de telfono. 4- Hijos de una Persona Considera la siguiente relacin PERSONA-TIENE HIJOS-PERSONA. Una persona puede tener muchos hijos/as o ninguno. Una persona siempre es hijo/a de otra persona. Los atributos de la persona son dni, nombre, direccin y telfono. 5- Liga de futbol profesional La liga de ftbol profesional, presidida por Don ngel Mara Villar, ha decidido informatizar sus instalaciones creando una base de datos para guardar la informacin de los partidos que se juegan en la liga. Se desea guardar en primer lugar los datos de los jugadores. De cada jugador se quiere guardar el nombre, fecha de nacimiento y posicin en la que juega (portero, defensa, centrocampista...). Cada jugador tiene un cdigo de jugador que lo identica de manera nica. De cada uno de los equipos de la liga es necesario registrar el nombre del equipo, nombre del estadio en el que juega, el aforo que tiene, el ao de fundacin del equipo y la ciudad de la que es el equipo. Cada equipo tambin tiene un cdigo que lo identica de manera nica. Un jugador solo puede pertenecer a un nico equipo. De cada partido que los equipos de la liga juegan hay que registrar la fecha en la que se juega el partido, los goles que ha metido el equipo de casa y los goles que ha metido el equipo de fuera. Cada partido tendr un cdigo numrico para identicar el partido. Tambin se quiere llevar un recuento de los goles que hay en cada partido. Se quiere almacenar el minuto en Recopilacin y edicin: Ing. CARLOS BAYES ANTUNEZ

15

BASE DE DATOS el que se realizar el gol y la descripcin del gol. Un partido tiene varios goles y un jugador puede meter varios goles en un partido. Por ltimo se quiere almacenar, en la base de datos, los datos de los presidentes de los equipos de ftbol (dni, nombre, apellidos, fecha de nacimiento, equipo del que es presidente y ao en el que fue elegido presidente). Un equipo de ftbol tan slo puede tener un presidente, y una persona slo puede ser presidente de un equipo de la liga. 6- Gestin de carreras de Institucin Educativa A partir del siguiente supuesto disear el modelo entidad-relacin. Se desea informatizar la gestin de un centro de enseanza para llevar el control de los alumnos matriculados y los profesores que imparten clases en ese centro. De cada profesor y cada alumno se desea recoger el nombre, apellidos, direccin, poblacin, DNI, fecha de nacimiento, cdigo postal y telfono. Los alumnos se matriculan en una o ms asignaturas, y de ellas se desea almacenar el cdigo de asignatura, nombre y nmero de horas que se imparten a la semana. Un profesor del centro puede impartir varias asignaturas, pero una asignatura slo es impartida por un nico profesor. De cada una de las asignaturas se desea almacenar tambin la nota que saca el alumno y las incidencias que puedan darse con l. Adems, se desea llevar un control de los cursos que se imparten en el centro de enseanza. De cada curso se guardar el cdigo y el nombre. En un curso se imparten varias asignaturas, y una asignatura slo puede ser impartida en un nico curso. Las asignaturas se imparten en diferentes aulas del centro. De cada aula se quiere almacenar el cdigo, piso del centro en el que se encuentra y nmero de pupitres de que dispone. Una asignatura se puede dar en diferentes aulas, y en un aula se pueden impartir varias asignaturas. Se desea llevar un registro de las asignaturas que se imparten en cada aula. Para ello se anotar el mes, da y hora en el que se imparten cada una de las asignaturas en las distintas aulas. La direccin del centro tambin designa a varios profesores como tutores en cada uno de los cursos. Un profesor es tutor tan slo de un curso. Un curso tiene un nico tutor. Se habr de tener en cuenta que puede que haya profesores que no sean tutores de ningn curso. 7- Organizacin interna de una empresa Una empresa necesita organizar la siguiente informacin referente a su organizacin interna. La empresa est organizada en una serie de departamentos. Cada departamento tiene un cdigo, nombre y presupuesto anual. Cada departamento est ubicado en un centro de trabajo. La informacin que se desea guardar del centro de trabajo es el cdigo de centro, nombre, poblacin y direccin del centro. La empresa tiene una serie de empleados. Cada empleado tiene un telfono, fecha de alta en la empresa, RUC y nombre. De cada empleado tambin interesa saber el nmero de hijos que tiene y el salario de cada empleado. A esta empresa tambin le interesa tener guardada informacin sobre los hijos de los empleados. Cada hijo de un empleado tendr un cdigo, nombre y fecha de nacimiento. Se desea mantener tambin informacin sobre las habilidades de los empleados (por ejemplo, mercadotecnia, trato con el cliente, fresador, operador de telefona, etc). Cada habilidad tendr una descripcin y un cdigo. Sobre este supuesto disear el modelo E/R y el modelo relacional teniendo en cuenta los siguientes aspectos. Un empleado est asignado a un nico departamento. Un departamento estar compuesto por uno o ms empleados. Cada departamento se ubica en un nico centro de trabajo. Estos se componen de uno o ms departamentos. Un empleado puede tener varios hijos. Un empleado puede tener varias habilidades, y una misma habilidad puede ser poseda por empleados diferentes. Un centro de trabajo es dirigido por un empleado. Un mismo empleado puede dirigir centros de trabajo distintos.

8- Control de accidentes y multas El municipio ha solicitado una base de datos mediante la cual llevar un control de los accidentes y las multas. Tras una serie de entrevistas, has tomado las siguientes notas: Se desean registrar todas las personas que tienen un vehculo. Es necesario guardar los datos personales de cada persona (nombre, apellidos, direccin, distrito, telfono y DNI). De cada vehculo se desea almacenar la matrcula, la marca y el modelo. Una persona puede tener varios vehculos, y puede darse el caso de un vehculo pertenezca a varias personas a la vez. Tambin se desea incorporar la informacin destinada a gestionar los accidentes del municipio. Cada accidente posee un nmero de referencia correlativo segn orden de entrada a la base de datos. Se desea conocer la fecha, lugar y hora en que ha tenido lugar cada accidente. Se debe tener en cuenta que un accidente puede involucrar a varias personas y varios vehculos. Se desea llevar tambin un registro de las multas que se aplican. Cada multa tendr asignado un nmero de referencia correlativo. Adems, deber registrarse la fecha, hora, lugar de infraccin e importe de la misma. Una multa solo se aplicar a un conductor e involucra a un solo vehculo. Recopilacin y edicin: Ing. CARLOS BAYES ANTUNEZ

16

BASE DE DATOS 9- Agencia de Viajes Una agencia de viajes desea informatizar toda la gestin de los viajeros que acuden a la agencia y los viajes que estos realizan. Tras ponernos en contacto con la agencia, sta nos proporciona la siguiente informacin. La agencia desea guardar la siguiente informacin de los viajeros: dni, nombre, direccin y telfono. De cada uno de los viajes que maneja la agencia interesa guardar el cdigo de viaje, nmero de plazas, fecha en la que se realiza el viaje y otros datos. Un viajero puede realizar tantos viajes como desee con la agencia. Un viaje determinado slo puede ser cubierto por un viajero. Cada viaje realizado tiene un destino y un lugar de origen. De cada uno de ellos se quiere almacenar el cdigo, nombre y otros datos que puedan ser de inters. Un viaje tiene un nico lugar de destino y un nico lugar de origen. Realizar el modelo E-R y pasarlo al modelo de datos relacional. Realizar el modelo E/R en ERwin 10- Control de proyectos Una empresa desea disear una base de datos para almacenar en ella toda la informacin generada en cada uno de los proyectos que sta realiza. De cada uno de los proyectos realizados interesa almacenar el cdigo, descripcin, cuanta del proyecto, fecha de inicio y fecha de fin. Los proyectos son realizados por clientes de los que se desea guardar el cdigo, telfono, domicilio y razn social. Un cliente puede realizar varios proyectos, pero un solo proyecto es realizado por un nico cliente. En los proyectos participan colaboradores de los que se dispone la siguiente informacin: RUC, nombre, domicilio, telfono, banco y nmero de cuenta. Un colaborador puede participar en varios proyectos. Los proyectos son realizados por uno o ms colaboradores. Los colaboradores de los proyectos reciben pagos. De los pagos realizados se quiere guardar el nmero de pago, concepto, cantidad y fecha de pago. Tambin interesa almacenar los diferentes tipos de pagos que puede realizar la empresa. De cada uno de los tipos de pagos se desea guardar el cdigo y descripcin. Un tipo de pago puede pertenecer a varios pagos.


17

BASE DE DATOS Modelo Relacional En este modelo se representan los datos y las relaciones entre estos, a travs de una coleccin de tablas, en las cuales los renglones (tuplas) equivalen a los cada uno de los registros que contendr la base de datos y las columnas corresponden a las caractersticas (atributos) de cada registro; Considerando nuestro ejemplo del empleado y el artculo:

Cmo se representan las relaciones entre las entidades en este modelo? Existen dos formas de representarla; pero para ello necesitamos definir que es una llave primaria: Es un atributo el cual definimos como atributo principal, es una forma nica de identificar a una entidad. Por ejemplo, el DNI de un empleado se distingue de otro por que los DNI no pueden ser iguales. Ahora s, las formas de representar las relaciones en este modelo son: 1. Haciendo una tabla adicional que contenga cada una de las llaves primarias de las entidades involucradas en la relacin. Esto se da en relaciones del tipo varios a varios Tomando en cuenta que la llave primaria del empleado es su DNI, y la llave primaria del artculo es la Clave. La relacin de nuestro modelo resulta: DNI Clave 10201020 C001 12139574 B300

2. Incluyendo en alguna de las tablas de las entidades involucradas, la llave de la otra tabla. Esto se aplica en relaciones de uno a varios

Cmo se pasa del esquema E/R a las tablas? Para cada entidad del esquema se crear una tabla con tantos campos como atributos tenga la entidad. Ejemplo:

Tabla 'TRABAJADORES' DNI 43658721 86326587 43092165 Num-Seguro Xxxxxxxxxxxx Yyyyyyyyyyyy zzzzzzzzzzzz Nombre Fulano Mengano Zutano Apellidos De Tal Del Cual


18

BASE DE DATOS Las relaciones 1-1 se pueden reflejar incluyendo en una de las dos tablas un campo en el que poder colocar la clave del elemento de la otra tabla con el que se est relacionado. Ese nuevo campo que se incluye en la tabla recibe el nombre de clave ajena. Ejemplo:

Donde el campo DNI-ESPOSO es clave ajena o fornea de la tabla HOMBRE. Aqu hay que hacer notar que el campo DNI-ESPOSO puede tomar o bien un valor nulo, en el caso de aquellas mujeres que no estn casadas, o bien el valor de alguno de los DNI de la tabla HOMBRE, en el caso de las mujeres casadas; en este segundo caso, ese DNI (la clave ajena) no se deber repetir en ningn otro registro de tabla MUJER. Las relaciones 1-n se representan de forma muy parecida a como se ha explicado para las relaciones 1-1. La diferencia est en que ahora no es indiferente donde se coloque la clave ajena, esta debe estar obligatoriamente en la tabla del 'mucho' (n); y adems, para este caso si se permitir que haya valores repetidos en dicho campo. Ejemplo:

Para representar las relaciones n-n en tablas lo que se hace es crear una nueva tabla solamente para la relacin. Esta nueva tabla tendr dos claves ajenas y su propia clave estar formada por la unin de las claves ajenas. Ejemplo:


19

BASE DE DATOS Problemas con las interrelaciones A la hora de establecer las interrelaciones existentes en un sistema de bases de datos nos podemos encontrar dos problemas: 1. Interrelaciones recursivas: un elemento se relaciona consigo mismo directamente. 2. Interrelaciones circulares o cclicas: A se relaciona con B, B se relaciona con C y C se relaciona con A. Ambos casos pueden suponer un grave problema si definimos una relacin con integridad referencial y decimos eliminar en cascada (al eliminar una clave de la tabla A se eliminan los elementos relacionados en la tabla B). Supongamos la relacin recursiva existen en la relacin Empleado y Supervisor (ambos son empleados de la empresa). Est claro que un empleado est supervisado por otro empleado. Veamos la forma de solucionarlo: Empleados Cdigo Nombre Supervisor 102 105 821 956 Juan Luis Mara Martn NO SI NO SI

Para solucionar la relacin debemos crear una tabla formada por dos campos. Ambos campos deben ser el cdigo del empleado pero como no podemos tener dos campos con el mismo nombre a uno de ellos le llamaremos cdigo supervisor. Tabla Puente Cdigo Empleado Cdigo Supervisor 102 105 821 956 105 956 105 105

Para terminar de resolver la interrelacin recursiva basta con definir dos interrelaciones entre la tabla empleados y la tabla puente de tipo 1: n. La primera relacin se crea utilizando las claves Empleados[Cdigo] y Tabla Puente[Cdigo Empleado]. La segunda entre Empleados[Cdigo] y Tabla Puente [Cdigo Supervisor]. Las interrelaciones cclicas o circulares no son muy frecuentes y no existe una metodologa estndar para su eliminacin, normalmente son debidas a errores de diseo en la base de datos, principalmente en el diseo conceptual del sistema de datos. Por tanto si llegamos a este punto hay que volver a replantearse todo el diseo de la base de datos. Atributos de las interrelaciones En la mayora de las interrelaciones definidas ser conveniente exigir integridad relacional entre las claves. Exigiendo la integridad referencial se consigue que en una relacin de tipo 1: n o de tipo 1: 1, no se puede aadir ningn valor en la tabla destino si no existe en la tabla origen. Dicho con un ejemplo: en la relacin Clientes y Pedidos la tabla Pedidos contiene un campo que se corresponde con el cdigo del Cliente, si se exige la integridad referencia no se podr escribir un cdigo de cliente en la tabla Pedidos que no exista en la tabla Clientes; de no exigir la integridad referencial se podrn crear pedidos con cdigos de clientes que no existen, generando incongruencia de datos en la base de datos. Definida la integridad referencial (siempre necesaria) podemos exigir la actualizacin en cascada (siempre necesaria); esta actualizacin implica que si cambiamos el cdigo a un cliente, debemos actualizar dicho cdigo en la tabla de pedidos, de no ser as, al cambiar el cdigo a un cliente, perderemos los pedidos que tena realizados. Para concluir debemos hablar de la eliminacin en cascada (NO siempre necesaria), la eliminacin en cascada consiste en eliminar todos los datos dependientes de una clave. En nuestro ejemplo implica que al borrar un cliente hay que eliminar todos los pendidos que ha realizado. En muchas ocasiones no interesa realizar esta operacin de eliminacin en cascada por motivos diversos. Si en el caso de clientes y pedidos no se exige eliminacin en cascada no se podr borrar ningn cliente en tanto en cuanto tenga realizado algn pedido (de lo contrario tendramos incongruencia de datos).


20

BASE DE DATOS MODELAMIENTO DE BASE DE DATOS CON ERWIN DATA MODELER Esta herramienta permite realizar desde un modelo lgico de requerimientos de informacin y reglas de negocio que definan una base de datos, hasta un modelo fsico, optimizado por las caractersticas especficas de la base de datos de destino. De este modo, se puede implementar la generacin y mantenimiento de la misma, as como, el modelado de proyectos, dimensional y de ingeniera de la informacin. Abrir Erwin

Con los conocimientos bsicos de base de datos ya podemos comenzar a disear modelos de base de datos. Se comenzara con el modo Lgico (modelo Entidad Relacion)

Diseo lgico con Erwin Clic en el botn Create Model (tambin podemos presionar Ctrl+N o ir a File/New )

Clic aqu para crear un nuevo modelo


21

BASE DE DATOS Se mostrara la siguiente pantalla, en ella debemos marcar las opciones que se indican ya que las bases de datos que generemos sern para SQL Server

Asegurmonos de estar en el modelo Logical


22

BASE DE DATOS Tool Box Para realizar nuestro modelo Entidad Relacin tenemos las herramientas necesarias en el TOOL BOX 1- Seleccionar entidades 2- Crear entidad 3- Crear subcategorias o subentidades 4- Relaciones de identidad 5- Relaciones muchos a muchos 6- Relaciones de referencia Crear Entidades Para crear una entidad basta hacer clic en el icono Create entity , luego el puntero cambiara de forma, permitiendo hacer clic sobre el lugar donde se desee colocar la nueva entidad. Notar que la nueva entidad tiene por nombre E/1, al hacer otra entidad su nombre ser E/2, y as sucesivamente. El puntero aparece justo en el nombre de la entidad para poder modificarlo. Luego que se escribe el nombre de la entidad y si se presiona enter se pasara al rea de llaves primarias. Si deseamos que un atributo no sea llave primaria solo basta hacer clic en la entidad y luego, notara que el puntero del mouse adopta la forma de una mano cuando se mueve dentro del rea de claves o entidades, de esta manera basta arrastrar la clave hacia el rea de atributos. El caso inverso de llevar un atributo al rea de llave se realiza de manera similar Cambiar el tipo de dato Apuntar a un atributo o llave y presionar el botn derecho del mouse, se mostrara un men contextual hacer clic en la opcin Attributes Se mostrara la siguiente ventana en la cual podemos crear nuevos atributos y tambin cambiar los tipos de datos.

Relaciones de Identificacin Una relacin es de identificacin cuando una entidad depende de otra a tal punto que las llaves primarias de la antecesora sern tambin llaves primarias de la predecesora

Relaciones de Muchos a Muchos Representa la dependencia entre dos entidades de parte de ambas, no se sugiere su uso ya que puede ser representado haciendo uso de tcnicas del modelo relacional.


23

BASE DE DATOS

Relaciones de No Identificacin o Referencia Este tipo de relaciones definen un vinculo entre dos entidades pero no de identificacin, es decir las llaves de la antecesora pasan a la dependiente pero como atributos y no como llaves primarias. Para crear una relacin entre dos entidades se hace clic en el botn que tiene la relacin que deseamos, luego clic en la entidad principal, finalmente clic en la entidad dependiente. Para borrar una relacin basta hacer clic sobre esta y luego presionar Supr

Ejercicio 1 A partir del siguiente enunciado se desea realiza el modelo entidad-relacin: Una empresa vende productos a varios clientes. Se necesita conocer los datos personales de los clientes (nombres, apellidos, DNI, direccin y fecha de nacimiento). Cada producto tiene un nombre y un cdigo, as como un precio unitario. Un cliente puede comprar varios productos a la empresa, y un mismo producto puede ser comprado por varios clientes. Los productos son suministrados por diferentes proveedores. Se debe tener en cuenta que un producto slo puede ser suministrado por un proveedor, y que un proveedor puede suministrar diferentes productos. De cada proveedor se desea conocer el RUC, nombre y direccin. Solucin Como ya conocemos los diferentes tipos Clientes de datos y adems como elegir los tipos DNICliente: CHAR(8) de datos procedemos a asignar los tipos de datos a los atributos. Nombre: VARCHAR(30) Con los conocimientos previos que ApellidoPat: VARCHAR(30) ApellidoMat: VARCHAR(30) tenemos para cada entidad elegimos la Direccion: VARCHAR(150) clave principal. FechaNac: DATE En el modelo lgico podemos dejar expresado las relaciones varios a varios y Productos Proveedores nos quedara la solucin ms bsica para CodigoProd: INTEGER el enunciado tal como podemos ver en la RUC: CHAR(11) nombreProd: VARCHAR(30) primera imagen.Nombre: VARCHAR(150) Direccion: VARCHAR(150) PrecioUnitario: MONEY RUC: CHAR(11) (FK)

Como ya tenemos base terica suficiente para salvar las relaciones varios a varios, podemos complementar el modelo lgico y proceder a crear las Clientes ClientesProductos entidades intermedias que permiten DNICliente: CHAR(8) DNICliente: CHAR(8) (FK) eliminar las relaciones varios a varios. CodigoProd: INTEGER (FK) Nombre: VARCHAR(30) El nombre de la nueva entidad intermedia ApellidoPat: VARCHAR(30) o puente en nuestro ejemplo es Clientes ApellidoMat: VARCHAR(30) Productos, es solo una sugerencia Direccion: VARCHAR(150) pudiendo ser cambiado el nombre de la FechaNac: DATE entidad en cualquier momento. En un primer momento la tabla intermedia Productos Proveedores o puente tiene una clave principal CodigoProd: INTEGER RUC: CHAR(11) compuesta formada por las claves nombreProd: VARCHAR(30) principales de las dos tablas que Nombre: VARCHAR(150) PrecioUnitario: MONEY intervienen en la relacin. De esta manera Direccion: VARCHAR(150) RUC: CHAR(11) (FK) los registros de la tabla ClientesProductos sern nicos y no se podrn duplicar dando as ms seguridad (se exige integridad referencial)

Clien una ta romp a var produ la tab pudie


24

BASE DE DATOS Clientes ClientesProductos A continuacin se muestra otra forma de crear la tabla ClientesProductos, en este DNICliente: CHAR(8) caso las claves principal de las tablas que Nombre: VARCHAR(30) DNICliente: CHAR(8) (FK) intervienen en la relacin varios a varios ApellidoPat: VARCHAR(30) CodigoProd: INTEGER (FK) pasan a ser simples atributos, de esta ApellidoMat: VARCHAR(30) manera es posible que pueda existir Direccion: VARCHAR(150) registros repetidos en la tabla ya que la FechaNac: DATE tabla en si no tiene clave principal. Por Productos qu se dejara una tabla sin clave? Proveedores Existen situaciones en las que se desea CodigoProd: INTEGER RUC: CHAR(11) que una entidad/tabla permita escribir de nombreProd: VARCHAR(30) Nombre: VARCHAR(150) forma duplicada todo un registro o al PrecioUnitario: MONEY Direccion: VARCHAR(150) menos los campos que son claves RUC: CHAR(11) (FK) forneas. Si se desea que suceda as es recomendable hacerlo si se cuenta con otro atributo mas aparte de las claves forneas en la entidad puente por ejemplo puede ser otro atributo/campo llamado fechaCompra.Clientes DNICliente: char(8) NOT NULL Nombre: varchar(30) NOT NULL ApellidoPat: varchar(30) NOT NULL ApellidoMat: varchar(30) NOT NULL Direccion: varchar(150) NULL FechaNac: datetime NULL Proveedores RUC: char(11) NOT NULL Nombre: varchar(150) NOT NULL Direccion: varchar(150) NULL ClientesProductos DNICliente: char(8) NOT NULL (FK) CodigoProd: integer NOT NULL (FK)

Productos CodigoProd: integer NOT NULL nombreProd: varchar(30) NOT NULL PrecioUnitario: money NULL RUC: char(11) NULL (FK)

Ejercicio 2 A partir del siguiente enunciado se desea realizar el modelo entidad-relacin. Se desea informatizar la gestin de una empresa de transportes que reparte paquetes por el pas. Los encargados de llevar los paquetes son los camioneros, de los que se quiere guardar el DNI, nombre, telfono, direccin, salario, distrito en que vive. De los paquetes transportados interesa conocer el cdigo de paquete, descripcin, destinatario y direccin del destinatario. Un camionero distribuye muchos paquetes, y un paquete slo puede ser distribuido por un camionero. De las provincias a las que llegan los paquetes interesa guardar el cdigo de provincia y el nombre. Un paquete slo puede llegar a una provincia. Sin embargo, a una provincia pueden llegar varios paquetes. De los camiones que llevan los camioneros, interesa conocer la matrcula, modelo, tipo y potencia. Un camionero puede conducir diferentes camiones en fechas diferentes, y un camin puede ser conducido por varios camioneros.Paquetes CodPaquete: INTEGER Descripcion: TEXT destinatario: VARCHAR(150) DestinatarioDireccion: VARCHAR(150) DNICamionero: CHAR(8) (FK) CodProvincia: INTEGER (FK) Camioneros DNICamionero: CHAR(8) CamioneroNombre: VARCHAR(30) CamioneroTelefono: CHAR(9) CamioneroDireccion: VARCHAR(150) Salario: MONEY CamioneroDistrito: VARCHAR(30)

Camiones Provincias CodProvincia: INTEGER Nombre: VARCHAR(30) Matricula: CHAR(6) modelo: VARCHAR(30) tipo: VARCHAR(30) potencia: FLOAT


25

BASE DE DATOSPaquetes CodPaquete: INTEGER Descripcion: TEXT destinatario: VARCHAR(150) DestinatarioDireccion: VARCHAR(150) DNICamionero: CHAR(8) (FK) CodProvincia: INTEGER (FK) Camioneros DNICamionero: CHAR(8) CamioneroNombre: VARCHAR(30) CamioneroTelefono: CHAR(9) CamioneroDireccion: VARCHAR(150) Salario: MONEY CamioneroDistrito: VARCHAR(30)

CamionesCamioneros Camiones Provincias CodProvincia: INTEGER Nombre: VARCHAR(30) Matricula: CHAR(6) modelo: VARCHAR(30) tipo: VARCHAR(30) potencia: FLOAT Matricula: CHAR(6) (FK) DNICamionero: CHAR(8) (FK)

Paquetes CodPaquete: integer NOT NULL Descripcion: text NULL destinatario: varchar(150) NULL DestinatarioDireccion: varchar(150) NULL DNICamionero: char(8) NULL (FK) CodProvincia: integer NULL (FK) Camiones Matricula: char(6) NOT NULL modelo: varchar(30) NULL tipo: varchar(30) NULL potencia: float NULL Provincias CodProvincia: integer NOT NULL Nombre: varchar(30) NULL

Camioneros DNICamionero: char(8) NOT NULL CamioneroNombre: varchar(30) NOT NULL CamioneroTelefono: char(9) NULL CamioneroDireccion: varchar(150) NULL Salario: money NULL CamioneroDistrito: varchar(30) NULL

CamionesCamioneros Matricula: char(6) NOT NULL (FK) DNICamionero: char(8) NOT NULL (FK)

Ejercicio 3 A partir del siguiente enunciado disear el modelo entidad-relacin. Se desea disear la base de datos de un Instituto. En la base de datos se desea guardar los datos de los profesores del Instituto (DNI, nombre, direccin y telfono). Los profesores imparten mdulos, y cada mdulo tiene un cdigo y un nombre. Cada alumno est matriculado en uno o varios mdulos. De cada alumno se desea guardar el n de Expediente, nombre, apellidos y fecha de nacimiento. Los profesores pueden impartir varios mdulos, pero un mdulo slo puede ser impartido por un profesor. Cada mdulo tiene un grupo de alumnos, uno de los cuales es el delegado del grupo. Aqu tenemos un caso de relacin recursiva en Profesores una tabla. Modulos DNI: CHAR(8) Un grupo ( varios alumnos) tiene un delegado, y Codigo: INTEGER nombre: VARCHAR(30) un alumno es delegado de un grupo de Direccion: VARCHAR(150) Nombre: VARCHAR(30) alumnos(varios alumnos). telefono: CHAR(9) DNI: CHAR(8) (FK) Notamos que hay una relacin de uno a varios en la Entidad/tabla Alumnos consigo mismo. El tipo de relacin es de uno a varios.Alumnos NroExpediente: INTEGER Nombre: VARCHAR(30) ApellidoPat: VARCHAR(30) ApellidoMat: VARCHAR(30) FechaNac: DATE


26

BASE DE DATOSProfesores DNI: CHAR(8) nombre: VARCHAR(30) Direccion: VARCHAR(150) telefono: CHAR(9) Modulos Codigo: INTEGER Nombre: VARCHAR(30) DNI: CHAR(8) (FK)

Alumnos NroExpediente: INTEGER Nombre: VARCHAR(30) ApellidoPat: VARCHAR(30) ApellidoMat: VARCHAR(30) FechaNac: DATE

AlumnosModulos NroExpediente: INTEGER (FK) Codigo: INTEGER (FK)

Ejercicio 4 Concesionario de coches A un concesionario de coches llegan clientes para comprar automviles. De cada coche interesa saber la matrcula, modelo, marca y color. Un cliente puede comprar varios coches en el concesionario. Cuando un cliente compra un coche, se le hace una cha en el concesionario con la siguiente informacin: dni, nombre, apellidos, direccin y telfono. Los coches que el concesionario vende pueden ser nuevos o usados (de segunda mano). De los coches nuevos interesa saber el nmero de unidades que hay en el concesionario. De los coches viejos interesa el nmero de kilmetros que lleva recorridos. El concesionario tambin dispone de un taller en el que los mecnicos reparan los coches que llevan los clientes. Un mecnico repara varios coches a lo largo del da, y un coche puede ser reparado por varios mecnicos. Los mecnicos tienen un dni, nombre, apellidos, fecha de contratacin y salario. Se desea guardar tambin la fecha en la que se repara cada vehculo y el nmero de horas que se tardado en arreglar cada automvil. CochesClientes DNICliente nombre apellidos direccion telefono matricula modelo marca color estadoCoche NumeroUnid Kilometraje DNICliente (FK) Coches Mecanicos CodigoReparacion matricula (FK) DNIMecanico (FK) FechaReparacion HoraReparacion Mecanicos DNIMecanico nombres apellidos FechaCont Salario


27

BASE DE DATOS Cuestionario 2 Cree el modelo E/R en Erwin y su correspondiente implementacin en SQL Server para los siguientes enunciados: 1- Ministerio de Defensa El Ministerio de Defensa desea disear una Base de Datos para llevar un cierto control de los soldados que realizan el servicio militar. Los datos significativos a tener en cuenta son: Un soldado se define por su cdigo de soldado (nico), su nombre y apellidos, y su graduacin. Existen varios cuarteles, cada uno se define por su cdigo de cuartel, nombre y ubicacin. Hay que tener en cuenta que existen diferentes Cuerpos del Ejrcito (Infantera, Artillera,....), y cada uno se define por un cdigo de Cuerpo y denominacin. Los soldados estn agrupados en compaas, siendo significativa para cada una de stas, el nmero de compaa y la actividad principal que realiza. Se desea controlar los servicios que realizan los soldados (guardias, imaginarias, cuarteleros,...), y se definen por el cdigo de servicio y descripcin. Consideraciones de diseo: Un soldado pertenece a un nico cuerpo y a una nica compaa, durante todo el servicio militar. A una compaa pueden pertenecer soldados de diferentes cuerpos, no habiendo relacin directa entre compaas y cuerpos. Los soldados de una misma compaa pueden estar destinados en diferentes cuarteles, es decir, una compaa puede estar ubicada en varios cuarteles, y en un cuartel puede haber varias compaas. Eso s, un soldado slo est en un cuartel. Un soldado realiza varios servicios a lo largo de la mili. Un mismo servicio puede ser realizado por ms de un soldado (con independencia de la compaa), siendo significativa la fecha de realizacin.

2- Control de carreteras Disear un modelo E/R que recoja la organizacin de una base de datos para contener la informacin sobre todas las carreteras del pas, sabiendo que se deben cumplir las siguientes especificaciones: - Las carreteras estn divididas en varias categoras (locales, comerciales, regionales, nacionales, autovas, etc.). - Las carreteras se dividen en tramos. Un tramo siempre pertenece a una nica carretera y no puede cambiar de carretera. - Un tramo puede pasar por varias comunas, interesando conocer el Km de la carretera y la comuna donde empieza el tramo y en donde termina. - Para los tramos que suponen principio o final de carretera, interesa saber si es que la carretera concluye fsicamente o es que confluye en otra(s) carretera(s). En este caso, interesa conocer con qu carretera confluye y en qu kilmetro, tramo y comuna.

3- Cadena de hoteles Cada hotel (del que interesa almacenar su nombre, direccin, telfono, ao de construccin, etc.) se encuentra clasificado obligatoriamente en una categora (por ejemplo, tres estrellas) pudiendo bajar o aumentar de categora. Cada categora tiene asociada diversas informaciones, como, por ejemplo, el tipo de IVA que le corresponde y la descripcin. Los hoteles tiene diferentes clases de habitaciones (suites, dobles, individuales, etc.), que se numeran de forma que se pueda identificar fcilmente la planta en la que se encuentran. As pues, de cada habitacin se desea guardar el cdigo y el tipo de habitacin. Los particulares pueden realizar reservas de las habitaciones de los hoteles. En la reserva de los particulares figurarn el nombre, la direccin y el telfono. Las agencias de viaje tambin pueden realizar reservas de las habitaciones. En caso de que la reserva la realiza una agencia de viajes, se necesitarn los mismos datos que para los particulares, adems del nombre de la persona para quien la agencia de viajes est realizando la reserva. En los dos casos anteriores tambin se debe almacenar el precio de la reserva, la fecha de inicio y la fecha de fin de la reserva.

4- Compaa area Se desea almacenar la informacin de una compaa area en una B.D relacional. La compaa area tiene tres recursos principales: Aviones, pilotos, tripulacin. De cada piloto se desea conocer su cdigo, nombre y horas de vuelo. De los miembros de la tripulacin solo se tendr el cdigo y el nombre. Los pilotos y la tripulacin tienen una base a la que regresan despus de cada jornada. Un vuelo va desde un origen a un destino a una hora concreta y tiene cdigo de vuelo. De cada vuelo se desea saber el avin en el que se va a hacer o en el que se ha hecho, el piloto y la tripulacin. Recopilacin y edicin: Ing. CARLOS BAYES ANTUNEZ

28

BASE DE DATOS Cada avin tiene un cdigo, un nombre y es de un tipo (boing, airbus, entre otros). Tambin tiene una base donde es sometido a mantenimiento. 5- Asignacin de personal de lneas areas Construir un diagrama entidad-relacin para una empresa de lneas areas. La empresa tiene distintos tipos de empleados (pilotos, personal auxiliar de vuelo, mecnicos, personal de gestin, de administracin, de limpieza,). La informacin que mantiene sobre sus empleados es: nmero de empleado, nombre, cargo, direccin y salario. Adems, le interesa almacenar datos sobre: los vuelos que realiza (nmero de vuelo, avin empleado, fecha, origen, destino, hora de salida, hora de llegada y empleados asignados al vuelo), los pasajeros registrados en cada vuelo (nombre, direccin y telfono) y los aviones que posee la empresa (constructor, nmero de modelo, nmero de serie). En el caso de los pilotos, se desea hacer constar adems su capacidad para poder pilotar los distintos modelos de aviones que posee la empresa. 6- Sistema de ventas Le contratan para implementar una BD en SQL Server que permita apoyar la gestin de un sistema de ventas. La empresa necesita llevar un control de proveedores, clientes, productos y ventas. Un proveedor tiene un RUC, nombre, direccin, telfono y pgina web. Un cliente tambin tiene RUC, nombre, direccin, pero puede tener varios telfonos de contacto. La direccin se entiende como Jr/Av/PJ/Urb, Distrito y Provincia. Un producto tiene un id nico, nombre, precio actual, stock y nombre del proveedor. Adems se organizan en categoras, y cada producto va slo en una categora. Una categora tiene id, nombre y descripcin. Por razones de contabilidad, se debe registrar la informacin de cada venta con un id, fecha, cliente, descuento y monto final. Adems se debe guardar el precio al momento de la venta, la cantidad vendida y el monto total por el producto. 7- Sedes olmpicas Las sedes olmpicas se dividen en complejos deportivos. Los complejos deportivos se subdividen en aquellos en los que se desarrolla un nico deporte y en los polideportivos. Los complejos polideportivos tienen reas designadas para cada deporte con un indicador de localizacin (ejemplo: centro, esquina- NE, etc.). Un complejo tiene una localizacin, un jefe de organizacin individual y un rea total ocupada. Los dos tipos de complejos (deporte nico y polideportivo) tendrn diferentes tipos de informacin. Para cada tipo de sede, se conservar el nmero de complejos junto con su presupuesto aproximado. Cada complejo celebra una serie de eventos (ejemplo: la pista del estadio puede celebrar muchas carreras distintas.). Para cada evento est prevista una fecha, duracin, nmero de participantes, nmero de comisarios. Una lista de todos los comisarios se conservar junto con la lista de los eventos en los que est involucrado cada comisario ya sea cumpliendo la tarea de juez u observador. Tanto para cada evento como para el mantenimiento se necesitar cierto equipamiento (ejemplo: arcos, prtigas, barras paralelas, etc.).

8- Artculos y encargos Una base de datos para una pequea empresa debe contener informacin acerca de clientes, artculos y pedidos. Hasta el momento se registran los siguientes datos en documentos varios: Para cada cliente: Nmero de cliente (nico), Direcciones de envo (varias por cliente), Saldo, Lmite de crdito (depende del cliente, pero en ningn caso debe superar los 5000 soles), Descuento. Para cada artculo: Nmero de artculo (nico), Fbricas que lo distribuyen, Existencias de ese artculo en cada fbrica, Descripcin del artculo. Para cada pedido: Cada pedido tiene una cabecera y el cuerpo del pedido. La cabecera est formada por el nmero de cliente, direccin de envo y fecha del pedido. El cuerpo del pedido son varias lneas, en cada lnea se especifican el nmero del artculo pedido y la cantidad. Adems, se ha determinado que se debe almacenar la informacin de las fbricas. Sin embargo, dado el uso de distribuidores, se usar: Nmero de la fbrica (nico) y Telfono de contacto. Y se desean ver cuntos artculos (en total) provee la fbrica. Tambin, por informacin estratgica, se podra incluir informacin de fbricas alternativas respecto de las que ya fabrican artculos para esta empresa. Nota: Una direccin se entender como Jr/Av/P.J/Urb, Distrito y Provincia. Una fecha incluye hora. Se pide hacer el diagrama ER para la base de datos que represente esta informacin. 9- Gestin de Proyectos por departamentos: Una Compaa est organizada en Departamentos. Cada Departamento tiene un nombre y nmero nico y un cierto empleado que lo dirige, y nos interesa la fecha en que dicho empleado comenz a dirigir el Departamento. Un Departamento puede estar distribuido en varios lugares. Todo Empleado est asignado a un departamento. Cada Departamento controla cierto nmero de Proyectos, cada uno de los cuales tienen un nombre y nmero nicos y se efecta en un solo lugar. Un Empleado puede trabajar en varios Proyectos, que no necesariamente estn controlados por el mismo Departamento. Nos interesa el nmero de horas por semana que un empleado trabaja en el proyecto. Recopilacin y edicin: Ing. CARLOS BAYES ANTUNEZ

29

BASE DE DATOS Realice el diagrama Entidad-Relacin que modele el problema mencionado anteriormente. 10- Reservaciones Instalaciones deportivas: Un centro de instalaciones deportivas quiere hacer una aplicacin de reservas. En el centro existen instalaciones deportivas sus atributos son: Cdigo, denominacin (piscinas, gimnasios, frontones, etc.), estado. El centro en cuestin tiene socios, de los cuales se almacenan su cdigo, direccin, ciudad, provincia, telfono, nombre y estado. Existen una serie de artculos (balones, redes, raquetas, etc) cuyos atributos son: cdigo, marca, denominacin, estado. Dichos artculos se pueden alquilar junto con las reservas, las reservas tienen Nro de reserva, fecha de reserva. Cada instalacin es reservada por un socio en una fecha dada desde una hora de inicio hasta una hora de fin siempre y cuando este al da en sus cuotas. Cada reserva puede tener asociada uno o varios artculos deportivos que se alquilan aparte. Por ejemplo si yo quiero hacer una reserva para jugar a voleibol tengo que reservar una instalacin polideportivo ms un artculo red, ms un artculo baln. Realiza el modelo entidad relacin. 11 Gestin de clubes de la Federacin El problema a modelar es la gestin de clubes en una federacin. El presidente de la federacin quiere saber qu clubes hay y quin los preside. Cada club puede tener varios equipos y los equipos estn formados por jugadores (porteros, defensas, centrales, delanteros,..). Adems los equipos participan cada ao en competiciones deportivas, cada una de las cuales tiene sus propios premios. 12- Fono ofertas: Haga el diagrama E/R del siguiente caso. En los atributos, slo incluya las claves primarias. La empresa La Eficiente S.A. se dedica a vender productos por telfono. La siguiente es la descripcin de sus procesos de venta. Cuando alguien llama por telfono para comprar, se le llama cliente, identificndolo apropiadamente. El cliente llena una orden de compra, con detalles de direccin de facturacin, quien lo atendi, etc. Pero lo ms importante, es que compra uno o ms productos (en cantidades que se especifican). De los productos, por supuesto, adems del precio unitario, se sabe la cantidad que la empresa tiene en stock. Por supuesto, con cada venta se genera una factura. Hay varios mtodos de pago (tarjeta de crdito, cheque al contado, cheques a fecha, etc.), cada uno con varias caractersticas particulares. Cada factura puede cancelarse con uno slo de los mtodos de pago. Cada uno de los elementos ordenado puede ser despachado individualmente o en forma conjunta en un slo paquete. Cada paquete tiene asociado un mtodo de envo con muchos detalles (transportista, fecha de envo, etc.). Desde luego, un empleado prepara cada despacho; esta persona no necesariamente es la misma que recibe la orden de compra. 13- Almacen Se quiere registrar informacin correspondiente a un almacn, sus departamentos, sus empleados, sus productos y los fabricantes de estos productos: Cada empleado est representado por un nmero de empleado, su nombre y direccin. Se debe indicar adems a qu departamento pertenece Cada departamento est representado por su nombre y se saben que empleados trabajan en l, quin es el jefe del departamento y los productos que vende Cada producto est representado por su nombre, fabricante, precio, nmero de producto asignado por el fabricante y nmero de producto asignado por el almacn Cada fabricante est representado por su nombre, direccin, productos que suministra al almacn y precios de estos productos. 14- Veterinario Un veterinario tiene como pacientes animales y como clientes familias. Un cliente es un conjunto de personas que suele corresponderse con una familia. . Cada cliente tiene un cdigo, el primer apellido del cabeza de familia, un nmero de cuenta bancaria, una direccin, un telfono y los nombres y NIF de las personas correspondientes. No existe lmite en el nmero de personas asociadas a una entidad cliente. Adems, una persona puede estar dada de alta en varios clientes (por ejemplo, un hombre que vive con su esposa tiene un gato y como tal pertenece a un cliente, pero tambin est dado de alta en el cliente asociado con el perro de sus padres). Los clientes pueden tener varias mascotas, cada mascota tiene un cdigo, un alias, una especie, una raza, color de pelo, fecha de nacimiento aproximada, peso medio del animal en las ltimas 10 visitas y el peso actual del animal. Asimismo se guardar un historial mdico con cada enfermedad que tuvo y la fecha en la que enferm. Adicionalmente cada mascota tiene un calendario de vacunacin, en el que se registrar la fecha de cada vacuna, la enfermedad de la que se vacuna. 15- Avcola Una empresa avcola desea llevar el control de la alimentacin diaria de las aves en cada una de las etapas bsicas Recopilacin y edicin: Ing. CARLOS BAYES ANTUNEZ

30

BASE DE DATOS de crecimiento (bebe, joven y consumo). Cada racin de alimento tiene una composicin en base a tres elementos, la cual es diferente en cada una de las etapas. Cada etapa tiene definido un periodo de tiempo. Todo empieza con la fecha de nacimiento del ave bebe y finaliza con la fecha de venta del ave de consumo. Las aves se identifican desde su nacimiento y en base al nmero de lote de incubacin. Cada lote posee un tamao de poblacin y una fecha de entrega para el inicio de crianza. El control de alimentacin de cada poblacin de un lote de crianza a lo largo de cada etapa incluye la cantidad total de la racin de alimento que se coloca, la poblacin de aves al inicio del da y el control de la merma en base al nmero de aves que mueren el da anterior.


31

base de datos 7 8 9 2008 v2012 i

Documents