W1
====

### Overview

>database里的data type，数据关系图，还有各种database的架构优势。
>
## Fundamental Relational Database Concepts

### 1. Review of Data Fundamentals

* 对data的部分做Review：
* Data type：
* Structured
* Semi-structured
* Unstructed

* Data source
* File format
* Data repositories
* OLTP
* OLAP

### 2. Information and Data Models

* Information Model
* Concept/Abstract Model For Designers And Operators.
* hierarchical model organizes(tree structure)

* Data Model
* Concrete Detailed Model For Implementers
* 多在使用 Relational Model

* Relational Model
* 数据獨立
* 数据存入Tables
* 提供一些特性：
* Logical data independence
* Physical data independence
* Physical storage independence

* Entity-Relationship Model
* Relational databases 的设计工具
* Building block：
* Entities
* Attributes<br><br>
<br><br>
* 以上图片：ER Diagram
* Book是Entities，Title，Edition及其他的是Book的Attributes。

* Example of ER Diagram


### 3. ERDs & Types of Relationship

* One-to-one Relationshaip
* A book written by many authors

* One-to-Many Relationship
* A book written ny many authors

* Many-to-Many Relationship
* Many books written by many authors
* Many authors writing many books
* Crows foot notation uses greater-than and less-than symbols

### 4. Mapping Entities to Tables
* Mapping an ERD to a table
* 把ER上定义Entity和Attribute物件，对应到table的columns上

* 结果如下表：

### 5. Data Types
* Character string
* Fixed length
* Example:
* CHAR(10)

* Variable length
* Example:
* VARCHAR(20)
* LONGCHAR

* Numeric
* Integer
* Example:
* INT
* SMALLINT
* BIGINT
*

* Decimal
* Example:
* DECIMAL
* NUMERIC
* FLOAT
* SINGLE
* DOUBLE

* Date/time
* Date
* Example:
* 2021-12-32

* Time
* Example:
* 02.11.35

* Timestamp
* Example:
* 2021-12-31-02.11.35.00

* Other data types
* Boolean
* Binary string
* Large object
* XML
* User defined types

* Advantages of using data types
* Data integrity
* Data sorting
* Range selection
* Data calculations
* Use of standard function

### 5. Relational Model Concepts
* Building blocks
* Relation
* Mathematical term for table
* Relation Schema
* Specifies relation name and attributes

* Relation Instance
* A table made up of rows and columns
* Column
* Attributes
* Field
* Row
* Tuple
* Degree
* The number of attributes in a relation
* Cardinality
* The number of tuples
* Specifies
* Name of a ralation, name and type of each column

* Relation Schema 和 Relation Instance 的图說:

* Sets
* Unordered collection of distinct element
* Items of same type
* No order and no duplicates

## Introducing Relational Database Products

### 1. Database Architecture
* Deployment Topologies
* Local
* Resides on the user's system
* Single user environment
* Single-tier architecture
* 使用场景：
* Development / test
* Database embedded in a local application

* Client/Server
* Databases resides on a database server
* Users acsess database from client systems
* Sometime there is a middle-tier(Application Server layer)

* Cloud
* Databases resides in a cloud environment
* No need to download or install software
* Can access easily
* Access the database through an application server layer or interface in the cloud

* 2-Tier Database Architecture

* 3-Tier Database Architecture

### 2. Distributed Architecture and Clustered Databases

* Database Architecture
* 使用场景：
* Mission Critical
* Large scale workloads

* 特性：
* High Availability
* High Scalability requirements

* 实践方法：
* Databases distributed on a cluster of servers

* Shared disk architecture
* Share common storage
* 把数据分散到数个Database Server，当某个Database Server出问题还有其他的Database Server作为后备支援。
* 正常运作
* Database Server故障

* Shared nothing architecture
* Replication(复制)
* 使用场景：
* 主要Database Server故障，所以会把访问转移到备份Database Server

* 转移至正常运作Database Server的地方

* Partitioning
* 把不同属性的数据存在不同的Database Server上，分担访问负载

* 当访问过载的时候，可以增设Database Server分担访问负载

### 3. Database Usage Patterns
* The main classes of users are:
* Data engineer
* Data Scientists and Business Analysts
* Application Developers

* Databases can be accesses through:
* Graphical and wrb interfaces
* Command line tools and scripts
* APIs and ORMs

* Major categories of databese applications include:
* Database Management tools
* Data Science and BI tools
* Business and Industry Applications

### 4. Introduction to Relational Database Offerings
* 本章主要讲解Relational Database的发展，以及介绍现有Database。
* Comercial license 与 opensource 的发展趋势。

### 5. Db2
* Features：
* AI- powered functionality：
* Machine Learning algorithms
* Column store
* Data skipping

* Common SQL Engine
* Support for all data types
* High availability and disaster recovery
* Scalability
* Table partitioning

* DB2分支：
* Db2 database
* Db2 warehouse
* Db2 on Cloud
* Db2 Big SQL

### 5. MySQL
* Object-relational database
* Low maintenance database
* Clusterd database
* Supports many operating systems
* Unix
* Linux
* Windows
* Supports a range og languages for client application development
* Python
* Java
* JavaScript
* C++
* c
* SQL
* Support relational and JSON data
* Provides mutiple storage engines for differing workloads
* InnoDB
* Transactions
* Row-level locking
* Clustered indexes
* Foraign keys

* MyISAM
* For data warehouse and web applications
* Table-level locking

* NDB
* Cluster
* High availability
* High redundancy

* Provides high availability and scalability options

### 6. PostgreSQL
* Open source
* Object-relational databases
* Simplify design
* Reuse objects
* Supports many operating systems
* Unix
* Linux
* Windows
* Support a range of languages for client application development
* Python
* Java
* JavaScript
* C++
* C
* SQL
* Supports relational,structured, and non-structured data
* Support replication and partitioning for high availability and scalability
* Multi-node asynchronous replication
* Milti-master read/write replication