1.- Crea una tabla llamada PELÍCULAS con los siguientes campos y restricciones
CREATE TABLE peliculas( cod_pelicula INT NOT NULL AUTO_INCREMENT, nombre VARCHAR(25) NOT NULL, fecha_alta DATE DEFAULT '2006/01/01', stock INT DEFAULT '10', precio INT NOT NULL, CONSTRAINT claveprimaria PRIMARY KEY(cod_pelicula) ) ENGINE=innoDB;
2.-Indica si las siguientes órdenes INSERT son correctas. En el caso de que no sean correctas indica el porqué:
1. INSERT INTO PELICULAS(cod_pelicula, nombre, fecha_alta, stock, precio)
values(1,’06/01/01’,20,60); No es correcta porque no existe ningun valor para el atributo 'nombre' 2. INSERT INTO PELICULAS VALUES(‘2’,’El señor de los anillos’,20);
No es correcta porque falta detallar los atributos a insertar. 3. INSERT INTO PELICULAS(cod_pelicula,nombre,precio) values(3,’Las dos torres’,24);
Correcta 4. INSERT INTO PELICULAS(cod_pelicula, nombre,fecha_alta) values(4,’El retorno del rey’,06/01/01);
No es correcta porque el valor del atributo 'fecha_alta' no está entre comillas y porque el atributo "precio" es obligatorio.
3.-Crea la tabla CLIENTES con los siguientes campos y restricciones: La clave primaria de esta tabla es el campo DNI_CLIENTE create table CLIENTES( dni_cliente varchar(100) not null, nombre varchar(100) not null, direccion varchar(100) null, poblacion varchar(100) null default 'Madrid', codigo_postal int(100) null, fecha_nac date null, primary key (dni_cliente) ) ENGINE=InnoDB;
4.- Crea la tabla COMPRAS con los siguientes campos y restricciones.
Establece la clave primaria de la tabla y las claves ajenas en caso de que las tenga. Debe permitirse la eliminación de registros en esta tabla en caso de que se eliminen registros tanto en CLIENTES como en PELICULAS. Debe permitirse la actualización de registros en la tabla en caso de que se actualicen registros tanto en CLIENTES como en PELICULAS.
CREATE TABLE compras( codigo_pelicula INT NOT NULL, dni_cliente INT NOT NULL, fecha_compra DATE NOT NULL, CONSTRAINT claveprimaria PRIMARY KEY(codigo_pelicula,dni_cliente), INDEX (dni_cliente), CONSTRAINT claveforana FOREIGN KEY (dni_cliente) REFERENCES clientes(dni_cliente) on delete cascade on update cascade, INDEX (codigo_pelicula), CONSTRAINT claveforanaa FOREIGN KEY (codigo_pelicula) REFERENCES peliculas(cod_pelicula) on delete cascade on update cascade ) ENGINE=innoDB;
5.- Responde a las siguientes preguntas. a. La tabla CLIENTES está inicialmente vacía (no hemos introducido datos). ¿Es posible introducir una compra en la tabla de COMPRAS? -Incompleto
b. ¿Qué ocurre si intentamos eliminar la tabla CLIENTES y la tabla COMPRAS no está vacía? -Incompleto
c. Tenemos los siguientes datos en la tabla CLIENTES. Indica a continuación si los siguientes INSERT en la tabla COMPRAS son correctos (supón que todos los artículos que introduzcas existen en la tabla ARTICULOS).
- INSERT INTO COMPRAS VALUES(444555666,888999777,`06/05/05´); Esta mal porque no existe el numero de DNI.en la tabla clientes
- INSERT INTO COMPRAS VALUES(666777666,111222333,`09/10/05´); CORRECTA
- INSERT INTO COMPRAS VALUES(987654321,88899988,09/11/05); Esta mal porque el dni del cliente no existe en la tabla clientes y ademas la fecha no esta entre comillas
6.- Crea las siguientes tablas: create table personas ( dni varchar(100) not null, nombre varchar(100) not null, apellido1 varchar(100) not null, apellido2 varchar(100) not null, codFamilia int(100) not null, constraint clave primary key (dni), index (codFamilia), constraint family foreign key (codFamilia) references familia(codFamilia) on delete cascade on update cascade )ENGINE=InnoDB ;
create table vehiculos ( codVehiculo int(100) not null, marca varchar(100) not null, modelo varchar(100) not null, cilindrada int(100) not null, dni_persona varchar(100) not null, primary key (codVehiculo), index (dni_persona), constraint personass foreign key (dni_persona) references personas(dni) on delete cascade on update cascade ) ENGINE=InnoDB ;
create table personasVehiculos ( dni_personas varchar(100) not null, codVehiculo int(100) not null, primary key (dni_personas,codVehiculo) );ENGINE=InnoDB ;
create table familia ( descripcion varchar(100) not null, codFamilia int(100) not null, primary key (codFamilia) )ENGINE=InnoDB ;
Identificar las claves primarias de cada tabla y las claves foráneas teniendo en cuenta que 1 PERSONA puede tener 0 o varios VEHICULOS. 1 FAMILIA tendrá 2 o más componentes,1 PERSONA puede pertenecer a 1 o a ninguna familia.
Insertar en tablas varios registros e indicar el orden de inserción de los mismos, es decir, en que tabla se inserta primero, segundo... 7.-
insert into familia (CodigoFamilia, DescripcionFamilia) VALUES (1,'Los simpson'); insert into familia (CodigoFamilia, DescripcionFamilia) VALUES (2,'Los Flanders'); insert into familia (CodigoFamilia, DescripcionFamilia) VALUES (3,'Apu Nahasapeemapetilon'); insert into personas VALUES (1,1,'Bart','J','Simpson'); insert into personas VALUES (2,1,'Homer','J','Simpson'); insert into personas VALUES (3,1,'Marge','J','Simpson'); insert into personas VALUES (4,1,'Lisa','J','Simpson'); insert into personas VALUES (5,1,'Magi','J','Simpson'); insert into personas VALUES (6,2,'Flandere','F','Flanders'); insert into personas VALUES (7,3,'Apu','Nahasapeemapetilon','Su'); insert into vehiculos VALUES (1,2,'Renault','Megane','105cv'); insert into vehiculos VALUES (2,7,'Renault','5','2cv'); insert into vehiculos VALUES (3,3,'Volkswagen','Passat','160cv'); insert into vehiculos VALUES (4,6,'Citroen','C2','95cvcv'); insert into vehiculos VALUES (5,1,'Volskwagen','Sirocco','180cv');
8.- Crea las siguientes tablas en MySQL especificando en cada una de ellas la clave primaria y ajena(s) en caso de que las haya. Se debe permitir la eliminación de registros en cascada. create table clientes( dni varchar(9) primary key not null, nombre varchar(100) not null, apellidos varchar(100) not null, provincia varchar(150) default 'Murcia', cod_postal int(5), tfno int(9), fecha_nac DATE not null ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; create table coches( matricula varchar(9) primary key not null, marca varchar(255) not null, modelo varchar(255) not null, color varchar(255) default 'Negro', num_caballos int default 90, num_puertas int default 5, codigo_cliente varchar(9), index (codigo_cliente), constraint claveexterna foreign key(codigo_cliente) references clientes(dni) on delete cascade ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
create table revisiones( id int not null auto_increment primary key, codigo int not null, precio int not null, codigo_coche varchar(9) not null, index (codigo_coche), constraint externa17889 foreign key(codigo_coche) references coches(matricula) on delete cascade ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
9.- Crea las siguientes tablas en MySQL especificando en cada una de ellas la clave primaria y ajena(s) en caso de que las haya. Se debe permitir la eliminación de registros en cascada. create table medicos ( dni varchar(10)not null, nombre varchar(100)not null, apellidos varchar(100)not null, especialidad varchar(100) default 'Psiquiatria', primary key (dni) ) ENGINE=InnoDB; create table pacientes ( dni varchar(10)not null, nombre varchar(100)not null, apellidos varchar(100)not null, provincia varchar(100) default 'Murcia', cod_postal int(5), telefono int(9), fecha_nac date not null, primary key (dni) ) ENGINE=InnoDB; create table ingresos ( cod_medico varchar(10) not null, dni_paciente varchar(10) not null, fecha_ingreso date not null, primary key (cod_medico,dni_paciente,fecha_ingreso), index(cod_medico), constraint claveforanea foreign key (cod_medico) references medicos(dni) on delete cascade, index(dni_paciente), constraint claveforanea2 foreign key (dni_paciente) references pacientes(dni) on delete cascade ) ENGINE=InnoDB;
10.- Inserta tres registros en cada una de las tablas. A continuación borra un cliente que haya realizado algún ingreso y comprueba que automáticamente desaparecen todos los ingresos que haya realizado el paciente. insert into medicos values("11111111a","juan","martin","neumologia"); insert into medicos values("22222222b","julian","mendez","psiquiatria"); insert into medicos values("33333333c","maria","martin","traumatologia"); insert into pacientes values("44444444f","antonia","gutierrez","Las Palmas",08080,937977966,"1957/04/23"); insert into pacientes values("55555555t","jose","ruiz","cadiz",43700,977977977,"1958/04/23"); insert into pacientes values("66666666f","luis","rubiales","gerona",75400,977977988,"1959/04/23"); insert into ingresos values("11111111a","44444444f","2009/08/03"); insert into ingresos values("22222222b","55555555t","2010/08/03"); insert into ingresos values("33333333c","66666666f","2008/08/03"); delete from pacientes where dni = "44444444f";
11.- Crea la siguiente tabla, llamada INSTITUTOS, con los siguientes campos y restricciones. El campo código_centro se debe incrementar automáticamente cada vez que se inserte un registro en la tabla CENTROS. create table institutos( cod_centro int not null auto_increment, nombre varchar(100) not null, direccion varchar(100) not null, poblacion varchar(100) default 'Murcia', num_plazas int, tipo varchar(1) default 'T', primary key(cod_centro), constraint nom check(nombre=upper(nombre)), constraint num check(num_plazas > 0), constraint type check(tipo in('T','S')) ) ENGINE=InnoDB;
12.- Crea la tabla FABRICANTES con las siguientes columnas y restricciones create table fabricantes( cod_fabricante int not null auto_increment, nombre varchar(100), pais varchar(100) default 'España', primary key (cod_fabricante), constraint nombre check (nombre=upper(nombre)), constraint nombre1 check (pais=upper(pais)) )ENGINE=InnoDB;
13.- Crea la tabla ARTICULOS con los siguientes campos y restricciones. create table articulos( articulo varchar(100), cod_fabricante int, peso int, categoria varchar(100), precio_venta int, precio_costo int, primary key (articulo,cod_fabricante,peso,categoria), constraint categoria check (categoria in("Primera","Segunda","Tercera")), constraint precio_venta check (precio_venta > 0), constraint precio_costo check (precio_costo > 0) )ENGINE=InnoDB;
14.- Añade la restricción de clave ajena a la columna COD_FABRICANTE de la tabla ARTICULOS (creada en el ejercicio anterior) para que referencie a la tabla FABRICANTES. Realiza un borrado en cascada.
alter table articulos add( index(cod_fabricante), constraint claveforanea foreign key (cod_fabricante) references fabricantes(cod_fabricante) on delete cascade )
15.- Añadir a la tabla PERSONAS_VEHICULOS,creada en el ejercicio 6, el campo FechaCompra de tipo Fecha ALTER TABLE PERSONAS_VEHICULOS ADD FechaCompra DATE;
16.- En la tabla PERSONAS modifica el campo nombre para que tenga una longitud de 100 caracteres. alter table personas modify nombre varchar(100);
17.- En la tabla VEHICULOS modifica el campo modelo para que sea un VARCHAR de 150 caracteres. alter table vehiculos modify modelo varchar(150);
18.- Crea una tabla llamada AFICION con dos campos llamados código_afición y descripción. La clave primaria de esta tabla será el campo código_afición.
19.- Crear una tabla llamada AFICION_PERSONAS con los campos dni y código_afición. Identificar y crear las claves primarias(PK) de cada tabla y las claves foráneas(FK) teniendo en cuenta que 1 PERSONA puede tener 0 o varias AFICIONES y 1 AFICION puede pertenecer a varias PERSONAS.
20.- Indica el orden de inserción de datos en las tablas creadas en el ejercicio anterior.
21.- A partir de la tabla EMPLE crea la tabla EMPLE30 con los datos de los empleados del departamento número 30. DROP TABLE EMPLE30 cascade constraints; CREATE TABLE EMPLE30 ( EMP_NO NUMBER(4) NOT NULL, APELLIDO VARCHAR(10) , OFICIO VARCHAR(10) , DIR NUMBER(4) , FECHA_ALT DATE , SALARIO NUMBER(10), COMISION NUMBER(10), DEPT_NO NUMBER(2) NOT NULL) ;
22.- Añade a la tabla PACIENTE, creada en el ejercicio 9, un campo llamado sexo. A continuación añade una restricción para que el sexo del paciente sólo pueda ser `Hombre´ o `Mujer´.
23.- Elimina la columna COD_POSTAL de la tabla PACIENTE. 24.- Crea una nueva tabla llamada PROFESORES con los campos dni, nombre, especialidad y código de centro. Dicha tabla no tendrá claves primarias ni ajenas. CREATE TABLE PROFESORES ( COD_CENTRO NUMBER(4) NOT NULL, DNI NUMBER(10), APELLIDOS VARCHAR2(30), ESPECIALIDAD VARCHAR2(16) );
25.- Añade a la tabla creada en el ejercicio anterior la restricción de clave primaria (dni) y la restricción de clave ajena (de nombre CLAVE AJENA), que referencia a la tabla INSTITUTOS (tabla creada en el ejercicio 11). CONSTRAINT Prof_Dni_PK PRIMARY KEY,
26.- ¿Qué ocurre si se intenta eliminar una tabla, con la orden DROP TABLE, que es referenciada por claves ajenas? Elimina la tabla INSTITUTOS 27.- Sobre la tabla EMPLE crea una vista, llamada VISTA1, con el apellido, oficio y salario de los empleados que ganan más de 1000 euros. create view VISTA1 as select apellido, oficio ,salario from emple where salar >1000;
28.- Crea una vista, llamada VISTA2, con los apellidos ,oficio, salario y nombre de departamento de los empleados que trabajan en el departamento número 10 create view VISTA2 as select apellido, oficio ,salario from emple where dept_no=10;
29.- Crea un trigger asociado a la tabla DEPARTAMENTOS que se active cada vez que se inserte una fila. La acción del trigger será insertar en la tabla AUDITORIA_DEPART una fila con la fecha y hora de inserción y el nombre del departamento que se inserta. Después inserta filas en DEPARTAMENTOS y comprueba el contenido de la tabla AUDITORIA_DEPART.
CREATE TABLE departamentos ( col1 VARCHAR2(200) ); CREATE OR REPLACE TRIGGER auditar_act_emp BEFORE INSERT OR DELETE ON EMPLE FOR EACH ROW BEGIN IF DELETING THEN INSERT INTO AUDITADEPART VALUES(TO_CHAR(sysdate,'DD/MM/YY*HH24:MI*') || :OLD.EMP_NO|| '*' || :OLD.APELLIDO || '* BORRADO '); ELSIF INSERTING THEN INSERT INTO AUDITAREMPLE VALUES(TO_CHAR(sysdate,'DD/MM/YY*HH24:MI*') || :NEW.EMP_NO || '*' || :NEW.APELLIDO||'* INSERCION '); END IF; END;
30.- Crea un trigger asociado a la tabla EMPLEADOS que se active cada vez que se elimine un empleado. La acción del trigger será insertar en la tabla AUDITORIA_EMPLE una fila con la fecha y hora de inserción y los apellidos y código de empleado que se inserta. Después borra algunos EMPLEADOS de la tabla EMPLE y comprueba el contenido de la tabla AUDITORIA_EMPLE.
CREATE OR REPLACE TRIGGER audit_modif BEFORE UPDATE ON EMPLE FOR EACH ROW DECLARE v_cad_inser auditaremple.col1%TYPE; BEGIN v_cad_inser := TO_CHAR(sysdate,'DD/MM/YY*HH24:MI*') ||:OLD.EMP_NO ||'* MODIFICACION *'; IF UPDATING ('EMP_NO') THEN v_cad_inser := v_cad_inser ||:OLD.EMP_NO|| '*'|| :NEW.EMP_NO; END IF; IF UPDATING ('APELLIDO') THEN v_cad_inser := v_cad_inser ||:OLD.APELLIDO|| '*'||:NEW.APELLIDO; END IF;
IF UPDATING ('OFICIO') THEN v_cad_inser := v_cad_inser ||:OLD.OFICIO|| '*'||:NEW.OFICIO; END IF; IF UPDATING ('DIR') THEN v_cad_inser := v_cad_inser ||:OLD.DIR|| '*'||:NEW.DIR; END IF; IF UPDATING ('FECHA_ALT') THEN v_cad_inser := v_cad_inser ||:OLD.FECHA_ALT||:NEW.FECHA_ALT; END IF; IF UPDATING ('SALARIO') THEN v_cad_inser := v_cad_inser ||:OLD.SALARIO|| '*'||:NEW.SALARIO; END IF; IF UPDATING ('COMISION') THEN v_cad_inser := v_cad_inser ||:OLD.COMISION|| '*'||:NEW.COMISION; END IF; IF UPDATING ('DEPT_NO') THEN v_cad_inser := v_cad_inser ||:OLD.DEPT_NO|| '*'||:NEW.DEPT_NO; END IF; INSERT INTO AUDITAREMPLE VALUES(v_cad_inser); END;
31.- Crea una tabla llamada auditatest1 con tan sólo un campo, llamado campo1(varchar(10)). Inserta en esa tabla 3 registros. A continuación crea un trigger asociado a la operación de modificación sobre la tabla auditatest1. La acción del trigger será insertar en la tabla AUDITORIA_CAMPO1 con el antiguo valor del campo1 antes de la modificación, el nuevo valor que va a tomar y la fecha y hora de la operación create table auditatest1 ( campo1 VARCHAR (10) ; ); CREATE TRIGGER TRIG1 AFTER DELETE ON AUDITATEST1 FOR EACH ROW BEGIN INSERT INTO AUDITORIA_CAMPO1 VALUES (OLD.CAMPO1, NULL, ``BORRADO´´, CURDATE(), CURTIME() ) ; END ;
