Database Schema

Database Schema

→ A database schema is the logical representation of a database which shows how the data is stored typically in the entire database. 
→ It contains list of attributes and instructions that informs the database engine that how data is organized and how the elements are related to each other.
→ Database schema is the skeleten structure that represents the logical view of the entire database. It defines how the data is organized and relations among them associated. 
→ It includes the tables, fields, relationship, views. indexes and other elements that make up the database.
→ A database schema object includes the following:- 
   ⤷ Consistent formatting for all data entries
  ⤷ Database objects and unique key for all data entries
  ⤷ Table with multiple columns and each column contains its name and data type

→ The given diagram is an example of a database schema. It contains three tables, their data types. This also represents the relationships between the tables and primary key as well as foreign key.

Types of database Schema

1. Physical Schema

→ It specifies how the data is stored physically on a storage system in the form of files and indices. 

→ Designing a database at the physical level is called a physical schema.

→ Physical schema are the most detailed type of database schema, providing information about how data is actually stored in a system.

→ This is also called internal schema. 

→ It includes details about file locations, indexing methods, partitioning schemas, storage allocation and storage format for each table in the database. 

→ The DBA decide where and how to store the data in different block of storage.

2. Logical Schema

→ A logical schema defines all the logical constraints that needs to be applied to the stored data and also described tables, views, entity relationships and integrity constraints. 

→ It is also known as conceptual schema represents the structure of the database.

→ It defines the tables, field, data types and relationship without considering how the data is physically stored.

3. View Schema

→ Defines how to show database to end user on screen.

→ It includes various views that can be created to simplify data access and enhance security by restricting access to certain data.

→ Also called subschema, external schema.

→ Views can be tailored to meet the specific need of different users or applications.