The test is open-everything with the sole limitation that you neither solicit nor give help while the exam is in progress.

This is a 3-hour, take-home exam. You have exactly three hours from time you access this page to the time that you must submit your exam via email. You are on the honor system to both stay within the time limit and to follow the No Solicitation policy.

It is possible (perhaps likely) that you won’t finish the exam, so work in such a fashion as to maximize your partial credit. The purpose of the exam is to provide a means of assessment, not to make you feel good. Better students will work more efficiently and answer more questions. Those who have developed fluency with setting up and working with Mongo, Postgres, Neo, and Redis will have skills enabling them to answer more questions correctly than those who have tried to coast through the course.

Many of these problems will make use of movie data. Each movie has a required title (string), an optional release year (integer), an optional primary studio (string), and an optional poster url (string). A movie may or may not be a sequel of another movie. People, who have a required name, optional birthdate, and optional headshot url, may act in or direct zero or more movies. Movies may have zero or more actors and may have zero or more directors. When a person acts in a movie, we record the roles that the person played.

1. A SURPRISE PROBLEM, to make sure you came to class! Answer quickly:
   1. Mongo is a ___________________ database.
   2. Postgres is a ___________________ database.
   3. Neo is a ___________________ database.
   4. Redis is a ___________________ database.
   5. Sarah Mei thinks you should ___________________ use Mongo.
   6. Redis (and similar) databases make really good ___________________s.
   7. ACID stands for ______________ ______________ ______________ ______________.
   8. Mongo doesn’t have joins because it favors ___________________ scaling.
   9. If you use promises, you don’t need callbacks (T/F): ___________________.
   10. Databases with schemas generally mean you must suffer with ___________________.
2. Suppose we decided to build a Mongo database for the movie data described above with a single collection (called movies). Each document is a movie with fields title, year (optional), studio (optional), actors (optional, a list of objects with fields name, birthdate (optional), headshot_url (optional) and roles (optional, a list)), directors (optional, a list of objects with fields name, birthdate (optional), and headshot_url (optional)), and sequel_of (optional, an ObjectId).
   1. Give a JavaScript statement to insert the 2004 Pixar movie The Incredibles directed by Brad Bird (b. 1957-09-24), and starring Holly Hunter (b. 1958-03-20) as both Elastigirl and Helen Parr, Samuel L. Jackson as both Lucius Best and Frozone, and Sarah Vowell as Violet Parr.
   2. Give a query to get the title and studio for sequels with actors born before 1940.
   3. Give a query to list the names of directors that directed a movie made in 2000.
   4. Give a query to list the names of all Pixar movies that John Ratzenberger starred in, together with the roles he played in each. Sort the result by movie release year, most recent first.
3. Suppose we chose Postgres to implement our movie database. We want the movie and person tables to have their own autoincrement primary keys. The director and actor join tables are to have composite primary keys, made up of the foreign keys to the tables they are associating. Represent the list of an actor’s roles in a movie as a column of type JSON (which is cooler and easier than using a separate role table).
   1. Do we need a separate table for the movie sequel relationship? Why or why not?
   2. Give create table statements to build the tables as described above. You will be graded on how perfectly you describe the primary and foreign keys, and how well you distnguish required from optional properties.
   3. Give a query to get the title and studio for non-sequels with actors born before 1940.
   4. Give a query to list, for each director, the number of movies they directed. Order the result by the number of movies descending.
   5. Give a query to list the names of all Pixar movies that John Ratzenberger starred in, together with the roles he played in each. Sort the result by movie release year, most recent first.
4. Suppose we chose Neo to implement the movie database. We need node labels Person and Movie, and relationship labels ACTED_IN, DIRECTS, and SEQUEL_OF. You should know how to allocate the properties among the nodes and relationships. Pay attention to the roles property!
   1. Give a single Cypher create statement to populate the database with the movies The Incredibles (Pixar, 2004), Cars (Pixar, 2006), and its sequel Cars 2 (Pixar, 2011); as well as the people Holly Hunter (b. 1958-03-20) who played Elastigirl and also Helen Parr in The Incredibles and John Ratzenberger (b. 1947-04-06), who played The Underminer in The Incredibles, Mack in Cars, and both Mack and Hamm Tuck in Cars 2.
   2. Give a query to get the title and studio for non-sequels with actors born before 1940.
   3. Give a query to list, for each director, the number of movies they directed. Order the result by the number of movies descending.
   4. Give a query to list the names of all Pixar movies that John Ratzenberger starred in, together with the roles he played in each. Sort the result by movie release year, most recent first.
   5. Give a Cypher query to return the titles of all movies that were sequels of sequels of sequels.
5. One of the freshmen decided to try Redis for the movie data. The student gave each movie a unique identifier such as m:the-incredibles, m:cars, and m:cars-2. People received unique identifiers, too, e.g., p:holly-hunter, p:brad-bird. When the movie id is used as a key, its value is a hash containing its title, studio, release year, poster url, and the id of the movie it was a sequel of, if any. When the person id is used as a key, its value is a hash containing the name, birthdate, and headshot url. The directors of a movie are stored at the key formed by suffixing :d to the movie id (e.g., m:the-incredibles:d) as a set of person ids (e.g., {"p:brad-bird"}). An actor’s roles are stored as in this example: key m:the-incredibles:p:holly-hunter, value ["Helen Parr", "Elastigirl"]. In addition, the student wants to store movies with their Rotten Tomatoes scores in a sorted set at the key rt-scores.
   1. Give a Redis command to make an entry for The Incredibles (Pixar, 2004).
   2. Give a Redis command to make entries for Holly Hunter, Sarah Vowell, and Brad Bird, using the data from Problem 2 above.
   3. Associate Hunter, Vowell, and Bird with the movie, according to the desired scheme.
   4. Give a Redis command to get the number of directors for The Prestige.
   5. Give a Redis command to determine whether Hugh Jackman acted in Gone With The Wind.
   6. Give a Redis command to change the release year of The Circle to 2017.
   7. Give a Redis command to get the five movies with the highest Rotten Tomatoes scores (together with their scores).
6. What was the most annoying thing about many-to-many relationships we saw when building Sequelize applications during class? (And yes, there is a most annoying thing.)