Page 76 - CITS - Computer Software Application -TT
P. 76

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
   71   72   73   74   75   76   77   78   79   80   81