D426 - Data Management Foundations - zyBook Terms & Definitions

Data

Numeric, textual, visual, or audio information that describes real-world systems.

Analog

Historically, data was mostly _______________, encoded as continuous variations on various physical media.

Digital

Format data is mostly in today, encoded as zeros and ones on electronic and magnetic media.

Database

A collection of data in a structured format. In principle, they can be stored on paper or even clay tablets. In practice, however, modern versions are invariably stored on computers.

Database system / database management system / DBMS

Software that reads and writes data in a database. Ensures data is secure, internally consistent, and available at all times. These functions are challenging for large databases with many users, so these systems are complex.

Query language

A specialized programming language, designed specifically for database systems.

Database application

Software that helps business users interact with database systems.

Database administrator

Person who is responsible for securing the database system against unauthorized users. They enforce procedures for user access and database system availability.

Database designer

Person who determines the format of each data element and the overall database structure. They 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.

Database programmer

Person who develops computer programs that utilize a database.

Database user

Person who is a consumer of data in a database. They request, update, or use stored data to generate reports or information. They also usually access the database via applications but can also submit queries directly to the database system.

Transaction

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.

Architecture

Describes the internal components and the relationships between components of a database system.

Query processor

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

Query optimization

The query processor performs this to ensure the most efficient instructions are executed on the data.

Storage manager

Translates the query processor instructions into low-level file-system commands that modify or retrieve data.

Indexes

Used by the storage manager to quickly locate data.

Transaction manager

Ensures transactions are properly executed.

Log

A file containing a complete record of all inserts, updates, and deletes processed by the database.

Catalog / data dictionary

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

Relational database

Stores data in tables, columns, and rows, similar to a spreadsheet.

SQL

Stands for Structured Query Language and includes statements that read and write data, create and delete tables, and administer the database system.

Big data

The growth of the internet in the 1990s generated massive volumes of online data, often with poorly structured or missing information.

NoSQL

The newer non-relational systems, stands for 'not only SQL', and are optimized for big data.

Open source

Software that anyone can inspect, copy, and modify with no licensing fee.

Query

A command for a database that typically inserts new data, retrieves data, updates data, or deletes data from a database.

Query language

A computer programming language for writing database queries.

CRUD

What the four common queries are sometimes referred to as, an acronym for Create, Read, Update, and Delete data operations.

Structured Query Language / SQL

The standard query language of relational database systems.

Statement

SQL database command, such as a query that inserts, selects, updates, or deletes data.

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

What each column is assigned that indicates the format of column values. Can be numeric, textual, or complex.

Database design

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

Analysis

Phase that specifies database requirements without regard to a specific database system.

ER diagrams

Entities, relationships, and attributes are depicted in these diagrams.

Logical design

Phase that implements database requirements in a specific database system.

Key

A column used to identify individual rows of a table.

Table diagram

What the logical design is depicted in.

Schema

The logical design, as specified in SQL and depicted in a table diagram, is called a database __________.

Physical design

Phase that adds indexes and specifies how tables are organized on storage media.

Data independence

The principle that physical design never affects query results.

Application programming interface / API

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

MySQL

A leading relational database system sponsored by Oracle.

MySQL Community / MySQL Server

A free edition of MySQL.

MySQL Enterprise

A paid edition for managing commercial databases. Includes MySQL Server and additional administrative applications.

Root account

The administrative account that has full control of MySQL.

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.

Error code

What MySQL Server returns when an SQL statement is syntactically incorrect or the database cannot execute the statement.

MySQL Workbench

Installed with MySQL Server and allows developers to execute SQL commands using an editor.

Database model

A conceptual framework for database systems, with three parts:

1. Data structures - prescribe how data is organized.

2. Operations - manipulate data structures.

3. Rules - govern valid data.

Relational model

Database model based on a tabular data structure. The model was published in 1970 by E. F. Codd of IBM and released in commercial products around 1980. The data structure, operations, and rules are standardized in SQL, the universal query language of relational databases.

Big data

What the rise of the internet in the 1990s generated, characterized by unprecedented data volumes and rapidly changing data structures.

Set

An unordered collection of elements enclosed in braces.

Tuple

An ordered collection of elements enclosed in parentheses.

Table

Has a name, a fixed tuple of columns, and a varying set of rows.

Column

Has a name and a data type.

Row

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

Data type

A named set of values, from which column values are drawn.

Relational algebra

What these operations are collectively called and are the theoretical foundation of the SQL language.

Relational rules

Part of the relational model and govern data in every relational database.

Business rules

Based on business policy and specific to a particular database.

Constraints

Relational rules are implemented as SQL ____________________ and enforced by the database system.

Structured Query Language / SQL

A high-level computer language for storing, manipulating, and retrieving data.

Statement

A complete command in SQL composed of one or more clauses.

Clause

Groups SQL keywords like SELECT, FROM, and WHERE with table names like City, column names like Name, and conditions like Population > 100000.

Data Definition Language / DDL

Defines the structure of the database.

Data Query Language / DQL

Retrieves data from the database.

Data Manipulation Language / DML

Manipulates data stored in a database.

Data Control Language / DCL

Controls database user access.

Data Transaction Language / DTL

Manages database transactions.

Database system instance

A single executing copy of a database system. Personal computers usually run just one instance of a database system. Shared computers, such as computers used for cloud services, usually run multiple instances of a database system.

CREATE DATABASE DatabaseName

Creates a new database.

DROP DATABASE DatabaseName

Deletes a database, including all tables in the database.

USE DatabaseName

Selects a default database for use in subsequent SQL statements.

SHOW DATABASES

Lists all databases in the database system instance.

SHOW TABLES

Lists all tables in the default database.

SHOW COLUMNS FROM TableName

Lists all columns in the TableName table of the default database.

SHOW CREATE TABLE TableName

Shows the CREATE TABLE statement for the TableName table of the default database.

Table

Has a name, a fixed sequence of columns, and a varying set of rows.

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.

Empty table

A table without rows.

Data independence

What Rule 4 is called.

CREATE TABLE

Statement that creates a new table by specifying the table name, column names, and column data types.

DROP TABLE

Statement that deletes a table, along with all the table's rows, from a database.

ALTER TABLE

Statement that adds, deletes, or modifies columns on an existing table.

Data type

A named set of values from which column values are drawn.

Integer

Data types that represent positive and negative integers.

Decimal

Data types that represent numbers with fractional values.

Character

Data types that represent textual characters.

Date and time

Data types that represent date, time, or both. Some data types include a time zone or specify a time interval.

Binary

Data types that store data exactly as the data appears in memory or computer files, bit for bit.

Spatial

Data types that store geometric information, such as lines, polygons, and map coordinates.

Document

Data types that contain textual data in a structured format such as XML or JSON.

Signed

A number that may be negative.

Unsigned

An number that cannot be negative.