D426 True/False

"Employee-Manages-Employee" is a good relationship name.

TRUE or FALSE?

True.

Sometimes relationships relate an entity to itself. Since "Manages" is the active form of "manage", "Employee-Manages-Employee" is a good relationship name.

Must entity, relationship, and attribute synonyms follow naming conventions?

Yes

No

No.

Synonyms should reflect common usage. Unlike official names, synonyms do not always follow naming conventions.

A DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. Which of the following is NOT one of those functions?

A. Data integrity management
B. Data storage management
C. Data reports
D. Security management

C. Data based reports from a database are generated using analytics tools rather than DBMS.

A Departments table has a ManagerID and an AdminAssistID, both of which are foreign keys to the Employees table. Is this a valid table design? 

No, a table can only have one foreign key 

No, a table can only have one foreign key referencing a specific primary key 

Yes, this is a valid design

Yes, but only if the foreign key columns have different data types

Yes, this is a valid design

A table can absolutely have multiple foreign keys, even if they all reference the same parent table.

ManagerID → references Employees(EmployeeID)

AdminAssistID → also references Employees(EmployeeID)

"Department-IsManagedBy-Employee" is a good relationship name.

TRUE or FALSE?

False.

"IsManagedBy" is the passive form of the verb "manage". Although relationship names are sometimes passive verbs, active verbs are more concise. "Employee-Manages-Department" uses an active verb and is a better relationship name.

Entities, relationships, and attributes always map directly to tables, foreign keys, and columns, respectively.

TRUE or FALSE?

False. In the logical design phase, entities, relationships, and attributes usually become tables, foreign keys, and columns. Sometimes, however, an entity splits into several tables, several entities merge into one table, and relationships and attributes become tables.

1. They have a physical table named AllEmployees that stores raw data for every employee.

2. From this, they create a standard view called MarketingDept_V to show only the employees in the Marketing department.

3. They create another standard view named SeniorMarketers_V which selects employees with 5+ years of experience from the MarketingDept_V view.

Based on this scenario, which of the following statements is NOT true?

The AllEmployees table is the only base table in this scenario.

For the SeniorMarketers_V view, the MarketingDept_V view is considered its base table.

The SeniorMarketers_V view is a nested view because its query uses another view as its source.

For the SeniorMarketers_V view, the MarketingDept_V view is considered its base table.

A base table is an actual physical table that stores data.

Using materialized views always improves database performance.

TRUE or FALSE?

FALSE.

Base table INSERTs, UPDATEs, and DELETEs must refresh materialized views and therefore are slower.

The performance of a query on a non-materialized view is identical to the performance of the corresponding merged query on base tables.

TRUE or FALSE?

True

The database converts a query against a non-materialized view to a merged query and then executes the merged query. The performance is the same as if the user entered the merged query.

A view query can reference another view table.

TRUE or FALSE?

True.

A view is a table and can appear in any SELECT statement. Since a view query is a SELECT statement, view queries can reference other view tables.

In MySQL, two different queries that generate the same result table always have the same execution time.

TRUE or FALSE?

FALSE.

MySQL efficiently compiles SQL queries. However no database always compiles equivalent queries in exactly the same way. As a result, equivalent queries often have different execution times.

Think of it this way. There are many different ways to query a database that result in the same table. It all depends on how the query is written.

Views can be used to hide rows as well as columns from database users.

TRUE or FALSE?

TRUE.

Columns can be excluded in the SELECT clause of a view query. Rows can be excluded in the WHERE clause of a view query. If a user is authorized to access the view but not the base table, the user cannot see excluded columns or rows.

The expression Age BETWEEN 13 AND 19 is TRUE when Age is 19.

TRUE or FALSE?

True. You can read the expression like <= and >=

If the Name column has data type VARCHAR(20), then the expression Name BETWEEN 'Anele' AND 'Jose' is valid.

TRUE or FALSE?

TRUE. BETWEEN also works with strings. It’s just asking if ‘Anele’ and ‘Jose’ work with VARCHAR(20) and it does, and it is also asking if it is valid given the use of BETWEEN.

Depending on the database, names like Bob, Carol, David, and Jennifer would be included because they fall alphabetically between ‘Anele’ and ‘Jose’.

A query containing BETWEEN expression runs faster than a query containing an equivalent expression written with comparison operators.

TRUE or FALSE?

FALSE. It’s asking if WHERE Salary BETWEEN 50000 AND 100000

is faster than WHERE Salary >= 50000 AND Salary <= 100000

Neither is faster, they are effectively the same thing. MySQL compiles a BETWEEN expression and the equivalent comparison operator expression to the same executable code. So the two expressions run in the same time.

SELECT select_expr [, select_expr] ...

[FROM table_references]

[WHERE where_condition]

[ORDER BY {col_name | expr | position}]

select_expr is an expression that appears in a SELECT clause, and may not be blank.

SELECT 'Hello'; is a valid statement. T/F?

True. The FROM, WHERE, and ORDER BY clauses are enclosed in brackets, and therefore optional. A statement containing only a SELECT clause is valid when each select expression consists of a literal, like 'Hello'.

Which of these is true about sets in the relational model?

A. Rows in a table are ordered and can repeat.

B. Rows are unordered and duplicates are not allowed (as entire-row duplicates).

C. Columns may have different names across rows.

D. A set cannot be empty

B. Rows are unordered and duplicates are not allowed (as entire-row duplicates).

Tables are sets of tuples (no inherent order and no identical duplicate rows), A and D are false and C is irrelevant.

Which statement about the ORDER BY clause is true?

A. ORDER BY must come before GROUP BY.

B. ORDER BY guarantees physical row order on disk.

C. ORDER BY sorts the result set by one or more columns.

D. ORDER BY can only sort ascending

C. ORDER BY sorts the result set by one or more columns. ORDER BY controls result presentation; it doesn't guarantee physical storage order (B), and it can sort ascending or descending.

Which of the following is true about character encodings and storage?

A. CHAR and VARCHAR always use the same number of bytes per character.

B. VARCHAR uses length + 1 bytes (in many systems) while CHAR uses fixed N bytes.

C. TEXT uses less storage than VARCHAR for short strings.

D. BLOB should be used for textual Unicode data

B. VARCHAR uses length + 1 bytes (in many systems) while CHAR uses fixed N bytes. CHAR is fixed-length padding to N bytes, VARCHAR stores only used length plus overhead bytes; TEXT/BLOB and encoding choices affect storage differently.

Which statement is true about CREATE INDEX?

A. It changes query results.

B. It always reduces storage use.

C. It improves query performance for some queries.

D. It removes duplicate rows.

C. It improves query performance for some queries. CREATE INDEX can speed queries that use indexed columns but does not change results; it increases storage and can slow writes.

Which statement about MERGE is true?

A. It deletes tables.

B. It combines source data into a target by inserting/updating depending on matches.

C. It creates an index on the combined tables.

D. It is identical to UNION.

B. It combines source data into a target by inserting/updating depending on matches. MERGE (or UPSERT-style operations) conditionally INSERTs or UPDATEs; it does not delete tables or create indexes.

Which of the following is true about foreign keys?

A. They must be unique.

B. They must be NOT NULL.

C. They refer to a primary key and enforce referential integrity.

D. They are only suggestions and not enforced.

C. They refer to a primary key and enforce referential integrity. Foreign keys enforce referential integrity by referencing primary keys; they need not be unique and can be NULL unless constrained.

Which statement about clustered (primary) and nonclustered (secondary) indexes is true?

A. A table can have many clustered indexes.

B. Clustered index determines table sort order; there can usually be only one.

C. Secondary indexes control physical row order.

D. Clustered indexes are always stored in memory.

B. Clustered index determines table sort order; there can usually be only one. A clustered (primary) index defines storage order and typically only one exists; many nonclustered indexes are allowed.

Which of these is true about partitioning?

A. Partitioning always makes queries faster.

B. Partitioning divides a table into subsets (partitions) often by range or list to improve performance for some queries.

C. Partitions are only vertical (columns).

D. Partitioning removes the need for indexes.

B. Partitioning divides a table into subsets (partitions) often by range or list to improve performance for some queries. Partitioning is horizontal (typically) and helps some query patterns; it’s not universally faster and does not replace indexes.

Which of these is true about function (expression) indexes?

A. They index the raw column value only.

B. They index the result of a function applied to the column, helping queries that filter using that function.

C. They are always faster than B+tree indexes.

D. They cannot be used for WHERE clauses.

B. They index the result of a function applied to the column, helping queries that filter using that function. Function indexes store function results to support queries that apply that function; they don’t always outperform B+trees universally.

Which of these is true about UNIQUE constraints?

A. UNIQUE implies NOT NULL by default.

B. UNIQUE allows duplicate values.

C. UNIQUE ensures values in a column (or group) are distinct; it can be named and dropped via DROP INDEX in some systems.

D. UNIQUE is the same as PRIMARY KEY.

C. UNIQUE ensures values in a column (or group) are distinct; it can be named and dropped via DROP INDEX in some systems. UNIQUE enforces distinctness and does not automatically imply NOT NULL (some systems treat NULLs specially), and PRIMARY KEY combines UNIQUE + NOT NULL.

Which of the following statements concerning the primary key is true?

A. All primary key entries are unique.
B. The primary key may be null.
C. The primary key is not required for all tables.
D. The primary key data do not have to be unique.

A. Rules for a primary key are that they are Unique and Not Null.

SELECT select_expr [, select_expr] ...

[FROM table_references]

[WHERE where_condition]

[ORDER BY {col_name | expr | position}]

select_expr is an expression that appears in a SELECT clause, and may not be blank.

SELECT FROM City; is a valid statement. T/F?

False. In the definition, the first select expression is not enclosed in brackets and hence is required. The select expression may not be empty, so the statement is not valid.

SELECT select_expr [, select_expr] ...

[FROM table_references]

[WHERE where_condition]

[ORDER BY {col_name | expr | position}]

select_expr is an expression that appears in a SELECT clause, and may not be blank.

The FROM clause must contain table names only. T/F?

False. In the definition, the FROM clause is followed by table_references, defined elsewhere, not table_name. A table reference may include complex language elements.

SELECT select_expr [, select_expr] ...

[FROM table_references]

[WHERE where_condition]

[ORDER BY {col_name | expr | position}]

select_expr is an expression that appears in a SELECT clause, and may not be blank.

The ORDER BY keyword must be followed by a column name. T/F?

False. The braces after ORDER BY indicate that one of three alternatives may be specified: a column name, an expression, or the column position within the table. expr and position are defined elsewhere in the documentation.