-
Nov 27th, 2016, 10:39 AM
#1
Thread Starter
Member
How To Add Class To Table InstructorCourses
I need to make schedule for Instructor include
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
Code:
CREATE TABLE [dbo].[Courses](
[CourseID] [int] IDENTITY(1,1) NOT NULL,
[CourseName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_dbo.Courses] PRIMARY KEY CLUSTERED
(
[CourseID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Class](
[ClassID] [int] IDENTITY(1,1) NOT NULL,
[ClassName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_dbo.Class] PRIMARY KEY CLUSTERED
(
[ClassID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Instructor](
[InstructorID] [int] IDENTITY(1,1) NOT NULL,
[IstructorName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_dbo.Instructor] PRIMARY KEY CLUSTERED
(
[InstructorID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[InstructorCourses](
[CourseID] [int] NOT NULL,
[InstructorID] [int] NOT NULL,
[fromtime] [nvarchar](50) NULL,
[totime] [nvarchar](50) NULL,
[day] [nvarchar](50) NULL,
CONSTRAINT [PK_dbo.InstructorCourses] PRIMARY KEY CLUSTERED
(
[CourseID] ASC,
[InstructorID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Instructor_Class](
[ClassID] [int] NOT NULL,
[InstructorID] [int] NOT NULL,
CONSTRAINT [PK_dbo.Instructor_Class] PRIMARY KEY CLUSTERED
(
[ClassID] ASC,
[InstructorID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) 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
-
Nov 29th, 2016, 02:23 AM
#2
Re: How To Add Class To Table InstructorCourses
not clear to me. you have instructors, classes and courses. classes i understand more as a 'location'. so you have one instructor having many courses and each course in only one class(room)?
if the above sentence is not correct, make it correct and you will know how you should set up your database. it is not logical (at least as i understand it) if courses can be in multiple classrooms, that would only make sense if a course has multipe coursedates and each in a different classroom....
Last edited by digitalShaman; Nov 29th, 2016 at 02:26 AM.
-
Nov 29th, 2016, 08:47 AM
#3
Re: How To Add Class To Table InstructorCourses
eeeeh.
I'm not sure the structure is right....
Instructor stands alone... that's fine.
Courses are broad and general... like English. Or Programming 101. They should probably stand alone as well.
Classes are an instance of that course... Programming 101, MWF, 1:30pm
Classes are in a Location like PS203
Classes are taught by an instructor
I'd probably have an Instructor table, a Course table, a Location table, a Class table (with an FKey to Course table, an FKey to Location table, and an FKey to the Instructor table)
4 tables.
Instructor - Prof Smith
Course - Programming 101
Location - PS203
Class - Programming 101 on MWF @ 1:30pm in PS203 w/ Prof Smith
-tg
-tg
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|