Midterm1

Question 1

Which operation benefits the most from a B+ tree index?
a) None of the other choices
b) Performing a range query to retrieve multiple records
c) Deleting a single record
d) Updating a specific field in a record
e) Counting the total number of records in a table

Answer 1

b) Performing a range query to retrieve multiple records

Question 2

Which one below is not correct about Primary and Clustered index?
a) A clustered Index is always unique
b) Only one clustering index can be created per table, but it may include multiple columns
c) Tables can exist without a clustered or primary index, but in that case data will be stored in the order of insertion
d) None of the other choices
e) Primary and Clustered indexes do not usually require additional disk space.

Answer 2

a) A clustered Index is always unique

Question 3

When a new record is inserted into a B+ tree index, how does the tree typically accommodate the new entry while maintaining its properties?
a) By promoting the new record to the root node
b) By moving the entire tree to secondary storage
c) None
d) By splitting a leaf node and redistributing keys
e) By removing the last leaf node and appending the new one.

Answer 3

d) By splitting a leaf node and redistributing keys

Question 4

Which operation is typically faster in a sequential file organization?
a) Searching for a specific record by key
b) Deleting a record from any position in the file
c) Updating the values of a record
d) All are equally fast
e) Inserting a new record in the middle of the file

Answer 4

a) Searching for a specific record by key

Question 5

Based on the discussions in our lectures, which of the following three approaches can be used when we map a 1-to-1 relationship from our ER diagram to a relational schema?
(1) the foreign key approach
(2) the merged relationship approach
(3) the cross-reference (or relationship relation) approach

Answer 5

All three.

Question 6

What is the primary purpose of a clustered index in a physicla database?
a) To optimize query performance by physically ordering data
b) None
c) To enforce the uniqueness of a primary key
d) To determine the logical order of rows in a table
e) To create a non-unique index for faster data retrieval

Answer 6

a) To optimize query performance by physically ordering data

Question 7

How does a dynamic hash index handle hash collision?
a) It redistributes records to new buckets.
b) It discards the colliding records.
c) It locks the database for maintenance
d) The choice of which one to use depends on the specific situation.
e) It increases the hash function complexity

Answer 7

a) It redistributes records to new buckets.

Question 8

A blank triangle in a UML diagram indicates the following:
a) Specialization/generalization with the disjoint constraint
b) Specialization/generalization with the overlapping constraint
c) Association
d) Base class
e) Aggregation

Answer 8

a) Specialization/generalization with the disjoint constraint

Question 9

Consider the relation schemas R = (A, B, C, D) and S = (D, E, F). What will be the degree of the resultant schema R * S, where the star (*) symbol represents the natural join operation?
a) None
b) 6
c) 5
d) 1
e) 7
f) 12

Answer 9

b) 6

Question 10

If a B+ tree index is created for primary or clustered indexing, where are the actual data records typically stored?
a) In the internal nods of the tree
b) In the leaf nodes of the tree
c) None of the other choices
d) In the root node of the tree
e) In a separate data file

Answer 10

b) In the leaf nodes of the tree

Question 11

________ is the process of defining a set of subclasses of an empty type
a) Expansion
b) Categorization
c) Division
d) Generalization
e) Specialization

Answer 11

e) Specialization

Question 12

A filled diamond in a UML diagram represents the following
a) Inheritance
b) Specialization/Generalization
c) Composition
d) Aggregation
e) Association

Answer 12

c) Composition

Question 13

If Block size = 8K, File size = 8M, Block transfer time (btt) = 0.1 ms, seek time (s) = 10.0 ms, rotational delay (r) = 5.0 ms, what is the total in ms to fetch all of the records form a pile file?
a) 415
b) None
c) 115
d) 815
e) 15100

Answer 13

e) 15100

Question 14

If Block size = 4K, File size = 20M, Block transfer time (btt) = 0.1 ms, seek time (s) = 10.0 ms, rotational delay (r) = 5.0 ms, what is the total in ms to fetch all of the records form a fully sorted file?
a) None
b) 1510
c) 415
d) 15100
e) 4151

Answer 14

a) None

Question 15

What is the main drawback of using a dynamic hash index?
a) All of them pose an equal drawback
b) Limited support for concurrent updates
c) Poor performance for read operations
d) Complexity in managing hash collisions
e) Inefficient storage space utilization

Answer 15

d) Complexity in managing hash collisions

Question 16

Consider two relations R1 = (A, B) with the tuples (1, 5), (3, 7) and R2 = (A, C) = (1, 7), (4, 9). Assume that R3 = (A, B, C) is the full outer join in R1 and R2. Consider the following tuples of the form (A, B, C): a = (1, 5, null), b = (1, null, 7), c = (3, null, 9), d = (4, 7, null), e = (1, 5, 7), f = (3, 7, null), g = (4, null, p). Which one of the following statements is correct?
a) R3 contains all of a, b, c, d, e, f, g
b) R3 contains e but not f, g.
c) R3 contains e, f, g but not a, b.
d) R3 contains a, b, e, f, g but not c, d
e) R3 contains e, f, g, a, but not b.

Answer 16

a) R3 contains all of a, b, c, d, e, f, g

Question 17

Based on our discussions in the lectures, a multivalued attribute from an EER diagram will generally be modeled as _______ in a UML diagram
a) a specialization
b) a multivalued attribute of the class it belongs to
c) a separate class
d) a multiplicity
e) a generalization

Answer 17

c) a separate class

Question 18

Which one of the following keys is specifically related to the category concept of the EER data model?
a) surrogate key
b) primary key
c) candidate key
d) superkey
e) foreign key

Answer 18

a) surrogate key

Question 19

How many tuples are there in a relation with degree 20?
a) 20
b) 5
c) 40
d) cannot be determined
e) 10

Answer 19

d) cannot be determined

Question 20

What is the main purpose of a secondary (non-clustered) index in a physical database?
a) To enforce data integrity constraints.
b) To ensure that data is stored in a specific physical order
c) To elimminate duplicate values in a table
d) To provide a faster path to retrieve data records
e) None

Answer 20

d) To provide a faster path to retrieve data records

Question 21

Which one may not be one of the file organizations?
a) Sequential
b) Indexed
c) Hashed sequential
d) Hashed
e) Indexed Sequential

Answer 21

c) Hashed sequential

Question 22

The ability to change the database at the physical level with no impact to the next higher level (logical level) is called
a) Data abstraction
b) Physical data independence
c) Data integrity
d) Data manipulation
e) Logical data independence

Answer 22

b) Physical data independence

Question 23

Which command below is not correct to create secondary index structures on student_name attribute of a relation Student?
a) CREATE INDEX indx_student_name ON Student (student_name)
b) CREATE INDEX indx_student_name ON Student (student_name) ASC
c) CREATE INDEX student_name USING Student (indx_student_name) UNCLUSTERED
d) None
e) CREATE INDEX indx_student_name ON Student (student_name) CLUSTURED

Answer 23

a) CREATE INDEX indx_student_name ON Student (student_name)