sql server - How to join section table with ERDigram

Question

Problem

Which is correct when join section table with class table OR with course table OR with instructor Table .

Details

section is group of student classified to ( aa,bb,cc )can take one course or more courses.

section can teach in one or more class(lab or class room) .

Instructor can teach to more sections and section can have more instructor raltion is many to many and made in third table Inst_Course

My ER diagram as following :

section table join

Database Schema as following :

CREATE TABLE [dbo].[Instructor](
    [InstructorID] [int] NOT NULL,
    [InstructorName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Instructor] PRIMARY KEY CLUSTERED 
(
    [InstructorID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Course](
    [CourseID] [int] NOT NULL,
    [CourseName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED 
(
    [CourseID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Class](
    [ClassID] [int] NOT NULL,
    [ClassName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED 
(
    [ClassID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Section](
    [SectionID] [int] NOT NULL,
    [SectionName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Section] PRIMARY KEY CLUSTERED 
(
    [SectionID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Inst_Course](
    [InstID] [int] NOT NULL,
    [CourseID] [int] NOT NULL,
 CONSTRAINT [PK_Inst_Course] PRIMARY KEY CLUSTERED 
(
    [InstID] ASC,
    [CourseID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Course_Class](
    [ClassID] [int] NOT NULL,
    [CourseID] [int] NOT NULL,
    [Fromtime] [int] NULL,
    [Totime] [int] NULL,
    [day] [nvarchar](50) NULL,
 CONSTRAINT [PK_Course_Class] PRIMARY KEY CLUSTERED 
(
    [ClassID] ASC,
    [CourseID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Relation between tables as following :

Class table and courses table has many to many relation ship in tableCourse_Class .

Instructor table and courses table has relation many to many in table Inst_Course .

Section is have many to many with instructor table and course table and class table which is correct for join section with instructor or course or class

Notes :this diagram not have student courses table because the goal from diagram is design schedule for instructor .

sample data

Sample data to table Course_Class for instructor schedule

join between tables as following :

SELECT     dbo.Class.ClassName, dbo.Course_Class.CourseID, dbo.Course_Class.Fromtime, dbo.Course_Class.Totime, dbo.Course_Class.day, dbo.Course.CourseName, 
                      dbo.Inst_Course.InstID, dbo.Inst_Course.CourseID AS Expr3, dbo.Instructor.InstructorID, dbo.Instructor.InstructorName
FROM         dbo.Class INNER JOIN
                      dbo.Course_Class ON dbo.Class.ClassID = dbo.Course_Class.ClassID INNER JOIN
                      dbo.Course ON dbo.Course_Class.CourseID = dbo.Course.CourseID INNER JOIN
                      dbo.Inst_Course ON dbo.Course.CourseID = dbo.Inst_Course.CourseID INNER JOIN
                      dbo.Instructor ON dbo.Inst_Course.InstID = dbo.Instructor.InstructorID
WHERE     (dbo.Inst_Course.InstID = 1)

Question is :Actually what i need is which table must join with section table class or course or instructor tables

Update

Class in my case represent classroom or lab meaning class is the place teach courses in it Section :(group of student)represent who teaches .

I can take course c# in class 1 meaning lab 1 or lab 2 or lab 3 and in lab1 i can get course c# OR c++ OR java in my case .

Here i treat with section to represent group of students .

section can teach more courses c# and c++ and java .

c# course can have more section aa,bb,cc .

Update2

student participate in one section only and cannot more section meaning relation one to many .

relation between section and class is many to many because section aa can take course c# in class a and class bb

and class bb can have course c# and c++

if you mean by session is course you are right .

Courses teaches in more classes in different times from 9-11,11-1,1-3,3-4.30 in different classes .

courses include more sections and every section can have more course

Answer 1

OK, updated based upon your update, I'd suggest you have the following structures:

create table dbo.Instructors (
    InstructorID int identity(1,1) not null ,
        constraint pkc_Instructors
            primary key clustered ( InstructorID ) ,
    InstructorName nvarchar(48) not null ,
        constraint uni_InstructorName@Instructors
            unique nonclustered ( InstructorName )
    )

create table dbo.Courses (
    CourseID int identity(1,1) not null ,
        constraint pkc_Courses
            primary key clustered ( CourseID ) ,
    CourseName nvarchar(48) not null ,
        constraint uni_CourseName@Courses
            unique nonclustered ( CourseName )
    )

create table dbo.ClassRooms (
    ClassRoomID int identity(1,1) not null ,
        constraint pkc_ClassRooms
            primary key clustered ( ClassRoomID ) ,
    ClassRoomName nvarchar(48) not null ,
        constraint uni_ClassRoomName@ClassRooms
            unique nonclustered ( ClassRoomName )
    )

create table dbo.Sections (
    SectionID int identity(1,1) not null ,
        constraint pkc_Sections
            primary key clustered ( SectionID ) ,
    CourseID int not null ,
        constraint fk_CourseID@Sections
            foreign key ( CourseID )
            references dbo.Courses ( CourseID ) ,
    SectionName nvarchar(48) not null ,
        constraint uni_SectionName@Sections
            unique nonclustered ( SectionName )
    )

create table dbo.StudentSections (
    StudentSectionID int identity(1,1) not null ,
        constraint pkn_StudentSections
            primary key nonclustered ( StudentSectionID ) ,
    StudentID int not null ,
        constraint fk_StudentID@StudentSections
            foreign key ( StudentID )
            references dbo.Students ( StudentID ) ,
    SectionID int not null ,
        constraint fk_SectionID@StudentSections
            foreign key ( SectionID )
            references dbo.Sections ( SectionID ) ,
        constraint uci_StudentID_SectionID@StudentSections
            unique clustered ( StudentID , SectionID )
    )

create table dbo.SectionClassRooms (
    SectionClassRoomID int identity(1,1) not null ,
        constraint pkn_SectionClassRooms
            primary key nonclustered ( SectionClassRoomID ) ,
    SectionID int not null ,
        constraint fk_SectionID@SectionClassRooms
            foreign key ( SectionID )
            references dbo.Sections ( SectionID ) ,
    ClassRoomID int not null ,
        constraint fk_ClassRoomID@SectionClassRooms
            foreign key ( ClassRoomID )
            references dbo.ClassRooms ( ClassRoomID ) ,
        constraint uci_SectionID_ClassRoomID@SectionClassRooms
            unique clustered ( SectionID , ClassRoomID )
    )

create table dbo.InstructorSections (
    InstructorSectionID int identity(1,1) not null ,
        constraint pkn_InstructorSections
            primary key nonclustered ( InstructorSectionID ) ,
    InstructorID int not null ,
        constraint fk_InstructorID@InstructorSections
            foreign key ( InstructorID )
            references dbo.Instructors ( InstructorID ) ,
    SectionID int not null ,
        constraint fk_SectionID@InstructorSections
            foreign key ( SectionID )
            references dbo.Sections ( SectionID ) ,
        constraint uci_InstructorID_SectionID@InstructorSections
            unique clustered ( InstructorID , SectionID )
    )

What this says:

• Instructors are their own entities, with each instructor having a unique name.
• Courses are similar, with uniqueness on their name.
• ClassRooms, ditto.
• Sections must be a section of a Course and cannot exist in isolation.
• Students participate in one or more Sections.
• Sections may take place in one or more ClassRooms (if this is not true, then we need to make ClassRoomID an attribute of each Section, rather than putting this in its own table SectionClassRooms).
• An Instructor may teach many Sessions and also a Session may be taught by multiple Instructors (if this is not the case, then InstructorID should be an attribute of Sessions).

Obviously, you're going to have a few more fields in some of these, as needed. For example: when do the Sessions take place? Does a Session have the same time regardless of the classroom in which it takes place? There are nuances here, I'm sure.

Lastly, I'd encourage you to review the clustered vs nonclustered indexing, as this should be optimized for how your information will be retrieved. Not critical, but I made a stab at how I thought it should work, not knowing your application requirements.

I'll modify this again if needed.