DATABASE DESIGN

Database design is a process of modeling a database. While designing a database, first you need to identify the tables to be created, then the columns to be created in those tables, the information to be stored in the tables, and finally how to link each table with one another. Mainly, there are two tasks involved in designing a database: entity-relationship model and normalizing a database. In this section, you will learn about entity-relationship modeling, relational database, and normalization.

Entity-Relationship Model

The Entity-Relationship (ER) modeling is a database modeling method that is used to produce a conceptual view of the data model of the system or the relational database. The graphical representation of the ER model is called the Entity-Relationship (ER) diagram. These diagrams represent the interrelationships between entities in a database. The ER diagrams use symbols that represent three types of information: boxes are commonly used to represent entities; diamonds are normally used to represent attributes; and lines are used to represent the relationship between entities.

Entity

An entity is an object or concept about which you want to store information. For example, the entity employee can be represented as given below:

Attribute

An attribute is associated with an entity. In the following example, the number and name of an employee are the attributes of that employee.

Relationships

Relationships indicate how two entities share information in a database structure. Consider the following example, which shows the relationship between two entities, Employee and Department.

The three relationships that can exist between entities are one-to-one, one-to-many, and many-to-many.

One-to-one relationship between two tables occurs when for one record in the first table, there is exactly one corresponding record in the other related table. Figure below shows the one-to-one relationship.

One-to-one relationship

One-to-many relationship between two tables occurs when a record in the first table has linked records in the second table, but each record in the second table may have only one corresponding record in the first table. Figure below shows the one-to-many relationship.

One-to-many relationship

Many-to-many relationship between any two tables occurs when each record in one table may have many linked records in the other table and vice-verse. Figure below shows the many-to-many relationship.

Many-to-many relationship

For example:

Following is a database that contains information about the residents of a city. The ER diagram shown in Figure below contains two entities: PEOPLE and CITIES. There is a one-to-one relationship Live In between PEOPLE and CITIES. PEOPLE have names and CITIES have populations, as shown in below figure.

ER diagram showing the one-to-one relationship between PEOPLE