SQL Server图书管理系统数据库设计：从E-R图到建表脚本的完整实践

简介：这是一份完整的基于SQL数据库的图书管理系统课程设计文档，以单个doc文件（709KB）打包，适合数据库应用技术课程的学生、需要完成图书馆管理类实训项目的开发者，以及正在学习关系数据库设计的初学者。系统针对传统人工管理方式混乱、效率低的问题，以读者信息、图书信息、操作员信息为核心模块，覆盖图书借阅、还书、超期罚款等业务场景。文档先后给出图书馆管理系统的存储结构设计、E-R图、数据字典、关系模式，再到SQL实现，具体包括读者基本信息、书籍类别、书籍库存、借书、还书、罚款等数据表的结构定义与增删改查操作，可直接借鉴其建表语句和查询代码，也可作为课程设计报告书的写作模板。资源已有2317人学习下载，是一份系统性强、步骤完整的图书管理数据库参考资料，有助于快速掌握数据库应用系统从需求分析到代码实现的全过程。

1. 从课设题目到完整可运行方案：SQL数据库图书管理系统怎么落地

做图书管理系统课设时，最容易翻车的不是SQL语法，而是表结构没立住。我见过不少同学把借书、还书、罚款各做一张表，再把所有读者信息和操作会话塞进同一张表，结果写到半途发现外键连不起来，查询统计全是坑。这套SQL数据库图书管理系统从某高校信息工程系的数据库课程设计报告整理而来，完整覆盖了E-R图、关系模式、数据字典、建库建表脚本和借还罚款的业务数据初始化，适合正在写数据库课程设计报告、需要一份可以直接参考甚至复用代码的同学。方案以读者、图书、借阅、归还、罚款为轴心，目标是让数据库模型尽量简单、关系清楚、验收时有据可查。

2. 总体架构与数据建模：六个实体、三张业务表与E-R图设计

整个系统的建模思路是先区分“基础数据”“业务数据”“统计数据”，再决定哪些东西进表、哪些只作为业务规则。这样写出来的SQL才不会越写越绕。基础数据包括读者信息、图书信息、操作员信息，业务数据包括借还书登记和罚款登记，统计数据则是书籍借阅情况或读者借阅情况。把它们落到E-R图里，就形成了书籍类别、读者、书籍、借阅记录、归还记录、罚款六个核心实体。

2.1 需求边界与角色划分：哪些数据该进表，哪些只该做约束

系统里有三类角色：读者、图书馆馆员、系统管理员。读者只产生借还行为，馆员负责录入和维护读者与图书的基础信息，管理员在此基础上处理超期罚款和统计查询。这三类角色并不一定都要建表，很多时候只是在功能菜单上做权限区分；但如果连“管理员如何登录”都讲不清楚，答辩时就会被追问。

把需求清单过一遍，会发现基础数据主要有读者借书证编号、读者姓名、读者性别、书籍编号、书籍名称、书籍类别、作者姓名、出版社名称、出版日期、登记日期等；业务数据主要有借书日期、还书日期、罚款金额等。整理时要注意：题目里如果写了操作员信息，表结构里就一定得有对应落点，否则就是“需求写了一套、实现另做一套”的典型翻车现场。我一般会先画一张功能清单和实体清单的对照表，确认每个需求点都有表可查、有字段可存。

2.2 E-R图与关系模式：把多对多拆成一对多

E-R图里最核心的关系是：书籍类别对书籍是一对多，读者对借阅记录是一对多，书籍对借阅记录也是一对多；而读者和书籍本质上是多对多关系，需要通过借阅记录这张中间表拆开。归还记录独立成表，不要和借阅记录塞在一起，否则“当前在借”和“历史借过”就很难分开统计。罚款记录以读者、书籍、借阅时间作为业务关联点，用来记录超期还书的金额。

对应到关系模式，整理后如下：

书籍类别（bookstyleno, bookstyle） 读者（readerid, readername, readersex, readertype, regdate） 书籍（bookid, bookname, bookstyleno, bookauthor, bookpub, bookpubdate, bookindate, isborrowed） 借阅（record_id, readerid, bookid, borrowdate） 归还（record_id, readerid, bookid, returndate） 罚款（fee_id, readerid, readername, bookid, bookname, bookfee, borrowdate）

注意这里的借阅和归还都加上了自增主键record_id，而不是直接用bookid当主键。很多课程设计里会把borrow_record的bookid设为主键，这在“一本书只借一次”的演示数据下勉强能跑，但只要出现还书后再借同一本书，就会主键冲突。为了避免这种低级翻车，通常做法是加一个无业务含义的自增主键，把readerid和bookid降为外键。

2.3 数据字典：字段类型、可空性与主外键约定

按照上面的关系模式，可以整理出六张表的数据字典。下面这张表基本和后续建表SQL保持一致，字段命名尽量用英文，类型以SQL Server为准。表格里PK表示主键，FK表示外键，NN表示非空。

这里有一个细节要特别说明：原报告把罚款金额定义成varchar，我觉得不妥。金额是典型数值型数据，需要用decimal或numeric存储，否则后面SUM统计时会出错。罚款表里的读者姓名、书籍名称属于冗余字段，目的是对账方便，不承担数据一致性兜底；真正的主外键仍然挂在readerid和bookid上。这是小型管理系统的常见取舍。

3. 数据库实现与SQL脚本：建库、六张表与业务数据初始化的落地细节

建模完成后进入实现阶段。整套SQL脚本按四个层次组织：建库、建表、初始化数据、查询统计。建库和建表可以一并执行，初始化数据是演示用的基础数据，查询统计则是验收时展示运行结果的关键。

3.1 建库与文件配置：库名、文件增长与日志策略

创建数据库时不要直接用“库名+姓名拼音”这种写法，更不要像某些示例那样把FILENAME直接写成c:\。真实执行时文件路径必须存在，否则数据库创建直接失败。我一般会先指定一个物理路径，再设置初始大小、最大大小和文件增长比例，让SQL Server自动扩容。

USE master; GO IF DB_ID('LibraryManage') IS NOT NULL DROP DATABASE LibraryManage; GO CREATE DATABASE LibraryManage ON ( NAME = librarysystem, FILENAME = 'D:\SQLData\LibraryManage.mdf', SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB ) LOG ON ( NAME = library_log, FILENAME = 'D:\SQLData\LibraryManage_log.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ); GO

这段配置里，SIZE指定初始文件大小，MAXSIZE限制上限，FILEGROWTH设置每次扩容的步长。日志文件一般比数据文件小，所以这里给了5MB到25MB的增长范围。如果你是第一次在机器上跑这套脚本，记得先创建D:\SQLData目录，或者把FILENAME改成自己的本机路径。

3.2 核心建表语句：主外键约束与借阅状态标记

建表顺序很关键。先建书籍类别表，再建书籍表和读者表，然后建借阅记录、归还记录和罚款表。因为后几张表都要引用前面的主键，顺序反了外键会找不到参照表。下面这组SQL可以直接跑。

USE LibraryManage; GO -- 书籍类别表 CREATE TABLE dbo.book_style ( bookstyleno VARCHAR(30) PRIMARY KEY, bookstyle VARCHAR(30) NOT NULL ); GO -- 书库表 CREATE TABLE dbo.system_books ( bookid VARCHAR(20) PRIMARY KEY, bookname VARCHAR(30) NOT NULL, bookstyleno VARCHAR(30) NOT NULL, bookauthor VARCHAR(30), bookpub VARCHAR(30), bookpubdate DATETIME, bookindate DATETIME, isborrowed CHAR(1) NOT NULL DEFAULT '0', CONSTRAINT FK_system_books_style FOREIGN KEY (bookstyleno) REFERENCES dbo.book_style(bookstyleno) ); GO -- 读者表 CREATE TABLE dbo.system_readers ( readerid VARCHAR(9) PRIMARY KEY, readername VARCHAR(9) NOT NULL, readersex VARCHAR(2) NOT NULL, readertype VARCHAR(10), regdate DATETIME ); GO -- 借阅记录表 CREATE TABLE dbo.borrow_record ( record_id INT IDENTITY(1,1) PRIMARY KEY, readerid VARCHAR(9) NOT NULL, bookid VARCHAR(20) NOT NULL, borrowdate DATETIME NOT NULL, CONSTRAINT FK_borrow_reader FOREIGN KEY (readerid) REFERENCES dbo.system_readers(readerid), CONSTRAINT FK_borrow_book FOREIGN KEY (bookid) REFERENCES dbo.system_books(bookid) ); GO -- 归还记录表 CREATE TABLE dbo.return_record ( record_id INT IDENTITY(1,1) PRIMARY KEY, readerid VARCHAR(9) NOT NULL, bookid VARCHAR(20) NOT NULL, returndate DATETIME NOT NULL, CONSTRAINT FK_return_reader FOREIGN KEY (readerid) REFERENCES dbo.system_readers(readerid), CONSTRAINT FK_return_book FOREIGN KEY (bookid) REFERENCES dbo.system_books(bookid) ); GO -- 罚款记录表 CREATE TABLE dbo.reader_fee ( fee_id INT IDENTITY(1,1) PRIMARY KEY, readerid VARCHAR(9) NOT NULL, readername VARCHAR(9) NOT NULL, bookid VARCHAR(20) NOT NULL, bookname VARCHAR(30) NOT NULL, bookfee DECIMAL(8,2) NOT NULL, borrowdate DATETIME NOT NULL, CONSTRAINT FK_fee_reader FOREIGN KEY (readerid) REFERENCES dbo.system_readers(readerid), CONSTRAINT FK_fee_book FOREIGN KEY (bookid) REFERENCES dbo.system_books(bookid) ); GO

这里把isborrowed设置成char(1)，默认值0，约定0表示在库，1表示已借出。所有涉及状态同步的操作都要围绕这个约定来做。借阅表不用bookid做主键，而是用自增流水号，这样同一本书归还后还能再次借阅。罚款表保留readername和bookname冗余字段，是为了罚款单据上能直接显示，不用每次join两张表。

3.3 数据初始化：类别、读者、书籍与借还状态同步

初始化数据分三步：先插类别，再插书籍和读者，最后录借书并同步更新isborrowed。为了演示方便，下面用最简单的三本书和三位读者。

INSERT INTO dbo.book_style(bookstyleno, bookstyle) VALUES ('C01', N'计算机'), ('C02', N'文学'), ('C03', N'历史'); GO INSERT INTO dbo.system_books (bookid, bookname, bookstyleno, bookauthor, bookpub, bookpubdate, bookindate, isborrowed) VALUES ('BK001', N'数据库原理与实践', 'C01', N'某作者一', N'某出版社', '2023-09-01', '2023-09-10', '0'), ('BK002', N'计算机网络基础', 'C01', N'某作者二', N'某出版社', '2023-08-15', '2023-09-11', '0'), ('BK003', N'中国通史入门', 'C03', N'某作者三', N'某出版社', '2022-06-01', '2023-09-12', '0'); GO INSERT INTO dbo.system_readers (readerid, readername, readersex, readertype, regdate) VALUES ('R001', N'A同学', '男', '学生', '2024-03-01'), ('R002', N'B同学', '女', '学生', '2024-03-02'), ('R003', N'C老师', '男', '教师', '2024-03-03'); GO

借书时先插入借阅记录，再立即把对应书籍的isborrowed改成1。这两步必须成套出现，不能只做其中一个。

INSERT INTO dbo.borrow_record(readerid, bookid, borrowdate) VALUES ('R001', 'BK001', '2024-03-05'); UPDATE dbo.system_books SET isborrowed = '1' WHERE bookid = 'BK001'; GO

还书时则反过来，先插入归还记录，再把书籍状态改回0。

INSERT INTO dbo.return_record(readerid, bookid, returndate) VALUES ('R001', 'BK001', '2024-03-12'); UPDATE dbo.system_books SET isborrowed = '0' WHERE bookid = 'BK001'; GO

这套两步骤操作看起来简单，但它是整个系统的核心。初始化脚本如果只是插入几条数据，而没有对应的状态同步，后面查询当前在借书籍时就会得到错误结果。SQL Server不要求这两条语句一定放在事务里，但为了安全，我建议至少在做还书操作时用BEGIN TRAN包起来。

3.4 查询与统计：借阅明细、当前在借与罚款汇总的SQL写法

基础数据放进去后，就可以用SELECT语句来验证系统是否能回答业务问题。以下几个查询是答辩时最常被要求现场演示的。

-- 书籍类别单表查询 SELECT * FROM dbo.book_style; GO -- 当前处于借出状态的书 SELECT * FROM dbo.system_books WHERE isborrowed = '1'; GO -- 查询某个读者当前在借的书籍明细 SELECT r.readername, b.bookname, br.borrowdate FROM dbo.borrow_record br INNER JOIN dbo.system_readers r ON br.readerid = r.readerid INNER JOIN dbo.system_books b ON br.bookid = b.bookid WHERE NOT EXISTS ( SELECT 1 FROM dbo.return_record rr WHERE rr.readerid = br.readerid AND rr.bookid = br.bookid ) ORDER BY br.borrowdate DESC; GO -- 统计每本书被借阅的次数 SELECT bookid, COUNT(*) AS borrow_times FROM dbo.borrow_record GROUP BY bookid ORDER BY borrow_times DESC; GO -- 按读者汇总罚款金额 SELECT readerid, SUM(bookfee) AS total_fee FROM dbo.reader_fee GROUP BY readerid; GO

这里最值得讲清楚的是NOT EXISTS子查询。借阅记录表里保存每一次借书，归还记录表里保存每一次还书，只要某本书的借阅记录在归还表中找不到对应记录，就说明这本书仍处于在借状态。这个写法比直接用isborrowed判断更可靠，因为它是从流水数据倒推出来的，能发现状态标记漏更新的问题。

另外，罚款汇总的前置条件是bookfee已经改成decimal类型。如果沿用varchar类型，SUM要么报错，要么返回0，这一点是课程设计里最常见的坑。

4. 避坑与常见问题排查：主键、状态同步与需求落地的四类翻车现场

模型和SQL都能跑通，不代表整个系统经得起追问。下面这些问题都是我实际带项目时经常看到的翻车点，按“现象、原因、解决”三条线来写，方便你直接对号入座。

4.1 借阅表主键选错，一本书第二次借直接报主键冲突

现象：演示完借书、还书，再借同一本书时，INSERT语句报错，提示主键冲突，或者提示无法插入重复键。明明书已经还回来了，程序却借不出去。

原因：原设计把bookid设置为borrow_record表的主键，而主键在整张表里只能出现一次。第一次借书记录插入后，归还记录只是写在另一张return_record里，borrow_record中的老记录并没有删除，所以再次插入同一本bookid必然冲突。

解决：把借阅记录表改成自增主键，bookid和readerid都降为外键。这样同一本书可以被同一个读者多次借阅，每次借阅都是一条独立流水。对于查询“某人当前在借什么书”，用4.1里给出的NOT EXISTS思路即可。如果是已经建好的库，可以对表结构做调整，删除原主键，改为identity列。

4.2 isborrowed状态与借还记录不一致

现象：书已经还了，系统里仍然显示“已借出”；或者书显示“在库”，但实际上存在未归还的借阅记录。查询当前在借列表时，数据对不上。

原因：isborrowed只是books表上的一个普通状态位，它和borrow_record、return_record是两条独立操作。任何一步漏执行、执行失败、或者写错了更新条件，状态位就跟实际流水不同步。课程设计里很多人靠手工UPDATE来维护这个字段，还书时只插了return_record，忘了改状态位。

解决：借书时把“插借阅记录”和“更新isborrowed为1”放进同一个事务，还书时把“插归还记录”和“更新isborrowed为0”放进同一个事务。写存储过程或应用程序事务都可以，重点是两条语句必须同生共死，不能只成功一半。

BEGIN TRAN; INSERT INTO dbo.borrow_record(readerid, bookid, borrowdate) VALUES ('R001', 'BK001', GETDATE()); UPDATE dbo.system_books SET isborrowed = '1' WHERE bookid = 'BK001'; COMMIT;

如果你沿用的是原始脚本里“插入借阅后update isborrowed=0”的写法，建议先统一状态语义。我一般规定0表示可借，1表示已借，所有代码都按这个约定写，不要一会儿0借出、一会儿1借出。

4.3 罚款表金额用了varchar，SUM结果变成0或字符串排序

现象：执行罚款汇总查询时，SUM(bookfee)返回0，或者SQL Server直接报“Operand data type varchar is invalid”的错误；按金额排序时，10排在了9前面。

原因：数据字典里把bookfee定义成了varchar(30)。字符串里存的数字可以显示，但不能被当成数值计算。数据库不会自动把“12.5”转成数字再求和，而是按字符类型处理。

解决：建表时就把bookfee字段改成decimal(8,2)。如果数据已经进了varchar字段，先清理可能存在的脏数据，再做ALTER修改字段类型，查询时也可以临时强制转换。

-- 如果表已存在且需要修复类型 ALTER TABLE dbo.reader_fee ALTER COLUMN bookfee DECIMAL(8,2); -- 临时转换写法 SELECT readerid, SUM(CAST(bookfee AS DECIMAL(8,2))) AS total_fee FROM dbo.reader_fee GROUP BY readerid;

金额、数量这类字段一开始就用数值类型，是最省事的做法。答辩时如果老师问“为什么金额用decimal”，你就回答“为了精确统计和排序”，这是很完美的理由。

4.4 需求里的操作员信息没有落到表结构

现象：设计报告里写了“基础数据包括操作员信息”，但数据库里没有操作员表，管理员登录功能无法对应到任何数据对象，答辩时被追问就直接卡住。

原因：需求清单是从题目里抄的，E-R图和表结构是另一套设计，两边没有对齐。操作员信息在页面上可能只是暂时没做，但在数据库设计里没有落点，就会被认为逻辑不完整。

解决：在模型里补一张operator表，作为管理员登录和权限控制的基础。如果时间来得及，就把它关联到读者管理、还书管理、罚款管理等模块上；如果只是应付课设，至少让表存在并且能查询。

CREATE TABLE dbo.operator ( opid VARCHAR(10) PRIMARY KEY, opname VARCHAR(20) NOT NULL, oppwd VARCHAR(32) NOT NULL );

另外，读者表里的readertype字段虽然写了“学生”“教师”“管理”等类型，但不要把它和操作员混为一谈。操作员是后台管理账号，读者是借阅账号，两者在业务上要分开。

4.5 快速排查顺序：出问题时先看这四个地方

如果查询结果不对或插入失败，我一般会按固定顺序排查，而不是盲目改代码。

先看外键方向。外键应该从业务记录表指向基础表，也就是借阅记录引用读者和书籍，而不是书籍表引用借阅表。再看isborrowed是否有遗漏的UPDATE。第三看日期和时间，SQL Server的datetime插入格式如果是2024-03-05可以识别，但如果前端传进来的是03/05/2024可能被当成月日，导致借出和归还顺序错乱。最后看金额类字段，所有带fee或price的字段都不要用varchar。

把这四条走一遍，大部分问题都能定位到具体表和具体语句。

5. 验收与答辩技巧：三张截图、一套测试用例和一张问题准备表

数据库课程设计的验收通常不看你写了多少行SQL，而看三件事：表结构是否合理、借还罚流程是否演示得通、被追问到底层设计时能不能讲清为什么这么设计。我会固定用下面这套方法准备验收。

5.1 验收顺序：按业务流完整跑一遍

不要只截一张建表成功图，也不要只展示SELECT出来的数据。正确的是按业务流程从头到尾演示，每一步提前准备好预期结果。

这七个步骤正好覆盖输入、修改、查询、统计、约束保护五类能力。每一步都截图保留结果，答辩时按顺序放出来，比自己临时敲命令要稳妥得多。

5.2 答辩时必准备的几个问题

第一个问题是“E-R图里的多对多关系怎么拆的”。你要能说出读者和图书是多对多，通过借阅记录拆成两个一对多。第二个问题是“为什么借阅表不用bookid做主键”。你直接说原设计会产生第二次借阅冲突，改成自增主键是为了让同本书可以反复借。第三个问题是“isborrowed有什么作用、怎么保证一致”。你要回答它是状态冗余，真正依据是流水表，借还操作必须成对更新。第四个问题是“罚款表里为什么冗余了读者姓名和书名”。你回答这是为了罚款单据直接显示，不用每次都join，只要主外键关系不乱就不会有严重问题。

准备好这四个问题，基本就能过掉百分之八十的答辩追问。剩下的时间用来展示你的查询语句和结果。从那以后我每次带课设，都会先写下这张验收表和问题清单再动手写代码，而不是等代码写完再硬测。把验收动作前置，整个项目的完成度和答辩信心会明显不一样。希望帮到你。

本文还有配套的精品资源，点击获取