BBS论坛数据库设计说明.doc

. . . .

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

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

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

课程设计报告书
第一章 需求分析
1.1 BBS的功能与应用需求
1.1.1BBS的功能
论坛又名BBS，全称为Bulletin Board System(电子公告板)或者Bulletin Board Service（公告板服务）。它是Internet上的一种电子信息服务系统。它提供一块公共电子白板，每个用户都可以在上面书写，可发布信息或提出看法。它是一种交互性强，容丰富而即使的电子信息服务系统。用户在BBS站点上可以获得各种信息服务、发布信息、进行讨论、聊天等等。像日常生活中的黑板报一样，论坛按不同的主题分为许多版块，版面的设立依据是大多数拥护的要求和喜好，用户可以阅读别人关于某个主题的看法，也可以将自己的想法毫无保留地帖到论坛中。
随着计算机网络技术的不断发展，BBS论坛的功能越来越强大，目前BBS的主要功能有以下几点:
(1) 供用户自我选择阅读若干感兴趣的专业组和讨论组的信息。
(2) 可随意检查是否有新消息发布并选择阅读。
(3) 用户可在站点发布消息或文章供他人查阅。
(4) 用户可就站点其他人的消息或文章进行评论。
(5) 同一站点的用户互通电子, 设定好友
1.1.2应用需求
现实生活中的交流存在时间和空间上的局限性，交流人群围的狭小，以与间断的交流，不能保证信息的准确性和可取性。因此，用户需要通过网上论坛也就是BBS的交流扩大交流面，同时可以从多方面获得自己的与时需求。同时信息时代迫切要求信息传播速度加快，局部围的信息交流只会减缓前进的步伐。BBS系统的开发能为分散于五湖四海的人提供一个提供一个共同交流、学习、倾吐心声的平台，实现来自不同地方用户的极强的信息互动性，用户在获得自己所需要的信息的同时也可以广交朋友拓展自己的视野和扩大自己的社交面。
1.2需求设计
根据BBS的功能与应用需求的简要介绍，可以得出设计BBS系统所要的基本实体有BBSUser(用户)、BBSSection(版块)、BBSTopic(主贴)、BBSReply(回复贴)。
1.3数据字典
表1-3-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
用户积分

表1-3-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
主帖最后点击时间

表1-3-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
版块主题数

表1-3-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.1基本概念介绍
⑴实体：现实世界中的各种事物的抽象。实体可以是具体的各种事物，如学生、教师、仓库、图书馆等；也可以是抽象的概念，如课程、部门、学校等。
⑵联系：两个实体集之间的联系。可以分为三类：
①一对一联系（1:1）
如果对于实体集A中的每一个实体，实体集B中最多有一个实体与之联系，反之亦然，则称实体集A和实体集B具有一对一联系，记作1:1。
②一对多联系（1:n）
如果对于实体集A中的每一个实体，实体集B中最多有n(n≧1)个实体与之联系，对于实体集B中的每一个实体，实体集A中最多有一个实体与之联系，则称实体集A和实体集B具有一对多联系，记作1:n。
③多对多联系(m:n)
如果对于实体集A中的每一个实体，实体集B中最多有n(n≧1)个实体与之联系，对于实体集B中的每一个实体，实体A中最多有m(m≧1)个实体与之联系，则称实体集A和实体集B具有多对多联系，记作m:n。
⑶属性：描述实体特征或性质。如学生实体的属性有：学号、、性别、年龄等。课程实体的属性有：课程号、课程名、学分等。
2.2实体E-R图
2.2.1用户E-R图

BBSUsers
UID
Uname
UEmail
UBirtheday
Usex
UStatement
Uclass
URegDate
UPoint
UState

2.2.2主贴E-R图

TID
BBSTopic
TSID
TUID
TReplyCount
TEmotion
TContents
TLastClick
TClickCount
TTopic
TTime

2.2.3版块E-R图

BBSSetion
SID
SName
SMasterID
STopicCount
SClickCount
SStatement

2.2.4回帖E-R图

BBSReply
RUID
REmtion
RTopic
RContents
RTime
RClickCount
RID
RTID
RSID

2.3实体总体E-R图
BBSUsers
BBSTopic
BBSReply
BBSSection
发表
发表
包含
包含
管理
对应
1
1
1
n
n
1
1
1
n
n
1
n
1

3.2实体联系图

第三章详细设计
3.1程序设计
3.1.1创建数据库
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创建用户表
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创建版块表
create table BBSSection
(
SID int constraint pk_SID primary key,--版块编号
SName char(20),--版块名称
SMasterID int, --版主编号
SStatement varchar(100), --版块说明
SClickCount int constraint df_SClickCount default '0', --版块点击次数
STopicCount int constraint df_STopicCount default '0' --版块主题数
)
3.1.3创建主贴表
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_TTopic 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创建回复贴表
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创建各表之间的联系
--建立联系
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.1.6插入用户信息（注册新用户）
insert
into BBSUsers(UID,UName,UPassword,UEmail,UBirthday,USex,UClass,UStatement,URegDate,UState)
values('1001' ,'无敌游客','159357','auroral163.','1991-5-12','1','','疯子','2010-5-7','2')
insert
into BBSUsers(UId,UName,UPassword,UEmail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1002' ,'熬吧马','654321','','1989-8-30','1','','爱吧','2011-4-7','2','25')
insert
into BBSUsers(UID,UName,UPassword,UEmail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1003' ,'真伪遢','158664','cookie ','1992-1-3','2','','快乐','2009-5-7','2','103')
insert
into BBSUsers(UID,UName,UPassword,UEmail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1004' ,'地球字幕','175175','','1990-7-4','2','','自我','2008-5-4','2','2')
insert
into BBSUsers(UID,UName,UPassword,UEmail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1005' ,'无伤大雅','198755','','1988-1-4','1','','和谐','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','','低调','2000-12-12','1','97')
3.1.7创建版块
insert
into BBSSection(SID,SName,SMasterID,SStatement,STopicCount)
values('01' ,'灌水区','1006','有什么您就说','5')
insert
into BBSSection(SID,SName,SMasterID,SStatement,SClickCount,STopicCount)
values('02' ,'魔兽贴吧','1005','请不要放水贴！','4','10')
3.1.8发帖
insert
into BBSTopic(TID,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)
values('001' ,'01','1005','5',''-~','无聊来冒个泡的。。','无聊','2011-1-10','5','2011-7-4')
insert
into BBSTopic(TID,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)
values('002' ,'02','1001','20','+_+','魔兽选哪个职业好？','求指导','2011-6-10','4','2011-7-4')
insert
into BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)
values('2014' ,'01','1004','0','+0+','我是打酱油的~','000','2011-1-21','1','2011-6-11')
3.1.9回帖
insert
into BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)
values('2001' ,'001','01','1004','>-=','这样的','如此如此，这般这般','2011-6-24','1')
insert
into BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)
values('2002' ,'002','02','1002','>_<','这样的','如此如此，这般这般','2011-6-29','1')
insert
into BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)
values('2003' ,'001','01','1005','--','这样的','如此如此，这般这般','2011-6-27','1')
第四章 数据管理与维护
4.1.单表查询
4.1.1查询用户表
select* from BBSUsers

4.1.2查询版块表
select* from BBSSection

4.1.3查询主贴表
select* from BBSTopic

4.1.4查询回帖表
select* from BBSReply
4.2规定查询
4.2.1查询元老(注册日期距今超过两年)排名
select UName as ,UEmail as 电子,URegDate as 注册日期
from BBSUsers
where datediff(yyyy,URegDate,getdate())>2

4.2.3查询BBSUsers表中UBirthday生日等于今天的用户
update bbsUsers set UBirthday='1980-7-5' where uid=1002（先更新一个生日日期使能有查询结果）
select UName as ,UBirthday as 出生日期
from BBSUsers
where day(getdate())=day(UBirthday) and month(getdate())=month(UBirthday)

4.2.4按降序显示所有元老用户
select UName as ,Usex as 性别,Upoint as 积分,URegDate as 注册日期
from BBSUsers
where datediff(yyyy,URegDate,getdate())>2
order by Upoint desc

4.2.5查询所有在线用户与禁言用户
select UName as ,URegDate as 注册日期,Ustate as 用户状态
from BBSUsers
where UState = 1 or UState = 3

4.2.6按回帖时间的降序显示所有回帖标题与回帖时间
select RTopic as 回帖标题,RTime as 回帖时间
from BBSreply
order by RTime desc

4.2.7列出BBSTopic表中点击次数最高的前5名帖子
select top 5 TTopic as 主题标题,TClickCount as 主贴点击次数
from BBStopic
order by TClickCount desc

4.2.8近一周用户发帖排名
根据系统日期向前推7天,之的发帖数排名前10名
select top 10 TUID as 用户ID ,count(*) as 发帖数
from bbstopic
where datediff(dd,TTime,getdate())<=7
group by tuid

4.2.9查询用户所发帖子的最高和最低回帖数
select tuid as 用户ID,max(TReplyCount) 最多回帖数,min(TReplyCount) as 最低回帖数
from bbsreply
group by tuid

4.3T－ＳＱＬ编程
⑴提升积分：
①查出用户表中注册时间超过2年的最低积分
②如果该积分低于100分,则对所有低于注册时间超过2年且积分低于198分的用户积分实施提升2分操作
⑵输出提分后评出各等级：
① 1000以上A级;500分以上B级;200分以上C级 ;100分以上D级;100分以下为E级
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
selectminPoint=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 bbsUsers
end

第五章 个人小结与体会
在选择这个课程设计题目前，看了一下题目要求应该蛮简单的吧，结果光是写代码就遇到很多问题，以前学过的知识还是不够熟练，编不了几段就要看一下书，比如说怎么限制主键约束，设置默认值，各个实体之间的联系怎么用SQL语言来建立等等。通过不断的翻书和请教其他做过类似实验的同学我算是基本上已经可以把几个基本的实体表建立起来了。在插入数据的过程中出现无法插入的情况，检查发现，然来定义属性变量的时候给的存空间不够，修改后正常插入。经过两天的编程我终于还是完成了，有点儿欣慰，也有点儿感触，我认为，不管做什么事儿都应该要有自信，不能只会知难而退，有时候当我们坦然面对之后，你就会发现其实情况并不是很复杂。

源程序代码：
--存在检测
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 --最大尺寸
)
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 '______')
)

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(50) not null CONSTRAINT ck_TTopic 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) --主贴最后点击时间
)

if exists(select*from sysobjects where name ='BBSSection')
drop table BBSSection
go
create table BBSSection
(
SID int constraint pk_SID primary key,--版块编号
SName char(20),--版块名称
SMasterID int, --版主编号
SStatement varchar(100), --版块说明
SClickCount int constraint df_SClickCount default '0', --版块点击次数
STopicCount int constraint df_STopicCount default '0' --版块主题数
)

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,--回帖点击次数
)

--建立联系
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 BBS