Chapter 6 Database Architecture Flashcards
What are the components of MySQL architecture?
What are the four layers of MySQL architecture?
The four layers are Tools Layer, Query Processor, Connectors, and Storage Engine.
What is the Tools Layer in MySQL architecture?
The Tools Layer includes user interfaces and applications for database interaction.
What does the Query Processor do?
The Query Processor manages connections and compiles SQL queries.
What are Connectors in MySQL?
Connectors are APIs that link applications to the query processor.
What is the purpose of APIs in MySQL?
APIs are older programming interfaces for database interaction.
What is MySQL Workbench?
MySQL Workbench is a desktop application for managing MySQL databases.
What are Utility Programs in MySQL?
Utility Programs are tools for database administration and management.
What is the Command-Line Client?
The Command-Line Client is an interactive tool for processing SQL queries.
What are Database Transactions?
Database Transactions are operations that ensure data integrity and consistency.
What is System Data in MySQL?
System Data in MySQL is the information stored in the mysql system schema. This schema contains tables with metadata about the server itself, including:
- Data Dictionary Tables: Store information about database objects like tables and columns.
- System Tables: Used for operational purposes like user accounts and server logs.
How do users interact with User Data in MySQL?
User Data is created and managed by database users.
What are High-End Installations?
High-End Installations are advanced setups requiring additional Enterprise features.
What is a Connection in MySQL?
A Connection specifies the database name and credentials.
What does the Connection Manager do?
The Connection Manager creates and manages connections to databases.
What is a Query Compilation?
Query Compilation generate a detailed execution plan for queries.
What is the CSV Storage Engine?
The CSV Storage Engine stores data in comma-separated values format.
What is the MEMORY Storage Engine?
The MEMORY Storage Engine stores data in RAM for fast access.
What is a Storage Engine?
A Storage Engine manages how data is stored in tables.
Who is a Database Administrator?
A Database Administrator manages database configurations and storage engines.
What is Block Replacement?
Block Replacement is the process of removing stale blocks from the buffer.
What is a Data Update?
A Data Update is the modification of existing data in a block.
What is Buffer Capacity?
Buffer Capacity is the maximum number of blocks the buffer can hold.
What is Block Discarding?
Block Discarding is removing blocks from the buffer to free space.
What is the File System Layer?
The File System Layer organizes data stored on physical storage media.
What are Database Queries?
Database Queries are requests for data retrieval or manipulation.
Multi-Tier Architecture is used how in management?
Multi-Tier Architecture groups computers in layers for application management.
What is the Top Tier in architecture?
The Top Tier interacts directly with end-users.
What does the Bottom Tier manage?
The Bottom Tier manages resources like databases and email.
What functions does the Middle Tier execute?
The Middle Tier executes functions like user authorization and business logic.
What is Web Architecture?
Web Architecture is a multi-tier architecture for web applications.
What role do Web Browsers play in web architecture?
Web Browsers are the top tier managing user interaction.
What do Web Servers do?
Web Servers are the middle tier generating web pages and handling requests.
What is the function of Application Servers?
Application Servers process requests and communicate with databases.
What does the Services Tier provide?
The Services Tier provides database and authentication services.
What is Single-tier architecture?
Single-tier architecture contains one layer of software components.
What does Multi-tier architecture contain?
Multi-tier architecture contains two or more interconnected software tiers.
What is On-premise software?
On-premise software is installed and run on customer computers.
What are Cloud services?
Cloud services are software provided over the internet for a fee.
What is Infrastructure-as-a-Service (IaaS)?
What does Platform-as-a-Service (PaaS) provide?
PaaS provides a platform for developers to create and manage applications
What is Software-as-a-Service (SaaS) & Platform as a Service?
- SaaS is about delivering ready-to-use software applications (like renting a furnished apartment)
- PaaS provides a platform for developers to create and manage applications (like leasing land to build a house).
What is Elastic Compute Cloud (EC2)?
EC2 is Amazonβs IaaS offering for virtual computing.
What is Microsoft Azure?
Microsoft Azure is Microsoftβs PaaS offering with various cloud services.
What is Salesforce?
Salesforce is a SaaS for sales management and customer relations.
What is a Cloud database?
A Cloud database is offered as a PaaS cloud service.
What is a Virtual machine (VM)?
A VM emulates a complete computing environment on a server.
Who is a Customer in cloud services?
A Customer is an end-user accessing cloud services.
What is Storage media?
Storage media are physical devices for storing data in the cloud.
What is a Network?
A Network connects multiple computers for data exchange.
What is an Operating system?
An Operating system is software managing computer hardware and resources.
What is a Database?
A Database is a structured collection of data for storage and retrieval.
What is a Server in a computer network?
A Server is a computer providing data or services to other computers.
What is a Shared memory computer?
A Shared memory computer has processors that share the same memory and storage.
What is a Shared storage computer?
A Shared storage computer has processors that share storage but have private memory.
What is a Shared nothing computer?
A Shared nothing computer has processors that share neither memory nor storage.
What are Parallel computers?
Parallel computers have multiple processors working under a single OS.
What is a Local area network?
A Local area network consists of cables connecting devices within a small area.
What is the Ethernet protocol?
The Ethernet protocol is a common communication protocol for local area networks.
What are Data transmission risks?
Data transmission risks are potential vulnerabilities when sending data online.
What is a corp Cloud service provider?
Cloud service providers are companies offering database management in the cloud.
What is Scalable storage?
Scalable storage is adjustable storage capacity based on customer needs.
What are Peak processing loads?
Peak processing loads refer to high demand periods requiring additional resources.
What is a Wide Area Network?
A Wide Area Network spans multiple facilities across large distances.
What is a Node in networking?
A Node is a computer connected in a network.
What is Shared Memory?
Shared Memory is when multiple processors share both memory and storage.
What is Shared Storage?
Shared Storage is when multiple processors share storage but not memory.
What is Shared Nothing?
Shared Nothing means each processor has private memory and storage.
What is the Ethernet Protocol?
The Ethernet Protocol is a standard for connecting computers in a network.
What is a Parallel Database?
A Parallel Database runs on parallel computers or clusters.
What is a Distributed Database?
A Distributed Database operates on multiple computers via a wide area network.
What is Query Decomposition?
Query Decomposition is breaking queries into parts for concurrent execution.
What is a Local Area Network?
A Local Area Network covers a small geographic area.
What are Technical Challenges in databases?
Technical Challenges arise from slow wide area networks.
What is a Unified View of Data?
A Unified View of Data provides consistent data representation for users and programmers.
What is Data Location Impact?
Data Location Impact refers to the significance of data placement on query processing.
What are Remote Queries?
Remote Queries access data from different locations.
What is Cluster Management Software?
This software coordinates nodes in a cluster.
Node: computer on network
What are Memory Access Limitations?
Memory Access Limitations indicate local networks canβt support shared memory access.
What is Data Visibility?
Data Visibility means only administrators see physical data locations.
What is Concurrency in databases?
Concurrency is the simultaneous execution of processes or queries.
What is Query Execution Speed?
Query Execution Speed is the time taken to process database queries.
What is Employee Data Storage?
Employee Data Storage refers to local nodes storing data for geographically distributed employees.
What are Communication Protocols?
Communication Protocols are rules for data exchange between networked devices.
What is a Processor?
A Processor is a component that executes instructions and processes data.
What is the Two-Phase Commit protocol?
The Two-Phase Commit protocol ensures all nodes commit or rollback updates.
What is a Transaction Coordinator?
- A transaction coordinator is a software component that manages distributed transaction processes.
- It ensures that all parts of a transaction are completed successfully or that the entire transaction is rolled back if there is an error.
What is a Local Log?
A Local Log is temporary storage for pending updates on nodes.
What is a Commit Message?
A Commit Message is an instruction to finalize updates in a database.
What is Rollback?
Rollback is the reversal of changes made during a transaction.
What is a Confirmation Message?
A Confirmation Message is an acknowledgment from nodes to the transaction coordinator.
What is Data Assignment?
Data Assignment is the distribution of data across nodes in a database.
What is Node Availability?
Node Availability refers to the status of nodes being operational during transactions.
Who are Database Administrators?
Database Administrators are professionals managing and optimizing database performance.
Aka root account
What are Distributed Database Characteristics?
Distributed Database Characteristics indicate nodes are not visible to users or programmers.
What is Two-phase locking Phase 1?
In Phase 1, the coordinator notifies the nodes of updates.
What is Two-phase locking; Phase 1 / 2?
- Phase 1 (Growing): Transactions acquire all necessary locks.
- Phase 2 (Shrinking): Transactions release all locks and commit.
What is a Commit message?
- A Commit message instructs nodes to finalize updates.
- The commit message flows from the coordinator to the nodes, confirming the update.
What is a Distributed transaction?
A Distributed transaction involves multiple nodes for data updates.
What is a Local transaction?
A Local transaction updates data on a single node only.
What is Temporary inconsistency?
Temporary inconsistency occurs when nodes are updated at different times, causing discrepancies.
What is a Debit Node?
A Debit Node is responsible for processing debit transactions.
What is a Credit Node?
A Credit Node is responsible for processing credit transactions.
What is the CAP Theorem?
The CAP Theorem limits simultaneous consistency, availability, and partition tolerances.
What is a Network Partition?
A Network Partition is a failure; preventing nodes from communicating with each other.
What is a Partition-Tolerant Database?
A Partition-Tolerant Database continues functioning despite network partitions.
What is a Consistent Database?
A Consistent Database requires data across nodes to match and conform.
What is an SQL Update Statement?
An SQL Update Statement is a command to modify existing data in a database.
What is an SQL Select Statement?
An SQL Select Statement is πͺ π¬πΈπΆπΆπͺπ·π π½πΈ retrieve data from a database.
What is Percentage Availability?
Percentage Availability is the proportion of time a database is responsive to users.
What is a Node in a distributed database?
A Node is an individual unit within a distributed database system.
What is Table Data Partition?
Table Data Partition is a subset of data within a database table.
What is a Live Node?
A Live Node is a node that actively responds to queries.
What is a Replicated Database?
A Replicated Database maintains multiple copies on different devices.
What is a Simple Backup?
A Simple Backup involves backing up one replica while others are active.
What is Enhanced Security?
Enhanced Security refers to improved data protection through multiple replicas.
What is Shared Nothing Architecture?
Shared Nothing Architecture is where nodes do not share storage.
What is Shared Storage?
Shared Storage allows multiple nodes to access common storage.
What is Shared Memory?
Shared Memory is an architecture where processors share a common memory.
What is Complex Server Administration?
Complex Server Administration involves managing update propagation across replicas.
What is Replication?
Replication is used in parallel and distributed databases.
What are Frequent Reads?
Frequent Reads are a common scenario for using database replication.
What are Infrequent Updates?
- Infrequent Updates occur less often than reads.
- The frequency of data reads is usually much higher than the frequency of data updates.
What is Temporary Inconsistency in replication?
Temporary Inconsistency is an acceptable state during data replication.
What is a Catalog in databases?
A Catalog is a directory of information describing database objects.
What is a Central Catalog?
A Central Catalog is catalog information stored on a single node.
What is Group Replication?
Group Replication is a technique allowing updates from any node in the group.
What do Double-Headed Arrows indicate?
Double-Headed Arrows indicate bidirectional communication between nodes and the cloud.
What are Animation Captions?
Animation Captions are visual aids explaining the database update process.
What is Transaction Information?
Transaction Information is data sent to nodes before commit to prevent conflicts.
What is a Database Management System?
A Database Management System is software for managing databases and processing queries.
What are Replicated Databases?
Replicated Databases maintain copies of data across nodes.
What is a Central Catalog?
A Central Catalog contains the entire catalog on a single node.
What are Local Transactions?
Local Transactions are transactions affecting only one nodeβs data.
What is an Out-of-Date Replica?
An Out-of-Date Replica is not updated with the latest changes.
What is an Error Display in databases?
An Error Display advises resubmitting queries if outdated.
What is Operational Data?
Operational Data is used for daily business functions.
What is Analytic Data?
Analytic Data is used for business analysis and planning.
What is Volatility in data?
- Volatility refers to the rate of change in operational data.
- Data volatility is about the information. Itβs about how often the meaning of the data changes.
What is Extracted Data?
Extracted Data is pulled from operational databases for processing.
What is Data Mart Derivation?
Data Mart Derivation is the creation of a data mart from operational or warehouse data.
What are Performance Issues?
Performance Issues are problems affecting the speed of database queries.
What is Data Refresh Frequency?
Data Refresh Frequency refers to regular intervals at which data is updated in the warehouse.
What is Query Conflict?
Query Conflict refers to discrepancies in reports generated by simultaneous queries.
What is Metric System Conversion?
Metric System Conversion is changing measurements to metric for consistency.
What is a system Catalog?
- A collection of tables within the database that store metadata about the database itself.
- Includes information about tables, columns, data types, constraints, and other structural details.
What is a Learning Management System?
A software application used to create, deliver, track, and manage online courses and training programs
What is a Temporary Database?
A Temporary Database is short-term storage for data processing.
What is Data Standardization?
Data Standardization applies consistent formats and definitions to data.
What is a Fact Table?
A Fact Table stores quantitative data for analysis.
What is a Dimension Table?
A Dimension Table contains descriptive attributes related to facts.
What is Summary Data?
Summary Data is precomputed data stored for fast access.
What is a Dimensional Model?
A Dimensional Model is a structure used to organize data in a data warehouse.
What is Nonvolatile Data?
- Nonvolatile Data remains unchanged (via power outage) until updated or replaced.
- Itβs stored in non-volatile storage mediums, such as hard drives. flash drives, and optical discs
What is Query Submission?
Query Submission is the process of requesting information from a data warehouse.
What are Ad Hoc Queries?
Ad Hoc Queries are spontaneous queries for specific data analysis needs.
What are Operational Systems?
Operational Systems are designed for executing business transactions.
What is Analysis Paralysis?
Analysis Paralysis is overthinking that hinders timely decision-making.
What are Reporting and Analytics?
Reporting and Analytics are processes for interpreting data, to inform business decisions.
Who are BI Consumers?
BI Consumers are individuals providing requirements for data analysis.
What is Data Profiling?
Data Profiling is assessing data quality of candidate sources.
What are Dimensional Models?
Dimensional Models are structures designed for analytical data representation.
What is Data Extraction?
Data Extraction is the process of retrieving data from sources.
What is Data Cleansing?
Data Cleansing involves removing inaccuracies from data sets.
What is Data Replication?
Data Replication is copying data from one database to another.
What is Change Data Capture?
Change Data Capture is tracking changes in data sources.
What are Computational Requirements?
Computational Requirements are resources needed for processing data aggregations. Which includes:
- The hardware (CPU, GPU, memory, storage)
- software (operating system, databases, algorithms) that enable data manipulation and analysis
What are Parallel Machine Architectures?
Parallel Machine Architectures are systems designed to handle large data processing.
What is Storage Space Calculation?
Storage Space Calculation is determined by dimensions and their sizes.
What is Data Migration?
Data Migration is moving data from one system to another.
What is a Staging Area?
A Staging Area is infrastructure for aggregating raw data sets.
What is Data Validation?
Data Validation ensures data accuracy and quality before processing.
What is Data Cleaning?
Data Cleaning involves removing inaccuracies and inconsistencies from data.
What is Data Transformation?
Data Transformation modifies data into a suitable format for analysis.
What is Data Requirements Analysis?
Data Requirements Analysis identifies data needed for business intelligence applications.
What is Automated Extraction?
Automated Extraction refers to tools that automate data retrieval processes.
What are SQL Queries?
SQL Queries are structured queries for data extraction.
What are Adapters?
Adapters are connectors for different data sources.
What are Code Generation Techniques?
Code Generation Techniques are methods for creating executable data extraction programs.
What is a Source System?
A Source System is the original system from which data is extracted.
What is Warehouse Population?
Warehouse Population is filling the data warehouse with processed data.
What is Metadata?
Metadata is data that describes other data.
What is Renormalization?
Renormalization is reverting denormalized data back to normalized form.
What is Aggregation?
Aggregation is summarizing data for reporting or analysis.
What is Audit Information?
Audit Information is data used to verify integrity and accuracy.
What are Row Counts?
Row Counts refer to the total number of rows in a dataset.
What is Null Conversion?
Null Conversion transforms various null representations into a standard form.
What is Data Augmentation?
Data augmentation is a statistical technique that creates new data from existing data to add to data records.
What does the architecture of a database system describe?
The internal components and the relationships between components
This includes understanding how different parts of the database system interact with each other.
What is the role of the query processor in a database system?
- Interprets queries
- creates a plan to modify the database or retrieve data
- Returns query results to the application
The query processor optimizes queries for efficiency.
How does the storage manager function in a database system?
Translates the query processor instructions into low-level file system commands that modify or retrieve data
It also uses indexes to quickly locate data due to the varying sizes of databases.
What is the function of the transaction manager?
Ensures transactions are properly executed and prevents conflicts between concurrent transactions
It also restores the database to a consistent state in case of failure.
What is contained in the log file of a database?
A complete record of all inserts, updates, and deletes processed by the database
The transaction manager writes log records before applying changes for recovery purposes.
What is the purpose of the catalog in a database system?
A directory of tables, columns, indexes, and other database objects
Other components use catalog information to process and execute queries.
True or False: All database systems support transactions.
False
Some database systems do not support transactions and therefore lack a transaction manager.
Fill in the blank: The database sizes range from _______ to many terabytes.
megabytes
What does the transaction manager do in the event of a failure?
Uses log records to restore the database
This ensures that the database can return to a consistent state after a failure.
What happens to a database transaction if a server failure occurs before completion?
The database must reverse any partial changes
This is necessary to maintain data integrity.
What is query optimization in the context of a database?
The process of ensuring the most efficient instructions are executed on the data
It is performed by the query processor.
What might vary significantly between different database systems?
Capabilities and component details
For example, the physical structure of data on storage media affects the storage managerβs implementation.
What are the main components of a database system?
A query processor, storage manager, transaction manager, log, and catalog.
What role does the query processor play in a database system?
It receives queries from applications and performs query optimization using information from the catalog.
How does the storage manager interact with the query processor?
It translates the query processorβs instructions into file-system commands and uses an index to locate requested data quickly.
What does the transaction manager do in a database system?
It logs insert, update, and delete queries, sending the result back to the application.
Does the query processor has direct access to the database data on storage media.
No, all access to the database data must go through the storage manager
Without query optimization, the storage manager cannot $ ?
- The storage manager can retrieve the data with efficient or inefficient instructions
- Query optimization ensures the most efficient instructions are sent to the storage manager.
The catalog allows the storage manager to $ ?
Catalogs allow quick locations to the requested data.
What must be done in order to correctly restore database changes? 2
- All inserts, updates, and deletes must be logged in order to correctly restore database changes.
- Reads are not needed to restore database changes, however, and thus are not logged.
What is metadata in the context of a database?
Data about the database, such as column names and the number of rows in each table.
Where is metadata stored in a database system?
In the catalog.
The _______ helps locate data quickly by providing information about where on the storage media certain pieces of data can be found.
[index]
What types of queries does the transaction manager log?
Insert, update, and delete queries.
What is the purpose of query optimization?
To ensure the most efficient instructions are sent to the storage manager.
What is a concurrent execution?
- Concurrent execution is when multiple processes are active at the same time, rather than one after the other.
- It can also refer to the execution of multiple transactions at the same time
What is query optimization?
A crucial aspect of database management focused on enhancing the efficiency and speed of data retrieval operations.
It involves techniques and strategies aimed at minimizing resources for executing queries while maximizing result quality.
What is the first step in the query optimization process?
The analysis of queries to understand their execution plans.
Execution plans are the specific steps the database engine takes to retrieve requested data.
How does indexing contribute to query optimization?
Allows for quicker data access and reduces the amount of data the database must scan.
Proper indexing speeds up response times significantly.
What are optimal join methods in query optimization?
Techniques for combining data from multiple tables, including:
* Nested loops
* Hash joins
* Merge joins
Selecting the appropriate join method based on data characteristics is vital for performance.
Why are statistics about data distribution and cardinality important?
They help the query optimizer make better decisions regarding execution plans.
Keeping these statistics updated is essential for maintaining optimal performance as data evolves.
What is query rewriting?
A strategy where queries are reformulated to improve performance without altering the returned results.
This can include simplifying expressions and consolidating multiple queries.
How do caching mechanisms enhance query performance?
By storing previously retrieved results to quickly return cached results instead of recalculating them.
This saves time and resources during query execution.
What is the overall goal of query optimization?
To ensure that queries run as efficiently as possible, improving performance and user satisfaction in data-driven applications.
True or False: Query optimization involves only the analysis of queries.
False.
Query optimization also involves techniques like indexing, join methods, query rewriting, and caching.
What is the memory hierarchy?
A structured arrangement of different types of memory in a computer system, organized by speed and cost.
What are registers in the memory hierarchy?
Small, fast storage locations within the CPU that hold the most frequently used data and instructions for quick access.
What is the function of the L1 cache?
Stores copies of frequently accessed data from main memory, allowing for quick retrieval.
What is the difference between L1 and L2 cache?
L1 is on-chip and smaller, while L2 is off-chip and larger, serving as an intermediary between L1 cache and main memory.
What is the primary storage area in a computer called?
Main Memory (DRAM).
What types of devices are considered local secondary storage?
Hard Drives (HDDs) and Solid State Drives (SSDs).
What is remote secondary storage?
Storage located on networks, often accessed via the internet, providing access to large amounts of data stored off-site.
Fill in the blank: Each level in the memory hierarchy provides a trade-off between _______ and cost.
speed.
What is the function of local secondary storage?
Holds data and programs that are not currently in use; slower than main memory.
What is the role of access patterns in memory hierarchy?
Programs are designed to access data in a way that takes advantage of the hierarchy, using faster memory for frequently accessed data.
What manages database connections and compiles SQL queries?
The query processor
The query processor is responsible for managing connections and translating SQL into low-level storage engine instructions.
What authorizes a user to access a database?
A connection
Connections are essential for user access to databases.
What is MySQL Workbench?
A desktop application that enables database administrators and users to interact with the database
MySQL Workbench does not directly interact with the query processor.
How does the file system interact with the query processor?
Indirectly, via the storage engine
The file system does not interact directly with the query processor.
Is the query processor aware of data addresses and structures on storage media?
False
The query processor executes instructions compiled from SQL queries without knowledge of data addresses.
What does the storage engine execute?
Low-level instructions generated by the query processor from SQL queries
This execution is crucial for the actual data operations within the database.
What do storage engines that support transactions manage?
Concurrency and recovery
This management is essential for maintaining data integrity in transactional systems.
What are the five categories of utility programs?
Installation, client, administrative, developer, miscellaneous tools
How many tools do utility programs include approximately?
Approximately 30 tools
Who are the primary users of utility programs?
Database administrators or programmers
What is one example function of utility programs?
Upgrade existing databases to a new MySQL release
Other functions include backing up databases, importing data, inspecting log files, and administering database servers.
Fill in the blank: Utility programs are intended primarily for _______.
[database administrators or programmers]
What are application programs built by?
Programmers using MySQL
Are application programs part of MySQL?
No
What is Workbench?
A desktop application with broad capabilities
What can Workbench do?
Assign data access privileges and monitor database performance
Many Workbench capabilities are administrative functions.
What is mysql_config_editor?
A tool in the administrative group of utility programs
What does mysql_config_editor help database administrators do?
Configure MySQL
Are MySQL APIs older or newer than Connectors?
A connection is a link between tools and the query processor.
Older
What are many Connectors built on top of?
The C API
What does the query parser do?
Checks each SQL statement for correct syntax and converts the statement to an internal representation
The query parser is essential for ensuring that SQL commands are valid before execution.
What is the role of the connection manager?
Processes connection requests from the tools layer
The connection manager is responsible for establishing links between the user applications and the database.
What information does a database connection include?
- Database name
- Server address
- Logon credentials
These elements are necessary for successful communication with the database.
What is the cache in the context of database management?
An area of main memory that contains recent queries, execution plans, and results for possible reuse
Caching improves performance by reducing the need to reprocess frequent queries.
What is the function of the cache manager?
Stores reusable information in main memory
The cache manager optimizes access to frequently used data, enhancing overall system efficiency.
Fill in the blank: The cache is an area of main memory that contains recent queries, execution plans, and _______.
[results]
True or False: The connection manager handles the actual execution of SQL statements.
False
The connection manager is involved in managing connections, not executing queries.
What does the file system layer consist of?
Data stored on storage media and organized in files
The file system layer is integral to managing how data is stored and organized in a database.
What are the three types of data contained in a database file system?
- User data
- Log files
- Data dictionary
These three types of data serve specific functions within the database management system.
What does user data include in a database?
- Tables
- Indexes
User data is crucial for storing actual information and facilitating efficient data retrieval.
What is the purpose of log files in a database?
To contain a detailed, sequential record of each change applied to a database
Log files are essential for the recovery system to restore data after failures.
How does the recovery system utilize log files?
To restore data in the event of a transaction, system, or storage media failure
Log files help ensure data integrity and reliability.
What is a catalog in the context of a relational database?
A directory of tables, columns, keys, indexes, and other objects
The catalog, or data dictionary, is critical for managing database structure.
What term does MySQL use for its data dictionary?
βData dictionaryβ
MySQLβs implementation includes a structured approach to managing metadata.
How many tables does the MySQL data dictionary contain?
Roughly 30 tables
These tables provide comprehensive information about the database schema.
What does the βtablesβ data dictionary table describe?
All tables
This table provides metadata about the structure of each table in the database.
What information does the βtable statsβ data dictionary table contain?
Table statistics, such as the number of rows in each table
This information is useful for optimization and performance tuning.
What does the βcolumnsβ data dictionary table describe?
All columns
This table details the structure and attributes of each column in the database.
What information does the βforeign keysβ data dictionary table provide?
All foreign keys
This table is important for understanding relationships between tables.
What does the βindexesβ data dictionary table describe?
All indexes
Indexes are crucial for improving query performance.
What does the βroutinesβ data dictionary table contain?
All stored procedures and stored functions
This table helps manage executable database logic.
What information does the βtriggersβ data dictionary table describe?
All triggers
Triggers are automated actions that occur in response to certain events in the database.
Can data dictionary tables be accessed directly with SELECT, INSERT, UPDATE, and DELETE queries?
No
Direct access is restricted to maintain the integrity of the data dictionary.
How can the contents of data dictionary tables be accessed?
Indirectly
This is done through specific commands like SHOW, which compile as SELECT queries.
What does the SHOW COLUMNS command generate?
A SELECT query against the columns table
This command is used to retrieve column information for a specific table.
What type of query does CREATE generate against dictionary tables?
INSERT
This reflects the addition of new objects to the database schema.
What type of query does ALTER generate against dictionary tables?
UPDATE
This reflects modifications to existing database objects.
What type of query does DROP generate against dictionary tables?
DELETE
This reflects the removal of objects from the database schema.
How can multiple nodes can be updated independently with local transactions?
- The transaction coordinator notifies participating nodes of required updates.
- Nodes commit immediately and confirm with the transaction coordinator.
- If a node is unavailable, the transaction coordinator repeats the update message until confirmation is received.
What are updates done in a distributed transactions and in separate local transactions called?
- Updates in separate local transactions are asynchronous.
- Updates in a distributed transaction are synchronous.
What must most distributed databases always function as?
Partition-tolerant
This means they can continue to operate even when there are network issues.
In a distributed database, what do consistency and availability guarantee?
Either consistency or availability, but not both
This is a key principle in distributed database design.
What does a consistent database conform to?
All rules at all times
Consistency ensures that data integrity is maintained across all nodes.
Give an example of a rule that may govern data in a distributed database.
Foreign key values on one node must match primary key values on another node
This ensures relational integrity across nodes.
What must be true about copies of data on multiple nodes in a consistent database?
They must be identical
This is crucial for maintaining data consistency.
What must live nodes do in an available database?
Respond to queries at all times
This means that even if some nodes are down, the database remains operational.
What occurs when a network error prevents nodes from communicating?
A network partition forms
This can disrupt the functioning of distributed databases.
Why do distributed databases occasionally experience network partitions?
Because nodes are connected by wide area networks that occasionally fail
This vulnerability is inherent in distributed systems.
What characterizes a partition-tolerant database?
It continues to function when a network partition occurs
This is essential for maintaining operational integrity in distributed systems.
What is the process for updating replicas in a database on a single node?
It is a simple process where either storage solutions handle replication internally or the database updates all replicas in a single local transaction.
What is required for synchronizing replicas in a database?
Synchronizing replicas does not necessitate any special database features.
What complicates the process of updating replicated data in a distributed database?
It is more complicated because updating all replicas in a distributed transaction is generally slower and fails if any replica is down.
What is the primary/secondary method in managing replicated data?
It assigns one node as the primary where all updates are made initially, and secondary nodes are updated afterward.
What happens if the primary node fails in the primary/secondary method?
The system automatically identifies a new primary node to maintain availability.
How does the group replication method work?
Updates can be applied to any node within a group, which shares transaction details with other nodes before committing.
What occurs if a conflict arises during the group replication method?
An algorithm decides which transaction will proceed and which will be rolled back.
What can influence the complexity of the algorithm used in group replication?
It can range from simple, allowing the first transaction to commit, to more complex solutions.
What happens during a network partition in group replication?
Processing will be paused temporarily.
How does participation activity differ among relational databases?
Support for primary/secondary and group replication methods can differ.
Which database supports both primary/secondary and group replication methods?
MySQL with the InnoDB storage engine.
What advantage does primary/secondary replication provide for read queries?
Each node can handle read queries independently without needing to connect with other nodes across the wide area network.
What is the process of reading data from operational systems and writing it to a temporary database called?
βextractionβ
This is the initial step in the data processing pipeline.
What is βcleansingβ in the context of data management?
Correcting errors in operational data and converting to a standard format
Cleansing ensures data quality and consistency.
What issue arises from data in different operational systems?
Incompatible or missing keys
This can complicate data integration efforts.
What is one example of data integration?
Creating uniform primary and foreign keys
This facilitates consistent data relationships across systems.
What is the term for converting data from a design optimized for operations to a design optimized for analytics?
βrestructuringβ
Restructuring is crucial for effective data analysis.
What do dimensional designs in data warehouses usually include?
Date and time dimension tables
These tables are essential for tracking historical data.
How many rows would the date dimension table contain if an organization tracks data for 100 years?
36,500 rows
This is calculated as 100 years x 365 days per year.
How many rows does the time dimension table contain?
1,440 rows
This is calculated as 24 hours x 60 minutes per hour.
What do fact tables contain in relation to date and time dimensions?
Foreign keys referencing date, time, or both dimensions
This establishes the time of a fact.
What is a dimension hierarchy?
A sequence of columns where each column has a one-many relationship to the next column
This structure helps in organizing data in a meaningful way.
What is a data lake?
An analytic database of raw, unprocessed data copied from multiple data sources.
Data lakes share characteristics of both data warehouses and federated databases.
What role does the global query processor play in a federated database?
It decomposes federated queries and merges the results into a federated result.
Individual databases process decomposed queries independently.
When is referential integrity enforced in InfoSphere?
When primary and foreign keys are in the same database.
Referential integrity is not enforced when they are in different databases.
What is a federated database?
A collection of two or more participating databases under a coordinating software layer.
The participating databases are autonomous and heterogeneous.
Define an autonomous database.
A database that operates independently of other participating databases.
It can be administered and queried as if it were not part of a federated database.
What does heterogeneous mean in the context of databases?
Databases that run under different database systems or have incompatible schema.
This may include inconsistent primary and foreign keys or similar tables with different designs.
What is the function of middleware in a federated database?
Middleware serves as the coordinating software layer between application programs and database software.
Many federated database middleware products are available.
Name two examples of federated database middleware products.
- InfoSphere Federated Server from IBM
- WebLogic Server from Oracle
What is a global catalog in a federated database?
A directory of participating database objects, such as tables, columns, and indexes.
It is one of the components found in most federated database middleware products.
What does a database wrapper do?
Converts decomposed queries to the appropriate syntax for each participating database.
True or False: A federated database can include databases with compatible schemas only.
False
Federated databases may include heterogeneous databases with incompatible schemas.
What is the typical nature of web click data?
Rapidly collected and high volume
How is social media data typically characterized?
Unstructured
What type of data analysis is often appropriate for high-volume, unstructured data?
Data lake supported by data scientists
What solution can make three operational databases appear as one?
Federated database
What types of data are usually highly structured and suitable for fact tables?
Sales revenue and manufacturing costs
What do analysis of revenue and cost by product line and region suggest?
Dimension tables
What is required for quarterly reports?
Periodic extracts at a fixed period
What do the requirements for periodic extracts suggest?
Data warehouse or data mart
What can easily handle data that is local to one software layer and not persisted on storage media?
Embedded database
When is an embedded database not appropriate?
If the data must be shared or persisted
