Proyecto de base de datos. Postgree (página 2)

DESCRIPCION DE TABLA

Nombre de la tabla: ________DETALLE______________

Llave Primaria: _{nventa, cod_producto} ____________

Llave secundaria: ________________________________

Llave Foránea: __ nventa, cod_producto_____________

Diccionario de datos

Nombre de archivo: ficha Fecha creación:12/11/11 Descripcion: Base de datos que contendrá la ficha de matrícula de los alumnos del Instituto

Relaciones: Campos Claves:

Ficha- Cursos rne- nombre, apellido, encargado

Nombre de archivo: Curso Fecha creación:12/11/11 Descripcion: Base de datos que contendrá la información de los cursos del Instituto.

Relaciones: Campos Claves:

Cursos – Ficha todos.

Nombre de archivo: Maestros Fecha creación:12/11/11 Descripción: Base de datos que contendrá la información de los Maestros del Instituto.

Relaciones: Campos Claves:

X cod-maestro-nombre-apellido.

Nombre de archivo: Materias Fecha creación:12/11/11 Descripción: Base de datos que contendrá la información de las materias de las carreras

Relaciones: Campos Claves:

Materias-curso-maestro cod_materia-materia

Nombre de archivo: Notas Fecha creación:12/11/11 Descripción: Base de datos que contendrá la información de las Notas de los alumnos.

Relaciones: Campos Claves:

Notas-Materias-curso-maestro rne, cod_curso, cod_materia, anio

Nombre de archivo:Conceptopago Fecha creación:12/11/11 Descripción: Base de datos que contendrá la información del pago que se efectua.

Relaciones: Campos Claves:

Nombre de archivo:Conceptopago Fecha creación:12/11/11 Descripción: Base de datos que contendrá la información del pago que se efectua.

Consultas SQL

• 1- Contar el número total de alumnos.

Select count (rne)

From ficha

• 2- Listado de Alumnos por un curso X.

Select ficha.rne, ficha.nombre, ficha.apellidos, cursos.curso, cursos.modalidad, cursos.seccion

From ficha, cursos

Where ficha.cod_curso=cursos.cod_curso and ficha.cod_curso='1CC1'

• 3- Cuadro de honor del parcial3 (notas mayores de 91)

Select notas.rne, ficha.nombre, ficha.apellidos, notas.cod_curso, avg (notas.parcial3)

From ficha, notas

Where notas.rne=ficha.rne and parcial3>91

Group by notas.rne,ficha.nombre,ficha.apellidos,notas.cod_curso

• 4- Aplazados por materia

Select notas.rne, ficha.nombre, ficha.apellidos, materias.materia, notas.promedio

From notas, ficha, materias

Where notas.rne = ficha.rne and notas.promedio <60 and notas.cod_materia=materias.cod_materia

Order by materias.materia

• 5- Aplazados por curso

Select notas.rne, ficha.nombre, ficha.apellidos, cursos.curso, cursos.modalidad, cursos.seccion, notas.promedio

From notas, ficha, cursos

Where notas.rne = ficha.rne and notas.promedio <60 and notas.cod_curso=cursos.cod_curso

Order by cursos.modalidad, cursos.curso, cursos.seccion

• 6- Mejores 10 promedios de todo el sistema para asignarles becas

Select notas.rne, ficha.nombre, ficha.apellidos, avg (notas.promedio)

From notas, ficha

Where notas.rne=ficha.rne

Group by notas.rne, ficha.nombre, ficha.apellidos

Order by avg (notas.promedio) desc limit 10 offset 0

• 7- Materias por maestros

Select materias.cod_materia, materias.materia, maestros.nombre, maestros.apellidos

From materias, maestros

Where materias. Cod_maestro=maestros. Cod_maestro

Order by maestros. Nombre, materias.materia

• 8- Contar cuantas materias por maestros.

Select maestros.nombre, maestros.apellidos, count (materias.materia)

From materias, maestros

Where materias. Cod_maestro=maestros. Cod_maestro

Group by maestros.nombre, maestros.apellidos

Order by maestros. nombre

• 9- Calcular el promedio anual de todas las notas.

Select avg (promedio)

From notas

• 10- Calcular el promedio por sexo

Select avg (notas.promedio), ficha.sexo

From notas, ficha

Where notas.rne=ficha.rne

Group by ficha.sexo

• 11- Alumnos a los que no se les ha asignado ninguna nota.

Select ficha.rne, ficha.nombre from ficha left join notas on ficha.rne=notas.rne

Where (notas.rne) is null

• 12- Maestros a los que no se les ha asignado ninguna materia.

Select maestros.cod_maestro, maestros.nombre, maestros.apellidos, materias.cod_maestro from maestros left join materias on maestros.cod_maestro=materias.cod_maestro

Where (materias.cod_maestro) is null

Order by nombre

• 13- Ver el historial de un alumno X

select notas.rne, ficha.nombre, ficha.apellidos, notas.cod_materia, materias.materia, notas.cod_curso, cursos.curso, cursos.modalidad, notas.anio, notas.parcial1, notas.parcial2, notas.parcial3, notas.parcial4, round(promedio)

From notas, ficha, materias, cursos

Where notas.rne=ficha.rne and notas.cod_curso=cursos.cod_curso and notas.cod_materia=materias.cod_materia and notas.rne = '0503-1999-00562'

• 14- Ver la nota más alta del año.

Select Max (promedio)

From notas

• 15- Ver la nota más baja del año.

Select Min (promedio)

From notas

• 16- Alumnos que pierden derecho a Primera Recuperación (promedio < 40)

Select * from notas where promedio <40

• 17- Materias por curso

Select materias.cod_materia, materias.materia, cursos.curso, cursos.modalidad, cursos.seccion

From materias, cursos

Where materias.cod_curso=cursos.cod_curso

Order by curso, modalidad, seccion, materia

• 18- Cuantos alumnos van a Recuperación

Select count (promedio)

From notas

Where promedio<60

• 19- Cuantos alumnos van a Recuperación por Materia.

Select notas.cod_materia, materias.materia, cursos.curso, cursos.modalidad, cursos.seccion, count (promedio)

From notas, materias, cursos

Where notas.cod_materia=materias.cod_materia and notas.cod_curso=cursos.cod_curso and promedio <60

Group by notas.cod_materia, materias.materia, cursos.curso, cursos.modalidad, cursos.seccion

Order by curso, modalidad, seccion

• 20- Alumnos Repitentes

Select * from ficha where repite = 'SI'

• 21- Sumar cuanto ha cobrado cada empleado.

Select cobros.cod_empleado, empleados.nombre, sum (conceptopago.precio)

From empleados, conceptopago, cobros

Where cobros. Cod_concepto=conceptopago.cod_concepto and empleados.cod_empleado=cobros.cod_empleado

Group by cobros.cod_empleado, empleados.nombre

• 22- Sumar Todos los Cobros realizados.

Select sum (conceptopago.precio)

From conceptopago, cobros

Where cobros. Cod_concepto=conceptopago.cod_concepto

• 23- Sumar los Cobros por Concepto.

Select cobros.cod_concepto, sum (precio)

From cobros, conceptopago

Where cobros. Cod_concepto=conceptopago.cod_concepto

Group by cobros.cod_concepto, conceptopago.cod_concepto

• 24- Contar cuantos cobros se han hecho.

Select count (numcobro)

From cobros

• 25- Ver el Estado de cuenta de un alumno.

Select cobros.rne, ficha.nombre, ficha.apellidos, cobros. Cod_concepto, conceptopago. Concepto, cobros.mes

From cobros, ficha, conceptopago

Where cobros.rne=ficha.rne and cobros. Cod_concepto=conceptopago.cod_concepto and cobros.rne='0503-1978-01415'

• 26- Contar cuantos empleados hay.

Select count (cod_empleado)

From empleados

• 27- Alumnos que no han realizado ningún pago.

Select ficha.rne, ficha.nombre, ficha.apellidos

From ficha left join cobros on ficha.rne = cobros.rne

Where (cobros.rne) is null

• 28- Contar cuantos productos hay en la tienda.

Select sum (existencia) from productos

• 29- Calcular la Inversión Total hecha en la tienda escolar.

Select sum (preciocosto)

From productos

• 30- Ver los artículos por proveedor.

Select productos.cod_producto, productos.producto, productos.preciocosto, proveedores.nombre

From productos, proveedores

Where productos.cod_proveedor=proveedores.codproveedor

Order by proveedores.nombre

• 31- Cuánto Dinero se le ha pagado a cada proveedor.

Select productos.cod_proveedor, proveedores.nombre, sum (preciocosto)

From productos, proveedores

Where productos.cod_proveedor=proveedores.codproveedor

Group by productos.cod_proveedor, proveedores.nombre

• 32- Cuantos proveedores tenemos

Select count (codproveedor)

From proveedores

• 33- Total Vendido

Select sum (total)

From detalle

• 34- Ventas por factura

Select detalle. nventa, detalle.cod_producto, productos.producto, detalle.total

From detalle, productos

Where detalle. Cod_producto=productos. Cod_producto

Order by nventa

• 35- Total ventas por factura

Select nventa, sum (total)

From detalle

Group by nventa

Order by nventa

• 36- Total impuesto cobrado.

Select sum (impuesto) from detalle

• 37- Total impuesto cobrado por factura.

Select nventa, round (sum (impuesto))

From detalle

Group by nventa

Order by nventa

• 38- Productos que no han tenido movimiento.

Select productos.cod_producto, productos.producto from productos left join detalle on productos. Cod_producto=detalle. cod_producto

Where (detalle. cod_producto) is null

Order by producto

• 39- Ver la factura más alta

Select sum (total), nventa

From detalle

Group by nventa

Order by sum (total) desc limit 1 offset 0

• 40- Ver una factura x completa.

Select empleados.nombre, ventas. Nventa, detalle.cod_producto, productos.producto, detalle.precio_venta, detalle.cantidad, detalle.subtotal, detalle.impuesto, detalle.total

From empleados, ventas, detalle, productos

Where ventas.cod_empleado=empleados.cod_empleado and detalle. cod_producto = productos. Cod_producto and ventas. Nventa=detalle. Nventa

And ventas. Nventa='1'

Procedimientos almacenados

En nuestro proyecto utilizamos los procedimientos más que todo para asuntos de cálculo en las tablas que tienen que ver con valores numéricos.

Dichos procedimientos los programamos junto a un trigger para que se levanten automáticamente al efectuar una acción ya sea la de insertar un registro o actualizar un registro.

Para desarrollarlos hicimos una investigación en internet a fin de empaparnos un poco más sobre el uso de los mismos. Adjuntamos el manual que nos permitió desarrollar nuestros procedimientos.

Procedimiento # 1

Nuestra primera función es la más simple que se puede definir y lo único que hará será devolver el valor NULL:

CREATE OR REPLACE FUNCTION proteger_datos () RETURNS TRIGGER AS $proteger_datos$

DECLARE

BEGIN

—

— Esta función es usada para proteger datos en una tabla

— No se permitirá el borrado de filas si la usamos

— en un disparador de tipo BEFORE / row-level

—

RETURN NULL;

END;

$proteger_datos$ LANGUAGE plpgsql;

Procedimiento # 2

Ahora vamos a definir una nueva función un poco más complicada y un nuevo disparador en nuestra tabla productos, el procedimiento como podemos ver únicamente nos permite calcular el precio de venta de un producto. La función se llama rellenar_datos ( ) y el trigger tiene el mismo nombre, como podemos ver en el trigger le damos la orden que se debe ejecutar cuando insertemos o modifiquemos una línea.

CREATE OR REPLACE FUNCTION rellenar_datos () RETURNS TRIGGER AS $rellenar_datos$

DECLARE

BEGIN

NEW.precioventa:= (NEW.preciocosto+NEW.utilidad);

RETURN NEW;

END;

$rellenar_datos$ LANGUAGE plpgsql;

CREATE TRIGGER rellenar_datos BEFORE INSERT OR UPDATE

ON numeros FOR EACH ROW

EXECUTE PROCEDURE rellenar_datos ();

Procedimiento # 3

Nuestro tercer procedimiento tiene como función encontrar los promedios de los alumnos una vez que les hemos introducido los 4 parciales, ejecutamos un trigger con el mismo nombre del procedimiento; rellenar_notas

CREATE OR REPLACE FUNCTION rellenar_notas () RETURNS TRIGGER AS $rellenar_notas$

DECLARE

BEGIN

NEW.promedio :=( round (NEW.parcial1+NEW.parcial2+NEW.parcial3+NEW.parcial4)/4);

RETURN NEW;

END;

$rellenar_notas$ LANGUAGE plpgsql;

CREATE TRIGGER rellenar_notas BEFORE INSERT OR UPDATE

ON numeros FOR EACH ROW

EXECUTE PROCEDURE rellenar_notas ();

Procedimiento # 4

Nuestro cuarto procedimiento tiene como objetivo calcular los valores de las facturas, específicamente sobre la tabla detalle. Lo acompañamos con un trigger con el mismo nombre del procedimiento; rellenar_detalle () para que funcione debemos llenarle el precio_venta y la cantidad.

CREATE OR REPLACE FUNCTION rellenar_detalle () RETURNS TRIGGER AS $rellenar_detalle$

DECLARE

BEGIN

NEW.subtotal:= (NEW.precio_venta*New.cantidad);

New.impuesto:=(New.subtotal*0.12);

New.total:= (NEW.subtotal New.impuesto);

RETURN NEW;

END;

$rellenar_detalle$ LANGUAGE plpgsql;

CREATE TRIGGER rellenar_detalle BEFORE INSERT OR UPDATE

ON numeros FOR EACH ROW

EXECUTE PROCEDURE rellenar_detalle ();

Procedimiento # 5

Este procedimiento permite que al ingresar datos en la tabla ficha, solo debamos escribir la F o M y sea el procedimiento quien escriba la palabra completa Femenino o Masculino.

CREATE OR REPLACE FUNCTION rellenar_genero () RETURNS TRIGGER AS $rellenar_genero$

DECLARE

BEGIN

if New.sexo = 'F' then

New.sexo:='Femenino'

elsif;

if New.sexo = 'M' then

New.sexo:='Masculino'

endif;

end if;

End if;

RETURN NEW;

END;

$rellenar_genero$ LANGUAGE plpgsql;

CREATE TRIGGER rellenar_genero BEFORE INSERT OR UPDATE

ON ficha FOR EACH ROW

EXECUTE PROCEDURE rellenar_genero ();

Desarrollo en SQL SERVER 2005

En vista de que nuestro sistema deseamos subirlo a internet de manera que funcione en ambiente web, elegimos SQLSERVER 2005 ¿Por qué SQLSERVER?

Lo elegimos ya que la plataforma que vamos a elegir para programar el sistema es visual studio 2005 y de alguna forma pues funcionan como mejor estos dos programas juntos. Aparte que ya hemos tenido un poco de experiencia en SQLSERVER.

A continuación se presenta el diagrama relacional de sqlserver:

Adjunto enviamos la base de datos creada en SQLSERVER 2005.

Autor:

Néstor Domingo Vargas Galindo

Luis Alonso Ríos Galindo

PROYECTO DE BASE DE DATOS