D426 WGU Data Management - Comprehensive Study Notes
D426 WGU Data Management - Comprehensive
Study Notes
Read through and take notes. Red underlined areas are very important but surrounding information
could also be tested on as well so it's important to at least have an understanding of each area.
1.1 DATABASE BASICS
Data
Data is numeric, textual, visual, or audio information that describes real-world
systems. Data is collected and processed to aid in a variety of tasks, such as
forecasting weather, analyzing financial investments, and tracking the global
spread of pandemics.
Data can vary in several important ways:
• Scope. The amount of data produced and collected can vary. Ex: A small
business might track an inventory of a few thousand items, but a large
commerce website might track billions of items.
• Format. Data may be produced as numbers, text, image, audio, or video. Ex:
A phone's proximity sensor generates raw numbers, and a satellite captures
images.
• Access. Some data sources are private while others are made publicly
available. Ex: A retail company may use private customer data to discover
purchasing behavior patterns, but a government may be required by law to
share certain data sets.
Databases
A database is a collection of data in a structured format. In principle, databases
can be stored on paper or even clay tablets. In practice, however, modern
databases are invariably stored on computers. The database structure ensures that
similar data is stored in a standardized manner.
• A database system, also known as a database management system or
DBMS, is software that reads and writes data in a database. Database
systems ensure data is secure, internally consistent, and available at all
times. These functions are challenging for large databases with many users,
so database systems are complex.
• A query is a request to retrieve or change data in a database. A query
language is a specialized programming language, designed specifically for
database systems. Query languages read and write data efficiently
• A database application is software that helps business users interact with
database systems. Many databases are complex, and most users are not
familiar with query languages. Consequently, direct database access is
usually not feasible. Instead, programmers write applications to simplify the
user experience and ensure data access is efficient and secure.
Database roles
People interact with databases in a variety of roles:
D426 WGU Data Management - Comprehensive Study Notes
,D426 WGU Data Management - Comprehensive Study Notes
• A database administrator is responsible for securing the database system
against unauthorized users. A database administrator enforces procedures
for user access and database system availability.
• A database designer determines the format of each data element and the
overall database structure. Database designers must balance several
priorities, including storage, response time, and support for rules that
govern the data. Since these priorities often conflict, database design is
technically challenging.
• A database programmer develops computer programs that utilize a
database. Database programmers write applications that combine database
query languages and general-purpose programming languages. Query
languages and general-purpose languages have significant differences, so
database programming is a specialized challenge.
• A database user is a consumer of data in a database. Database users
request, update, or use stored data to generate reports or information.
Database users usually access the database via applications but can also
submit queries directly to the database system.
1.2 DATABASE SYSTEMS
File systems and database systems
Small databases that are shared by one or two users can be managed in a text file
or spreadsheet. Text files and spreadsheets are inadequate, however, as databases
grow in size, complexity, and use. Large, complex databases that are shared by
many users have special requirements:
• Performance. When many users and applications simultaneously access
large databases, query response time degrades rapidly. Database systems
maintain fast response times by structuring data properly on storage media
and processing queries efficiently.
• Authorization. Many database users should have limited access to specific
tables, columns, or rows of a database. Database systems authorize
individual users to access specific data.
• Security. Database systems ensure authorized users only access
permissible data. Database systems also protect against hackers by
encrypting data and restricting access.
• Rules. Database systems ensure data is consistent with structural and
business rules. Ex: When multiple copies of data are stored in different
locations, copies must be synchronized as data is updated. Ex: When a
course number appears in a student registration record, the course must
exist in the course catalog.
• Recovery. Computers, database systems, and individual transactions
occasionally fail. Database systems must recover from failures and restore
the database to a consistent state without loss of data.
A transaction is a group of queries that must be either completed or rejected as a
whole. Execution of some, but not all, queries results in inconsistent or incorrect
data. Ex: A debit-credit transaction transfers funds from one bank account to
D426 WGU Data Management - Comprehensive Study Notes
,D426 WGU Data Management - Comprehensive Study Notes
another. The first query removes $100 from one account and the second query
deposits $100 in another account. If the first query succeeds but the second fails,
$100 is mysteriously lost. The transaction must process either both queries or
neither query.
When processing transactions, database systems must:
• Ensure transactions are processed completely or not at all. A
computer or application might fail while processing a transaction. When
failing to process a transaction, the database system must reverse partial
results and restore the database to the values prior to the transaction.
• Prevent conflicts between concurrent transactions. When multiple
transactions access the same data at the same time, a conflict may occur.
Ex: Sam selects a seat on a flight. Maria purchases the same seat in a
separate transaction before Sam completes his transaction. When Sam clicks
the 'purchase' button, his seat is suddenly unavailable.
• Ensure transaction results are never lost. Once a transaction completes,
transaction results must always be saved on storage media, regardless of
application or computer failures.
Architecture
The architecture of a database system describes the internal components and the
relationships between components. At a high level, the components of most
database systems are similar:
• The query processor interprets queries, creates a plan to modify the
database or retrieve data, and returns query results to the application. The
query processor performs query optimization to ensure the most efficient
instructions are executed on the data.
• The storage manager translates the query processor instructions into low-
level file-system commands that modify or retrieve data. Database sizes
range from megabytes to many terabytes, so the storage manager uses
indexes to quickly locate data.
• The transaction manager ensures transactions are properly executed. The
transaction manager prevents conflicts between concurrent transactions.
The transaction manager also restores the database to a consistent state in
the event of a transaction or system failure.
• The log is a file containing a complete record of all inserts, updates, and
deletes processed by the database. The transaction manager writes log
records before applying changes to the database. In the event of a failure,
the transaction manager uses log records to restore the database.
• The catalog, also known as a data dictionary, is a directory of tables,
columns, indexes, and other database objects. Other components use catalog
information to process and execute queries.
Metadata is data about the database, such as column names and the number of
rows in each table.
D426 WGU Data Management - Comprehensive Study Notes
, D426 WGU Data Management - Comprehensive Study Notes
Products
Most leading database systems are relational. A relational database stores data
in tables, columns, and rows, similar to a spreadsheet. All data in a column has the
same format. All data in a row represents a single object, such as a person, place,
product, or activity.
All relational database systems support the SQL query language. SQL stands for
Structured Query Language and includes statements that read and write data,
create and delete tables, and administer the database system.
Relational systems are ideal for databases that require an accurate record of every
transaction, such as banking, airline reservation systems, and student records. The
growth of the internet in the 1990s generated massive volumes of online data,
called big data, often with poorly structured or missing information. Relational
systems were not initially designed for big data and, as a result, many non-
relational systems have appeared since 2000. The newer non-relational systems
are called NoSQL, for 'not only SQL', and are optimized for big data.
Prior to 2000, most database systems were commercial products, developed by for-
profit companies and licensed for a fee. Since 2000, an alternative licensing model,
called open source, has become popular. Open source software is software that
anyone can inspect, copy, and modify with no licensing fee.
DB-Engines
Product Sponsor Type License rank
(May 2020)
Oracle
Oracle Relational Commercial 1
Database
MySQL Oracle Relational Open source 2
SQL Server Microsoft Relational Commercial 3
PostgreSQL
Global
PostgreSQL Relational Open source 4
Development
Group
MongoDB MongoDB NoSQL Open source 5
1.3 QUERY LANGUAGES
Common queries
A database system responds to queries written in a query language. A query is a
command for a database that typically inserts new data, retrieves data, updates
data, or deletes data from a database. A query language is a computer
programming language for writing database queries.
D426 WGU Data Management - Comprehensive Study Notes
D426 WGU Data Management - Comprehensive
Study Notes
Read through and take notes. Red underlined areas are very important but surrounding information
could also be tested on as well so it's important to at least have an understanding of each area.
1.1 DATABASE BASICS
Data
Data is numeric, textual, visual, or audio information that describes real-world
systems. Data is collected and processed to aid in a variety of tasks, such as
forecasting weather, analyzing financial investments, and tracking the global
spread of pandemics.
Data can vary in several important ways:
• Scope. The amount of data produced and collected can vary. Ex: A small
business might track an inventory of a few thousand items, but a large
commerce website might track billions of items.
• Format. Data may be produced as numbers, text, image, audio, or video. Ex:
A phone's proximity sensor generates raw numbers, and a satellite captures
images.
• Access. Some data sources are private while others are made publicly
available. Ex: A retail company may use private customer data to discover
purchasing behavior patterns, but a government may be required by law to
share certain data sets.
Databases
A database is a collection of data in a structured format. In principle, databases
can be stored on paper or even clay tablets. In practice, however, modern
databases are invariably stored on computers. The database structure ensures that
similar data is stored in a standardized manner.
• A database system, also known as a database management system or
DBMS, is software that reads and writes data in a database. Database
systems ensure data is secure, internally consistent, and available at all
times. These functions are challenging for large databases with many users,
so database systems are complex.
• A query is a request to retrieve or change data in a database. A query
language is a specialized programming language, designed specifically for
database systems. Query languages read and write data efficiently
• A database application is software that helps business users interact with
database systems. Many databases are complex, and most users are not
familiar with query languages. Consequently, direct database access is
usually not feasible. Instead, programmers write applications to simplify the
user experience and ensure data access is efficient and secure.
Database roles
People interact with databases in a variety of roles:
D426 WGU Data Management - Comprehensive Study Notes
,D426 WGU Data Management - Comprehensive Study Notes
• A database administrator is responsible for securing the database system
against unauthorized users. A database administrator enforces procedures
for user access and database system availability.
• A database designer determines the format of each data element and the
overall database structure. Database designers must balance several
priorities, including storage, response time, and support for rules that
govern the data. Since these priorities often conflict, database design is
technically challenging.
• A database programmer develops computer programs that utilize a
database. Database programmers write applications that combine database
query languages and general-purpose programming languages. Query
languages and general-purpose languages have significant differences, so
database programming is a specialized challenge.
• A database user is a consumer of data in a database. Database users
request, update, or use stored data to generate reports or information.
Database users usually access the database via applications but can also
submit queries directly to the database system.
1.2 DATABASE SYSTEMS
File systems and database systems
Small databases that are shared by one or two users can be managed in a text file
or spreadsheet. Text files and spreadsheets are inadequate, however, as databases
grow in size, complexity, and use. Large, complex databases that are shared by
many users have special requirements:
• Performance. When many users and applications simultaneously access
large databases, query response time degrades rapidly. Database systems
maintain fast response times by structuring data properly on storage media
and processing queries efficiently.
• Authorization. Many database users should have limited access to specific
tables, columns, or rows of a database. Database systems authorize
individual users to access specific data.
• Security. Database systems ensure authorized users only access
permissible data. Database systems also protect against hackers by
encrypting data and restricting access.
• Rules. Database systems ensure data is consistent with structural and
business rules. Ex: When multiple copies of data are stored in different
locations, copies must be synchronized as data is updated. Ex: When a
course number appears in a student registration record, the course must
exist in the course catalog.
• Recovery. Computers, database systems, and individual transactions
occasionally fail. Database systems must recover from failures and restore
the database to a consistent state without loss of data.
A transaction is a group of queries that must be either completed or rejected as a
whole. Execution of some, but not all, queries results in inconsistent or incorrect
data. Ex: A debit-credit transaction transfers funds from one bank account to
D426 WGU Data Management - Comprehensive Study Notes
,D426 WGU Data Management - Comprehensive Study Notes
another. The first query removes $100 from one account and the second query
deposits $100 in another account. If the first query succeeds but the second fails,
$100 is mysteriously lost. The transaction must process either both queries or
neither query.
When processing transactions, database systems must:
• Ensure transactions are processed completely or not at all. A
computer or application might fail while processing a transaction. When
failing to process a transaction, the database system must reverse partial
results and restore the database to the values prior to the transaction.
• Prevent conflicts between concurrent transactions. When multiple
transactions access the same data at the same time, a conflict may occur.
Ex: Sam selects a seat on a flight. Maria purchases the same seat in a
separate transaction before Sam completes his transaction. When Sam clicks
the 'purchase' button, his seat is suddenly unavailable.
• Ensure transaction results are never lost. Once a transaction completes,
transaction results must always be saved on storage media, regardless of
application or computer failures.
Architecture
The architecture of a database system describes the internal components and the
relationships between components. At a high level, the components of most
database systems are similar:
• The query processor interprets queries, creates a plan to modify the
database or retrieve data, and returns query results to the application. The
query processor performs query optimization to ensure the most efficient
instructions are executed on the data.
• The storage manager translates the query processor instructions into low-
level file-system commands that modify or retrieve data. Database sizes
range from megabytes to many terabytes, so the storage manager uses
indexes to quickly locate data.
• The transaction manager ensures transactions are properly executed. The
transaction manager prevents conflicts between concurrent transactions.
The transaction manager also restores the database to a consistent state in
the event of a transaction or system failure.
• The log is a file containing a complete record of all inserts, updates, and
deletes processed by the database. The transaction manager writes log
records before applying changes to the database. In the event of a failure,
the transaction manager uses log records to restore the database.
• The catalog, also known as a data dictionary, is a directory of tables,
columns, indexes, and other database objects. Other components use catalog
information to process and execute queries.
Metadata is data about the database, such as column names and the number of
rows in each table.
D426 WGU Data Management - Comprehensive Study Notes
, D426 WGU Data Management - Comprehensive Study Notes
Products
Most leading database systems are relational. A relational database stores data
in tables, columns, and rows, similar to a spreadsheet. All data in a column has the
same format. All data in a row represents a single object, such as a person, place,
product, or activity.
All relational database systems support the SQL query language. SQL stands for
Structured Query Language and includes statements that read and write data,
create and delete tables, and administer the database system.
Relational systems are ideal for databases that require an accurate record of every
transaction, such as banking, airline reservation systems, and student records. The
growth of the internet in the 1990s generated massive volumes of online data,
called big data, often with poorly structured or missing information. Relational
systems were not initially designed for big data and, as a result, many non-
relational systems have appeared since 2000. The newer non-relational systems
are called NoSQL, for 'not only SQL', and are optimized for big data.
Prior to 2000, most database systems were commercial products, developed by for-
profit companies and licensed for a fee. Since 2000, an alternative licensing model,
called open source, has become popular. Open source software is software that
anyone can inspect, copy, and modify with no licensing fee.
DB-Engines
Product Sponsor Type License rank
(May 2020)
Oracle
Oracle Relational Commercial 1
Database
MySQL Oracle Relational Open source 2
SQL Server Microsoft Relational Commercial 3
PostgreSQL
Global
PostgreSQL Relational Open source 4
Development
Group
MongoDB MongoDB NoSQL Open source 5
1.3 QUERY LANGUAGES
Common queries
A database system responds to queries written in a query language. A query is a
command for a database that typically inserts new data, retrieves data, updates
data, or deletes data from a database. A query language is a computer
programming language for writing database queries.
D426 WGU Data Management - Comprehensive Study Notes
