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
- Structural Constraint
- Type Constraint
- Range Constraint
- Relationship Constraint
- Temporal Constraint
- 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.
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.
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.