MySQL数据库应用技术课件：数据库设计案例

数据库设计案例MySQL数据库应用技术知识目标（1）掌握数据库设计的流程；（2）了解图书管理系统各功能模块的划分；能力目标素养目标思政目标（1）能熟练的进行图书管理系统后台数据库设计；（2）能开发图书管理系统各功能模块，实现简单的增删改查功能。（1）增强实际动手能力；（2）养成良好的代码编写习惯。（1）培养学生主动学习的意识和兴趣，以及对终身学习的认同感。一教学目标教学重点、难点1.数据库设计2.功能模块划分重点难点1.页面设计2.代码实现二三

学习任务完成图书管理系统数据库设计及系统开发，要求如下：1.绘制E-R图；2.创建表；3.创建视图、索引、序列、同义词等其他数据库对象；4.编写实现用户登录、注册、图书录入、修改、删除和查询等功能；授课内容110.1.1绘制E-R图210.1.2创建数据库对象310.1.3创建表的命令四

任务1数据库设计思维导图10.1.1

绘制E-R图图书管理系统中涉及的对象主要包括管理员、教师、学生和图书等，主要实现新书的入库、图书的借出、归还以及对现有图书的查询、信息修改和下架操作。

创建数据库对象根据E-R图，本系统共设计了图书表、图书类型表、书架表、管理员表、学生表、教师表、学生图书借阅表和教师图书借阅表等。另外本系统还设计了索引、序列、视图和同义词等其他数据库对象。1.创建数据表10.1.2列名数据类型长度约束说明typeIdint

Primarykey图书类型typeNamevarchar20Notnull类型名称daysint

Notnull可借天数（1）图书类型表

创建数据库对象（2）图书表10.1.2列名数据类型长度约束说明barCodevarchar30Primarykey条形码bookNamevarchar50Notnull图书名称typeIdint

Notnull图书类型authorvarchar20Notnull作者publishervarchar30Notnull出版社isbnvarchar30NotnullISBNpricefloat7,2Notnull价格bookcaseIdint

Notnull书架号

创建数据库对象（3）书架表10.1.2列名数据类型长度约束说明bookcaseIdint

Primarykey书架号bookcaseNamevarchar20Notnull书架名称（4）管理员表列名数据类型长度约束说明Manager_idint

Primarykey管理员账户Manager_namevarchar20Notnull管理员姓名Manager_pwdvarchar20Notnull密码Manager_telvarchar20Notnull电话

创建数据库对象（5）学生表10.1.2列名数据类型长度约束说明Stu_noint

Primarykey学号Stu_namevarchar20Notnull姓名sexvarchar10Notnull性别Stu_collegevarchar30Notnull学院Stu_majorvarchar30Notnull专业Stu_classvarchar30Notnull班级Stu_dormvarchar30Notnull宿舍Stu_telvarchar20Notnull电话emailvarchar50NotnullE-mailremarkvarchar100

备注

创建数据库对象（6）教师表10.1.2列名数据类型长度约束说明tea_noint

Primarykey职工号Tea_namevarchar20Notnull姓名Tea_collegevarchar30Notnull所在学院Tea_telvarchar20Notnull电话（7）学生图书借阅表列名数据类型长度约束说明Stu_noint

Primarykey学号barCodevarchar30Notnull条形码Borrow_timedatetime

Notnull借阅时间Return_timedatetime

Notnull应还时间

创建数据库对象（8）教师图书借阅表10.1.2列名数据类型长度约束说明tea_noint

Primarykey教师工号barCodevarchar30Notnull条形码Borrow_timedatetime

Notnull借阅时间Return_timedatetime

Notnull应还时间

创建数据库对象2.创建索引10.1.2对象命令说明作用索引CREATEBITMAPINDEXstudent_sex_bitmapONSTUDENT(sex)学生表索引列为性别的位图索引只需要更少的存储空间，这样每次读取可以读更多的记录，连接和聚集都变成了位算数运算，大大减少了运行时间，从而得到性能上的极大提升。CREATEBITMAPINDEXbook_typeid_bitmapONBOOK(typeId)图书表索引列为图书类型的位图索引CREATEBITMAPINDEXbook_bookcaseid_bitmapONBOOK(bookcaseId)图书表索引列为书架号的位图索引CREATEUNIQUEINDEXbook_barcode_uniqueonBOOK(barCode)图书表索引列为图书条形码的唯一索引唯一索引是不允许其中任何两行具有相同的索引值。CREATEUNIQUEINDEXteacher_uniqueonTEACHER(tea_no)教师表索引列为教师工号的唯一索引

创建数据库对象3.创建序列10.1.2对象命令说明作用序列CREATESEQUENCEbook_squSTARTWITH10001INCREMENTBY1CACHE20NOCYCLE图书表序列，开始值为10001，增量为1，缓存序列值为20，不循环自动生成唯一整数CREATESEQUENCEbooktype_squSTARTWITH1INCREMENTBY1CACHE10图书类型表序列，开始值为1，增量为1，缓存序列值为10，不循环CREATESEQUENCEbookcase_squSTARTWITH101INCREMENTBY1CACHE10书架表序列，开始值为101，增量为1，缓存序列值为10，不循环CREATESEQUENCEmanager_squSTARTWITH2017010000INCREMENTBY1CACHE10NOCYCLE管理员表序列，开始值为2017010000，增量为1，缓存序列值为10，不循环

创建数据库对象4.创建视图10.1.2对象命令说明作用视图Createorreplaceviewjyphbasselectpublisher,isbn,bookcaseIdfrombook,(selectbarCode,count(*)wherebook.barCode=t1.barCode;创建视图按照借阅次数降序排序，形成借阅排行榜简化查询

创建数据库对象5.创建同义词10.1.2对象命令说明作用同义词Createpublicsynonymbookforroot.book;图书表公有同义词，每个用户都能访问该对象方便引用对象、简化查询及保证数据安全Createprivatesynonymmanagerforroot.manager;管理员表私有同义词，只有管理员才能访问该对象Createpublicsynonymphbforroot.jyphb;借阅排行榜公有同义词，每个用户都能访问该对象Createprivatesynonymunreturnforroot.unreturn;逾期未还书表私有同义词，只有管理员才能访问该对象

创建表命令10.1.31.图书表Createtablebook(barCodevarchar(30)primarykey,bookNamevarchar(50)notnull,typeIdintnotnull,Authorvarchar(20)notnull,Publishervarchar(30)notnull,Isbnvarchar(30)notnull,Pricefloat(7,2)notnull,bookcaseIdintnotnull)2.图书类型表Createtablebooktype(typeIdintprimarykey,typeNamevarchar(20)notnull,Daysintnotnull)3.书架表Createtablebookcase(bookcaseIdintprimarykey,bookcaseNamevarchar(20)notnull)5.管理员表Createtablemanager(Manager_idintprimarykey,Manager_namevarchar(20)notnull,Manager_pwdvarchar(20)notnull)4.学生表Createtablestudent(Stu_nointprimarykey,Stu_namevarchar(20)notnull,Sexvarchar(10)notnull,Stu_collegevarchar(30)notnull,Stu_majorvarchar(30)notnull,Stu_classvarchar(30)notnull,Stu_dormvarchar(30)notnull,Stu_telvarchar(20)notnull,Emailvarchar(50)notnull,Remarkvarchar(100))

创建表命令10.1.36.教师表Createtableteacher(Tea_nointprimarykey,Tea_namevarchar(20)notnull,Tea_collegevarchar(30)notnull,Tea_telvarchar(20)notnull)7.学生图书借阅表Createtablestudent_borrow(Stu_nointprimarykey,barCodevarchar(30)notnull,Borrow_timedatetimenotnull,Return_timedatetimenotnull)8.教师图书借阅表Createtableteacher_borrow(tea_nointprimarykey,barCodevarchar(30)notnull,Borrow_timedatetimenotnull,Return_timedatetimenotnull)课堂总结绘制E-R图123创建数据库对象创建表；创建索引；创建序列；创建视图；创建同义词。创建表命令编写创建表命令授课内容10.2.1用户登录及注册110.2.2图书管理210.2.3功能代码编写3五

任务2系统开发思维导图9.2.1

用户登录及注册本系统基于B/S结构，采用MVC开发模式，主要用户为学生、教师和管理员，系统总体功能包括用户登录、用户注册、图书的录入、修改、删除、查询、浏览以及用户口令修改等。在浏览器中输入网址，确认后进入系统登录页面。10.2.19.2.1

图书管理

图书管理主界面中以列表的方式列出所有图书的详细信息，单击图书录入按钮可以录入图书信息，录入后即可在图书管理界面中进行显示，单击修改按钮，弹出图书修改界面，可以修改相应的图书信息，单击删除按钮会弹出对话框，提示确认是否删除，如确定则删除对应的图书内容。10.2.29.2.1

图书管理10.2.29.2.1

图书管理10.2.29.2.1

图书管理10.2.29.2.1

部分功能代码1.连接MySQL数据库10.2.3publicclassJDBCConnectionFactory{publicstaticConnectiongetConnection(){ Connectionconn=null; try{ Class.forName("com.mysql.jdbc.Driver"); conn=DriverManager.getConnection( "jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&characterSetResults=utf8", "root","111"); }catch(Exceptione){ } returnconn;}}9.2.1

部分功能代码2.BookDAO图书数据处理10.2.3publicclassBookDAO{//录入图书信息publicvoidinsert(intbarCode,StringbookName,inttypeId,Stringauthor,Stringpublisher,Stringisbn,floatprice,intbookcaseId){ Connectionconn=JDBCConnectionFactory.getConnection(); Stringsql="insertintobookvalues(?,?,?,?,?,?,?,?)"; try{ PreparedStatementpstmt=conn.prepareStatement(sql); pstmt.setInt(1,barCode); pstmt.setString(2,bookName); pstmt.setInt(3,typeId); pstmt.setString(4,author); pstmt.setString(5,publisher); pstmt.setString(6,isbn); pstmt.setFloat(7,price); pstmt.setInt(8,bookcaseId); pstmt.executeUpdate(); }catch(Exceptione){ }}9.2.1

部分功能代码2.BookDAO图书数据处理10.2.3//删除图书信息publicintdelete(intbarCode){ intn=0; Connectionconn=JDBCConnectionFactory.getConnection(); Stringsql="deletefrombookwherebarCode=?"; try{ PreparedStatementpstmt=conn.prepareStatement(sql); pstmt.setInt(1,barCode); n=pstmt.executeUpdate(); }catch(Exceptione){ } returnn; }9.2.1

部分功能代码2.BookDAO图书数据处理10.2.3//修改图书信息publicvoidupdate(intbarCode,StringbookName,inttypeId,Stringauthor,Stringpublisher,Stringisbn,floatprice,intbookcaseId){ Connectionconn=JDBCConnectionFactory.getConnection(); Stringsql="updatebooksetbookName=?,typeId=?,author=?,publisher=?,isbn=?,price=?,bookcaseId=?wherebarCode=?"; try{ PreparedStatementpstmt=conn.prepareStatement(sql); pstmt.setString(1,bookName); pstmt.setInt(2,typeId); pstmt.setString(3,author); pstmt.setString(4,publisher); pstmt.setString(5,isbn); pstmt.setFloat(6,price); pstmt.setInt(7,bookcaseId); pstmt.setInt(8,barCode); pstmt.executeUpdate(); }catch(Exceptione){ }}9.2.1

部分功能代码2.BookDAO图书数据处理10.2.3//