Este articulo trata sobre la creación de tablas con restricciones sobre los posibles valores de los atributos así como de la realización de consultas sencillas.
Este articulo trata sobre la creación de tablas con restricciones sobre los posibles valores de los atributos así como de la realización de consultas sencillas.
Una empresa desea almacenar información sobre sus departamentos y sus empleados para lo cual dispone de sendas tablas DEPT y EMP. Los atributos de la tabla DEPT son: deptno (número de departamento que actúa como clave primaria), dnombre (nombre de departamento) y loc (ciudad donde está ubicado). Los atributos de la tabla EMP son: empno (número de empleado que actúa como clave primaria), enombre (nombre), puesto (actividad o responsabilidad que desempeña dentro de la empresa: presidente, vendedor, etc), fecha (fecha de alta en la empresa), sal (salario o sueldo), comm (comisiones por venta) y deptno (departamento al que pertenece el empleado; clave ajena).
Han de tenerse en cuenta las siguientes restricciones:
- Los puestos laborales existentes en la empresa son ADMINISTRATIVO, VENDEDOR, GERENTE, ANALISTA y PRESIDENTE.
- Los vendedores son los únicos empleados que pueden tener comisiones.
- Las comisiones de un vendedor no pueden superar el 200 % de su salario.
Creación de la tabla de departamentos
CREATE TABLE dept(
deptno NUMBER(2) PRIMARY KEY,
dnombre VARCHAR(15),
loc CHAR(15));
|
Creación de la tabla de empleados
CREATE TABLE emp(
empno NUMBER(4) PRIMARY KEY,
enombre VARCHAR(20),
puesto VARCHAR(15),
fecha DATE,
sal NUMBER(7),
comm NUMBER(7),
deptno NUMBER(2),
FOREIGN KEY (deptno) REFERENCES dept,
CONSTRAINT ck_puesto CHECK (puesto IN
('ADMINISTRATIVO','VENDEDOR','GERENTE','ANALISTA','PRESIDENTE')),
CONSTRAINT ck_comm1 CHECK ((comm IS NULL AND puesto != 'VENDEDOR') OR
(comm IS NOT NULL AND puesto = 'VENDEDOR' AND comm>=0)),
CONSTRAINT ck_comm2 CHECK (comm<=2*sal));
|
También se puede crear la tabla sin las restricciones CONSTRAINT y especificar éstas posteriormente mediante ALTER TABLE. Por ejemplo:
|
ALTER TABLE emp ADD CONSTRAINT ck_comm2 CHECK (comm<=2*sal);
|
Creación de restricciones genéricas adicionales
Un departamento no puede tener más de un gerente.
CREATE ASSERTION as_gerente CHECK (NOT EXISTS (
SELECT COUNT(*) FROM emp WHERE puesto='GERENTE'
GROUP BY deptno HAVING COUNT(*) > 1);
|
Solo puede existir un presidente en la empresa.
CREATE ASSERTION as_presidente CHECK (
(SELECT COUNT(*) FROM emp WHERE puesto='PRESIDENTE') < 2);
|
Inserción de tuplas de ejemplo
Inserción de filas en la tabla de departamentos.
INSERT INTO dept VALUES(10,'CONTABILIDAD','SEVILLA');
INSERT INTO dept VALUES(20,'I+D','MADRID');
INSERT INTO dept VALUES(40,'OPERACIONES','BARCELONA');
INSERT INTO dept VALUES(30,'VENTAS','MALAGA');
|
Inserción de filas en la tabla de empleados.
INSERT INTO emp VALUES(7369,'PEREZ PEREZ','ADMINISTRATIVO',171280,150000,NULL,20);
INSERT INTO emp VALUES(7499,'LOPEZ CASTO','VENDEDOR',200281,150000,300000,30);
INSERT INTO emp VALUES(7521,'RUIZ REY','VENDEDOR',220281,125000,275000,30);
INSERT INTO emp VALUES(7566,'BARRIO PADILLA','GERENTE',020481,300000,NULL,20);
INSERT INTO emp VALUES(7654,'JIMENEZ MARTIN','VENDEDOR',280981,250000,150000,30);
INSERT INTO emp VALUES(7698,'SALVADOR BRAVO','GERENTE',010581,350000,NULL,30);
INSERT INTO emp VALUES(7782,'HALCON MALTES','GERENTE',090681,350000,NULL,10);
INSERT INTO emp VALUES(7788,'PERA LOPEZ','ANALISTA',091181,245000,NULL,20);
INSERT INTO emp VALUES(7839,'GONZALEZ MARQUEZ','PRESIDENTE',171181,500000,NULL,10);
INSERT INTO emp VALUES(7844,'ROBLES ROBLES','VENDEDOR',080981,150000,280000,30);
INSERT INTO emp VALUES(7876,'NADAL SILES','ADMINISTRATIVO',230981,160000,NULL,20);
INSERT INTO emp VALUES(7900,'NARANJO DIAZ','ADMINISTRATIVO',031281,140000,NULL,30);
INSERT INTO emp VALUES(7902,'ROMERO BORT','ANALISTA',031281,245000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLAN VAZQUEZ','ADMINISTRATIVO',230182,140000,NULL,10);
|
