Database Design



Database Design Process

The first step of database design is requirement collection and analysis. During this step, the database designers interview prospective database users to understand and document their data requirements. The result of this step is a concisely written set of user's requirements.


Once all the requirements have been collected and analyzed 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 which consists primarily of defining the data elements to be included in the database, the relationships that exist between them, and the value constraints that apply.


The conceptual schema is a concise description of the data requirements of the users and includes detailed descriptions of the entity types, relationships, and constraints. These are expressed using the concepts provided by the high-level data model. Because these concepts do not include implementation details, they are usually easier to understand and used to communicate with nontechnical users. The high-level conceptual schema can also be used as a reference to ensure that all user's data requirements are met and that the requirements do not include conflicts. This approach enables the database designers to concentrate on specifying the data, without being concerned with storage details.


The next step in database design is the actual implementation of the database, using a commercial DBMS. Most current commercial DBMSs use an implementation data model. Such as the relational or object database model. So the conceptual schema is transformed from the high-level data model into the implementation data model. This step is called the logical design or data model mapping. The ER diagram is converted into set relations/tables in the relational data model. Here the logical structures are defined to represent data. The outcome of logical database design is a set of well-structured relations that has a logical relationship among them.


Finally, the last step is the physical design phase, during which the internal storage structures, access paths, and file organizations for the database files are specified. In parallel with these activities, application programs are designed and implemented as database transactions corresponding to the high-level transaction specifications. Physical database design determines the physical structure of the database and includes such decisions as what access method will be used to retrieve data, and what indexes will be built to improve the performance of the system.


Database design decisions are documented in the data dictionary. The DBA control the contents of the data dictionary and records there as metadata the names of data elements, files, screens, reports forms, and life. The manipulation is very carefully controlled by the DBA since the data in the data dictionary are vital to the proper functioning of the database system as a whole.


ER Concepts and Terminology

An entity-relationship model is a detailed conceptual representation of the data for an organization or for a business area.


The ER model is expressed in terms of entities in the business environment, the relationships among those entities and the attributes (properties) of both entities and their relationships.


An ER model is normally expressed as an ER diagram, which is the graphical representation of an ER model.


Terminology Description
Entity a person place object event or  concept in the  user environment about which day organization wishes to maintained data
Entity type a collection or set of entities that share common properties or characteristics
Entity Instance a single occurrence of an entity type
Attribute attributes is a property or a characteristic of an entity type that is of interest to the organization


Type of Attributes

Simple Attribute

Value of the attribute cannot be broken down into the component parts.


Composite Attribute

Value of the attribute can be subdivided into component parts.


Single-Valued Attribute

An attribute that takes only one value for a given entity instance.


Multi-valued Attribute

An attribute that may take more than one value for a given entity instance.


Stored Attribute

An attribute whose values are physically stored in the database.


Derived Attribute

An attribute whose values can be calculated from related stored attribute values.



An identifier is a set of an attribute which uniquely identifies each entity instance. There are two types of Identifiers.


Simple Identifier

Identifier is simple attributes.


Composite Identifier

Identifier is composite attribute.



The relationship is a meaningful association among entities.


Relationship Type

Meaningful associations among entity types.

Eg: Student follows a class


Relationship Instance

An association between entity instance where each relationship instance includes exactly one entity from each participating type.

Eg: John is following BIT 



What is Next?

The next section explains about Database Security

About Tec News

Tec News is optimized for learning Web Development, Programming, Database System and Tutorials, Articles, References for your development. This web help developers, learners learn these technologies easy and free.

Contact Us

If you have any suggestions or have any questions regarding our web please feel free to contact us.