# 資料庫系統設計


:::info
Which of the following format is **not** the most common format to release the educational datasets?
- [ ] A. Comma-Separated Value Files
- [x] B. JavaScript Object Notation Files
- [ ] C. Excel Files
- [ ] D. SPSS Files
:::

## Excel
- Excel file → workbook（活頁簿）
- Table in the workbook → worksheet（工作表）
- Protected view
- Click "Enable Editing" to edit the file.
- Change display language
- File > Options > Language
- Formats
- Excel Workbook (*.xlsx)
- CSV UTF-8 (*.csv)
- comma-delimited text file
- XML Data (*.xml)
- PDF (*.pdf)
- Open Document Spreadsheet (*.ods)
- Text (*.txt)
- Tab-delimited text file

:::info
When we save an Excel file to .csv format, when will Excel **not** put the content of a cell in quote?
- [ ] A. The content contains a comma.
- [ ] B. The content contains a tab.
- [ ] C. The content contains multiple lines.
- [x] D. The content contains a period.
:::

---

- Bytes literals are always prefixed with 'b' or 'B'

:::info
Which character does b'\xe8\xa6\x8f' represent if it is encoded in utf-8?
- [ ] A. '課'
- [ ] B. '程'
- [x] C. '規'
- [ ] D. '畫'
:::

:::info
在 Python 開檔或把位元轉成字串時，如果本來的資訊是用大五碼編碼，那要設 encoding 為何才能正確轉換？
- [ ] A. utf-8
- [x] B. cp950
- [ ] C. uft-7
- [ ] D. cp1252
:::


---

## Numeric precision in Excel
- Excel can store numbers from 1.79769313486232E308 to 2.2250738585072E-308
- it can only do so within **15 digits of precision**.

:::info
Which of the following pair of numbers computed in Excel are **not equal**?
- [ ] A. 2/3000+4-3 and 4-3+2/3000
- [x] B. 1/9000+1-1 and 1-1+1/9000
- [ ] C. HiPrecMath("Add",HiPrecMath("Add",HiPrecMath("Div",1,9000),1),-1) and HiPrecMath("Add",HiPrecMath("Add",1,-1),HiPrecMath("Div",1,9000)) , where HiPrecMath is defined in https://superuser.com/questions/1113640/what-decimal-precision-does-excel-support
- [ ] D. (1/9000*9000+9000-9000)/9000 and (9000-9000+1/9000*9000)/9000
:::

:::info
Which of the algebraic laws may **not** be preserved if we use the decimal types (either in Excel VBA or in Python)?
- [ ] A. Multiplicative associative law: (𝑎𝑏)𝑐 = 𝑎(𝑏𝑐)
- [ ] B. Multiplicative commutative law: 𝑎𝑏 = 𝑏𝑎
- [ ] C. 1 is the multiplicative identity: 𝑎∙1 = 𝑎
- [x] D. Every non-zero number has a multiplicative inverse: for each non-zero 𝑎 as decimal type, there is an 𝑏 as decimal type such that 𝑎𝑏 = 1
:::

---


---

## 巨集錄製
- 巨集要存檔成 **.xlsm**

:::info
Which file format could save Visual Basic for Application macros in Excel ?
- [ ] A. *.xlsx file
- [x] B. *.xlsm file
- [ ] C. *.xls file
- [ ] D. *.csv file
:::

- why 1NF?
- get the information from table easily.
- When want to get the information from two tables in SQL, we can use JOIN.
- inner join
- left join
- right join
- full join
- cross join
- use **VLOOKUP** fn to perform LEFT & RIGHT JOIN easily in excel.
- after LEFT & RIGHT JOIN, we can use **filter** to perform INNER JOIN in excel.
- FULL JOIN → needs a UNION → **PivotTable**

:::info
When we want to JOIN data tables in Excel, which of the following could NOT be used in Excel?
- [ ] A. VLOOKUP function
- [ ] B. Filter
- [ ] C. PivotTable
- [x] D. Unmerge cells
:::

- `VLOOKUP(lookup_value, table_array, col_idx_num, [range_lookup])`