Steps! one should consider while Designing a Database

Designing Steps

Step 1

Mission statement and Mission objectives

Mission statement

— …..The purpose of the database….. —

A database is created for a specific purpose, whether it’s to solve a particular business problem, to manage the daily transactions of a business or organization, or to be used as part of an information system.So we need to identify the purpose of your database and define it within a mission statement , which will help ensure that you develop an appropriate database structure and that you collect the data necessary to support the intended purpose of the database.

Mission Objective

— …..General tasks the user can perform against the data reside in the database….. —

These are statements that represent the general tasks your users can perform against the data in the database. You use these objectives to support your mission statement and to help you determine various aspects of the database structure.

Step 2

Analyze current database if one exists

• Whatever the database type or condition, however, analyzing it will yield valuable information about the way your organization is currently using and managing its data. The analysis also involves reviewing the way your organization currently collects and presents the data.
• Examine how your organization uses paper forms and reports or desktop applications to collect data and present data. Finally, you take into account how your organization uses its data on the Internet and review any web-based applications that work with the database.
• Another important approach is timely and practical interview with users and as well as organization. Interviews with users and management helps to identify how they interact with the database on a daily basis.
• Then use the information you’ve gathered from the analysis and the interviews to compile an initial list of fields.Then refine this list by removing all calculated fields and placing them on their own list.
• The refined list constitutes your organization’s fundamental data requirements and provides a starting point for the design of a new database.

Step 3

Creating the data structure

• It is the third phase in the database design process. Here we define tables and fields, establish keys, and define field specifications for every field.
• Tables are the first structures we define in the database. Her we determine the various subjects that the tables will represent from the mission objectives that we compiled during the first phase of the design process and the data requirements we have gathered during the second phase.
• Establish these subjects as tables and associate them with fields from the field list that we have compiled during the second phase of the design process.
• Then we need to start the filtering process, First we need to ensure that each table must define a specific subject and there should not be any duplicate fields in that table.
• Secondly we need to review the fields within each table and refine all multipart or multivalued fields in the table so that they each store only a single value, and we can move or delete fields that do not represent distinct characteristics of the subject the table represents.
• Then we need establish the appropriate keys for each table. One important task is to ensure that each table has a properly defined primary key which uniquely identifies each record within a table.
• Finaly we need to establish field specifications for each field in the database after conducting interviews with users and management .

Step 4

Determine and establishing table relationship

• It is the fourth phase of the database design process which involves establishing table relationships. Here we need to conduct interviews with users and management once again, identify relationships, identify relationship characteristics, and establish relationship-level integrity.
• This interview process is a prudent exercise because they can assist you in identifying relationships among the data because one cannot possibly be familiar with every aspect of the data your organization uses, so leveraging whatever knowledge they have about the data they use will be very beneficial for you.
• After you’ve identified the relationships, you need to establish a logical connection between the tables in each relationship with a primary key or with a linking table.
• Then you have to determine the type of participation and degree of participation for the tables in each relationship.
• The participation characteristics based on specific business rules and the nature of data stored in tables.

Step 5

Determining and defining business rule

• In the fifth phase of the database design process you need to determine and define business rules.
• Here we need to again conduct interviews, identify limitations on various aspects of the database, establish business rules, and define and implement validation tables.
• You can determine a set of limitations and requirements that you must build into the database by following the manner in which your organization views and uses its data.
• Interviews with users and management will help you identify the specific constraints that you will impose on the data, data structures, or relationships, then we can document these specifications as business rules.
• Then you define and implement validation tables as necessary to support certain business rules.
• Determining and establishing business rules is an ongoing, iterative process, and you must be constantly diligent if you are going to maintain this level of integrity properly.

Step 6

Determining and defining views

• Here during the sixth phase of the design process involves determining and defining views.Once again you’ll conduct interviews and identify various ways of working with the data, and establish the views.
• You need to define different types of views that you need to build in the database by interviewing users and management and determining how they work with their respective data.
• For example, many users require detailed information to perform their work, where as others need only summary information to help them make strategic decisions for the organization , each group of users must access information in very specific ways, and you can use views to accommodate these situations.

Step 7

Reviewing data integrity

• It is the seventh and final phase in the database design process.This involves reviewing the final database structure for data integrity.
• At First, you need to review each table to ensure that it meets the criteria of a properly designed table and check every field within each table for proper structure. Then you can resolve any inconsistencies or problems you may encounter and review the structures once more and verify that your tables have table-level integrity.
• Next you need to review and check the field specifications for each field, you can make necessary refinements to the fields and then check field-level integrity. This review assures the field-level integrity that you have identified and established earlier in the database design process.
• Then, you have to review the validity of each relationship, confirm the relationship type, confirm the participation characteristics for each table within the relationship and review relationship integrity to ensure that there are matching values between shared fields and that no problems occur when inserting, updating, or deleting data in either of the tables within the relationship.
• Finally, you review the business rules that you identified earlier in the database design process and confirm the constraints you’ve placed on various aspects of the database.