CS44 Lab 5: Movie Database

createDB.py - a main program for creating your initial database schema including the creation of tables and indices plus the insertion of records. You will only need to run this program one time once you get it working (but be sure to test it!)

queryDB.py - the main user program. This program will prompt the user with a menu of all possible queries to run and execute the user’s choice(s)

README.md - describe your indices as well as your free-design query.

/home/soni/public/cs44/movieDB/ - this folder contains the records to insert into your tables in the form of relationName.txt (e.g., Casts.txt contains all records of actors cast in a movie). DO NOT COPY THIS FOLDER - it will use up all of your disk quota.

SQLite API - a detailed description of the full library interface. This is a good reference for help on accomplishing specific tasks or inspecting data

Python tutorial - a quick-start guide for the basics of opening a database connection

Python Central tutorial guide - another quick-start guide

You can also do a web search on the specific topic. Start your search with "sqlite python"; e.g., how do I retrieve query results? Google: "sqlite python retrieve query results".

Complete createTables()

Complete insertValues()

Verify that all primary and foreign key constraints have been defined correctly

Complete the first three queries in queryDB.py

Define at least one index

queryDB.py and createDB.py. Be sure your code compiles and runs efficiently.

Describe your extension query and provide a justification for each of your indices in README.md.

Do not commit any other files than these three. Use git rm if you mistakingly commit other files.

Your Lab 5 Questionnaire to be completed individually (This will open on the due date and close after 3 days)

Actor (id, fname, lname, gender)

Movie (id, title, year)

Director (id, fname, lname)

Casts (actorID, movieID, role)

DirectsMovie (directorID, movieID)

Genre (movieID, type)

All id fields are integers, as is year.

All other fields are character strings. You can use either CHAR(N) or VARCHAR(N) for the character strings.

Use a length of 30 characters for any name or title field

Use a length of 50 characters for role and type

gender is a single character

id is the key for Actor, Movie, and Director.

For the remaining relations, the primary key is the combination of all attributes. This is because an actor can appear in a movie many times in different roles; each movie can fit multiple genres; and each movie can have multiple directors.

Casts.actorID references Actor.id

Casts.movieID and DirectsMovie.movieID reference Movie.id.

DirectsMovie.directorID references Directors.id

main()
This method has been provided for you; read it to understand the top-level design of the rest of the program. The program takes the name of the database file as a command-line argument, verifies the file exists, establishes a connect to the sqlite dbms, creates all tables and inserts values, and builds indices before exiting.

checkDB()
Determines if the database already exists. If it does the user must choose between removing the database (starting from scratch), exiting (do nothing) or keep the existing database and only reconstruct indexes. This third option will come in handy when are designing your queries for the second part of the assignment. The methods returns True if either the file did not exist or if the user choose to remove the existing file. The program returns False if schemas and tuples should be kept and only indexes need to be reconstructed.

dropIndexes()
Removes all existing indexes in the database. This is useful during query design when you experiment with the best indexes to construct for your queries.

createTables()
Issues SQL commands to create all 6 tables in the schema. The first line of the given code turns referential integrity checks on. You will need to submit commands to the sqlite to define all table schemas.

insertValues()
You may not use .import to import all values. Instead, you must read each file, parsing the arguments on each line, and insert the results one-by-one using the INSERT INTO SQL command. You may want to take a look at executemany() function to see how all insertions can be executed at one time. There are helpful examples here and here.

with open('/home/soni/public/cs44/movieDB/Actor.txt','r',errors="backslashreplace") as f:
  #process file

createIndex()
Place all SQL commands for creating indices here. You should choose your indices wisely. You will probably need to complete this function after solving some of the queries below. As an aside, most queries should run quickly (a few seconds at most). In the README.md file, you should provide a justification for each index constructed by citing which queries it is useful for. Each index must be justified (your grade depends on both building indices that improve run time as well as avoiding unnecessary indices). See the tips below for additional help. Also, note that SQLite automatically builds some indices (e.g., on primary keys) so pay attention to your query plans to see if SQLite is actually using the ones you specify (and drop it if it is not used).

Each query should be defined in a function queryX where X is the number below. For example the first query listed in the Queries table below should be defined in the method query1().

Your queries should be easy to read. Your SQL commands are sent as strings through the execute method. Since these queries can be long, entering them as a very long line will make them very hard to read. Instead, you should use Python’s multi-line string format (i.e., """Long string""" to create strings with line breaks that make your queries easy to parse). For example, to query the number of Directors named "Steven":

  command = """
            SELECT COUNT(*)
            FROM Directors D
            WHERE D.name = 'Steven'
            """
  db.execute(command)
  results = db.fetchall()

Print out the results in a formatted manner. Use the printResults function we provide to help with this.

Print out the query plan after each query. Use the explainQuery() we provide to help with this. You can also do this in SQLite directly to help you develop your queries and indexes. To get the query plan prefix EXPLAIN QUERY PLAN to the query:

Your SQL queries should be written so that they are run efficiently. SQLite will make use of indices if they exist. Try out queries in SQLite command line, look at the query plan, and think about re-structuring and adding indices to improve the performance of slow queries. Just because you define an index doesn’t mean it is useful. Be sure to check your query plans to see if the index is being utilized (see Tips below).

You should print out the time it takes to perform each query using the time library in Python:

  start = time.time()
  # execute query
  # call fetchall to get results
  end = time.time()
  print("\nCompleted in %.3f seconds" % (end-start))
  # print results

You are allowed to create temporary tables to store intermediate queries. But you must drop temporary tables as soon as you complete the query. For example, if you create a temporary table called MyTable, remove it when you are done with the query by running:

  db.execute("DROP TABLE MyTable")