M&IB Devx - Database
This is part of the DevX course assessment that will be taken by delegates before and after the programme.
Questions and Answers
- 1.
The first normal form (1NF) ensures that?
- A.
All attributes of a table are dependent on the entire primary key
- B.
All attributes are atomic, i.e. there are no repeating groups of data
- C.
All integrity constraints are met
- D.
All foreign keys are identified
Correct Answer
B. All attributes are atomic, i.e. there are no repeating groups of dataExplanation
The first normal form (1NF) ensures that all attributes are atomic, meaning there are no repeating groups of data. This means that each attribute in a table contains only a single value and cannot be further divided. This helps in eliminating redundancy and ensures that the data is organized in a structured manner. It also allows for easier data manipulation and retrieval.Rate this question:
-
- 2.
Which of the following describes a primary key?
- A.
A primary key uniquely identifies a row in a table
- B.
A primary key is the only key in that table
- C.
A primary key must always be a single column
- D.
Primary keys are not required in most databases
Correct Answer
A. A primary key uniquely identifies a row in a tableExplanation
A primary key is a unique identifier for each row in a table. It ensures that each row in the table can be uniquely identified and distinguishes it from other rows. This allows for efficient retrieval and manipulation of data within the table.Rate this question:
-
- 3.
Which of the following statements best describes "isolated", the "I" in ACID?
- A.
Transactions complete in one complete unit of work, or not at all.
- B.
Databases limit the number of concurrent users.
- C.
A transaction's effect is not visible to another transaction.
- D.
Databases must use ANSI-compliant SQL
Correct Answer
C. A transaction's effect is not visible to another transaction.Explanation
The correct answer is "A transaction's effect is not visible to another transaction." In the ACID (Atomicity, Consistency, Isolation, Durability) properties of database transactions, isolation refers to the concept that each transaction should operate independently and not be affected by or affect other concurrent transactions. This ensures that the changes made by one transaction are not visible to other transactions until the changes are committed. This level of isolation helps maintain data integrity and prevents conflicts or inconsistencies between concurrent transactions.Rate this question:
-
- 4.
Which best describes the relationship between the normal forms?
- A.
Each normal form is independent from the others and may be applied selectively
- B.
Higher normal form (e.g. 3NF) are dependent upon lower normal forms (e.g 1NF)
- C.
Lower normal form (e.g. 1NF) are dependent upon higher normal forms (e.g 3NF)
- D.
Higher normal forms guarantee better performance
Correct Answer
B. Higher normal form (e.g. 3NF) are dependent upon lower normal forms (e.g 1NF)Explanation
Higher normal forms (e.g. 3NF) are dependent upon lower normal forms (e.g 1NF) because each normal form builds upon the previous one. In order to achieve a higher normal form, the lower normal forms must first be satisfied. For example, to achieve 3NF, the database must first satisfy 1NF and 2NF. This is because each normal form introduces additional rules and requirements for data organization and eliminates certain types of data anomalies. Therefore, the lower normal forms serve as a foundation for achieving higher normal forms.Rate this question:
-
- 5.
What are the four attributes of an A.C.I.D. transaction?
- A.
Atomic, Consistent, Isolated and Durable
- B.
Atomic, Coherent, Immediate and Dependable
- C.
Atomic, Consistent, Immediate and Dependable
- D.
Atomic, Coherent, Isolated, Durable
Correct Answer
A. Atomic, Consistent, Isolated and DurableExplanation
The four attributes of an A.C.I.D. transaction are atomic, consistent, isolated, and durable. Atomic refers to the transaction being treated as a single unit of work that is either fully completed or fully rolled back. Consistent means that the transaction brings the database from one consistent state to another. Isolated ensures that concurrent transactions do not interfere with each other, and each transaction is executed as if it is the only one running. Durable guarantees that once a transaction is committed, its changes are permanent and will survive any subsequent failures.Rate this question:
-
- 6.
A primary key that consists of two or more columns is called?
- A.
Surrogate
- B.
Generated
- C.
Composite
- D.
Complex
Correct Answer
C. CompositeExplanation
A primary key that consists of two or more columns is called a composite key. This type of key is used when a single column cannot uniquely identify each record in a table, so multiple columns are combined to create a unique identifier. A composite key ensures that no two records in a table have the same combination of values in the specified columns, allowing for more precise data retrieval and integrity.Rate this question:
-
- 7.
Which THREE of the following are benefits of data normalization?
- A.
Clearer data relationships
- B.
Ease of adding data
- C.
Flexible data structure
- D.
Creates the database design
Correct Answer(s)
A. Clearer data relationships
B. Ease of adding data
C. Flexible data structureExplanation
Data normalization is a process that organizes data in a database to eliminate redundancy and improve efficiency. Clearer data relationships are a benefit of data normalization because it helps in understanding the relationships between different data entities. Ease of adding data is another benefit as it allows for easy insertion of new data without affecting the existing structure. Flexible data structure is also a benefit as it allows for modifications and updates to the database design without causing major disruptions.Rate this question:
-
- 8.
In a database, a Foreign Key is used for which of the following?
- A.
To make a relationship from an attribute in a table to the primary key of another table
- B.
To internationalize a primary key value
- C.
To enforce an optimistic locking strategy
- D.
To combine multiple primary key values into a single key
Correct Answer
A. To make a relationship from an attribute in a table to the primary key of another tableExplanation
A Foreign Key is used in a database to establish a relationship between two tables. It allows an attribute in one table to refer to the primary key of another table. By using a Foreign Key, data integrity can be maintained and referential integrity constraints can be enforced, ensuring that the relationship between the tables remains valid. This helps in creating efficient and organized database structures, enabling data retrieval and manipulation operations.Rate this question:
-
- 9.
Set theory implies which of the following is true of a database?
- A.
All tables have required ordering
- B.
Null values may never exist
- C.
Each row in the table is unique
- D.
The database must be in at least 2NF
Correct Answer
C. Each row in the table is uniqueExplanation
In set theory, each element in a set is unique. Similarly, in a database, each row in a table is unique. This means that no two rows in a table can have the exact same values for all columns. The uniqueness of rows is typically enforced by a primary key constraint, which ensures that each row has a unique identifier. Therefore, the statement "Each row in the table is unique" is true in a database.Rate this question:
-
- 10.
Which is these is a definition of a correlated subquery?
- A.
A subquery which references a table in the outer query
- B.
A subquery which references an outer join
- C.
A subquery which references an inner join
- D.
None of the above
Correct Answer
A. A subquery which references a table in the outer queryExplanation
A correlated subquery is a type of subquery that references a table in the outer query. This means that the subquery is dependent on the outer query for its execution and the results of the subquery are based on the values from the outer query. The subquery can access and use the values from the outer query to filter or retrieve data. This type of subquery is often used when we need to perform a calculation or comparison for each row in the outer query.Rate this question:
-
- 11.
Which THREE of the following are true about bulk processing?
- A.
Is a form of array processing
- B.
Reduces performance when processing a large number of rows
- C.
Allows a statement to process many rows
- D.
Fetches rows into a collection
Correct Answer(s)
A. Is a form of array processing
C. Allows a statement to process many rows
D. Fetches rows into a collectionExplanation
Bulk processing is a form of array processing where a statement can process many rows at once. It fetches rows into a collection, which allows for efficient processing. It is a useful technique when dealing with large amounts of data, as it can improve performance by reducing the number of round trips between the application and the database. Therefore, the three statements that are true about bulk processing are: it is a form of array processing, it allows a statement to process many rows, and it fetches rows into a collection.Rate this question:
-
- 12.
Which TWO of these are a reason to use database-specific features?
- A.
Useful for scalar functions
- B.
Upskills developers for that database
- C.
Makes it easier to port queries
- D.
Gets the best value out of our databases
Correct Answer(s)
A. Useful for scalar functions
D. Gets the best value out of our databasesExplanation
Using database-specific features can be useful for scalar functions because these functions are specific to a particular database and may not be available in other databases. This allows developers to leverage the unique capabilities of the database to enhance the functionality and performance of their scalar functions. Additionally, using database-specific features can help in getting the best value out of our databases by optimizing queries and utilizing the advanced features and optimizations provided by the database. This can lead to improved performance, efficiency, and overall utilization of the database.Rate this question:
-
- 13.
An explain plan shows how a statement was actually processed
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
An explain plan does not show how a statement was actually processed. Instead, it shows how the database optimizer plans to execute the statement. The explain plan provides information about the operations and their order, but it does not show the actual execution details or the resources used during the execution. Therefore, the given statement is false.Rate this question:
-
- 14.
All columns that are part of primary and unique keys are indexed automatically
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
When a column is part of a primary or unique key, it means that the values in that column must be unique and used to identify each row in the table. In order to enforce this uniqueness and optimize the performance of queries that involve these columns, the database automatically creates an index on them. This index allows for efficient searching and retrieval of data based on the values in these columns. Therefore, it is true that all columns that are part of primary and unique keys are indexed automatically.Rate this question:
-
- 15.
Each index used on a table decreases performance of Delete statements on that data
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
When an index is used on a table, it creates additional data structures that need to be maintained and updated whenever data is inserted, updated, or deleted. This means that when a delete statement is executed on a table with indexes, the database has to not only remove the row from the table but also update the index data structures accordingly. This additional work can slow down the performance of delete statements, making them slower compared to when no indexes are present. Therefore, the statement that each index used on a table decreases the performance of delete statements is true.Rate this question:
-
- 16.
Partitioning cannot be changed once it is implemented
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
Partitioning can be changed once it is implemented. Partitioning refers to the process of dividing a database table into smaller, more manageable parts called partitions. These partitions can be based on various criteria such as range, list, or hash. The purpose of partitioning is to improve performance and manageability of the database. If the need arises, the partitioning strategy can be altered by modifying the partitioning scheme or moving data between partitions. Therefore, the statement that partitioning cannot be changed once it is implemented is false.Rate this question:
-
- 17.
When do you design for database performance?
- A.
Once the system has run and you have relaible performance statistics
- B.
Just before the system is released into UAT
- C.
Up front as part of the database design process
- D.
None of the above
Correct Answer
C. Up front as part of the database design processExplanation
Designing for database performance should be done up front as part of the database design process. This means considering performance factors such as indexing, query optimization, and data storage requirements from the beginning. Waiting until the system has run and reliable performance statistics are available or just before the system is released into UAT may lead to suboptimal performance and require costly modifications. By designing for performance upfront, potential performance issues can be identified and addressed early on, resulting in a more efficient and scalable database system.Rate this question:
-
- 18.
Object-relational mapping tools present a table-centriic view of the database to application developers.
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
Object-relational mapping (ORM) tools actually present a more object-oriented view of the database to application developers, rather than a table-centric view. ORM tools allow developers to interact with the database using objects and classes, rather than directly dealing with tables and SQL queries. This abstraction simplifies the development process and allows for more efficient and flexible database operations. Therefore, the correct answer is False.Rate this question:
-
- 19.
What is the purpose of the Data Access Object (DAO) pattern?
- A.
To cache data that has previously been accessed from the database
- B.
To ensure transactional concurrency
- C.
To enforce referential integrity
- D.
To encapsulate database access
Correct Answer
D. To encapsulate database accessExplanation
The purpose of the Data Access Object (DAO) pattern is to encapsulate database access. This pattern provides a layer of abstraction between the application and the database, allowing the application to interact with the database without having to directly deal with the complexities of the underlying data access code. This helps to improve code maintainability, reusability, and testability by separating the business logic from the data access logic.Rate this question:
-
- 20.
PL/SQL variables are bind variables
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
PL/SQL variables are bind variables because they are used to hold data temporarily during the execution of a PL/SQL block. Bind variables are placeholders that are used to pass values between different parts of a program or between a program and the database. In PL/SQL, variables are declared and assigned values, and these values can be used in SQL statements or other parts of the program. The use of bind variables improves performance by reducing the parsing and execution time of SQL statements, as the values do not need to be re-parsed each time the statement is executed.Rate this question:
-
- 21.
Which TWO of following two approaches may be used to prevent SQL Injection attacks?
- A.
Use bind variables
- B.
Validate inputs
- C.
Use dynamic queries
- D.
Careful construction of SQL statements
Correct Answer(s)
A. Use bind variables
B. Validate inputsExplanation
Using bind variables and validating inputs are two approaches that can be used to prevent SQL Injection attacks.
Using bind variables involves using placeholders in SQL statements and then binding the actual values to those placeholders at runtime. This helps to separate the actual SQL code from the user input, preventing malicious SQL code from being injected.
Validating inputs involves checking and sanitizing user inputs before using them in SQL statements. This can include techniques like input validation, input filtering, and parameterized queries to ensure that only valid and safe inputs are used in the SQL statements.
Both of these approaches help to mitigate the risk of SQL Injection attacks by ensuring that user inputs are handled securely and that malicious code cannot be injected into the SQL statements.Rate this question:
-
- 22.
Each entry in an index points to a unique row in a table
- A.
True
- B.
FALSE
Correct Answer
B. FALSEExplanation
An index in a database does not necessarily point to a unique row in a table. It can point to multiple rows that have the same value for the indexed column. This is especially true for non-unique indexes, where multiple rows can have the same indexed value. Therefore, the statement that each entry in an index points to a unique row in a table is false.Rate this question:
-
- 23.
Reverse key indexes can be used to reduce contention on index blocks in high volume OLTP systems where the primary key is a sequence of numbers.
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
Reverse key indexes are a type of index in a database that can be used to reduce contention on index blocks in high volume OLTP (Online Transaction Processing) systems. In these systems, where the primary key is a sequence of numbers, using reverse key indexes can help distribute the data more evenly across index blocks, reducing the likelihood of contention and improving performance. Therefore, the statement that reverse key indexes can be used to reduce contention on index blocks in high volume OLTP systems where the primary key is a sequence of numbers is true.Rate this question:
-
- 24.
Full table scans should always be avoided, as they yield poorer performance than using an index.
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
Full table scans should not always be avoided, as there are cases where they can be more efficient than using an index. For example, if the table is small or if the query needs to retrieve a large portion of the data from the table, a full table scan can be faster. Additionally, if the table is not properly indexed or if the index is not selective enough, a full table scan may be the better option. Therefore, it is not always true that full table scans yield poorer performance than using an index.Rate this question:
-
- 25.
Which index type often works well for data that is not highly selective, such as boolean values?
- A.
A reverse-key index
- B.
A balanced B-tree
- C.
A function index
- D.
A bitmapped index
Correct Answer
D. A bitmapped indexExplanation
A bitmapped index often works well for data that is not highly selective, such as boolean values. This type of index uses a bitmap to represent the presence or absence of a value in a column. It is efficient for low cardinality columns where there are a limited number of distinct values. In the case of boolean values, there are only two possible values, so a bitmapped index can quickly determine which rows have a specific value. This type of index is particularly useful for queries that involve filtering or joining on boolean columns.Rate this question:
-
- 26.
Which of the following best describes domain integrity
- A.
Ensures column values are consistent between tables
- B.
Ensures columns are of the correct type and contain the correct set of values
- C.
Ensures columns contain the correct set of values
- D.
Ensures increased performance when accessing columns
Correct Answer
B. Ensures columns are of the correct type and contain the correct set of valuesExplanation
Domain integrity refers to the rules and constraints applied to the columns of a database table to ensure that they contain the correct type of data and adhere to a specific set of values. This ensures the accuracy and consistency of the data stored in the database. By enforcing these rules, domain integrity helps to maintain data quality and prevent data inconsistencies or errors.Rate this question:
-
- 27.
Second normal form (2NF) ensures that...
- A.
All columns have atomic values
- B.
No transitive dependencies exist in the database
- C.
All non-key attributes are dependent on the entire key.
- D.
No data anomolies are possible.
Correct Answer
C. All non-key attributes are dependent on the entire key.Explanation
The second normal form (2NF) ensures that all non-key attributes are dependent on the entire key. This means that every attribute in a table should be functionally dependent on the entire primary key and not on only a part of it. This helps in eliminating any partial dependencies and ensures that the table is properly normalized. By ensuring that all non-key attributes are dependent on the entire key, 2NF helps in avoiding data anomalies and maintaining data integrity.Rate this question:
-
- 28.
More indexes improve write performance to the database
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
This statement is false. Increasing the number of indexes in a database can actually decrease write performance. Indexes are used to speed up read operations by creating a separate data structure that allows for faster searching. However, when a write operation is performed, the database needs to update not only the actual data but also all the associated indexes. This additional overhead can slow down write performance. Therefore, more indexes do not necessarily improve write performance to the database.Rate this question:
-
- 29.
The "IN" operator in a WHERE clause operates on a set of values.
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
The "IN" operator in a WHERE clause allows for the comparison of a column value to a set of values. It checks if the column value matches any of the values in the set. This is useful when you want to filter rows based on multiple possible values for a column. For example, you can use the "IN" operator to find all customers whose country is either "USA" or "Canada". Therefore, the statement that the "IN" operator operates on a set of values is correct.Rate this question:
-
- 30.
Which of the following may suppress the use of an index?
- A.
Joining on character data
- B.
Joining on incompatible types
- C.
Using aggregate functions
- D.
Using the DISTINCT keyword
Correct Answer
B. Joining on incompatible typesExplanation
Joining on incompatible types may suppress the use of an index because when joining on incompatible types, the database engine may need to perform type conversions in order to compare the values. This can be a time-consuming process and can prevent the use of an index, which is designed to quickly retrieve and compare values. Incompatible types can also lead to errors or unexpected results in the join operation.Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 20, 2023Quiz Edited by
ProProfs Editorial Team -
Nov 06, 2013Quiz Created by
James Richardson
What Celebrity Do I Look Like Quiz? Discover Your Celebrity Look Alike
What Celebrity Do I Look Like Quiz? Discover Your Celebrity Look Alike
Book Genre Quiz: What Book Genre Is Right For You?
Book Genre Quiz: What Book Genre Is Right For You?
Back to top