设计任务要求：

一、产品销售数据库操作

创建产品销售数据库CPXS和库中的4张表：

产品表（产品编号，产品名称，单价，库存量）

销售商表（销售商编号，销售商名称，地区，负责人，电话）

客户表（客户编号，客户姓名，电话）

产品销售表（产品编号，销售商编号，客户编号，销售日期，单价，销售数量，销售金额）

利用T-SQL语句，在SQL Server Management Studio(SSMS)上完成题目要求的各种操作，包括数据库及表结构的创建，修改和删除；数据的增加、删除、修改以及查询，对于查询，涉及单表、多表连接、嵌套等多种查询方式；索引的创建与使用；完整性机制的定义；视图的定义及查询等。

二、人事管理信息系统的设计

1、了解系统的功能。

2、掌握数据库系统的设计步骤和过程，包括：概念模型（E-R图）的设计，逻辑模型（关系模式）的设计，概念模型向逻辑模型的转换。 3、进一步加深理解关系模式的规范化理论。

一，产品销售数据库操作

1.创建产品销售数据库CPCS

CREATE DATABASE CPCS

2.创建CPCS中表如下：

产品表product（产品编号pno，产品名称pname，单价pprice，库存量pamount）

CREATE TABLE product
(
    	pno varchar(36) PRIMARY KEY NOT NULL,
    	pname varchar(128) NULL
        	DEFAULT
            NULL,
    	pprice money NULL
        	DEFAULT
            NULL,
    	pamount int NULL
        	DEFAULT
            	NULL
)

销售商表sale（销售商编号sno，销售商名称sname，地区splace，负责人sperson，电话sphone）

CREATE TABLE sale
(
    sno varchar(36) PRIMARY KEY NOT NULL,
    sname varchar(50)
        DEFAULT
            NULL,
    splace varchar(100)
        DEFAULT
            NULL,
    spreson varchar(100)
        DEFAULT
            NULL,
    sphone varchar(30) NOT NULL,
)

客户表customer（客户编号cno，客户姓名cname，电话cphone）

CREATE TABLE customer  
(
    cno varchar(36) PRIMARY KEY NOT NULL,
    cname varchar(50) NOT NULL,
    cphone varchar(30) NOT NULL,
)

产品销售表prosale（产品编号pno，销售商编号sno，客户编号cno，销售日期psdate，单价pprice，销售数量psamount，销售金额psmoney）

CREATE TABLE prosale
(
    pno varchar(36) NOT NULL,
    sno varchar(36) NOT NULL,
    cno varchar(36) NOT NULL,
    psdate datetime NULL DEFAULT NULL,
    pprice money NULL DEFAULT NULL,
    psamount int NULL DEFAULT NULL,
    psmoney money NULL DEFAULT NULL,
    PRIMARY KEY (pno, sno, cno),
    CONSTRAINT cno FOREIGN KEY (cno) REFERENCES customer(cno) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT pno FOREIGN KEY (pno) REFERENCES product(pno) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT sno FOREIGN KEY (sno) REFERENCES sale(sno) ON DELETE NO ACTION ON UPDATE NO ACTION,
)

3.将所创建的CPCS数据库的产品表增加产品简介“pintro”列

ALTER TABLE product ADD pintro varchar(128) NULL

4.向客户表插入4名同学的信息

INSERT INTO customer
VALUES
    ('22134', 'Juno', '7458654'),
    ('22135', 'Steve', '3156568'),
    ('22136', 'Alex', '9752568'),
    ('22137', 'Trump', '3868893')

5.将产品表中的每种产品半价销售

SELECT
    pno,pname,pprice/2,pamount
FROM
Product

6.删除产品表中半价高于200的产品

DELETE FROM
    product
WHERE
(pprice/2) > 200

7.查找单价在100～300元之间的产品名称

SELECT
    pname
FROM
    Product
WHERE
pprice BETWEEN 100 AND 300

8.查找与自己同姓的所有客户

SELECT 
    cname
FROM
    customer
WHERE 
cname like 'Huan%'

9.在销售商表上按照地区建立聚簇索引

CREATE CLUSTERED INDEX IX_splace ON sale(splace)

10.查询产品销售表的详细情况，查询结果按照销售金额的降序排序，同一销售金额的按照销售数量的升序排序

SELECT
    *
FROM
    prosale
ORDER BY 
psmoney DESC , psamount ASC

11.计算有库存的产品的平均单价

SELECT
    AVG(pprice)
FROM
    product
WHERE
pamount > 0

12.求每个销售商的客户数量

SELECT
    sno , COUNT(DISTINCT cno)
FROM 
    prosale
GROUP BY 
Sno

13.查询自己购买的产品名称（连接查询和嵌套查询）

SELECT
    pname
FROM
    product,prosale,customer
WHERE
prosale.cno = customer.cno AND prosale.pno = product.pno AND customer.cname = 'HuanHuan'

SELECT
    pname
FROM
    product
WHERE pno IN
(
    SELECT pno
    FROM prosale
    WHERE cno IN(
    SELECT cno
    FROM customer
    WHERE cname = 'HuanHuan'
    )
)

14.查询北京销售商销售的所有产品名称

SELECT
    product.pname
FROM
    product , prosale , sale
WHERE
prosale.pno = product.pno AND prosale.sno = sale.sno AND splace = '北京';

15.查询总的销售金额最高的销售商名称

SELECT
    sname
FROM
    sale
WHERE sno IN
(
    SELECT
        sno
    FROM
        prosale
    GROUP BY
        sno
    HAVING SUM(psmoney) IN
    (
        SELECT
            MAX(total)
        FROM
        (
            SELECT
                SUM(psmoney)
            as
                total
            FROM
                prosale
            GROUP BY
                sno
        ) as av
    )
)

16.建立视图BXCP，实现查询5号销售商销售的产品名称，销售数量和销售金额

CREATE
    VIEW BXCP
AS
SELECT
    pname,psamount,psmoney
FROM
    product,prosale
WHERE
product.pno = prosale.pno AND prosale.sno = '5';

17.在BXCP视图上查询Saler59号销售商销售的总数量和总金额

SELECT
    SUM(BXCP.psamount),SUM(BXCP.psmoney)
FROM
    BXCP,prosale
WHERE
    prosale.sno = 'Saler59'

18.查询各个地区的负责人数

SELECT
    splace,COUNT(DISTINCT spreson)
FROM
    sale
GROUP BY
    splace

19.查询负责人数不少于4人的地区

SELECT
    splace, COUNT(DISTINCT spreson)
FROM
    sale
GROUP BY
    splace
HAVING
    COUNT(DISTINCT spreson) >= 3

20.查询购买单价最贵的产品的客户姓名

SELECT
    cname 
FROM
    customer 
WHERE cno IN
( 
    SELECT cno 
    FROM prosale 
    WHERE pprice IN
    ( 
        SELECT MAX(pprice) 
        FROM prosale
    ) 
)

21.查询每个销售商销售的金额总值

SELECT
    prosale.sno,SUM(psmoney)
FROM
    prosale
GROUP BY
    prosale.sno

22.查询同时购买A4号和FA7号产品的客户编号

SELECT
    A.cno
FROM
    prosale A, prosale B
WHERE
    A.cno = B.cno AND A.pno = 'A4' AND B.pno = 'FA7'

23.查询供应Pro23号产品或者Pro55号产品的销售商编号

SELECT
    sno
FROM
    prosale
WHERE
    pno = 'Pro23' OR pno = 'Pro55'

二，人事管理信息系统的设计

1.查阅相关资料，了解管理的业务流程，说明系统预计完成哪些功能

人事管理系统，属于ERP的一个部分。它单指聚集成功企业先进的人力资源管理理念、人力资源管理实践、人力资源信息化系统建设的经验，以信息技术实现对企业人力资源信息的高度集成化管理，为中国企业使用的人力资源管理解决方案。核心价值在于将人力资源工做者从繁重的平常琐碎事务中解放出来，将更多地精力用于企业的人力资源职能管理和管理决策，保持企业的持续高效运营。 集中记录、监测和分析全部劳动力的技能和资格，提供决策分析。提升企业总体的科技含量与管理效率，加快企业的信息化建设。

人事管理系统须要满足的基本功能以下:

(1) 员工管理:添加员工信息、修改员工共信息、删除员工信息、查询员工信息

(2) 部门管理:添加部门信息、修改部门信息、删除部门信息、查询部门信息

(3) 薪资管理:薪资分配、薪资历史查询

(4) 人事管理:人员调动，调动历史查询

(6) 考核管理:人员考核，考核历史查询

2.根据你了解到的业务语义，以及预计完成的功能，确定实体，实体属性，联系，联系属性，联系类型，并画出E-R图。

3.把E-R图转换为相应的关系模式，说明这样转换的理由；请指出主码，从函数决定关系角度说明确定主码的理由；如果有外码，请指出外码，并给出确定外码的理由

4.对每个关系模式，分析主码对非码属性的函数决定关系，判断所属范式，给出判断的理由；对于不是3NF的关系模式进行分解，使得每个关系模式都属于3NF。

表staff，department，professional，education，sp，checking，salary，current_salary均只有单一主码，且表中不包含已在其它表中已包含的非主关键字信息，满足函数依赖关系，均符合2NF范式。

表transfer中的字段都是单一属性的，不可再分。符合1NF范式。

5.根据你的设计，在平台上建立库、表，定义主码，外码，理解实体完整性和参照完整性

略

6.创建合适的索引，以便加快查询速度。

CREATE CLUSTERED INDEX IX_sname ON sale(s_name)