Assigned: Tuesday, 18th Sept. 2001
Due: Tuesday, 25th Sept. 2001 (at start of class; in hardcopy)!
Maximum: 100pts.
Note: This homework is to be done by an individual student, no team work allowed.
Purpose of homework: To become familiar with database
design using the ER Model
and its mapping to a relational
database representation.
Do exercise 2.2 from text book on designing different cases for a university database.
A university database contains information about professors
(identified by social security number, or SSN) and courses
(identified by course id). Professors teach courses.
Professors also have names, and courses have course titles.
Each of the situations described below concerns the Teaches relationship set.
For each situation, draw an ER diagram that describes it (assuming that no
further constraints hold).
1. Professors can teach the
same course in several semesters, and each offering must
be
recorded.
2. Professors can teach the same course in several
semesters, and only the most recent
such offering needs to be
recorded. (Assume this condition applies in all subsequent questions.)
3. Every professor must teach some course.
4. Every
professor teaches exactly one course (no more, no less).
5. Every professor teaches exactly one course (no more, no less), and
every course
must be taught by some professor.
6. Now
suppose that certain courses can be taught by a team of professors
jointly,
but it is possible that no one professor in a team can
teach the course. Model this situation,
introducing additional
entity sets and relationship sets if necessary.
Problem 2: [50pts max]
Draw the ER diagram to develop a database for the application described below. Indicate if a given assertion in the description can't be captured by a ER constraint. If there are any special symbols you introduce, make sure to explain them in a legend.
Although you always wanted to be an artist, you ended up being an expert on databases because you have to cook data and you somehow confused 'data base' with 'data baste.' Your old love is still there, however, so you set up a database company, ArtBase, that builds a product for art galleries. The core of this product is a database with a schema that captures all the information that galleries need to maintain. Galleries keep information about artists, their names (which are unique), birthplaces, age, and style of art. The artist is either independent artist or a contracted artist of a studio, but not both. For each independent artist, the amount of artwork must be recorded. Galleries also need to store each contracted artist's contract ID and all working periods in any studio. Each studio's unique name and address must be recorded too. Each studio must have some artists work for them.
For each piece of artwork, the artist, the year it was made, its unique title,
its type of art (e.g., painting, lithograph, sculpture, photograph), and its
price must be stored. Pieces of artwork are classified into groups of
various kinds, for example, portraits, still lifes, works by Picasso, or works
of the 19th century.
A given piece may belong to more than one group. Each
group is identified by a name (like those above) that describes the group.
Finally, galleries keep information about customers. For each customer,
galleries keep their unique name, address, total amount of dollars they have
spent in the gallery (very important!), and the artists and groups of art that
each customer tends to like.