text: ﻿create database secundaria;
  use secundaria;
  
  create table periodo(
      idp varchar(10) CHARACTER SET utf8 COLLATE utf8_spanish_ci not null,
      descper varchar(100) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      predet tinyint,
      primary key(idp)
  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  insert into periodo (idp, descper, predet) values 
  ('2017', 'AGOSTO 2017 - JULIO 2018',0),
  ('2018', 'AGOSTO 2018 - JULIO 2019',0),
  ('2019', 'AGOSTO 2019 - JULIO 2020',0),
  ('2020', 'AGOSTO 2020 - JULIO 2021',1);
  
  
  create table control(
      idc int not null primary key,
      descc varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      val int
  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  insert control (idc, descc, val) values
  (1,'Contador de numeros de control',0),
  (2,'Contador de numeros de horario',0),
  (3,'Matutino Control de trimestres',0),
  (4,'Vespertino Control de trimestres',0);
  
  create table trimestre(
      trim int not null primary key,
      dest varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci
  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  insert trimestre values 
  (1,'1er Trimestre'),
  (2,'2o Trimestre'),
  (3,'3er Trimestre'),
  (4,'Abierto'),
  (5,'Cerrado');
  
  create table taller(
      idt varchar(10) CHARACTER SET utf8 COLLATE utf8_spanish_ci not null,
      nomt varchar(100) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      primary key(idt)
  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  insert into taller (idt,nomt) values
  ('T307','CONTABILIDAD'),
  ('T310','DIBUJO INDUSTRIAL'),
  ('T315','ELECTRICIDAD'),
  ('T319','INDUSTRIA DEL VESTIDO'),
  ('T325','COMPUTACION'),
  ('T327','SECRETARIADO');
  
  create table rol(
   idr int not null primary key,
   tipo varchar(100) CHARACTER SET utf8 COLLATE utf8_spanish_ci
  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  insert into rol (idr, tipo) values 
  (1,'Administrador'),
  (2,'Académico'),
  (3,'Docente'),
  (4,'Prefecto');
  
  
  create table personal (
      rfcp varchar(13) CHARACTER SET utf8 COLLATE utf8_spanish_ci not null,
      nomp varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      turno varchar(1) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      passp varchar(40) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      primary key(rfcp)
  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  insert into personal (rfcp, nomp,  passp,turno) values 
  ('VIJV','VICENTE JIMENEZ VICTOR IVAN','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V'),
  ('P002','VICENTE JIMENEZ JOSE INES','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V'),
  ('P003','SANTIAGO CHIÑAS ARES IVAN','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V'),
  ('P001','CANDELARIO CARLOS','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V'),
  
  ('P004', 'LIC. ARAIZA HERRERA CINTHYA BERENICE', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V'),
  ('P005', 'LIC. SALMERON PEREZ P005 JOVANNY', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P006', 'M. EN T. A. GALLEGOS PEREZ P006', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P007', 'MC.ANA LILIA LOPEZ VALDEZ', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V'),
  ('P008', 'LIC. ANDRES MORALES P008', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V'),
  ('P009', 'MATL. MONTES RIVERA P009', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P010', 'ING. P023 P010 CHAVARRIA LOMELI', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P011', 'LIC. CASTILLO CASTILLO PABLO', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P012', 'QUIM. CANEDA GUZMAN ENRIQUE', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P014', 'M. ISC. P014 ALEJANDRO LOPEZ CASTAÑEDA', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P015', 'REYES AVENDA?O P015 GABRIELA', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P016', 'LIC. PASCUAL CORAZA AGUIRRE', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P017', 'ING. JOSE ANTONIO CONEJO PLATA', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P018', 'ING. SALVADOR CUEVAS BUSTOS', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P019', 'P032 ERNESTO P019 CARREON', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P020', 'P020 EMILIANO MENDOZA AVILA', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P021', 'ING. RONQUILLO ARVIZU REY P021', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P022', 'LIC. ANTERO REYES P022', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P023', 'M. EN T.A. P023 GABRIEL PANTOJA NERIA', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P024', 'ING. P024 MEJIA CRUZ', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P025', 'LIC. FLORES LOPEZ EVA MARIA', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P026', 'ING. P026 MUNOZ BUSTOS', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V'),
  ('P027', 'FIS. EDUARDO GARCIA AMADOR', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P028', 'LIC. GARCIA ORTIZ REBECA ESTHER', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P029', 'LIC. GARCIA WALLE ANA CELSA', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P030', 'ING. PRADEL MU?OZ P030', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P031', 'ING. GOMEZ AYALA IVAN ALEJANDRO', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V') ,
  ('P032', 'LIC. P032 MACIAS MACEDO', '7110EDA4D09E062AA5E4A390B0A572AC0D2C0220','V');
  
  create table permisos(
      rfcp varchar(13) CHARACTER SET utf8 COLLATE utf8_spanish_ci not null,
      idr int not null,
      status tinyint,
      primary key(rfcp, idr),
      FOREIGN KEY(rfcp) REFERENCES personal(rfcp) on update cascade on delete cascade,
      FOREIGN KEY(idr) REFERENCES rol(idr) on update cascade on delete cascade
  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  
  INSERT into permisos (rfcp, idr, status) values 
  ('VIJV',1,1),
  ('P001',3,1),
  ('P002',4,1),
  ('P003',2,1),
  
  ('P004', 3,1),
  ('P005', 3,1),
  ('P006', 3,1),
  ('P007', 3,1),
  ('P008', 3,1),
  ('P009', 3,1),
  ('P010', 3,1),
  ('P011', 3,1),
  ('P012', 3,1),
  ('P014', 3,1),
  ('P015', 3,1),
  ('P016', 3,1),
  ('P017', 3,1),
  ('P018', 3,1),
  ('P019', 3,1),
  ('P020', 3,1),
  ('P021', 3,1),
  ('P022', 3,1),
  ('P023', 3,1),
  ('P024', 3,1),
  ('P025', 3,1),
  ('P026', 3,1),
  ('P027', 3,1),
  ('P028', 3,1),
  ('P029', 3,1),
  ('P030', 3,1),
  ('P031', 3,1),
  ('P032', 3,1);
  
  create table materia(
      idmat varchar(10) CHARACTER SET utf8 COLLATE utf8_spanish_ci not null,
      nommat varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      grado int,
      status tinyint,
      primary key (idmat)
  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  insert into materia (idmat, nommat, grado, status) values
   ('A001','ESPAÑOL',1,1),
   ('A002','MATEMATICAS',1,1),
   ('A003','HISTORIA UNIVERSAL I',1,1),
   ('A004','GEOGRAFIA GENERAL',1,1),
   ('A005','FORM. CIV. Y ETICA',1,1),
   ('A006','BIOLOGIA',1,1),
   ('A007','INT. A LA FIS. Y QUIM.',1,1),
   ('A008','LENGUA EXTRANJERA',1,1),
   ('A009','EXP. Y APREC. ARTISTICAS',1,1),
   ('A010','EDUCACION FISICA',1,1),
   ('A011','EDUCACION TECNOLOGICA',1,1),
   ('A012','ESPAÑOL',2,1),
   ('A013','MATEMATICAS',2,1),
   ('A014','HISTORIA UNIVERSAL II',2,1),
   ('A015','GEOGRAFIA DE MEXICO',2,1),
   ('A016','FORM. CIV. Y ETICA',2,1),
   ('A017','BIOLOGIA',2,1),
   ('A018','FISICA',2,1),
   ('A019','QUIMICA',2,1),
   ('A020','LENGUA EXTRANJERA',2,1),
   ('A021','EXP. Y APREC. ARTISTICAS',2,1),
   ('A022','EDUCACION FISICA',2,1),
   ('A023','EDUCACION TECNOLOGICA',2,1),
   ('A024','ESPAÑOL',3,1),
   ('A025','MATEMATICAS',3,1),
   ('A026','HISTORIA UNIVERSAL',3,1),
   ('A027','FORM. CIV. Y ETICA',3,1),
   ('A028','FISICA',3,1),
   ('A029','QUIMICA',3,1),
   ('A030','LENGUA EXTRANJERA',3,1),
   ('A031','ASIGNATURA OPCIONAL',3,1),
   ('A032','EXP. Y APREC. ARTISTICAS',3,1),
   ('A033','EDUCACION FISICA',3,1),
   ('A034','EDUCACION TECNOLOGICA',3,1);
  
  
  
  CREATE TABLE alumno ( 
      matricula varchar(9) CHARACTER SET utf8 COLLATE utf8_spanish_ci not null,
      nom varchar(40) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      app varchar(20) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      apm varchar(20) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      sexo varchar(2) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      curp varchar(25) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      fecnac date default NULL,
      calle varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      colonia varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      ciudad varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      cp varchar(5) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      telal varchar(15) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      email varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      
      idt varchar(10) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      passal varchar(40) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      grado int(11) default NULL,
      nomtut varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      dirtut varchar(60) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      teltut varchar(15) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      fecalta date default NULL,
      status int(11) default NULL,
      turno varchar(1) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      PRIMARY KEY (matricula),
      KEY matricula (matricula),
      KEY idt (idt),
      FOREIGN KEY (idt) REFERENCES taller (idt) ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  
  insert into alumno (matricula, nom, app, apm, sexo, curp, grado, idt, passal, status, turno) VALUES
  ('3863548','JOEL ENRIQUE','P008','MONARES','M','AEMJ061120HMCNNLA2',1,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1186304','JOSE ANTONIO','ANGUIANO','GONZALEZ','M','AUGA060523HDFNNNA4',1,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3858518','AXEL TADEO PARIS','AVALOS','HERRERA','M','AAHA061230HMCVRXA0',1,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1762748','JOSMAR MIGUEL','BARRAGAN','BAEZ','M','BABJ060406HMCRZSA2',2,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3955119','P014 EFREN','BECERRIL','JIMENEZ','M','BEJC060605HMCCMSA4',2,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1336843','P008 EDUARDO','BRUCE','RODRIGUEZ','M','BURA060114HDFRDNA4',2,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('159017','CARLOS DE JESUS','CAMACHO','RESENDIZ','M','CARC050321HMCMSRA7',3,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1341195','DIANA PAOLA','CANO','GARCIA','F','CAGD060530MDFNRNA7',3,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1338882','LENA VALENTINA','CASTRO','DE JESUS','F','CAJL060320MDFSSNA7',3,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('2922547','P030','DE LA ROSA','HERNANDEZ','M','ROHG051121HNESRRA0',1,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1324992','P008 EDUARDO','DURAN','HERNANDEZ','M','DUHA060225HDFRRNA4',1,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3902336','EFRAIN','ESPAÑA','ZAVALA','M','EAZE061203HTLSVFA3',1,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3906727','FERNANDO','FLORES','CLIMACO','M','FOCF060928HMCLLRA4',2,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3903866','NATALY CITLALY','FLORES','DE PAZ','F','FOPN050420MMCLZTA4',2,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1330367','P010 RUBEN','GALAN','FLORES','M','GAFA060614HMCLLRA7',2,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1763503','CARLOS BARUCH','GARCIA','ORTEGA','M','GAOC060622HMCRRRA2',3,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3857390','JUAN CARLOS','GONZALEZ','RUIZ','M','GORJ061116HDFNZNA4',3,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1762933','LUZ BELEN','GONZALEZ','VELAZQUEZ','F','GOVL061204MDFNLZA8',3,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3928628','IAN YAEL','HERNANDEZ','ARIAS','M','HEAI060927HMCRRNA3',3,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1762757','BRAULIO','HERNANDEZ','SEBASTIAN','M','HESB061019HDFRBRA1',1,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('34684','ESTHER','LUCAS','LOPEZ','F','LULE050517MOCCPSA2',1,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1186314','MEILY MICHELLE','LUNA','RODRIGUEZ','F','LURM060209MDFNDLA9',1,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3323585','ALONDRA MICHELL','MARTINEZ','HERMENEGILDO','F','MAHA060731MDFRRLA6',2,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('2967851','PERLA ESMERALDA','MONDRAGON','JARDINEZ','F','MOJP060628MDFNRRA1',2,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3903332','AMAIRANI','MORENO','FELIX','F','MOFA060813MMCRLMA4',2,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1203479','MARIA FERNANDA','OLEA','CASTANEYRA','F','OECF061205MOCLSRA0',3,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3861850','P008 GABRIEL','ORTIZ','LEMUS','M','OILA060710HMCRMNA4',3,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3955830','JORGE ALFREDO','PALACIOS','MARCIAL','M','PAMJ060601HPLLRRA7',3,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3902235','TANIA LISSETTE','PELAEZ','HEREDIA','F','PEHT060515MMCLRNA6',1,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3905916','ANTHONY JOSEP','PEREZ','HERNANDEZ','M','PEHA060314HMCRRNA2',2,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3163641','LESLI JANETE','RAMOS','ANDRADE','F','RAAL060201MDFMNSA6',3,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3901367','KEYLANI PAMELA','RODRIGUEZ','AGUILAR','F','ROAK060629MDFDGYA5',1,'T315','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1337080','DILAN ALEXIS','ROMERO','MENDEZ','M','ROMD060118HMCMNLA6',1,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3859392','MITZY MARIANA','ROQUE','GARCIA','F','ROGM060526MDFQRTA4',2,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1950846','NICOLE LIVIER','RUIZ','TOBON','F','RUTN061119MDFZBCA5',3,'T310','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1186201','DIANA LAURA','VALENCIA','AGUILAR','F','VAAD061110MDFLGNA3',1,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('3952276','PAOLA MARLEN','YAÑEZ','ROJAS','F','YARP060110MMCXJLA2',2,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1757491','GENESIS NATALIA','ZAMBRANO','PEREZ','F','ZAPG060424MDFMRNA0',3,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V'),
  ('1186328','GABRIEL SEBASTIAN','ZAVALA','MATA','M','ZAMG061012HMCVTBA7',1,'T307','7110EDA4D09E062AA5E4A390B0A572AC0D2C0220',1,'V');
  
  -- creacion de horario --
  
  
  CREATE TABLE grupo (
  idg int, 
  grupo varchar(3) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
  grado int,
  PRIMARY KEY (idg)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  insert grupo values
  (1,'1A',1),
  (2,'1B',1),
  (3,'1C',1),
  (4,'1D',1),
  (5,'1E',1),
  (6,'1F',1),
  (7,'1G',1),
  (8,'1H',1),
  (9,'1I',1),
  (10,'1J',1),
  (11,'1K',1),
  (12,'1L',1),
  (13,'1M',1),
  (14,'1N',1),
  (15,'1O',1),
  (16,'1P',1),
  (17,'1Q',1),
  (18,'2A',2),
  (19,'2B',2),
  (20,'2C',2),
  (21,'2D',2),
  (22,'2E',2),
  (23,'2F',2),
  (24,'2G',2),
  (25,'2H',2),
  (26,'2I',2),
  (27,'2J',2),
  (28,'2K',2),
  (29,'2L',2),
  (30,'2M',2),
  (31,'2N',2),
  (32,'2O',2),
  (33,'2P',2),
  (34,'2Q',2),
  (35,'3A',3),
  (36,'3B',3),
  (37,'3C',3),
  (38,'3D',3),
  (39,'3E',3),
  (40,'3F',3),
  (41,'3G',3),
  (42,'3H',3),
  (43,'3I',3),
  (44,'3J',3),
  (45,'3K',3),
  (46,'3L',3),
  (47,'3M',3),
  (48,'3N',3),
  (49,'3O',3),
  (50,'3P',3),
  (51,'3Q',3);
  
  
  CREATE TABLE semana (
  idia int(11) NOT NULL,
  dia varchar(10) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
  PRIMARY KEY (idia)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  INSERT INTO semana (idia, dia) VALUES
  (1, 'LUNES'),
  (2, 'MARTES'),
  (3, 'MIERCOLES'),
  (4, 'JUEVES'),
  (5, 'VIERNES'),
  (6, 'SABADO'),
  (7, 'DOMINGO');
  
  CREATE TABLE reloj (
  idr int(11) NOT NULL,
  hora varchar(11) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
  PRIMARY KEY (idr)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  INSERT INTO reloj (idr, hora) VALUES
  (1, '07-08'),
  (2, '08-09'),
  (3, '08-09'),
  (4, '09-10'),
  (5, '10-11'),
  (6, '11-12'),
  (7, '12-13'),
  (8, '13-14'),
  (9, '14-15'),
  (10, '15-16'),
  (11,'16-17'),
  (12,'17-18'),
  (13,'18-19'),
  (14,'19-20'),
  (15,'20-21');
  
  CREATE TABLE aula ( 
      ida int(11) NOT NULL, 
      aula varchar(10) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      PRIMARY KEY (ida) 
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  
  
  INSERT INTO aula (ida, aula) VALUES
  (1, 'A-1') ,
  (2, 'A-L1') ,
  (3, 'B-1') ,
  (4, 'B-2') ,
  (5, 'B-3') ,
  (6, 'B-4') ,
  (7, 'B-5') ,
  (8, 'B-6') ,
  (9, 'C-1') ,
  (10, 'C-2') ,
  (11, 'C-3') ,
  (12, 'C-4') ,
  (13, 'C-5') ,
  (14, 'C-6') ,
  (15, 'C-L1') ,
  (16, 'C-C1') ,
  (17, 'C-C2') ,
  (18, 'D-1') ,
  (19, 'D-2') ,
  (20, 'D-3') ,
  (21, 'D-4') ,
  (22, 'D-5') ,
  (23, 'D-6') ,
  (24, 'D-7') ,
  (25, '25') ,
  (26, '26') ,
  (27, '27') ,
  (28, '28') ,
  (29, 'B2') ,
  (30, 'CC');
  
  CREATE TABLE horario (
  idh int NOT NULL,
  idmat varchar(10) CHARACTER SET utf8 COLLATE utf8_spanish_ci not null,
  rfcp varchar(13) CHARACTER SET utf8 COLLATE utf8_spanish_ci not null,
  idg int not null,
  idp varchar(10) CHARACTER SET utf8 COLLATE utf8_spanish_ci not null,
  turno varchar(1) CHARACTER SET utf8 COLLATE utf8_spanish_ci not null,
  PRIMARY KEY (idh),
  FOREIGN KEY (rfcp) REFERENCES personal (rfcp) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (idmat) REFERENCES materia (idmat) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (idg) REFERENCES grupo (idg) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (idp) REFERENCES periodo (idp) ON UPDATE CASCADE ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  
  insert horario values 
  (1,'A001','P001',1,'2020','V'),
  (2,'A002','P004',1,'2020','V'),
  (3,'A003','P003',1,'2020','V'),
  (4,'A004','P006',1,'2020','V'),
  (5,'A005','P012',1,'2020','V'),
  (6,'A006','P009',2,'2020','V'),
  (7,'A007','P011',2,'2020','V'),
  (8,'A008','P001',2,'2020','V'),
  (9,'A009','P011',3,'2020','V'),
  (11,'A010','P003',3,'2020','V'),
  (12,'A011','P003',3,'2020','V'),
  (13,'A012','P001',4,'2020','V'),
  (14,'A013','P011',4,'2020','V'),
  (15,'A014','P003',4,'2020','V');
  
  
  CREATE TABLE imparte (
  idh int(11) NOT NULL,
  idia int(11) NOT NULL,
  idr int(11) NOT NULL,
  ida int(11) NOT NULL,
  PRIMARY KEY (idh,idia,idr),
  FOREIGN KEY (idh) REFERENCES horario (idh) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (idia) REFERENCES semana (idia) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (idr) REFERENCES reloj (idr) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (ida) REFERENCES aula (ida) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  
  insert into imparte (idh,idia,idr,ida) values 
  (1, 1, 1, 1),
  (1, 2, 1, 1),
  (1, 3, 1, 1),
  (1, 4, 1, 1),
  (1, 5, 1, 1),
  (2, 1, 2, 2),
  (2, 2, 2, 2),
  (2, 3, 2, 2),
  (3, 1, 3, 3),
  (3, 2, 3, 3),
  (3, 3, 3, 3),
  (3, 4, 3, 3),
  (3, 5, 3, 3),
  (4, 1, 4, 4),
  (4, 2, 4, 4),
  (4, 3, 4, 4),
  (4, 4, 4, 4),
  (4, 5, 4, 4),
  (5, 1, 5, 5),
  (5, 2, 5, 6),
  (5, 3, 5, 7),
  (5, 4, 5, 8),
  (5, 5, 5, 9),
  (6, 1, 1, 10),
  (6, 2, 2, 11),
  (6, 3, 3, 12),
  (6, 4, 4, 13),
  (6, 5, 5, 14);
  
  
  CREATE TABLE cursa (
      idh int(11) NOT NULL,
      matricula varchar(9) CHARACTER SET utf8 COLLATE utf8_spanish_ci not null,
      tr1 int,
      ftr1 date,
      asigna1 varchar(10) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      tr2 int,
      ftr2 date,
      asigna2 varchar(10) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      tr3 int,
      ftr3 date,
      asigna3 varchar(10) CHARACTER SET utf8 COLLATE utf8_spanish_ci,
      deser int(11) default NULL,
      prom float default NULL,
      PRIMARY KEY (idh,matricula),
      KEY idh (idh,matricula),
      FOREIGN KEY (idh) REFERENCES horario (idh) ON UPDATE CASCADE,
      FOREIGN KEY (asigna1) REFERENCES personal (rfcp) ON UPDATE CASCADE,
      FOREIGN KEY (asigna2) REFERENCES personal (rfcp) ON UPDATE CASCADE,
      FOREIGN KEY (asigna3) REFERENCES personal (rfcp) ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  
  insert into cursa (idh, matricula) values 
  (1,'3863548'),
  (1,'1186304'),
  (1,'3858518'),
  (1,'1762748'),
  (1,'3955119'),
  (2,'3863548'),
  (2,'1186304'),
  (2,'3858518'),
  (2,'1762748'),
  (2,'3955119'),
  (3,'3863548'),
  (3,'1186304'),
  (3,'3858518'),
  (3,'1762748'),
  (3,'3955119'),
  (4,'3863548'),
  (4,'1186304'),
  (4,'3858518'),
  (4,'1762748'),
  (4,'3955119'),
  (5,'3863548'),
  (5,'1186304'),
  (5,'3858518'),
  (5,'1762748'),
  (5,'3955119'),
  (6,'1336843'),
  (6,'159017'),
  (6,'1341195'),
  (6,'1338882'),
  (7,'1336843'),
  (7,'159017'),
  (7,'1341195'),
  (7,'1338882'),
  (8,'1336843'),
  (8,'159017'),
  (8,'1341195'),
  (8,'1338882'),
  (9,'2922547'),
  (9,'1324992'),
  (9,'3902336'),
  (9,'3906727'),
  (11,'2922547'),
  (11,'1324992'),
  (11,'3902336'),
  (11,'3906727'),
  (12,'2922547'),
  (12,'1324992'),
  (12,'3902336'),
  (12,'3906727');