You must Sign In to post a response.
  • Category: SQL Server

    How To Add Days And Time For Instructor Schedule

    Problem

    I need to make design database(ERD) for displaying schedule for every instructor

    include

    day of course(Saturday or Monday,etc)

    And which time it will give course(period FROM 12 PM TO 03 PM ) .

    And what course it will give(c# or SQL) .

    Example

    Instructor martin give course c# in Monday and Saturday for period 12 pm - 03 pm

    And SQL in Sunday and Wednesday for period 12 pm - 03 pm .

    Details

    So that i designed the following tables :

    Instructors table (InstractorID,InstractorName)

    Courses table (CourseID,CourseName)

    Instructors_courses table (instcourseID,InstractorID,CourseID)

    And relation between instructors table and Courses table many to many so that I do another table is Instructors_Courses table .

    My questions are

    1- How to represent days and time for every course added

    Can i add table for time and table for days and make relation with

    Instructors_courses table by adding day id and time id (one to many)

    OR do it programming from user interface .

    2- course start date and course end date these two fields how to represent in

    table Instructors_courses table .

    I can added but it will repeated with every course are this correct or what .
  • #768236
    Hi ,

    In table => Instructors_courses , you can add 3 columns for Course Start Date, StartTime and EndTime.Then you can make join between these these three tables.

  • #768281
    you can implement a CHECK constraint to enforce the logic to make design database(ERD) for displaying schedule for every instructor
    CREATE FUNCTION [dbo].[CheckScheduledCourseDoesNotOverlap](
    @course INT,
    @instructor INT,
    @startOn DATETIME2(0),
    @endOn DATETIME(0)
    )
    RETURNS BIT
    AS
    BEGIN
    DECLARE @overlaps BIT = 0;
    IF EXISTS(SELECT * FROM [dbo].[ScheduledInstructor]
    WHERE [Course] = @course
    AND [Instructor] = @instructor
    AND(@startOn BETWEEN [StartOn] AND [EndOn] OR @endOn BETWEEN [StartOn] AND [EndOn]))
    BEGIN
    SET @overlap = 1;
    END
    RETURN @overlap;
    END
    GO


Sign In to post your comments