1. (2 pts) What is a primary reason that indexes are used?
2. (2 pts) Why wouldn't a database administrator (DBA) simply index every field?
3. (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 4.

1. (2 pts) Are B-trees an implementation of ordered indexing or hashed indexing?
2. (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.

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.

1. (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. NOTE: You will use this updated design the answer the remaining questions in this problem.

2. Suppose, for marketing purposes, a genre called “New” will be added to each album automatically when a new album is added to the database.
   1. (2 pts) Write a SQL command that will add a genre with the name “New” having an id number of 0 to the database.

   2. (4 pts) Write a SQL trigger command that will call a trigger function called add_genre_to_new_album whenever a new album is inserted into the album table.

   3. (6 pts) Write the PL/PGSQL trigger function mentioned in problem 4(b)ii that when called will add the genre named “New” to the newly inserted album.

3. (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/her/their diversity field should be set to 2 (because they are associated with two genres). Write a stored procedure that could be run once and would will correctly populate the diversity field for all artists.

1. (4 pts) In a couple of sentences, explain the CAP theorem as it applies to distributed databases.
2. (2 pts) What type of NoSQL DMBS is MongoDB?

1. (8 pts) Suppose you wanted to store the above data using MongoDB in a database named “baseball”. Further suppose that you decide to use a single collection (called “players”) to represent the data. Write the necessary MongoDB commands to establish the database and collection and to populate the collect with the data represented above for the first two players.

2. (8 pts) Assume you have produced a correct solution to the previous question. Use the find(), sort(), and forEach() commands to produce a list of players (firstname and lastname) along with their career total RBIs. Only players with more than 40 RBIs should be included in the list. Of course, your query should work on any set of data (not just the data listed above).

3. (8 pts) Use an aggregate query to produce the same results as the previous problem (8b) except sort the results so that players with higher RBIs are listed first. Do not use a forEach() loop. Make the query results to be stored in a new collection called “bighitters”. HINT: You can use $project along with $sum to get the total number of rbis per player.