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
01 | CREATE TABLE [dbo].[Courses]( |
02 | [CourseID] [int] IDENTITY(1,1) NOT NULL, |
03 | [CourseName] [nvarchar](50) NOT NULL, |
04 | CONSTRAINT [PK_dbo.Courses] PRIMARY KEYCLUSTERED |
05 | ( |
06 | [CourseID] ASC |
07 | )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] |
08 | ) ON [PRIMARY] |
09 |
10 | CREATE TABLE [dbo].[Class]( |
11 | [ClassID] [int] IDENTITY(1,1) NOT NULL, |
12 | [ClassName] [nvarchar](50) NOT NULL, |
13 | CONSTRAINT [PK_dbo.Class] PRIMARY KEY CLUSTERED |
14 | ( |
15 | [ClassID] ASC |
16 | )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] |
17 | ) ON [PRIMARY] |
18 |
19 | CREATE TABLE [dbo].[Instructor]( |
20 | [InstructorID] [int] IDENTITY(1,1) NOT NULL, |
21 | [IstructorName] [nvarchar](50) NOT NULL, |
22 | CONSTRAINT [PK_dbo.Instructor] PRIMARY KEYCLUSTERED |
23 | ( |
24 | [InstructorID] ASC |
25 | )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] |
26 | ) ON [PRIMARY] |
27 |
28 | CREATE TABLE [dbo].[InstructorCourses]( |
29 | [CourseID] [int] NOT NULL, |
30 | [InstructorID] [int] NOT NULL, |
31 | [fromtime] [nvarchar](50) NULL, |
32 | [totime] [nvarchar](50) NULL, |
33 | [day] [nvarchar](50) NULL, |
34 | CONSTRAINT [PK_dbo.InstructorCourses] PRIMARYKEY CLUSTERED |
35 | ( |
36 | [CourseID] ASC, |
37 | [InstructorID] ASC |
38 | )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] |
39 | ) ON [PRIMARY] |
40 | CREATE TABLE [dbo].[Instructor_Class]( |
41 | [ClassID] [int] NOT NULL, |
42 | [InstructorID] [int] NOT NULL, |
43 | CONSTRAINT [PK_dbo.Instructor_Class] PRIMARYKEY CLUSTERED |
44 | ( |
45 | [ClassID] ASC, |
46 | [InstructorID] ASC |
47 | )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] |
48 | ) 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
ahmed salahPosted Nov 27, 2016, 4:00 PM
Bikesh SrivastavaPosted Nov 27, 2016, 3:01 PM