database Implementation

[gmc1103]

Hi

 

I need some advices and help regarding this database.

This is what i have so far with all tables necessary

This is a school timetable where the teachers must register the class summary

My main problem is this:

Every monday there is classes....so every teacher must register the summary, how do i know from timetable table and "apoios" table is the summary has been registered?

 

Thanks

Edited March 9, 2016 by gmc1103

[Barand]

 

how do i know from timetable table and "apoios" table is the summary has been registered?

 

Good question, and one that is impossible for us to answer from the scant information you have given us.

 

Define "register a summary".

 

What are the processes and data involved in the registration?

[gmc1103]

Hi Barand

 

Sorry for not be so clear as i should.

Well, in every classe the teacher must insert into the datatable  the subject of the classe, for example if i have a classe of math on monday at 12:00, i must fill in a form the following data

 

Subject

Data

Classe

Missing Student

 

Somy question is, how can i prevent a teacher of not fill the form...since every week (monday) i have this classe and he must fill that form every monday?

 

Thanks 

[Barand]

 

if i have a classe of math on monday at 12:00, i must fill in a form the following data

 

Subject

Data

Classe

Missing Student

 

So having filled in a form, what happens to that data?

[gmc1103]

Hi Barand

The data is inserted into the database, this table

"apoios"

CREATE TABLE `apoios` (
  `idtimetable` int(11) NOT NULL AUTO_INCREMENT,
  `sala_id` int(11) NOT NULL,
  `data` date NOT NULL,
  `inicio_id` time NOT NULL,
  `grupo` time NOT NULL,
  `teacher_sub_teacher_idteacher` int(11) NOT NULL,
  `teacher_sub_subject_idsubject` int(11) NOT NULL,
  `teacher_sub_turma_idturma` int(11) NOT NULL,
  PRIMARY KEY (`idtimetable`),
  KEY `fk_timetable_teacher_sub1_idx` (`teacher_sub_teacher_idteacher`,`teacher_sub_subject_idsubject`,`teacher_sub_turma_idturma`),
  KEY `sala_id` (`sala_id`),
  CONSTRAINT `apoios_ibfk_1` FOREIGN KEY (`sala_id`) REFERENCES `salas` (`idsala`),
  CONSTRAINT `fk_timetable_teacher_sub1` FOREIGN KEY (`teacher_sub_teacher_idteacher`, `teacher_sub_subject_idsubject`, `teacher_sub_turma_idturma`) REFERENCES `prof_disc` (`teacher_idteacher`, `subject_idsubject`, `turma_idturma`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEF

This is the table where the data inserted in the form will be saved.

 

My main issue here is this table

CREATE TABLE `timetable` (
  `id_timetable` int(11) NOT NULL AUTO_INCREMENT,
  `id_grupo` int(11) NOT NULL,
  `id_dia` int(11) NOT NULL,
  `id_tempo` int(11) NOT NULL,
  `id_prof` int(11) NOT NULL,
  PRIMARY KEY (`id_timetable`),
  KEY `id_grupo` (`id_grupo`),
  KEY `id_dia` (`id_dia`),
  KEY `id_tempo` (`id_tempo`),
  CONSTRAINT `timetable_ibfk_1` FOREIGN KEY (`id_grupo`) REFERENCES `grupos` (`id_grupos`),
  CONSTRAINT `timetable_ibfk_2` FOREIGN KEY (`id_dia`) REFERENCES `dias` (`id_dia`),
  CONSTRAINT `timetable_ibfk_3` FOREIGN KEY (`id_tempo`) REFERENCES `tempos` (`id_tempo`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

This is my timetable table where i have all classes scheduled.

 

So, between those two tables how i can check if teacher A has filled the form for hhis scheduled classes?

 

Thanks

[Barand]

 

The data is inserted into the database, this table

"apoios"

 

Not instantly obvious, as there is data entered in the form that is not in that table and data in that table that is not in the form. So the extra data in the table has to come from somewhere. I guess there is more to this process than "The teacher fills in a form". More confusing as "grupo" has become a time field in the "apoios" table.

 

You can probably do something with day of week from apoios date field but not sure what your day data looks like in the timetable data. From what you have said so far (not much) there is little data in common between those two tables from the process as described so far. Where, for instance, does the timetable (primary key ???) appear from.

 

If you wanted someone to write a program to handle this process, do you think that they could do it from the description you have given me, namely

 

if i have a classe of math on monday at 12:00, i must fill in a form the following data

 

Subject

Data

Classe

Missing Student

[gmc1103]

Hi Barand

 

Ok, let me try to clarify everything

This is a school management system.

• Teachers have timetable
• Timetable have teachears, students and subject
• I need to for every teachers timetable to have a lesson.

So, if my season starts on september and i have 35 school weeks, i must "give" 35 lessons, so must store 35 lessons in database.

To do so, today, regarding my timetable, i have classe between 2 PM and 4 PM,  so after or during the class time i must be able to fill that form( regarding the plan lesson, missing students, etc), but if i forgot to do it, i want to have a warning to remember that lesson x has not been registered.

 

To avoid any confusion...this is my sql script

 

Hope now it can be clear.. 

esmaior_ca.zip

[Barand]

I was thinking you could match on idtimetable but I notice it is autoincrement in both tables and not a foreign key, so that is out of the question. Plus you have no dates to match on.

 

Sorry but I cannot see any obvious way that your data structure supports the processing that you require.

[gmc1103]

Hi Barand

 

So, do you suggest to have a foreign key between "apoio" and "timetable"?

[Barand]

With my limited knowledge of your processes and data there is little help I can offer. You need sufficient common data in the two tables to be able to match one with the other so you can determine if a record is present or not.