OBJETIVO:

Conocer la aplicación de las restricciones (constraint) de las estructuras de

almacenamiento (tablas), las cuales permiten proporcionar la integridad de la información.

DESARROLLO

En los ejercicios que se mostrarán a continuación, se deberá tomar de base el modelo de datos de la

base que se ha estado utilizando.

Procederemos a crear las tablas, y se conocerán algunos de detalles a considerar durante la

construcción.

UNIVERSIDAD FRANCISCO GAVIDIA FACULTAD DE INGENIERIA Y ARQUITECTURA

PRACTICA CICLO I - 2015

Asignatura: Diseño de Base de Datos Grupo: V01

Profesor: Ing. Luis Enrique Reyes Fecha desarrollo:

sábado 25/04/2015 Aula: Virtual

PRACTICA: CREACION DE TABLAS Y CONSTRAINT con MySQL

Veremos la justificación del uso de las restricciones dentro de la creación de las estructuras:

Unicidad

Dominios

Nulidad

Dependencias

Indices

EJECUCIÓN DE LA PRÁCTICA.

Ejercicio 1.

Creación de tabla definiendo el nombre de la restricción:

CREATE TABLE regions (

region_id INT NOT NULL,

region_name VARCHAR(25)

);

En el caso del campo region_name, al no colocar la frase NOT NULL, significa que el campo

no es obligatorio que posea información (le pudimos haber puesto la frase NULL).

Ejercicio 2.

Creación de una llave primaria luego de haber creado una tabla:

ALTER TABLE regions

ADD ( CONSTRAINT reg_id_pk

PRIMARY KEY (region_id)

) ;

En este caso, después de haber creado la tabla regions, podemos indicarle al DBMS que

requerimos agregar una llave primaria a la tabla, también se le puede dar un nombre a la

restricción de llave primaria cuyo nombre sería reg_id_pk, para el campo region_id.

Ejercicio 3.

Otra forma de hacer directamente el ejercicio 1 y 2:

CREATE TABLE regions (

region_id INTEGER NOT NULL PRIMARY KEY,

region_name VARCHAR(25)

);

Si en este caso se le muestra el mensaje de que la tabla ya existe, entonces deberá utilizar la

siguiente instrucción antes de crearla:

DROP TABLE regions;

Luego intente crear la tabla de este ejercicio.

Ejercicio 4.

Creación de las tablas definiendo al final la llave primaria de la tabla:

CREATE TABLE countries (

country_id CHAR(2) ,

country_name VARCHAR(40) ,

region_id INT ,

CONSTRAINT country_c_id_pk PRIMARY KEY (country_id)

);

A diferencia del ejercicio anterior, otra forma de crear una llave primaria es colocar al final de

la instrucción el CONSTRAINT para crear la llave primaria.

Ejercicio 5.

Creación de una dependencia después de haber creado una tabla:

ALTER TABLE countries

ADD ( CONSTRAINT countr_reg_fk

FOREIGN KEY (region_id)

REFERENCES regions(region_id)

) ;

En este caso, definimos una restricción la cual indica que se creará una llave primaria, este es el

caso cuando ya existe una tabla y deseamos que dependan sus datos de otra tabla.

Vemos que cada restricción (CONSTRAINT) le asignamos un nombre. Esto es importante

cuando queremos borrarlo en algún momento.

En este caso la tabla countries, dependerá de la tabla regions. El enlace será el campo region_id

en ambas tablas.

Ejercicio 6.

El ejercicio 4 y 5 definido en una sola instrucción.

CREATE TABLE countries (

country_id CHAR(2) NOT NULL ,

country_name VARCHAR(40) ,

region_id INT ,

CONSTRAINT country_c_id_pk PRIMARY KEY (country_id) ,

CONSTRAINT countr_reg_fk FOREIGN KEY (region_id)

REFERENCES regions(region_id)

);

Ejercicio 7.

Creación de la tabla Location, siguiendo el modelo de datos y otra forma de crear la llave primaria

y la llave foránea.

CREATE TABLE locations (

location_id INT(4) NOT NULL AUTO_INCREMENT,

street_address VARCHAR(40),

postal_code VARCHAR(12),

city VARCHAR(30) NOT NULL,

state_province VARCHAR(25),

country_id CHAR(2) ,

CONSTRAINT loc_id_pk PRIMARY KEY (location_id),

CONSTRAINT loc_c_id_fk FOREIGN KEY (country_id)

REFERENCES countries(country_id)

) ;

Ejercicio 8.

Creación de la tabla departments

CREATE TABLE departments (

department_id INTEGER(4) NOT NULL PRIMARY KEY

AUTO_INCREMENT,

department_name VARCHAR(30) NOT NULL,

manager_id NUMERIC(6),

location_id INTEGER(4),

CONSTRAINT dept_loc_fk FOREIGN KEY (location_id)

REFERENCES locations (location_id)

) ;

Ejercicio 9.

Creación de la tabla job, para crear otras dependencias según el modelo

CREATE TABLE jobs (

job_id VARCHAR(10),

job_title VARCHAR(35) NOT NULL,

min_salary NUMERIC(6),

max_salary NUMERIC(6),

CONSTRAINT job_id_pk PRIMARY KEY(job_id)

) ;

Ejercicio 10.

Al crear la tablas y al incluir condiciones de validación entre campos.

CREATE TABLE job_history (

employee_id INTEGER(6) NOT NULL,

start_date DATE NOT NULL,

end_date DATE NOT NULL,

job_id VARCHAR(10) NOT NULL,

department_id INTEGER(4),

CONSTRAINT jhist_date_interval CHECK (end_date > start_date)

) ;

Vemos que la restricción indica que al ingresar las fechas de inicio y fin, la condición será que

la fecha fin siempre deberá ser mayor, nunca menor o igual.

Ejercicio 11.

Creación de las tablas que incluyen validación de datos, llave única y que corresponde a la última

tabla a crear debido a las dependencias:

CREATE TABLE employees (

employee_id INTEGER(6),

first_name VARCHAR(20),

last_name VARCHAR(25) NOT NULL,

email VARCHAR(25) NOT NULL,

phone_number VARCHAR(20),

hire_date DATE NOT NULL,

job_id VARCHAR(10) NOT NULL,

salary NUMERIC(8,2),

commission_pct NUMERIC(4,2),

manager_id INTEGER(6),

department_id INTEGER(4),

CONSTRAINT emp_salary_min CHECK (salary > 0),

CONSTRAINT emp_email_uk UNIQUE (email)

);

En este caso al campo emp_salary_min, se le está indicando que el valor del salario deberá

tener como valor mínimo 100, nunca serán valores negativos.

En el caso del campo emp_email_uk, lo que se le indica es que el contenido del campo nunca

deberá repetirse. Normalmente estas son llamadas llaves alternas.

Ejercicio 12.

Podremos definir varias dependencias hacia otras tablas.

ALTER TABLE employees

ADD (

CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id)

) ;

ALTER TABLE employees

ADD (

CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)

REFERENCES departments(department_id),

CONSTRAINT emp_job_fk FOREIGN KEY (job_id)

REFERENCES jobs (job_id),

CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id)

REFERENCES employees (employee_id)

) ;

En este caso, se hacen dos ALTER TABLE debido a que hay que crear primero la llave primaria

de la tabla empleado pues en seguida será utilizada para crear una llave foránea.

Ejercicio 13.

Creación de las tablas con llaves primarias compuestas:

ALTER TABLE job_history

ADD (

CONSTRAINT jhist_emp_id_st_date_pk PRIMARY KEY (employee_id, start_date),

CONSTRAINT jhist_job_fk FOREIGN KEY (job_id)

REFERENCES jobs (job_id),

CONSTRAINT jhist_emp_fk FOREIGN KEY (employee_id)

REFERENCES employees (employee_id),

CONSTRAINT jhist_dept_fk FOREIGN KEY (department_id)

REFERENCES departments (department_id)

) ;

En este ejercicio se muestra que la llave primaria es una llave compuesta. Para ello,

simplemente colocamos los campos separados por comas para indicar cuáles son los esos

campos. Debe tenerse cuidado en el orden que se colocan pues normalmente cuando se crea

una llave primaria, automáticamente se crea un índice y no será lo mismo que se cambie el

orden de los campos por efecto de rendimiento y búsqueda.

Ejercicio 14.

Creación de índices simples

CREATE UNIQUE INDEX reg_id_pk

ON regions (region_id);

En este caso, el índice creado es en la tabla regions, en el que se le indica el campo region_id.

La palabra UNIQUE, está indicando que no habran datos duplicados.

Una aclaración importante es que los índices se crean automáticamente en orden ascendente.

Ejercicio 15.

Creación de índices simples controlando el orden en que estarán los datos

CREATE UNIQUE INDEX jhist_st_date_pk

ON job_history (start_date) ;

En este caso, el índice creado es en la tabla job_history, en el que se le indica el campo

start_date.

Una aclaración en este caso es que el índice se creará de manera descendente, es decir, a la hora

de hacer búsqueda de datos por medio de este campo el registro más inmediato es el que posea

una fecha más reciente.

Ejercicio 16.

Creación de índices compuestos en el que incluye dos campos. Lo mismo puede suceder si se

tienen índices de más de dos campos.

CREATE INDEX emp_name_ix

ON employees (last_name, first_name);

En este caso la table employees tendrá un índice compuesto por dos campos.

El uso de este tipo de índices debe ser bien analizado, en el sentido de cuál debería ser la

búsqueda de datos más frecuentes. Esto significa que si la búsqueda más frecuente es primero

por nombre y luego por apellido deberá cambiarse la forma de creación del índice de la

siguiente manera:

CREATE INDEX emp_name_ix

ON employees (first_name, last_name);