數(shù)據(jù)庫設(shè)計：使用MySQL和PHP
零售企業(yè)管理系統(tǒng)的實施
[私以為票唆，原文比較Nice~]
本項目是利用MySQL和PHP來實現(xiàn)RBMS應(yīng)用。
1屹徘。準備（5分）
我們將使用以下表格：
員工（EID走趋，EnMead，城市）
客戶（CID噪伊，CNED簿煌，城市氮唯，VISITSH制造，LASTYVISITITYTIME）
產(chǎn)品（PID姨伟，PNED惩琉，QOH，QOHOHEAST夺荒，原價瞒渠，DelntTyRead，SID）
供應(yīng)商（SID技扼，S名字伍玖，城市，電話）
采購（PUR剿吻，CID窍箍，EID，PID和橙，QTY仔燕，PDATE，TooToCalm價格）
此外魔招，該項目還需要下表：
日志（LogID、WHO五辽、TIME办斑、Table名稱、操作杆逗、Kig值）
日志表中的每個元組描述誰（數(shù)據(jù)庫用戶的登錄名）在什么時間對哪個表（給出表名）和哪個元組（由元組的主鍵值指示）執(zhí)行了什么操作（插入乡翅、刪除、更新）罪郊。屬性LogID是表的主鍵蠕蚜。
使用以下SQL DDL語句創(chuàng)建該項目所需的七個表。請注意悔橄，您需要使用如下所示的精確語句靶累，以確保教員以后可以使用教員的數(shù)據(jù)測試您的程序。還請注意癣疟，這些表是以一定的順序創(chuàng)建的挣柬，以便在需要創(chuàng)建外鍵時，相應(yīng)的主鍵已經(jīng)創(chuàng)建睛挚。
創(chuàng)建表員工
（eid varchar（3）not null邪蛔，//將來將所有eid、cid扎狱、pid侧到、pur勃教、key_value更改為int類型
Enim-VARCHAR（15）
城市VARCHAR（15）
主鍵（EID）；
創(chuàng)建表客戶
（CID VARCHAR（4）不為空匠抗，
CNM VARCHAR（15）
城市VARCHAR（15）
VISITSH制造INT（5）荣回，
LasTyVisteTimeTimeDATE時間，
主鍵（CID）戈咳；
創(chuàng)建表格供應(yīng)商
（sid varchar（2）不為空心软，
SNEAR VARCHAR（15）不為空，
城市VARCHAR（15）
TelePosieNO Char（10）
主鍵（SID）著蛙，
獨特（SnNe）删铃；
創(chuàng)建表格產(chǎn)品
（PID VARCHAR（4）不為空，
PNEAR VARCHAR（15）不為空踏堡，
Qoint int（5）不為空猎唁，
QoHoHealthin int（5），
十進制價格小數(shù)（6顷蟆，2）诫隅，
//DECIMAL(6,2) 總共能存6位數(shù)字,末尾2位是小數(shù)
十進制十進制（3，2）帐偎，
//總共能存3位數(shù)字,末尾2位是小數(shù)
SID VARCHAR（2）
主鍵（PID）逐纬，
外鍵（SID）參考供應(yīng)商（SID）；
創(chuàng)建表格購買
（Pur-VARCHAR（4）不為空削樊，
CID VARCHAR（4）不為空豁生，
EID VARCHAR（3）不為空，
PID VARCHAR（4）不為空漫贞，
QTY int（5）甸箱，
時間基準時間，
十進制小數(shù)（7迅脐，2）芍殖，
主鍵（PUR），
外鍵（CID）參考客戶（CID）谴蔑，
外鍵（EID）參考員工（EID）豌骏，
外鍵（PID）參考產(chǎn)品（PID）；
創(chuàng)建表日志
（LogIDint（5）非空自動輸入树碱，
誰VARCHAR（10）不為空肯适，
時間日期時間不為空，
Table名稱VARCHAR（20）不為空成榜，
操作VARCHAR（6）不為空框舔，
KEY值VARCHAR（4），
主鍵（LogID）；
創(chuàng)建表
//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));
大多數(shù)表的含義和它們的屬性是清楚的刘绣。如果他們對你不清楚樱溉，請讓教練知道。
對于給定的客戶纬凤，.s_maked指示客戶從企業(yè)購買產(chǎn)品的次數(shù)福贞。
對于給定的客戶，last_.time是客戶最近一次訪問的時間停士。
產(chǎn)品表中的屬性qoh指示手頭數(shù)量挖帘，對于每個產(chǎn)品，qoh_.old是一個整數(shù)恋技，使得當(dāng)qoh變得小于qoh_.old時拇舀，是時候從供應(yīng)商那里獲得該產(chǎn)品的新供應(yīng)品了。
purchases表中的每個元組告訴哪個客戶（cid）以什么數(shù)量（qty）購買了什么產(chǎn)品（pid）以及何時（ptime）從哪個員工（eid）購買的總價格（._price）蜻底。
總價格通過乘以產(chǎn)品的折扣價格和購買的數(shù)量來計算骄崩。產(chǎn)品的折扣價格是根據(jù)其原始價格和折扣率計算的。
您應(yīng)該用適當(dāng)?shù)脑M填充前五個表來測試您的程序薄辅。
2要拂。MySQL實現(xiàn)（50點）
您需要編寫SQL查詢、存儲過程/函數(shù)和觸發(fā)器來實現(xiàn)這個項目站楚。需要執(zhí)行以下要求和功能脱惰。
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點）編寫一個存儲過程來顯示每個表中的元組。例如源请，您可以實現(xiàn)一個過程枪芒，比如show_.()，以顯示產(chǎn)品表中的所有產(chǎn)品谁尸。
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點）寫一個程序來報告任何給定產(chǎn)品的月銷售信息。例如纽甘，對于這個操作良蛮，可以使用.monthly.(prod_id)過程。對于給定的產(chǎn)品id悍赢，您需要報告產(chǎn)品名稱决瞳、月份（月份的前三個字母，例如左权，2月份的FEB）皮胡、年份、每月銷售總量赏迟、每月銷售總金額和平均銷售價格（總金額除以總數(shù)量）屡贺。每個月。您只需列出一些客戶購買了給定產(chǎn)品的那些月的信息。
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分）編寫將元組添加到購買表和產(chǎn)品表的過程甩栈。
例如泻仙，可以使用add_.(pur_no、c_id量没、e_id玉转、p_id、pur_qty)過程在purchases表中添加元組殴蹄，其中pur_no究抓、c_id、e_id袭灯、p_id和pur_qty是過程的參數(shù)刺下。注意，.price應(yīng)該根據(jù)數(shù)據(jù)庫中的數(shù)據(jù)自動計算妓蛮，而ptime應(yīng)該是當(dāng)前時間（使用.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點）每當(dāng)修改任何表時蛤克，自動將元組添加到日志表中捺癞。為了簡化，您只需要考慮以下修改（事件）：
（1）將元組插入purchases表构挤；
（2）更新products表的qoh屬性髓介；
（3）更新customers表的.s_maked屬性。當(dāng)由于第一個事件將元組添加到日志表時筋现，table_name應(yīng)該是“purchases”唐础，操作應(yīng)該是“insert”，key_value應(yīng)該是新插入的pur矾飞。當(dāng)由于第二個事件而將元組添加到日志表時一膨，table_name應(yīng)該是“.”，操作應(yīng)該是“update”洒沦，key_value應(yīng)該是受影響產(chǎn)品的pid豹绪。當(dāng)由于第三個事件將元組添加到日志表時，table_name應(yīng)該是“customer”申眼，操作應(yīng)該是“update”瞒津，key_value應(yīng)該是受影響客戶的cid。將元組添加到日志表應(yīng)該使用觸發(fā)器實現(xiàn)括尸。您需要為這個任務(wù)實現(xiàn)三個觸發(fā)器巷蚪，每個事件都需要一個觸發(fā)器。
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點)在實際進行購買之前(即濒翻，在將元組添加到購買表之前)屁柏，您的程序需要確保啦膜，對于所涉及的產(chǎn)品，要購買的數(shù)量等于或小于手頭數(shù)量(qoh)前联。否則功戚，應(yīng)該顯示適當(dāng)?shù)南ⅲɡ纾皫齑媪坎蛔恪保┧凄停⑶覒?yīng)該拒絕購買請求啸臀。
- (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點)在購買表中添加元組后，對產(chǎn)品表的qoh列進行相應(yīng)的修改烁落，即購買所涉及的產(chǎn)品的qoh應(yīng)按購買數(shù)量減少乘粒。如果購買導(dǎo)致產(chǎn)品的qoh低于qoh_.，則程序應(yīng)該執(zhí)行以下任務(wù)：(1)打印一條指示產(chǎn)品當(dāng)前qoh的消息伤塌，(b)通過使其為2*old_qoh來增加qoh灯萍，其中old_qoh表示相應(yīng)purc之前的qoh值進行hase（產(chǎn)品的其他屬性值將不改變），并（c）打印另一條消息每聪，指示產(chǎn)品手頭的數(shù)量已經(jīng)增加了old_qoh+qty_.旦棉，其中qty_selet是相關(guān)購買中銷售的產(chǎn)品的數(shù)量。此外药薯，在purchases表中插入新元組將導(dǎo)致客戶的.s_make增加1绑洛。使用觸發(fā)器實現(xiàn)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點）你需要通過設(shè)計和顯示針對所有異常的適當(dāng)消息來使代碼用戶友好真屯。例如，如果有人想要查找客戶的購買信息穷娱，但是輸入了一個不存在的客戶ID绑蔫，那么您的程序應(yīng)該清楚地報告問題。
- (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實現(xiàn)Web交互界面配深。接口程序應(yīng)該盡可能多地使用MySQL存儲過程/函數(shù)。
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.您為項目創(chuàng)建的每個過程和函數(shù)以及所有其他對象都需要關(guān)于其目標(biāo)和使用進行清楚的解釋凉馆。
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.需要使用由講師創(chuàng)建的元組向講師演示您的項目。演示前會給出更多的演示演示文稿母谎。
3.評分將基于代碼的質(zhì)量瘦黑、文檔以及演示的成功程度。
- 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.
