Faculty of Arts and Social Sciences
文学院及社会科学院

Department of Library and Information Science
图书情报学系

Semester 1 2024/2025
第一学期 2024/2025

AQC7005
型号 AQC7005

TECHNOLOGIES FOR INFORMATION MANAGEMENT
信息管理技术

Assignment 3
作业 3

DATABASE PROJECT
数据库项目

Prepared for:
准备对象：

PROFESSOR Dr. Noorhidawati Binti Abdullah
Noorhidawati Binti Abdullah 教授

3. Define Business Rules (Progress Update Week 8)
3. 定义业务规则（第 8 周进度更新）

§ Identify and document no less than ten (10) business rules that govern the data stored in your database. Business rules could include constraints, validation criteria, or policies specific to your business scenario.
§ 确定并记录不少于十 （10） 个业务规则，这些规则用于管理数据库中存储的数据。业务规则可能包括特定于您的业务方案的约束、验证标准或策略。

§ Business rules should address data integrity, consistency, and security.
§ 业务规则应解决数据完整性、一致性和安全性问题。

Title: How to manage a flower shop easily
标题：如何轻松管理花店

Business Scenario
业务场景

We selected a florist for the business scenario. This florist has been in business locally for many years and has built a stable customer base with warm service. It mainly provides sales and delivery of flowers for customers. In addition, the florist offers gift-wrapping services to make orders more appealing, especially for special occasions like birthdays, anniversaries, and holidays. There is also a small warehouse belonging to the florist. It has been cooperating with some suppliers for a long time to ensure ample stock.
我们为业务场景选择了一家花店。这家花店在当地经营多年，并以热情的服务建立了稳定的客户群。它主要为客户提供鲜花的销售和配送。此外，花店还提供礼品包装服务，使订单更具吸引力，尤其是在生日、周年纪念日和节假日等特殊场合。还有一个属于花店的小仓库。它一直与一些供应商合作很长时间，以确保 充足的库存。

The Purpose of the Database
Database 的 T he Purpose

With the increasing scope of business, now an effective database is required to manage incoming and outgoing stock, customer and order records, and delivery status. Here are the purposes of establishing the database:
随着业务范围的扩大，现在需要一个有效的数据库来管理进出库存、客户和订单记录以及交货状态。 以下是建立数据库的目的：

Manage Customer Order in Details
在详细信息中管理客户订单

Each order will be linked to the relevant customer information, allowing for efficient order processing, record-keeping, and follow-up communications with customers. Through organized orders and deliveries in the database, it will ensure each customer receives their order on time, enhancing the overall customer experience.
Each 订单将与 相关的客户信息相关联，从而实现高效的订单处理、记录保存和与客户的后续沟通。 通过在数据库中组织订单和交付，它将确保每个客户按时收到他们的订单，从而增强整体客户体验。

Track the Inventory on Time
按时跟踪库存

The database will keep stock information updated during outgoing and incoming shipments, and maintain close contact with suppliers, ensuring quick and efficient reordering when stock is low.
该数据库将在出货和进货期间保持库存信息更新，并与供应商保持密切联系，确保在库存不足时快速有效地重新订购。

Improve Overall Operational Efficiency
提高 Overall Operational Efficiency

With the database established, a small number of employees can be trained to manage data from all in-store information with suppliers and customers, simplifying operations for the florist and reducing operational stress.
建立数据库后，可以培训少数员工与供应商和客户一起管理来自所有店内信息的数据，从而简化花店的操作并减轻运营压力。

The primary Entities and their Relationships in the Database
The primary Entities 及其在 Database 中的研究

Customers
客户

Order:
Order：

Delivery
交货

Flower
花

Stock-in
入库

Stock-out
缺货

Supplier
供应商

Develop an Entity-Relationship Diagram (ERD)
开发实体关系图 （ERD）

**Compared with other models, we connect StockIn entity and StockOut entity so that each shipment of flowers can be traced back to its StockIn ID.
**与其他模型相比，我们将 StockIn 实体和 StockOut 实体连接起来，以便每批鲜花都可以追溯到其 StockIn ID。

Entities and Relationships:
实体和关系：

1) Customer and Order:
1）客户和订单：

Relationship Type: One-to-Many (1:N).
关系类型： 一对多 （1：N）。

Description: A customer can place multiple orders, but each order is associated with only one customer.
描述： 一个客户可以下多个订单，但每个订单只与一个客户关联。

2) Order and Delivery:
2） 订单和交付：

Relationship Type: One-to- Many (1:N).
关系类型： 一对多 （1：N）。

Description: Although each order typically has a single delivery that one delivery can be associated with multiple orders.
描述： 虽然每个订单通常都有一个配送，但一个配送可以与多个订单相关联。

3) Order and Flower:
3）订单和花朵：

Relationship Type: Many-to-One (N:1).
关系类型：多对一 （N：1）。

Description: An order can contain multiple types of flowers, but each flower type in an order references a single flower in the inventory.
描述： 一个订单可以包含多种类型的鲜花，但订单中的每种鲜花类型都引用库存中的一朵鲜花。

4) Flower and StockIn:
4） 花和袜子：

Relationship Type: One-to-Many (1:N).
关系类型： 一对多 （1：N）。

Description: A single flower type can have multiple stock-in records, representing different batches or entry dates.
描述： 单个鲜花类型可以有多个入库记录，分别代表不同的批次或入库日期。

5) StockIn and Supplier:
5） StockIn 和供应商：

Relationship Type: Many-to-One (N:1).
关系类型：多对一 （N：1）。

Description: Multiple stock-in records can be linked to a single supplier, but each stock-in record comes from one supplier.
描述：多个入库记录可以链接到一个供应商，但每个入库记录都来自一个供应商。

6) StockOut and StockIn:
6） StockOut 和 StockIn：

Relationship Type: Many-to-One (N:1).
关系类型：多对一 （N：1）。

Description: Multiple stock-out records reference a specific stock-in record, representing quantities taken from different batches.
描述： 多个出库记录引用一个特定的入库记录，表示从不同批次中提取的数量。

7) StockOut and Flower:
7） StockOut 和 Flower：

Relationship Type: Many-to-One (N:1).
关系类型：多对一 （N：1）。

Description: Multiple stock-out records can relate to a single flower type, tracking the quantities and dates flowers are sold or moved out of inventory.
描述： 多个缺货记录可能与单个鲜花类型相关，跟踪鲜花售出或移出库存的数量和日期。

8) StockOut and Supplier:
8） 缺货和供应商：

Relationship Type: Many-to-One (N:1).
关系类型：多对一 （N：1）。

Description: Multiple stock-out records can be associated with a supplier (likely representing returns or adjustments), but each stock-out entry relates to one supplier.
描述： 多个缺货记录可以与一个供应商关联（可能代表退货或调整），但每个缺货条目都与一个供应商相关。

Business Rules
业务规则

1) Each order can be delivered many times, but each delivery ID should be unique and only belong to one order.
1） 每个订单可以多次发货，但每个发货 ID 应该是唯一的，并且只属于一个订单。

2) Each customer can have zero or many orders, but each order belongs to one customer.
2） 每个客户可以有零个或多个订单，但每个订单都属于一个客户。

3) The date of each delivery (Delivery_Date) must be later than the time the order was created (Order_Date).
3） 每次交货日期 （Delivery_Date） 必须晚于创建订单的时间 （Order_Date）。

4) Customers can’t re-order the same flower type (Flower_ID) and quantity (Order_Quantity) at the same time.
4）客户不能同时重新订购相同的花型 （Flower_ID） 和数量 （Order_Quantity）。

5) Each order must be linked to a valid customer (Customer_ID) and include at least one product (Flower_ID).
5） 每个订单必须关联一个有效的客户 （Customer_ID） 并包含至少一个产品 （Flower_ID）。

6) An order can only be processed if there is sufficient stock for all included products.
6） 只有在所有包含的产品都有足够的库存的情况下，才能处理订单。

7) The Supplier_ID must be unique for each supplier record, ensuring each supplier can be distinctly identified in the system.
7） 每个供应商记录的Supplier_ID必须是唯一的，以确保每个供应商都可以在系统中被清楚地识别。

8) The stock-in quantity must be greater than zero, and the unit price (Flower_UnitPrice) must be greater than or equal to zero.
8） 入库数量必须大于零，单价 （Flower_UnitPrice） 必须大于或等于零。

9) Each supplier can have more than one incoming record at a time (StockIn_ID), but each supply record must have and can only correspond to one supplier (Suppelier_ID).
9） 每个供应商一次可以有多个进货记录 （StockIn_ID），但每个供应记录必须具有且只能对应一个供应商 （Suppelier_ID）。

10) Each type of flower (Flower_ID) in the same incoming stock (StockIn_ID) can be shipped in multiple shipments or without any outgoing record (StockOut_ID).
10） 同一进货库存（库存 I n_ID）中的每种花卉 （Flower_ID）可以分多次发货或没有任何出货记录（库存Out_ID）。

Reference
参考

https://blog.csdn.net/m0_58153897/article/details/141997914