D426: Data Management Foundations > Red text > Flashcards

Red text Flashcards

(70 cards)

Study These Flashcards
1
Q

What type of exam is the D426 OA?

A

ALL multiple choice and heavily definition based

Understanding the Zybooks material is crucial for success in the D427 course.

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

What is a database application?

A

Software that helps business users interact with database systems

It facilitates the management and retrieval of data.

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

What is the role of a database administrator?

A

Responsible for securing the database system against unauthorized users

Enforces procedures for user access and database system availability.

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

What does authorization in a database context refer to?

A

Limiting access to specific tables, columns, or rows of a database

Database systems authorize individual users to access specific data.

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

What is the function of the query processor?

A

Interprets queries, creates a plan to modify the database or retrieve data, and returns query results

Performs query optimization for efficient execution.

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

What does the storage manager do?

A

Translates query processor instructions into low-level file-system commands

Uses indexes to quickly locate data in large databases.

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

What is the role of the transaction manager?

A

Ensures transactions are properly executed and restores the database to a consistent state

Prevents conflicts between concurrent transactions.

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

What does DML stand for?

A

Data Manipulation Language

Involves operations like DELETE, UPDATE, INSERT, and SELECT.

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

What does the SQL CREATE TABLE statement do?

A

Creates a new table by specifying the table and column names

Each column is assigned a data type.

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

What is the data type for storing integer values?

A

INT

Other examples include SMALLINT and BIGINT.

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

What is an entity in database analysis?

A

A person, place, activity, or thing

Entities are fundamental components in database requirements.

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

What is the logical design phase in database design?

A

Implements database requirements in a specific database system

Converts entities, relationships, and attributes into tables, keys, and columns.

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

What does data independence mean?

A

Allows database administrators to improve query performance without affecting query results

It relates to how data is organized on storage devices.

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

What does the DROP TABLE statement do?

A

Deletes a table along with all its rows from a database

It permanently removes the table structure and data.

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

What is the purpose of the ALTER TABLE statement?

A

Adds, deletes, changes, or modifies columns on an existing table

It allows for structural changes to the table.

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

What are literals in SQL?

A

Explicit values that are string, numeric, or binary

Strings must be surrounded by quotes.

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

What is the primary key in a database?

A

A column or group of columns used to identify a row

It is usually the first column in table diagrams.

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

What is a foreign key?

A

A column or group of columns that refer to a primary key

It maintains referential integrity between tables.

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

What does the UPDATE statement do?

A

Modifies existing rows in a table

Uses the SET clause to specify new column values.

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

What is the MERGE statement used for?

A

Selects data from one table and inserts it into another table

It combines data from source and target tables.

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

What is the project operation in SQL?

A

Selects specific table columns

Equivalent to SELECT Column1, Column2, … FROM Table.

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

What is a tuple?

A

An ordered collection of elements enclosed in parentheses

Examples include (a, b, c) and (c, b, a).

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

What is the relationship between Employee and Paycheck?

A

Many-to-Many (M:M) relationship

Many employees earn many paychecks.

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

What is the DELETE statement used for?

A

Deletes existing rows in a table

An optional WHERE clause specifies which rows to delete.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
What is the **TRUNCATE statement**?
Deletes all rows from a table ## Footnote Similar to DELETE without a WHERE clause but with minor differences.
26
What is a **foreign key constraint**?
A foreign key constraint is added to a CREATE TABLE statement with the FOREIGN KEY and REFERENCES keywords ## Footnote It rejects insert, update, and delete statements that violate referential integrity.
27
List the **four referential integrity actions**.
* RESTRICT * SET NULL * SET DEFAULT * CASCADE ## Footnote These actions specify how to handle violations of referential integrity.
28
What is a **constraint** in a database?
A constraint is a rule that governs allowable values in a database ## Footnote Constraints are based on relational and business rules.
29
How are constraints **added and dropped**?
Constraints are added and dropped with the ALTER TABLE TableName followed by an ADD, DROP, or CHANGE clause ## Footnote This allows modification of existing table constraints.
30
What does the **BETWEEN operator** do?
The BETWEEN operator determines if a value is between two other values ## Footnote It is equivalent to value >= minValue AND value <= maxValue.
31
What are the wildcard characters used with the **LIKE operator**?
* %: matches any number of characters * _: matches exactly one character ## Footnote These wildcards help in pattern matching within text.
32
What does the **ORDER BY clause** do?
The ORDER BY clause orders selected rows by one or more columns ## Footnote The DESC keyword orders rows in descending order.
33
What is an **aggregate function**?
An aggregate function processes values from a set of rows and returns a summary value ## Footnote Common aggregate functions include COUNT(), MIN(), MAX(), SUM(), and AVG().
34
What is the purpose of the **HAVING clause**?
The HAVING clause is used with the GROUP BY clause to filter group results ## Footnote It follows the GROUP BY clause and precedes the ORDER BY clause.
35
What is a **join** in SQL?
A join is a SELECT statement that combines data from two tables ## Footnote The tables are combined by comparing columns from the left and right tables.
36
What does an **INNER JOIN** do?
INNER JOIN selects only matching left and right table rows ## Footnote It excludes unmatched rows from both tables.
37
What is a **self-join**?
A self-join joins a table to itself ## Footnote This is useful for comparing rows within the same table.
38
Define a **subquery**.
A subquery is a query within another SQL query ## Footnote It is often used with aggregate functions within a WHERE clause.
39
What is an **alias** in SQL?
An alias is a temporary name assigned to a column or table ## Footnote The AS keyword follows a column or table name to create an alias.
40
What is a **materialized view**?
A materialized view is a view for which data is stored at all times ## Footnote It must be refreshed whenever a base table changes.
41
What is an **entity-relationship model**?
An entity-relationship model is a high-level representation of data requirements ## Footnote It includes entities, relationships, and attributes.
42
What is an **entity** in an ER model?
An entity is a person, place, product, concept, or activity ## Footnote Entities are the primary objects in an entity-relationship model.
43
What is a **relationship** in an ER model?
A relationship is a statement about two entities ## Footnote It defines how entities are related to each other.
44
What is an **attribute** in an ER model?
An attribute is a descriptive property of an entity ## Footnote Attributes provide additional information about entities.
45
What does **cardinality** refer to in ER modeling?
Cardinality refers to the maxima and minima of relationships and attributes ## Footnote It defines how many instances of one entity can relate to another.
46
What is a **weak entity**?
A weak entity does not have an identifying attribute ## Footnote It usually has an identifying relationship to another entity.
47
What is a **primary key**?
A primary key is a unique identifier for a table's records ## Footnote It should be stable, simple, and meaningless.
48
What is **normalization**?
Normalization eliminates redundancy by decomposing a table into two or more tables in higher normal form ## Footnote It is a crucial step in database design.
49
What is a **candidate key**?
A candidate key is a simple or composite key that is unique and minimal ## Footnote A table may have several candidate keys, but only one is designated as the primary key.
50
What is **First Normal Form (1NF)**?
A table is in First Normal Form whenever each table has a primary key and each entry is atomic ## Footnote This means no repeating groups or arrays.
51
What is **Boyce-Codd normal form**?
Boyce-Codd normal form is ideal for tables with frequent inserts, updates, and deletes ## Footnote It eliminates redundancy by ensuring that every determinant is a candidate key.
52
What is an **index** in a database?
An index speeds up the query process by allowing the compiler to run through data more efficiently ## Footnote It is particularly useful for large datasets.
53
What is a **heap table**?
In a heap table, no order is imposed on rows ## Footnote It optimizes insert operations and is fast for bulk loads.
54
What is a **table scan**?
A table scan reads table blocks directly, without accessing an index ## Footnote It is less efficient than an index scan.
55
What is a **hit ratio**?
Hit ratio is the percentage of table rows selected by a query ## Footnote It is also known as filter factor or selectivity.
56
What is a **table cluster** in database terminology?
A group of blocks containing rows, interleaving rows of two or more tables in the same storage area ## Footnote Table clusters are also known as multi-tables.
57
What is a **table scan**?
A database operation that reads table blocks directly, without accessing an index ## Footnote This operation is used to retrieve all rows from a table.
58
What is an **index scan**?
A database operation that reads index blocks sequentially to locate the needed table blocks ## Footnote This method is often more efficient than a table scan.
59
Define **hit ratio** in the context of databases.
The percentage of table rows selected by a query ## Footnote Also known as filter factor or selectivity.
60
What is the process of a **binary search** in databases?
Repeatedly splits the index in two until it finds the entry containing the search value ## Footnote This method is efficient for locating entries in sorted data.
61
What are the two types of indexes based on density?
* Dense index * Sparse index ## Footnote A dense index contains an entry for every table row, while a sparse index contains an entry for every table block.
62
What is a **hash index**?
An index where entries are assigned to buckets ## Footnote This type of index is used for fast data retrieval.
63
What is a **bitmap index**?
A grid of bits containing ones and zeros ## Footnote Bitmap indexes are efficient for columns with a limited number of distinct values.
64
What is a **tablespace** in database management?
A database object that maps one or more tables to a single file ## Footnote It is used to manage storage for database objects.
65
Define an **associative entity**.
The combination of unique identifiers of the many-to-many relationship between two entities ## Footnote This entity helps to manage relationships in a relational database.
66
What does **logical design** specify in database design?
Tables, columns, and keys ## Footnote It focuses on the structure of the data without considering how it will be stored.
67
What does **physical design** specify in database design?
Indexes, table structures, and partitions ## Footnote It affects query performance but never affects query results.
68
What is the role of a **storage engine**?
Translates instructions generated by a query processor into low-level commands that access data on storage media ## Footnote The storage engine is crucial for data retrieval and manipulation.
69
What is the syntax to **create an index** in SQL?
CREATE INDEX IdxColumnName ON TableName (ColumnName1, ColumnName2, ..., ColumnN); ## Footnote This command is used to improve the performance of queries.
70
What is a **NoSQL** database?
A non-relational database that addresses the need for web-based databases to handle large amounts of traffic and data ## Footnote NoSQL databases are easier to scale for web applications.
D426: Data Management Foundations
flashcards
Decks in class (4)
# Cards
Brainscape helps you reach your goals faster, through stronger study habits.
© 2026 Bold Learning Solutions. Terms and Conditions