Data Management (Foundations) D426

Correct Answer:

ALTER

Explanation:

Data Definition Language (DDL) commands are used to define, modify, and manage database structures such as tables, schemas, and indexes. The ALTER command specifically allows changes to an existing table’s structure, such as adding or removing columns, changing data types, or renaming objects. Since it manipulates the schema rather than the data itself, ALTER is correctly classified as a DDL command.

Why Other Options Are Wrong:

SELECT

SELECT is a Data Query Language (DQL) command used to retrieve data from tables. It does not alter the structure or definition of database objects, so it is not part of DDL.

INSERT

INSERT is a Data Manipulation Language (DML) command used to add new rows of data into a table. While it changes data content, it does not change the schema or structure of the database.

FETCH

FETCH is associated with retrieving rows from a cursor in SQL. Like SELECT, it is about accessing data, not defining or altering database structures, so it does not belong to DDL.

Correct Answer

B. Primary key

Explanation

A primary key is used to uniquely identify each row in a database table. Each primary key value is unique and not null, ensuring that every row can be precisely identified. This is a fundamental concept in relational databases.

Why other options are wrong

A. Foreign key

A foreign key is used to establish a relationship between two tables, linking a column in one table to a primary key in another. It is not used to uniquely identify a row in the current table.

C. Plural column

A plural column refers to a column that may hold multiple values for a single record, which violates the principles of first normal form (1NF). It does not serve the purpose of uniquely identifying a row.

D. Multi-valued column

A multi-valued column holds more than one value in a single column for a row, which also violates 1NF. It is not used to uniquely identify rows in a table.

Correct Answer

A. To specify a range of values for a column

Explanation

The BETWEEN operator is used in SQL to filter the result set within a certain range. It simplifies the syntax for checking whether a value falls between two specified limits, including the boundary values. This makes queries more concise and easier to read when working with ranges.

Why other options are wrong

B. To perform a logical comparison of two columns

This describes the use of comparison operators like =, <, or > rather than the BETWEEN operator. BETWEEN does not compare columns to each other but checks if a column’s value is within a specified range. Therefore, this is not an accurate description of its function.

C. To define a sequence of actions

Defining a sequence of actions is typically handled using procedural extensions like PL/SQL or control-of-flow statements, not the BETWEEN operator. BETWEEN is strictly used in filtering query results based on a defined range.

D. To link two separate conditions

Linking conditions is done using logical operators such as AND or OR in SQL. BETWEEN simplifies range checking into a single condition rather than linking multiple conditions. Hence, this option does not reflect the actual use of BETWEEN.

Correct Answer:

composite

Explanation:

When no single column can uniquely identify a record, multiple columns can be combined to form a composite key. This ensures that the combination of values across these columns uniquely identifies each row. Composite keys are widely used in relational database design, especially in junction tables that model many-to-many relationships. They maintain data integrity while avoiding duplication.

Why Other Options Are Wrong:

joined

Although it sounds related, "joined" is not the correct term in database terminology for a key made up of multiple columns. Joins are operations used to combine rows from different tables, not to define keys.

aggregate

Aggregate refers to functions like SUM, AVG, COUNT, etc., which perform calculations on sets of values. It has no relation to primary or candidate keys in database design, so this is an incorrect option.

union

Union is an SQL set operator used to combine results from two queries. It does not refer to key structures and cannot uniquely identify rows within a single table. Therefore, it is not the right choice.

Correct Answer

A. NOT will be evaluated first; AND will be evaluated second; OR will be evaluated last.

Explanation

In SQL, logical operators follow a specific order of precedence. The NOT operator has the highest precedence, followed by AND, and then OR. This means that if there are no parentheses to alter the order, NOT will be evaluated first, then AND, and finally OR.

Why other options are wrong

B. NOT will be evaluated first; OR will be evaluated second; AND will be evaluated last.

This is incorrect because AND has a higher precedence than OR. Therefore, AND will be evaluated before OR, not after.

C. AND will be evaluated first; OR will be evaluated second; NOT will be evaluated last.

This is incorrect because NOT has the highest precedence and should be evaluated before both AND and OR.

D. The order of occurrence determines the order of evaluation.

This is incorrect because the order of occurrence is overridden by operator precedence. Parentheses can alter the evaluation order, but without them, SQL follows the predefined precedence rules.

Correct Answer

 A. To connect two tables with a many-to-many relationship

Explanation

A junction table is used to resolve a many-to-many relationship between two tables. It contains foreign keys that reference the primary keys of the two tables being connected. This allows multiple records from both tables to be associated with each other, providing a way to store many-to-many relationships efficiently.

Why other options are wrong

B. To store primary keys

While a junction table contains foreign keys, it is not primarily used to store primary keys. It stores relationships between tables, with the foreign keys referencing the primary keys of the related tables. The primary key itself is typically stored in the main tables, not the junction table.

C. To perform complex queries

Junction tables are not designed specifically for performing complex queries. They are meant to store the relationship between tables in a many-to-many relationship. Complex queries can involve multiple tables, including junction tables, but the table itself is not responsible for the complexity of the queries.

D. To enforce referential integrity

While junction tables play a role in maintaining relationships between tables, they do not inherently enforce referential integrity. Referential integrity is enforced by the database system through constraints like FOREIGN KEY, not by the junction table itself.

Correct Answer

A. A view for which data is stored and needs to be refreshed when base tables change

Explanation

A materialized view is a database object that stores the results of a query as actual data, rather than just the SQL statement. The data is physically stored and needs to be refreshed periodically or when the underlying base tables change. This differs from a regular view, which is virtual and does not store data, only the query definition.

Why other options are wrong

B. Virtual table created as a result of a SELECT query

This describes a regular view, not a materialized view. A regular view does not store data; it only stores the SQL query used to generate the result, and the data is re-computed every time the view is accessed.

C. A view that cannot be updated or altered

This is incorrect because a materialized view can be updated and altered, though the process of refreshing the data in the view is separate from altering the view itself. The statement does not accurately describe materialized views.

D. A view that only displays the most recent data

While materialized views do need refreshing to display the most current data, they are not restricted to showing only the most recent data. The view can be refreshed on a schedule or when triggered, but it does not automatically display only the most recent data unless specifically managed that way.

Correct Answer

B. many

Explanation

In a one-to-many relationship, the foreign key is placed in the table on the "many" side of the relationship. This is because the foreign key is used to reference the primary key in the table on the "one" side, establishing the relationship between the two tables. For example, in a relationship where one department can have many employees, the foreign key (department ID) will be placed in the employee table.

Why other options are wrong

A. weak

A weak entity is one that cannot be uniquely identified without the help of another entity. Weak entities are not relevant to the placement of foreign keys in a one-to-many relationship.

C. many-to-many

In a many-to-many relationship, a junction table is typically used to represent the relationship, which contains foreign keys referencing both related tables. The foreign key does not reside on just one side in a many-to-many relationship but is instead distributed across the junction table.

D. one

In a one-to-many relationship, the foreign key is placed in the table on the "many" side, not the "one" side. The "one" side will have the primary key, which is referenced by the foreign key on the "many" side.

E. cardinal

Cardinal refers to the cardinality of the relationship (such as one-to-many or many-to-many), not to the location of the foreign key in a one-to-many relationship.

Correct Answer:

Representing high-level data requirements without focusing on implementation details

Explanation:

An entity-relationship (ER) model is a conceptual tool used in database design to describe the high-level structure of data. It defines entities, relationships, attributes, and constraints without diving into how the database will be physically implemented. This helps stakeholders and designers visualize data requirements clearly, ensuring that the logical design aligns with real-world needs before transitioning into implementation and optimization.

Why Other Options Are Wrong:

Storing and retrieving data from a database

This describes the function of an operational database system, not the role of an ER model. ER models are used during the design phase, not execution.

Optimizing the performance of database queries

Query optimization happens after implementation, often involving indexing, query tuning, and execution plans. ER models do not directly optimize performance.

Implementing database tables and relationships

While ER models guide the creation of tables, the actual implementation happens at the physical database level using SQL Data Definition Language (DDL). The ER model itself is conceptual.