学生选课系统---数据库课程设计SQL Server-阿里云开发者社区

开发者社区> 数据库> 正文

学生选课系统---数据库课程设计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图:

这里写图片描述

逻辑图:

这里写图片描述

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章