Database Architecture Flashcards
Blank describes the components of a computer system and the relationships between components
Architecture
MySQL components are organized in four blank
Layers
Name the four layers of MySQL components
Tools
The query processor
The storage engine
The file system
Blank interact directly with database users and administrators, and send queries to the query processor.
Tools
The blank manages connections from multiple users and compiles queries into low-level instructions for the storage engine.
Query processor
The blank, also called a storage manager, executes instructions, manages indexes, and interacts with the file system. Some storage engines support database transactions, described elsewhere in this material.
Storage engine
The blank accesses data on storage media. The file system contains both system and user data, such as log files, tables, and indexes.
File system
The Enterprise Edition includes MySQL Server and components for high-end commercial installations, such as what two things
Monitor
Audit
Blank collects and displays information on CPU, memory, and index utilization, as well as queries and results. Database administrators use Enterprise Monitor to manage and tune large databases with many users.
Monitor
Blank keeps track of all database changes. For each change, Audit tracks the time of change and who made the change. Audit supports government and business audit requirements for sensitive databases such as financial, medical, and defense.
Audit
The blank layer includes Connectors and APIs, Workbench, and utility programs.
Tools
Blank and blank are groups of application programming interfaces, linking applications to the query processor layer.
Connectors and APIs
Blank are newer and developed by Oracle, which sponsors MySQL.
Connectors
Blank are older and, with the exception of the C API, developed by other organizations.
APIs
Most programmers use blank but system programmers may write specialized utilities in C with the C API.
Connectors
Blank is a desktop application to manage and use databases. Blank is designed for both database administrators and users.
Workbench
Blank programs include approximately 30 tools, grouped in five categories.
Utility
Name the five categories of utility programs
installation, client, administrative, developer, and miscellaneous tools.
Upgrade existing databases to a new MySQL release
Backup databases
Import data to databases
Inspect log files
Administer database servers
Are all things that the blank programs do.
utility
The blank is a particularly important utility program, commonly used by both database administrators and users. The blank displays the mysql> prompt and processes individual SQL queries interactively.
Command-Line Client
The query processor layer has two main functions. Name them.
manage connections and compile queries.
A blank is a link between tools and the query processor.
connection
Each connection specifies a blank, blank, blank, and blank. The connection manager creates connections and manages communications between tools and the query parser.
database name, server address, logon name, and password
Blank generates a query execution plan.
Query compilation
An blank is a detailed, low-level sequence of steps that specify exactly how to process a query.
execution plan
The blank generates an execution plan in two steps.
query processor
The query processor generates an execution plan in two steps. Name them.
The query parser checks each query for syntax errors and converts valid queries to an internal representation.
The query optimizer reads the internal representation, generates alternative execution plans, estimates 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.
For optimal performance, the query processor layer has a blank that stores reusable information in main memory.
cache manager
If data used in repeated queries does not change, the cache manager may also blank query results.
save
The storage engine layer has two main functions. Name them.
transaction management and data access.
Blank includes the concurrency system, recovery system, and lock manager. These components ensure all transactions are atomic, consistent, isolated, and durable,
Transaction management
The data access component communicates with the file system and translates table, column, and index reads into blank.
block addresses
To reduce data access time, the blank retains data blocks from the file system for possible reuse.
buffer manager
The data blocks are retained in an area of main memory called the blank.
buffer
The buffer manager is similar to the blank of the query processor layer.
cache manager
The buffer manager has a fixed amount of blank.
memory
As the database processes queries and reads blocks, an blank determines which blocks to retain and which to discard.
algorithm
The InnoDB buffer manager uses a least recently used or blank, which tracks the time each block was last accessed and, when space is needed, discards ‘stale’ blocks. If data in a block has been updated, discarded blocks are first saved on disk.
LRU algorithm
The database administrator can assign a different blank to each table in a database. InnoDB is the default and most commonly used storage engine.
storage engine
The blank layer consists of data stored on storage media and organized in files.
file system
The file system contains three types of data for each database. Name them
user data, log files, and a data dictionary.
Blank data includes tables and indexes.
User
Blank files contain a detailed, sequential record of each change applied to a database.
Log
The recovery system uses blank to restore data in the event of a transaction, system, or storage media failure.
log files
A blank, also known as a data dictionary, is a directory of tables, columns, keys, indexes, and other objects in a relational database.
catalog
All relational databases contain a blank. Query processors and storage managers use this information when queries are processed and executed.
catalog
MySQL uses the term blank instead of catalog
‘data dictionary’
Data dictionary tables cannot be accessed directly with SELECT, INSERT, UPDATE, and DELETE queries. However, the table contents can be accessed indirectly. The blank query is compiled as a SELECT query against dictionary tables.
SHOW
Multiple computers linked by a network are often grouped in layers, called blank, and arranged in a hierarchy.
tiers
Prior to 1990, most software ran in a blank, consisting of a personal or corporate computer connected directly to monitors. Although computers often communicated with each other, the dependencies between applications running on different computers were limited.
single-tier architecture
Since 1990, complex corporate and government applications have increasingly been implemented in a blank.
multi-tier architecture
In a multi-tier architecture, the top tier consists of computers interacting directly with blank
end-users
In a multi-tier architecture, the bottom tier consists of blank managing resources like databases and email.
servers
In a multi-tier architecture, one or more blank execute a variety of functions, such as user authorization, business logic, and communication with other computers.
middle tiers
Typically, blank run on a middle tier and implement business logic.
application programs
Since user interaction and data are managed in the top and bottom tiers, applications are easier to write and maintain in a blank.
multi-tier architecture
Blank is a multi-tier architecture consisting of web browsers and web servers communicating over the internet
Web architecture
In a web architecture, blank on the top tier, manage user interaction.
web browsers
In a web architecture, blank, on a middle tier, generate web pages for display on web browsers and transmit user requests to services running on lower tiers.
Web servers
In a web architecture, blank run application software, process user requests, and communicate with databases and other services.
Application servers
In a web architecture, blank, such as database and authentication, comprise the bottom tier.
Services
The term blank refers to either a software or hardware layer
tier
Prior to 2000, most commercial software was blank, or installed and run on customer computers.
on-premise
Since 2000, blank have increasingly replaced on-premise software. With blank, a vendor such as Amazon, Microsoft, or Google implements computer services on lower tiers of a web architecture. For a fee, blank are made available over the internet to customers.
cloud services
Cloud services fall into three broad categories. Name them.
Infrastructure-as-a-service, or IaaS
Platform-as-a-service, or PaaS
Software-as-a-service, or SaaS
Blank provides computer processing, memory, and storage media, as if the customer were renting a computer. Ex: Elastic Compute Cloud, or EC2, from Amazon Web Services offers it
Infrastructure-as-a-service, or IaaS,
Blank provides tools and services, such as databases, application development tools, and messaging services. Ex: Azure is Microsoft’s cloud services environment, offering the SQL Database service.
Platform-as-a-service, or PaaS
Blank provides complete applications, usually through web browsers on customer machines. Ex: Salesforce offers sales management software, and Google offers document processing applications like Docs, Sheets, and Pages.
Software-as-a-service, or SaaS
Usually blank are offered on virtual machines.
cloud services
A blank, is a software layer that emulates a complete, independent computing environment.
virtual machine, or VM
Multiple virtual machines can run on blank, enabling cloud providers to support many customers on the same machine.
one computer
A cloud database is a database offered as a blank cloud service. Most databases are now available either on-premise or as a cloud service, but cloud database use is growing rapidly.
PaaS
A blank is a statement or proposition, from which another statement is inferred.
premise
Premises refers to buildings and land occupied by a business. Thus, blank is technically correct in the context of cloud software. However on-premise is easier to say and therefore commonly used.
on-premises
Cloud databases have a number of compelling benefits. Name them (5)
Administration.
Security.
Reliability.
Elasticity.
Capital cost.
Installing, managing, upgrading, and backing up database systems is time-consuming and complex. With cloud databases, consumers delegate blank to cloud providers.
administrative activities
Cloud providers are large companies with extensive resources. Cloud providers can invest heavily in blank providing better than most cloud customers.
security professionals and infrastructure
Cloud providers provide blank computing systems with little or no down-time.
redundant
Many organizations struggle with daily, monthly, or seasonal fluctuations in processing workload. By averaging fluctuations over many customers, cloud providers provide blank on demand.
flexible database resources
Cloud providers absorb all initial, or capital, costs of computers and facilities. Blank is recovered by cloud service fees.
Capital cost
Cloud databases raise blank. Companies entrust data to cloud providers, which may store data on servers located in countries with different privacy regulations.
data privacy questions
In the United States, data privacy is governed by blank in specific areas, such as medical and financial. As a result, a European company may avoid servers located in the United States.
limited regulations
Data privacy is a concern primarily for blank, such as financial and medical applications. For organizations that do not manage blank, cloud databases offer convincing benefits and have been widely adopted.
sensitive data
A blank consists of multiple processors managed by a single operating system instance.
parallel computer
Parallel computers achieve faster processing speeds by processing multiple instructions blank.
concurrently
Parallel computers fall into three categories. Name them.
A shared memory computer
A shared storage computer
A shared nothing computer
In a blank, processors share the same memory and storage media.
shared memory computer
In a blank, processors share storage media only. Each processor has private memory.
shared storage computer
In a blank, processors share neither memory nor storage media.
shared nothing computer
Blank memory is optimal for parallel processing against a common data set in a single memory space.
Shared
Blank and blank scale to more processors, since processors do not contend for the same memory.
Shared storage and shared nothing
Multiple computers can communicate via a blank or blank network
local or wide area
A blank consists of cables extending over a small area, typically within one facility.
local area network
Local area networks usually use the blank protocol.
Ethernet communication
A blank spans multiple facilities in different geographic locations, separated by many miles.
wide area network
Wide area networks may communicate via cables, satellite, or telephone lines, often using blank protocols.
internet communication
A blank is one of a group of computers connected by either a local or wide area network.
node
A blank is a group of nodes connected by a local area network, managed by separate operating system instances, and coordinated by specialized cluster management software.
cluster
A cluster is similar to a blank. Both can execute program instructions in parallel on multiple processors. Both can share storage or share nothing. Computers in a cluster cannot share memory, however, since local area networks are too slow to support memory access.
parallel computer
Blank can often be decomposed into parts that run concurrently and execute faster on parallel computers or clusters.
Queries
Parallel and distributed databases exploit blank for faster query execution
multiple processors
A blank runs on a parallel computer or cluster.
parallel database
A blank runs on multiple computers connected by a wide area network.
distributed database
Both parallel and distributed databases present a blank view of data to database users and programmers. The physical location of data on storage media is visible to database administrators only.
unified
In a parallel database, data location has blank on query processing since local area networks are relatively fast and reliable.
limited impact
In a distributed database, data location is blank since wide area networks are relatively slow and unreliable. Wide area networks create technical challenges with distributed transaction
significant
Despite technical challenges, blank offer compelling benefits for databases with users in many locations
distributed databases
A blank updates data on multiple nodes of a distributed database.
distributed transaction
In a distributed transaction, either blank or blank must be successfully updated.
all nodes or no nodes
Databases commonly implement distributed transactions with a technique called blank.
two-phase commit
The two-phase commit has four steps. Name them.
In phase 1, a central transaction coordinator notifies all participating nodes of the required updates.
Participating nodes receive the notification, store the update in a local log, and send a confirmation message to the transaction coordinator. Participating nodes do not yet commit the update to the database.
Phase 2 begins when the transaction coordinator receives confirmation from all participating nodes. The transaction coordinator now instructs all nodes to commit.
Participating nodes receive the commit message, commit the update to the database, and notify the transaction coordinator of success.
The two-phase commit must account for what two failure scenarios:
In step 2, if the transaction coordinator does not receive confirmation from all nodes within a fixed time period, the transaction coordinator instructs participating nodes to roll back the update.
In step 4, if a node becomes unavailable and fails to notify the transaction coordinator of success, the transaction coordinator resends the commit message until the node responds.
The two-phase commit ensures updates are applied to either all nodes or no nodes. In the first failure scenario, the transaction blank, and no updates are applied. In the second failure scenario, the transaction blank, and all updates are applied.
rolls back
commits
Two-phase commit and two-phase locking are different procedures. Two-phase commit blank and blank at the end of distributed transactions only. Two-phase locking, governs blank and blank of locks during either local or distributed transactions.
governs commit and rollback
acquisition and release
A blank updates data on a single node of a distributed database.
local transaction
Distributed transactions are relatively blank, as multiple nodes must respond before the transaction commits. As a faster alternative, multiple nodes can be updated blank with local transactions
slow
independently
In a local transaction, the blank notifies participating nodes of required updates.
Nodes blank and confirm with the transaction coordinator.
If a node is unavailable, the transaction coordinator blank until confirmation is received.
transaction coordinator
commit immediately
repeats the update message
Local transactions create blank, as nodes are updated at different times.
temporary inconsistency
