Skip to main content
Engineering LibreTexts

5.3: Schemas

  • Page ID
    92173
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)

    ( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\id}{\mathrm{id}}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\kernel}{\mathrm{null}\,}\)

    \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\)

    \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\)

    \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    \( \newcommand{\vectorA}[1]{\vec{#1}}      % arrow\)

    \( \newcommand{\vectorAt}[1]{\vec{\text{#1}}}      % arrow\)

    \( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vectorC}[1]{\textbf{#1}} \)

    \( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)

    \( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)

    \( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)

    \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    Schemas

    schema is an overall description of a database, and it is usually represented by the entity relationship diagram (ERD). There are many subschemas that represent external models and thus display external views of the data. Below is a list of items to consider during the design process of a database.

    • External schemas: there are multiple
    • Multiple subschemas: these display multiple external views of the data
    • Conceptual schema: there is only one. This schema includes data items, relationships and constraints, all represented in an ERD.
    • Physical schema: there is only one

    Logical and Physical Data Independence

    Data independence refers to the immunity of user applications to changes made in the definition and organization of data. Data abstractions expose only those items that are important or pertinent to the user. Complexity is hidden from the database user.

    Data independence and operation independence together form the feature of data abstraction. There are two types of data independence: logical and physical.

    Logical data independence

    A logical schema is a conceptual design of the database done on paper or a whiteboard, much like architectural drawings for a house. The ability to change the logical schema, without changing the external schema or user view,  is called logical data independence. For example, the addition or removal of new entities, attributes or relationships to this conceptual schema should be possible without having to change existing external schemas or rewrite existing application programs.  

    In other words, changes to the logical schema (e.g., alterations to the structure of the database like adding a column or other tables) should not affect the function of the application (external views).

    Physical data independence

    Physical data independence refers to the immunity of the internal model to changes in the physical model. The logical schema stays unchanged even though changes are made to file organization or storage structures, storage devices or indexing strategy.

    Physical data independence deals with hiding the details of the storage structure from user applications. The applications should not be involved with these issues, since there is no difference in the operation carried out against the data.

    Key Terms

    conceptual model: the logical structure of the entire database

    conceptual schema: another term for logical schema

    data independence: the immunity of user applications to changes made in the definition and organization of data

    data model:a collection of concepts or notations for describing data, data relationships, data semantics and data constraints

    data modelling: the first step in the process of database design

    database logical design:  defines a database in a data model of a specific database management system

    database physical design: defines the internal database storage structure, file organization or indexing techniques

    entity relationship diagram (ERD): a data model describing the database showing tables, attributes and relationships

    external model:  represents the user’s view of the database

    external schema: user view

    internal model: a representation of the database as seen by the DBMS

    logical data independence: the ability to change the logical schema without changing the external schema

    logical design: where you create all the tables, constraints, keys, rules, etc.

    logical schema: a conceptual design of the database done on paper or a whiteboard, much like architectural drawings for a house

    operating system (OS): manages the physical level of the physical model

    physical data independence: the immunity of the internal model to changes in the physical model

    physical model: the physical representation of the database

    schema: an overall description of a database

     

     

    Exercises

    1. Describe the purpose of a conceptual design.
    2. How is a conceptual design different from a logical design?
    3. What is an external model?
    4. What is a conceptual model?
    5. What is an internal model?
    6. What is a physical model?
    7. Which model does the database administrator work with?
    8. Which model does the end user work with?
    9. What is logical data independence?
    10. What is physical data independence?

    Also see Appendix A: University Registration Data Model Example


    5.3: Schemas is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?