学生选课系统---数据库课程设计SQL Server

可以直接从我的GitHub中获取文档：
##学生选课系统GitHub <https://github.com/jjc123/a-database-of-curriculum-design>
#一 题目

###学生选课系统

#二 需求分析
1.根据学生专业学年学期等信息，录入课程完成课程计划
2.根据课程计划，录入任课教师信息
3.学生可以根据学年学期等信息，选择课程完成选课要求

#三 结构概念设计

计划受众表信息
学生表信息
教师表信息
课程表信息
教学计划表信息
任课表信息
学生选课表信息

#四 ER图（基于三级范式）



##这里我遇到一个问题如何设计ER图 和如何根据ER图设计关系逻辑呢？
##数据库设计方法 <https://wenku.baidu.com/view/d9592377eefdc8d376ee32bb.html?re=view>
看了以上这篇 收益很多 可以参考下
#五 数据字典

####Stdunt表（学生表）
字段名 数据类型 长度 约束 描述 Student_id Nvar char 50 主键 学生学号 Gender Nvarchar 50 无 性别 Name
Nvarchar 50 无 学生名字 category Nvarchar 50 无 种类
####Course表（课程表）
字段名 数据类型 长度 约束 描述 course_id Nvar char 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图：

##逻辑图：

• « 上一篇：大学生社团人员管理数据库系统开发
• » 下一篇：Word 2016 撰写论文(1): 公式居中、编号右对齐