CIS275 - Chapter 7: Database Architecture Flashcards
describes the components of a computer system and the relationships between components.
Architecture
MySQL components are organized in four layers:
_____ interact directly with database users and administrators, and send queries to the query processor.
Tools
The tools layer includes Connectors and APIs, Workbench, and utility programs.
MySQL components are organized in four layers:
The _____ manages connections from multiple users and compiles queries into low-level instructions for the storage engine.
query processor
The query processor layer has two main functions: manage connections and compile queries.
MySQL components are organized in four layers:
The _____ executes instructions, manages indexes, and interacts with the file system. Some storage engines support database transactions, described elsewhere in this material.
storage engine
The storage engine layer has two main functions: transaction management and data access.
Transaction management includes the concurrency system, recovery system, and lock manager. These components ensure all transactions are atomic, consistent, isolated, and durable, as explained elsewhere in this material.
The data access component communicates with the file system and translates table, column, and index reads into block addresses.
MySQL components are organized in four layers:
The _____ contains system and user data, such as log files, tables, and indexes.
file system
The file system layer consists of data stored on storage media and organized in files. The file system contains three types of data for each database: user data, log files, and a data dictionary.
_____ include approximately 30 tools, grouped in five categories: installation, client, administrative, developer, and miscellaneous tools.
Utility programs
Most utility programs are intended for database administrators or programmers. Example functions include:
Upgrade existing databases to a new MySQL release
Backup databases
Import data to databases
Inspect log files
Administer database servers
a link between tools and the query processor.
connection
Each connection specifies a database name, server address, logon name, and password. The connection manager creates connections and manages communications between tools and the query parser.
a detailed, low-level sequence of steps that specify exactly how to process a query.
execution plan
- The query selects employee name and department name for employees that work in Illinois.
- Step 1: The plan retrieves Illinois employees using an index on State.
- Step 2: The plan sorts selected employees by DeptCode.
- Step 3: The plan retrieves matching departments using a table scan.
- Step 4: The plan sorts the selected departments by Code.
- Step 5: The plan merges the two result tables using the join technique called ‘sort-merge’.
The query processor generates an execution plan in two steps:
The _____ checks each query for syntax errors and converts valid queries to an internal representation.
query parser
The query processor generates an execution plan in two steps:
The _____ reads the internal representation, generates alternative execution plans, estimates execution times, and selects the fastest plan.
query optimizer
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 _____ that stores reusable information in main memory.
cache manager
Ex: The cache manager retains execution plans for queries that are submitted multiple times. If data used in repeated queries does not change, the cache manager may also save query results.
To reduce data access time, the _____ 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 buffer. Ex: If queries frequently access department data, the buffer manager may retain some or all blocks of the Department table. The buffer manager is similar to the cache manager of the query processor layer.
The InnoDB buffer manager uses a ____ or _____ algorithm.
least recently used or LRU algorithm
The LRU algorithm tracks the time each block was last used and, when space is needed, discards ‘stale’ blocks. If data in a block has been updated, discarded blocks are first saved on disk.
- Data in the file system is organized into blocks.
- The database reads blocks from the file system and stores blocks in the buffer.
- As the database reads new blocks, prior blocks become ‘stale’.
- Data block A becomes ‘fresh’ when updated.
- Eventually, the buffer fills up.
- To make space for block E, stale block B is deleted from the buffer.
- Block A has been updated and must be saved to the file system before deletion from the buffer.
A _____ is a directory of tables, columns, keys, indexes, and other objects in a relational database.
catalog, also known as a data dictionary
All relational databases contain a catalog. Query processors and storage managers use catalog information when queries are processed and executed.
Multiple computers linked by a network are often grouped in layers, called ___, and arranged in a hierarchy.
tiers
The term tier refers to either a software or hardware layer. In this material, tier refers to a hardware layer.
a multi-tier architecture consisting of web browsers and web servers communicating over the internet:
Web architecture
Web browsers, on the top tier, manage user interaction.
Web servers, on a middle tier, generate web pages for display on web browsers and transmit user requests to services running on lower tiers.
Application servers run application software, process user requests, and communicate with databases and other services.
Services, such as database and authentication, comprise the bottom tier.
Prior to 1990, most software ran in a _____, consisting of a personal or corporate computer connected directly to monitors.
single-tier architecture
Although computers often communicated with each other, the dependencies between applications running on different computers were limited.
Since 1990, complex corporate and government applications have increasingly been implemented in a _____:
multi-tier architecture
The top tier consists of computers interacting directly with end-users.
The bottom tier consists of servers managing resources like databases and email.
One or more middle tiers execute a variety of functions, such as user authorization, business logic, and communication with other computers.
With _____, a vendor such as Amazon, Microsoft, or Google implements computer services on lower tiers of a web architecture.
cloud services
For a fee, cloud services are made available over the internet to customers.
_____, or _____, provides computer processing, memory, and storage media, as if the customer were renting a computer.
Infrastructure-as-a-service, or IaaS
Ex: Elastic Compute Cloud, or EC2, from Amazon Web Services offers infrastructure-as-a-service.
_____, or _____, provides tools and services, such as databases, application development tools, and messaging services.
Platform-as-a-service, or PaaS
Ex: Azure is Microsoft’s cloud services environment, offering the SQL Database service.
_____, or _____, provides complete applications, usually through web browsers on customer machines.
Software-as-a-service, or SaaS
Ex: Salesforce offers sales management software, and Google offers document processing applications like Docs, Sheets, and Pages.
a software layer that emulates a complete, independent computing environment.
virtual machine, or VM
Usually cloud services are offered on virtual machines.
Multiple virtual machines can run on one computer , enabling cloud providers to support many customers on the same machine.
a database offered as a PaaS cloud service.
cloud database
Most databases are now available either on-premise or as a cloud service, but cloud database use is growing rapidly.
Prior to 2000, most commercial software was _____, or installed and run on customer computers.
on-premise
Expected:
(a) 1, 2, 3, 4, 5, On-premise
(b) 1, SaaS
(c) 1, 2, 5, laaS
(d) 1, 2, PaaS
(a) All layers are installed, administered, and run on customer computers, so on-premise.
(b) The service offers an application to customers. Customers access the application via a web browser, so SaaS.
(c) The service offers virtual machines and all other layers are managed by the customers, so IaaS.
(d) The service offers computer services and tools running within virtual machines. Any application run on the service is managed by the customers and accessed via a web browser, so PaaS.
Expected:
(a) 3, SaaS
(b) 1, 3, 5, laaS
(c) 1, 2, 3, 4, 5, On-premise
(d) 3, 5, PaaS
(a) The service offers an application to customers. Customers access the application via a web browser, so SaaS.
(b) The service offers virtual machines and all other layers are managed by the customers, so IaaS.
(c) All layers are installed, administered, and run on customer computers, so on-premise.
(d) The service offers computer services and tools running within virtual machines. Any application run on the service is managed by the customers and accessed via a web browser, so PaaS.
Expected:
(a) 2, 4, laaS
(b) 2, 4, 5, PaaS
(c) None, On-premise
(d) 1, 2, 4, 5, SaaS
(a) The service offers virtual machines and all other layers are managed by the customers, so IaaS.
(b) The service offers computer services and tools running within virtual machines. Any application run on the service is managed by the customers and accessed via a web browser, so PaaS.
(c) All layers are installed, administered, and run on customer computers, so on-premise.
(d) The service offers an application to customers. Customers access the application via a web browser, so SaaS.
Expected:
(a) 1, 3, laaS
(b) None, On-premise
(c) 1, 2, 3, 5, SaaS
(d) 1, 3, 5, PaaS
(a) The service offers virtual machines and all other layers are managed by the customers, so IaaS.
(b) All layers are installed, administered, and run on customer computers, so on-premise.
(c) The service offers an application to customers. Customers access the application via a web browser, so SaaS.
(d) The service offers computer services and tools running within virtual machines. Any application run on the service is managed by the customers and accessed via a web browser, so PaaS.
Expected:
(a) 4, 5, laaS
(b) 2, 4, 5, PaaS
(c) 2, 3, 4, 5, SaaS
(d) None, On-premise
(a) The service offers virtual machines and all other layers are managed by the customers, so IaaS.
(b) The service offers computer services and tools running within virtual machines. Any application run on the service is managed by the customers and accessed via a web browser, so PaaS.
(c) The service offers an application to customers. Customers access the application via a web browser, so SaaS.
(d) All layers are installed, administered, and run on customer computers, so on-premise.
Expected:
(a) 1, 2, 5, laaS
(b) 1, 2, PaaS
(c) 1, 2, 3, 4, 5, On-premise
(d) 1, SaaS
(a) The service offers virtual machines and all other layers are managed by the customers, so IaaS.
(b) The service offers computer services and tools running within virtual machines. Any application run on the service is managed by the customers and accessed via a web browser, so PaaS.
(c) All layers are installed, administered, and run on customer computers, so on-premise.
(d) The service offers an application to customers. Customers access the application via a web browser, so SaaS.
Expected:
(a) 1, 2, 3, 4, 5, On-premise
(b) 1, 2, 4, laaS
(c) 1, SaaS
(d) 1, 2, PaaS
(a) All layers are installed, administered, and run on customer computers, so on-premise.
(b) The service offers virtual machines and all other layers are managed by the customers, so IaaS.
(c) The service offers an application to customers. Customers access the application via a web browser, so SaaS.
(d) The service offers computer services and tools running within virtual machines. Any application run on the service is managed by the customers and accessed via a web browser, so PaaS.
Expected:
(a) 3, 4, laaS
(b) None, On-premise
(c) 2, 3, 4, 5, SaaS
(d) 2, 3, 4, PaaS
(a) The service offers virtual machines and all other layers are managed by the customers, so IaaS.
(b) All layers are installed, administered, and run on customer computers, so on-premise.
(c) The service offers an application to customers. Customers access the application via a web browser, so SaaS.
(d) The service offers computer services and tools running within virtual machines. Any application run on the service is managed by the customers and accessed via a web browser, so PaaS.
Expected:
(a) 1, 2, 4, 5, SaaS
(b) None, On-premise
(c) 1, 4, 5, PaaS
(d) 1, 4, laaS
(a) The service offers an application to customers. Customers access the application via a web browser, so SaaS.
(b) All layers are installed, administered, and run on customer computers, so on-premise.
(c) The service offers computer services and tools running within virtual machines. Any application run on the service is managed by the customers and accessed via a web browser, so PaaS.
(d) The service offers virtual machines and all other layers are managed by the customers, so IaaS.
Expected:
(a) None, On-premise
(b) 2, 5, laaS
(c) 1, 2, 5, PaaS
(d) 1, 2, 3, 5, SaaS
(a) All layers are installed, administered, and run on customer computers, so on-premise.
(b) The service offers virtual machines and all other layers are managed by the customers, so IaaS.
(c) The service offers computer services and tools running within virtual machines. Any application run on the service is managed by the customers and accessed via a web browser, so PaaS.
(d) The service offers an application to customers. Customers access the application via a web browser, so SaaS.
Expected:
(a) 2, 5, PaaS
(b) 1, 2, 3, 4, 5, On-premise
(c) 2, SaaS
(d) 2, 3, 5, laaS
(a) The service offers computer services and tools running within virtual machines. Any application run on the service is managed by the customers and accessed via a web browser, so PaaS.
(b) All layers are installed, administered, and run on customer computers, so on-premise.
(c) The service offers an application to customers. Customers access the application via a web browser, so SaaS.
(d) The service offers virtual machines and all other layers are managed by the customers, so IaaS.
A _____ consists of multiple processors managed by a single operating system instance.
parallel computer
In a _____, processors share the same memory and storage media.
shared memory computer
Shared memory is optimal for parallel processing against a common data set in a single memory space.
In a _____, processors share storage media only. Each processor has private memory.
shared storage computer
shared storage and shared nothing scale to more processors, since processors do not contend for the same memory.
In a _____, processors share neither memory nor storage media.
shared nothing computer
shared storage and shared nothing scale to more processors, since processors do not contend for the same memory.
A _____ consists of cables extending over a small area, typically within one facility.
local area network
Local area networks usually use the Ethernet communication protocol.
A _____ 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 internet communication protocols.
one of a group of computers connected by either a local or wide area network.
node
A node is a single processor with associated memory and storage.
a group of nodes connected by a local area network, managed by separate operating system instances, and coordinated by specialized _____ management software.
cluster
A cluster is similar to a parallel computer. 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.