学生成绩管理系统_数据库设计(内含sql查询语句)

m n
N1
N4
1
N2
1 1
N3
1
create database 学生成绩管理系统
go
use 学生成绩管理系统
go
create table 系(
系编号 char(15) NOT NULL UNIQUE,
系名称 char(20) NOT NULL UNIQUE,
constraint 系pk primary key(系编号,系名称)
)
学 生 学号
性别 年龄 姓名 属于
班 级
班号 属于 专 业 开设 系 专
业 代码
专业名
系编号 系名称
选课 课 程 开设 ISA 必修课 选修课 课程号 课程名 学时 学分 开课学期
create table 专业(
专业编号char(15) NOT NULL UNIQUE,
专业名char(20) NOT NULL UNIQUE,
系编号char(15) NOT NULL,
constraint 专业pk primary key(专业编号,专业名),
constraint FK_系_专业foreign key(系编号) references 系(系编号)
)
create table 班级(
班号char(15) NOT NULL UNIQUE,
班名char(20) NOT NULL UNIQUE,
专业编号char(15) NOT NULL,
constraint 班级pk primary key(班号,班名),
constraint FK_专业_班级foreign key(专业编号) references 专业(专业编号) )
create table 学生(
学号char(15) NOT NULL UNIQUE,
姓名char(20) NOT NULL,
性别char(5),
年龄int,
班号char(15),
constraint 学生pk primary key(学号),
constraint 性别ck check(性别='男' or 性别='女'),
constraint 年龄ck check(年龄>0 and 年龄<90),
constraint FK_班级_学生foreign key(班号) references 班级(班号)
)
create table 课程(
课程号char(15) NOT NULL UNIQUE,
课程名char(20) NOT NULL,
学时int,
学分float,
开课学期char(20),
课程性质char(10),
constraint 课程pk primary key(课程号),
constraint 课程性质ck check(课程性质='选修' or 课程性质='必修')
)
create table 选课(
学号char(15) NOT NULL,
课程号char(15) NOT NULL,
成绩float,
constraint 选课pk primary key(学号,课程号),
constraint 成绩ck check(成绩>=0 and 成绩<=100),
constraint FK_学生_选课foreign key(学号) references 学生(学号),
constraint FK_课程_选课foreign key(课程号) references 课程(课程号)
)
create view 学生成绩(学号,姓名,班级,课程名,成绩,学分绩点)
AS
select 选课.学号,姓名,班级=(select 班名from 班级where 班号=(
select 班号from 学生where 学生.学号=选课.学号)),
课程名,成绩,学分绩点=
case
when 成绩>=90 then 学分*4
when 成绩<90 and 成绩>=85 then 学分*3.5
when 成绩<85 and 成绩>=80 then 学分*3
when 成绩<80 and 成绩>=75 then 学分*2.5
when 成绩<75 and 成绩>=70 then 学分*2
when 成绩<70 and 成绩>=65 then 学分*1.5
when 成绩<65 and 成绩>=60 then 学分*1
when 成绩<60then 学分*0
end
from 学生,课程,选课
where 学生.学号=选课.学号and 课程.课程号=选课.课程号。