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.
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.)
- 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).
- 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.
- 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.
- 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.
- 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'.
(Let me know if the diagram is too small and you want a bigger version of it.)
- 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.
- Find the artist identifiers and last names of artists that have the last name "Picasso".
- Find the museum names and addresses of museums that display the art item with
identifier equal to 5432.
- Find the artist last names and birthdates for artists who were born before
1900.
(Birthdate less than).
- Find the names and web sites of museums that display an art item titled
"Tutankhamun's Golden Mask".
- Find the first and last names of artists that created an art item titled
"Mona Lisa" and also an art item titled "Vitruvian Man".
- Find the names and addresses of museums that display art item(s) by
an artist with last name "Michelangelo".