概念模型
概念模型按“实体和键、关系、非键属性”三步画出,每个关系都标明最小和最大基数。案例没说清的地方写成设计假设,例如员工角色必填、报价和任务只能由调度员处理。
客户发起报价,报价最多转成一个任务;任务使用一个车辆组合,每个组合由一辆牵引车和一辆挂车构成。
项目 / BigRig Movers
为卡车租赁公司 BigRig Movers 设计并建成运输业务数据库,把客户报价、运输任务、车辆组合和员工连成一套能互相核对的数据。
BigRig Movers 出租牵引车和挂车,帮客户把货物从一地运到另一地。客户打电话询价,调度员算出报价;客户接受后,调度员排出任务,配好车辆组合。
麻烦在于每一步都有自己的规则。报价阶段还不分配车辆;客户可能当场接受,也可能过几天才回电,不回电的报价也要留在系统里。任务的最终费用可能和报价不一样,但原报价不能改。出车前必须有机械师确认车况。车牌号还会从卖掉的旧车转到新买的车上。
这些规则都得落到数据结构里,数据库才能支撑报价、排车和结算。
设计依据是案例里的业务描述、调度员选车用的租金表单,以及客户补充的六条业务说明。涉及的信息可以分成五类:
| 客户与报价 | 客户资料,报价日期、金额、取货和送达地点,以及准备报价的调度员 |
|---|---|
| 任务 | 取货和送达时间、付款状态、修订后的费用、使用的车辆组合和安排任务的调度员 |
| 车辆 | 牵引车按 VIN 识别,记车牌、总里程和上次保养里程;挂车按五位代码识别,记品牌、型号、载重和尺寸;两者都有购买日期、价格和停用日期 |
| 员工 | 经理、调度员、机械师和司机四种角色,每人只有一种;记汇报对象、联系方式、税号和年薪,司机另记驾照号 |
| 租金表单 | 每种用途下可用的车辆组合和租金,附牵引车和挂车信息 |
报价阶段还没有车辆,客户也可能不回电,所以报价单独成表,客户接受后才生成任务。任务表里的报价编号加了唯一索引,一份报价最多对应一个任务;没成交的报价留在报价表里。
最终费用和报价不同时,原报价不动,任务表另有一个实际费用字段,只在两者不同时填写,两边靠报价编号关联。
车牌号会从卖掉或报废的旧车转到新车上,同一个车牌在系统里可能对应两辆车。所以牵引车用 VIN 做主键,车牌只是普通属性。停用日期为空,说明车辆还在使用。
租金表单把用途、车辆组合、牵引车和挂车信息混在一张表里。从非规范形式一步步拆到第三范式,得到用途、车辆组合、牵引车、挂车和租金五个关系,租金由用途和车辆组合一起确定。
公司规定出车前要由机械师确认车辆组合状况良好。认证单独成表,记下认证时间和机械师,每个任务只有一条认证记录。
客户明确要求不拆员工子类,四种角色放在同一张员工表里,角色必填。设计假设里另外写明:只有司机填驾照号,准备报价和安排任务的必须是调度员。
概念模型按“实体和键、关系、非键属性”三步画出,每个关系都标明最小和最大基数。案例没说清的地方写成设计假设,例如员工角色必填、报价和任务只能由调度员处理。
客户发起报价,报价最多转成一个任务;任务使用一个车辆组合,每个组合由一辆牵引车和一辆挂车构成。
以一种用途的租金表单为例,从非规范形式依次做到 1NF、2NF 和 3NF。每一步都写出候选键和依赖:1NF 找部分依赖,2NF 查传递依赖,3NF 列出完整依赖。
最终五个关系各管一类信息,车辆属性和租金都有固定的维护位置。
概念模型和规范化结果在 Oracle Data Modeler 里合成逻辑模型,每个字段都写了数据库注释。租金表加了代理键,认证编号由序列和触发器自动生成。建表脚本由模型直接生成,在 Oracle 中运行并用 spool 保存记录。
10 张表、10 个主键、10 个外键和 58 条字段注释全部创建成功。
| 报价跟进 | 每份报价都有编号和准备人,客户过几天回电也能调出原报价;没成交的报价留在系统里,之后可以回头统计。 |
|---|---|
| 费用核对 | 原报价和实际费用分开保存,按报价编号就能对出每个任务的差额。 |
| 车辆管理 | 车辆按 VIN 识别,车牌换车也不会混淆;里程、保养里程和停用日期都有记录。 |
| 选车定价 | 租金按用途和车辆组合查询,调度员报价时有统一的依据。 |
| 出车安全 | 每个任务出车前的认证都有记录,能查到是哪位机械师、什么时候确认的。 |