手册中的示例说明

为方便读者阅读并尽快学会使用 DM系统，本手册在介绍利用 DM建立、维护数据库以

及对数据库进行的各种操作中，使用了示例库 BOOKSHOP。本章将对该示例库进行说明。

2.1 示例库说明

示例库 BOOKSHOP 模拟武汉代理图书的某销售公司，该公司欲建立在线购物平台来拓

展其代理产品的销售渠道，该在线购物平台支持网上产品信息浏览、订购等服务(仅限同城

内销售及送货)。该销售公司的雇员、部门信息、业务方案等是所有实例的基础。

安装该示例后，将在数据库中创建 BOOKSHOP表空间，同时创建 RESOURCES、PERSON、

PRODUCTION、PURCHASING、SALES、OTHER这 6个模式和相关的表。示例库 BOOKSHOP

的默认数据库名为 DAMENG，默认实例名为 DMSERVER。

示例库 BOOKSHOP所包含的模式、表如下：

表 2.1.1 BOOKSHOP

模式包含相关对象包含的表

RESOURCES 公司基本信息表 EMPLOYEE

EMPLOYEE_ADDRESS

DEPARTMENT

EMPLOYEE_DREPARTMENT

PERSON 各个客户、雇员、供应商的名称和地址 ADDRESS

ADDRESS_TYPE

PERSON

PERSON_TYPE

PRODUCTION 产品销售的信息 PRODUCT

PRODUCT_CATEGORY

PRODUCT_SUBCATEGORY

PRODUCT_INVENTORY

LOCATION

PRODUCT_REVIEW

PRODUCT_VENDOR

PURCHASING 供应商列表(采购订单等) PURCHASEORDER_HEADER

PURCHASEORDER_DETIAL

VENDOR

VENDOR_ADDRESS

VENDOR_PERSON

SALES 与客户销售相关的数据(销售定单等) CUSTMER

CUSTMER_ADDRESS

SALESORDER_HEADER

SALESORDER_DETAIL

SALESPERSON

OTHER 用到的其他表 DEPARTMENT

EMPSALARY

ACCOUNT

ACTIONS

READER

READERAUDIT

DEPTTAB

EMPTAB

SALGRADE

COMPANYHOLIDAYS

EMPLOYEE

该公司的雇员信息表，包含在 RESOURCES模式中。

表 2.1.2 EMPLOYEE

列数据类型是否为空说明

EMPLOYEEID INT 非空主键，自增列

NATIONALNO VARCHAR(18) 非空身份证号码

PERSONID INT 非空指向 PERSON.PERSONID的外键

LOGINID VARCHAR(256) 非空用户登录 ID

TITLE VARCHAR(50) 非空职位

MANAGERID INT 空

直接上司 ID，指 EMPLOYEE.EMPLOYEEID

的外键

BIRTHDATE DATE 非空出生日期

MARITALSTATUS CHAR(1) 非空

S=未婚

M=已婚

PHOTO IMAGE 空照片

HAIRDATE DATE 非空入职时间

SALARY DEC(19,4) 非空薪资（元）

EMPLOYEE_ADDRESS

将雇员映射到 ADDRESS表中的地址信息，包含在 RESOURCES模式中。

表 2.1.3 EMPLOYEE_ADDRESS

列数据类型是否为空说明

EMPLOYEEID INT 非空指向 EMPLOYEE.EMPLOYEEID的外键

ADDRESSID INT 非空指向 ADDRESS.ADDRESSID的外键

EMPLOYEE_DEPARTMENT

将雇员映射到 DEPARTMENT表中的部门信息，包含在 RESOURCES模式中。

表 2.1.4 EMPLOYEE_DEPARTMENT

列数据类型是否为空说明

EMPLOYEEID INT 非空指向 EMPLOYEE.EMPLOYEEID的外键

DEPARTMENTID INT 非空

员工所在部门，指向 DEPARTMENT. DEPARTMENTID

的外键

STARTDATE DATE 非空在该部门开始工作的日期

ENDDATE DATE 空

DEPARTMENT

离开该部门的日期

空=雇员在当前部门

wps261wps262wps263wps264wps265wps266wps267第 2章手册中的示例说明

该公司的部门信息，包含在 RESOURCES模式中。

表 2.1.5 DEPARTMENT

列数据类型是否为空说明

DEPARTENTID INT 非空主键，自增列

NAME VARCHAR(50) 非空部门名称

PERSON

该公司所有雇员、供应商、客户的姓名信息表，包含在 PERSON模式中。

表 2.1.6 PERSON

列数据类型是否为空说明

PERSONID INT 非空主键，自增列，聚集索引

NAME VARCHAR(50) 非空姓名

SEX CHAR(1) 非空

M=男

F=女

EMAIL VARCHAR(50) 空电子邮件地址

PHONE VARCHAR(25) 空电话

PERSON_TYPE

存储 PERSON 表中的联系人类型，客户中的联系人类型有采购经理、采购代表，供应

商的联系人类型有销售经理、销售代表。包含在 PERSON模式中。

表 2.1.7 PERSON_TYPE

列数据类型是否为空说明

PERSON_TYPEID INT 非空主键，自增列

NAME VARCHAR(50) 非空联系人类型说明

ADDRESS

雇员、客户、供应商的地址信息，包含在 PERSON模式中。

表 2.1.8 ADDRESS

列数据类型是否为空说明

ADDRESSID INT 非空主键，自增列

ADDRESS1 VARCHAR(60) 非空第一通讯地址

ADDRESS2 VARCHAR(60) 空第二通讯地址

CITY VARCHAR(30) 非空市/县名称

POSTALCODE VARCHAR(15) 非空邮政编码

ADDRESS_TYPE

为雇员、客户、供应商定义地址类型的表，包含在 PERSON模式中。

表 2.1.9 ADDRESS_TYPE

列数据类型是否为空说明

ADDRESS_TYPEID INT 非空主键，自增列

NAME VARCHAR(50) 非空

地址类型的说明如开票地址、家庭地址、

送货地址、公司地址等

CUSTOMER

当前客户信息，包含在 SALES模式中。

表 2.1.10 CUSTOMER

列数据类型是否为空说明

CUSTOMERID INT 非空主键，自增列

wps268wps269wps270wps271wps272wps273第 2章手册中的示例说明

PERSONID INT 非空指向 PERSON.PERSONID的外键

CUSTOMER_ADDRESS

将客户映射到某个地址或多个地址，包含在 SALES模式中。

表 2.1.11 CUSTOMER_ADDRESS

列数据类型是否为空说明

CUSTOMERID INT 非空主键，指向 CUSTOMER.CUSTOMERID的外键

ADDRESSID INT 非空主键，指向 ADDRESS.ADDRESSID的外键

ADDRESS_TYPEID INT 非空

地址类型，指向 ADDRESS_TYPE.ADDRESS_TYPEID

的外键

SALESPERSON

销售代表的销售统计信息，包含在 SALES模式中。

表 2.1.12 SALESPERSON

列数据类型是否为空说明

SALESPERSONID INT 非空主键，自增列

EMPLOYEEID INT 非空

该销售代表对应的雇员号，指向

EMPLOYEE.EMPLOYEEID的外键

SALESTHISYEAR DEC(19,4) 非空今年到目前为止销售总额(万)

SALESLASTYEAR DEC(19,4) 非空去年销售总额(万)

VENDOR

所有供应商公司信息，包含在 PURCHASING模式中。

表 2.1.13 VENDOR

列数据类型是否为空说明

VENDORID INT 非空主键，自增列

ACCOUNTNO VARCHAR(15) 非空供应商账户号

NAME VARCHAR(50) 非空供应商名称

0=不再使用供应商提供的产品

ACTIVEFLAG BIT 非空 1=正在使用供应商提供的产品

CHECK约束

WEBURL VARCHAR(1024) 空供应商服务 URL

1=高级

2=很好

CREDIT INT 非空

3=较好

4=一般

5=较差

CHECK约束

VENDOR_ADDRESS

所有供应商地址信息，包含在 PURCHASING模式中。

表 2.1.14 VENDOR_ADDRESS

列数据类型是否为空说明

VENDORID INT 非空主键，指向 VENDOR.VENDORID 的外键

ADDRESSID INT 非空主键，指向 ADDRESS.ADDRESSID外键

ADDRESS_TYPEID INT 非空

地址类型，指向 ADDRESS_TYPE.ADDRESS_TYPEID

外键

VENDOR_PERSON

wps274wps275wps276wps277wps278第 2章手册中的示例说明

供应商联系人信息表。

表 2.1.15 VENDOR_PERSON

列数据类型是否为空说明

VENDORID INT 非空主键，指向 VENDOR.VENDORID外键

PERSONID INT 非空主键，指向 PERSON.PERSONID外键

PERSON_TYPEID INT 非空

联系人的类型，指向 PERSON_TYPE.PERSON_TYPEID

的外键

PRODUCT

该公司售出的所有产品(图书)，包含在 PRODUCTION模式中。

表 2.1.16 PRODUCT

列数据类型是否为空说明

PRODUCTID INT 非空主键，自增列

NAME VARCHAR(50) 非空产品名称

AUTHOR VARCHAR(25) 非空作者

PUBLISHER VARCHAR(50) 非空出版社

PUBLISHTIME DATE 非空出版时间

产品所属子类别，

PRODUCT_SUBCATEGORYID INT 非空 PRODUCT_SUBCATEGORY .PROD

UCT_SUBCATEGORYID的外键

PRODUCTNO VARCHAR(25) 非空唯一产品标识号， unique约束

DESCRIPTION TEXT 空产品的说明\简介

PHOTO IMAGE 空产品的照片

SATETYSTOCKLEVEL SMALLINT 非空最小库存量

ORIGINALPRICE DEC(19,4) 非空原价(初始价格)

NOWPRICE DEC(19,4) 非空当前销售价格

DISCOUNT

DECIMAL(2,1

)

非空折扣

TYPE VARCHAR(5) 空产品开本规格，如 16开

PAPERTOTAL INT 空总页数

WORDTOTAL INT 空总字数

SELLSTARTTIME DATE 非空开始销售日期

SELLENDTIME DATE 空停止销售的日期

PRODUCT_CATEGORY

产品分类表，包含在 PRODUCTION模式中。

表 2.1.17 PRODUCT_CATEGORY

列数据类型是否为空说明

PRODUCT_CATEGORYID INT 非空产品类别 ID，主键，自增列

NAME VARCHAR(50) 非空产品类别名称

PRODUCT_SUBCATEGORY

产品子分类表，包含在 PRODUCTION模式中。

表 2.1.18 PRODUCT_SUBCATEGORY

列数据类型是否为空说明

PRODUCT_SUBCATEGORYID INT 非空产品子类别 ID，主键,自增列

wps279wps280wps281wps282wps283wps284第 2章手册中的示例说明

PRODUCT_CATEGORYID INT 非空产品类别 ID

NAME VARCHAR(50) 非空产品类别名称

PRODUCT_INVENTORY

产品的库存信息，包含在 PRODUCTION模式中。

表 2.1.19 PRODUCT_INVENTORY

列数据类型是否为空说明

PRODUCTID INT 非空产品标识，指向 PRODUCT.PRODUCTID的外键

LOCATIONID INT 非空库存位置标识，指向 LOCATION.LOCATIONID的外键

QUANTITY INT 非空库存位置的产品数量

PRODUCT_VENDOR

产品分类表，包含在 PRODUCTION模式中。

表 2.1.20 PRODUCT_VENDOR

列数据类型是否为空说明

PRODUCTID INT 非空主键，指向 PRODUCT.PRODUCTID外键

VENDORID INT 非空主键，指向 VENDOR.VENDORID外键

STANDARDPRICE DEC(19,4) 非空通常价格

LASTPRICE DEC(19,4) 空上次采购价格

LASTDATE DATE 空上次收到供应商产品的日期

MINQTY INT 非空应订购的最小定购数量

MAXQTY INT 非空应订购的最大定购数量

ONORDERQTY INT 空当前定购的数量

PRODUCT_REVIEW

已售产品的评论表，包含在 PRODCUTION模式中。

表 2.1.21 PRODUCT_REVIEW

列数据类型是否为空说明

PRODUCT_REVIEWID INT 非空主键，自增列

PRODUCTID INT 非空指向 PRODUCT.PRODUCTID外键

NAME VARCHAR(50) 非空评论人姓名

REVIEWDATE DATE 非空提交评论的日期

EMAIL VARCHAR(50) 非空评论人的 EMAIL地址

RATING INT 非空

评论人给出的产品等级

范围 1-5，5为最高级，CHECK约束

COMMENTS TEXT 空评论人的注释

LOCATION

产品的库存地址信息，包含在 PRODUCTION模式中。

表 2.1.22 LOCATION

列数据类型是否为空说明

LOCATIONID INT 非空主键，自增列

NAME

VARCHAR(50

)

非空地点说明

指向

PRODUCT_SUBCATEGORYID INT 非空 PRODUCT_SUBCATEGORY(PRODU

CT_SUBCATEGORYID)的外键

SALESORDER_HEADER

销售订单常规信息表，包含在 SALES模式中。

表 2.1.23 SALESORDER_HEADER

是

列数据类型

否

为

说明

空

SALESORDERID INT

非

空

订单 ID，主键，自增列

ORDERDATE DATE

非

空

创建订单的日期

DUEDATE DATE

非

空

客户订单到期的日期

订单状态， CHECK约束

1=待用户确认

STATUS TINYINT

非

空

2=待发货

3=已发货

4=已完成

5=意外终结

ONLINEORDERFLAG BIT

非

空

0=销售人员下的订单(包括电话订单)

1=在线订单

CUSTOMERID INT

非

空

客户标识号，指向 CUSTOMER.CUSTOMERID的外键

SALESPERSONID INT

非

空

销售代表 ID，指向 SALESPERSON.SALESPERSONID的

外键

ADDRESSID INT

非

空

客户收货地址，指向 ADDRESS.ADDRESSID外键

SHIPMETHOD BIT

发货方法

非

0= ( ) 免费送货仅限中心城区

空

1= (>=400 <400 10 ) 有偿送货元，免费送货；，运费元

SUBTOTAL DEC(19,4)

非

空

销售小计

计算方式:SUM(SALESORDER_DETAIL.LINETOTAL)

FREIGHT DEC(19,4)

非

空

运费(非中城区，subtotal>=400，freight=0；<400，

freight=10；中心城区， freight=0)

TOTAL DEC(19,4)

非

空

应付款总计 SUBTOTAL+FREIGHT

COMMENTS TEXT 空销售代表备注说明

SALESORDER_DETAIL

与特定销售订单关联的各个产品信息表，包含在 SALES模式中。

表 2.1.24 SALESORDER_DETAIL

列数据类型是否为空说明

主键，指向

SALESORDERID INT 非空 SALESORDER_HEADER.SALESORDER

ID的外键

SALESORDER_DETAILID INT 非空主键，确保数据唯一性的连续编号

CARRIERNO VARCHAR(25) 非空发货跟踪号

PRODUCTID INT 非空

定购产品的 ID，指向

PRODUCT.PRODUCTID的外键

ORDERQTY INT 非空每件产品定购数量

LINETOTAL DEC(19,4) 非空产品的销售小计

PURCHASEORDER_HEADER

采购订单常规信息，包含在 PURCHASING模式中。

表 2.1.25 PURCHASEORDER_HEADER

列数据类型是否为空说明

PURCHASEORDERID INT 非空订单 ID，主键，自增列

ORDERDATE DATE 非空创建订单的日期

订单状态， CHECK约束

0=等待批准

STATUS TINYINT 非空 1=已批准

2=已拒绝

3=已完成

EMPLOYEEID INT 非空

创建采购订单的雇员 ID，指向

EMPLOYEE.EMPLOYEEID的外键

VENDORID INT 非空

所采购产品的供应商 ID，指向

VENDOR.VENDORID的外键

SHIPMETHOD VARCHAR(50) 非空发货方法

SUBTOTAL DEC(19,4) 非空产品价格小计

TAX DEC(19,4) 非空税额

FREIGHT DEC(19,4) 非空运费

TOTAL DEC(19,4) 非空

应向供应商付款总计(SUBTOTAL+ TAX+

FREIGHT)

PURCHASEORDER_DETAIL

与特定采购订单相关联的每个产品的采购信息，包含在 PURCHASING模式中。

表 2.1.26 PURCHASEORDER_DETAIL

列数据类型是否为空说明

PURCHASEORDERID INT 非空主键，指向 PURCHASEORDER_HEADER

.PURCHASEORDERID的外键

PURCHASEORDER_DET INT 非空主键，确保数据唯一性的连续编号

AILID

DUEDATE DATE 非空希望从供应商收到产品的日期

PRODUCTID INT 非空定购产品的 ID，指向

PRODUCT.PRODUCTID

的外键

ORDERQTY INT 非空定购数量

PRICE DEC(19,4) 非空单件产品的价格

SUBTOTAL DEC(19,4) 非空产品价格小计(RPICE*ORDERQTY)

RECEIVEDQTY DECIMAL(8,2) 非空实际从供应商收到的数量

REJECTEDQTY DECIMAL(8,2) 非空检查时拒收的数量

STOCKEDQTY DECIMAL(8,2) 非空纳入库存的数量

2.2 参考脚本

2.2.1 创建示例库

--创建示例库

安装 DM时，如果选择安装示例库，系统会自动安装一个名为 BOOKSHOP的示例库。该示例库中，默

认数据库名称为 DAMENG，默认实例名为 DMSERVER。

如果安装时没有选择安装示例库，可以通过如下 SQL语句自行创建：

CREATE TABLESPACE BOOKSHOP DATAFILE 'BOOKSHOP.DBF' size 150;

2.2.2 创建模式及表

--创建模式

CREATE SCHEMA RESOURCES AUTHORIZATION SYSDBA;

CREATE SCHEMA PERSON AUTHORIZATION SYSDBA;

CREATE SCHEMA SALES AUTHORIZATION SYSDBA;

CREATE SCHEMA PRODUCTION AUTHORIZATION SYSDBA;

CREATE SCHEMA PURCHASING AUTHORIZATION SYSDBA;

CREATE SCHEMA OTHER AUTHORIZATION SYSDBA;

--创建表

--CREATE ADDRESS

CREATE TABLE PERSON.ADDRESS

(ADDRESSID INT IDENTITY(1,1) PRIMARY KEY,

ADDRESS1 VARCHAR(60) NOT NULL,

ADDRESS2 VARCHAR(60),

CITY VARCHAR(30) NOT NULL,

POSTALCODE VARCHAR(15) NOT NULL) STORAGE (on BOOKSHOP);

--CREATE ADDRESS_TYPE

CREATE TABLE PERSON.ADDRESS_TYPE

(ADDRESS_TYPEID INT IDENTITY(1,1) PRIMARY KEY,

NAME VARCHAR(50) NOT NULL) STORAGE (ON BOOKSHOP);

--CREATE PERSON

CREATE TABLE PERSON.PERSON

(

PERSONID INT IDENTITY(1,1) CLUSTER PRIMARY KEY,

SEX CHAR(1) NOT NULL,

NAME VARCHAR(50) NOT NULL,

EMAIL VARCHAR(50),

PHONE VARCHAR(25)) STORAGE (ON BOOKSHOP);

--CREATE PERSON_TYPE

CREATE TABLE PERSON.PERSON_TYPE

(PERSON_TYPEID INT IDENTITY(1,1) PRIMARY KEY,

NAME VARCHAR(256) NOT NULL) STORAGE (ON BOOKSHOP);

--CREATE DEPARTMENT

CREATE TABLE RESOURCES.DEPARTMENT(DEPARTMENTID INT IDENTITY(1,1) PRIMARY

KEY,NAME VARCHAR(50) NOT NULL) STORAGE (ON BOOKSHOP);

--CREATE EMPLOYEE

CREATE TABLE RESOURCES.EMPLOYEE(

EMPLOYEEID INT IDENTITY(1,1) PRIMARY KEY ,

NATIONALNO VARCHAR(18) NOT NULL,

PERSONID INT NOT NULL REFERENCES PERSON.PERSON(PERSONID),

LOGINID VARCHAR(256) NOT NULL,

TITLE VARCHAR(50) NOT NULL,

MANAGERID INT,

BIRTHDATE DATE NOT NULL,

MARITALSTATUS CHAR(1) NOT NULL,

PHOTO IMAGE,

HAIRDATE DATE NOT NULL,

SALARY DEC(19,4) NOT NULL

) STORAGE (ON BOOKSHOP);

--CREATE EMPLOYEE_ADDRESS

CREATE TABLE RESOURCES.EMPLOYEE_ADDRESS

(ADDRESSID INT NOT NULL REFERENCES PERSON.ADDRESS(ADDRESSID),

EMPLOYEEID INT NOT NULL REFERENCES RESOURCES.EMPLOYEE(EMPLOYEEID)) STORAGE

(ON BOOKSHOP);

--CREATE EMPLOYEE_DEPARTMENT

CREATE TABLE RESOURCES.EMPLOYEE_DEPARTMENT

(EMPLOYEEID INT NOT NULL REFERENCES RESOURCES.EMPLOYEE(EMPLOYEEID),

DEPARTMENTID INT NOT NULL REFERENCES

RESOURCES.DEPARTMENT(DEPARTMENTID),

STARTDATE DATE NOT NULL,

ENDDATE DATE) STORAGE (ON BOOKSHOP);

--CREATE CUSTOMER

CREATE TABLE SALES.CUSTOMER(CUSTOMERID INT IDENTITY(1,1) PRIMARY

KEY ,PERSONID INT NOT NULL REFERENCES PERSON.PERSON(PERSONID)) STORAGE (ON

BOOKSHOP);

--CREATE CUSTOMER_ADDRESS

CREATE TABLE SALES.CUSTOMER_ADDRESS

(CUSTOMERID INT REFERENCES SALES.CUSTOMER(CUSTOMERID),

ADDRESSID INT REFERENCES PERSON.ADDRESS(ADDRESSID),

ADDRESS_TYPEID INT NOT NULL REFERENCES PERSON.ADDRESS_TYPE(ADDRESS_TYPEID),

PRIMARY KEY (CUSTOMERID,ADDRESSID)) STORAGE (ON BOOKSHOP);

--CREATE PRODUCT_CATEGORY

CREATE TABLE PRODUCTION.PRODUCT_CATEGORY

(PRODUCT_CATEGORYID INT IDENTITY(1,1) PRIMARY KEY,

NAME VARCHAR(50) NOT NULL) STORAGE (ON BOOKSHOP);

--CREATE PRODUCT_SUBCATEGORY

CREATE TABLE PRODUCTION.PRODUCT_SUBCATEGORY

(PRODUCT_SUBCATEGORYID INT IDENTITY(1,1) PRIMARY KEY,

PRODUCT_CATEGORYID INT NOT NULL ,

NAME VARCHAR(50) NOT NULL) STORAGE (ON BOOKSHOP);

--CREATE PRODUCT

CREATE TABLE PRODUCTION.PRODUCT

(PRODUCTID INT IDENTITY(1,1) PRIMARY KEY,

NAME VARCHAR(50) NOT NULL,

AUTHOR VARCHAR(25) NOT NULL,

PUBLISHER VARCHAR(50) NOT NULL,

PUBLISHTIME DATE NOT NULL,

PRODUCT_SUBCATEGORYID INT NOT NULL REFERENCES

PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_SUBCATEGORYID),

PRODUCTNO VARCHAR(25) NOT NULL,

SATETYSTOCKLEVEL SMALLINT NOT NULL,

ORIGINALPRICE DEC(19,4) NOT NULL,

NOWPRICE DEC(19,4) NOT NULL,

DISCOUNT DECIMAL(2,1) NOT NULL,

DESCRIPTION TEXT,

PHOTO IMAGE,

TYPE VARCHAR(5),

PAPERTOTAL INT,

WORDTOTAL INT,

SELLSTARTTIME DATE NOT NULL,

SELLENDTIME DATE,

UNIQUE(PRODUCTNO)) STORAGE (ON BOOKSHOP);

--CREATE LOCATION

CREATE TABLE PRODUCTION.LOCATION

(LOCATIONID INT IDENTITY(1,1) PRIMARY KEY,

PRODUCT_SUBCATEGORYID INT NOT NULL REFERENCES

PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_SUBCATEGORYID),

NAME VARCHAR(50) NOT NULL) STORAGE (ON BOOKSHOP);

--CREATE PRODUCT_INVENTORY

CREATE TABLE PRODUCTION.PRODUCT_INVENTORY

(PRODUCTID INT NOT NULL REFERENCES PRODUCTION.PRODUCT(PRODUCTID),

LOCATIONID INT NOT NULL REFERENCES PRODUCTION.LOCATION(LOCATIONID),

QUANTITY INT NOT NULL) STORAGE (ON BOOKSHOP);

--CREATE PRODUCT_REVIEW

CREATE TABLE PRODUCTION.PRODUCT_REVIEW

(PRODUCT_REVIEWID INT IDENTITY(1,1) PRIMARY KEY,

PRODUCTID INT NOT NULL REFERENCES PRODUCTION.PRODUCT(PRODUCTID),

NAME VARCHAR(50) NOT NULL,

REVIEWDATE DATE NOT NULL,

EMAIL VARCHAR(50) NOT NULL,

RATING INT NOT NULL CHECK(RATING IN(1,2,3,4,5)),

COMMENTS TEXT) STORAGE (ON BOOKSHOP);

--CREATE VENDOR

CREATE TABLE PURCHASING.VENDOR

(VENDORID INT IDENTITY(1,1) PRIMARY KEY,

ACCOUNTNO VARCHAR(15) NOT NULL,

NAME VARCHAR(50) NOT NULL,

ACTIVEFLAG BIT NOT NULL,

WEBURL VARCHAR(1024),

CREDIT INT NOT NULL CHECK(CREDIT IN(1,2,3,4,5))) STORAGE (ON BOOKSHOP);

--CREATE VENDOR_ADDRESS

CREATE TABLE PURCHASING.VENDOR_ADDRESS

(VENDORID INT NOT NULL REFERENCES PURCHASING.VENDOR(VENDORID),

ADDRESSID INT NOT NULL REFERENCES PERSON.ADDRESS(ADDRESSID),

ADDRESS_TYPEID INT NOT NULL REFERENCES PERSON.ADDRESS_TYPE(ADDRESS_TYPEID),

PRIMARY KEY (VENDORID,ADDRESSID)) STORAGE (ON BOOKSHOP);

--CREATE VENDOR_PERSON

CREATE TABLE PURCHASING.VENDOR_PERSON

(VENDORID INT NOT NULL REFERENCES PURCHASING.VENDOR(VENDORID),

PERSONID INT NOT NULL REFERENCES PERSON.PERSON(PERSONID),

PERSON_TYPEID INT NOT NULL REFERENCES PERSON.PERSON_TYPE(PERSON_TYPEID),

PRIMARY KEY (VENDORID,PERSONID)) STORAGE (ON BOOKSHOP);

--CREATE PRODUCT_VENDOR

CREATE TABLE PRODUCTION.PRODUCT_VENDOR

(PRODUCTID INT REFERENCES PRODUCTION.PRODUCT(PRODUCTID),

VENDORID INT REFERENCES PURCHASING.VENDOR(VENDORID),

STANDARDPRICE DEC(19,4) NOT NULL,

LASTPRICE DEC(19,4),

LASTDATE DATE,

MINQTY INT NOT NULL,

MAXQTY INT NOT NULL,

ONORDERQTY INT,

PRIMARY KEY(PRODUCTID,VENDORID)) STORAGE (ON BOOKSHOP);

--CREATE SALESPERSON

CREATE TABLE SALES.SALESPERSON

(SALESPERSONID INT IDENTITY(1,1) PRIMARY KEY,

EMPLOYEEID INT NOT NULL REFERENCES RESOURCES.EMPLOYEE(EMPLOYEEID),

SALESTHISYEAR DEC(19,4) NOT NULL,

SALESLASTYEAR DEC(19,4) NOT NULL) STORAGE (ON BOOKSHOP);

--CREATE PURCHASEORDER_HEADER

CREATE TABLE PURCHASING.PURCHASEORDER_HEADER

(PURCHASEORDERID INT IDENTITY(1,1) PRIMARY KEY,

ORDERDATE DATE NOT NULL,

STATUS TINYINT NOT NULL CHECK(STATUS IN(0,1,2,3)),

EMPLOYEEID INT NOT NULL REFERENCES RESOURCES.EMPLOYEE(EMPLOYEEID),

VENDORID INT NOT NULL REFERENCES PURCHASING.VENDOR(VENDORID),

SHIPMETHOD VARCHAR(50) NOT NULL,

SUBTOTAL DEC(19,4) NOT NULL,

TAX DEC(19,4) NOT NULL,

FREIGHT DEC(19,4) NOT NULL,

TOTAL DEC(19,4) NOT NULL) STORAGE (ON BOOKSHOP);

--CREATE PURCHASEORDER_DETAIL

CREATE TABLE PURCHASING.PURCHASEORDER_DETAIL

(PURCHASEORDERID INT NOT NULL REFERENCES

PURCHASING.PURCHASEORDER_HEADER(PURCHASEORDERID),

PURCHASEORDER_DETAILID INT NOT NULL,

DUEDATE DATE NOT NULL,

PRODUCTID INT NOT NULL REFERENCES PRODUCTION.PRODUCT(PRODUCTID),

ORDERQTY INT NOT NULL,

PRICE DEC(19,4) NOT NULL,

SUBTOTAL DEC(19,4) NOT NULL,

RECEIVEDQTY INT NOT NULL,

REJECTEDQTY INT NOT NULL,

STOCKEDQTY INT NOT NULL,

PRIMARY KEY(PURCHASEORDERID,PURCHASEORDER_DETAILID)) STORAGE (ON BOOKSHOP);

--CREATE SALESORDER_HEADER

CREATE TABLE SALES.SALESORDER_HEADER

(SALESORDERID INT IDENTITY(1,1) PRIMARY KEY,

ORDERDATE DATE NOT NULL,

DUEDATE DATE NOT NULL,

STATUS TINYINT NOT NULL CHECK(STATUS IN(0,1,2,3,4,5)),

ONLINEORDERFLAG BIT NOT NULL,

CUSTOMERID INT NOT NULL REFERENCES SALES.CUSTOMER(CUSTOMERID),

SALESPERSONID INT NOT NULL REFERENCES SALES.SALESPERSON(SALESPERSONID),

ADDRESSID INT NOT NULL REFERENCES PERSON.ADDRESS(ADDRESSID),

SHIPMETHOD BIT NOT NULL,

SUBTOTAL DEC(19,4) NOT NULL,

FREIGHT DEC(19,4) NOT NULL,

TOTAL DEC(19,4) NOT NULL,

COMMENTS TEXT) STORAGE (ON BOOKSHOP);

--CREATE SALESORDER_DETAIL

CREATE TABLE SALES.SALESORDER_DETAIL

(SALESORDERID INT NOT NULL REFERENCES

SALES.SALESORDER_HEADER(SALESORDERID),

SALESORDER_DETAILID INT NOT NULL,

CARRIERNO VARCHAR(25) NOT NULL,

PRODUCTID INT NOT NULL REFERENCES PRODUCTION.PRODUCT(PRODUCTID),

ORDERQTY INT NOT NULL,

LINETOTAL DEC(19,4) NOT NULL,

PRIMARY KEY(SALESORDERID,SALESORDER_DETAILID)) STORAGE (ON BOOKSHOP);

--CREATE OTHER.DEPARTMENT

CREATE TABLE OTHER.DEPARTMENT

(

HIGH_DEP VARCHAR(50),

DEP_NAME VARCHAR(50)) STORAGE (ON BOOKSHOP);

--CREATE OTHER.EMPSALARY

CREATE TABLE OTHER.EMPSALARY

(

ENAME CHAR(10),

EMPNO NUMERIC(4),

SAL NUMERIC(4)) STORAGE (ON BOOKSHOP);

--CREATE OTHER.ACCOUNT

CREATE TABLE OTHER.ACCOUNT

(

"ACCOUNT_ID" INTEGER NOT NULL,

"BAL" DEC(10,2),

PRIMARY KEY("ACCOUNT_ID"))STORAGE (ON BOOKSHOP);

--CREATE OTHER.ACTIONS

CREATE TABLE OTHER.ACTIONS

(

"ACCOUNT_ID" INTEGER NOT NULL,

"OPER_TYPE" CHAR(1),

"NEW_VALUE" DEC(10,2),

"STATUS" VARCHAR(50),

PRIMARY KEY("ACCOUNT_ID"))STORAGE (ON BOOKSHOP);

--CREATE OTHER.READER

CREATE TABLE OTHER.READER

(

READER_ID INT PRIMARY KEY,

NAME VARCHAR(30),

AGE SMALLINT,

GENDER CHAR,

MAJOR VARCHAR(30)) STORAGE (ON BOOKSHOP);

--CREATE OTHER.READERAUDIT

CREATE TABLE OTHER.READERAUDIT

(

CHANGE_TYPE CHAR NOT NULL,

CHANGED_BY VARCHAR(8) NOT NULL,

OP_TIMESTAMP DATE NOT NULL,

OLD_READER_ID INT,

OLD_NAME VARCHAR(30),

OLD_AGE SMALLINT,

OLD_GENDER CHAR,

OLD_MAJOR VARCHAR(30),

NEW_READER_ID INT,

NEW_NAME VARCHAR(30),

NEW_AGE SMALLINT,

NEW_GENDER CHAR,

NEW_MAJOR VARCHAR(30)) STORAGE (ON BOOKSHOP);

--CREATE OTHER.DEPTTAB

CREATE TABLE OTHER.DEPTTAB

(

DEPTNO INT PRIMARY KEY,

DNAME VARCHAR(15),

LOC VARCHAR(25)) STORAGE (ON BOOKSHOP);

--CREATE OTHER.EMPTAB

CREATE TABLE OTHER.EMPTAB

(

EMPNO INT PRIMARY KEY,

ENAME VARCHAR(15) NOT NULL,

JOB VARCHAR(10),

SAL FLOAT,

DEPTNO INT) STORAGE (on BOOKSHOP);

--CREATE OTHER.SALGRADE

CREATE TABLE OTHER.SALGRADE

(

LOSAL FLOAT,

HISAL FLOAT,

JOB_CLASSIFICATION VARCHAR(10)) STORAGE (ON BOOKSHOP);

--CREATE OTHER.COMPANYHOLIDAYS

CREATE TABLE OTHER.COMPANYHOLIDAYS

(

HOLIDAY DATE) STORAGE (ON BOOKSHOP);

2.2.3 插入数据

--插入数据

--INSERT ADDRESS

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山

区 369号金地太阳城 56-1-202','','武汉市洪山区','430073');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山

区 369号金地太阳城 57-2-302','','武汉市洪山区','430073');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('青山

区青翠苑 1号','','武汉市青山区','430080');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('武昌

区武船新村 115号','','武汉市武昌区','430063');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('汉阳

大道熊家湾 15号','','武汉市汉阳区','430050');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山

区保利花园 50-1-304','','武汉市洪山区','430073');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山

区保利花园 51-1-702','','武汉市洪山区','430073');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山

区关山春晓 51-1-702','','武汉市洪山区','430073');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('江汉

区发展大道 561号','','武汉市江汉区','430023');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('江汉

区发展大道 555号','','武汉市江汉区','430023');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('武昌

区武船新村 1号','','武汉市武昌区','430063');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('江汉

区发展大道 423号','','武汉市江汉区','430023');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山

区关山春晓 55-1-202','','武汉市洪山区','430073');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山

区关山春晓 10-1-202','','武汉市洪山区','430073');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山

区关山春晓 11-1-202','','武汉市洪山区','430073');

INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山

区光谷软件园 C1_501','','武汉市洪山区','430073');

--INSERT ADDRESS_TYPE

INSERT INTO PERSON.ADDRESS_TYPE(NAME) VALUES('发货地址');

INSERT INTO PERSON.ADDRESS_TYPE(NAME) VALUES('送货地址');

INSERT INTO PERSON.ADDRESS_TYPE(NAME) VALUES('家庭地址');

INSERT INTO PERSON.ADDRESS_TYPE(NAME) VALUES('公司地址');

--INSERT DEPARTMENT

INSERT INTO RESOURCES.DEPARTMENT(NAME) VALUES('采购部门');

INSERT INTO RESOURCES.DEPARTMENT(NAME) VALUES('销售部门');

INSERT INTO RESOURCES.DEPARTMENT(NAME) VALUES('人力资源');

INSERT INTO RESOURCES.DEPARTMENT(NAME) VALUES('行政部门');

INSERT INTO RESOURCES.DEPARTMENT(NAME) VALUES('广告部');

--INSERT PERSON

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F',' 李丽

','lily@sina.com','02788548562');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('M',' 王刚

','','02787584562');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('M',' 李勇

','','02782585462');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F',' 郭艳

','','02787785462');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F',' 孙丽

','','13055173012');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('M',' 黄非

','','13355173012');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F',' 王菲

','','13255173012');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('M',' 张平

','','13455173012');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('M',' 张红

','','13555173012');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F',' 刘佳

','','13955173012');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F',' 王南

','','15955173012');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F',' 李飞

','','15954173012');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F',' 张大海

','','15955673012');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F',' 王宇轩

','','15955175012');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F',' 桑泽恩

','','15955173024');

INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F',' 刘青

','','15955173055');

INSERT INTO PERSON.PERSON(SEX,NAME,PHONE) VALUES('F',' 杨凤兰

','02785584662');

--INSERT PERSON_TYPE

INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('采购经理');

INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('采购代表');

INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('销售经理');

INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('销售代表');

--INSERT CUSTOMER

INSERT INTO SALES.CUSTOMER(PERSONID) VALUES((SELECT PERSONID FROM

PERSON.PERSON WHERE NAME='刘青'));

INSERT INTO SALES.CUSTOMER(PERSONID) VALUES((SELECT PERSONID FROM

PERSON.PERSON WHERE NAME='桑泽恩'));

INSERT INTO SALES.CUSTOMER(PERSONID) VALUES((SELECT PERSONID FROM

PERSON.PERSON WHERE NAME='王宇轩'));

INSERT INTO SALES.CUSTOMER(PERSONID) VALUES((SELECT PERSONID FROM

PERSON.PERSON WHERE NAME='张大海'));

INSERT INTO SALES.CUSTOMER(PERSONID) VALUES((SELECT PERSONID FROM

PERSON.PERSON WHERE NAME='李飞'));

INSERT INTO SALES.CUSTOMER(PERSONID) VALUES((SELECT PERSONID FROM

PERSON.PERSON WHERE NAME='王南'));

--INSERT CUSTOMER_ADDRESS

INSERT INTO SALES.CUSTOMER_ADDRESS(CUSTOMERID,ADDRESSID,ADDRESS_TYPEID)

SELECT CUSTOMERID,11,2 FROM SALES.CUSTOMER;

INSERT INTO SALES.CUSTOMER_ADDRESS(CUSTOMERID,ADDRESSID,ADDRESS_TYPEID)

SELECT CUSTOMERID,12,3 FROM SALES.CUSTOMER;

--INSERT EMPLOYEE

INSERT INTO

RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MAR

ITALSTATUS,PHOTO,HAIRDATE,SALARY)

VALUES('420921197908051523',1,'L1',' 总经理

','','1979-08-05','S','','2002-05-02',40000);

INSERT INTO

RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MAR

ITALSTATUS,PHOTO,HAIRDATE,SALARY)

VALUES('420921198008051523',2,'L2',' 销售经理 ',(SELECT EMPLOYEEID FROM

RESOURCES.EMPLOYEE WHERE TITLE=' 总经理 '),'1980-08-05','S','','2002-05-02',

26000);

INSERT INTO

RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MAR

ITALSTATUS,PHOTO,HAIRDATE,SALARY)

VALUES('420921198408051523',3,'L3',' 采购经理 ',(SELECT EMPLOYEEID FROM

RESOURCES.EMPLOYEE WHERE TITLE=' 总经理

'),'1981-08-05','S','','2002-05-02',23000);

INSERT INTO

RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MAR

ITALSTATUS,PHOTO,HAIRDATE,SALARY)

VALUES('420921198208051523',4,'L4',' 销售代表 ',(SELECT EMPLOYEEID FROM

RESOURCES.EMPLOYEE WHERE TITLE=' 销售经理

'),'1982-08-05','S','','2002-05-02',15000);

INSERT INTO

RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MAR

ITALSTATUS,PHOTO,HAIRDATE,SALARY)

VALUES('420921198308051523',5,'L5',' 销售代表 ',(SELECT EMPLOYEEID FROM

RESOURCES.EMPLOYEE WHERE TITLE=' 销售经理

'),'1983-08-05','S','','2002-05-02',16000);

INSERT INTO

RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MAR

ITALSTATUS,PHOTO,HAIRDATE,SALARY)

VALUES('420921198408051523',6,'L6',' 采购代表 ',(SELECT EMPLOYEEID FROM

RESOURCES.EMPLOYEE WHERE TITLE=' 采购经理

'),'1984-08-05','S','','2005-05-02',12000);

INSERT INTO

RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MAR

ITALSTATUS,PHOTO,HAIRDATE,SALARY)

VALUES('420921197708051523',7,'L7','人力资源部经理',(SELECT EMPLOYEEID FROM

RESOURCES.EMPLOYEE WHERE TITLE=' 总经理

'),'1977-08-05','M','','2002-05-02',25000);

INSERT INTO

RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MAR

ITALSTATUS,PHOTO,HAIRDATE,SALARY)

VALUES('420921198008071523',8,'L8','系统管理员 ',(SELECT EMPLOYEEID FROM

RESOURCES.EMPLOYEE WHERE TITLE=' 人力资源部经理

'),'1980-08-07','S','','2004-05-02',20000);

--INSERT EMPLOYEE_DEPARTMENT

INSERT INTO

RESOURCES.EMPLOYEE_DEPARTMENT(EMPLOYEEID,DEPARTMENTID,STARTDATE,ENDDATE)

SELECT EMPLOYEEID,'2','2005-02-01',null FROM RESOURCES.EMPLOYEE WHERE

RESOURCES.EMPLOYEE.TITLE='销售代表' OR RESOURCES.EMPLOYEE.TITLE='销售经理';

INSERT INTO

RESOURCES.EMPLOYEE_DEPARTMENT(EMPLOYEEID,DEPARTMENTID,STARTDATE,ENDDATE)

SELECT EMPLOYEEID,'1','2005-02-01',null FROM RESOURCES.EMPLOYEE WHERE

RESOURCES.EMPLOYEE.TITLE='采购代表' OR RESOURCES.EMPLOYEE.TITLE='采购经理';

INSERT INTO

RESOURCES.EMPLOYEE_DEPARTMENT(EMPLOYEEID,DEPARTMENTID,STARTDATE,ENDDATE)

SELECT EMPLOYEEID,'3','2005-02-01',null FROM RESOURCES.EMPLOYEE WHERE

RESOURCES.EMPLOYEE.TITLE='系统管理员';

INSERT INTO

RESOURCES.EMPLOYEE_DEPARTMENT(EMPLOYEEID,DEPARTMENTID,STARTDATE,ENDDATE)

SELECT EMPLOYEEID,'4','2001-02-01',null FROM RESOURCES.EMPLOYEE WHERE

RESOURCES.EMPLOYEE.TITLE='总经理';

--INSERT EMPLOYEE_ADDRESS

INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(1,1);

INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(2,2);

INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(3,3);

INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(4,4);

INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(5,5);

INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(6,6);

INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(7,7);

INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(8,8);

--INSERT SALES.SALESPERSON

INSERT INTO SALES.SALESPERSON(EMPLOYEEID,SALESTHISYEAR,SALESLASTYEAR)

SELECT EMPLOYEEID,8,10 FROM RESOURCES.EMPLOYEE WHERE TITLE='销售代表';

--INSERT VENDOR

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)

VALUES('00','上海画报出版社','1','','2');

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)

VALUES('00','长江文艺出版社','1','','2');

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)

VALUES('00','北京十月文艺出版社','1','','1');

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)

VALUES('00','人民邮电出版社','1','','1');

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)

VALUES('00','清华大学出版社','1','','1');

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)

VALUES('00','中华书局','1','','1');

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)

VALUES('00','广州出版社','1','','1');

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)

VALUES('00','上海出版社','1','','1');

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)

VALUES('00','21世纪出版社','1','','1');

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)

VALUES('00','外语教学与研究出版社','1','','1');

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)

VALUES('00','机械工业出版社','1','','1');

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)

VALUES('00','文学出版社','1','','1');

--INSERT VENDOR_ADDRESS

INSERT INTO PURCHASING.VENDOR_ADDRESS(VENDORID,ADDRESSID,ADDRESS_TYPEID)

SELECT VENDORID,9,1 FROM PURCHASING.VENDOR;

INSERT INTO PURCHASING.VENDOR_ADDRESS(VENDORID,ADDRESSID,ADDRESS_TYPEID)

SELECT VENDORID,10,4 FROM PURCHASING.VENDOR;

--INSERT VENDOR_PERSON

INSERT INTO PURCHASING.VENDOR_PERSON(VENDORID,PERSONID,PERSON_TYPEID)

SELECT VENDORID,9,4 FROM PURCHASING.VENDOR;

--INSERT PRODUCT_CATEGORRY

INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('小说');

INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('文学');

INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('计算机');

INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('英语');

INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('管理');

INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('少儿');

INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('金融');

--INSERT PRODUCT_SUBCATEGORY