Homeworks:

1. How does the concept of an object in the object-oriented model differ from the concept of an entity in the entity-relation model?

2. Explain how the concept of object identity in the object-oriented model differs from the concept of tuple equality(E.g. primary key equality) in the relational model.

3. A lake is naturally modeled as a spatial object. Can you give a reason where it might be useful to model a lake as a spatial field.

4. Consider a relational database to represent orthogonal rectangles in a plane (say X-Y plane). The rectangles have Sides parallel to coordinate axis. There are three relations:

        rectangle ( rectangle-name, one-of-the-edges),
        edge(edge-name, one-of-the-endpoints),
        point(point-name, x-coordinate, y-coordinate).

    The first attribute in each relation is an unique identifier.

    Example: Unit square will be represented in the database as a collection of following tuples:

        rectangle (unit-square, edge1), rectangle (unit-square, edge2),
        rectangle (unit-square, edge3), rectangle (unit-square, edge4),
        edge(edge1, point1), edge(edge1, point2), edge(edge2, point2),
        edge(edge2, point3), edge(edge3, point3), edge(edge3, point4),
        edge(edge4, point4), edge(edge4, point1).

    Geometry computations can be represented by queries over this database.

    Qa. Give an expression in SQL for the query: Find all rectangles which contain "point1" as a corner point.

    Qb. Give an expression in SQL for the query: Find all rectangles which contain "point1" as an inside point.

    Qc. Object-relational extensible databases and query languages can be used to simplify the queries considerably. Propose new abstract data types and operations to simplify the representation of the rectangles and queries given in questions Qa and Qb.

5. Consider the database schema

        Emp = (ename, setof(Children, setof(Skills))
        Children = (name, Birthday)
        Birthday = (day, month, year)
        Skills = (type, setof(Exams))
        Exams = (year, city)

    Write the following queries in the extended SQL for the relation emp(Emp).
    (a) Find the names of all employees who have a child who has a birthday in March.
    (b) Find those employees who took an examination for the skill type "typing" in the city "Dayton."
    (c) List all skill types in the relation emp.

6. Database systems that store each relation in a separate operating-system file may use the operating system's security and authorization scheme, rather than defining a special scheme themselves. Discuss an advantage and a disadvantage of such an approach.

7. Consider the following schema:

             EMPLOYEE(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPEEERSSN, DNO)
             WORKS_ON(ESSN, PNO, HOURS)
             PROJECT(PNAME, PNUMBER, PLOCATION, DNUM)
 

7.a Express the following integrity constraints as Postgres rules by specifying the event and qualification for each rule. Specify the notation used.
        (1) Salary of an employee can not exceed $1000,000 unless he/she works for department 007.
            Event:
            Qualification:

        (2) Salary for a supervisor is always more than the supervisee. (Hint: Transitive closure is not relevant)
            Event:
            Qualification:

        (3) Salary for an employee can not be retrieved if his/her salary is more that the average salary of all employees.
            Event:
            Qualification:

        (4) Salary of an employee never decreases if his/her birthdate is before 1945.
            Event:
            Qualification:

7.b Represent the following recursive query in SQL : Retrieve all supervisors (direct and indirect) from John B. Smith. The answer should include 1st level, 2nd level and nth level managers for John, where n is not known beforehand.

8. Question 1 from Csci 5702 sample exam. (in postscript format)

9. Question 1 from Csci 3321 Sample Exam. (in postscript format) .

10. Question 3 (excluding part ii) from Csci 3321 Sample Exam. (in postscript format) .

Labs:

The labs are based on the PREDATOR software.

Lab1:

(a) List 3 classes from PREDATOR source code, which were assigned to you for documentation by the TA.

(b) For each class, list the directory path(e.g. ~/code/newtypes/polygon.c ), and the module name related to your class(e.g. box, complex, circle, bytearray, counter, point, date, document, enum, docutils, genadt, geometry, polygon, raster and struct).

(c) List the name and email address of other people who are documenting classes in the same module(e.g. box, complex, circle, bytearray, counter, point, date, document, enum, docutils, genadt, geometry, polygon, raster and struct). TA post the classes assigned to various people.

(d) List the sections in a typical Unix Man-page. Briefly describe the purpose of each section and how they apply to class documentation.

Lab2:

(a) Develop an Unix Man-page for each class assigned to you.

(b) Put the 3 Man-pages and a summary page on the web via your itlabs student account. Test to ensure that the pages are readable.

Lab3:

(a) Review the Man-pages of related classes documented by others to complete the "SEE ALSO" section of your Man-pages

(b) Add a section called "Major Data Structures and Algorithm" to each Man-page. Describe the "major" data structure and algorithm in the section.

(c) Add a section called "USAGE HIERARCHY". List the methods (with class name) called by the code of your classes. Provide web links to Man-pages of the called methods (or classes).

(d) Add a section called "INHERITS FROM". List the super classes of your classes along with web-links to relevant pages.

Lab4:

(a) Review the Man-pages of classes in your module(e.g. box, complex, circle, bytearray, counter, point, date, document, enum, docutils, genadt, geometry, polygon, raster and struct). Write a summary document for the entire module.

(b) Specify role of your classes in the PREDATOR object-relaional database in terms of major functionalities.

(c) Provide a couple of uage scenarios for your module.