MYSQL数据库设计题-窗帘店

        个人答案，非参考答案。

---

•  Mysql数据库实验及练习题相关

        MySQL 数据库和表的管理-数据库实验一

        MySQL连接查询、索引、视图-数据库实验二、实验三

        MySQL约束、触发器-数据库实验四

        MYSQL存储过程和存储函数-数据库实验五

                MySQL批量随机生成name、TEL、idNumber

        MYSQL数据库的安全管理-数据库实验六

                MYSQL数据库安全性练习题

        MYSQL数据库的备份与恢复-数据库实验七

        MYSQL数据库设计题-窗帘店

---

目录

1、请创建ER图，包括联系的数量和实体、联系、联系属性（5分）

2、转换成关系模型，请补充完成（2分）

3、请补充完善数据库创建（3分）

4、完成数据库定义和数据更新操作(9分，每小题3分)

5、请完成以下数据查询，包括关系代数表达式和SQL语句。（7分）

6、请完成如下查询。（14分）

7、查询所有客户订单情况，包括客户名称、订单编号、订单类型描述、下单时间、订单金额、订单子项目、订单子项目金额，请使用视图完成该功能。(10分)

8、客户下订单，要在订单表中添加记录，请编写一个存储过程完成以上功能，输入参数为客户编号和订单类型、订单金额。

---

        有一个窗帘店，窗帘营销管理系统管理客户、订单等信息，其业务规则如下：
        软件为客户信息建立客户档案表（基本信息包括客户编号，姓名，地址，电话）。
        窗帘订单有各种类型的套餐，比如 1999 订单、3999 订单、自由订单等，类型信息放在订单类型表（类型编号，类型描述）。
        客户根据需求下订单，订单信息存放在订单表（订单编号，类型编号，客户编号，下单时间，预计安装时间，订单金额，订单状态），订单详细情况存放在订单详单中，记录每笔订单中子项目情况，这些子项目包括：窗帘布、花边、绑带、手工费、安装费等，订单详单表（订单编号，订单子项目，子项目金额）。
        客户下订单时，应支付第一笔金额，在安装前客户需要交付订单余额，收款都记录在收款流水账中（收款编号，订单编号，收款时间，金额），每收到一笔金额，都需要给用户发送短信回执，短信回执表如下：（收款编号，短信信息，发送状态），软件会轮训该表发送短信给客户。

下面列出了一笔收款和短信回执的样例数据：

收款流水账：

收费编号	收款时间	订单编号	金额
21	2018-6-8 20：15	3	200

短信回执：

收费编号	短信内容	发送标志
21	尊敬的王明客户，现收到你交费200元，收费编号：21	0

下面列出了一笔订单和订单详单的样例数据：

订单表:

订单编号	订单类型	下单时间	客户编号	预计安装时间	订单金额	订单状态
5	2	2018-6-8 12：00	3	2018-7-28	3999	0

订单详单:

订单编号	订单子项目	子项目金额
5	窗帘布	2600
5	花边	4000
5	手工费	499
5	安装费	500

1、请创建ER图，包括联系的数量和实体、联系、联系属性（5分）

2、转换成关系模型，请补充完成（2分）

客户档案表（客户编号，姓名，地址，电话）;

订单类型表（类型编号，类型描述）；

订单表（订单编号，类型编号，客户编号，下单时间，预计安装时间，订单金额，订单状态）;

订单详单表（订单编号，订单子项目，子项目金额）；

收款流水账中（收款编号，订单编号，收款时间，金额）;

短信回执表（收款编号，短信信息，发送状态）；

3、请补充完善数据库创建（3分）

-- 客户档案表
Create table customers
(
 CID    int primary key,  -- 客户编号
 CName  nchar (20) not null, -- 名称
 ADDR   nchar (50),          -- 地址
 CPhoneNO char (11)
);
-- 订单类型
Create table OrderType
(
  OTID     int primary key,  -- 类型编号
  TypeDesc nchar (50) not null-- 类型描述
);
-- 订单
Create table orders
(
  OID  int primary key,    -- 订单编号
  OTID int references OrderType (OTID ), -- 类型编号
  CID  int references customers (CID ),  -- 客户编号
  orderDT datetime default now (),      -- 下单时间
  installDT datetime,      -- 安装时间
  Amount decimal (7, 2), -- 订单金额
  status char (1) default0     -- 订单状态，0 进行中，1 已完成
);
-- 订单详单
Create table orderDetail
(
 OID int,
 o_s_project nchar (20),
 s_project_amount decimal (7, 2),
 primary key (OID, o_s_project)
);
-- 收款流水账
Create table gathering
(
  GID  int primary key,            -- 收费编号
  OID  int references orders (OID),  -- 订单编号
  gatheringDT datetime default now (), -- 收款时间
  gatheringAmount decimal (6, 2)            -- 金额
);
-- 短信回执
Create table recMessage
(
  GID  int primary key references gathering (GID),    -- 收费编号
  MessageContent nchar (50),  -- 回执内容
  SendFlag char (1) default0 --0 未发送 1 已发送
);

4、完成数据库定义和数据更新操作(9分，每小题3分)

（1）修改订单orders ，为安装时间installDT  添加用户自定义完整性约束，要求安装时间小于下单时间  。

ALTER TABLE `orders` ADD CONSTRAINT inst_time_ck
CHECK (installDT > orderDT);

（2）为订单详单表orderDetail创建索引，订单编号+订单子项目应该是唯一的，先按照订单编号排序，如果订单编号相同，则按照订单子项目倒序排序。

CREATE UNIQUE INDEX odet_id_p_idx
ON orderDetail(OID, o_s_project);

（3）在订单表orders中修改王明的所有订单状态为已完成。

UPDATE oders SET `status` = 1
WHERE CID = (SELECT CID FROM customers WHERE CName = ' 王明 ') ;

5、请完成以下数据查询，包括关系代数表达式和SQL语句。（7分）

（1）查询客户编号为170121的客户信息。（3分）

Π(ρ(CID = 170121)(customers))
SELECT * FROM customers WHERE CID = 170121;

（2）查询王明所有的订单详情，包括订单号、下单时间、订单金额、订单子项目，订单子项目金额。（4分）

Π(OID, orderDT, Amount, o_s_project, s_project_amount)(ρ(CName = ' 王明 ')(orders oo orderDetail oo customers))
SELECT or. OID, orderDT, Amount, o_s_project, s_project_amount
FROM orders `or`, orderDetail od, customers ct
WHERE or. OID = od. OID AND or. CID = ct. CID AND ct. CName = ' 王明 ';

6、请完成如下查询。（14分）

（1）查询订单类型为‘3999沁心套餐’的订单记录，包括：订单号、客户编号、客户名称、下单时间、状态，按照下单时间排序。（4分）

SELECT od. OID, od. CID, CName, orderDT, `status`
FROM orders od, OrderType ot, customers ct
WHERE od. OTID = ot. OTID AND od. CID = ct. CID AND TypeDesc = '3999 沁心套餐 '
ORDER BY orderDT;

（2）按订单类型统计订单金额，列出订单数最多的订单类型和累计金额，包括：订单类型描述、累计金额。（4分）

SELECT od. OID, od. CID, CName, orderDT, `status`
FROM orders od, OrderType ot, customers ct
WHERE od. OTID = ot. OTID AND od. CID = ct. CID AND TypeDesc = '3999 沁心套餐 '
ORDER BY orderDT;

 （3）查询目前欠费客户名单，也就是该客户有这样的订单，这个订单的交费金额小于订单金额，包括：客户名称，订单号，该订单的订单金额，该订单的累计交费金额，欠费金额（6分）

SELECT CName, od. OID AS OID, Amount, gat, (Amount - gat) AS debt
(SELECT od. OID, od. CID AS CID, Amount, SUM(gatheringAmount) AS gat
FROM gathering gt, orders od WHERE gt. OID = od. OID AND gat < Amount GROUP BY OID)
AS debate, customers ct WHERE debate. CID = ct. CID;

7、查询所有客户订单情况，包括客户名称、订单编号、订单类型描述、下单时间、订单金额、订单子项目、订单子项目金额，请使用视图完成该功能。(10分)

CREATE VIEW all_cust_or_v
AS SELECT CName, od. OID, TypeDesc, orderDT, Amount, o_s_project, s_project_amount
FROM customers ct, orders od, OrderType ot, orderDetail ota
WHERE ct. CID = od. CID AND od. OTID = ot. OTID AND ota. OID = od. OID;

8、客户下订单，要在订单表中添加记录，请编写一个存储过程完成以上功能，输入参数为客户编号和订单类型、订单金额。

CREATE VIEW all_cust_or_v
AS SELECT CName, od. OID, TypeDesc, orderDT, Amount, o_s_project, s_project_amount
FROM customers ct, orders od, OrderType ot, orderDetail ota
WHERE ct. CID = od. CID AND od. OTID = ot. OTID AND ota. OID = od. OID;

9、窗帘店收到客户付款后，要记录到收款表中，也就是需要插入一条记录到收款表gathering中，同时需要生成一条短信回执到短信回执表recMessage中，MessageContent格式为“尊敬的XX（客户名称）客户，现收到你交费XX（本次收费金额）元，收费编号：XX(本次收入编号)”。请使用触发器完成gathering表新增记录时，recMessage表的添加记录功能。(10分)

DELIMITER @@
CREATE TRIGGER gat_trg
    AFTER INSERT
    ON gathering
    FOR EACH ROW
BEGIN
    DECLARE @message VARCHAR (256);
    DECLARE NAME CHAR (20);
    SET NAME = (SELECT CName
                FROM gathering
                         LEFT JOIN orders o ON gathering. OID = o. OID
                         LEFT JOIN customers c ON o. CID = c. CID
                WHERE gathering. OID = NEW. OID);
    SET message = ' 尊敬的 ' + NAME + ' 客户，现收到你交费 ' + new. gatheringAmount + ' 元，收费编号: ' + new. GID;
    INSERT INTO recMessage VALUES (message, NEW. GID, 1);
END @@