Reading Assignments:
2.1-2.4, 2.5.6-2.5.7,
2.6
Number of points:
100 points
Due date:
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:
- countries
name
area
population
gdp ("gross domestic product")
- cities
name
population
longitude
latitude
- rivers
name
length
- seas
name
max depths
Model the following relationships between the geographical
objects:
- 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
exists, together
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
office
building and now plans to migrate its administrative data
from
scratch paper to a real database. It needs information
about the
following entities:
- employees
- stock owners
- contractors
- teams
- modules
- products
- customers
In this problem you will map out an entity relationship
diagram
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
of room.
a. build an E/R diagram with the following entity sets and properties:
- person:
lastName
firstName
ssn
email
- module:
name
description
versionNumber
- team:
teamName
emailGroup
b. the company wants now a finer separation of the people
in the
database. Create four subclasses of person, with
the following
additional properties:
- employee:
salary
- owner:
number of shares
- contractor:
hourly wage
- customer:
phone
shipping address
billing address
c. some modules become products. Create a subclass
"product" of
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
their data.
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
an applicant
a position
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
job-hunters.
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
the
arrow(s) you added is (are) complete for describing that
piece of
knowledge, or incomplete.
d. Convert the 'interview' and 'hires' from ternary to
binary
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
the
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
?