0
Answer

How to join section table with class or course or instructor

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 :

  1. CREATE TABLE [dbo].[Instructor](  
  2.     [InstructorID] [int] NOT NULL,  
  3.     [InstructorName] [nvarchar](50) NULL,  
  4.  CONSTRAINT [PK_Instructor] PRIMARY KEY CLUSTERED   
  5. (  
  6.     [InstructorID] ASC  
  7. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]  
  8. ) ON [PRIMARY]  
  9.   
  10. CREATE TABLE [dbo].[Course](  
  11.     [CourseID] [int] NOT NULL,  
  12.     [CourseName] [nvarchar](50) NULL,  
  13.  CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED   
  14. (  
  15.     [CourseID] ASC  
  16. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]  
  17. ) ON [PRIMARY]  
  18.   
  19. CREATE TABLE [dbo].[Class](  
  20.     [ClassID] [int] NOT NULL,  
  21.     [ClassName] [nvarchar](50) NULL,  
  22.  CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED   
  23. (  
  24.     [ClassID] ASC  
  25. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]  
  26. ) ON [PRIMARY]  
  27.   
  28. CREATE TABLE [dbo].[Section](  
  29.     [SectionID] [int] NOT NULL,  
  30.     [SectionName] [nvarchar](50) NULL,  
  31.  CONSTRAINT [PK_Section] PRIMARY KEY CLUSTERED   
  32. (  
  33.     [SectionID] ASC  
  34. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]  
  35. ) ON [PRIMARY]  
  36.   
  37. CREATE TABLE [dbo].[Inst_Course](  
  38.     [InstID] [int] NOT NULL,  
  39.     [CourseID] [int] NOT NULL,  
  40.  CONSTRAINT [PK_Inst_Course] PRIMARY KEY CLUSTERED   
  41. (  
  42.     [InstID] ASC,  
  43.     [CourseID] ASC  
  44. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]  
  45. ) ON [PRIMARY]  
  46.   
  47. CREATE TABLE [dbo].[Course_Class](  
  48.     [ClassID] [int] NOT NULL,  
  49.     [CourseID] [int] NOT NULL,  
  50.     [Fromtime] [int] NULL,  
  51.     [Totime] [int] NULL,  
  52.     [day] [nvarchar](50) NULL,  
  53.  CONSTRAINT [PK_Course_Class] PRIMARY KEY CLUSTERED   
  54. (  
  55.     [ClassID] ASC,  
  56.     [CourseID] ASC  
  57. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]  
  58. ) 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 :

  1. SELECT     dbo.Class.ClassName, dbo.Course_Class.CourseID, dbo.Course_Class.Fromtime, dbo.Course_Class.Totime, dbo.Course_Class.day, dbo.Course.CourseName,   
  2.                       dbo.Inst_Course.InstID, dbo.Inst_Course.CourseID AS Expr3, dbo.Instructor.InstructorID, dbo.Instructor.InstructorName  
  3. FROM         dbo.Class INNER JOIN  
  4.                       dbo.Course_Class ON dbo.Class.ClassID = dbo.Course_Class.ClassID INNER JOIN  
  5.                       dbo.Course ON dbo.Course_Class.CourseID = dbo.Course.CourseID INNER JOIN  
  6.                       dbo.Inst_Course ON dbo.Course.CourseID = dbo.Inst_Course.CourseID INNER JOIN  
  7.                       dbo.Instructor ON dbo.Inst_Course.InstID = dbo.Instructor.InstructorID  
  8. 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