CS 4250: Database Management Systems

Fall 2026 - Homework 1

Due 15 September 2026

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. Nor may you submit the work of software programs as your own.

Turn in as hardcopy (in class time) or upload to Canvas class web site on or before midnight on 15 September 2026. 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.

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

Put your own name at the top of your own homework answers.

  1. This August, the singer Dolly Parton passed away. Inspired by her, consider a database about a charity that sends gifts to children each month. Dolly Parton started and sponsored such a charity, the Imagination Library, sending books to millions of children under the age of five.

    If you are unfamiliar with her singing, here is a newspaper article with videos of some of her best songs: "From Jolene to Islands in the Stream: Dolly Parton's 10 best recordings". (The Guardian, 26 Aug 2026)

    Create entities and relationships as appropriate for each (independent) situation described below.

    Read This Line! Information that is true for ALL of the following ER diagrams: Each charity has a unique charity identifing number. Each charity has a name, address, and web page.

    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 parentheses is simply information. Information in parentheses does not affect the ER diagrams, but may help you understand the real world context of the question. (Note: I may have "fudged" the truth a bit, on these questions, to make good homework questions. Draw the ER diagrams for the situations as described.)

    1. A charity runs on the work of volunteers. One charity might have many volunteers who do volunteer work for it, and a volunteer might do volunteer work for multiple charities. The database stores each volunteer's first and last name, email address, and a unique volunteer identifying number. For each volunteer and each charity they volunteer for, the database stores the date they started volunteering for that charity.

    2. Each charity has one primary founder. The database stores the founder's name, birthdate, job, and the most significant source of their wealth (for example, singing, mining, inheriting, etc). A charity's founder might found multiple charities.

      (Dolly Parton, for example, founded the Imagination Library, and the Buddy Program. Jean-Henri Dunant is credited with being the main founder of the Red Cross.)

    3. The charities in this database send gifts to children. Each charity sends gifts to many children, but, for privacy reasons, there are no unique identifiers of the children. For any particular charity, the database tracks the children that charity gives to using the child's mailing address and first name. The database also stores the child's age, and the date the charity started sending that child gifts.

    4. Some charities are membership charities, where the database stores the number of members and the date of the last election of a board and the date of the next scheduled meeting of the board. (Membership charities have members elect their boards.) Some charities are board-only charities, where the database stores the number of board members. (For more information, see Wikipedia's page on nonprofit organizations.)

    5. Some charities give awards. The database stores a unique award identifier, the name of the award and the amount of money awarded (if any). For each award a charity gives, the database stores the date the award was given. A charity can give many awards, and an award can be given many times. Awards must be given to be in the database, but some charities do not give awards. (Famously, the Make-A-Wish Foundation awards very sick children a "wish".)

  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.
    Location (latitude: double, longitude: double, name: string, country: string)
    FoundIn (latitude: double, longitude: double, artID: integer, dateFound: date, digFundedBy: string, finder: integer)
    Archeologist (personID: integer, lastname: string, firstname: string, birthyear: integer, speciality: string, organization: string)
    ArtItem (aid: integer, title: string, weightPnd: integer, type: string, homeMuseum: integer)
    Museum (mid: integer, name: string, street: string, state: string, country: string, postalcode: string, website: string)
    Conservator (restorerID: integer, lastname: string, firstname: string, speciality: string, employer: integer)
    Restoration (artID: integer, restID: integer, startdate: date, stopdate: date, cost: integer)

    (The "bit" data type stores 0 or 1. False or true. Booleans.)
    "homeMuseum" in ArtItem and employer in Conservator are foreign keys referencing "mid" in Museum.
    "artID" in Restoration is a foreign key referencing "aid" in ArtItem.
    The art item weights are measured in pounds.
    "finder" in FoundIn is a foreign key referencing "personID" in Archeologist.
    "restID" in Restoration is a foreign key referencing "restorerID" in Conservator.

    You may assume simple boolean comparisons (<, >, ≠) work on dates. Write the following queries in relational algebra.

    Note: The De Young Museum in San Francisco is currently hosting an exhibit of objects found in Egypt, Treasures of the Pharaohs.

    1. Find the last names, first names and employing organizations of archeologists.

    2. Find the names of locations and the names of the countries they are in, for art items found before January 1, 1900.

    3. Find the titles and weights of art items that are found by archeologists with the first and last names "Indiana" and "Jones".

    4. Find the name of the country art items were found in, and the last and first name of the archeologists who found them, for art items of type "sarcophagus".

    5. Find the titles and art identifiers of art items, and the name of their home museums, for art items found in digs funded by "George Herbert, 5th Earl of Carnarvon" or "Chinese Academy of Social Sciences".

    6. Find the titles and art identifiers of art items, and the name of their home museums, for art items found in digs funded by "George Herbert, 5th Earl of Carnarvon" AND "Chinese Academy of Social Sciences".

Note: George Herbert, 5th Earl of Carnarvon funded Howard Carter's dig that found the tomb of Tutankhamun. The Chinese Academy of Social Sciences funded the excavation of tomb of Fu Hao, a similarly spectacularly preserved tomb in Yinxu, China. (Fu Hao was one of the wives of a king in the Shang dynasty, living around the same time as Tutankhamun.)