本实例为综合实例，考察数据库原理中的，sql脚本的编写，创建——增删改查，视图和索引的创建等；数据库ER图，关系模式；以及一些高级的应用包括：触发器，函数和存储过程。
(一). 数据库设计题目如下
有一个图书出版发行管理系统，其主要业务规则如下：
一个作者可以编写多本图书，一个出版社可以出版多种图书，一种图书可以由多个作者编写、但只能由一个出版社在一定时间内出版；图书发行只批发给经销商，不零售，批发价格可能会随时调整。图书实体的属性包括书号（ISBN）、题名、价格、关键词、内容提要、库存量、批发价；作者实体的属性包括身份证号、姓名、电话；出版社实体的属性包括出版社代码、名称、地址、负责人、电话；另外，每种图书拥有许多教学资源文档，资源文档的属性包括文档ID、文档名、摘要、关键词、资源路径、下载次数；经销商实体包括工商号、银行账号、名称、负责人、办公电话、移动电话、地址、邮编。读者可以自由查询出版社的图书书目信息，通过注册后方可下载教学资源。读者可以通过作者名、出版社名、书名、ISBN号、关键词查找图书，也可以通过文档名、摘要、关键词等途径查找教学资源。
1.请设计出此系统的数据库模型的ERD:
1.1ER图
ER图设计注意的问题如下:
1.1.ER图的设计可以用visio设计，上图既是用此软件设计.
1.2.每一个实体的主键属性都用下划线注明
1.3.ER图中的属性中不要加入外键。关系模式实体联系中才会出现外键作为自己的“属性”
2.将ER图转换为关系模式:
关系模式如下:
图书（ISBN，书名，关键词，内容提要，价格，批发价，库存，出版社编号）
主键为ISBN.
外键为出版社编号
作者（身份证，姓名，电话）
主键为身份证
没有外键
出版社（编号，名称，地址，负责人，电话）
主键为编号
没有外键
教学资源（资源编号，文档名，摘要，关键词，资源路径，下载次数，ISBN）
主键为资源编号
外键为ISBN
经销商（工商号，名称，银行账号，地址，邮编，负债人，办公电话，移动电话）
主键为工商号
没有外键
用户（用户号，姓名，密码）
主键为 用户号
没有外键
编著（身份证，ISBN，排名)
外键为身份证，ISBN
销售（ISBN,工商号，数量，价格）
外键为ISBN,工商号
3. 编写创建此数据库的SQL。
create database yourdatabase;
4.定义此数据库系统方案中的所有数据库对象（表，索引）
注意:根据关系模式创建物理表。
主键的索引，在创建表格的时自动创建，题目提到的根据“什么等等”查询的属性都要在相应的表列上创建索引。
创建出版社表
Create Table publisher
(
pub_id varchar(16) primary key,
name varchar(256) not null unique,
addr varchar(256),
boss varchar(32),
tel varchar(32)
);
go
为出版社名创建索引
create index idx_publisher_name on publisher(name);
go
创建作者表
Create table author
(
id_card varchar(18) primary key,
name varchar(32),
tel varchar(32)
);
go
以作者名创建索引
create index idx_author_name on author(name);
go
创建书籍表
Create Table books
(
isbn varchar(16) primary key,
title varchar(256) not null,
keyword varchar(32),
summary varchar(2048),
price numeric(6,1) default 0,
w_price numeric(6,1) default 0,
stocks int default 0,
pub_date datetime default getdate(),
pub_id varchar(16) foreign key references publisher(pub_id) on update cascade
);
go
在书主题和书关键字列创建索引
create index idx_books_title on books(title);
create index idx_books_keyword on books(keyword);
go
drop table Resources;
go
创建资源表
create table Resources
(
id int IDENTITY primary key ,
filename varchar(128),
keyword varchar(32),
summary varchar(2048),
url varchar(64),
downloads int default 0,
isbn varchar(16) foreign key references books(isbn) on update cascade
);
go
创建索引
create index idx_resources_filename on resources(filename);
create index idx_resources_keyword on resources(keyword);
create index idx_resources_summary on resources(summary);
go
创建经销商表
create table dealer
(
code varchar(32) primary key,
name varchar(80) not null unique,
account varchar(256),
addr varchar(256),
poscode varchar(6) check(len(poscode)=6),
boss varchar(32),
o_tel varchar(32),
h_tel varchar(32)
);
go
创建用户信息表
create table userinfo
(
userid varchar(16) primary key ,
name varchar(32) ,
password varchar(16)
);
go
创建编著表
create table write
(
id_card varchar(18) foreign key references author(id_card) on update cascade,
isbn varchar(16) foreign key references books(isbn) on update cascade,
sort int default 1
);
go
创建销售表
create table sales
(
isbn varchar(16) foreign key references books(isbn) on update cascade,
dealer varchar(32) foreign key references dealer(code) on update cascade,
counts int default 0,
w_price numeric(6,1)
);
go
(5)设计一个视图，返回作者查找到的图书信息，属性包括书名、作者名、出版社名、出版时间、价格、内容提要。要求写出视图查询的关系代数表达式和SQL语句
create view v_books as
select b.title,a.name as author,p.name as publisher,b.pub_date,b.price,b.summary
from books b,author a,write w,publisher p
where w.id_card=a.id_card and w.isbn=b.isbn and b.pub_id=p.pub_id;
go
(6)设计一个视图，返回读者查询到的教学资源清单，属性包括书名、作者名、资源名、下载地址。要求写出视图查询的关系代数表达式和SQL语句
create view v_resources as
select b.title,a.name as author,r.filename as filename,r.url
from books b,author a,write w,Resources r
where w.id_card=a.id_card and w.isbn=b.isbn and r.isbn=b.isbn
go
πtitle,name,author,filename,url(books author write Resources);
(7)设计一个函数，实现教学资源下载地址生成功能，输入一个教学资源的ID，要求返回一个URI,格式为：域名（）+’/resource/’+资源路径。
create function get_url(@id int) returns varchar(256) as
begin
declare
@ret varchar(256);
set @ret='';
select @ret='/resource/'+url from Resources where id=@id;
return @ret;
end;
go
select dbo.get_url(4);
select'www\.ptpub\.com\.cn/resource/'+url from Resources where id=3;
调用如下:
select dbo.get_url(10);
或者
print dbo.get_author(9787115338488);
(8)编写1个存贮过程，完成图书批发业务处理，处理过程为：将指定的图书按当前的批发价格批发一定数量给指定的经销商。
create Procedure prg_book
@isbn varchar(16) ,
@dealer varchar(16) ,
@count int ,
@w_price numeric(6,1)
as
/*检测是否合法*/
if not exists(select title from books where isbn=@isbn)
return 6001
if not exists(select name from dealer where code=@dealer)
return 6002
if not exists(select title from books where isbn=@isbn and stocks >=@count)
return 6003
insert into sales values(@isbn , @dealer , @count , @w_price);
go
(9)编写1个触发器完成图书库存量的自动减。
create trigger newtrigger
on sales for insert
as
begin
declare @isbn varchar(16) , @num int;
select @isbn = isbn , @num = counts from inserted;
update books set stocks = stocks - @num where isbn = @isbn ;
end;
