KTV自主选歌系统数据库(期末大作业-看看你就知道对你多有用!).doc

数据库大作业SQL大作业2011-2012学年度KTV自主选歌系统数据库设计说明书2011/12/20基于SQL2008数据库设计软件，设计的一款方便大家K歌的小型数据库服务系统系统简介。KTV自主选歌系统数据库说明书目录KTV自主选歌系统数据库说明书11引言11.1设计目的11.2 定义12需求分析12.1功能分析12.2数据字典12.3数据流图43结构设计43.1概念结构设计43.2逻辑结构设计63.3物理结构设计71引言2011-2012学年第一学期SQL大作业，欢迎检查指导！1.1设计目的 本系统主要用于KTV自助选歌，现在社会年轻人娱乐休闲方式很多，其中去KTV high一下是必备项目。本数据库包括歌曲信息，歌手信息，歌曲所属专辑信息等，方便顾客根据歌曲名、歌曲类型等选择自己喜欢的歌曲，满足顾客需求。1.2 定义1.系统名称：KTV自主选歌系统2.数据库设计人员：2需求分析2.1功能分析1.歌手信息的管理：可以查询、增加、删除、修改管理2歌曲信息的管理：可以查询、增加、删除、修改管理3.乐队信息管理：可以查询、增加、删除、修改管理4.专辑信息的管理：可以查询、增加、删除、修改管理5.曲目类型的管理：可以查询、增加、删除、修改管理6.预选歌曲表的管理：根据用户的自己的需求，选择歌曲曲目2.2数据字典1.数据项数据项含义说明类型长度歌手名字指定歌手姓名Char10所属公司指定歌手所属公司名称Char40性别指定歌手性别Char2来自地区说明歌手所属范围Char10乐队编号唯一标识一个乐队乐队名称说明乐队包含歌手名称Char20曲风编号唯一标识一种曲风类型曲风类型描述歌曲类型Char30曲风区域描述歌曲风土人情背景Char8歌曲名称指定歌曲名称Char10乐队来自地区说明乐队来自地区Char10专辑编号唯一标识一张专辑Char4专辑名称说明专辑名称Char10歌曲编号标识歌曲信息Char4歌手编号标识歌手信息Char4专辑编号标识专辑信息Char42.数据结构编号数据结构名属性1歌手歌手编号，歌手名字，性别，所属公司，来自地区，乐队编号2曲目歌曲编号，歌曲名称，曲风类型，专辑编号3专辑专辑编号，专辑名称，歌曲编号，歌手编号4乐队信息乐队名称，乐队编号，来自地区5曲风曲风类型，歌曲编号6歌曲信息歌曲编号，歌手编号 7专辑信息专辑编号，歌手编号8预选歌曲歌曲名称，歌手名字3.数据流编号数据流名输入输出1变更歌曲变更信息歌曲信息2查询歌曲歌曲编号歌曲信息3变更歌手变更信息歌手信息4查询歌手歌手编号歌手信息5变更乐队变更信息乐队信息6查询乐队乐队编号乐队信息7查询专辑专辑编号专辑信息8变更专辑变更信息专辑信息9变更曲风变更信息曲风信息10查询曲风曲风类型曲风信息11提交选歌信息歌曲编号，歌手编号，乐队编号，专辑编号预选歌曲信息12查询已选歌曲歌曲编号，歌手编号歌曲信息4.数据存储数据存储名输入数据流输出数据流说明部分歌曲信息变更歌曲查询歌曲，提交选歌信息歌手信息变更歌手查询歌手，提交选歌信息专辑信息变更专辑，变更歌曲，变更歌手查询专辑乐队信息变更乐队查询乐队选歌信息提交选歌信息查询已选歌曲1. 处理过程处理过程名输入数据流输出数据流说明部分整理分析数据终端歌曲信息，歌手信息，专辑信息，乐队信息根据互联网的信息定时更新数据库操作数据终端变更歌曲，变更歌手，变更专辑，变更乐队将得到的数据向数据库增加，更改选歌提交选歌信息2.3数据流图3结构设计3.1概念结构设计性别歌手姓名3.3.1局部E-R 图所属公司来自地区歌手编号歌手信息歌曲名称歌曲类型专辑编号 歌曲编号歌手编号歌曲信息乐队名称来自地区乐队编号乐队信息专辑名字曲风区域类型编号专辑编号所属歌手类型说明语言类型专辑信息曲风类型 3.3.2 全局关系图演唱歌手信息歌曲信息 N M 包含拥有 1 N N N N属于属于 N 1专辑信息 属于属于 N N乐队信息曲风类型 1 1 1 1 13.3.3全局E-R图曲风编号歌曲名称歌曲编号歌曲编号来自地区所属公司歌手编号专辑编号歌手编号性别演唱歌手信息歌曲信息 M N包含 N N拥有 N 1 N专辑编号属于属于歌手编号专辑信息 N 1属于属于 N N专辑名称 1 1 1 1乐队信息 1 曲风类型来自地区乐队名称曲风描述乐队编号曲风编号曲风区域3.2逻辑结构设计32. 1基本表歌手(歌手编号，歌手名字，所属公司，性别，来自地区，乐队编号)曲目（歌曲编号，歌曲名称，曲风编号，专辑编号，乐队编号）专辑（专辑编号，专辑名称，歌手编号 ，曲风编号，乐队编号）乐队信息（乐队编号，乐队名称，来自地区）曲风（曲风编号，曲风描述，曲风区域，语言类型）歌曲信息（歌曲编号，歌手编号）预选歌曲（歌曲名称，歌手名字）3.3物理结构设计 3.3.1创建数据库代码CREATE DATABASE KTV自主选歌系统 on(name=KTV自主选歌系统_data,filename=e:sqlKTV自主选歌系统_data.mdf,size=10,maxsize=50,filegrowth=5mb) Log on(name=KTV自主选歌系统_log,filename=e:sqlKTV自主选歌系统_log.ldf,size=5mb,maxsize=25mb,filegrowth=5mb)go 3.3.2创建表代码CREATE TABLE 歌手信息表 (歌手编号 char(4), 歌手名字 CHAR(10), 所属公司 CHAR(40), 性别 CHAR(2) CHECK (性别 IN(男,女) NOT NULL, 来自地区 CHAR(10), 乐队编号 CHAR(4), PRIMARY KEY (歌手编号 ), FOREIGN KEY (乐队编号) REFERENCES 乐队信息表(乐队编号)CREATE TABLE 曲风表(曲风编号 CHAR(4) PRIMARY KEY,曲风类型 CHAR(30),曲风区域 CHAR(8) CREATE TABLE 预选歌曲表(歌曲名称 CHAR(10), 歌手名字 CHAR(10) CREATE TABLE 乐队信息表(乐队编号 CHAR(4) PRIMARY KEY,乐队名称 CHAR(20), 来自地区 CHAR(10), 歌手编号 CHAR(10)CREATE TABLE 专辑信息表(专辑编号 CHAR(4),专辑名称 CHAR(30) , 曲风类型 CHAR(10), 乐队编号 CHAR(40), 歌手编号 CHAR(10) PRIMARY KEY (专辑编号) CREATE TABLE 曲目表(歌曲编号 CHAR(4) PRIMARY KEY, 歌曲名称 CHAR(10), 曲风编号 CHAR(10), 专辑编号 CHAR(30) ,乐队编号 CHAR(30)CREATE TABLE 歌曲信息表(歌曲编号 CHAR(4), 歌手编号 CHAR(10), PRIMARY KEY (歌曲编号,歌手编号)3.3.3表截图 3.3.4 创建视图，存储过程，默认，权限，约束，触发器代码及视图 1创建约束代码ALTER TABLE 曲目表ADD CONSTRAINT FK_QM FOREIGN KEY (曲风编号) REFERENCES 曲风表(曲风编号)ALTER TABLE 曲目表ADD CONSTRAINT FK2_QM FOREIGN KEY (专辑编号) REFERENCES 专辑信息表(专辑编号)ALTER TABLE 歌曲信息表ADD CONSTRAINT FK_XX FOREIGN KEY (歌曲编号) REFERENCES 曲目表(歌曲编号)ALTER TABLE 曲风表 ADD CONSTRAINT aa DEFAULT 个性 for 曲风编号ALTER TABLE 专辑信息表ADD CONSTRAINT FK_ZJ FOREIGN KEY (歌手编号) REFERENCES 歌手信息表(歌手编号)ALTER TABLE 歌曲信息表ADD CONSTRAINT FK2_XX FOREIGN KEY (歌手编号) REFERENCES 歌手信息表(歌手编号) 2触发器CREATE TRIGGER TR1_ADDON 歌手表FOR INSERTASIF EXISTS(SELECT * FROM INSERTED WHERE 性别 NOT IN (男,女)BEGINRAISERROR(输入值为男或女,16,1)ROLLBACK TRANSACTIONENDCREATE TRIGGER UP_triON 曲目表FOR UPDATEASDECLARE OLD NVARCHAR(255)DECLARE NEW NVARCHAR(255)SELECT OLD=专辑编号 FROM DELETEDSELECT NEW=专辑编号 FROM INSERTEDUPDATE 专辑表 SET 专辑编号=new where 专辑编号=oldbeginraiserror (需要级联更新专辑表中的专辑编号)rollback transactionend改的：CREATE TRIGGER TR1_ADDON 歌手信息表FOR INSERTASIF EXISTS(SELECT * FROM INSERTED WHERE 性别 NOT IN (男,女)BEGINRAISERROR(输入值为男或女,16,1)ROLLBACK TRANSACTIONENDCREATE TRIGGER UP_triON 曲目表FOR UPDATEASDECLARE OLD NVARCHAR(255)DECLARE NEW NVARCHAR(255)SELECT OLD=专辑编号 FROM DELETEDSELECT NEW=专辑编号 FROM INSERTEDUPDATE 专辑表 SET 专辑编号=new where 专辑编号=oldbeginrollback transactionend3授予权限USE KTV自主选歌系统gogrant select,update,deleteon 预选歌曲表to public DENY INSERT,delete ON 曲目表 TO publicgrant selecton 歌手表to publicgodeny delete,updateon 歌手表to public DENY INSERT,delete ON 专辑表 TO public DENY INSERT,delete ON 乐队信息表 TO public（grant select,delete,updateon 歌手表to db_ownergo）（消息4617，级别16，状态1，第1 行无法对特殊角色授予、拒绝或撤消权限。）改过的：USE KTV自主选歌系统gogrant select,update,deleteon 预选歌曲表to public DENY INSERT,delete ON 曲目表 TO publicgrant selecton 歌手信息表to publicgodeny delete,updateon 歌手信息表to public DENY INSERT,delete ON 专辑信息表 TO public DENY INSERT,delete ON 乐队信息表 TO public 4存储过程if exists (select name from sysobjects where name=歌手选歌 and type=P)Drop procedure 歌手选歌gocreate procedure 歌手选歌 歌手名称 varchar(10) as select 歌手表.歌手编号,歌手名字,歌曲名称 from 歌手表,歌曲信息表,曲目表 where 歌手表.歌手编号=歌曲信息表.歌手编号 and 歌曲信息表.歌曲编号=曲目表.歌曲编号 and 歌手名字=歌手名称exec 歌手选歌 阿桑改过的：if exists (select name from sysobjects where name=歌手选歌 and type=P)Drop procedure 歌手选歌gocreate procedure 歌手选歌 歌手名字 varchar(10) as select 歌手信息表.歌手编号,歌手信息表.歌手名字,曲目表.歌曲名称 from 歌手信息表,歌曲信息表,曲目表 where 歌手信息表.歌手编号=歌曲信息表.歌手编号 and 歌曲信息表.歌曲编号=曲目表.歌曲编号 and 歌手名字=歌手名字exec 歌手选歌 阿桑if exists (select name from sysobjects where name=歌曲选歌 and type=P)Drop procedure 歌曲选歌gocreate procedure 歌曲选歌 歌曲名称 varchar(10) as select 曲目表.歌曲编号,歌手名字,歌曲名称 from 歌手表,歌曲信息表,曲目表 where 歌手表.歌手编号=歌曲信息表.歌手编号 and 歌曲信息表.歌曲编号=曲目表.歌曲编号 and 歌曲名称=歌曲名称exec 歌手选歌 阿桑if exists (select name from sysobjects where name=乐队选歌 and type=P)Drop procedure 乐队选歌gocreate procedure 乐队选歌 乐队名称 varchar(10) as select 歌曲编号,歌曲名称,乐队名称 from 曲目表,乐队信息表 where 乐队信息表.乐队编号=曲目表.乐队编号 and 乐队名称=乐队名称exec 乐队选歌 SHEif exists (select name from sysobjects where name=曲风类型选歌 and type=P)Drop procedure 曲风类型选歌gocreate procedure 曲风类型选歌 曲风类型 char(30) as select 曲目表.歌曲编号,歌手名字,歌曲名称,曲风类型,曲风区域 from 歌手表,歌曲信息表,曲目表,曲风表 where 歌手表.歌手编号=歌曲信息表.歌手编号 and 歌曲信息表.歌曲编号=曲目表.歌曲编号 and 曲目表.曲风编号=曲风表.曲风编号 and 曲风类型=曲风类型exec 曲风类型选歌 欢快,5视图的创建create view 全部歌曲浏览as select 曲目表.歌曲编号,歌曲名称,歌手名字from 歌曲信息表,歌手表,曲目表where 歌曲信息表.歌曲编号=曲目表.歌曲编号 and 歌手表.歌手