---
# System prepended metadata

title: 資料庫系統設計

---

# 資料庫系統設計

![](https://i.imgur.com/TqA3QqO.png)

:::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
:::

![](https://i.imgur.com/cAzV7AT.png)
![](https://i.imgur.com/9rFGoNV.png)
![](https://i.imgur.com/SaACnQc.png)
![](https://i.imgur.com/xjYEJVC.png)

---

## 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
:::

---

![](https://i.imgur.com/popb0il.png)
![](https://i.imgur.com/IfEHWI8.png)

---

## 巨集錄製
![](https://i.imgur.com/erhVith.png)
![](https://i.imgur.com/by2jYXv.png)
![](https://i.imgur.com/WFaKi2p.png)
![](https://i.imgur.com/AHu8k8w.png)
![](https://i.imgur.com/yv6B3dk.png)
![](https://i.imgur.com/x1hOkWs.png)
![](https://i.imgur.com/lOCvXUv.png)
![](https://i.imgur.com/7GR1Q5R.png)
- 巨集要存檔成 **.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])`
![](https://i.imgur.com/e6nogbb.png)
![](https://i.imgur.com/6nl9kFK.png)


![](https://i.imgur.com/aJPvuKl.png)
![](https://i.imgur.com/Ra2cROD.png)
![](https://i.imgur.com/wKDSXVt.png)
