DDL Scripts from a Data Model

How to create DDL Scripts from Data Model?

  • Open the physical data model “Data_Modeling_Relationship.txl”.
  • Click menu Model/Verify Model.
  • A new window will be opened.
  • Check the appropriate objects like entities, functions, packages, materialized views, relationship, synonyms, data types etc which are listed in the new window.
  • Click “Verify” button on the bottom of the page.
  • It shows the errors and warning in a data model. If there are no errors and warnings.
  • Click Menu/Generate DDL Script.
  • A new window will be opened. It has tabs like what to generate, detail settings, referential integrity, Select List.
  • Check the appropriate objects like directories, editions, entities, functions, java, materialized views, packages, permissions to objects, procedures, relationships, sequences, synonyms, user data types, user groups, views etc which are listed in the new window.
  • In the bottom of the window, you can see buttons like Generate, Show Code, Show Log.
    • Click the button ‘Generate’. It will save the DDL scripts in a file under a folder. You can see the location of the file in “what to generate tab”. Example: C:\Users\Data Modeling\Documents\Toad Data Modeler\GeneratedScripts\Generated.SQL
    • If you have access to the databases, then you can directly execute those scripts and create database objects in database or database schema.
  • Click Show. It will retrieve the information from that file “Generated.sql” and code is shown below.

— Create tables section —————–

— Table DEPARTMENT

CREATE TABLE “DEPARTMENT”(

“DEPARTMENT_NO” Integer NOT NULL,

“DEPARTMENT_NAME” Varchar2(30 ) NOT NULL

)

/
— Add keys for table DEPARTMENT

ALTER TABLE “DEPARTMENT” ADD CONSTRAINT “Department_PK” PRIMARY KEY (“DEPARTMENT_NO”)

/

 

— Table TITLE

CREATE TABLE “TITLE”(

“TITLE_CODE” Integer NOT NULL,

“TITLE_DESCRIPTION” Varchar2(30 ) NOT NULL

)

/

 

— Add keys for table TITLE

ALTER TABLE “TITLE” ADD CONSTRAINT “TITLE_PK” PRIMARY KEY (“TITLE_CODE”)

/

 

— Table PROJECT

CREATE TABLE “PROJECT”(

“PROJECT_CODE” Integer NOT NULL,

“SOFTWARE_USED” Varchar2(30 ) NOT NULL

)

/

 

— Add keys for table PROJECT

ALTER TABLE “PROJECT” ADD CONSTRAINT “PROJECT_PK” PRIMARY KEY (“PROJECT_CODE”)

/

— Table EMPLOYEE

CREATE TABLE “EMPLOYEE”(

“EMPLOYEE_NO” Integer NOT NULL,

“DEPARTMENT_NO” Integer NOT NULL,

“TITLE_CODE” Integer NOT NULL,

“EMPLOYEE_NAME” Varchar2(30 ) NOT NULL,

“MANAGER_NO” Integer

)

/

 

— Add keys for table EMPLOYEE

ALTER TABLE “EMPLOYEE” ADD CONSTRAINT “EMPLOYEE_PK” PRIMARY KEY (“EMPLOYEE_NO”)

/

— Table EMPLOYEE_PROJECT

CREATE TABLE “EMPLOYEE_PROJECT”(

“EMPLOYEE_NO” Integer NOT NULL,

“PROJECT_CODE” Integer NOT NULL

)

/

 

— Add keys for table EMPLOYEE_PROJECT

ALTER TABLE “EMPLOYEE_PROJECT” ADD CONSTRAINT “EMPLOYEE_PROJECT_FK” PRIMARY KEY (“PROJECT_CODE”,”EMPLOYEE_NO”)

/

 

— Create relationships section ————————————————-

ALTER TABLE “EMPLOYEE” ADD CONSTRAINT “EMPLOYEE_FK01” FOREIGN KEY (“DEPARTMENT_NO”) REFERENCES “DEPARTMENT” (“DEPARTMENT_NO”)

/

 

ALTER TABLE “EMPLOYEE” ADD CONSTRAINT “EMPLOYEE_FKO2” FOREIGN KEY (“TITLE_CODE”) REFERENCES “TITLE” (“TITLE_CODE”)

/

 

ALTER TABLE “EMPLOYEE_PROJECT” ADD CONSTRAINT “EMPLOYEE_PROJECT_FK02” FOREIGN KEY (“PROJECT_CODE”) REFERENCES “PROJECT” (“PROJECT_CODE”)

/

 

ALTER TABLE “EMPLOYEE_PROJECT” ADD CONSTRAINT “EMPLOYEE_PROJECT_FK01” FOREIGN KEY (“EMPLOYEE_NO”) REFERENCES “EMPLOYEE” (“EMPLOYEE_NO”)

/

 

ALTER TABLE “EMPLOYEE” ADD CONSTRAINT “EMPLOYEE_FK03” FOREIGN KEY (“MANAGER_NO”) REFERENCES “EMPLOYEE” (“EMPLOYEE_NO”)

/

 

(Visited 1,170 times, 1 visits today)

Leave a Reply

Your email address will not be published. Required fields are marked *