数据库设计：使用MySQL和PHP
零售企业管理系统的实施
[私以为，原文比较Nice~]
本项目是利用MySQL和PHP来实现RBMS应用。
1。准备（5分）
我们将使用以下表格：
员工（EID，EnMead，城市）
客户（CID，CNED，城市，VISITSH制造，LASTYVISITITYTIME）
产品（PID，PNED，QOH，QOHOHEAST，原价，DelntTyRead，SID）
供应商（SID，S名字，城市，电话）
采购（PUR，CID，EID，PID，QTY，PDATE，TooToCalm价格）
此外，该项目还需要下表：
日志（LogID、WHO、TIME、Table名称、操作、Kig值）
日志表中的每个元组描述谁（数据库用户的登录名）在什么时间对哪个表（给出表名）和哪个元组（由元组的主键值指示）执行了什么操作（插入、删除、更新）。属性LogID是表的主键。
使用以下SQL DDL语句创建该项目所需的七个表。请注意，您需要使用如下所示的精确语句，以确保教员以后可以使用教员的数据测试您的程序。还请注意，这些表是以一定的顺序创建的，以便在需要创建外键时，相应的主键已经创建。
创建表员工
（eid varchar（3）not null，//将来将所有eid、cid、pid、pur、key_value更改为int类型
Enim-VARCHAR（15）
城市VARCHAR（15）
主键（EID）；
创建表客户
（CID VARCHAR（4）不为空，
CNM VARCHAR（15）
城市VARCHAR（15）
VISITSH制造INT（5），
LasTyVisteTimeTimeDATE时间，
主键（CID）；
创建表格供应商
（sid varchar（2）不为空，
SNEAR VARCHAR（15）不为空，
城市VARCHAR（15）
TelePosieNO Char（10）
主键（SID），
独特（SnNe）；
创建表格产品
（PID VARCHAR（4）不为空，
PNEAR VARCHAR（15）不为空，
Qoint int（5）不为空，
QoHoHealthin int（5），
十进制价格小数（6，2），
//DECIMAL(6,2) 总共能存6位数字,末尾2位是小数
十进制十进制（3，2），
//总共能存3位数字,末尾2位是小数
SID VARCHAR（2）
主键（PID），
外键（SID）参考供应商（SID）；
创建表格购买
（Pur-VARCHAR（4）不为空，
CID VARCHAR（4）不为空，
EID VARCHAR（3）不为空，
PID VARCHAR（4）不为空，
QTY int（5），
时间基准时间，
十进制小数（7，2），
主键（PUR），
外键（CID）参考客户（CID），
外键（EID）参考员工（EID），
外键（PID）参考产品（PID）；
创建表日志
（LogIDint（5）非空自动输入，
谁VARCHAR（10）不为空，
时间日期时间不为空，
Table名称VARCHAR（20）不为空，
操作VARCHAR（6）不为空，
KEY值VARCHAR（4），
主键（LogID）；
创建表
//change all eid, cid, pid, pur, key_value to int types in the future
create table employees
(eid varchar(3) not null,
ename varchar(15),
city varchar(15),
primary key(eid));
create table customers
(cid varchar(4) not null,
cname varchar(15),
city varchar(15),
visits_made int(5),
last_visit_time datetime,
primary key(cid));
create table suppliers
(sid varchar(2) not null,
sname varchar(15) not null,
city varchar(15),
telephone_no char(10),
primary key(sid),
unique(sname));
create table products
(pid varchar(4) not null,
pname varchar(15) not null,
qoh int(5) not null,
qoh_threshold int(5),
original_price decimal(6,2),
discnt_rate decimal(3,2),
sid varchar(2),
primary key(pid),
foreign key (sid) references suppliers (sid));
create table purchases
(pur varchar(4)not null,
cid varchar(4) not null,
eid varchar(3) not null,
pid varchar(4) not null,
qty int(5),
ptime datetime,
total_price decimal(7,2),
primary key (pur),
foreign key (cid) references customers(cid),
foreign key (eid) references employees(eid),
foreign key (pid) references products(pid));
create table logs
(logid int(5) not null auto_increment,
who varchar(10) not null,
time datetime not null,
table_name varchar(20) not null,
operation varchar(6) not null,
key_value varchar(4),
primary key (logid));
大多数表的含义和它们的属性是清楚的。如果他们对你不清楚，请让教练知道。
对于给定的客户，.s_maked指示客户从企业购买产品的次数。
对于给定的客户，last_.time是客户最近一次访问的时间。
产品表中的属性qoh指示手头数量，对于每个产品，qoh_.old是一个整数，使得当qoh变得小于qoh_.old时，是时候从供应商那里获得该产品的新供应品了。
purchases表中的每个元组告诉哪个客户（cid）以什么数量（qty）购买了什么产品（pid）以及何时（ptime）从哪个员工（eid）购买的总价格（._price）。
总价格通过乘以产品的折扣价格和购买的数量来计算。产品的折扣价格是根据其原始价格和折扣率计算的。
您应该用适当的元组填充前五个表来测试您的程序。
2。MySQL实现（50点）
您需要编写SQL查询、存储过程/函数和触发器来实现这个项目。需要执行以下要求和功能。
You need to write SQL queries, stored procedures/functions, and triggers to implement this project. The following requirements and functionalities need to be implemented.
1.（6点）编写一个存储过程来显示每个表中的元组。例如，您可以实现一个过程，比如show_.()，以显示产品表中的所有产品。
1.(6 points) Write a stored procedure to show the tuples in each table.
For example, you can implement a procedure, say show_products(), to display all products in the products table.
2.（4点）写一个程序来报告任何给定产品的月销售信息。例如，对于这个操作，可以使用.monthly.(prod_id)过程。对于给定的产品id，您需要报告产品名称、月份（月份的前三个字母，例如，2月份的FEB）、年份、每月销售总量、每月销售总金额和平均销售价格（总金额除以总数量）。每个月。您只需列出一些客户购买了给定产品的那些月的信息。
2.(4 points) Write a procedure to report the monthly sale information for any given product. For example, you can use a procedure, say report_monthly_sale(prod_id), for this operation. For the given product id, you need to report the product name, the month (the first three letters of the month, e.g., FEB for February), year, the total quantity sold each month, the total dollar amount sold each month, and the average sale price (the total dollar amount divided by the total quantity) of each month. You need to list the information for only those months during which the given product has been purchased by some customers.
3.（7分）编写将元组添加到购买表和产品表的过程。
例如，可以使用add_.(pur_no、c_id、e_id、p_id、pur_qty)过程在purchases表中添加元组，其中pur_no、c_id、e_id、p_id和pur_qty是过程的参数。注意，.price应该根据数据库中的数据自动计算，而ptime应该是当前时间（使用.timestamp）。
3.(7 points) Write procedures to add tuples into the purchases table and the products table. As an example, you can use a procedure, say add_purchase(pur_no, c_id, e_id, p_id, pur_qty), to add a tuple in the purchases table, where pur_no, c_id, e_id, p_id and pur_qty are parameters of the procedure. Note that total_price should be computed based on the data in the database automatically and ptime should be the current time (use current_timestamp).
4、（9点）每当修改任何表时，自动将元组添加到日志表中。为了简化，您只需要考虑以下修改（事件）：
（1）将元组插入purchases表；
（2）更新products表的qoh属性；
（3）更新customers表的.s_maked属性。当由于第一个事件将元组添加到日志表时，table_name应该是“purchases”，操作应该是“insert”，key_value应该是新插入的pur。当由于第二个事件而将元组添加到日志表时，table_name应该是“.”，操作应该是“update”，key_value应该是受影响产品的pid。当由于第三个事件将元组添加到日志表时，table_name应该是“customer”，操作应该是“update”，key_value应该是受影响客户的cid。将元组添加到日志表应该使用触发器实现。您需要为这个任务实现三个触发器，每个事件都需要一个触发器。
4.(9 points) Add a tuple to the logs table automatically whenever any table is modified. To simplify, you are only required to consider the following modifications (events): (1) insert a tuple into the purchases table; (2) update the qoh attribute of the products table; and (3) update the visits_made attribute of the customers table. When a tuple is added to the logs table due to the first event, the table_name should be “purchases”, the operation should be “insert” and the key_value should be the pur of the newly inserted purchase. When a tuple is added to the logs table due to the second event, the table_name should be “products”, the operation should be “update” and the key_value should be the pid of the affected product. When a tuple is added to the logs table due to the third event, the table_name should be “customers”, the operation should be “update” and the key_value should be the cid of the affected customer. Adding tuples to the logs table should be implemented using triggers. You need to implement three triggers for this task, one for each event.
5.(4点)在实际进行购买之前(即，在将元组添加到购买表之前)，您的程序需要确保，对于所涉及的产品，要购买的数量等于或小于手头数量(qoh)。否则，应该显示适当的消息（例如，“库存量不足”），并且应该拒绝购买请求。
- (4 points) Before a purchase is actually made (i.e., before a tuple is added into the purchases table), your program needs to make sure that, for the involved product, the quantity to be purchased is equal to or smaller than the quantity on hand (qoh). Otherwise, an appropriate message should be displayed (e.g., “Insufficient quantity in stock.”) and the purchase request should be rejected.
6.(16点)在购买表中添加元组后，对产品表的qoh列进行相应的修改，即购买所涉及的产品的qoh应按购买数量减少。如果购买导致产品的qoh低于qoh_.，则程序应该执行以下任务：(1)打印一条指示产品当前qoh的消息，(b)通过使其为2*old_qoh来增加qoh，其中old_qoh表示相应purc之前的qoh值进行hase（产品的其他属性值将不改变），并（c）打印另一条消息，指示产品手头的数量已经增加了old_qoh+qty_.，其中qty_selet是相关购买中销售的产品的数量。此外，在purchases表中插入新元组将导致客户的.s_make增加1。使用触发器实现qoh的更新、消息的打印以及.s_maked和last_._time的更新。
- (16 points) After adding a tuple to the purchases table, the qoh column of the products table should be modified accordingly; that is, the qoh of the product involved in the purchase should be reduced by the quantity purchased. If the purchase causes the qoh of the product to be below qoh_threshold, your program should perform the following tasks: (1) print a message indicating the current qoh of the product, (b) increase qoh by making it 2 * old_qoh, where old_qoh represents the value of qoh before the corresponding purchase was made (other attribute values of the product will not be changed), and (c) print another message indicating that the quantity on hand of the product has been increased by old_qoh + qty_sold, where qty_sold is the number of the product sold in the involved purchase. In addition, the insertion of the new tuple in the purchases table will cause the visits_made of the customer to be increased by one. Use triggers to implement the update of qoh, printing of the messages and the update of visits_made and last_visit_time.
7.（4点）你需要通过设计和显示针对所有异常的适当消息来使代码用户友好。例如，如果有人想要查找客户的购买信息，但是输入了一个不存在的客户ID，那么您的程序应该清楚地报告问题。
- (4 points) You need to make your code user friendly by designing and displaying appropriate messages for all exceptions. For example, if someone wants to find the purchases of a customer but entered a non-existent customer id, your program should report the problem clearly.
三。接口（35点）
使用PHP实现Web交互界面。接口程序应该尽可能多地使用MySQL存储过程/函数。
Implement a Web interactive interface using PHP. Your interface program should utilize as many of your MySQL stored procedures/functions as possible.
4。文献（10分）
文件包括以下几个方面：
1.您为项目创建的每个过程和函数以及所有其他对象都需要关于其目标和使用进行清楚的解释。
2、您的代码需要有在线注释的详细记录。
Documentation consists of the following aspects:
- Each procedure and function and every other object you create for your project needs to be explained clearly regarding its objective and usage.
- Your code needs to be well documented with in-line comments.
5。手抄、演示和评分
您还需要将源代码和文档一起提交到黑板。
2.需要使用由讲师创建的元组向讲师演示您的项目。演示前会给出更多的演示演示文稿。
3.评分将基于代码的质量、文档以及演示的成功程度。
- You will also need to submit your source code along with your documentation to the Blackboard.
- It is required to demonstrate your project to the instructor using tuples created by the instructor. More instructions on demo will be given before the demo.
- The grading will be based on the quality of your code, the documentation and on how successful of your demo is.
