DESCRIPCION DE TABLA
Nombre de la tabla:
________DETALLE______________
Llave Primaria: _{nventa,
cod_producto} ____________
Llave secundaria:
________________________________
Llave Foránea: __ nventa,
cod_producto_____________
Atributo | Tipo Dato | Tamaño | Observaciones | |||
nventa | Character | 8 | ||||
cod_producto | Character | 8 | ||||
precio_venta | Real | Defecto | ||||
cantidad | Integer | Defecto | ||||
subtotal | Real | Defecto | ||||
impuesto | Real | Defecto | ||||
total | real | defecto |
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
Campo | Tamaño | Tipo de | Descripción |
rne | 15 | Character | Clave única de registro |
nombre | 30 | Character Varying | Nombre del alumno |
apellidos | 60 | Character Varying | Apellido del alumno |
dirección | 70 | Character Varying | Direccion del alumno |
fecha_nacimiento | Date | Fecha que nació el | |
sexo | 10 | Character Varying | El sexo del estudiante |
padre | 70 | Character Varying | Padre del estudiante |
madre | 70 | Character Varying | Madre del estudiante |
encargado | 70 | Character Varying | Encargado del alumno |
cod_curso | 8 | Character | El código del curso que se |
Fecha_matricula | Date | La fecha de matricula. |
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.
Campo | Tamaño | Tipo de | Descripción |
cod_curso | 8 | Character | Llave principal único |
curso | 20 | Character Varying | El nombre del curso |
modalidad | 50 | Character Varying | Modalidad de la carrera |
sección | 3 | Character Varying | La sección |
jornada | 20 | Character Varying | Jornada de estudio |
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.
Campo | Tamaño | Tipo de | Descripción |
cod_maestro | 8 | Character | Código único de los |
nombre | 30 | Character Varying | Código de los |
apellido | 60 | Character Varying | Apellido del Maestro |
direccion | 70 | Character Varying | Dirección del |
teléfono | 9 | Character Varying | Teléfono del |
60 | Character Varying | Correo del maestro |
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
Campo | Tamaño | Tipo de | Descripción |
cod_materia | 8 | Character | Codigo único de las |
materia | 30 | Character Varying | Nombre de la materia |
Cod_curso | 8 | Character | El código del curso que |
Cod_maestro | 8 | Character | El código del maestro, llave |
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.
Campo | Tamaño | Tipo de | Descripción |
rne | 15 | Character | Llave principal del registro nacional |
cod_curso | 8 | Character | Llave principal del código del |
cod_materia | 8 | Character | Llave principal de la |
anio | defecto | Integer | Llave principal del |
parcial1 | defecto | Real | Nota del I parcial |
parcial2 | defecto | Real | Nota del II parcial |
parcial3 | defecto | Real | Nota del III parcial |
parcial4 | defecto | Real | Nota del IV parcial |
promedio | defecto | Real | Nota promedio |
recuperacion1 | defecto | Real | Primera |
recuperacion2 | defecto | Real | Segunda |
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.
Campo | Tamaño | Tipo de | Descripción |
cod_concepto | 8 | Character | Unico código del concepto de |
concepto | 50 | Character Varying | Concepto de pago |
precio | defecto | Real | El valor del pago |
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.
Campo | Tamaño | Tipo de | Descripción |
cod_empleado | 8 | Character | Codigo único del |
nombre | 30 | Character Varying | Nombre del empleado |
apellidos | 60 | Character Varying | Apellidos del empleados |
dirección | 70 | Character Varying | Dirección del |
teléfono | 9 | Character Varying | Teléfono del |
60 | Character Varying | Correo del empleado |
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
2011 | ||||||||
UNIVERSIDAD PEDAGOGICA NACIONAL |
Página anterior | Volver al principio del trabajo | Página siguiente |