Transcript Repaso

Repaso
DBD!!!
(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
Generar el Modelo Relacional de este MER (individual)
codigo_l
titulo_l
estado
Libro
(1,1)
pedido
en
(1,n)
(1,n)
Préstamo
(1,1)
(1,n)
pide
fecha_inicio_p
escribe
pertenece
(1,1)
codigo_a
nombre_a
nacionalidad
genero
rut_a
nombre_a
e-mail_a
(1,n)
fecha_ven_p
codigo_p
Alumno
(1,1)
Autor
Carrera
codigo_c
nombre_c
descripcion_c
(1,n)
dicta
(1,n)
rut_p
nombre_p
titulo universitario
e-mail_p
Professor
(1,1)
(1,n)
realiza
Ramo
codigo_r
nombre_r
creditos
descripcion_r
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.
Parte 3. Creación de BD
(escrito)
• 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.
([email protected])
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 by
Operadores logicos: and, or, not
Operadores de comparación: >, >=…between, like, in,
not in
Funciones de agregado: AVG, COUNT, MAX, MIN, SUM
Predicado: 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 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’
Usando consultas anidadas:
SELECT Nombre AS NOMBRE_ASIGNATURA
FROM ASIGNATURA
WHERE Creditos > (SELECT Creditos
FROM ASIGNATURA
WHERE 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')