How to Design a Database?

Databases exist because of the need to change data into information. Data are the raw and unprocessed facts. Information is obtained by processing the data into something useful.

images/coding/coding12.webp

Database Life Cycle

There are generally six stages of the database life cycle:

1. Analysis: The analysis phase is where the stakeholders are interviewed and any existing system is examined to identify problems, possibilities and constraints. The objectives and scope of the new system are determined.

2. Design: The design phase is where a conceptual design is created from the previously determined requirements, and a logical and physical design are created that will ready the database for implementation.

3. Implementation: The implementation phase is where the database management system (DBMS) is installed, the databases are created, and the data are loaded or imported.

4. Testing: The testing phase is where the database is tested and fine-tuned, usually in conjunction with the associated applications.

5. Operation: The operation phase is where the database is working normally, producing information for its users.

6. Maintenance: The maintenance phase is where changes are made to the database in response to new requirements.

Entity-Relationship Model

Entities are people, places, or things you want to keep information about. For example, a library management system may have the book, library and borrower entities.

Relationships: Entities are related in certain ways. For example, a borrower may belong to a library and can take out books. A book can be found in a particular library. There are a number of possible relationships:

  • Mandatory
  • Optional
  • One-to-one (1:1)
  • One-to-many (1:M)
  • Many-to-many (M:N)

An entity can also have a relationship with itself. Such an entity is called a recursive entity. Take a person entity. If you're interested in storing data about which people are brothers, you will have an "is brother to" relationship. In this case, the relationship is a M:N relationship.

The term cardinality refers to the specific number of instances possible for a relationship. Cardinality limits list the minimum and maximum possible occurrences of the associated entity.

An entity-relationship diagram models how the entities relate to each other. These entities become the database tables. 

  1. The first step in developing the diagram is to identify all the entities in the system. In the initial stage, it is not necessary to identify the attributes.
  2. Once the entities are listed, relationships between these entities are identified and modelled according to their type: one-to-many, optional and so on.
  3. The next stage is to replace many-to-many relationships with two one-to-many relationships.

A DBMS cannot directly implement many-to-many relationships, so they are decomposed into two smaller relationships. To achieve this, you have to create an intersection, or composite entity type. You can name them according to the two entities being intersected. For example, you can intersect the many-to-many relationship between student and course by a student-course entity.

The same applies even if the entity is recursive.