your.harvard

Log into cs50.dev, click on your terminal window, and execute cd by itself. You should find that your terminal window’s prompt resembles the below:

$

Next execute

wget https://cdn.cs50.net/sql/2023/x/psets/5/harvard.zip

in order to download a ZIP called harvard.zip into your codespace.

Then execute

unzip harvard.zip

to create a folder called harvard. You no longer need the ZIP file, so you can execute

rm harvard.zip

and respond with “y” followed by Enter at the prompt to remove the ZIP file you downloaded.

Now type

cd harvard

followed by Enter to move yourself into (i.e., open) that directory. Your prompt should now resemble the below.

harvard/ $

If all was successful, you should execute

ls

and see a database named harvard.db alongside a SQL file named indexes.sql. If not, retrace your steps and see if you can determine where you went wrong!

The students table contains the following columns:

• id, which is the student’s ID.
• name, which is the student’s name.

The courses table contains the following columns:

• id, which is the courses’s ID.
• department, which is the department in which the course is taught (e.g., “Computer Science”, “Economics”, “Philosophy”).
• number, which is the course number (e.g., 50, 12, 330).
• semester, which is the semester in which the class was taught (e.g., “Spring 2024”, “Fall 2023”).
• title, which is the title of the course (e.g., “Introduction to Computer Science”).

The enrollments table contains the following columns:

• id, which is the ID to identify the enrollment.
• student_id, which is the ID of the student enrolled.
• course_id, which is the ID of the course in which the student is enrolled.

The requirements table contains the following columns:

• id, which is the ID of the requirement.
• name, which is the name of the requirement.

The satisfies table contains the following columns:

• id, which is the ID of the course-requirement pair.
• course_id, which is the ID of a given course.
• requirement_id, which is the ID of the requirement which the given course satisfies.

• Find a student’s historical course enrollments, based on their ID:

  SELECT "courses"."title", "courses"."semester"
  FROM "enrollments"
  JOIN "courses" ON "enrollments"."course_id" = "courses"."id"
  JOIN "students" ON "enrollments"."student_id" = "students"."id"
  WHERE "students"."id" = 3;
  

• Find all students who enrolled in Computer Science 50 in Fall 2023:

  SELECT "id", "name"
  FROM "students"
  WHERE "id" IN (
      SELECT "student_id"
      FROM "enrollments"
      WHERE "course_id" = (
          SELECT "id"
          FROM "courses"
          WHERE "courses"."department" = 'Computer Science'
          AND "courses"."number" = 50
          AND "courses"."semester" = 'Fall 2023'
      )
  );
  

• Sort courses by most- to least-enrolled in Fall 2023:

  SELECT "courses"."id", "courses"."department", "courses"."number", "courses"."title", COUNT(*) AS "enrollment"
  FROM "courses"
  JOIN "enrollments" ON "enrollments"."course_id" = "courses"."id"
  WHERE "courses"."semester" = 'Fall 2023'
  GROUP BY "courses"."id"
  ORDER BY "enrollment" DESC;
  

• Find all computer science courses taught in Spring 2024:

  SELECT "courses"."id", "courses"."department", "courses"."number", "courses"."title"
  FROM "courses"
  WHERE "courses"."department" = 'Computer Science'
  AND "courses"."semester" = 'Spring 2024';
  

• Find the requirement satisfied by “Advanced Databases” in Fall 2023:

  SELECT "requirements"."name"
  FROM "requirements"
  WHERE "requirements"."id" = (
      SELECT "requirement_id"
      FROM "satisfies"
      WHERE "course_id" = (
          SELECT "id"
          FROM "courses"
          WHERE "title" = 'Advanced Databases'
          AND "semester" = 'Fall 2023'
      )
  );
  

• Find how many courses in each requirement a student has satisfied:

  SELECT "requirements"."name", COUNT(*) AS "courses"
  FROM "requirements"
  JOIN "satisfies" ON "requirements"."id" = "satisfies"."requirement_id"
  WHERE "satisfies"."course_id" IN (
      SELECT "course_id"
      FROM "enrollments"
      WHERE "enrollments"."student_id" = 8
  )
  GROUP BY "requirements"."name";
  

• Search for a course by title and semester:

  SELECT "department", "number", "title"
  FROM "courses"
  WHERE "title" LIKE "History%"
  AND "semester" = 'Fall 2023';
  

Begin by assessing where best to create indexes by understanding the plan for each typical query on my.harvard’s database.

For example, try revealing the plan for the first typical query, as by executing the following:

EXPLAIN QUERY PLAN
SELECT "courses"."title", "courses"."semester"
FROM "enrollments"
JOIN "courses" ON "enrollments"."course_id" = "courses"."id"
JOIN "students" ON "enrollments"."student_id" = "students"."id"
WHERE "students"."id" = 3;

The output of the above is as follows:

QUERY PLAN
|--SEARCH students USING INTEGER PRIMARY KEY (rowid=?)
|--SCAN enrollments
`--SEARCH courses USING INTEGER PRIMARY KEY (rowid=?)

Notice that, while the database engine is already SEARCHing the students and courses tables using their primary key indexes, there are still improvements to be made: the database engine is SCANning the enrollments table without an index. Recall that to SCAN means that the database engine must search through all rows, one by one—a process that is much slower than searching an index!

Experiment now by creating an index which could turn that SCAN into a SEARCH that uses an index. Then, repeat the same process for each of the typical queries on my.harvard’s database until you’ve arrived at a set of indexes which ensure all queries are using indexes to their full potential.

Keep in mind that indexes take up additional space, and that they can slow INSERT, UPDATE, and DELETE queries. Once you’ve arrived at an initial set of indexes, start paring them down until you’ve created the minimum required for each query to use indexes optimally. How to start this process? Consider the following questions:

• Do any of your indexes include the same columns? If so, it’s likely you need only one index on that particular column.
• Do any of your indexes include columns unused by the given queries? If so, it’s likely you can remove those columns from your indexes.
• Does removing an index have any impact on each query’s plan? If not, might be best to remove it!

Through the iterative process above, you’ll refine the indexes you’ve chosen to create.

Notice this command has three parts:

• zip, which is the name of the tool which will create a .zip file
• harvard-solution.zip, which is the name of the .zip file to create
• *.sql, which represents the file(s) to include.
  • *.sql matches all files that end with .sql, as * is a “wildcard” character which matches any set of characters (similar to % in SQL!).