JamesPCSchool: SQL Database Management Part 4

1.Database
a.Normalization
Please refer to Microsoft website below about database normalization
Description of the database normalization basics

b.Denormalization
Example: Book Order

In general, the unit price in the order is linked by the unit price in the book data sheet. However, because the unit price will fluctuate, if we want to check one order in the previous year, the unit price cannot be linked by the unit price in the book table, because that is the unit price of today. Therefore, it is necessary to have moderate denormalization.

The unit price of the transaction on the current day is directly placed in the order details and is not linked with the unit price in the book data sheet. This ensures that the price of each order will not change because the unit price in the book data sheet is adjusted.

範例：書籍訂單
一般而言，訂單裡面的單價是由 書籍資料表裡的單價連動而來。但是因為單價會波動，若今天要查前年的某一張訂單，裡面的單價不可以由 書籍 table裡的單價 連動而來，因為那是今天的單價。所以就必須要適度的反正規化。

將當日的交易單價直接放在訂單細目裡面，不與書籍資料表裡的單價連動，如此可確保每筆訂單的價格，不會因為調整了書籍資料表裡的單價 而改變。

c. Relational database
1.One-to-One
2.One-to-many

If you have a table head or a table body, you need to use One-to-many.

E.g:
1. Head: student name, student number
2. Header: Order Number, Customer Name Body: Order Details

有表頭，有表身的，就需要用到 One-to-many。
例如：
1.表頭：學生姓名、學號表身：各科成績
2.表頭：訂單編號、客戶名稱表身：訂單細目內容

3.Many-to-many
Please refer to my blog
SQL Database Management Part 5

2.Access Database
a. Create baseball DB
b. Create teams, players, and two data sheets.

c. Establish an association with the team number as the key value of the associated field.

a. 建立 baseball DB
b. 建立球隊、球員，二張資料表。
c. 建立關聯，以球隊編號為關聯欄位的鍵值。

Once "Forced Reference Integrity" is checked, symbols for PK (1) and FK (∞) appear.
勾選 "強迫參考完整性" 之後，會有 PK (1) 以及 FK (∞)的符號出現。

d. Create a query
d. 建立查詢

search result
查詢結果

e.Join
1.Inner Join：Intersection (交集)
2.Outer join：Union (聯集)
Left outer join：Display all data on the left + eligible data on the right (顯示左方全部 + 右方符合條件的資料)
Right outer join：vice versa

3.Access Relational Database
a. Sales Database
1. Establish two data sheets for business and performance.
2. Create a query: the total number of the performance of each salesperson
The No. 1 salesperson's total performances is 4

1.建立業務、業績二個資料表。
2.建立查詢：每個業務的業績總數
1號業務之業績共4個

Create a query
建立查詢

Result

PS: After pressing the " SUM Σ" button in the upper right corner of the toolbar, there will be an additional field "SUM" (Steps 4 - 5 of the previous figure). The default value is "Group". Change the "SUM" field for the number of customers to "Total Sum" using the drop-down window.

Description:
Group: Use the value of this field as a group. This means that all "Business 1" will be integrated into one.
Total: The total number of all customers with integrated "Business 1" will be added

PS：按下右上角工具列的 "合計Σ" 按鈕之後，下方會多出一個欄位 "合計" (上一張圖的第4 - 5步驟)，預設值為"群組"。將客戶數量的 "合計" 欄位，用下拉視窗改為 "總計" 。
說明：群組：以該欄位的值，做為群組。意思是所有 "業務1" 都會整合成一筆。
總計：整合好的 "業務1" 之所有客戶數量將做加總

b. Book database (Advance query)
Purpose: Create a query => A customer, the number of months in a certain year, are displayed

目的：建立查詢 => 某一客戶，某一年某一月的數量，都顯示出來

1.Join the field 加入欄位

2. Enter the field: "Year: Year ([date])" --> Select "Quantity" --> By total --> Select "Total"
2.輸入欄位："年:Year([日期])" --> 選"數量" -->按合計 --> 選"總計"

Result

c. Book database (Advance query + left outer join)
Purpose: Create a query => The number of each bookstore in April (including zero), all displayed
In three steps
Step 1. Establish the first query

目的：建立查詢 => 每個書店四月份的數量 (包含零)，都顯示出來
分三個步驟
1.建立第一查詢

Result: There are only four, and there should be a total of eight. Since the other four companies did not trade in April (the number is zero), a second query is required.

結果：只有4家，應該共要有8家。因為其他4家在4月份沒有交易 (數量為零)，所以要建第二查詢。

Step 2. Create a second query: The third step is to right click on the "Associated Line" --> Select "Link Properties"

2.建立第二查詢：第三步是在 "關聯線" 上按右鍵 --> 選 "連結屬性"

Selecting the second will select all customers, regardless of whether there is a transaction. Is the meaning of the left outer join.

Therefore, all customer names are listed on the left (regardless of whether they are traded in April, the total number of identical customers on the right is added, including Null), so you will see that the number of parts on the right is Null.

選第二個，就會選取所有客戶，不論是否有交易。就是 left outer join 的意思。
因此，左邊就會列出所有客戶名稱 (不論在四月是否交易，將右邊相同客戶的數量加總，包含 Null )，所以就會看到右邊有部分的數量是 Null。

Result: Although all customers have it, the quantity cannot be blank, and "0" must be added.

結果：雖然所有客戶都有了，但是數量不能空白，要補上 "0"。

Step 3. Modify the second query
Join the customer and the first query, enter the function below or use the little helper.
Function: IIF ( IsNull ([first query)! [total amount]), 0, [first query]! [total amount])
Note: If the first value is Null, the second value is "0", otherwise it brings in the third value (here the same as the first value).

3.修改第二查詢
加入客戶以及 第一查詢，輸入下面的函數，或是利用小幫手。
函數： IIF( IsNull([第一查詢)![數量之總計]),0,[第一查詢)![數量之總計])
說明：若第一個值為Null，則帶入第二個值 "0"，否則就帶入第三個值(這裡與第一個值相同 )。