Database Architecture Flashcards
(172 cards)
1
Q
Architecture
A
components of a computer system and the relationships between components
2
Q
Tools
A
Directly interact with database users and administrators, and send queries to the query processor
3
Q
Query Processor
A
Manages connections from multiple users and compiles queries into low-level instructions for the storage engine
4
Q
Storage Engine/Storage Manager
A
Executes instructions, manages indexes, and interacts with the file system
5
Q
File System
A
Accesses data on storage media. The file system contains both system and user data, such as log files, tables, and indexes
6
Q
MySQL Server/Enterprise
A
MySQL server - free, MySQL enterprise - paid
7
Q
Monitor
A
Collects and displays information on CPU, memory, and index utilization, as well as queries and results.
8
Q
Audit
A
Keeps track of all database changes. It also tracks the time of change and who made the change.
9
Q
Utility Programs
A
Program suite includes 30 tools grouped into five categories. Installation, Client, Administrative, Developer, and miscellaneous tools.
10
Q
Connection
A
Link between tools and the query processor. Each connection specifies a database name, server address, logon name, and password.
11
Q
Execution plan
A
Detailed, low-level sequence of steps that specify exactly how to process a query
12
Q
Query Parser
A
Check each query for syntax errors and converts valid queries to an internal representation
13
Q
Query Optimizer
A
Reads the internal representation, generates alternative execution plans, estimated execution times, and selects the fastest plan. Estimates are based on heuristics and statistics about data, like the number of rows in each table and the number of values in each column. These statistics are maintained in the data dictionary described below.
14
Q
Cache Manager
A
Query processor layer has a cache manager that stores reusable information in main memory
15
Q
Buffer Manager
A
Retains data blocks from the file system for possible reuse to reduce access time
16
Q
Buffer
A
Data blocks retained in an area of main memory
17
Q
Least Recently Used/LRU
A
LRU algorithm tracks the time each block was last accessed and, when space is needed, discards ‘stale’ blocksC
18
Q
Catalog, Data dictionary
A
Directory of tables, columns, keys, indexes, and other objects in a relational database
19
Q
Tiers
A
Computers linked by a network are often grouped in layers, called tiers, and arranged in a hierarhcy.
20
Q
Single-Tier Architecture
A
Consists of a personal or corporate computer connected directly to monitors
21
Q
Multi-tier Architecture
A
Complex corporate and government applications have increasingly been implemented in a multi-tier architecture
22
Q
Web Architecture
A
Consists of web browsers and web servers communicating over the internet. Web browsers on the top tier, webservers on the middle tier, application servers, and then services on the bottom
23
Q
On-premise
A
Installed and run on a customer computers
24
Q
Cloud Services
A
Computer services on a lower tier of a web architecture. Cloud services are made available over the internet to customers for a monthly fee
25
Infrastructure-as-a-service/IaaS
provides computer processing, memory, and storage media, as if the customer were renting a computer
26
Platform-as-a-service/PaaS
provides tool and services, such as a databases, application development tools, and messaging services
27
Virtual Machine/VM
Software layer that emulates a complete, independent computing environment.
28
Cloud Database
database offered as a PaaS cloud service
29
Parallel Computer
Computer consists of multiple processor managed by a single operating system instance
30
Shared Memory
Processor share the same memory and storage media
31
Shared Storage
Processor share storage media only
32
Shared nothing
Processor share neither memory nor storage media
33
Local Area Network
Consists of cable extending over a small area, typically within one facility. LAN usually use the Ethernet communication protocol.
34
Wide Area Network
WAN spans multiple facilities in different geographic locations, separated by many miles. WAN communicate via cables, satellite, or telephone lines, often using internet communication protocols.
35
CLuster
Group of nodes connected by a local area network, managed by separate operating systems instances, and coordinated by specialized cluster management software
35
Node
Group of computers connected by either a local or wide area network
35
Parallel Database
Runs on a parallel computer or cluster
35
Distributed database
runs on multiple computers connected by a wide area network
36
Distributed transcation
Updates data on multiple nodes of a distributed database. Either all nodes or no nodes must be successfully updated
37
Two-phase Commit
Phase 1.1, central transaction coordinator notifies all participating nodes of the required updates. 1.2. Participating nodes receive the notification, store the update in a local log, and send a confirmation message. Phase 2.1 begins when the transaction coordinator receives confirmation from all participating nodes. The transition coordinator now instructs all nodes to commit. 2.2 participating nodes receive the commit message, commit the update to the database, and notify the transaction coordinator of success.
38
Local Transaction
Updates data on a single node of a distribute ddatabase
39
Synchronous
Distributed transaction are synchronous, since the updates occur at the same time from the perspective of the database user
40
Asynchronous
updates in separate local transactions are asynchronous
41
eventually consistent
Databases that use local rather than distributed transactions are called eventually consistent
42
Consistent
Database conforms to all rules at all times
43
Available
live nodes must respond to queries at all times
44
Network partition
When a network error prevents nodes from communicating
45
Partition-tolerant
Continues to function when a network partition occurs
46
CAP theorem
Distributed datatbase cannot simultaneously be Consistent, Available, and Partition-tolerant. Distributed database can guarantee any two, but not all three. of these properties.
47
Replica
Copy of an entire database, a table or a subset of table data
48
Replicated Database
Maintains two or more replicas on seperate storage devices
49
Storage arrays
Manage replicas internally, without database intervention
50
Primary/Secondary
designates one node as primary. All updates are first applied to the primary node in local transactions, Secondary nodes are updated after the primary node commits, with independent local transactions.
51
Group replication
Applies updates to any node in a group. If a node detects a conflict, an algorithm determines which transaction commits and which rolls back
52
Central Catalog
Entire catalog resides on a single node.
53
Replicated catalog
Copy of the catalog resides on each node
54
Operational Data
Conduct daily business functions
55
Analytic Data
Data that is used to understand, manage, and plan the business
56
Reporting Data/Decision support Data.
Data that is reporting data or decision support data
57
Data warehouse
Seperate database optimized for analytics rather than operations
58
Data mart
Data warehouse designed for a specific business area, such as sales, human resources, or product development
59
Extract-transform-load/ETL
Five-step process commonly referred to as the extract-transform-load or ETL process
60
ETL tools
ETL is a time-consuming process so most organizations use special software products to minimize coding.
61
Dimensional Design/Star Schema
consists of fact and dimension tables
62
fact table
Contains numeric data used to measure business performance, such as sales revenue or number of employees. Each row in a fact table consists of numeric fact columns and foreign keys that reference dimension tables.
63
Dimension table
Contains textual data that described the fact data, such as product line, organizational unit, and geographical region
64
Dimension Hierarhhcy
Sequence of columns in which each column has a one-many relationship to the next column
65
Date dimension
Each row corresponds to a day. Contains 36500 rows (100 years x 365days per year)
66
Time Deimension
Each row corresponds to a minute of the day. Contains 1440 rows (24 hours x 60 minute per hour)
67
Type 2 Design for slowly chanign dimension
Start and end foreign keys to the fact table
68
In-memory Database
Database that stores data in main memory, instead of or in addition to storage media
69
Embedded Database/In-Process Database
Database that is packaged with a programming language. Embedded database and application program execute together in a single software process
70
SQLite
dominant embedded relational database
71
SQL Server Compact
Embedded database form microsoft, discontinued support after 2021
72
Libmysqld
Discontinued version that configured MySQL as embedded.
73
Federated Database
Collection of two or more participating databases underneath a coordinating software layer. The participating databases are autonomous and heterogeneous
74
Autonomous datbase
Operates independently of other participating databases. It is administered and can be queried as if the database were not part of a federated database
75
Heterogeneous databases
Run under different database systems or have incompatible schema
76
Middleware
coordinating sfotware layer, since the software lies between application programs and database software
77
Global Catalog
Directory of participating database objects, such as tables, columns, and indexes
78
Databse Wrapper
Converts the decomposed queries to the appropriate syntax for each participating database
79
SQL/Management of External Data/SQL/MED
Extension of the SQL standard for federated databases
80
Nickname
Database name for a participating database object, such as tables and columns
81
User Mapping
Associates a federated database user with a participating database user
82
Data Lake
Analytic database of raw, unprocessed data copied from multiple data sources. Data lakes share some characteristics of data warehouses and some characteristic of federated databases
83
BackTick
Delimits literals that represent identifiers which allows spaces and reserved words to be used as identifiers.
84
components of a computer system and the relationships between components
Architecture
85
Directly interact with database users and administrators, and send queries to the query processor
Tools
86
Manages connections from multiple users and compiles queries into low-level instructions for the storage engine
Query Processor
87
Executes instructions, manages indexes, and interacts with the file system
Storage Engine/Storage Manager
88
Accesses data on storage media. The file system contains both system and user data, such as log files, tables, and indexes
File System
89
MySQL server - free, MySQL enterprise - paid
MySQL Server/Enterprise
90
Collects and displays information on CPU, memory, and index utilization, as well as queries and results.
Monitor
91
Keeps track of all database changes. It also tracks the time of change and who made the change.
Audit
92
Program suite includes 30 tools grouped into five categories. Installation, Client, Administrative, Developer, and miscellaneous tools.
Utility Programs
93
Link between tools and the query processor. Each connection specifies a database name, server address, logon name, and password.
Connection
94
Detailed, low-level sequence of steps that specify exactly how to process a query
Execution plan
95
Check each query for syntax errors and converts valid queries to an internal representation
Query Parser
96
Reads the internal representation, generates alternative execution plans, estimated execution times, and selects the fastest plan. Estimates are based on heuristics and statistics about data, like the number of rows in each table and the number of values in each column. These statistics are maintained in the data dictionary described below.
Query Optimizer
97
Query processor layer has a cache manager that stores reusable information in main memory
Cache Manager
98
Retains data blocks from the file system for possible reuse to reduce access time
Buffer Manager
99
Data blocks retained in an area of main memory
Buffer
100
LRU algorithm tracks the time each block was last accessed and, when space is needed, discards 'stale' blocksC
Least Recently Used/LRU
101
Directory of tables, columns, keys, indexes, and other objects in a relational database
Catalog, Data dictionary
102
Computers linked by a network are often grouped in layers, called tiers, and arranged in a hierarhcy.
Tiers
103
Consists of a personal or corporate computer connected directly to monitors
Single-Tier Architecture
104
Complex corporate and government applications have increasingly been implemented in a multi-tier architecture
Multi-tier Architecture
105
Consists of web browsers and web servers communicating over the internet. Web browsers on the top tier, webservers on the middle tier, application servers, and then services on the bottom
Web Architecture
106
Installed and run on a customer computers
On-premise
107
Computer services on a lower tier of a web architecture. Cloud services are made available over the internet to customers for a monthly fee
Cloud Services
108
provides computer processing, memory, and storage media, as if the customer were renting a computer
Infrastructure-as-a-service/IaaS
109
provides tool and services, such as a databases, application development tools, and messaging services
Platform-as-a-service/PaaS
110
Software layer that emulates a complete, independent computing environment.
Virtual Machine/VM
111
database offered as a PaaS cloud service
Cloud Database
112
Computer consists of multiple processor managed by a single operating system instance
Parallel Computer
113
Processor share the same memory and storage media
Shared Memory
114
Processor share storage media only
Shared Storage
115
Processor share neither memory nor storage media
Shared nothing
116
Consists of cable extending over a small area, typically within one facility. LAN usually use the Ethernet communication protocol.
Local Area Network
117
WAN spans multiple facilities in different geographic locations, separated by many miles. WAN communicate via cables, satellite, or telephone lines, often using internet communication protocols.
Wide Area Network
118
Group of nodes connected by a local area network, managed by separate operating systems instances, and coordinated by specialized cluster management software
CLuster
119
Group of computers connected by either a local or wide area network
Node
120
Runs on a parallel computer or cluster
Parallel Database
121
runs on multiple computers connected by a wide area network
Distributed database
122
Updates data on multiple nodes of a distributed database. Either all nodes or no nodes must be successfully updated
Distributed transcation
123
Phase 1.1, central transaction coordinator notifies all participating nodes of the required updates. 1.2. Participating nodes receive the notification, store the update in a local log, and send a confirmation message. Phase 2.1 begins when the transaction coordinator receives confirmation from all participating nodes. The transition coordinator now instructs all nodes to commit. 2.2 participating nodes receive the commit message, commit the update to the database, and notify the transaction coordinator of success.
Two-phase Commit
124
Updates data on a single node of a distribute ddatabase
Local Transaction
125
Distributed transaction are synchronous, since the updates occur at the same time from the perspective of the database user
Synchronous
126
updates in separate local transactions are asynchronous
Asynchronous
127
Databases that use local rather than distributed transactions are called eventually consistent
eventually consistent
128
Database conforms to all rules at all times
Consistent
129
live nodes must respond to queries at all times
Available
130
When a network error prevents nodes from communicating
Network partition
131
Continues to function when a network partition occurs
Partition-tolerant
132
Distributed datatbase cannot simultaneously be Consistent, Available, and Partition-tolerant. Distributed database can guarantee any two, but not all three. of these properties.
CAP theorem
133
Copy of an entire database, a table or a subset of table data
Replica
134
Maintains two or more replicas on seperate storage devices
Replicated Database
135
Manage replicas internally, without database intervention
Storage arrays
136
designates one node as primary. All updates are first applied to the primary node in local transactions, Secondary nodes are updated after the primary node commits, with independent local transactions.
Primary/Secondary
137
Applies updates to any node in a group. If a node detects a conflict, an algorithm determines which transaction commits and which rolls back
Group replication
138
Entire catalog resides on a single node.
Central Catalog
139
Copy of the catalog resides on each node
Replicated catalog
140
Conduct daily business functions
Operational Data
141
Data that is used to understand, manage, and plan the business
Analytic Data
142
Data that is reporting data or decision support data
Reporting Data/Decision support Data.
143
Seperate database optimized for analytics rather than operations
Data warehouse
144
Data warehouse designed for a specific business area, such as sales, human resources, or product development
Data mart
145
Five-step process commonly referred to as the extract-transform-load or ETL process
Extract-transform-load/ETL
146
ETL is a time-consuming process so most organizations use special software products to minimize coding.
ETL tools
147
consists of fact and dimension tables
Dimensional Design/Star Schema
148
Contains numeric data used to measure business performance, such as sales revenue or number of employees. Each row in a fact table consists of numeric fact columns and foreign keys that reference dimension tables.
fact table
149
Contains textual data that described the fact data, such as product line, organizational unit, and geographical region
Dimension table
150
Sequence of columns in which each column has a one-many relationship to the next column
Dimension Hierarhhcy
151
Each row corresponds to a day. Contains 36500 rows (100 years x 365days per year)
Date dimension
152
Each row corresponds to a minute of the day. Contains 1440 rows (24 hours x 60 minute per hour)
Time Deimension
153
Start and end foreign keys to the fact table
Type 2 Design for slowly chanign dimension
154
Database that stores data in main memory, instead of or in addition to storage media
In-memory Database
155
Database that is packaged with a programming language. Embedded database and application program execute together in a single software process
Embedded Database/In-Process Database
156
dominant embedded relational database
SQLite
157
Embedded database form microsoft, discontinued support after 2021
SQL Server Compact
158
Discontinued version that configured MySQL as embedded.
Libmysqld
159
Collection of two or more participating databases underneath a coordinating software layer. The participating databases are autonomous and heterogeneous
Federated Database
160
Operates independently of other participating databases. It is administered and can be queried as if the database were not part of a federated database
Autonomous datbase
161
Run under different database systems or have incompatible schema
Heterogeneous databases
162
coordinating sfotware layer, since the software lies between application programs and database software
Middleware
163
Directory of participating database objects, such as tables, columns, and indexes
Global Catalog
164
Converts the decomposed queries to the appropriate syntax for each participating database
Databse Wrapper
165
Extension of the SQL standard for federated databases
SQL/Management of External Data/SQL/MED
166
Database name for a participating database object, such as tables and columns
Nickname
167
Associates a federated database user with a participating database user
User Mapping
168
Analytic database of raw, unprocessed data copied from multiple data sources. Data lakes share some characteristics of data warehouses and some characteristic of federated databases
Data Lake
169
Delimits literals that represent identifiers which allows spaces and reserved words to be used as identifiers.
BackTick
