Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Data Management > OA Questions > Flashcards

OA Questions Flashcards

1
Q

Software that reads and writes data in a database:

A

DBMS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Software that helps business users interact with database systems:

A

Database Application

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Responsible for securing the database system against unauthorized users:

A

Database Administrator

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

Database Designer

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Develops computer programs that utilize a database:

A

Database Programmer

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

Metadata

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

SQL stands for:

A

Structured Query Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Database design phases:

A
  1. Analysis
  2. Logical Design
  3. Physical Design
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

Logical Design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

Physical Design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

Data Independence

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

Data Structure

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

An ordered collection of elements enclosed in parentheses:

A

Tuple

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

Aggregate

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Selects all rows of two tables:

A

Union

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Combines two tables by comparing related columns:

A

Join

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

The language which defines the structure of the database:

A

DDL: Data Definition Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

The language which retrieves data from the database:

A

DQL: Data Query Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

The language which manipulates data stored in a database:

A

DML: Data Manipulation Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

The language which controls database user access:

A

DCL: Data Control Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

The language which manages database transactions:

A

DTL: Data Transaction Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

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

A

ALTER TABLE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Integer type allocation size and value ranges:

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

% (modulo)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

A special value that represents either unknown or inapplicable data:

A

null

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

INSERT statement boilerplate:

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

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

A

All rows in the Table table are deleted. WHERE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What single statement deletes all rows from a table.

A

TRUNCATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

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

A

MERGE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

A primary key made of multiple columns:

A

Composite primary key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

A rule that governs allowable values in a database:

A

Constraint

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

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

A

UNIQUE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

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

A

CHECK

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

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

A

LIKE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

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

A

Aggregate function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

Clause used with GROUP BY to filter group results:

A

HAVING

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

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

A

prefix

38
Q

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

A

ALIAS

39
Q

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

A

Left Join

40
Q

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

A

Outer Join

41
Q

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

A

Full Join

42
Q

This keyword combines joins into one table:

A

UNION

43
Q

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

A

Equijoin

44
Q

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

A

Self-join

45
Q

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

A

Cross-join

46
Q

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

A

Materialized View

47
Q

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

A

WITH CHECK OPTION

48
Q

A relationship which relates an entity to itself:

A

Reflexive

49
Q

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

A

Glossary
AKA Data Dictionary
AKA Repository

50
Q

A set of the same kind of element:

A

Type

51
Q

An element within a set of similar types:

A

Instance

52
Q

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

A

Analysis

53
Q

Analysis stages:

A
  1. Discover elements
  2. Determine cardinality
  3. Distinguish strong vs weak entities
  4. Enumerate sub and supertype entities
54
Q

Logical design stages:

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

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

A

Cardinality

56
Q

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

A

Follow; parentheses

57
Q

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

A

Identifying

58
Q

This kind of entity has one or more identifying attributes:

A

Strong

59
Q

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

A

Weak; Identifying

60
Q

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

A

Diamond; Identifying

61
Q

A relationship identifying subtypes of a supertype is called:

A

IsA

62
Q

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

A

Partition

63
Q

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

A

Vertically; Horizontally

64
Q

A primary key is, ideally:

A

Simple
Stable
Meaningless

65
Q

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

A

Artificial Key

66
Q

Dependence of one column on another:

A

Functional Dependence

67
Q

Repetition of related values in a table:

A

Redundancy

68
Q

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

A

First Normal Form

69
Q

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

A

Second Normal Form

70
Q

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

A

Third Normal Form

71
Q

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

A

Candidate

72
Q

Form when all dependencies are on unique columns:

A

Boyce-Codd Normal Form

73
Q

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

A

Trivial

74
Q

Steps to achieve Boyce-Codd Normal Form:

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

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

A

Table Structure

76
Q

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

A

INSERT/bulk load, Heap

77
Q

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

A

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

78
Q

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

A

Bucket; Dynamic Hash Function; Hash

79
Q

Modulo Hash Function algorithm:

A
  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
80
Q

A table structure interleaving rows of multiple tables:

A

Cluster

81
Q

The percentage of table rows selected by a query:

A

Hit ratio
AKA Filter factor
AKA Selectivity

82
Q

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

A

Sparse

83
Q

An index containing an entry for every table row is:

A

Dense

84
Q

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

A

fan-out

85
Q

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

A

B-tree; B+tree

86
Q

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

A

Logical

87
Q

A ______ index contains the return value processed on columns:

A

Function

88
Q

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

A

Tablespace

89
Q

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

A

EXPLAIN

90
Q

Physical design stages:

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

Data Management (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions