April 24th, 2013, 03:58 AM
Object Relational Schema help required please (Oracle Apex)
Hi guys, I have been given a situation to solve. I am a complete noob to using Oracles Apex, and to creating types and nested tables.
Here is the situation:
A number of special types of person have been identified on campus: students, who can be undergraduate, postgraduate or alumni (past graduates) and staff who can be further subdivided into lecturer, senior lecturer and professor.
Students enrol on a single course and take six modules a year. A course-profile is maintained of all modules attempted or completed by a student on a particular course. A course maintains a list of modules (which will be a subset of the larger list of modules offered on that campus) that are suitable for students enrolled on that particular course. A module may be taught by one or more members of staff.
Some modules can require that you have taken another module before you are allowed to enrol on them – so called “pre-requisites”. So for example com572 might have a pre-requisite of com203 the “Web Database” module. It is possible that this pre-requisite could itself have a pre-requisite forming a chain of modules.
Develop suitable types in an Object-Relational schema in Oracle for the above. Create tables from your types and populate them with sample data. Create a series of queries to demonstrate at least the following functionality:
• List the modules taken by a single student
• List the staff teaching a module.
• List the students enrolled on a module.
• Show the students profile to date.
• List all modules that are pre-requisites for a given module.
My code so far for creating the types and nested tables is shown below. Can anyone please tell me if I am on the right lines or what I need to do next, I am getting confused. Thanks in advance.
CREATE TYPE person_type AS OBJECT ( name VARCHAR2(30), email VARCHAR2(50), phone VARCHAR2(20), address VARCHAR2(50) ) NOT FINAL; / CREATE TYPE staff_type UNDER person_type ( staff_id VARCHAR2(30), staff_type VARCHAR2(30))NOT FINAL; / CREATE TYPE student_type UNDER person_type ( student_id VARCHAR2(30), student_type VARCHAR2(30))NOT FINAL; / CREATE TYPE alumni_type UNDER student_type ( )NOT FINAL; / CREATE TYPE lecturer_type UNDER staff_type ( )NOT FINAL; / CREATE TYPE postgraduate_type UNDER student_type ( )NOT FINAL; / CREATE TYPE professor_type UNDER staff_type ( )NOT FINAL; / CREATE TYPE senior_lecturer_type UNDER staff_type ( )NOT FINAL; / CREATE TYPE undergraduate_type UNDER student_type( )NOT FINAL; / CREATE TYPE course_type AS OBJECT ( course_id VARCHAR(30), course_name VARCHAR(30)) NOT FINAL; / CREATE TYPE module_type UNDER course_type ( module_id VARCHAR2(30), name VARCHAR2(30), module_year VARCHAR2(30), module_level VARCHAR2(30) )NOT FINAL; / CREATE TABLE course_obj_table OF course_type ( PRIMARY KEY (course_id)) OBJECT IDENTIFIER IS PRIMARY KEY; CREATE TABLE module_obj_table OF module_type ( PRIMARY KEY (module_id)) OBJECT IDENTIFIER IS PRIMARY KEY; CREATE TABLE staff_obj_table OF staff_type ( PRIMARY KEY (staff_id)) OBJECT IDENTIFIER IS PRIMARY KEY; CREATE TABLE student_obj_table OF student_type ( PRIMARY KEY (student_id)) OBJECT IDENTIFIER IS PRIMARY KEY;