Olaf Heimburger's Blog

Huuh, creating a database? Why is that? I just want to display the data...

The Online Survey Application

Since there are so many types of web-based applications we just pick one as a sample to implement. The demo application we are building during this series is an Online Survey. The Online Survey has these requirements:

• Have authors to create, update, and delete surveys.
• A survey has a number of questions.
• Each question may have a number of possible answers.
• A question may be answered mandatory.
• An answer of a question can be a text field, a single value from a multiple-choice list or multiple values from a multiple-choice list.
• Each applicant has it's own list of answers.
• The author can request reports of the answers per survey.

The Overall Project Structure

Before we start to create a data model according to the requirements above, we need to plan the project setup for a good project work. In JDeveloper we have the concept of an Application which consists of a number of Projects. No Project can be used outside of an Application. Applications and Projects are stored in different file types. When you examine your disk you will find a jws file for the Application and some jpr files for the Projects.
The structure of the Online Survey application will look like this

• Online Survey Application
• Database Project
• Model Project
• etc.

Creating the Project Structure

To create this structure in JDeveloper, just start your favorite version and follow the steps below.

In JDeveloper 10.1.3

1. Select the Applications root in the Applications Navigator
2. Open the Context Menu and select New Application ...
3. The Create Application wizard opens
4. Set the Application Name to OnlineSurvey.
5. For the Application Package Prefix we chose demo.survey.
6. As Application Template select the No Template [All Technologies] template.
7. Click on the OK button to finish the wizard.
8. Immediately after creating the application OnlineSurvey, JDeveloper opens the Create Project wizard for the first project in the application tree.
9. Enter Database for the Database project.
10. Click on the OK button to finish the wizard.

In JDeveloper 11g

1. In the Application Navigator click on the New Application ... button.
2. The Create Application wizard opens.
3. Enter the Application Name as OnlineSurvey
4. For the Application Package Prefix we chose demo.survey.
5. As Application Template we select the No Template [All Technologies] template.
6. Click on the OK button to finish the wizard.
7. Immediately after creating the application OnlineSurvey, JDeveloper
   opens the Create Project wizard for the first project in the
   application tree.
8. 
   Enter Database for the Database project.
9. 
   Click on the OK button to finish the wizard.

We now have the first parts of our project structure created, let's continue with the database design.

The Data Model

The dynamic nature of the Online Survey suggests to store the configuration of each survey into a database.  The current status as well as the results of the survey need to be stored in the same database. According to the requirements above the following tables need to be created:

• AUTHORS
• SURVEYS
• QUESTIONS
• ITEMS
• RESPONSES
• ANSWERS

Each of these tables has an id column for a unique key (aka primary key). Some of them have foreign key relationships to other tables.

The Database Diagrammer

For better visualisation and understanding, building a diagram in the JDeveloper Database Diagrammer is a good approach for design and verification with others. Both versions of JDeveloper support the Database Diagrammer, so the next task is to create a diagram for the Online Survey application:

1. In your OnlineSurvey application click on the Database project.
2. From the Context Menu select New ... to open the New Gallery.
3. In the New Gallery expand the General group in the Categories panel and select the Diagrams entry. In the Items area on the right all possible diagram types will be displayed.
4. Select Database Diagram and click on the OK button to open the Create Database Diagram wizard.
5. Enter Online Survey for the Name field, set the Package to demo.survey.db and click on the OK button to start the diagram creation.

JDeveloper 10.1.3 vs. 11g

Although the differences between 10.1.3 and 11g are minimal, you will notice them immediately. The obvious differences for the Database Diagrammer are in the Component Palette. First the ordering is a bit different and there are more components. With 11g it is now possible to differentiate between Public and Private Synonyms, create Materialized Views and Join Objects. A minor change is the different icon for the Foreign Key relationship.

Creating the Database Model

To create the database model for the Online Survey application, we use the Database Diagrammer for modelling all the tables and columns we can imagine so far. To do this we need to select the Table component from the Component Palette and move it to the diagram.

Tables In JDeveloper 10.1.3

JDeveloper 10.1.3 allows to create a number of Table components at once by holding the Shift key and clicking in the diagram area. This way we create six Table components. End this behaviour by selecting the Pointer component.

Tables In JDeveloper 11g

JDeveloper 11g behaves a bit differently here. It does not assume anything and when the first component is placed on the diagram area, an Offline Database wizard appears. Here the Offline Database can be selected or created.

1. To create a new Offline Database click on the New... button and a Create Offline Database wizard window opens.
2. Complete the Properties entry with Name: SURVEY and Default Schema: SURVEY.
3. For Database to emulate select Oracle 10g Express Edition Release 2 from the drop down list.
4. Click on the OK button to close the window.
5. The SURVEY offline database will appear in the Offline Database drop down list.
6. Click on the OK to close the Offline Database window.

Now five additional Table components can be added. Unlike JDeveloper 10.1.3 the Table component must be selected from Component Palette every time.

Both Versions

To change the name from the generic TABLEn to a real one simply click on on the name and use one of the following for the table component names: AUTHORS, SURVEYS, QUESTIONS, ITEMS, RESPONSES, and ANSWERS.
For every Table we have to add an ID column which acts as the primary key. As an example the Table AUTHORS will be used:

1. 
   Double click on the Table AUTHORS. The Edit Offline Table window opens with the Column Information selected.
2. 
   Clicking on the green plus creates a new column named COLUMN1.
3. 
   Since the name should be ID the name COLUMN1 has to be changed to ID.
4. 
   Now select the Type as NUMBER with a Precision of 38.
5. 
   Finally, the Cannot be NULL must be selected as this is the primary key with a known value.
6. To make the ID the primary key, click on the Primary Key category, select the ID column and moved it to the select part ...
   
7. 
   Clicking on OK closes the window and the ID column appears in the AUTHORS Table component in the diagram area.

Do the same for the tables SURVEYS, QUESTIONS, ITEMS, RESPONSES, and ANSWERS.

Sequences

To generate a table specific number for every table we create six Sequences:

1. 
   Click on the Sequence component and move it to the diagram area. Do this six times.
2. 
   Rename the just created sequences to SEQ_AUTHORS, SEQ_SURVEYS, SEQ_QUESTIONS, SEQ_ITEMS, SEQ_RESPONSES, and SEQ_ANSWERS.

To leverage the Oracle Database default behaviour of an unbounded
sequence incrementing by 1 everything else could be left blank.

Foreign Key Relationships

To establish relationships between tables, Foreign Key Relationships need to be modeled. In the database diagrammer a Foreign Key Relationship has the semantics from One To Many, one row in a table has a relationship to many rows in another table. For the Online Survey model the following relationships need to be created:

• One Author has Many Surveys (AUTHORS to SURVEYS)
• One Survey has Many Questions (SURVEYS to QUESTIONS)
• One Question has Many Items (QUESTIONS to ITEMS)
• One Survey has Many Responses (SURVEYS to RESPONSES)
• One Response has Many Answers (RESPONSES to ANSWERS)

Using the AUTHORS to SURVEYS foreign key relationship as an example for all:

1. Select the Foreign Key component from the Component Palette
2. Move the mouse to the AUTHORS table and select it
3. Move the mouse to the SURVEYS table and select it
4. A Create Foreign Key window opens and shows the name of the foreign key (SURVEYS_AUTHORS_FK) and the related table columns. This window helps to verify the correctness of the relationship, where the existing primary key column the One part of the relation is and in the right column of the table appears, ie. ID from AUTHORS in this case. By clicking on the OK button the defaults are accepted, the window closes and a line between AUTHORS (with a 0..1 on this end) and SURVEYS (with a * on this end) is drawn.

Repeat this for the other relationships to build the complete picture.

More Table Columns

Finally, each table consists of a number of additional columns. These can be added by the same technique as the one used for the ID column above. The additional columns for the tables are as follows:

• AUTHORS
• name : VARCHAR(200)
  
• SURVEYS
• name : VARCHAR2(200)
• introduction : VARCHAR2(400)
  
• valid_from : TIMESTAMP
• valid_to : TIMESTAMP
• QUESTIONS
• text : VARCHAR2(200)
  
• ITEMS
• text : VARCHAR2(200)

A Note on JDeveloper 10.1.3

You might have noticed it! By default JDeveloper creates every new Database Object in the MYSCHEMA schema. You can either

1. Create all Objects (ie, Tables, Sequences, etc.) as described above and change the schema in every object by selecting the object in the Application Navigator, selecting Properties... from the Context Menu and change the Schema Information (eg. in the Table Information section or Sequence Information) to SURVEY.
2. Create only one object like a Table, change the Schema as above and create the all other objects from the Application Navigator by selecting the SURVEY node and choosing New Database Object ... from the Context Menu.

The Database Creation Script

The last step is to create an SQL script for setting up the SURVEY schema in the database. JDeveloper can do this online, but it is a good practice to create a script for reproducable code, source code control, and later reference.

There are two ways to start the SQL script generation for the database model (aka Data Definition Language)

In JDeveloper 10.1.3

1. Open the Context Menu on the SURVEY database diagram and move to the Generate -> Data Definition Language for Diagram
2. In the Applications Navigator expand the Database project and the Offline Database Sources folder, open the Context Menu on the SURVEY schema and move to Generate or Reconcile Database Objects ...

Both steps open the Generate SQL from Offline Database Objects wizard.

1. The wizard Welcome page will be show on the first call of the wizard. Check the Skip this Page Next Time checkbox.
   
2. Click on the Next button to go to Step 1 (Select Objects).
3. On the Selected area you will find all the database objects that should be created.
4. Click on the Next button to go to Step 2 (Generate Objects).
5. Select the Generation Type: CREATE Objects
   
6. Click on the Next button to go to Step 3 (SQL Script Options).
7. Select the Generate SQL Scripts.
   
8. Select the Prefix object name with schema option.
9. Set the SQL File Name to create_survey_schema.sql
10. Click on the Next button to go to Step 4 (Database Option).
11. Do not select anything on this page!
12. Click on the Finish button to create the SQL file.

In JDeveloper 11g

1. Open the Context Menu on the SURVEY database diagram and move to the Synchronize to Database -> Generate To ->  SQL Script...
2. In the Applications Navigator expand the Database project and the Offline Database Sources folder, open the Context Menu on the SURVEY schema and move to Generate  ...

Both steps open the Generate SQL from Offline Database Objects wizard at Step 4 (Choose Operation).

1. For the first run the selected CREATE action is the right choice. The other values are perfectly selected.
2. Click on the Next button to go Step 5 (SQL Script Options)
3. Select the Prefix object name with schema option
4. Set the SQL File Name to create_survey_schema.sql
5. Click on the Next button to go to Step 6 (Finish) which shows the Overview of the selected options.
6. Click on the Finish button to create the SQL file.
7. Optional: Check the Save settings for reuse to reuse these settings.

So far so good

We have created a basic data model for the Survey application. It is far from complete and will be refined incrementally during the interations of the application.

What's Next?

The next step is the O/R-mapping with a decent framework. JDeveloper supports TopLink, EJB 3, and ADF Business Components out of the box. Each one will be covered separately in the next posts.