学生选课系统---数据库课程设计SQL Server
可以直接从我的GitHub中获取文档： 学生选课系统GitHub 一 题目 学生选课系统 二 需求分析 1.根据学生专业学年学期等信息，录入课程完成课程计划2.根据课程计划，录入任课教师信息3.学生可以根据学年学期等信息，选择课程完成选课要求 三 结构概念设计 计划受众表信息学生表信息教师表信息课程表信息教学计划表信息任课表信息学生选课表信息 四 ER图（基于三级范式） 这里我遇到一个问题如何设计ER图 和如何根据ER图设计关系逻辑呢？ 数据库设计方法 看了以上这篇 收益很多 可以参考下 五 数据字典 Stdunt表（学生表） 字段名 数据类型 长度 约束 描述 字段名 数据类型 长度 约束 描述 Student_id Nvar char 50 主键 学生学号 Gender Nvarchar 50 无 性别 Name Nvarchar 50 无 学生名字 category Nvarchar 50 无 种类 Course表（课程表） 字段名 数据类型 长度 约束 描述 course_id Nvarchar 50 主键 课程号 Name Nvarchar 50 唯一键 课程名 mode Nvarchar 50 无 考核方式 Credit Int 无 学分 each表（教师任课表） 字段名 数据类型 长度 约束 描述 Teach_id Nvarchar 50 主键 任课号 Plan_id Nvarchar 50 外键，组合唯一键 教学计划号 Teacher_id Nvarchar 50 外键，组合唯一键 教师编号 Teacher表（教师表） 字段名 数据类型 长度 约束 描述 Teacher_id Nvarchar 50 主键 教师编号 Name Nvarchar 50 无 教师名 Gender Nvarchar 50 无 性别 Student_select表（学生选课表） 字段名 数据类型 长度 约束 描述 Select_id Nvarchar 50 主键 选课号 Student_id Nvarchar 50 外键，组合唯一键 学生编号 Teach_id Nvarchar 50 外键，组合唯一键 排课编号 Administrator表 字段名 数据类型 长度 约束 描述 administrator Nvarchar 50 管理员号 password Nvarchar 50 密码 Plan表（教学计划表） 字段名 数据类型 长度 约束 描述 plan_id nvarchar 50 主键 教学计划号 audience_id nvarchar 50 外键，组合唯一键 计划受众号 course_id nvarchar 50 外键，组合唯一键 课程号 audience表（计划受众表） 字段名 数据类型 长度 约束 描述 auience_id nvarchar 50 主键约束 计划受众id academy nvarchar 50 无 学院 major nvarchar 50 无 专业 grade nvarchar 50 无 年级 semester Int 无 学期 六 数据库定义语句 CREATE DATABASE [Student_Select] GO USE [Student_Select] GO /****** Object: Table [dbo].[Teacher] Script Date: 06/27/2018 10:37:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Teacher]( [teacher_id] [nvarchar](50) NOT NULL, [name] [nvarchar](50) NULL, [gender] [nchar](10) NULL, CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED ( [teacher_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[Teacher]([teacher_id], [name], [gender]) VALUES (N'1', N'1', N'1 ') INSERT [dbo].[Teacher]([teacher_id], [name], [gender]) VALUES (N'2', N'2', N'2 ') /****** Object: Table [dbo].[Course] Script Date: 06/27/2018 10:37:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Course]( [course_id] [nvarchar](50) NOT NULL, [name] [nvarchar](50) NULL, [mode] [nvarchar](50) NOT NULL, [credit] [nvarchar](50) NULL, CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ( [course_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_Course] UNIQUE NONCLUSTERED ( [name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'考核方式' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Course', @level2type=N'COLUMN',@level2name=N'mode' GO INSERT [dbo].[Course]([course_id], [name], [mode], [credit]) VALUES (N'1', N'1', N'1', N'1') INSERT [dbo].[Course]([course_id], [name], [mode], [credit]) VALUES (N'2', N'2', N'2', N'2') /****** Object: Table [dbo].[audience] Script Date: 06/27/2018 10:37:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[audience]( [audience_id] [nvarchar](50) NOT NULL, [academy] [nvarchar](50) NULL, [major] [nvarchar](50) NULL, [grade] [nvarchar](50) NULL, [semester] [int] NULL, CONSTRAINT [PK_audience] PRIMARY KEY CLUSTERED ( [audience_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'受众ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'audience', @level2type=N'COLUMN',@level2name=N'audience_id' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学院' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'audience', @level2type=N'COLUMN',@level2name=N'academy' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年级' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'audience', @level2type=N'COLUMN',@level2name=N'grade' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'audience', @level2type=N'COLUMN',@level2name=N'semester' GO INSERT [dbo].[audience]([audience_id], [academy], [major], [grade], [semester]) VALUES (N'1', N'1', N'1', N'1', 1) INSERT [dbo].[audience]([audience_id], [academy], [major], [grade], [semester]) VALUES (N'2', N'2', N'2', N'2', 2) /****** Object: Table [dbo].[Administrator] Script Date: 06/27/2018 10:37:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Administrator]( [administrator] [nvarchar](50) NULL, [password] [nvarchar](50) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[Student] Script Date: 06/27/2018 10:37:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Student]( [student_id] [nvarchar](50) NOT NULL, [gender] [nchar](10) NULL, [name] [nvarchar](50) NULL, [category] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ( [student_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[Student]([student_id], [gender], [name], [category]) VALUES (N'1', N'1 ', N'1', N'1') INSERT [dbo].[Student]([student_id], [gender], [name], [category]) VALUES (N'2', N'2 ', N'2', N'2') /****** Object: Table [dbo].[Plan] Script Date: 06/27/2018 10:37:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Plan]( [plan_id] [nvarchar](50) NOT NULL, [audience_id] [nvarchar](50) NOT NULL, [course_id] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Plan] PRIMARY KEY CLUSTERED ( [plan_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_Plan] UNIQUE NONCLUSTERED ( [audience_id] ASC, [course_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[Plan]([plan_id], [audience_id], [course_id]) VALUES (N'1', N'1', N'1') INSERT [dbo].[Plan]([plan_id], [audience_id], [course_id]) VALUES (N'2', N'2', N'2') /****** Object: Table [dbo].[Teach] Script Date: 06/27/2018 10:37:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Teach]( [teach_id] [nvarchar](50) NOT NULL, [plan_id] [nvarchar](50) NULL, [teacher_id] [nvarchar](50) NULL, CONSTRAINT [PK_Teach] PRIMARY KEY CLUSTERED ( [teach_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_Teach_1] UNIQUE NONCLUSTERED ( [teach_id] ASC, [plan_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[Teach]([teach_id], [plan_id], [teacher_id]) VALUES (N'1', N'1', N'1') INSERT [dbo].[Teach]([teach_id], [plan_id], [teacher_id]) VALUES (N'2', N'2', N'2') /****** Object: Table [dbo].[Student_Selete] Script Date: 06/27/2018 10:37:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Student_Selete]( [selete_id] [nvarchar](50) NOT NULL, [student_id] [nvarchar](50) NOT NULL, [teach_id] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Student_Selete] PRIMARY KEY CLUSTERED ( [selete_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_Student_Selete_1] UNIQUE NONCLUSTERED ( [student_id] ASC, [teach_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: ForeignKey [audience_id] Script Date: 06/27/2018 10:37:01 ******/ ALTER TABLE [dbo].[Plan] WITH CHECK ADD CONSTRAINT [audience_id] FOREIGN KEY([audience_id]) REFERENCES [dbo].[audience]([audience_id]) GO ALTER TABLE [dbo].[Plan] CHECK CONSTRAINT [audience_id] GO /****** Object: ForeignKey [course_id] Script Date: 06/27/2018 10:37:01 ******/ ALTER TABLE [dbo].[Plan] WITH CHECK ADD CONSTRAINT [course_id] FOREIGN KEY([course_id]) REFERENCES [dbo].[Course]([course_id]) GO ALTER TABLE [dbo].[Plan] CHECK CONSTRAINT [course_id] GO /****** Object: ForeignKey [plan_id] Script Date: 06/27/2018 10:37:01 ******/ ALTER TABLE [dbo].[Teach] WITH CHECK ADD CONSTRAINT [plan_id] FOREIGN KEY([plan_id]) REFERENCES [dbo].[Plan]([plan_id]) GO ALTER TABLE [dbo].[Teach] CHECK CONSTRAINT [plan_id] GO /****** Object: ForeignKey [teacher_id] Script Date: 06/27/2018 10:37:01 ******/ ALTER TABLE [dbo].[Teach] WITH CHECK ADD CONSTRAINT [teacher_id] FOREIGN KEY([teacher_id]) REFERENCES [dbo].[Teacher]([teacher_id]) GO ALTER TABLE [dbo].[Teach] CHECK CONSTRAINT [teacher_id] GO /****** Object: ForeignKey [student_zhujian] Script Date: 06/27/2018 10:37:01 ******/ ALTER TABLE [dbo].[Student_Selete] WITH CHECK ADD CONSTRAINT [student_zhujian] FOREIGN KEY([student_id]) REFERENCES [dbo].[Student]([student_id]) GO ALTER TABLE [dbo].[Student_Selete] CHECK CONSTRAINT [student_zhujian] GO /****** Object: ForeignKey [teach_zhujian] Script Date: 06/27/2018 10:37:01 ******/ ALTER TABLE [dbo].[Student_Selete] WITH CHECK ADD CONSTRAINT [teach_zhujian] FOREIGN KEY([teach_id]) REFERENCES [dbo].[Teach]([teach_id]) GO ALTER TABLE [dbo].[Student_Selete] CHECK CONSTRAINT [teach_zhujian] GO 七 数据库关系图 最后贴一下朋友的数据库ER图和逻辑设计，是仓库管理系统，我觉得也可以参考： ER图： 逻辑图：
