CS 4250: Database Management Systems

Spring 2020 - Homework 1

Due February 27, 2020, 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, be obvious. Put a gap between double-lines. Draw subset symbols nice and large.

  1. Consider a database containing information about state parks, and park rangers. Create entities and relationships as appropriate for each situation.

    Information that is true for all of the following ER diagrams: Each and every state park has a unique identifying number, and also a name, address and area. (You can think of the area as the park's size, measured in acres). Park rangers have identifying ranger numbers, first and last names, and phone numbers.

    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. The database stores information about archaeological sites in state parks. Each archaeological site can be uniquely identified using latitude and longitude information. The database also stores names of sites, and a photograph of the site. The database stores information about which sites are in or overlap which state parks. An archeological site may be in one state park, or it might overlap several state parks; a site must be affiliated with at least one state park to be in the database. Some state parks might not have any archaeological sites in them; some state parks have many archaeological sites.
    2. State parks may have historic buildings in them. A state park may have multiple historic buildings in them. Each historic building has a name, date it was built, and information on when the building is open to visitors. There is no unique identifier for a historic building in the database as a whole, but each building in a particular state park will have a different name from other buildings at the park.

    3. Rangers work at state parks. Each ranger is primarily assigned to one state park, but a state park may have many rangers assigned to it. Each state park must have at least one ranger assigned to it.
    4. Some state parks are nature reserves. For nature reserves, the database stores the name of the most significant nature attraction, and the start and end of the time of year when that attraction is most attractive. (For example, the California poppy flower blooms in the Antelope Valley California Poppy Reserve.)

      Some state parks are state beaches. For state beaches, the the database stores the address of the nearest parking lot, and the distance and direction from the parking lot to the biggest tide pool.

      It is possible for a state park to be both a nature reserve and a state beach. Some state parks are neither nature reserves nor state beaches.

    5. Rangers can supervise parks. A ranger may not supervise any parks, or one, or several. For each park that a ranger supervises, the database will store the date the ranger became a supervisor at that park. A park must have at least one supervisor, and might have many supervisors. (For example, a small park might have one supervisor, a supervisor who also supervises other, nearby parks. But a big state park might have multiple supervisors, so that there can be one on duty 24 hours a day.)

  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.)

  4. 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 art item identifiers and titles of art items that weigh more than 10 pounds.
    2. Find the artist last names and first names for artists that created the art item with identifier equal to 2345.
    3. Find the museum identifiers, art item titles and art item types for art items displayed at a museum, where the display starts after "2020 Feb 12".
    4. Find the artist last names and birthdates, where the artist created an art item titled "A Forest Landscape".
    5. Find the names and addresses of museums that display an art item titled "Junon evening dress" and also an art item titled "Standing figure".
    6. Find the last names, birthdates and deathdates of artists who created art item(s) that are displayed at a museum named "Crocker Art Museum".