简介这份《用户需求定义》PDF面向数据库课程学习者与系统设计初学者以StayHome录像租赁连锁为背景完整梳理分公司视图的数据需求与事务需求帮助读者理解从业务描述到数据库设计的转化过程。资源包内含1个PDF文件约28KB内容涵盖分公司、员工、录像、会员、租借五类核心数据的字段定义录入、更新、删除与查询等事务操作以及初始规模、增长速度、网络共享、性能、安全、备份恢复和法律合规等系统定义要点。文中还给出按城市查分公司、按姓名排序员工、按种类统计录像等具体查询示例并附有数据量与并发访问的量化指标可作为需求分析、ER建模与数据库课程设计的参考素材。目前已有66人学习适合需要撰写需求规格说明书或进行数据库课程实践的学生与开发者借鉴。1. 从一份 2013 年的需求定义 PDF 说起StayHome 数据库到底要建什么如果你手上只有一份《用户需求定义[定义].pdf》却要把它变成能跑的库第一道坎不是写 SQL而是把散落在业务描述里的实体、唯一键和基数关系抠出来。StayHome 这份文档就是典型它用“数据需求 事务需求 系统定义”三段式把一家连锁录像租赁公司的分公司视图讲清楚了。核心实体有六个——分公司、员工、录像、拷贝、会员、租借每个实体都带唯一标识和业务约束。它适合谁适合正在做数据库课程设计、需要一份真实需求规格来练手的学生也适合想复盘“需求到表结构”映射逻辑的初级开发。这份 PDF 不是代码包但它是建库前必须吃透的输入。2. 把需求定义拆成实体关系从文字到表结构的映射方法2.1 六个核心实体的唯一键与基数关系需求文档里最值钱的信息不是“有哪些字段”而是“谁唯一标识谁、谁和谁几对几”。StayHome 的原文写得很密我把它拆成下面这张映射表方便你直接对照建表。实体唯一标识关键属性与其他实体的关系分公司分公司名称全公司唯一地址、电话最多3行拥有员工、库存、会员员工员工号码全公司唯一姓名、职务、薪水属于一个分公司含经理、监理录像目录号唯一片名、种类、日租费、购买价、状态、演员、导演有多个拷贝拷贝录像号唯一状态可租/不可租属于一个录像、一个分公司会员会员号所有分公司唯一姓名、地址、注册日期、注册员工可在多分公司使用最多租10部租借租借号全公司唯一会员号、录像号、日租费、租出/归还日期关联会员与拷贝这张表里有两个容易翻车的点第一会员号“对所有分公司唯一”意味着它是全局主键不是分公司内自增第二录像和拷贝是两层概念目录号标识“片名”录像号标识“具体那盘带子”租借业务挂在拷贝上而不是录像上。很多新手一上来就把片名当主键后面查“某分公司某录像的拷贝情况”时直接卡死。2.2 从事务需求反推约束与索引需求文档的第二部分列了 26 条事务a 到 z这些不是功能清单而是约束和索引的线索。我一般会按“写事务”和“读事务”分开处理。写事务a 到 l决定外键和级联规则。比如“录入某分公司的新员工”要求员工必须挂在一个已存在的分公司下“删除某会员租借某部录像的信息”意味着租借表要有明确的删除路径不能因为删会员就把租借历史全冲掉。读事务m 到 z决定索引。举几个原文里的高频查询m) 列出给定城市的分公司情况 → 分公司表按城市建索引n) 按员工名字顺序列出指定分公司的员工 → 员工表按分公司, 姓名建复合索引q) 按片名顺序列出某分公司指定演员的录像 → 需要演员-录像关联表并按片名排序v) 列出每个分公司每种录像的数量 → 拷贝表按分公司, 录像建索引配合分组统计y) 列出每个分公司在某一年注册的会员数量 → 会员表按分公司, 注册日期建索引这些查询在原文里还带了频率指定录像查询每天 5000 次周日到周四、10000 次周五周六高峰在下午 6 到 9 点。这意味着索引不是“有就行”而是要在高峰期扛住每秒几次的并发。我一般会先把高频查询的 WHERE 和 ORDER BY 列出来再决定复合索引的列顺序。2.3 建表 SQL 骨架与参数说明下面这段 SQL 是我按需求文档抠出来的最小可用骨架用 PostgreSQL 语法写MySQL 也能改。注意看注释里的约束来源。-- 分公司表名称全公司唯一电话最多3行用单独字段存 CREATE TABLE branch ( branch_name VARCHAR(100) PRIMARY KEY, -- 需求名称在全公司唯一 street VARCHAR(200) NOT NULL, city VARCHAR(100) NOT NULL, state VARCHAR(50) NOT NULL, zip VARCHAR(20) NOT NULL, phone1 VARCHAR(30), phone2 VARCHAR(30), phone3 VARCHAR(30) ); -- 员工表员工号全公司唯一职务区分经理/监理/其他 CREATE TABLE employee ( emp_no VARCHAR(20) PRIMARY KEY, -- 需求员工号全公司唯一 emp_name VARCHAR(100) NOT NULL, position VARCHAR(50) NOT NULL, -- 经理/监理/助理/采购员等 salary NUMERIC(10,2), branch_name VARCHAR(100) NOT NULL REFERENCES branch(branch_name) ); -- 录像表目录号唯一种类限定为五类 CREATE TABLE video ( catalog_no VARCHAR(30) PRIMARY KEY, -- 需求目录号唯一标识一盘录像 title VARCHAR(200) NOT NULL, category VARCHAR(20) NOT NULL CHECK (category IN (动作,成人,儿童,恐怖,科幻)), daily_rent NUMERIC(8,2) NOT NULL, purchase_price NUMERIC(10,2), status VARCHAR(20) DEFAULT 可租 ); -- 拷贝表录像号唯一属于某个分公司和某个录像 CREATE TABLE copy ( copy_no VARCHAR(30) PRIMARY KEY, -- 需求录像号唯一标识一份拷贝 catalog_no VARCHAR(30) NOT NULL REFERENCES video(catalog_no), branch_name VARCHAR(100) NOT NULL REFERENCES branch(branch_name), rentable BOOLEAN DEFAULT TRUE -- 需求状态指出是否可出租 ); -- 会员表会员号全局唯一注册员工姓名也要记录 CREATE TABLE member ( member_no VARCHAR(30) PRIMARY KEY, -- 需求会员号对所有分公司唯一 member_name VARCHAR(100) NOT NULL, address VARCHAR(300), reg_date DATE NOT NULL, reg_emp_name VARCHAR(100) -- 需求负责注册的员工姓名 ); -- 租借表租借号全公司唯一关联会员和拷贝 CREATE TABLE rental ( rental_no VARCHAR(30) PRIMARY KEY, -- 需求租借号全公司唯一 member_no VARCHAR(30) NOT NULL REFERENCES member(member_no), copy_no VARCHAR(30) NOT NULL REFERENCES copy(copy_no), rent_date DATE NOT NULL, return_date DATE, daily_fee NUMERIC(8,2) NOT NULL );这段骨架里我故意没加演员和导演表因为原文对演员的描述是“主要演员名字以及扮演的角色”这是一个多对多关系需要单独拆表。如果你只是做课程设计可以先建actor和video_actor两张表如果要完整复现查询 q 和 x就必须拆。参数说明VARCHAR长度我按业务量估的分公司名 100 够用地址 300 能放下完整街道NUMERIC(10,2)存薪水NUMERIC(8,2)存日租费避免浮点误差。CHECK约束把录像种类锁死在原文列的五类里这是需求文档明确写的不要漏。3. 事务需求落地录入、更新、删除与高频查询的 SQL 实现3.1 录入与更新事务的 SQL 模板原文的 a 到 l 是写事务我挑几个最容易出错的写成模板。注意看每个事务对应的约束检查。-- a) 录入一个新分公司 INSERT INTO branch (branch_name, street, city, state, zip, phone1) VALUES (西雅图中心店, 123 Main St, Seattle, WA, 98101, 206-555-0100); -- b) 录入某分公司的新员工 INSERT INTO employee (emp_no, emp_name, position, salary, branch_name) VALUES (E2001, 张三, 监理, 5500.00, 西雅图中心店); -- d) 录入给定分公司的某部录像拷贝 INSERT INTO copy (copy_no, catalog_no, branch_name, rentable) SELECT C0001, V100, 西雅图中心店, TRUE WHERE EXISTS (SELECT 1 FROM video WHERE catalog_no V100) AND EXISTS (SELECT 1 FROM branch WHERE branch_name 西雅图中心店); -- f) 录入租借协议先检查会员已租数量是否小于10 INSERT INTO rental (rental_no, member_no, copy_no, rent_date, daily_fee) SELECT R0001, M500, C0001, CURRENT_DATE, v.daily_rent FROM copy c JOIN video v ON c.catalog_no v.catalog_no WHERE c.copy_no C0001 AND (SELECT COUNT(*) FROM rental r WHERE r.member_no M500 AND r.return_date IS NULL) 10;逻辑说明录入拷贝时用EXISTS双重检查防止往不存在的录像或分公司下挂拷贝录入租借时用子查询卡住“一次最多租十部”的约束。这两个检查如果放到应用层做并发下会翻车放在 SQL 里至少能保证单条语句的原子性。更新和删除事务g 到 l要注意级联顺序。比如删除某会员不能直接DELETE FROM member因为租借表有外键。常见做法是先删该会员未归还的租借记录再删会员或者把外键设为ON DELETE CASCADE但那样会丢掉历史租借数据。我一般会保留历史用软删除标记会员状态而不是物理删除。3.2 高频查询的索引设计与执行计划原文的 m 到 z 是查询事务其中 c、d、f 三条每天上万次是性能大头。我把它们对应的索引写出来。-- 查询 c指定录像的情况按目录号查 CREATE INDEX idx_video_catalog ON video(catalog_no); -- 查询 d某盘录像的某份拷贝按录像号查 CREATE INDEX idx_copy_copy_no ON copy(copy_no); -- 查询 f会员租借录像的详细情况按会员号查未归还 CREATE INDEX idx_rental_member_return ON rental(member_no, return_date); -- 查询 n按员工名字顺序列出指定分公司员工 CREATE INDEX idx_employee_branch_name ON employee(branch_name, emp_name); -- 查询 v每个分公司每种录像的数量 CREATE INDEX idx_copy_branch_catalog ON copy(branch_name, catalog_no);参数说明idx_rental_member_return把member_no放前面、return_date放后面是因为查询 f 先按会员过滤再判断是否未归还。如果反过来索引选择性会变差。idx_employee_branch_name的列顺序对应查询 n 的 WHERE 和 ORDER BY能同时命中过滤和排序。验证方法用EXPLAIN ANALYZE跑一遍查询看是否走 Index Scan 而不是 Seq Scan。如果数据量小的时候优化器不选索引可以临时SET enable_seqscan off强制走索引确认索引本身有效。3.3 系统定义里的性能与安全参数怎么落原文的系统定义部分给了很具体的数字初始 20000 盘录像、400000 盘拷贝、2000 员工、100000 会员每月新增 100 部新片、每片 20 份拷贝每天 5000 条租借记录。这些数字直接决定分区和备份策略。我一般会按租借表的增长速度做分区每天 5000 条两年就是 365 万条左右按rent_date做月度分区查询时能裁剪掉大部分数据。备份按原文要求“每天半夜 12 点”用pg_dump加 cron 就能满足但要注意备份期间不要锁表用pg_dump -Fc自定义格式恢复时更灵活。安全性方面原文要求“每个员工分配一个到特定用户视图的数据库访问权限”对应到 PostgreSQL 就是建角色加视图授权。比如给监理建一个只能看本分公司员工和拷贝的视图再GRANT SELECT ON view_name TO role_name。这一步很多课程设计会跳过但它是需求文档明确写的答辩时容易被问。4. 避坑与排查需求定义到建库过程中最容易翻车的五件事4.1 把“录像”和“拷贝”混成一张表现象建表时只建了video用copy_no当主键结果查“某分公司某录像的拷贝情况”时发现同一个片名在不同分公司有不同拷贝主键冲突。原因原文明确写了“目录号唯一地表示一盘录像”和“每个拷贝由录像号唯一地表示”这是两层实体。录像描述片名和种类拷贝描述具体那盘带子在哪家店、能不能租。解决拆成video和copy两张表copy表用catalog_no外键关联video用branch_name关联分公司。租借业务挂copy_no不挂catalog_no。4.2 会员号用分公司内自增现象两个分公司各自从 1 开始编会员号跨店租借时会员号撞车查租借历史查到别人的记录。原因原文写“会员号对所有分公司都是唯一的而且可以在多个分公司使用同一会员注册号”。这意味着会员号是全局主键不能按分公司自增。解决用全局序列或 UUID 生成会员号或者在应用层用“分公司代码 序列”拼成全局唯一字符串。建表时member_no直接做主键不要加branch_name做复合主键。4.3 租借表只记会员号不记每日费用现象会员租的时候日租费是 5 元还的时候录像涨价到 8 元系统按新价格算租金会员投诉。原因原文写租借业务数据包括“每日费用”这个费用是租出时锁定的不是还的时候现查的。解决rental表里加daily_fee字段录入租借时从video.daily_rent复制一份存进去。这样即使录像价格调整历史租借的计费不受影响。4.4 删除员工时把租借历史一起冲掉现象员工离职后执行DELETE FROM employee因为外键级联该员工经手的所有租借记录被删月底对账少了几百条。原因原文写“离开公司一年的员工记录从数据库中删除”但租借记录要保留两年。这两个删除周期不一致不能简单级联。解决员工表用软删除加is_active字段标记离职一年后再物理删除租借表的外键设为ON DELETE RESTRICT防止误删。或者把员工姓名冗余到租借表里删员工不影响租借历史。4.5 高峰期查询不走索引现象下午 6 到 9 点查询指定录像的响应时间从 0.5 秒涨到 8 秒超过原文要求的 5 秒上限。原因录像表数据量不大时优化器选全表扫描但高峰期并发上来后全表扫描抢 CPU拖慢所有查询。解决用EXPLAIN ANALYZE确认执行计划对高频查询强制走索引同时检查连接池大小原文要求“每个分公司三名成员同时访问”100 个分公司就是 300 并发连接池不够会排队。常见做法是加 PgBouncer 做连接池把最大连接数控制在数据库能扛的范围内。5. 进阶技巧用视图和物化视图把复杂查询压到毫秒级原文的查询 v 和 z 是典型的聚合查询v 要列出每个分公司每种录像的数量z 要列出每个分公司的可能租金收入。这两条如果每次实时算在 400000 盘拷贝的数据量下会拖垮数据库。我一般会用物化视图加定时刷新来解决。-- 物化视图每个分公司每种录像的数量 CREATE MATERIALIZED VIEW mv_branch_category_count AS SELECT c.branch_name, v.category, COUNT(*) AS copy_count FROM copy c JOIN video v ON c.catalog_no v.catalog_no GROUP BY c.branch_name, v.category; -- 建唯一索引支持 CONCURRENTLY 刷新 CREATE UNIQUE INDEX idx_mv_branch_cat ON mv_branch_category_count(branch_name, category); -- 定时刷新比如每小时一次避开高峰期 REFRESH MATERIALIZED VIEW CONCURRENTLY mv_branch_category_count;逻辑说明CONCURRENTLY刷新不锁表查询可以继续走旧数据适合高峰期前刷新一次。参数上刷新频率取决于业务对实时性的要求原文没有明确说 v 和 z 的查询频率我一般按每小时一次处理如果业务要求更实时可以降到 15 分钟。另一个技巧是把查询 q 和 r 的演员、导演关联做成覆盖索引。原文要求“按照片名顺序列出某分公司指定演员的录像名称、种类和是否可租借”这条查询要跨actor、video_actor、video、copy四张表。我一般会建一个video_actor表然后在(actor_name, title)上建复合索引让排序在索引里完成避免额外的 sort 步骤。验证方法用EXPLAIN ANALYZE对比物化视图和实时查询的执行时间在 400000 行数据下物化视图通常能把聚合查询从秒级压到毫秒级。但要注意物化视图的数据新鲜度如果业务要求“可租借情况”实时反映就不能用物化视图得用普通视图加索引。从那以后我每次拿到一份需求定义 PDF都强制先做一遍实体-唯一键-基数映射再动手写第一行 SQL。这份 StayHome 的需求文档虽然写于 2013 年但它对唯一键和事务频率的描述放到今天做任何租赁类系统都不过时。希望帮到你。本文还有配套的精品资源点击获取