Database Principles and Technologies - Based on Huawei Gaussdb

ISBN-13: 9789811930317 / Angielski / Miękka / 2022 / 314 str.

Ltd. Huawei Technologies Co.

Spis treści

Chapter 1 Introduction to Database 1

1.1 Overview of database technology 2

1.1.1 Data 2

1.1.2 Records 3

1.1.3 Databases 3

1.1.4 Database management systems 4

1.1.5 Database systems 5

1.2 Development history of database technology 5

1.2.1 The emergence and development of database technology 5

1.2.2 Comparison of the three stages of data management 6

1.2.5 Hierarchical model, network model and relational model 8

1.2.6 Structured Query Language 11

1.2.7 Characteristics of relational database 12

1.2.8 History review of relational database products 12

1.2.9 Other data models 14

1.2.10 New challenges in data management technology 15

1.2.11 NoSQL database 16

1.2.12 NewSQL 18

1.2.13 Database ranking 19

1.3 Architecture of relational database 19

1.3.1 Development of database architecture 19

1.3.2 Standalone architecture 20

1.3.3 Packet architecture – active/standby 21

1.3.4 Packet architecture – master/slave 22

1.3.5 Packet architecture – master/master 22

1.3.6 Shared storage multi-site architecture 23

1.3.7 Sharding architecture 24

1.3.8 Non-sharing architecture 24

1.3.9 Massively parallel processing architecture 25

1.3.10 Comparison of the characteristics of database architecture 26

1.4 Main stream application scenarios of relational database 27

1.4.1 Online transaction processing 27

1.4.2 Online analytical processing 27

1.4.3 Measurement indicators of database performance 28

1.5 Summary of the chapter 29

1.6 Exercises 30

Chapter 2 Database Fundamentals 31

2.1 Introduction to database management 32

2.1.1 Database management and its scope of work 32

2.1.2 Object management 33

2.1.3 Backup and recovery management 34

2.1.4 Security management 38

2.1.5 Performance management 41

2.1.6 Operation and maintenance management 43

2.2 Important concepts of database 46

2.2.1 Database and cases of database 46

2.2.2 Database connection and session 47

2.2.3 Schema 48

2.2.4 Tablespace 49

2.2.5 Table 50

2.2.6 How tables are stored 51

2.2.7 Partition 52

2.2.8 Data distribution 54

2.2.9 Data types 55

2.2.10 View 57

2.2.11 Index 58

2.2.12 Constraints 59

2.2.13 Transaction 61

2.4 Exercises 66

Chapter 3 Introduction to SQL Syntax...67

3.1 Overview of SQL statement 68

3.1.1 Introduction to SQL statement 68

3.1.2 Comprehensive application of SQL statement 69

3.2 Data types 69

3.2.1 Common data types 70

3.2.2 Uncommon data types 72

3.2.3 Cases of data types 72

3.3 System functions 73

3.3.1 Numeric computation functions 73

3.3.2 Character handling functions 75

3.3.3 Date and time functions 78

3.3.4 Type conversion functions 80

3.3.5 System information functions 81

3.4 Operators 81

3.4.1 Logical operators 82

3.4.2 Comparison operators 83

3.4.3 Arithmetic operators 83

3.4.4 Test operators 84

3.4.5 Other operators 86

3.5 Summary 86

3.6 Exercises 86

Chapter 4 Classification of SQL Syntax…88

4.1 Data queries 89

4.1.1 Simple queries 89

4.1.2 Remove duplicate values 90

4.1.3 Query column selection 90

4.1.4 Conditional queries 92

4.1.5 Join queries 95

4.1.6 Subqueries 99

4.1.7 Merge result set 100

4.1.8 Differential result set 102

4.1.9 Data packet 102

4.1.10 Data sorting 104

4.1.11 Data limit 105

4.2 Data update 105

4.2.1 Data insertion 105

4.2.2 Data modification 107

4.2.3 Data deletion 108

4.3 Data definition 110

4.3.1 Database objects 110

4.3.2 Create a table 110

4.3.3 Modify table properties 112

4.3.4 Delete a table 113

4.3.5 Index 113

4.3.6 View 116

4.4 Data control 118

4.4.1 Transaction control 118

4.4.2 Commit a transaction 118

4.4.3 Rollback transaction 118

4.4.4 Transaction savepoints 119

4.5 Other 120

4.5.1 The show command 120

4.5.2 The set command 121

4.6 Summary of the chapter 122

4.7 Exercises 122

Chapter 5 Database Security Fundamentals 125

5.1 Overview of data base security features 126

5.1.1 Overview of database security management 126

5.1.2 Database security framework 126

5.1.3 Overview of database security features 126

5.2 Access control 127

5.2.1 Overview of IAM 127

5.2.2 IAM features 127

5.2.3 IAM authorization 129

5.2.4 Relationship between IAM and GaussDB (for MySQL) application 129

5.2.5 Process of IAM using GaussDB (for MySQL) 130

5.2.6 Detailed explanation of SSL 130

5.3 User permission control 131

5.3.1 Concepts of permission 131

5.3.2 Users 132

5.3.3 Modification of users 133

5.3.4 Deletion of users 134

5.3.5 Roles 134

5.3.6 Authorization 135

5.3.7 Permission revocation 137

5.4 CTS auditing 138

5.4.1 Overview of CTS auditing 138

5.4.2 Key operations to support the auditing 139

5.5 Summary of the chapter 140

5.6 Exercises 140

Chapter 6 Database Development Environment 141

6.1 GaussDB database drivers 142

6.1.1 Introduction to the concept of drivers 142

6.1.2 JDBC 142

6.1.3 ODBC 145

6.1.4 Others 150

6.2 Database tools 152

6.2.1 DDM 152

6.2.2 DRS 157

6.2.3 DAS 162

6.3 Client tools 170

6.3.1 zsql 170

6.2.2 gsql 178

6.2.3 Data Studio 180

6.2.4 MySQL Workbench 182

6.3 Summary of the chapter 183

6.4 Exercises 183

Chapter 7 Database Design Fundamentals 185

7.1 Overview of database design 186

7.1.1 Difficulties in database design 186

7.1.2 Goals of database design 186

7.1.3 Methods of database design 187

7.2 Requirement analysis 187

7.2.1 Significance of requirement analysis 187

7.2.2 Tasks of requirement analysis phase 188

7.2.3 Methodology of requirement analysis phase 188

7.2.4 Data dictionary 189

7.3 Conceptual design 189

7.3.1 Conceptual design and conceptual models 189

7.3.2 E-R methodology 190

7.4 Logical design 191

7.4.1 Logical design and logical models 191

7.4.2 IDEF1X method 192

7.4.3 Entities and properties in the logical model 192

7.4.4 Normal Form 197

7.4.5 Notes for logical design 203

7.5 Physical design 203

7.5.1 Physical design and physical models 203

7.5.2 Denormalization of physical models 204

7.5.3 Maintain data integrity 207

7.5.4 Establish a physicalized naming convention 207

7.5.5 Physicalization of tables and fields 208

7.5.6 Use modelling software 210

7.5.7 Outputs of physical models 211

7.6 Cases of database design 211

7.6.1 Scenario descriptions 211

7.6.2 Regularization 211

7.6.3 Data types and lengths 214

7.6.4 Denormalization 215

7.6.5 Index selection 216

7.7 Summary 216

7.8 Exercises 217

Chapter 8 Introduction to Huawei Database Product GaussDB 219

8.1 Overview of GaussDB database 220

8.1.1 GaussDB database family 220

8.1.2 Typical enterprise OLTP and OLAP databases 221

8.2 Introduction to relational database products 221

8.2.1 GaussDB (for MySQL) 221

8.2.2 GaussDB (openGauss) 228

8.2.3 GaussDB (DWS) 230

8.2.4 Data Studio 234

8.3 Introduction to NoSQL database products 236

8.3.1 GaussDB (for Mongo) 236

8.3.2 GaussDB (for Cassandra) 238

8.4 Summary of the chapter 239

8.5 Exercises 239

O autorze

Huawei Technologies Co., Ltd. Founded in 1987, Huawei is a leading global provider of information and communications technology (ICT) infrastructure and smart devices. We have approximately 197,000 employees and we operate in over 170 countries and regions, serving more than three billion people around the world. 

Huawei’s mission is to bring digital to every person, home and organization for a fully connected, intelligent world. To this end, we will: drive ubiquitous connectivity and promote equal access to networks to lay the foundation for the intelligent world; provide the ultimate computing power to deliver ubiquitous cloud and intelligence; build powerful digital platforms to help all industries and organizations become more agile, efficient, and dynamic; redefine user experience with AI, offering consumers more personalized and intelligent experiences across all scenarios, including home, travel, office, entertainment, and fitness & health.

Blurb

This open access book contains eight chapters that deal with database technologies, including the development history of database, database fundamentals, introduction to SQL syntax, classification of SQL syntax, database security fundamentals, database development environment, database design fundamentals, and the application of Huawei’s cloud database product GaussDB database.

This book can be used as a textbook for database courses in colleges and universities, and is also suitable as a reference book for the HCIA-GaussDB V1.5 certification examination. The Huawei GaussDB (for MySQL) used in the book is a Huawei cloud-based high-performance, highly applicable relational database that fully supports the syntax and functionality of the open source database MySQL. All the experiments in this book can be run on this database platform.

As the world’s leading provider of ICT (information and communication technology) infrastructure and smart terminals, Huawei’s products range from digital data communication, cyber security, wireless technology, data storage, cloud computing, and smart computing to artificial intelligence.