Read This Line! Information that is true for ALL of the following ER diagrams: Each ship has a unique identifying code. Each ship also has a name, a date it was commissioned and a date it was lost or retired.
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.)
- Naval ships must be built in a shipyard. Each ship is built in exactly one
boatyard. Boatyards each have a unique business license number. They also have
a name, address, and founding date.
(For example, the frigate Constellation, one of the first ships in the U.S. Navy, was built in the Sterrett Shipyard in Baltimore, Maryland and launched in 1797.) - Naval ships are commanded by many navy officers over the course of their
service lives. This database will store which officers have ccommanded which navy
ships; an officer can command many ships over the course of their career. The
database will store the military identification number of each commander, plus their
name, rank, and the year they entered the Navy.
The database will also store the dates each commander begins and ends their command of each ship.
(For example, the USS Cole was first commanded by Commander M. Stewart O'Bryan, and the USS Arizona was first commanded by Captain John D. McDonald.) - Naval ships sometimes go through overhauls. The overhauls do not have identifying numbers. However, each ship can only start one overhaul on a particular date, and the database stores the date each overhaul starts. It also stores a description of the overhaul and the name of the naval yard where the overhaul occurred.
- Some naval ships are aircraft carriers. For aircraft carriers, the database
tracks how many jet fighters can be on the carrier at one time, and the number of
crewpeople needed to staff aircraft operations.
Some naval ships are steamships. For steamships, the database stores how many steam engines powered it, plus how the ship was propelled. (Truthfully, most steamships are no longer in service. But they served well, in their time. See Wikipedia's steamship page for pictures. Steamships could be propelled using a paddlewheel, propeller, etc.) - The Naval History and Heritage Command stores many pictures of naval ships. This database tracks those pictures and which ships are in each picture. Ships can be in many pictures, or in none. (Naval ships existed before photography was common.) For each picture, the database stores a NHHC official identifying code, the date the picture was taken, and a note about the picture. (Picture NH 46298-KN is of the first battleship Texas; picture UA 473.01 is of the USS Arizona.) Pictures may be of many naval ships, or none. (Some pictures are of crew, etc.)
(Let me know if the diagram is too small and you want a bigger version of it.)
Owner (oid: integer, firstname: string, lastname: string, birthyear: integer, birthregion: string, bio: string)
OwnedBy (ownerID: integer, artID: integer, purchasePrice: integer, currency: string, purchaseDate: date)
ArtItem (aid: integer, title: string, weightPnd: integer, type: string, creator: integer)
ExhibitsIn (artID: integer, exhibitID: integer, insuranceCost: integer, deliveryDate: date, homeMuseum: integer)
Exhibition (eID: integer, title: string, curatorName: string, specialFee: integer)
Artist (personID: integer, lastname: string, firstname: string, birthyear: integer, birthregion: string, bio: string)
Regions (regionname: string, latitude: string, longitude: string, sizeSqMi: integer, climate: string)
(The "bit" data type stores 0 or 1. False or true. Booleans.)
"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.
"creator" in ArtItem is a foreign key referencing "personID" in Artist.
"birthregion" in Artist and Owner are foreign keys referencing "regionname" in Regions. A region where an artist was born might be a country, like Vietnam, or a particular area within a country, like Siberia, or a city, like New York City.
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).
Sometimes art acquires value because it belonged to someone famous. For example, in 2023 the Legion of Honor museum held an exhibition devoted to objects the Tudor rulers of England had owned. You may assume simple boolean comparisons (<, >, ≠) work on dates. Write the following queries in relational algebra.- Find the last names and first names of art owners born in a region named "Paris".
- Find the titles and types of art items created by artists with the last name O'Keeffe.
- Find the last names and first names of art owners who purchased art items at prices bigger than a million USA dollars.
- Find the titles of art items, and the last and first names of their owners, for art items and owners where the owner purchased the art after 2022.
- Find the exhibition titles, and art item titles, for exhibitions that include art items where the insurance cost for including that art item is greater than $10,000 and the art item was purchased for more than 1 million USA dollars.
- Find art item titles for art items that were owned by an owner with last name "Tudor" and first name "Elizabeth" or by an owner with last name "Stuart" and first name "Mary".
- Find art item titles for art items that were owned by an owner with last name "Tudor" and first name "Elizabeth" and by an owner with last name "Stuart" and first name "Mary".