CHAPTER 2 OPTICAL DESIGN
2.5 Tilted Element System Design
LENGUAJE DE DEFINICIÓN DE DATOS
1.- Introducción
Una vez que ya hemos diseñado nuestro diseño Lógico de la Base de Datos (Modelo Entidad / Relación y Modelo Relacional), pasamos a crear el diseño Físico. En primer lugar, tendremos que decidir qué Sistema Gestor de Bases de Datos utilizaremos. En nuestro caso, será el SQL Server de Microsoft.
Para poder explicar el diseño Físico, utilizaremos el ejemplo de una Clínica, dónde se quiere obtener información de los Pacientes que van a realizar visitas a la clínica, y también de las Visitas que se hacen a la misma.
El Modelo e-r de la Clínica sería el siguiente:
De dicho modelo podemos deducir que 1 paciente puede realizar MUCHAS visitas, pero que 1 visita sólo será realizada por 1 paciente, y de ahí la relación 1:N entre ambas entidades.
Partiendo de este modelo e-r obtendríamos el siguiente Modelo Relacional: PACIENTES (DNI, Nombre, Dirección, CP, Ciudad, FechaNac) VISITAS (Nvisita, Fecha, DniPaciente, Concepto, Importe)
Pero para pasar al diseño Físico, todavía nos hace falta un poco más de información, como por ejemplo, el tipo de datos que corresponde a cada atributo, las restricciones que podemos encontrarnos, los valores predeterminados, … Por este motivo necesitamos la siguiente información adicional:
PACIENTES
Tipo de datos ObservacionesDNI Alfanumérico (9) Se podrá guardar números (8) y texto (1) para la
letra. Además, el campo será clave principal de la tabla.
Nombre Alfanumérico (40) Dejaremos 40 caracteres para el nombre del
paciente. Dicho nombre no podrá quedarse vacío, y se deberá rellenar obligatoriamente.
Dirección Alfanumérico (50) Este campo sí que puede quedarse vacío
CP Alfanumérico (5) Se guardará como alfanumérico y no como texto,
porque algunos códigos postales empiezan por 0, y tampoco necesitamos realizar operaciones con dicho campo. Puede contener valores nulos
Ciudad Alfanumérico (15) Puede contener valores nulos, y por defecto, para los registros nuevos almacenará el valor de Alicante.
FechaNac Fecha Se puede dejar vacío.
VISITAS
Tipo de datos ObservacionesNvisita Numérico Guardará un número correlativo, y cada visita tendrá
un número distinto. Además, será clave principal de la tabla.
Fecha Fecha No podrá dejarse vacío.
DniPaciente Alfanumérico (9) Del mismo tipo que el campo con el que está
relacionado de la tabla Pacientes. No podrá dejarse vacío.
Concepto Alfanumérico (200) Contendrá el motivo por el cual se realiza la visita. Tampoco podrá dejarse vacío.
Importe Moneda Campo monetario, que recogerá el importe que el
paciente debe de abonar. Dicho importe no puede ser inferior a 50 €, y tampoco podrá dejarse vacío.
Para poder empezar a crear el diseño Físico de nuestra base de datos, necesitamos conocer con más detalle los tipos de datos que dispone SQL Server para la definición de datos.
Sólo veremos los que nos resulten más importantes para nuestro diseño y los que consideremos básicos para el diseño de bases de datos.
1.1.- Tipos de datos
Los tipos de datos más utilizados son: a) NUMÉRICOS
Dentro de los datos numéricos podemos distinguir varias categorías: ENTEROS
Almacenan números sin decimales. Incluyendo los positivos (1, 2, 3,…), el 0 y los negativos (-1, -2, -3,…). Básicamente utilizaremos 4 tipos de datos:
Tipo de datos Almacenamiento Mínimo y máximo
Bit 1/8 de byte 0 a 1
Tinyint 1 byte 0 a 255
Smallint 2 bytes -32.768 a 32767
Int 4 bytes -2.147.483.648 a 2.147.483.647
El tipo de datos BIT se utiliza para campos de tipo verdadero/falso, estado,…Hay que tener en cuenta que este tipo de datos puede tener el valor nulo, con lo que se puede encontrar en tres estados: 0, 1 y nulo.
Hay que tener en cuenta también que el tipo de datos TINYINT no permite números negativos.
DECIMALES y MONETARIOS
Los números decimales servirán para almacenar valores con parte decimal. La información contable y financiera se expresará con los tipos de datos monetarios. Los más importantes son:
Tipo de datos Espacio Precisión Escala Mínimo y máximo
Decimal 5 bytes 9 0-5 -999.999.999 a 999.999.999
Numeric 9 bytes 19 0-9 -1019+1 a 1019-1
Money 8 bytes Aprox. 18 4 -922.337.203.685.477,5808 a
922.337.203.685.477,5807
Smallmoney 4 bytes Aprox. 9 4 -2.147.483.648 a 2.147.483.647
La Precisión es el número máximo de dígitos que se almacenan en el tipo de datos. Por ejemplo, el número 21.543,98 tendrá siete dígitos. En la precisión se tendrá en cuenta todos los dígitos antes y después de la coma decimal. La Escala es el número
b) CADENAS o ALFANUMÉRICOS
En este tipo de datos se guardara informaciones tales como direcciones, nombres, conceptos,… Este tipo de datos almacenará letras, números y símbolos. Los tipos de datos cadena son:
Tipo de cadena Carácter Unicode
Fijo Char nChar
Variable Varchar nVarchar
Los tipos de datos Unicode están diseñados para el texto y símbolos de los diferentes idiomas (árabe, chino, japonés,…). Los tipos de datos Carácter se utilizan para cadenas empleadas en páginas de códigos específicas. Los tipos Carácter son más eficientes, ya que representan un byte por carácter, mientras que los Unicode utilizan dos por carácter.
Los tipos de datos Fijo, utilizan una cantidad de espacio fija, independientemente del valor del dato. Por ejemplo, si tengo una campo definido como Char(12), y la palabra que voy a guardar es “Antonio”, aunque tenga 7 letras en el campo se guardarán los 12 definidos en el tipo de datos.
Así, los tipos de datos de longitud Variable, ajustan su espacio de almacenamiento al valor que del campo, pero esto requiere dos bytes adicionales para controlar la longitud del valor. En el ejemplo anterior, para almacenar la palabra “Antonio”, emplearíamos 9 bytes, 2 para controlar la longitud y 7 para la palabra.
Los tipos de datos Char y VarChar soportan hasta 8.000 caracteres por campo, y los Unicode la mitad (hasta 4.000 caracteres).
Si queremos almacenar textos largos en estos tipos de datos cadena, utilizaremos VarChar (max), para cuando el tamaño supere los 8.000 bytes. Estos datos pueden almacenar hasta 2.147.483.647 caracteres (2 gigabytes).
c) FECHA y HORA
Muchas veces nos encontraremos con la necesidad de guardar atributos de fecha hora (fecha de la factura, fecha y hora de una visita,…). Para ello contamos con dos tipos de datos.
Tipo Tamaño Máximo y mínimo
DateTime 8 bytes 1-ene-1753 a 31-dic-999
2.- Creación y Administración de tablas (modo
gráfico)
Una vez ya definido el modelo e-r y el modelo relacional, junto con los tipos de datos de cada atributo, estaremos en disposición de implementar en un sistema gestor de base de datos las distintas tablas.
Para ver su funcionamiento, utilizaremos la herramienta SQL Management Studio, que pone a nuestra disposición SQL Server.
Veremos cómo crear gráficamente las tablas Pacientes y Visitas, de nuestra base de datos Clínica.
Para crear la Base de datos Pacientes, pulsaremos botón derecho sobre Base de Datos, y seleccionaremos Nueva Base de Datos…
Nos aparecerá el siguiente cuadro de texto, donde escribiremos el nombre de la base de datos: CLINICA.
2.1.- Creación de tablas y campos
Para crear las tablas, desplegamos la base de datos CLINICA, y pulsar el botón derecho, y seleccionar NUEVA TABLA…
Ahora pasaremos a crear los campos definidos en el modelo Relacional. Escribiremos el nombre del campo, y a continuación el tipo de datos que representa. En la tercera columna, indicaremos si el campo puede contener valores nulos o no se puede dejar vacío.
A parte de esto, tendremos que definir cuál va a ser la clave principal de esta tabla (DNI), para ello seleccionaremos el campo DNI, y a continuación pulsamos sobre la llave de la barra de herramientas o botón derecho sobre el campo, y elegir Establecer clave Principal.
Para el campo Ciudad, tendremos un valor por defecto para los registros nuevos, y así no tener que escribir su valor. Para colocar ese valor predeterminado, seleccionamos el campo y en las propiedades (parte inferior de donde se encuentra la definición de campos) elegimos Valor o enlace predeterminado, y colocamos el valor de “Alicante”.
Para la creación de la tabla VISITAS, seguimos los mismos pasos que con la tabla Pacientes.
En esta tabla, definiremos el campo Nvisita como smallint y con la propiedad Identity, para que vaya renumerándose correlativa y automáticamente en cada nuevo registro.
Una vez creada las tablas, podemos ver su estructura desde el Explorador de objetos situado en la parte izquierda de la pantalla. Al hacer clic sobre el + que hay a la izquierda de tabla, nos aparece una carpeta que pone Columnas, donde podremos ver el nombre de los campos y el tipo de datos del mismo.
2.2.- Modificación de la estructura de una tabla
Si queremos modificar cualquiera de las dos tablas creadas, sólo tenemos que pulsar botón derecho sobre la que queremos modificar y elegir la opción:
Y nos aparecerá la misma pantalla que nos apareció anteriormente de creación de tabla, y donde podemos añadir o modificar los campos que deseemos.
2.3.- Creación de índices y claves
Los índices serán como una estructura interna que nos va a servir para encontrar registros en nuestra tabla de una forma más rápida.
Si en nuestra tabla Pacientes se realizasen muchas búsquedas por el campo Ciudad, deberíamos crear un índice para esa columna. Para ello realizaríamos los siguientes pasos:
• Primero pulsar botón derecho sobre cualquier campo de la tabla.
• En el cuadro de diálogo que aparece, pulsamos el botón agregar.
• Donde elegiremos la-s columna-s que formarán parte del índice. Por defecto nos aparecerá un nombre asociado a ese índice llamado IX_PACIENTES (pero puede ser cambiado si lo deseamos)
De esta forma también podríamos definir la clave principal de una tabla sino lo hubiéramos hecho mediante el icono de la llave o se nos hubiera olvidado.
2.4.- Claves ajenas
Para que los registros de Pacientes y Visitas estén relacionados correctamente debemos crear las claves ajenas. Estás serán las encargadas de mantener las relaciones entre ambas tablas. En nuestro ejemplo, tenemos una clave ajena para el campo DniPaciente en la tabla Visitas, y que nos va a servir para que en tabla Visitas sólo se den de alta Dni-s de Pacientes que existan, y también a mantener la integridad de los datos introducidos en ese campo.
Para crear la clave ajena, iremos al diseño de la tabla, y pulsando botón derecho sobre cualquier campo, eligiendo a continuación la opción Relaciones.
Llegaremos a este cuadro de dialogo:
Pulsaremos en el desplegable Especificación de tablas y columnas para elegir los campos y las tablas que forman parte de la relación. Y a continuación, sobre los tres puntos que hay a la derecha de Especificación de tablas y columnas, apareciéndonos la siguiente pantalla.
Desde donde seleccionaremos las tablas y campos que se relacionan. En nuestro ejemplo son: DNI de la tabla Pacientes con DniPaciente de la tabla Visitas.
Una vez guardado, nos tiene que aparecen en el apartado de Claves de la tabla Visitas la nueva clave ajena (si no aparece, tendremos que pulsar el botón de Actualizar del explorador de objetos, el de las dos flechas verdes para que se actualice ).
2.5.- Restricciones Check
Las restricciones Check nos van a ayudar a que ciertos campos cumplan unas normas o condiciones para todos los valores que introduzcamos. Así, en nuestro ejemplo hemos puesto una restricciones de que todos los Importes que introduzcamos en una visita tendrán que ser superiores a 50 €, sino no debe dejar dar de alta ese registro.
Para poder dar de alta una restricción, vamos al diseño de la tabla y pulsamos botón derecho sobre la misma, y ahí elegimos la opción Restricciones Check.
En la pantalla rellenaremos la Expresión que contendrá la restricción (Importe >50), y si queremos cambiar el nombre a la restricción Check también podremos hacerlo. Para que nos aparezca en el explorador de objetos,
debemos guardar la tabla y pulsar actualizar para ver la restricción.
2.6.- Diagramas
Si queremos tener una representación gráfica de las tablas y sus relaciones, tendremos que crearnos un diagrama. Para ello, desde el explorador de objetos, en la Base de datos CLINICA, pulsaremos el botón derecho en Diagrama de base de datos, seleccionando la opción Nuevo diagrama de base de datos.
A continuación añadimos las tablas que deseamos que contenga el diagrama, obteniendo la siguiente pantalla que nos ayudará a ver la representación gráfica de nuestra estructura de tablas y campos.
Desde el panel del Diagrama también podremos realizar tareas de mantenimiento de las tablas o crearnos tablas nuevas, sin necesidad de irnos a la pantalla de diseño. Para ello pulsamos botón derecho sobre cualquier parte “blanca” del diagrama, y obtendremos el siguiente menú:
Las opciones aumentan si pulsamos el botón derecho sobre la tabla, donde incluso podremos tener varias opciones de visualización del diagrama.
3.- Administración de tablas (mediante SQL)
3.1.- Create table (creación de tablas)
La sintaxis para la creación de tablas será la siguiente:CREATE TABLE <nombre de la tabla> (campo1 <tipo de dato>, campo2 <tipo de dato>, …,
CONSTRAINT <nombre del índice> <tipo de índice>)
Veamos un ejemplo para ir entendiendo poco a poco la creación de tablas mediante SQL. Primero crearemos la tabla Pacientes, y luego la de Visitas que tiene la clave ajena.
Pero como utilizamos el SQL Management Studio para ejecutar scripts con comandos sql. Para ello pulsaremos en Nueva Consulta de la barra de herramientas o desde la opción del menú Archivo Nuevo Consulta con conexión actual. A continuación se abre la ventana del editor de sql
en blanco, pero antes debemos conectar con la Base de datos que contendrá las tablas. Esta operación se puede realizar de dos maneras: escribiendo una primera línea de código que ponga (USE Clinica) o con el desplegable de la segunda barra de herramientas, que normalmente pone Master, y tendremos que seleccionar Clínica.
Una vez seleccionada la Base de datos, ya podemos escribir el siguiente código sql.
CREATE TABLE Pacientes (
Dni CHAR(9) PRIMARY KEY, Nombre VARCHAR(40) NOT NULL, Direccion VARCHAR(50) NULL, CP CHAR(5) NULL,
Ciudad VARCHAR(15) NULL DEFAULT (‘Alicante’), FechaNac SMALLDATETIME NULL)
Ahora, antes de ejecutar el script lo guardaremos con el icono del diskette de la barra de herramientas, y le daremos un nombre. El documento creado es un fichero de texto plano, pero que le da la extensión .sql, con lo que podríamos haberlo creado con el bloc de notas, pero no tendríamos el interfaz de corrección de errores que incorpora el Management Studio.
Una vez guardado, comprobaremos si la sintaxis escrita es correcta. Para ello, pulsaremos el icono de analizar o Ctrl+F5, y si esta todo correcto, en la parte inferior de la pantalla nos aparecerá el siguiente texto: Comandos completados correctamente. Y si hubiese algún fallo no indicaría que hay algún error en alguna línea.
A continuación tenemos que ejecutar la sentencia SQL para que cree la tabla Pacientes, para ello pulsamos la tecla
F5 o el botón de Ejecutar de la barra de herramientas. Y de esta forma se creará una tabla, que podremos ver en el Explorador de objetos. Pero hay que tener en cuenta, que en nuestro ejemplo, ya creamos en modo gráfico, una tabla llamada Pacientes, por lo cual, al ejecutar el script no dará un error, indicando que ya existe esa tabla Pacientes. Para solucionarlo tendremos que cambiar el nombre de la nueva tabla, por ejemplo Pacientes1.
Hacemos lo mismo para la tabla Visitas.
CREATE TABLE Visitas (
Nvisita SMALLINT IDENTITY(1,1) PRIMARY KEY, Fecha SMALLDATETIME NOT NULL,
DniPaciente CHAR(9) NOT NULL, Concepto VARCHAR(200) NOT NULL,
Importe SMALLMONEY NOT NULL CHECK (Importe>50), CONSTRAINT Fk_Visitas FOREIGN KEY (DniPaciente) REFERENCES Pacientes (Dni))
Aquí pasará lo mismo, como ya existe Visitas, nos dará un error, pero que solucionaremos al cambiar el nombre de la tabla por Visitas1.
3.1.1.- Definición de columnas
Una vez visto los ejemplos, entenderemos mejor la sintaxis para la definición de las columnas de una tabla.
Nombre_columna <tipo de datos> [Null | Not Null]
[Default expresión]
[Identity (valor inicial, incremento)]
En el argumento del tipo de datos, podremos seleccionar cualquiera de los que definimos en el apartado de Tipos de Datos (numéricos, alfanuméricos, fecha,...).
Para cada tipo de datos, podemos colocar Null (o no poner nada en la definición) o Not Null. De esta última forma, el contenido del campo nunca podrá dejarse vacío. El valor DEFAULT recogerá el valor por defecto para el campo cuando no se halla especificado un valor durante la inserción de registros.
3.1.2.- Restricciones de integridad
Nos servirán para exigir integridad de los datos del campo. Los mecanismos proporcionados por SQL son los siguientes:
• Clave primaria (Primary key) • Clave ajena (Foreign key) • Unicidad (Unique)
• Limitación de valores (Check)
Clave Primaria - PRIMARY KEY
Es el campo-s que suele identificar de forma única cada fila de una tabla. Así para indicar una clave principal en SQL podemos actuar de dos formas:
a) A continuación del campo que será clave principal.
DNI Char(9) PRIMARY KEY
b) O al final de la definición de campos mediante la cláusula CONSTRAINT.
CREATE TABLE Pacientes ( …,
CONSTRAINT Pk_Pacientes PRIMARY KEY(Dni))
Esta segunda forma es la única aplicable cuando la clave principal es compuesta.
Por ejemplo, supongamos que tenemos una tabla de Alquiler para gestionar las películas que son alquiladas por los socios de un video club. La definición de la clave principal sería de esta segunda forma, ya que sólo puede existir un Primary Key definido por tabla.
…,
CONSTRAINT Pk_Alquiler PRIMARY KEY(Cod_Socio, Cod_Pelicula, Fecha_Salida)
Clave Ajena – FOREIGN KEY
La clave ajena nos servirá para relacionar dos tablas y mantener la integridad referencial de las mismas. La forma de crearlas será al final de la definición de datos con la siguiente sintaxis.
…, CONSTAINT <nombre del índice> FOREING KEY <campo de la tabla> REFERENCES <tabla ajena> (<campo relacionado>)
[ON DELETE {CASCADE | NO ACTION}] [ON UPDATE {CASCADE | NO ACTION}]
En el ejemplo de la Clínica, la definición de la clave ajena sería de la siguiente forma.
CONSTRAINT Fk_Visitas FOREIGN KEY (DniPaciente) REFERENCES Pacientes (Dni))
Si la tabla tuviese varias claves ajenas, tendríamos que definir tantos CONSTRAINT-s como claves ajenas tengamos.
La opción ON DELETE (el NO ACTION está activo por defecto) significa que si se elimina un registro de Pacientes, por ejemplo el DNI 3, y ese paciente tiene visitas, no podrá realizarse la eliminación. Pero si esta activa la opción CASCADE, al eliminar el Dni 3, se eliminarán también todas las visitas cuyo Dni es el 3.
Con la opción ON UPDATE, sucede algo parecido, si activamos CASCADE, cuando yo modifique el Dni 5 por el número 29, automáticamente todas las visitas donde aparecía el Dni 5 pasan a convertirse al Dni 29.
Un ejemplo podría ser:
CONSTRAINT Fk_Visitas FOREIGN KEY (DniPaciente) REFERENCES Pacientes (Dni) ON DELETE CASCADE)
Unicidad – UNIQUE
Lo utilizaremos para asegurarnos que no se escriban valores duplicados en los campos que no forman parte de la clave principal.
En las columnas con Unique pueden aceptar valores nulos. La sintaxis sería al final de la definición del campo:
Limitación de valores - CHECK
Se utilizan para exigir una limitación de los valores que puede aceptar un campo en concreto.
Se pueden definir al final del campo donde va la restricción:
Importe SMALLMONEY NOT NULL CHECK (Importe>50 AND Importe<4000),
O al final de la definición de todos los campos con una cláusula CONSTRAINT.
.., CONSTRAINT Ck_Visitas CHECK (Importe>50 AND CP=’03001’),
3.2.- Alter table (modificación de tablas)
Puede que después de crear una tabla, surjan modificaciones en el diseño de la misma, queriendo añadir campos, eliminar columnas,… en definitiva cambiar la estructura de la tabla original. Las operaciones que podemos realizar son:
• Añadir, modificar o eliminar campos de una tabla. Por ejemplo, podemos añadir un campo como el Teléfono, o modificar el tamaño de uno ya existente (la Ciudad de 15 a 20 caracteres).
• Añadir o eliminar claves ajenas o primarias, por que se modifique la estructura de la tabla original, o que se relacione con una tabla nueva. • Agregar o eliminar restricciones Check, Default o Unique.
La sintaxis SQL para realizar estas operaciones sería la siguiente: