BBS论坛数据库设计开发.doc

. . . .

理工大学华夏学院
课程设计报告书

课程名称： 数据库原理与应用

题 目： BBS论坛数据库设计开发
系 名：信息工程系
专业班级： 计应2091
姓 名： 梦柯
学 号：
指导教师: 春燕

2010 年 7月 1 日

课程设计任务书
学生： 梦柯 专业班级： 计应2091
指导教师： 春燕 工作单位： 信息工程系
设计题目：BBS论坛数据库设计开发
要求完成的主要任务：
（包括课程设计工作量与其技术要求，以与说明书撰写等具体要求）
1． 创建数据库 BBS，数据表，并建立主外键、约束、建立表测试数据；
2． 创建视图
3． 实现以下功能：
（1）简单查询
（2）连接查询；
（3）嵌套查询
（4）创建触发器
（5）创建存储过程
设计报告撰写格式要求：
1、明确课程设计的目的和重要性，认真领会课程设计的题目，读懂课程设计指导书的要求，学会设计的基本方法与步骤，学会如何运用前修知识与收集、归纳相关资料解决具体问题的方法。严格要求自己，要独立思考，按时、独立完成课程设计任务。
2、设计报告：要求层次清楚、整洁规、不得相互抄袭，凡正文容有整段完全一样者一律以抄袭论处。设计报告正文字数不少于0.2万字（不包括附录）
时间安排：
消化资料、系统调查 1天
系统分析、总体设计，实施计划、撰写报告  3天
演示、验收　　　　　　　　　　　 　 1天

指 导 教 师 签字：2011年 6月 25 日

系 主 任 签 字： 2011 年6 月 26 日
目录
1.问题描述4
1.1背景4
1.2数据需求4
２.方案图表设计5
2.1 E-R图5
2.2数据字典7
2.4关系图：8
3.数据库源代码9
3.1数据库建立9
3.2数据初始化错误！未定义书签。
4.结果数据处理14
4.1单表查询14
4.2要求数据查询15
5.结束语25

1.问题描述
1.1背景
随着BBS的出现，人们的交流有了新的变化。在BBS里，人们之间的交流打破了空间、时间的限制。BBS连人方便，可以通过Internet登录，也可以通过网拨号登录。BBS站往往是由一些有志于此道的爱好看建立，对所有人都免费开放。而且，由于BBS的参与人众多，因此各方面的话题都不乏热心者。可以说，在BBS上可以找到任何你感兴趣的话题。
1.2数据需求
BBS论坛数据库需要完成功能主要有：
BBS 最主要的功能之一。包括各类的学术专题讨论区，疑难问题解答区和闲聊区等等。在这些信件区中，上站的用户留下自己想要与别人交流的信件，如在各种软件硬件的使用、天文、医学、体育、游戏……等等方面的心得和经验。
这是BBS 一个令用户们心动的功能。一般的BBS 站台中，大多设有交流用的文件区，里面依照不同的主题分区存放了为数不少的软件，有的BBS 站还设有CD－ROM 光碟区，使得电脑玩家们对这个眼前的宝库都趋之若鹜。众多的共享软件和免费软件都可以通过BBS 获取得到，不仅使用户得到合适的软件，也使软件开BBS 对国Shareware （共享软件）的发展将起到不可替代的推动作用。国BBS 主要提供的文件服务区主要有BBS 建站、通信程序、网络工具、Internet程序、加解密工具、多媒体程序、电脑游戏、病毒防治、图象、创作发表和用户这是BBS 最基本的功能了。一些有心的站长会在自己的站台上摆出为数众多的信息。如怎样使用BBS 、国BBS 台站介绍、某些热门软件的介绍、BBS 用户统计资料等；用户在生日时甚至会收到站长的一封热情洋溢的“贺电”，令您感受到BBBS 还会自动统计出热门话题排行榜，看看谁的文章受到的回应最多线的BBS 可以与其他同时上站的用户做到即时的联机交谈。这种功能也有许多变化，如ICQ 、Chat、NetMeeting等。有的只能进行文字交谈。

２.方案图表设计
2.1.1 用户 E-R图
用户

用户密码

用户Email

用户生日

用户性别

用户等级

个人说明

用户积分

用户编号

用户XX

用户状态

注册时间

图2.1用户BBSUSER E-R图
2.1.2主贴 E-R图
主贴

用户编号

回复次数

主帖表情

主帖标题

主帖内容

发帖时间

主帖编号

版块编号

最后点击时间

点击次数

图2.2用户BBSTOPIC E-R图

2.1.3板块 E-R图
板块

版块名称

版主编号

版块说明

版块点击次数

版块编号

版块主题数

图2.3用户BBSSection E-R图

2.1.4回帖 E-R图
回帖

回复版块编号

回复用户编号

回复表情

回帖主题

回帖内容

回帖时间

回复编号

回复帖子编号

回帖点击次数

图2.4用户BBSREPLY E-R图
2.1.5整体E-R图
1 n

1 1 n n

n
n 1
1

n 1

用户
主贴
回帖
板块
发表
对应
发表
管理
包含
包含

图2.5 整体E-R图
2.2数据字典
表２-1　 BBSUsers 用户信息表
表中列名
数据类型
可否为空
说明
UID
Int
not null(主键)
用户编号
UName
char
not null
用户
UPassword
char
not null
用户密码
UEmail
char
not null
用户Email
UBirthday
datetime
not null
用户生日
USex
bit
not null
用户性别
UClass
Int
not null
用户等级
UStatement
varchar
not null
用户个人说明
URegDate
datetime
not null
用户注册时间
UState
tinyint
not null
用户状态
UPoint
in
not null
用户积分

表２-2　　BBSTopic主贴信息表格
表中列名
数据类型
可否为空
说明
TID
Int
not null(主键)
主帖编号
TSID
Int
not null
主帖版块编号
Tuid
Int
not null
主帖用户编号
TReplyCount
Int
not null
主帖回复次数
TEmotion
Char(10)
not null
主帖表情
TTopic
Varchar
not null
主帖标题
TContents
Text
not null
主帖容
TTime
Datetime
not null
发帖时间
TClickCount
Int
not null
主帖点击次数
TLastClickT
Datetime
not null
主帖最后点击时间

表2-3　BBSSection板块信息
表中列名
数据类型
可否为空
说明
sid
Int
Not null(主键)
版块编号
SName
char
Not null
版块名称
SMasterID
Int
Not null
版主编号
SStatement
Varchar
Not null
版块说明
SClickCount
Int
Not null
版块点击次数
STopicCount
int
Not null
版块主题数

表2-4　BBSReply回帖信息表
表中列名
数据类型
可否为空
说明
SID
Int
Not null(主键)
版块编号
Sname
Char
Not null
版块名称
SMasterID
Int
Not null
版块编号
SStatement
varchar
Not null
板块说明
SClickCount
Int
Not null
版块点击次数
STopicCount
int
Not null
板块主题数
2.4关系图：

图２.6数据库存表关系图
3.数据库源代码
3.1数据库建立
3.1.1创建数据库
--建库，建表，添加约束
--插入测试数据
--存在检测
use master
go
if exists(select*from sysdatabases where name ='BBS')
drop database bbs
go

--建库
create database BBS
on primary
(
name = BBS_data, --逻辑名
filename ='d:\BBS_data.mdf', --物理文件
size = 20MB, --初始大小
filegrowth=10%, --文件增长率
maxsize = 200MB --初始大小
)
log on
(
name =bbs_log, --逻辑名
filename ='d:\bbs_data.ldf', --物理文件
size =10mb, --初始大小
filegrowth = 10%,--文件增长率
maxsize = 100MB --最大尺寸
)
3.1.2用户信息表建立
use BBS
go
if exists(select*from sysobjects where name ='BBSUsers')
drop table BBSUsers
go
create table BBSUsers
(
Uid int constraint pk_Uid primary key,--用户编号
Uname char(8) not null, --用户
UPassword char(16) constraint df_UPassword default '888888',--用户密码
UEmail char(20) not null constraint ck_Uemail check(Uemail like'%%.%'),--用户EMAIL
UBirthday datetime not null,--用户生日
Usex int constraint df_Usex default '1', --用户性别
UClass int constraint df_UClass default '1', --用户等级
UStatement varchar(150)not null, --用户说明
URegDate datetime not null default getdate(), --用户注册时间
UState tinyint constraint df_UState default '1', --用户状态
UPoint int constraint df_UPoint default '20' , --用户积分
constraint ck_UPassword check(UPassword like '______')
)
3.1.3创建主贴表
if exists(select*from sysobjects where name ='BBSTopic')
drop table BBSTopic
go
create table BBSTopic
(
tid int constraint pk_tid primary key, --主贴编号
tsid int not null, --主贴板块编号
tuid int not null, --主贴用户编号
treplycount int not null, --主贴回复次数
temotion char(10) not null, --主贴表情
TTopic varchar(16) not null CONSTRAINT ck_tt check(TTopic not like'%''%'), --主贴标题
TContents text not null, --主贴容
TTime datetime not null default getdate() ,--发帖时间
TClickCount int not null, --点击次数
TLastClickT datetime not null ,
CONSTRAINT [CK_TLC] CHECK ([TLastClickT]>=TTime) --主贴最后点击时间
)3.1.4回复表建立
if exists(select*from sysobjects where name ='BBSReply')
drop table BBSReply
go
create table BBSReply
(
RID int constraint pk_RID primary key,--回复编号
RTID int,--回复帖子编号
RSID int,--回复版块编号
RUID int,--回复用户编号
REmotion char(10),--回复表情
RTopic varchar(20),--回帖主题
RContents text,--回帖容
RTime datetime default getdate(),--回帖时间
RClickCount int,--回帖点击次数
)3.1.5版块信息表建立
if exists(select*from sysobjects where name ='BBSSection')
drop table BBSSection
go
create table BBSSection
(
SID int constraint pk_SID primary key,--版块编号
SName char(10),--版块名称
SMasterID int, --版主编号
SStatement varchar(80), --版块说明
SClickCount int constraint df_SClickCount default '0', --版块点击次数
STopicCount int constraint df_STopicCount default '0' --版块主题数
3.1.6 表之间的约束与联系
alter table BBSTopic
add constraint fk1_BBSTopic_BBSUsers foreign key(tuid) references BBSUsers(Uid) --一个用户可以发表多篇主帖容
alter table BBSReply
add constraint fk1_BBSReply_BBSTopic foreign key(RTID) references BBSTopic(TID) --一篇主帖能够对应多条回帖
alter table BBSReply
add constraint fk1_BBSReply_BBSUsers foreign key(RUID) references BBSUsers(UID) --一个用户可以发表多条回帖容
alter table BBSTopic
add constraint fk1_BBSTopic_BBSSection foreign key(tsid) references BBSSection(Sid) --一个版块可以包含多篇主帖
alter table BBSReply
add constraint fk1_BBSReply_BBSSection foreign key(RSID) references BBSSection(Sid) --一个版块可以包含多篇回帖
alter table BBSSection
add constraint fk1_BBSSection_BBSUsers foreign key(SMasterID) references BBSUsers(Uid) --一个用户可以管理多个版块
3.2.1将数据加入表BBSUSERS中
--插入数据
insert
into BBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1001' ,'龙','123456','','1990-12-12','1','','ANGEL','2010-5-7','2','')
insert
into BBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1002' ,'妄想者','654321','','1980-7-1','2','','ANGEL','2011-4-7','2','4')
insert
into BBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1003' ,'刺客','158664','','1997-1-03','2','','ANGEL','2009-5-7','2','3')
insert
into BBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1004' ,'毁灭','175175','','1992-1-4','1','','ANGEL','2008-5-4','2','2')
insert
into BBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1005' ,'魔女','198755','','1994-1-4','1','','ANGEL','2000-12-12','1','100')
insert
into BBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1006' ,'魔王','192445','','1994-12-14','2','','fdfs','2000-12-12','1','97')
3.2.2将已有版块数据加入BBSSection表中
--Section
insert
into BBSSection(SID,SName,SMasterID,SStatement,SClickCount,STopicCount)
values('01' ,'灌水懒','1006','说废话的地方','5','5')
insert
into BBSSection(SID,SName,SMasterID,SStatement,SClickCount,STopicCount)
values('02' ,'游戏厅','1005','说游戏的地方','4','5')
3.2.3将已有帖子信息加入BBSTOPIC表中*/
--topic
insert
into BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)
values('2001' ,'01','1006','1','.....','我是魔王','DEVIL MAY CRY','2011-1-10','5','2011-6-24')
insert
into BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)
values('2002' ,'01','1006','0','.....','灌水来着~~','DEVIL MAY CRY','2011-4-10','4','2011-6-28')
insert
into BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)
values('2003' ,'01','1006','1','.....','灌水来着1~~','DEVIL MAY CRY','2011-1-10','3','2011-6-27')
insert
into BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)
values('2004' ,'01','1006','0','.....','灌水来着2~~','DEVIL MAY CRY','2011-1-10','2','2011-6-26')
insert
into BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)
values('2005' ,'01','1006','1','.....','灌水来着3~~','DEVIL MAY CRY','2011-4-10','1','2011-6-24')
insert
into BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)
values('2011' ,'02','1001','1','.....','求助','请问。。。。','2011-1-11','6','2011-6-29')
insert
into BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)
values('2012' ,'02','1002','0','.....','讨论','DN好玩不？','2011-1-11','7','2011-6-23')
insert
into BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)
values('2013','02','1003','0','.....','研究','那个。。。。','2011-2-11','8','2011-6-23')
insert
into BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)
values('2014' ,'02','1004','0','.....','我是打酱油的~','就是这样。。。。','2011-1-21','1','2011-6-11')
3.2.4-将已有的回帖信息加入BBSreply表中
--reply
insert
into BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)
values('9001' ,'2001','01','1004','。。。。','这样的','如此如此，这般这般','2011-6-24','1')
insert
into BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)
values('9002' ,'2011','02','1002','。。。。','这样的','如此如此，这般这般','2011-6-29','1')
insert
into BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)
values('9003' ,'2003','01','1005','。。。。','这样的','如此如此，这般这般','2011-6-27','1')
insert
into BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)
values('9004' ,'2005','01','1005','。。。。','这样的','如此如此，这般这般','2011-6-24','1')
4.结果数据处理
4.1单表查询
4.1.1表BBSUSER中查询演示:
BBSUSER

图４-1 表BBSUSER中容

4.1.2表BBSSECTION中查询演示:

图４-2 表BBSSECTION中容
4.1.3表BBSTOPIC中查询演示:

图４-3 表BBSTOPIC中容

4.1.4表BBSTOPIC中查询演示:

图４-4 表BBSTOPIC中容
4.1.5元老查询演示:

图４-5 元老查询
SQL语句
--查询元老
select uname as ,Uemail as 电子,UregDate as 注册日期
from bbsUsers
where --datediff(yyyy,uregdate,getdate())>2
dateadd(yyyy,2,uregdate)<getdate()
4.1.6今天过生日的人

图4-6今天过生日的人
SQL语句
--为了让下个指令好看清效果，修改数据
update bbsUsers set UBirthday='1980-7-1' where uid=1002
--查询今天过生日的用户
select uname ,uBirthday 出生日期
from bbsUsers
where --datepart(mm,ubirthday)=datepart(mm,getdate()) and
--datepart(dd,ubirthday)=datepart(dd,getdate())
day(getdate())=day(ubirthday) and month(getdate())=month(ubirthday)
4.1.7元老排名

图4-7 元老排名
SQL语句
--按降序显示所有元老用户(注册时间超过2年)的积分前4名
--/性别/积分/注册日期
select uname as ,Uemail as 电子,UregDate as 注册日期,UPoint as 积分
from bbsUsers
where --datediff(yyyy,uregdate,getdate())>2
dateadd(yyyy,2,uregdate)<getdate()
order by UPoint desc
4.18查询所有在线用户和禁言用户

图4-8 在线与禁言用户
SQL语句
--为了方便查询，修改数据
update bbsUsers set UState ='3' where uid=1004
--查询所有在线用户和禁言用户
select*
from bbsUsers
where UState = 1 or UState = 3
4.9按回帖时间的降序显示所有回帖标题与回帖时间

图4-9按回帖时间的降序显示所有回帖标题与回帖时间
SQL语句
select RTopic as 回帖标题,RTime as 回帖时间
from bbsreply
order by RTime desc
4.10列出BBSTopic表中点击次数最高的前5名帖子

图4-10列出BBSTopic表中点击次数最高的前5名帖子
SQL语句
select top 5 TTopic as 主题标题,TClickCount as 主贴点击次数
from bbstopic
order by TClickCount desc
4.11近一周发帖最多的两名

图4-11近一周发帖最多的两名
SQL语句
--因为数据原因无法显示，先进行数据修改
update bbstopic set ttime = '2011-6-27' where tid = '2002'
update bbstopic set ttime = '2011-6-26' where tid = '2003'
update bbstopic set ttime = '2011-6-28' where tid = '2011'
update bbstopic set TLastClickT ='2011-6-30' where tid = '2012'
update bbstopic set ttime = '2011-6-24' where tid = '2012'

--近一周用户发帖排名
--根据系统日期向前推7天,之的发帖数排名前2名
--显示中文列名
select top 2 tuid as用户ID ,count(*) as 发帖数
from bbstopic
where datediff(dd,ttime,getdate())<=7
group by tuid
4.12查询用户所发帖子的最高和最低回帖数

图4-12查询用户所发帖子的最高和最低回帖数
SQL语句
select tuid as 用户ID,max(TReplyCount) 最多回帖数,min(TReplyCount) as 最低回帖数
from bbstopic
group by tuid
4.13提升积分与等级评价

图4-13提升积分与等级评价
SQL语句
begin
--T-SQL编程
declare minPoint int --定义变量，表示元老最低积分
--查询所有元老用户的最低积分,并赋值给minPoint
select minPoint=min(upoint) from bbsUsers
where dateadd(yyyy,2,uregdate)<getdate()
--提分操作
while(minPoint<100)
begin
update bbsUsers set Upoint=Upoint+2 where dateadd(yyyy,2,uregdate)<getdate() and Upoint<198
select minPoint=min(Upoint) from bbsUSers where dateadd(yyyy,2,uregdate)<getdate()
end
print '元老用户提分完成,用户积分的等级如下：'
select uid 用户ID,Uname 用户名,等级=case when upoint>1000 then 'A'
when upoint>500 then 'B'
when upoint>200 then 'C'
when upoint>100 then 'D'
else 'E' end
from bbsUse