1.学生表
create table Student(
Sno char(7) not null primary key,
Sname char(10) not null,
Ssex char(2) not null,
Sage smallint check (Sage>14 and Sage<65),
Clno char(5),
--foreign key (Clno) references class(Clno)
);
2.课程表
create table Course(
Cno char(1) not null primary key,
Cname char(20) not null unique,
Credit smallint check (Credit=1 or Credit=2 or Credit=3 or Credit=4 or credit=5 or Credit=6)
);
3.班级表
create table Class(
Clno char(5) not null primary key,
Speciality char(20) not null ,
Inyear datetime not null ,
Number smallint check (Number>1 and Number <60),
Monitor char (7),
foreign key (Monitor) references Student(Sno)
);
4.成绩表
create table Cj(
Sno char(7) not null,
Cno char(1) not null,
Grade decimal(4,1) check (Grade>0 and Grade<100),
primary key (Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
);
5。为student表添加外码
alter table Student add constraint pk_student foreign key(Clno) references Class(Clno);
6.
Student
Sno(学号) | Sname(学生姓名) | Ssex(性别) | Sage(年龄) | Clno(班级号) |
2000101 | 李勇 | 男 | 20 | 00311 |
2000102 | 刘诗晨 | 女 | 19 | 00311 |
2000103 | 王一鸣 | 男 | 20 | 00312 |
2000104 | 张婷婷 | 女 | 21 | 00312 |
2001101 | 李勇敏 | 女 | 19 | 01311 |
2001102 | 贾向东 | 男 | 22 | 01311 |
2001103 | 陈宝玉 | 男 | 20 | 01311 |
course:
Cno(课程号) | Cname(课程名称) | Credit(学分) |
1 | 数据库 | 4 |
2 | 离散数学 | 3 |
3 | 管理信息系统 | 2 |
4 | 操作系统 | 4 |
5 | 数据结构 | 4 |
6 | 数据处理 | 2 |
7 | C 语言 | 4 |
Class
Clno(班级号) | Speciality(班级所在专业) | Inyear(入校年份) | Number(班级人数) | Monitor(班长学号) |
00311 | 计算机软件 | 2000-01-01 | 45 | 2000101 |
00312 | 计算机应用 | 2000-01-01 | 42 | 2000103 |
01311 | 计算机软件 | 2001-01-01 | 39 | 2001103 |
Cj:
Sno(学号) | Cno(课程号) | Grade(成绩) |
2000101 | 1 | 92 |
2000101 | 3 | 88 |
2000101 | 5 | 86 |
2000102 | 1 | 78 |
2000102 | 6 | 55 |
2001101 | 2 | 70 |
2001101 | 4 | 65 |
2001102 | 2 | 80 |
2001102 | 4 | 90 |
2001102 | 6 | 83 |
Create table student(
Sno Char(7) Primary key,
Sname Char(10) Unique,
Ssex Char(2) not null Check(Ssex='男' or Ssex='女'),
Sage Smallint Check(Sage>14 and Sage<65),
Clno Char(5) not null,
);
Create table course (
Cno Char(1) not null Primary key,
Cname Char(20) not null Unique,
Credit Smallint check(Credit in (1,2,3,4,5,6))
);
Create table Class(
Clno Char(5) not null primary key,
Speciality Char(20) not null,
Inyear Datetime not null,
Number Smallint check (Number>1 and Number<60),
Monitor Char(7),
foreign key (Monitor) References Student (sno)
);
Create table Cj(
Sno char(7) not null,
Cno Char(1) not null,
Grade Decimal(4, 1) check (Grade>0 and grade< 100),
primary key(Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
);
Alter table student add constraint ck_students_clno foreign key (Clno) references Class (Clno);
INSERT INTO Student(Sno,Sname,Ssex,Sage) VALUES
('2000101','李勇','男',20),
('2000102','刘诗晨','女',19),
('2000103','王一鸣','男',20),
('2000104','张婷婷','女',21),
('2001101','李勇敏','女',19),
('2001102','贾向东','男',22),
('2001103','陈宝玉','男',20)
INSERT INTO class(Clno,Speciality,Inyear,Number,Monitor) VALUES ('00311','计算机软件','2000-01-01','45','2000101'),
('00312','计算机应用','2000-01-01','42','2000103'),
('01311','计算机软件','2001-01-01','39','2001103')
INSERT INTO Course (Cno,Cname,Credit) VALUES
('1','数据库',4),
('2 ','离散数学',3),
('3 ','管理信息系统',2),
('4 ','操作系统',4),
('5','数据结构',4),
('6','数据处理',2),
('7','C语言',4)
INSERT INTO Cj (Sno,Cno,Grade) VALUES
('2000101','1',92),
('2000101','3',88),
('2000101','5',86),
('2000102','1',78),
('2000102','6',55),
('2001101','2',70),
('2001101','4',65),
('2001102','2',80),
('2001102','4',90),
('2001102','6',83)
//手动添加Clno列