-- =============================================
-- zhen:模仿pubs库 《图书馆库》
-- 12:51 2011/12/7
-- ylb, tech
-- 感谢贡献者：lgz
-- =============================================
USE master
GO
-- Drop the database if it already exists
IF EXISTS (
SELECT name
FROM sysdatabases
WHERE name = N'fpubs'
)
DROP DATABASE fpubs
GO
CREATE DATABASE fpubs
GO
use fpubs
go
-- =============================================
-- zhen：1，图书表
-- =============================================
create table titles
(
title_id varchar(6) primary key , --编号【PK】
title varchar(80) not null, --标题
[type] char(12) default('undecided') not null, --类型
pub_id char(4) null, --出版社编号【FK】
price money null, --单价
advance money null, --预付款
royalty int null,
ytd_se int null,
notes varchar(200) null, --备注
pubdate datetime default(getdate()) not null --上架日期
)
go
-- =============================================
-- zhen：2，作者表
-- =============================================
create table authors
(
au_id varchar(11) primary key check([au_id] like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'), --编号【pk】
au_iname varchar(40) not null, --姓
au_fname varchar(20) not null, --名
phone char(12) default'(未知的)' not null, -- 手机号码
address varchar(40), --地址
city varchar(20), --城市
[state] char(5), --洲
zip char(5) check([zip] like '[0-9][0-9][0-9][0-9][0-9]'), --邮编
[contract] bit --是否在签约中；0；不在；1：在。
)
-- =============================================
-- zhen：3，图书-作者表
-- =============================================
create table titleauthor
(
au_id varchar(11) foreign key references authors(au_id), --作者编号[FK]
title_id varchar(6) foreign key references titles(title_id), --图书编号[FK]
au_ord tinyint null, --
royaltyper int null,
primary key(au_id,title_id) --设置联合主键[PK]
)
--drop table titleauthor
go
-- =============================================
-- zhen：4，storesn. 贮藏；备用品；商店（store的复数）v. 储存；供应；容纳（store的三单形式）
-- =============================================
create table stores
(
stor_id char(4) primary key not null, --商店编号[PK]
stor_name varchar(40) null, --商店名称
stor_address varchar(40) null, --商店地址
city varchar(20) null, --所在城市
state char(2) null, --所在州
zip char(5) null --邮编
)
-- =============================================
-- zhen：5，discounts优惠，折价
-- =============================================
create table discounts
(
discounttype varchar(40) not null, --折扣类型
stor_id char(4) foreign key references stores(stor_id) , --商店编号[FK]
lowqty smallint null, --数量下限
highqty smallint null, --数量上限
discount decimal(4,2) not null , --折扣
)
--drop table discounts
-- =============================================
-- zhen：6，jobs 工作（job的复数形式）
-- =============================================
create table jobs
(
job_id smallint primary key not null, --工作编号[FK]
job_desc varchar(50) DEFAULT ('New Position - title not formalized yet') not null, --工作描述
min_lvl tinyint CHECK ([min_lvl] >= 10) not null, --
max_lvl tinyint CHECK ([max_lvl] <= 250) not null --
)
--drop table jobs
-- =============================================
-- zhen：7，publishers--出版社
-- =============================================
create table publishers
(
pub_id char(4) primary key CHECK([pub_id] = '1756' or ([pub_id] = '1622' or ([pub_id] = '0877' or ([pub_id] =
'0736' or [pub_id] = '1389'))) or [pub_id] like '99[0-9][0-9]'), --出版社编号[PK]
pub_name varchar(40) null, --出版社名称
city varchar(20) null, --所在城市
state char(2) null, --所在州
country varchar(30) DEFAULT ('USA') null --所在国家
)
--drop table publishers
-- =============================================
-- zhen：8，pub_info出版社详细
-- =============================================
create table put_info
(
pub_id char(4) primary key not null, --出版社编号[PK]
logo image null, --标志图
pr_info char(4) foreign key references publishers(pub_id) null --出版信息[FK]
)
--drop table put_info
-- =============================================
-- zhen：9，employee雇员；从业员工
-- =============================================
create table employee
(
emp_id int primary key CHECK([emp_id] like '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or [emp_id] like
'[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]') not null, --职工编号
fname varchar(20) not null, --职工名
minit char(1) null, --
lname varchar(30) not null, --职工姓
job_id smallint DEFAULT(1) foreign key references jobs(job_id) not null, --工作编号[FK]
job_lvl tinyint DEFAULT (10) null, --
pub_id char(4) DEFAULT ('9952') foreign key references publishers(pub_id) not null, --出版社编号[FK]
hire_date datetime DEFAULT (getdate()) not null --工作日期
)
--drop table employee
-- =============================================
-- zhen：10，roysched
-- =============================================
--drop table roysched
create table roysched
(
title_id varchar(6) foreign key references titles(title_id), --书编号[FK]
lorange int null, --低
hirange int null, --高
royalty int null --版权
)
-- =============================================
-- zhen：11，salesadj. 销售的，售货的；有关销售的n. 销售额；销售（sale的复数）
-- =============================================
create table sales
(
stor_id char(4) foreign key references stores(stor_id), --商店编号[FK]
ord_num varchar(20), --订单编码
ord_date datetime not null, --订购日期
qty smallint not null, --数量
payyterms varchar(12) not null, --付款方式
title_id varchar(6) foreign key references titles(title_id) not null, --书编号[FK]
primary key (stor_id,ord_num,title_id) --设置联合主键[PK]
)
--drop table sales
-- =============================================
-- zhen：12，usersn. 使用者；受限用户（user的复数）
-- =============================================
create table users
(
uid char(10) primary key not null, --用户名【PK】
uname char(10) not null, --
ups char(10) not null, --
gender char(2) DEFAULT ('男') null, --性别
age int CHECK (([age] >= 15 and [age] <= 80)) null, --年龄
upower char(10) DEFAULT ('Guest') null, --
sex char(2) null --性别
)
--drop table users
--print '创建图书馆库成功！'
