您现在的位置是:首页 > 文章详情

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

日期:2018-07-11点击:323

可以直接从我的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图:

这里写图片描述

逻辑图:

这里写图片描述

原文链接:https://yq.aliyun.com/articles/609978
关注公众号

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。

持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。

文章评论

共有0条评论来说两句吧...

文章二维码

扫描即可查看该文章

点击排行

推荐阅读

最新文章