DIAGRAMA DE LA BASE DE DATOS
ESTE ES NUESTRO DIAGRAMA CON SUS RELACIONES INCLUIDAS, EN LA CUAL INCLUYE UNA TABLA PARA LOS PAGOS LLAMADA "CUENTACTE" LA CUAL SE VA INGRESAR LAS 9 CUOTAS AUTOMATICAMENTE ATRAVEZ DE UN TRIGGER QUE SE ACTIVARA CUANDO SE INGRESA UN REGISTRO A LA TABLA "MATRICULA".
Esta base de datos ha sido creada en el motor MYSQL por ser gratuito y atravez de una conexion ODBC.
Aqui les voy a dejar el Query de la Base De Datos:
CREATE DATABASE IF NOT EXISTS `db_colegio` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `db_colegio`;
-- MySQL dump 10.13 Distrib 5.5.16, for Win32 (x86)
--
-- Host: localhost Database: db_colegio
-- ------------------------------------------------------
-- Server version 5.5.28
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `administrador`
--
DROP TABLE IF EXISTS `administrador`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `administrador` (
`idadministrador` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(45) NOT NULL,
`ape_pat` varchar(45) NOT NULL,
`telefono` varchar(45) NOT NULL,
`correo` varchar(45) NOT NULL,
`cargo` varchar(45) NOT NULL,
`direccion` varchar(45) NOT NULL,
`id_distrito` int(11) NOT NULL,
`user` varchar(45) NOT NULL,
PRIMARY KEY (`idadministrador`),
KEY `fk_distadmin_idx` (`id_distrito`),
KEY `fk_useradmin_idx` (`user`),
CONSTRAINT `fk_distadmin` FOREIGN KEY (`id_distrito`) REFERENCES `distrito` (`iddistrito`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_useradmin` FOREIGN KEY (`user`) REFERENCES `usuario` (`user`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `administrador`
--
LOCK TABLES `administrador` WRITE;
/*!40000 ALTER TABLE `administrador` DISABLE KEYS */;
INSERT INTO `administrador` VALUES (1,'fresa','linda','bonita','fresita@hotmail.com','secretaria','av quincena 412',5,'admin');
/*!40000 ALTER TABLE `administrador` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `alumno`
--
DROP TABLE IF EXISTS `alumno`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `alumno` (
`idalumno` int(11) NOT NULL,
`nombre` varchar(45) NOT NULL,
`ape_pat` varchar(45) NOT NULL,
`ape_mat` varchar(45) NOT NULL,
`correo` varchar(45) NOT NULL,
`fec_nac` varchar(45) NOT NULL,
`id_apoderado` int(11) NOT NULL,
`sexo` bit(1) NOT NULL,
PRIMARY KEY (`idalumno`),
KEY `apoderado_idx` (`id_apoderado`),
CONSTRAINT `fk_apoderado` FOREIGN KEY (`id_apoderado`) REFERENCES `apoderado` (`dni`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `alumno`
--
LOCK TABLES `alumno` WRITE;
/*!40000 ALTER TABLE `alumno` DISABLE KEYS */;
INSERT INTO `alumno` VALUES (11111,'franz','portocarrero','la torre','franz@hotmail.com','28/8/1991',4856975,'\0'),(11112,'diego','garcia','perez','diego@hotmail.com','13/11/2012',458759,'\0'),(11113,'juana','la cubana','maria','juana@hotmail.com','27/11/2012',5895689,' '),(11114,'carlos','chipana','carepana','carlos@hotmail.com','20/11/2012',5895689,'\0'),(11115,'pedro','pedrin','alofonsin','pedro@hotmail.com','5/11/2012',47487942,'\0'),(22221,'dieguin','pelucas','cochos','dieguin@hotmail.com','11/11/2012',4785898,'\0'),(22222,'miriam','fernandez','diaz','miriam@hotmail.com','13/11/2012',47487942,' ');
/*!40000 ALTER TABLE `alumno` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `apoderado`
--
DROP TABLE IF EXISTS `apoderado`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `apoderado` (
`dni` int(11) NOT NULL,
`nombre` varchar(45) NOT NULL,
`ape_pat` varchar(45) NOT NULL,
`correo` varchar(45) NOT NULL,
`telefono` varchar(45) NOT NULL,
`celular` varchar(45) DEFAULT NULL,
`direccion` varchar(45) NOT NULL,
`id_distrito` int(11) NOT NULL,
`id_relacion` int(11) NOT NULL,
PRIMARY KEY (`dni`),
KEY `fk_relacion_idx` (`id_relacion`),
KEY `fk_distrito_idx` (`id_distrito`),
CONSTRAINT `fk_distrito` FOREIGN KEY (`id_distrito`) REFERENCES `distrito` (`iddistrito`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_relacion` FOREIGN KEY (`id_relacion`) REFERENCES `relacion` (`idrelacion`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `apoderado`
--
LOCK TABLES `apoderado` WRITE;
/*!40000 ALTER TABLE `apoderado` DISABLE KEYS */;
INSERT INTO `apoderado` VALUES (124,'carlos','magno','magno@hotmail.com','2563594','985145263','av aguarico 625',4,1),(458759,'juan','garcia','garcia@hotmail.com','3569842','987548956','av matellini 875',6,1),(4785895,'NOMBRE','APELLIDO PATERNO','CORREO','TELEFONO','CELULAR','DIRECCION',3,2),(4785898,'luis','pelucas','luis@hotmail.com','2563895','','av pollos 567',6,1),(4856975,'juan','portocarrero','juan@hotmail.com','2586398','','av naranja 658',2,1),(5895689,'maria','muñoz','maria@hotmail.com','3893135','','av napoles 256',4,2),(47487942,'francisco','portocarrero','ibero@hotmail.com','2528942','','av matelinni 752',5,1);
/*!40000 ALTER TABLE `apoderado` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `aula`
--
DROP TABLE IF EXISTS `aula`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `aula` (
`idaula` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(45) DEFAULT NULL,
PRIMARY KEY (`idaula`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `aula`
--
LOCK TABLES `aula` WRITE;
/*!40000 ALTER TABLE `aula` DISABLE KEYS */;
INSERT INTO `aula` VALUES (1,'AIII'),(2,'AI'),(3,'BI'),(4,'AI'),(5,'BII');
/*!40000 ALTER TABLE `aula` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `cuentacte`
--
DROP TABLE IF EXISTS `cuentacte`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cuentacte` (
`idcuenta` int(11) NOT NULL AUTO_INCREMENT,
`monto` decimal(10,2) NOT NULL,
`fecha_vencimiento` int(11) NOT NULL,
`fecha_pago` varchar(45) DEFAULT NULL,
`id_estado` int(11) NOT NULL,
`id_matricula` int(11) NOT NULL,
PRIMARY KEY (`idcuenta`),
KEY `fk_matricula_idx` (`id_matricula`),
KEY `fk_estadocuenta_idx` (`id_estado`),
CONSTRAINT `fk_estadocuenta` FOREIGN KEY (`id_estado`) REFERENCES `estado_cta` (`idestado_cta`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_matricula` FOREIGN KEY (`id_matricula`) REFERENCES `matricula` (`idmatricula`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=188 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `cuentacte`
--
LOCK TABLES `cuentacte` WRITE;
/*!40000 ALTER TABLE `cuentacte` DISABLE KEYS */;
INSERT INTO `cuentacte` VALUES (122,200.00,3,'5/11/2012',2,13),(123,150.00,3,'5/11/2012',2,13),(124,150.00,4,'',1,13),(125,150.00,5,'5/11/2012',2,13),(126,150.00,6,'5/11/2012',2,13),(127,150.00,7,'5/11/2012',2,13),(128,150.00,8,'5/11/2012',2,13),(129,150.00,9,'5/11/2012',2,13),(130,150.00,10,'5/11/2012',2,13),(131,150.00,11,'',1,13),(132,150.00,12,'',1,13),(133,200.00,3,'5/11/2012',2,15),(134,150.00,3,'5/11/2012',2,15),(135,150.00,4,'',1,15),(136,150.00,5,'5/11/2012',2,15),(137,150.00,6,'5/11/2012',2,15),(138,150.00,7,'5/11/2012',2,15),(139,150.00,8,'5/11/2012',2,15),(140,150.00,9,'5/11/2012',2,15),(141,150.00,10,'5/11/2012',2,15),(142,150.00,11,'5/11/2012',2,15),(143,150.00,12,'',1,15),(144,200.00,3,'',1,18),(145,150.00,3,'',1,18),(146,150.00,4,'',1,18),(147,150.00,5,'',1,18),(148,150.00,6,'',1,18),(149,150.00,7,'',1,18),(150,150.00,8,'',1,18),(151,150.00,9,'',1,18),(152,150.00,10,'',1,18),(153,150.00,11,'',1,18),(154,150.00,12,'',1,18),(155,200.00,3,'',1,19),(156,150.00,3,'',1,19),(157,150.00,4,'',1,19),(158,150.00,5,'',1,19),(159,150.00,6,'',1,19),(160,150.00,7,'',1,19),(161,150.00,8,'',1,19),(162,150.00,9,'',1,19),(163,150.00,10,'',1,19),(164,150.00,11,'',1,19),(165,150.00,12,'',1,19),(166,200.00,3,'5/11/2012',2,20),(167,150.00,3,'5/11/2012',2,20),(168,150.00,4,'5/11/2012',2,20),(169,150.00,5,'',1,20),(170,150.00,6,'5/11/2012',2,20),(171,150.00,7,'5/11/2012',2,20),(172,150.00,8,'5/11/2012',2,20),(173,150.00,9,'5/11/2012',2,20),(174,150.00,10,'5/11/2012',2,20),(175,150.00,11,'5/11/2012',2,20),(176,150.00,12,'',1,20),(177,200.00,3,'5/11/2012',2,21),(178,150.00,3,'5/11/2012',2,21),(179,150.00,4,'',1,21),(180,150.00,5,'5/11/2012',2,21),(181,150.00,6,'5/11/2012',2,21),(182,150.00,7,'5/11/2012',2,21),(183,150.00,8,'5/11/2012',2,21),(184,150.00,9,'5/11/2012',2,21),(185,150.00,10,'5/11/2012',2,21),(186,150.00,11,'5/10/2012',2,21),(187,150.00,12,'5/11/2012',2,21);
/*!40000 ALTER TABLE `cuentacte` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `curso`
--
DROP TABLE IF EXISTS `curso`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `curso` (
`idcurso` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(45) NOT NULL,
`id_grado` int(11) NOT NULL,
`id_profesor` int(11) NOT NULL,
PRIMARY KEY (`idcurso`),
KEY `fk_grado_idx` (`id_grado`),
KEY `fk_profe_idx` (`id_profesor`),
KEY `fk_profesor_idx` (`id_profesor`),
CONSTRAINT `fk_grado` FOREIGN KEY (`id_grado`) REFERENCES `grado` (`idgrado`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_profesor` FOREIGN KEY (`id_profesor`) REFERENCES `profesor` (`dni`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `curso`
--
LOCK TABLES `curso` WRITE;
/*!40000 ALTER TABLE `curso` DISABLE KEYS */;
INSERT INTO `curso` VALUES (2,'aritmetica i',1,457),(3,'lenguaje',1,356),(4,'religion',1,85421),(5,'algebra i',1,123),(6,'desarrollo personal i',1,457),(7,'aritmetica ii',2,234),(8,'comunicacion',2,356),(9,'creencias religiosas',2,75478),(10,'algebra ii',2,123),(11,'desarrollo personal ii',2,75478),(12,'aritmetica iii',3,123),(13,'dinamicas comunicativas',3,356),(14,'trigonometria i / geometria i',3,5689),(15,'desarrollo sexual',3,75478),(16,'historia del peru',3,85421),(17,'fisica',4,234),(18,'quimica',4,234),(19,'trigonometria ii / geometria ii',4,234),(20,'historia humana',4,85421),(21,'geografia',4,85421),(22,'fisica ii',5,85421),(23,'quimica ii',5,5689),(24,'anatomia / botanica',5,6575),(25,'trigonometria iii / geometria ii',5,5689),(26,'tutoria vocacional',5,75478);
/*!40000 ALTER TABLE `curso` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `distrito`
--
DROP TABLE IF EXISTS `distrito`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `distrito` (
`iddistrito` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(45) NOT NULL,
PRIMARY KEY (`iddistrito`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `distrito`
--
LOCK TABLES `distrito` WRITE;
/*!40000 ALTER TABLE `distrito` DISABLE KEYS */;
INSERT INTO `distrito` VALUES (1,'Cercado de Lima'),(2,'Ate'),(3,'Barranco'),(4,'Breña'),(5,'Comas'),(6,'Chorrillos'),(7,'El Agustino'),(8,'Jesús María'),(9,'La Molina'),(10,'La Victoria'),(11,'Lince'),(12,'Magdalena del Mar'),(13,'Miraflores'),(14,'Pueblo Libre'),(15,'Puente Piedra'),(16,'Rimac'),(17,'San Isidro'),(18,'Independencia'),(19,'San Juan de Miraflores'),(20,'San Luis'),(21,'San Martin de Porres'),(22,'San Miguel'),(23,'Santiago de Surco'),(24,'Surquillo'),(25,'Villa María del Triunfo'),(26,'San Juan de Lurigancho'),(27,'Santa Rosa'),(28,'Los Olivos'),(29,'San Borja'),(30,'Villa El Savador'),(31,'Santa Anita');
/*!40000 ALTER TABLE `distrito` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `estado_cta`
--
DROP TABLE IF EXISTS `estado_cta`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `estado_cta` (
`idestado_cta` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(45) NOT NULL,
PRIMARY KEY (`idestado_cta`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `estado_cta`
--
LOCK TABLES `estado_cta` WRITE;
/*!40000 ALTER TABLE `estado_cta` DISABLE KEYS */;
INSERT INTO `estado_cta` VALUES (1,'pendiente'),(2,'cancelado');
/*!40000 ALTER TABLE `estado_cta` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `estado_user`
--
DROP TABLE IF EXISTS `estado_user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `estado_user` (
`idestado_user` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(45) NOT NULL,
PRIMARY KEY (`idestado_user`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `estado_user`
--
LOCK TABLES `estado_user` WRITE;
/*!40000 ALTER TABLE `estado_user` DISABLE KEYS */;
INSERT INTO `estado_user` VALUES (1,'activo'),(2,'inactivo');
/*!40000 ALTER TABLE `estado_user` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `grado`
--
DROP TABLE IF EXISTS `grado`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `grado` (
`idgrado` int(11) NOT NULL AUTO_INCREMENT,
`id_aula` int(11) NOT NULL,
PRIMARY KEY (`idgrado`),
KEY `fk_aula_idx` (`id_aula`),
CONSTRAINT `fk_aula` FOREIGN KEY (`id_aula`) REFERENCES `aula` (`idaula`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `grado`
--
LOCK TABLES `grado` WRITE;
/*!40000 ALTER TABLE `grado` DISABLE KEYS */;
INSERT INTO `grado` VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
/*!40000 ALTER TABLE `grado` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `matricula`
--
DROP TABLE IF EXISTS `matricula`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `matricula` (
`idmatricula` int(11) NOT NULL AUTO_INCREMENT,
`id_alumno` int(11) NOT NULL,
`id_grado` int(11) NOT NULL,
`periodo` int(11) NOT NULL,
PRIMARY KEY (`idmatricula`),
KEY `fk_alumno_idx` (`id_alumno`),
KEY `fk_gradomat_idx` (`id_grado`),
CONSTRAINT `fk_alumno` FOREIGN KEY (`id_alumno`) REFERENCES `alumno` (`idalumno`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_gradomat` FOREIGN KEY (`id_grado`) REFERENCES `grado` (`idgrado`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `matricula`
--
LOCK TABLES `matricula` WRITE;
/*!40000 ALTER TABLE `matricula` DISABLE KEYS */;
INSERT INTO `matricula` VALUES (13,11111,1,2012),(15,11112,1,2012),(18,11113,1,2012),(19,11114,1,2012),(20,22221,2,2012),(21,22222,2,2012);
/*!40000 ALTER TABLE `matricula` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `db_colegio`.`crea_cuenta`
AFTER INSERT ON `db_colegio`.`matricula`
FOR EACH ROW
begin
insert into cuentacte(monto,fecha_vencimiento,fecha_pago,id_estado,id_matricula) values(200,3,'',1,new.idmatricula);
insert into cuentacte(monto,fecha_vencimiento,fecha_pago,id_estado,id_matricula) values(150,3,'',1,new.idmatricula);
insert into cuentacte(monto,fecha_vencimiento,fecha_pago,id_estado,id_matricula) values(150,4,'',1,new.idmatricula);
insert into cuentacte(monto,fecha_vencimiento,fecha_pago,id_estado,id_matricula) values(150,5,'',1,new.idmatricula);
insert into cuentacte(monto,fecha_vencimiento,fecha_pago,id_estado,id_matricula) values(150,6,'',1,new.idmatricula);
insert into cuentacte(monto,fecha_vencimiento,fecha_pago,id_estado,id_matricula) values(150,7,'',1,new.idmatricula);
insert into cuentacte(monto,fecha_vencimiento,fecha_pago,id_estado,id_matricula) values(150,8,'',1,new.idmatricula);
insert into cuentacte(monto,fecha_vencimiento,fecha_pago,id_estado,id_matricula) values(150,9,'',1,new.idmatricula);
insert into cuentacte(monto,fecha_vencimiento,fecha_pago,id_estado,id_matricula) values(150,10,'',1,new.idmatricula);
insert into cuentacte(monto,fecha_vencimiento,fecha_pago,id_estado,id_matricula) values(150,11,'',1,new.idmatricula);
insert into cuentacte(monto,fecha_vencimiento,fecha_pago,id_estado,id_matricula) values(150,12,'',1,new.idmatricula);
end */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `notas`
--
DROP TABLE IF EXISTS `notas`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `notas` (
`idnotas` int(11) NOT NULL AUTO_INCREMENT,
`unidad` int(11) NOT NULL,
`practicas` decimal(10,2) NOT NULL,
`trabajos` decimal(10,2) NOT NULL,
`actitud` decimal(10,2) NOT NULL,
`examen` decimal(10,2) NOT NULL,
`promedio` decimal(10,2) DEFAULT NULL,
`periodo` int(11) NOT NULL,
`id_alumno` int(11) NOT NULL,
`id_curso` int(11) NOT NULL,
PRIMARY KEY (`idnotas`),
KEY `fk_curso_idx` (`id_curso`),
KEY `fk_alumnonotas_idx` (`id_alumno`),
KEY `fk_unidad_idx` (`unidad`),
CONSTRAINT `fk_alumnonotas` FOREIGN KEY (`id_alumno`) REFERENCES `alumno` (`idalumno`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_curso` FOREIGN KEY (`id_curso`) REFERENCES `curso` (`idcurso`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_unidad` FOREIGN KEY (`unidad`) REFERENCES `unidad` (`idunidad`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `notas`
--
LOCK TABLES `notas` WRITE;
/*!40000 ALTER TABLE `notas` DISABLE KEYS */;
INSERT INTO `notas` VALUES (19,3,15.00,19.00,14.00,18.00,16.50,2012,11111,2),(20,1,15.00,12.00,16.00,20.00,15.75,2012,22221,7),(21,1,10.00,12.00,15.00,20.00,14.25,2012,22222,7),(22,2,15.00,15.00,10.00,12.00,13.00,2012,22221,7),(23,2,13.00,13.00,20.00,14.00,15.00,2012,22222,7),(24,3,15.00,19.00,12.00,18.00,16.00,2012,22221,7),(25,3,15.00,12.00,14.00,12.00,13.25,2012,22222,7),(26,3,10.00,9.00,12.00,20.00,12.75,2012,11112,2);
/*!40000 ALTER TABLE `notas` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `profesor`
--
DROP TABLE IF EXISTS `profesor`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `profesor` (
`dni` int(11) NOT NULL,
`nombre` varchar(45) NOT NULL,
`ape_pat` varchar(45) NOT NULL,
`ape_mat` varchar(45) NOT NULL,
`correo` varchar(45) NOT NULL,
`telefono` varchar(45) NOT NULL,
`direccion` varchar(45) NOT NULL,
`celular` varchar(45) NOT NULL,
`profesion` varchar(45) NOT NULL,
`id_distrito` int(11) NOT NULL,
`user` varchar(45) NOT NULL,
`sexo` bit(1) NOT NULL,
PRIMARY KEY (`dni`),
KEY `fk_distrito_idx` (`id_distrito`),
KEY `fk_userprofe_idx` (`user`),
CONSTRAINT `fk_distritoprofe` FOREIGN KEY (`id_distrito`) REFERENCES `distrito` (`iddistrito`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_userprofe` FOREIGN KEY (`user`) REFERENCES `usuario` (`user`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `profesor`
--
LOCK TABLES `profesor` WRITE;
/*!40000 ALTER TABLE `profesor` DISABLE KEYS */;
INSERT INTO `profesor` VALUES (123,'luis','puerta','torres','luis@hotmail.com','4357823','av st lineas','985623698','ing sistema',2,'luis_pt','\0'),(234,'carlos','santi','milagros','carlos@hotmail.com','4586549','av anta 785','956741284','ing electrica',5,'carlos_sm','\0'),(356,'diego','pablo','perez','diego@hotmail.com','5623591','av lima 3456','986214589','literatura',12,'diego_pp','\0'),(457,'juan','garcia ','solo','juan@hotmail.com','4871239','av refugio 785','971248396','administracion',20,'juan_gs','\0'),(5689,'karen','guillen','fardo','karen@hotmail.com','3592641','av las flores 241','988412369','ing civil',8,'karen_gf','\0'),(6575,'ana','fariachi','diaz','ana@hotmail.com','2541236','av looder 784','951246831','nutrisionista',4,'ana_fd',' '),(75478,'juan','sobones','saldos','juans@hotmail.com','5254872','av villa cantares 125','988623547','sociologia',14,'juan_ss','\0'),(85421,'pedro','santos','garcho','pedro@hotmail.com','2356841','av tusilagos 456','984123657','historiador geografo',22,'pedro_sg','\0');
/*!40000 ALTER TABLE `profesor` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `relacion`
--
DROP TABLE IF EXISTS `relacion`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `relacion` (
`idrelacion` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(45) NOT NULL,
PRIMARY KEY (`idrelacion`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `relacion`
--
LOCK TABLES `relacion` WRITE;
/*!40000 ALTER TABLE `relacion` DISABLE KEYS */;
INSERT INTO `relacion` VALUES (1,'padre'),(2,'madre'),(3,'tio(a)'),(4,'tutor legal'),(5,'otros');
/*!40000 ALTER TABLE `relacion` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `unidad`
--
DROP TABLE IF EXISTS `unidad`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `unidad` (
`idunidad` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`idunidad`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `unidad`
--
LOCK TABLES `unidad` WRITE;
/*!40000 ALTER TABLE `unidad` DISABLE KEYS */;
INSERT INTO `unidad` VALUES (1),(2),(3),(4);
/*!40000 ALTER TABLE `unidad` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `usuario`
--
DROP TABLE IF EXISTS `usuario`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `usuario` (
`user` varchar(15) NOT NULL,
`pass` varchar(15) NOT NULL,
`id_estado` int(11) NOT NULL,
`privilegio` int(11) NOT NULL,
PRIMARY KEY (`user`),
KEY `fk_estadouser_idx` (`id_estado`),
CONSTRAINT `fk_estadouser` FOREIGN KEY (`id_estado`) REFERENCES `estado_user` (`idestado_user`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `usuario`
--
LOCK TABLES `usuario` WRITE;
/*!40000 ALTER TABLE `usuario` DISABLE KEYS */;
INSERT INTO `usuario` VALUES ('admin','admin',1,0),('ana_fd','12345',1,1),('carlos_sm','54321',1,1),('diego_pp','32147',1,1),('juan_gs','15963',1,1),('juan_ss','45632',1,1),('karen_gf','85213',1,1),('luis_pt','12345',1,1),('pedro_sg','12345',1,1);
/*!40000 ALTER TABLE `usuario` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2012-12-05 8:59:12
0 comentarios:
Publicar un comentario