# Database Theories

NTNU 資料庫理論

##### [Back to Note Overview](https://reurl.cc/XXeYaE)

{%hackmd @sophie8909/pink_theme %}

###### tags: `NTNU` `CSIE` `必修` `Database Theories`

<!-- tag順序 [學校] [系 必選] or [學程 學程名(不含學程的 e.g. 大師創業)] [課程] -->

## Score

- Midterm Exam 25%
- Final Exam 25%
- Homework 20%
- Team Project 25%

## Textbook

### Database System Concepts

#### Ch.27 ~ 32

<https://www.db-book.com/db7/>

## Schedule


## Chapter 1 Introduction

### 1.3 View of Data


#### Data Level

- Physical Level
- Logical Level
- View Level

#### Instances and Schemas

##### Schema

structure

##### Instances

value

### 1.4 Database Languages

#### Data Manipulation Language (DML)

- known as query language

#### Data Definition Language (DDL)

- DDL compiler generates a set of tables stored in a data dictionary
- Data dictionary contains metadata
- Database schema
- Integrity constraints
Primary key (ID uniquely identifies instructors)
- Referential integrity
- e.g. dept_name value in any instructor tuple must appear in department relation

### 1.6 Database Engine

- Storage manager
- the interface between
- the low-level data stored in the database
- the application programs and queries submitted to the system
- Authorization and integrity manager 權限控管
- Transaction Manager 過程控管
- Recovery manager
- ensures consistent (correct) of the database
- state despite failures
- Concurrency-control manager
- controls the interaction among the concurrent transactions
- ensure the consistency of the database.
- File manager 倉庫管理
- Buffer manager 取貨控管

## Chapter 2 Introduction to the Relational Model

### 2.3 Keys

- Let $K \subseteq R$
- **superkey**
- identify of unique tuple of possible relation
- K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R
- **candidate key**: if superkey $K$ is minimal
- 只要沒有多餘的欄位就算，不一定是這個表裡面需要取最小的，所以一個表可以有多個 candidate key
- **primary key**: one of the candidate keys
- 通常是最常用來查表的
- 只有一個
- 別人的 primary key
- **Foreign Key**
- value in one relation must appear in another
- 連接跟另一個表的關係
- Referencing relation
- Referenced relation

### 2.6 The Relational Algebra


## Chapter 3 Introduction to SQL
## Chapter 4 Intermediate SQL
### Join operations
| operations | meaning |
| ---------------- | ------- |
| Outer Join | |
| Left Outer Join | |
| Right Outer Join | |
| Full Outer Join | |

#### on
#### using
### Create Views

### Transactions

### Integrity Constraints
#### DDL 的參數設定
- Not null
- check
- casecade
- 如果飲用的東西被刪掉就跟著被刪
- set nu
### Built-in Data Types in SQL
### Index
### Authorization

## Chapter 5 Advanced SQL
## Chapter 6 Database Design Using the E-R Model

## Chapter 7 Relational Database Design

## Chapter 27 Formal Relational Query Languages

### Relational Algebra

- Procedural language
- Six basic operators
- selection: $\sigma$
- project: $\Pi$
- union: $\cup$
- set difference: –
- Cartesian product: x
- rename: $\rho$
- The operators take one or two relations as inputs and produce a new relation as a result

<!-- class note -->

## Relational Model
### Data Definition Language(DDL)
- Database schema
- Integrity
### Data Manipulation Language (DML)
- also known as query language
### Procedure Query Language (relational algebra)
### Embedded SQL
### Database Design
- Design Process
- Initial
- 了解使用者需求
- Outcome: a specification of user requirements
- Secomd phase(conceptual design)
- 選擇一個資料模型
- 將需求轉換成
- Logical Design - Deciding on the database schema
### Transaction Management
- atomicity
- consistency
- durability
- 一旦交易成功，新的資料必須存在disk
#### Recovery manager
- ensure that the database remains in a consistent(correct) state despite failures
- system faiure

### Two-tier and Three-tier architectures
### History of Database Systems

## Entity-Relationship Model
### Example
attribute
tuple
### Binary relations
### Relations notations

## Relational Algebra
- Procedural Language
<!-- https://hackmd.io/@NTNUCSIE112/DB109-2_RelaAlge -->

### Selection operation
- Notation: $\sigma_p(r)$
- $p$ selection predicate
- Defined as $\sigma_p(r) = \{ t| t \in r \land p(t)\}$
### Project Operation
- Notation: $\Pi$
### Union Operation
### Set different Operation
### Cartesian-Product Operation
Composition
### Natural Join Operation
相同欄位會合併
#### Theta Join Operation
$r\bowtie_\theta s=\sigma_\theta(r\times s)$
相同欄位會保存
### Outer Join Operation
#### Left
#### Right

### Aggregate Function
{%hackmd @NTNUCSIE112/DB109-2_RelaAlge %}

## ER Model
### Design Process
- Characterize the data needs
- Conceptual design
- scheme 要有什麼
- Specify functional requirements
- 需要哪些功能性需求
- Implementation
- Logical-design phase
- Physical-design phase
- Design alternatives
- Redundancy
- Incompleteness
## Attributes
- Attribute types
- Simple and composite
### Composite Attributes

### Strong Entity Set
### Weak Entity Set
- Redundant: 關係的表在一邊都已經記錄到了

### Design Issus
- Use of entity set or attributes
- Entity set
- Attributes
- 如果該資料有獨立需要管理的東西
## Boyce-Codd Normal Form

## Ch.8 Relational Database Design

### Features of Good Relational Design

decompose: split up

Functional dependency

lossy decomposition: cannot reconstruct the original relation 有損

lossless-join decomposition: 無損
### Atomic Domains and First Normal Form
first normal form: the domains of all attributes of R are atomic 沒有多筆資料在一個欄位

- functional dependencies
- multivalued dependencies

### Functional Dependency Theory

- closure: certain other functional dependencies
- denote the closure of F by F+
- $F^+ \supseteq F$
### Decomposition Using Functional Dependencies

### Decomposition Algorithms

### Decomposition Using Multivalued Dependencies

### Database-Design Process