Data Management Technology

KIT712 Data Management Technology

Project Phase 3

This phase needs to use Oracle Database for implementation part.

Phase 3.1 Logical Modelling (5 Marks)

You need to update your ER diagram based on the feedback you will receive on the ER diagram submitted in Phase 2. You may contact your tutor or unit coordinator to check whether you have correctly updated your diagram based on the feedback. This is an important step as you may loose marks for Phase 3 if your Relational Model does not cover correct number of tables based on the business rules used in Phase 2.

Using the updated ER Model, you need to develop your relational model by following conversion algorithm steps as taught during tutorial. You need to give details of each conversion step in your submission to get marks. You may seek help of your tutor for any feedback while doing this part.

In summary, you need to do following for this part of phase 3:

Update ER diagram based on phase 2 feedback. Get it checked by your tutor whether you have addressed the feedback.

Use conversion algorithm to convert ER diagram to Relational Model

Submission for this phase includes:

a. Revised ER diagram

b. All ER to relational model intermediatory conversion steps with final relational model (i.e. step 4)

Phase 3.2 Database Implementation (15 Marks)

In this phase, you need to consider the final relational model from phase 3.1 and the business rules used in Phase 2 to do final implementation of the database. You need to implement all the database tables with appropriate data types, constraints (e.g. primary keys, foreign keys, check constraints) and triggers. You need to also insert some dummy data in the database.

Based on the business rules following triggers are possible:

a) Changing the status of driver automatically to terminated once breach count reaches to 10.

b) Computing daily payment of driver based on payments received from the customers

c) Generation of invoice as soon as ride status changes to completion or cancellation.

NOTE: You may have to assume certain numbers such as OLA fees etc to during implementation of triggers. These assumptions should be clearly specified during your submission.

Submission for this phase includes: A zip file containing all SQL files for creating tables with required constraints and trigger codes. Also, you need to include a one page document for assumption you made during implementation.

Phase 3.3. Querying the database (5 Marks)

In this phase you need to insert some dummy/sample data in your database and implement atleast three PL/SQL procedures or functions that can extract data required to answer a query by the end user (i.e. a rider). Such queries can be of your own choice. For example, a rider may need a cab and enter starting location, the query will be “to extract location/details of all cabs near to starting location”.

Submission for this phase includes:

a. SQL file containing all Insert queries to add sample data

b. 3 PL/SQL codes

Deadline: Week 14 Friday 11.55 (3rd June 2022)