数据库课程设计（游戏管理系统的设计与实现）

一、系统需求分析

1.1数据需求

1. 厂商管理：厂商作为游戏发布者，每个厂商可以关联多款游戏。
2. 游戏信息：游戏包含核心属性，如游戏名称、厂商、发售日期、价格、版本、类型、用户评价、打折信息和拓展内容（如音乐包、资源、角色等）。
3. 拓展内容管理：厂商直接管理游戏的附加内容，可更新或修复。
4. 用户系统：用户可以拥有游戏库，可以对游戏进行评价和评分。
5. 社交功能：用户能通过好友系统发现新游戏，查看好友的游戏库。

1.2功能需求

1. 注册模块：

• 用户注册：为用户定制的注册流程，确保用户账户的创建并创建空的游戏库。
• 厂商注册：为游戏发行商定制的注册流程，简化游戏上架过程。

2. 游戏管理：

• 游戏上架：允许厂商将新游戏提交至管理系统，以创建游戏条目。
• 游戏下架：允许厂商将有技术问题的游戏进行下架，同时会删除当前游戏的评分等一系列内容。
• 游戏更新：允许更新游戏信息，包括版本升级和内容更新（例如修复bug、增加新内容）。

3. 评分与统计：

• 游戏评分：计算并显示游戏的平均用户评分。
• 游戏排名：
  • 综合排名：根据综合评分对所有游戏进行排名。
  • 分类排名：按游戏类型进行排名，并提供筛选功能，方便用户查看。

4. 搜索与发现（用户）：

• 游戏搜索：
  • 用户可以通过输入游戏名称来进行搜索，并查看详细信息。
  • 用户可以通过游戏主页入库游戏，并在库中给游戏进行评价。
• 排行榜：展示总排行榜和分类排行榜，助力用户发现热门及高评分的游戏。

6. 购买与退款：

• 购买游戏：用户可以通过在游戏主页购买游戏，将游戏添加到自己的游戏库。（由于没有购买服务器以及备案，无法真正实现消费功能，所以点击购买即视为成功购买入库）
• 退款：用户可以在自己的游戏库中选定游戏进行退款申请。申请通过后将从游戏库中移除该游戏的数据。

7. 社交互动：

• 好友系统：用户能够通过搜索好友ID来发送好友请求，从而拓展其社交网络，也可以通过好友的游戏库查看好友对游戏的评价，寻找新的游戏入库。

二、概念结构设计

2.1数据库需求分析

• 厂商：厂商id，密码，注册日期。
• 用户：用户id，密码，注册日期。
• 游戏：游戏id，类型，玩法，发售日期，配置要求。
• 厂商发布游戏列表：厂商id，游戏id，发售日期，是否新增内容。
• 用户关注厂商列表：用户id，厂商id。
• 用户游戏列表：用户id，游戏id，购买日期，游戏时长。
• 用户订单表：订单id，用户id，游戏id，操作日期，具体操作（0退款/1购买）。
• 游戏评价列表：游戏id，用户id，评价日期，评分，评论。
• 好友：用户id，好友（用户）id。

2.2E-R图

三、逻辑结构数据

3.1E-R图转换关系模式

• 游戏厂商：（厂商编号，简介，账号，密码）
• 游戏：（游戏编号，厂商编号，游戏名称，游戏简介，售价，发布日期）
• 游戏类型：（游戏编号，类型名称）
• 类型表：（类型名称，类型好感度）
• 厂商游戏库：（厂商编号，游戏编号）
• 用户：（用户编号，账号，密码）
• 用户游戏库：(用户编号，游戏编号，购买日期，游戏时长)
• 好友列表：（用户编号，好友用户编号）
• 用户关注厂商表()：（用户编号，厂商编号）
• 订单表：(用户编号，游戏编号，购买状态，订单时间）
• 评价表：（用户编号，游戏编号，评价，评分，发布日期）

3.2数据表设计

3.2.1游戏厂商表

字段名称	数据类型	长度	约束	备注
MANUFACTURER_ID	char	10	主键	厂商编号
MANUFACTURER_INTRODUCTION	char	100	非空	简介
MANUFACTURER_ACCOUNT_NUMBER	varchar	15	非空	账号
MANUFACTURER_PASSWORD	varchar	15	非空	密码

CREATE TABLE MANUFACTURER(
    ID CHAR(10) PRIMARY KEY COMMENT'厂商编号',
    INTRODUCTION CHAR(100) COMMENT'简介',
    ACCOUNT_NUMBER VARCHAR(15)UNIQUE NOT NULL DEFAULT(CONCAT('MANUFACTURER',ID)) COMMENT'账号',
    PASSWORD VARCHAR(15) NOT NULL DEFAULT('123456') COMMENT'密码',
    CONSTRAINT check_id_start_with_m CHECK (ID REGEXP '^M')
);

3.2.2游戏表

字段名称	数据类型	长度	约束	备注
GAME_ID	char	10	主键	游戏编号
MANUFACTURER_ID	char	10	外键	厂商编号
GAME_NAME	char	10	外键	游戏名称
GAME_INTRODUCTION	varchar	100	非空	游戏简介
PRICE	real	4	非空	售价
PUBLISH_TIME	date	8	非空	发布日期

CREATE TABLE GAME(
    ID CHAR(10) PRIMARY KEY COMMENT'游戏编号',
    GAME_NAME CHAR(10) NOT NULL COMMENT'游戏名',
    MANUFACTURER_ID CHAR(10) COMMENT'厂商编号',
    INTRODUCTION VARCHAR(100) COMMENT'游戏简介',
    PRICE REAL NOT NULL DEFAULT(1) COMMENT'售价',
    PUBLISH_TIME DATE NOT NULL DEFAULT('1979-10-01') COMMENT'发布日期',
    FOREIGN KEY(MANUFACTURER_ID) REFERENCES MANUFACTURER(ID) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT check_id_start_with_g CHECK (ID REGEXP '^G')
);

3.2.3游戏类型表

字段名称	数据类型	长度	约束	备注
GAME_ID	char	10	主键/外键	游戏编号
TYPE_NAME	char	10	主键/外键	类型名称

CREATE TABLE GAME_TO_TYPE(
    GAME_ID CHAR(10) COMMENT'游戏编号',
    TYPE_NAME CHAR(10) COMMENT'类型名',
    FOREIGN KEY(GAME_ID) REFERENCES GAME(ID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(TYPE_NAME) REFERENCES GAME_TYPE(TYPE_NAME) ON DELETE CASCADE ON UPDATE CASCADE
);

3.2.4类型表

字段名称	数据类型	长度	约束	备注
TYPE_NAME	char	10	主键/外键	类型名称
TYPE_SUPPORT	int	10	非空	类型好感度

CREATE TABLE GAME_TYPE(
    TYPE_NAME CHAR(10) COMMENT'类型',
    TYPE_SUPPORT INT COMMENT'类型的欢迎度',
    PRIMARY KEY(TYPE_NAME)
);

3.2.5厂商发布游戏表

字段名称	数据类型	长度	约束	备注
MANUFACTURER_ID	char	10	主键	厂商编号
GAME_ID	char	10	外键	游戏编号

CREATE TABLE PUBLISH_GAME(
    MANUFACTURER_ID CHAR(10) COMMENT"厂商的编号",
    GAME_ID CHAR(10) COMMENT'游戏的编号',
    PRIMARY KEY(MANUFACTURER_ID,GAME_ID),
    FOREIGN KEY(MANUFACTURER_ID) REFERENCES MANUFACTURER(ID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(GAME_ID) REFERENCES GAME(ID) ON DELETE CASCADE ON UPDATE CASCADE
)COMMENT'发布的游戏表';

3.2.6用户表

字段名称	数据类型	长度	约束	备注
USER_ID	char	10	主键	用户编号
USER_ACCOUNT_NUMBER	varchar	15	非空	账号
USER_PASSWORD	varchar	15	非空	密码

CREATE TABLE USER(
    ID CHAR(10) PRIMARY KEY COMMENT'用户编号',
    ACCOUNT_NUMBER VARCHAR(15)UNIQUE NOT NULL DEFAULT(CONCAT('MANUFACTURER',ID)) COMMENT'账号',
    PASSWORD VARCHAR(15) NOT NULL DEFAULT('123456') COMMENT'密码',
    CONSTRAINT check_id_start_with_u CHECK (ID REGEXP '^U')
)COMMENT'用户表';

3.2.7用户游戏表

字段名称	数据类型	长度	约束	备注
USER_ID	char	10	主键	用户编号
GAME_ID	char	10	主键/外键	游戏编号
BUY_TIME	date	8	非空	购买日期
PLAY_TIME	real	4	非空	游戏时长

CREATE TABLE HAVING_GAMES(
    USER_ID CHAR(10) COMMENT'用户编号',
    GAME_ID CHAR(10) COMMENT'游戏编号',
    BUY_TIME DATE NOT NULL DEFAULT('1979-10-01') COMMENT'购买日期',
    PLAY_TIME REAL NOT NULL DEFAULT(0) COMMENT'游戏时长',
    PRIMARY KEY(USER_ID,GAME_ID),
    FOREIGN KEY(USER_ID) REFERENCES USER(ID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(GAME_ID) REFERENCES GAME(ID) ON DELETE CASCADE ON UPDATE CASCADE
)COMMENT'拥有的游戏表';

3.2.8用户好友表

字段名称	数据类型	长度	约束	备注
USER_ID	char	10	主键	用户编号
FRIEND_ID	char	10	外键	好友编号

CREATE TABLE FRIEND(
    ID CHAR(10) COMMENT'好友的编号',
    USER_ID CHAR(10) COMMENT'用户的编号',
    PRIMARY KEY(ID,USER_ID),
    FOREIGN KEY(USER_ID) REFERENCES USER(ID) ON DELETE CASCADE ON UPDATE CASCADE
)COMMENT'好友表';

3.2.9用户关注厂商表

字段名称	数据类型	长度	约束	备注
USER_ID	char	10	主键	用户编号
MANUFACTURER_ID	char	10	外键	厂商编号

CREATE TABLE CONCERN(
    USER_ID CHAR(10) COMMENT'用户的编号',
    MANUFACTURER_ID CHAR(10) COMMENT'厂商的编号',
    PRIMARY KEY(USER_ID,MANUFACTURER_ID),
    FOREIGN KEY(USER_ID) REFERENCES USER(ID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(MANUFACTURER_ID) REFERENCES MANUFACTURER(ID) ON DELETE CASCADE ON UPDATE CASCADE
)COMMENT'关注表';

3.2.10订单表

字段名称	数据类型	长度	约束	备注
USER_ID	char	10	主键	用户编号
GAME_ID	char	10	主键/外键	游戏编号
STATE	int	1	非空	状态
ORDER_DATE	date	8	非空	订单日期

CREATE TABLE ORDER_FOR_GOODS(
    USER_ID CHAR(10) COMMENT'用户的编号',
    GAME_ID CHAR(10) COMMENT'游戏的编号',
    BUY_OR_REFUND BOOLEAN COMMENT'购买或者退款,0退款,1购买',
    ORDER_TIME DATE NOT NULL DEFAULT('1979-10-01'),
    PRIMARY KEY(USER_ID,GAME_ID),
    FOREIGN KEY(USER_ID) REFERENCES USER(ID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(GAME_ID) REFERENCES GAME(ID) ON DELETE CASCADE ON UPDATE CASCADE
)COMMENT'订单表';

3.2.11评价表

字段名称	数据类型	长度	约束	备注
USER_ID	char	10	主键	用户编号
GAME_ID	char	10	主键/外键	游戏编号
EVALUATE	text	65535	非空	评价
SCORE	real	4	非空	评分
EVALUATE_DATE	date	8	非空	发布日期

CREATE TABLE EVALUATETABLE(
    USER_ID CHAR(10),
    GAME_ID CHAR(10),
    EVALUATE VARCHAR(100) COMMENT'评价',
    EVALUATE_DATE DATE NOT NULL DEFAULT('1979-10-01') COMMENT'发布日期',
    SCORE REAL NOT NULL DEFAULT(5) COMMENT'评分',
    PRIMARY KEY(USER_ID,GAME_ID),
    FOREIGN KEY(USER_ID) REFERENCES USER(ID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(GAME_ID) REFERENCES GAME(ID) ON DELETE CASCADE ON UPDATE CASCADE
)COMMENT'评价表';

3.3数据插入

3.3.1厂商数据添加

INSERT INTO MANUFACTURER(ID,INTRODUCTION,ACCOUNT_NUMBER,PASSWORD) VALUES
    ('M001','','米哈游','001'),
    ('M002','','鹰角','002'),
    ('M003','','通讯','003');

3.3.2用户数据添加

INSERT INTO USER(ID,ACCOUNT_NUMBER,PASSWORD) VALUES
    ('U001','USER001','001'),
    ('U002','USER002','002'),
    ('U003','USER003','003');

3.3.3游戏数据添加

INSERT INTO GAME(ID,GAME_NAME,MANUFACTURER_ID,INTRODUCTION,PRICE) VALUES
    ('G001','原神','M001','',50),
    ('G002','明日方舟','M002','',12.9),
    ('G003','星之破晓','M003','',10);

3.3.5用户游戏表数据添加

INSERT INTO HAVING_GAMES(USER_ID,GAME_ID,BUY_TIME,PLAY_TIME) VALUES
    ('U001','G001','2001-01-01',100),
    ('U002','G002','2002-02-02',2000),
    ('U003','G003','2003-03-03',1000);

3.3.6厂商游戏表数据添加

INSERT INTO PUBLISH_GAME(MANUFACTURER_ID,GAME_ID) VALUES
    ('M001','G001'),
    ('M002','G002'),
    ('M003','G003');

3.3.7用户好友表数据添加

INSERT INTO FRIEND(USER_ID,ID) VALUES
    ('U001','U002'),
    ('U001','U003'),
    ('U002','U001'),
    ('U002','U003'),
    ('U003','U001'),
    ('U003','U002');

3.3.8用户关注厂商表数据添加

INSERT INTO concern(USER_ID,MANUFACTURER_ID) VALUES
    ('U001','M001'),
    ('U002','M002'),
    ('U003','M003');

3.3.9订单表数据添加

INSERT INTO EVALUATETABLE(USER_ID,GAME_ID,EVALUATE,EVALUATE_DATE,SCORE) VALUES
    ('U001','G001','','2001-01-01',5),
    ('U002','G002','','2002-02-02',3),
    ('U003','G003','','2003-03-03',4);

3.3.10类型表添加

INSERT INTO GAME_TYPE(TYPE_NAME,TYPE_SUPPORT) VALUES
    ('RPG',5),
    ('二次元',9999),
    ('肉鸽',5);

3.3.4游戏类型表添加

INSERT INTO GAME_TO_TYPE(GAME_ID,TYPE_NAME) VALUES
    ('G001','RPG'),
    ('G002','肉鸽'),
    ('G002','二次元'),
    ('G002','肉鸽');