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”)
/