Database Principles and Technologies – Based on Huawei GaussDB

1 Introduction to Databases
1.​1 Overview of Database Technology
1.​1.​1 Data
1.​1.​2 Database
1.​1.​3 Database Management System
1.​1.​4 Database System
1.​2 History of Database Technology
1.​2.​1 Emergence and Development of Database Technology
1.​2.​2 Comparison of the Three Stages of Data Management
1.​2.​3 Benefits of Database
1.​2.​4 Development Characteristics of the Database
1.​2.​5 Hierarchical Model, Mesh Model and Relational Model
1.​2.​6 Structured Query Language
1.​2.​7 Characteristics of Relational Databases
1.​2.​8 Historical Review of Relational Database Products
1.​2.​9 Other Data Models
1.​2.​10 New Challenges for Data Management Technologies
1.​2.​11 NoSQL Database
1.​2.​12 NewSQL Database
1.​2.​13 Database Ranking
1.​3 Architecture of Relational Databases
1.​3.​1 Development of Database Architecture
1.​3.​2 Single-Host Architecture
1.​3.​3 Group Architecture:​ Master-Standby Architecture
1.​3.​4 Group Architecture:​ Master-Slave Architecture
1.​3.​5 Group Architecture:​ Multi-Master Architecture
1.​3.​6 Shared Disk Architecture
1.​3.​7 Sharding Architecture
1.​3.​8 Shared-Nothing Architecture
1.​3.​9 Massively Parallel Processing Architecture
1.​3.​10 Comparison of the Characteristics of Database Architectures
1.​4 Mainstream Applications of Relational Databases
1.​4.​1 Online Transaction Processing
1.​4.​2 Online Analytical Processing
1.​4.​3 Database Performance Measurement Indicators
1.​5 Summary
1.​6 Exercises
2 Basic Knowledge of Database
2.​1 Overview of Database Management
2.​1.​1 Database Management and Its Scope of Work
2.​1.​2 Object Management
2.​1.​3 Backup and Recovery Management
2.​1.​4 Security Management
2.​1.​5 Performance Management
2.​1.​6 O&​M Management
2.​2 Key Concepts of Database
2.​2.​1 Database and Database Instance
2.​2.​2 Database Connection and Session
2.​2.​3 Schema
2.​2.​4 Tablespace
2.​2.​5 Table
2.​2.​6 How the Table Is Stored
2.​2.​7 Partition
2.​2.​8 Data Distribution
2.​2.​9 Data Types
2.​2.​10 View
2.​2.​11 Index
2.​2.​12 Constraints
2.​2.​13 Transaction
2.​3 Summary
2.​4 Exercises
3 Getting Started with SQL Syntax
3.​1 Overview of SQL Statements
3.​1.​1 What is an SQL Statement
3.​1.​2 Comprehensive Application of SQL Statements
3.​2 Data Types
3.​2.​1 Common Data Types
3.​2.​2 Uncommon Data Types
3.​2.​3 Cases of Data Types
3.​3 System Functions
3.​3.​1 Numeric Calculation Functions
3.​3.​2 Character Processing Functions
3.​3.​3 Time and Date Functions
3.​3.​4 Type Conversion Functions
3.​3.​5 System Information Functions
3.​4 Operators
3.​4.​1 Logical Operators
3.​4.​2 Comparison Operators
3.​4.​3 Arithmetic Operators
3.​4.​4 Test Operators
3.​4.​5 Other Operators
3.​5 Summary
3.​6 Exercises
4 SQL Syntax Categories
4.​1 Data Query
4.​1.​1 Simple Query
4.​1.​2 Removing Duplicate Values
4.​1.​3 Query Column Selection
4.​1.​4 Conditional Query
4.​1.​5 Join Query
4.​1.​6 Subquery
4.​1.​7 Merging Result Sets
4.​1.​8 Difference Result Sets
4.​1.​9 Data Grouping
4.​1.​10 Data Sorting
4.​1.​11 Data Restriction
4.​2 Data Update
4.​2.​1 Data Insertion
4.​2.​2 Data Modification
4.​2.​3 Data Deletion
4.​3 Data Definition
4.​3.​1 Database Objects
4.​3.​2 Creating a Table
4.​3.​3 Modifying Table Properties
4.​3.​4 Deleting a Table
4.​3.​5 Index
4.​3.​6 View
4.​4 Data Control
4.​4.​1 Transaction Control
4.​4.​2 Committing a Transaction
4.​4.​3 Rolling Back a Transaction
4.​4.​4 Transaction Save Points
4.​5 Others
4.​5.​1 SHOW Command
4.​5.​2 SET Command
4.​6 Summary
4.​7 Exercises
5 Database Security Fundamentals
5.​1 Overview of Database Security Features
5.​1.​1 What Is Database Security Management
5.​1.​2 Database Security Framework
5.​1.​3 Database Security Features
5.​2 Access Control
5.​2.​1 What Is IAM
5.​2.​2 IAM Features
5.​2.​3 IAM Authorization
5.​2.​4 Relationship Between IAM and GaussDB (for MySQL) usage
5.​2.​5 How to Use GaussDB(for MySQL) with IAM
5.​2.​6 Detailed Explanation of SSL
5.​3 User Permission Control
5.​3.​1 Permission Concept
5.​3.​2 Users
5.​3.​3 Modifying a User
5.​3.​4 Deleting a User
5.​3.​5 Roles
5.​3.​6 Authorization
5.​3.​7 Permission Recovery
5.​4 Cloud Audit Services
5.​4.​1 What Are Cloud Audit Services
5.​4.​2 Key Operations to Support Cloud Audit Services
5.​5 Summary
5.​6 Exercises
6 Database Development Environment
6.​1 GaussDB Database Driver
6.​1.​1 What Is a Driver
6.​1.​2 JDBC
6.​1.​3 ODBC
6.​1.​4 Others
6.​2 Database Tools
6.​2.​1 DDM
6.​2.​2 DRS
6.​2.​3 DAS
6.​3 Client Tools
6.​3.​1 zsql
6.​3.​2 gsql
6.​3.​3 Data Studio
6.​3.​4 MySQL Workbench
6.​4 Summary
6.​5 Exercises
7 Database Design Fundamentals
7.​1 Database Design Overview
7.​1.​1 Difficulties of Database Design
7.​1.​2 Goal of Database Design
7.​1.​3 Methods of Database Design
7.​2 Requirements Analysis
7.​2.​1 Significance of Requirement Analysis
7.​2.​2 Tasks of the Requirement Analysis Stage
7.​2.​3 Methods of Requirement Analysis
7.​2.​4 Data Dictionary
7.​3 Conceptual Design
7.​3.​1 Conceptual Design and Conceptual Model
7.​3.​2 E-R Approach
7.​4 Logical Design
7.​4.​1 Logical Design and Logical Models
7.​4.​2 IDEF1X Method
7.​4.​3 Entities and Attributes in the Logic Model
7.​4.​4 NF Theory
7.​4.​5 Logic Design Considerations
7.​5 Physical Design
7.​5.​1 Physical Design and Physical Models
7.​5.​2 Denormalization of the Physical Model
7.​5.​3 Maintaining Data Integrity
7.​5.​4 Establishing a Physicalized Naming Convention
7.​5.​5 Physicalizing Tables and Fields
7.​5.​6 Using Modeling Software
7.​5.​7 Physical Model Products
7.​6 Database Design Case
7.​6.​1 Scenario Description
7.​6.​2 Regularization Processing
7.​6.​3 Data Types and Length
7.​6.​4 Denormalization
7.​6.​5 Index Selection
7.​7 Summary
7.​8 Exercises
8 Introduction to Huawei Cloud Database GaussDB
8.​1 GaussDB Database Overview
8.​1.​1 GaussDB Database Family
8.​1.​2 Typical OLTP and OLAP Databases
8.​2 Relational Database Products and Related Tools
8.​2.​1 GaussDB (for MySQL)
8.​2.​2 GaussDB (openGauss)
8.​2.​3 GaussDB (DWS)
8.​2.​4 Data Studio
8.​3 NoSQL Databases
8.​3.​1 GaussDB (for Mongo)
8.​4 Summary
8.​5 Exercises
Index