--- tags: System Architecture --- Database === * [Main Referance：資料庫的核心理論與實務7／e](https://www.books.com.tw/products/0010779579) * Learning Resource * [DBMS & SQL Tutorial for Beginners](https://www.studytonight.com/dbms/) * [DBMS Tutorial: Learn Database Management System](https://www.guru99.com/dbms-tutorial.html) * [SQLZOO](https://sqlzoo.net/wiki/SQL_Tutorial) * [dblp: computer science bibliography](https://dblp.uni-trier.de/) * [CMU Database Group](https://www.youtube.com/channel/UCHnBsf2rH-K7pn09rb3qvkA/playlists) * [Let's Build a Simple Database](https://cstack.github.io/db_tutorial/parts/part7.html) * Tools * [dbdiagram.io](https://dbdiagram.io/home) * [DBeaver:Free multi-platform database tool for developers](https://dbeaver.io/) # Introduction * What is data? * Data is nothing but facts and statistics stored or free flowing over a network, generally it's raw and unprocessed. Data becomes information when it is processed, turning it into something meaningful. * This information can later be used for a website, an application or any other client to store for future purpose. * What is database? * A Database is a collection of related data organised in a way that data can be easily accessed, managed and updated. * Database can be software based or hardware based, with one sole purpose, storing data. * What is database management system (DBMS)? * A DBMS is a software that allows creation, definition and manipulation of database, allowing users to store, process and analyse data easily. * DBMS provides us with an interface or a tool, to perform various operations like creating database, storing data in it, updating data, creating tables in the database and a lot more. * DBMS also provides protection and security to the databases. It also maintains data consistency in case of multiple users. * Whait is database application systems? * All kind of online platform almost have a database application systems to manage data. ## DBMS (Database Management System) * Database system structure ![](https://i.imgur.com/O2Uexdc.jpg) * [DB-Engines Ranking](https://db-engines.com/en/ranking) ![](https://i.imgur.com/mnYrBWK.png) * DBMS 演進過程: * 1960：檔案系統 * 1970：階層式 DBMS * 1980：關聯式 DBMS，在大型主機上執行 * 1985：紀錄型 DBMS,早期 PC 上執行 * 1990：物件導向式 DBMS * 1995：個人用 DBMS (MS Access) * 2000：加入物件, XML 和分析功能 DBMS (MS SQL Server, Oracle 8) * 2005：加入資料探勘(Data mining)功能的 DBMS (MS SQL Server 2005, Oracle 10g) * 2010：加入雲端運算功能的 DBMS，包含 NoSQL DBMS(Google Cloud BigTable, MongoDB, Neo4j) * DBMS Architecture * 2-tier DBMS Architecture * Application layer * DBMS * 3-tier DBMS Architecture：3-tier DBMS architecture is the most commonly used architecture for web applications. 1. User (Presentation) Tier * End-users operate on this tier and they know nothing about any existence of the database beyond this layer. * In web, this layer is **Browser** 2. Application (Middle) Tier * At this tier reside the application server and the programs that access the database. * 展示層和資料服務層的橋樑，負責應用系統作業，包含 Business Rules * just like a web **backend** or a **server** 3. DBMS * At this tier, the database resides along with its query processing languages. * We also have the relations that define the data and their constraints at this level. ## Data model * What is data model? * A Data model defines the logical design and structure of a database and defines how data will be stored, accessed and updated in a database management system. * Data model is a conceptual representation of Data objects, the associations between different data objects and the rules. * Why use Data Model? * A data model helps design the database at the conceptual, physical and logical levels. * Several data mdoels: * Entity-relationship Model * Relational model * Network model * Hierarchical model * Object-oriented model # Data Modelling * What is Data Modelling? * Data modeling is the process of creating a data model for the data to be stored in a Database. * There are mainly three different types of data models: 1. Conceptual data modeling * This Data Model defines WHAT the system contains. * The purpose is to organize, scope and define business concepts and rules. * creater: Business stakeholders and Data Architects 2. Logical data modeling * Defines HOW the system should be implemented regardless of the DBMS. * The purpose is to developed technical map of rules and data structures. * creater: Data Architects and Business Analysts 3. Physical data modeling * This Data Model describes how the system will be implemented using a specific DBMS system. * The purpose is actual implementation of the database. * creater: DBA and developers * Comparing these three types of data models: ![](https://i.imgur.com/PE5kAIU.png) * Data Modeling Flow: ![](https://i.imgur.com/GwX7UO6.jpg) * The two types of Data Models techniques are: 1. Entity Relationship (ER) Model * 之後有擴充加入物件導向概念(Enhanced Entity Relationship Model, EER Model)，但這裡先不探討 2. UML (Unified Modelling Language) ## Entity relationship model * What is Entity relationship model * In this database model, relationships are created by dividing object of interest into entity and its characteristics into attributes. * Different entities are related using relationships. * This model is good to design a database, which can then be turned into tables in relational model. ### Entity * Key words: * 實體(Entity)：一個被抽象化的主體都可以是實體，像是線上購物資料庫裡面的一位會員，一本書，一張CD 或是一個購物車...等。 * 屬性(Attribute)：一個實體會有迷你世界所需的相關附屬特性稱為屬性，例如：會員有：Name, Birthday, Address...等 * 屬性值(Attribute value)：每個實體其屬性所對應的值，像是剛剛的 Name 屬性，有些值是 Mary or Eric ... * 單值屬性(Single-valued attribute) * 多值屬性(Multivalued attribute)-屬性值不只一個 * 簡單屬性(Simple attribute) * 複合屬性(Composite attribute)-該屬性由數個子屬性所組成 * 空值(Null):屬性值是空值的三種可能: * **已知**有值但未填入 * 該實體的該屬性**不可能**有值(Not Applicable, N/A) * **不知道**該實體的該屬性是否有值 * 系統分析人員的任務就是界定迷你世界，並描繪所需的相關資料 * 實體型態(Entity type) * 當眾多實體中，有一些其屬性是一樣的(EX: 100會員)，此時我們可以用一個實體型態（Entity type）來描繪這些實體。 * 關鍵屬性(Key attribute)：為了在同一個實體型態區分不同實體，因此規定實體型態必須至少一個特別屬性，其每個實體的屬性值必然不同(EX: ID) * 實體關係圖(Entity Relationship Diagram, EDR)： * 圖形化表示法易於溝通 * 基本圖示： ![](https://i.imgur.com/d4xnL03.png) * **Key attribute** with bottom line ![](https://i.imgur.com/SFtyfgI.png) * Weak Entity:A weak entity is an entity that depends on the existence of another entity. ![](https://i.imgur.com/GJ4C7aD.png) * Multivalued Attribute: ![](https://i.imgur.com/LAOx2eY.png) ### Relationlship * Basic： * 一個實體型態(Entity Type)的某些屬性，與其他實體有些關連，會用關係型態(Relationship Type)來連接兩實體 * 該屬性稱為相關屬性 * 例如：交易實體&會員實體, carpenter & table * 在 ERD，用菱形方塊來連結兩個實體 ![](https://i.imgur.com/d5KVNjA.png) * 二元關係型態(Binary relationship type) * 關係只涉及兩個實體(大部分狀況） * 結構上限制(Structural constraint) 1. 基數比(Cardinality ratio) * 一個實體**最多**與對方實體有關係的數量 * EX: Both's cardinality is 1: ![](https://i.imgur.com/Iibm8t2.png) * EX: One many, another is 1: ![](https://i.imgur.com/g6PKRMK.png) * EX: bath's cardinality is many: ![](https://i.imgur.com/QXNS9DB.png) 2. 參與度(Participation constraint) *　一個實體**最少**與對方實體有關係的數量 *　只須確認實體是否需要全部參與此類關係 *　是：完全參與(Total participation)-雙線 *　否：部份參與(Partial participation)-單線 ![](https://i.imgur.com/f188iHi.png) * 關係型態也能有屬性 ![](https://i.imgur.com/fhKsBi3.png) * 弱實體型態(Weak entity type) * A weak entity is simple an entity where it's existence depends on another entity * 部份鍵：the key attribute become **Partial key** * 識別關係型態：the relathonship become **Identifying relationship type** * 主實體型態：the another entity (not weak entity) called **owner entity type** ![](https://i.imgur.com/fEDJG96.png) * 遞迴關係型態 # Relational Model # 資料儲存結構 * Basic concept * DBMS 管理資料量大，所以其資料必須存在**次記憶體(Secondary storage)**，通常是硬碟(Hard disk) * 電腦一般須透過**直接記憶存取裝置 DMA(Direct Memory Access)**，將硬碟資料抓取到主記憶體後才能處理資料，寫回硬碟也是 * 硬碟資料格式化後的單位是頁(page)，同時也是 DMA 抓取單位 ## 資料表的儲存結構 * SQL 的基本單位是資料表 * 以資料儲存角度來看 * 一個資料表在硬碟中是由數個資料頁(page)所構成 * 每個資料頁(page)包含數筆紀錄 ![](https://i.imgur.com/HH9Tup2.jpg) ## B+ tree Index structure * 為了解決大量硬碟頁(page)載入主記憶體的速度問題 * 基於**二元樹(Binary tree)**的搜尋會比順序搜尋快 # PostgreSQL * Installation * [Installing PostgreSQL by APT](https://wiki.postgresql.org/wiki/Apt) * Quickstart * Import the repository key from https://www.postgresql.org/media/keys/ACCC4CF8.asc: * ```sudo apt-get install curl ca-certificates gnupg``` * ```curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -``` * create /etc/apt/sources.list.d/pgdg.list * ```sudo vim /etc/apt/sources.list.d/pgdg.list``` * add "deb http://apt.postgresql.org/pub/repos/apt bionic-pgdg main" * Note: bionic should replaced by your OS version * Or add below for auto check OS version: "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" * install: * ```sudo apt-get update``` * ```sudo apt install postgresql postgresql-contrib``` * Get start * ```sudo -i -u postgres``` * ```psql``` * Now you can see ```postgres=# ``` it mean you can start to use postgreSQL * ```postgres=# \q``` for leave # MySQL * Underlying principle * InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 8.0, InnoDB is the default MySQL storage engine. * [The underlying principle of mysql index](https://bzdww.com/article/202323/) * [InnoDB - Jeremy Cole](https://blog.jcole.us/innodb/) * [当我们输入一条SQL查询语句时，发生了什么？](https://mp.weixin.qq.com/s/2hJzdILGgLwcw8mkCY1uLw) * [MySQL索引背后的数据结构及算法原理](https://mp.weixin.qq.com/s/QhN0B_EBtaZenveNe2fkcw) # NoSQL * [AresDB](https://developer.download.nvidia.com/video/gputechconf/gtc/2019/presentation/s91021-aresdb-a-gpu-powered-real-time-analytical-engine.pdf)