The Administration department wants to hold information about students, staff, the courses that are currently taught, the modules of each course and the takers of each course. The requirements collection and analysis phase of the database design process based on the Director’s view has provided the following data requirements specification for the Community Centre Administration Database System:
For each student and staff, the database maintains information about their
number, name, address (street, town, county, postcode), and date of birth. Although the age of students is not reported, the database should offer a function to calculate the age from the date of birth provided (please use PL/SQL technology to write such a function).
For each taught course, the database holds information about the course number,
course name, and department name. The course can contain many students
but each student can enroll on one course only. The database should offer a function to
obtain the number of students enrolled on any course. (please use PL/SQL technology to
write such a function)
Each course consists of a number of modules and these modules belong to one course
only. For each module the database holds information about the module number,
module name, and the credit.
The Department has recently bought the Oracle9i database and Java.2
platform and it requires you to use the OBJECT feature of these technologies.
Please can you do the following:
1- Design the conceptual schema of the database using UML notation.
2- Based on your conceptual schema, define your logical schema and provide all
DDL commands that are required to implement the administration database.
3- Populate the object tables to contain instances of the object types. All SQL
statements and their output should be shown. Examples include:
•©Inserting staff instances.
•©Inserting student instances.
•©Inserting courses instances.
•©Inserting module instances.
4- Formulate a set of retrieval queries that fully explore the likely functionality of
the application area. All SQL queries and their output should be shown. Examples
•©Displaying all staff information.
•©Displaying all student information.
•©Displaying all students and details of the courses they are taking.
•©Displaying the details of students taking a certain course.
•©Displaying the number of students taking each course.
5- Design the appropriate Java application interface to populate, update and delete
the staff and student database ONLY. The application should provide the
following functionalities (screen shots to demonstrate the following functions are
•©The options of adding and updating certain student information. Also the
option of retrieving all student information (including student age). (Hint: Please use
the student number as the key for dealing with student instances).
•©The options of adding and updating certain staff information. Also the option
of retrieving all staff information. (Please use the staff number as the key for
dealing with student instances)