SQL数据库图书管理系统课程设计：表结构与建表实战解析

简介一份完整的 SQL 数据库图书管理系统课程设计文档面向数据库初学者、高校信息管理相关专业学生尤其适合正在完成课程设计或毕业设计的读者。资源以图书馆真实管理场景为背景围绕读者信息、图书信息、操作员信息三大模块覆盖图书借阅、还书、超期罚款等业务流程系统讲解从数据库存储设计、E-R 图、数据字典、关系模式设计到 SQL 语句实现的完整链路。文档还包含了详细的查询描述、关系代数表达和部分 SQL 查询结果便于理解图书管理系统的表结构逻辑与增删改查写法。资源仅 1 个 doc 文件压缩包大小 709KB轻量易下载打开即可对照学习和二次开发。目前已吸引 2317 人学习下载是课程设计报告撰写的实用参考。1. 图书管理系统背后的SQL数据库设计一份课程设计报告能复现什么图书馆管理系统是数据库课程设计里出现频率最高的题目但大部分流传出来的资料只有零散脚本能跑通的不多。这份《SQL数据库图书管理系统(完整代码)》是一份完整的设计报告外加可执行代码来自广西交通职业技术学院信息工程系的课程设计包含六张业务表、完整的外键约束、初始化的借阅数据和罚款记录覆盖了从E-R图设计到SQL实现再到结果验证的全流程。对正在做数据库课程设计、或者想快速搭一套图书借阅管理原型的人来说这份资源的价值在于它把系统分析、数据字典、关系模式、SQL建库建表、数据初始化、查询验证这条路完整走了一遍拿到手可以直接在SQL Server里执行不需要自己从头设计表结构。适合三类人一是正在写数据库课程设计报告的学生二是想参考小型业务系统表结构设计的开发新手三是需要一份带外键约束和业务状态管理的SQL范例的从业者。我把这套代码完整拆了一遍下面是表结构、建表语句、初始化脚本和踩坑点的逐项分析。2. 六张表怎么设计出来的E-R图、数据字典与关系模式2.1 实体划分与E-R图背后的业务推导图书管理系统这个问题的难点不在SQL语法而在业务实体怎么划分。这份报告把系统拆成六个实体书籍类别、读者、书籍、借阅记录、还书记录、罚款记录。这个划分对应的是图书馆日常业务的三条主线——管书、管人、管借还。书籍类别和书籍是主从关系一个类别下面挂多本书所以书籍表里放了一个外键bookstyleno指向类别表。读者和借阅记录、还书记录是一对多的关系一个读者可以借多本书。罚款记录本质上是由还书超期派生出来的业务数据注意看它的字段设计readerid、readername、bookid、bookname、bookfee、borrowdate这里冗余了读者姓名和书籍名称这种冗余在小系统里是合理的因为罚款单打印时需要直接显示这些信息省去每次关联查询。从这个设计能看出来做数据库设计第一步不是画表而是把业务过程捋清楚读者登记、借书、还书、超期罚款每个环节产生什么数据数据之间怎么关联。E-R图在这里起的作用是沟通工具让人一眼看清实体之间的关系而不是为了凑报告页数。2.2 数据字典里值得注意的字段设计数据字典定义了每张表的字段、类型、是否为空和主外键约束。我挑选几个关键点表名字段类型约束设计意图book_stylebookstylenovarchar(30)主键类别编号业务上可能是1、2这种短编码system_booksbookidvarchar(20)主键书籍编号示例数据用901、902三位数system_booksisborrowedvarchar(2)非空是否被借出用1和0表示字符类型而非bitsystem_readersreaderidvarchar(9)主键借书证编号学生号、教师号、管理号前缀不同borrow_recordbookidvarchar(20)主键兼外键一本书同一时间只能有一条借出记录return_recordbookidvarchar(20)主键兼外键还书记录同样是一本书一条reader_feebookfeevarchar(30)无约束罚款金额注意这里用了字符类型有两个细节值得展开说。第一个是borrow_record和return_record的主键设计。两张表都用bookid做主键这意味着同一本书在同一张表里只能出现一次。从业务角度理解这个设计隐含了一个假设一本书同一时间只能被一个人借走所以借阅记录表里一本书一条记录就够了。还书也一样一本书归还一次就销掉一条记录。这在小型图书馆场景下是成立的但如果做大型系统同一本书有多个副本就需要用流水号或者复合主键来区分。第二个是isborrowed字段的设计。这个字段在报告的数据字典里标的是varchar(2)初始化的数据里直接用1表示已借出0表示可借。用字符类型而不是bit类型这种选择在课程设计里很常见因为教学环境里bit类型的显示和操作对学生来说不够直观而且varchar(2)写起来灵活做演示效果更直观。我会在后面的初始化数据部分详细说这个字段是怎么配合更新逻辑的。2.3 关系模式与关系代数的落地价值报告里给出了六个关系模式形式上就是表结构的抽象描述。在实际开发中关系模式最大的用途是检查逻辑是否闭环。比如罚款表的关系模式里出现了两个借书证编号一个是读者信息一个是借阅信息这其实指向借阅时间的获取来源——罚款金额需要根据借书日期和还书日期计算超期天数所以borrowdate在罚款表里是必要的。报告里专门提到用关系代数进行运算得到所需结果。关系代数的选择、投影、连接操作对应到SQL里就是select、where、join。对于这份资源来说关系代数部分主要用于报告撰写上机实现看的是SQL语句。我的建议是如果时间紧张优先把SQL跑通关系代数可以放在文档里对照着写因为评分看的是逻辑正确性不是看你用了哪种运算符号。3. 建库建表与外键约束从CREATE DATABASE到六张核心表的落地3.1 创建数据库的参数细节这份资源的可执行脚本从建库开始以下是完整代码USE master GO CREATE DATABASE tangzhangsentsg ON ( NAME librarysystem, FILENAME c:\librarysystem.mdf, SIZE 10, MAXSIZE 50, FILEGROWTH 5 ) LOG ON ( NAME library_log, FILENAME c:\librarysystem_log.ldf, SIZE 5MB, MAXSIZE 25MB, FILEGROWTH 5MB ) GO这段脚本有几个参数需要实际动手时调整。SIZE 10表示初始大小10MBMAXSIZE 50限制最大50MBFILEGROWTH 5表示每次自动增长5MB。对课程设计这个量级的数据来说10MB初始空间绰绰有余即便插入几百条记录也用不到1MB。但要注意FILENAME的路径原文写的是c:这在大部分机器上会因为权限问题建库失败我一般会改成当前实例的数据目录或者直接用默认路径。另一个坑是数据库名。tangzhangsentsg是作者名字拼音加缩写命名这个库名在实际项目里没法复用建议改成library_db之类有业务含义的名字。CREATE DATABASE语句里的ON和LOG ON分别定义数据文件和日志文件的属性数据文件存表数据日志文件存事务日志。如果只写ON不写LOG ONSQL Server会默认创建一个1MB的日志文件但显式指定大小更可控。数据库创建完成后后续所有建表语句都要确保当前数据库是tangzhangsentsg否则表会建到master库里。常见做法是在建表脚本最前面加USE tangzhangsentsg或者手动在SSMS左上角下拉框切换。3.2 六张表的建表语句与外键链以下是书籍类别表、书籍表、读者表、借阅记录表、还书记录表、罚款记录表的完整建表代码-- 书籍类别表主键是类别编号类别名称非空 CREATE TABLE book_style ( bookstyleno varchar(30) PRIMARY KEY, bookstyle varchar(30) ) GO -- 书籍表主键是书籍编号外键关联类别表 CREATE TABLE 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 varchar(2), FOREIGN KEY (bookstyleno) REFERENCES book_style(bookstyleno) ) GO -- 读者表主键是借书证编号姓名和性别非空 CREATE TABLE 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 borrow_record ( bookid varchar(20) PRIMARY KEY, readerid varchar(9), borrowdate datetime, FOREIGN KEY (bookid) REFERENCES system_books(bookid), FOREIGN KEY (readerid) REFERENCES system_readers(readerid) ) GO -- 还书记录表结构与借阅记录表对应 CREATE TABLE return_record ( bookid varchar(20) PRIMARY KEY, readerid varchar(9), returndate datetime, FOREIGN KEY (bookid) REFERENCES system_books(bookid), FOREIGN KEY (readerid) REFERENCES system_readers(readerid) ) GO -- 罚款记录表书籍编号是主键也有外键约束 CREATE TABLE reader_fee ( readerid varchar(9) NOT NULL, readername varchar(9) NOT NULL, bookid varchar(20) PRIMARY KEY, bookname varchar(30) NOT NULL, bookfee varchar(30), borrowdate datetime, FOREIGN KEY (bookid) REFERENCES system_books(bookid), FOREIGN KEY (readerid) REFERENCES system_readers(readerid) ) GO先说书名为首的system_books表。它的外键指向book_style表建表顺序有讲究必须先建被引用的表book_style再建引用它的system_books否则SQL Server会报外键引用的对象不存在。同理borrow_record和return_record都引用了system_books和system_readers所以这两张表必须排在读者表和书籍表之后。reader_fee表虽然字段最多但它依赖的system_books和system_readers已经建好放在最后没有问题。这个建表顺序是这类资源的第一个隐藏考察点很多人拿到脚本按从上到下执行前面没问题到borrow_record就报错其实是前面的表还没建。再看字段类型的选择。所有编号字段都是varchar不是int或bigint。这个设计符合业务习惯——借书证编号可能有前缀字母Q、GL、201005这样的混合内容用数字类型会丢信息。书籍编号901看起来是数字但未来如果要加字母编号varchar才能兼容。日期字段用datetime金额字段bookfee用varchar(30)这个后面在避坑章节我会专门说。外键约束方面borrow_record的readerid允许为空因为SQL Server默认允许外键列存NULL。这在业务里意味着可以插入一条只有bookid、没有readerid的借阅记录这在逻辑上说不通。实际项目里应该把readerid改成NOT NULL至少加个检查约束。这是这个设计的薄弱点但不影响课程设计的演示效果。3.3 主键选择的取舍为什么用业务主键而不是自增ID这里值得思考一个问题为什么所有表都用业务字段做主键而不是加一个自增的id列比如书籍表用bookid做主键读者表用readerid做主键借阅记录表用bookid做主键。用业务主键的好处是查询直观不需要额外的索引查找。比如要知道一本书的借阅历史直接where bookid 901就能命中主键索引速度很快。对于数据量在几千条级别的课程设计系统这种方式没有任何性能问题。坏处是业务主键对变化不敏感。如果一本书的编号规则变了比如从三位数变成带字母的五位编码主键改动会影响所有外键引用它的表。自增ID的好处是物理主键和业务编号解耦无论业务编号怎么变主键都不用动。但在课程设计里业务主键展示起来更直观评分老师一眼能看懂bookid就是书籍编号不需要解释id1对应哪本书。我给一个实用建议如果你后续要把这个系统扩展成真正可用的图书管理系统书籍表要加副本数量和唯一标识码比如ISBN或者馆藏条码这时候再用图书id做唯一主键更合适。课程设计阶段保持原样没问题理解这个取舍就够了。4. 数据初始化与借阅状态维护INSERT和UPDATE的配合逻辑4.1 基础档案数据的加载表建好后第一步是加载基础数据。先往book_style表插入七种书籍类别再往system_books表插入八本示例图书。代码如下INSERT INTO book_style(bookstyleno, bookstyle) VALUES(1, 修真小说) INSERT INTO book_style(bookstyleno, bookstyle) VALUES(2, 穿越小说) INSERT INTO book_style(bookstyleno, bookstyle) VALUES(3, 恐怖小说) INSERT INTO book_style(bookstyleno, bookstyle) VALUES(4, 都市小说) INSERT INTO book_style(bookstyleno, bookstyle) VALUES(5, 科幻小说) INSERT INTO book_style(bookstyleno, bookstyle) VALUES(6, 仙侠小说) INSERT INTO book_style(bookstyleno, bookstyle) VALUES(7, 言情小说) GO INSERT INTO system_books( bookid, bookname, bookstyleno, bookauthor, bookpub, bookpubdate, bookindate, isborrowed ) VALUES ( 901, 飘渺之旅, 1, 萧潜, 鲜网, 2005-09-01, 2013-05-25, 1 ) INSERT INTO system_books( bookid, bookname, bookstyleno, bookauthor, bookpub, bookpubdate, bookindate, isborrowed ) VALUES ( 902, 唐朝好男人, 2, 多一半, 新星出版社, 2008-05-09, 2013-05-26, 1 ) -- 后续书本记录的插入格式相同这里省略中间四条 INSERT INTO system_books( bookid, bookname, bookstyleno, bookauthor, bookpub, bookpubdate, bookindate, isborrowed ) VALUES ( 908, 步步惊心, 2, 桐华, 民族出版社, 2006-06-20, 2013-05-30, 1 ) GObook_style的插入没有悬念就是七条固定记录。system_books插入时需要特别注意isborrowed字段初始值全部是1表示这批书默认都是已借出状态。为什么初始状态是已借出而不是可借因为后面的借阅记录表里马上会插入对应的借书记录这些书确实都已经被借走了所以初始值是1是符合业务事实的。日期字段的值用字符串格式2005-09-01直接插入datetime类型列SQL Server会自动做隐式转换。这个转换依赖数据库的日期格式设置在中文版SQL Server默认配置下没有问题。如果遇到日期转换报错常见做法是用CONVERT函数显式转换格式如CONVERT(datetime, 2005-09-01, 120)。4.2 借阅记录与状态同步的UPDATE联动接下来是这套系统里最核心的一个逻辑插入借阅记录后同步把书籍表里的isborrowed字段从1改成0。代码原文如下INSERT INTO borrow_record(bookid, readerid, borrowdate) VALUES(901, Q001, 2013-01-18 12:20) UPDATE system_books SET isborrowed 0 WHERE bookid 901 AND isborrowed 1这里有个反直觉的地方初始时isborrowed 1表示已借出插入借阅记录后UPDATE system_books SET isborrowed 0那0表示什么我按这个逻辑推一遍首先往system_books插入图书时把isborrowed设为1紧接着往borrow_record插入借阅记录然后update把isborrowed改为0。如果1表示已借出0表示未借出那么在插入借阅记录之前的瞬间这本书的isborrowed是1状态是已借出——但此时borrow_record里并没有对应的记录。这说不通。再换一个理解方向1表示这本书在馆内未被借出插入借阅记录后改成0表示这本书已借出。初始全部为1表示所有书都在馆内然后依次插入借阅记录每借出一本书就把状态改成0。这个解释和代码行为完全吻合。即isborrowed 1代表未借出可借isborrowed 0代表已借出不可借和注释里写的将已借出的借阅标记置0对应。理解这个逻辑很重要因为后面做查询时如果你想查哪些书还在馆内条件是isborrowed 1而不是0。这个字段的语义和直觉相反我在排错部分会再提到一次这是这套代码里最容易搞反的地方。UPDATE语句带AND isborrowed 1这个条件作用是防止重复更新。如果一本书的isborrowed已经是0说明它已经被借出此时再插入一条借阅记录然后执行UPDATE条件不满足不会更新。这算是一个简易的幂等保护虽然它不能阻止重复的借阅记录插入但至少状态不会被二次改写。实际项目里应该用事务把INSERT和UPDATE包起来保证两步操作要么都成功要么都失败课程设计代码没有用事务这是一个可以改进的点。4.3 读者数据的多样性和借阅记录的分布读者表的数据体现了借书证编号的多样性Q001、Q002这种纯字母加数字的面向学生201005、201006纯数字的面向教师GL001带管理含义的面向管理员。这个设计说明借书证编号是分前缀规则的有了readertype字段做辅助说明学生、教师、管理三种角色区分清楚。借阅记录也按照不同读者分布学生读者借了前五本教师读者借了后两本管理员的GL001没有借阅记录。这样设计的好处是后续做查询演示时可以用读者类型做分组统计比如按readertype统计借阅数量结果会自然呈现出学生比教师借书多的数据分布。到这里基础数据和业务数据都加载完了。下一章我直接说坑点因为这套代码的执行过程有几个地方第一次跑的时候非常容易翻车。5. 避坑指南外键、主键和初始化数据里的四个典型翻车点5.1 数据库文件路径导致建库失败现象执行CREATE DATABASE语句时报错错误信息类似操作系统拒绝了对路径的访问或者文件无法创建。原因原文中FILENAME指定为c:在Windows Vista之后的系统上普通用户对C盘根目录没有写权限SQL Server服务账户无法在C盘根目录创建.mdf和.ldf文件。解决把FILENAME改成SQL Server默认数据目录。可以先执行SELECT physical_name FROM sys.master_files WHERE name master查出主数据文件的路径然后把建库脚本里的路径改成这个目录下例如FILENAME C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\librarysystem.mdf。如果不想手改路径更省事的做法是直接删掉FILENAME这一行让SQL Server用默认路径建库。5.2 表依赖顺序导致外键创建失败现象按文档顺序执行建表脚本到borrow_record表时报错外键FK__borrow_re__booki__...引用了无效的表system_books或者类似提示。原因SQL Server要求外键引用的父表必须先存在。borrow_record的bookid外键依赖system_booksreaderid外键依赖system_readers如果这两张表还没创建就执行borrow_record的建表语句必然报错。解决严格执行创建顺序——先建book_style再建system_books和system_readers然后建borrow_record和return_record最后建reader_fee。如果你用的是SSMS的脚本执行窗口建议把这些建表语句分段执行每段执行后用SELECT name FROM sys.tables确认表已经创建成功再继续。还有一种粗暴但有效的办法如果已经建了一些表但报错了可以先把所有表drop掉重新来用DROP TABLE IF EXISTS逐张清理注意先删子表再删父表。5.3 isborrowed字段语义反转导致查询结果错乱现象按直觉写SELECT * FROM system_books WHERE isborrowed 1想查已借出的书结果查出来的是全部八本书跟预期完全相反。原因这套代码里isborrowed的语义是1表示未借出在库0表示已借出。初始插入时为1插入借阅记录后UPDATE改为0。很多人按字段字面意思理解以为1是已借出结果查出来的数据恰好是反的。解决在查询前先做一步数据验证。执行SELECT bookid, bookname, isborrowed FROM system_books对照borrow_record里的记录检查有借阅记录的书isborrowed应该是0没有借阅记录的书isborrowed应该是1。如果一致说明状态维护正常。这个确认动作花不了十秒钟但能避免后续所有查询都基于错误理解。我在第一次跑这套代码时就翻了车用1查在架图书结果查出了全部书籍后来对着borrow_record逐条比对才反应过来。5.4 NOT NULL约束挡住非法数据现象向system_readers表插入一行没有姓名的读者记录时报错提示不能将值NULL插入列readername。原因readername字段在建表时定义了NOT NULL约束业务上要求每个读者必须有姓名。课程设计报告里对读者信息的定义就是借书证编号、读者姓名、读者性别三项必填。解决按约束补全必填字段。这也是数据库设计的目的之一——在应用层没做校验的情况下数据库约束是最后一道防线。如果你在做自己的系统建议在应用层也做同样的非空校验让用户在界面上就填不了空值而不是等到数据库报错。另外注意readertype和regdate两个字段允许为空如果插入时省略这两个值不会报错比如INSERT INTO system_readers(readerid, readername, readersex) VALUES(X001, 张三, 男)是可以执行的。6. 用查询验证系统闭环把报告里的SQL变成可检查的功能点文档的最后一个部分是结果数据处理用单表查询演示。实际复现时我建议不只是跑一句SELECT * FROM book_style看结果而是把整个系统的业务逻辑用查询串起来验证一遍确认六张表的数据是自洽的。最值得先跑的是借阅状态验证查询SELECT b.bookid, b.bookname, b.isborrowed, br.readerid, br.borrowdate FROM system_books b LEFT JOIN borrow_record br ON b.bookid br.bookid WHERE b.isborrowed 0这条语句能查出所有已借出的书籍及其借阅人、借阅日期。如果join出来的结果和borrow_record里的记录一一对应说明前面的INSERT和UPDATE联动没有漏执行。LEFT JOIN保证即使某本书没有借阅记录也会出现在结果集里便于排查数据初始化的疏漏。第二个建议跑的是多表关联查询按书籍类别统计在架图书数量SELECT bs.bookstyle, COUNT(b.bookid) AS total_books, SUM(CASE WHEN b.isborrowed 1 THEN 1 ELSE 0 END) AS available_books FROM book_style bs LEFT JOIN system_books b ON bs.bookstyleno b.bookstyleno GROUP BY bs.bookstyle这个查询同时验证了外键关联关系和数据状态字段的正确性。比如穿越小说类目下有两本书编号902和908两本都有借阅记录所以total_books是2、available_books是0。如果查询结果和预期一致说明从建表到初始化再到状态更新整条链路都是通的。验证的方式我习惯在SSMS里把上面两条查询和文档里的原始查询都跑一遍然后手动数一遍borrow_record的行数再数一遍isborrowed为0的书数两者必须相等。这个习惯救了我好几次因为手动执行的SQL脚本一旦漏掉某个UPDATE数据状态就错位了。文档里还提到罚款信息管理这部分在原报告里没有给完整的计算SQL。要补全的话需要根据还书日期和应还日期算出超期天数乘以每日罚金再写入reader_fee表。这类SQL在数据量大之后性能会明显下降因为日期计算没法走索引。不过课程设计的数据量完全不用担心先把业务逻辑跑通更重要。从那以后我每次拿到一份数据库课程设计代码都会先执行一遍状态自洽验证再动查询。这套资源最值得学习的地方不在于SQL技巧有多高深而在于它完整展示了设计文档——建库建表——数据初始化——业务验证的闭环。照着复现一遍跑通了你再去看E-R图和关系模式会清晰很多。希望帮到你。本文还有配套的精品资源点击获取