Lab Exercise

Document Sample
Lab Exercise Powered By Docstoc
					Lab Exercise # 2
Object-Relational Database

  Drop object tables created during first
   lab session. Think about order
  Drop types created during first lab
   session. Think about order

                Marina G. Erechtchoukova     2
Object-Relational Database
   Create logical schema according to the class
    diagram provided:
       Class diagram
   Create two subtypes of U_student_t
     Graduate   students G_student_T with additional
      attribute Degree
     Undergraduate students UG_student_t with the same
      attributes as the supertype, but with different
      implementation of method COMPS. The method
      maps a student to their first name.

                       Marina G. Erechtchoukova       3
Object-Relational Database
 Insert data into object tables. You may
  make your own data or follow the example
  from the slides
 Formulate a query returning titles of the
  faculties in whose course the student John
  Smith is enrolled.

                 Marina G. Erechtchoukova   4
Object-Relational Database
 Formulate other queries returning data
  from more than one object table. Use
  object references
 Formulate queries demonstrating that all
  methods work

                 Marina G. Erechtchoukova    5
Function TREAT
   the function attempts to treat a supertype
    instance as a subtype instance
   Property SID of U_transcript_t may contain
    referneces to both subtypes pf the supertype
   TREAT function is needed to reach properties
    which are subtype specific
   Formulate a query returning the status of
    undergraduate students enrolled for courses.
    Use Oracle function TREAT

                    Marina G. Erechtchoukova       6
Sample Data
Table u_student:
Table g_student:

                 Marina G. Erechtchoukova   7
Sample Data (cont…)
Table u_faculty:
 ‘FSE’, ‘Science’
 ‘LAPS’, ‘Arts’
Table u_course:
 ‘CS3421’,‘DBMS’,‘COSC’, reference to FSE
 ‘IT1010’,‘Inform’,‘ITEC’, reference to LAPS
 ‘IT2010’,‘SDLC’, ‘ITEC’, reference to LAPS

                 Marina G. Erechtchoukova      8
Sample Data (cont…)
Table u_transcript:
  ‘001’, ’53’, ‘IT1010’, 78
  ‘002’, ’53’, ‘IT2010’, 74
  ‘003’, ’55’, ‘CS3421’,73
  ‘004’, ’75’, ‘CS3421’, 81
  ‘005’, ’85’, ‘IT2010’, 80
Use references instead of primary key values for
  students and courses

                   Marina G. Erechtchoukova        9

Shared By: