Class Scheduling System Table Structure

Submitted by:

Sunday, August 19, 2012 - 11:42

In Class Scheduling System/ Module you have to consider the following constraint:

1. Academic Year and Term
2. Class Section
3. Class Room and Building
4. Faculty
5. Subjects
6. Curriculum
7. Class Schedule (Subject Offering) - Class Schedule is set for maximum of 5 Schedule per Subject

USING MySQL as the Primary Database......

CREATE TABLE `tblayterm` (
  `TermID` INT(10) UNSIGNED NOT NULL,
  `SchoolYear` VARCHAR(10) DEFAULT NULL,
  `Locked` tinyint(1) DEFAULT '0',
  `SchoolTerm` VARCHAR(45) DEFAULT NULL,
  `StartofSY` datetime DEFAULT NULL,
  `EndofSY` datetime DEFAULT NULL,
  `ExpireReg` tinyint(3) UNSIGNED DEFAULT NULL,
  `ExpireDays` INT(10) UNSIGNED DEFAULT NULL,
  `LastModified` VARCHAR(45) DEFAULT NULL,
  `LastModifiedDate` datetime DEFAULT NULL,
  `NumWeeks` INT(11) DEFAULT NULL,
  `Hidden` INT(11) DEFAULT NULL,
  PRIMARY KEY (`TermID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `tblsection` (
  `SectionID` VARCHAR(20) NOT NULL,
  `SectionTitle` VARCHAR(255) DEFAULT NULL,
  `YearLevelID` INT(11) DEFAULT '0',
  `TermID` INT(10) UNSIGNED DEFAULT NULL,
  `CampusID` VARCHAR(45) DEFAULT NULL,
  `CollegeID` VARCHAR(45) DEFAULT NULL,
  `CurriculumID` VARCHAR(45) DEFAULT NULL,
  `ProgramID` VARCHAR(45) DEFAULT NULL,
  `AdviserID` VARCHAR(45) DEFAULT NULL,
  `IsBlock` tinyint(3) UNSIGNED DEFAULT NULL,
  `RoomID` VARCHAR(45) DEFAULT NULL,
  `Limit` INT(10) UNSIGNED DEFAULT NULL,
  `IsEvening` tinyint(3) UNSIGNED DEFAULT NULL,
  `IsDissolved` tinyint(3) UNSIGNED DEFAULT NULL,
  `CreationDate` datetime DEFAULT NULL,
  `CreatedBy` VARCHAR(45) DEFAULT NULL,
  `ModifiedBy` VARCHAR(45) DEFAULT NULL,
  `ModifiedOn` datetime DEFAULT NULL,
  PRIMARY KEY (`SectionID`),
  KEY `YearLevelID` (`YearLevelID`),
  KEY `TermID` (`TermID`) USING BTREE,
  KEY `CampusID` (`CampusID`) USING BTREE,
  KEY `CollegeID` (`CollegeID`) USING BTREE,
  KEY `ProgramID` (`ProgramID`) USING BTREE,
  CONSTRAINT `FKTermID` FOREIGN KEY (`TermID`) REFERENCES `tblayterm` (`TermID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `tblbuilding` (
  `BldgID` INT(11) NOT NULL,
  `CampusID` INT(11) DEFAULT NULL,
  `BldgName` VARCHAR(100) CHARACTER SET latin1 DEFAULT NULL,
  `BldgOtherName` VARCHAR(100) CHARACTER SET latin1 DEFAULT NULL,
  `Acronym` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL,
  `FloorsCount` INT(11) DEFAULT NULL,
  `BldgPic` longblob,
  `IsLANReady` tinyint(3) UNSIGNED DEFAULT NULL,
  `Elevator` tinyint(3) UNSIGNED DEFAULT NULL,
  `Escalator` tinyint(3) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`BldgID`),
  KEY `Index_2` (`CampusID`),
  CONSTRAINT `FK_CampusID` FOREIGN KEY (`CampusID`) REFERENCES `tblcampus` (`CampusID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `tblroom` (
  `RoomID` VARCHAR(20) NOT NULL,
  `BldgID` INT(11) DEFAULT NULL,
  `Floor` INT(11) DEFAULT NULL,
  `Room` VARCHAR(100) DEFAULT NULL,
  `Capacity` INT(11) DEFAULT NULL,
  `RoomNo` VARCHAR(20) DEFAULT NULL,
  `RoomTypeID` INT(11) DEFAULT NULL,
  `IsAirConditioned` tinyint(3) UNSIGNED DEFAULT NULL,
  `IsUsable` tinyint(3) UNSIGNED DEFAULT NULL,
  `IsLANMember` tinyint(3) UNSIGNED DEFAULT NULL,
  `AllowNightClass` tinyint(3) UNSIGNED DEFAULT NULL,
  `Shared` tinyint(3) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`RoomID`),
  KEY `RoomTypeID` (`RoomTypeID`),
  CONSTRAINT `FK_RoomTypeID` FOREIGN KEY (`RoomTypeID`) REFERENCES `tblroomtypes` (`RoomTypeID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `tblteacher` (
  `TeacherID` VARCHAR(10) NOT NULL,
  `EmployeeID` VARCHAR(45) NOT NULL,
  `DeptID` VARCHAR(45) NOT NULL,
  `CampusID` VARCHAR(45) NOT NULL,
  `CollegeID` VARCHAR(45) NOT NULL,
  `RankID` VARCHAR(45) NOT NULL,
  `IsRegularFaculty` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
  `IsFullTime` tinyint(3) UNSIGNED NOT NULL,
  `PRC_LicenseID` VARCHAR(45) NOT NULL,
  `DegreeDiscipline` VARCHAR(45) NOT NULL,
  `TeachLoadLevel` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`TeacherID`),
  KEY `TeacherID` (`TeacherID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `tblcurriculum` (
  `CurriculumID` VARCHAR(45) NOT NULL,
  `CurriculumCode` VARCHAR(45) NOT NULL,
  `Description` VARCHAR(200) DEFAULT NULL,
  `Notes` text,
  `CreatedBy` VARCHAR(45) DEFAULT NULL,
  `CreationDate` datetime DEFAULT NULL,
  `ModifiedBy` VARCHAR(45) DEFAULT NULL,
  `ModifiedDate` datetime DEFAULT NULL,
  `IsLocked` tinyint(3) UNSIGNED DEFAULT '0',
  `DateLocked` datetime DEFAULT NULL,
  `ProgramID` VARCHAR(45) DEFAULT NULL,
  `MajorID` VARCHAR(45) DEFAULT NULL,
  `CampusID` INT(11) DEFAULT NULL,
  PRIMARY KEY (`CurriculumID`),
  KEY `Index_3` (`ProgramID`) USING BTREE,
  KEY `Index_1` (`CampusID`) USING BTREE,
  KEY `Index_2` (`MajorID`) USING BTREE,
  CONSTRAINT `FKCampusID` FOREIGN KEY (`CampusID`) REFERENCES `tblcampus` (`CampusID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FKProgramID` FOREIGN KEY (`ProgramID`) REFERENCES `tblprograms` (`ProgID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `tblcurriculumdetails` (
  `IndexID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `CurriculumID` VARCHAR(45) NOT NULL,
  `YearTermID` INT(10) UNSIGNED NOT NULL,
  `SubjectID` VARCHAR(45) NOT NULL,
  `YearStandingID` INT(10) UNSIGNED DEFAULT NULL,
  `EquivalentSubjectID` VARCHAR(45) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`IndexID`) USING BTREE,
  KEY `CurriculumID` (`CurriculumID`) USING BTREE,
  KEY `SubjectID` (`SubjectID`) USING BTREE,
  KEY `YearTermID` (`YearTermID`) USING BTREE,
  CONSTRAINT `FK_CurriculumID` FOREIGN KEY (`CurriculumID`) REFERENCES `tblcurriculum` (`CurriculumID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_YearTermID` FOREIGN KEY (`YearTermID`) REFERENCES `tblyearlevelterm` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=184 DEFAULT CHARSET=utf8;
 
CREATE TABLE `tblsection` (
  `SectionID` VARCHAR(20) NOT NULL,
  `SectionTitle` VARCHAR(255) DEFAULT NULL,
  `YearLevelID` INT(11) DEFAULT '0',
  `TermID` INT(10) UNSIGNED DEFAULT NULL,
  `CampusID` VARCHAR(45) DEFAULT NULL,
  `CollegeID` VARCHAR(45) DEFAULT NULL,
  `CurriculumID` VARCHAR(45) DEFAULT NULL,
  `ProgramID` VARCHAR(45) DEFAULT NULL,
  `AdviserID` VARCHAR(45) DEFAULT NULL,
  `IsBlock` tinyint(3) UNSIGNED DEFAULT NULL,
  `RoomID` VARCHAR(45) DEFAULT NULL,
  `Limit` INT(10) UNSIGNED DEFAULT NULL,
  `IsEvening` tinyint(3) UNSIGNED DEFAULT NULL,
  `IsDissolved` tinyint(3) UNSIGNED DEFAULT NULL,
  `CreationDate` datetime DEFAULT NULL,
  `CreatedBy` VARCHAR(45) DEFAULT NULL,
  `ModifiedBy` VARCHAR(45) DEFAULT NULL,
  `ModifiedOn` datetime DEFAULT NULL,
  PRIMARY KEY (`SectionID`),
  KEY `YearLevelID` (`YearLevelID`),
  KEY `TermID` (`TermID`) USING BTREE,
  KEY `CampusID` (`CampusID`) USING BTREE,
  KEY `CollegeID` (`CollegeID`) USING BTREE,
  KEY `ProgramID` (`ProgramID`) USING BTREE,
  CONSTRAINT `FKTermID` FOREIGN KEY (`TermID`) REFERENCES `tblayterm` (`TermID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `tblclassschedule` (
  `SubjectOfferingID` VARCHAR(31) NOT NULL,
  `TermID` INT(20) NOT NULL,
  `SubjectID` VARCHAR(10) DEFAULT NULL,
  `SectionID` VARCHAR(20) DEFAULT NULL,
  `IsSpecialClasses` tinyint(1) DEFAULT NULL,
  `SchedTimeStart` INT(10) UNSIGNED DEFAULT NULL,
  `SchedTimeEnd` INT(10) UNSIGNED DEFAULT NULL,
  `TeacherID` VARCHAR(10) DEFAULT NULL,
  `RoomID` VARCHAR(50) DEFAULT NULL,
  `Days` VARCHAR(20) DEFAULT NULL,
  `EventID1` INT(10) UNSIGNED DEFAULT NULL,
  `Sched1` VARCHAR(45) DEFAULT NULL,
  `SchedTimeStart2` INT(10) UNSIGNED DEFAULT NULL,
  `SchedTimeEnd2` INT(10) UNSIGNED DEFAULT NULL,
  `TeacherID2` VARCHAR(45) DEFAULT NULL,
  `RoomID2` VARCHAR(45) DEFAULT NULL,
  `Days2` VARCHAR(45) DEFAULT NULL,
  `EventID2` INT(10) UNSIGNED DEFAULT NULL,
  `Sched2` VARCHAR(45) DEFAULT NULL,
  `SchedTimeStart3` INT(10) UNSIGNED DEFAULT NULL,
  `SchedTimeEnd3` INT(10) UNSIGNED DEFAULT NULL,
  `TeacherID3` VARCHAR(45) DEFAULT NULL,
  `RoomID3` VARCHAR(45) DEFAULT NULL,
  `Days3` VARCHAR(45) DEFAULT NULL,
  `EventID3` INT(10) UNSIGNED DEFAULT NULL,
  `Sched3` VARCHAR(45) DEFAULT NULL,
  `SchedTimeStart4` INT(10) UNSIGNED DEFAULT NULL,
  `SchedTimeEnd4` INT(10) UNSIGNED DEFAULT NULL,
  `TeacherID4` VARCHAR(45) DEFAULT NULL,
  `RoomID4` VARCHAR(45) DEFAULT NULL,
  `Days4` VARCHAR(45) DEFAULT NULL,
  `EventID4` INT(10) UNSIGNED DEFAULT NULL,
  `Sched4` VARCHAR(45) DEFAULT NULL,
  `SchedTimeStart5` INT(10) UNSIGNED DEFAULT NULL,
  `SchedTimeEnd5` INT(10) UNSIGNED DEFAULT NULL,
  `TeacherID5` VARCHAR(45) DEFAULT NULL,
  `RoomID5` VARCHAR(45) DEFAULT NULL,
  `Days5` VARCHAR(45) DEFAULT NULL,
  `EventID5` INT(10) UNSIGNED DEFAULT NULL,
  `Sched5` VARCHAR(45) DEFAULT NULL,
  `OverRideConflict` tinyint(4) DEFAULT NULL,
  `IsDissolved` tinyint(4) DEFAULT NULL,
  `PostedBy` VARCHAR(45) DEFAULT NULL,
  `DatePosted` VARCHAR(45) DEFAULT NULL,
  `RoomPostedBy` VARCHAR(45) DEFAULT NULL,
  `RoomDatePosted` VARCHAR(20) DEFAULT NULL,
  `FacultyDatePosted` VARCHAR(45) DEFAULT NULL,
  `FacultyPostedBy` VARCHAR(45) DEFAULT NULL,
  `CreationDate` VARCHAR(20) DEFAULT NULL,
  `CreatedBy` VARCHAR(70) DEFAULT NULL,
  `ModifiedDate` VARCHAR(45) DEFAULT NULL,
  `ModifiedBy` VARCHAR(70) DEFAULT NULL,
  PRIMARY KEY (`SubjectOfferingID`),
  KEY `SubjectID` (`SubjectID`),
  KEY `SectionID` (`SectionID`) USING BTREE,
  KEY `TermID` (`TermID`) USING BTREE,
  CONSTRAINT `FKSectionID` FOREIGN KEY (`SectionID`) REFERENCES `tblsection` (`SectionID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_SubjectID` FOREIGN KEY (`SubjectID`) REFERENCES `tblsubject` (`SubjectID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

==================STORED PROCEDURE ==============================

CREATE DEFINER = 'root'@'localhost' PROCEDURE `GetClassScheduleConflicts`(
        IN TermID INTEGER,
        IN SectionID VARCHAR(45),
        IN sDay VARCHAR(10),
        IN TimeStart VARCHAR(10),
        IN TimeEnd VARCHAR(10)
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
 
BEGIN
        SELECT  CS.SubjectOfferingID, CS.TermID, CS.SubjectID, S.SubjectCode, S.SubjectTitle, S.CreditUnits,
                CS.SectionID, CSec.SectionName,
                CS.Sched1, fnRoomName2(RoomID) AS Room_1,
                CS.Sched2, fnRoomName2(RoomID2) AS Room_2,
                CS.Sched3, fnRoomName2(RoomID3) AS Room_3,
                CS.Sched4, fnRoomName2(RoomID4) AS Room_4,
                CS.Sched5, fnRoomName2(RoomID4) AS Room_5,
                CS.Days1,CS.Days2,CS.Days3,CS.Days4,CS.Days5,
                fnCollegeCode(CSec.CollegeID) AS CollegeCode,
                fnProgramCode(CSec.ProgramID) AS ProgramCode
        FROM   tblClassSchedule AS CS LEFT JOIN
                tblSubject AS S ON S.SubjectID = CS.SubjectID LEFT JOIN
                tblSection AS CSec ON CSec.SectionID = CS.SectionID    
        WHERE (CS.TermID = TermID) AND (CS.SectionID = SectionID)
            AND (
                ( (CS.Days1 LIKE sDay) AND ((CS.SchedTimeStart BETWEEN TimeStart AND TimeEnd) OR (CS.SchedTimeEnd BETWEEN TimeStart AND TimeEnd) OR ((CS.SchedTimeEnd > TimeStart AND CS.SchedTimeEnd
                    AND ((CS.SchedTimeEnd > TimeStart AND CS.SchedTimeEnd
                )
            OR
                ( (CS.Days2 LIKE sDay) AND ((CS.SchedTimeStart2 BETWEEN TimeStart AND TimeEnd) OR (CS.SchedTimeEnd2 BETWEEN TimeStart AND TimeEnd) OR ((CS.Time2_End > TimeStart AND CS.SchedTimeEnd2
                    AND ((CS.SchedTimeEnd2 > TimeStart AND CS.Time2_End
                )
            OR
                ( (CS.Days3 LIKE sDay) AND ((CS.SchedTimeStart3 BETWEEN TimeStart AND TimeEnd) OR (CS.SchedTimeEnd3 BETWEEN TimeStart AND TimeEnd) OR ((CS.Time3_End > TimeStart AND CS.SchedTimeEnd3
                    AND ((CS.SchedTimeEnd3 > TimeStart AND CS.SchedTimeEnd3
                )
            OR
                ( (CS.Days4 LIKE sDay) AND ((CS.SchedTimeStart4 BETWEEN TimeStart AND TimeEnd) OR (CS.SchedTimeEnd4 BETWEEN TimeStart AND TimeEnd) OR ((CS.Time4_End > TimeStart AND CS.SchedTimeEnd4
                    AND ((CS.SchedTimeEnd4 > TimeStart AND CS.SchedTimeEnd4
                )
            OR
                ( (CS.Days5 LIKE sDay) AND ((CS.SchedTimeStart5 BETWEEN TimeStart AND TimeEnd) OR (CS.SchedTimeEnd5 BETWEEN TimeStart AND TimeEnd) OR ((CS.Time5_End > @TimeStart AND CS.SchedTimeEnd5
                    AND ((CS.SchedTimeEnd5 > TimeStart AND CS.SchedTimeEnd5
                )
                );
END;
 
CREATE DEFINER = 'root'@'localhost' PROCEDURE `GetFacultyScheduleConflicts`(
        TermID  INT,
        FacultyID  VARCHAR(15),
        sDay  VARCHAR(10),
        TimeStart VARCHAR(10),
        TimeEnd VARCHAR(10)
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
 
BEGIN
    SELECT  SubjectOfferingID, CS.TermID, CS.SubjectID, S.SubjectCode, S.SubjectTitle, S.CreditUnits, CSec.SectionTitle,
            CS.Sched1, fnRoomName2(CS.RoomID) AS Room_1,
            CS.Sched2, fnRoomName2(CS.RoomID2) AS Room_2,
            CS.Sched3, fnRoomName2(CS.RoomID3) AS Room_3,
            CS.Sched4, fnRoomName2(CS.RoomID4) AS Room_4,
            CS.Sched5, fnRoomName2(CS.RoomID5) AS Room_5,
            fnProgramCode(CSec.ProgramID) AS ProgramCode
    FROM tblClassSchedule AS CS LEFT JOIN
            tblSubject AS S ON S.SubjectID = CS.SubjectID LEFT JOIN
            tblSection AS CSec ON CSec.SectionID = CS.SectionID
    WHERE   (CS.TermID = TermID) AND
            (CS.TeacherID = FacultyID) AND
            (CS.Days LIKE sDay) AND
            ((SchedTimeStart BETWEEN TimeStart AND TimeEnd) OR (SchedTimeEnd BETWEEN TimeStart AND TimeEnd)
            OR ((SchedTimeEnd > TimeStart AND SchedTimeEnd
            AND ((SchedTimeEnd > TimeStart AND SchedTimeEnd
 
        /*----------------------------------*/     
    UNION -- SCHEDULE.1 TO SCHEDULE.2
    /*----------------------------------*/
 
    SELECT  SubjectOfferingID, CS.TermID, CS.SubjectID, S.SubjectCode, S.SubjectTitle, S.CreditUnits, CSec.SectionTitle,
            CS.Sched1, fnRoomName2(CS.RoomID) AS Room_1,
            CS.Sched2, fnRoomName2(CS.RoomID2) AS Room_2,
            CS.Sched3, fnRoomName2(CS.RoomID3) AS Room_3,
            CS.Sched4, fnRoomName2(CS.RoomID4) AS Room_4,
            CS.Sched5, fnRoomName2(CS.RoomID5) AS Room_5,
            fnProgramCode(CSec.ProgramID) AS ProgramCode
    FROM    tblClassSchedule AS CS LEFT JOIN
            tblSubject AS S ON S.SubjectID = CS.SubjectID LEFT JOIN
            tblSection AS CSec ON CSec.SectionID = CS.SectionID
    WHERE   (CS.TermID = TermID) AND
            (CS.TeacherID2 = FacultyID) AND
            (Days2 LIKE sDay) AND
            ((SchedTimeStart2 BETWEEN TimeStart AND TimeEnd) OR (SchedTimeEnd2 BETWEEN TimeStart AND TimeEnd)
            OR ((SchedTimeEnd2 > TimeStart AND SchedTimeEnd2
            AND ((SchedTimeEnd2 > TimeStart AND SchedTimeEnd2
 
        /*----------------------------------*/       
    UNION -- SCHEDULE.2 TO SCHEDULE.3
        /*----------------------------------*/
 
    SELECT  SubjectOfferingID, CS.TermID, CS.SubjectID, S.SubjectCode, S.SubjectTitle, S.CreditUnits, CSec.SectionTitle,
            CS.Sched1, fnRoomName2(CS.RoomID) AS Room_1,
            CS.Sched2, fnRoomName2(CS.RoomID2) AS Room_2,
            CS.Sched3, fnRoomName2(CS.RoomID3) AS Room_3,
            CS.Sched4, fnRoomName2(CS.RoomID4) AS Room_4,
            CS.Sched5, fnRoomName2(CS.RoomID5) AS Room_5,
            fnProgramCode(CSec.ProgramID) AS ProgramCode
    FROM    tblClassSchedule AS CS LEFT JOIN
            tblSubject AS S ON S.SubjectID = CS.SubjectID LEFT JOIN
            tblSection AS CSec ON CSec.SectionID = CS.SectionID
    WHERE   (CS.TermID = TermID) AND
            (CS.TeacherID3 = FacultyID) AND
            (Days3 LIKE sDay) AND
            ((SchedTimeStart3 BETWEEN TimeStart AND TimeEnd) OR (SchedTimeEnd3 BETWEEN TimeStart AND TimeEnd)
            OR ((SchedTimeEnd3 > TimeStart AND SchedTimeEnd3
            AND ((SchedTimeEnd3 > TimeStart AND SchedTimeEnd3
 
        /*----------------------------------*/   
    UNION -- SCHEDULE.3 TO SCHEDULE.4
        /*----------------------------------*/
 
     SELECT  SubjectOfferingID, CS.TermID, CS.SubjectID, S.SubjectCode, S.SubjectTitle, S.CreditUnits, CSec.SectionTitle,
            CS.Sched1, fnRoomName2(CS.RoomID) AS Room_1,
            CS.Sched2, fnRoomName2(CS.RoomID2) AS Room_2,
            CS.Sched3, fnRoomName2(CS.RoomID3) AS Room_3,
            CS.Sched4, fnRoomName2(CS.RoomID4) AS Room_4,
            CS.Sched5, fnRoomName2(CS.RoomID5) AS Room_5,
            fnProgramCode(CSec.ProgramID) AS ProgramCode
    FROM    tblClassSchedule AS CS LEFT JOIN
            tblSubject AS S ON S.SubjectID = CS.SubjectID LEFT JOIN
            tblSection AS CSec ON CSec.SectionID = CS.SectionID
    WHERE   (CS.TermID = TermID) AND
            (CS.TeacherID4 = FacultyID) AND
            (Days4 LIKE sDay) AND
            ((SchedTimeStart4 BETWEEN TimeStart AND TimeEnd) OR (SchedTimeEnd4 BETWEEN TimeStart AND TimeEnd)
            OR ((SchedTimeEnd4 > TimeStart AND SchedTimeEnd4
            AND ((SchedTimeEnd4 > TimeStart AND SchedTimeEnd4
 
        /*----------------------------------*/       
    UNION -- SCHEDULE.4 TO SCHEDULE.5
        /*----------------------------------*/
 
     SELECT  SubjectOfferingID, CS.TermID, CS.SubjectID, S.SubjectCode, S.SubjectTitle, S.CreditUnits, CSec.SectionTitle,
            CS.Sched1, fnRoomName2(CS.RoomID) AS Room_1,
            CS.Sched2, fnRoomName2(CS.RoomID2) AS Room_2,
            CS.Sched3, fnRoomName2(CS.RoomID3) AS Room_3,
            CS.Sched4, fnRoomName2(CS.RoomID4) AS Room_4,
            CS.Sched5, fnRoomName2(CS.RoomID5) AS Room_5,
            fnProgramCode(CSec.ProgramID) AS ProgramCode
    FROM    tblClassSchedule AS CS LEFT JOIN
            tblSubject AS S ON S.SubjectID = CS.SubjectID LEFT JOIN
            tblSection AS CSec ON CSec.SectionID = CS.SectionID
    WHERE   (CS.TermID = TermID) AND
            (CS.TeacherID5 = FacultyID) AND
            (Days5 LIKE sDay) AND
            ((SchedTimeStart5 BETWEEN TimeStart AND TimeEnd) OR (SchedTimeEnd5 BETWEEN TimeStart AND TimeEnd)
            OR ((SchedTimeEnd5 > TimeStart AND SchedTimeEnd5
            AND ((SchedTimeEnd5 > TimeStart AND SchedTimeEnd5
END;
 
CREATE DEFINER = 'root'@'localhost' PROCEDURE `GetRoomScheduleConflicts`(
        TermID  INT,
        RoomID  VARCHAR(15),
        sDay  VARCHAR(10),
        TimeStart VARCHAR(10),
        TimeEnd VARCHAR(10)
    )
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
 
BEGIN
 
    SELECT  SubjectOfferingID, CS.TermID, CS.SubjectID, S.SubjectCode, S.SubjectTitle, S.CreditUnits, CSec.SectionTitle,
            CS.Sched1, fnRoomName2(CS.RoomID) AS Room_1,
            CS.Sched2, fnRoomName2(CS.RoomID2) AS Room_2,
            CS.Sched3, fnRoomName2(CS.RoomID3) AS Room_3,
            CS.Sched4, fnRoomName2(CS.RoomID4) AS Room_4,
            CS.Sched5, fnRoomName2(CS.RoomID5) AS Room_5,
            fnProgramCode(CSec.ProgramID) AS ProgramCode
    FROM tblClassSchedule AS CS LEFT JOIN
            tblSubject AS S ON S.SubjectID = CS.SubjectID LEFT JOIN
            tblSection AS CSec ON CSec.SectionID = CS.SectionID
    WHERE   (CS.TermID = TermID) AND
            (CS.RoomID = RoomID) AND
            (CS.Days LIKE sDay) AND
            ((SchedTimeStart BETWEEN TimeStart AND TimeEnd) OR (SchedTimeEnd BETWEEN TimeStart AND TimeEnd)
            OR ((SchedTimeEnd > TimeStart AND SchedTimeEnd
            AND ((SchedTimeEnd > TimeStart AND SchedTimeEnd
 
        /*----------------------------------*/     
    UNION -- SCHEDULE.1 TO SCHEDULE.2
    /*----------------------------------*/
 
    SELECT  SubjectOfferingID, CS.TermID, CS.SubjectID, S.SubjectCode, S.SubjectTitle, S.CreditUnits, CSec.SectionTitle,
            CS.Sched1, fnRoomName2(CS.RoomID) AS Room_1,
            CS.Sched2, fnRoomName2(CS.RoomID2) AS Room_2,
            CS.Sched3, fnRoomName2(CS.RoomID3) AS Room_3,
            CS.Sched4, fnRoomName2(CS.RoomID4) AS Room_4,
            CS.Sched5, fnRoomName2(CS.RoomID5) AS Room_5,
            fnProgramCode(CSec.ProgramID) AS ProgramCode
    FROM    tblClassSchedule AS CS LEFT JOIN
            tblSubject AS S ON S.SubjectID = CS.SubjectID LEFT JOIN
            tblSection AS CSec ON CSec.SectionID = CS.SectionID
    WHERE   (CS.TermID = TermID) AND
            (CS.RoomID2 = RoomID) AND
            (Days2 LIKE sDay) AND
            ((SchedTimeStart2 BETWEEN TimeStart AND TimeEnd) OR (SchedTimeEnd2 BETWEEN TimeStart AND TimeEnd)
            OR ((SchedTimeEnd2 > TimeStart AND SchedTimeEnd2
            AND ((SchedTimeEnd2 > TimeStart AND SchedTimeEnd2
 
        /*----------------------------------*/       
    UNION -- SCHEDULE.2 TO SCHEDULE.3
        /*----------------------------------*/
 
    SELECT  SubjectOfferingID, CS.TermID, CS.SubjectID, S.SubjectCode, S.SubjectTitle, S.CreditUnits, CSec.SectionTitle,
            CS.Sched1, fnRoomName2(CS.RoomID) AS Room_1,
            CS.Sched2, fnRoomName2(CS.RoomID2) AS Room_2,
            CS.Sched3, fnRoomName2(CS.RoomID3) AS Room_3,
            CS.Sched4, fnRoomName2(CS.RoomID4) AS Room_4,
            CS.Sched5, fnRoomName2(CS.RoomID5) AS Room_5,
            fnProgramCode(CSec.ProgramID) AS ProgramCode
    FROM    tblClassSchedule AS CS LEFT JOIN
            tblSubject AS S ON S.SubjectID = CS.SubjectID LEFT JOIN
            tblSection AS CSec ON CSec.SectionID = CS.SectionID
    WHERE   (CS.TermID = TermID) AND
            (CS.RoomID3 = RoomID) AND
            (Days3 LIKE sDay) AND
            ((SchedTimeStart3 BETWEEN TimeStart AND TimeEnd) OR (SchedTimeEnd3 BETWEEN TimeStart AND TimeEnd)
            OR ((SchedTimeEnd3 > TimeStart AND SchedTimeEnd3
            AND ((SchedTimeEnd3 > TimeStart AND SchedTimeEnd3
 
        /*----------------------------------*/   
    UNION -- SCHEDULE.3 TO SCHEDULE.4
        /*----------------------------------*/
 
     SELECT  SubjectOfferingID, CS.TermID, CS.SubjectID, S.SubjectCode, S.SubjectTitle, S.CreditUnits, CSec.SectionTitle,
            CS.Sched1, fnRoomName2(CS.RoomID) AS Room_1,
            CS.Sched2, fnRoomName2(CS.RoomID2) AS Room_2,
            CS.Sched3, fnRoomName2(CS.RoomID3) AS Room_3,
            CS.Sched4, fnRoomName2(CS.RoomID4) AS Room_4,
            CS.Sched5, fnRoomName2(CS.RoomID5) AS Room_5,
            fnProgramCode(CSec.ProgramID) AS ProgramCode
    FROM    tblClassSchedule AS CS LEFT JOIN
            tblSubject AS S ON S.SubjectID = CS.SubjectID LEFT JOIN
            tblSection AS CSec ON CSec.SectionID = CS.SectionID
    WHERE   (CS.TermID = TermID) AND
            (CS.RoomID4 = RoomID) AND
            (Days4 LIKE sDay) AND
            ((SchedTimeStart4 BETWEEN TimeStart AND TimeEnd) OR (SchedTimeEnd4 BETWEEN TimeStart AND TimeEnd)
            OR ((SchedTimeEnd4 > TimeStart AND SchedTimeEnd4
            AND ((SchedTimeEnd4 > TimeStart AND SchedTimeEnd4
 
 
        /*----------------------------------*/       
    UNION -- SCHEDULE.4 TO SCHEDULE.5
        /*----------------------------------*/
 
     SELECT  SubjectOfferingID, CS.TermID, CS.SubjectID, S.SubjectCode, S.SubjectTitle, S.CreditUnits, CSec.SectionTitle,
            CS.Sched1, fnRoomName2(CS.RoomID) AS Room_1,
            CS.Sched2, fnRoomName2(CS.RoomID2) AS Room_2,
            CS.Sched3, fnRoomName2(CS.RoomID3) AS Room_3,
            CS.Sched4, fnRoomName2(CS.RoomID4) AS Room_4,
            CS.Sched5, fnRoomName2(CS.RoomID5) AS Room_5,
            fnProgramCode(CSec.ProgramID) AS ProgramCode
    FROM    tblClassSchedule AS CS LEFT JOIN
            tblSubject AS S ON S.SubjectID = CS.SubjectID LEFT JOIN
            tblSection AS CSec ON CSec.SectionID = CS.SectionID
    WHERE   (CS.TermID = TermID) AND
            (CS.RoomID5 = RoomID) AND
            (Days5 LIKE sDay) AND
            ((SchedTimeStart5 BETWEEN TimeStart AND TimeEnd) OR (SchedTimeEnd5 BETWEEN TimeStart AND TimeEnd)
            OR ((SchedTimeEnd5 > TimeStart AND SchedTimeEnd5
            AND ((SchedTimeEnd5 > TimeStart AND SchedTimeEnd5
END;
 
 
/*=================== STORED FUNCTION ===========================*/
 
CREATE DEFINER = 'root'@'localhost' FUNCTION `fnProgramName`( ProgID INT )
    RETURNS VARCHAR(100) CHARSET utf8
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
 
BEGIN
 
    DECLARE ProgramName VARCHAR(100);
    SELECT Programs.ProgName INTO ProgramName  FROM  tblPrograms AS Programs WHERE Programs.ProgID = ProgID
    LIMIT 1;
 
    RETURN ifnull(ProgramName,'');
END;
 
CREATE DEFINER = 'root'@'localhost' FUNCTION `fnRoomName2`(
        sRoomID VARCHAR(45)
    )
    RETURNS VARCHAR(60) CHARSET utf8
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
 
BEGIN
 
    DECLARE RoomName VARCHAR(60);
    SELECT CONCAT(fnBuildingAcronym(R.BldgID) , '-' , R.Room) INTO RoomName FROM tblRoom AS R
    WHERE R.RoomID = sRoomID LIMIT 1;
    RETURN RoomName;
END;

Tags:

Class Scheduling - MySQL

MYSQL

class scheduling system

Article by itachi_philip

itachi_philip has submitted 30 source code / articles.

If you like This post, you can follow SourceCodester on Twitter.

Subscribe to SourceCodester feed via RSS or EMAIL to receive instant updates.

Comments

Normalization

Permalink Submitted by Oelasor on Mon, 08/20/2012 - 07:44

No offense, the author should have normalized some tables in the structure. The normalization process eliminates data redundancies and ensures logical data dependencies. For more on information on normalization, please try visit this address: http://databases.about.com/od/specificproducts/a/normalization.htm. Hope you find this helpful... magandang araw :)

Thank you for your suggestion

Permalink Submitted by itachi_philip on Mon, 08/20/2012 - 22:08

Thank you for your suggestion...By the way Alam ko naman yung normalization... The truth behind normalization is hindi naman mag.eliminates ng data redundancies, kung hindi breaking down data warehousing to ensures logical data dependencies. Most of widely used software's today, most of them never apply normalization dahil ito ang pinagmumulan na tinatawag nating USER ERROR... Based on our experience, in providing School Management Solutions in the entire Philippines, NORMALIZATION is maririnig mo lang yan sa mga School, pero in Applied Software Engineering... bihira ka lang makakita ng mga ganyan.... =)

false

Permalink Submitted by imjapz on Sat, 09/15/2012 - 10:39

http://stackoverflow.com/questions/3752564/how-important-is-database-normalization-in-a-very-simple-database

Database

Permalink Submitted by Anonymous on Tue, 09/04/2012 - 10:10

Actually the tblClassSchedule needs some tweaking,
first things first, you should simplify the database structure for you to have a manageable records in which it will have the flexibility without overdoing it.

Though i got your logic with the current table design, the consumption in memory of your stored procedure might produce some future errors. Fetching records will be slow as you pile-up records , try to study Database Modeling and Query Optimization.

I already did 5+ scheduling system in the past both with Windows and Web based applications. (ASP.NET C#,C#.VB.NET,VB.6).

As we all know, the the records per semester might eat around 50 to 200 mb or even more depends on the other records.

imaginage a 5g of records after only 2 or 5 years of usage.

Class Scheduling System Table Structure

Article by itachi_philip

Comments

Normalization

Thank you for your suggestion

false

Database

Add new comment

Filtered HTML

Plain text

Share Your Source Code or Article

Sponsor

Popular Source Code (Today)

Random Source Code

New forum topics

Receive Free Updates

Connect on Facebook

Book navigation

Stats

You are here

Class Scheduling System Table Structure

Article by itachi_philip

Comments

Normalization

Thank you for your suggestion

false

Database

Add new comment

Filtered HTML

Plain text

Share Your Source Code or Article

Sponsor

Popular Source Code (Today)

Random Source Code

New forum topics

Receive Free Updates

Connect on Facebook

Book navigation

Stats