Database Principles and Technologies – Based on Huawei GaussDB

Chapter 1 Introduction to Database 1<div>1.1 Overview of database technology 2</div><div>1.1.1 Data 2</div><div>1.1.2 Records 3</div><div>1.1.3 Databases 3</div><div>1.1.4 Database management systems 4</div><div>1.1.5 Database systems 5</div><div>1.2 Development history of database technology 5</div><div>1.2.1 The emergence and development of database technology 5</div><div>1.2.2 Comparison of the three stages of data management 6</div><div>1.2.5 Hierarchical model, network model and relational model 8</div><div>1.2.6 Structured Query Language 11</div><div>1.2.7 Characteristics of relational database 12</div><div>1.2.8 History review of relational database products 12</div><div>1.2.9 Other data models 14</div><div>1.2.10 New challenges in data management technology 15</div><div>1.2.11 NoSQL database 16</div><div>1.2.12 NewSQL 18</div><div>1.2.13 Database ranking 19</div><div>1.3 Architecture of relational database 19</div><div>1.3.1 Development of database architecture 19</div><div>1.3.2 Standalone architecture 20</div><div>1.3.3 Packet architecture – active/standby 21</div><div>1.3.4 Packet architecture – master/slave 22</div><div>1.3.5 Packet architecture – master/master 22</div><div>1.3.6 Shared storage multi-site architecture 23</div><div>1.3.7 Sharding architecture 24</div><div>1.3.8 Non-sharing architecture 24</div><div>1.3.9 Massively parallel processing architecture 25</div><div>1.3.10 Comparison of the characteristics of database architecture 26</div><div>1.4 Main stream application scenarios of relational database 27</div><div>1.4.1 Online transaction processing 27</div><div>1.4.2 Online analytical processing 27</div><div>1.4.3 Measurement indicators of database performance 28</div><div>1.5 Summary of the chapter 29</div><div>1.6 Exercises 30</div><div><br></div><div>Chapter 2 Database Fundamentals 31</div><div>2.1 Introduction to database management 32</div><div>2.1.1 Database management and its scope of work 32</div><div>2.1.2 Object management 33</div><div>2.1.3 Backup and recovery management 34</div><div>2.1.4 Security management 38</div><div>2.1.5 Performance management 41</div><div>2.1.6 Operation and maintenance management 43</div><div>2.2 Important concepts of database 46</div><div>2.2.1 Database and cases of database 46</div><div>2.2.2 Database connection and session 47</div><div>2.2.3 Schema 48</div>2.2.4 Tablespace 49<div>2.2.5 Table 50</div><div>2.2.6 How tables are stored 51</div><div>2.2.7 Partition 52</div><div>2.2.8 Data distribution 54</div><div>2.2.9 Data types 55</div>2.2.10 View 57<div>2.2.11 Index 58</div><div>2.2.12 Constraints 59</div><div>2.2.13 Transaction 61</div><div>2.4 Exercises 66</div><div>Chapter 3 Introduction to SQL Syntax...67</div><div>3.1 Overview of SQL statement 68</div><div>3.1.1 Introduction to SQL statement 68</div><div>3.1.2 Comprehensive application of SQL statement 69</div><div>3.2 Data types 69</div><div>3.2.1 Common data types 70</div><div>3.2.2 Uncommon data types 72</div><div>3.2.3 Cases of data types 72</div><div>3.3 System functions 73</div><div>3.3.1 Numeric computation functions 73</div><div>3.3.2 Character handling functions 75</div><div>3.3.3 Date and time functions 78</div><div>3.3.4 Type conversion functions 80</div><div>3.3.5 System information functions 81</div><div>3.4 Operators 81</div><div>3.4.1 Logical operators 82</div><div>3.4.2 Comparison operators 83</div><div>3.4.3 Arithmetic operators 83</div><div>3.4.4 Test operators 84</div><div>3.4.5 Other operators 86</div><div>3.5 Summary 86</div><div>3.6 Exercises 86</div><div><br></div><div>Chapter 4 Classification of SQL Syntax…88</div><div>4.1 Data queries 89</div><div>4.1.1 Simple queries 89</div><div>4.1.2 Remove duplicate values 90</div><div>4.1.3 Query column selection 90</div><div>4.1.4 Conditional queries 92</div><div>4.1.5 Join queries 95</div><div>4.1.6 Subqueries 99</div><div>4.1.7 Merge result set 100</div><div>4.1.8 Differential result set 102</div><div>4.1.9 Data packet 102</div><div>4.1.10 Data sorting 104</div><div>4.1.11 Data limit 105</div><div>4.2 Data update 105</div><div>4.2.1 Data insertion 105</div><div>4.2.2 Data modification 107</div><div>4.2.3 Data deletion 108</div><div>4.3 Data definition 110</div><div>4.3.1 Database objects 110</div><div>4.3.2 Create a table 110</div><div>4.3.3 Modify table properties 112</div><div>4.3.4 Delete a table 113</div><div>4.3.5 Index 113</div><div>4.3.6 View 116</div><div>4.4 Data control 118</div><div>4.4.1 Transaction control 118</div><div>4.4.2 Commit a transaction 118</div><div>4.4.3 Rollback transaction 118</div><div>4.4.4 Transaction savepoints 119</div><div>4.5 Other 120</div><div>4.5.1 The show command 120</div><div>4.5.2 The set command 121</div><div>4.6 Summary of the chapter 122</div><div>4.7 Exercises 122</div><div><br></div><div>Chapter 5 Database Security Fundamentals 125</div><div>5.1 Overview of data base security features 126</div><div>5.1.1 Overview of database security management 126</div><div>5.1.2 Database security framework 126</div><div>5.1.3 Overview of database security features 126</div><div>5.2 Access control 127</div><div>5.2.1 Overview of IAM 127</div><div>5.2.2 IAM features 127</div><div>5.2.3 IAM authorization 129</div><div>5.2.4 Relationship between IAM and GaussDB (for MySQL) application 129</div><div>5.2.5 Process of IAM using GaussDB (for MySQL) 130</div><div>5.2.6 Detailed explanation of SSL 130</div><div>5.3 User permission control 131</div>5.3.1 Concepts of permission 131<div>5.3.2 Users 132</div><div>5.3.3 Modification of users 133</div><div>5.3.4 Deletion of users 134</div><div>5.3.5 Roles 134</div><div>5.3.6 Authorization 135</div><div>5.3.7 Permission revocation 137</div><div>5.4 CTS auditing 138</div><div>5.4.1 Overview of CTS auditing 138</div><div>5.4.2 Key operations to support the auditing 139</div><div>5.5 Summary of the chapter 140</div><div>5.6 Exercises 140</div><div><br></div><div>Chapter 6 Database Development Environment 141</div><div>6.1 GaussDB database drivers 142</div><div>6.1.1 Introduction to the concept of drivers 142</div><div>6.1.2 JDBC 142</div><div>6.1.3 ODBC 145</div><div>6.1.4 Others 150</div><div>6.2 Database tools 152</div><div>6.2.1 DDM 152</div><div>6.2.2 DRS 157</div><div>6.2.3 DAS 162</div><div>6.3 Client tools 170</div><div>6.3.1 zsql 170</div><div>6.2.2 gsql 178</div><div>6.2.3 Data Studio 180</div><div>6.2.4 MySQL Workbench 182</div><div>6.3 Summary of the chapter 183</div><div>6.4 Exercises 183</div><div><br></div><div>Chapter 7 Database Design Fundamentals 185</div><div>7.1 Overview of database design 186</div><div>7.1.1 Difficulties in database design 186</div><div>7.1.2 Goals of database design 186</div><div>7.1.3 Methods of database design 187</div><div>7.2 Requirement analysis 187</div><div>7.2.1 Significance of requirement analysis 187</div><div>7.2.2 Tasks of requirement analysis phase 188</div><div>7.2.3 Methodology of requirement analysis phase 188</div><div>7.2.4 Data dictionary 189</div>7.3 Conceptual design 189<div>7.3.1 Conceptual design and conceptual models 189</div><div>7.3.2 E-R methodology 190</div><div>7.4 Logical design 191</div><div>7.4.1 Logical design and logical models 191</div><div>7.4.2 IDEF1X method 192</div><div>7.4.3 Entities and properties in the logical model 192</div><div>7.4.4 Normal Form 197</div><div>7.4.5 Notes for logical design 203</div><div>7.5 Physical design 203</div><div>7.5.1 Physical design and physical models 203</div>7.5.2 Denormalization of physical models 204<div>7.5.3 Maintain data integrity 207</div><div>7.5.4 Establish a physicalized naming convention 207</div><div>7.5.5 Physicalization of tables and fields 208</div><div>7.5.6 Use modelling software 210</div><div>7.5.7 Outputs of physical models 211</div><div>7.6 Cases of database design 211</div><div>7.6.1 Scenario descriptions 211</div><div>7.6.2 Regularization 211</div><div>7.6.3 Data types and lengths 214</div><div>7.6.4 Denormalization 215</div>7.6.5 Index selection 216<div>7.7 Summary 216</div><div>7.8 Exercises 217</div><div><br></div><div>Chapter 8 Introduction to Huawei Database Product GaussDB 219</div><div>8.1 Overview of GaussDB database 220</div>8.1.1 GaussDB database family 220<div>8.1.2 Typical enterprise OLTP and OLAP databases 221</div><div>8.2 Introduction to relational database products 221</div><div>8.2.1 GaussDB (for MySQL) 221</div><div>8.2.2 GaussDB (openGauss) 228</div><div>8.2.3 GaussDB (DWS) 230</div><div>8.2.4 Data Studio 234</div><div>8.3 Introduction to NoSQL database products 236</div><div>8.3.1 GaussDB (for Mongo) 236</div><div>8.3.2 GaussDB (for Cassandra) 238</div><div>8.4 Summary of the chapter 239</div>8.5 Exercises 239<div><br></div>