Data Management Foundations - D426

Is software that helps business users interact with database systems

Responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability.

Nterprets 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.

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.

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.

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.

Inserts rows into a table.

Retrieves data from a table.

Modifies data in a table.

Deletes rows from a table.

Stores integer values

Stores fractional numeric values

stores year, month, and day.

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

Implements database requirements in a specific database system.

Adds indexes and specifies how tables are organized on storage media

To simplify the use of SQL with a general-purpose language, database programs typically use an application programming interface

An ordered collection of elements enclosed in parentheses.

Manages database transactions

Defines the structure of the database.

Retrieves data from the database.

Data independence

TINYINT

SMALLINT

INTEGER or INT

Divides one numeric value by another and returns the integer remainder

Compares two values for inequality

The UPDATE statement modifies existing rows in a table. The UPDATE statement uses the ___ clause to specify the new column values

The DELETE statement deletes existing rows in a table. The ___ keyword is followed by the table name whose rows are to be deleted

Deletes all rows from a table.

Consists of multiple columns

Column, or group of columns, that refer to a primary key

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

Propagates primary key changes to foreign keys.

ABS

TRIM

Computes the arithmetic mean of all the values in the set

Finds the maximum value in the set

A SELECT statement that combines data from two tables, known as the left table and right table, into a single result. The tables are combined by comparing columns from the left and right tables, usually with the = operator.

A subquery, sometimes called a

An alias is a temporary name assigned to a column or table. The ___ keyword follows a column or table name to create an alias.

A person, place, product, concept, or activity

A descriptive property of an entity

Analysis steps

Logical design steps

The least number of instances of one entity that can relate to a single instance of another entity.

Depicts cardinality as a circle (zero), a short line (one), or three short lines (many)

An equijoin compares columns of two tables with the ___ operator

Primary key values should be easy to type and store. Small values are easy to specify in an SQL WHERE clause and speed up query processing

A single-column primary key created by the database designer when no suitable single-column or composite primary key exists

Rules for designing tables with less redundancy

Normalization eliminates redundancy by ____ a table into two or more tables

In a hash index, index entries are assigned to