Oracle APEX Project – Database

The task is to Design; Implement; and Test; a database to manage data for a scenario project.

You will need to do the following;

1. Design The Conceptual Database

1.1 Entity Relationship Diagram

You will be given scenario and will need to create a data model for it (ERD), The completed design should at least be in 1NF and have resolved any many-to-many relationships, and may be drawn using either UML or Crows Foot (information engineering) notation.

You can create the ERD with any tool of your choosing, it is preferable to use MS-Visio, but not necessary.

1.2 Business Rules / Relationships for the ERD

You are required to write the business rules (BR) for the relationships you develop between the entities of your ERD. You may need to briefly explain any assumptions you have made as part of your Business Rules section, and you may demonstrate how a many-to-many business rule became sets of one-to-many business rules or you can just include all the final business rules that accurately reflect your final ERD.

Your business rules must reflect all the relationships drawn in your ERD and should adequately describe the business and/or information processes that the database will manage in everyday language.

2. Logical Database

2.1 DDL-Schema

Your DDL-Schema is the list of all your tables, the columns that make up each table, along with which of those columns are primary keys (PKs) and which are foreign keys (FKs). This very important step moving from your conceptual model to your logical model is designed to help you work out your Table Creation order, and so the DDL-schema must be in the right order.

2.2 Data Dictionary

Your data dictionary is a second important step in moving from your conceptual and logical database to the physical one. It becomes the foundation for all the SQL code you will write to implement and test your working database. It is much easier to write your code after you have written your dictionary, so you must include it.

The names, relationships, constraints and metadata must match EXACTLY the names/conventions used in your ERD notation and the SQL coding syntax of our project.

3. SQL Implementation

3.1 Create Table SQL Syntax

Write SQL statements to implement your design (i.e., table creation). You will be creating your database in an Oracle environment.

It is recommended you keep a record of your syntax using a program such as Notepad++ in case you have to “DROP” created tables and so write the scripting again, as well as this you will need to present your syntax and present the result of your syntax as part of the project.

2.2 INSERT (data population) SQL Syntax

You will need to insert (data populate) enough data into all the tables in your database to capture 4 weeks of activity/records associated with the scenario. Details will be provided in the interview.

4. SQL testing/statements

4.1 Whole Season/Competition of Races and Race Results (scenario)

There will be queries that need to be done, we will provide this in the interview.

When you successfully complete this project, there are additional projects, we can offer to you. We are looking for individuals that are willing to complete these projects within our time-frames.

Candidates need to have experience in;
– Oracle Application Express
– MySQL
– ERD Database Design
– English Speaking and Writing Skills

The job is for an Australian client, located in Perth, WA. There is no time difference between Perth WA and the Philippines, so communication is direct and reliable.

Look forward to connecting with you!