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.

OracleTech

Add comment