D426 Data Management - Foundation Objective Assessment Practice Exam

Database Application

Software that helps business users interact with database systems.

Database Administrator

The individual who is responsible for safeguarding the database system against unauthorized access and enforcing procedures for user access and system availability.

Database Designer

Determines the format of each data element and the overall database structure.

Authorization

Many database users should have limited access to specific tables, columns, or rows of a database. Database systems use this to grant individual users access to specific data.

Query Processor

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

Rules

Database systems ensure data is consistent with structural and business rules.

What does the query processor perform?

Query optimization 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

Database sizes range from megabytes to many terabytes, so the storage manager uses indexes to quickly locate data.

Transaction Manager

The transaction manager ensures efficient transaction execution, prevents conflicts between concurrent transactions, and restores the database to a consistent state in case of a transaction or system failure.

Metadata

Is data about the database, such as column names and the number of rows in each table.

Relational Database

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

SQL Query Language

All relational database systems support this.

Relational systems are ideal for databases that require....

An accurate record of every transaction, such as banking, airline reservation systems, and student records.

NoSQL

A nonrelational database.

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

Statement creates a new table by specifying the table and column names.

Data Type

Indicates the format column names. These can be numerical, textual or complex.

INT

Stores integer values.

DECIMAL

Stores fractional numeric values.

VARCHAR

Stores textual values.

DATE

Stores year, month, and day.

The Analysis Phase

Specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes.

Analysis

Has many alternative names, conceptual design, entity-relationship modeling, and requirements definition.

What shape represents an Entitiy?

Rectangle

3 multiple choice options

Logical Design Phase

Implements database requirements in a specific database system. For relational database systems, it converts entities, relationships, and attributes into tables, keys, and columns.

Physical Design Phase

Adds indexes and specifies how tables are organized on storage media with SQL statements such as CREATE INDEX and is specific to a database system.

Data Independence

The principle that physical design never affects query results

MySQL Command-Line Client

A text interface that is included in the MySQL Server download.

Error Code

Generated when a SQL statement is syntactically incorrect or the database cannot execute the statement.

Data Structures

Prescribes how data is organized.

Table, Column, Row, & Data Type

Data structures that organizes data in table.

Tuple

An ordered collection of elements is enclosed in parentheses such as (a, b, c) and (a, b, c).

3 multiple choice options

Rules

Governs valid data.

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.

Select

Selects a subset of rows of a table.

Join

Combines two tables by comparing related columns.

Union

Selects all rows of two tables.

Aggregate

Performs a calculation on a set of values and returns a single result. They are commonly used to summarize or analyze data, such as calculating sums, averages, counts, or finding maximum or minimum values within a dataset.

Logical Constraints

Rules that ensure data is valid.

Business Rules

Based on policies that are specific to a particular database.

Ex: PassportNumber values may not repeat in different Employee rows.

Structured Query Language

The standard language for relational databases, and is commonly supported in non-relational databases.

Literals

Explicit values that are string (surrounded by single or double quotes), numeric, or binary (represented with x'0', where '0' is any hex value).

Keywords

Words with special meaning such as SELECT, FROM, and WHERE.

Identifiers

Objects from the database like tables, columns, etc.

Ex. City, Name, Population

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.

Table

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.

No row order

Rows are not ordered. The organization of rows on a storage device, such as a disk drive, never affects query results.

INT or INTEGER

Integer values

VARCHAR(N)

Values with 0 to N characters.

DATE

Date values in YYYY-MM-DD

3 multiple choice options

DECIMAL(M, D)

Numeric values with M digits, of which D digits follow the decimal point.

ALTER TABLE

Adds, deletes, or modifies columns on an existing table.

Data Type

Is a named set of values from which column values are drawn.

Integer

A data type that represents positive and negative integers. Several integer data types exist, varying by the number of bytes allocated for each value. Common integer data types include INT, implemented as 4 bytes of storage, and SMALLINT, implemented as 2 bytes.

% (modulo)

Divides one numeric value by another and returns the integer remainder.

^

Raises one numeric value to the power of another.

=

An operator that compares two values for equality.

!=

An operator that compares two values for inequality.

NULL

A special value that represents either unknown or inapplicable data.

SET clause

Used by the UPDATE statement to specify the new column values.

FROM

A keyword followed by the table name whose rows are to be deleted. Ex: DELETE ___________ TableName;

TRUNCATE

A statement that deletes all rows from a table.

MERGE

A statement that selects data from one table, called the source, and inserts the data to another table, called the target.

Primary Key

A column, or group of columns (usually the table's first column) that is used to identify a row.

Simple Primary Key

Consists of a single column.

Composite Primary Key

Consists of multiple columns.

Auto-increment

A numeric column that is assigned an automatically incrementing value when a new row is inserted.

Database users occasionally make the following errors when inserting primary keys. What are they?

Inserting values for auto-increment primary keys.

Omitting values for primary keys that are not auto-increment columns.

3 multiple choice options

Foreign Key

A column, or group of columns, that refer to a primary key.

Foreign Key Constraint

Rejects insert, update, and delete statements that violate referential integrity.

CASCADE

Propagates primary key changes to foreign keys.

RESTRICT

Rejects an insert, update, or delete that violates referential integrity.

Constraint

A rule based on relational and business rules that governs allowable values in a database.

UNIQUE

A constraint ensures that values in a column, or group of columns, are unique.

CHECK

A constraint that specifies an expression on one or more columns of a table.

A statement that changes the structure of a table (by adding or dropping) when used with the clauses ADD, DROP, or CHANGE.

ALTER TABLE

IN

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

BETWEEN

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

EX: Written value ___________ minValue AND maxValue and is equivalent to value >= minValue AND value <= maxValue.

LIKE

An operator that is used with a WHERE clause that matches text against a pattern using the two wildcard characters % and _.

% (LIKE Operator)

Matches any number of characters.

Ex: _______ 'L%t' matches "Lt", "Lot", "Lift", and "Lol cat".

ORDER BY

A clause that orders selected rows by one or more columns in ascending (alphabetic or increasing) order.

The DESC keyword with the ____________ clause orders rows in descending order.

ABS(n)

Returns the absolute value of n.

LOWER(s)

Returns the lowercase s.

Ex. SELECT ___________ ('MySQL'); returns 'mysql'

TRIM(s)

Returns the string s without leading and trailing spaces.

Ex. SELECT _________ (' test '); returns 'test'

HOUR(t)

MINUTE(t)

SECOND(t)

Returns the hour, minute, or second from time t

Ex. SELECT ___________ ('22:11:45'); returns 22

Aggregate Function

Processes values from a set of rows and returns a summary value.