How to add class to table instructorcourses

Question

0.00/5 (No votes)

See more:

, +

I need to make schedule for Instructor include

day,time,date time,courses,classes(lab or class room),instructor

so that i designed my database as following

my relations as following

Instructor with courses many to many

class with instructor many to many

Relation between class and instructor many to many because

instructor can teach in more classroom and class room can have

more instructor

SQL

CREATE TABLE [dbo].[Courses](

    [CourseID] [int] IDENTITY(1,1) NOT NULL,

    [CourseName] [nvarchar](50) NOT NULL,

 CONSTRAINT [PK_dbo.Courses] PRIMARY KEY CLUSTERED

(

    [CourseID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

 

CREATE TABLE [dbo].[Class](

    [ClassID] [int] IDENTITY(1,1) NOT NULL,

    [ClassName] [nvarchar](50) NOT NULL,

 CONSTRAINT [PK_dbo.Class] PRIMARY KEY CLUSTERED

(

    [ClassID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

 

CREATE TABLE [dbo].[Instructor](

    [InstructorID] [int] IDENTITY(1,1) NOT NULL,

    [IstructorName] [nvarchar](50) NOT NULL,

 CONSTRAINT [PK_dbo.Instructor] PRIMARY KEY CLUSTERED

(

    [InstructorID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

 

CREATE TABLE [dbo].[InstructorCourses](

    [CourseID] [int] NOT NULL,

    [InstructorID] [int] NOT NULL,

    [fromtime] [nvarchar](50) NULL,

    [totime] [nvarchar](50)  NULL,

    [day] [nvarchar](50) NULL,

 CONSTRAINT [PK_dbo.InstructorCourses] PRIMARY KEY CLUSTERED

(

    [CourseID] ASC,

    [InstructorID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[Instructor_Class](

    [ClassID] [int] NOT NULL,

    [InstructorID] [int] NOT NULL,

 CONSTRAINT [PK_dbo.Instructor_Class] PRIMARY KEY CLUSTERED

(

    [ClassID] ASC,

    [InstructorID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

To make schedule for instructor i make relation between Instructor

table and Courses table many to many and generate third table

InstructorCourses table have InstructorID and CourseID

But
How to add ClassID to table InstructorCourses although Class table have relation many to many with table Instructor

What I have tried:

I try Add Class To Table InstructorCourses

Posted 27-Nov-16 5:07am

ahmed_sa

Updated 27-Nov-16 7:26am

Add a Solution

Comments

ahmed_sa 27-Nov-16 12:30pm

How to add class to instuctorcourse table although it have relation many to many with instructor table

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-11-27T07:11:00

From what I gather, the instructor relation to class isn't many-to-many for a single course. So should you simply have a table which contains classes related to a single row in InstructorCourses.

In other words a bit like following

Instructor
- Sam
- Tim

Courses
- C#
- Java

Classes
- Class 1
- Class 2

Instructor courses
- Sam, C#
- Tim, C#
- Tim, Java

Instructor course classes
- Sam, C#, Class 1
- Tim, C#, Class 2
- Tim, Java, Class 1 
- Tim, Java, Class 2

This is just a data example so of course you should have proper foreign keys pointing to correct tables and so on.

Tomas Takac · Answer 2 · 2016-11-27T07:26:00

If I understand it correctly then all three tables (instructor, course, class) participate in the schedule. In this case I would remove the many-to-many relationships you have now and connect all three entities using one associative table. This new table then can have the day & time of the course.

SQL

create table dbo.Schedule
(
    ScheduleId INT NOT NULL, -- PK
    InstructorId INT NOT NULL, -- FK to instructor = who
    CourseId INT NOT NULL, -- FK to course = what
    ClassId INT NOT NULL, -- FK to class = where
    [fromtime] [nvarchar](50) NULL, -- when
    [totime] [nvarchar](50)  NULL,
    [day] [nvarchar](50) NULL
)

How to add class to table instructorcourses

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0