]
Cjb结构
埶抹类型
允诈Null(f
|lilv-lllll#-illli-illlli-illllM'llllf-B4illIHlIlli-ilIIIHlllll*«l\
►?
|VhWi
char[6)
□
加
diar[S)
sf
vardi3rf20)
團
mn
char[6)
m
br
v3rdiart50)
a
Yhb结构
列容敎拐粪型冗许N』值
diar(6}
char{8^warcharf^fl)varchar(30)
varchar(20)
varchar^O)
varchar(50)
Jsb结构
□
Vgj
圍
O
jsxm
竹
ZWbz
藪攜类型允许Niil值
7
jsbh
char⑹
kdi
diar(3)
时
varcharCSO)
SJ
varcharC2O)
dd
varchar[50)
0
Skb结构
列名
数据类型
冗许Null值
►7S3
char⑷
□
R51
int
0
RS2
int
RE3
int
RS4
int
RS5
int
□
Tjb结构
埶拐类型
冗许恥II值
►阖
bit
甲XBM
char(4)
B
Cxb结构
(3)表优化(判断每个关系是否达到3NF要求,如果没有达到,则继续规范)
按照(1nf->2nf->3nf进行判断和优化)
Cjb(xh,kch,cj)
码:
(xh,kch)
非主属性:
cj因为cj完全依赖于(xh,kch),所以属于2NF
因为不存在传递函数依赖,所以属于3NF
Cxb(xb,xbm)
码:
(xbm)
非主属性:
xb
因为xb完全依赖于(kch),所以属于2NF
因为不存在传递函数依赖,所以属于3NF
Jsb(isbh,jsxm,zy,fy,zc,zw,bz)
码:
(jsbh)
非主属性:
jsxm,zy,fy,zc,zw,bz
因为jsxm,zy,fy,zc,zw,bz完全依赖于(kch),所以属于2NF
因为不存在传递函数依赖,所以属于3NF
Kcb(kch,kcm,kkxq,xs,xf)
码:
(kch)
非主属性:
kcm,kkxq,xs,xf
因为kcm,kkxq,xs,xf完全依赖于(kch),所以属于2NF
因为不存在传递函数依赖,所以属于3NF
Skb(jsbh,kch,bj,sj,dd)
码:
(jsbh,kch,bj)非主属性:
sj,dd
因为sj,dd完全依赖于(jsbh,kch,bj),所以属于2NF
因为不存在传递函数依赖,所以属于3NF
Tib(kch,rs1,rs2,rs3,rs4,rs5)
码:
(kch)
非主属性:
rs1,rs2,rs3,rs4,rs5
因为rs1,rs2,rs3,rs4,rs5完全依赖于(kch),所以属于2NF
因为不存在传递函数依赖,所以属于3NF
Xsb(xh,xm,xb,cssj,bj,zy,zxf,bz,lxfs,zp)
码:
(xh)
非主属性:
xm,xb,cssj,bj,zy,zxf,bz,lxfs,zp
2NF
因为xm,xb,cssj,bj,zy,zxf,bz,lxfs,zp完全依赖于(xh),所以属于
因为不存在传递函数依赖,所以属于3NF
Yhb(yhbh,yhm,mm,bz)
码:
(yhbh)
非主属性:
yhm,mm,bz
因为yhm,mm,bz完全依赖于(yhbh),所以属于2NF
因为不存在传递函数依赖,所以属于3NF
4、物理设计
选择合适的DBMS(要求用sqlserver2008)
5、实施(把sql语句贴在下面)
(1)创建数据库
(把sql语句贴在下面)createdatabasepxscj1on
(
name='pxscj1_data',
filename='f:
\',
size=3mb,
filegrowth=10%
)
logon
(
name='pxscj1_log',filename='f:
\',size=4mb,maxsize=6mb,filegrowth=1mb
)
(2)创建表
(把sql语句贴在下面)createtablexsb(xhchar(6)notnullprimarykey,xmchar(8)notnull,xbbit,cssjdatetime,bjvarchar(50)notnull,zychar(12),
zxfint,bzvarchar(500),
lxfsvarchar(50),
zpvarbinary(max)
)
createtablekcb(
kchchar(3)notnullprimarykey,
kcmchar(16)notnull,
kkxqtinyint,
xstinyint,
xftinyint
)
createtablecjb(
xhchar(6),
kchchar(6),
cjint,
constraintc1primarykey(xh,kch)
)
createtableyhb(
yhbhchar(6)primarykeynotnull,
yhmchar(8),
sfvarchar(20),
mmchar(6),
bzvarchar(5)
createtablejsb(
jsbhchar(6)notnullprimarykey,jsxmchar(8),zyvarchar(30),fyvarchar(30),zcvarchar(20),zwvarchar(20),bzvarchar(50)
)
createtableskb(jsbhchar(6)notnull,kchchar(3)notnull,bjvarchar(50)notnull,sjvarchar(20),ddvarchar(50),constraintc2primarykey(jsbh,kch,bj))
createtabletjb(kchchar(3)primarykeynotnull,rs1int,rs2int,rs3int,rs4int,rs5int
createtablecxb(xbbitnotnull,
xbmchar(4)notnullprimarykey
)
(3)数据库完整性
2)视图
学生选课情况视图(学号,姓名,课程号,课程名,成绩,学分,总学分,教师)createviewxs_xk_view
as
select,XM,,kcm,CJ,xf,zxf,,jsxm
fromxsb,KCB,CJB,jsb,skb
where=and=
and=and=
select*fromxs_xk_view
3)自定义数据库类型
学号,课程号可以考虑用自定义的数据类型。
sp_addtype'xh','char(6)'sP_addtype'kch','char(3)'
4)默认值对象
Xsb:
性别默认为1,总学分默认为0。
Kcb:
学生人数默认为40,学分默认为2。
Jsb:
zy计算机,fy信息,zc讲师,zw无。
Yhb:
mm'123456'
Xsb:
性别默认为1,
altertablexsbaddconstraintb1DEFAULT1forxb
总学分默认为0。
altertablexsbaddconstraintb2DEFAULT0forzxf
Kcb:
学生人数默认为40,
altertablekcbaddconstraintb3DEFAULT40forxs学分默认为2。
altertablekcbaddconstraintb4DEFAULT2forxf
Jsb:
zy计算机,
altertablejsbaddconstraintb5DEFAULT计算机'forzy
fy信息,
altertablejsbaddconstraintb6DEFAULT'信息'forfy
zc讲师,
altertablejsbaddconstraintb7DEFAULT讲师'forzczw无。
altertablejsbaddconstraintb8DEFAULT'无'forzw
Yhb:
mm'123456'
altertableyhbaddconstraintb9DEFAULT'123456'formm
5)规则和check约束
Xsb:
zxf范围在0-160之间。
Kcb:
kkxq范围在1-8之间,xf在1-15之间
Cjb:
cj范围在0-100之间
Xsb:
zxf在0-160之间。
altertablexsb
addconstraintpk_1check(zxfbetween0and160)
Kcb:
kkxq在1-8之间,xf在1-15之间
altertablekcbaddconstraintpk_2check(kkxqbetween1and8)
altertablekcb
addconstraintpk_3check(xfbetween1and15)
Cjb:
cj在0-100之间
altertablecjb
addconstraintpk_4check(cjbetween0and100)
6)参照关系
cjb(xh)参照xsb(xh)
cjb(kch)参照kcb(kch)
yhb(yhbh)参照xsb(xh)和jsb(jsbh)必须用触发器实现
skb(jsbh)参照jsb(jsbh)
skb(bj)参照xsb(bj)
skb(kch)参照kcb(kch)
cjb(xh)—xsb(xh)
altertablecjb
addconstraintfk_1foreignkey(xh)referencesxsb(xh)
cjb(kch)—kcb(kch)
altertablecjb
addconstraintfk_2foreignkey(kch)referenceskcb(kch)
skb(jsbh)—jsb(jsbh)altertableskbaddconstraintfk_4foreignkey(jsbh)referencesjsb(jsbh)skb(kch)—kcb(kch)
altertableskb
addconstraintfk_6foreignkey(kch)referenceskcb(kch)
skb(bj)—xsb(bj)--用触发器实现
createtriggert1onskb
forinsert
as
begin
ifnotexists(select*fromxsbwherebj=(selectbjfrominserted))
begin
print'插入的班级不存在!
'
rollbacktransaction
end
end
yhb(yhbh)—xsb(xh)和jsb(jsbh)--必须用触发器实现
altertriggert2onyhb
forinsert
as
begin
ifnotexists(select*fromxsbwherexh=(selectyhbhfrominserted))
ifnotexists(select*fromjsbwherejsbh=(selectyhbhfrominserted))begin
print'插入的编号出错!
'
rollbacktransaction
end
end
end
(4)用户自定义函数
(把sql语句贴在下面)
(5)用户自定义存储过程
1)根据学号查询学生的选课情况
(把sql语句贴在下面)
CREATEPROCEDURExhcxxs@xhchar(6)
as
begin
ifexists(select*fromcjbwherexh=@xh)
select*fromcjbwherexh=@xh
else
print'无此学生选课信息'
end
2)根据课程号查询课程的选修情况
(把sql语句贴在下面)
CREATEPROCEDUREkchcxxk@kchchar(3)as
begin
ifexists(select*fromcjbwherekch=@kch)select*fromcjbwherekch=@kch
else
print'无此课程选课信息'
end
根据教师编号查询授课情况
(把sql语句贴在下面)
CREATEPROCEDUREjsbhcxsk@jsbhchar(6)as
begin
ifexists(select*fromskbwherejsbh=@jsbh)select*fromskbwherejsbh=@jsbh
else
print'无此教师授课信息'
end
3)根据班级查询该班级的授课情况
(把sql语句贴在下面)
CREATEPROCEDUREbjcxsk@bjvarchar(50)
as
begin
ifexists(select*fromskbwherebj=@bj)select*fromskbwherebj=@bj
else
print'无此班级授课信息
end
4)根据课程号号查看授课情况
(把sql语句贴在下面)
CREATEPROCEDUREkchcxsk@kchchar(3)
as
begin
ifexists(select*fromskbwherekch=@kch)
select*fromskbwherekch=@kch
else
print'无此课程授课信息'
end
5)课程成绩分布统计。
存储过程名称TJ_CJ参数:
课程号(@kch)。
实现功能:
把成绩表(CJB中指定课程按照分数段人数进行统计,放入统计表(TJB中。
编写思路:
(1)清空TJB表,插入一行所有分数段的人数都为0的所要查找的课程的记
录。
(2)判断所查的课程号在CJB表中是否有记录,若有则查找出各个分数段的人数并且更新到TJB表中。
方法一:
createPROCEDURE[d