CS 4250: Database Management Systems

Spring 2021 - Homework 1

Due 4 March 2021

This is an individual assignment. All work must be your own. You should not look at any other student's work (in whole or in part, on paper or on screen), nor allow anyone else to look at yours, during the course of this assignment.

Upload to the CS Homework system on or before midnight on 4 March 2021. Typed, wherever possible. Or photographs of hand-drawn answers (dark writing, white paper, even lighting). Save in a widely used file format, like JPG, PNG, etc.

When drawing ER diagrams, be obvious. Put a gap between double-lines. Draw subset symbols nice and large.

  1. Consider a database containing information about space exploration robots and satellites, like MARS Perseverance, and the space agencies that launch them. Create entities and relationships as appropriate for each situation.

    Information that is true for all of the following ER diagrams: Each and every space exploration vehicle has a unique identifying number, and also a name, weight and height. Space agencies have identifying names, and also acronyms and web sites.

    For each of the following situations, draw an ER diagram that describes it. Consider each situation independently. (Multiple situations means multiple, separate ER diagrams in a correct homework solution.)

    Information in parantheses is simply information. Information in parentheses does not affect the ER diagrams. It may help you understand the real world context of the question.

    1. The database stores information about the space exploration vehicles and the agencies that launch them. (See above for more details.) A particular machine might be launched by one agency, or can be launched by multiple agencies working together (like poor Beagle 2).

    2. The database must store information about space exploration vehicles (see above) and publicity photographs of them. Publicity photographs are taken by many different individuals and agencies, so there are no universally valid identifiers for them. However, for any particular exploration vehicle, a photograph could be identified by the timestamp when the photograph of that vehicle was taken. The database stores the photographs themselves, the timestamps, the name of the photographer of each photo, and the organization that holds the copyright on the photograph. Each publicity photograph is of only one vehicle.

    3. The database stores information about space exploration vehicles and the instruments carried on each vehicle. Each instrument has an identifying instrument number, a name, a weight, a date of installation, and the name of the lead scientist. (Teams design and build each instrument, but the database just stores the name of the team leader.) Each instrument is a highly specialized device, built and customized for only one exploration vehicle.

    4. Some space exploration vehicles are orbiters, some are landers, and some perform "fly-bys" of solar system objects. For landers, the database stores the number of wheels and maximum speeds. For orbiters, the database stores orbiting height (above ground) of the orbiter and the orbiting period (how many hours in each orbit). For vehicles that fly by their targets, the database stores the intended final destination of the vehicle and the number of solar system objects it will visit.
      (For example, the Voyager probes are headed for deep space, and the Cassini probe was deliberately crashed into Saturn, to protect possible life on the moons.)

    5. The database stores information about space exploration vehicles and the launch facilities where they are sent into space. Each launch facility can be uniquely identified using its latitude and longitude. The database also stores launch facility names and the year rockets were first launched from there.

  2. Translate the following image into relations. Write the SQL statements to create those relations. Write English explanations of anything in the ER diagram that is not captured in your SQL. Assume all keys are data type 'integer' and all other fields are data type 'date'.

    3. (Let me know if the diagram is too small and you want a bigger version of it.)

  3. Suppose a database has the following three relations.
    Museum (mid: integer, name: string, address: string, website: string)
    Displays (museumID: integer, artID: integer, startDate: date)
    ArtItem (aid: integer, title: string, weightPnd: integer, type: string)
    Artist (pid: integer, firstname: string, lastname: string, birthdate: date, deathdate: date)
    Creates (artistID: integer, artID: integer, year: integer)

    (The "bit" data type stores 0 or 1. False or true. Booleans.)
    "museumID" in Displays is a foreign key referencing "mid" in Museum.
    "artistID" in Creates is a foreign key referencing "pid" in Artist. ("pid" for person identifier)
    "artID" in Displays and Creates are foreign keys referencing "aid" in ArtItem. The art item weights are measured in pounds.

    You may assume simple boolean comparisons (<, >, ≠) work on dates.

    Write the following queries in relational algebra.

    1. Find the artist last and first names for artists whose date of death was before 1500.

    2. Find the museum names and addresses for museums displaying an art item with identifier equal to 3456.

    3. Find the museum identifiers, art item titles and art item weights for art items displayed at a museum, where the display starts after "2021 Mar 12" and the art item weighs more than 20 lbs.

    4. Find the names and web sites of museums that display an art item titled "Rosetta Stone".

    5. Find the first and last names of artists that created an art item titled "Café Terrace at Night" or an art item titled "The Parnassus".

    6. Find the first and last names of artists that created an art item titled "Mona Lisa" and also an art item titled "The Battle of Anghiari".