实验目的

1. 熟悉开发实现一个数据库应用系统的基本流程。

2. 熟悉数据库操作以及应用与数据库交互过程的编写。

3. 掌握使用数据库作为工具完成访问控制的方法。

4. 掌握防止 SQL 注入的基本方法。

5. 掌握数据库并发、数据备份与恢复等功能的实现。

实验内容

实验任务一：需求分析

在设计数据库应用系统前，需要对此系统进行需求分析。在需求分析中，主要针对数据库进行需求分析，可以采用数据流图、数据字典等方式。

实验任务二：系统设计与数据库设计

在编写数据库应用系统之前，需要对系统和数据库进行总体设计。

根据需求分析的有关内容，画出系统的 E-R 图，并据此设计对应的数据库表结构，然后对系统进行简单范式分析等。

实验任务三：编写数据库应用系统

设计实现一个数据库应用系统，Web、App 等皆可，数据库、语言、平台、框架等可自选，推荐使用 Linux+Apache+PHP+Mysql。有如下要求：

1）用户登录，至少两种不同类型的用户权限访问控制；

2）用户口令哈希存储；

3）体现数据库完整性检查；

4）体现防 SQL 注入；

5）进行系统并发测试；

6）具有数据备份与恢复功能

实验环境

处理器：Apple M1 pro

操作系统：maOS Ventura 13.0.1

开发平台：MacOS + Pycharm + MySQL

环境依赖：

  • Python 3.10

  • Flask 2.3.2

  • PyMySQL 1.0.3

  • MySQL 8.0.33

需求分析

总体分析

需要建立一个外卖订餐平台，为餐饮店与客户提供服务。平台总共有三种用户类型：管理员、商家用户和买家用户，可以实现用户的注册和登陆。

通过管理员登陆的用户可以查看平台上的商家列表以及用户评价，并且拥有移除商家的权限；
商家用户需要看到自己的菜单、用户的评论、订单信息，同时可以修改个人信息；

买家用户可以看到商家、查看评价、菜单、购物车，自己的订单和评价，发表评价以及修改个人信息。

数据字典

数据项名 名称 数据类型 长度 取值范围 取值含义 与其他数据的逻辑关系
用户名 username CHAR 15 用户昵称 主键或外键
餐厅 restaurant CHAR 15 餐厅名字 主键或外键
菜名 dishname CHAR 15 菜品名字 主键或外键
价格 price DECIMAL （5，2） 0-99999 菜品价格
餐厅图片路径 img_res VARCHAR 50 0-50 图片路径
密码 password CHAR 12 密码
地址 address VARCHAR 30 地址
电话 phone CHAR 15 电话号码
菜品信息 dishinfo VARCHAR 50 菜的介绍
营养成分 mutriention VARCHAR 30 菜的营养成分
售量 sales INT 2 菜的销售量
菜品图片路径 imgsrc VARCHAR 50 图片路径
是否是招牌菜 isSpecialty TINYINT 1 招牌菜的标签
订单号 orderID CHAR 15 订单号 主键或外键
订单完成情况 isFinished TINYINT 1 订单完成标签
开销 cost DECIMAL （5，2） 订单实际花费
评分 c_rank TINYINT 1 客户评分
评论 text VARCHAR 50 用户评论
交易时间 transactiontime TIMESTAMP 记录评论的订单时间

系统设计与数据库设计

系统设计

设计外卖订餐平台“思琪の外卖屋”，平台系统设计如下图所示。

image-20250731155441421

数据库设计

ER图

image-20250731155457787

关系模式

管理员（管理员昵称，密码）

买家用户（用户名，密码，地址，电话）

菜品（菜名，餐厅，餐品信息，营养成分，价格，销量，图片位置，是否是特色菜）

评价（订单号，用户昵称，餐厅，订单是否完成，花费，评分，评价，交易时间）

商家用户（餐厅名称，密码，地址，电话，图片位置）

购物车（用户，餐厅，菜品名称，价格，图片位置）

表结构

总共需要六张表来存储数据：ADMIN（管理员）、CUSTOMER （买家用户）、DISHES（菜品）、 ORDER_COMMENT（评价）、 RESTAURANT（商家用户）、SHOPPINGCART（购物车）。在MySQL中表示如下：

image-20250731155525179

ADMIN需要两类数据：username（管理员昵称）、password（密码）：

image-20250731155536180

CUSTOMER需要四类数据：username（用户名）、password（密码）、address（地址）和phone（电话）：

image-20250731155548489

DISHES需要八类数据：dishname（菜名）、restaurant（餐厅）、dishinfo（餐品信息）、nutriention（营养成分）、price（价格）、sales（销量）、imgsrc（图片位置）、isSpecialty（是否是特色菜）；

image-20250731155600974

ORDER_COMMENT需要8类数据：orderID（订单号）、username（用户昵称）、restaurant（餐厅）、inFinished（订单是否完成）、cost（花费）、c_rank（评分）、text（评价）、transactiontime（交易时间）：

image-20250731155610526

RESTAURANT需要5类数据：username（餐厅名称）、password（密码）、address（地址）、phone（电话）、img_res（图片位置）：

image-20250731155619387

SHOPPINGCART需要5类数据：username（用户）、restaurant（餐厅）、dishname（菜品名称）、price（价格）、img_res（图片位置）：

image-20250731155627651

范式分析

本数据库的关系均为BC范式，因为非主属性对候选键的函数依赖中不存在部分函数依赖或传递函数依赖，且每个依赖的决定因素均为候选键，因此属于BC范式。

实现数据库应用系统

前端页面

本系统的前端页面通过html+css+javascrpt来完成，相关代码在工程文件中的两个文件夹static和templates中。

image-20250731155639413

如下图所示，templates存储页面设计。

image-20250731155650932

static存储动态效果。

image-20250731155658583

最后渲染页面如下：

首页：

image-20250731155708292

注册界面：

image-20250731155717139

登陆界面：

image-20250731155724583

管理员界面：

登陆成功：

image-20250731155733881

登陆失败：

image-20250731155741028

商家列表：

image-20250731155752577

商家界面：

登陆成功：

image-20250731155801686

登陆失败：

image-20250731155810005

首页：

image-20250731155817115

菜单列表：

image-20250731155825019

新增菜品：

image-20250731155832786

修改菜品信息：

image-20250731155841499

个人中心：

image-20250731155849910

修改个人信息：

image-20250731155857191

修改密码：

image-20250731155905302

买家用户：

登陆成功：

image-20250731155914359

登陆失败：

image-20250731155920926

商家列表：

image-20250731155930632

商家菜单：

image-20250731155939656

购物车：

image-20250731160041293

结账：

image-20250731160049270

个人中心：

image-20250731160058901

修改个人信息：

image-20250731160105822

修改密码：

image-20250731160113605

下拉功能：

image-20250731160123333

此外，因为工作量的问题，订单、评价等页面并未完善，在此不予展示。

后端代码

在app.py中，我通过PyMySQL完成与MySQL Server的连接，并借助flask进行Web的搭建，实现前端界面与数据库的交互。因为代码很多，在此就不一一展示，以注册为例，具体实现过程如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
@app.route('/register', methods=['GET', 'POST'])
def registerPage():
    global username
    global userRole
    msg = ""
    if request.method == 'GET':
        return render_template('Register.html')
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        phone = request.form.get('phone')
        addr = request.form.get('addr')
        userRole = request.form.get('userRole')
        print(userRole)
        print(username)
        # 连接数据库，默认数据库用户名root，密码空
        db = pymysql.connect(host="localhost", user="root", password=mysql_pwd, database=db_name,charset='utf8')

        if userRole == 'RESTAURANT':
            cursor = db.cursor()
            try:
                cursor.execute("use appDB")
            except:
                print("Error: unable to use database!")
            sql1 = "SELECT * from RESTAURANT where username = '{}' ".format(username)
            cursor.execute(sql1)
            db.commit()
            res1 = cursor.fetchall()
            num = 0
            for row in res1:
                num = num + 1
            # 如果已经存在该商家
            if num == 1:
                print("失败！商家已注册！")
                msg = "fail1"
            else:
                sql2 = "insert into RESTAURANT (username, password, address, phone) values ('{}', '{}', '{}', '{}') ".format(username, password, addr, phone)

                try:
                    cursor.execute(sql2)
                    db.commit()
                    print("商家注册成功")
                    msg = "done1"
                except ValueError as e:
                    print("--->", e)
                    print("注册出错，失败")
                    msg = "fail1"
            return render_template('Register.html', messages=msg, username=username, userRole=userRole)

        elif userRole == 'CUSTOMER':
            cursor = db.cursor()
            try:
                cursor.execute("use appDB")
            except:
                print("Error: unable to use database!")
            sql1 = "SELECT * from CUSTOMER where username = '{}'".format(username)
            cursor.execute(sql1)
            db.commit()
            res1 = cursor.fetchall()
            num = 0
            for row in res1:
                num = num + 1
            # 如果已存在该用户
            if num == 1:
                print("用户已注册！请直接登录。")
                msg = "fail2"
            else:
                sql2 = "insert into CUSTOMER (username, password, address, phone) values ('{}', '{}', '{}', '{}') ".format(username, password, addr, phone)

                try:
                    cursor.execute(sql2)
                    db.commit()
                    print("商家注册成功")
                    msg = "done2"
                except ValueError as e:
                    print("--->", e)
                    print("注册出错，失败")
                    msg = "fail2"
            return render_template('Register.html', messages=msg, username=username, userRole=userRole)

数据库实现

实现基本数据库

将ER图导出为init.sql，通过执行SQL脚本, 初始化数据库与数据表项。

Init.sql:

image-20250731160241817

执行mysql -u root -p < init.sql，输入密码后，完成基本的数据库创建。因为表比较多，在此只以顾客评论表为例进行展示。

img

img

用户口令哈希存储

哈希保存即用户注册时，把他的密码做一次MD5运算储存起来；用户登录时，把他输入的密码做一次MD5运算，再验证是否和数据库里储存的一致。在MD5被证明不够安全以后，大家又开始选择其他的哈希算法如sha256等，这里我仅做MD5处理。以商家客户为例，下面是未经加密的代码。

1
sql2 = "insert into RESTAURANT (username, password, address, phone) values ('{}', '{}', '{}', '{}') " .format(username, password, addr, phone)

可以看到密码以明文的形式存储在数据库中，而这存在着极大的安全隐患，因此，在密码保存前进行MD5加密。

1
sql2 = "insert into RESTAURANT (username, password, address, phone) values ('{}', '{}', '{}', '{}') " .format(username, md5(password), addr, phone)

这样密码就以MD5哈希值存储在数据库中了，这里可以通过MySQL查看确认。

image-20250731160405307

对应地，登陆的时候，也要将输入的数据进行MD5哈希之后，再与数据库中的值进行对比。

体现数据库完整性检查

数据库操作的完整性检查在实际应用中有非常重要的作用，稍有欠缺，就可能造成严重的后果，因此我增加了数据库操作的限制，比如实体完整性、参照完整性和用户定义的完整性。在此，以评价表中的约束为例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE `ORDER_COMMENT`(
 `orderID` CHAR(15) PRIMARY KEY,
 `username` CHAR(15) NOT NULL,
 `restaurant` VARCHAR(15) NOT NULL,
    `isFinished` BOOLEAN,
    CHECK(isFinished=1 or isFinished =0),
    `cost` DECIMAL(5,2) NOT NULL,
 `c_rank` TINYINT(1),
    CHECK(c_rank BETWEEN 1 AND 5),
    `text` VARCHAR(50),
    `transactiontime` TIMESTAMP(0) NOT NULL,
    CHECK(transactiontime BETWEEN '1970-01-01 00:00:01' AND '2038-01-19 03:14:07'),
    FOREIGN KEY (username)
    REFERENCES CUSTOMER(username),
 FOREIGN KEY (restaurant)
    REFERENCES RESTAURANT(username)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

这里我对订单号进行了实体完整性约束，对用户名、餐厅进行了参照完整行约束，并规定了用户名、餐厅、开销、交易时间不能为空，并且交易时间在1970-01-01 00:00:01-2038-01-19 03:14:07之间。

体现防SQL注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

有输入的地方就可能存在SQL注入，SQL注入产生的原理归根结底还是用户输入的代码被数据库执行了，所以解铃还须系铃人，防御SQL注入还是要在代码层面上去解决。防止SQL注入的方法有很多，比如采取预编译技术、严格控制数据类型、对特殊的字符进行转义等等，在flask框架中为了防止SQL注入，在请求之前可以加一层参数过滤，以POST请求为例，可以通过以下函数实现：

1
2
3
4
5
6
7
8
9
10
@app.before_request
def before_request():
    #假设是post请求，data为传入的请求参数
    data =request.json
    for v in data.values():
        v= str(v).lower()
        pattern = r"\b(and|like|exec|insert|select|drop|grant|alter|delete|update|count|chr|mid|master|truncate|char|delclare|or)\b|(\*|;)"
        r = re.search(pattern,v)
        if r:
            return '请输入规范的参数！'

在对GET请求增加了过滤之后，当输入含敏感字符的参数时，请求将失败：

进行系统并发测试

Flask框架是通过多线程/多进程+阻塞的socket实现非阻塞并发的，其本质是基于python的源库socketserver实现的，即socketserver通过ThreadingMixIn和TCPServer这两个类的组合实现server的多线程非阻塞。比较常用的方式有使用Gunicorn或uWSGI作为WSGI容器，可以将Flask应用部署到多个工作进程上，实现多进程并发处理请求；使用Flask-SocketIO可以将Flask应用扩展为WebSocket应用，可以通过它实现长连接和消息推送功能，从而实现高并发；使用Flask-SQLAlchemy可以将Flask应用与数据库进行集成，可以使用数据库连接池来实现高效的数据库访问，从而提高Flask应用的并发能力；

使用Flask-Cache可以将Flask应用的某些响应结果进行缓存，从而避免重复计算，提升应用的并发能力。以Werkzeug的TCPServer

为例：

1
2
3
4
5
6
7
8
9
10
class ThreadedTCPRequestHandler(socketserver.BaseRequestHandler):
    def handle(self):
        data = self.request.recv(1024)
        cur_thread = threading.current_thread()
        response = f'{cur_thread.name}:{data}'
       
        print(f"{cur_thread.name}收到数据{data}:阻塞")
        time.sleep(15)
        print(f"{cur_thread.name}阻塞结束")
        self.request.sendall(bytes(response, encoding='utf-8'))

具有数据备份与恢复功能

有时用户会希望撤销某次操作，这个时候就需要复原回上一个状态，因此数据需要有备份和恢复的功能，我基于MySQL的mysqldumps指令以及Flask的特点，增加备份与恢复功能，主要代码如下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
def dbDump():
    # 创建备份目录
    if not os.path.isdir(backDir):
        try:
            os.makedirs(backDir)
        except Exception:
            log("创建目录失败")
    # 创建压缩目录
    if not os.path.isdir(zip_path):
        try:
            os.makedirs(zip_path)
        except Exception:
            log("创建目录失败")
    # 获取备份时间
    backDay = time.strftime("%Y%m%d%H%M%S")
    # 获取当前项目路径
    start_path = os.getcwd()
    # 切换到配置文件配置的mysql的bin目录 做这步操作的目的是防止mysql安装服务了但是没有添加到环境变量中，而导致备份失败。
    os.chdir(dump)
    for database in databases:
       # 定义备份的名称
        backName = backDir + database + '.sql'
        b_name = '"' + backName + '"'
        # 定义备份的命令
        sql_command = "%s -u%s -p%s -h%s -P%s %s -B > %s" % (mysql_dump, user, pwd, host, port, database, b_name)
        # 执行备份操作
        os.system(sql_command)
    # 切换到项目的路径  这步必须操作。如果步做的话会导致自己的项目报错，不能运行    
    os.chdir(start_path)
    try:
        # 定义压缩文件的文件名
        zipName = zip_path + 'DB' + '_' + backDay + '.zip'
        # 执行压缩操作并且判断是否压缩成功
        if (zip_dir(backDir, zipName) == 0):
            files = os.listdir(backDir)
            for fi in files:
                print(backDir + fi)
                # 删除临时文件夹的文件 以便下次备份使用
                os.remove(backDir + fi)
            message = 'DB' + "备份压缩成功!"
            log(message)
        else:
            message = 'DB' + "备份压缩失败!"
            log(message)
    except Exception:
        message = 'DB' + "备份异常!!"
        log(message)

如下图所示，删除一件商品。

image-20250731160749413

点击撤销本次删除，进行数据恢复。

image-20250731160758803

补充说明

平台使用时，先以MySQL的root身份执行SQL脚本初始化数据库与数据表项(会提示输入root用户的登录密码)：

1
mysql -u root -p  \< init.sql

再执行Web服务启动程序：

1
python app.py --mysql_pwd 05082966 --db_name appDB

此处mysql_pwd是MySQL的root用户登录密码，db_name是用init.sql创建的数据库名称。

实验心得

完成一项完整的数据库系统的实现是一项非常有挑战性的任务，从设计到创建再到不断地丰富，我遇到了很多的知识盲区，因此也花了许多的时间去查阅资料、尝试思考以及和同学交流学习，在这个过程中，我学会了很多关于数据库系统设计和实现的知识，同时也遇到了很多困难和挑战。下面是我在完成过程中的一些感想：

首先，在开始做大作业之前，一定要认真阅读作业要求和说明，确保自己完全理解要求和任务，以免走弯路或者遗漏重要要求；

其次，在开始设计和实现数据库之前，需要认真评估不同的数据库系统，以确定哪种数据库最适合你的项目需求。我选择了MySQL数据库，因为它是一种成熟的关系型数据库系统，功能强大，社区支持广泛；

再次，要设计良好的数据库结构，在设计数据库结构时，我灵活运用了各种数据模型和设计工具，并考虑到了数据的完整性、一致性和可扩展性等因素。另外，我还考虑了索引、分区、备份等方面的设计，以提高性能和可维护性。

然后，在实现数据库系统时，我根据设计好的数据库结构，使用了Python编程语言和MySQL数据库管理系统进行开发。在开发过程中，我遇到了很多问题，如性能问题、数据完整性问题等。但是，通过查找资料、与同学交流，我最终成功地解决了这些问题。

最后，我撰写了详细的文档和报告，介绍了我的数据库设计和实现过程，包括数据库架构、数据模型、数据存储和查询性能等方面的内容。我还提供了必要的代码、测试用例和演示等。

通过完成这个数据库系统大作业，我学会了很多关于数据库系统设计和实现的知识，同时也提高了自己的编程技能和解决问题的能力。我也意识到，在完成一个大作业时，规划和执行项目、注重细节和质量、积极与同学或老师交流和讨论都是非常重要的。

项目代码

Takeaway_of_Siqi