D426 Practice Questions

Determine the minima.

Person-Marries-Person

zero-zero

zero-one

one-zero

one-one

zero-zero

A person may not be married. A person doesn’t HAVE to be married.

During which step in the ETL process are raw data sets aggregated?

Transformation

During which step in the ETL process is data is pulled from one or more source systems?

Extraction

During which step in the ETL process is data cleaned?

Transformation

During which step in the ETL process is data formatted?

Transformation

During which step in the ETL process is data combined from multiple sources?

Transformation

During which step in the ETL process is data de/normalized?

Transformation

During which step in the ETL process is data filtered?

Transformation

During which step in the ETL process is data inserted into the target system?

Loading

During which step in the ETL process is data made available for analysis and reporting?

Loading

Determine the minima.

Person-Has-Passport

zero-zero

zero-one

one-zero

one-one

one-zero

A person may have zero passport, but a passport always has one person. one-zero

Determine the minima.

Flight-ArrivesAt-Airport

zero-zero

zero-one

one-zero

one-one

one-one

A flight is always going to have to land at SOME airport. The airport is always expecting flights. one-one

Our state's Department of Education maintains a database of colleges in the state. Each college has a name, a code, and the count of students currently enrolled. Each college may have more than one campus. Each campus has a name that is unique within the college and has an address. More than one campus of a college may be located in the same city. Cities are identified by name. A city may contain campuses of more than one college.

The entities are College, Campus, and City. What are the relationships?

☐ College-Has-Campus ☐ College-In-State

☐ Student-GoesTo-College ☐ Campus-IsLocatedIn-City

☐ College-IsLocatedIn-City

College-Has-Campus, Campus-IsLocatedIn-City

The database does not track which college is located in each city. So College-IsLocatedIn-City is not a relationship.

Campus and City are entities, and the database tracks which campus is located in each city. So Campus-IsLocatedIn-City is a relationship.

Relationships are defined by Entity - (relationship) - Entity

Our company sells loose-leaf teas from all over the world. We record the type of each tea, such as black, green, and oolong. We also code the tea grade, such as FTGFOP1. Each kind of tea is produced by a specific plantation. Each plantation has a name, such as Duflating Estate, and a regional label, such Assam or Ceylon. We track all the teas each customer has purchased to make it easier for them to repurchase the same teas. For each of our customers, we have an email address and a home address.

What are the entities?

☐ Type ☐ Plantation ☐ Tea

☐ Company ☐ Region ☐ Customer

Tea, Plantation, Customer

Since the database does not track multiple companies, Company is not an entity.

Our library system has several branches. Each branch has an address and a unique code. Each book belongs to a particular branch. Each book is assigned a unique barcode number and has a title and a publisher name. Each library member may borrow up to three books at a time. To join the library, a member provides an identification number and identification type, such as driver's license, passport number, or employee number.

What are the entities?

☐ Branch ☐ Member ☐ Library

☐ Publisher ☐ Address ☐ Book

Branch, Member, Book

What is wrong with the following relationship description?

"Student-Takes-Course" describes all course instances taken by a student instance.

Description should begin with an entity name.

Description does not use complete sentences.

Description does not include examples and counterexamples.

Description does not include examples and counterexamples.

The description does not clarify the meaning of "takes" with examples and counterexamples. Ex: The description might state that failing grades are not included in the "Student-Takes-Course" relationship.

What is wrong with the following entity description?

The difference between a course and a class is that a course refers to the catalog description, while a class is an individual offering of a course in a specific term.

Description does not begin with entity name.

Description does not include counterexamples.

Description does not use complete sentences.

Description does not begin with entity name.

The first noun in the description is "difference". Description should begin with the name of the entity being described, such as "class" or "course".

“Students at San Antonio Community College”

A. Entity Type B. Entity Instance

C. Relationship Type D. Relationship Instance

E. Attribute Type F. Attribute Instance

Entity Type.

"Students" are a set of people. A set of things is an entity type.

“Eleanor Rigby, a student at San Antonio community college”

A. Entity Type B. Entity Instance

C. Relationship Type D. Relationship Instance

E. Attribute Type F. Attribute Instance

Entity Instance

In some sentences, "Eleanor Rigby" might refer to a value of the name attribute, or attribute instance. However, in the sentence "Eleanor Rigby, a student at San Antonio community college", the name refers to a student, which is an entity instance.

“Students take exams”

A. Entity Type B. Entity Instance

C. Relationship Type D. Relationship Instance

E. Attribute Type F. Attribute Instance

Relationship Type.

"Students take exams" is a statement about student and exam, two entity types. A statement about entity types is a relationship type.

“Eleanor Rigby takes the final exam in calculus”

A. Entity Type B. Entity Instance

C. Relationship Type D. Relationship Instance

E. Attribute Type F. Attribute Instance

Relationship Instance

"Eleanor Rigby" is a student entity instance. "Final exam in calculus" is an exam entity instance. A statement about entity instances is a relationship instance.

“Student record number”

A. Entity Type B. Entity Instance

C. Relationship Type D. Relationship Instance

E. Attribute Type F. Attribute Instance

Attribute Type.

"Student record number" is a set of numbers describing the student entity. A set of descriptive properties is an attribute type.

“324A21” (A student record number)

A. Entity Type B. Entity Instance

C. Relationship Type D. Relationship Instance

E. Attribute Type F. Attribute Instance

Attribute Instance.

324A21 is an individual student record number. Individual values are attribute instances.

A query is written as SELECT * FROM Employees, Departments; without a WHERE or ON clause. What type of result will this produce?

An inner join based on commonly named columns

An error, because a join condition is required

A cross-join, showing every employee combined with every department

An empty result set

A cross-join, showing every employee combined with every department

Which join clauses can be used in a non-equijoin query?

INNER JOIN and FULL JOIN

LEFT JOIN and RIGHT JOIN

All JOIN clauses

All JOIN clauses

A database administrator needs to create a new Products table. Which sublanguage of SQL would they use for this task?

Data Manipulation Language (DML)

Data Definition Language (DDL)

Data Transaction Language (DTL)

Data Query Language (DQL)

Data Definition Language (DDL). Think of it as defining something new.

DDL (Data Definition Language) includes commands such as:

CREATE

ALTER

DROP

TRUNCATE

A user updates their phone number in their profile on a website. In the background, a statement is executed. This statement is part of which SQL sublanguage?

Data Manipulation Language (DML)

Data Definition Language (DDL)

Data Control Language (DCL)

Data Query Language (DQL)

Data Manipulation Language (DML)

You are manipulating data that already exists in the table. You’re not creating anything new. So you’re not defining anything new. Contains commands like INSERT, UPDATE, DELETE, SELECT.

A banking application needs to ensure that a transfer of funds is completed entirely or not at all. Which sublanguage, containing commands like COMMIT and ROLLBACK, manages these operations?

Data Manipulation Language (DML)

Data Definition Language (DDL)

Data Transaction Language (DTL)

Data Query Language (DQL)

Data Transaction Language (DTL)

A security officer needs to give a new data scientist read-only access to a specific table. Which sublanguage would be used?

Data Manipulation Language (DML)

Data Definition Language (DDL)

Data Control Language (DCL)

Data Query Language (DQL)

Data Control Language (DCL)

In the SQL statement “SELECT FirstName FROM Employees;”, what is FirstName?

A keyword

A literal

An identifier

A comment

An identifier

In the SQL statement “UPDATE Products SET Price = 29.99 WHERE ProductID = 101;” , what is 29.99?

A keyword

A literal

An identifier

An expression

A literal

In the SQL statement “INSERT INTO Customers (Name) VALUES ('John Smith');”, what is INSERT?

A keyword

A literal

An identifier

A comment

A keyword

In a formal SQL syntax definition, what do square brackets [] around a clause indicate?

The clause is required and must be included

The clause is optional and may be omitted

The user must choose one of several options within the brackets

The clause can be repeated multiple times

The clause is optional and may be omitted

A developer needs to ensure that the combination of a FlightNumber and FlightDate is unique in the Flights table. How should this be implemented? 

By adding a UNIQUE constraint to FlightNumber and a separate UNIQUE constraint to FlightDate 

By adding a single UNIQUE table constraint on both (FlightNumber, FlightDate) columns

By making (FlightNumber, FlightDate) the primary key 

By creating a CHECK constraint on both columns 

By adding a single UNIQUE table constraint on both (FlightNumber, FlightDate) columns

You need this: UNIQUE (FlightNumber, FlightDate)

You need the combination of the FlightNumber and FlightDate to be unique, not each one separately to be unique.

A developer adds a NOT NULL constraint to a PhoneNumber column in a table that already contains several rows with NULL phone numbers. What will happen when they try to apply this change? 

The rows with NULL phone numbers will be automatically deleted 

The NULL values will be automatically converted to empty strings 

The ALTER TABLE statement will fail

The constraint will be added, but it will only apply to new rows A table Courses has a primary key CourseID. 

The ALTER TABLE statement will fail

A NOT NULL constraint requires that every existing row already has a valid (non-NULL) value.

Would using a line like this return anything?: WHERE Population = NULL;

No. For WHERE to return anything, it has to be TRUE. NULL = NULL will always equal NULL, not TRUE.

Would using a line like this return anything?: WHERE Population IS NULL;

Yes. If NULL exists in Population, then NULL IS NULL is TRUE, and WHERE clauses can only return things if it is TRUE.

The INSERT statement adds a student to the Student table. How many clauses are in the INSERT statement?

INSERT INTO Student

VALUES (888, 'Smith', 'Jim', 3.0);

A. 1

B. 2

C. 3

B. 2. One SQL statement is normally composed of multiple clauses. INSERT INTO is one clause, and VALUES is another clause.

INSERT is a ___ statement.
A. Data Definition Language

B. Data Query Language

C. Data Manipulation Language

D. Data Transaction Language

E. Data Control Language

C.

COMMIT is a ___ statement.
A. Data Definition Language

B. Data Query Language

C. Data Manipulation Language

D. Data Transaction Language

E. Data Control Language

D.

SELECT is a ___ statement.
A. Data Definition Language

B. Data Query Language

C. Data Manipulation Language

D. Data Transaction Language

E. Data Control Language

B.

GRANT is a ___ statement.
A. Data Definition Language

B. Data Query Language

C. Data Manipulation Language

D. Data Transaction Language

E. Data Control Language

E.

CREATE is a ___ statement.
A. Data Definition Language

B. Data Query Language

C. Data Manipulation Language

D. Data Transaction Language

E. Data Control Language

A.

If a table already contains rows that violate a newly added constraint, what happens when you ADD that constraint?

A. The constraint is added and old rows are ignored.

B. Adding the constraint fails until data is corrected.

C. The DBMS automatically repairs the rows.

D. The constraint applies only to new rows.

B. Adding the constraint fails until data is corrected. Adding a constraint is validated against existing data; if existing rows violate it, the ADD operation fails.

What is the best way to implement a many-to-many relationship?

A. Use a comma-separated list in one column.

B. Create a junction (association) table with two foreign keys.

C. Merge both entities into one table.

D. Use a JSON column in one table.

B. Create a junction (association) table with two foreign keys.

A proper many-to-many is implemented with a new table holding two FKs and typically a composite PK; storing comma-separated lists violates normalization.

In cardinality notation, "1..*" between A and B means:

A. A relates to exactly one B

B. A relates to zero or one B

C. A relates to one or many B (one-to-many)

D. Many-to-many

C. A relates to one or many B (one-to-many). 1..* means one-to-many (one on left relates to many on right); the others misinterpret notation.

When should you create an index on a column?

A. When it is frequently used in WHERE clauses and has good selectivity.

B. On every column to speed up all queries.

C. Only on text columns.

D. Never — indexes always slow everything down.

A. When it is frequently used in WHERE clauses and has good selectivity. Indexes help selective queries used often; indexing every column can hurt write performance and storage.

What is a B+tree index characteristic?

A. All indexed values appear only at the root.

B. Indexed values and pointers only appear at the top level.

C. All indexed values appear in the bottom level and pointers to table blocks are in the bottom level.

D. It is only used for hash joins.

C. All indexed values appear in the bottom level and pointers to table blocks are in the bottom level. B+tree stores values at the leaf level with pointers; this distinguishes it from older B-tree variants.

What is the effect of too many unused indexes on a table?

A. Faster inserts and updates.

B. Slower inserts, updates, and more storage usage.

C. No effect.

D. The DBMS drops unused indexes automatically.

B. Slower inserts, updates, and more storage usage. Unused indexes still incur maintenance cost on writes and use space, so too many degrade write performance.

What is a primary effect of block splits during insertions into an indexed table?

A. Improved locality of reference always.

B. Additional I/O and possible index maintenance overhead.

C. Immediate reduction in table size.

D. Loss of index entries.

B. Additional I/O and possible index maintenance overhead. Block splits during inserts require extra I/O and index reorganization; they don’t reduce table size and can hurt performance.

Which item is unstructured data?

A. A record representing one student
B. A video
C. A table that holds student data
D. A relational database that holds course registration data

B. A video itself is not an example of structured data, but various fields describing the video would be.

The database tracks that a student borrows a book from the library. What type of relationship is this?

Unary

Binary

Ternary

Reflexive

Binary.

Two entities participate in the relationship (Student and Book).

Example: A pet owner can have many pets; a specific pet is linked to one pet owner.

A. One-to-one
B. One-to-many
C. Many-to-many
D. Associative

B. Since there can only be one owner per pet and a pet owner can have many pets, the relationship is one-to-many.

What type of relationship is expressed with the phrase “A Painter paints one or more Paintings”?

A. 1:M
B. 1:1
C. M:1
D. M:N

A. If you think about a real artist, Da Vinci produced several works of art. The relationship between him and his paintings is 1:M (single artist, several works).

When designing a new database, it is a good idea to ____.

A. minimize data redundancy
B. include redundant fields
C. include a common field in all tables
D. use composite keys

A. Data redundancy creates more opportunity to introduce errors into the database, increased storage needs, and reduces query performance.

A ____ relationship exists when three entities are associated.

A. unary
B. binary
C. ternary
D. weak

C.

Unary means 1, Binary means 2, and Ternary means 3, which is also how many entities exist in each type of relationship.

Which attribute(s) make up the primary key in the table definition: CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_ROOM, PROF_NUM)

A. CRS_CODE
B. CLASS_SECTION
C. CRS_CODE and CLASS_SECTION
D. There is no primary key

C. The underlined values represent the two columns in a Composite Primary Key for this table.

Data redundancy produces ____.

A. slower lookups
B. robust design
C. efficient storage use
D. data integrity problems

D. Data redundancy can result in the same data needing to be updated in multiple locations, introducing more opportunity to violate data integrity.

Key ETL loading issue is ____.

A. mapping keys
B. refresh volume/frequency
C. content determination
D. denormalization

B. The loading component of the ETL process is centered on moving transformed data into the data warehouse. Key issues include target dependencies and refresh volume/frequency.

A database administrator needs to perform a massive, one-time data import where insert speed is the top priority and the order of rows does not matter. Which table structure is the best choice?

Sorted table

Hash table

Heap table

Table cluster

Heap table

A query needs to find all employees hired between January 1st and June 30th. Which table structure would be most efficient if the table is organized by HireDate?

Heap table

Hash table with EmployeeID as the hash key

Sorted table with HireDate as the sort column

Table cluster with the Department table

Sorted table with HireDate as the sort column

The primary purpose of a hash function is to:

Sort the rows within a block to speed up range queries.

Calculate a block address for a row based on its key value. 

Securely encrypt the primary key before it is stored.

Link free space from deleted rows together.

Calculate a block address for a row based on its key value. 

A database design physically stores related rows from the Invoices and InvoiceItems tables together in the same blocks based on InvoiceID. This is an example of a:

Hash table

Table cluster 

Heap table

Sorted table

Table cluster 

What is the primary performance trade-off when using a sorted table?

Faster inserts in exchange for slower range queries.

Reduced storage space in exchange for slower joins.

Faster ordered reads and range queries in exchange for slower inserts and updates. 

Faster single-row lookups in exchange for slower bulk loads.

Faster ordered reads and range queries in exchange for slower inserts and updates. 

A single-level index is best described as a file containing:

A complete copy of the table, but in a sorted order.

Only the primary key and a row count for the table.

Column values and pointers to the table blocks where rows with those values are located. 

A log of all insert, update, and delete operations on the table.

Column values and pointers to the table blocks where rows with those values are located. 

An index is a separate data structure that helps the database find rows quickly.

For a binary search to be an effective method for locating a value, what property must the index have?

It must be sorted. 

It must be a dense index.

It must be on a unique column.

It must be a sparse index.

It must be sorted. 

A binary search works by repeatedly splitting a dataset in half and eliminating one side.

Binary search works with 10, 20, 30, 40, 50 because you can compare middle value and eliminate the left or right half.

It doesn’t work with 40, 10, 60, 20, 50 because you cannot eliminate halves logically. Binary search breaks.

An index created on the sort column of a sorted table is known as a:

Primary index 

Secondary index 

Hash index

Multi-column index

Primary index 

A Users table is sorted by UserID. An index is then created on the LastLoginDate column. This index on LastLoginDate is a:

Primary index

Secondary index 

Sparse index

Cluster key

Secondary index 

An index that contains an entry for every single row in the table is called a:

Sparse index

Dense index 

Primary index

Clustered index

Dense index 

A Sales table has 10 million rows and is sorted by SaleDate. An index is created on SaleDate that only contains one entry for each data block, pointing to the first SaleDate value in that block. This is an example of a:

Sparse index 

Dense index

Secondary index

Hash index

Sparse index: Stores one index entry per data block (or page)

❌ Dense index: Has an entry for every row in the table. Not just each block.

❌ Secondary index: Refers to a non-primary ordering index

❌ Hash index: Uses a hash function to map keys to locations

How many primary indexes can a single sorted table have?

As many as there are columns.

Zero.

One.

It depends on the number of unique columns.

One

Which type of table can ONLY have secondary indexes?

A sorted table

A heap table 

A table cluster

A table with a primary key

A heap table: A table where data is stored in no particular order (not sorted by any key).

❌ A sorted table can be organized by a primary index

❌ A clustered table stores data physically sorted by a key, so it uses a clustered index

❌ A table with a primary key means it has a primary index, that already eliminates it as an option.

Why are sparse indexes significantly smaller and faster to scan than dense indexes?

They use a better compression algorithm.

They only contain one entry per table block. 

They are always stored in main memory, while dense indexes are on disk.

They do not store pointers, only column values.

They only contain one entry per table block. 

When a new row is inserted into a table with a dense index, what is the impact on the index?

A pointer in the table header is updated to point to the new row.

The index is marked as "dirty" but no entry is added immediately.

A new entry for the row must be created and inserted into the correct sorted position in the index. 

The index is not affected until it is manually rebuilt.

A new entry for the row must be created and inserted into the correct sorted position in the index. 

What happens to a table's index when a column that is NOT part of any index is updated on an existing row?

The entire index entry for that row must be updated.

The index entry is deleted and a new one is inserted.

The index is not affected at all. 

The index block containing the entry is marked as stale

The index is not affected at all. 

❌ If the column is not part of any index, there is no index entry that needs updated or deleted or marked as stale.

What is the primary disadvantage of using a table cluster?

It is slow for joins on the cluster key.

It uses significantly more disk space than separate tables.

It is often slow for full scans of a single table. 

It does not allow for primary keys.

It is often slow for full scans of a single table. 

Scans/searches of a table cluster are meant to view data based on both tables, not a single table.

❌ Cluster keys make JOINs faster.

❌ Tables in clusters can still have primary keys.

In a database, which term is often used interchangeably with "non-clustered index"?

Primary index

Secondary index 

Dense index

Sparse index

Secondary index 

When a row is deleted from a table, its corresponding entry in a dense index is typically:

Immediately physically removed, causing subsequent entries to be shifted.

Ignored, leaving an orphaned pointer in the index.

Marked as 'deleted' to be cleaned up later by a reorganization process. 

Moved to a separate "deleted entries" index.

Marked as 'deleted' to be cleaned up later by a reorganization process. 

If every delete required shifting index entries:

• performance would be very slow

• indexes would constantly be rewritten

❌ There is no “deleted entries” index

❌ Indexes don’t “ignore” deletions.

A Products table has its physical records on disk sorted according to its ProductID. The index built on ProductID is known as what?

A secondary index

A non-clustered index

A clustered index 

A bitmap index

A clustered index: The physical order of the table’s data matches the index order

"hit ratio" refers to:

The speed at which the index can be read from disk.

The percentage of table rows that are selected by a query. 

The amount of free space available in an index block.

The number of times a query successfully uses an index versus a table scan.

The number of times a query successfully uses an index versus a table scan.

Which condition would make a sparse index possible?

The index is on a non-unique column.

The underlying table is a heap table.

The underlying table is sorted on the same column as the index. 

The index is a secondary index.

The underlying table is sorted on the same column as the index. 

A sparse index works only when the data is physically ordered.

❌ Heap tables are not ordered.

❌ Uniqueness is not required for sparse indexes.

❌ Secondary vs primary index does not determine sparse capability.

In a hash table, a "bucket" is best described as:

A single row.

A block or a chain of linked blocks that stores rows with the same hash value. 

A pointer to free space.

An overflow area for when the table gets too full, that stores rows with the same hash value.

A block or a chain of linked blocks that stores rows with the same hash value. 

To maintain logical sort order efficiently without constantly moving physical rows during an insert, sorted tables often use:

A secondary heap structure.

A linked list of rows within each data block. 

An in-memory hash map of row locations.

A separate overflow block for all new inserts.

A linked list of rows within each data block. 

In a sorted (or indexed) table, keeping rows physically sorted all the time would be expensive because inserts would require shifting large amounts of data and disk operations would become slow. So instead, many systems use a linked list of rows within each data block. 

❌ Heap structures are NOT ordered.

❌ Hashing is NOT ordered.

❌ Overflow blocks don’t maintain sorted order by themselves.

Updating the value of an indexed column is often handled as:

A single, in-place update of the index entry.

A delete of the old index entry followed by an insert of the new index entry. 

A process that requires a full index rebuild.

An operation that is not allowed on indexed columns.

A delete of the old index entry followed by an insert of the new index entry. 

❌ Single in-place update isn’t allowed. If Bob → Row 22 is updated to Annie → Row 22, the sort of the table may be broken. Say it was sorted alphabetically, it would go:

Bill → Row 21, Annie → Row 22, Bob → Row 23.

That’s not in order anymore. A delete of the old index would remove it and an insert of the new value would automatically sort it, rather than update it in-place.

❌ Index rebuilds only happen for maintenance scenarios, not every update.

❌ Indexed columns are allowed to be updated.

A secondary index is always:

Sparse

Dense 

On a unique column

Slower than a table scan

Dense

Secondary index = lookup structure independent of physical order

So it must track all rows, which makes it dense.

❌ Uniqueness isn’t a factor

❌ Sparse indexes rely on sorted order. They say “I can skip around because the table is already ordered”. Remember secondary indexes are also called “non-clustered indexes” which don’t require a sorted order.

❌ Scanning an index is faster than scanning a whole table.

The main reason an index scan is generally faster than a table scan for a low-hit-ratio query is that:

The operating system gives priority to reading index files.

Index data is always uncompressed, while table data is compressed.

An index is much smaller than the full table. 

Indexes are write-only structures.

An index is much smaller than the full table.