D426 > StudyGuide > Flashcards

StudyGuide Flashcards

(77 cards)

Study These Flashcards
1
Q

[Database Fundamentals] What is a database application?

A

A database application is software that helps business users interact with database systems.

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

[Database Fundamentals] What is the primary role of a database administrator?

A

A database administrator is responsible for securing the database system against unauthorized users.
They enforce procedures for user access and database system availability.

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

[Database Fundamentals] How do database systems handle authorization and rules?

A

Database systems authorize individual users to access specific data.
They ensure data is consistent with structural and business rules.
For example, when a course number appears in a student registration record, the course must exist in the course catalog.

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

[Database Architecture] What is the role of the query processor?

A

The query processor interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application.
Why: It performs query optimization to ensure the most efficient instructions are executed on the data.

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

[Database Architecture] Fill in the blank: The ________ translates the query processor instructions into low-level file-system commands.

A

storage manager.
Note: Because database sizes range from megabytes to many terabytes, the storage manager uses indexes to quickly locate data.

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

[Database Architecture] What component prevents conflicts between concurrent transactions and restores the database after a failure?

A

The transaction manager.
Real-world application: The transaction manager ensures transactions are properly executed and restores the database to a consistent state in the event of a transaction or system failure.

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

[Database Platforms] What are the key characteristics of MongoDB?

A

MongoDB is a NoSQL, open-source database.

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

[SQL Basics] Describe the purpose of the four primary data manipulation commands: INSERT, SELECT, UPDATE, and DELETE.

A

INSERT inserts rows into a table.
SELECT retrieves data from a table.
UPDATE modifies data in a table.
DELETE deletes rows from a table.

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

[SQL Basics] Practice: Which SQL statement would you use to create a new table and assign a format to its columns?

A

The SQL CREATE TABLE statement creates a new table by specifying the table and column names.
Each column is assigned a data type that indicates the format of column values.

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

[SQL Basics] Mastery: What are the differences between the INT, DECIMAL, VARCHAR, and DATE data types?

A

INT stores integer values.
DECIMAL stores fractional numeric values.
VARCHAR stores textual values.
DATE stores year, month, and day.

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

[Database Design] What occurs during the analysis phase of database design?

A

The analysis phase specifies database requirements without regard to a specific database system.
Requirements are represented as entities, relationships, and attributes.
Note: Analysis has alternative names, such as conceptual design, entity-relationship modeling, and requirements definition.

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

[Database Design] Describe the logical design phase of database creation.

A

The logical design phase implements database requirements in a specific database system.
For relational database systems, logical design 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

[Database Design] What is the physical design phase and how does it relate to data independence?

A

The physical design phase adds indexes and specifies how tables are organized on storage media.
Physical design affects query processing speed but never affects the query result.
The principle that physical design never affects query results is called data independence.

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

[Entity-Relationship Model] Define entities, relationships, and attributes.

A

An entity is a person, place, activity, or thing.
A relationship is a link between entities, and an attribute is a descriptive property of an entity.

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

[Database Interfaces] What is an Application Programming Interface (API) used for in database programming?

A

To simplify the use of SQL with a general-purpose language, database programs typically use an application programming interface.

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

[Database Interfaces] What happens when a SQL statement is syntactically incorrect in the MySQL Command-Line Client?

A

MySQL Server returns an error code and description when an SQL statement is syntactically incorrect or the database cannot execute the statement.

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

[Relational Structure] What is a tuple in the context of a relational database?

A

A tuple is an ordered collection of elements enclosed in parentheses.
For example, (a, b, c) and (c, b, a) are different, since tuples are ordered.

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

[Relational Structure] Describe the components of a table structure: table, column, and row.

A

A table has a name, a fixed sequence of columns, and a varying set of rows.
A column has a name and a data type.
A row is an unnamed sequence of values.

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

[Relational Structure] What are the common synonyms used for Table, Row, and Column?

A

Synonyms for Table: File, Relation.
Synonyms for Row: Record, Tuple.
Synonyms for Column: Field, Attribute.

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

[SQL Syntax] How are string and binary literals formatted in SQL?

A

Strings must be surrounded by single quotes or double quotes.
Binary values are represented with x’0’ where the 0 is any hex value.

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

[SQL Syntax] How do you write a single-line comment and a multi-line comment in SQL?

A

A single-line comment starts with –.
A multi-line comment is enclosed between /* and */.

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

[SQL Sublanguages] What is the difference between DDL and DML?

A

Data Definition Language (DDL) defines the structure of the database.
Data Manipulation Language (DML) manipulates data stored in a database.

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

[SQL Sublanguages] Fill in the blank: The ________ retrieves data from the database, while the ________ manages database transactions.

A

Data Query Language (DQL) retrieves data from the database.
Data Transaction Language (DTL) manages database transactions.

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

[SQL Sublanguages] What is the purpose of the Data Control Language (DCL)?

A

Data Control Language (DCL) controls database user access.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
[Relational Structure] Define a cell and an empty table.
A cell is a single column of a single row. A table without rows is called an empty table. Note: A table must have at least one column but any number of rows.
26
[Data Independence] How does data independence benefit database administrators?
Data independence allows database administrators to improve query performance by changing the organization of data on storage devices, without affecting query results.
27
[DDL Commands] What is the function of the DROP TABLE statement?
The DROP TABLE statement deletes a table, along with all the table's rows, from a database.
28
[DDL Commands] How do you add, delete, or modify columns on an existing table?
The ALTER TABLE statement adds, deletes, or modifies columns on an existing table.
29
[Data Types] What is the difference in storage size between the TINYINT, SMALLINT, and INT data types?
TINYINT requires 1 byte of storage. SMALLINT requires 2 bytes of storage. INTEGER or INT requires 4 bytes of storage.
30
[DML Commands] Explain how the UPDATE statement works and the risk of omitting the WHERE clause.
The UPDATE statement uses the SET clause to specify the new column values. An optional WHERE clause specifies which rows are updated. Risk: Omitting the WHERE clause results in all rows being updated.
31
[DML Commands] How does the DELETE statement differ from the TRUNCATE statement?
The DELETE statement deletes existing rows in a table and can use an optional WHERE clause to specify which rows should be deleted. The TRUNCATE statement deletes all rows from a table and is nearly identical to a DELETE statement with no WHERE clause, except for minor database-system dependent differences.
32
[DML Commands] What is the purpose of the MERGE statement?
The MERGE statement selects data from one table, called the source, and inserts the data to another table, called the target.
33
[Database Keys] What is a primary key?
A primary key is a column, or group of columns, used to identify a row. Note: The primary key is usually the table's first column and appears on the left of table diagrams, but the position is not significant to the database.
34
[Database Keys] Explain the difference between a simple primary key and a composite primary key.
A simple primary key consists of a single column. A composite primary key consists of multiple columns.
35
[Database Keys] Fill in the blank: An ________ is a numeric column that is assigned an automatically incrementing value when a new row is inserted.
auto-increment column. Note: Database users occasionally make errors by inserting values for auto-increment primary keys, or omitting values for primary keys that are not auto-increment columns.
36
[Database Keys] Can a specific value be inserted into an auto-increment column in MySQL?
MySQL allows insertion of a specific value to an auto-increment column. Why avoid: However, overriding auto-increment for a primary key is usually a mistake.
37
[Database Keys] What is a foreign key?
A foreign key is a column, or group of columns, that refer to a primary key. Note: The data types of the foreign and primary keys must be the same, but the names may be different.
38
[Constraints] What is the purpose of a FOREIGN KEY constraint?
When a foreign key constraint is specified, the database rejects insert, update, and delete statements that violate referential integrity. It is added to a CREATE TABLE statement with the FOREIGN KEY and REFERENCES keywords.
39
[Constraints] Name the four actions databases use to automatically correct referential integrity violations.
1. RESTRICT rejects an insert, update, or delete that violates referential integrity. 2. SET NULL sets invalid foreign keys to NULL. 3. SET DEFAULT sets invalid foreign keys to the foreign key default value. 4. CASCADE propagates primary key changes to foreign keys.
40
[Constraints] How are constraints added and dropped from an existing table?
Constraints are added and dropped with the ALTER TABLE TableName followed by an ADD, DROP, or CHANGE clause.
41
[SQL Operators] How does the BETWEEN operator function in an SQL statement?
The BETWEEN operator provides an alternative way to determine if a value is between two other values. Syntax: value BETWEEN minValue AND maxValue, which is equivalent to value >= minValue AND value <= maxValue.
42
[SQL Operators] Explain how the LIKE operator uses wildcard characters.
The LIKE operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and . The '%' matches any number of characters. The '' matches exactly one character.
43
[SQL Clauses] What does the ORDER BY clause do?
The ORDER BY clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order. Note: The DESC keyword with the ORDER BY clause orders rows in descending order.
44
[SQL Functions] What do the ABS(), LOWER(), and TRIM() functions do?
ABS() returns the absolute value of a number. LOWER() returns the lowercase of a string. TRIM() returns the string without leading and trailing spaces.
45
[SQL Aggregates] Define the five common aggregate functions: COUNT, MIN, MAX, SUM, and AVG.
An aggregate function processes values from a set of rows and returns a summary value. COUNT() counts the number of rows in the set. MIN() finds the minimum value in the set. MAX() finds the maximum value in the set. SUM() sums all the values in the set. AVG() computes the arithmetic mean of all the values in the set.
46
[SQL Clauses] What is the purpose of the HAVING clause?
The HAVING clause is used with the GROUP BY clause to filter group results. Note: The optional HAVING clause follows the GROUP BY clause and precedes the optional ORDER BY clause.
47
[SQL Joins] What is a join?
A join is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result. Note: The tables are combined by comparing columns from the left and right tables, usually with the = operator, and the columns must have comparable data types. +4
48
[SQL Joins] Explain the difference between an INNER JOIN and a FULL JOIN.
An INNER JOIN selects only matching left and right table rows. A FULL JOIN selects all left and right table rows, regardless of match. In a FULL JOIN result table, unmatched left table rows appear with NULL values in right table columns, and vice versa.
49
[SQL Joins] Fill in the blank: A ________ selects all left table rows but only matching right table rows, while a ________ selects all right table rows but only matching left table rows.
LEFT JOIN. RIGHT JOIN.
50
[SQL Joins] Practice: Describe a Cross-join and a Self-join.
A self-join joins a table to itself. A cross-join combines two tables without comparing columns, using a CROSS JOIN clause without an ON clause. As a result, all possible combinations of rows from both tables appear in the result.
51
[Database Views] What is a materialized view and why must it be refreshed?
A materialized view is a view for which data is stored at all times. Why refresh: Whenever a base table changes, the corresponding view tables can also change, so materialized views must be refreshed.
52
[Database Views] What happens when WITH CHECK OPTION is specified for a view?
The database rejects inserts and updates that do not satisfy the view query WHERE clause. Instead, the database generates an error message that explains the violation.
53
[SQL Basics] What is an alias in SQL?
An alias is a temporary name assigned to a column or table. The AS keyword follows a column or table name to create an alias.
54
[Entity-Relationship Model] What are the three main kinds of objects in an entity-relationship model?
An entity is a person, place, product, concept, or activity. A relationship is a statement about two entities. An attribute is a descriptive property of an entity.
55
[Entity-Relationship Model] What is a reflexive relationship?
A reflexive relationship relates an entity to itself.
56
[Visual] How are entities represented in an Entity-Relationship Diagram?
Entities are drawn as rectangles.
57
[Data Modeling] Explain the difference between a type and an instance in entity-relationship modeling.
A type is a set, such as an entity type which is a set of things. An instance is an element of a set, such as an entity instance which is an individual thing.
58
[Data Modeling] Practice: Provide examples of an entity type and an entity instance.
Entity type example: All employees in a company. Entity instance example: The employee Sam Snead.
59
[Data Modeling] Mastery: How do entity, relationship, and attribute types typically translate to a relational database?
Entity, relationship, and attribute types usually become tables, foreign keys, and columns, respectively.
60
[Data Modeling] What does cardinality refer to in database relationships?
Cardinality refers to maxima and minima of relationships and attributes. Relationship maximum is the greatest number of instances of one entity that can relate to a single instance of another entity.
61
[Visual] Describe Crow's foot notation in an Entity-Relationship Diagram.
One popular convention, called crow's foot notation, depicts cardinality as a circle (zero), a short line (one), or three short lines (many). The three short lines look like a bird's foot, hence the name.
62
[Data Modeling] What is a subtype entity and how does it relate to a supertype?
A subtype entity is a subset of another entity type, called the supertype entity. The identifying relationship is called an IsA relationship.
63
[Database Design] What are the three ideal characteristics of a primary key?
Primary keys should be stable, simple, and meaningless.
64
[Database Design] Practice: Why should a primary key be meaningless?
Primary keys should not contain descriptive information. Descriptive information occasionally changes, so primary keys containing descriptive information are unstable.
65
[Database Design] Mastery: When no suitable single-column or composite primary key exists, what should a database designer do?
Create an artificial key, which is a single-column primary key created by the database designer. Usually artificial key values are integers, generated automatically by the database as new rows are inserted to the table.
66
[Normalization] What are normal forms and what is the main goal of normalization?
Normal forms are rules for designing tables with less redundancy. Normalization eliminates redundancy by decomposing a table into two or more tables in higher normal form.
67
[Normalization] Fill in the blank: Dependence of one column on another is called ________.
functional dependence.
68
[Normalization] Mastery: Describe the difference between Third Normal Form and Boyce-Codd Normal Form.
A table is in third normal form if, whenever a non-key column A depends on column B, then B is unique. A table is in Boyce-Codd normal form if, whenever column A depends on column B, then B is unique. This definition is identical to the definition of third normal form with the term 'non-key' removed.
69
[Normalization] What is denormalization?
Denormalization means intentionally introducing redundancy by merging tables.
70
[Physical Design] Describe the characteristics and benefits of a heap table.
In a heap table, no order is imposed on rows. Heap tables optimize insert operations and are particularly fast for bulk load of many rows, since rows are stored in load order.
71
[Physical Design] What is a hash table in the context of physical database design?
In a hash table, rows are assigned to buckets. A bucket is a block or group of blocks containing rows.
72
[Physical Design] What is the difference between a table scan and an index scan?
A table scan is a database operation that reads table blocks directly, without accessing an index. An index scan is a database operation that reads index blocks sequentially, in order to locate the needed table blocks.
73
[Physical Design] Define hit ratio.
Hit ratio, also called filter factor or selectivity, is the percentage of table rows selected by a query.
74
[Physical Design] What is the difference between a dense index and a sparse index?
A dense index contains an entry for every table row. A sparse index contains an entry for every table block.
75
[Physical Design] How is a bitmap index structured?
A bitmap index is a grid of bits. Bitmap indexes contain ones and zeros.
76
[Physical Design] What is a tablespace?
A tablespace is a database object that maps one or more tables to a single file.
77
[SQL Syntax] How do you write the SQL statement to create an index on a table?
CREATE INDEX IndexName ON TableName (Column1, Column2, ..., ColumnN);
D426
flashcards
Decks in class (7)
# Cards
Brainscape helps you reach your goals faster, through stronger study habits.
© 2026 Bold Learning Solutions. Terms and Conditions