D426 Study Guide (Red Text ONLY)

Database Application

A _______________ is software that helps business users interact with database systems

Database Administrator

Database Role:

What role is responsible for securing the database system against unauthorized users? This role enforces procedures for user access and database system availability.

Authorization

Many database users should have limited access to specific tables, columns, or rows of a database. What database "special requirement" allows database systems to allow individual users access to specific data?

Rules

Database special requirements:

Database systems ensure data is consistent with structural and business______________.

Query Processor

Architecture Question:

This interprets Queries, creates a plan to modify the database or retrieve data, and returns query results to the application.

Query Optimization

Architecture Question:

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

Storage Manager

Architecture Question:

This 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 __________ to quickly locate data.

Transaction Manager

Architecture Question:

The ______________ ensures the following:

- Ensures transactions are properly executed.

- Prevents conflicts between concurrent transactions.

- Restores the database to a consistent state in the event of transaction or system failure.

Metadata

General Database Systems Question:

This is data about the database, such as column names, number of rows in a table, etc.

Open-Source

Is the MongoDB database engine Open-Source, Private, Free, or Commercial?

NoSQL

What is the MongoDB database Engine type?

INSERT

SQL statement:

This command inputs rows into a table.

SELECT

SQL Statement:

This command retrieves data from a table

UPDATE

SQL Statement:

This command modifies data in a table.

DELETE

SQL Statement:

This command deletes rows from a table.

CREATE TABLE

SQL Statement:

This statement makes a new table by specifying the table and column names.

Data Type

During creation, each column is assigned a ____________ that indicates the format of column values. These can be numeric, textual, or complex.

INT

Data Type Question:

This data type stores integer values.

DECIMAL

Data Type Question:

This datatype stores fractional numeric values.

VARCHAR

Data Type Question:

This datatype stores textual values.

DATE

Data Type Question:

This datatype stores year, month, and day.

Analysis

Name for the first phase of database design.

Logical Design

Name for the second phase of database design.

Physical Design

Name for the third phase of database design.

Analysis

Database Design phases:

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

Relationship

Database Design Related Question:

A(n) ____________ is a link between entities.

Attribute

Database Design Related Question:

A(n) _______________ is a descriptive property of an entity.

Analysis

Database Design Related Question:

The following are other names for what phase: Conceptual Design, Entity-Relationship Modeling, and Requirements Definition

Logical Design

Database Design Related Question:

This phase implements database requirements in a specific database system.

Logical

Database Design Related Question:

For relational database systems, this type of design converts entities, relationships, and attributes into tables, keys, and columns.

Physical Design

Database Design Related Question:

This phase adds indexes and specifies how tables are organized on storage media.

Physical

Database Design Related Question:

This kind of design affects query processing speed but NEVER affects the query result

Data Independence

Database Design Related Question:

The principle that physical design never affects query results is called _______________.

Application Programming Interface (API)

Database Design Related Question:

To simplify the use of SQL with a general-purpose language, database programs typically use a(n)____________.

MySQL Command-Line Client

MySQL Related Question:

This is a text-interface included in the MySQL Server download.

Error Code

MySQL Related Question:

MySQL returns a(n) ________________ and description when an SQL statement is syntactically incorrect or the database cannot execute the statement.

Tuple

Relational Data Structure Question:

What is an ordered collection of elements enclosed in parenthesis called?

Example: (a, b, c) and (c, b, a) are different.

Table

Data Structure Organization Question:

A(n) ___________ has a name, a fixed tuple of columns, and a varying set of rows.

Column

Data Structure Organization Question:

A(n) _____________ has a name and a data type.

Row

Data Structure Organization Question:

A(n) ____________ is an unnamed tuple of values. Each value corresponds to a column and belongs to the column's data type.

Data Type

Data Structure Organization Question:

A(n) _____________ is a named set of values, from which column values are drawn.

File, Relation

Synonyms for: Table

Record, Tuple

Synonyms for: Row

Field, Attribute

Synonyms for: Column

SELECT

Relational Operations Question:

Which operation selects a subset of rows of a table?

JOIN

Relational Operations Question:

Which operation combines two tables by comparing related columns?

UNION

Relational Operations Question:

Which operation selects all rows of two tables?

AGGREGATE

Relational Operations Question:

Which operation computes functions over multiple table rows, such as SUM() and COUNT()?

Business Rules

Relational Rules Question:

What rules are based on business policy and are specific to a particular database?

Literals

SQL Question:

Explicit values that are string, numeric, or binary are called __________. Examples are: 'String', "String", 123, x'0fa2'

Literals

SQL Question:

Strings must be surrounded by single quotes or double quotes. This is part of a description of _____________.

Literals

SQL Question:

Binary values are represented with x'0' where the 0 is any hex value. This is part of a description of ____________.

Keywords

SQL Question:

Words with special meaning are called ____________.

Examples: SELECT, FROM, WHERE

Identifiers

SQL Question:

________________ are objects from the database like Tables, Columns, etc. Examples: City, Name, Population

Comments

SQL Question:

Statements intended only for humans and ignored by the database when parsing an SQL statement are called ___________.

Example: -- for single line, /* for multi-line */

Data Definition Language (DDL)

SQL Sublanguages:

This defines the structure of the database.

Data Query Language (DQL)

SQL Sublanguages:

This language retrieves data from the database.

Data Manipulation Language (DML)

SQL Sublanguages:

This language manipulates data stored in databases.

Data Control Language (DCL)

SQL Sublanguages:

This language controls the database user access.

Data Transaction Language (DTL)

SQL Sublanguages:

This language manages database transactions.

Table

Question about Tables:

A ____________ has a name , a fixed sequence of columns, and a varying set of rows.

Column

Question about Tables:

A ______________ has a name and a data type.

Row

Question about Tables:

A ___________ is an unnamed sequence of values. Each value corresponds to a column and belongs to the column's data type.

Cell

Question about Tables:

A __________ is a single column of a single row.

Data Independence

Question about Tables:

What allows Database Administrators to improve query performance by changing the organization of data on storage devices without affecting query results?

CREATE TABLE

Question about Tables:

What statement creates a new table by specifying the table name, column names, and column data types?

INT or INTEGER

Question about Data Types:

________ is used for integer values.

VARCHAR(N)

Question about Data Types:

_____________ is used for values with 0 to N characters.

DATE

Question about Data Types:

__________ is used for Date values.

DECIMAL(M,D)

Question about Data Types:

___________ is used for numeric values with M digits of which D digits follow after the decimal point.

DROP TABLE

Question about Tables:

What statement deletes a table along with all the tables rows from a database?

ALTER TABLE

Question about Tables:

What statement adds, deletes, or modifies columns on an existing table?

Data Type

Data Type Question:

___________ is a named set of values for which column values are drawn

Integer

Data Type Question:

This data type represents positive and negative integers and has several variations.

INT

Question about Integers:

This type of integer is equal to 4 bytes.

TINYINT

Question about Integers:

This type of integer is equal to 1 byte.

MEDIUMINT

Question about Integers:

This type of integer is equal to 3 bytes.

SMALLINT

Question about Integers:

This type of integer is equal to 2 bytes.

BIGINT

Question about Integers:

This type of integer is equal to 8 bytes.

% (Modulo)

Integer Operator Question:

This operator divides one numeric value by another and returns the integer remainder.

!=

This operator compares two values for inequality.

UPDATE

SQL Statement Question:

What statement modifies existing rows in a table?

SET

SQL Statement Question:

The UPDATE statement uses what clause to specify new column values?

DELETE

SQL Statement Question:

What statement removes existing rows from a table?

FROM

SQL Statement Question:

Which keyword follows DELETE in a DELETE statement to specify the Table name where rows are to be deleted?

TRUNCATE

SQL Statement Question:

What statement deletes ALL rows from a table?

MERGE

SQL Statement Question:

The ____________ statement selects data from one table, called the source, and inserts the data into another table, called the target.

Primary Key

Question on Keys:

A ____________ is a column or group of columns used to identify a row.

Primary Key

Question on Keys:

This key is usually a table's first column.

Simple Primary Key

What kind of primary key consists of a single column?

Composite Primary Key

What kind of primary key consists of multiple columns?

Auto-Incrementing Column

What kind of column is a numeric column that is assigned an automatically incrementing value when a new row is inserted?

Manually inserting values for primary keys in an auto-incrementing column.

Users occasionally make this error when inserting primary keys into an auto-incrementing column. (This is not a repeat question)

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

Users occasionally make this error when inserting primary keys into an auto-incrementing column. (This is not a repeat question.)

A Mistake

Overriding auto-increment for a primary key is usually _____________.

Foreign Key

Question on Keys:

This type of key is a column or group of columns that refer to a primary key. The names of these keys and primary keys may be different but the data types must be the same.

REFERENCES

A Foreign Key constraint is added to a CREATE TABLE statement with FOREIGN KEY and ____________ Keywords.

INSERT, UPDATE, DELETE

These 3 statements are rejected when a Foreign Key Constraint is specified if they violate referential integrity.

RESTRICT

Referential Integrity Constraints:

___________ rejects an insert, update, or delete that violates referential integrity.