Assignment 1

Total Points: 100 points

Note: You need to put all your assignments (starting from Assignment #0) in the ITK 478 course H: drive so that if I need to verify any of the work, I'll have an electronic copy to do the verification with. Also, for all assignments, you need to turn in a hardcopy as well. 

Due: 9/06/07 (class time)

  1. [15 points]

    A class hierarchy is a hierarchy that is formed based on the inheritance (is-a) relationship of classes. Below is an example of a class hierarchy representing Material Objects [Budd 2000]. (Note that in the example below, each indentation represents an is-a relationship. For example, Carnation is-a Flower, Flower in turn is-a Plant, and Plant in turn is-a Material Object.)

    Material Object

                    Animal

                                    Mammal

                                                    Dog

                                                    Human

                                                                    Shopkeeper

                                                                                    Florist

                                                                    Artist

                                                                                    Potter

                                                                    Dentist

                                                    Platypus

                    Plant

                                    Flower

                                                    Carnation

    In this question, you are asked to provide a class hierarchy for two (choose any two) of the following:
     

    1. Transportation (car, boat, etc.)
    2. ReadingMaterials (book, magazine, etc.)
    3. Employees (staff, manager, etc.)

    You need to provide approximately 10 classes for each of the hierarchies. Also, for each of the hierarchies, pick 5 classes (not all of which are direct descendents of the highest level superclass (i.e., Transportation and ReadingMaterials)) and briefly describe the characteristics of the classes. For example, in the Material Object hierarchy above, some of the classes can be described as follows [Budd 2000].
     
      * A Material Object has mass and weight (and thus all its descendent classes have these properties)
      * An Animal object breathes oxygen (...)
      * A Mammal object nurses its young and has hair (...)
      * etc.

    For Questions 2-4 below, consider the following organization chart for a university system and the Employee table given below.

Employee (SSN, Name, Salary, Title, SupervisorNumber)  Note: SSN and SupervisorNumber are defined on the same domain and one represents a foreign key to the other. (You should know which is which!)

  1. [10 pts] Is the following query doable in SQL? If so, provide a solution in SQL. If not, state why not.

    "Find all faculty members who earn more than his/her direct supervisor."
     
  2. [5 pts] Assume that the above organization chart is now generalized to include more than the above 4 levels (i.e., we have now Associate Chair, Assistant Chair, Associate Dean, etc.) and one does not know the exact number of levels. Is the following query doable in SQL? If so, provide a solution in SQL. If not, state why not.

    "Find all faculty members who earn more than his/her supervisors, direct or indirect ones."
     
  3. [15 pts] Redo the query in the previous question (i.e., #3) by using SQL and a high-level language (e.g., Java, C, COBOL, etc.) You do not need to compile or run this program. Only pseudo-code is needed. The logic of the program is the most essential part of this question. Note: Almost all DBMS books have a section on this topic called embedded SQL. Thus, if you are unsure about this material, just look for the topic in a DBMS text or on the Web.
     
  4. [10 pts] Impedance mismatch was discussed in class as a weakness of RDBMS. In the above question, describe where impedance mismatch occurs and why it is indeed a weakness of RDBMS (using the specific example, not generically).
     
  5. [10 pts] Assuming that you are to work with a (Spy) Satellite Image Processing System, a system that continually receives and processes the images sent by the satellite so that one can monitor a certain set of conditions and alert the appropriate authority if/when the need arises. Describe two specific scenarios that you would likely work with and explain why traditional DBMSs are ill-suited for handling these scenarios. Hint: Refer to our class discussions on CAD/CAM and the likely scenarios and why those scenarios exhibit characteristics that expose the weaknesses in traditional DBMSs.
     
  6. [10 pts] Read the "OODBMS Manifesto" paper (just click on the link) and answer the following questions:

    (a) The paper states "there is no distinction in usage between system defined and user defined types." Which feature of OODBMS is the phrase applicable to?

    (b) What is the position of the paper with regard to "programming paradigm." Is OO programming language (e.g., C++, Java) preferred compared imperative languages (e.g., COBOL, C)?
     
  7. [15 pts] Consider the following ER model.

    8. Note: The arrow (-->) signifies that the entity being pointed to is uniquely identified in the relationship. For example, in the FatherOf relationship, the father of a child is uniquely identified. The line (--) in a relationship indicates that the connected entity can be multiply associated with the other entity. Here, a father can have any number of children in the FatherOf relationship.

    (a) [9 pts] Map the above ER model to a relational schema (i.e., a set of tables) using each of the three approaches discussed in class. You should have three separate schemas as a result of this question.

    (b) [6 pts] Assume that you are to convert the above ER model to an OO model. Your job here is to add methods (at least 2) to each of the 4 entities. You simply need to write the method name and describe (using 1-2 sentences) the purpose of the method.

  8. [10 pts] coming ...