實機練習



需求~建立一個資料庫系統,紀錄公司的產品資料,並且記錄客戶的訂單、進貨、出貨、收款。

需要產出以下內容以及回答問題~

(因為答案不是只有一種,資料表結構不同,想法就可能不同)

(1) 這個系統的ERD 

(2) 這個系統的資料表單結構(Schema)

(3) 老闆想知道在某個時間區間內,哪個客戶購買金額最大?最少?

思考 : 從這個需求知道,需要一個客戶資料表(customer),以及紀錄訂購的資料表,因為order是保留字,所以我們用order_main當訂購的表頭檔,用order_body當訂購的表身檔。

為何紀錄訂購資料需要訂購的表頭檔跟訂購的表身檔呢?

要知道某個時間區間內,哪個客戶購買金額最大?最少? 需要客戶資料表(customer)、order_main 與 order_body。

某個時間區間內,哪個客戶購買金額最大?最少? --> 從客戶資料表+訂購的表頭檔+訂購的表身檔,找到加總訂購金額最大(最少)的客戶名稱,條件是訂購資料在特定時間區間內。

(4) 老闆想知道目前總共還有多少應收款? 應收款最多的是哪個客戶?

思考 : 從這個需求知道,訂購的價錢需要紀錄已付或是未付,這個可以用一個欄位來表示,你可以有一個paid欄位,放在訂購的表頭檔或是訂購的表身檔。當然這個paid欄位最好再紀錄付款日期paid_date。或是省下paid欄位,只要有paid_date資料,表示已付。

已付或是未付放在訂購的表頭檔或是訂購的表身檔,差異在哪裡呢?

目前總共還有多少應收款?  只要把時間區間內未付的加總就好啦。應收款最多的是哪個客戶? 也是需要客戶資料表(customer)、order_main 與 order_body。

老闆想知道目前總共還有多少應收款? --> 從訂購的表頭檔+訂購的表身檔,找到未付款的加總。
應收款最多的是哪個客戶? --> 從客戶資料表+訂購的表頭檔+訂購的表身檔,找到未付款的加總最大的客戶名稱。

(5) 老闆想知道目前還有那些未出貨的產品? 那些未出貨的產品是缺貨的? 那些產品庫存量是低於安全庫存量的?

思考 : 因為出貨一定是根據訂單的資料,所以紀錄是否已經出貨,可以使用一個shipped欄位來記錄。如果你要完整記錄出貨,也可以另外用一個出貨表單來記錄。跟訂購一樣,會有出貨表頭檔shipin_main與出貨的表身檔shipin_body,並且訂購跟出貨要關聯,才知道哪個訂單項目已經出貨。

使用一個欄位來記錄出貨與否,跟使用額外的出貨表單來記錄,差異在哪裡呢?

是否缺貨、產品庫存量、安全庫存量這些資料,實務上通常會把安全庫存量放在產品表單中,但是是否缺貨、產品庫存量會額外拉出一個庫存表單(inventory),紀錄每個品項在倉庫中的現況。

如果把結構弄簡單一點,就只需要一個出貨日期(ship_date),而不需要出貨表單與庫存表單,只要有ship_date資料,表示已出貨。也只要把庫存與安全存量放在產品資料表即可。

目前還有那些未出貨的產品? --> 從訂購的表頭檔+訂購的表身檔+產品資料表,找到未出貨的產品。
那些未出貨的產品是缺貨的?  --> 從訂購的表頭檔+訂購的表身檔+產品資料表,找到未出貨的產品,並且存量小於訂購量加總。
那些產品庫存量是低於安全庫存量的? --> 從產品資料表,找到庫存量是低於安全庫存量的產品。

(6) 老闆想知道在某個時間區間內,哪個產品訂購數量最大?最少?哪個產品訂購金額最大?最少?

思考 : 訂購數量當然從訂單表單而來,並且需要完整的產品表單(product)。從這個需求知道,訂單需要紀錄訂購金額與數量。因此在訂購的表身檔,要有order_qty與order_unit_price。

產品價錢應該已經在產品表單,為何訂單中還需要紀錄訂購金額呢?

某個時間區間內,哪個產品訂購數量最大?最少? --> 從產品資料表+訂購的表頭檔+訂購的表身檔,找到加總訂購數量最大(最少)的產品,條件是訂購資料在特定時間區間內。
某個時間區間內,哪個產品訂購金額最大?最少? --> 從產品資料表+訂購的表頭檔+訂購的表身檔,找到加總訂購金額最大(最少)的產品,條件是訂購資料在特定時間區間內。

(7) 老闆想知道在某個時間區間內,哪個類型產品訂購數量最大?最少?哪個類型產品訂購金額最大?最少?

思考 : 產品表單需要分類,分類可以再拉出一個分類表單(category),然後跟產品表單做關聯。

某個時間區間內,哪個類型產品訂購數量最大?最少? --> 從產品資料表+類別資料表+訂購的表頭檔+訂購的表身檔,找到加總訂購數量最大(最少)的產品,條件是訂購資料在特定時間區間內。
某個時間區間內,哪個類型產品訂購金額最大?最少? --> 從產品資料表+類別資料表+訂購的表頭檔+訂購的表身檔,找到加總訂購金額最大(最少)的產品,條件是訂購資料在特定時間區間內。

(8) 老闆想知道在某個時間區間內,公司哪個業務人員的業績最好?最差?

思考 : 表示需要一個職員表單(employee)來記錄訂單與業務人員的關係,訂單要有個欄位來記錄職員編號,表示這個訂單屬於誰的業績。並且因為職員不是都屬於業務部,所以還需要一個部門表單(deptartment)。

某個時間區間內,公司哪個業務人員的業績最好?最差? --> 從職員資料表+訂購的表頭檔+訂購的表身檔,找到加總訂購金額最大(最少)的業務人員,條件是訂購資料在特定時間區間內。

(9) 老闆想知道在某個時間區間內,向哪個供應商購入金額最多?最少?

思考 : 庫存表單中的資料,當然要由供應商而來 (當然如果屬於具有生產的公司,就不一定都由供應商而來),因此還要有供應商表單(supplier),並且要有進貨表單,然後再轉入庫存表單。進貨表單一樣會有表頭(purchase_main)與表身(purchase_body)。當進貨表單中的項目真正進貨時,就轉入庫存表單。

其實客戶向公司訂購(order_main與order_body),公司向供應商進貨(purchase_main與purchase_body),概念是一樣的。

某個時間區間內,向哪個供應商購入金額最多?最少? --> 從供應商資料表+訂購的表頭檔+訂購的表身檔,找到加總購入金額最多(最少)的供應商名稱,條件是訂購資料在特定時間區間內。

(10) 老闆想知道目前總共還有多少應付款? 應付款最多的是哪個供應商?

跟(4)其實很類似。

老闆想知道目前總共還有多少應付款? --> 從進貨的表頭檔+進貨的表身檔,找到未付款的加總。
應付款最多的是哪個供應商? --> 從供應商資料表+進貨的表頭檔+進貨的表身檔,找到未付款的加總最大的供應商名稱。

綜合以上,會有那些表單呢?

客戶資料表 customer *
供應商資料表 supplier *
職員資料表 employee *
部門資料表 department
產品資料表 product *
產品類型資料表 category

出貨訂單表頭 order_main *
出貨訂單表身 order_body *
出貨表頭 shipout_main
出貨表身 shipout_body
(由出貨動作產生應收款項)

庫存資料表 inventory
(出貨時由庫存資料減去數量,進貨時加入數量)

進貨訂單表頭 purchase_main *
進貨訂單表身 purchase_body *
進貨表頭 shipin_main
進貨表身 shipin_body
(由進貨動作產生應付款項)

以上標註*表示是必要的

留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理