Database Systems - Exam 3
Write all answers on the answer sheets provided. You may refer
to source code from any program you have written.
- (3 pts each) Briefly define each of these terms:
- ODBC (don't expand the acronym; instead, define it)
- shadow copy
- Answer the following question about indexing.
- (2 pts) What is a primary reason that indexes are used?
- (2 pts) Why wouldn't a database administrator (DBA) simply
index every field?
- (4 pts) Show the SQL command that would be used to create an
index on title field of the album table of the database
specified in problem 6.
- Answer the following question about B-trees.
- (2 pts) Are B-trees an implementation of ordered indexing or
- (4 pts) Suppose that for an initially empty B-tree. Show
what the B-tree would look like after each of the following elements
are inserted: A,B,C,D,E,F.
- (4 pts) Suppose that . How many values can be stored in a
B-tree with three levels if every node is completely full?
- Answer the following questions about transactions and locking.
- (3 pts) Give an example of a transaction.
- (4 pts) Expand the acronym ACID.
- (3 pts) How do failed transactions and aborted transactions differ?
- (3 pts) Explain how a software lock works.
- (2 pts) For what purpose are locks typically used in a DBMS?
- (4 pts) The following schedule is conflict serializable. Given an example of a
schedule that is not conflict serializable.
- Consider these SQL commands that establishes a
database for storing information about albums and their artists.
In the current design each album has a single genre. Suppose you have been
asked to redesign the database so that albums can have multiple genres
associated with them.
- (6 pts) Describe changes you would need to make to existing tables to
support this change. If new tables are needed show the CREATE
TABLE command(s) that would establish those tables.
- Suppose, for marketing purposes, a genre called ``New'' will
be added to each album automatically when a new album is added to the
- (2 pts) Write an SQL command that will add a genre with the
name ``New'' having an id number of 0 to the database.
- (4 pts) Write an SQL trigger command that will call a trigger
add_genre_to_new_album whenever a new album
is inserted into the album table.
- (6 pts) Write the PL/PGSQL trigger function mentioned in
problem 6(b)ii that when called will add the genre
named ``New'' to the newly inserted album.
- (8 pts) Suppose the diversity field in the artists
table has just been added for the purpose of storing the number of
distinct genres attributed to the artist in the database. For example,
if the artist ``Dog Man'' has three albums one of which is ``Rock'' and
the other two are considered both ``Rock'' and ``Funk'' then his
diversity field should be set to 2 (because he is associated with
two genres). Write a stored procedure that could be run once and
would will correctly populate the diversity field for all
- Answer the following questions about NoSQL DBMSs.
- (6 pts) Explain the CAP theorem.
- (2 pts) What type of NoSQL DMBS is MongoDB?
- Write MongoDB commands to accomplish these tasks:
- (6 pts) Suppose you wanted to represent information about
albums in a way that is similar to what was done in
problem 6. Create a collection called album
that stores the album title, year, and a list of genres. Add
three entries to your collection. At least one of the documents
should have multiple genres associated with it.
- (6 pts) Produce a query, or if necessary, write a function
that will display all albums that have a genre of ``Pop'' in
their list of genres. The list should not show the built-in