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

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_38409944/article/details/80830620

可以直接从我的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	    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图:
这里写图片描述
##逻辑图:
这里写图片描述

展开阅读全文

没有更多推荐了,返回首页