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.

1 multiple choice option

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.

2 multiple choice options

Metadata

General Database Systems Question:

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

2 multiple choice options

Open-Source

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

3 multiple choice options

NoSQL

What is the MongoDB database Engine type?

3 multiple choice options

INSERT

SQL statement:

This command inputs rows into a table.

3 multiple choice options

SELECT

SQL Statement:

This command retrieves data from a table

3 multiple choice options

UPDATE

SQL Statement:

This command modifies data in a table.

3 multiple choice options

DELETE

SQL Statement:

This command deletes rows from a table.

3 multiple choice options

CREATE TABLE

SQL Statement:

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

3 multiple choice options

Data Type

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

2 multiple choice options

INT

Data Type Question:

This data type stores integer values.

3 multiple choice options

DECIMAL

Data Type Question:

This datatype stores fractional numeric values.

3 multiple choice options

VARCHAR

Data Type Question:

This datatype stores textual values.

3 multiple choice options

DATE

Data Type Question:

This datatype stores year, month, and day.

3 multiple choice options

Analysis

Name for the first phase of database design.

3 multiple choice options

Logical Design

Name for the second phase of database design.

3 multiple choice options

Physical Design

Name for the third phase of database design.

3 multiple choice options

Analysis

Database Design phases:

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

3 multiple choice options

Relationship

Database Design Related Question:

A(n) ____________ is a link between entities.

1 multiple choice option

Attribute

Database Design Related Question:

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

1 multiple choice option

Analysis

Database Design Related Question:

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

3 multiple choice options

Logical Design

Database Design Related Question:

This phase implements database requirements in a specific database system.

3 multiple choice options

Logical

Database Design Related Question:

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

3 multiple choice options

Physical Design

Database Design Related Question:

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

3 multiple choice options

Physical

Database Design Related Question:

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

3 multiple choice options

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)____________.

1 multiple choice option

MySQL Command-Line Client

MySQL Related Question:

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

2 multiple choice options

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.

2 multiple choice options

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.

2 multiple choice options

Table

Data Structure Organization Question:

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

3 multiple choice options

Column

Data Structure Organization Question:

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

3 multiple choice options

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.

3 multiple choice options

Data Type

Data Structure Organization Question:

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

3 multiple choice options

File, Relation

Synonyms for: Table

2 multiple choice options

Record, Tuple

Synonyms for: Row

2 multiple choice options

Field, Attribute

Synonyms for: Column

2 multiple choice options

SELECT

Relational Operations Question:

Which operation selects a subset of rows of a table?

3 multiple choice options

JOIN

Relational Operations Question:

Which operation combines two tables by comparing related columns?

3 multiple choice options

UNION

Relational Operations Question:

Which operation selects all rows of two tables?

3 multiple choice options

AGGREGATE

Relational Operations Question:

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

3 multiple choice options

Business Rules

Relational Rules Question:

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

3 multiple choice options

Literals

SQL Question:

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

3 multiple choice options

Literals

SQL Question:

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

2 multiple choice options

Literals

SQL Question:

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

3 multiple choice options

Keywords

SQL Question:

Words with special meaning are called ____________.

Examples: SELECT, FROM, WHERE

3 multiple choice options

Identifiers

SQL Question:

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

3 multiple choice options

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 /

3 multiple choice options

Data Definition Language (DDL)

SQL Sublanguages:

This defines the structure of the database.

3 multiple choice options

Data Query Language (DQL)

SQL Sublanguages:

This language retrieves data from the database.

3 multiple choice options

Data Manipulation Language (DML)

SQL Sublanguages:

This language manipulates data stored in databases.

3 multiple choice options

Data Control Language (DCL)

SQL Sublanguages:

This language controls the database user access.

3 multiple choice options

Data Transaction Language (DTL)

SQL Sublanguages:

This language manages database transactions.

3 multiple choice options

Table

Question about Tables:

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

3 multiple choice options

Column

Question about Tables:

A ______________ has a name and a data type.

3 multiple choice options

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.

3 multiple choice options

Cell

Question about Tables:

A __________ is a single column of a single row.

3 multiple choice options

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?

3 multiple choice options

CREATE TABLE

Question about Tables:

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

3 multiple choice options

INT or INTEGER

Question about Data Types:

________ is used for integer values.

3 multiple choice options

VARCHAR(N)

Question about Data Types:

_____________ is used for values with 0 to N characters.

3 multiple choice options

DATE

Question about Data Types:

__________ is used for Date values.

3 multiple choice options

DECIMAL(M,D)

Question about Data Types:

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

3 multiple choice options

DROP TABLE

Question about Tables:

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

3 multiple choice options

ALTER TABLE

Question about Tables:

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

3 multiple choice options

Data Type

Data Type Question:

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

3 multiple choice options

Integer

Data Type Question:

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

3 multiple choice options

INT

Question about Integers:

This type of integer is equal to 4 bytes.

3 multiple choice options

TINYINT

Question about Integers:

This type of integer is equal to 1 byte.

3 multiple choice options

MEDIUMINT

Question about Integers:

This type of integer is equal to 3 bytes.

3 multiple choice options

SMALLINT

Question about Integers:

This type of integer is equal to 2 bytes.

3 multiple choice options

BIGINT

Question about Integers:

This type of integer is equal to 8 bytes.

3 multiple choice options

% (Modulo)

Integer Operator Question:

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

3 multiple choice options

!=

This operator compares two values for inequality.

3 multiple choice options

UPDATE

SQL Statement Question:

What statement modifies existing rows in a table?

3 multiple choice options

SET

SQL Statement Question:

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

3 multiple choice options

DELETE

SQL Statement Question:

What statement removes existing rows from a table?

3 multiple choice options

FROM

SQL Statement Question:

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

3 multiple choice options

TRUNCATE

SQL Statement Question:

What statement deletes ALL rows from a table?

3 multiple choice options

MERGE

SQL Statement Question:

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

3 multiple choice options

Primary Key

Question on Keys:

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

3 multiple choice options

Primary Key

Question on Keys:

This key is usually a table's first column.

3 multiple choice options

Simple Primary Key

What kind of primary key consists of a single column?

3 multiple choice options

Composite Primary Key

What kind of primary key consists of multiple columns?

3 multiple choice options

Auto-Incrementing Column

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

3 multiple choice options

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)

3 multiple choice options

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

3 multiple choice options

A Mistake

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

1 multiple choice option

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.

3 multiple choice options

REFERENCES

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

3 multiple choice options

INSERT, UPDATE, DELETE

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

3 multiple choice options

RESTRICT

Referential Integrity Constraints:

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

3 multiple choice options