CODEWITHSUNDEEP
sqloracle-databasedata-modeling
Velocity
Velocity

Reputation: 479

Oracle How to download/display physical data model

I am designing a database and need to show the logical and physical data models. I am using SQL developer and SQL Data Modeler for the same.

The logical model is fine and it gets generated via Data Modeler: Relational Model-> Engineer to logical Model. This generated the logical model in blue.

So my question or what i wish to understand is, what exactly is physical model. Just the scripts generated in DDL editor or it is also a flowchart type design?.

Oracle Database:19c SQL Developer 21.1

Upvotes: 1

Views: 939

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21085

The strict distinction between the logical and physical schema was proposed in a ANSI standard decades ago.

For relational database (and other as well) in general and for Oracle in specific case this distinction was never realized.

There is (in the database) only one level - the DDL which covers the whole definition.

For your particular purpose you may of course (following the original definition) consider

DDL as a physical model

ERD that leaves out indexes, partitioning, tablespaces etc as a logical model

Depending on your design process you may either reverse engineer the ERD from you schema or create it first (using some tool or tool chain) and transform (generate) it in DDL

Upvotes: 0

k33da_the_bug
k33da_the_bug

Reputation: 820

The Physical level is nothing but how your DB vendor implements things in background at lowest level. Something like your DB vendor might be using some complex data structure to store out data. Generally it is very low level representation. He/She might have implemented some optimization features using some complex logic and data structures.

Consider an example you have School DB which contain Student details. You know that Student has roll no, name, phone address attributes. Now you choose RDBMS or NO-SQL your student will remain same in both the cases having logical structure as Student(name,roll_no,phone,address) but its physical implementation in RDBMS is different and in No-SQL is different i.e storage method it implements, its data types and how relations are maintained behind the scenes are different.

Usually ERD tools helps to map your data model logically. You might see table like structures connected to each other with relations, that is logical level representation of your data model, these tools include DDL generation feature which generates your logical mappings into vendor specific DDL commands. Generated DDL is still treated as logical schema, it then gets converted to physical schema once you fire the query. When you use DDL commands the DBMS will convert your DDL to low level implementation, internally it will apply different optimizations, maintain some complex data structures to store your actual data.

In lay mans term Physical level is low level structural implementation of your data model, and Logical is high level implementation of your data model.

Upvotes: 1

Related Questions