Database Schema, Definition, Design, Types and Example

What is database schema?

Database schema is a visual representation of database. The structure is described by formal language and is supported by database management system (DBMS). It represents logical view of the database.

The database schema has a list of attributes and instructions for database engine showing how data is organized and how the components are associated in a database.

A database schema describes entities and relationship among them.

What is schema?

In its literal meaning, the term schema is called “form” or “shape” of the database which shows that how the data is organized in database.

A schema is a representation of design or idea in the shape of model. A database may contain one or multiple schemas.

A database schema consists of objects like tables, views and stored procedure etc. In other words, we can say that database schema is a container of objects.

A collection of database objects is called schema in Oracle Database. These objects are associated with a database user name who is called schema owner.

Depending upon database privileges, user can have complete control over these objects.

What is database instance?

Database schema is the skeleton of a database. Database schema is designed before creation of database. A database schema does not contain any information or data.

While a database instance is a state of an operational database having information or data. It tends to change with the time. A database management system(DBMS) ensures that its every instance follows validation, constraints, and conditions imposed by database designers and that every instance is in a valid state.

Database schema design tool

There are many online design tools available for creating database schema design like dbschema,  lucidchart, vertabelo, mongodb and many more. By the help of them you can easily design and create database schema and diagrams.

Database schema diagram

Physical Schema:

At physical level describes how data is stored in blocks physical on a storage system. The design of a database at this level is called physical schema.

Logical Schema:

The working is performed by programmers and database administrators at this level. The data records are stored is stored in data structures. The implementation of data structure is not visible at this level. Design of database at logical level is called logical schema.

It describes logical constraints that apply to the stored data. It may define integrity constraints, views, and tables.

View Schema:

Database design at view level is called view schema. It pertains to interaction of end user with database systems.

Schema reduce the work requirement and improve flexibility for security relating administration of database.
The database objects protected from being altered by users without permission of database owner.
The Active Directory Schema as defined in technet.microsoft.com is “active directory components that defines all the objects and attributes that the directory service uses to store data. The physical structure of the schema consist of object definitions. The schema itself is stored in the director”.
The schema components are objects(used to store data in directory), attributes(rules defining the structure of objects) and classes(structure and content of directory).

The schema instructs about how information stored, therefore, if any change made in schema is replicated among all the domain controllers.

Read also: What is database and its types

nc