Databases Midterm Review

Q1. Which of the following is NOT an advantage of using the database approach over traditional file

processing?

A) Program-data independence

B) Increased data redundancy

C) Improved data sharing

D) Reduced program maintenance

B) Increased data redundancy

Q2. In the three-schema architecture, the _______ schema describes the physical storage structures and access

paths.

A) Conceptual

B) External

C) Internal

D) Logical

C) Internal

Q3. Which of the following best describes logical data independence?

A) The ability to change physical storage without affecting the conceptual schema

B) The ability to change the conceptual schema without affecting external schemas and applications

C) The ability to access data through multiple servers

D) The separation of data into multiple locations

B) The ability to change the conceptual schema without affecting external schemas and applications

Q4. The _______ is a collection of concepts that describes the structure of a database and the basic operations.

A) Database instance

B) Database schema

C) Data model

D) Database state

C) Data model

Q5. Which type of user is responsible for defining the content, structure, and constraints of the database?

A) Database administrator

B) Application developer

C) Database designer

D) End-user

C) Database designer

Q6. Physical data independence refers to:

A) The ability to change the conceptual schema without affecting applications

B) The ability to change the internal schema without affecting the conceptual schema

C) The separation of logical and physical storage

D) The distribution of data across multiple servers

B) The ability to change the internal schema without affecting the conceptual schema

Q7. Which of the following is NOT a characteristic of the database approach?

A) Self-describing nature

B) Data independence

C) Data redundancy is always minimized

D) Support for multiple user views

C) Data redundancy is always minimized

Q8. The _______ is responsible for authorizing access to the database and monitoring its efficiency.

A) Database designer

B) Application developer

C) Database administrator

D) System analyst

C) Database administrator

Q9. In the three-schema architecture, mappings between schemas are necessary to:

A) Encrypt data

B) Transform requests and data between levels

C) Backup data

D) Create redundancy

B) Transform requests and data between levels

Q10. Which of the following best describes a database instance?

A) The schema of the database

B) The actual data stored in the database at a particular moment

C) A copy of the database on a separate server

D) The metadata of the database

B) The actual data stored in the database at a particular moment

Q11. Which of the following is NOT a type of attribute in the ER model?

A) Simple attribute

B) Composite attribute

C) Derived attribute

D) Atomic attribute

D) Atomic attribute

Q12. In an ER diagram, a _______ represents a relationship between entities from more than two entity types.

A) Binary relationship

B) Unary relationship

C) N-ary relationship

D) Recursive relationship

C) N-ary relationship

Q13. Which of the following best describes a weak entity type?

A) An entity that has partial key attributes

B) An entity that does not have key attributes of its own and is identified through a relationship with a strong entity

C) An entity with only composite attributes

D) An entity with multiple partial keys

B) An entity that does not have key attributes of its own and is identified through a relationship with a strong entity

Q14. In the cardinality ratio notation for binary relationships, which of the following represents a one-to-many

relationship?

A) 1:1

B) 1:N

C) M:N

D) N:N

B) 1:N

Q15. A _______ relationship type involves the same entity type participating more than once in different roles.

A) Binary

B) Ternary

C) Recursive

D) Aggregate

C) Recursive

Q16. In an ER diagram using (min, max) notation, what does (0,1) mean for an entity's participation in a

relationship?

A) The entity must participate in at least 1 relationship instance

B) The entity must participate in exactly 1 relationship instance

C) The entity may or may not participate (0 or 1 relationship instance)

D) The entity can participate in multiple instances up to 1

C) The entity may or may not participate (0 or 1 relationship instance)

Q17. Which constraint specifies whether each entity in an entity type must participate in a relationship set?

A) Cardinality constraint

B) Participation constraint

C) Domain constraint

D) Key constraint

B) Participation constraint

Q18. An entity type is represented in an ER diagram as:

A) An ellipse

B) A rectangle

C) A diamond

D) A circle

B) A rectangle

Q19. A relationship type is represented in an ER diagram as:

A) An ellipse

B) A rectangle

C) A diamond

D) A circle

C) A diamond

Q20. In an ER diagram, an attribute that can have multiple values for a single entity is called a:

A) Composite attribute

B) Derived attribute

C) Multivalued attribute

D) Simple attribute

C) Multivalued attribute

Q21. In an ER diagram, a _______ attribute is represented as an ellipse with double lines.

A) Multivalued

B) Derived

C) Composite

D) Simple

A) Multivalued

Q22. Which of the following is an example of a derived attribute?

A) Employee name

B) Employee age (calculated from birth date)

C) Employee SSN

D) Employee address

B) Employee age (calculated from birth date)

Q23. In the (min, max) notation for ER diagrams, (1,1) means:

A) The entity must participate in at least 1 relationship instance, with a maximum of 1

B) The entity may or may not participate

C) The entity can participate in multiple relationship instances

D) The entity cannot participate in any relationship

A) The entity must participate in at least 1 relationship instance, with a maximum of 1

Q24. A role name in an ER diagram signifies:

A) The primary key of an entity

B) The name of an attribute

C) The role that a participating entity plays in a relationship instance

D) The type of database being used

C) The role that a participating entity plays in a relationship instance

Q25. Total participation in an ER relationship means:

A) Every entity in the entity type must participate in the relationship

B) Only some entities participate in the relationship

C) The relationship has a many-to-many cardinality

D) The entity has a multivalued attribute

A) Every entity in the entity type must participate in the relationship

Q26. Partial participation in an ER relationship means:

A) Every entity in the entity type must participate in the relationship

B) Only some entities in the entity type participate in the relationship

C) The relationship is optional and has 0 cardinality

D) Both A and C

B) Only some entities in the entity type participate in the relationship

Q27. In a one-to-one relationship, the identifying relationship is:

A) Always present

B) Used only for weak entities

C) Used only for strong entities

D) Never used

B) Used only for weak entities

Q28. The degree of a ternary relationship is:

A) 1

B) 2

C) 3

D) n

C) 3

Q29. When an ER diagram uses only binary relationships to represent what was originally a ternary

relationship:

A) No information is lost

B) Some relationships may not be correctly represented

C) It is always more efficient

D) It cannot be done

B) Some relationships may not be correctly represented

Q30. A key attribute in an ER diagram is represented by:

A) Underlining the attribute name

B) Using a double ellipse

C) Connecting it with a bold line

D) Using italics

A) Underlining the attribute name

Q31. The EER model extends the ER model by including all of the following EXCEPT:

A) Subtypes and supertypes

B) Attribute inheritance

C) Specialization and generalization

D) Multi-valued attributes

D) Multi-valued attributes

Q32. In a _______ specialization, the subclasses are mutually exclusive (no entity can be in more than one

subclass).

A) Total

B) Partial

C) Disjoint

D) Overlapping

C) Disjoint

Q33. A _______ is the reverse process of specialization where entity types are generalized into a single

superclass.

A) Subclass

B) Supertype

C) Generalization

D) Inheritance

C) Generalization

Q34. Which of the following best describes a specialization hierarchy?

A) Every subclass participates in only one class/subclass relationship, resulting in a tree structure

B) A subclass can participate in multiple class/subclass relationships

C) Multiple subclasses can share the same superclass

D) A subclass inherits from multiple superclasses

A) Every subclass participates in only one class/subclass relationship, resulting in a tree structure

Q35. A _______ constraint specifies that an entity in the superclass must also be an entity in at least one of its

subclasses.

A) Disjoint

B) Overlapping

C) Total

D) Partial

C) Total

Q36. In a _______ (or union type), the subclass represents a subset of the union of distinct entity types.

A) Specialization

B) Category

C) Hierarchy

D) Generalization

B) Category

Q37. Which of the following best describes the relationship between a superclass and a subclass in the EER

model?

A) The subclass inherits only key attributes from the superclass

B) The subclass inherits all attributes and relationships of the superclass

C) The superclass inherits attributes from the subclass

D) There is no attribute inheritance between them

B) The subclass inherits all attributes and relationships of the superclass

Q38. In the EER model, _______ occurs when subclasses from different specialization hierarchies are

combined into a single subclass.

A) Categorization

B) Multiple inheritance

C) Specialization

D) Generalization

B) Multiple inheritance

Q39. A _______ is a specialization where a subclass can be a subclass in more than one class/subclass

relationship.

A) Hierarchy

B) Lattice

C) Tree

D) Cascade

B) Lattice

Q40. In a specialization lattice with multiple inheritance, if an attribute originates from the same superclass:

A) It is duplicated in the shared subclass

B) It is included only once in the shared subclass

C) It is removed from the shared subclass

D) It creates a conflict that must be resolved

B) It is included only once in the shared subclass

Q41. A _______ subclass is determined by a condition on an attribute value.

A) User-defined

B) Attribute-defined

C) Condition-based

D) Predicate-defined

D) Predicate-defined

Q42. Overlapping specialization allows:

A) An entity to belong to only one subclass

B) An entity to belong to multiple subclasses simultaneously

C) Subclasses to share attributes

D) None of the above

B) An entity to belong to multiple subclasses simultaneously

Q43. In an EER diagram with specialization, a total and disjoint specialization means:

A) Some entities don't belong to any subclass, and some belong to multiple subclasses

B) Every entity belongs to exactly one subclass, and subclasses are mutually exclusive

C) Every entity belongs to at least one subclass, and subclasses can overlap

D) Some entities belong to no subclass, but those that do can belong to multiple subclasses

B) Every entity belongs to exactly one subclass, and subclasses are mutually exclusive

Q44. A category (or union type) is different from a specialization because:

A) It involves a single superclass while specialization involves multiple

B) It involves multiple superclasses while specialization involves a single superclass

C) Categories do not support inheritance

D) Categories are only used for weak entities

B) It involves multiple superclasses while specialization involves a single superclass

Q45. In EER notation, a circle is used to represent:

A) An entity type

B) A relationship type

C) A specialization/generalization hierarchy

D) An attribute

C) A specialization/generalization hierarchy

Q46. When converting a category (union type) to a relational schema with different keys:

A) A surrogate key is created

B) Multiple primary keys are used

C) The category is split into separate tables

D) No conversion is necessary

A) A surrogate key is created

Q47. Single inheritance in EER means:

A) A subclass has only one attribute inherited from the superclass

B) A subclass has exactly one superclass

C) A superclass has only one subclass

D) An entity belongs to only one specialization

B) A subclass has exactly one superclass

Q48. The main reason for including specialization and generalization in the EER model is:

A) To simplify the schema design

B) To represent certain attributes and relationships that apply only to some entities

C) To reduce the number of tables in the database

D) To eliminate weak entities

B) To represent certain attributes and relationships that apply only to some entities

Q49. A specialization process is a:

A) Top-down refinement process

B) Bottom-up synthesis

C) Side-to-side mapping

D) Horizontal decomposition

A) Top-down refinement process

Q50. A generalization process is a:

A) Top-down refinement process

B) Bottom-up synthesis

C) Side-to-side mapping

D) Horizontal decomposition

B) Bottom-up synthesis

Q51. What is the degree of the following relation? STUDENT(Name, Ssn, Address, Age, GPA)

A) 3

B) 4

C) 5

D) 6

C) 5

Q52. Which of the following is NOT a characteristic of relations?

A) Each value in a tuple is atomic

B) The order of tuples matters

C) Attributes are ordered within the schema

D) No duplicate tuples exist

B) The order of tuples matters

Q53. A _______ is a minimal superkey that uniquely identifies each tuple in a relation.

A) Superkey

B) Foreign key

C) Candidate key

D) Primary key

D) Primary key

Q54. The _______ constraint states that no primary key value can be NULL.

A) Key constraint

B) Domain constraint

C) Entity integrity constraint

D) Referential integrity constraint

C) Entity integrity constraint

Q55. Which of the following best describes a foreign key?

A) A key that uniquely identifies tuples within a relation

B) An attribute (or set of attributes) whose values match the primary key of another relation

C) A composite key of two or more attributes

D) A key that cannot contain NULL values

B) An attribute (or set of attributes) whose values match the primary key of another relation

Q56. A _______ is a relationship among attributes in the same relation where the value of one attribute

determines a unique value of another.

A) Key constraint

B) Functional dependency

C) Referential dependency

D) Domain constraint

B) Functional dependency

Q57. If SSN → Fname, Lname is a functional dependency, what does this mean?

A) Fname and Lname determine SSN

B) SSN uniquely determines Fname and Lname

C) There must be one Fname and Lname for each SSN

D) Both B and C are correct

D) Both B and C are correct

Q58. Which of the following constraints can be violated by a DELETE operation?

A) Domain constraint

B) Entity integrity constraint

C) Referential integrity constraint

D) All of the above

C) Referential integrity constraint

Q59. When deleting a tuple and using the CASCADE option for referential integrity:

A) The deletion is rejected

B) The referencing tuples are also deleted

C) The referencing tuples have their foreign key set to NULL

D) The foreign key is set to a default value

B) The referencing tuples are also deletedB

Q60. In the relational model, a _______ is a set of related data values that represents a specific entity or

relationship instance.

A) Relation

B) Attribute

C) Tuple

D) Domain

C) Tuple

Q61. The primary difference between partial and total participation constraints is:

A) Partial allows NULL values; total does not

B) Partial means optional; total means mandatory

C) Partial is used for weak entities; total for strong entities

D) There is no difference

B) Partial means optional; total means mandatory

Q62. In the relational model, the _______ is the total number of possible unique values in a domain.

A) Cardinality

B) Degree

C) Arity

D) Magnitude

A) Cardinality

Q63. When converting a many-to-many relationship from an ER diagram to a relational schema, you create:

A) A foreign key in one of the tables

B) A new junction (or bridge) table

C) A composite primary key

D) A subclass relationship

B) A new junction (or bridge) table

Q64. The process of breaking down a composite attribute into its simple component parts when converting an

ER schema to a relational schema is called:

A) Normalization

B) Decomposition

C) Flattening

D) Simplification

B) Decomposition

Q65. In the relational model, _______ refers to the suppression of details of data organization and storage while

highlighting essential features.

A) Data hiding

B) Data abstraction

C) Data independence

D) Data encryption

B) Data abstraction

Q66. A _______ is a relation that is created through the CREATE VIEW statement and does not actually store

data on disk.

A) Base table

B) Virtual relation

C) Temporary table

D) System table

B) Virtual relation

Q67. In the relational model, a _______ may (or may not) contain unnecessary attributes.

A) Superkey

B) Candidate key

C) Primary key

D) All of the above

A) Superkey

Q69. Which of the following operations on a relational database can violate key constraints?

A) INSERT only

B) DELETE only

C) UPDATE only

D) Both INSERT and UPDATE

D) Both INSERT and UPDATE

Q68. Which of the following is true about NULL values in SQL?

A) NULL represents the value zero

B) NULL represents an empty string

C) NULL represents a missing or unknown value

D) NULL and 0 are equivalent

C) NULL represents a missing or unknown value

Q70. In the relational model, which constraint ensures that each row in a table is unique?

A) Domain constraint

B) Key constraint

C) Referential integrity constraint

D) Entity integrity constraint

B) Key constraint

Q71. A multivalued attribute in the ER model is represented in the relational model by:

A) A column in the same table

B) A separate relation

C) A composite attribute

D) A derived attribute

B) A separate relation

Q72. When converting a weak entity type to a relational schema:

A) The weak entity becomes a standalone table

B) The primary key of the weak entity includes the partial key plus the primary key of the identifying

strong entity

C) No special considerations are needed

D) A surrogate key is always created

B) The primary key of the weak entity includes the partial key plus the primary key of the identifying

Q73. The SET NULL referential integrity option means:

A) When referenced tuple is deleted, set the foreign key to NULL

B) When foreign key is NULL, delete the referenced tuple

C) When foreign key is NULL, no deletion is allowed

D) Set all NULL values to a default before deletion

A) When referenced tuple is deleted, set the foreign key to NULL

Q74. The SET DEFAULT referential integrity option means:

A) When referenced tuple is deleted, set the foreign key to NULL

B) When referenced tuple is deleted, set the foreign key to a default value

C) When foreign key is NULL, set it to default automatically

D) Set all foreign keys to default values when table is created

B) When referenced tuple is deleted, set the foreign key to a default value

Q75. In the relational model, a domain is:

A) A table in the database

B) The set of valid atomic values for an attribute

C) A collection of relations

D) The primary key of a table

B) The set of valid atomic values for an attribute

Q76. Which SQL statement is used to create a new table?

A) CREATE DATABASE

B) CREATE TABLE

C) CREATE SCHEMA

D) CREATE RELATION

B) CREATE TABLE

Q77. In SQL, the _______ keyword is used to specify that a column cannot have NULL values.

A) UNIQUE

B) PRIMARY KEY

C) NOT NULL

D) CONSTRAINT

C) NOT NULL

Q78. Which of the following SQL clauses is used to specify a condition that tuples must satisfy to be retrieved?

A) SELECT

B) FROM

C) WHERE

D) ORDER BY

C) WHERE

Q79. The _______ clause in SQL is used to remove duplicate tuples from query results.

A) UNIQUE

B) DISTINCT

C) GROUP BY

D) HAVING

B) DISTINCT

Q80. Which SQL operator is used for string pattern matching?

A) =

B) LIKE

C) IN

D) BETWEEN

B) LIKE

Q81. In SQL, the symbol _______ in a LIKE clause replaces an arbitrary number of zero or more characters.

A) _

B) %

C) *

D) @

B) %

Q82. In SQL, the symbol _______ in a LIKE clause replaces a single character.

A) _

B) %

C) *

D) &

A) _

Q83. Which of the following SQL clauses groups tuples and is used with aggregate functions?

A) WHERE

B) GROUP BY

C) ORDER BY

D) HAVING

B) GROUP BY

Q84. The _______ clause in SQL is used to select or reject entire groups based on a condition.

A) WHERE

B) GROUP BY

C) HAVING

D) ORDER BY

C) HAVING

Q85. Which SQL keyword is used to arrange query results in ascending or descending order?

A) SORT BY

B) ARRANGE BY

C) ORDER BY

D) RANK BY

C) ORDER BY

Q86. In SQL, the keyword _______ specifies ascending order (which is the default).

A) ASC

B) DESC

C) UP

D) LOW

A) ASC

Q87. Which of the following is a set operation in SQL?

A) AND

B) OR

C) UNION

D) NOT

C) UNION

Q88. What is the difference between UNION and UNION ALL in SQL?

A) UNION removes duplicates; UNION ALL keeps duplicates

B) UNION keeps duplicates; UNION ALL removes duplicates

C) There is no difference

D) UNION ALL is used for multiple tables

A) UNION removes duplicates; UNION ALL keeps duplicates

Q89. Which SQL set operation returns only rows that appear in both queries?

A) UNION

B) INTERSECT

C) EXCEPT

D) MINUS

B) INTERSECT

Q90. Which SQL set operation returns rows from the first query that do not appear in the second query?

A) UNION

B) INTERSECT

C) EXCEPT

D) SUBTRACT

C) EXCEPT

Q91. Which SQL command is used to insert new data into a table?

A) INSERT INTO

B) ADD INTO

C) PUT INTO

D) APPEND INTO

A) INSERT INTO

Q92. The _______ command in SQL is used to remove tuples from a relation.

A) REMOVE

B) DROP

C) DELETE

D) ERASE

C) DELETE

Q93. Which SQL aggregate function returns the total count of rows?

A) TOTAL()

B) COUNT()

C) SUM()

D) ADD()

B) COUNT()

Q94. Which SQL aggregate function returns the highest value?

A) HIGHEST()

B) TOP()

C) MAX()

D) MAXIMUM()

C) MAX()

Q95. Which SQL aggregate function returns the lowest value?

A) LOWEST()

B) BOTTOM()

C) MIN()

D) MINIMUM()

C) MIN()

Q96. In SQL, the _______ keyword specifies the default value for an attribute if no value is provided.

A) NULL

B) DEFAULT

C) CONSTRAINT

D) CHECK

B) DEFAULT

Q97. The CHECK clause in SQL is used to:

A) Verify a foreign key reference

B) Specify a condition that must be true for values in a column

C) Check for NULL values

D) Check the data type of a column

B) Specify a condition that must be true for values in a column

Q98. In SQL, which of the following keywords is used to combine the results of multiple SELECT statements

while removing duplicates?

A) UNION

B) UNION ALL

C) INTERSECT

D) EXCEPT

A) UNION

Q99. Which clause in SQL specifies which columns to retrieve from a table?

A) FROM

B) WHERE

C) SELECT

D) ORDER BY

C) SELECT

Q100. Which clause in SQL specifies which table(s) to retrieve data from?

A) FROM

B) WHERE

C) SELECT

D) ORDER BY

A) FROM