2
1720056
1
1
;;;
1
ER
1ER
1
2
11:1
21:N
N1
3M:N
2
12
111
222
33NF
12
111
222
1SQL
create database competition
on primary
name competitiondata,
filenameD:sqlcompetitiondata.mdf,
size30mb,
maxsizeunlimited
,
namecompetitiondata1,
filenameD:sqlcompetitiondata.ndf,
size30mb,
maxsizeunlimited
log on
name competitionlog,
filenameD:sqlcompetitiondata.ldf,
size15mb,
maxsizeunlimited
2SQL
1
create table team
Teamnumber char20 not null primary key,
competitionnumber char1 CHECKcompetitionnumber LIKEAD,
production varchar30 not null ,
leadername varchar30 not null,
member1name varchar30 not null,1
member2name varchar30 not null,2
Tname varchar30 not null ,
schoolname varchar30 not null,
Schooladministratornumber char30 not null ,
foreign keyleadername references leaderleadername,
foreign keymember1name references member1member1name ,
foreign keymember2name references member2member2name ,
foreign keyTname references mentorTname,
foreign keySchooladministratornumber references schooladministratorSchooladministratornumber
2
create table leader
leaderid char40 not null ,
leadername varchar30 not null primary key,
telenumber char20 not null,
leadermail varchar30 not null ,
leadersex char2checkleadersex like ,
leaderclass char20,
leaderdepartment varchar20 not null,
Leaderschool char20 not null
1
create table member1
member1id char40 not null , 1
member1name varchar30 not null primary key,1
member1telenumber char20 not null,1
member1mail varchar30 not null ,1
member1sex char2checkmember1sex like ,1
member1class char20,1
member1department varchar13 not null,
member1school char20 not null
2
create table member2
member2id char40 not null , 2
member2name varchar30 not null primary key,2
member2telenumber char20 not null,2
member2mail varchar20 not null ,2
member2sex char2checkmember2sex like ,2
member2class char20,2
member2department varchar13 not null,
member2school char20 not null
create table mentor
Tname varchar30 not null primary key ,
Ttelenumber char30 not null,
Tmail varchar20 not null ,
Ttitle varchar20 ,
Tnumber char20 not null ,
Tdepartment varchar20 not null,
Tschool char20 not null
create table schooladministrator
Schooladministratorname varchar30 not null ,
Schooladministratornumber char30 not null primary key,
schooladministratorschool char20 not null
create table grade
Teamnumber char20 not null ,
production varchar30 not null ,
Pgrade float checkPgrade0 and Pgrade100 not null,
Lgrade float checkLgrade0 and Lgrade100 not null,
hosternumber char30 not null ,
primary key Pgrade,Lgrade,
foreign keyTeamnumber references teamTeamnumber,
foreign keyhosternumber references hosteradministratorhosternumber
create table hosteradministrator
hostername varchar30 not null ,
hosternumber char30 not null primary key,
hosterschool char20 not null
1
2
1
insert into team values1,A,,,,,,,C12341;
insert into team values2,A,,,,,,,C12342;
insert into team values3,A,,,,,,,C12343;
insert into team values4,A,,,,,,,C12347;
insert into team values5,A,,,,,,,C12343;
insert into team values6,A,,,,,,,C12343;
Go
2
insert into leader values 2102873,,13320031,13320031sina.com,,,,;
insert into leader values 2039843,,12003334,12003334qq.com,,,,;
insert into leader values 3002938,,1223334,1223334sina.com,,,,;
insert into leader values 1202932,,1588897,1588897qq.com,,,,;
insert into leader values 23465665,,1234543,1234543126.com,,,,;
insert into leader values 1323456,,1988005,1988005126.com,,,,;
go
1
insert into member1 values 4415232,,1986341,1986341sina.com,,,,;
insert into member1 values 2319873,,12276654,12276654qq.com,,,,;
insert into member1 values 3238723,,1988644,1988644qq.com,,,,;
insert into member1 values 2233412,,18898734,18898734qq.com,,,,;
insert into member1 values 4323423,,19983474,19983474sina.com,,,,;
insert into member1 values 2343543,,1223665,1223665126.com,,,,;
go
2
insert into member2 values 3413434,,1344531,1344531126.com,,,,;
insert into member2 values 2335423,,1982742,1982742qq.com,,,,;
insert into member2 values 3543151,,12887613,12887613qq.com,,,,;
insert into member2 values 2432562,,11344543,11344543qq.com,,,,;
insert into member2 values 2351665,,19773242,19773242126.com,,,,;
insert into member2 values 3464456,,1234996,1234996126.com,,,,;
go
insert into mentor values ,12834675,12834675gench.cn,,S18723,,;
insert into mentor values ,12354456,12354456gench.cn,,S12445,,;
insert into mentor values ,18977667,18977667gench.cn,,S214112,,;
insert into mentor values ,15657577,15657577gench.cn,,S124324,,;
go
insert into schooladministrator values ,C12341,;
insert into schooladministrator values ,C12342,;
insert into schooladministrator values ,C12343,;
insert into schooladministrator values ,C12347,;
go
7
insert into grade values1,,88,90,C12344;
insert into grade values2,,70,0,C12345;
insert into grade values3,,88,94,C12344;
insert into grade values4,,85,91,C12346;
insert into grade values5,,90,88,C12345;
insert into grade values6,,86,85,C12346;
go
8
insert into hosteradministrator values ,C12344,;
insert into hosteradministrator values ,C12345,;
insert into hosteradministrator values ,C12346,;
go
3
12
2
3SQL
4
alter table leader alter column leadersex char2
alter table member1 alter column member1sex char2 ;
alter table member2 alter column member2sex char2
6
7
8SQL
9TSQL
10
1
2
3
200
A4
520