2.1-2.4, 2.5.6-2.5.7, 2.6
Number of points:
Friday, October 6.
1. [10 points] Design a database schema for geography. It
should contain the following
kinds of objects together with the listed attributes:
gdp ("gross domestic product")
Model the following relationships between the geographical
- each city belongs to exactly one country
- each river crosses one or several countries
- each river ends in another river or in a sea
2. [15 points] Consider the following E/R diagram:
- list all possible kinds of objects that may
with their attributes
- model the same scenario in ODL (hint: you need more classes than entity sets)
3. [35 points] Startup company A just moved from a garage into a real
building and now plans to migrate its administrative data from
scratch paper to a real database. It needs information about the
- stock owners
In this problem you will map out an entity relationship
for company A. You should only turn in one diagram that has all of
the requirements in parts a through h. Make sure you leave plenty
a. build an E/R diagram with the following entity sets and properties:
b. the company wants now a finer separation of the people
database. Create four subclasses of person, with the following
number of shares
c. some modules become products. Create a subclass
module. Each product has a retail price.
d. choose the keys.
e. now model the following relationships:
e1. employees and contractors are part of teamsf. each module is developed by a exactly one team (when it is taken over
e2. each team works on zero, one or more modules
e3. customers order products; and for each order we need to track
the order date, status, and shipping date
g. a team can work on at most 5 modules: model this in the E/R diagram
h. the contractor's hourly wage is limited to at most $50/hour:
model this in
the E/R diagram.
4. [35 points] A group of independent job-hunters decide to integrate
They decided to model the integrated data in three entity sets:
- job-hunterThe duty of a job-hunter is to represent his/her clients, the applicants. For each
- applicant (i.e. people seeking jobs)
- position (to be filled)
a. Design an E/R digram with two relationships:
- interview: relates
- hire: relatesa job-hunter
a job-hunter (he gets the fee)
an applicant (he gets the job)
and a position (gets filled)
b. We have the following knowledge about the job hunting process:
b1. each applicant gives at most one interview for a given
position (rationale: obvious). In other words: when an
applicant is referred to the same position by several
job-hunters, he will choose a single job-hunter to arrange an
interview for that position.
b2. for each position a job-hunter will arrange an interview
with at most one applicant (rationale: job-hunters do not want
to create competition between their clients). In other words:
when there are several interviews for the same position, we
can be sure that they are all arranged by different
b3. for each position at most applicant is hired, and at mostRepresent (where possible) b1, b2, b3 with arrows in the diagram
one agent receives a fee (rationale: obvious).
c. For each piece of knowledge (b1, b2, b3) indicate whether
arrow(s) you added is (are) complete for describing that piece of
knowledge, or incomplete.
d. Convert the 'interview' and 'hires' from ternary to
relationships. You have to draw a new E/R diagram here, with
appropriate arrows (read Section 2.2.5.). Where possible, add
arrows to represent b1, b2, b3. For each arrow in the diagram
indicate wether it is there because of the conversion, or because
of b1, b2, or b3 respectively.
e. For each piece of knowledge b1, b2, b3, indicate whether
arrows in the diagram above are a complete representation or not.
5. [5 points] Please answer the following questions:
a. How long did it take you to complete this assignment ?
b. What did you like the best about this assignment ?
c. What did you like the least about this assignment ?
d. What helped you learn the best in this assignment ?
e. What distracted from your learning in this assignment