CS 4250: Database Management Systems

Fall 2019 - Homework 1

Due September 27, 2019, at the beginning of class

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.

Turn in as hardcopy or via email (MS Word or PDF or plain text only). Typed wherever possible.

(Note: if homework is emailed, it must be all computer-program created. SQL typed, diagrams drawn with software. Photographs of hand-drawn material are not acceptable.)

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

  1. Consider a database containing information about rockets, and the companies that make them. Create entities and relationships as appropriate for each situation.

    Information that is true for all of the following ER diagrams: Every rocket has a unique identifying number, and also a model name, and a value of kilonewtons of thrust. Companies have uniquely identifying business numbers, plus names 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.)

    1. Companies make rockets. Each company might make multiple rockets. Each rocket must be made by one company. For example, Blue Origin makes the BE-4 and the BE-7 models of rockets (or plans to make them).

    2. Rockets are attached to launch vehicles. A launch vehicle must have at least one rocket attached. Launch vehicles may have multiple rockets attached. Rockets may be attached to multiple rockets. For example, the SpaceX Falcon Heavy launch vehicle has eighteen Merlin 1D rocket engines attached.

    3. The database also stores photographs of rockets. The database stores the photographs, and the dates the photographs were taken. Photographs are numbered when they are entered into the database. The numbers cannot uniquely identify a photograph across the whole database, but, for each rocket, the numbers can be used to specify different photographs of that particular rocket.

    4. The database also stores information about videos of rockets. Each video can be identified by its YouTube URL, and the database also stores the length of each video and the title.

      Some videos are video montages, with clips of many rockets included. The database stores information about which rockets are included in each montage video. Rockets might be in montage videos or not, but each montage video must include a clip of at least one rocket. (For example, "How Not to Land an Orbital Rocket Booster." Space travel is hard!)

      Some videos are of testing events. The database will store the date, time and location where these videos were shot, plus a brief description of the event.

  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.

    You may assume simple boolean comparisons work on dates.

    Write the following queries in relational algebra.

    1. Find the artist identifiers and last names of artists that have the last name "Picasso".

    2. Find the museum names and addresses of museums that display the art item with identifier equal to 5432.

    3. Find the artist last names and birthdates for artists who were born before 1900. (Birthdate less than).

    4. Find the names and web sites of museums that display an art item titled "Tutankhamun's Golden Mask".

    5. Find the first and last names of artists that created an art item titled "Mona Lisa" and also an art item titled "Vitruvian Man".

    6. Find the names and addresses of museums that display art item(s) by an artist with last name "Michelangelo".