数据库课程设计——订餐系统（PowerBuilder+SQL Sever）

一、选题介绍

本系统要求学生对订餐系统进行设计，包括用户组设置（如餐厅管理员、顾客），订单管理（如增删改查）等功能，在此基础上对数据库进行设计，要求：符合数据库设计标准，减少冗余度

二、需求分析

（思维导图与系统功能图略，可根据以下文字自行绘制）

系统主要包含以下功能：

1. 用户管理：通过用户类型（餐厅管理员或顾客）实现不同权限的设置；用户可以登录系统，使用用户名和密码进行身份验证。

2. 餐厅管理：餐厅管理员可以创建与修改餐厅，提供餐厅名称和地址。

3. 菜单管理：餐厅管理员可以为餐厅添加、修改或删除菜单项，包括菜名、价格和描述。

4. 订单管理：用户可以浏览餐厅的菜单，并输入相应菜品信息下订单；餐厅管理员可以查看餐厅的订单。

5. 订单详情：订单详情将记录每个订单中的菜品信息，包括菜单ID和数量。

三、数据库设计

（一）E-R图（略，可根据以下文字自行绘制）

餐厅和用户之间的关系是多对多关系，一个餐厅可以有多个用户，一个用户可以选择多个餐厅。

用户和订单之间的关系为一对多关系，一个用户可以创建多个订单，而每个订单只属于一个用户。

订单和订单详情之间的关系是一对多关系，一个订单可以有多个订单详情，每个订单详情只属于一个订单。

菜单与订单详情之间的关系是多对多关系，一个订单详情可以对应一个或多个菜单项，而一个菜单项可以出现在多个订单详情中：一个订单可能包含多个菜品，每个菜品对应一个订单详情，而一个菜品可以在多个订单中出现。

餐厅与菜单之间的关系是一对多关系，一个餐厅可以有多个菜单，但每个菜单只属于一个餐厅。

（二）关系模型：

用户（用户ID，用户名，密码，用户类型）

餐厅（餐厅ID，餐厅名称，餐厅地址，餐厅管理员ID）

菜单(菜单ID，餐厅ID，菜名，价格，描述)

订单(订单ID，用户ID，订单日期，总价，状态)

订单详情（订单详情ID，订单ID，菜单ID，数量）

（三）表设计：

设计了五张表，分别是用户表，餐厅表，菜单表，订单表与订单详情表。

用户表（UUser）中包含了用户ID（UserID），用户名（Username），密码（Password），用户类型（UserType）：餐厅管理员或顾客，其中用户ID为主键。

用户表（UUser）

列名数据类型是否允许为空描述 UserID int false 用户ID Username varchar(50) true 用户名 Password varchar(50) true 密码 UserType varchar(20) true 用户类型

餐厅表（Restaurant）中包含了餐厅ID（RestaurantID），餐厅名称（RestaurantName），餐厅地址（RestaurantAddress），餐厅管理员ID（AdminID），其中餐厅管理员ID为外键，关联用户表中的用户ID。

餐厅表（Restaurant）

列名数据类型是否允许为空描述 RestaurantID int false 餐厅ID Restaurantname varchar(50) true 餐厅名称 RestaurantAddress varchar(100) true 餐厅地址 AdminID int true 餐厅管理员ID

菜单表（Menu）中包含了菜单ID（MenuID），餐厅ID（RestaurantID），菜名（DishName），价格（Price），描述（Description），其中菜单ID为主键，餐厅ID为外键，关联餐厅表中的ID。

菜单表（Menu）

列名数据类型是否允许为空描述 MenuID int false 菜单ID RestaurantID int true 餐厅ID DishName varchar(50) true 菜名 Price decimal(8,2) true 价格 Description varchar(200) true 描述

订单表（OOrder）中包含了订单ID（OrderID），用户ID（UserID），订单日期（OrderDate），总价（TotalPrice），地址（Address），其中订单ID为主键，用户ID为外键，关联用户表中的用户ID。

订单表（OOrder）

列名数据类型是否允许为空描述 OrderID int false 订单ID UserID int true 用户ID OrderDate date true 日期 TotalPrice decimal(10,2) true 总价 Address varchar(20) true 地址

订单详情表（OrderDetail）中包含了订单详情ID（DetailID），订单ID（OrderID），菜单ID（MenuID），数量（Quantity），其中订单详情ID为主键，订单ID为外键，关联订单表中的订单ID，菜单ID为外键，关联菜单表中的菜单ID。

订单详情表（OrderDetail）

列名数据类型是否允许为空描述 DetailID int false 订单详情ID OrderID int true 订单ID MenuID int true 菜单ID Quantity int true 数量

（四）视图设计：

设计一个名为RestaurantMenuOrderView的视图，将餐厅表、菜单表连接在一起，提供餐厅名称、菜单号、菜名、价格的信息。使用这个视图可以方便地查看餐厅的菜单信息。

CREATE VIEW RestaurantMenuOrderView AS

SELECT R.RestaurantName, M.MenuID, M.DishName, M.Price

FROM Restaurant R

JOIN Menu M ON R.RestaurantID = M.RestaurantID;

RestaurantMenuOrderView视图

还设计了一个名为OrderDetailView的视图，用来显示订单详情与订单的结合，该视图为顾客设计，提供了直观的订单详情。

CREATE VIEW OrderDetailView AS

SELECT

OD.detailid,OD.menuid,OD.quantity,O.orderid,O.userid,O.orderdate,O.address

FROM

OrderDetail OD

JOIN

OOrder O ON OD.orderid = O.orderid;

OrderDetailView视图

（五）存储过程设计：

设计一个名为GetMenuByRestaurantName的带参数的存储过程，当输入餐厅名称时返回对应的菜单信息。

CREATE PROCEDURE GetMenuByRestaurantName

@Restaurantname VARCHAR(100)

BEGIN

SELECT M.MenuID, M.DishName, M.Price, M.Description

FROM Menu AS M

INNER JOIN Restaurant AS R ON M.RestaurantID=R.RestaurantID

WHERE R.Restaurantname = @Restaurantname;

END;

GetMenuByRestaurantName存储过程调用

（六）触发器设计：

设计一个名为UpdateTotalPriceOnOrderDetailInsert的触发器，根据订单详情表中的新增记录来更新订单表中的总价。通过内连接将订单表与新增的订单详情表关联起来，然后使用子查询计算订单详情的总价。子查询中使用 SUM函数计算每个菜单的总价。最后，将计算得出的新总价更新到订单表中。

CREATE TRIGGER UpdateTotalPriceOnOrderDetailInsert

ON OrderDetail

AFTER INSERT,DELETE

BEGIN

-- Update the TotalPrice in the Order table

UPDATE OOrder

SET TotalPrice = (SELECT SUM(D.Price * OD.Quantity)

FROM OrderDetail OD

INNER JOIN Menu D ON OD.MenuID = D.MenuID

WHERE OD.OrderID = OOrder.OrderID)

FROM OOrder

INNER JOIN inserted ON OOrder.OrderID = inserted.OrderID;

END;

执行以上语句，在订单详情表中对OrderID为1的订单增加了新菜品，再次查询订单表可以发现总价由原来的27.07更新为59.27。

增加语句前

增加语句后

还设计了一个触发器名为ADD_ORDER_DATE，实现增加订单时同步更新当前系统日期。

CREATE TRIGGER ADD_ORDER_DATE

ON OOrder

AFTER INSERT

BEGIN

UPDATE OOrder

SET OrderDate=GETDATE()

WHERE OrderDate is NULL;

END;

执行insert语句

系统日期添加成功

（七）用户设计：

用户类型分为以下两种：

管理员用户，对餐厅、菜单信息进行增删改查，对餐厅订单与订单详情进行改查，但没有删除权限与增加权限，保证订单的真实性，不会出现商家刷单或恶意删除订单；普通用户，对餐厅，菜单，订单信息进行查找，在餐厅与菜单表中没有增删改权限，但在订单表中可增加订单，无法修改或删除，也可在订单详情表中增加订单详情，但无法修改与删除，这意味着普通顾客想要修改订单信息需要联系餐厅管理员进行修改。

使用ODBC接口时仅使用数据库默认sa账号进行数据库连接，若想实现不同用户使用不同的数据库账号，可以设置两个ODBC数据源，分别使用餐厅管理员用户和普通顾客用户进行数据库连接，分为餐厅管理员客户端与普通顾客客户端。

由于制作两个程序工作量较大，且设计了用户表，用户表中包含了用户id，用户名，密码与用户类型，我们打算通过登录界面来识别用户类型，用户类型决定用户进入管理员窗口或顾客窗口，以达到不同用户不同权限的功能，因此只在此写出数据库用户授权的代码，本次课程设计中没有用到。

--创建餐厅管理员用户

CREATE LOGIN Restaurant_admin WITH PASSWORD='88888888';

CREATE USER Restaurant_admin FOR LOGIN Restaurant_admin;

--授予all权限，收回订单与订单详情表中的删除权限与增加权限

GRANT all privileges ON Menu TO restaurant_admin;

GRANT all privileges ON OOrder TO restaurant_admin;

REVOKE DELETE ON OOrder FROM restaurant_admin;

REVOKE INSERT ON OrderDetail FROM restaurant_admin;

GRANT all privileges ON OrderDetail TO restaurant_admin;

REVOKE DELETE ON OrderDetail FROM restaurant_admin;

REVOKE INSERT ON OOrder FROM restaurant_admin;

GRANT all privileges ON Restaurant TO restaurant_admin;

普通顾客数据库用户创建于授权代码如下：

--创建普通顾客用户

CREATE LOGIN Restaurant_customer WITH PASSWORD='11111111';

CREATE USER Restaurant_customer FOR LOGIN Restaurant_customer;

--授予查看菜单表、餐厅表，订单表与订单详情，增加订单与订单详情权限

GRANT SELECT ON Menu TO restaurant_customer;

GRANT SELECT ON OOrder TO restaurant_customer;

GRANT INSERT ON OOrder TO restaurant_customer;

GRANT SELECT ON OrderDetail TO restaurant_customer;

GRANT INSERT ON OOrder TO restaurant_customer;

GRANT SELECT ON Restaurant TO restaurant_customer;

四、数据库实现

--创建数据库

CREATE DATABASE RO；

--创建用户表

CREATE TABLE UUser (

UserID INT PRIMARY KEY,

Username VARCHAR(50),

Password VARCHAR(50),

UserType VARCHAR(20)

);

--创建餐厅表

CREATE TABLE Restaurant (

RestaurantID INT PRIMARY KEY,

RestaurantName VARCHAR(50),

RestaurantAddress VARCHAR(100),

AdminID INT,

FOREIGN KEY (AdminID) REFERENCES UUser(UserID)

);

--创建菜单表

CREATE TABLE Menu (

MenuID INT PRIMARY KEY,

RestaurantID INT,

DishName VARCHAR(50),

Price DECIMAL(8, 2),

Description VARCHAR(200),

FOREIGN KEY (RestaurantID) REFERENCES Restaurant(RestaurantID)

);

--创建订单表

CREATE TABLE OOrder (

OrderID INT PRIMARY KEY,

UserID INT,

OrderDate DATE,

TotalPrice DECIMAL(10, 2),

Address VARCHAR(20),

FOREIGN KEY (UserID) REFERENCES UUser(UserID)

);

--创建订单详情表

CREATE TABLE OrderDetail (

DetailID INT PRIMARY KEY,

OrderID INT,

MenuID INT,

Quantity INT,

FOREIGN KEY (OrderID) REFERENCES OOrder(OrderID),

FOREIGN KEY (MenuID) REFERENCES Menu(MenuID)

);

--插入用户表数据

INSERT INTO UUser (UserID, Username, Password, UserType)

VALUES (1, 'admin', 'admin123', '餐厅管理员');

INSERT INTO UUser (UserID, Username, Password, UserType)

VALUES (2, 'john_doe', 'password123', '顾客');

INSERT INTO UUser (UserID, Username, Password, UserType)

VALUES (3, 'jane_smith', '123456', '顾客');

INSERT INTO UUser (UserID, Username, Password, UserType)

VALUES (4, 'michelle_ang', '667798', '顾客');

INSERT INTO UUser (UserID, Username, Password, UserType)

VALUES (5, 'adam_louis', 'adam145534', '顾客');

--插入餐厅表数据

INSERT INTO Restaurant (RestaurantID, RestaurantName, RestaurantAddress, AdminID)

VALUES (1, '餐厅A', '地址A', 1);

INSERT INTO Restaurant (RestaurantID, RestaurantName, RestaurantAddress, AdminID)

VALUES (2, '餐厅B', '地址B', 1);

INSERT INTO Restaurant (RestaurantID, RestaurantName, RestaurantAddress, AdminID)

VALUES (3, '餐厅C', '地址C', 1);

INSERT INTO Restaurant (RestaurantID, RestaurantName, RestaurantAddress, AdminID)

VALUES (4, '餐厅D', '地址D', 1);

INSERT INTO Restaurant (RestaurantID, RestaurantName, RestaurantAddress, AdminID)

VALUES (5, '餐厅E', '地址E', 1);

INSERT INTO Restaurant (RestaurantID, RestaurantName, RestaurantAddress, AdminID)

VALUES (6, '餐厅F', '地址F', 1);

--插入菜单表数据