数据库代码-实验六.doc
文本预览下载声明
实验三代码
create table Course(
Cid char(6) primary key,
Cname char(20) unique,
Ccredit int
);
create table Teacher(
Tid char(6) primary key,
Tname char(20),
Tdept char(20),
TstartDate char(20)
);
create table Class(
Classid char(6) primary key,
ClassDept char(10)
);
create table Classroom(
Roomid char(6) primary key,
Building char default TB1,
RoomType char(20)
);
create table Student(
Sid char(6),
Sname char(20) Constraint SnameUnique Unique(Sname),
Sage smallint Constraint SageConstraint check(Sage=10 and Sage=30),
Ssex char(2) Check(Ssex in (F,M)) Constraint SexDefault DefaultM,
Classid char(6),
primary key (Sid)
);
create table Department(
Deptid char(6),
Dname char(20),
Dchairman char(20),
primary key (Deptid),
);
create table Enrollment(
Sid char(6)not null,
Cid char(6)not null,
grade smallint check (grade=0 and grade=100),
semester char(10),
type char(20),
primary key (Sid,Cid),
foreign key (Sid) references Student (Sid),
foreign key (Cid) references Course (Cid)
);
create table ClassAssign(
Classid char(6)not null,
Roomid char(6)not null,
Ctime char(20),
primary key (Classid,Roomid),
foreign key (Classid) references Class (Classid),
foreign key (Roomid) references Classroom (Roomid)
);
实验四代码
1、
A
create clustered index Coucid on Course(Cid);
create clustered index Teatid on Teacher(Tid);
create clustered index Claclassid on Class(Classid);
create clustered index Claroomid on Classroom(Roomid);
create clustered index Stusid on Student(Sid);
create clustered index Depdeptid on Department(Deptid);
create clustered index Enrsid on Enrollment(Sid,Cid);
create clustered index Claid on ClassAssign(Classid,Roomid);
B
create unique index Coucname on Course(Cname);
C
create unique index Depdname on Department(Dname);
D
create index Stunamesage on Student(Sname asc,Sage desc);
E
create view MA_Student
AS
Select Sid,Sname,Sage,Ssex,Cla
显示全部