Museum

Fogg Art Museum The Fogg Art Museum in 1932, by William Ritasse

Problem to Solve

Suppose you’ve just been hired to help a museum keep track of their digitally-scanned, historic photographs. In a file called schema.sql in a folder called museum, write a set of SQL statements to design a database with which the museum could keep track of their photographs.

Wondering how to create a folder and file yourself?

Up until now, you’ve been downloading distribution code. Now, you’ll need to make your own folders and files!

If you’ve never created a new folder (“directory”) before, you can do so by learning a new unix (not SQL) command: mkdir!

To create a folder called museum in the main (“root”) directory of your Codespace, first ensure your terminal looks as such:

$

Then, type the following in your terminal:

mkdir museum

Note that the folder museum will be created within the “working directory” of your terminal (i.e., the folder on which your terminal is currently focused).

Once you’ve created the museum folder, you can type the following to change your terminal’s working directory to the museum folder:

cd museum

Afterwards, type code schema.sql to create a blank schema.sql file.

To remember mkdir in the future, recall that mkdir stands for “make directory!”

Specification

Your task at hand is to create a SQLite database for the museum from scratch, as by writing a set of CREATE TABLE statements in a schema.sql file. The implementation details are up to you, though you should minimally ensure your database meets the museum’s requirements and that it can represent the given sample data.

Requirements

The museum would like to be able to:

  • Title each of their photographs.
  • Date the photograph.
  • Attribute authorship of the photograph.
  • “Tag” each photograph to categorize it.
    • For instance, the museum would like to sort all photos of Harvard by applying a tag called “Harvard.”
    • A photo may have multiple tags, and a tag may be applied to multiple photos.

Sample Data

Your database should be able to represent…

  • A photograph, titled “Memorial Church,” by William Rittase, taken in 1932. The photo should be tagged with “Harvard” and “Memorial Church.”
  • A photograph, titled “Widener Library,” also by William Rittase, also taken in 1932. The photo should be tagged with “Harvard” and “Widener Library.”

Usage

To create a database from your schema, within your museum folder, type

sqlite3 museum.db

The above will create an empty SQLite database called museum.db.

Then, in the sqlite3 prompt, type

.read schema.sql

to read the statements from schema.sql.

Recall you can use DROP TABLE tablename, where tablename is the name of your table, to delete a table from your database.