Parcial#2

Primera parte:

Parte 2.

Una escuela de básica secundaria desea sistematizar el proceso interno del manejo de la matrícula, para lo cual requiere que se le construya una BD que permita almacenar del estudiante(código, nombre, dirección, teléfono, acudiente, email) tenga presente que un acudiente puede representar varios estudiantes.

También se debe almacenar la información del docente(código, nombre, teléfono, email) que le dicta la materia al estudiante, tenga presente que un docente puede dictar varias materias.

Se debe almacenar un registro de las materias vistas por el estudiante(código de la materia, nombre de la materia) tenga presente que un estudiante puede ver varias materias.

1. Darle un nombre a la base de datos.

R//:Matricula

2. Listado de tablas que llevara la BD. (cuales son referenciales y cuales son de movimiento)

R//:

cuales son referenciales: 

• ACUDIENTE
• ESTUDIANTE
• MATERIA
• PROFESOR 

cuales son de movimiento: 

• ESTUDIANTE_ACUDIENTE
  
• MATERIA-PROFESOR
  
• ESTUDIANTE_MATERIA

3. Diseñar la estructura de cada tabla (campo, nombre el campo, tipo, tamaño y descripción).

4. Montar o diseñar el modelo de Tablas. 

5. Dibujar el Diagrama relacional. 

6. Crear la BD y las tablas respectivas en MySQL .

7. A cada tabla ingresarle 10 registros

8. Crear una copia de la BD.

Base de datos:

TALLER CONSULTAS RELACIONALES

Con la información de la base de datos de la Institución realizar las siguientes consultas: 

1. Visualizar los estudiantes con sus acudientes

R//:

Análisis:

Que desea mostrar:

• Acudientes 
• Estudiantes

Tablas involucradas:

• Acudiente
• Estudiante
• Estudiante_Acudiente

Relaciones:

• estudiante.id_estdiante = estudiante_acudiente.Fk_estudiante
  
• acudiente.id_acudiente = estudiante_acudiente.Fk_Acudiente

2. Que estudiantes no tienen acudiente asignado

 Que desea mostrar:

• Estudiante

Tablas involucradas:

• Acudiente
• Estudiante
• Estudiante_Acudiente

Relaciones:

• estudiante.id_estudiante=estudiante_acudiente.Fk_estudiante

• left join

• SELECT estudiante.nombre FROM estudiante LEFT JOIN estudiante_acudiente ON estudiante.id_Estudiante = estudiante_acudiente.FK_Estudiante WHERE estudiante_acudiente.FK_Estudiante IS NULL;

R//: Todos los estudiantes tienen acudiente.

3. Que acudientes no matricularon sus hijos en la institución

Qué desea mostrar:

Acudiente

Tablas involucradas:

Acudiente
Estudiante_Acudiente

Relaciones:

acudiente.id_acudiente = estudiante_acudiente.Fk_acudiente

Función a utilizar:

LEFT JOIN

Código:SELECT acudiente.nombre FROM acudiente LEFT JOIN estudiante_acudiente ON acudiente.id_Acudiente = estudiante_acudiente.FK_Acudiente WHERE estudiante_acudiente.FK_Acudiente IS NULL;

4.Visualizar las materias que matriculó el estudiante 00001 

Qué desea mostrar:

Materias matriculadas por un estudiante

Tablas involucradas:

Estudiante
Materia
Estudiante_Materia

Relaciones:

estudiante.id_estudiante = estudiante_materia.Fk_estudiante
materia.id_materia = estudiante_materia.Fk_materia

Función a utilizar:

INNER JOIN

Código:SELECT materia.nombre FROM materia INNER JOIN estudiante_materia ON materia.id_Materia = estudiante_materia.FK_Materia WHERE estudiante_materia.FK_Estudiante = '00001';

5. Visualizar los estudiantes con las materias que matricularon

Qué desea mostrar:

Estudiantes con sus materias matriculadas

Tablas involucradas:

Estudiante
Materia
Estudiante_Materia

Relaciones:

estudiante.id_estudiante = estudiante_materia.Fk_estudiante
materia.id_materia = estudiante_materia.Fk_materia

Función a utilizar:

INNER JOIN

Código:SELECT estudiante.nombre, materia.nombre FROM estudiante INNER JOIN estudiante_materia ON estudiante.id_Estudiante = estudiante_materia.FK_Estudiante INNER JOIN materia ON materia.id_Materia = estudiante_materia.FK_Materia;

6. Visualizar un listado de los estudiantes que no se han matriculado

Qué desea mostrar:

Estudiantes no matriculados

Tablas involucradas:

Estudiante
Estudiante_Materia

Relaciones:

estudiante.id_estudiante = estudiante_materia.Fk_estudiante

Función a utilizar:

LEFT JOIN

Código:SELECT estudiante.nombre FROM estudiante LEFT JOIN estudiante_materia ON estudiante.id_Estudiante = estudiante_materia.FK_Estudiante WHERE estudiante_materia.FK_Estudiante IS NULL;

8. Visualizar los docentes con las materias que dictan

Qué desea mostrar:

Docentes con sus materias

Tablas involucradas:

Profesor
Materia
Materia_Profesor

Relaciones:

profesor.id_profesor = materia_profesor.FK_Profesor
materia.id_materia = materia_profesor.FK_Materia

Función a utilizar:

INNER JOIN

Código:SELECT profesor.nombre, materia.nombre FROM profesor INNER JOIN materia_profesor ON profesor.id_profesor = materia_profesor.FK_Profesor INNER JOIN materia ON materia.id_materia = materia_profesor.FK_Materia;

9. Visualizar las materias que no tienen docente asignado

Qué desea mostrar:

Materias sin docente asignado

Tablas involucradas:

Materia
Materia_Profesor

Relaciones:

materia.id_materia = materia_profesor.FK_Materia

Función a utilizar:

select

Código:SELECT nombre FROM materia WHERE id_Materia NOT IN (SELECT FK_Materia FROM materia_profesor);

10. Visualizar qué docentes no tienen materias asignadas

Qué desea mostrar:

Docentes sin materias asignadas

Tablas involucradas:

Profesor
Materia_Profesor

Relaciones:

profesor.id_profesor = materia_profesor.FK_Profesor

Función a utilizar:

Select

Código:SELECT nombre FROM profesor WHERE id_Profesor NOT IN (SELECT FK_Profesor FROM materia_profesor);

11. Visualizar el estudiante con las materias que tiene matriculadas y qué docentes las dictan

Qué desea mostrar:

Estudiantes con sus materias matriculadas y los docentes que las dictan

Tablas involucradas:

Estudiante
Estudiante_Materia
Materia
Materia_Profesor
Profesor

Relaciones:

• estudiante.id_estudiante = estudiante_materia.FK_Estudiante
• materia.id_materia = estudiante_materia.FK_Materia
• materia.id_materia = materia_profesor.FK_Materia
• profesor.id_profesor = materia_profesor.FK_Profesor

Función a utilizar:

INNER JOIN

Código:SELECT estudiante.nombre, materia.nombre AS nombre_materia, profesor.nombre AS nombre_docente FROM materia INNER JOIN estudiante_materia ON materia.id_Materia = estudiante_materia.FK_Materia INNER JOIN estudiante ON estudiante.id_Estudiante = estudiante_materia.FK_Estudiante INNER JOIN materia_profesor ON materia.id_Materia = materia_profesor.FK_Materia INNER JOIN profesor ON profesor.id_Profesor = materia_profesor.FK_Profesor;

Segunda Parte:

SEGUNDO PARCIAL

1. Obtener los nombres de los profesores que dictan en la carrera de Ingeniería de Sistemas

Qué desea mostrar:

• Nombres de los profesores que dictan en la carrera de Ingeniería de Sistemas.

Tablas involucradas:

• Profesor
• Carrera
• ProfCar 
• Nota para mi:  ProfCar relaciona a los profesores con las carreras
  

Relaciones:

• profcar.codprofe = profesor.codprofe
• profcar.codcarre = carrera.codcarre

Función a utilizar:

• JOIN

Código:MariaDB [matricula2]> SELECT p.nombre AS profesor FROM profesor p JOIN profcar pc ON p.codprofe = pc.codprofe JOIN carrera c ON pc.codcarre = c.codcarre WHERE c.carrera = 'Ingenieria de Sistemas';

2. Obtener los alumnos matriculados en la carrera de Derecho y los profesores que dictan en esa carrera.

Qué desea mostrar:

• Alumnos matriculados en la carrera de Derecho y los profesores que dictan en esa carrera.

Tablas involucradas:

• Alumno
• Carrera
• Profesor
• ProfCar 
• AlumCar 
• Nota para mi: AlumCar relaciona a los alumnos con las carreras

Relaciones:

• alumcar.codestu = alumno.codestu
• alumcar.codcarre = carrera.codcarre
• profcar.codprofe = profesor.codprofe
• profcar.codcarre = carrera.codcarre

Función a utilizar:

• JOIN

Código: select alumno.nombre as 'alumno', profesor.nombre as 'profesor', carrera.carrera as 'carerra' from alumno join matricula on matricula.codestu=alumno.codestu join carrera on matricula.codcarre=carrera.codcarre join profcar on matricula.codprofe = profcar.codprofe join profesor on profcar.codprofe = profesor.codprofe where carrera.carrera ='Derecho';

3. Obtener cuánto les costó el semestre a los alumnos matriculados en Veterinaria.

Qué desea mostrar:

• Alumnos matriculados en Veterinaria y el costo del semestre.

Tablas involucradas:

• Alumno
• Carrera
• Matricula 
• AlumCar 
  

Relaciones:

• matricula.codcarre = carrera.codcarre
  
• matricula.codestu = alumno.codestu
• alumcar.codcarre = carrera.codcarre
• alumcar.codestu = alumno.codestu

Función a utilizar:

•  JOIN

Código:select alumno.nombre as 'alumno', carrera.carrera as 'carerra', matricula.valorSemestre as 'valor semestre' from alumno join matricula on matricula.codestu = alumno.codestu join carrera on matricula.codCarre = carrera.codCarre where carrera.carrera = 'Veterinaria';

4. Obtener los nombres de los alumnos que tendrían clases con el profesor Portacio Cartagena

Qué desea mostrar:

• Nombres de los alumnos que tienen clases con el profesor Portacio Cartagena.

Tablas involucradas:

• Alumno
• Matricula 
• Profesor
• profCar 
  

Relaciones:

• alumno.codestu = matricula.Codestu 
  
• profCar.codProfe = profesor.CodProfe 
  
• profCar.codCarre= matricula.codCarre 
  

Función a utilizar:

•  JOIN

Código:SELECT a.nombre FROM alumno a JOIN alumcar ac ON a.codestu = ac.codestu JOIN matricula m ON a.codestu = m.codestu JOIN profcar pc ON m.codcarre = pc.codcarre JOIN profesor p ON pc.codprofe = p.codprofe WHERE p.nombre = 'Portacio Cartagena';

5. Obtener el valor del semestre más alto

Qué desea mostrar:

• El valor más alto del semestre.

Tabla involucrada:

• Matricula

Función a utilizar:

• MAX()

6. Obtener el promedio del valor de semestre

Qué desea mostrar:

• Promedio del valor del semestre.

Tabla involucrada:

• Matricula

Función a utilizar:

• AVG()

Código: select avg(valorsemestre)"Valor promedio matricula bb" from matricula;

7. Obtener los nombres de los estudiantes que comienzan por 'A' o que terminan en 'R'.

Qué desea mostrar:

• Nombres de los estudiantes que comienzan por 'A' o terminan en 'R'.

Tabla involucrada:

• Alumno

Función a utilizar:

• LIKE

Código:SELECT nombre FROM alumno WHERE nombre LIKE 'A%' OR nombre LIKE '%r';

8. Obtener el total del valor del semestre pagado por carrera.

Qué desea mostrar:

• Total del valor del semestre pagado por cada carrera.

Tablas involucradas:

• Matricula
• Carrera

Relaciones:

• matricula.codcarre = carrera.codcarre

Función a utilizar:

• SUM() 

Código:  SELECT c.carrera, SUM(m.valorsemestre) AS total_valor_semestre FROM matricula m JOIN alumcar ac ON m.codestu = ac.codestu JOIN carrera c ON ac.codcarre = c.codcarre GROUP BY c.carrera;

9. Visualizar los estudiantes que no están matriculados

Qué desea mostrar:

• Estudiantes que no están matriculados.

Tablas involucradas:

• Alumno
• Matricula

Relaciones:

• alumno.codestu = matricula.codestu

Función a utilizar:

• JOIN 

Código: SELECT a.nombre FROM alumno a LEFT JOIN alumcar ac ON a.codestu = ac.codestu WHERE ac.codestu IS NULL;

Crear una copia de la BD:

base de datos: