base de datos 7 8 9 2008 v2012 i

BASE DE DATOS

Recopilación y edición: Ing. CARLOS BAYES ANTUNEZ

1

6- Diseño de una Base de Datos

El diseño 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 métodos y técnicas específicas.

Así el diseño de la base de datos se descompone en las siguientes fases:

Figura 6.1 Etapas en el diseño de una Base de Datos

•Hasta ahora no se habia indicado cuales son los métodos y técnicas 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 diseñar 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

IMPORTANTE:

Diseño Conceptual Se inicia con la labor de levantamiento de información de la organización, para luego realizar la especificación de requerimientos del usuario. El resultado de este trabajo será el esquema conceptual de la base de datos. Es una descripción 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 información dentro de una organización y como se relacionan estos. No es relevante centrarse en la forma como se estructura su almacenamiento.

Diseño Lógico Empieza el trabajo tomando como base el esquema conceptual y terminado la labor debe generarse el esquema lógico. Un esquema lógico es una descripción de la estructura de la base de datos que puede ser procesada por un SGBD

Diseño Físico Parte del esquema lógico para obtenerse luego un esquema físico. Un esquema físico es una descripción de la implantación de una base de datos en algún medio de almacenamiento; describe las estructuras de almacenamiento y los métodos usados para tener un acceso efectivo a los datos. Por esta razón un diseño físico está firmemente atado a un SGBD específico. El esquema físico se expresa mediante el uso del lenguaje de definición de datos del SGBD elegido, así la base de datos puede ser creada y probada.

BASE DE DATOS


2

6.1 Modelo de Datos

Figura 3.2 Modelo de Datos

6.1.1 Sublenguajes de un Modelo de Datos Un modelo de datos es un lenguaje orientado a describir una Base de Datos, típicamente, tiene dos sublenguajes:

Un Lenguaje de Definición 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 Manipulación de Datos o DML (Data Manipulation Language), orientado a describir las operaciones de manipulación de los datos.

A la parte del DML orientada a la recuperación de datos, usualmente se le llama Lenguaje de Consulta o QL (Query Language). (Repase el capítulo 2.3)

.

Modelo de Datos

Es una colección de herramientas conceptuales para describir los datos, las relaciones que existen entre ellos, semántica asociada a los datos y restricciones de consistencia.

Clasificación

Modelos de alto nivel o conceptuales:

•Orientados a la descripción de estructuras de datos y restricciones de integridad. Se usan fundamentalmente durante la etapa de Análisis de un problema dado y están orientados a representar los elementos que intervienen en ese problema y sus relaciones. El ejemplo más típico es el Modelo Entidad-Relación.

•Actualmente tambien se usa los Diagramas UML (Lenguaje Unificado de Modelado).

Modelos de implementación o lógicos

•Orientados a las operaciones más que a la descripción de una realidad. Usualmente están implementados en algún Manejador de Base de Datos. El ejemplo más típico es el Modelo Relacional, que cuenta con la particularidad de contar también con buenas características conceptuales (Normalización de bases de datos).

Modelos de Bajo nivel o físicos

•Describen como se almacenan los datos en el computador al representar información como los formatos, ordenamientos de los registros que hacen mas eficiente el acceso, etc.

•En el curso trabajaremos de la siguiente manera:

•Modelo de alto nivel o Conceptuales: Se indicará la parte teórica del modelo entidad/relación y se implementara haciendo uso de la herramienta ERwin, que es un programa de diseño de Base de Datos. Aclaramos que en esta etapa para el modelado E/R se puede usar incluso papel por ser la etapa incial. Además la herramienta ERwin no es el único de su tipo ya que existen otros programas similares: Power Designer, Rational Rose, etc.

•Modelo de Implementación o Lógicos: Se indicará la parte teórica del Modelo relacional: la normalización; posteriormente se implementara en SQL Server

IMPORTANTE:

http://es.wikipedia.org/wiki/Lenguaje_de_definici%C3%B3n_de_datos

http://es.wikipedia.org/wiki/Lenguaje_de_Manipulaci%C3%B3n_de_Datos

http://es.wikipedia.org/wiki/Lenguaje_de_Manipulaci%C3%B3n_de_Datos

http://es.wikipedia.org/wiki/Lenguaje_de_consulta

http://es.wikipedia.org/wiki/Lenguaje_de_consulta

BASE DE DATOS


3

6.1.2 Recomendaciones para crear atributos/campos

•En vez de un campo llamado nombres, crear los campos: Nombre1, Nombre2, ApellidoPat, ApellidoMat

En lo posible utilizar atributos/campos para cada dato en individual

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

•En vez de crear un campo TiempoDeServicio, crear el campo fechaContratacion

Con datos numericos que son el resultado de operaciones con fechas como edad, años de servicio, etc. , crear campos que almacenen las fechas que intervienen en la operación y si en la operación se usa la fecha actual, no es necesario crear un campo para la fecha actual.

•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

Si ningún campo reune las condiciones de ser clave principal, crear un nuevo campo para la clave y es recomendable que sea de tipo entero y además que sea identidad.

•Se tiene el campo PrecioProducto y el campo CantidadComprada, esta demás crear un campo adicional llamado: MontoTotalCompra (MontoTotalCompra=PrecioProducto*CantidadComprada)

Evite crear campos que sean el resultado de la operación de otros campos

•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 existe un dato que será único y/o su valor no cambiará (constante), entonces no será necesario crear un campo para ese dato.

•Ejemplo se crea una tabla adicional llamada Tasas que contiene todos los valores de los impuestos: IGV, AporteSeguroSocial, AporteAFPIntegra.

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.

•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 solución 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.

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.

BASE DE DATOS


4

6.1.3 Recomendaciones para elegir el Tipo de Dato para un campo

•NumeroHistoriaClinica: Int, es correcto

•NumeroHistoriaClinica: Char(7) es correcto

•DNI: Char(8), es correcto

•DNI: Varchar(8), incorrecto

Los campos que serán claves principales pueden ser números enteros o caracteres de longitud fija

•Telefono: Char(9), es caracter ya que no multiplicamos ni dividimos números telefónicos

Si con un dato se debe hacer operaciones entonces es un número

•El "número" de DNI si bien esta formado digitos, es un caracter ya que no realizamos operaciones con los números de DNI

Un dato cuyos valores contengan dígitos no necesariamente será un número

•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 existe un campo que será clave principal en lo posible que sea numero entero y/o además identidad

•Sexo: Bit

•EsAlergico: Bit

•EstaVivo: Bit

Si un campo admite solo dos valores o alternativas entonces elegir el tipo de dato bit o lógico.

BASE DE DATOS


5

7 Modelo Entidad Relación

Generalmente todo modelo tiene una representación gráfica, para el caso de datos el modelo más popular es el modelo entidad-relación o diagrama E/R. Se denomina así debido a que precisamente permite representar relaciones entre entidades (objetivo del modelado de datos).

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

Atributos: Es una característica (adjetivo) de una entidad Por ejemplo: - el nombre, dirección teléfono, grado, grupo, etc. son atributos de la entidad alumno - Clave, número de seguro social, departamento, etc., son atributos de la entidad empleado. - título, ISBN, edición, número de páginas son atributos de la entidad libro. - nombre, apellidos, seudónimo, fecha de nacimiento, son atributos ee la entidad autor

Mo

delo

En

tid

ad

Rela

cio

n E

-R

Repre

senta

a la r

ealidad a

tra

ves d

e:

Entidades

Atributos

Relaciones

Cardinalidad

Llaves

En

tid

ad

es

Se c

lasific

an e

n:

Tangibles: Son todos aquellos objetos físicos que podemos ver, tocar o sentir.

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.

Atr

ibu

to

Realiza u

na d

e t

res c

osas:

Identificar

Relacionar

Describir

BASE DE DATOS


6

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. Como ejemplo para la entidad persona una clave seria número 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 posibles claves y necesitamos elegir una, lo haremos atendiendo a estas normas:

A su vez una entidad se puede asociar o relacionar con más entidades a través de relaciones.

Relación: Asociación entre entidades, sin existencia propia en el mundo real que estamos modelando, pero necesaria para reflejar las interacciones existentes entre entidades. Características delas relaciones:

Grado: Numero de tipos de entidades que participan en la relación

Cardinalidad: Número de elementos de un tipo que se conectan con un elemento de otro (restricción que se observa en el dominio del problema y que controla las ocurrencias de las relaciones)

Cla

ve o

lla

ve

Para

ele

gir

lo d

e a

cuerd

o a

esta

s n

orm

as:

Que sea única, debe identificar inequivocamente cada fila

Que se tenga pleno conocimiento de ella.- ¿Por qué en las empresas se asigna cada cliente un número de cliente?

Que sea mínima, ya que será muy utilizada por el gestor de base de datos.

Nunca debe estar vacio ni ser nulo (siempre debe contener un valor)

Casi nunca (o preferiblemente, nunca) debe cambiar

Rela

cio

nes (

card

inali

dad

)

Relaciones 1-1.- Las entidades que intervienen en la relación se asocian una a una (Ej.: la entidad HOMBRES, la entidad MUJERES y entre ellos la relación MATRIMONIO).

Relaciones 1-n.- Una ocurrencia de una entidad está asociada con muchas (n) de otra (Ej.: la entidad EMPRESA, la entidad TRABAJADOR y entre ellos la relación TRABAJAR-EN).

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

BASE DE DATOS


7

7.1 Diseño de una base de datos Hay cuatro pasos para diseñar una base de datos usando el modelo Entidad-Relación:

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 artículo (que es el producto en venta), y las características que los identifican son:

Empleados Artículos

Nombre Descripción

Puesto costo

Salario clave

DNI

La relación entre ambas entidades la podemos establecer como Venta. Para representar un modelo E-R gráficamente, se emplean símbolos, los cuales son: 7.1.1- Diagrama Entidad / Relación (notación tradicional)

Así nuestro ejemplo anterior quedaría representado de la siguiente forma (notación tradicional):

a. Identificar los conjuntos de entidades y los conjuntos de relaciones de interés.

b. Identificar la información semántica en los conjuntos de relaciones, tales como si un cierto conjunto de relaciones es una proyección 1:N.

c. Definir los dominios y los atributos.

d. Organizar la información dentro del Diagrama Entidad-Relación, y decidir las claves primarias.

Empleados Artículos N:M

Venta

Nombre

Puesto

DNI

Salario Descripción

Clave Costo

1:N 1:M

BASE DE DATOS


8

7.1.2- Diagrama Entidad / Relación (notación UML) Nuestro ejemplo anterior quedaría representado de la siguiente forma (notación UML):

7.1.3 Símbolos usados en un modelo Entidad Relación Relación uno a uno

E/R Clásico

Profesores

Departamentos

1 1

Notación UML

Profesores

dirige Departamentos

1 1

Relación muchos a uno

E/R Clásico

Profesores

Departamentos

M 1

Notación UML

Profesores

perteneceA Departamentos

1

Relación muchos a muchos

E/R Clásico

Profesores

Escuelas

N M

Notación UML

Profesores

enseña Escuelas

DNI Nombre Puesto Salario

Empleados

Clave Descripción Costo

Artículos

Venta

dirige

pertenece

A

enseña

BASE DE DATOS


9

Cardinalidad mínima en una relación E/R Clásico

Cuentas

Clientes

0..N 1..M

Notación UML

Cuentas

es titular de

Clientes

0.. 1..

Relación opcional Un cliente puede o no ser titular de

una cuenta

Relación obligatoria Una cuenta ha de tener un titular

como mínimo Relaciones involutivas Relacion de un tipo consigo mismo.

E/R Clásico

Empleados

Personas

1

1 1 dirige casada con

Notación UML

Empleados

Personas

1

1 1 dirige casada con

Atributos

E/R clásico

Notación UML

se matricula en

calificacion

es titular

de

Empleados

Nombre

Puesto

DNI

Salario


Empleados

Se matricula

en

calificación

BASE DE DATOS


10

Relaciones de especialización y generalización

Atributos y claves

Las subtipos heredan los atributos de los supertipos: los subtipos poseen todos los atributos del supertipo más algunos propios.

La clave primaria de los subtipos es la clave primaria del supertipo.

Empleados

Profesores Contadores

BASE DE DATOS


11

Ejercicio 1 A partir del siguiente enunciado se desea realiza el modelo entidad-relación: Gestión de Ventas “Una empresa vende productos a varios clientes. Se necesita conocer los datos personales de los clientes (nombres, apellidos, DNI, dirección y fecha de nacimiento). Cada producto tiene un nombre y un código, 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 sólo puede ser suministrado por un proveedor, y que un proveedor puede suministrar diferentes productos. De cada proveedor se desea conocer el RUC, nombre y dirección”. Solución 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.

Ejercicio 2 A partir del siguiente enunciado se desea realizar el modelo entidad-relación. Empresa de Transportes “Se desea informatizar la gestión de una empresa de transportes que reparte paquetes por el país. Los encargados de llevar los paquetes son los camioneros, de los que se quiere guardar el DNI, nombre, teléfono, dirección, salario, distrito en que vive. De los paquetes transportados interesa conocer el código de paquete, descripción, destinatario y dirección del destinatario. Un camionero distribuye muchos paquetes, y un paquete sólo puede ser distribuido por un camionero. De las provincias a las que llegan los paquetes interesa guardar el código de provincia y el nombre. Un paquete sólo puede llegar a una provincia. Sin embargo, a una provincia pueden llegar varios paquetes. De los camiones que llevan los camioneros, interesa conocer la matrícula, modelo, tipo y potencia. Un camionero puede conducir diferentes camiones en fechas diferentes, y un camión puede ser conducido por varios camioneros”. Solución

Clientes Productos N:M Compra

NombreCli

ApellidoPat

DirecciónCli

ApellidoMat NombreProd

CodigoProd PrecioUnit

Proveedores

FechaNac

RUC NombrePro DirecciónPro

1:M Suministra

0:M 0:M

1:1

1:M

BASE DE DATOS


12

Ejercicio 3 Base de datos de un instituto A partir del siguiente enunciado diseñar el modelo entidad-relación. “Se desea diseñar la base de datos de un Instituto. En la base de datos se desea guardar los datos de los profesores del Instituto (DNI, nombre, dirección y teléfono). Los profesores imparten módulos, y cada módulo tiene un código y un nombre. Cada alumno está matriculado en uno o varios módulos. De cada alumno se desea guardar el nº de Expediente, nombre, apellidos y fecha de nacimiento. Los profesores pueden impartir varios módulos, pero un módulo sólo puede ser impartido por un profesor. Cada módulo tiene un grupo de alumnos, uno de los cuales es el delegado del grupo”. Solución Aquí tenemos un caso de relación 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 relación de uno a varios en la Entidad/tabla Alumnos consigo mismo. El tipo de relación es de uno a varios.

Paquetes Camioneros M:1 Repartir

Destinatario

DireccionDest

DNIDest telefonoCamionero

DNICamionero

DistritoCamionero

Camiones

CodPaquete

Matricula Modelo Tipo

N:M Manejar

0:M 1:1

0:M

0:N

Salario

DireccionCamionero

Provincias

CodProvincia

Nombre

1:M Enviar

1;1

0:M

BASE DE DATOS


13

Ejercicio 4 Concesionario de coches “A un concesionario de coches llegan clientes para comprar automóviles. De cada coche interesa saber la matrícula, modelo, marca y color. Un cliente puede comprar varios coches en el concesionario. Cuando un cliente compra un coche, se le hace una ficha en el concesionario con la siguiente información: dni, nombre, apellidos, dirección y teléfono. Los coches que el concesionario vende pueden ser nuevos o usados (de segunda mano). De los coches nuevos interesa saber el número de unidades que hay en el concesionario. De los coches viejos interesa el número de kilómetros que lleva recorridos. El concesionario también dispone de un taller en el que los mecánicos reparan los coches que llevan los clientes. Un mecánico repara varios coches a lo largo del día, y un coche puede ser reparado por varios mecánicos. Los mecánicos tienen un dni, nombre, apellidos, fecha de contratación y salario. Se desea guardar también la fecha en la que se repara cada vehículo y el número de horas que se tardado en arreglar cada automóvil”. Solución

Profesores Modulos 1:M Imparte

NombreProf

DireccionProf TelefonoProf nombreMod

CodModulo

Alumnos

DNIProfesor

NroExped

Nombre

ApellidoPat

N:M Tiene

1:1 1:M

1:M

1:N

1:1

1:M

1:M Representa

BASE DE DATOS


14

Ejercicio 5 Biblioteca “En la biblioteca del centro se manejan fichas de autores y libros. En la ficha de cada autor se tiene el código de autor y el nombre. De cada libro se guarda el código, título, ISBN, editorial y número de página. 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 código y una localización. Un libro tiene muchos ejemplares y un ejemplar pertenece sólo a un libro. Los usuarios de la biblioteca del centro también disponen de ficha en la biblioteca y sacan ejemplares de ella. De cada usuario se guarda el código, nombre, dirección y teléfono. 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 préstamos interesa guardar la fecha de préstamo y la fecha de devolución”. Solución

Clientes Coches 1:M compra

NombreCliente

DireccionClient TelefonoClient Modelo

Matricula

Mecánicos

DNICliente

DNIMecanico

NombreMEc

FEchaCont

N:M Revisa

1:1 1:M

1:N

1:M

CochesNuevos CochesUsados

NroUnidades Kilometraje

BASE DE DATOS


15

Autores Libros N:M Escribe

NombreAutor

Titulo

CodLibro

Usuarios

CodAutor

CodUsuario

NombreUsuar

TelefonoUsuar

N:M Préstamo

1:N 1:M

1:N

1:M Ejemplares

CodEjemplar Localizacion

1:M Tiene

1:1

1:M

FechaPrestamo FechaDevolucion

BASE DE DATOS


16

Cuestionario 1

Cree el modelo entidad relación para los siguientes enunciados usando notación Clásica y notación UML 1- Empresa de Venta de automoviles “Se desea diseñar una base de datos para almacenar y gestionar la información empleada por una empresa dedicada a la venta de automóviles, teniendo en cuenta los siguientes aspectos: La empresa dispone de una serie de coches para su venta. Se necesita conocer la matrícula, marca y modelo, el color y el precio de venta de cada coche. Los datos que interesa conocer de cada cliente son el DNI, nombre, dirección, ciudad y número de teléfono: además, los clientes se diferencian por un código interno de la empresa que se incrementa automáticamente 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 también se encarga de llevar a cabo las revisiones que se realizan a cada coche. Cada revisión tiene asociado un código que se incrementa automáticamente por cada revisión que se haga. De cada revisión 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 La clínica “Santo Dinero” necesita llevar un control informatizado de su gestión de pacientes y médicos. De cada paciente se desea guardar el código, nombre, apellidos, dirección, distrito, provincia, código postal, teléfono y fecha de nacimiento. De cada médico se desea guardar el código, nombre, apellidos, teléfono 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 código de ingreso (que se incrementará automáticamente cada vez que el paciente realice un ingreso), el número de habitación y cama en la que el paciente realiza el ingreso y la fecha de ingreso. Un médico puede atender varios ingresos, pero el ingreso de un paciente solo puede ser atendido por un único médico. Un paciente puede realizar varios ingresos en el hospital”. 3- Tienda Informática Se desea informatizar la gestión de una tienda informática. La tienda dispone de una serie de productos que se pueden vender a los clientes. “De cada producto informático se desea guardar el código, descripción, precio y número de existencias. De cada cliente se desea guardar el código, nombre, apellidos, dirección y número de teléfono. 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 artículo quedará registrada la compra en la base de datos junto con la fecha en la que se ha comprado el artículo. 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 código, nombre, apellidos, dirección, provincia y número de teléfono”. 4- Hijos de una Persona Considera la siguiente relación 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, dirección y teléfono. 5- Liga de futbol profesional La liga de fútbol profesional, presidida por Don Ángel María Villar, ha decidido informatizar sus instalaciones creando una base de datos para guardar la información 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 posición en la que juega (portero, defensa, centrocampista...). Cada jugador tiene un código de jugador que lo identifica 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 año de fundación del equipo y el distrito del equipo. Cada equipo también tiene un código que lo identifica de manera única. Un jugador solo puede pertenecer a un solo 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 código numérico para identificar el partido. También se quiere llevar un recuento de los goles que hay en cada partido. Se quiere almacenar el minuto en el que se realizar el gol y la descripción 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 fútbol:

BASE DE DATOS


17

(Numero de documento, tipo de documento, nombre, apellidos, fecha de nacimiento, equipo del que es presidente y año en el que fue elegido presidente). Un equipo de fútbol tan sólo puede tener un presidente, y una persona sólo puede ser presidente de un equipo de la liga. 6- Gestión de carreras de Institución Educativa “Se desea informatizar la gestión de un centro de enseñanza 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, dirección, distrito, DNI, fecha de nacimiento, código postal y teléfono. Los alumnos se matriculan en una o más asignaturas, y de ellas se desea almacenar el código de asignatura, nombre y número de horas que se imparten a la semana. Un profesor del centro puede impartir varias asignaturas, pero una asignatura sólo es impartida por un único profesor. De cada una de las asignaturas se desea almacenar también la nota que saca el alumno y las incidencias que puedan darse con él. Además, se desea llevar un control de las carreras que se imparten en el centro de enseñanza. De cada carrera se guardará el código y el nombre. En una carrera se imparten varias asignaturas, y una asignatura sólo puede ser impartida en una carrera. Las asignaturas se imparten en diferentes aulas del centro. De cada aula se quiere almacenar el código, piso del centro en el que se encuentra y número 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, día y hora en el que se imparten cada una de las asignaturas en las distintas aulas. La dirección del centro también designa a varios profesores como tutores en cada uno de las carreras. Un profesor es tutor tan sólo de una carrera. Una carrera tiene un único tutor. Se habrá de tener en cuenta que puede que haya profesores que no sean tutores de ninguna carrera”. 7- Organización interna de una empresa Una empresa necesita organizar la siguiente información referente a su organización interna. La empresa está organizada en una serie de departamentos. Cada departamento tiene un código, nombre y presupuesto anual. Cada departamento está ubicado en un centro de trabajo. La información que se desea guardar del centro de trabajo es el código de centro, nombre, distrito y dirección del centro. La empresa tiene una serie de empleados. Cada empleado tiene un teléfono, fecha de alta en la empresa, DNI y nombre. De cada empleado también interesa saber el número de hijos que tiene y el salario de cada empleado. A esta empresa también le interesa tener guardada información sobre los hijos de los empleados. Cada hijo de un empleado tendrá un código, nombre y fecha de nacimiento. Se desea mantener también información sobre las habilidades de los empleados (por ejemplo, mercadotecnia, trato con el cliente, fresador, operador de telefonía, etc…). Cada habilidad tendrá una descripción y un código”. Sobre este supuesto diseñar 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 más empleados. • Cada departamento se ubica en un único centro de trabajo. Estos se componen de uno o más departamentos. • Un empleado puede tener varios hijos. • Un empleado puede tener varias habilidades, y una misma habilidad puede ser poseída 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 vehículo. Es necesario guardar los datos personales de cada persona (nombre, apellidos, dirección, distrito, teléfono y DNI). De cada vehículo se desea almacenar la matrícula, la marca y el modelo. Una persona puede tener varios vehículos, y puede darse el caso de un vehículo pertenezca a varias personas a la vez. También se desea incorporar la información destinada a gestionar los accidentes del municipio. Cada accidente posee un número de referencia correlativo según 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 vehículos. Se desea llevar también un registro de las multas que se aplican. Cada multa tendrá asignado un número de referencia correlativo. Además, deberá registrarse la fecha, hora, lugar de infracción e importe de la misma. Una multa solo se aplicará a un conductor e involucra a un solo vehículo.” 9- Agencia de Viajes Una agencia de viajes desea informatizar toda la gestión 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 información. “La agencia desea guardar la siguiente información de los viajeros: Numero de Documento, tipo de documento, nombre, dirección y teléfono.

BASE DE DATOS


18

De cada uno de los viajes que maneja la agencia interesa guardar el código de viaje, número 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 sólo 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 código, nombre y otros datos que puedan ser de interés. Un viaje tiene un único lugar de destino y un único lugar de origen”. 10- Control de proyectos Una empresa desea diseñar una base de datos para almacenar en ella toda la información generada en cada uno de los proyectos que ésta realiza. “De cada uno de los proyectos realizados interesa almacenar el código, descripción, cuantía del proyecto, fecha de inicio y fecha de fin. Los proyectos son realizados por clientes de los que se desea guardar el código, teléfono, domicilio y razón 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 información: RUC, nombre, domicilio, teléfono, banco y número de cuenta. Un colaborador puede participar en varios proyectos. Los proyectos son realizados por uno o más colaboradores. Los colaboradores de los proyectos reciben pagos. De los pagos realizados se quiere guardar el número de pago, concepto, cantidad y fecha de pago. También interesa almacenar los diferentes tipos de pagos que puede realizar la empresa. De cada uno de los tipos de pagos se desea guardar el código y descripción. Un tipo de pago puede pertenecer a varios pagos”.

BASE DE DATOS


19

8 Modelo Relacional En este modelo se representan los datos y las relaciones entre estos, a través de una colección 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 características (atributos) de cada registro;

Modelo Entidad Relación • Entidades • Atributos • Clave o identificador • Relaciones.

Conversión hacia el Modelo Relacional • Entidades pasan a ser

tablas. • Atributos pasan a ser

campos o columnas, además en lo posible los atributos compuestos y polivalentes se eliminan..

• La clave del modelo E-R pasa a ser clave principal o clave primaria, además surge el dependiendo de las relaciones las claves foráneas o claves externas.

• Las relaciones deben implementarse según las reglas del modelo relacional.

• Normalización de tablas

Modelo Relacional • Tablas • Campos o columnas únicos y

en lo posible que no sean compuestos ni polivalentes

• Clave Primaria o clave Principal, clave foránea o clave externa

• Relaciones. • Tablas normalizadas.

8.1 Tablas y Campos Considerando nuestro ejemplo del empleado y el artículo:

Cada una de las columnas representa a los atributos de la entidad Empleados

Tabla Empleados

nombre puesto salario DNI Registros que contienen la información de la entidad Empleados

Juan Perez Cota vendedor 1000 10201020

Nora Mendez Angel vendedor 1000 12139574

… … … …

Tabla Articulos

clave descripción costo

502 colcha matrimonial 200

… … …

BASE DE DATOS


20

8.1.1 Eliminar atributo Compuesto. Tenemos dos alternativas:

Eliminar el atributo compuesto considerando todos sus atributos simples.

Personas

cambiar a

Personas DNI DNI nombres primerNombre direccion segundoNombre

apellidoPat apellidoMat_Esposo direccion

Eliminar los componentes individuales y considerar el atributo compuesto entero como un solo atributo En el ejemplo anterior también tenemos un atributo compuesto llamado dirección, en el caso de Perú la dirección presenta demasiadas posibilidades de configuración (Urbanizacion, Pueblo Joven, Carretera, Avenida, Jirón, callejón, interior, Lote, S/N etc…) por lo que muchas veces no es práctico descomponerlo, por tanto lo consideramos como un solo atributo.

8.1.2 Atributos Polivalentes. Requieren la introducción de entidades nuevas: cada atributo polivalente requiere una entidad en la cual pueda estar representado como un atributo monovalente. La nueva entidad contiene el atributo polivalente más clave principal de la entidad original; la clave principal de la nueva entidad es el conjunto de todos sus atributos

Personas

cambiar a

Personas TelefonosPers DNI DNI 1 DNI

primerNombre primerNombre NroTelefonico

segundoNombre segundoNombre apellidoPat apellidoPat apellidoMat_Esposo apellidoMat_Esposo direccion direccion telefonos …

…

8.2 Clave Principal y Clave Foránea Un SGBD relacional utiliza campos de clave principal para asociar rápidamente los datos de varias tablas y combinar esos datos de forma significativa. Una vez definida la clave principal, se puede utilizar en otras tablas para hacer referencia a la tabla que contiene la clave principal. Por ejemplo, un campo Id de la tabla Clientes podría aparecer también en la tabla Pedidos. En la tabla Clientes es la clave principal y en la tabla Pedidos es una clave externa. Una clave externa, en términos simples, es la clave principal de otra tabla.

1 Clave principal 2 Clave externa

En algunos casos, sera conveniente utilizar dos o más campos juntos como clave principal de una tabla. Por ejemplo, una tabla Detalles de pedidos que contenga artículos de línea de pedidos tendría dos columnas en su clave principal: Id. de pedido e Id. de producto. Cuando una clave principal está formada por más de una columna se denomina clave compuesta.

BASE DE DATOS


21

1 Una columna con el tipo de datos Autonumérico (en Access) o Int y con especificación de Identidad (SQL Server) suele ser una buena clave principal, porque garantiza que no habrá dos Id. de producto iguales

8.3 Relaciones Una vez establecido las claves principales de las tablas, podemos establecer las relaciones entre las mismas:

8.3.1 Relaciones 1:1 Si en la relación binaria, las dos entidades participan con cardinalidad máxima y mínima igual a uno, entonces:

Si las dos entidades tienen la misma clave principal, entonces se transforman en una sola tabla por la agregación de los atributos de las dos entidades y la clave es la clave de las entidades (es la misma en ambas).

Clientes InformEnvio

NumCliente 1:1 con 1:1 NumCliente

nombreCliente DireccionEnvio

Clientes

NumCliente nombreCliente DireccionEnvio

Si las dos entidades tienen distinto identificador, entonces en este caso también se integran en una relación combinando todos los atributos e incluyendo las claves primarias de ambas. Una de las dos claves primarias será la clave primaria de la relación resultante.

Tabla1 Tabla2

clave1 1:1 con 1:1 claveA

atributo1 atributoA

Tabla3

clave1 atributo1 claveA atributoA

Si en la relación binaria, alguna de las entidades participa con cardinalidad mínima igual a cero, entonces:

Cada entidad se transforma en una tabla con su propia clave principal, se construye una nueva tabla correspondiente a la relación, la clave de la misma estará formada por las claves de cada tabla y los atributos de la relación (si los hay).

Tabla1 Tabla2

clave1 0:1 Rel 1:1 claveA

atributo1 atributoA

Tabla1 1 1 Rel 1 1 Tabla2

clave1 claveA claveA

atributo1 clave1 atributoA

8.3.2 En las relaciones 1:N la clave primaria de la tabla con cardinalidad 1 pasa a la tabla cuya cardinalidad es N. Supongamos que tenemos las tablas de empleados y centroLaboral, asumiendo que un empleado trabaja solo en un centro laboral, y en un centro laboral trabajan varios empleados. Modelo E-R en notación UML

Empleados CentroLaboral

IDEmpleado 1 IDCentroLab

nombreEmp direccionCentroLab direccionEmp telefono

BASE DE DATOS


22

Para pasar al modelo relacional debemos establecer las claves principales y luego crear un campo en la tabla con cardinalidad varios que corresponda con la clave principal de la tabla con cardinalidad 1. El nuevo campo creado se le conoce como Clave Externa o Clave Foránea


IDEmpleado 1 IDCentroLab

nombreEmp direccionCentroLab direccionEmp teléfono IDEmpleado

Importante En la tabla destino donde se creara una copia de la clave principal es posible cambiarle el nombre, pero el tipo de dato que se asignó a la(s) clave(s) principal(es) en su tabla original debe coincidir obligatoriamente en la tabla destino.


IDEmpleado int 1 IDCentroLab int

nombreEmp varchar(30) direccionCentroLab varchar(150) direccionEmp varchar(150) teléfono char(9) IDTrabajador int

8.3.3 Toda relación N:M se crea una nueva tabla que tendrá mínimo como campos las dos claves primarias de las entidades que se asocian Considerando nuestro ejemplo de empleados y artículos Notación UML

Para pasar al modelo Relacional, luego de establecer las claves principales, debemos transformar la relación N:M a un equivalente 1:M, para ello creamos una tabla intermedia o tabla puente. En la nueva tabla creamos como mínimo y obligatoriamente dos campos que corresponden a las claves principales de las tablas que intervienen en la asociación.

Empleados Ventas Artículos

DNI DNI Clave nombre Clave descripción

puesto costo

salario

La nueva tabla debe estar relacionada con las tablas originales en forma obligatoria mediante relaciones 1:M


DNI 1 DNI 1 Clave

nombre Clave descripción

puesto costo

salario


Empleados

Clave Descripción Costo

Artículos

Venta

BASE DE DATOS


23

Finalmente tenemos el modelo que se implementara en un SGBD relacional como SQL Server, el cual una vez con los datos ingresados se verá así:

Tabla Empleados DNI nombre puesto salario

10201020 Juan Perez Cota vendedor 1000

12139574 Nora Mendez Angel vendedor 1000

… … … … Tabla Articulos

clave descripción costo

502 colcha matrimonial 200

… … …

Tabla Ventas DNI Clave

10201020 502

12139574 204

… … Importante: El nombre de la nueva tabla surge muchas veces del nombre de la relación (en el ejemplo la relación se llama venta), también es una buena práctica poner como nombre de la nueva tabla la combinación de los nombres de las tablas que intervienen en la relación N:M, por tanto también podría haberse llamado a la nueva tabla: EmpleadosArticulos.

Empleados EmpleadosArticulos Artículos

DNI 1 DNI 1 Clave


puesto costo

salario

En la nueva tabla creada, los campos corresponden a las claves principales de las tablas originales, pero es posible cambiarles el nombre dentro de la tabla puente, pero el tipo de dato que se asignó a las claves principales en su tabla original debe coincidir obligatoriamente en la nueva tabla.


DNI char(8) 1 DNIEmp char(8) 1 Clave int

nombre varchar(30) ClaveArt int descripción text

puesto varchar(30) costo money

salario money

Las claves principales pueden pasar a la nueva tabla o bien como clave compuesta o bien los dos como simples campos pero debemos evitar que uno de ellos pase como clave y el otro solo como campo. En ambas situaciones ya sea que pasen como claves compuestas o no, tiene sus ventajas y desventajas que se discutirá más adelante.


DNI 1 DNI 1 Clave


puesto costo

salario

La tabla puente puede tener otros campos adicionales e incluso un campo para clave principal: supongamos que deseamos saber cuándo se hizo una venta, y además cada venta genera un numero de comprobante único (número de boleta o factura, etc) entonces tendremos lo siguiente:


DNI 1 NroComprobante 1 Clave

nombre DNI descripción puesto Clave costo

salario FechaVenta

BASE DE DATOS


24

Si durante el análisis encontramos tres tablas relacionadas de esta manera: tabla1 tabla2 tabla3

1 1

Se deduce que entre la tabla1 y tabla3 existe una relación de varios a varios o N:M, por tanto en este caso ya no será necesario crear una tabla adicional o puente ya que tabla2 es la tabla puente y es la que se usara para eliminar la relación N:M.

8.3.4 En las relaciones N:M existen tres posibilidades: Si la cardinalidad es (0,1) en ambas entidades, se crea una tabla. Mientras que si la cardinalidad de una es (0,1) y de la otra es (1,1) se suele pasar la clave primaria de (1,1) a la de (0,1). Si la cardinalidad de ambas es (1,1) se pasa la clave de cualquiera de ellas a la otra.

8.3.5 Especialización y Generalización Los modelos lógicos no permiten representar las jerarquías de generalización ni los subconjuntos, en consecuencia se debe modelar las jerarquías de generalización y los subconjuntos usando solo entidades e interrelaciones. Existen tres alternativas: a- Integrar la jerarquía de generalización en una sola entidad uniendo los atributos de las subentidades y añadiendo estos a los de la superentidad. Se añade un atributo que permita discriminar, clasificar, categorizar, ordenar, etc, para indicar el caso al cual pertenece la entidad en consideración. Ejemplo Una tienda de modas y confección de trajes a medidas tiene clientes que pueden ser damas o caballeros los cuales tienen sus propias medidas corporales para la confección de los trajes. Modelo E-R (notación UML)

Clientes

CodCliente PrimerNombre ApellidoPat dirección cintura …

Caballeros Damas

anchoEspalda contornoBusto contornoPecho contornoCadera contornoEstomago sobreMamario … …

Correspondencia del modelo E-R al modelo relacional: En nuestro caso agregamos un campo llamado sexo. Dependiendo del SGBD, a los atributos que son propios de cada subentidad se le dará la propiedad de permitir valores nulos o requeridos.

Clientes CodCliente

primerNombre

apellidoPat

dirección

cintura

sexo

anchoEspalda

contornoPecho

contornoEstomago

…

contornoBusto

contornoCadera

sobreMamario

…

Las ventajas de esta solución son:

Es la solución más simple desde el punto de vista del esquema resultante.

Teóricamente, esta alternativa es aplicable a todos los tipos de jerarquías de generalización: total o parcial, superpuesta o no superpuesta.

Las desventajas de esta alternativa son dos:

Puede generar gran cantidad de valores nulos para los atributos que se aplican solo a las subentidades.

Todas las operaciones que tenían acceso solo a las subentidades tienen que buscar ahora el caso correspondiente dentro del conjunto completo de casos de la subentidad.

b- Eliminar la superentidad y retener las subentidades. Aquí los atributos heredados deben propagarse entre las subentidades. se modelan de manera independiente las distintas entidades, la clave principal original se convierte en clave principal de cada entidad. Esta alternativa tiene varias desventajas:

No es práctica para generalizaciones superpuestas o parciales; sólo es práctica para jerarquías totales y exclusivas.

Se pierde el concepto de que las subentidades originales son subconjuntos de la misma entidad.

Si el número de atributos de la superentidad (comunes a todas las entidades) es excesivo, su

BASE DE DATOS


25

duplicación en el esquema de cada subentidad no se justifica.

Cada operación que originalmente tenía acceso a sólo la superentidad debe tener acceso ahora a todos los casos de todas las subentidades.

En el ejemplo de la tienda de modas aplicando este enfoque se tendría lo siguiente:

Clientes

CodCliente PrimerNombre ApellidoPat dirección cintura …

Caballeros Damas

anchoEspalda contornoBusto contornoPecho contornoCadera contornoEstomago sobreMamario … …

Caballeros Damas

CodCliente CodCliente primerNombre primerNombre apellidoPat apellidoPat dirección dirección cintura cintura … … anchoEspalda contornoBusto contornoPecho contornoCadera contornoEstomago sobreMamario … …

c- Retener todas las entidades y establecer explícitamente las interrelaciones entre la superentidad y las subentidades. Todas las entidades originales y sus atributos se preservan en el esquema. Se añaden diferentes interrelaciones ES_UN para cada subentidad. Esta alternativa tiene 2 desventajas:

El esquema resultante es bastante complejo; por ejemplo, para insertar un nuevo caso de subentidad se requiere insertar dos casos adicionales: uno para la subentidad y otro para la interrelación con la superentidad.

Hay una redundancia inherente (al menos en el nivel conceptual) Al representar cada eslabón ES_UN en la jerarquía original a través de una interrelación explícita.

La ventaja principal es que modela las 4 combinaciones de jerarquías parcial/total y exclusiva/superpuesta. Ejemplo: Una empresa lleva el control de sus proyectos los cuales pueden ser proyectos de software, proyectos de hardware y proyectos bajo subcontrato, un proyecto puede tener varios miembros y un miembro puede trabajar en varios proyectos. Para desarrollar un proyecto de hardware en algunas ocasiones se puede usar varios componentes.

Proyectos 1: 1: MienbrosProyecto

NroProyecto tiene …

NombreProyecto Presupuesto …

ProyectoSoft ProyectoHard Subcontrato

componenteHardw NumTableros contratistaPrincipal … … …

1: usos 0:

ComponenteHardw

…

Bajo este enfoque el modelo anterior quedaría así:

BASE DE DATOS


26

Proyectos 1: 1: MienbrosProyecto

NroProyecto tiene …

NombreProyecto Presupuesto

esSoftware 0:1 … 0:1 esSubcontratado

0:1 esHardware

1:1 1:1 1:1

ProyectoSoft ProyectoHard Subcontrato

NroProyecto NroProyecto NroProyecto componenteHardw NumTableros contratistaPrincipal

… … …

1: usos 0:

ComponenteHardw

…

8.4 Problemas con las interrelaciones A la hora de establecer las interrelaciones existentes en un sistema de bases de datos nos podemos encontrar dos problemas:

Relaciones recursivas: un elemento se relaciona consigo mismo directamente.

Relaciones circulares o cíclicas: 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 relación con integridad referencial y decidimos eliminar en cascada (al eliminar una clave de la tabla A se eliminan los elementos relacionados en la tabla B).

Relaciones Recursivas Una relación recursiva R de una entidad E a sí misma se modela como una nueva relación que incluye dos atributos; ambos corresponden a la clave primaria de E, y sus nombres corresponden a los dos papeles de E en la relación. Uno de ellos (o ambos) se elige(n) como clave primaria para la nueva relación, de acuerdo con el tipo de interrelación (1:1, 1:N, N:M), como se expuso anteriormente. Supongamos la relación recursiva existen en la relación 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 Código Nombre Supervisor

102 Juan NO

105 Luis SI

821 María NO

956 Martín SI

Para solucionar la relación debemos crear una tabla formada por dos campos. Ambos campos deben ser el código del empleado pero como no podemos tener dos campos con el mismo nombre a uno de ellos le llamaremos código supervisor.

Tabla Puente CódigoEmpleado CódigoSupervisor

102 105

105 956

821 105

956 105

Para terminar de resolver la interrelación recursiva basta con definir dos interrelaciones entre la tabla empleados y la tabla puente de tipo 1: n. La primera relación se crea utilizando las claves Empleados[Código] y Tabla Puente[Código Empleado]. La segunda entre Empleados[Código] y Tabla Puente [Código Supervisor].

BASE DE DATOS


27

Empleados Codigo

nombre supervisor

1 supervisa

Empleados 1 Supervicion

Codigo CodigoSupervisor nombre 1 CodigoEmpleado

supervisor

Relaciones Ciclicas o Circulares Las interrelaciones cíclicas o circulares no son muy frecuentes y no existe una metodología estándar para su eliminación, normalmente son debidas a errores de diseño en la base de datos, principalmente en el diseño conceptual del sistema de datos. Por tanto si llegamos a este punto hay que volver a replantearse todo el diseño de la base de datos.

TablaA 1 TablaB

1 1

TablaD 1 TablaC

8.5 Atributos de las interrelaciones En la mayoría de las interrelaciones definidas será conveniente exigir integridad relacional entre las claves. Exigiendo la integridad referencial se consigue que en una relación de tipo 1: n o de tipo 1: 1, no se puede añadir ningún valor en la tabla destino si no existe en la tabla origen. Dicho con un ejemplo: en la relación Clientes y Pedidos la tabla Pedidos contiene un campo que se corresponde con el código del Cliente, si se exige la integridad referencial no se podrá escribir un código de cliente en la tabla Pedidos que no exista en la tabla Clientes; de no exigir la integridad referencial se podrán crear pedidos con códigos de clientes que no existen, generando incongruencia de datos en la base de datos. Definida la integridad referencial (siempre necesaria) podemos exigir la actualización en cascada (siempre necesaria); esta actualización implica que si cambiamos el código a un cliente, debemos actualizar dicho código en la tabla de pedidos, de no ser así, al cambiar el código a un cliente, perderemos los pedidos que tenía realizados. Para concluir debemos hablar de la eliminación en cascada (NO siempre necesaria), la eliminación 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 operación de eliminación en cascada por motivos diversos. Si en el caso de clientes y pedidos no se exige eliminación en cascada no se podrá borrar ningún cliente en tanto en cuanto tenga realizado algún pedido (de lo contrario tendríamos incongruencia de datos).

BASE DE DATOS


28

Ejercicio 1 A partir del modelo entidad relación que ya se resolvió anteriormente, generar el modelo relacional. Gestión de Ventas “Una empresa vende productos a varios clientes. Se necesita conocer los datos personales de los clientes (nombres, apellidos, DNI, dirección y fecha de nacimiento). Cada producto tiene un nombre y un código, 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 sólo puede ser suministrado por un proveedor, y que un proveedor puede suministrar diferentes productos. De cada proveedor se desea conocer el RUC, nombre y dirección”. Solución 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.

Clientes Compras Productos

DNI 1 DNI 1 CodProd nombre1 CodProd RUC

nombre2 nombreProd apellidoPatCli precioUnitProd

apellidoMatCli

dirección fechaNacCli 1

Proveedores

RUC nombreProv direccionProv

Ejercicio 2 A partir del modelo entidad relación que ya se resolvió anteriormente, generar el modelo relacional. Empresa de Transportes “Se desea informatizar la gestión de una empresa de transportes que reparte paquetes por el país. Los encargados de llevar los paquetes son los camioneros, de los que se quiere guardar el DNI, nombre, teléfono, dirección, salario, distrito en que vive. De los paquetes transportados interesa conocer el código de paquete, descripción, destinatario y dirección del destinatario. Un camionero distribuye muchos paquetes, y un paquete sólo puede ser distribuido por un camionero. De las provincias a las que llegan los paquetes interesa guardar el código de provincia y el nombre. Un paquete sólo puede llegar a una provincia. Sin embargo, a una provincia pueden llegar varios paquetes. De los camiones que llevan los camioneros, interesa conocer la matrícula, modelo, tipo y potencia. Un camionero puede conducir diferentes camiones en fechas diferentes, y un camión puede ser conducido por varios camioneros”. Solución

Paquetes camioneros Manejar

CodPaquete DNICamionero DNICamionero

nombre1Destinatario 1 telefonoCamionero 1 matricula

nombre2Destinatario direccionCamionero

apePatDestinatario salario apePatDestinatario DistritoCamionero

direccionDestinatario DNICamionero CodProvincia DNIDestinatario

1

1 Camiones

Provincias matricula

CodProvincia modelo nombreProvincia tipo

BASE DE DATOS


29

Ejercicio 3 A partir del modelo entidad relación que ya se resolvió anteriormente, generar el modelo relacional. Base de datos de un instituto A partir del siguiente enunciado diseñar el modelo entidad-relación. “Se desea diseñar la base de datos de un Instituto. En la base de datos se desea guardar los datos de los profesores del Instituto (DNI, nombre, dirección y teléfono). Los profesores imparten módulos, y cada módulo tiene un código y un nombre. Cada alumno está matriculado en uno o varios módulos. De cada alumno se desea guardar el nº de Expediente, nombre, apellidos y fecha de nacimiento. Los profesores pueden impartir varios módulos, pero un módulo sólo puede ser impartido por un profesor. Cada módulo tiene un grupo de alumnos, uno de los cuales es el delegado del grupo”. Solución Aquí tenemos un caso de relación 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 relación de uno a varios en la Entidad/tabla Alumnos consigo mismo. El tipo de relación es de uno a varios.

Profesores Modulos modulosAlumnos

DNIProf 1 CodModulo 1 CodModulo

nombre1Prof nombreMod NroExpediente

nombre2Prof DNIProf

apellidoPatProf

apellidoMatProf 1

direccionProf Representacion 1 Alumnos

telefonoProf NroExpedDelegado NroExpediente

NroExped nombre1Alumno

1 nombre2Alumno

ApellidoPatAlum

ApellidoMatAlum

fechaNac

Ejercicio 4 A partir del modelo entidad relación que ya se resolvió anteriormente, generar el modelo relacional. Concesionario de coches “A un concesionario de coches llegan clientes para comprar automóviles. De cada coche interesa saber la matrícula, modelo, marca y color. Un cliente puede comprar varios coches en el concesionario. Cuando un cliente compra un coche, se le hace una ficha en el concesionario con la siguiente información: dni, nombre, apellidos, dirección y teléfono. Los coches que el concesionario vende pueden ser nuevos o usados (de segunda mano). De los coches nuevos interesa saber el número de unidades que hay en el concesionario. De los coches viejos interesa el número de kilómetros que lleva recorridos. El concesionario también dispone de un taller en el que los mecánicos reparan los coches que llevan los clientes. Un mecánico repara varios coches a lo largo del día, y un coche puede ser reparado por varios mecánicos. Los mecánicos tienen un dni, nombre, apellidos, fecha de contratación y salario. Se desea guardar también la fecha en la que se repara cada vehículo y el número de horas que se tardado en arreglar cada automóvil”. Solución

BASE DE DATOS


30

Clientes Coches

DNICliente matricula

nombre1Cliente modelo

nombre2Cliente 1 marca

apellidoPatCli color

apellidoMatCli estadoCoche

direccionCli NroUnidades

telefonoCli kilometraje

DNICliente

Mecanicos 1

DNIMecanico

nombre1Mec

nombre2Mec 1 Revisiones

apellidoPatMec matricula

apellidoMatMec DNIMecanico

fechaContrato fechaRevision

Ejercicio 5 A partir del modelo entidad relación que ya se resolvió anteriormente, generar el modelo relacional. Biblioteca “En la biblioteca del centro se manejan fichas de autores y libros. En la ficha de cada autor se tiene el código de autor y el nombre. De cada libro se guarda el código, título, ISBN, editorial y número de página. 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 código y una localización. Un libro tiene muchos ejemplares y un ejemplar pertenece sólo a un libro. Los usuarios de la biblioteca del centro también disponen de ficha en la biblioteca y sacan ejemplares de ella. De cada usuario se guarda el código, nombre, dirección y teléfono. 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 préstamos interesa guardar la fecha de préstamo y la fecha de devolución”. Solución

Autores Escribe Libros

CodAutor 1 CodAutor 1 CodLibro

nombreAutor CodLibro titulo

ISBN editorial nroPaginas

1

Usuarios Prestamos Ejemplares

CodUsuario CodUsuario CodEjemplar

nombrUsu 1 CodEjemplar 1 localizacion

nombre2Usu fechaPrestamo CodLibro

apellidoPatUsu fechaDevolucion

apellidoMatUsu

direccionUsu

telefonoUsu

BASE DE DATOS


31

9 Modelamiento de base de datos con ERwin Data Modeler Esta herramienta permite realizar desde un modelo lógico de requerimientos de información y reglas de negocio que definan una base de datos, hasta un modelo físico, optimizado por las características específicas de la base de datos de destino. De este modo, se puede implementar la generación y mantenimiento de la misma, así como, el modelado de proyectos, dimensional y de ingeniería de la información. Con los conocimientos básicos de base de datos ya podemos comenzar a diseñar modelos de base de datos. Se

comenzara con el modo Lógico (modelo Entidad Relacion)

Diseño lógico con Erwin

Clic en el botón Create Model (también podemos presionar Ctrl+N o ir a File/New… )

Se mostrara la siguiente pantalla, en ella debemos marcar las opciones que se indican ya que las bases de datos que generemos serán para SQL Server

Clic aquí para crear un nuevo

modelo

BASE DE DATOS


32

Asegurémonos de estar en el modelo Logical

BASE DE DATOS


33

Tool Box Para realizar nuestro modelo Entidad Relación 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 botón derecho del mouse, se mostrara un menú contextual hacer clic en la opción Attributes… Se mostrara la siguiente ventana en la cual podemos crear nuevos atributos y también cambiar los tipos de datos.

BASE DE DATOS


34

Relaciones de Identificación Una relación es de identificación cuando una entidad depende de otra a tal punto que las llaves primarias de la antecesora serán también 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 técnicas del modelo relacional.

Relaciones de No Identificación o Referencia Este tipo de relaciones definen un vínculo entre dos entidades pero no de identificación, es decir las llaves de la antecesora pasan a la dependiente pero como atributos y no como llaves primarias. Para crear una relación entre dos entidades se hace clic en el botón que tiene la relación que deseamos, luego clic en la entidad principal, finalmente clic en la entidad dependiente. Para borrar una relación basta hacer clic sobre esta y luego presionar Supr Ejercicio 1 A partir del siguiente enunciado se desea realiza el modelo entidad-relación: “Una empresa vende productos a varios clientes. Se necesita conocer los datos personales de los clientes (nombres, apellidos, DNI, dirección y fecha de nacimiento). Cada producto tiene un nombre y un código, 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 sólo puede ser suministrado por un proveedor, y que un proveedor puede suministrar diferentes productos. De cada proveedor se desea conocer el RUC, nombre y dirección”. Solución Como ya conocemos los diferentes tipos de datos y además como elegir los tipos de datos procedemos a asignar los tipos de datos a los atributos. Con los conocimientos previos que tenemos para cada entidad elegimos la clave principal. En el modelo lógico podemos dejar expresado las relaciones varios a varios y nos quedaría la solución más básica para el enunciado tal como podemos ver en la primera imagen.

Como ya tenemos base teórica suficiente para salvar las relaciones varios a varios, podemos complementar el modelo lógico y proceder a crear las entidades intermedias que permiten eliminar las relaciones varios a varios. El nombre de la nueva entidad intermedia o puente en nuestro ejemplo es Clientes Productos, es solo una sugerencia pudiendo ser cambiado el nombre de la entidad en cualquier momento. En un primer momento la tabla intermedia o puente tiene una clave principal compuesta formada por las claves principales de las dos tablas que intervienen en la relación. De esta manera los registros de la tabla ClientesProductos serán únicos y no se podrán duplicar dando así más seguridad (se exige integridad referencial)

Clientes

DNICliente: CHAR(8)

Nombre: VARCHAR(30)

ApellidoPat: VARCHAR(30)

ApellidoMat: VARCHAR(30)

Direccion: VARCHAR(150)

FechaNac: DATE

Productos

CodigoProd: INTEGER

nombreProd: VARCHAR(30)

PrecioUnitario: MONEY

RUC: CHAR(11) (FK)

Proveedores

RUC: CHAR(11)

Nombre: VARCHAR(150)


ClientesProductos, es

una tabla usada para

romper la relacion varios

a varios entre clientes y

productos. El nombre de

la tabla es solo sugerido

pudiendose cambiar

Clientes

DNICliente: CHAR(8)

Nombre: VARCHAR(30)




FechaNac: DATE

Productos

CodigoProd: INTEGER



RUC: CHAR(11) (FK)

Proveedores

RUC: CHAR(11)



ClientesProductos

DNICliente: CHAR(8) (FK)

CodigoProd: INTEGER (FK)

BASE DE DATOS


35

A continuación se muestra otra forma de crear la tabla ClientesProductos, en este caso las claves principal de las tablas que intervienen en la relación varios a varios pasan a ser simples atributos, de esta manera es posible que pueda existir registros repetidos en la tabla ya que la tabla en si no tiene clave principal. ¿Por qué se dejaría una tabla sin clave? Existen situaciones en las que se desea que una entidad/tabla permita escribir de forma duplicada todo un registro o al menos los campos que son claves foráneas. Si se desea que suceda así es recomendable hacerlo si se cuenta con otro atributo más aparte de las claves foráneas en la entidad puente por ejemplo puede ser otro atributo/campo llamado fechaCompra.

Ejercicio 2 A partir del siguiente enunciado se desea realizar el modelo entidad-relación. “Se desea informatizar la gestión de una empresa de transportes que reparte paquetes por el país. Los encargados de llevar los paquetes son los camioneros, de los que se quiere guardar el DNI, nombre, teléfono, dirección, salario, distrito en que vive. De los paquetes transportados interesa conocer el código de paquete, descripción, destinatario y dirección del destinatario. Un camionero distribuye muchos paquetes, y un paquete sólo puede ser distribuido por un camionero. De las provincias a las que llegan los paquetes interesa guardar el código de provincia y el nombre. Un paquete sólo puede llegar a una provincia. Sin embargo, a una provincia pueden llegar varios paquetes. De los camiones que llevan los camioneros, interesa conocer la matrícula, modelo, tipo y potencia. Un camionero puede conducir diferentes camiones en fechas diferentes, y un camión puede ser conducido por varios camioneros”.

Clientes

DNICliente: CHAR(8)

Nombre: VARCHAR(30)




FechaNac: DATE

Productos

CodigoProd: INTEGER



RUC: CHAR(11) (FK)

Proveedores

RUC: CHAR(11)



ClientesProductos

DNICliente: CHAR(8) (FK)

CodigoProd: INTEGER (FK)

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

Productos

CodigoProd: integer NOT NULL

nombreProd: varchar(30) NOT NULL

PrecioUnitario: money NULL

RUC: char(11) NULL (FK)

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)

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)

Provincias

CodProvincia: INTEGER

Nombre: VARCHAR(30)

Camiones

Matricula: CHAR(6)

modelo: VARCHAR(30)

tipo: VARCHAR(30)

potencia: FLOAT

BASE DE DATOS


36

Ejercicio 3 A partir del siguiente enunciado diseñar el modelo entidad-relación. “Se desea diseñar la base de datos de un Instituto. En la base de datos se desea guardar los datos de los profesores del Instituto (DNI, nombre, dirección y teléfono). Los profesores imparten módulos, y cada módulo tiene un código y un nombre. Cada alumno está matriculado en uno o varios módulos. De cada alumno se desea guardar el nº de Expediente, nombre, apellidos y fecha de nacimiento. Los profesores pueden impartir varios módulos, pero un módulo sólo puede ser impartido por un profesor. Cada módulo tiene un grupo de alumnos, uno de los cuales es el delegado del grupo”. Aquí tenemos un caso de relación 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 relación de uno a varios en la Entidad/tabla Alumnos consigo mismo. El tipo de relación es de uno a varios.

Paquetes

CodPaquete: INTEGER

Descripcion: TEXT

destinatario: VARCHAR(150)

DestinatarioDireccion: VARCHAR(150)


CodProvincia: INTEGER (FK)

Camioneros

DNICamionero: CHAR(8)

CamioneroNombre: VARCHAR(30)

CamioneroTelefono: CHAR(9)

CamioneroDireccion: VARCHAR(150)

Salario: MONEY

CamioneroDistrito: VARCHAR(30)

Provincias

CodProvincia: INTEGER

Nombre: VARCHAR(30)

Camiones

Matricula: CHAR(6)

modelo: VARCHAR(30)

tipo: VARCHAR(30)

potencia: FLOAT

CamionesCamioneros

Matricula: CHAR(6) (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)

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

Provincias

CodProvincia: integer NOT NULL

Nombre: varchar(30) NULL

Camiones

Matricula: char(6) NOT NULL

modelo: varchar(30) NULL

tipo: varchar(30) NULL

potencia: float NULL

CamionesCamioneros

Matricula: char(6) NOT NULL (FK)

DNICamionero: char(8) NOT NULL (FK)

Profesores

DNI: CHAR(8)

nombre: VARCHAR(30)


telefono: CHAR(9)

Modulos

Codigo: INTEGER

Nombre: VARCHAR(30)

DNI: CHAR(8) (FK)

Alumnos

NroExpediente: INTEGER

Nombre: VARCHAR(30)



FechaNac: DATE

BASE DE DATOS


37

Concesionario de coches “A un concesionario de coches llegan clientes para comprar automóviles. De cada coche interesa saber la matrícula, modelo, marca y color. Un cliente puede comprar varios coches en el concesionario. Cuando un cliente compra un coche, se le hace una ficha en el concesionario con la siguiente información: dni, nombre, apellidos, dirección y teléfono. Los coches que el concesionario vende pueden ser nuevos o usados (de segunda mano). De los coches nuevos interesa saber el número de unidades que hay en el concesionario. De los coches viejos interesa el número de kilómetros que lleva recorridos. El concesionario también dispone de un taller en el que los mecánicos reparan los coches que llevan los clientes. Un mecánico repara varios coches a lo largo del día, y un coche puede ser reparado por varios mecánicos. Los mecánicos tienen un dni, nombre, apellidos, fecha de contratación y salario. Se desea guardar también la fecha en la que se repara cada vehículo y el número de horas que se tardado en arreglar cada automóvil”.

Coches

matricula

modelo

marca

color

estadoCoche

NumeroUnid

Kilometraje

DNICliente (FK)

Clientes

DNICliente

nombre

apellidos

direccion

telefono

Mecanicos

DNIMecanico

nombres

apellidos

FechaCont

Salario

Coches Mecanicos

CodigoReparacion

matricula (FK)

DNIMecanico (FK)

FechaReparacion

HoraReparacion

Profesores

DNI: CHAR(8)

nombre: VARCHAR(30)


telefono: CHAR(9)

Modulos

Codigo: INTEGER

Nombre: VARCHAR(30)

DNI: CHAR(8) (FK)

Alumnos

NroExpediente: INTEGER

Nombre: VARCHAR(30)



FechaNac: DATE

AlumnosModulos

NroExpediente: INTEGER (FK)

Codigo: INTEGER (FK)

BASE DE DATOS


38

Cuestionario 2 Cree el modelo E/R en Erwin y su correspondiente implementación en SQL Server para los siguientes

enunciados: 1- Ministerio de Defensa El Ministerio de Defensa desea diseñar 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 código de soldado (único), su nombre y apellidos, y su graduación. · Existen varios cuarteles, cada uno se define por su código de cuartel, nombre y ubicación. · Hay que tener en cuenta que existen diferentes Cuerpos del Ejército (Infantería, Artillería,....), y cada uno se define por un código de Cuerpo y denominación. · Los soldados están agrupados en compañías, siendo significativa para cada una de éstas, el número de compañía y la actividad principal que realiza. · Se desea controlar los servicios que realizan los soldados (guardias, imaginarias, cuarteleros,...), y se definen por el código de servicio y descripción. Consideraciones de diseño: · Un soldado pertenece a un único cuerpo y a una única compañía, durante todo el servicio militar. A una compañía pueden pertenecer soldados de diferentes cuerpos, no habiendo relación directa entre compañías y cuerpos. · Los soldados de una misma compañía pueden estar destinados en diferentes cuarteles, es decir, una compañía puede estar ubicada en varios cuarteles, y en un cuartel puede haber varias compañías. Eso sí, un soldado sólo está en un cuartel. · Un soldado realiza varios servicios a lo largo de la mili. Un mismo servicio puede ser realizado por más de un soldado (con independencia de la compañía), siendo significativa la fecha de realización. 2- Control de carreteras Diseñar un modelo E/R que recoja la organización de una base de datos para contener la información sobre todas las carreteras del país, sabiendo que se deben cumplir las siguientes especificaciones: - Las carreteras están divididas en varias categorías (locales, comerciales, regionales, nacionales, autovías, 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 físicamente o es que confluye en otra(s) carretera(s). En este caso, interesa conocer con qué carretera confluye y en qué kilómetro, tramo y comuna. 3- Cadena de hoteles “Cada hotel (del que interesa almacenar su nombre, dirección, teléfono, año de construcción, etc.) se encuentra clasificado obligatoriamente en una categoría (por ejemplo, tres estrellas) pudiendo bajar o aumentar de categoría. Cada categoría tiene asociada diversas informaciones, como, por ejemplo, el tipo de impuesto a la renta que le corresponde y la descripción. Los hoteles tiene diferentes clases de habitaciones (suites, dobles, individuales, etc.), que se numeran de forma que se pueda identificar fácilmente la planta en la que se encuentran. Así pues, de cada habitación se desea guardar el código y el tipo de habitación. Los particulares pueden realizar reservas de las habitaciones de los hoteles. En la reserva de los particulares figurarán el nombre, la dirección y el teléfono. Las agencias de viaje también pueden realizar reservas de las habitaciones. En caso de que la reserva la realiza una agencia de viajes, se necesitarán los mismos datos que para los particulares, además del nombre de la persona para quien la agencia de viajes está realizando la reserva. En los dos casos anteriores también se debe almacenar el precio de la reserva, la fecha de inicio y la fecha de fin de la reserva”. 4- Compañía aérea Se desea almacenar la información de una compañía aérea en una B.D relacional. La compañía aérea tiene tres recursos principales: Aviones, pilotos, tripulación. De cada piloto se desea conocer su código, nombre y horas de vuelo. De los miembros de la tripulación solo se tendrá el código y el nombre. Los pilotos y la tripulación tienen una base a la que regresan después de cada jornada. Un vuelo va desde un origen a un destino a una hora concreta y tiene código de vuelo. De cada vuelo se desea saber el avión en el que se va a hacer o en el que se ha hecho, el piloto y la tripulación. Cada avión tiene un código, un nombre y es de un tipo (boing, airbus, entre otros). También tiene una base donde es sometido a mantenimiento.

BASE DE DATOS


39

5- Asignación de personal de líneas aéreas Construir un diagrama entidad-relación para una empresa de líneas aéreas. La empresa tiene distintos tipos de empleados (pilotos, personal auxiliar de vuelo, mecánicos, personal de gestión, de administración, de limpieza,…). La información que mantiene sobre sus empleados es: número de empleado, nombre, cargo, dirección y salario. Además, le interesa almacenar datos sobre: los vuelos que realiza (número de vuelo, avión empleado, fecha, origen, destino, hora de salida, hora de llegada y empleados asignados al vuelo), los pasajeros registrados en cada vuelo (nombre, dirección y teléfono) y los aviones que posee la empresa (constructor, número de modelo, número de serie). En el caso de los pilotos, se desea hacer constar además 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 gestión de un sistema de ventas. La empresa necesita llevar un control de proveedores, clientes, productos y ventas. Un proveedor tiene un RUC, nombre, dirección, teléfono y página web. Un cliente también tiene RUC, nombre, dirección, pero puede tener varios teléfonos de contacto. La dirección se entiende como Jr/Av/PJ/Urb, Distrito y Provincia. Un producto tiene un id único, nombre, precio actual, stock y nombre del proveedor. Además se organizan en categorías, y cada producto va sólo en una categoría. Una categoría tiene id, nombre y descripción. Por razones de contabilidad, se debe registrar la información de cada venta con un id, fecha, cliente, descuento y monto final. Además se debe guardar el precio al momento de la venta, la cantidad vendida y el monto total por el producto. 7- Sedes olímpicas Las sedes olímpicas 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 localización (ejemplo: centro, esquina- NE, etc.). Un complejo tiene una localización, un jefe de organización individual y un área total ocupada. Los dos tipos de complejos (deporte único y polideportivo) tendrán diferentes tipos de información. Para cada tipo de sede, se conservará el número 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, duración, número de participantes, número 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, pértigas, barras paralelas, etc.). 8- Artículos y encargos Una base de datos para una pequeña empresa debe contener información acerca de clientes, artículos y pedidos. Hasta el momento se registran los siguientes datos en documentos varios: • Para cada cliente: Número de cliente (único), Direcciones de envío (varias por cliente), Saldo, Límite de crédito (depende del cliente, pero en ningún caso debe superar los 5000 soles), Descuento. • Para cada artículo: Número de artículo (único), Fábricas que lo distribuyen, Existencias de ese artículo en cada fábrica, Descripción del artículo. • Para cada pedido: Cada pedido tiene una cabecera y el cuerpo del pedido. La cabecera está formada por el número de cliente, dirección de envío y fecha del pedido. El cuerpo del pedido son varias líneas, en cada línea se especifican el número del artículo pedido y la cantidad. Además, se ha determinado que se debe almacenar la información de las fábricas. Sin embargo, dado el uso de distribuidores, se usará: Número de la fábrica (único) y Teléfono de contacto. Y se desean ver cuántos artículos (en total) provee la fábrica. También, por información estratégica, se podría incluir información de fábricas alternativas respecto de las que ya fabrican artículos para esta empresa. Nota: Una dirección 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 información. 9- Gestión de Proyectos por departamentos: Una Compañía está organizada en Departamentos. Cada Departamento tiene un nombre y número ú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 número de Proyectos, cada uno de los cuales tienen un nombre y número únicos y se efectúa en un solo lugar. Un Empleado puede trabajar en varios Proyectos, que no necesariamente están controlados por el mismo Departamento. Nos interesa el número de horas por semana que un empleado trabaja en el proyecto. Realice el diagrama Entidad-Relación que modele el problema mencionado anteriormente.

BASE DE DATOS


40

10- Reservaciones Instalaciones deportivas: Un centro de instalaciones deportivas quiere hacer una aplicación de reservas. En el centro existen instalaciones deportivas sus atributos son: Código, denominación (piscinas, gimnasios, frontones, etc.), estado. El centro en cuestión tiene socios, de los cuales se almacenan su código, dirección, ciudad, provincia, teléfono, nombre y estado. Existen una serie de artículos (balones, redes, raquetas, etc) cuyos atributos son: código, marca, denominación, estado. Dichos artículos se pueden alquilar junto con las reservas, las reservas tienen Nro de reserva, fecha de reserva. Cada instalación es reservada por un socio en una fecha dada desde una hora de inicio hasta una hora de fin siempre y cuando este al día en sus cuotas. Cada reserva puede tener asociada uno o varios artículos deportivos que se alquilan aparte. Por ejemplo si yo quiero hacer una reserva para jugar a voleibol tengo que reservar una instalación polideportivo más un artículo red, más un artículo balón. 11 Gestión de clubes de la Federación El problema a modelar es la gestión de clubes en una federación. El presidente de la federación quiere saber qué clubes hay y quién los preside. Cada club puede tener varios equipos y los equipos están formados por jugadores (porteros, defensas, centrales, delanteros,..). Además los equipos participan cada año en competiciones deportivas, cada una de las cuales tiene sus propios premios. 12- Fono ofertas: La empresa “La Eficiente S.A.” se dedica a vender productos por teléfono. La siguiente es la descripción de sus procesos de venta. Cuando alguien llama por teléfono para comprar, se le llama “cliente”, identificándolo apropiadamente. El cliente llena una orden de compra, con detalles de dirección de facturación, quien lo atendió, etc. Pero lo más importante, es que compra uno o más productos (en cantidades que se especifican). De los productos, por supuesto, además del precio unitario, se sabe la cantidad que la empresa tiene en stock. Por supuesto, con cada venta se genera una factura. Hay varios métodos de pago (tarjeta de crédito, cheque al contado, cheques a fecha, etc.), cada uno con varias características particulares. Cada factura puede cancelarse con uno sólo de los métodos de pago. Cada uno de los elementos ordenado puede ser despachado individualmente o en forma conjunta en un sólo paquete. Cada paquete tiene asociado un método de envío con muchos detalles (transportista, fecha de envío, etc.). Desde luego, un empleado prepara cada despacho; esta persona no necesariamente es la misma que recibe la orden de compra. 13- Almacén Se quiere registrar información correspondiente a un almacén, sus departamentos, sus empleados, sus productos y los fabricantes de estos productos: • Cada empleado está representado por un número de empleado, su nombre y dirección. Se debe indicar además a qué departamento pertenece • Cada departamento está representado por su nombre y se saben que empleados trabajan en él, quién es el jefe del departamento y los productos que vende • Cada producto está representado por su nombre, fabricante, precio, número de producto asignado por el fabricante y número de producto asignado por el almacén • Cada fabricante está representado por su nombre, dirección, productos que suministra al almacén 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 código, el primer apellido del cabeza de familia, un número de cuenta bancaria, una dirección, un teléfono y los nombres y NIF de las personas correspondientes. No existe límite en el número de personas asociadas a una entidad cliente. Además, 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 también está dado de alta en el cliente asociado con el perro de sus padres). Los clientes pueden tener varias mascotas, cada mascota tiene un código, 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 médico con cada enfermedad que tuvo y la fecha en la que enfermó. Adicionalmente cada mascota tiene un calendario de vacunación, en el que se registrará la fecha de cada vacuna, la enfermedad de la que se vacuna. 15- Avícola Una empresa avícola desea llevar el control de la alimentación diaria de las aves en cada una de las etapas básicas de crecimiento (bebe, joven y consumo). Cada ración de alimento tiene una composición 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

BASE DE DATOS


41

su nacimiento y en base al número de lote de incubación. Cada lote posee un tamaño de población y una fecha de entrega para el inicio de crianza. El control de alimentación de cada población de un lote de crianza a lo largo de cada etapa incluye la cantidad total de la ración de alimento que se coloca, la población de aves al inicio del día y el control de la merma en base al número de aves que mueren el día anterior. Agregar, establecer, cambiar o quitar la clave principal http://office.microsoft.com/es-es/access-help/agregar-establecer-cambiar-o-quitar-la-clave-principal-HA010014099.aspx

http://office.microsoft.com/es-es/access-help/agregar-establecer-cambiar-o-quitar-la-clave-principal-HA010014099.aspx

http://office.microsoft.com/es-es/access-help/agregar-establecer-cambiar-o-quitar-la-clave-principal-HA010014099.aspx

base de datos 7 8 9 2008 v2012 i

Documents