【软考高级·系统分析师全链路通关实战】第 53 篇案例专项三——数据库设计与 SQL 真题模式本系列定位面向有开发经验、从零备考软考高级「系统分析师」的工程师以《系统分析师教程第 2 版》为主线按「综合知识 → 案例分析 → 论文」三科组织需求工程与 UML 建模深拆60 篇带你从考试小白到三科同过。本篇你将学到数据库案例题的四类命题模式E-R 补全、E-R 转关系模式、范式判定、SQL 书写E-R 题的「实体-联系-属性」审题法与转换规则速查范式判定的决策树1NF → 2NF → 3NF → BCNF 逐级检查法SQL 题的书写规范与连接/子查询的答题套路真题风格演练 2 道云诊通挂号与处方场景含完整题干、参考答案、扣分点提示学完本篇你将把第 10-11 篇的数据库理论转化为案例科目的稳定输出。对有开发经验的读者这是六大题型族中性价比最高的一个战场。考点热力表知识点综合知识案例分析论文E-R 模型实体/联系/属性★★★★★★★E-R 图转关系模式规则★★★★★—范式判定与规范化★★★★★★★★SQL连接/子查询/分组★★★★★—概念模型到逻辑模型流程★★★★★★说明数据库设计案例题对工程师几乎是无门槛送分题——E-R 与 SQL 是日常技能需要补的只是「按教材规则作答」的规范性。一、命题模式拆解数据库题怎么出1.1 四类命题与分值结构命题模式题干特征典型小问分值E-R 补全给业务说明 带空 E-R 图「补充实体/联系/属性」「判断联系类型1:1/1:n/m:n」6-9 分E-R 转关系模式给 E-R 图问转换结果「将 E-R 图转换为关系模式标注主键与外键」6-8 分范式判定给关系模式与函数依赖「判断属于第几范式并说明理由」「分解到 3NF」6-8 分SQL 书写给表结构问查询「写出满足 XX 条件的 SQL 语句」5-8 分一道 25 分大题通常是「E-R 补全 转换」或「转换 范式 SQL」的组合正好覆盖概念设计→逻辑设计→物理实现的链路第 11 篇六阶段。1.2 E-R 审题法实体-联系-属性三问E-R 题的审题是一个机械流程对题干每句话问三个问题这句话在描述「谁」——名词主语是实体候选患者、医生、号源、处方这些「谁」之间发生了什么——动词谓语是联系候选预约、开立、审核联系本身也可有属性预约时间挂在联系上而不是患者上描述了它们的什么特征——定语宾语是属性候选患者的手机号处方的药品明细联系类型的判定口诀看两端实例的配对数量——一个患者可预约多个号源、一个号源只被一个患者预约 → 1:n一名医生可开多张处方、一张处方只由一名医生开 → 1:n一名医生可排班多个科室、一个科室有多名医生排班 → m:n。高频埋坑位出题人删空的位置m:n 联系业务说明里「多对多」关系的联系菱形被挖空联系的属性如「就诊时段」被错误画在实体上找错题让它现形二、E-R 转关系模式与范式判定2.1 转换规则速查表E-R 构件转换规则示例云诊通挂号域实体独立关系模式主键照搬患者(患者ID, 姓名, 手机号)1:1 联系并入任一端加对方主键与联系属性或独立成表患者—账户 1:1 并入患者端1:n 联系并入 n 端n 端加 1 端主键作外键 联系属性挂号单(挂号ID, 患者ID外键, 号源ID外键)m:n 联系必须独立成表主键 两端主键组合 联系属性排班(医生工号, 科室编码, 排班日期)多值属性独立成表医生(工号,…)医生资质(工号, 资质类型)主外键标注是硬采分点每个关系模式都要注明主键与外键指向如「患者ID外键参照患者表」。漏标外键是最常见的隐形失分。2.2 范式判定决策树范式判定题给出一组函数依赖要求判定当前属于第几范式。逐级检查法否是存在部分依赖完全依赖存在传递依赖无传递依赖否是给定关系模式R与函数依赖集F所有属性是否原子不可再分?不满足1NF先拆属性非主属性是否完全函数依赖于码?仅1NF非主属性是否传递依赖于码?2NF每个决定因素是否都包含码?3NFBCNF判定三步的口诀记忆1NF属性原子性——没有「拆不开的组合属性」2NF消除部分依赖——非主属性不依赖组合码的真子集。典型症状主键是医生工号, 科室编码组合但「医生职称」只依赖工号 → 只有 1NF3NF消除传递依赖——非主属性不依赖其他非主属性。典型症状挂号单里「科室编码 → 科室名称」科室名称传递依赖挂号单主键 → 2NFBCNF所有决定因素都含码——案例题一般考到 3NF 为止第 10 篇详述分解到 3NF 的标准动作把「码 → 非主属性组」按依赖源头拆表依赖的决定因素作主键。上例拆为挂号单(挂号ID, 患者ID, 科室编码)、科室(科室编码, 科室名称)并注明科室编码为挂号单外键。三、SQL 题书写规范3.1 判分口径案例 SQL 题判「结构正确 关键子句齐全」书写规范五条SELECT … FROM … WHERE 三段骨架清晰多表先写连接条件再写筛选条件连接优先显式 JOIN ON内连接A JOIN B ON A.x B.y判分比逗号隐式连接更稳分组统计记住三件套GROUP BY 聚合函数COUNT/SUM/AVG 分组后筛选用 HAVING子查询按语义选型「大于平均值」用标量子查询WHERE 成绩 (SELECT AVG(...))「存在于集合」用 IN 或 EXISTS中文场景词到 SQL 的换算「每个」→ GROUP BY「至少/最多」→ HAVING 配 COUNT 比较「前 N」→ ORDER BY LIMIT注明方言3.2 高频题型模板场景词SQL 骨架「谁的 XX 最多」GROUP BY … ORDER BY COUNT(*) DESC 取首行「高于平均值」WHERE 列 (SELECT AVG(列) FROM 表)「同时满足两条件」GROUP BY HAVING COUNT(DISTINCT 条件列) 2「没做过 XX」NOT IN / NOT EXISTS 子查询多表取列JOIN ON 连接 WHERE 筛选工程师的日常直觉在这里高度可用只需克制一个习惯别炫技。窗口函数、CTE 能不用就不用简洁清晰的标准 SQL 骨架比巧妙答案安全。四、真题风格演练一挂号域 E-R 与转换4.1 题干模拟真题云诊通预约挂号域的业务说明1患者实名注册后可预约挂号一名患者可多次预约一个号源某医生某时段的出诊单元至多被一名患者预约2医生按科室排班一名医生属于一个科室一个科室有多名医生一名医生可在多个院区出诊3每次预约生成一张挂号单记录就诊时段与支付状态挂号单需关联患者与号源4科室有科室编码、名称医生有工号、姓名、职称患者有患者ID、姓名、手机号号源有号源ID、出诊日期、时段。分析师绘制的 E-R 图中实体「患者、医生、科室、号源」已画出「预约」联系及其属性、医生与院区的联系被挖空待补。问题 18 分补充 E-R 图「预约」联系两端的实体与联系类型「预约」联系的属性以及「医生—院区」联系的类型。问题 29 分将上述 E-R 模型转换为关系模式标注各关系模式的主键与外键。问题 38 分若将医生信息与排班信息合并为排班(工号, 科室编码, 院区编码, 出诊日期, 姓名, 职称)函数依赖为工号→姓名, 工号→职称, 工号→科室编码。判断该关系模式最高属于第几范式说明理由并分解到 3NF。参考答案问题 1「预约」联系连接患者与号源类型m:n 联系多对多——一名患者可预约多个号源一个号源至多被一名患者预约4 分判 1:n 并以「号源端为 1」论证言之成理给一半分联系属性预约时间、支付状态——它们依赖「某患者预约某号源」这一事实必须挂在联系上2 分「医生—院区」一名医生可在多个院区出诊、一个院区有多名医生 →m:n 联系2 分问题 2每个模式含主外键判定 1.5-2 分患者(患者ID, 姓名, 手机号)科室(科室编码, 科室名称)医生(工号, 姓名, 职称, 科室编码 外键→科室)号源(号源ID, 出诊日期, 时段, 工号 外键→医生)挂号单(挂号ID, 患者ID 外键→患者, 号源ID 外键→号源, 预约时间, 支付状态)——m:n 联系独立成表(患者ID, 号源ID) 为候选键并建唯一约束出诊(工号 外键→医生, 院区编码, 出诊日期)——m:n 联系独立成表组合主键(工号, 院区编码)问题 3组合码为 (工号, 院区编码, 出诊日期)存在函数依赖 工号→姓名、工号→职称、工号→科室编码即非主属性姓名、职称、科室编码部分函数依赖于码的真子集工号3 分不满足 2NF 的「完全依赖」要求最高属于1NF2 分分解到 3NF医生(工号, 姓名, 职称, 科室编码)排班(工号 外键→医生, 院区编码, 出诊日期)组合主键(工号, 院区编码, 出诊日期)——消除部分依赖后即无传递依赖工号→科室编码若需消除传递依赖可再拆科室表注明者加分3 分4.3 扣分点提示演练一把「预约」的属性支付状态画到患者实体上属性归属是 E-R 找错题经典坑m:n 联系转换时并入了某一端m:n 必须独立成表规则分直接丢转换答案不标外键主外键标注各占采分点范式题只说「属于 1NF」不写理由判定与理由部分依赖的具体依赖式分列采分理由必须落到具体函数依赖五、真题风格演练二处方域范式与 SQL5.1 题干模拟真题云诊通处方域的关系模式部分字段省略处方(处方ID, 患者ID, 工号, 开方日期, 患者姓名, 医生姓名, 复核药师ID)处方明细(处方ID, 药品编码, 药品名称, 单价, 数量, 用法用量)药品(药品编码, 药品名称, 生产企业, 处方分类)已知函数依赖处方ID → 患者ID、工号、开方日期、患者姓名、医生姓名、复核药师ID患者ID → 患者姓名工号 → 医生姓名(处方ID, 药品编码) → 数量、用法用量药品编码 → 药品名称、单价、生产企业、处方分类。业务查询需求(a) 统计 2026 年 10 月各科室开立的处方张数降序排列(b) 查询处方金额高于全平台平均处方金额的处方ID 与金额处方金额 明细中单价 × 数量之和。问题 18 分判断「处方」「处方明细」各属于第几范式说明理由将「处方」分解到 3NF。问题 29 分写出满足查询 (a) 的 SQL 语句。问题 38 分写出满足查询 (b) 的 SQL 语句。5.2 参考答案问题 1处方码为处方ID单属性无部分依赖问题但存在传递依赖 处方ID → 患者ID → 患者姓名、处方ID → 工号 → 医生姓名 →2NF3 分处方明细组合码 (处方ID, 药品编码)数量与用法用量完全依赖于组合码但药品名称依赖药品编码码的真子集→ 存在部分依赖 →1NF3 分处方分解到 3NF处方(处方ID, 患者ID, 工号, 开方日期, 复核药师ID)患者(患者ID, 患者姓名)医生(工号, 医生姓名)2 分问题 2骨架 5 分 细节 4 分SELECTd.科室编码,k.科室名称,COUNT(*)AS处方张数FROM处方 pJOIN医生 dONp.工号d.工号JOIN科室 kONd.科室编码k.科室编码WHEREp.开方日期BETWEEN2026-10-01AND2026-10-31GROUPBYd.科室编码,k.科室名称ORDERBYCOUNT(*)DESC;判分要点三表连接 ON 条件2 分、日期 WHERE1 分、GROUP BY 科室2 分、COUNT 聚合2 分、ORDER BY DESC2 分。问题 3子查询骨架 5 分 聚合细节 3 分SELECTm.处方ID,SUM(m.单价*m.数量)AS金额FROM处方明细 mGROUPBYm.处方IDHAVINGSUM(m.单价*m.数量)(SELECTAVG(t.金额)FROM(SELECTSUM(单价*数量)AS金额FROM处方明细GROUPBY处方ID)t);判分要点按处方分组求金额2 分、标量子查询求全平台平均3 分、HAVING 而非 WHERE 做分组后筛选2 分、两处聚合表达式一致1 分。5.3 扣分点提示演练二处方明细范式误判 2NF漏看「药品名称 → 药品编码」这条对码真子集的依赖——先把全部函数依赖列出来再判定查询 (a) 缺日期条件或忘了 ORDER BY每个关键子句都是独立采分点查询 (b) 用 WHERE 筛选聚合结果分组后条件必须 HAVING分解 3NF 后不重建外键说明患者ID、工号的保留要写明数据库题的完整作战主线业务说明 → E-R概念模型→ 关系模式逻辑模型→ 范式检查 → SQL使用验证与第 11 篇的六阶段设计流程一一对应。考场遇到数据库大题先在草稿上画这条链路每个小问自然落位业务说明实体-联系-属性三问E-R图联系类型判定关系模式转换规则主外键标注范式检查部分依赖与传递依赖SQL书写连接分组子查询规范真题风格自测题1. E-R 模型中联系的属性应挂载在 。A. 任一端实体上 B. 联系本身 C. 主键上 D. 外键上2. m:n 联系转换为关系模式时规范做法是 。A. 并入任意一端实体 B. 独立成表主键为两端实体的主键组合 C. 删除该联系 D. 拆成两个 1:n3. 1:n 联系转换为关系模式时外键应加在 。A. 1 端 B. n 端 C. 两端都加 D. 不需要外键4. 判定 2NF 的关键检查项是 。A. 属性是否原子 B. 非主属性是否完全函数依赖于码 C. 是否消除传递依赖 D. 决定因素是否含码5. 关系模式 R(学号, 课程号, 姓名, 成绩)码为学号, 课程号学号→姓名则 R 至多是 。A. 1NF B. 2NF C. 3NF D. BCNF6. 判定 3NF 的关键检查项是 。A. 消除非主属性对码的部分依赖 B. 消除非主属性对码的传递依赖 C. 属性原子性 D. 消除多值依赖7. SQL 中对分组结果的筛选条件应写在 。A. WHERE B. HAVING C. GROUP BY D. ORDER BY8. 「查询成绩高于平均成绩的学生」通常采用的子查询形态是 。A. IN 子查询 B. 标量子查询与 AVG 比较 C. EXISTS 子查询 D. 不可能用子查询9. 「统计各科室处方张数」的 SQL 必须包含 。A. GROUP BY 科室 与 COUNT 聚合 B. DISTINCT C. UNION D. 视图10. E-R 审题法中「多值属性」如医生的多个执业资质的规范处理是 。A. 拼成逗号分隔字符串 B. 独立成表 C. 放入主表数组字段 D. 忽略11. 弱实体如处方明细转换时其主键的特征是 。A. 自身独立主键 B. 包含所依赖实体的主键 C. 无主键 D. 用外键作主键即可无需组合12. 处方(处方ID, 患者ID, 患者姓名)中处方ID→患者ID→患者姓名该模式属于 。A. 1NF B. 2NF C. 3NF D. BCNF13. 数据库设计案例的完整主线是 。A. E-R → 关系模式 → 范式检查 → SQL B. SQL → E-R → 范式 C. 范式 → E-R → SQL D. 无固定顺序参考答案1.B 2.B 3.B 4.B 5.A 6.B 7.B 8.B 9.A 10.B 11.B 12.B 13.A本篇小结知识点核心内容四类命题E-R 补全 / 转关系模式 / 范式判定 / SQL 书写E-R 审题三问谁是实体名词、发生什么联系、什么特征属性联系类型判定看两端实例配对数量m:n 必须独立成表转换规则1:1 并入任一端、1:n 并入 n 端加外键、m:n 独立成表组合主键范式判定1NF 原子性 → 2NF 消部分依赖 → 3NF 消传递依赖 → BCNF 决定因素含码分解到 3NF按依赖源头拆表决定因素作主键重建外键说明SQL 规范显式 JOIN ON、GROUP BY聚合HAVING 三件套、标量子查询比较平均值演练一要点联系属性挂联系上m:n 不许并入外键标注是隐形采分点演练二要点先列全函数依赖再判范式分组后筛选用 HAVING判分按子句拆点下篇预告第 54 篇案例专项四——架构与新技术场景真题模式给场景选架构并论证、微服务拆分论证、新技术适用性分析三类命题的「结论 理由三点 风险一点」论证模板配 Web 高并发与微服务拆分两道真题风格演练。如果本篇内容对你有帮助欢迎点赞收藏有任何疑问欢迎在评论区交流。
