D426 Study Guide (Red Text ONLY)

Crows Foot Notation

Modeling Conventions Question:

_____________ depicts cardinality as a circle (zero), a short line (one), or three short lines (many).

Database Application

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

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?

Query Optimization

Architecture Question:

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

UPDATE

SQL Statement:

This command modifies data in a table.

3 multiple choice options

Analysis

Name for the first phase of database design.

3 multiple choice options

UNION

Relational Operations Question:

Which operation selects all rows of two tables?

3 multiple choice options

Identifiers

SQL Question:

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

3 multiple choice options

Data Query Language (DQL)

SQL Sublanguages:

This language retrieves data from the database.

3 multiple choice options

Cell

Question about Tables:

A __________ is a single column of a single row.

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

% (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

Composite Primary Key

What kind of primary key consists of multiple columns?

3 multiple choice options

RESTRICT

Referential Integrity Constraints:

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

3 multiple choice options

LOWER(s)

Question on Simple Functions:

This function returns the lowercase of s.

Example: SELECT _______('MySQL');

returns 'mysql'

2 multiple choice options

Equijoin

A(n) ______________ compares columns of two tables with the = operator. Most Joins are this type.

Materialized View

A(n) ____________ is a view for which data is stored at all times.

Entity Instance

Instances Question:

A(n) _____________ is an individual thing.

Example: The employee Sam Snead.

CREATE INDEX IndexName

ON TableName (Column1, Column2, ..., ColumnN);

What is the syntax for creating an Index?

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.

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.

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

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

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

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

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

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

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

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

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