OA Questions Flashcards by Unknown Unknown

Software that reads and writes data in a database:

DBMS

How well did you know this?

Not at all

Perfectly

Software that helps business users interact with database systems:

Database Application

How well did you know this?

Not at all

Perfectly

Responsible for securing the database system against unauthorized users:

Database Administrator

How well did you know this?

Not at all

Perfectly

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

Database Designer

How well did you know this?

Not at all

Perfectly

Develops computer programs that utilize a database:

Database Programmer

How well did you know this?

Not at all

Perfectly

Data about the database, such as column names and the number of rows in each table:

Metadata

How well did you know this?

Not at all

Perfectly

SQL stands for:

Structured Query Language

How well did you know this?

Not at all

Perfectly

Database design phases:

Analysis
Logical Design
Physical Design

How well did you know this?

Not at all

Perfectly

This design phase implements database requirements in a specific database system. Converts entities, relationships, and attributes into tables, keys, and columns.

Logical Design

How well did you know this?

Not at all

Perfectly

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

Physical Design

How well did you know this?

Not at all

Perfectly

The principle that physical design never affects query results and allows database designers to tune query performance without changes to application programs:

Data Independence

How well did you know this?

Not at all

Perfectly

How data is organized (eg: relational/table or graph)

Data Structure

How well did you know this?

Not at all

Perfectly

An ordered collection of elements enclosed in parentheses:

Tuple

How well did you know this?

Not at all

Perfectly

Computes functions over multiple table rows, such as sum and count:

Aggregate

How well did you know this?

Not at all

Perfectly

Selects all rows of two tables:

Union

How well did you know this?

Not at all

Perfectly

Combines two tables by comparing related columns:

Join

How well did you know this?

Not at all

Perfectly

The language which defines the structure of the database:

DDL: Data Definition Language

How well did you know this?

Not at all

Perfectly

The language which retrieves data from the database:

DQL: Data Query Language

How well did you know this?

Not at all

Perfectly

The language which manipulates data stored in a database:

DML: Data Manipulation Language

How well did you know this?

Not at all

Perfectly

The language which controls database user access:

DCL: Data Control Language

How well did you know this?

Not at all

Perfectly

The language which manages database transactions:

DTL: Data Transaction Language

How well did you know this?

Not at all

Perfectly

Statement which adds, deletes, or modifies columns on an existing table:

ALTER TABLE

How well did you know this?

Not at all

Perfectly

Integer type allocation size and value ranges:

TINYINT (1B)
SMALLINT (2B)
MEDIUMINT (3B)
INT/INTEGER (4B)
BIGINT (8B)

How well did you know this?

Not at all

Perfectly

Operator which divides one numeric value by another and returns the integer remainder:

% (modulo)

How well did you know this?

Not at all

Perfectly

A special value that represents either unknown or inapplicable data:

null

INSERT statement boilerplate:

INSERT [INTO] TableName (Column1, Column2, ...) VALUES (Value1, Value2, ...);

What is the result of the following statement: DELETE FROM Table; What keyword/clause would increase selectivity?

All rows in the Table table are deleted. WHERE

What single statement deletes all rows from a table.

TRUNCATE

What statement selects data from one table, called the source, and inserts the data to another table, called the target?

MERGE

A primary key made of multiple columns:

Composite primary key

A rule that governs allowable values in a database:

Constraint

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

UNIQUE

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

CHECK

An operator which matches text against a pattern using the two wildcard characters % and _.

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

Aggregate function

Clause used with GROUP BY to filter group results:

HAVING

When duplicate column names appear in a query, the names must be distinguished with a:

prefix

To simplify queries or result tables, especially when using a prefix, a column name can be replaced with an:

ALIAS

To select all left table rows, but only matching right table rows, use a:

Left Join

Any join that selects unmatched rows, including left, right, and full joins, is an:

Outer Join

To select all left and right table rows, regardless of match:

Full Join

This keyword combines joins into one table:

UNION

Compares columns of two tables with the = operator. Most joins are these.

Equijoin

Joins a table to itself as long as the columns have comparable data types. Aliases are necessary.

Self-join

All possible combinations of rows from both tables appear in the result when this is join is used:

Cross-join

A view for which updates on source fields are recomputed and stored:

Materialized View

Clause which rejects inserts and updates that do not satisfy the view query WHERE clause, providing sensible errors to users.

WITH CHECK OPTION

A relationship which relates an entity to itself:

Reflexive

This document notes additional detail in text format, including names, synonyms, and descriptions of entities, relationships, and attributes:

Glossary AKA Data Dictionary AKA Repository

A set of the same kind of element:

Type

An element within a set of similar types:

Instance

This step of design develops an entity-relationship model, capturing data requirements while ignoring implementation details:

Analysis

Analysis stages:

1. Discover elements 1. Determine cardinality 1. Distinguish strong vs weak entities 1. Enumerate sub and supertype entities

Logical design stages:

1. Implement A. Entities B. Relationships C. Attributes 2. Normalize

Maxima and minima of relationships and attributes are referred to as:

Cardinality

In ER diagrams, attribute maximum and minimum ______ the attribute name. The minimum appears in ________.

Follow; parentheses

A unique, singular, and required attribute is referred to as:

Identifying

This kind of entity has one or more identifying attributes:

Strong

A _______ entity does not have an identifying attribute, but rather has an _______ relationship.

Weak; Identifying

In an ER diagram, an identifying relationship has a _______ next to the ________ entity.

Diamond; Identifying

A relationship identifying subtypes of a supertype is called:

IsA

A supertype entity is diagramed as a group of mutually exclusive subtype entities in a:

Partition

Subtypes within a partition are _______ aligned, while those in different partitions are ________ aligned.

Vertically; Horizontally

A primary key is, ideally:

Simple Stable Meaningless

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

Artificial Key

Dependence of one column on another:

Functional Dependence

Repetition of related values in a table:

Redundancy

Form when every non-key column depends on the primary key and there are no duplicate rows:

First Normal Form

Form when all non-key columns depend on the whole primary key and not only a part of one, if composite:

Second Normal Form

Form when all NON-KEY columns depend on the key, the whole key, and nothing but the key:

Third Normal Form

A simple or composite column that is unique and minimal is a ______ key:

Candidate

Form when all dependencies are on unique columns:

Boyce-Codd Normal Form

When the columns of A are a subset of the columns of B, A always depends on B and the dependency is known as:

Trivial

Steps to achieve Boyce-Codd Normal Form:

1. Identify all unique columns 2. Identify dependencies on non-unique columns 3. Move non-unique dependents to a foreign table

A scheme for organizing rows in blocks on storage media is known as:

Table Structure

A table structure wherein no order is imposed on rows, ideal for _______ operations:

INSERT/bulk load, Heap

A table structure wherein a column determines physical row order, assigned to blocks by a given range and ideal for _______, ________, and ________:

JOIN, WHERE, ORDER BY on the sorted column; Sorted

A table structure wherein rows are assigned to linked blocks within ______s by a _____ _____ ____:

Bucket; Dynamic Hash Function; Hash

Modulo Hash Function algorithm:

1. Convert the key to int 2. Divide by the number of blocks 3. Remainder becomes bucket number 4. Bucket number converts to physical block address containing the row

A table structure interleaving rows of multiple tables:

Cluster

The percentage of table rows selected by a query:

Hit ratio AKA Filter factor AKA Selectivity

An index containing an entry for every table block (as opposed to every row) is:

Sparse

An index containing an entry for every table row is:

Dense

The number of multi-level index entries per block is called:

fan-out

In a B_tree multi-level index values/pointers appear in intermediate levels. In a B_tree, they appear only at the bottom level.

B-tree; B+tree

A _____ index is a secondary index pointing to a primary with physical addresses:

Logical

A ______ index contains the return value processed on columns:

Function

A database object that maps one or more tables to a single file:

Tablespace

A statement that generates a result table to describe how a statement is executed by the storage engine:

EXPLAIN

Physical design stages:

1. Create indexes 2. Review the Slow Query Log 3. EXPLAIN slow queries 4. CREATE or DROP indexes 5. Consider table partitions

OA Questions Flashcards

(91 cards)