Tareas 1º – Base de datos – DAM/DAW e-learning resueltas

Tarea para BD02

Detalles de la tarea de esta unidad.

Enunciado.

Realiza los ejercicios en la Hoja de trabajo de SQLDeveloper o desde SQLPlus conectando al usuario, creado en la primera tarea y comprueba que las sentencias son correctas antes de incluirlas en el script. Una vez hayas hecho todas para probar el script, desde SQLPlus, ejecuta start nombrescript.sql o @nombrescript.sql eliminando previamente todas las tablas creadas para poder crearlas de nuevo.

Ejercicio 1:

Vamos a crear las tablas para una tienda virtual que distribuye productos agrupados en familias en varias tiendas.

Realiza un script llamado Creatienda.sql que implemente los ejercicios descritos a continuación.

Precede cada una de las sentencias SQL de los ejercicios con un comentario que incluya el enunciado del ejercicio correspondiente. Recuerda que los comentarios van precedidos con el simbolo – al inicio de la linea.

Con las sentencias DDL de SQ crea las tablas especificadas a continuación aplicando las restricciones (constraints) pedidas. Se debe cumplir la integridad referencial.

TABLA FAMILIA; Contiene las familias a las que pertenecen los productos, como por ejemplo ordenadores, impresoras, etc.

TABLA PRODUCTO: contendrá información general sobre los productos que distribuye la empresa a las tiendas.

TABLA TIENDA: contendrá información básica sobre las tiendas que distribuyen los productos.

TABLA STOCK: Contendrá para cada tienda el número de unidades disponibles de cada producto. La clave primaria está formada por la concatenación de los campos codtienda y codproducto.

Ejercicio 2:

A) Modificar las tablas creadas en el ejercicio anterior siguiendo las indicaciones. Los ejercicios se incluirán en un script llamado ModificarTienda.sql. Cada uno de ellos, como en el ejercicio anterior, irá precedido de un comentario con el enunciado.

• Añadir a la tabla STOCK
  • Una columna de tipo fecha llamada FechaUltimaEntrada porcentaje que por defecto tome el valor de la fecha actual.
  • Una columna llamada Beneficio que contendrá el tipo de porcentaje de beneficio que esa tienda aplica en ese producto. Se debe controlar que el valor que almacene sea 1,2,3,4 o 5.
• En la tabla PRODUCTO
  • Eliminar de la tabla producto la columna Descripción.
  • Añadir una columna llamada perecedero que únicamente acepte los valores: S o N.
  • Modificar el tamaño de la columna Denoproducto a 50.
• En la tabla FAMILIA
  • Añadir una columna llamada IVA, que represente el porcentaje de IVA y únicamente pueda contener los valores 21,10 o 4.
• En la tabla tienda
  • La empresa desea restringir el número de tiendas con las que trabaja, de forma que no pueda haber más de una tienda en una misma zona (la zona se identifica por el código postal). Definir mediante DDL las restricciones necesarias para que se cumpla en el campo correspondiente.

  B) Renombra la tabla STOCK por PRODXTIENDAS.

  C) Elimina la tabla FAMILIA y su contenido si lo tuviera.

  D) Crea un usuario llamado C##INVITADO siguiendo los pasos de la unidad 1 y dale todos los privilegios sobre la tabla PRODUCTO.

  E) Retira los permisos de modificar la estructura de la tabla y borrar contenido de la tabla PRODUCTO al usuario anterior.

Ejercicio 3:

SQLDeveloper permite obtener el diagrama del modelo entidad relación a partir de las tablas ya creadas con la información contenida en el Diccionario de Datos. Una vez tengas realizados los ejercicios 1 y 2 genera el diagrama entidad relación y expórtalo en formato PNG.

Aquí lo puedes descargar.

La tarea consiste en realizar el Diseño Conceptual y el Diseño lógico en el modelo relacional del Sistema de Información descrito a continuación.

Un Centro de enseñanza de secundaria desea registrar los datos de los alumnos que han cursado ciclos de FP manteniendo actualizados los datos con los trabajos o formaciones recibidas porque a menudo en el Centro, se reciben llamadas de empresas solicitando alumnos que hayan finalizado con un perfil determinado para ocupar una vacante.

Con esta base de datos queremos registrar y así poder obtener a posteriori, la información de las actividades realizadas por los alumnos en las FCT, y en trabajos anteriores, así como los estudios que hayan cursado y finalizado consiguiendo de esta manera una selección de alumnos más rápida y eficaz al perfil solicitado por la empresa.

Además se registrarán los proyectos que entregan los alumnos en el módulo final de Proyectos.

También guardaremos información de las empresas relacionadas con los ciclos con indicación de las actividades o áreas que desarrollan.

ANALISIS DEL SISTEMA DE INFORMACIÓN:

Especificaciones y requerimientos:

De los alumnos interesa conocer sus datos personales, académicos y experiencia laboral:

• Datos personales: DNI, nombre, apellidos, fecha nacimiento, dirección, Municipio, Provincia, Código postal, email y teléfono de contacto.
• Datos académicos:
  • Información de la FCT: Empresa en la que la ha realizado, Convocatoria (JUN o DIC) y año, Areas en las que las ha desarrollado (Programación, Redes, Sistemas Operativos,..)
  • Información del Proyecto del Ciclo actual: Título del proyecto, Breve descripción, Profesor tutor (dni,nombre y apellidos, teléfono, email), Fecha presentación, Nota obtenida
  • Para cada titulación obtenida por el alumno: Título, Nombre del Centro, Municipio del Centro, Provincia del Centro, Año, Nota obtenida por el alumno en ese título.
• Experiencia laboral con indicación para cada una de las empresas en las que ha trabajado de: Nombre de la Empresa. Fecha inicio de trabajo del alumno en esa empresa, Fecha fin de trabajo del alumno en esa empresa (si sigue trabajando no tendrá información), Areas o actividades en las que ha trabajado en ese periodo en esa empresa

De las empresas interesa conocer: CIF, Nombre,  NumConvenio (Número de convenio con el Centro en el caso de que exista, para hacer la FCT), Dirección, Municipio, Código Postal, Email, Teléfono, Sitio Web,  Areas o actividades en las que trabaja (Programación, Redes, Sistemas Operativos,..)

Restricciones:

Una ver recogidos todos los datos debemos tener en cuenta una serie de restricciones necesarias para que la base de datos tenga coherencia y no se produzcan errores:

• Los proyectos podrán ser realizados por varios alumnos.
• Un proyecto solo podrá tener asignado un tutor.
• Un tutor podrá tener más de un proyecto.
• Un alumno podrá tener solamente un proyecto.
• Un alumno podrá haber estudiado en distintos centros, o en el mismo centro, varias titulaciones diferentes.
• Un alumno solo realizará las prácticas en una empresa.
• Un alumno podrá haber trabajado en distintas empresas o en la misma varias veces.
• Una empresa podrá acoger a varios alumnos en prácticas.
• Una empresa podrá contratar a varios alumnos.
• Una empresa puede dedicarse a varias actividades.

DISEÑO CONCEPTUAL

Comienza identificando las entidades, sus atributos con la descripción, posibles valores y restricciones, y el atributo o atributos clave de la entidad.

Utiliza una herramienta gráfica tipo DIA o similar para realizar el MER. Para no sobrecargar mucho el MER escribe únicamente los atributos que sean clave y los atributos de las relaciones.Incluye a continuación toda la información para cada una de las entidades del modelo comenzando por una breve descripción de la entidad. Nosotros representaremos esta información en modo de tabla, aunque también se puede representar gráficamente.

A menudo se descompone el Modelo Entidad Relación en dos diagramas: El diagrama Entidad atributo, donde se reflejan cada entidad con sus atributos, y el diagrama Entidad Relación donde se especifican las entidades con sus claves y la relación entre ellas.

Utiliza verbos descriptivos para las relaciones entre las entidades. Si lo crees conveniente explica las relaciones que consideres convenientes.

Utiliza el siguiente formato para cada una de las entidades:

NOMBRE ENTIDAD: Descripción breve de contenido

DISEÑO LÓGICO
Realiza el diseño lógico en el modelo relacional pasando a tablas el modelo conceptual obtenido, detallando las tablas, atributos, restricciones, claves principales y claves ajenas o foráneas.Aplica el proceso de Normalización a las tablas obtenidas hasta la Forma Normal de Boyce Codd

Debes entregar un ZIP que incluya el diagrama Entidad Relación (formato jpg o dia) y un documento con la descripción de entidades según especificaciones para el Diseño conceptual y descripción de tablas normalizadas para el Diseño lógico. Realiza cuantos comentarios consideres oportunos para clarificar el proceso.

Aquí puedes descargar la tarea.

CREATE TABLE CENTRO(
    codcentro number(2) not null, 
    direccion varchar2(30) not null, 
    localidad varchar2(20) not null
);
insert into centro values (01,'Rambla Nova','Tarragona');
insert into centro values (02,'Alcala','Madrid');
insert into centro values (03,'Sierpes','Sevilla');
alter table centro add constraints pk_codcentro primary key(codcentro); 
CREATE TABLE DPTO(
    coddpto number(2) not null, 
    denominacion varchar2(20) not null, 
    codcentro number(2) not null,
    coddptodepende number(2), 
    codemplejefe number(3) not null,
    tipo char(1) not null , 
    presupuesto number(8,2) not null
);
insert into dpto values (01,'DIRECCIÓN',01,NULL,01,'P',100000);
insert into dpto values (02,'ADMINISTRACION',01,01,03,'F',50000);
insert into dpto values (03,'RECURSOS HUMANOS',01,01,05,'P',30000);
insert into dpto values (05,'CENTRAL COMERCIAL',01,01,07,'P',100000);
insert into dpto values (06,'COMERCIAL CENTRO',02,05,02,'F',5000);
insert into dpto values (07,'COMERCIAL SUR',03,05,04,'F',40000); 
create table empleado(
    codemple number(3) not null, 
    ape1 varchar2(20) not null,
    ape2 varchar2(20) not null,
    nombre varchar2(15) not null,
    direccion varchar2(25) not null,
    localidad varchar(25) not null,
    telef varchar(9),
    coddpto number(2) not null,
    codcate number(2) not null,
    fechaingreso date not null,
    salario number(6,2) not null,
    comision number(6,2)
);
insert into empleado values (01,'LOPEZ','GARCIA','ANA','C/ ANAS','MADRID',666666666,01,01,TO_DATE('01/02/2000','DD/MM/YYYY'),3000,NULL);
insert into empleado values (02,'FERNANDEZ','MORON','JUAN','C/FUENTE','TARRAGONA', 7777777,01,02,TO_DATE('01/02/2002','DD/MM/YYYY'),2000,NULL);
insert into empleado values (03,'CORTES','LOPEZ','ANGEL','C/CIFUENTES','BARACALDO',888888,02,01,TO_DATE('01/03/2003','DD/MM/YYYY'),2000,NULL);
insert into empleado values (04,'SANCHEZ','LUZ','FABIOLA','C/CARDON','SEVILLA',99999999,03,02,TO_DATE('21/05/2001','DD/MM/YYYY'),2500,NULL);
insert into empleado values (05,'RAJOY','AZNAR','PAZ','C/MAR','JAEN',88888888,03,01, TO_DATE('23/02/2000','DD/MM/YYYY'),2000,130);
insert into empleado values (06,'ZAPATERO','GALLARDON','ANGUSTIAS','C/SUR','MADRID', 78787878,05,03,TO_DATE('01/02/2000','DD/MM/YYYY'),2000,NULL);
insert into empleado values (07,'FLOR','LUZ','BLANCA','C/TECLA','SEVILLA',7777777,06,01,TO_DATE('01/02/2000','DD/MM/YYYY'),3000,130);
insert into empleado values (08,'ROS','SANTON','ALFONSO','C/ LUZ','MADRID',888888,07,03,TO_DATE('01/02/2003','DD/MM/YYYY'),2000,NULL);
insert into empleado values (09,'LOPEZ','ITURRIALDE','GANDI','C/OASIS','TARRAGONA',777777,05,01,TO_DATE('01/02/1998','DD/MM/YYYY'),1500,210);
insert into empleado values (10,'JAZMIN','EXPOSITO','MARIA','C/MANDRAGORA','MADRID',888888,05,03,TO_DATE('01/03/2001','DD/MM/YYYY'),1000,200);
alter table dpto add constraints pk_coddpto primary key (coddpto);
alter table dpto add constraints fk_codcentro foreign key(codcentro) references centro(codcentro);
alter table dpto add constraints fk_coddptodepende foreign key(coddptodepende) references dpto(coddpto);
alter table dpto add constraints chk_tipo check(tipo in('P','F')); 
alter table empleado add constraints pk_codemple primary key(codemple);
alter table dpto add constraints fk_codemplejefe foreign key (codemplejefe) references empleado(codemple);
alter table empleado add constraints fk_coddpto foreign key(coddpto) references dpto(coddpto);
create table categoria (codcate number(2) not null, denom varchar2(20) not null, julio number(6,2) not null, diciembre number(6,2) not null);
insert into categoria values (1,'ALTOS DIRECTIVOS',6000,5000);
insert into categoria values (2,'DIRECTIVOS',3000,2000);
insert into categoria values (3,'ADMINISTRATIVOS',2000,1500);
alter table categoria add constraints pk_codcate primary key(codcate);
alter table empleado add constraints fk_codcate foreign key(codcate) references categoria(codcate);

1. Obtener los nombres y salarios de los empleados con más de 1000 euros de salario por orden alfabético.
2. Obtener el nombre de los empleados cuya comisión es superior al 20% de su salario.
3. Obtener el código de empleado, código de departamento, nombre y sueldo total en pesetas, de aquellos empleados cuyo sueldo total (salario más comisión) supera los 1800 euros. Presentarlos ordenados por código de departamento y, dentro de éstos, por orden alfabético.
4. Obtener, por orden alfabético, los nombres de empleados cuyo salario igualen o superen en más de un 5% al salario de la empleada ‘MARIA JAZMIN’.
5. Obtener un listado ordenado por años en la empresa con los nombres,y apellidos de los empleados, y los años de antigüedad en la empresa.
6. Obtener el nombre de los empleados que trabajan en un departamento con presupuesto superior a 50.000 euros. Hay que usar predicado cuantificado.
7. Obtener los nombres y apellidos de empleados que más cobran en la empresa. Considerar el salario más la comisión.
8. Obtener en orden alfabético los nombres de empleado cuyo salario es inferior al mínimo de los empleados del departamento 1.
9. Obtener los nombres de empleados que trabajan en el departamento del cuál es jefe el empleado con código 1.
10. Obtener los nombres de los empleados cuyo primer apellido empiece por las letras p, q, r, s.
11. Obtener los empleados cuyo nombre de pila contenga el nombre JUAN.
12. Obtener los nombres de los empleados que viven en ciudades en las que hay algún centro de trabajo.
13. Obtener el nombre del jefe de departamento que tiene mayor salario de entre los jefes de departamento.
14. Obtener en orden alfabético los salarios y nombres de los empleados cuyo salario sea superior al 60% del máximo salario de la empresa.
15. Obtener en cuántas ciudades distintas viven los empleados.
16. El nombre y apellidos del empleado que más salario cobra.
17. Obtener las localidades y número de empleados de aquellas en las que viven más de 3 empleados.
18. Obtener, para cada departamento, cuántos empleados trabajan, la suma de sus salarios y la suma de sus comisiones, para aquellos departamentos en los que hay algún empleado cuyo salario es superior a 1700 euros.
19. Obtener el departamento que más empleados tiene.
20. Obtener los nombres de todos los centros y los departamentos que se ubican en cada uno, así como aquellos centros que no tienen departamentos.
21. Obtener el nombre del departamento de más alto nivel, es decir, aquel que no depende de ningún otro.
22. Obtener todos los departamentos existentes en la empresa y los empleados (si los tiene) que pertenecen a él.
23. Obtener un listado en el que aparezcan todos los departamentos existentes y el departamento del cual depende, si depende de alguno.
24. Obtener un listado, ordenado alfabéticamente, donde aparezcan los nombres de los empleados y a continuación el literal «tiene comisión» si la tiene, y «no tiene comisión» si no la tiene.
25. Obtener un listado de las localidades en las que hay centros y no vive ningún empleado, ordenado alfabéticamente.
26. Obtener un listado de las localidades en las que hay centros y además vive al menos un empleado, ordenado alfabéticamente.
27. Esta cuestión puntúa doble. Se desea dar una gratificación por navidades en función de la antigüedad en la empresa siguiendo estas pautas:
    • Si lleva entre 1 y 5 años, se le dará 100 euros
    • Si lleva entre 6 y 10 años, se le dará 50 euros por año
    • Si lleva entre 11 y 20 años, se le dará 70 euros por año
    • Si lleva más de 21 años, se le dará 100 euros por año

Obtener un listado de los empleados, ordenado alfabéticamente, indicando cuánto le corresponde de gratificación.

28. Obtener los nombres y apellidos de los empleados que no son jefes de departamento.

Aquí puedes descargar la tarea.