CSE 444 Homework 1

Objective:
        To understand and be able to manipulate ODL definitions and E/R diagrams

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 teams
  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
   f. each module is developed by a exactly one team (when it is taken over
   by a new team the company's policy is to create a new version of
   that module): model this in the E/R diagram.

   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-hunter
 - applicant (i.e. people seeking jobs)
 - position  (to be filled)
   The duty of a job-hunter is to represent his/her clients, the applicants.  For each
   client  she tries to find matching positions and arrange interviews.
   When an interview results in the applicant being hired, the
   referring job-hunter receives a fee.  Applicants desperate for jobs
   sometimes contact several job-hunters, in hope to find a job
   quicker.

   a. Design an E/R digram with two relationships:

         - interview:  relates

a job-hunter
an applicant
a position
         - hire: relates
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 most
 one agent receives a fee (rationale: obvious).
      Represent (where possible) b1, b2, b3 with arrows in the diagram
      from point a.  (You do not have to draw a new diagram here).
      Label each arrow you add with "b1", "b2", or "b3"
      respectively, indicating the reason you added it.

   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 ?