Base de datos de ejemplo para L4 GD PDF

Preview

Summary

Download Base de datos de ejemplo para L4 GD PDF

Description

Página 1

BASE DE DATOS DE EJEMPLO Se ha elegido un ejemplo de base de datos, no muy complejo, pero sí lo suficiente para que se puedan estudiaren la práctica todos los conceptos más importantes. Este ejemplo es bastante real, aunque para su uso práctico en el mundo real podrían eliminarse algunas tablas, como la de pueblos y provincias. No obstante, se han dejado ambas dado que permiten practicar los distintos conceptos muy fácilmente con una escasa dificultad teórica. Control de Stock y facturación de una empresa1: Se presentan y describen las tablas que la componen. Para cada tabla se presenta su nombre y las columnas de que consta entre paréntesis. Las claves primarias aparecerán subrayadas. Las claves ajenas están en cursiva. - Tabla provincias(codpro, nombre): Eta tabla almacena las provincias de España, cada una con su código de provincia (clave primaria) y su nombre. - Tabla pueblos(codpue, nombre, codpro): Almacena los pueblos de España o, por lo menos, aquéllos donde tenemos clientes. Para cada pueblo es dispone de su código de pueblo (clave primaria), su nombre y el código de la provincia a la que pertenece (clave ajena). - Tabla clientes(codcli, nombre, direccion, codpostal, codpue): Almacena información sobre los clientes de la empresa. Para cada cliente se dispone de su código de cliente (clave primaria), su nombre, su dirección, su código postal y el código de pueblo donde reside (clave ajena). - Tabla vendedores(codven, nombre, direccion, codpostal, codpue, codjefe): Almacena información sobre los vendedores de la empresa. Para cada vendedor se dispone de su código de vendedor (clave primaria), su nombre, su dirección, su código postal, el código de pueblo donde reside (clave ajena a la tabla pueblos) y el código de un jefe inmediato superior (clave ajena a la misma tabla de vendedores). - Tabla articulos(codart, descrip, precio, stock, stockmin): Almacena información sobre los artículos que ofrece la empresa y sus cantidades disponibles en el almacén (stocks). Para cada artículo se dispone de su código de artículo específico (clave primaria), su descripción, su precio actual, su stock y su stock mínimo, es decir, el valor umbral por debajo del cual se debe reponer. - Tabla facturas(codfac, fecha, codcli, codven, iva, dto): Almacena toda la información sobre las facturas, excepto sus líneas. Como en cada factura el número de líneas es variable, todas las líneas de todas las facturas se almacenan juntas en otra tabla. Para cada factura en esta tabla se guarda su código de factura (clave primaria), su fecha, el código del cliente que ha realizado la compra (clave ajena), el código del vendedor que ha realizado la venta (clave ajena), el iva aplicado y el descuento global de la factura. - Tabla lineas_fac(codfac, linea, cant, codart, precio, dto): Almacena información sobre las líneas de las facturas. Para cada línea se dispone del código de factura a la que pertenece (clave ajena), su número de línea, la cantidad de la línea, el código del artículo vendido (clave ajena), el precio al que se vende el artículo y el descuento que se debe aplicar en la línea. No hay que confundir este descuento, cuyo ámbito de aplicación es la línea, con el descuento global de la factura, el cual se halla obviamente en la tabla de facturas. La clave primaria de esta tabla va a ser la combinación del código de factura y del número de línea pues, por ejemplo, sólo existirá una única tercera línea de la factura 15.

Los nombres de las tablas y atributos se han escrito sin acentuar para evitar problemas con algunos sistemas de gestión de bases de datos.

1 Fuente: Quintana Ortí, G., Marqués Andrés, M., Aliaga Estellés, J. I., & Aramburu Cabo, M. J. (2006). SQL en la práctica. http://repositori.uji.es/xmlui/

Página 2

Esquema de la base de datos que se utilizará en los ejemplos.

A continuación, se muestra diversa información sobre las columnas de las tablas: si aceptan nulos y su tipo de datos (o dominio). Si en la segunda columna aparece el texto NOT NULL, entonces la columna no acepta nulos. La tercera columna muestra el tipo de datos: VARCHAR2(x) significa una tira de hasta x caracteres de longitud. Tabla PROVINCIAS Columna ¿Nulo? Tipo de Datos codpro

NOT NULL

VARCHAR2(2)

nombre

NOT NULL

VARCHAR2(30)

Como se puede ver, el código de la provincia es una tira de dos caracteres. Tanto el código como el nombre no aceptan nulos. Tabla PUEBLOS Columna

¿Nulo?

Tipo de Datos

codpue

NOT NULL

VARCHAR2(5)

nombre

NOT NULL

VARCHAR2(40)

codpro

NOT NULL

VARCHAR2(2)

Como se puede ver, el código de pueblo es una tira de 5 caracteres. Las tres columnas no aceptan nulos. Tabla CLIENTES Columna

¿Nulo?

codcli

NOT NULL

NUMBER(5)

nombre

NOT NULL

VARCHAR2(50)

dirección

NOT NULL

VARCHAR2(50)

codpostal codpue

Tipo de Datos

VARCHAR2(5) NOT NULL

VARCHAR2(5)

En la tabla de clientes el código de cliente es un número de hasta 5 dígitos. La única columna que acepta nulos es el código postal (éste puede ser desconocido). Tabla VENDEDORES Columna

¿Nulo?

Tipo de Datos

codven

NOT NULL

NUMBER(5)

nombre

NOT NULL

VARCHAR2(50)

dirección

NOT NULL

VARCHAR2(50)

codpostal

VARCHAR2(6)

codpue

NOT NULL

VARCHAR2(5)

codjefe

NOT NULL

NUMBER(5)

Página 3 En la tabla de vendedores el código de cliente es también un número de hasta 5 dígitos. La única columna que acepta nulos es el código postal (éste puede ser desconocido). Tabla ARTICULOS Columna

¿Nulo?

Tipo de Datos

codart

NOT NULL

VARCHAR2(8)

descrip

NOT NULL

VARCHAR2(40)

precio

NOT NULL

NUMBER(7,2)

stock

NUMBER(6)

stock min

NUMBER(6)

En la tabla de artículos el código de artículo es una tira de hasta 8 caracteres. El precio es un número de hasta 7 dígitos, dos de los cuales son la parte fraccionaria. Las columnas stock y stock_min son las únicas que aceptan nulos. Tabla FACTURAS Columna

¿Nulo?

Tipo de Datos

codfac fecha codcli codven iva dto

NOT NULL NOT NULL

NUMBER(6) DATE NUMBER(5) NUMBER(5) NUMBER(2) NUMBER(2)

En la tabla de facturas el código de factura es un numero de hasta 6 dígitos. La fecha es de tipo DATE. El resto de columnas aceptan valores nulos. El código de cliente o de vendedor puede ser nulo (valor desconocido). Si el descuento es nulo, se entiende que es cero. Tabla LINEAS FAC Columna

¿Nulo?

Tipo de Datos

codfac linea cant codart precio dto

NOT NULL NOT NULL

NUMBER(6) NUMBER(2) NUMBER(5) VARCHAR2(8) NUMBER(7,2) NUMBER(2)

NOT NULL

En la tabla de líneas de facturas la cantidad, el precio y el descuento pueden ser nulos. Si el descuento es nulo, se entiende que es cero.

Página 4

 CREACIÓN Y ACTUALIZACIÓN DE LOS DATOS Operaciones SQL que permiten la creación y el borrado de tablas, así como la actualización de los datos que las tablas almacenan con las operaciones de inserción, modificación y borrado de filas.

 Creación de tablas Para crear una tabla en una base de datos se utiliza la sentencia CREATE TABLE. Su sintaxis es la siguiente:

- nombre_tabla: nombre de la nueva tabla. - nombre_columna: nombre de una columna de la tabla. - tipo_datos: tipo de datos de la columna. - DEFAULT expr: asigna un valor por defecto a la columna junto a la que aparece; este valor se utilizará cuando en una inserción no se especifique valor para la columna.

A continuación, se muestran un par de ejemplos de creación de tablas en las que únicamente se incluyen la definición de los campos.  Ejemplo: Creación de la tabla de provincias, sin restricciones. CREATE TABLE provincias ( codpro CHARACTER(2) NOT NULL, nombre CHARACTER(20) NOT NULL DEFAULT ' ' );

 Ejemplo: Creación de la tabla de artículos, sin restricciones. CREATE TABLE articulos ( codart CHARACTER(8) NOT NULL, descrip CHARACTER(40) NOT NULL, precio DECIMAL(7,2) NOT NULL DEFAULT 0.0, stock INTEGER(6), stock_min INTEGER(6) );

Las restricciones son los elementos del modelo relacional que permiten que éste funcione, ya que sin ellas una base de datos no es más que un mero almacén de información pero sin ningún tipo de conexión entre los datos. La definición de éstas presenta una misma estructura: CONSTRAINT nombre tipo parametros

Es importante destacar que el nombre definido para una restricción debe ser único para toda la base de datos, por lo que se aconseja tomar un criterio general que incluya el tipo de restricción, la tabla o tablas que involucra, y los campos correspondientes.

Página 5 En los ejemplos posteriores se utilizará un criterio posible. • Definición de la clave primaria de una tabla. PRIMARY KEY (campo1[, campo2, ... ] )

• Definición de una clave alternativa de una tabla. UNIQUE (campo1[, campo2, ... ] )

• Definición de la clave ajena de una tabla sobre otra. FOREIGN KEY (campo1[, campo2, ... ] ) REFERENCES tabla_referida [(campo1[, campo2, ... ] )] ON DELETE [ NO ACTION|SET NULL|SET DEFAULT|CASCADE|RESTRICT ] ON UPDATE [ NO ACTION|SET NULL|SET DEFAULT|CASCADE|RESTRICT ]

• Definición de una restricción sobre las filas de la tabla. CHECK (condicion)

De las restricciones mostradas, las dos primeras únicamente indican los campos que conforman la clave correspondiente, la última especifica condiciones que deben cumplir los valores que desean ser insertados o modificados en las filas de la tabla, y la tercera especifica qué atributos definen una clave ajena a otra tabla y las características necesarias para u correcto funcionamiento. De todas ellas, esta última es la que tiene aspectos adicionales a desarrollar. La tabla referida debe existir para poder ser incluida en la definición de la clave ajena, por lo que la definición de las tablas debe seguir un orden. Así, la tabla de lineas_fac no puede ser creada antes que la tabla de artículos. - Una clave ajena debe tener el mismo número de atributos que la clave primaria de la tabla a la que referencia, y además deben corresponder en tipo y dimensión. - Si la clave ajena es compuesta se recomienda especificar las columnas de la tabla actual y de la tabla referida, para asegurar que la correspondencia entre campos sea la adecuada. La restricción de columna REFERENCES permite indicar que la columna hace referencia a una columna de otra tabla. Si la referencia apunta a la clave primaria, no es necesario especificar el nombre de la columna a la que se hace referencia (estamos definiendo una clave ajena). Cuando se añade o actualiza un valor en esta columna, se comprueba que dicho valor existe en la tabla referenciada. -

RESTRICCIÓN_COLUMNA:

Cuando la restricción es a nivel de tabla (FOREIGN KEY) hay dos tipos de comprobación: MATCH FULL y MATCH PARTIAL. - Con MATCH FULL, si la clave ajena está formada por varias columnas y admite nulos, esta comprobación es la que corresponde a la regla de integridad referencial: en cada fila, o todas las columnas de la clave ajena tienen valor o ninguna de ellas lo tiene (todas son nulas), pero no se permite que en una misma fila, algunas sean nulas y otras no. - Con MATCH PARTIAL, si la clave ajena está formada por varias columnas y admite nulos, se permiten claves ajenas parcialmente nulas y se comprueba que en la tabla referenciada se podría apuntar a alguna de sus filas si los nulos se sustituyeran por los valores adecuados. Además, se pueden establecer reglas de comportamiento para cada clave ajena cuando se borra o se actualiza el valor referenciado.

Página 6 En ambos casos hay cuatro posibles opciones que se enumeran a continuación. NO ACTION produce un error por intento de violación de una restricción. RESTRICT es igual que NO ACTION. CASCADE borra/actualiza las filas que hacen referencia al valor borrado/actualizado. SET NULL pone un nulo en las filas donde se hacía referencia al valor borrado/actualizado. SET DEFAULT pone el valor por defecto en las filas donde se hacía referencia al valor borrado/actualizado.

RESTRICCIÓN_TABLA:

- CONSTRAINT nombre_restricción: a las restricciones que se definen sobre columnas y sobre tablas se les puede dar un nombre (si no se hace, el sistema generará un nombre automáticamente). - NOT NULL: la columna no admite nulos. - NULL: la columna admite nulos (se toma por defecto si no se especifica NOT NULL). - UNIQUE especificada como restricción de columna indica que la columna sólo puede contener valores únicos. UNIQUE (nombre_columna [,...]) especificada como restricción de tabla indica que el grupo de columnas sólo pueden contener grupos de valores únicos. Mediante esta cláusula se especifican las claves alternativas. - PRIMARY KEY especificada como restricción de columna o bien PRIMARY KEY (nombre_columna[,...]) especificada como restricción de tabla indica la columna o el grupo de columnas que forman la clave primaria de la tabla. Los valores de la clave primaria, además de ser únicos, deberán ser no nulos. - CHECK (expr) : permite incluir reglas de integridad específicas que se comprueban para cada fila que se inserta o que se actualiza. La expresión es un predicado que produce un resultado booleano. Si se especifica a nivel de columna, en la expresión sólo puede hacerse referencia a esta columna. Si se especifica a nivel de tabla, en la expresión puede hacerse referencia a varias columnas. Por ahora no se puede incluir Subconsultas en esta cláusula.

La cláusula ON DELETE indica qué ocurrirá cuando se intente borrar en la tabla referida una fila cuya clave primaria aparece como valor de clave ajena en alguna fila de la tabla actual. Definiéndose cinco opciones, 1. NO ACTION, es decir, no hacer nada en la tabla actual, pero borrar la fila en la tabla referida, lo que puede provocar problemas de falta de integridad de los datos. 2. SET NULL, se asigna el valor NULL en los campos que forman la clave ajena de aquellas filas que tengan como clave ajena el valor de la clave primaria que se desea borrar en la tabla referida, y posteriormente se borra esta fila. 3. SET DEFAULT, se asigna el valor por defecto en los campos que forman la clave ajena de aquellas filas que tengan como clave ajena el valor de la clave primaria que se desea borrar en la tabla referida, y posteriormente se borra esta fila. 4. CASCADE, se borran las filas que tienen como clave ajena el valor de la clave primaria que se desea borrar en la tabla referida antes de borrar éstas. 5. RESTRICT, si existe alguna fila que tiene como clave ajena el valor de la clave primaria que se desea borrar en la tabla referida, la fila asociada no se borra.

La cláusula ON UPDATE quiere indicar que ocurrirá cuando e intente modificar en la tabla referida la fila a la que apunta una fila de la tabla actual. Se definen las mismas cinco opciones que antes. 1. NO ACTION, es decir, no hacer nada, pero modificar la fila en la tabla referida, lo que puede provocar problemas de falta de integridad de los datos.

Página 7 2. SET NULL, se asigna el valor NULL en los campos que forman la clave ajena de aquellas filas que tengan como clave ajena el valor de la clave primaria que se desea modificar en la tabla referida, y posteriormente se modifica esta fila. 3. SET DEFAULT se asigna el valor por defecto en los campos que forman la clave ajena de aquellas filas que tengan como clave ajena el valor de la clave primaria que se desea modificar en la tabla referida, y posteriormente se modifica esta fila. 4. CASCADE, se modifican las filas que tienen como clave ajena el valor de la clave primaria que se desea modificar en la tabla referida antes de modificar éstas. 5. RESTRICT, si existe alguna fila que tenga como clave ajena el valor de la clave primaria que se desea modificar en la tabla referida, la fila asociada no se modifica. Ahora se presentan ejemplos en los que ya se detallan las restricciones de cada tabla.  Ejemplo: Creación de la tabla de provincias con definición de clave primaria. CREATE TABLE provincias ( codpro CHARACTER(2) NOT NULL, nombre CHARACTER(20) NOT NULL DEFAULT ' ', CONSTRAINT pk_provincias PRIMARY KEY (codpro) );

 Ejemplo: Creación de la tabla de artículos con definición de clave primaria y restricciones sobre los campos precio, stock y stock_min para que sólo admitan números no nulos y positivos. CREATE TABLE articulos ( codart CHARACTER(8) NOT NULL, descrip CHARACTER(40) NOT NULL, precio DECIMAL(7,2) NOT NULL DEFAULT 0.0, stock INTEGER(6), stock_min INTEGER(6), CONSTRAINT pk_articulos PRIMARY KEY (codart), CONSTRAINT ch_precio_articulos CHECK (precio > 0.0), CONSTRAINT ch_strockm_articulos CHECK (COALESCE(stock_min,0) > 0), CONSTRAINT ch_stock_articulos CHECK (COALESCE(stock,0) > 0) );

 Ejemplo: Creación de la tabla de facturas con definición de clave primaria, claves ajenas y restricciones sobre los campos iva y dto, para que sólo admitan unos valores concretos. CREATE TABLE facturas ( codfac INTEGER(6) NOT NULL, fecha date NOT NULL, codcli INTEGER(5), codven INTEGER(5), iva INTEGER(2), dto INTEGER(2), CONSTRAINT pk_facturas PRIMARY KEY (codfac), CONSTRAINT ch_iva_facturas CHECK (COALESCE(iva,0) IN (0,7,16)), CONSTRAINT ch_dto_facturas CHECK (COALESCE(dto,0) IN (0,10,20,40,50)), CONSTRAINT fk_fact_cli FOREIGN KEY (codcli), REFERENCES clientes ON DELETE RESTRICT ON UPDATE CASCADE CONSTRAINT fk_fact_ven FOREIGN KEY (codven), REFERENCES vendedores ON DELETE RESTRICT ON UPDATE CASCADE );

Página 8  Ejemplo: Creación de la tabla de LINEAS_FAC: CREATE TABLE lineas_fac ( codfac numeric(6,0) NOT NULL, linea numeric(2,0) NOT NULL, cant numeric(5,0) NOT NULL, codart varchar(8) NOT NULL, precio numeric(6,2) NOT NULL, dto numeric(2,0), CONSTRAINT cp_lineas_fac PRIMARY KEY (codfac, linea), CONSTRAINT ca_lin_fac FOREIGN KEY (codfac) REFERENCES facturas(codfac) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT ca_lin_art FOREIGN KEY (codart) REFERENCES articulos(codart) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT ri_dto_lin CHECK (dto BETWEEN 0 AND 50) );

 Inserción de datos Una vez creada una tabla podemos introducir datos en ella mediante la sentencia INSERT. La operación insert permite la introducción de nuevas filas en una tabla de la base de datos. INSERT INTO facturas(codfac,fecha, codcli,codven,iva,dto ) VALUES(6600, ’30/04/2007’,111, 55, 0, NULL); INSERT INTO lineas_fac(codfac,linea,cant,codart, precio,dto) VALUES(6600, 1, 4, ’L76425’,3.16, 25 ); INSERT INTO lineas_fac(codfac,linea,cant,codart, precio,dto) VALUES(6600, 2, 5, ’B14017’,2.44, 25 ); INSERT INTO lineas_fac(codfac,linea,cant,codart, precio,dto) VALUES(6600, 3, 7, ’L92117’,4.39, 25 );

Mediante estas sentencias se ha introducido la cabecera de una factura y tres de sus líneas. Nótese que tanto las cadenas de caracteres como las fechas, se introducen entre comillas simples. Para introducir nulos se utiliza la expresión NULL. Se muestra un ejemplo sencillo de utilización de esta operación:  Ejemplo: Introducir un nuevo artículo cuyo código es 'ARTXXX', su descripción es "Artículo de prueba 1", con un precio actual de 10,20 euros, un stock de 90 y un stock mínimo de 10. Solución1: INSERT INTO articulos VALUES ('ARTXXX', 'Artículo de prueba 1' 10.20, 90, 10);

Solución2: INSERT INTO artículos (codart, descrip, precio, stock, stock_min) VALUES ('ARTXXX', 'Artículo de prueba 1', 10.20, 90, 10);

Como se puede observar...