Designing Database

To create a database, you must understand the relationships among the data, the type of data that will be maintained in the database, how the data will be used, and how the organization will need to change to manage data from a company- wide perspective. Designing a database requires an understanding of the business functions you want to model. It also requires an understanding of the database concepts and features that you want to use to represent those business functions. Make sure that you accurately design the database to model the business, because it can be time-consuming to significantly change the design of

a database after you implement it. A well-designed database also performs better.

The database requires both a conceptual design and a physical design. The conceptual, or logical, design of a database is an abstract model of the database from a business perspective, whereas the physical design shows how the database is actually arranged on direct-access storage devices.

The conceptual database design describes how the data elements in the database are to be grouped. The design process identifies relationships among data elements and the most efficient way of grouping data elements together to meet business information requirements. The process also identifies redundant data elements and the groupings of data elements required for specific application programs. Groups of data are organized, refined, and streamlined until  an overall logical view of the relationships among all the data in the database emerges.

The conceptual database design deals with two important concepts:

• Normalization and
• Entity relationship diagram

1)         Normalization

“The process of decomposing unsatisfactory "bad" relations by breaking up their attributes into smaller relations is called normalization”

While designing a database out of an entity–relationship model, the main problem existing in that raw database is redundancy. Redundancy is storing the same data item in more one place. A redundancy creates several problems like the following:

• Extra storage space: storing the same data in many places takes large amount of disk
• Entering same data more than once during data
• Deleting data from more than one place during
• Modifying data in more than one
• Anomalies may occur in the database if insertion, deletion, modification etc are no done properly. It creates inconsistency and unreliability in the

To solve this problem, the raw database needs to be normalized. This is a step by step process of removing different kinds of redundancy and anomaly at each

step. At each step a specific rule is followed to remove specific kind of impurity in order to give the database a slim and clean look. The process of reducing data redundancy and removing database modification anomaly in a relational database is called normalization.

In brief the process of creating small, stable, yet flexible and adaptive data structures from complex groups of data is called normalization.

Example: Let‘s take a relation that is in un-normalized form as, Student

Since in this relation multi-valued attribute exist thus this relation is not in normalized form. Now converting this relation into normal form by decomposing this relation into two relations as,

Student                                                                 

Phone

Fig: Relations in Normalized                                           form

Example 2: Employee-Department

In the above relation {Emp-Id, Dept-No} is the primary key. Emp-Name, Emp- Salary and Dept-Name all depend upon {Emp-Id, Dept-No}. Again Emp- IdàEmp-Name, Emp-IdàEmp-Salary and Dept-NoàDept-Name, thus  there also occur partial dependency. Due to which this relation is not in 2 NF.

Now converting this relation into 2 NF by decomposing this relation into three relations as,

Fig: Relations in 2 NF

2)           Entity relationship diagram

An E-R diagram is a specialized graphical tool that demonstrates the interrelationships among various entities of a database. It is used to represent the overall logical structure of the database. While designing E-R diagrams, the emphasis is on the schema of the database and not on the instances. This is because the schema of the database is changed rarely; however, the instances in the entity and relationship sets change frequently. Thus, E-R diagrams are more useful in designing the database. E-R diagram focuses high level database design and hides low level details of database representation therefore it can be used to communicate with users of the system while collecting information.

Example: