Ejemplos resueltos en Access

Jader Castro

Antes de entrar en detalles al diseño del primer ejemplo, veamos el procedimiento general:

Mundo real

RECOLECCIÓNY ANALISIS DE

REQUERIMIENTOS

Requerimientos de la base de datos

DISEÑO CONCEPTUAL

Esquema conceptual(en un modelo de datos de alto nivel)

(por ejemplo: modelo E/R)

DISEÑO LOGICO

ndependiente de S.G.B.D.

(TRANSFORMACION DEL MODELO DE DATOS)

Específico para cada S.G.B.D.

Esquema (conceptual) lógico(en el modelo de datos de S.G.B.D.)

DISEÑO FÍSICO

Esquema interno(para el mismo S.G.B.D.)

Recolección y análisis de requerimientos: 

• Los diseñadores entrevistan a los futuros usuarios de la base de datos para recoger y documentar sus necesidades de información.

• En paralelo, conviene definir los requerimientos funcionales que  consisten en operaciones (transacciones) que se aplicarán a la base de datos, e incluyen la obtención de datos y la actualización.

Diseño conceptual: 

• Una vez recogidos todos los requerimientos, el siguiente paso es crear un esquema conceptual para la base de datos mediante un modelo de datos conceptual de alto nivel.

 • El esquema conceptual contiene una descripción detallada de los

requerimientos de información de los usuarios, y contiene descripciones de los tipos de datos, relaciones entre ellos y restricciones.

 • Nosotros utilizaremos para el diseño de esquemas conceptuales el modelo

E-R (entidad‑relación), que describe los datos cono entidades, vínculos (relaciones) y atributos.

 

Diseño lógico de la base de datos (transformación de modelo de datos): 

• El siguiente paso en el proceso de diseño consiste en implementar de hecho la base de datos con un S.G.B.D. comercial, transformando el modelo conceptual al modelo de datos empleados por el S.G.B.D. (jerárquico, red o relacional).

 • En nuestro módulo haremos la implementación con un S.G.B.D. relacional,

por ser el modelo más utilizado por las empresas en la actualidad.

Diseño físico de la base de datos: 

• En este paso se especifican las estructuras de almacenamiento internas y la organización de los archivos de la base de datos.

 

Para los siguientes ejemplos aplicaremos se debe obtener:

a) Recolección y análisis de requerimientos.

b) Diseño conceptual: E-R.

Del cual hay que determinar:• Entidades y sus atributos• Relaciones.• Determinar si hay súper entidades.• Verificar si se puede aplicar la generalización y especialización.• verificar si se puede obtener agregación.• Pasar los diagramas E-R a tablas.• Aplicar los procesos de normalización.

1. Ejemplo:

Diseñar un esquema de base de datos en E-R, para un centro de enseñanza, que contenga la información sobre los alumnos, las asignaturas, y las calificaciones, que se obtienen en cada una de ellas. Desarrollar el modelo E-R y luego reducirlo a tablas.

Solución:

2. Del planteamiento del problemas podemos extraer la información dada por el usuario, de los preguntas y requerimientos que de el usuario se establecen las relaciones. También después habrá preguntas a resolver de parte del usuario final.

Entidades:

Alumno : ( atributos ) : cod_alum, nombres, apellidos, dirección, teléfono, carrera.

Asignatura: ( atributos): cod_asig, nombre_mat

Relación: Cursa: Alumno – Asignatura: ( atributos): calificación.

• Al pasar en diagramas E-R:

Alumno cursaAsignatura

Cod_alum

nombres

Apellidos

Dirección

carrera

Cod_ asigNombre_mat

Calificación

• para este ejemplo, solo son dos entidades, y para los requerimientos dados no aplica super-entidades o la generalización, porque no tienen atributos en común.

• Agregados: No aplica porque solo existen dos entidades.• Reducir los diagramas a tablas:

• Aplicando el proceso de normalización:• primera norma: Se cumple, ya que todos los atributos tienen valores simples,

no se repiten en columnas, los tipos de datos de cada columna es del mismo tipo, las filas no se repiten.

• Segunda forma: Observamos que los atributos de cada tabla dependen del atributo clave principal.

• Tercera forma normal: No se aplica, ya observando las tablas, vemos que no se presente dependencia transitiva, es decir, debe estar en norma 1, norma 2, y cada atributo no se referencie por otro que no sea la llave principal. Es decir no existe códigos de control secundarios.

• Forma normal de Boyce Codd: En este caso, Se trata de observar, se uno o mas atributos determinan a otro, es decir que aparte de la clave primaria puede existir otras claves secundarias, las cuales se llaman llave candidato. Para este caso no se aplica, no existe otra llave secundaria.

• Cuarta forma normal: No existe dependencia de valores múltiples, el estudiante de código 12 , tiene 3 registros en la tabla de relación pero cod_asig y calificación no son independientes.

2. Ejemplo:

Diseñar una base de datos, para una universidad que contenga información sobre los alumnos, asignaturas y los cursos que pueden estudiar. Construir un modelo E-R, pasarlo al modelo relacional, teniendo encuesta las restricciones:

a) Un alumno puede estar matriculado de muchas asignaturas.

b) Una asignatura solo puede pertenecer a una carrera.

c) Una carrera puede tener muchas asignaturas.

Solución:

1. Del enunciado del problema, se tiene como datos la información de los alumnos, asignaturas y cursos , con ellos las restricciones respectivas para organizar la información.

2. Modelo E-R

2.1 Entidades:

Alumnos: cod_alum, nombre, apellidos, dirección, teléfono.

Asignatura: cod_asig, nombre, intensidad horaria, prerrequisito, grado

Carrera: cod_carr, nombre, campus,

2.2 relaciones:

Matriculado: Alumno – Asignatura: pertenece: Asignatura – Carrera

2.4 pasar al diagrama E-R: Lo cual queda como sigue:

Alumno Matriculado

Asignatura

nombres

Apellidos

Dirección

Teléfono

Cod_ asigNombre_mat

Intensidad horaria

prerrequisito

Grado

Cod_alum

Pertenece

CarreraCod_carr

nombre campus

• 2.3 Súper entidades o generalización: No se aplica, porque las entidades no tienen atributos comunes.

• Agregación: No aplica, aunque existan 3 entidades, en las restricciones o peticiones de la base de datos no lo requiere, por ejemplo una de las peticiones seria relacionar la entidad matricula con la entidad carrera, donde la entidad matricula resulta de la agregación o agrupación de la relación matriculado.

• Reducir los diagramas a tablas:

Tabla Alumno

         

Cod_Alum nombres Apellidos Dirección Teléfono

         

         

         

Tabla Asignatura  

           

Cod_Asig nombre_mat Intensidad horaria Prerrequisito Grado Cod_carr

           

           

           

Tabla Matriculado

   

Cod_Alum Cod_Asig   

   

   

Tabla Carrera

     

Cod_carr Nombre campus     

     

     

• Aplicando el proceso de normalización:• 1 forma normal: la cumple cada tabla.• 2 forma normal: Debemos si se aplica en cada tabla, observemos los atributos

depende de la clave principal en todas las tablas excepto en la tabla asignatura, donde el atributo cod_carr no depende de la clave principal cod_Asig.

Observemos que cod_carr no referencia atributos en Asignatura.

• 3 forma normal: No aplica, por que en las tablas no existe dependencia transitiva, ya que en las tablas observamos que no existe otra llave primaria como los códigos en cada tabla, excepto en el caso de la tabla asignatura que aparece la clave cod_carr.

• Forma normal de Boyce Codd: En este caso, Se trata de observar, se uno o mas atributos determinan a otro, es decir que aparte de la clave primaria puede existir otras claves secundarias, las cuales se llaman llave candidato. Para este caso no se aplica, no existe otra llave secundaria, excepto en la tabla Asignatura en el modelo relacional: que aparece cod_carr.

• Lo que se hace es crear una superllave en la tabla asignatura con la llave cod_asig y cod_carr, que hace estas dos refencie a los demás atributos.

• 4 cuarta forma normal: No aplica por que no existe atributos múltiples en una misma tabla, ya que no se presenta el caso en que un alumno puede matricular las asignaturas de una carrera, también puede cursa 2 carreras de seguido, en este caso, se presenta en la tabla los datos de cada asignatura por grupos.

3. Ejemplo:

Diseñar una base de datos para una universidad que contenga la información de los alumnos, asignaturas y los profesores. Construir el modelo E-R, y luego pasarlo al modelo relacional, Teniendo en cuenta las siguientes restricciones.

a) Una asignatura puede estar impartida por muchos profesores ( no a la vez), ya que pueden existir varias grupos.

b) Un profesor puede dar clases de muchas asignaturas. Un alumno puede estar matriculado en varias asignaturas

c) Se necesita tener constancia de la asignaturas en las que esta matriculado un alumno, la nota obtenida, y el profesor que le ha calificado.

d) Un alumno no puede estar matriculado de la misma asignatura con dos profesores distintos.

Solución:

1. Entidades:

• Alumnos: cod_alum, nombres, apellidos, dirección, teléfono

• Asignatura: cod_asig, nombre_mat, intensidad horaria,

• profesores: cod_prof, nombres, apellidos, direccion, teléfono

• Grupos: cod_grup, nombre, max_num_alum.

2. Relaciones:

• Impartidad: Asignatura – Profesor: uno a muchos.

• Clase: profesor – asignatura: uno – muchos

• Matriculado: alumno – asignatura : uno – muchos: nota, cod_prof.

• Asignado a : profesor – grupo: muchos - muchos.

• Registrado a : grupo - alumno: uno - muchos.

• incondicional : matriculado – profesores : uno – uno

Observación: que las relaciones impartida y clase, intervienen las mismas entidades, pero en orden contrario, lo cual es lo mismo que dejar una y la relación seria de mucho – muchos. Dejamos a impartida: asignatura – profesor : muchos – muchos.

Una asignatura puede ser vista por dos alumnos diferentes, lo cual la relación asignatura – alumno es de uno – muchos, podemos dejar la relación Matriculado de muchos - muchos.

Impartida

Asignado a

Registrado a

Matriculado

Nota Cod_prof

• 2.3 Súper entidades o generalización : Observemos que las entidades Alumno y Profesor, tienen 4 atributos en común: nombres, Apellidos, dirección, teléfono.

• Se crea la súper entidad Persona que tiene los atributos comunes, de las entidades Alumno y Profesor.

• 2.4 Especialización: Se aplica, se obtiene uniendo la Entidad fuerte Persona a través del triangulo ISA con las entidades débiles Alumno y Profesores.

• 2.5 Agregación o agrupación: Se aplica en el caso c) y d)

Relación : Matricula – Profesor : muchos – uno: atributo: nota.• Donde, Matricula es la Entidad obtenida de la relación Matriculado entre las

entidades Alumno y Asignatura. • Por cada pareja ( Alumno, asignatura) existe un único profesor, relación tipo

muchos – uno, el cual da la nota.

2.6 Reducir los diagramas a tablas:

Tabla Persona

cod_pers

nombres Apellidos

Dirección Teléfono

         

         

         

Tabla Alumno

cod_pers

cod_alum

   

   

   

Tabla Profesor

cod_pers cod_prof

   

   

   

Tabla Asignatura

cod_asig

nombre_mat

Intensidad_horaria

     

     

     

Tabla Grupo

cod_grup Nombre_grupMax_num_alum

     

     

     

Tabla Relación Matriculado

cod_alum cod_asig nota

     

     

     

Tabla Relación Registro

cod_alum cod_asig nota cod_prof

       

       

       

Tabla Relación Registro es igual a Tabla Relación Matriculado con atributo cod_prof::::

Debe eliminarse Tabla relación Matriculado

Tabla Relación Impartida

cod_prof cod_asig

   

   

   

Tabla Relación Registrado a Alumno

cod_pers cod_alum cod_grup

     

     

     

Tabla Relación Asignado a

cod_prof cod_grup

   

   

   

Tabla Relación Registrado a igual a tabla Alumno con atributo adicional de cod_grup

Debe eliminarse tabla alumno.

• 2.6 Aplicar normalización:• 1 forma normal: se aplica, verificando que en cada tabla sus atributos sean

atómicos y no múltiples.• 2 forma normal: Observemos en las tablas que se cumpla que todos los

atributos de una tabla dependan de la llave primaria o si hay una llave compuesta ( dos lleves primarías) el o los atributos deben depender de esta llave compuesta y no parcialmente solo por alguna de ellas.

• las tablas de las entidades, cumplen la 2 forma normal.• La tabla Relación Registro, cumple 2 forma normal, porque cumple que de sus 4

atributos, 3 son llaves primarias, y atributo nota depende de la tripleta, cod_alum, cod_asig, cod_prof.

• las demás tablas de relación también cumplen la 2 forma normal.• 3 forma normal: las tablas de relación cumplen la 3 forma normal, ya que los

atributos dependen de la llave primaria o llave compuesta.• 4 forma normal: se cumple, ya que los atributos tienen valores atómicos y no

múltiples.

4. ejemplo:

Diseñar una base de datos para una discoteca-videoteca que contenga información de los videos, discos, socios, empleados, y prestamos.

Construir el modelo E-R, pasarlo al modelo relacional, teniendo encuenta las restricciones:• un socio puede tener un prestamos de varios videos y discos a la vez.• un video o disco puede estar prestado a un socio.• un empleado puede prestar muchos videos o discos.• Cuando se realiza un préstamo debe aparecer el socio, el video, la fecha y el

empleado.• En los discos debe aparecer información sobre el autor y en los videos sobre el

protagonista principal.

5. Ejemplo:

Se desea diseñar una base de datos para un centro comercial organizado por departamentos que contenga información sobre los clientes que han comprado algo, los trabajadores, el genero de oferta, y las ventas realizadas. Construir un modelo E-R y pasarlo luego al modelo relacional teniendo en cuenta las siguientes restricciones:• Existen 3 tipos de trabajadores: gerentes, jefes y vendedores.• cada departamento esta gobernado por un gerente.• Un determinado producto solo se encuentra en un departamento.• Los jefes y los vendedores solo pueden pertenecer a un único departamento.• un gerente tiene a su cargo un cierto numero de jefes y estos a su vez un cierto

numero de vendedores.• una venta la realiza un vendedor a un cliente y debe quedar constancia del

articulo vendido. Solo un articulo por apunte de venta.