Page 76 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

Database Schema
A database schema is a logical and structured representation of the organization, arrangement, and relationships
among the data stored in a database. It defines the design, format, and constraints of the data stored in the
database tables, along with the interconnections between these tables. In essence, a database schema outlines
the blueprint for how data is organized, stored, and accessed within a database management system. It includes
information about tables, fields, data types, relationships, constraints, and other elements that define the structure
and integrity of the database.
Types of Database Schema
The database schema is categorized into three types, namely

1 Logical Schema
2 Physical Schema
3 View Schema
1 Physical Database Schema
• The physical schema outlines how data is physically stored within storage systems as files and indices.
It involves the concrete code or syntax required to establish the database’s structure. When crafting a
database structure on the physical level, it is referred to as the physical schema.

• The choice of data storage locations and methods within various storage blocks is made by the Database
Administrator.
2 Logical Database Schema
• The logical database schema encompasses all the rational restrictions to be enforced on the stored data,
as well as outlines the tables, perspectives, entity connections, and integrity constraints.
• The logical schema elucidates the manner in which data is stored, comprising tables and their interconnected
attributes.
• Through the utilization of ER modeling, the connections among data elements are upheld.
• Within the logical schema, diverse integrity constraints are outlined to ensure the accuracy of data insertion
and updates.
3 View Schema
• This refers to a view-level design that outlines how interactions between end-users and the database are
defined.
• Users can interact with the database through an interface without requiring extensive knowledge about the
underlying data storage methods employed within the database.

Three Layer Schema Design
Creating Database Schema
To create a schema, the “CREATE SCHEMA” statement is employed in various databases. However, the
interpretation of this statement can differ across different database systems. Let’s explore some examples of
statements used for creating a database schema in various database systems:
1 MySQL: In MySQL, the “CREATE SCHEMA” statement is utilized to create a database. This is because, in
MySQL, both “CREATE SCHEMA” and “CREATE DATABASE” statements serve the same purpose.
2 SQL Server: Within SQL Server, the “CREATE SCHEMA” statement is employed to generate a new schema.
3 Oracle Database: In Oracle Database, the “CREATE USER” statement is used to create a new schema. In
Oracle, a schema is automatically generated with every database user. The “CREATE SCHEMA” statement,
however, doesn’t create a new schema. Instead, it populates the existing schema with tables and views,
facilitating access to these objects without necessitating multiple SQL statements for separate transactions.

63

CITS : IT&ITES - Computer software application - Lesson 18 - 36