A data model is a collection of conceptual tools used for describing data, data relationships, data semantics, and consistency constraints. The most widely used data model introduced for designing a database system is relational data model.
The relational data model uses a collection of tables to represent both data and the relationships among those data. A relational database is a database system that works on the principle defined in relational model.
A relational database consists of a collection of tables, each of which is assigned a unique name. Each table contains records of a particular entity and hence a table is also called as entity set. Each table has multiple columns, and each column has a unique name. The columns of the table correspond to the attributes of the record-type.
A row in the table represents a relationship among a set of values that belong to a particular entity. Since a table is a collection of such relationships, there is a close correspondence between the concept of table and the mathematical concept of relation, from which the realational data model takes its name.
The Entity-Relationship (E-R) Model
E-R Model is a high-level conceptual data model, which is widely used in database design. The E-R data model is based on the perception of the real world that consists of a collection of basic objects called entities, and of relationships among those objects.
An entity is a "thing" or "object" in the real world that is distinguishable from other objects. For e.g., each person is an entity, which is tangible and bank accounts are entities that are intangible. Entities are described in a database by a set of attributes.
A relationship is an association among several entities. For e.g., a depositor relationship associates a customer with each account she/he has. In addition to entities and relationships, the E-R model represents certain constraints to which the contents of the database must conform.
As an illustration, the above E-R Diagram represents the part of a database used in banking system with two entities namely customer and account. Each of these entities have attributes of its own. The attributes customer_id, customer_name, customer_street, customer_city are properties of a customer entity. Similarly, account_number and balance describe one particular account in a bank and hence they become the attributes of the account entity. The above E-R diagram also shows a relationship depositor between the entities - customer and account.
Database Design Process
The first step in database design is requirements collection and analysis. During this step, the database designer interviews prospective database users to understand and document their data requirements. The result of this step is a concisely written set of users' requirements. These requirements should be specified as detailed as possible.
In parallel with specifying the data requirements, it is useful to specify the functional requirements of the application that will operate on data to be stored in the database. These consist of user-defined operations (or transactions) that will be applied to the database, including both retrievals and updates.
Once the requirements have been collected and analysed, the next step is to create a conceptual schema for the database, using a high-level conceptual data model. This step is called conceptual design. Conceptual schema describes the data identified during analysis phase, using concepts provided in high-level data model such as entity types, relationships and constraints.
The higher-level data model such as E-R Model doesn't include implementation details, and hence they are easier to understand. It can be used as a communication tool to communicate the database design with the non-technical users. It can also be used as a reference to ensure that all user's data requirements are met and that the requirements do not conflict.
During or after the conceptual schema design, SQL Statements such as DDL and DML can be used to specify the high-level user queries and operations identified during functional analysis. This phase of design process is called application program design. This also serves to confirm that the conceptual schema meets all the identified functional requirements.
Logical and Physical Design
The next step in database design is the actual implementation of the database, using a commercial DBMS. In this stage, the conceptual schema is transferred from the high-level data model into the implementation data model. This step is called logical design or data model mapping; its result is a database schema in the implementation data model of the database.
The last step is the physical design phase, during which the internal storage structures, file organisations, indexes, access paths, and physical design parameters for the database files are specified. This physical design is done by DBMS with the help of stored data manager, a functional component of the Database Management System.
In parallel with these activities, application programs are designed and implemented as database transactions corresponding to the high-level transaction specifications.
