1. Data model

A data model is a clear description of the data concepts, their relationships and various data constraints that together make up the content of the database. There exist different types of data models.

  • Conceptual data model: This type is used as a communication tool to make sure that data requirements are adequately capped and It should be implementation independent, user friendly and close to how business users perceive the data.

Ex : (E)ER, object-oriented models

  • Logical data model : it is a kind of translation or mapping of the conceptual Data models. The concepts may be understood by business users but are not too far removed from physical data

Ex : Hierarchical model, network model, relational model, object oriented model.

  • Physical data model : this low level concept clearly describes which data will be stored where, which format, …

2. Schemas and instances

In any data model it is important to distinguish between the description of the data and the data itself.

On the one hand, there is a database schema which is stored in the catalog. This schema is a description of a database, which is specified during database design. It is not expected to change too frequently. On the other hand there is the database state which corresponds to the data in the database at a particular moment, also called the current set of instances. It changes on ongoing basis.

3. 

3. The three-schema architecture

The three-schema architecture is an essential element of any database environment or application. This figure illustrates this architecture.

Let’s start with the middle layer : here we have the conceptual data scheme. As mentioned before, it focuses on data concepts, their relationships and data constraints without bothering about the actual DBMS implementation. It should be an user friendly and transparent data model. It would be matched to a logical data model based on a close collaboration between DB designer and users. The middle layer includes both conceptual as logical scheme.

The top level represents the external scheme. Here, views can be defined which offers a window on a carefully selected part of conceptual model. The external views will be tailored to what’s a data need of application. A view conserves one or more applications. Ex : view offering only student information or only building information to capacity planning application.

  • External view: Each external schema describes the part of the database that a particular user group is interested in and hides the rest of database from that user It allows controlling the data access.
  • Conceptual scheme: Specifies data concepts, characteristics, relationships, integrity rules and behavior.
  • Internal scheme: specifies how data is stored or organized Ex: indexes, access paths, etc.
  • Ideally, changes shouldn’t have impacts on the other parts or at least, changes in one layer should have minimal impact on the other

This three-schema architecture has advantages in terms of efficiency, maintainability, security and performance.

4. Data dictionary (catalog)

The data dictionary or catalog constitutes the heart of the database system. It contains the data definition or Metadata of database application. More specifically it stores the definitions of the external, conceptual and physical scheme. It also synchronizes these three schemes to make sure consistency is guaranteed.

5. DBMS languages

  • Every DBMS comes with one or more data base
    • Data Definition Language (DDL) : This language is used by the database administrator to define the database’s logical, internal and external schemas. It is stored in the
    • Data Manipulation Language (DML) : This language is used to retrieve, insert, delete and modify DML statements can be embedded in a general-purpose programming language or entered interactively through a front-end querying tool.
  • For relational database systems, SQL is both the DDL and DML. It can be used interactively (= interactive SQL) or embedded in a programming language (= embedded SQL).

6. Database users

For a small personal database, such as the list of addresses, one person typically defines, constructs, and manipulates the database, and there is no sharing. However, in large organizations, many people are involved in the design, use and maintenance of a large database with hundreds of users.

  • Database Designer: He is responsible for identifying the data to be stored in the database and for choosing appropriate structures to represent and store this data. Therefore he is responsible for designing the conceptual schema. These tasks are mostly undertaken before the database is actually implemented and populated with He closely interacts with all prospective database users to make sure to understand their requirements and to create a design that meets these requirements.
  • Database Administrator: He is responsible for administering the resources. The DBA is responsible for authorizing access to the database, coordinating and monitoring its use and acquiring software and hardware resources as Moreover, he designs the external and physical schema. He also set up the database infrastructure and monitors its performance by inspecting KPI indicators (e.g. response time and storage consumed).
  • Application developer: Responsible for developing the database applications in a programming language such as He will provide the data requirements which will then be translated by the DBA to few definitions.
  • Business users : Will run the applications to perform specific data operations. He can also query database using interactive facilities.
  • DBMS vendors : offers the DBMS software Ex: Oracle, Microsoft and IBM.