Learning Outcomes Being Assessed

1.      Design of the relational database model for the new system.

2.      Ability to implement the designed database using Oracle DBMS.

3.      The completeness and the quality of the database implemented for your application.

Your Tasks.

No

Task

Deadline

1.

A complete ER data model in 3rd Normal Form. All assumptions and enhancements must be clearly written and presented.

All primary keys, foreign keys, relationships and attributes must be clearly shown.

2.

Prepare the Data Definition Language (DDL) statements to implement tables for the new system using the Oracle database system. Include relevant integrity constraints to ensure database integrity must be applied to each table. Include also the necessary check constraints and default values to enforce your business rules.

You are advised to insert sufficient data records for each table that you have created in the Oracle database server. Transaction tables would have more records than base tables. For example, if there are 20 students and 5 subjects per semester, then the student enrolment table would have [20 x 5 = 100 records]. If you maintain the enrolment for 5 semesters, you would have 500 enrolment transaction data.

Furthermore, you should create sample data that has different date/month/year to simulate a real-world environment.

3.

Produce queries to extract relevant information for decision making. Single table queries are not allowed. Make use of multiple tables and aggregate functions where necessary.

Each student is to produce at least 3 queries. The queries produced by the team must address the informational needs of management at 3 different levels: Strategic, Tactical and Operational.

4.

Design and create the stored procedures that cater for the various use case scenarios for new system.

Each student is to produce at least 2 stored procedures.

Procedures can be used for data manipulation (Add, Update, Delete), validation, etc.

5.

Design and create triggers that enforces system-wide business rules and policies.

Each student is to produce at least 2 triggers.

6.

Write procedures to generate reports (include summary, detail and on demand basis reports) for the company.

On demand basis report is a report that will be generated once it is called by the users, and parameter value(s) might be passed in during the call.

Each student is to produce at least (one summary, one detail and one on demand) report. Use cursor in report generation.