新聞系統(tǒng)數(shù)據(jù)庫設(shè)計

1、1根據(jù)需求分析的結(jié)果規(guī)劃出的實體有：管理員實體、單條新聞實體、新聞版塊實體、新聞評論實體管理員表(admin)(admin)Sql:Sql:DROP DATABASE IF EXISTS webnews;CREATE DATABASE webnews ;USE webnews ;CREATE TABLE admin (adminlDadminlD int(4) NOT NULL auto_increment,adminType int(4) not null,adminName char(30) not null,loginName char(30) not null,loginPwd cha

2、r(30) not null,PRIMARY KEY (adminID)ENGINE=InnoDB DEFAULT CHARSET=gb2312;,admin1,admin1);,admin2,admin2);,admin3,admin3);字段名類型長度是否為空注釋默memberIDint4Not null主鍵，會員ID無loginNamevarchar50Not null登錄名無loginPwdvarchar50Not null登錄密俏無會員表(Member)Member)認值INSERT INTO admin (adminlDadminlD ,adminType,adminName,lo

3、ginName,loginPwd) VALUES (1,1,系統(tǒng)管理員INSERT INTO admin (adminlDadminlD ,adminType,adminName,loginName,loginPwd) VALUES (2,2,新聞管理員INSERT INTO admin (adminlDadminlD ,adminType,adminName,loginName,loginPwd) VALUES (3,3,會員管理員INSERT INTO admin( ,admin4,admin4);adminlDadminlD ,adminType,adminName,LoginName,

4、LoginPwd) VALUES (4,4,評論管理員realNamevarchar50Not null真實姓名無emailvarchar100Not null會員email無CREATE TABLE member (memberlDmemberlD int(4) NOT NULL auto_increment,loginName char(50) not null,loginPwd char(50) not null,realName char(50) not null,email varchar(100) not null,PRIMARY KEY (memberID)ENGINE=Inno

5、DB DEFAULT CHARSET=gb2312;INSERT INTO member ( memberIDmemberID ,loginName,loginPwd,realName,email) VALUES (1, member1,member1,會員1號,member1163);INSERT INTO member ( memberIDmemberID ,loginName,loginPwd,realName,email) VALUES (2, member2,member2,會員2號,member2163);INSERT INTO member ( memberIDmemberID

6、,loginName,loginPwd,realName,email) VALUES (3, member3,member3,會員3號,member3163);INSERT INTO member ( memberIDmemberID ,loginName,loginPwd,realName,email) VALUES (4, member4,member4,會員4號,member4163);INSERT INTO member ( memberIDmemberID ,loginName,loginPwd,realName,email) VALUES (5, member5,member5,會

7、員5號,member5163);INSERT INTO member ( memberIDmemberID ,loginName,loginPwd,realName,email) VALUES (6, member6,member6,會員6號,member6163);INSERT INTO member ( memberIDmemberID ,loginName,loginPwd,realName,email) VALUES (7, member7,member7,會員7號,member7163);INSERT INTO member ( memberIDmemberID ,loginName

8、,loginPwd,realName,email) VALUES (8, member8,member8,會員8號,member8163);INSERT INTO member ( memberIDmemberID ,loginName,loginPwd,realName,email) VALUES (9, member9,member9,會員9號,member9163);INSERT INTO member ( memberlDmemberlD ,loginName,loginPwd,realName,email) VALUES (10, member10,member10,會員10號,me

9、mber10163);新聞評論表CommentsComments字段名類型長度是否為空注釋默認值CommentIDint4否主鍵評論標識無reviewerint4是外鍵 評論者姓名會員ID無Titlevarchar100否評論標題無ContentText16否評論內(nèi)容無CommentDateDatetime8否評論時間Now()newsIDint4是外鍵所屆新聞ID無CREATE TABLE comments (commentIDcommentID int(4) NOT NULL auto_increment,reviewerreviewer int(4),newsnews int(4),ti

10、tle varchar(100) not NULL,content text not null,commentDate datetime not null,PRIMARY KEY (commentID),foreign key (reviewer) references member(memberID) on delete cascade,foreign key (news) references news(newsID) on delete cascade)ENGINE=InnoDB DEFAULT CHARSET=gb2312;INSERT INTO comments ( commentI

11、DcommentID, reviewerreviewer, newsnews, title,content, commentDate) VALUES (1, 1,1, ,真假？ 火箭就是垃圾,now();INSERT INTO comments ( commentIDcommentID, reviewerreviewer, newsnews, title,content, commentDate) VALUES (2, 2,1,強大的 隊伍正常表現(xiàn),期待火箭會越來越好,now();INSERT INTO comments ( commentIDcommentID, reviewerreview

12、er, newsnews, title,content, commentDate) VALUES (3, 1,3,真假？ 飯島愛死了，太可惜了。多好的一人??！, now();INSERT INTO comments ( commentIDcommentID, reviewerreviewer, newsnews, title,content, commentDate) VALUES (4, 1,2,讀書？ 讀書到死,now();INSERT INTO comments ( commentIDcommentID, reviewerreviewer, newsnews, title,content

13、, commentDate) VALUES (5, 2,1,真假？ 我也是,now();新聞版塊表section字段名類型長度是否為空注釋默認值sectionIDint4否主鍵版塊標識無sectionNameVarchar30否棋板名無descriptionVarchar100是版塊描述無CREATE TABLE section (sectionID int(4) NOT NULL auto_increment,sectionName varchar(30) not NULL,description varchar(100),PRIMARY KEY (sectionID)ENGINE=Inno

14、DB DEFAULT CHARSET=gb2312;INSERT INTO section ( sectionIDsectionID,sectionName,description) VALUES (1, NBA,NBA賽事最新最快信息);INSERT INTO section ( sectionIDsectionID,sectionName,description) VALUES (2,讀書,讀書時代，今天你讀書了嗎?);INSERT INTO section ( sectionIDsectionID,sectionName,description) VALUES (3,財經(jīng),財經(jīng)報道，股票

15、、基金);INSERT INTO section ( sectionIDsectionID,sectionName,description) VALUES (4,娛樂,我最八卦);INSERT INTO section ( sectionIDsectionID,sectionName,description) VALUES (5,軍事,最新軍事報道);INSERT INTO section ( sectionIDsectionID,sectionName,description) VALUES (6,時事,國內(nèi)外時事);新聞表newsnews字段名類型長度是否為空注釋默認值NewsIDint4

16、否主鍵新聞標識無submtDatedatetime8否新聞提交時間now ()Authorvarchar50否記者姓名無Titlevarchar100否新聞標題無Contenttext16否新聞內(nèi)容無sectionIDint4是外鍵所屆版 塊無keyWordsVarchar100是關(guān)鍵字無clickedTimesInt4否點擊次數(shù)0picturevarchar(100)varchar(100)是圖片，存放圖片路徑default NULLCREATE TABLE news (newsIDnewsID int(4) NOT NULL auto_increment,sectionsection in

17、t(4) not null,title varchar(100) not NULL,author varchar(50) not null,content text not null,keyWords varchar(100) not NULL,submitDate datetime not null,clickedTimes int(4) default 0,picture varchar(100),PRIMARY KEY (newsID),foreign key (section) references section(sectionID) on delete cascade)ENGINE

18、=InnoDB DEFAULT CHARSET=gb2312;INSERT INTO news ( newsIDnewsID,section,title,author,content,keywords,submitDate,clickedTimes,picture) VALUES (1, 1,火箭四連敗,小貓,正文四連敗,nba火箭,now(),0,圖片1);INSERT INTO news ( newsIDnewsID,section,title,author,content,keywords,submitDate,clickedTimes,picture) VALUES (2, 2,我在讀

19、書,小明,正文我在讀書，另順我！,讀書,now(),0,圖片2);INSERT INTO news ( newsIDnewsID,section,title,author,content,keywords,submitDate,clickedTimes,picture) VALUES (3, 3,財經(jīng)新聞,洋洋,正文財經(jīng)股票大漲,財經(jīng) 股票,now(),0,圖片3);INSERT INTO news ( newsIDnewsID,section,title,author,content,keywords,submitDate,clickedTimes,picture) VALUES(4, 4,飯島愛死了!,石頭,正文AV女星飯島愛死了，真可惜,飯島愛AV女星,now(),0,圖片4);INSERT INTO news ( newsIDnewsID,section,title,author,content,keyw