RepasoDBD!!!

(Es ahora o nunca)

Parte 1. Modelamiento.

• Trabajo grupal para “soltar la mano”.

• Genere un MER del siguiente problema de acuerdo a lo que Ud. entiende del tema:

• Se requiere registrar la organización territorial de los países (suponiendo que es igual en todos los países) y sus respectivas autoridades.

• De cada parte de esta organización se quiere conocer el nombre, población, km cuadrados, capital, entre otros.

• De cada autoridad se requiere conocer cargo, nombre y apellido, edad, estudios, fecha en que asumió.

• Dentro de las autoridades tomar en cuenta a los Ministros de Estado.

• Trabajo individual. Genere un MER que represente lo siguiente:

• Una empresa almacena en bodega distintos materiales que utilizan sus trabajadores y que son pedidos directamente por ellos.

• Cada material pertenece a un área especifica. Tiene un código propio y necesita del código de su área para reconocerse. Existe el área eléctrica, mecánica, administrativa, etc.

• Cada material tiene un proveedor único. De éste se requiere saber el nombre, contacto, dirección.

• De cada área se debe conocer código, nombre, ubicación, numero de contacto y persona a cargo.

• De cada material debe conocerse el código, nombre, precio, disponibilidad, proveedor, descripción.

• Los trabajadores deben reconocerse por un código interno. Además se requiere conocer nombres y apellidos, edad, tipo (contratado u honorarios) y permisos para pedir materiales. Si es 1 puede pedir cualquier material, si es 2 solo materiales eléctricos, si es 3 solo materiales mecánicos, etc.

• Un trabajador puede tener o no un área a cargo.• Los trabajadores piden materiales, y se debe registrar la fecha y cantidad de material pedido

de acuerdo con sus permisos.• En caso de no utilizarse, el trabajador debe devolver el material, registrándose también la

fecha y cantidad de material devuelto. Además se debe registrar el estado en que se devuelve.

• Trabajo individual-en parejas.

• Genere de forma individual el problema escrito de su proyecto semestral.

• Intercambie el texto con su compañero, quien lo revisará (forma escrita) y le objetará en caso de no entenderse bien.

• Luego este compañero generará el MER correspondiente, que será revisado por el creador.

Parte 2. Modelo Relacional

• Generar el modelo relacional del MER generado para el problema anterior (empresa).

• Grupos de a 2 personas.

Parte 2. Modelo Relacional

Libro Préstamoped idoen

cod igo_p

(1 ,1 ) (1 ,n )titu lo_ l

cod igo_ l

fecha _ in ic io_p

Autor

A lumno

Carrera

RamoProfessor

escr ibe

p ide

pertenec e

d icta

rea liza

(1 ,n )

(1 ,1 )

(1 ,n ) (1 ,1 )

(1 ,1 )

(1 ,n )

(1 ,n )

(1 ,n )

(1 ,n )(1 ,1 )

estado

cod igo_anombre_a

naciona lidadgene ro

fecha_ ven_p

ru t_anombre_a

cod igo_cnombre_cdescr ipc ion _c

cod igo_ rnombre_ r

descr ipc ion _ rcred itos

ru t_pnombre_p

e -ma il_ptitu lo un ive rs ita r io

e -ma il_a

Generar el Modelo Relacional de este MER (individual)

Parte 3. Creación de BD(escrito)

• Genere el código SQL de la BD del problema de la empresa:

• Tomar en cuenta tablas, claves primarias y foráneas, tipos de datos, check (5). (libre)

• En parejas.

• Genere el código SQL de la BD del problema anterior: tablas, claves primarias y foráneas, tipos de datos. (individual)

• Genere los siguientes check:– Fecha de vencimiento de prestamo debe ser mayor a

la fecha de inicio.– El estado de un libro puede ser ‘r’

(reservado),’l’(libre), ‘p’(prestamo).– Los rut deben ser de la forma nn.nnn.nnn-n– Los creditos de los ramos solo pueden ser 3,4 ó 5.– Los correos tengan forma de correo.

(nnnn@nnn.nnn)

Parte 3. Creación de BD(escrito)

Parte 4. SQL-Querys• Para el problema de la empresa, en parejas:• Genere el código SQL:

– para insertar datos en sus tablas.– para modificar datos de sus tablas.– para eliminar datos de sus tablas.

• Genere consultas tomando en cuenta…:Clausulas: Where, order by (ASC, DESC), group byOperadores logicos: and, or, notOperadores de comparación: >, >=…between, like, in, not inFunciones de agregado: AVG, COUNT, MAX, MIN, SUMPredicado: DISTINCT• Escriba la consulta (5) y genere el código SQL.• Individual, tema libre.

Parte 4. SQL-Querys• Para el problema de prestamos, individual:• Genere las siguientes consultas en SQL:1. Conocer el nombre de los libros pedidos despues del 21-12-2010, no

repetir los nombres.2. Conocer el nombre y mail de los profesores que imparten ramos en la

carrera de Construccion Civil.3. Conocer los nombres de los libros pedidos por alumnos de la carrera de

Derecho.4. Conocer la cantidad de alumnos por carrera: mostrar la cantidad y el

nombre de la carrera.5. Conocer al apellido de todas las personas de nombre Luis.6. Nombre de los ramos que tienen mas creditos que “ingles basico”7. Desplegar el rut y nombre del alumno que nunca han solicitado

(préstamo) un libro, los alumnos debe ser de la carrera de informática.8. Conocer el nombre de los profesores que hacen ramos que tengan el

minimo de creditos registrado.

1. Conocer el nombre de los libros pedidos despues del 21-12-2010, no repetir los nombres.

• Select distinct titulo_l • From prestamo, libro• Where prestamo.codigo_l=libro.codigo_l• And fecha_inic>21/12/2010

2. Conocer el nombre y mail de los profesores que imparten ramos en la carrera de Construccion Civil.

• Select nombre_p, a-mail_p• From profesor, ramo, dicta, carrera• Where profesor.rut_p=ramo.rut_p• And ramo.codigo_r=dicta.codigo_r• And dicta.codigo_c=carrera.codigo_c• And nombre_c=‘Construccion Civil’

3. Conocer los nombres de los libros pedidos por alumnos de la carrera de Derecho.

• Select titulo_l• From libro, prestamo, alumno, carrera• Where libro.codigo_l= prestamo.codigo_l• And prestamo.rut_a=alumno.rut_a• And alumno.codigo_c=carrera.codigo_c• And nombre_c=‘Derecho’

4. Conocer la cantidad de alumnos por carrera: mostrar la cantidad y el nombre de la carrera.

• Select count(rut_a), nombre_c• From alumno, carrera• Where alumno.codigo_c=carrera.codigo_c• Group by nombre_c

5. Conocer al apellido de todas las personas de nombre Luis.

• Select distinct al1.apellido• From persona as al1, persona as al2• Where al1.nombre=al2.nombre• and al2.nombre='luis'

6. Nombre de los ramos que tienen mas creditos que “ingles basico”

• Select r1.nombre_r• From ramo as r1, ramo as r2• Where r1.creditos>r2.creditos• and r2.nombre_r=‘ingles basico'

7. Desplegar el rut y nombre del alumno que nunca han solicitado (préstamo) un libro, los alumnos deben ser de la carrera de informática.

• SELECT rut_a, nombre_a• FROM alumno, carrera• WHERE alumno.codigo_c=carrera.codigo_c• And nombre_c ='INF' • AND rut_a NOT IN (SELECT rut_a • FROM prestamo, alumno• Where alumno.rut_a=prestamo.rut_a)

8. Conocer el nombre de los profesores que hacen ramos que tengan el minimo de creditos registrado.

• SELECT nombre_p• FROM profesor, ramo• WHERE profesor.codigo_p=ramo.codigo_p• And creditos=(SELECT min(creditos)• FROM ramo)

• Nombre de las asignaturas que tienen más créditos que "Seguridad Vial".

• Usando consultas anidadas:• SELECT Nombre AS NOMBRE_ASIGNATURA• FROM ASIGNATURA• WHERE Creditos > (SELECT Creditos• FROM ASIGNATURA• WHERE Nombre = ‘Seguridad Vial’)

• Usando alias:• SELECT DISTINCT(ASIG1.Nombre) AS NOMBRE_ASIGNATURA• FROM ASIGNATURA AS ASIG1, ASIGNATURA AS ASIG2• WHERE ASIG1.Creditos > ASIG2.Creditos• AND ASIG2.Nombre = ‘Seguridad Vial’

• Mostrar el identificador de los alumnos matriculados en cualquier asignatura excepto la "150212" o la "130113"

• SELECT IdAlumno• FROM ALUMNO• WHERE IdAlumno NOT IN (SELECT IdAlumno• FROM ALUMNO_ASIGNATURA• WHERE IdAsignatura = "150212" OR

IdAsignatura = "130113")

• Id de los alumnos matriculados en la asignatura "150212" pero no en la "130113".

• SELECT IdAlumno• FROM ALUMNO• WHERE IdAlumno IN (SELECT IdAlumno• FROM ALUMNOASIGNATURA• WHERE IdAsignatura = "150212")• AND IdAlumno NOT IN (SELECT IdAlumno• FROM ALUMNOASIGNATURA• WHERE IdAsignatura = "130113");

• Nombre de las asignaturas de la titulación "130110" cuyos costes básicos sobrepasen el coste básico promedio por asignatura en esa titulación.

• SELECT Nombre FROM ASIGNATURA• WHERE CosteBasico > (SELECT AVG(CosteBasico)• FROM ASIGNATURA• WHERE IdTitulacion = "130110")• AND IdTitulacion = "130110";

• DNI, Nombre y Apellido de los alumnos a los que imparte clases el profesor Jorge Sáenz.

• Usando Alias:

• SELECT P1.DNI AS DNI, P1.Nombre AS NOMBRE, P1.Apellido AS APELLIDO FROM PERSONA AS P1, PERSONA AS P2, ALUMNO, ALUMNOASIGNATURA, ASIGNATURA, PROFESOR

• WHERE P1.DNI = ALUMNO.DNI• AND P2.DNI = PROFESOR.DNI• AND ALUMNO.IdAlumno = ALUMNOASIGNATURA.IdAlumno• AND ALUMNOASIGNATURA.IdAsignatura =

ASIGNATURA.IdAsignatura• AND ASIGNATURA.IdProfesor = PROFESOR.IdProfesor• AND P2.Nombre = 'Jorge'• AND P2.Apellido = 'Sáenz‘

• Usando consultas anidadas:

• SELECT PERSONA.DNI AS DNI, PERSONA.Nombre AS NOMBRE, PERSONA.Apellido AS APELLIDO

• FROM ALUMNO, ALUMNOASIGNATURA, ASIGNATURA, PERSONA• WHERE PERSONA.DNI = ALUMNO.DNI• AND ALUMNO.IdAlumno = ALUMNOASIGNATURA.IdAlumno• AND ALUMNOASIGNATURA.IdAsignatura =

ASIGNATURA.IdAsignatura• AND ASIGNATURA.IdProfesor IN• (SELECT PROFESOR.IdProfesor• FROM PERSONA, PROFESOR• WHERE PERSONA.DNI = PROFESOR.DNI• AND PERSONA.Nombre = 'Jorge'• AND PERSONA.Apellido = 'Sáenz')