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 :
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
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