a group of students can not use that classroom if it is already reserved at that time [duplicated]

Question

a group of students can not use that classroom if it is already reserved at that time [duplicated]

Navigation

#1 by (3 votes)

0

I have a problem I do not know how to design the solution.
A group of students can not use that classroom if it is already reserved at that time.
For example, you can not assign a group to a classroom that is already occupied with a specific schedule.

Example:
Group1 occupies the laboratory1 from 12:30 to 3:00 so group 2 can not use that laboratory in that timeframe. but if you can use it from 3:00 to 6:00

 create table grupo 
(
 id_grupo int not null primary key ,
 nombre_grupo nvarchar (50),
 responsable_grupo int references tprofesor
)
create table Aula
(
 Id_Aula int ,
 Nombre_Aula nvarchar (50),
 Ubicacion_Aula nvarchar (50),
 Descripcion_Aula varchar(50),
 Id_Asignatura int references Asignatura
)

Some idea how to solve

sql sql-server postgresql

asked by gelder gomez 04.05.2018 в 09:30

source

1 answer

Javascript difference of 1 decimal [duplicate] Darken background when running a boostrap modal?

score 3 · Answer 1

You need to add another table that relates the groups to the classrooms. as "Reservations"

Reservations will have 3 fields

1.- Group_ID - foreign key to the group table
2.- Classroom Id - foreign key to the Classroom table
3.- Start_time - field type date or time
4 - end_time - camo type date or time

What you need now is to make a restriction through a trigger or store procedure.

What you have to check is that when making a new reservation, the room is vacated, how can you do this?

 select * from reserva where Aula = AulaAReservar 
 and (
           (hora_inicial < HoraInicialAreservar and Hora_final > HoraInicialAreservar ) 
        or 
           (hora_inicial > HoraInicialAreservar and hora_inicial < HoraFinalAReservar)
     )

The first condition within AND is to make sure we are not booking during the course of a reservation,
the OR is to ensure that there is no reservation in the middle of the period we want to reserve.

If the result above returns any result, it means that you can not reserve in that classroom during that period