Data Management - Foundations - D426 WGU

Data

Numeric, textual, visual, or audio information that describes real-world systems. It is collected and processed to aid in a variety of tasks.

Analog Data

Most common before computers. Audio was recorded as vibrations impressed on vinyl disks.

Digital Data

Most common today. Encoded as 0s and 1s on electronic magnetic data.

Database

A collection of data in structured format. In principle, can be stored on paper or even clay tablets.

Database System (DBMS)

Software that reads and writes in a database.

Query

A request to retrieve or change data in a database. Typically inserts new data, retrieves data, updates, data, or deletes data from a database.

Query Language

Specialized programming language designed specifically for database systems. Reads and writes data efficiently.

Database Application

Software that HELPS business users interact with database systems.

Database Administrator

What role am I?

Responsible for securing the database system against unauthorized users. They enforce procedures for user access and database system availability.

Database Designer

What role am I?

Determines the format of each data element AND the overall database STRUCTURE. Must balance priorities like including storage, response time, and support for rules that govern the data.

Database Programmer

What role am I?

Develop programs that utilize a database and write applications that combine query languages and general purpose programming language.

Database User

What role am I?

A consumer of data in a database. They request, update, or use stored data to generate reports or information.

Transaction

A group of queries that MUST be completed or rejected as a whole.

Architecture

Describes the internal components AND the relationships between components.

Query Processor

Interprets queries, creates a plan to modify the database or retrieve the data, and returns query results to the application. Performs query optimization.

Query Optimization

Ensures efficient instructions are executed on the data from the Query Processor.

Storage Manager

Translates the query processor instructions into low-level file-system commands that modify OR retrieve data. Uses indexes to quickly locate data.

Transaction Manager

Ensures transactions are properly executed. Prevents conflicts between concurrent transactions. Also restores the database to a consistent state in the event of a failure.

Log

A file containing a complete record of ALL inserts, updates, and deletes processed by the database. The records can be used to restore a database.

Catalog/Data Dictionary/Repository

A directory of tables, columns, indexes, and other database objects.

Relational Database

Stores data in tables, columns, and rows, SIMILAR to a spreadsheet. It also supports SQL. Made up of relations.

NoSQL

A new generation of DBMS that is NOT based on the traditional relational database model.

CRUD

Acronym for actions that can be performed by a query.

SQL Statement

A database command such as a query that inserts, retrieves, updates, or deletes data. Composed of one or more clauses.

INSERT

Inserts rows into a table.

SELECT

Retrieves data from a table.

UPDATE

Modifies data in a table.

DELETE

Deletes rows from a table.

CREATE TABLE

Creates a new table by specifying the table and column names.

Data Type

Indicates the TYPE of data that can be stored in a field of a column.

Data Independence

Allows database administrators to improve query performance by changing the organization of data on storage devices, without affecting query results or changes to programs.

Database Design

A specification of database objects such as tables, columns data types, and indexes. Also refers to the process used to develop the specifications.

Metadata

Data ABOUT the database, such as column names and the number of rows in each table.

Database Design - Analysis

This phase specifies database requirements without regard to a specific database system. May also be referred to as conceptual design, entity-relationship modeling, and requirements definition.

Database Design - Logical Design

This phase implements database requirements in a specific database system. Depicted in a table diagram.

Database Design - Physical Design

This phase adds indexes and specifies HOW tables are organized on storage media. Specified with SQL statements.

Key

A column used to identify individual rows of a table. Used to refer to only one specific row or record.

Application Programming Language (API)

A library of procedures or classes that links a host programming language to a database.

Schema

The structure of tables in the database.

Foreign Key

What a primary key from one table is called when it is referenced in another table. Important when starting to build relationships between tables. Refers to a primary key.

One-to-Many

This is the most common relationship. Associates ONE record in ONE table with MULTIPLE records in another table.

One-to-One

Associates only ONE record on one table with ONE record on another table.

Data Manipulation Language (DML)

What language am I?

Allows users to access and modify the data to MAKE queries, and GENERATE reports. Interacting with the data.

Data Definition Language (DDL)

What language am I?

Allows a database administrator to DEFINE the database structure, schema, and subschema.

Data Control Language (DCL)

What language am I?

Commands that CONTROL a database. Includes administering privileges and committing data.

MySQL Community/MySQL Server

Is a FREE EDITION. Includes a complete set of database services and tools, and is suitable for non-commercial applications such as education.

MySQL Enterprise

PAID EDITION for managing commercial databases. Includes MySQL Server AND additional administrative applications.

MySQL Command-Line Client

A text interface included in the MySQL Server download. Allows developers to connect to the database server, perform administrative functions, and execute SQL statements.

MySQL Workbench

Installed with MySQL Server AND allows developers to EXECUTE SQL commands using an editor.

Database Model

A conceptual framework for database software containing 3 parts:

Data Structures

Operations

Rules

Relational Model

A database model based on mathematical principles with three parts:

Data Structures

Operations

Rules

Set

A collection of values or elements with NO INHERENT order / unordered. Denoted with braces {}.

Domain

A named set of possible database values, such as integers, dictionary words, or logical values (TRUE and FALSE).

Tuple

A finite sequence of values, each drawn from a fixed domain. Ordered in parentheses.

Relation

A NAMED set of tuples, all drawn from the same sequence of domains.

Relational Rules/Integrity Rules

Logical constraints that ensure data is valid and conforms to business policy. Governs the data.

Structural Rules

Relational rules that govern data in every relational database.

Business Rules

Relational rules specific to a particular database AND application. Discovered during database design.

Table

A collection of data organized as columns and rows. Has a name, a fixed sequence of columns, and a varying set of rows.

Column

Has a name and a data type.

Row

An unnamed sequence of values. Each value corresponds to a column and belongs to the column's data type.

Cell

A single column of a single row. In relational databases, each contains exactly one value.

Aggregate

Computes functions over multiple table rows, such as SUM and COUNT.

Join

A SELECT statement that combines data from two tables (left and right), into a single result. The tables are combined by comparing columns from the left and right tables.

Data Query Language (DQL)

What language am I?

Retrieves data from the database.

Data Transaction Language (DTL)

What language am I?

Manages database transactions.

Database System Instance

A single executing copy of a database system. Personal computers usually run just one.

DROP TABLE

This statement deletes a table, along with all the table's rows, from a database.

Operator

A symbol that computes a value from one or more other values called operands.

Unary

Operator that has ONE operand.

Binary

Operator that has TWO operands.

Condition

An expression that evaluates to a logical value.

NULL

Special value that represents either UNKNOWN or inapplicable data. Not the same as zero.

TRUNCATE

Deletes ALL ROWS from a table.

MERGE

Selects data from one table, called the source, and inserts the data to another table, called the target. If the primary keys of source and target rows match, the target values are updated to the corresponding values.

Primary Key

A column, or group of columns, used to identify a row. Usually the table's FIRST column.

Simple Primary Key

Consists of a single column.

Composite Primary Key

Consists of multiple columns and are denoted with parentheses ().

Referential Integrity

A relational rule that requires foreign key values are either fully NULL or match some primary key value.

CASCADE

Propagates primary key changes to foreign keys.

Constraint

A rule that governs allowable values in a database.

Column Constraint

Appears after the column name and data type in a CREATE TABLE statement. Govern values in a single column.

Table Constraint

Appears in a SEPARATE clause of a CREATE TABLE statement.

BETWEEN operator

This operator provides an alternative way to determine if a value is between two other values.

LIKE operator

When used in a WHERE clause, this operator matches text against a pattern using the two wildcard characters "%" and "_".

ORDER BY

This clause orders selected rows by one or more columns in ascending (alphabetical) order.

JOIN Clause

Determines how a join query handles unmatched rows.

INNER JOIN

Selects ONLY matching left and right table rows.

FULL JOIN

Selects ALL left and right table rows, regardless of match.

LEFT JOIN

Selects all LEFT table rows, but only matching right table rows.

RIGHT JOIN

Selects all RIGHT table rows, but only matching left table rows.

OUTER JOIN

ANY JOIN that selects unmatched rows, including, left, right, and full joins.

EQUIJOIN

Compares columns of two tables with the = operator.

NON-EQUIJOIN

Compares columns with an operator OTHER THAN =, such as < and >.

SELF-JOIN

Joins a table to ITSELF.

CROSS-JOIN

Combines two tables WITHOUT comparing columns.

DISTINCT clause

Used with a SELECT statement to return only unique or "distinct" values.

IN operator

This operator is used in a WHERE clause to determine if a value matches one of several values.

Subquery

Sometimes called a nested query or inner query, is a query within another SQL query.

Flattening

Replacing a subquery with an equivalent join.