Fusion Blog

EBS Blog


Contact Us

  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
Database schema is the first step you would take when building a web application. So, I am going to start with showing how can you do DB schema modeling in JDeveloper. Also, I will use schema created in this article for other ADFbc and ADF UI related examples to follow.
Assuming you have a DB installed that you can connect to, we will use the famous scott/tiger schema. You can also download this sample schema project.

Create a new Project

  1. Open JDeveloper and use "Default Role".
  2. Click File : New and select General : Applications : Generic Application.
  3. Give application name and directory where you want to store the source files and click next.
  4. Give project name and finish. This will create a new application and new project where we can model DB schema.

Create DB Diagram

  1. Click File : New and select Database Tier : Databse Object : Database Diagram
  2. Give it a name and save.

Create Offline DB

Offline DB is the JDev version of your actual physical DB. You can make changes to offline DB and then propage to the online DB or any other DB where you need those changes.

  1. Click File : New and select Database Tier : Offline Databse Object : Offline Database
  2. Give it a name and package and save.

Import existing objects to Offline DB

You can start from sctatch and create objects into Offline DB or if you already have some tables, views and other objects, you can import into Offline DB. Let us use scott/tiger schema to import some of the existing tables and then modify offline DB for changes we want.

  1. Click File : New and select Database Tier : Offline Databse Object : Copy database objects to a project.
  2. In Step 1 of 5, create a new DB connection to using TNS where you have your scott schema and click next.
  3. In Step 2, keep defaults and click next.
  4. In Step 3, click on Query to see what all objects are available. Click on Filter Type to select what type of objects you want to serach.
  5. Select EMP, DEPT, SALGRADE and BONUS in available list and click ">" arrow to move them to selected list. Click Next
  6. In Step 4, choose CREATE and click next.
  7. Click Finish on Step 5. You can see the objects in Offline DB that you just imported.
  8. Drag-And-Drop all tables onto the DB Digram created above. Now you have Offline DB and diagram ready for edits.

Adding PK and FK to tables

  1. Double click on EMP table in offline DB or diagram to open the table editor.
  2. Choose Primary Key and Move EMPNO from available to selected columns.
  3. Double click on DEPT table in offline DB or diagram to open the table editor.
  4. Choose Primary Key and Move DEPTNO from available to selected columns.
  5. Click on "Foreigh Key" under component palette to create FK relationship between DEPT and EMP tables.
  6. Click on DEPT.DEPTNO and then click on EMP.DEPTNO. This will show up FK dialog. Accept the defaults.
  7. Notice that the 0..1 to * relationship is captured in the DB diagram.

Modify table columns

  1. Click on the BONUS table in DB diagram. Notice a blue row highlighted.
  2. Click on the blue row again and it will add a new column with default name and datatype.
  3. Change that to EMPNO : NUMBER(4, 0) and click somewhere else in the diagram. This adds a new column to table.
  4. Click on ENAME and press "Delete" so that that column is deleted from the table.
  5. Select EMPNO, right click and select "Add to Primary Key". This is another way to set PK on table.
  6. Add a FK from EMP to BONUS with EMPNO.
  7. Drag-And-Drop Table from component palette onto the diagram. Choose Application Project.
  8. Give the talbe name = LOCATIONS
  9. Add three columns LOC : VARCHAR2(13 BYTE), LNAME : VARCHAR2(13 BYTE), LDESC : VARCHAR2(100 BYTE).
  10. Make LOC as PK for table.
  11. Add a FK relationship with DEPT.LOC column.

Apply the changes to Online DB

  1. Right click on the diagram and select "Synchronize with Database : Generate TO : ".
  2. Choose options as ALTER and finish the wizard. 
  3. Verify your DB by connecting to it and checking if changes are applied successfully or not.
  4. Right click on the diagram and select "Synchronize with Database : Generate TO : SQL Script". 
  5. Select "CREATE" as option. This generates DDL script that can be used if you want to apply same schema on other DB.



0 #1 dimple 2009-11-25 05:28
Thanks for such a beautifully composed, informative article.I think your designing work to this is really great .I really appreciate your work to this site.So thanks for it.I hope you can continue this type of hard work to this site in future also..Because this blog is really very informative and it helps me lot.
play in online roulette websites
0 #2 dissertation help 2010-10-29 02:01
nice article sharing
0 #3 ChavezMarcy 2011-07-27 17:59
Set your own life time more easy take the home loans and everything you need.
0 #4 http://www.coachoutletsstoreonlines.net/ 2012-09-06 20:29
Very grateful to you for bringing me to come here, let me share the happiness; article will bring me good mood, if you want to share with you on the click of a mouse you will have unexpected gains.Coach Factory natural elegant, high-grade soft fabric, light perception unique metal buckle for mainstream, shaping Variety fashionable!
0 #5 http://www.coachs-factoryoutletonlines.com/ 2012-09-06 20:45
This blog will be passed to each website can stroll through for all the data to allow more people to enjoy such a good article, I believe that we can see here, it will strive to do every step of the positive.Also hope that we pass along this great blog.Share it with more people.
0 #6 burberry outlet 2012-11-08 19:08
insulted by the violation of decorum, and interested in the preservation of order,

Add comment

Security code

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Aug 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
  2  3  4  5  6  7  8

Enquire For Training

Related Items

Fusion Training Packages

Get Email Updates

Powered by Google FeedBurner