问题描述
问题
我需要为培训中心的讲师安排.
我创建了表,但是我无法做的关系.
我在encertoder_course表班级表和节表之间有什么关系? (我必须从表类和表部分中有一个外键吗?我必须从表类和cloteD添加classID fk从表级别添加到表inst_courses表?)
表详细信息
我需要在周日至周四的一周内显示教师课程的时间表.
例如. 在周日2-4个时钟讲师米歇尔给教室里的C#课程a B节B
所以我创建了以下表:
Courses (like c#,access,SQL) Instructor(Teacher) Int_Courses Class( Lab or class room) Section(group of student take courses classified to a,b,c,c2)
我已经在教师表和课程之间建立了关系表和第三个表Inst_courses表中的许多人之间的关系.
结果预期
数据库架构
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, CONSTRAINT [PK_dbo.InstructorCourses] PRIMARY KEY CLUSTERED ( [CourseID] ASC, [InstructorID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[Section]( [SecID] [int] IDENTITY(1,1) NOT NULL, [SecName] [nvarchar](50) NOT NULL, [Active] [bit] NOT NULL, CONSTRAINT [PK_dbo.Section] PRIMARY KEY CLUSTERED ( [SecID] 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]
推荐答案
此报告的表格
此报告仅在"米歇尔"标识一位讲师时才有效.否则,您要么需要多个子报告,要么一个工作日的交叉点,要么需要多个报告.
报告的每一行和列的每个非空白子报告告诉您:instructor Michel teaches course C C in classroom in classroom CR CR to section S for department D ..
因此,该报告告诉您总体上与一张持有行的表相同的事情,其中:instructor Michel teaches course C C in classroom in classroom CR CR to section to section S for department D D in timeslot TS TS on weekday on weekday WD .请注意,我们如何将类似的多维报告的每一列和行添加到该表中的每个多维子报告中的表格中.
.可能您想要一张表格告诉您与所有教师的所有报告相同的事情:instructor I I teaches course C C in classroom in classroom CR to section S S for department D D in timeslot TS TS
(现在我们不必担心米歇尔是否识别一位讲师.)
该报告的第一个设计
讲师名称可能是非唯一的或不任职的.因此,将ID添加到名称并报告标题.您可能有更多有关讲师,课程和部门的数据.因此,为他们准备桌子.显然,部分编号仅在课程中唯一.
-- instructor ID is named NAME and ... Instructor(id, name, ...) CK(id) -- course NAME ... Course(name, ...) CK (name) -- department NAME ... Department(name, ...) CK (name) -- course C_NAME has section S_NUMBER Course_Has_Section(C_name, S_number) CK (C_name, S_number) FK(C_name) to Course -- instructor I_id teaches course C_NAME in classroom CR_NAME to section S_NUMBER -- for department D_NAME in timeslot TS_NAME on weekday WD_NAME Weekly_Lecture(I_id, C_name, CR_name, S_number, D_name, TS_name, WD_name) FK(I_id) to Instructor FK(C_name, S_number) to Course_Has_Section FK(D_name) to Department
您的设计细节会有所不同.也许课程和/或部门具有独特的代码.然后,您可以将它们用作FKS.然后添加一个表.显然,无论是什么意思,部分都可以活跃.
ck和归一化
给定的讲师,时段和工作日只能每周一次讲座.但是没有较小的子集.所以我们有Weekly_Lecture CK(I_id, TS_name, WD_name).给定的课程,部分,时隙和工作日只能每周一次讲座.但是没有较小的子集.所以我们有Weekly_Lecture CK(C_name, S_number, TS_name, WD_name).给定的教室,时隙和工作日只能每周进行一次讲座.但是没有较小的子集.所以我们有Weekly_Lecture CK(CR_name, TS_name, WD_name).
也许只能为一个部门教给定课程吗?也许给定的部分号码只能由给定的讲师教?通过识别所有FD(功能依赖性),我们确定所有CKS(候选密钥).然后归一化使用这些建议可能为基础表提出"更好的"选择.
问题描述
Problem
I need to make scheduale for instructor in training center.
I created tables but relation I cannot do.
What relation do I make between instructor_course table class table and section table? (Must I have a foreign key from table class and table section? Must I add ClassID FK from table class And SectionID from table section to table Inst_Courses Table?)
Table details
I need to show schedule for instructor courses within week from Sunday to Thursday.
E.g. In Sunday from 2 - 4 clock Instructor Michel give C# Course in ClassRoom A Section B
So I created following tables :
Courses (like c#,access,SQL) Instructor(Teacher) Int_Courses Class( Lab or class room) Section(group of student take courses classified to a,b,c,c2)
I already made relations between Instructor table and Courses table many to many in third table Inst_Courses table.
Result expected
Database Schema
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, CONSTRAINT [PK_dbo.InstructorCourses] PRIMARY KEY CLUSTERED ( [CourseID] ASC, [InstructorID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[Section]( [SecID] [int] IDENTITY(1,1) NOT NULL, [SecName] [nvarchar](50) NOT NULL, [Active] [bit] NOT NULL, CONSTRAINT [PK_dbo.Section] PRIMARY KEY CLUSTERED ( [SecID] 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]
推荐答案
A table for this report
This report only works if 'Michel' identifies one instructor. Otherwise either you need multiple sub-reports for one weekday-timeslot intersection or you need multiple reports.
Each non-blank sub-report at a row and column of your report tells you: instructor Michel teaches courseCin classroomCRto sectionSfor departmentD.
So the report tells you the same thing overall as a table holding the rows where: instructor Michel teaches courseCin classroomCRto sectionSfor departmentDin timeslotTSon weekdayWD. Notice how we take each column and row of a multi-dimensional report like this and add a column for it to the table for each multi-dimensional sub-report where they intersect.
Probably you want a table telling you the same thing as all reports for all instructors: instructorIteaches courseCin classroomCRto sectionSfor departmentDin timeslotTSon weekdayWD. Notice how we take a parameter in the title and add a column for it to the table.
(Now we don't need to worry about whether Michel identifies one instructor.)
A first design for this report
Instructor names are probably non-unique or non-permanent. So add ids to names and report titles. You probably have more data about instructors, courses and departments. So have tables for them. Apparently a section number is only unique within a course.
-- instructor ID is named NAME and ... Instructor(id, name, ...) CK(id) -- course NAME ... Course(name, ...) CK (name) -- department NAME ... Department(name, ...) CK (name) -- course C_NAME has section S_NUMBER Course_Has_Section(C_name, S_number) CK (C_name, S_number) FK(C_name) to Course -- instructor I_id teaches course C_NAME in classroom CR_NAME to section S_NUMBER -- for department D_NAME in timeslot TS_NAME on weekday WD_NAME Weekly_Lecture(I_id, C_name, CR_name, S_number, D_name, TS_name, WD_name) FK(I_id) to Instructor FK(C_name, S_number) to Course_Has_Section FK(D_name) to Department
Your design details will differ. Maybe courses and/or departments have unique codes. Then you might use them as FKs. Then add a table. Apparently a section can be active, whatever that means.
CKs and Normalizing
A given instructor, timeslot & weekday can only have one weekly lecture. But no smaller subset of those does. So we have Weekly_Lecture CK(I_id, TS_name, WD_name). A given course, section, timeslot & weekday can only have one weekly lecture. But no smaller subset of those does. So we have Weekly_Lecture CK(C_name, S_number, TS_name, WD_name). A given classroom, timeslot & weekday can only have one weekly lecture. But no smaller subset of those does. So we have Weekly_Lecture CK(CR_name, TS_name, WD_name).
Maybe a given course can only be taught for one department? Maybe a given section number can only be taught by a given instructor? By identifying all FDs (functional dependencies) we determine all CKs (candidate keys). Then normalization uses these to possibly suggest "better" choices for base tables.