Category: SQL Server

05 Nov 2016 05:44

Joined:	23/04/2012
Level:	Silver
Points:	104

Points:	5

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

07 Nov 2016 13:17

Joined:	13/01/2015
Level:	Silver
Points:	230

Points:	2

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.

Thanks!
Anjali Bansal

~Give your best and lead the world

#768281

11 Nov 2016 19:12

Joined:	30/04/2012
Level:	Gold
Points:	7233

Points:	4

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

How To Add Days And Time For Instructor Schedule

About dotnetspider.com

Quick Links

General

Services