在线点歌系统的数据库设计

1、在线点歌系统的数据库设计班级：12级自动化学院组成员：xxx 3012203xxx xxx 3012203xxx xxx 3012203xxx 系统名称：在线点歌系统一、需求分析（1）数据检索 用户查询歌曲输入：歌曲名称输出：歌曲名称 歌手姓名 歌曲类型 歌曲时长输入：歌曲拼音缩写输出：歌曲名称 歌手姓名 歌曲类型 歌曲时长 用户查询歌手输入：歌手姓名输出：歌手姓名 歌手性别 地区 歌曲名称输入：歌手姓名拼音缩写输出：歌手姓名 歌手性别 地区 歌曲名称 用户查询会员信息输入：会员登录名 会员密码输出：会员编号 会员姓名 会员收藏歌曲名称 用户查询排行榜 输出：歌曲名称 歌手 点击率 管理员查询

2、后台日志 输入：操作编号 输出：操作时间 操作内容 管理员编号（2）数据插入 歌曲数据插入 歌手数据插入 会员数据插入 管理员数据插入（3）数据修改 歌曲数据修改：某歌曲数据变化时，输入该歌曲编号及需修改的属性，完成对歌曲表的修改。 歌手数据修改：某歌手数据变化时，输入该歌手编号及需修改的属性，完成对歌手表的修改。 会员数据修改：某会员数据变化时，输入该会员编号及需修改的属性，完成对会员表的修改。 管理员数据修改：某管理员数据变化时，输入该管理员编号及需修改的属性，完成对管理员表的修改。（4）数据统计与查询 显示每个歌曲的点击率。 显示会员全部信息及其收藏的所有歌曲的信息。 显示管理员全部信息

3、及其所有操作的信息。二、系统概念模型（e-r图）（1）歌曲实体（图1） 歌曲时长歌曲名称歌曲编号 歌手编号歌曲类型语种拼音缩写歌曲歌曲 图1 歌曲实体（2）歌手实体（图2）歌手歌手编号性别地区歌手姓名拼音缩写 图2 歌手实体（3）管理员实体（图3）管理员管理员编号用户名密码 图3 管理员实体（4）排行榜实体（图4）歌曲编号排行榜歌曲名称歌手姓名点击率 图4 排行榜实体（5）会员实体（图5）会员编号点击歌曲编号收藏歌曲编号姓名密码登录名会员 图5 会员实体（6）后台日志实体（图6）后台日志操作时间操作编号管理员编号备注操作内容 图6 后台日志实体（7）全局e-r图（图7）nptm1mnmqn1n

4、管理员会员歌曲排行榜管理1点击排行浏览1q歌手演唱搜索管理2pt统计时间排列顺序浏览日期点击歌曲编号点歌总数点歌时间收藏歌曲编号演唱风格发行时间后台日志查看添加数量添加时间搜索时间搜索方式添加时间添加数量三、关系模式 歌曲（歌曲编号 歌曲时长 歌曲名称 歌手编号 歌曲类型 语种 拼音缩写）歌手（歌手编号 性别 拼音缩写 歌手姓名 地区）管理员（管理员编号 用户名 密码）排行榜（歌曲编号 歌手姓名 歌曲名称 点击率）会员（会员编号 姓名 密码 登录名 点击歌曲编号 收藏歌曲编号 统计时间 浏览日期）后台日志（操作编号 操作时间 操作内容 备注 管理员编号）点击（会员编号 点击歌曲编号 收藏歌曲编

5、号 点歌时间 点歌总数）管理1（管理员编号 歌手编号 添加数量 添加时间）管理2 （管理员编号 歌曲编号 添加数量 添加时间 ）搜索（会员编号 歌手编号 会员点击歌手编号 会员收藏歌曲编号 搜索时间 搜索方式）演唱（歌手编号 歌曲编号 演唱风格 发行时间）四、物理设计表一、歌曲表结构字段名类型名特殊属性歌曲编号char（20）primary key歌曲时长char(20)0歌曲名称char(80)not null歌手编号char（20）foreign key歌曲类型char（20）语种char（20）拼音缩写char（20）not null表二、歌手表结构字段名类型名特殊属性歌手编号char（

6、20）primary key歌手姓名char(40)not null性别char(4)not null地区char(80)拼音缩写char(20)not null表三、管理员表结构字段名类型名特殊属性管理员编号char（20）primary key用户名char(20)not null密码char(20)not null表四、排行榜表结构字段名类型名特殊属性歌曲编号char（20）primary key歌曲名称char（40）not null歌手名称char(20)not null点击率intnot null表五、会员表结构字段名类型名特殊属性会员编号char（20）primary key姓名

7、char（20）not null密码char（20）not null登录名char(20)not null点击歌曲编号char(20)primary key foreign key收藏歌曲编号char(20)primary key foreign key统计时间datetimenot null浏览日期datetime表六、后台日志表结构字段名类型名特殊属性操作编号char(20)primary key管理员编号char(20)foreign key操作时间datetimenot null操作内容nvarchar(100)not null备注nvarchar(100)表七、点击表结构字段名类型名

8、特殊属性会员编号char(20)primary key，foreign key点击歌曲编号char(20)primary key，foreign key收藏歌曲编号char(20)primary key，foreign key点歌时间datetime点歌总数int=0表八、管理1表结构字段名类型名特殊属性管理员编号char(20)primary key，foreign key歌曲编号char(20)primary key，foreign key添加数量int=0添加时间datetime表九、管理2表结构字段名类型名特殊属性管理员编号char(20)primary key，foreign key

9、歌手编号char(20)primary key，foreign key添加数量int=0添加时间datetime表十、搜索表结构字段名类型名特殊属性会员编号char(20)primary key，foreign key歌手编号char(20)primary key，foreign key会员点击歌曲编号char（20）foremary key会员收藏歌曲编号char（20）foremary key搜索时间datetime搜索方式char(20)表十一、演唱表结构字段名类型名特殊属性歌手编号char(20)primary key，foreign key歌曲编号char(20)primary ke

10、y，foreign key演唱风格char(20)发行时间datetime五、系统实现-1、数据库的建立create database 在线点歌on primary(name=在线点歌_data,filename=f:在线点歌系统在线点歌data.mdf)log on(name=在线点歌_log,filename=f:在线点歌系统在线点歌log.ldf)-2、数据表的建立use 在线点歌 gocreate table 歌手(歌手编号 char(20) primary key,歌手姓名 char(40) not null,性别 char(4) not null,地区 char(80) ,拼音缩写

11、 char(20) not null,)create table 歌曲(歌曲编号 char(20) primary key,歌曲时长 char(20) not null,歌曲名称 char(80) not null,歌手编号 char(20),歌曲类型 char(20),语种 char(20),拼音缩写 char(20) not null, foreign key(歌手编号) references 歌手(歌手编号)create table 管理员(管理员编号 char(20) primary key,用户名 char(20) not null,密码 char(20) not null)crea

12、te table 排行榜(歌曲编号 char(20) primary key,歌曲名称 char(40) not null,歌手名称 char(20) not null,点击率 int not null)create table 会员(会员编号 char(20) ,姓名 char(20) not null,密码 char(20) not null,登录名 char(20) not null,点击歌曲编号 char(20),收藏歌曲编号 char(20),统计时间 datetime not null,浏览日期 datetime,primary key(会员编号,点击歌曲编号,收藏歌曲编号),fo

13、reign key (点击歌曲编号) references 歌曲(歌曲编号),foreign key(收藏歌曲编号) references 歌曲(歌曲编号)create table 后台日志(操作编号 char(20),管理员编号 char(20),操作时间 datetime not null,操作内容 nvarchar(100) not null,备注 nvarchar(100),primary key(操作编号,管理员编号),foreign key (管理员编号) references 管理员(管理员编号)create table 点击表(会员编号 char(20),点击歌曲编号 char

14、(20),收藏歌曲编号 char(20),点歌时间 datetime,点歌总数 int,primary key(会员编号,点击歌曲编号,收藏歌曲编号),foreign key(会员编号,点击歌曲编号,收藏歌曲编号) references 会员(会员编号,点击歌曲编号,收藏歌曲编号)check(点歌总数=0)create table 管理1表(管理员编号 char(20), 歌曲编号 char(20),添加数量 int,添加时间 datetime,primary key(管理员编号,歌曲编号),foreign key(管理员编号) references 管理员(管理员编号),foreign ke

15、y(歌曲编号) references 歌曲(歌曲编号),check(添加数量=0)create table 管理2表(管理员编号 char(20), 歌手编号 char(20),添加数量 int,添加时间 datetime,primary key(管理员编号,歌手编号),foreign key(管理员编号) references 管理员(管理员编号),foreign key(歌手编号) references 歌手(歌手编号),check(添加数量=0)create table 搜索表(会员编号 char(20),会员点击歌曲编号 char(20),会员收藏歌曲编号 char(20),歌手编号

16、char(20),搜索时间 datetime,搜索方式 char(20),primary key(会员编号,歌手编号),foreign key(会员编号,会员点击歌曲编号,会员收藏歌曲编号) references 会员(会员编号,点击歌曲编号,收藏歌曲编号),foreign key(歌手编号) references 歌手(歌手编号)create table 演唱(歌手编号 char(20),歌曲编号 char(20),演唱风格 char(20),发行时间 datetime,primary key(歌手编号,歌曲编号),foreign key(歌手编号) references 歌手(歌手编号),

17、foreign key(歌曲编号) references 歌曲(歌曲编号)-3、数据的输入-歌手表数据的输入insert into 歌手values(000001,河图,男,中国大陆,ht)insert into 歌手values(000002,周杰伦,男,中国台湾,zjl)insert into 歌手values(000003,林俊杰,男,新加坡,ljj)insert into 歌手values(000004,小曲儿,男,中国大陆,xqe)insert into 歌手values(000005,丁当,女,中国台湾,dd)insert into 歌手values(000006,梁静茹,女,马来

18、西亚,ljr)insert into 歌手values(000007,孙燕姿,女,新加坡,syz)insert into 歌手values(000008,吴莫愁,女,中国大陆,wmc)insert into 歌手values(000009,张杰,男,中国大陆,zj)insert into 歌手values(000010,kelly clarkson,女,美国,kc)-歌曲表输入insert into 歌曲values(000001,3:28,倾尽天下,000001,古风,中文,qjtx)insert into 歌曲values(000002,4:20,双截棍,000002,rap,中文,sjg

19、)insert into 歌曲values(000003,3:50,修炼爱情,000003,流行,中文,xlaq)insert into 歌曲values(000004,4:38,上邪,000004,古风,中文,sy)insert into 歌曲values(000005,4:10,猜不透,000005,流行,中文,cbt)insert into 歌曲values(000006,3:53,勇气,000006,流行,中文,yq)insert into 歌曲values(000007,3:40,绿光,000007,流行,中文,lg)insert into 歌曲values(000008,3:28,

20、让我一次爱个够,000008,流行,中文,rwycagg)insert into 歌曲values(000009,4:50,征途,000009,流行,中文,zt)insert into 歌曲values(000010,4:28,because of you,000010,流行,英文,boy)-管理员表输入insert into 管理员values (000001,guanliyuan1,123456a)insert into 管理员values (000002,guanliyuan2,123456b)insert into 管理员values (000003,guanliyuan3,12345

21、6c)insert into 管理员values (000004,guanliyuan4,123456d)insert into 管理员values (000005,guanliyuan5,123456e)insert into 管理员values (000006,guanliyuan6,123456f)insert into 管理员values (000007,guanliyuan7,123456g)insert into 管理员values (000008,guanliyuan8,123456h)insert into 管理员values (000009,guanliyuan9,12345

22、6i)insert into 管理员values (000010,guanliyuan10,123456j)-排行榜表输入insert into 排行榜values(000001,倾尽天下,河图,231)insert into 排行榜values(000004,上邪,小曲儿,204)insert into 排行榜values(000002,双截棍,周杰伦,1231)insert into 排行榜values(000003,修炼爱情,林俊杰,675)insert into 排行榜values(000010,because of you,kelly clarkson,731)insert into

23、 排行榜values(000005,猜不透,丁当,322)insert into 排行榜values(000006,勇气,梁静茹,682)insert into 排行榜values(000007,绿光,孙燕姿,531)insert into 排行榜values(000009,征途,张杰,266)insert into 排行榜values(000008,让我一次爱个够,吴莫愁,145)-会员表输入insert into 会员values(000001,赵一,000001,zhaoyi,000002,000002,20130630,20131123)insert into 会员values(000

24、001,赵一,000001,zhaoyi,000006,000006,20130630,20131127)insert into 会员values(000002,钱二,000002,qianer,000005,000005,20130630,20131123)insert into 会员values(000003,孙三,000003,sunsan,000004,000004,20130630,20130920)insert into 会员values(000003,孙三,000003,sunsan,000007,000007,20130630,20131004)insert into 会员va

25、lues(000003,孙三,000003,sunsan,000009,000009,20130630,20131120)insert into 会员values(000004,李四,000004,lisi,000008,000008,20130630,20131113)insert into 会员values(000005,周五,000005,zhouwu,000002,000002,20130630,20131120)insert into 会员values(000006,吴六,000006,wuliu,000006,000006,20130630,20131103)insert into

26、 会员values(000007,郑奇,000007,zhengqi,000001,000001,20130630,20130823)insert into 会员values(000008,王伟,000008,wangwei,000003,000003,20130630,20131123)insert into 会员values(000009,凤九,000009,fengjiu,000010,000010,20130630,20130716)insert into 会员values(000010,陈石,000003,chenshi,000004,000004,20130630,20131121

27、)-后台日志表的输入insert into 后台日志values(000001,000001,20130603,增加会员,无)insert into 后台日志values(000002,000001,20130613,增加歌曲,null)insert into 后台日志values(000003,000001,20130619,删除歌曲,null)insert into 后台日志values(000004,000003,20130903,增加会员,无)insert into 后台日志values(000005,000005,20131003,增加歌手,周华健)insert into 后台日志v

28、alues(000006,000004,20131005,删除会员,null)insert into 后台日志values(000007,000002,20131008,增加会员,null)insert into 后台日志values(000008,000004,20131013,管理,null)insert into 后台日志values(000009,000002,20131024,增加会员,null)insert into 后台日志values(000010,000005,20131025,增加歌曲,遇见)-点击表的输入insert into 点击表values(000001,00000

29、2,000002,20131123,1)insert into 点击表values(000001,000006,000006,20131127,1)insert into 点击表values(000002,000005,000005,20131123,1)insert into 点击表values(000005,000002,000002,20131120,1)insert into 点击表values(000003,000004,000004,20130920,1)insert into 点击表values(000003,000007,000007,20131004,1)insert int

30、o 点击表values(000003,000009,000009,20131120,1)insert into 点击表values(000004,000008,000008,20131113,1)insert into 点击表values(000006,000006,000006,20131103,1)insert into 点击表values(000007,000001,000001,20130823,1)insert into 点击表values(000008,000003,000003,20131123,1)insert into 点击表values(000009,000010,0000

31、10,20130716,1)insert into 点击表values(000010,000004,000004,20131121,1)-管理1表的输入insert into 管理1表values(000001,000001,1,20130201)insert into 管理1表values(000002,000002,1,20130202)insert into 管理1表values(000003,000003,1,20130203)insert into 管理1表values(000004,000004,1,20130204)insert into 管理1表values(000005,00

32、0005,1,20130204)insert into 管理1表values(000006,000006,1,20130205)insert into 管理1表values(000007,000007,1,20130208)insert into 管理1表values(000008,000008,1,20130213)insert into 管理1表values(000009,000009,1,20130214)insert into 管理1表values(000010,000010,1,20130217)-管理2表的输入insert into 管理2表values(000001,000001

33、,1,20130201)insert into 管理2表values(000002,000002,1,20130202)insert into 管理2表values(000003,000003,1,20130203)insert into 管理2表values(000004,000004,1,20130204)insert into 管理2表values(000005,000005,1,20130204)insert into 管理2表values(000006,000006,1,20130205)insert into 管理2表values(000007,000007,1,20130208)

34、insert into 管理2表values(000008,000008,1,20130213)insert into 管理2表values(000009,000009,1,20130214)insert into 管理2表values(000010,000010,1,20130217)-搜索表的输入insert into 搜索表(会员编号,会员点击歌曲编号,会员收藏歌曲编号,歌手编号,搜索时间,搜索方式)values(000001,000002,000002,000002,20131123,歌手拼音缩写搜索)insert into 搜索表(会员编号,会员点击歌曲编号,会员收藏歌曲编号,歌手编

35、号,搜索时间,搜索方式)values(000001,000006,000006,000006,20131127,歌手拼音缩写搜索)insert into 搜索表(会员编号,会员点击歌曲编号,会员收藏歌曲编号,歌手编号,搜索时间,搜索方式)values(000002,000005,000005,000005,20131123,歌手名称搜索)insert into 搜索表(会员编号,会员点击歌曲编号,会员收藏歌曲编号,歌手编号,搜索时间,搜索方式)values(000005,000002,000002,000002,20131120,拼音缩写搜索)insert into 搜索表(会员编号,会员点击

36、歌曲编号,会员收藏歌曲编号,歌手编号,搜索时间,搜索方式)values(000003,000004,000004,000004,20130920,歌手名称搜索)insert into 搜索表(会员编号,会员点击歌曲编号,会员收藏歌曲编号,歌手编号,搜索时间,搜索方式)values(000003,000007,000007,000007,20131004,歌曲名称搜索)insert into 搜索表(会员编号,会员点击歌曲编号,会员收藏歌曲编号,歌手编号,搜索时间,搜索方式)values(000003,000009,000009,000009,20131120,歌手名称搜索)insert int

37、o 搜索表(会员编号,会员点击歌曲编号,会员收藏歌曲编号,歌手编号,搜索时间,搜索方式)values(000004,000008,000008,000008,20131113,歌曲拼音缩写搜索)insert into 搜索表(会员编号,会员点击歌曲编号,会员收藏歌曲编号,歌手编号,搜索时间,搜索方式)values(000006,000006,000006,000006,20131103,歌手拼音缩写搜索)insert into 搜索表(会员编号,会员点击歌曲编号,会员收藏歌曲编号,歌手编号,搜索时间,搜索方式)values(000007,000001,000001,000001,2013082

38、3,歌曲名称搜索)insert into 搜索表(会员编号,会员点击歌曲编号,会员收藏歌曲编号,歌手编号,搜索时间,搜索方式)values(000008,000003,000003,000003,20131123,歌曲拼音缩写搜索)insert into 搜索表(会员编号,会员点击歌曲编号,会员收藏歌曲编号,歌手编号,搜索时间,搜索方式)values(000009,000010,000010,000010,20130716,歌手名称搜索)insert into 搜索表(会员编号,会员点击歌曲编号,会员收藏歌曲编号,歌手编号,搜索时间,搜索方式)values(000010,000004,0000

39、04,000004,20131121,歌曲拼音缩写搜索)-演唱表的输入insert into 演唱values(000001,000001,rap,20080505)insert into 演唱values(000002,000002,古风,20041115)insert into 演唱values(000003,000003,流行,20130301)insert into 演唱values(000004,000004,古风,20130112)insert into 演唱values(000005,000005,流行,20080708)insert into 演唱values(000006,0

40、00006,流行,20000801)insert into 演唱values(000007,000007,流行,20010709)insert into 演唱values(000008,000008,流行,20120930)insert into 演唱values(000009,000009,流行,20061201)insert into 演唱values(000010,000010,流行,20041130)-4、索引的创建-歌曲表：按歌曲名称升序排列 create index 歌曲_歌曲名称 on 歌曲(歌曲名称) -歌手表：按歌手姓名升序排列 create index 歌手_歌手姓名 on

41、 歌手(歌手姓名) -排行榜：按点击率降序排列 create index 排行榜_点击率 on 排行榜(点击率 desc) -后台日志:按管理员编号升序排列 create index 后台日志_管理员编号 on 后台日志(管理员编号) -5、视图的创建 -1、显示歌曲点击率 create view 歌曲点击率视图as select 歌曲编号, 歌曲名称,点击率 from 排行榜 -2、显示会员全部信息及其收藏的所有歌曲的信息 create view 会员信息与歌曲视图 as select 会员.会员编号,会员.登录名,会员.收藏歌曲编号,歌曲.歌曲名称,歌手.歌手姓名from 会员 join

42、歌曲 on 会员.收藏歌曲编号=歌曲.歌曲编号 join 歌手 on 歌曲.歌手编号=歌手.歌手编号 -3、显示管理员全部信息及其所有操作的信息 create view 管理员信息及操作视图 as select 管理员.管理员编号,管理员.用户名,后台日志.操作编号,后台日志.操作内容,后台日志.操作时间,后台日志.备注 from 管理员 join 后台日志 on 管理员.管理员编号=后台日志.管理员编号 -6、存储过程的创建 -(1)数据检索 -用户查询歌曲1： 输入：歌曲名称 输出：歌曲名称 歌手姓名 歌曲类型 歌曲时长 create procedure 歌曲检索1(gequ char(2

43、0) as if gequ is null begin print请输入歌曲名称 end else begin select 歌曲名称,歌手姓名,歌曲类型,歌曲时长 from 歌曲,歌手 where 歌曲.歌手编号=歌手.歌手编号 and gequ=歌曲.歌曲名称 end -用户查询歌曲2： 输入：歌曲拼音缩写；输出：歌曲名称 歌手姓名 歌曲类型 歌曲时长 create procedure 歌曲检索2(gequ char(20) as if gequ is null begin print请输入歌曲拼音缩写 end else begin select 歌曲名称,歌手姓名,歌曲类型,歌曲时长 f

44、rom 歌曲,歌手 where 歌曲.歌手编号=歌手.歌手编号 and gequ=歌曲.拼音缩写 end -用户查询歌手1:输入：歌手姓名；输出：歌手姓名 歌手性别 地区 歌曲名称 create procedure 歌手检索1(geshou char(20) as if geshou is null begin print请输入歌手姓名 end else begin select 歌手姓名,性别,地区,歌曲名称 from 歌曲,歌手 where 歌曲.歌手编号=歌手.歌手编号 and geshou=歌手.歌手姓名 end -用户查询歌手2:输入：歌手姓名拼音缩写；输出：歌手姓名 歌手性别 地区

45、 歌曲名称 create procedure 歌手检索2(geshou char(20) as if geshou is null begin print请输入歌手拼音缩写 end else begin select 歌手姓名,性别,地区,歌曲名称 from 歌曲,歌手 where 歌曲.歌手编号=歌手.歌手编号 and geshou=歌手.拼音缩写 end -用户查询会员信息:输入：会员登录名 会员密码 输出：会员编号 会员姓名 会员收藏歌曲名称 create procedure 会员信息检索(id int =null) as begin select 会员编号,姓名,歌曲名称 as 收藏歌

46、曲名称 from 会员,歌曲 where 歌曲.歌曲编号=会员.收藏歌曲编号 and id=会员.会员编号 end - 用户查询排行榜 输出：歌曲名称 歌手 点击率 create procedure 排行榜查询 as begin select 歌曲名称,歌手名称,点击率 from 排行榜 order by 点击率 desc end - 管理员查询后台日志 输入：操作编号 输出：操作时间 操作内容 管理员编号 create procedure 后台日志检索(caozuo int =null) as if caozuo is null begin print请输入操作编号 end else begin select 操作时间,操作内容,管理员编号 from 后台日志 where caozuo=操作编号end-（2）数据插入 -歌曲数据插入 create procedure 歌曲数据插入(歌曲编号 char(20),歌曲时长 char(20), 歌曲名称 char(80),歌手编号 char(