Práctica de

Normalización
y SQL

Base de Datos I

1
Práctica de Normalización y
SQL.
Introducción
Tomaremos el caso de los estudiantes, las carreras en las que se anota, las materias
en las que se inscribe y sus exámenes. Estos temas conocidos por todos nos
ayudarán a aplicar lo estudiado para lograr una primer experiencia que consolide
lo aprendido.

Es muy importante destacar que la tarea de construir un modelo de datos para que
el sistema que lo use cumpla con todos los requerimientos del usuario, requiere un
proceso de relevamiento, de raciocinio y comunicación de parte del Analista, para
lo cual éste debe usar una metodología para recopilar datos y expresar con la mayor
claridad posible al usuario final, para que este pueda entender lo que la aplicación
está previsto atender y poder tener una noción de lo que no va a cubrir el sistema.
Sobre este punto existen metodologías probadas como Rational Unified Process
(RUP) que está orientada a aplicaciones de gran porte, que abarcan la totalidad de
una empresa, con múltiples actores y otras mas acotadas, para proyectos de menor
envergadura, llamadas metodologías Ágiles, destacamos en éstas a SCRUM y a
Extreme Programming (XP). Cada una de éstas necesitaría para su tratamiento
respectivas materias de una carrera relacionada. Este trabajo que presentamos en
esta actividad es un proceso real que se cumple durante el Análisis y del Diseño de
Sistemas y solamente se dedica a las estructuras de datos no se ocupa de cómo son
los procesos o funciones de negocios.

El caso de estudio o narrativa

A continuación presentamos una breve narración que describe las características

del negocio, para el que se debe modelar lo que se necesita registrar en este futuro
sistema.

“En una universidad se deben organizar las tablas para poder almacenar la
información de estudiantes, inscripciones, exámenes, materias, profesores.

Los estudiantes tienen apellido, nombre, tipo y numero de documento, es necesario

conocer la edad, el sexo, en que colegio finalizó el secundario, su talla, peso, la fecha
del ingreso, su teléfono móvil, mas uno fijo y otros teléfonos que puedan servir para
comunicarse, su domicilio familiar y el que tiene en córdoba, el promedio obtenido
en su colegio de origen. Debe conocerse si existe un vínculo familiar con otro
estudiante, cónyuge, hijo, padre, madre y hermano, limitando esta relación
solamente a éstos vínculos.

2
Además se debe registrar las carreras que se dictan en esta casa de estudios,
debiendo registrar el nombre, los títulos que otorga, (al menos dos, pero pueden ser
mas), una carrera puede tener varios planes y estos planes se relacionan con
diversas materias.

Las carreras se dictan en las distintas sedes de la Universidad y se requiere saber

cuál carrera se dicta en cada sede. Actualmente la Universidad tiene sedes propias
y de tercero como los centros CAU, centros CEO. Estas sedes se deben registrar con
el nombre, su tipo, dirección, localidad y provincia.

Por ahora se necesitará registrar qué estudiante está inscripto en cada carrera, en
que fecha lo hizo, la fecha de baja y las razones de la misma.”

Para iniciar buscaremos las entidades, los atributos y las relaciones. Para detectar
las Entidades nos fijaremos en los sustantivos:

“En una universidad se deben organizar las tablas para poder almacenar la
información de estudiantes, inscripciones, exámenes, materias, profesores.

Los estudiantes tienen apellido, nombre, tipo y numero de documento, es necesario

conocer la edad, el sexo, en que colegio finalizó el secundario y la localidad del
mismo, que puede ser distinta del estudiante, su talla, peso, la fecha del ingreso, su
teléfono móvil, mas uno fijo y otros teléfonos que puedan servir para comunicarse,
su domicilio familiar y el que tiene en córdoba, el promedio obtenido en su colegio
de origen. Debe conocerse si existe un vínculo familiar con otro estudiante, cónyuge,
hijo, padre, madre y hermano, limitando esta relación solamente a éstos vínculos.

Además se debe registrar las carreras que se dictan en esta casa de estudios,
debiendo registrar el nombre, los títulos que otorga, (al menos dos, pero pueden ser
mas), una carrera puede tener varios planes y estos planes se relacionan con
diversas materias.

Las carreras se dictan en las distintas sedes de la Universidad y se requiere saber

cuál carrera se dicta en cada sede. Actualmente la Universidad tiene sedes propias
y de tercero como los centros CAU, centros CEO. De éstas sedes se deben registrar
con el nombre, su tipo, dirección, localidad y provincia.

Por ahora se necesitará registrar qué estudiante está inscripto en cada carrera, en
que fecha lo hizo, la fecha de baja y las razones de la misma.”

Luego hacemos una lista con los atributos que nos parece que describen a cada
entidad y tratamos de definir las claves candidatas.

“En una universidad se deben organizar las tablas para poder almacenar la
información de estudiantes, inscripciones, exámenes, materias, profesores.

3
 Los estudiantes tienen apellido, nombre, tipo y numero de documento, es necesario
conocer la edad, el sexo, en que colegio finalizó el secundario y la localidad del
colegio, que puede ser distinta del estudiante, su talla, peso, la fecha del ingreso,
su teléfono móvil, mas uno fijo y otros teléfonos que puedan servir para
comunicarse, su domicilio familiar y el que tiene en córdoba, el promedio obtenido
en su colegio de origen. Debe conocerse si existe un vínculo familiar con otro
estudiante, cónyuge, hijo, padre, madre y hermano, limitando esta relación
solamente a éstos vínculos.

Además se debe registrar las carreras que se dictan en esta casa de estudios,
debiendo registrar el nombre, los títulos que otorga, (al menos dos, pero pueden ser
mas), una carrera puede tener varios planes y estos planes se relacionan con
diversas materias. Los planes tienen un nombre y fecha desde y hasta para indicar
su vigencia

Las carreras se dictan en las distintas sedes de la Universidad y se requiere saber

cuál carrera se dicta en cada sede. Actualmente la Universidad tiene sedes propias
y de tercero como los centros CAU, centros CEO. De éstas sedes se deben registrar
con el nombre, su tipo, dirección, localidad y provincia.

Por ahora se necesitará registrar qué estudiante está inscripto en cada carrera, en
que fecha lo hizo, la fecha de baja y las razones de la misma.”

Universidad Sede Estudiante Colegio Vinculo Carrera Plan Materia

Nombre Nombre Nombre Nombre Tipo Vinculo nombre Nombre nombre
Tipo Apellido Localidad Titulo 1 Fecha dde
Dirección Edad (*) Titulo 2
Localidad Tipo doc Titulo?
Provincia Nro doc
Colegio
Promedio
Sexo
Tel. celu
Tel. fijo
Tel. otro
Domicilio1
Domicilio2

Este cuadro resume las entidades que podemos incluir en un primer momento. Este
proceso va a ir refinando y creando o eliminando entidades y atributos, a medida
que se realice el análisis adecuado.

4
Sobre las relaciones al buscar los verbos, encontramos que:

1. Los estudiantes se relacionan con una carrera inscribiéndose a ella.

2. Los estudiantes se relacionan entre sí por vínculos familiares.
3. Un estudiante egresa de un colegio con un promedio.
4. Las carreras se relacionan con las sedes por que allí se dictan.
5. Las carreras tienen planes asociados.
6. Los planes ser relacionan con materias
7. Las carreras están formadas de materias
8. Las carreras son de la universidad
9. Las sedes son de la universidad
10. Los estudiantes son de la universidad

Cuadro 1 – Relaciones halladas en la narrativa

Para continuar debiéramos revisar si están todas las entidades y si todas las que
tenemos, son las adecuadas.

Observemos que la Universidad es una entidad que surge del máximo nivel de
jerarquía, ya que carrera, estudiante, sede tienen relación con ellas. Pero
imaginando cuantas tuplas o filas en esta tabla puede tener para este pequeño
modelos de datos, podríamos asumir que aún no se necesita una entidad por que
estamos dentro de un contexto interno de la misma y esto puede definirse como
obvio y no considerar aún la entidad de Universidad, dejando para un futuro el
desarrollo de una entidad o varias para que den paso a una o varias tablas que
contenga los datos impositivos de la Universidad, como el cuit que nos ayudaría a
identificar las operaciones en otro sistema contable y ser usado por los sistemas
para imprimir listados, formularios con la información grabada en las tablas y no
deban ser escritos dentro del código de los programas listadotes. Por lo tanto
descartaremos la entidad Universidad y no incluiremos en el estudio las relaciones
8, 9 y 10.

En Estudiante, aunque el texto se refiere explícitamente a la edad, no es

conveniente incluir un atributo con este dato, que es derivado de la fecha de
nacimiento, es decir se puede calcular de esta fecha, por lo que debe definirse el
atributo fecha de nacimiento en vez de Edad. Este caso es aplicable a una
Antigüedad laboral, en los estudios, etc. En el primer cuadro se lo incluyó (ver (*)),
para contener todos los requerimientos originales pero en los siguientes cuadro
será reemplazado por la fecha de nacimiento.

Sobre la entidad Vínculo y Materia, tienen hasta ahora un solo atributo lo que nos
hace sospechar si pueden mantenerse como entidades o se pueden transformar en

5
atributos de las entidades más importantes. Veremos en los próximos pasos que
pasará con ellas. Una alternativa es dejarlas pendientes y en próximas entrevistas
revisar si hay atributos no nombrados para aceptar su participación o limitarla
como atributo en las entidades conocidas o descartarlas completamente.

Debiéramos resolver como se van a identificar cada una de estas entidades,

buscando entre los atributos descriptos a aquellos capaces de identificar a cada una
de las instancias de las entidades. Volvemos al cuadro para marcar con rojo las
Claves Candidatas y las claves primarias

Sede Estudiante Colegio Vinculo Carrera Plan Materia

Nombre Nombre Nombre Tipo Vinculo nombre nombre Nombre
Tipo Apellido Localidad Titulo1 Fechadde
Dirección FechaNac Titulo2
Localidad Tipo doc Titulo?
Provincia Nro doc
Promedio
Sexo
Tel. celu
Tel. fijo
Tel. otro
Domicilio1
Domicilio2

Cuadro 2 – Claves Candidatas del Modelo

Definidas cuáles son las claves candidatas, debemos analizar cuales pueden ser las
claves primarias de cada entidad.

Se deberán elegir aquellas claves candidatas con menor cantidad de atributos y que
no tengan nulos, por la Regla de Integridad de Entidades. Por ejemplo, en la entidad
Estudiante, hay dos claves candidatas, TIPO DOC y NRO DOC en conjunto y TEL.
CELULAR, pero como no es obligatorio, aunque muy difundido, tener un Teléfono
Celular, al encontrarnos que un estudiante puede no tener este número, se tendría
que poner un NULO para el atributo TEL. CELU, y esto está prohibido por la Regla
mencionada anteriormente, por lo tanto TEL. CELU debe ser descartada como Clave
primaria. Con TIPO y NRO DOC, el caso es que al ser compuesta, es habitual que los
Analistas definan una Clave Artificial como Clave Primaria, eligiendo un solo

6
atributo, numérico y lo que permite simplificar la identificación, este tipo de claves
son compactos no son modificables con el tiempo, y en entidades que son muy
importantes en un modelo y son referenciadas por numerosas entidades, es un
factor de peso que se elija una clave primaria simple, es decir que este resuelta en
una sola columna. Este tema de diseño de agregar claves artificiales esta
relacionado con el problema de diseño, “Dispersión de Claves primarias complejas
o compuestas”.

Este tema es tópico de múltiples discusiones que serán tratadas en un foro de la

materia.

A las claves candidatas no utilizadas como clave primaria, normalmente se usan

como forma de acceder rápidamente a la información y en la etapa de diseño,
cuando estamos definiendo índices, se les asigna un constraint o restricción del tipo
Unique, que tiene asociado un índice que acepta valores nulos. Esta clave artificial
se llama legajo y es habitual esta decisión, en las situaciones en que no es directa
la elección de clave primaria, basándose en las Claves candidatas.

En la entidad sede, la clave candidata es alfanumérica, lo que hace que influyan las
mayúsculas y minúsculas, por lo tanto se recurre a otra clave artificial, en este caso
la llamamos ID_SEDE. Lo mismo para el colegio, nombre es un atributo que puede
repetir valores, (cuantos colegios hay llamados ‘Sarmiento’ o similares) por lo tanto
asignaremos una clave artificial. En Carrera y en Plan consideramos con el mismo
criterio anterior que las mayúsculas y minúsculas y hacer repetir carreras con el
mismo nombre, diferenciados sólo por esto, por lo que le asignaremos ID_CARRERA
e ID_PLAN respectivamente

En materias, para agregar atributos propios, podemos sumar el atributo Créditos

que es la unidad de medida con que se define la duración del cursado. Y como en
los casos anteriores, se decide aplicarle una clave primaria artificial llamada
ID_MATERIA. Veamos como queda en el Cuadro 3

Sede Estudiante Colegio Carrera Plan Materia

ID_SEDE LEGAJO ID_COLE ID_CARRERA ID_PLAN ID_MATERI A

Nombre Nombre Nombre nombre nombre Nombre

Tipo Apellido Localidad Titulo1 Fechadde Créditos
Dirección FechaNac Titulo2
Localidad Tipo doc Titulo?
Provincia Nro doc
Colegio

7
 Promedio
Tel. celu
Sexo
Tel. fijo
Tel. otro
Domicilio1
Domicilio2

Cuadro 3 – Claves Candidatas del Modelo

Una vez definidas las claves primarias, para iniciar el uso de la normalización sobre
estas entidades tomaremos la de estudiante.

La primera forma normal indica que debe existir la clave primaria y que no debe
haber campos repetitivos.

En estudiantes tenemos la clave primaria, legajo y en primera instancia, dos grupo

de campos repetitivos, los referidos a teléfonos y los domicilios. Para los domicilios,
se recomienda consultar al usuario final, para que en su experiencia nos diga si dos
domicilios, caracterizados como local y familiar, alcanzan para las necesidades
propias de la institución o si hay ocasiones en que este modelo no serviría por
insuficiente, a las necesidades de notificaciones legales o de otro tipo. Si la
respuesta fuera que no hay más necesidades que estos dos domicilios. Al calificarlos
como domicilio local y domicilio familiar, queda definido estos atributos, y que no
son lo mismo y para el mismo uso. Si la respuesta es necesitamos conocer la mayor
cantidad posible de domicilios permanentes o temporales de la persona, es posible
que se los considere a todos ellos como repetitivos y que deben ser derivados a
otra entidad. Justamente esto es lo que fijamos para los teléfonos, veremos en el
siguiente cuadro, como descomponemos para llegar a la primera forma normal a la
entidad estudiante y como construimos la nueva entidad telefonoEstudiante

Estudiante telefonoEstudiante
LEGAJO CP Legajo CP y CF
Nombre Número CP
Apellido Tipo
FechaNac
Tipo doc
Nro doc
Sexo

8
 Promedio
Domicilioestudiante
Domiciliofamiliar

Cuadro 4. La entidad Estudiante y una entidad derivada para pasar a la 1FN

En la nueva entidad telefonoEstudiante tomamos al Legajo y al número

propiamente dicho como la clave primaria compuesta y no se define una clave
artificial por que telefonoEstudiante no será referenciada por otra tabla por lo que
el problema de diseño de ‘Propagación de Claves compuestas o complejas’ no se
presentará.

Esta forma de identificar a cada fila de telefonoEstudiante, permite que se puedan

cargar distintos números telefónicos asociados, si se dejara solo el legajo no podría
repetirse otra fila por que la clave primaria legajo solo, no lo permitiría, como se ve
en el ejemplo, en el primer cuadro la entidad con solamente el legajo como CP:

telefonoEstudiante

Legajo Numero Tipo

2152 035188999900 Fijo
2152 XXX REPETIDO! 0351156667788 Celular
Cuadro 5. La identificación de la entidad con solamente el legajo

telefonoEstudiante

Legajo Número Tipo

2152 035188999900 Fijo
2152 0351156667788 OK! Celular

Cuadro 5. La identificación de la entidad con dos columnas permite más de una fila por
estudiante

Esta forma de identificación debe ser decidida por el equipo de modeladores de

datos, para lograr un criterio común a seguir y que éste sea consistente a lo largo
de las numerosas entidades de este tipo. En anteriores ejemplos mostramos otro
diseño de la tabla telefonoEstudiante, esto muestra que no hay una solución única
cuando modela datos y por esto se deben buscar criterios consistentes, para lograr
soluciones eficaces sin enormes diferencias entre los equipos que producen
modelos de datos.

9
 En la revisión de las siguientes entidades, encontramos también en la entidad
Carrera la situación de atributos repetidos, operamos en el mismo sentido y
obtenemos así el cuadro con las entidades en primera forma normal.

Sede Estudiante Colegio Carrera Plan Materia

ID_SEDE LEGAJO ID_COLE ID_CARRERA ID_PLAN ID_MATERIA
Nombre Nombre Nombre Nombre nombre Nombre
Tipo Apellido Localidad Fechadde Créditos
Dirección FechaNac
Localidad Tipo doc
Provincia Nro doc
Sexo telefonoEstudiante títuloCarrera
Promedio Legajo Id_carrera
Domicilioestudiante Número Título
Domiciliofamiliar Tipo Tipo

Cuadro 6 – Modelo en Primera Forma Normal 1FN en rojo las claves primarias

Con este panorama, avanzaremos en la definición de las relaciones trabajando con

la herramienta

MySQL Workbench, introducidas en el módulo de Modelado Relacional. Como

vemos en la Figura 1, se está modelando la entidad Estudiantes, tomando
definiciones sobre los tipos de datos e indicando si son obligatorios (un check en la
columna NN de Not Null) con un rombo relleno con celeste o vacío si no es
obligatorio a la izquierda de la columna y cual es la clave primaria, con una llavecita
en el mismo costado.

10
Figura 1. Cargando la entidad Estudiante

Algunos detalles de la carga en Workbench de la información que tenemos hasta

ahora, usamos para la información alfanumérica al tipo de dato VARCHAR y para
números enteros como Legajo al tipo INT, si es numérico pero llevará decimales
optamos por DECIMAL(i, j) con i que indica la cantidad total de dígitos a registrar y
j la cantidad de decimales participantes, un promedio tiene como valor máximo a
10 y entre medio, por ejemplo a “6,33”, “9,50”, etc. Para esta columna de
promedios se usará DECIMAL(3,2) (Ver tipos de datos en esta documentación,
http://dev.mysql.com/doc/refman/5.0/es/numeric-types.html)

Avanzando con la carga en la herramienta Workbench, tendremos las entidades

cargadas, ver en la figura 2. Notar que no están reflejadas las relaciones, por lo
tanto cada entidad tiene sólo sus atributos propios, las relaciones se verán en los
próximos pasos.

11
Figura 2. El mapa de entidades en la herramienta

Resolución de las Relaciones

Analizamos las relaciones y cómo se resuelven en el modelo relacional. La forma de
determinar las características de las relaciones es tomar una de las entidades y
realizar la pregunta, “una instancia de la primera entidad, ¿con cuantas instancias
de la otra entidad se relaciona?, la respuesta pueden ser, con una o con mas de una
(usualmente se usa el varias para decir ‘mas de una’) y luego tomar la segunda
entidad y hacer la pregunta “una instancia de esta entidad ¿con cuantas instancias
de la primera entidad se relaciona?” .

Para empezar, tomaremos la primera relación

“Los estudiantes se relacionan con una carrera inscribiéndose a ella.”

Las preguntas serán:

Un estudiante, ¿con cuántas carreras puede relacionarse? La respuesta es “para ser

considerado estudiante debes estar inscripto en una carrera, al menos, y una vez
inscripto puede hacerlo en otra carrera, sin límites”

12
Es decir como mínimo a Una carrera y como máximo, es entonces mas de uno o
como generalización, “Muchos”. Esto hace una relación uno a muchos, simbolizada
como 1:M en esta parte de la relación.

La segunda pregunta es ¿con cuantos estudiantes puede relacionarse una carrera?

Y la generalización es con ninguno (una carrera recién implementada) o con mas de
uno o muchos, entonces este extremo es simbolizada también con 1:M lo que
resulta en una relación M:M que debe ser resuelta con la creación de una tercera
entidad, llamada asociativa o de relación… que contendrá las claves primarias de
ambas entidades que, en conjunto, serán parte o la totalidad de la clave primaria
de esta nueva entidad a la que llamaremos estudianteXcarrera, con atributos que
caracterizan la entidad, como la fecha de inscripción, fecha de baja y el motivo,
previéndose como valores de dominio, “abandono”, “egreso”, “cambio de
universidad”

En la herramienta bastó con elegir la relación N:M y elegir las entidades a relacionar
para que cree automáticamente la tercer entidad con las claves primarias. Como
vemos en la figura 3, usando MySQL Workbench.

13
Figura 3 – con la herramienta hemos elegido relacionar “n:m” o “muchos a muchos” dos
entidades y se crea la entidad asociativa

Luego de la creación, se crearon los atributos propios de la nueva entidad

asociativa. En este caso un estudiante se inscribe una sola vez en una carrera por lo
que las dos claves foráneas alcanzan para identificar las distintas filas, pero si esta
relación puede repetirse, como por ejemplo un estudiante puede anotarse varias
veces en una materia por haber quedado libre antes, deberá incluirse otra columna
como clave primaria, por ejemplo la fecha de inscripción, lo que nos lleva a una
clave primaria de tres columnas. En este punto debiéramos revisar si no
necesitamos una clave artificial, por si esta entidad será referenciada por varias
entidades para evitar lo visto como propagación de claves complejas.

La relación creada cuando pusimos en primera forma normal a los teléfonos de un

estudiante genera una relación 1:M por que las respuestas a las correspondientes
preguntas serian, “Un estudiante puede tener varios teléfonos” y un teléfono
pertenece a un solo estudiante” lo que produce la ubicación de una clave foránea
en el lado muchos de la relación, es decir en telefonoEstudiante. Con la herramienta
debe elegirse primero la entidad del lado del muchos (telefonoEstudiante) y luego
de elegir la relación que queremos graficar 1:n hacemos click sobre la clave primaria
de la entidad del extremo uno, es decir Estudiante y se crea automáticamente la
flecha que los describe y una entrada en los datos de la entidad que recibe la clave
foránea. Como se ve en la Figura 4.

14
Figura 4 Creación de una relación 1:n con la clave foránea del lado “Muchos”

Un detalle a destacar es que con el uso de la herramienta, se acotan pasos ya que

esta le pone el nombre a la columna (anteponiéndole el nombre de la entidad y el
nombre de la columna original) y el tipo de dato idéntico al de la clave primaria y
resuelve posteriormente la sintaxis en la sentencia SQL, reduciendo eventuales
errores y olvidos. Para completar el trabajo deberá elegirse ambas columnas como
claves primarias.

Con estas tareas, hemos iniciado la construcción de un modelo de datos relacional

luego de aplicarles las restantes Formas Normales, llegaremos a un modelo que
reúne las características deseadas, con redundancia mínima y flexibilidad ante los
cambios, es decir un modelo mejor de lo que inicialmente se puede prever.

Esta lectura pretende revisar la modelización, ya vista, la creación de las tablas, la

carga de las filas y las consultas que podrían realizarse sobre estas tablas.

Luego de repetir las preguntas en todas la relaciones llegamos a un modelo de

tablas que se muestra en la figura 5, se ha creado una nueva tabla para reflejar la
relación n:m que hay entre plan y materias, y se han creado todas las relaciones

15
posibles, aplicando las tres formas normales ya que todos los atributos no clave
dependen de toda la clave y de nada mas que de la clave primaria.

Figura 5 Modelo normalizado

Se habrá notado que no hemos modelado la relación asociada al requerimiento

específico que dice:

“Debe conocerse si existe un vínculo familiar con otro estudiante, cónyuge, hijo,
padre, madre y hermano, limitando esta relación solamente a éstos vínculos”.

Al ser una relación de estudiante con estudiante y muchos a muchos, por que un
estudiante puede tener varios parientes y un pariente puede ser de varios
estudiantes, esto crea una nueva tabla llamada parentezcoXestudiante que no
mostramos por cuestiones de espacio. Pero lo animamos a resolverla y consultar
con su tutor.

Creación de las tablas con SQL

Con la ayuda de Workbench generaremos las sentencias SQL capaces de crear las
tablas:

Añadimos las sentencias creadas para las 11 tablas resultantes.

16
Inicia con unos seteos para MySQL, que han sido generados automáticamente:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,

FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

Si no existiera crea el esquema muestro_BD1.

CREATE SCHEMA IF NOT EXISTS `muestro_BD1` DEFAULT CHARACTER SET latin1

COLLATE latin1_swedish_ci ;

Fija como esquema por defecto a muestro_BD1.

USE `muestro_BD1`;

Comienzan los “create table” que crean el modelo.

-- -----------------------------------------------------

-- Table `muestro_BD1`.`colegio`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `muestro_BD1`.`colegio` (

`idcolegio` INT NOT NULL ,

`nombre` VARCHAR(45) NOT NULL ,

`localidad` VARCHAR(45) NOT NULL ,

PRIMARY KEY (`idcolegio`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `muestro_BD1`.`estudiante`

17
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `muestro_BD1`.`estudiante` (

`legajo` INT NULL ,

`Apellido` VARCHAR(45) NOT NULL ,

`Nombre` VARCHAR(45) NOT NULL ,

`fecha nacimiento` TIMESTAMP NOT NULL ,

`tipo documento` VARCHAR(3) NOT NULL ,

`nro documento` VARCHAR(12) NOT NULL ,

`sexo` VARCHAR(1) NOT NULL ,

`promedio` DECIMAL(3,2) NULL ,

`domicilioEstudiante` VARCHAR(45) NOT NULL ,

`domicilioFamiliar` VARCHAR(45) NULL ,

`colegio_idcolegio` INT NOT NULL ,

PRIMARY KEY (`legajo`) ,

INDEX `fk_estudiante_colegio1` (`colegio_idcolegio` ASC) ,

CONSTRAINT `fk_estudiante_colegio1`

FOREIGN KEY (`colegio_idcolegio` )

REFERENCES `muestro_BD1`.`colegio` (`idcolegio` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB

COMMENT = 'Tabla para registrar los estudiantes de la Universidad XXI';

-- -----------------------------------------------------

-- Table `muestro_BD1`.`telefonoEstudiante`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `muestro_BD1`.`telefonoEstudiante` (

18
 `estudiante_legajo` INT NOT NULL ,

`telefono` INT NOT NULL ,

`tipo` VARCHAR(10) NOT NULL ,

INDEX `fk_telefonoEstudiante_estudiante1` (`estudiante_legajo` ASC) ,

PRIMARY KEY (`telefono`, `estudiante_legajo`) ,

CONSTRAINT `fk_telefonoEstudiante_estudiante1`

FOREIGN KEY (`estudiante_legajo` )

REFERENCES `muestro_BD1`.`estudiante` (`legajo` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `muestro_BD1`.`sede`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `muestro_BD1`.`sede` (

`idsede` INT NULL ,

`Nombre` VARCHAR(45) NOT NULL ,

`domicilio` VARCHAR(45) NOT NULL ,

`localidad` VARCHAR(45) NOT NULL ,

`propio` VARCHAR(45) NOT NULL ,

`fecha de inicio` VARCHAR(45) NOT NULL ,

`nombre responsable` VARCHAR(45) NULL ,

PRIMARY KEY (`idsede`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

19
-- Table `muestro_BD1`.`carrera`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `muestro_BD1`.`carrera` (

`idcarrera` INT NOT NULL ,

`nombre` VARCHAR(45) NOT NULL ,

PRIMARY KEY (`idcarrera`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `muestro_BD1`.`plan`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `muestro_BD1`.`plan` (

`idplan` INT NULL ,

`nombre` VARCHAR(45) NOT NULL ,

`fecha desde` TIMESTAMP NOT NULL ,

`fecha hasta` TIMESTAMP NULL ,

`carrera_idcarrera` INT NOT NULL ,

PRIMARY KEY (`idplan`) ,

INDEX `fk_plan_carrera1` (`carrera_idcarrera` ASC) ,

CONSTRAINT `fk_plan_carrera1`

FOREIGN KEY (`carrera_idcarrera` )

REFERENCES `muestro_BD1`.`carrera` (`idcarrera` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

20
-- Table `muestro_BD1`.`tituloCarrera`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `muestro_BD1`.`tituloCarrera` (

`nombre` VARCHAR(30) NOT NULL ,

`tipo de título` VARCHAR(10) NOT NULL ,

`carrera_idcarrera` INT NOT NULL ,

INDEX `fk_tituloCarrera_carrera1` (`carrera_idcarrera` ASC) ,

CONSTRAINT `fk_tituloCarrera_carrera1`

FOREIGN KEY (`carrera_idcarrera` )

REFERENCES `muestro_BD1`.`carrera` (`idcarrera` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `muestro_BD1`.`materia`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `muestro_BD1`.`materia` (

`idmateria` INT NOT NULL ,

`nombre` VARCHAR(45) NOT NULL ,

`Créditos` DECIMAL(3,1) NULL ,

PRIMARY KEY (`idmateria`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `muestro_BD1`.`estudianteXcarrera`

-- -----------------------------------------------------

21
CREATE TABLE IF NOT EXISTS `muestro_BD1`.`estudianteXcarrera` (

`estudiante_legajo` INT NOT NULL ,

`carrera_idcarrera` INT NOT NULL ,

`fecha ingreso` TIMESTAMP NOT NULL ,

`fecha baja` TIMESTAMP NULL ,

`motivo` VARCHAR(10) NULL ,

PRIMARY KEY (`estudiante_legajo`, `carrera_idcarrera`) ,

INDEX `fk_estudiante_has_carrera_estudiante` (`estudiante_legajo` ASC) ,

INDEX `fk_estudiante_has_carrera_carrera1` (`carrera_idcarrera` ASC) ,

CONSTRAINT `fk_estudiante_has_carrera_estudiante`

FOREIGN KEY (`estudiante_legajo` )

REFERENCES `muestro_BD1`.`estudiante` (`legajo` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_estudiante_has_carrera_carrera1`

FOREIGN KEY (`carrera_idcarrera` )

REFERENCES `muestro_BD1`.`carrera` (`idcarrera` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `muestro_BD1`.`carreraXsede`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `muestro_BD1`.`carreraXsede` (

`carrera_idcarrera` INT NOT NULL ,

`sede_idsede` INT NOT NULL ,

22
 `fecha_inicio` TIMESTAMP NOT NULL ,

PRIMARY KEY (`carrera_idcarrera`, `sede_idsede`) ,

INDEX `fk_carrera_has_sede_carrera1` (`carrera_idcarrera` ASC) ,

INDEX `fk_carrera_has_sede_sede1` (`sede_idsede` ASC) ,

CONSTRAINT `fk_carrera_has_sede_carrera1`

FOREIGN KEY (`carrera_idcarrera` )

REFERENCES `muestro_BD1`.`carrera` (`idcarrera` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_carrera_has_sede_sede1`

FOREIGN KEY (`sede_idsede` )

REFERENCES `muestro_BD1`.`sede` (`idsede` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `muestro_BD1`.`materiaXplan`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `muestro_BD1`.`materiaXplan` (

`materia_idmateria` INT NOT NULL ,

`plan_idplan` INT NOT NULL ,

`semestre` INT NULL ,

PRIMARY KEY (`materia_idmateria`, `plan_idplan`) ,

INDEX `fk_materia_has_plan_materia1` (`materia_idmateria` ASC) ,

INDEX `fk_materia_has_plan_plan1` (`plan_idplan` ASC) ,

CONSTRAINT `fk_materia_has_plan_materia1`

23
 FOREIGN KEY (`materia_idmateria` )

REFERENCES `muestro_BD1`.`materia` (`idmateria` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_materia_has_plan_plan1`

FOREIGN KEY (`plan_idplan` )

REFERENCES `muestro_BD1`.`plan` (`idplan` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

Puesta de los seteos en la forma anterior para que no quede alterada la conexión.

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Inserción de filas

Una vez creada las tablas, con la opción de Workbench, referidas a las Bases de
Datos, llamada Forward Engineering to Database, se ejecuta el script generado
automáticamente transcripto en las paginas anteriores y encontramos con SQL
BROWSER, la herramienta usada para ejecutar las sentencias SQL de capítulos
anteriores. Lo vemos en la figura 6.

24
Figura 6 – Las 11 tablas analizadas y definidas en los pasos anteriores ya están construidas en la
Base de datos, listas para recibir filas.

Las sentencias Insert que permiten grabar filas en las tablas, al haber restricciones
o constraints referenciales activas deben ejecutarse en orden, respetando que
primero se cargan las filas de las tablas referenciadas, estas son Colegio, Estudiante,
Carrera, Materia, Sede. Nuevamente usando Workbench, al hacer click con el botón
derecho se genera una sentencia Insert para realizar el trabajo. Ver cuadro 7 y 8

INSERT INTO `colegio` (`idcolegio`, `nombre`, `localidad`) VALUES ( , ,);

INSERT INTO `estudiante` (`legajo`, `Apellido`, `Nombre`, `fecha nacimiento`, `tipo

documento`,

`nro documento`, `sexo`, `promedio`, `domicilioEstudiante`, `domicilioFamiliar`,

`colegio_idcolegio`) VALUES ( , , , , , , , , , ,);

INSERT INTO `carrera` (`idcarrera`, `nombre`) VALUES ( ,);

INSERT INTO `materia` (`idmateria`, `nombre`, `creditos`) VALUES ( , ,);

25
INSERT INTO `sede` (`idsede`, `Nombre`, `domicilio`, `localidad`, `propio`, `fecha de
inicio`, `nombre responsable`) VALUES ( , , , , , ,);

Cuadro 7. Sentencias Insert generada automáticamente por Workbench

INSERT INTO `colegio` (`idcolegio`, `nombre`, `localidad`) VALUES ( 1,’Dean Funes’,

’Córdoba’);

INSERT INTO `colegio` (`idcolegio`, `nombre`, `localidad`) VALUES ( 2, ’IEC’,

’Córdoba’);

INSERT INTO `colegio` (`idcolegio`, `nombre`, `localidad`) VALUES ( 3,’Dante

Alighieri’,
’Córdoba’);

INSERT INTO `colegio` (`idcolegio`, `nombre`, `localidad`) VALUES ( 4,’IPEM 250

Bialet Masse’ ,’Córdoba’);

INSERT INTO `estudiante` (`legajo`, `Apellido`, `Nombre`, `fecha nacimiento`, `tipo

documento`,
`nro documento`, `sexo`, `promedio`, `domicilioEstudiante`, `domicilioFamiliar`,
`colegio_idcolegio`) VALUES ( 1,’Alvarez’ , ‘Julieta’, ‘90/03/01 00:00:00’, ‘DNI’,
303928919,’F’ , 9.60, ‘calle 1 nro 1’, null,4);

INSERT INTO `estudiante` (`legajo`, `Apellido`, `Nombre`, `fecha nacimiento`, `tipo

documento`,
`nro documento`, `sexo`, `promedio`, `domicilioEstudiante`, `domicilioFamiliar`,
`colegio_idcolegio`) VALUES ( 2,’ Gomez’ , ‘Ignacio’, ‘90/06/10 00:00:00’, ‘DNI’,
30999884, ’M’, 8.60, ‘calle 1 nro 1’, ‘calle 221 nro 67’, 1);

INSERT INTO `estudiante` (`legajo`, `Apellido`, `Nombre`, `fecha nacimiento`, `tipo

documento`,
`nro documento`, `sexo`, `promedio`, `domicilioEstudiante`, `domicilioFamiliar`,
`colegio_idcolegio`) VALUES ( 3,’ Casas’ , ‘Nicanor’, ‘89/03/21 00:00:00’, ‘DNI’,
27033322 , ’M’, 6.85, ‘calle 1 nro 1’, ‘calle 44 nro 1212’,2);

INSERT INTO `estudiante` (`legajo`, `Apellido`, `Nombre`, `fecha nacimiento`, `tipo

documento`,
`nro documento`, `sexo`, `promedio`, `domicilioEstudiante`, `domicilioFamiliar`,
`colegio_idcolegio`) VALUES ( 4,’ Litvin’ , ‘Ludmila’, ‘90/07/19 00:00:00’, ‘PAS’,
9922322, ’F’, 8.36, ‘calle 1 nro 1’,null ,3);

26
INSERT INTO `estudiante` (`legajo`, `Apellido`, `Nombre`, `fecha nacimiento`, `tipo
documento`,
`nro documento`, `sexo`, `promedio`, `domicilioEstudiante`, `domicilioFamiliar`,
`colegio_idcolegio`) VALUES ( 5,’Alemany’ , ‘Sofia’, ‘92/03/09 00:00:00’, ‘DNI’,
30035566 , ’F’ , 6.60 , ‘calle 1 nro 1’, null, 2);

INSERT INTO `estudiante` (`legajo`, `Apellido`, `Nombre`, `fecha nacimiento`, `tipo

documento`,
`nro documento`, `sexo`, `promedio`, `domicilioEstudiante`, `domicilioFamiliar`,
`colegio_idcolegio`)
VALUES ( 6,’Martin’ , ‘Alicia’, ‘88/09/19 00:00:00’, ‘DNI’, 30033322 , ’F’, 7.33, ‘calle
1 nro 1’,null, 1);

Cuadro 7 sentencias Insert para crear nuestros primeros colegios y estudiantes

Para las otras tablas cargamos las filas, en archivo aparte quedan las sentencias
correspondientes a los Inserts.

Consultas simples

Realizaremos algunas consultas simples para repasar la sintaxis, de lo visto en el

capitulo anterior.

1) Mostrar los datos de las Tablas Estudiantes y Carreras, ver figura 7 con el
resultado de la primera sentencia, haga Ud. mismo la otra consulta.

Figura 7. Consulta de todas las filas de la tabla estudiante, (para la imagen expuesta se editó el
ancho de todas las columnas)

2) Mostrar la cantidad de créditos de las materias y las horas reloj de cada una
de ellas considerando que los créditos son las horas semanales de dictado y
que existen 16 semanas efectivas de clases en un semestre. Se usa el alias de
columna “horas” Ver figura 8.

27
Figura 8 – Resultado de la consulta simple con cálculos en el select

2) aplicar condiciones para que solo se muestren las filas que las cumplan, por
ejemplo, sólo las filas que tienen más de 100 horas.

Figura 9. Condiciones Simples

3) Enuncie consultas del tipo presentado en las lecturas y ejercítelas.

Consultas complejas

En esta parte iniciaremos con consultas con funciones de grupo y de múltiples

tablas y luego las combinaremos.

1) Listar los nombres de los planes y materias asociadas de la carrera

‘CONTADOR PUBLICO’, pero solo de aquellos vigentes, esto le obligará a
preguntar por la columna
`fecha hasta`, note que ya hablamos de las columnas encomilladas y esta
es una de ellas (usar la tecla de acento y la barra espaciadora). Sobre esta

28
 columna se debe aplicar el operador de comparación con nulos, ‘not null’.
En la figura 10 la consulta sin la restricción de los planes actuales y en la
figura 11 la consulta completa y el resultado

Figura 10 – Consulta de los planes de la carrera de Contador Público

Figura 11 – Consulta de los planes de la carrera de Contador Público, pero sólo el

vigente

Hemos realizado una consulta que reunió a varias tablas ahora agregaremos
funciones de grupo para alcanzar mayor complejidad.

29
 2) Mostrar las carreras y planes vigentes junto con la cantidad de horas que
requieren para su cursado. Ver figura 12 para la solución.

Figura 12 – Consulta de los planes de todas las carreras, pero sólo el vigente, acumulando las
horas de todas sus materias. Notar la pregunta por valores nulos.

Hasta aquí llegamos con la lectura presente que buscó guiarlos en la aplicación de
todo lo aprendido en un caso real y que esperamos haya contribuido a completar
el aprendizaje logrado para alcanzar así los objetivos de la materia.

30