CS 4250: Database Management Systems

Spring 2023 - Homework 1

Due 28 February 2023

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 the CS Homework system on or before midnight on 28 February 2023. 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.

(Note: if homework is uploaded to CS Homework, 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. November 2022 was the 100 year anniversary of the discovery of King Tutankhamun's tomb, so...

    Consider a database containing information about ancient Egyptian pharaohs and archeology.

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

    Information that is true for all of the following ER diagrams: Each pharaoh has a name, a nickname, a birth and a death year. The name is a unique identifier for each pharaoh (Ramses I, Ramses II, Akhenaton, etc.)

    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. Each pharaoh must be part of one Egyptian dynasty. Egyptian dynasties have a start year and a stop year, a capital city, and a number. The number of the dynasty can be used to identify it ("Twenty-Second dynasty", "Eighteenth Dynasty", etc. King Tutankhamun was a member of the Eighteenth Dynasty.) Dynasties have one or more members.

    2. Famously, the ancient Egyptians mummified their dead. Archeologists and others have found mummies that may be the remains of ancient pharaohs, but have not reliably determined which mummy is what pharaoh, in all cases.

      The database stores information about mummies: a unique mummy identifier, a mummy nickname, the year it was found, and the location where it was found. Some pharaohs have no mummy, some have one mummy identified as theirs, and some have multiple mummies that are candidates to be that pharaoh. Some mummies have not been identified with any pharaoh, some mummies have been clearly identified as a particular pharaoh, and some mummies have been potentially identified as multiple possible pharaohs.

    3. Some pharaohs were buried in tombs, and some pharaohs were buried in pyramids. For the pharaohs buried in tombs, the database stores the official idenfier of the tomb, plus the latitude and longitude of its location. (For example, Tutankhamun was buried in KV62 -- the 62nd tomb numbered in the King's Valley near Luxor.) For pharaohs buried in pyramids, the database stores the name of the pyramid and its type. (Some pyramids are step pyramids, some are true pyramids, etc.)

    4. The database stores information about relatives of pharaohs. For non-pharaoh relatives, the database stores a unique identifier, the person's informal name, and the person's name. The likely relationships between non-pharaoh relatives and pharaohs are also stored. (For example, Tutankhamun's mother's name is unknown, but her mummy has been identified and is called "The Younger Lady." The Younger Lady is also, probably, a wife of Tutankhamun's father Akhenaten. And she is a daughter of the pharaoh Amenhotep III. Royal family trees in ancient Egypt were complicated! If you don't mind slightly gross pictures, more information on The Younger Lady is here.)

    5. The database stores information about mummies: a unique mummy identifier, a mummy nickname, the year it was found, and the location where it was found. The database also stores information about the amulets and other charms found within mummy wrappings. The amulets cannot be identified separately from their mummy, since one tiny carving looks very like another. An amulet can be identified by a nickname and the mummy the amulet was found with. The database also stores the height, weight, and primary material of each amulet. ("Green heart scarab found on Mummy5", "Golden Thoth found on Mummy12", etc.) (One reason many mummies are in poor condition is that thieves - or people who ought to have known better - ripped the mummy wrappings apart to get at items made of gold or jewels.)

  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.
    ArtItem (aid: integer, title: string, weightPnd: integer, type: string, pricePaid: integer)
    ExhibitsIn (artID: integer, exhibitID: integer, insuranceCost: integer, deliveryDate: date, homeMuseum: integer)
    Exhibition (eID: integer, title: string, curatorName: string, specialFee: integer)
    Visits(exhibitionID: number, lat: string, long: string, startdate: date, enddate: date)
    Location(latitude: string, longitude: string, name: string, address: string, country: string, phonenumber: string)

    (The "bit" data type stores 0 or 1. False or true. Booleans.)
    "lat, long" in Visits are a foreign key referencing "latitude, longitude" in Location.
    "exhibitionID" in Visits are a foreign key referencing "eID" in Exhibition.
    "exhibitID" in ExhibitsIn is a foreign key referencing "eid" in Exhibition.
    "artID" in ExhibitsIn is a foreign key referencing "aid" in ArtItem. The art item weights are measured in pounds.

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

    If you are unfamiliar with art museums, they will frequently organize special exhibitions. Museums will borrow art from other museums for a few months, art that fits an exhibition theme, and charge a small extra fee for admission to the exhibition. For examples, here are some past Asian Art Museum of San Francisco exhibitions. Art items must, of course, be insured appropriately before they leave their home museum and are shipped to the exhibition host museum, in case of loss or damage in transit. Some exhibitions will tour art around the world, displaying it at one museum for a few months before moving on to a museum in a different city (or country).

    Write the following queries in relational algebra.

    1. Find the titles and weights for art items that cost more than $5000.

    2. Find the titles and special fees for exhibitions with curators named "Howard Carter."

    3. Find the exhibition titles and special fees for exhibitions that will be traveling to locations in South Africa.

    4. Find exhibition identifiers, titles and curator names, for exibitions that will include art items that require insurance that costs more than $10000.

    5. Find the titles and curator names for exibitions that will include an art item titled "Tutankhamun's golden mask."

    6. Find the exhibition titles and exhibition identifiers, and location names, for exhibitions that have been displayed at location in the country "Canada" or at a location in the country "Japan".

    7. Find the exhibition titles and exhibition identifiers, and location names, for exhibitions that have been displayed at location in the country "Peru" and at a location in the country "United States".