ahmed salah

ahmed salah

  • 1.6k
  • 530
  • 122.9k

How to join section table with class or course or instructor

Nov 29 2016 4:03 PM

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