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

Sid

Sname

Address

Phone_No

1

Bishnu

Kalanki

9849145464, 9813335467

2

Ramhari

Balkhu

9841882345, 099392844

3

Geeta

Kirtipur

9848334898,

4

Dipika

Pokhara

9849283847

5

Monika

Ratopool

9840084732, 9803267499

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                                                                 

 

Sid

Sname

Address

1

Bishnu

Kalanki

2

Ramhari

Balkhu

3

Geeta

Kirtipur

4

Dipika

Pokhara

5

Monika

Ratopoo

l

                           

Phone

                                                                            

Sid

Phone_No

1

9849145464

1

9813335467

2

9841882345

2

099392844

3

9848334898

4

9849283847

5

9840084732

5

9803267499

 

 

Fig: Relations in Normalized                                           form

 

Example 2: Employee-Department

 

Emp-Id

Emp-Name

Emp-Salary

Dept-No

Dept-Name

1

Bhupi

40000

D1

BBA

1

Bhupi

40000

D2

CSIT

2

Bindu

30000

D3

BBS

 

3

Arjun

60000

D1

CSIT

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: