数据库课程设计：利用python+MySQL+pyqt5设计一个带UI界面的书店管理系统

1.项目简述

1.1项目来源

基于MySQL+python+pyqt5设计和实现的书店管理系统
这个项目原本是这学期数据库课程设计，经过老师提醒准备在暑假时间进行完善和修改，待新学期申请一项软件著作权（这个很水的），也算是更进一步的磨练自己。暑期完成了以下内容：
①完成整个UI界面的实现（也是最主要的部分）
②对数据库相关表的定义进行完善和修改
③对相关异常进行了处理

1.2 相关工具

python3.8、MySQL8.0、pycharm2020.1、datagrip2020.1、pymysql、pyqt5。

2.需求分析

2.1 需求信息

书店管理系统需要满足以下需求：
对于一般店员来说：
①书店店员通过系统管理书籍的购入和卖出，以及查看书籍的存货量
②店员可以通过该系统对需要借书和还书的人员进行借书还书操作
③新增和删除新的借阅人员信息。
对于店长（也就是管理员），除了以上的三种需求之外，还有
①增加和删除雇员，
②增加和删除系统操作人员账号，
③修改雇员的信息。

2.2 数据需求

根据2.1中的需求信息，我们可以设计出以下的基本功能：
1.借阅人员信息的输入、查询，包括借阅证ID、姓名、年龄、性别、手机号
2.书籍信息的输入和删除包括ISBN、书籍名称、作者、定价
3.馆藏书籍信息包括ISBN、收藏量
4.书籍的卖出和购入包括书籍的ISBN、卖出量、购入量
5.雇员信息的增删查改，包括雇员姓名、性别、年龄、工资

2.3系统流程设计

根据上述的描述我们可以容易设计出系统流程图，流程图如下

图2.3 系统流程图

3.模型设计

3.1 employee表

3.2 book表

3.3 reader表

3.4 collectionofbook表

3.5 borrow表

3.6 return表

3.7 sell表

3.8 全局ER图

一个读者可以借到多本书，同一本书也可以被多人借去。Book表是指所有已经发行的书籍，而collectionofbook代表书店里面有的书籍，两者1:1关系。同一本书可以被买入或者卖出多本，两者是1：N的关系。

4.规范性检查

1）对于book表 属性有（ISBN, BookName, Author, Price），其中ISBN为主键，无非主属性对候选键的传递依赖且候选键只有一个，符合BCNF

（2）对于employee表，属性有（employeeid, employname, employsex, employage, employtel, salary），其中employeeid为主键，无非主属性对候选键的传递依赖且候选键只有一个，符合BCNF

（3）对于reader表，属性有(readerid, readername, sex, age, tel) 其中readerid为主键，无非主属性对候选键的传递依赖且候选键只有一个，符合BCNF

（4）对于collection表，属性有(isbn, totalnum)，其中ISBN不仅为主键也为外键，参考book表中的ISBN。无非主属性对候选键的传递依赖且候选键只有一个，符合BCNF

（5）对于borrow表，属性有（borrowID，date，ISBN，readerID），borrowid为主键，决定了哪个人借了那本书，ISBN和readerid参考了collectionofbook中的isbn以及reader中的readerid，无非主属性对候选键的传递依赖且候选键只有一个，符合BCNF

（6）对于return表，属性有（returnID，ISBN，readerID，date），returnid为主键，决定了哪个人借了那本书，ISBN和readerid参考了collectionofbook中的isbn，以及reader中的readerid，无非主属性对候选键的传递依赖且候选键只有一个，符合BCNF

（7）对于sell表和purchase表，属性有（sellid，ISBN，alreadysold，price）和（purchaseid，ISBN，num，price），无非主属性对候选键的传递依赖且候选键只有一个，符合BCNF

5.系统实现

5.1 系统实现环境和技术

pycharm，DataGrip，MySQL。利用Python第三方库MySQLlite来实现对MySQL数据库的操作。先在datagrip中创建好数据库和表以及触发器，再在Pycharm中编写相关系统程序。
5.2系统界面与关键代码
以下是关键几个表的创建

create table Reader(
    ReaderID int,
    ReaderName varchar(10) default '张三',
    Sex varchar(1),
    Age int default 18,
    TEL varchar(12) default NULL,
    primary key (ReaderID),
    unique (ReaderID),
    CHECK ( Sex='男' or Sex='女')
);

create table Book(
  ISBN varchar(20),
  BookName varchar(20) not null ,
  Author varchar(20) default ' ',
  Price int ,
    primary key (ISBN),
    unique (ISBN),
    check ( Price>0 )
);

create table CollectionOfBook(
    ISBN varchar(20),
    TotalNum int default 0,
    foreign key (ISBN)
        references Book(ISBN)
);
alter table collectionofbook add constraint check(TotalNum>=0);

CREATE table Employee(
    EmployeeID int primary key ,
    EmployName varchar(20),
    EmploySex varchar(1),
    EmployAge int,
    EmployTEL varchar(20),
    Salary int,
    check ( EmploySex ='男' or EmploySex = '女')
);
//创建触发器
create trigger increaseNumberOfBooks after insert on purchasebook
    for each row
    begin
        if not exists(select 1 from collectionofbook where NEW.ISBN in(select ISBN from collectionofbook)) then
            begin
       insert into CollectionOfBook(ISBN, TotalNum) VALUE (NEW.ISBN,NEW.PurchaseNum);
            end;
        else
            begin
                update CollectionOfBook set TotalNum = TotalNum+NEW.PurchaseNum
        where CollectionOfBook.ISBN = NEW.ISBN;
                end;
        end if;
    end;

create trigger decreaseNumberOfBooks after insert on sell
    for each row
    begin
        update CollectionOfBook set TotalNum = TotalNum-NEW.AlreadySold
        where  CollectionOfBook.ISBN = NEW.ISBN;
    end;

create  trigger borrowBook before insert on borrow
    for each row
    begin
        if (select TotalNum from collectionofbook where CollectionOfBook.ISBN
            = NEW.ISBN
            ) -1 >=0 then
        update collectionofbook set TotalNum = TotalNum-1 where CollectionOfBook.ISBN
        = NEW.ISBN;
        end if;
    end;

create  trigger ReturnBook before insert on returnofbook
    for each row
    begin
        update collectionofbook set TotalNum = TotalNum+1 where CollectionOfBook.ISBN
        = NEW.ISBN;
    end;

python创建类来封装操作
class BasicSqlOperation(object):
    def __init__(self):
        # 打开数据库连接
        self.db = connect(host='localhost', port=3306, charset='utf8', database='MySQL', password='zyh20000205',
                          user='root')
        # 创建游标对象
        self.cursor = self.db.cursor()
        sql = "use bookshopmanagement"
        self.cursor.execute(sql)

6.UI 界面

在登录界面可以选择登录级别

管理员登录

一般操作用户登录

操作窗口

7.GitHub地址

https://github.com/zyhsna/Database_courseDesign

8. 更新

更新于2020/12/14，修改了一些bug，对程序运行可能出现的一些错误进行了说明和修改，具体内容可见GitHub上readme