数据库原理与应用BBS论坛数据库设计开发

1、课程设计任务书学生姓名： 专业班级： 计网2093 指导教师： 工作单位： 信息工程系 设计题目： bbs论坛数据库设计开发要求完成的主要任务：（包括课程设计工作量及其技术要求，以及说明书撰写等具体要求）1 创建数据库bbs，数据表，并建立主外键、约束、建立表测试数据；2 创建视图3 实现以下功能：（1）简单查询；（2）连接查询；（3）嵌套查询；（4）创建触发器；（5）创建存储过程；+设计报告撰写格式要求：1、明确课程设计的目的和重要性，认真领会课程设计的题目，读懂课程设计指导书的要求，学会设计的基本方法与步骤，学会如何运用前修知识与收集、归纳相关资料解决具体问题的方法。严格要求自己，要独立思

2、考，按时、独立完成课程设计任务。2、设计报告：要求层次清楚、整洁规范、不得相互抄袭，凡正文内容有整段完全相同者一律以抄袭论处。设计报告正文字数不少于0.2万字（不包括附录）时间安排：消化资料、系统调查1天系统分析、总体设计，实施计划、撰写报告 3天演示、验收 1天指 导 教 师 签 字： 2011年 7 月 1 日系 主 任 签 字： 2011 年 7 月 1 日课程设计报告书第一章 需求分析1.1 bbs的功能与应用需求1.1.1bbs的功能论坛又名bbs，全称为bulletin board system(电子公告板)或者bulletin board service（公告板服务）。它是int

3、ernet上的一种电子信息服务系统。它提供一块公共电子白板，每个用户都可以在上面书写，可发布信息或提出看法。它是一种交互性强，内容丰富而即使的电子信息服务系统。用户在bbs站点上可以获得各种信息服务、发布信息、进行讨论、聊天等等。像日常生活中的黑板报一样，论坛按不同的主题分为许多版块，版面的设立依据是大多数拥护的要求和喜好，用户可以阅读别人关于某个主题的看法，也可以将自己的想法毫无保留地帖到论坛中。随着计算机网络技术的不断发展，bbs论坛的功能越来越强大，目前bbs的主要功能有以下几点:(1) 供用户自我选择阅读若干感兴趣的专业组和讨论组内的信息。(2) 可随意检查是否有新消息发布并选择阅读。

4、(3) 用户可在站点内发布消息或文章供他人查阅。(4) 用户可就站点内其他人的消息或文章进行评论。(5) 同一站点内的用户互通电子邮件, 设定好友名单 1.1.2应用需求现实生活中的交流存在时间和空间上的局限性，交流人群范围的狭小，以及间断的交流，不能保证信息的准确性和可取性。因此，用户需要通过网上论坛也就是bbs的交流扩大交流面，同时可以从多方面获得自己的及时需求。同时信息时代迫切要求信息传播速度加快，局部范围的信息交流只会减缓前进的步伐。bbs系统的开发能为分散于五湖四海的人提供一个提供一个共同交流、学习、倾吐心声的平台，实现来自不同地方用户的极强的信息互动性，用户在获得自己所需要的信息的

5、同时也可以广交朋友拓展自己的视野和扩大自己的社交面。1.2需求设计 根据bbs的功能与应用需求的简要介绍，可以得出设计bbs系统所要的基本实体有bbsuser(用户)、bbssection(版块)、bbstopic(主贴)、bbsreply(回复贴)。1.3数据字典表1-3-1 bbsusers 用户信息表表中列名数据类型可否为空说明uidintnot null(主键)用户编号unamecharnot null用户姓名upassword charnot null用户密码uemailcharnot null用户email ubirthdaydatetimenot null用户生日 usexbit

6、not null用户性别 uclass intnot null用户等级 ustatementvarcharnot null用户个人说明 uregdate datetimenot null用户注册时间 ustatetinyintnot null用户状态 upoint innot null用户积分 表1-3-2bbstopic主贴信息表格表中列名数据类型可否为空说明tidintnot null(主键)主帖编号 tsidintnot null主帖版块编号 tuidintnot null主帖用户编号 treplycountintnot null主帖回复次数 temotion char(10)not n

7、ull主帖表情 ttopic varcharnot null主帖标题 tcontentstextnot null主帖内容 ttime datetimenot null发帖时间 tclickcount intnot null主帖点击次数 tlastclicktdatetimenot null主帖最后点击时间 表1-3-3 bbssection板块信息表中列名数据类型可否为空说明sidintnot null(主键)版块编号 snamecharnot null版块名称 smasterid intnot null版主编号 sstatementvarcharnot null版块说明 sclickcoun

8、tintnot null版块点击次数 stopiccountintnot null版块主题数 表1-3-4 bbsreply回帖信息表表中列名数据类型可否为空说明sidintnot null(主键)版块编号 snamecharnot null版块名称 smasteridintnot null版块编号 sstatementvarcharnot null板块说明 sclickcountintnot null版块点击次数 stopiccountintnot null板块主题数 第二章 概要设计2.1基本概念介绍 实体：现实世界中的各种事物的抽象。实体可以是具体的各种事物，如学生、教师、仓库、图书馆等

9、；也可以是抽象的概念，如课程、部门、学校等。联系：两个实体集之间的联系。可以分为三类：一对一联系（1:1）如果对于实体集a中的每一个实体，实体集b中最多有一个实体与之联系，反之亦然，则称实体集a和实体集b具有一对一联系，记作1:1。一对多联系（1:n）如果对于实体集a中的每一个实体，实体集b中最多有n(n1)个实体与之联系，对于实体集b中的每一个实体，实体集a中最多有一个实体与之联系，则称实体集a和实体集b具有一对多联系，记作1:n。多对多联系(m:n)如果对于实体集a中的每一个实体，实体集b中最多有n(n1)个实体与之联系，对于实体集b中的每一个实体，实体a中最多有m(m1)个实体与之联系，

10、则称实体集a和实体集b具有多对多联系，记作m:n。属性：描述实体特征或性质。如学生实体的属性有：学号、姓名、性别、年龄等。课程实体的属性有：课程号、课程名、学分等。2.2实体e-r图2.2.1用户e-r图bbsusersuidunameuemailubirthedayusexustatementuclassuregdateupointustate2.2.2主贴e-r图tidbbstopictsidtuidtreplycounttemotiontcontentstlastclicktclickcountttopicttime 2.2.3版块e-r图bbssetionsidsnamesmaster

11、idstopiccountsclickcountsstatement 2.2.4回帖e-r图bbsreplyruidremtionrtopicrcontentsrtimerclickcountridrtidrsid2.3实体总体e-r图bbsusersbbstopicbbsreplybbssection发表发表包含包含管理对应111nn111nn1n13.2实体联系图第三章详细设计3.1程序设计3.1.1创建数据库create database bbson primary(name = bbs_data, -逻辑名filename =d:bbs_data.mdf, -物理文件size = 20

12、mb, -初始大小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) con

13、straint df_upassword default 888888,-用户密码uemail char(20) not null constraint ck_uemail check(uemail like%.%),-用户emailubirthday datetime not null,-用户生日usex int constraint df_usex default 1, -用户性别uclass int constraint df_uclass default 1, -用户等级ustatement varchar(150)not null, -用户说明uregdate datetime no

14、t 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, -版

15、主编号 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

16、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 (tla

17、stclickt=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创建各表之间的联

18、系-建立联系alter table bbstopicadd constraint fk1_bbstopic_bbsusers foreign key(tuid) references bbsusers(uid) -一个用户可以发表多篇主帖内容alter table bbsreplyadd constraint fk1_bbsreply_bbstopic foreign key(rtid) references bbstopic(tid) -一篇主帖能够对应多条回帖alter table bbsreplyadd constraint fk1_bbsreply_bbsusers foreign k

19、ey(ruid) references bbsusers(uid) -一个用户可以发表多条回帖内容alter table bbstopicadd constraint fk1_bbstopic_bbssection foreign key(tsid) references bbssection(sid) -一个版块可以包含多篇主帖alter table bbsreplyadd constraint fk1_bbsreply_bbssection foreign key(rsid) references bbssection(sid) -一个版块可以包含多篇回帖alter table bbsse

20、ctionadd constraint fk1_bbssection_bbsusers foreign key(smasterid) references bbsusers(uid) -一个用户可以管理多个版块 3.1.6插入用户信息（注册新用户） insertinto bbsusers(uid,uname,upassword,uemail,ubirthday,usex,uclass,ustatement,uregdate,ustate)values(1001 ,无敌游客,159357,auroral,1991-5-12,1,疯子,2010-5-7,2)insertinto bbsusers(

21、uid,uname,upassword,uemail,ubirthday,usex,uclass,ustatement,uregdate,ustate,upoint)values(1002 ,熬吧马,654321,shanhu,1989-8-30,1,爱吧,2011-4-7,2,25)insertinto bbsusers(uid,uname,upassword,uemail,ubirthday,usex,uclass,ustatement,uregdate,ustate,upoint)values(1003 ,真伪遢,158664,cookie,1992-1-3,2,快乐,2009-5-7,

22、2,103)insertinto bbsusers(uid,uname,upassword,uemail,ubirthday,usex,uclass,ustatement,uregdate,ustate,upoint)values(1004 ,地球字幕,175175,ibook,1990-7-4,2,自我,2008-5-4,2,2)insertinto bbsusers(uid,uname,upassword,uemail,ubirthday,usex,uclass,ustatement,uregdate,ustate,upoint)values(1005 ,无伤大雅,198755,ipd12

23、3,1988-1-4,1,和谐,2000-12-12,1,100)insertinto bbsusers(uid,uname,upassword,uemail,ubirthday,usex,uclass,ustatement,uregdate,ustate,upoint)values(1006 ,回来吧主,192445,zhenmi,1994-12-14,2,低调,2000-12-12,1,97) 3.1.7创建版块insertinto bbssection(sid,sname,smasterid,sstatement,stopiccount)values(01 ,灌水区,1006,有什么您就

24、说,5)insertinto bbssection(sid,sname,smasterid,sstatement,sclickcount,stopiccount)values(02 ,魔兽贴吧,1005,请不要放水贴！,4,10) 3.1.8发帖insertinto bbstopic(tid,tsid,tuid,treplycount,temotion,ttopic,tcontents ,ttime,tclickcount,tlastclickt)values(001 ,01,1005,5,-,无聊来冒个泡的。,无聊,2011-1-10,5,2011-7-4)insertinto bbstop

25、ic(tid,tsid,tuid,treplycount,temotion,ttopic,tcontents ,ttime,tclickcount,tlastclickt)values(002 ,02,1001,20,+_+,魔兽选哪个职业好？,求指导,2011-6-10,4,2011-7-4)insertinto bbstopic(tid,tsid,tuid,treplycount,temotion,ttopic,tcontents ,ttime,tclickcount,tlastclickt)values(2014 ,01,1004,0,+0+,我是打酱油的,000,2011-1-21,1

26、,2011-6-11) 3.1.9回帖insertinto bbsreply(rid,rtid,rsid,ruid,remotion,rtopic,rcontents,rtime,rclickcount)values(2001 ,001,01,1004,-=,这样的,如此如此，这般这般,2011-6-24,1)insertinto bbsreply(rid,rtid,rsid,ruid,remotion,rtopic,rcontents,rtime,rclickcount)values(2002 ,002,02,1002,_24.2.3查询bbsusers表中ubirthday生日等于今天的用

27、户update bbsusers set ubirthday=1980-7-5 where uid=1002（先更新一个生日日期使能有查询结果） select uname as 姓名,ubirthday as 出生日期from bbsuserswhere day(getdate()=day(ubirthday) and month(getdate()=month(ubirthday) 4.2.4按降序显示所有元老用户 select uname as 姓名,usex as 性别,upoint as 积分,uregdate as 注册日期from bbsuserswhere datediff(yy

28、yy,uregdate,getdate()2 order by upoint desc 4.2.5查询所有在线用户与禁言用户 select uname as 姓名,uregdate as 注册日期,ustate as 用户状态from bbsuserswhere ustate = 1 or ustate = 34.2.6按回帖时间的降序显示所有回帖标题与回帖时间select rtopic as 回帖标题,rtime as 回帖时间from bbsreplyorder by rtime desc4.2.7列出bbstopic表中点击次数最高的前5名帖子select top 5 ttopic as

29、 主题标题,tclickcount as 主贴点击次数from bbstopicorder by tclickcount desc4.2.8近一周用户发帖排名根据系统日期向前推7天,之内的发帖数排名前10名select top 10 tuid as 用户id ,count(*) as 发帖数from bbstopicwhere datediff(dd,ttime,getdate()=7group by tuid 4.2.9查询用户所发帖子的最高和最低回帖数 select tuid as 用户id,max(treplycount) 最多回帖数,min(treplycount) as 最低回帖数f

30、rom bbsreplygroup by tuid4.3t编程提升积分：查出用户表中注册时间超过2年的最低积分如果该积分低于100分,则对所有低于注册时间超过2年且积分低于198分的用户积分实施提升2分操作输出提分后评出各等级： 1000以上a级;500分以上b级;200分以上c级 ;100分以上d级;100分以下为e级begin-t-sql编程declare minpoint int -定义变量，表示元老最低积分-查询所有元老用户的最低积分,并赋值给minpointselect minpoint=min(upoint) from bbsusers where dateadd(yyyy,2,u

31、regdate)getdate()-提分操作while(minpoint100)begin update bbsusers set upoint=upoint+2 where dateadd(yyyy,2,uregdate)getdate() and upoint198 select minpoint=min(upoint) from bbsusers where dateadd(yyyy,2,uregdate)1000 then awhen upoint500 then bwhen upoint200 then cwhen upoint100 then delse e end from bb

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

33、，你就会发现其实情况并不是很复杂。源程序代码：-存在检测use mastergoif exists(select*from sysdatabases where name =bbs)drop database bbsgo-建库create database bbson primary(name = bbs_data, -逻辑名filename =d:bbs_data.mdf, -物理文件size = 20mb, -初始大小filegrowth=10%, -文件增长率maxsize = 200mb -初始大小)log on(name =bbs_log, -逻辑名filename =d:bbs_d

34、ata.ldf, -物理文件size =10mb, -初始大小filegrowth = 10%,-文件增长率maxsize = 100mb -最大尺寸)use bbsgoif exists(select*from sysobjects where name =bbsusers)drop table bbsusersgocreate table bbsusers(uid int constraint pk_uid primary key,-用户编号uname char(8) not null, -用户姓名upassword char(16) constraint df_upassword def

35、ault 888888,-用户密码uemail char(20) not null constraint ck_uemail check(uemail like%.%),-用户emailubirthday 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()

36、, -用户注册时间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 bbstopicgocreate table bbstopic(tid int constraint pk_tid primary key, -主贴编

37、号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

38、, -主贴点击次数 tlastclickt datetime not null ,constraint ck_tlc check (tlastclickt=ttime) -主贴最后点击时间 )if exists(select*from sysobjects where name =bbssection)drop table bbssectiongocreate table bbssection(sid int constraint pk_sid primary key,-版块编号sname char(20),-版块名称 smasterid int, -版主编号 sstatement varch

39、ar(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 bbsreplygocreate table bbsreply(rid int constraint pk_rid primary key,-回复编号 rtid int,-回复帖子编号 rsid i

40、nt,-回复版块编号 ruid int,-回复用户编号 remotion char(10),-回复表情 rtopic varchar(20),-回帖主题rcontents text,-回帖内容rtime datetime default getdate(),-回帖时间rclickcount int,-回帖点击次数)-建立联系alter table bbstopicadd constraint fk1_bbstopic_bbsusers foreign key(tuid) references bbsusers(uid) -一个用户可以发表多篇主帖内容alter table bbsreplyad

41、d constraint fk1_bbsreply_bbstopic foreign key(rtid) references bbstopic(tid) -一篇主帖能够对应多条回帖alter table bbsreplyadd constraint fk1_bbsreply_bbsusers foreign key(ruid) references bbsusers(uid) -一个用户可以发表多条回帖内容alter table bbstopicadd constraint fk1_bbstopic_bbssection foreign key(tsid) references bbssec

42、tion(sid) -一个版块可以包含多篇主帖alter table bbsreplyadd constraint fk1_bbsreply_bbssection foreign key(rsid) references bbssection(sid) -一个版块可以包含多篇回帖alter table bbssectionadd constraint fk1_bbssection_bbsusers foreign key(smasterid) references bbsusers(uid) -一个用户可以管理多个版块-注册新用户insertinto bbsusers(uid,uname,up

43、assword,uemail,ubirthday,usex,uclass,ustatement,uregdate,ustate)values(1001 ,无敌游客,159357,auroral,1991-5-12,1,疯子,2010-5-7,2)insertinto bbsusers(uid,uname,upassword,uemail,ubirthday,usex,uclass,ustatement,uregdate,ustate,upoint)values(1002 ,熬吧马,654321,shanhu,1989-8-30,1,爱吧,2011-4-7,3,25)insertinto bbs

44、users(uid,uname,upassword,uemail,ubirthday,usex,uclass,ustatement,uregdate,ustate,upoint)values(1003 ,真伪遢,158664,cookie,1992-1-3,2,快乐,2009-5-7,2,103)insertinto bbsusers(uid,uname,upassword,uemail,ubirthday,usex,uclass,ustatement,uregdate,ustate,upoint)values(1004 ,地球字幕,175175,ibook,1990-7-4,2,自我,200

45、8-5-4,2,102)insertinto bbsusers(uid,uname,upassword,uemail,ubirthday,usex,uclass,ustatement,uregdate,ustate,upoint)values(1005 ,无伤大雅,198755,ipd123,1988-1-4,1,和谐,2000-12-12,1,150)insertinto bbsusers(uid,uname,upassword,uemail,ubirthday,usex,uclass,ustatement,uregdate,ustate,upoint)values(1006 ,回来吧主,1

46、92445,zhenmi,1994-12-14,2,低调,2000-12-12,1,97)-创建版块insertinto bbssection(sid,sname,smasterid,sstatement,stopiccount)values(01 ,灌水区,1006,有什么您就说,5)insertinto bbssection(sid,sname,smasterid,sstatement,sclickcount,stopiccount)values(02 ,魔兽贴吧,1005,请不要放水贴！,4,10)-发帖insertinto bbstopic(tid,tsid,tuid,treplyco

47、unt,temotion,ttopic,tcontents ,ttime,tclickcount,tlastclickt)values(2001 ,01,1005,5,+_,无聊,无聊来冒个泡的。,2011-6-26,5,2011-7-4)insertinto bbstopic(tid,tsid,tuid,treplycount,temotion,ttopic,tcontents ,ttime,tclickcount,tlastclickt)values(2002 ,02,1001,20,+_+,求指导,魔兽选哪个职业好？,2011-6-27,4,2011-7-4)insertinto bbstopic(tid,tsid,tuid,treplycount,temotion,ttopic,tcontents ,ttime,tclickcount,tlastclickt)values(2003 ,01,1004,1,+_+,000,我是打酱油的,2011-6-30,1,2011-7-3)-添加回复insertinto bbsreply(rid,rtid,rsid,ruid,remotion,