While going through Database architecture I came across Schema's . I am very much confused with:
Logical schema
Physical schema
How these two schema exist in the Database?
How can these be manipulated according to need?
How Logical schema and physical schema are related?
A logical schema won't exist in your database. A logical schema is a design-centric database structure built to meet your business requirements. It is a model that exists on a white board or in a diagraming tool. It is like the architect's drawings of your database.
A physical model is what is actually implemented in your DBMS.
The two can be different for a variety of reasons and in several ways:
Your logical model should be properly normalized, but your physical model may have denormalization which you've added deliberately and for all the right reasons.
Your physical model may have different naming conventions. Some people use plain English (or the language of choice) for their logical models and impose a more "system-ish" naming convention in their physical model.
Your logical model may have many-to-many relationships. Physical models implement m:n using intersection tables.
Your logical model may only use natural or business keys. Physical models may also add surrogate keys.
You should have a logical model because it lets you think about your database design without having to get bogged down in physical constraints. You need to have the physical model because that will be where your data eventually lives.
How Logical schema and physical schema are related?
I will explain this concepts with the help of this picture bellow:
We will take as an example an Oracle database(but this apply's to most of RDBMS).
Logical Schema
- is made out of actually what you plan(your initial data model) and it's objects are:
- tablespaces
- tables
- views
- procedures
- pkgs etc...
Physical Schema
-here is where you have your data files, redo logs, control files etc, physical objects that reside on a O.S. and are in row state. These objects are used by logical structures to make our data make sense and readable.
How these two schema exist in the Database?
A schema is the set of metadata (data dictionary) used by the database, typically generated using DDL. It defines attributes of the database, such as tables, columns, and properties. A database schema is a description of the data in a database.
One or more schemas can reside on the same database.
How can these be manipulated according to need?
A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are logical structures created by users. Objects may define areas of the database to hold data, such as tables or indexes, or may consist just of a definition, such as a views or synonyms.
Objects in the same schema can use storage in different tablespaces, and a tablespace can hold contain storage from different schemas.
Schema objects can be created and manipulated using SQL. As an administrator, you can create and manipulate schema objects, just as you can the logical and physical structures of your database.
i hope it my answer helped.
I started working with databases in the 70's and tend to use data dictionary to mean the physical representation of data, and the schema to mean the logical model.
A schema is like a blueprint that helps you visualize the way the data is organized.
A data dictionary is the list of table names, column names, lenghts, variable types and other specific, up-to-date information that will be stored in the database.
I have used these definitions for so long that I have forgotten where I first read them. Usually when on a new project, with a new team, or new co-worker I like to confirm that we are using the terms similarly. As with so many buzzwords in IT jargon, different people sometimes use the same terms differently, so it's good to verify.
