Database Design in Database Management System

Database design provides a means to represent real world entities in a form that can be processed by the  computer. Database models present a process of abstracting real world entities into computer  representations. 

To develop a good design, one has to understand the meaning of information and the intended use of  stored representation within the computer system. Once we develop the understanding and identify the  use of information in the application, we can determine how much and what kind of information we  require. 

After determination of application’s information requirement, it will be clear that which data entities  represent information redundancies, entities that are critical, useful and are not related to the  applications. 

It is important to collect and analyze the static and dynamic information available about real world  application before starting the database design. 

For evolving a good database design, it is important that one uses a model, a database design model. The  database design models have following benefits. 

They provide a means to represent real-world objects in computer usable form 

Steps of Database Design 

1. Requirement analysis 

To determine how to construct the DBMS for an application, the designer must first determine  the scope of the problem requiring the database system. 

Requirement analysis are used to define the scope of the requirement of an application It includes 

  • Defining the human factors of the application 
  • Defining the application’s functionality 
  • Defining all the information managed and used by the application 
  • Determining from where to where all interfaces to an application are derived 
  • Identifying all the resource requirements including hardware, software and other physical  resources. 
  • Deciding on the security requirements and mechanisms 
  • Defining the quality, reliability, performance and operational aspect of the application. 

2. Information Modeling 

The objective of information modeling is to identify the major entities that are fundamental in an  application and model them in the target database schema model 

The information collected during the requirement analysis stage forms the input for information  modeling. This information will enable the database designer to fully and correctly define the  major entities to be modeled in the database 

The attributes that define the entities of the application are grouped together according to the  data model used and stored for further reference. 

3. Design Constraints 

The database systems require certain controls and limits for it to truly represent the real-world system behavior. 

These limits or controls are called constraints in database parlance 

There are many kinds of database constraints as follows 

  1. Structural Constraint 
  2. Type Constraint 
  3. Range Constraint
  4. Relationship Constraint 
  5. Temporal Constraint 
  6. Structural Constraint 

The structure of the information within the database gives an idea about entities in the database. 

For example, simple data structures are represented using simple structures while complex data  structures will need advanced structures. 

Structural constraints are specified to force the placement of information into structures that best  matches the application 

a. Type constraints 

A type constraint limits the application to only one representation of information for an entity’s  attribute. 

For example, the database designer might want to limit the name attribute to a fixed length  character string, the age attribute to a number etc. Type constraints allow a limitation of the range  of information representations that an attribute can have. 

b. Range Constraints 

Range constraints can limit the values an attribute can take. It refers to the possible values that a  particular data item can have. Range constraints can be used to limit the value of a particular  attribute within a range. 

For example, We can specify that the employee numbers should be in the range 1000-9999. 

c. Relational constraints 

These constraints represent relationships on values between entities. For example, there could  be a relationship constraint between the entities Manager and Employee that the maximum  bonus of manager should not be greater than six times that of the employee 

d. Temporal Constraints 

These constraints indicate the time period for which some information is valid. For example, the  value of attribute sale tax or exercise duty is valid for a specific period. Once the period is over,  new values will come into effect.

Data Security in Database Management System

Database security involves protecting a database from unauthorized access, malicious destruction and  even any accidental loss or misuse. Due to the high value of data incorporate databases, there is strong  motivation for unauthorized users to gain access to it, for instance, competitors or dissatisfied employees 

The competitors may have strong motivation to access confidential information about product  development plans, cost-saving initiatives and customer profiles.  

Some may want to access information regarding unannounced financial results, business transactions and  even customers credit card numbers. They may not only steal the valuable information, in fact, if they  have access to the database, they may even destroy it and great havoc may occur. 

There are various ways how we can secure our system. The types of computer-based controls to threats  on computer systems range from physical controls to administrative policies and procedures. 

1. Authorization 

Authorization is the granting of a right or privilege that enables a subject to have legitimate access  to a system or a system’s object.

Usually, a user or subject can gain access to or a system through individual user accounts where  each user is given a unique identifier, which is used by the operating system to determine that  they have the authorization to do so. 

2. Access Control 

Access controls to a database system is based on the granting and revoking of privileges. A  privilege allows a user to create or access (that is read, write or modify) a database object or to  execute a DBMS utility. 

The DBMS keeps track of how these privileges are granted to users and possibly revoked, and  ensures that at all times only users with necessary privileges can access an object. 


A view is created by querying one or more of the base tables, producing a dynamic result table  for the user at the time of the request. The user may be allowed to access the view but not the  base tables which the view is based.

The view mechanism hides some parts of the database from  certain users and the user is not aware of the existence of any attributes or rows that are missing  from the view. 

4. Backup and recovery 

Backup is the process of periodically taking a copy of the database and log file to offline storage  media. Backup is very important for a DBMS to recover the database following a failure or  damage.  

5. Encryption 

Encryption is the process of encoding of the data using a special algorithm that renders the data  unreadable by any program without the decryption key.

Data encryption can be used to protect  highly sensitive data like customer credit card numbers or user password. Some DBMS products  include encryption routines that would automatically encode the sensitive data when they are  stored or transmitted over communication channels 

6. RAID (Redundant Array of Independent Disks) 

The DBMS should continue to operate even though if one of the hardware components fails. The  hardware that the DBMS is running on must be fault-tolerant where the DBMS should continue  operating and processing even if there is hardware failure.