Comp 8571
Assignment 3
[12 marks]
Consider the database object relational schema that you have created in
assignment 2:
Student (student_id, name, address, phone, next_of_kin, major, birthday)
Enrollment (student_id, course_id, semester, grade)
Course (course_id, course_name, department)
1. Modify your object types to include the following member functions:
a. a map member function, called AGE, that calculate the age of the
student [1 mark]
b. a member function, called AVG_GRADE, to get the average grade
of each student [1 mark]
2. After inserting the data, write the following queries (apply un-nesting in
your query for the nested table data and round the average age and
average grade to one decimal point):
a. list the average age for each course and average age for each
semester (by using the created member function)[2 marks]
b. list the average grade for each course and average grade for each
semester (by using the created member function) [2 marks]
3. Enhance your object table to store LOBS:
a. create a tablespace called lobs8571to store the lobs in this
assignment and create a directory called bfiles8571 to store the
bfiles in this assignment [1 mark]
b. add two LOBs, picture (BFILE or out of line LOB) and one page
profile-resume word document (in-line LOB) to the student object
table. Make up your own resume and your own picture file [2
marks]
c. update the picture and the one-page word document to the student
object table [2 marks]
d. create an unique index to the student object to enhance query
performance. [1 mark]
1
SUBMISSION GUIDELINE
Due: Week 11 Mar 29, 2011
Late submission will not be accepted in D2L dropbox
You may do this assignment in pair. If you are doing this in pair you
must write down both last names on the submission file and the
document. Only one person needs to submit and the other make a note
in the dropbox “My Assignment 3 is submitted in xxxxx’s dropbox”.
Submit two files to D2Ldropbox and one printed copy to hand in class.
You must follow the required naming convention for your data of your
code and file.
File 1 in D2L (naming: StudentLastName_FirstName_Assn3_script.sql)
A PL/SQL script containing all your code that your instructor can run
(including the DDL and DML)
File 2 in D2L (naming: StudentLastName_FirstName_Assn3.doc)
The electronic copy of your printed copy.
You must use the template given below as your cover sheet.
Shows your code and corresponding query output
2
COMP 8571 Assignment 3
Marketing Sheet
Student ID / Name __________________________
Student ID / Name __________________________
Q1 (2) ___________
Q2 (4) ___________
Q3 (6) ___________
TOTAL Marks ___________ (out of 12)
===============================================================
Question 1 Modify your object types [2 marks]
a a map member function that calculate the age of the student [1 marks]
b a member function to get the average grade of each student [1 marks]
Question 2 Insert the data and write queries [4 marks]
After inserting the data:
a list the average age for each course (using map function) [1 mark]
b list the average age for each semester [1 mark]
c list the average grade for each course (using member function) [1 mark]
d list the average grade for each semester [1 mark]
Question 3 Store LOBS [6 marks]
a create a tablespace called lobs8571to store the lobs in this assignment
and create a directory called bfiles8571 to store the bfiles in this
assignment [1 mark]
b add two LOBs to the student table: one picture (out of line LOB) and one
page profile-resume word document (in-line LOB) [2 marks]
c update a picture and a one-page word document to the student object
table and write a query to retrieve the objects with the LOBS [2 marks]
d create an unique index to the student object to enhance query
performance [1 mark]
