Generating Database Change Script

In order to patch existing database to newer version, database administrator need to prepare DDL (Data Definition Language) scripts with commands of updates. Visual Paradigm is capable in updating database, or just to generate the DDL required, by comparing differences between the design, which is the ERD(s), and the database, and to generate the DDL scripts accordingly. In addition to generating update scripts, Visual Paradigm can also generate scripts for creating and dropping database. In this tutorial, we will focus on generating the update scripts.
Topics
1. Update database design - the ERD
2. Generate database scripts for "Update Database"
3. Execute scripts in Oracle

September 1, 2009
Views: 51,378
PDF Download
Compatible: Enterprise, Professional, Standard

The original database design for the Computer Sales project:

Computer Sales project

  1. Let's update the design. Rename the entity Addresses to Customer_Addresses.
    Rename entity in ERD
  2. Add a new column to the entity Computer_Part by right-clicking on it and selecting New Column from the popup menu.
    Create new column in entity
  3. Enter discount : numeric(8, 2) as the column definition, press Enter, and then press Esc to confirm editing.
    Enter new columns in ERD
  4. Move the mouse cursor over Computer_Part, click on the One-to-Many Relationship -> Entity icon in the resource-centric interface, and drag to the empty region to create a new entity. Enter Part_Usage as the entity name.
  5. Rename the automatically created FK (foreign key) column to part_id.
  6. Repeat the previous steps to add the following columns to Part_Usage.
    • +usage_id : number(10)
    • description : varchar2(1024)

    Create new columns in entities
  7. The design is now updated. To generate the scripts of changes, select Tools > Database > Generate Database... from the main menu.
  8. In the Database Code Generation dialog box, specify the output path of the DDL script file.
    Specify output path of generated script
  9. Select Update Database as the type of generation.
    Generate script to update Oracle database schema
  10. Uncheck Export to database to generate only the scripts file.
    Uncheck on export to database
  11. Click OK to start generation.
  12. The script is generated to the given path.
    Open generated update database script
  13. Let's take a look at the generated scripts.
    Take a look to generated script
  14. To execute the scripts, open the Oracle Web client, select SQL > SQL Scripts, and click Upload to upload the generated DDL file.
    Upload Oracle database update script
  15. Once the file is uploaded, click the script icon.
    Select available database script
  16. Click Run to execute the scripts to the database.
    Run Oracle database script
  17. Click Run again to confirm.
    Confirm to run select database script
  18. Click the file icon to check the result.
    View database script execution results
  19. The scripts are executed successfully. The database is updated.
    Detail view of execution result
  20. You can see the changes in the Object Browser.
    Confirm database is updated with Oracle object browser
Resources
  1. What is Entity Relationship Diagram (ERD)?
  2. Evaluate Visual Paradigm for FREE
Trademark Disclaimer

Oracle is a U.S. registered trademark of Oracle Corporation