图书管理系统-数据库设计

选题背景

依照图书管理系统的要求，完成如下任务

1. 设计适合此应用需求的规范化的数据库；
2. 建立ODBC数据源；
3. 创建数据库连接；
4. 制作一个具有安全性控制（权限管理和视图管理等）的数据库访问程序。

数据项

借阅人信息：学号(stuid)，姓名(name)，性别(sex)，科别代号(depno)，科别名(depname)，年级(grade)，班级(class)，电话(tel)，地址(addr)

书籍信息：书籍编号(code)，书籍名称(bookname)，作者(writer)，出版商(publisher)，购买日期(buydate)，借阅次数(number)，是否借出(isloan)

借阅信息：借书人号，书号，借阅日期(loandate)，归还日期(returndate)

任务要求

1. 学生和图书信息的增删查改及查询操作；
2. 借书和还书操作。（其中图书管理员可以更新书库；图书操作人员可以进行借、还书操作；借书人可以查询图书及自己的借阅情况。

实验环境

VSCode

• 创建数据库

mysql> create database library;
Query OK, 1 row affected (0.01 sec)

mysql> use library;
Database changed

• 创建表

/* 借阅人信息 */
mysql> create table user(
    -> stuid varchar(10) primary key,
    -> name varchar(20) not null,
    -> sex varchar(20),
    -> depno varchar(20),
    -> depname varchar(20),
    -> grade varchar(20),
    -> class varchar(20),
    -> tel varchar(20),
    -> addr varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert
    -> into user
    -> values('2005120220','张三','男','321001','网络空间安全','20级','网安2班','13207527332','武汉工程大学');
Query OK, 1 row affected (0.00 sec)

mysql> insert
    -> into user
    -> values('2005120213','李四','女','320211','人工智能','20级','人工智能3班','18801447232','武汉工程大学');
Query OK, 1 row affected (0.00 sec)

mysql> insert
    -> into user
    -> values('2006121110','王五','男','416627','计算机科学与技术','21级','计科6班','18707527556','武汉工程大学');
Query OK, 1 row affected (0.00 sec)

mysql> insert
    -> into user
    -> values('2004120220','李晨','男','132001','网络空间安全','19级','网安5班','17288644522','武汉大学');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+------------+------+------+--------+------------------+-------+-------------+-------------+--------------+
| stuid      | name | sex  | depno  | depname          | grade | class       | tel         | addr         |
+------------+------+------+--------+------------------+-------+-------------+-------------+--------------+
| 2004120220 | 李晨 | 男   | 132001 | 网络空间安全     | 19级  | 网安5班     | 17288644522 | 武汉大学     |
| 2005120213 | 李四 | 女   | 320211 | 人工智能         | 20级  | 人工智能3班 | 18801447232 | 武汉工程大学 |
| 2005120220 | 张三 | 男   | 321001 | 网络空间安全     | 20级  | 网安2班     | 13207527332 | 武汉工程大学 |
| 2006121110 | 王五 | 男   | 416627 | 计算机科学与技术 | 21级  | 计科6班     | 18707527556 | 武汉工程大学 |
+------------+------+------+--------+------------------+-------+-------------+-------------+--------------+
4 rows in set (0.00 sec)


/* 书籍信息 */
mysql> create table books(
    -> code varchar(10) primary key,
    -> bookname varchar(50),
    -> writer varchar(20),
    -> pubilsher varchar(50),
    -> buydate varchar(20),
    -> number varchar(20),
    -> isloan varchar(10)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert
    -> into books
    -> values('001','狂人日记','鲁迅','中国商业出版社','2017-07-13','23','否');
Query OK, 1 row affected (0.01 sec)

mysql> insert
    -> into books
    -> values('002','老人与海','海明威','南海出版社','2016-03-29','122','是');
Query OK, 1 row affected (0.00 sec)

mysql> insert
    -> into books
    -> values('003','放学后','东野圭吾','南海出版社','2022-07-24','179','否');
Query OK, 1 row affected (0.00 sec)

mysql> insert
    -> into books
    -> values('004','三国演义','罗贯中','人民出版社','2019-12-21','765','是');
Query OK, 1 row affected (0.00 sec)

mysql> insert
    -> into books
    -> values('005','计算机操作系统','汤小丹','电子科技大学出版社','2020-10-12','1324','是');
Query OK, 1 row affected (0.01 sec)


mysql> insert
    -> into books
    -> values('006','通信原理','樊昌信','高等教育出版社','2019-08-19','89','否');
Query OK, 1 row affected (0.00 sec)

mysql> insert
    -> into books
    -> values('007','数据库系统概论','王珊','高等教育出版社','2018-11-23','72','是');
Query OK, 1 row affected (0.00 sec)

mysql> select * from books;
+------+----------------+----------+--------------------+------------+--------+--------+
| code | bookname       | writer   | pubilsher          | buydate    | number | isloan |
+------+----------------+----------+--------------------+------------+--------+--------+
| 001  | 狂人日记       | 鲁迅     | 中国商业出版社     | 2017-07-13 | 23     | 否     |
| 002  | 老人与海       | 海明威   | 南海出版社         | 2016-03-29 | 122    | 是     |
| 003  | 放学后         | 东野圭吾 | 南海出版社         | 2022-07-24 | 179    | 否     |
| 004  | 三国演义       | 罗贯中   | 人民出版社         | 2019-12-21 | 765    | 是     |
| 005  | 计算机操作系统 | 汤小丹   | 电子科技大学出版社 | 2020-10-12 | 1324   | 是     |
| 006  | 通信原理       | 樊昌信   | 高等教育出版社     | 2019-08-19 | 89     | 否     |
| 007  | 数据库系统概论 | 王珊     | 高等教育出版社     | 2018-11-23 | 72     | 是     |
+------+----------------+----------+--------------------+------------+--------+--------+
7 rows in set (0.00 sec)


/* 借阅信息 */

mysql> create table records(
    -> stuid varchar(10),
    -> code varchar(10),
    -> loandate varchar(20),
    -> returndate varchar(20),
    -> primary key(stuid,code),
    -> foreign key(stuid) references user(stuid),
    -> foreign key(code) references books(code)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> insert
    -> into records
    -> values('2004120220','002','2023-1-1',null);
Query OK, 1 row affected (0.01 sec)

mysql> insert
    -> into records
    -> values('2004120220','007','2022-01-02',null);
Query OK, 1 row affected (0.00 sec)

mysql> update recodes
    -> set loandate='2021-07-18'
    -> where stuid='2004120220' and code ='002';
ERROR 1146 (42S02): Table 'library.recodes' doesn't exist
mysql> update records
    ->  set loandate='2021-07-18'
    -> where stuid='2004120220' and code ='002';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update records
    -> set returndate='2021-08-01'
    ->  where stuid='2004120220' and code ='002';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from records;
+------------+------+------------+------------+
| stuid      | code | loandate   | returndate |
+------------+------+------------+------------+
| 2004120220 | 002  | 2021-07-18 | 2021-08-01 |
| 2004120220 | 007  | 2022-01-02 | NULL       |
+------------+------+------------+------------+
2 rows in set (0.00 sec)

mysql> insert
    -> into records
    -> values('2005120213','001','2022-03-23','2022-04-12');
Query OK, 1 row affected (0.00 sec)

mysql> insert
    -> into records
    -> values('2005120213','004','2021-09-27','2021-10-14');
Query OK, 1 row affected (0.00 sec)

mysql> insert
    -> into records
    -> values('2005120213','006','2023-02-03',null);
Query OK, 1 row affected (0.00 sec)

mysql> insert
    -> into records
    -> values('2005120220','005','2022-09-21','2022-09-30');
Query OK, 1 row affected (0.00 sec)

mysql> insert
    -> into records
    -> values('2006121110','003','2021-09-22','2021-10-13');
Query OK, 1 row affected (0.00 sec)

mysql> select * from records;
+------------+------+------------+------------+
| stuid      | code | loandate   | returndate |
+------------+------+------------+------------+
| 2004120220 | 002  | 2021-07-18 | 2021-08-01 |
| 2004120220 | 007  | 2022-01-02 | NULL       |
| 2005120213 | 001  | 2022-03-23 | 2022-04-12 |
| 2005120213 | 004  | 2021-09-27 | 2021-10-14 |
| 2005120213 | 006  | 2023-02-03 | NULL       |
| 2005120220 | 005  | 2022-09-21 | 2022-09-30 |
| 2006121110 | 003  | 2021-09-22 | 2021-10-13 |
+------------+------+------------+------------+
7 rows in set (0.00 sec)

mysql> insert
    -> into records
    -> values('2005120220','004','2022-12-21',null);
Query OK, 1 row affected (0.00 sec)

mysql> insert
    -> into records
    -> values('2006121110','005','2023-2-1',null);
Query OK, 1 row affected (0.00 sec)

mysql> update
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2
mysql> update records
    -> set returndate='202300203'
    -> where stuid='2005120213' and code='006';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update records
    ->  set returndate='2023-02-03'
    ->  where stuid='2005120213' and code='006';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from records
    -> ;
+------------+------+------------+------------+
| stuid      | code | loandate   | returndate |
+------------+------+------------+------------+
| 2004120220 | 002  | 2021-07-18 | 2021-08-01 |
| 2004120220 | 007  | 2022-01-02 | NULL       |
| 2005120213 | 001  | 2022-03-23 | 2022-04-12 |
| 2005120213 | 004  | 2021-09-27 | 2021-10-14 |
| 2005120213 | 006  | 2023-02-03 | 2023-02-03 |
| 2005120220 | 004  | 2022-12-21 | NULL       |
| 2005120220 | 005  | 2022-09-21 | 2022-09-30 |
| 2006121110 | 003  | 2021-09-22 | 2021-10-13 |
| 2006121110 | 005  | 2023-2-1   | NULL       |
+------------+------+------------+------------+
9 rows in set (0.00 sec)

• 创建用户

mysql> grant all privileges on library.records to "李四"@"%";
Query OK, 0 rows affected (0.00 sec)

mysql> create user "王五" identified by "wangwu123456";
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on library.records to "王五"@"%";
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,update on library.user to "王五"@"%";
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on library to "张三"@"%";
Query OK, 0 rows affected (0.00 sec)

以上是本人数据库的课设，选题是图书管理系统，这一篇是相关数据库设计，网站制作期间如有相关问题会修正，后期还会发布关于网站设计部分，希望各位兄弟姐妹能够多多指教哦！