Finding Empty Hours For A Specified Classroom ( Database Design Issue )_问答_开发者

I am trying to design a database for creating a school syllabus. In a nutshell, all classrooms can be active for 10 hours from 8.00am to 18.00 pm. How to create the database to answer the below ?开发者_如何转开发

Get me empty hours for the classroom which is A101 on Monday ?

I made this one below but its not enough good to answer this question efficiently |: http://i51.tinypic.com/110drgk.png

I only dabble in database, but the problem seemed interesting to me.

From what I'm getting, it sounds like you are more interested in how to write the query than how to store the data. Based on your explanation, what you want is a query whose output looks something like this:

classroom  openStart  openEnd  day session
A101       9:00       10:00    mo  spring
A101       14:00      16:00    mo  spring

Assuming schema test, table class. Class holds a startTime and endTime value. Other values on which filtering will be performed are ignored here to focus on just the logic you are interested in. This query was run on MySQL.

-- 8 and 18 are added as the boundaries for the day.
Select * from (Select * from ((select endTime as a from test.class) union (select 8)) as t1 
   join ((select startTime as b from test.class) union (select 18 as startTime)) as t2 
   where a <= b group by a order by a, b) as t3 where a != b;

Three subqueries.

The first creates a cross-product of all end times (with the start time for the day appended) by all start times (with the end time for the day appended in). What this gives you is a series of rows representing every conceivable open block of time. (arranged as block start/ block end)

The second then pares the results of the first subquery by filtering out values where block start happens after block end, then sorts the values by block start, block end. This makes it so that when the values are grouped by block-start, you get a nice list of pairs that make it easy to spot places where classes are continuous.

The third query then filters out the values where the times are the same, giving you just the time blocks that are relevant. Hypothetically the third query could use a comparison that ruled out cases where classes were less than an hour apart as well.

I'm sure others might have somewhat simpler solutions for you.

You don't even have classroom in your database diagram.

If one of your main concerns is read premoance time I would suggest something along the lines of the following:

A timeSlot table that has column for the blocks of time in a day (since this can be broken down into a relatively small finite number, say one per 30 minutes). A classroom table would then have 5 or 7 IDs referring to 5 or 7 entries in the timeSlot table

Hope this helps,

Ryan Taylor

DBMS's that support generic interval types make this dead easy :

Define a relvar with the attributes and .

Then just query 'X NOT MATCHING Y', where :

X is a relation literal mentioning the appropriate classroom identifier and the time interval 08.00-18.00,

Y is your relvar holding the <scheduled_during>,

(and NOT MATCHING is whatever keyword you need to invoke the relational algebra's SEMIMINUS operator).

In SIRA_PRISE, for example, you could have a relvar named, e.g., SCH, holding, e.g., the "scheduled_during" tuple {classRoomID:"A101" scheduledDuring:[1999-01-02-09.30.00-1999-01-02-12.00.00]}.

A relation literal could be "RELATION{TUPLE{classRoomID:"A101" scheduledDuring:[1999-01-02-08.00.00-1999-01-02-18.00.00]}}".

And querying SEMIMINUS(RELATION{TUPLE{...}},SCH) would then yield the tuples

classRoomID:"A101" scheduledDuring:[1999-01-02-08.00.00-1999-01-02-09.30.00] and classRoomID:"A101" scheduledDuring:[1999-01-02-12.00.00-1999-01-02-18.00.00]

EDIT

PS don't be confused by that attribute name scheduledDuring showing periods during which the room is NOT scheduled. The meaning of the values is in the "external predicate" of your queries, not in the attribute names.

Your solution is a basic reservation system. In a reservation system the resources are defined with an availability period. If the business rule was the resource was used in single hour increments I would design the resource table with 24 periods to represent each hour of the day. If the business rule stated the resource was available during 8AM to 10AM I would place a "True value" in the period columns representing that time frame. Reservations are tracked in a seperate table with a foreign key back to the resource. The reservation table has matching period columns from the resource table. When a reservation is made during a period the resource is available a "True value" is placed in the column. Resources available for reservation are found by

Aggregating the reservation values for each period for the day
Build a list of available resources
Display the resources which do not have a reservation.

In the calculation for step three we can rule out any resource who's period availability is "False" then we look at the aggregate value from the reservations table and state if there is a reservation("True") then the availability is "False".

The code below is written in SQL Server 2008 and demonstrates the principals above.

    /****** Object:  Table [dbo].[Classroom]    Script Date: 05/20/2011 08:25:53 ******/
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Classroom]') AND type in (N'U'))
    DROP TABLE [dbo].[Classroom]
    GO
    /****** Object:  Table [dbo].[Classroom]    Script Date: 05/20/2011 08:25:53 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Classroom]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[Classroom](
        [ClassRoomID] [int] IDENTITY(1,1) NOT NULL,
        [CrIsAvailablePeriod01] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod01 DEFAULT 0,
        [CrIsAvailablePeriod02] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod02 DEFAULT 0,
        [CrIsAvailablePeriod03] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod03 DEFAULT 0,
        [CrIsAvailablePeriod04] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod04 DEFAULT 0,
        [CrIsAvailablePeriod05] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod05 DEFAULT 0,
        [CrIsAvailablePeriod06] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod06 DEFAULT 0,
        [CrIsAvailablePeriod07] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod07 DEFAULT 0,
        [CrIsAvailablePeriod08] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod08 DEFAULT 0,
        [CrIsAvailablePeriod09] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod09 DEFAULT 0,
        [CrIsAvailablePeriod10] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod10 DEFAULT 0,
        [CrIsAvailablePeriod11] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod11 DEFAULT 0,
        [CrIsAvailablePeriod12] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod12 DEFAULT 0,
        [CrIsAvailablePeriod13] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod13 DEFAULT 0,
        [CrIsAvailablePeriod14] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod14 DEFAULT 0,
        [CrIsAvailablePeriod15] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod15 DEFAULT 0,
        [CrIsAvailablePeriod16] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod16 DEFAULT 0,
        [CrIsAvailablePeriod17] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod17 DEFAULT 0,
        [CrIsAvailablePeriod18] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod18 DEFAULT 0,
        [CrIsAvailablePeriod19] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod19 DEFAULT 0,
        [CrIsAvailablePeriod20] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod20 DEFAULT 0,
        [CrIsAvailablePeriod21] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod21 DEFAULT 0,
        [CrIsAvailablePeriod22] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod22 DEFAULT 0,
        [CrIsAvailablePeriod23] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod23 DEFAULT 0,
        [CrIsAvailablePeriod24] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod24 DEFAULT 0,
        [CrShortName] [char](10) NOT NULL,
        [CrLongName] [varchar](128) NULL,

     CONSTRAINT [PK_Classroom] PRIMARY KEY CLUSTERED 
    (
        [ClassRoomID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO

    SET ANSI_PADDING ON
    GO


    INSERT INTO [deleteme].[dbo].[Classroom]
               ([CrIsAvailablePeriod01]
               ,[CrIsAvailablePeriod02]
              ,[CrShortName])
         VALUES
               (0,0,'A1')
               ,(0,1,'B1')
               ,(1,0,'C1')
               ,(1,1,'D1')
    GO
USE [deleteme]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Reservation_Classroom]') AND parent_object_id = OBJECT_ID(N'[dbo].[Reservation]'))
ALTER TABLE [dbo].[Reservation] DROP CONSTRAINT [FK_Reservation_Classroom]
GO

USE [deleteme]
GO

/****** Object:  Table [dbo].[Reservation]    Script Date: 05/20/2011 08:29:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Reservation]') AND type in (N'U'))
DROP TABLE [dbo].[Reservation]
GO

USE [deleteme]
GO

/****** Object:  Table [dbo].[Reservation]    Script Date: 05/20/2011 08:29:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Reservation]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Reservation](
    [ReservationId] [int] Identity (1,1)NOT NULL,
    [ResClassroomID] [int] NOT NULL,
    [ResDate] [date] NOT NULL,
    [ResIsReservedPeriod01] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod01 DEFAULT 0, 
    [ResIsReservedPeriod02] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod02 DEFAULT 0, 
    [ResIsReservedPeriod03] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod03 DEFAULT 0, 
    [ResIsReservedPeriod04] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod04 DEFAULT 0, 
    [ResIsReservedPeriod05] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod05 DEFAULT 0, 
    [ResIsReservedPeriod06] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod06 DEFAULT 0, 
    [ResIsReservedPeriod07] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod07 DEFAULT 0, 
    [ResIsReservedPeriod08] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod08 DEFAULT 0, 
    [ResIsReservedPeriod09] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod09 DEFAULT 0, 
    [ResIsReservedPeriod10] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod10 DEFAULT 0, 
    [ResIsReservedPeriod11] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod11 DEFAULT 0, 
    [ResIsReservedPeriod12] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod12 DEFAULT 0, 
    [ResIsReservedPeriod13] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod13 DEFAULT 0, 
    [ResIsReservedPeriod14] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod14 DEFAULT 0, 
    [ResIsReservedPeriod15] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod15 DEFAULT 0, 
    [ResIsReservedPeriod16] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod16 DEFAULT 0, 
    [ResIsReservedPeriod17] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod17 DEFAULT 0, 
    [ResIsReservedPeriod18] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod18 DEFAULT 0, 
    [ResIsReservedPeriod19] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod19 DEFAULT 0, 
    [ResIsReservedPeriod20] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod20 DEFAULT 0, 
    [ResIsReservedPeriod21] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod21 DEFAULT 0, 
    [ResIsReservedPeriod22] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod22 DEFAULT 0, 
    [ResIsReservedPeriod23] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod23 DEFAULT 0, 
    [ResIsReservedPeriod24] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod24 DEFAULT 0, 

    CONSTRAINT [PK_Reservation] PRIMARY KEY CLUSTERED 
(
    [ReservationId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Reservation_Classroom]') AND parent_object_id = OBJECT_ID(N'[dbo].[Reservation]'))
ALTER TABLE [dbo].[Reservation]  WITH CHECK ADD  CONSTRAINT [FK_Reservation_Classroom] FOREIGN KEY([ResClassroomID])
REFERENCES [dbo].[Classroom] ([ClassRoomID])
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Reservation_Classroom]') AND parent_object_id = OBJECT_ID(N'[dbo].[Reservation]'))
ALTER TABLE [dbo].[Reservation] CHECK CONSTRAINT [FK_Reservation_Classroom]
GO


INSERT INTO [deleteme].[dbo].[Reservation]
           ([ResClassroomID]
           ,[ResDate]
           ,[ResIsReservedPeriod01]
           ,[ResIsReservedPeriod02])
     VALUES
           (1,'06-02-2011',1,0)
           ,(1,'06-02-2011',0,1)
           ,(2,'06-03-2011',1,1)
           ,(4,'06-03-2011',0,1)


GO

The code below will return three result sets. The first set is the availability calculation, second is the list of the classroom table and finally the reservation table.

declare @when date
set @when = '06-03-2011'


;With CTE_Res AS
(
SELECT     
Reservation.ResClassroomID AS 'ResClassroomID'
,ResDate as 'ResDate'
, CAST(ISNULL(MAX(CAST([ResIsReservedPeriod01] as int)),0)as BIT) AS'IsReserved01'
, CAST(ISNULL(MAX(CAST([ResIsReservedPeriod02] as int)),0)as BIT) AS'IsReserved02'
, CAST(ISNULL(MAX(CAST([ResIsReservedPeriod03] as int)),0)as BIT) AS'IsReserved03'
, CAST(ISNULL(MAX(CAST([ResIsReservedPeriod04] as int)),0)as BIT) AS'IsReserved04'
, CAST(ISNULL(MAX(CAST([ResIsReservedPeriod05] as int)),0)as BIT) AS'IsReserved05'
, CAST(ISNULL(MAX(CAST([ResIsReservedPeriod06] as int)),0)as BIT) AS'IsReserved06'
FROM         
Reservation 
WHERE ResDate = @when --'2011-06-03'
GROUP BY Reservation.ResClassroomID, ResDate
)
--Select * from CTE_RES
SELECT     
Classroom.ClassRoomID
,Classroom.CrShortName

, CASE Classroom.CrIsAvailablePeriod01 
    WHEN 0 then 0
    Else  1 ^ ISNULL(CTE_Res.IsReserved01,0) 
    END As 'IsOpenPeriod01'
, CASE Classroom.CrIsAvailablePeriod02 
    WHEN 0 then 0
    Else  1 ^ ISNULL(CTE_Res.IsReserved02,0) 
    END As 'IsOpenPeriod02'

--, CTE_Res.ResDate
, @when as [Day]
FROM         
Classroom 
LEFT OUTER JOIN CTE_Res 
ON Classroom.ClassRoomID = CTE_Res.ResClassroomID
--WHERE ResDate = '2011-06-03'



SELECT  [ClassRoomID]
,[CrShortName]
      ,[CrIsAvailablePeriod01]
      ,[CrIsAvailablePeriod02]

  FROM [deleteme].[dbo].[Classroom]

SELECT TOP 200 [ResClassroomID]
,ResDate
      ,[ResIsReservedPeriod01]
      ,[ResIsReservedPeriod02]
      ,[ResDate]  
      FROM [deleteme].[dbo].[Reservation]  

      WHERE ResDate = @when --'2011-06-03'