Database Chapters 4,5,7 Midterm 2 Quiz

1. A transaction is the complete set of closely related update commands that must all be done, or none of them done, for the database to remain valid

True

True

False

Joining tables or using a subquery can produce the same result because both methods allow us to combine data from multiple tables. Joining tables involves combining rows from two or more tables based on a related column, while a subquery involves nesting one query inside another to retrieve data based on a condition. In both cases, we can obtain the desired result by selecting specific columns or applying filters. Therefore, it is true that joining tables or using a subquery can yield the same outcome.

Explanation

Joining tables or using a subquery can produce the same result because both methods allow us to combine data from multiple tables. Joining tables involves combining rows from two or more tables based on a related column, while a subquery involves nesting one query inside another to retrieve data based on a condition. In both cases, we can obtain the desired result by selecting specific columns or applying filters. Therefore, it is true that joining tables or using a subquery can yield the same outcome.

14. Designing physical files requires __________ of where and when data are used in various ways

Maps

Descriptions

Keys

When designing physical files, it is important to have descriptions of where and when data are used in various ways. This means that the designer needs to have a clear understanding of how the data will be accessed, manipulated, and utilized within the system. By having detailed descriptions of these aspects, the designer can ensure that the physical files are organized and structured in a way that supports efficient data management and retrieval.

Explanation

When designing physical files, it is important to have descriptions of where and when data are used in various ways. This means that the designer needs to have a clear understanding of how the data will be accessed, manipulated, and utilized within the system. By having detailed descriptions of these aspects, the designer can ensure that the physical files are organized and structured in a way that supports efficient data management and retrieval.

15. The natural join is very rarely used

True

False

The statement that the natural join is very rarely used is false. The natural join is a type of join in relational databases that combines rows from two tables based on matching values in columns with the same name and data type. It is commonly used when there is a need to combine data from multiple tables without explicitly specifying the join conditions. While other types of joins may be more commonly used in certain scenarios, the natural join still has its applications and can be a useful tool in database queries and operations.

Explanation

The statement that the natural join is very rarely used is false. The natural join is a type of join in relational databases that combines rows from two tables based on matching values in columns with the same name and data type. It is commonly used when there is a need to combine data from multiple tables without explicitly specifying the join conditions. While other types of joins may be more commonly used in certain scenarios, the natural join still has its applications and can be a useful tool in database queries and operations.

16. While triggers run automatically, ________________ do not and have to be called

Trapdoors

Routines

Selects

Updates

Routines do not run automatically like triggers. They need to be explicitly called or invoked in order to execute their defined set of instructions. Triggers, on the other hand, are automatically triggered by specific events or actions in the database. Therefore, the correct answer is "routines".

Explanation

Routines do not run automatically like triggers. They need to be explicitly called or invoked in order to execute their defined set of instructions. Triggers, on the other hand, are automatically triggered by specific events or actions in the database. Therefore, the correct answer is "routines".

17. A type of query that is palced within a WHERE or HAVING clause of another query is called a:

Is used to generate appropriate SQL code on the fly, as an application is processing

Is quite volatile

Creates a less flexible application

Dynamic SQL is a technique used to generate SQL code dynamically during runtime. It allows applications to construct and execute SQL statements based on specific conditions or user inputs. This flexibility enables developers to create more adaptable and customizable applications. Unlike static SQL, which is predefined and fixed, dynamic SQL allows for the generation of SQL code on the fly, making it suitable for scenarios where the SQL code needs to be determined at runtime.

Explanation

Dynamic SQL is a technique used to generate SQL code dynamically during runtime. It allows applications to construct and execute SQL statements based on specific conditions or user inputs. This flexibility enables developers to create more adaptable and customizable applications. Unlike static SQL, which is predefined and fixed, dynamic SQL allows for the generation of SQL code on the fly, making it suitable for scenarios where the SQL code needs to be determined at runtime.

28. SQL-invoked routines can be:

Procedures

Functions

All of the above

None of the above

SQL-invoked routines can be procedures or functions. Procedures are a set of SQL statements that perform a specific task and can be invoked using the CALL statement. Functions, on the other hand, return a single value and can be used in SQL statements. Therefore, both procedures and functions are examples of SQL-invoked routines, making "all of the above" the correct answer.

Explanation

SQL-invoked routines can be procedures or functions. Procedures are a set of SQL statements that perform a specific task and can be invoked using the CALL statement. Functions, on the other hand, return a single value and can be used in SQL statements. Therefore, both procedures and functions are examples of SQL-invoked routines, making "all of the above" the correct answer.

29. When all multivalued attributes have been removed from a relation , it is said to be in:

First normal form

Second normal form

Boyce-Codd normal form

Third normal form

When all multivalued attributes have been removed from a relation, it is said to be in the first normal form. This means that each attribute in the relation contains only atomic values, and there are no repeating groups or arrays of values within a single attribute. The first normal form is the basic level of normalization and ensures that the relation is free from redundancy and anomalies.

Explanation

When all multivalued attributes have been removed from a relation, it is said to be in the first normal form. This means that each attribute in the relation contains only atomic values, and there are no repeating groups or arrays of values within a single attribute. The first normal form is the basic level of normalization and ensures that the relation is free from redundancy and anomalies.

30. One field or combination of fields for which more than one record may have the same combination of values is called a(n):

Secondary key

Index

Composite key

Linked key

A secondary key is a field or combination of fields in a database table that allows more than one record to have the same combination of values. It is used to provide an alternate way to uniquely identify records in addition to the primary key. This can be useful in situations where there is a need to efficiently search and retrieve data based on criteria other than the primary key.

Explanation

A secondary key is a field or combination of fields in a database table that allows more than one record to have the same combination of values. It is used to provide an alternate way to uniquely identify records in addition to the primary key. This can be useful in situations where there is a need to efficiently search and retrieve data based on criteria other than the primary key.

31. Explicit commands to manage transactions are needed when:

A transaction consists of just one SQL command

Multiple SQL commands must be run as part of a transaction

Autocommit is set to off

None of the above

Explicit commands to manage transactions are needed when multiple SQL commands must be run as part of a transaction. In a transaction, multiple SQL commands are grouped together as a single unit of work that must be executed atomically. This means that either all the commands in the transaction are executed successfully, or none of them are executed at all. To ensure this atomicity, explicit commands such as BEGIN TRANSACTION, COMMIT, and ROLLBACK are used to start, end, and undo the transaction, respectively. If there is only one SQL command or if autocommit is set to off, these explicit commands are not necessary as each command is treated as a separate transaction.

Explanation

Explicit commands to manage transactions are needed when multiple SQL commands must be run as part of a transaction. In a transaction, multiple SQL commands are grouped together as a single unit of work that must be executed atomically. This means that either all the commands in the transaction are executed successfully, or none of them are executed at all. To ensure this atomicity, explicit commands such as BEGIN TRANSACTION, COMMIT, and ROLLBACK are used to start, end, and undo the transaction, respectively. If there is only one SQL command or if autocommit is set to off, these explicit commands are not necessary as each command is treated as a separate transaction.

32. START CH5 - A method to allow adjacent secondary memory space to contain rows from several tables is called:

Cluttering

Clustering

Concatenating

Compiling

Clustering is a method that allows adjacent secondary memory space to contain rows from several tables. It involves grouping similar data together based on certain criteria, such as common attributes or values. This helps improve data retrieval and processing efficiency by reducing the need to access multiple locations in secondary memory. Clustering can be particularly beneficial in database management systems where tables with related data can be physically stored close to each other, resulting in faster query execution and improved performance.

Explanation

Clustering is a method that allows adjacent secondary memory space to contain rows from several tables. It involves grouping similar data together based on certain criteria, such as common attributes or values. This helps improve data retrieval and processing efficiency by reducing the need to access multiple locations in secondary memory. Clustering can be particularly beneficial in database management systems where tables with related data can be physically stored close to each other, resulting in faster query execution and improved performance.

33. Distributing the rows of data into separate files is called:

Normalization

Horizontal partitioning

Vertical partitioning

File allocation

Horizontal partitioning is the correct answer because it refers to the process of distributing rows of data into separate files. This technique is commonly used in database management systems to improve performance and scalability. By dividing the data horizontally, each partition can be stored and processed independently, allowing for parallel processing and reducing the load on individual servers. Horizontal partitioning is especially useful in scenarios where the dataset is too large to fit on a single server or when different parts of the data need to be accessed or modified separately.

Explanation

Horizontal partitioning is the correct answer because it refers to the process of distributing rows of data into separate files. This technique is commonly used in database management systems to improve performance and scalability. By dividing the data horizontally, each partition can be stored and processed independently, allowing for parallel processing and reducing the load on individual servers. Horizontal partitioning is especially useful in scenarios where the dataset is too large to fit on a single server or when different parts of the data need to be accessed or modified separately.

34. All of the following are common denormalization opportunities EXCEPT:

Two entities with a one-to-one relationship

A one-to-many relationship

A many-to-many relationship with nonkey attributes

Reference data

Denormalization is a technique used in database design to improve performance by adding redundant data to eliminate the need for complex joins. In a one-to-many relationship, there is already a natural redundancy as the "one" side can be repeated multiple times in the "many" side. Therefore, denormalizing this relationship would not provide any additional benefits. However, in a one-to-one relationship, a many-to-many relationship with nonkey attributes, or with reference data, denormalization can be beneficial as it can reduce the number of joins required and improve query performance.

Explanation

Denormalization is a technique used in database design to improve performance by adding redundant data to eliminate the need for complex joins. In a one-to-many relationship, there is already a natural redundancy as the "one" side can be repeated multiple times in the "many" side. Therefore, denormalizing this relationship would not provide any additional benefits. However, in a one-to-one relationship, a many-to-many relationship with nonkey attributes, or with reference data, denormalization can be beneficial as it can reduce the number of joins required and improve query performance.

35. A type of join where a table is joined to itself is called a(n)

Unary join

Self join

Unnatural join

Pinned join

A self join is a type of join where a table is joined to itself. This is useful when we want to combine rows from the same table based on a related condition. In a self join, two instances of the same table are treated as separate entities, with different aliases assigned to them. This allows us to compare and match rows within the same table, enabling us to retrieve information that relates to each other.

Explanation

A self join is a type of join where a table is joined to itself. This is useful when we want to combine rows from the same table based on a related condition. In a self join, two instances of the same table are treated as separate entities, with different aliases assigned to them. This allows us to compare and match rows within the same table, enabling us to retrieve information that relates to each other.

36. An attribute (or attributes) that uniquely identifies each row in a relation is called a:

Column

Foreign field

Primary key

Duplicate key

A primary key is an attribute or set of attributes that uniquely identifies each row in a relation. It ensures that there are no duplicate values in the primary key column(s) and provides a way to uniquely identify and access each row in the table. This is essential for maintaining data integrity and for establishing relationships between tables in a database.

Explanation

A primary key is an attribute or set of attributes that uniquely identifies each row in a relation. It ensures that there are no duplicate values in the primary key column(s) and provides a way to uniquely identify and access each row in the table. This is essential for maintaining data integrity and for establishing relationships between tables in a database.

37. The smallest unit of application data recognized by system software is a:

Field

Row

Data type

Column

A field is the smallest unit of application data recognized by system software. It refers to a single piece of information within a record or a database. For example, in a database table of student records, a field could represent the student's name, age, or ID number. Fields are used to organize and store data in a structured manner, allowing for efficient retrieval and manipulation of information.

Explanation

A report is desired that lists all customers who placed an order

A report is desired that lists all customers and the total of their orders

There is never a situation that requires only an outer join

In this situation, an outer join would be required to obtain the desired results because it includes customers who did not place an order during the most recent month. An outer join allows for the combination of data from two tables, in this case the customers table and the orders table, even if there is no matching data in the orders table. This ensures that all customers are included in the report, regardless of whether they placed an order or not during the most recent month.

Explanation

In this situation, an outer join would be required to obtain the desired results because it includes customers who did not place an order during the most recent month. An outer join allows for the combination of data from two tables, in this case the customers table and the orders table, even if there is no matching data in the orders table. This ensures that all customers are included in the report, regardless of whether they placed an order or not during the most recent month.

55. Two or more attributes having different names but the same meaning are called:

Homonyms

Aliases

Synonyms

Alternate attributes

Synonyms are two or more attributes that have different names but the same meaning. In other words, they are different terms used to describe the same concept or attribute. For example, "car" and "automobile" are synonyms as they both refer to the same object. Similarly, in the context of databases, synonyms can be used to refer to different attribute names that represent the same underlying data.

Explanation

Synonyms are two or more attributes that have different names but the same meaning. In other words, they are different terms used to describe the same concept or attribute. For example, "car" and "automobile" are synonyms as they both refer to the same object. Similarly, in the context of databases, synonyms can be used to refer to different attribute names that represent the same underlying data.

56. A candidate key must satisfy all of the following conditions EXCEPT:

The key must uniquely identify the row

The key must indicate the rows position in the table

The key must be nonredundant

Each nonkey attribute is functionally dependent upon it

A candidate key is a unique identifier for a row in a table. It must satisfy the conditions of being nonredundant, ensuring that each nonkey attribute is functionally dependent upon it, and uniquely identifying the row. However, it does not need to indicate the row's position in the table. The position of a row in a table is typically determined by the order in which it was inserted, but it is not a requirement for a candidate key.

Explanation

A candidate key is a unique identifier for a row in a table. It must satisfy the conditions of being nonredundant, ensuring that each nonkey attribute is functionally dependent upon it, and uniquely identifying the row. However, it does not need to indicate the row's position in the table. The position of a row in a table is typically determined by the order in which it was inserted, but it is not a requirement for a candidate key.

57. All of the following are the main goals of normalization EXCEPT:

Minimize data redundancy

Simplify the enforcement of referential integrity

Maximize storage space

Make it easier to maintain data

Normalization is a database design technique that aims to minimize data redundancy and improve data integrity. It simplifies the enforcement of referential integrity by ensuring that relationships between tables are properly defined and maintained. It also makes it easier to maintain data by organizing it into smaller, more manageable tables. However, maximizing storage space is not a goal of normalization. Instead, normalization focuses on optimizing data structure and reducing duplication to improve efficiency and maintainability.

Explanation

Normalization is a database design technique that aims to minimize data redundancy and improve data integrity. It simplifies the enforcement of referential integrity by ensuring that relationships between tables are properly defined and maintained. It also makes it easier to maintain data by organizing it into smaller, more manageable tables. However, maximizing storage space is not a goal of normalization. Instead, normalization focuses on optimizing data structure and reducing duplication to improve efficiency and maintainability.

58. When a regular entity type contains a multivalued attribute, one must:

Create a single relation with multiple lines for each instance of the multivalued attribute

Create two new relations, one containing the multivalued attribute

Create two new relations, both containing the multivalued attribute

None of the above

When a regular entity type contains a multivalued attribute, creating two new relations, one containing the multivalued attribute, is the correct approach. This is because a multivalued attribute cannot be represented in a single relation with multiple lines for each instance of the attribute. Instead, it requires a separate relation to properly capture the multiple values. Therefore, the option of creating two new relations, one containing the multivalued attribute, is the appropriate solution in this scenario.

Explanation

When a regular entity type contains a multivalued attribute, creating two new relations, one containing the multivalued attribute, is the correct approach. This is because a multivalued attribute cannot be represented in a single relation with multiple lines for each instance of the attribute. Instead, it requires a separate relation to properly capture the multiple values. Therefore, the option of creating two new relations, one containing the multivalued attribute, is the appropriate solution in this scenario.

59. ________________ is a component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated.

Business rule constraint

Data integrity

Business integrity

Data structure

Data integrity is a component of the relational data model that ensures the accuracy, consistency, and reliability of data. It specifies the rules and constraints that need to be followed when manipulating data to maintain its integrity. These rules can include constraints such as primary key uniqueness, foreign key relationships, and data validation rules. By enforcing data integrity, organizations can ensure that their data remains reliable and consistent, thereby improving the overall quality and trustworthiness of their data.

Explanation

Data integrity is a component of the relational data model that ensures the accuracy, consistency, and reliability of data. It specifies the rules and constraints that need to be followed when manipulating data to maintain its integrity. These rules can include constraints such as primary key uniqueness, foreign key relationships, and data validation rules. By enforcing data integrity, organizations can ensure that their data remains reliable and consistent, thereby improving the overall quality and trustworthiness of their data.

60. A method for handling missing data is to:

Substitute and estimate for the missing data

Track missing data with special reports

Perform sensitivity testing

All of the above

The correct answer is "all of the above" because each of the options mentioned - substituting and estimating for the missing data, tracking missing data with special reports, and performing sensitivity testing - are valid methods for handling missing data. By using a combination of these approaches, one can effectively address the issue of missing data and ensure that it does not significantly impact the overall analysis or results.

Explanation

The correct answer is "all of the above" because each of the options mentioned - substituting and estimating for the missing data, tracking missing data with special reports, and performing sensitivity testing - are valid methods for handling missing data. By using a combination of these approaches, one can effectively address the issue of missing data and ensure that it does not significantly impact the overall analysis or results.

61. RANK and DENSE-RANK are examples of:

Ceilings

Door functions

Window functions

Moving functions

RANK and DENSE-RANK are examples of window functions. Window functions are used to perform calculations across a set of rows that are related to the current row. They allow us to partition the data into smaller groups and perform calculations within each group. RANK and DENSE-RANK specifically assign a unique rank or dense rank to each row based on a specified order.

Explanation

RANK and DENSE-RANK are examples of window functions. Window functions are used to perform calculations across a set of rows that are related to the current row. They allow us to partition the data into smaller groups and perform calculations within each group. RANK and DENSE-RANK specifically assign a unique rank or dense rank to each row based on a specified order.

62. An understanding of how to merge relation is important because:

A) there may be a need to merge relations on projects with subteams

Different views may be integrated

New data requirements may produce new relations to be merged

All of the above

An understanding of how to merge relations is important because there may be a need to merge relations on projects with subteams, different views may need to be integrated, and new data requirements may produce new relations that need to be merged.

Explanation

An understanding of how to merge relations is important because there may be a need to merge relations on projects with subteams, different views may need to be integrated, and new data requirements may produce new relations that need to be merged.

63. Which of the following anomalies result from a transitive dependency?

Insertion

Modification

Deletion

All of the above

A transitive dependency is a relationship between three or more attributes in a database table, where the value of one attribute determines the value of another attribute indirectly. In this case, all of the given anomalies (insertion, modification, and deletion) can occur as a result of a transitive dependency. When a transitive dependency exists, inserting, modifying, or deleting a value in one attribute can cause inconsistencies or errors in the related attributes. Therefore, all of the mentioned anomalies can be caused by a transitive dependency.

Explanation

A transitive dependency is a relationship between three or more attributes in a database table, where the value of one attribute determines the value of another attribute indirectly. In this case, all of the given anomalies (insertion, modification, and deletion) can occur as a result of a transitive dependency. When a transitive dependency exists, inserting, modifying, or deleting a value in one attribute can cause inconsistencies or errors in the related attributes. Therefore, all of the mentioned anomalies can be caused by a transitive dependency.

64. Another form of demornalization where the same data are stored in multiple places in the database is called:

Data duplication

Data replication

Advanced placement

Horizaontal partitioning

Data replication refers to the practice of storing the same data in multiple places within a database. This is done to improve data availability, reliability, and performance. By duplicating data, it becomes possible to access it from different locations, reducing the risk of data loss and ensuring that the system can continue to function even if one copy of the data becomes unavailable. Data replication is commonly used in distributed databases and systems that require high levels of data availability and fault tolerance.

Explanation

Data replication refers to the practice of storing the same data in multiple places within a database. This is done to improve data availability, reliability, and performance. By duplicating data, it becomes possible to access it from different locations, reducing the risk of data loss and ensuring that the system can continue to function even if one copy of the data becomes unavailable. Data replication is commonly used in distributed databases and systems that require high levels of data availability and fault tolerance.

65. Extensions defined in SQL-99 that include the capability to create and drop modules of code stored in the database schema across user sessions are called:

Stored procedures

Persistent stored modules

Flow control modules

None of the above

Persistent stored modules are extensions defined in SQL-99 that allow the creation and deletion of code modules stored in the database schema across multiple user sessions. These modules are designed to persist in the database and can be accessed and executed by different users at different times. This capability provides a way to store and manage reusable code within the database, enhancing code organization and promoting code reusability.

Explanation

Persistent stored modules are extensions defined in SQL-99 that allow the creation and deletion of code modules stored in the database schema across multiple user sessions. These modules are designed to persist in the database and can be accessed and executed by different users at different times. This capability provides a way to store and manage reusable code within the database, enhancing code organization and promoting code reusability.

66. Horizontal partitioning makes sense:

When different categories of a table's rows are processed seperately

When less security is needed

When partitions must be orgranized the same

When all of the above are true

Horizontal partitioning makes sense when different categories of a table's rows are processed separately. This means that the rows of the table are divided into separate partitions based on specific categories or criteria. By doing so, it becomes easier to perform operations or queries on specific categories of data without affecting the entire table. This can improve performance and efficiency in situations where different categories of data require different processing or analysis.

Explanation

Horizontal partitioning makes sense when different categories of a table's rows are processed separately. This means that the rows of the table are divided into separate partitions based on specific categories or criteria. By doing so, it becomes easier to perform operations or queries on specific categories of data without affecting the entire table. This can improve performance and efficiency in situations where different categories of data require different processing or analysis.

67. Which of the following is an objective of selecting a data type?

Represent a small number of possible values

Maximize storage space

Limit security

Improve data integrity

Selecting a data type helps improve data integrity by ensuring that the data is stored and processed correctly. By choosing the appropriate data type, data can be validated and constraints can be applied to prevent incorrect or inconsistent data from being entered. This helps in maintaining the accuracy and reliability of the data, reducing errors and improving overall data quality.

Explanation

Selecting a data type helps improve data integrity by ensuring that the data is stored and processed correctly. By choosing the appropriate data type, data can be validated and constraints can be applied to prevent incorrect or inconsistent data from being entered. This helps in maintaining the accuracy and reliability of the data, reducing errors and improving overall data quality.

68. A procedure is:

Stored within the databse

Given a unique name

Called by name

All of the above

A procedure refers to a set of instructions or operations that are stored within a database. It is given a unique name to identify and distinguish it from other procedures. These procedures are called by their respective names to execute the instructions they contain. Therefore, the correct answer is "all of the above" as all the statements mentioned - being stored within the database, having a unique name, and being called by name - are true for a procedure.

Explanation

A procedure refers to a set of instructions or operations that are stored within a database. It is given a unique name to identify and distinguish it from other procedures. These procedures are called by their respective names to execute the instructions they contain. Therefore, the correct answer is "all of the above" as all the statements mentioned - being stored within the database, having a unique name, and being called by name - are true for a procedure.

69. If the DBA wishes to describe all tables in the database, which data dictionary view should be accessed in Oracle?

DBA_tab_privs

DBA_tab_comments

DBA_table_label

DBA_tables

The correct answer is DBA_tables. This data dictionary view in Oracle contains information about all tables in the database. It includes details such as table names, owner names, number of rows, and table creation and modification timestamps. By accessing this view, the DBA can obtain a comprehensive description of all tables in the database.

Explanation

The correct answer is DBA_tables. This data dictionary view in Oracle contains information about all tables in the database. It includes details such as table names, owner names, number of rows, and table creation and modification timestamps. By accessing this view, the DBA can obtain a comprehensive description of all tables in the database.

70. In order for two quieries to be UNION-compatible, they must:

Both have the same number of lines in their SQL statements

Both output compatible data types for each column and return the same number of rows

Both return at least one row

All of the above

For two queries to be UNION-compatible, they must both output compatible data types for each column and return the same number of rows. This means that the columns in both queries should have the same data types and the number of rows returned by each query should be equal. This ensures that the result of the UNION operation will have consistent data types and a predictable number of rows.

Explanation

For two queries to be UNION-compatible, they must both output compatible data types for each column and return the same number of rows. This means that the columns in both queries should have the same data types and the number of rows returned by each query should be equal. This ensures that the result of the UNION operation will have consistent data types and a predictable number of rows.

71. The UNION clause is used to:

Combine the output from multiple queries into a single result table

Join two tables together to form one table

Find all rows that do not match in two tables

None of the above

The UNION clause is used to combine the output from multiple queries into a single result table. This means that it allows you to retrieve data from multiple tables or queries and display the results as if they came from a single table. By using the UNION clause, you can merge the rows returned by each query and eliminate duplicates, resulting in a unified and comprehensive result set.

Explanation

The UNION clause is used to combine the output from multiple queries into a single result table. This means that it allows you to retrieve data from multiple tables or queries and display the results as if they came from a single table. By using the UNION clause, you can merge the rows returned by each query and eliminate duplicates, resulting in a unified and comprehensive result set.

72. CH -4 A nonkey attribute is also called a(n)

Column

Unimportant datum

Descriptor

Address

A nonkey attribute is also called a descriptor because it provides additional information about an entity but does not play a role in identifying or distinguishing that entity. It is not a critical piece of data in the database and does not contribute to the primary key or any unique identifier. Instead, it helps to describe or provide details about the entity.

Explanation

A nonkey attribute is also called a descriptor because it provides additional information about an entity but does not play a role in identifying or distinguishing that entity. It is not a critical piece of data in the database and does not contribute to the primary key or any unique identifier. Instead, it helps to describe or provide details about the entity.

73. Which of the following are anomalies that can be caused by redundancies in tables

Insertion

Deletion

Modification

All of the above

Redundancies in tables can lead to anomalies such as insertion anomalies, deletion anomalies, and modification anomalies. Insertion anomalies occur when it is not possible to insert certain data into the table without also inserting unrelated data. Deletion anomalies occur when deleting certain data also results in the unintentional loss of other related data. Modification anomalies occur when updating certain data requires making changes in multiple places, leading to inconsistencies. Therefore, all of the options mentioned (insertion, deletion, and modification) can be caused by redundancies in tables.

Explanation

Redundancies in tables can lead to anomalies such as insertion anomalies, deletion anomalies, and modification anomalies. Insertion anomalies occur when it is not possible to insert certain data into the table without also inserting unrelated data. Deletion anomalies occur when deleting certain data also results in the unintentional loss of other related data. Modification anomalies occur when updating certain data requires making changes in multiple places, leading to inconsistencies. Therefore, all of the options mentioned (insertion, deletion, and modification) can be caused by redundancies in tables.

74. The relational data model consists of which components?

Data structure

Data manipulation

Data integrity

All of the above

The relational data model consists of all of the mentioned components: data structure, data manipulation, and data integrity. Data structure refers to the organization and format of the data stored in tables. Data manipulation involves operations such as inserting, updating, and deleting data in the tables. Data integrity ensures that the data is accurate, consistent, and follows predefined rules or constraints. Therefore, all of these components are essential in the relational data model.

Explanation

The relational data model consists of all of the mentioned components: data structure, data manipulation, and data integrity. Data structure refers to the organization and format of the data stored in tables. Data manipulation involves operations such as inserting, updating, and deleting data in the tables. Data integrity ensures that the data is accurate, consistent, and follows predefined rules or constraints. Therefore, all of these components are essential in the relational data model.

75. A(n) _______________ is a routine that converts a primary key value into a relative record number.

Record index calculator

Index pointer program

Hashing algorithm

Pointing algorithm

A hashing algorithm is a routine that converts a primary key value into a relative record number. It uses a mathematical function to map the key value to a specific location in a data structure, such as a hash table. This allows for efficient retrieval and storage of records based on their primary key values.

Explanation

A hashing algorithm is a routine that converts a primary key value into a relative record number. It uses a mathematical function to map the key value to a specific location in a data structure, such as a hash table. This allows for efficient retrieval and storage of records based on their primary key values.

76. LAST CH 4 A form of database specification that indicates all the parameters for data storage that are then input to database implementation is:

Logical

Physical

Schematic

Conceptual

The correct answer is "physical". In database specification, the physical level focuses on the actual implementation of the database and includes details such as file organization, indexing methods, and storage structures. It specifies how the data is physically stored on the storage medium. Logical and conceptual levels deal with the logical organization and structure of the database, while the schematic level focuses on the design and organization of individual database components.

Explanation

The correct answer is "physical". In database specification, the physical level focuses on the actual implementation of the database and includes details such as file organization, indexing methods, and storage structures. It specifies how the data is physically stored on the storage medium. Logical and conceptual levels deal with the logical organization and structure of the database, while the schematic level focuses on the design and organization of individual database components.

77. ___________ takes a value of true if a subquery returns an intermediate results table which contains one or more rows

IN

HAVING

EXISTS

EXTENTS

The EXISTS operator takes a value of true if a subquery returns an intermediate results table which contains one or more rows. It is used to check for the existence of rows in a subquery and can be used in the WHERE clause of a SQL statement.

Explanation

The EXISTS operator takes a value of true if a subquery returns an intermediate results table which contains one or more rows. It is used to check for the existence of rows in a subquery and can be used in the WHERE clause of a SQL statement.

78. A file organization that uses hashing to map a key into a location in an index where there is a pointer to the actual data record matching the hash key is called a:

Hashed file organization

Hash key

Multi-indexed file organization

Hash index table

A file organization that uses hashing to map a key into a location in an index where there is a pointer to the actual data record matching the hash key is called a hash index table. This type of file organization utilizes a hash function to generate a unique index for each key, allowing for efficient retrieval of data records by directly accessing the index table. The index table contains pointers to the actual data records, enabling quick and direct access to the desired data based on the hash key.

Explanation

A file organization that uses hashing to map a key into a location in an index where there is a pointer to the actual data record matching the hash key is called a hash index table. This type of file organization utilizes a hash function to generate a unique index for each key, allowing for efficient retrieval of data records by directly accessing the index table. The index table contains pointers to the actual data records, enabling quick and direct access to the desired data based on the hash key.

79. Transaction integrity commands are not used to identify whole units of database changes that must be completed in full for the database to retain integrity

True

False

Transaction integrity commands are actually used to identify whole units of database changes that must be completed in full for the database to retain integrity. These commands ensure that all changes made within a transaction are either committed or rolled back as a single unit, preventing partial updates and maintaining the consistency and integrity of the database. Therefore, the correct answer is False.

Explanation

True

False

An equi-join is a type of join where the equality condition is applied on two or more columns from different tables. It returns rows that have matching values in the specified columns. In an equi-join, duplicate columns are not eliminated in the result table. Instead, all columns from the joined tables are included in the result, including any duplicate columns. Therefore, the given statement that an equi-join eliminates one of the duplicate columns in the result table is false.

Explanation

An equi-join is a type of join where the equality condition is applied on two or more columns from different tables. It returns rows that have matching values in the specified columns. In an equi-join, duplicate columns are not eliminated in the result table. Instead, all columns from the joined tables are included in the result, including any duplicate columns. Therefore, the given statement that an equi-join eliminates one of the duplicate columns in the result table is false.

86. One major advantage of the outer join is that:

Information is easily accessible

Information is not lost

The query is easier to write

All of the above

The correct answer is "information is not lost". This is because in an outer join, all the rows from one table are included in the result set, even if there is no matching row in the other table. This ensures that no information is lost during the join operation.

Explanation

The correct answer is "information is not lost". This is because in an outer join, all the rows from one table are included in the result set, even if there is no matching row in the other table. This ensures that no information is lost during the join operation.

87. A primary key whose value is unique across all relations is called a(n):

Global primary key

Inter-table primary key

Enterprise key

Foreign global key

An enterprise key is a primary key that is unique across all relations in a database. This means that no other relation within the database can have the same value for this key. It is used to uniquely identify records across multiple tables in the entire enterprise system. This ensures data integrity and consistency throughout the database, allowing for efficient data retrieval and manipulation.

Explanation

An enterprise key is a primary key that is unique across all relations in a database. This means that no other relation within the database can have the same value for this key. It is used to uniquely identify records across multiple tables in the entire enterprise system. This ensures data integrity and consistency throughout the database, allowing for efficient data retrieval and manipulation.

88. Which of the following are properties of relations

Each attribute has a unique name

No two rows in a relation are identical

There are no multivalued attributes are in a relation

All of the above

All of the given options are properties of relations. In a relation, each attribute must have a unique name to identify it uniquely. Additionally, no two rows in a relation can be identical, as each row represents a unique tuple. Lastly, relations in a database do not allow multivalued attributes, meaning that each attribute in a relation can have only a single value. Therefore, all of the given options are correct properties of relations.

Explanation

All of the given options are properties of relations. In a relation, each attribute must have a unique name to identify it uniquely. Additionally, no two rows in a relation can be identical, as each row represents a unique tuple. Lastly, relations in a database do not allow multivalued attributes, meaning that each attribute in a relation can have only a single value. Therefore, all of the given options are correct properties of relations.

89. In which data model would a code table appear

Conceptual

Logical

Physical

Data layout

A code table would appear in the physical data model. The physical data model represents the actual implementation of the database and includes details such as data types, indexes, and storage structures. Code tables are used to store codes or values that are frequently used in the database, such as status codes or category codes. They provide a standardized and efficient way to store and retrieve these values, making them an important component of the physical data model.

Explanation

A code table would appear in the physical data model. The physical data model represents the actual implementation of the database and includes details such as data types, indexes, and storage structures. Code tables are used to store codes or values that are frequently used in the database, such as status codes or category codes. They provide a standardized and efficient way to store and retrieve these values, making them an important component of the physical data model.

90. In order to embed SQL inside of another language, the __________ statement must be placed before the SQL in the host language

GET SQL

EXEC SQL

RUN SQL

SQL SQL

To embed SQL inside of another language, the "EXEC SQL" statement must be placed before the SQL in the host language. This statement signals the host language that the following code is SQL and needs to be executed accordingly. By using "EXEC SQL" before the SQL code, the host language can properly interpret and execute the SQL statements.

Explanation

To embed SQL inside of another language, the "EXEC SQL" statement must be placed before the SQL in the host language. This statement signals the host language that the following code is SQL and needs to be executed accordingly. By using "EXEC SQL" before the SQL code, the host language can properly interpret and execute the SQL statements.

91. A type of join implemented in SQL-1999 and by extension SQL-2003 that returns all of the data from each table that is joined is called a(n)

Outer join

Inner join

Union join

Intersect join

An outer join is a type of join implemented in SQL-1999 and by extension SQL-2003 that returns all of the data from each table that is joined. In an outer join, if there is no match between the tables on the join condition, null values are returned for the columns of the table that does not have a match. This allows for the inclusion of unmatched rows from both tables in the result set. A union join, on the other hand, is not a valid type of join in SQL.

Explanation

An outer join is a type of join implemented in SQL-1999 and by extension SQL-2003 that returns all of the data from each table that is joined. In an outer join, if there is no match between the tables on the join condition, null values are returned for the columns of the table that does not have a match. This allows for the inclusion of unmatched rows from both tables in the result set. A union join, on the other hand, is not a valid type of join in SQL.

92. The most commonly used form of join operation is the:

Outer join

Union join

Equi-join

Natural join

The natural join is the most commonly used form of join operation. It combines rows from two tables based on matching values in their common columns. It returns only the rows where the values in the common columns match in both tables. This type of join is useful when you want to combine data from two tables that have a common attribute. It eliminates the need to specify the join condition explicitly, as it automatically matches the columns with the same name in both tables.

Explanation

The natural join is the most commonly used form of join operation. It combines rows from two tables based on matching values in their common columns. It returns only the rows where the values in the common columns match in both tables. This type of join is useful when you want to combine data from two tables that have a common attribute. It eliminates the need to specify the join condition explicitly, as it automatically matches the columns with the same name in both tables.

93. A constraint between two attributes is called a(n):

Functional relation

Attribute dependency

Functional dependency

Functional relation constraint

A constraint between two attributes is called a functional dependency. This means that one attribute's value is determined by another attribute's value. In other words, if you know the value of one attribute, you can determine the value of the other attribute. This constraint helps maintain data integrity and ensures that the database remains consistent.

Explanation

A constraint between two attributes is called a functional dependency. This means that one attribute's value is determined by another attribute's value. In other words, if you know the value of one attribute, you can determine the value of the other attribute. This constraint helps maintain data integrity and ensures that the database remains consistent.

94. One advantage of partitioning is:

Efficiency

Remote optimization

Extra space and update time

Both A and B

Partitioning provides the advantage of efficiency. By dividing a larger problem or dataset into smaller, more manageable parts, partitioning allows for parallel processing and distributed computing. This can significantly improve the overall efficiency of a system or algorithm, as multiple tasks can be executed simultaneously. Additionally, partitioning can help optimize remote operations, as data can be distributed across different nodes or servers, reducing network latency and improving performance. Therefore, the correct answer is efficiency.

Explanation

A listing of all customers in the customer table

A listing of the owener of the customer table

A listing of the owner of the customer table as well as customers

The given SQL statement will produce a listing of the owner of the customer table. The SELECT statement is retrieving the "owner" and "table_name" columns from the "dba_tables" table. The WHERE clause filters the results to only include rows where the "table_name" is equal to 'CUSTOMERS'. Therefore, the output will consist of the owner(s) of the customer table(s) that match the specified condition.

Explanation

The given SQL statement will produce a listing of the owner of the customer table. The SELECT statement is retrieving the "owner" and "table_name" columns from the "dba_tables" table. The WHERE clause filters the results to only include rows where the "table_name" is equal to 'CUSTOMERS'. Therefore, the output will consist of the owner(s) of the customer table(s) that match the specified condition.

101. A two-dimensional table of data is called a:

Group

Set

Declaration

Relation

A two-dimensional table of data is commonly referred to as a "relation" in the field of database management. This term is used to describe the organization and structure of data in a tabular format, where each row represents a record and each column represents a specific attribute or field. The use of the term "relation" emphasizes the interconnectedness and interdependence of the data within the table, highlighting the ability to establish relationships and perform operations such as joins and queries.

Explanation

A two-dimensional table of data is commonly referred to as a "relation" in the field of database management. This term is used to describe the organization and structure of data in a tabular format, where each row represents a record and each column represents a specific attribute or field. The use of the term "relation" emphasizes the interconnectedness and interdependence of the data within the table, highlighting the ability to establish relationships and perform operations such as joins and queries.

102. A contiguous section of disk storage space is called a(n):

Track

Sector

Extent

Tablespace

An extent is a contiguous section of disk storage space. It is a range of consecutive blocks allocated to a file or database object. Extents are used to efficiently allocate and manage disk space, as they reduce fragmentation and improve I/O performance. Therefore, an extent is the correct term for a contiguous section of disk storage space.

Explanation

An extent is a contiguous section of disk storage space. It is a range of consecutive blocks allocated to a file or database object. Extents are used to efficiently allocate and manage disk space, as they reduce fragmentation and improve I/O performance. Therefore, an extent is the correct term for a contiguous section of disk storage space.

103. A relation that contains minimal redundancy and allows easy use is considered to be:

Clean

Simple

Complex

Well structured

A relation that is well structured means that it is organized and designed in a way that minimizes redundancy, meaning that there is no unnecessary repetition of data. It also implies that the relation is easy to use and navigate, making it user-friendly. Therefore, a well-structured relation is the one that contains minimal redundancy and allows easy use.

Explanation

Primary

Recursive primary

Composite

Recursive foreign

If a foreign key is referenced to the primary key in the same relation, it is called a recursive foreign key. This means that the foreign key is pointing back to the same table and is used to establish a relationship between different rows within the same table. This can be useful in scenarios where there is a hierarchical or recursive relationship between entities in the table.

Explanation

If a foreign key is referenced to the primary key in the same relation, it is called a recursive foreign key. This means that the foreign key is pointing back to the same table and is used to establish a relationship between different rows within the same table. This can be useful in scenarios where there is a hierarchical or recursive relationship between entities in the table.

Database Chapters 4,5,7 Midterm 2 Quiz

1. A transaction is the complete set of closely related update commands that must all be done, or none of them done, for the database to remain valid

2.

What first name or nickname would you like us to use?

3. When EXISTS or NOT EXISTS is used in a subquery, the select list of the subquery will usually just select all columns as a placeholder because it doesnt matter which columns are returned

4. Persistent stored modules are extensions defined in SQL:1999 that include the capability to add and drop modules of code

5. In order to find out what customers have not placed an order for a particular item, one might use the NOT qualifier along with the IN qualifier

6. RDBMS's store database definition information in system-created tables which can be considered a data dictionary

7. The UNION clause is used to combine the output from multiple queries into a single result table

8. An SQL query that implements an outer join will return rows that do not have matching values in common columns

9. The joining condition of an equi-join based upon an equality

10. SQL:200n allows one to calculate linear regressions, moving averages and correlations without moving the data outside of the database

11. Combining a table with iself results in a faster query

12. A correlated subquery is executed once for each iteration through the outer loop

13. Joining tables or using a subquery may produce the same result

14. Designing physical files requires __________ of where and when data are used in various ways

15. The natural join is very rarely used

16. While triggers run automatically, ________________ do not and have to be called

17. A type of query that is palced within a WHERE or HAVING clause of another query is called a:

18. START CH 7 - Constraints are a special case of triggers

19. When the value of one attribute (the determinant) determines the value of another attribute, it is called:

20. The __________ states that no primary key attribute may be null

21. DBA_USERS contains comments on all tables in an Oracle databse

22. Correlated subqueries are less efficient than queries that do not use nesting

23. A join operation:

24. A primary key that consists of more than one attribute is called a:

25. In which type of file is multiple key retrieval not possible

26. EXISTS takes a value of false if the subquery returns an intermediate result set

27. Dynamic SQL:

28. SQL-invoked routines can be:

29. When all multivalued attributes have been removed from a relation , it is said to be in:

30. One field or combination of fields for which more than one record may have the same combination of values is called a(n):

31. Explicit commands to manage transactions are needed when:

32. START CH5 - A method to allow adjacent secondary memory space to contain rows from several tables is called:

33. Distributing the rows of data into separate files is called:

34. All of the following are common denormalization opportunities EXCEPT:

35. A type of join where a table is joined to itself is called a(n)

36. An attribute (or attributes) that uniquely identifies each row in a relation is called a:

37. The smallest unit of application data recognized by system software is a:

38. IF-THEN-ELSE logical processing cannot be accomplished within an SQL statement

39. Which of the following is not a new data type that were added in SQL:200n

40. A _________ is a temporary table used in the FROM clause of an SQL query

41. The entity integrity rule states that:

42. Embedded SQL consists of:

43. In SQL, a(n) _________ subquery is a type of subquery in which processing the inner query depends on data from the outer query

44. A rule that states that each foreign key value must match a primary key value in the other

45. One disadvantage of partitioning is:

46. Sensitivity testing involves:

47. All of the following are valid datatypes in Oracle 11g EXCEPT

48. A key decision in the physical design process is:

49. All of the following are guidelines for better query design EXCEPT:

50. An attribute that may have more than one meaning is called a(n):

51. An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a:

52. A relation that contains no multivalued attributes, and has nonkey attributes solely dependent on the primary key, but contains transitive dependencies is in which normal form?

53. Physical database design decisions must be made carefully because of impacts on:

54. In which of the following situations would one have to use an outer join in order to obtain the desired results?

55. Two or more attributes having different names but the same meaning are called:

56. A candidate key must satisfy all of the following conditions EXCEPT:

57. All of the following are the main goals of normalization EXCEPT:

58. When a regular entity type contains a multivalued attribute, one must:

59. ________________ is a component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated.

60. A method for handling missing data is to:

61. RANK and DENSE-RANK are examples of:

62. An understanding of how to merge relation is important because:

63. Which of the following anomalies result from a transitive dependency?

64. Another form of demornalization where the same data are stored in multiple places in the database is called:

65. Extensions defined in SQL-99 that include the capability to create and drop modules of code stored in the database schema across user sessions are called:

66. Horizontal partitioning makes sense:

67. Which of the following is an objective of selecting a data type?

68. A procedure is:

69. If the DBA wishes to describe all tables in the database, which data dictionary view should be accessed in Oracle?

70. In order for two quieries to be UNION-compatible, they must:

71. The UNION clause is used to:

72. CH -4 A nonkey attribute is also called a(n)

73. Which of the following are anomalies that can be caused by redundancies in tables

74. The relational data model consists of which components?

75. A(n) _______________ is a routine that converts a primary key value into a relative record number.

76. LAST CH 4 A form of database specification that indicates all the parameters for data storage that are then input to database implementation is:

77. ___________ takes a value of true if a subquery returns an intermediate results table which contains one or more rows

78. A file organization that uses hashing to map a key into a location in an index where there is a pointer to the actual data record matching the hash key is called a: