数据库原理与应用--实验十四

实验内容：

根据学校的实际情况，规划数据库：

1. 学校行政设置：学校->学院->系->专业。
2. 学院基本情况：学院编号、学院名称、院长
3. 系：系编号、系名、系主任
4. 学生基本情况：学号、姓名、性别、出生年月日、民族、籍贯、所在学院、系、专业、所在班级、宿舍、手机号码。
5. 班级基本情况：班级编号、班级名称、班主任
6. 老师基本情况：教职工号、姓名、性别、职称、学历、所在学院、系。注：为了简单，职称只分四种助教、讲师、副教授、教授。
7. 课程基本情况：课程号、课程名称、课程学分、课程学时、课程性质（只分必修和选修）、学期。
8. 每门课只有一个老师上，一个老师可以上多门课，多个学生上一门课，学生每门课只有一个成绩，成绩有两种，一种是分数，由平时成绩(30%)和期末成绩(70%)综合计算得出，一种是综合评价，为优秀、良好等。
9. 学生学分统计：包括学生学号、学生绩点、已修的必修学分、已修的选修学分。注：只有那门课成绩超过60分才算修了该学分。
10. 如果要建立一个类似学校的教务管理系统，还需要其它哪些表。

注：每个表至少录入三行数据，而且数据要和学校真实数据相符，如学号是12位、课程号是6位等。

分析过程：

按照题意，建立以下关系模型（横线为主键）：

学院（学院编号、学院名称、院长）

系(系编号、系名、系主任、所属学院编号)

学生(学号、姓名、性别、出生年月日、民族、籍贯、所在系、专业、班级、宿舍、手机号码)

班级(班级编号、班级名称、班主任、所属专业)

老师(教职工号、姓名、性别、职称、学历、所在系)

课程(课程号、课程名称、课程学分、课程学时、课程性质、学期、老师工号)

学生课程成绩(学号、课程号、平时成绩、期末成绩、综合分数、综合评价)

学生学分统计(学生学号、学生绩点、已修的必修学分、已修的选修学分)

源代码：

create database college;
use college;
create table 学院(
编号 int identity,
名称 varchar(20) not null,
院长 char(20),
primary key(编号))
insert into 学院(名称,院长)
values('人文与社会科学学院','张三'),('数学与数据科学学院','李四'),('城乡建设学院','王五'),('信息科学与技术学院','关六')
go
create table 系(
编号 int identity,
名称 varchar(20)not null,
系主任 char(20),
primary key(编号)
)
insert into 系(名称,系主任)
values('信息与计算科学系','李红'),('统计系','王家'),('应用数学系','张三')
go
create table 班级(
班号 int identity,
班名 char(20),
班主任 char(20),
primary key(班号) 
)
insert into 班级(班名,班主任)
values('信计211','李红'),('信计212','张三'),('信计213','李四'),('信计214','刘五')
go
create table 学生(
学号 int identity(1,1),
姓名 char(20)not null,
性别 char(2) not null,
出生日期 date,
民族 char(50),
籍贯 char(50),
所在学院 int,
所在系 int,
所在专业 varchar(50),
班级 varchar(50),
宿舍 varchar(50),
手机号码 char(11),
primary key (学号),
check(性别='男'or 性别='女'),
foreign key (所在学院) references 学院(编号),
foreign key (所在系) references 系(编号)
)

insert into 学生(姓名,性别,出生日期,民族,籍贯,宿舍,手机号码)
values ('张楼','男','2002.06.01','汉族','广东汕头','4110','13918877756'),
('刘艳芬','女','2002.04.01','满族','广东深圳','18905','14417675499'),
('李振','男','2002.08.01','汉族','广东广州','4111','13918897861')
go
create table 教师(
教职工号 int identity,
姓名 char(20),
性别 char(2),
职称 char(20),
学历 char(50),
所在学院 int,
所在系 int,
primary key(教职工号),
check(性别 in('男','女')),
foreign key (所在学院) references 学院(编号),
foreign key (所在系) references 系(编号)
)
insert into 教师(姓名,性别,职称,学历,所在学院,所在系)
values('张楼','男','教授','博士','2','1'),
('李振','男','教授','博士','2','1'),
('黄与','男','教授','博士','2','1')
 go
 create table 课程(
课程号 char(5),
名称 varchar(50),
学分 smallint,
学时 char(100),
性质 varchar(50),
check (性质 in('必修','选修')),
primary key(课程号)
)
insert into 课程(课程号,名称,学分,学时,性质)
values ('10001','数据库','3','72','必修'),('10002','数据结构','4','72','必修'),('10003','操作系统','4','66','选修')
go
create table 成绩(
学号 int,
课程号 char(5),
平时成绩 int default 0,
卷面成绩 int default 0,
期末成绩 int default 0,
primary key(学号,课程号),
check(期末成绩=平时成绩*0.3+卷面成绩*0.7),
foreign key (学号) references 学生(学号),
foreign key (课程号) references 课程(课程号),
)
insert into 成绩(学号,课程号,平时成绩,卷面成绩)
values('1','10001','90','90'),('2','10001','50','70'),('3','10003','60','80')