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.问题描述41.1背景41.2数据需求4.方案图表设计52.1 E-R图52.2数据字典72.4关系图：83.数据库源代码93.1数据库建立93.2数据初始化错误！未定义书签。4.结果数据处理144.1单表查询144.2要求数据查询155.结束语251.问题描述1.1背景随着BBS的出现，人们的交流有了新的变化。在BBS里，人们之间的交流打破了空间、时间的限制。BBS连人方便，可以通过Internet登录，也可以通过网拨号登录。BBS站往往是由一些有志于此道的爱好看建立，对所有人都免费开放。而且，由于BBS的参与人众多，因此各方面的话题都不乏热心者。可以说，在BBS上可以找到任何你感兴趣的话题。1.2数据需求BBS论坛数据库需要完成功能主要有：BBS 最主要的功能之一。包括各类的学术专题讨论区，疑难问题解答区和闲聊区等等。在这些信件区中，上站的用户留下自己想要与别人交流的信件，如在各种软件硬件的使用、天文、医学、体育、游戏等等方面的心得和经验。这是BBS 一个令用户们心动的功能。一般的BBS 站台中，大多设有交流用的文件区，里面依照不同的主题分区存放了为数不少的软件，有的BBS 站还设有CDROM 光碟区，使得电脑玩家们对这个眼前的宝库都趋之若鹜。众多的共享软件和免费软件都可以通过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 用户信息表表中列名数据类型可否为空说明UIDIntnot null(主键)用户编号UNamecharnot null用户UPassword charnot null用户密码UEmailcharnot null用户Email UBirthdaydatetimenot null用户生日 USexbitnot null用户性别 UClass Intnot null用户等级 UStatementvarcharnot null用户个人说明 URegDate datetimenot null用户注册时间 UStatetinyintnot null用户状态 UPoint innot null用户积分 表-2BBSTopic主贴信息表格表中列名数据类型可否为空说明TIDIntnot null(主键)主帖编号 TSIDIntnot null主帖版块编号 TuidIntnot null主帖用户编号 TReplyCountIntnot null主帖回复次数 TEmotion Char(10)not null主帖表情 TTopic Varcharnot null主帖标题 TContentsTextnot null主帖容 TTime Datetimenot null发帖时间 TClickCount Intnot null主帖点击次数 TLastClickTDatetimenot null主帖最后点击时间 表2-3BBSSection板块信息表中列名数据类型可否为空说明sidIntNot null(主键)版块编号 SNamecharNot null版块名称 SMasterID IntNot null版主编号 SStatementVarcharNot null版块说明 SClickCountIntNot null版块点击次数 STopicCountintNot null版块主题数 表2-4BBSReply回帖信息表表中列名数据类型可否为空说明SIDIntNot null(主键)版块编号 SnameCharNot null版块名称 SMasterIDIntNot null版块编号 SStatementvarcharNot null板块说明 SClickCountIntNot null版块点击次数 STopicCountintNot null板块主题数2.4关系图：图.6数据库存表关系图3.数据库源代码3.1数据库建立3.1.1创建数据库-建库，建表，添加约束-插入测试数据-存在检测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_data.ldf, -物理文件size =10mb, -初始大小filegrowth = 10%,-文件增长率maxsize = 100MB -最大尺寸)3.1.2用户信息表建立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 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 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 BBSTopicgocreate 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 BBSReplygocreate 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 BBSSectiongocreate 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 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 key(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 BBSSectionadd constraint fk1_BBSSection_BBSUsers foreign key(SMasterID) references BBSUsers(Uid) -一个用户可以管理多个版块3.2.1将数据加入表BBSUSERS中-插入数据insertinto BBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)values(1001 ,龙,123456,1990-12-12,1,ANGEL,2010-5-7,2,)insertinto 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)insertinto 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)insertinto 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)insertinto 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)insertinto 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表中-Sectioninsertinto BBSSection(SID,SName,SMasterID,SStatement,SClickCount,STopicCount)values(01 ,灌水懒,1006,说废话的地方,5,5)insertinto BBSSection(SID,SName,SMasterID,SStatement,SClickCount,STopicCount)values(02 ,游戏厅,1005,说游戏的地方,4,5)3.2.3将已有帖子信息加入BBSTOPIC表中*/-topicinsertinto 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)insertinto 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)insertinto 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)insertinto 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)insertinto 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)insertinto BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)values(2011 ,02,1001,1,.,求助,请问。,2011-1-11,6,2011-6-29)insertinto BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)values(2012 ,02,1002,0,.,讨论,DN好玩不？,2011-1-11,7,2011-6-23)insertinto BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)values(2013,02,1003,0,.,研究,那个。,2011-2-11,8,2011-6-23)insertinto 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表中-replyinsertinto BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)values(9001 ,2001,01,1004,。,这样的,如此如此，这般这般,2011-6-24,1)insertinto BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)values(9002 ,2011,02,1002,。,这样的,如此如此，这般这般,2011-6-29,1)insertinto BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)values(9003 ,2003,01,1005,。,这样的,如此如此，这般这般,2011-6-27,1)insertinto 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)2 dateadd(yyyy,2,uregdate)getdate()order by UPoint desc4.18查询所有在线用户和禁言用户图4-8 在线与禁言用户SQL语句-为了方便查询，修改数据update bbsUsers set UState =3 where uid=1004-查询所有在线用户和禁言用户select*from bbsUserswhere UState = 1 or UState = 34.9按回帖时间的降序显示所有回帖标题与回帖时间图4-9按回帖时间的降序显示所有回帖标题与回帖时间SQL语句select RTopic as 回帖标题,RTime as 回帖时间from bbsreplyorder by RTime desc4.10列出BBSTopic表中点击次数最高的前5名帖子图4-10列出BBSTopic表中点击次数最高的前5名帖子SQL语句select top 5 TTopic as 主题标题,TClickCount as 主贴点击次数from bbstopicorder by TClickCount desc4.11近一周发帖最多的两名图4-11近一周发帖最多的两名SQL语句-因为数据原因无法显示，先进行数据修改update bbstopic set ttime = 2011-6-27 where tid = 2002update bbstopic set ttime = 2011-6-26 where tid = 2003update bbstopic set ttime = 2011-6-28 where tid = 2011update bbstopic set TLastClickT =2011-6-30 where tid = 2012update bbstopic set ttime = 2011-6-24 where tid = 2012-近一周用户发帖排名-根据系统日期向前推7天,之的发帖数排名前2名-显示中文列名select top 2 tuid as用户ID ,count(*) as 发帖数from bbstopicwhere datediff(dd,ttime,getdate()=7group by tuid4.12查询用户所发帖子的最高和最低回帖数 图4-12查询用户所发帖子的最高和最低回帖数SQL语句select tuid as 用户ID,max(TReplyCount) 最多回帖数,min(TReplyCount) as 最低回帖数from bbstopicgroup by tuid4.13提升积分与等级评价图4-13提升积分与等级评价SQL语句begin-T-SQL编程declare minPoint int -定义变量，表示元老最低积分-查询所有元老用户的最低积分,并赋值给minPointselect minPoint=min(upoint) from bbsUsers where dateadd(yyyy,2,uregdate)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 bbsUse