What is a database design?

A database design is the process of data organization based on a database model. The process deals with identifying what data should be stored in a database and how data elements relate to each other.

Database and related terminologies

A database is a systematic collection of data or information. The data in the database is stored and accessed electronically using a computer. It makes data handling easier. Users use database management systems (DBMS) applications or software to analyze and access the data.

Before beginning with the database designing process, it is essential to understand the following key terms associated with this concept.

• Data models: A data model is a conceptual model that deals with the organization of data elements and how they relate to one another.
• Database models: A database model is a data model that specifies how a database will be stored, organized, and manipulated. One of the commonly used database models is the relational database model that arranges data in a tabular format.
• Primary key: A primary key is a column that identifies each record uniquely. It can never be null. For example, in a student database, the student ID column can be assigned as a primary key of the database.
• Foreign key: It is a set of attributes that refer to the primary key of another table. They need not have unique values.

Database design phases

A typical database design process involves the following stages:

• Requirement gathering and analysis
• Conceptual database design
• Logical schema design
• Physical schema design
• Optimization and refining of database

Requirement gathering and analysis

In this stage, the purpose of the database is defined in detail. All the necessary information is collected and documented by the database designer. After gathering the information, constraints, relationships, and attributes of the data are identified.

This process is usually done by conducting interviews, surveys, meetings, research, and prototyping. The designer is responsible for cutting out the unnecessary and redundant information obtained during this phase before entering the next stage.

Conceptual database design

The next step after gathering the requirements is to create a conceptual database design. The main objective at this moment is to create a database that is independent of software and physical details. A conceptual data model with data entities, relationships, attributes, and constraints is obtained at the end of this phase. All the elements required in the database should be defined in this model.

In other words, the conceptual design should define the entity and relationships, attributes, primary keys, and integrity constraints of the database. After completion of this phase, the database is represented in the form of an entity-relationship model (ER model).

Logical schema design

Logical schema design involves arranging and mapping data into DBMS tables. All the entities, attributes, and relationships shown in the ER model are mapped into DBMS tables. Data normalization techniques are used to normalize the relationships.

Physical schema design

Physical schema design involves the physical implementation of the database. It deals with all the technical aspects such as data storage, data element specification, indexing, data security, attribute definitions, usage type, processing time, and performance measurements. This phase is crucial since it affects the performance of the entire database. Physical database designers should know how the computer manages and operates the DBMS.

Optimization and refining of database

Finally, the database design needs to be analyzed to detect errors. If any problem is identified, the design should be optimized.

Features of a good database design

A good database design fulfills the following criteria:

• It should be correct and have complete information. If a database is incorrect or incomplete, the information pulled out from the database will lack accuracy and completeness.
• The database should divide the information into subject-based data tables to avoid redundancy.
• It should fulfill all the data processing requirements.
• It should have a primary key.
• It should not have multi-valued fields.

Normalization

Normalization is a database design technique used for data optimization. Data redundancy, and insertion, deletion, and update anomalies are avoided through normalization. Normalization divides large tables into smaller ones and connects them through relationships to eliminate redundant data.

Database designers use the normalization process to design databases. There are five main normalization forms:

1. First normal form: This form states that every cell in the database table should have only one value, and the value should be unique.
2. Second normal form: This normal form states that the table should be in the first normal form, and the primary key should not be functionally dependent on any subset of the candidate key relation.
3. Third normal form: A table is in third normal form if it is in second normal form and has no transitive dependencies. A transitive dependency is a dependency in which one attribute is functionally dependent on a non-primary key attribute.

In most cases, databases are created by normalizing data up to the third normal form. However, there are other normal forms like Boyce-Codman normal form (BCNF), fourth normal form (4NF), and fifth normal form (5NF), which are used to normalize databases.

Entity-relationship model

The ER model is a common technique used to graphically design databases. An entity is a thing or an object which has a physical or logical existence. An entity-relationship diagram is built of entities and represents the relationships between entities.

In database design concepts, entities can have the following three types of relationships:

1. One-to-one relationships: In one-to-one relationships, a record in one table relates to only one record in the other table. This type of relationship creates equally dependent tables. For example, in a student database, each student can be linked to only one student ID.
2. One-to-many relationships: In one-to-many relationships, a record in one table can be associated with multiple records in another table.
3. Many-to-many relationships: In many-to-many relationships, multiple records of one table can associate with multiple records of the second table.

Context and applications

The topic is an essential concept studied by students in courses like:

• Bachelors in Technology (Computer Science)
• Masters in Technology (Computer Science)
• Masters in Science in Data Analytics

Practice problems

Q1. Which of these is a type of relationship?

1. One-to-one relationship
2. Multi-to-DBMS relationship
3. DBMS-to-DBMS relationship
4. Logical-to-conceptual relationship

Answer: Option a

Explanation: One-to-one, many-to-one, and many-to-many are the three types of relationships.

Q2. Which is the first stage in the database design process?

1. Data modeling
2. Requirements gathering
3. Finding primary key
4. Identifying database management system security options

Answer: Option b

Explanation: The first phase in the database design process involves requirement gathering and analysis.

Q3. What is normalization used for in a database design?

1. Data optimization
2. Data modeling
3. Data relationships
4. Design databases

Answer: Option a

Explanation: Normalization is used in database designing to optimize data.

4. In which type of relationship does a record of one table relate to only one record of the other table?

1. One-to-one relationship
2. One-to-many relationship
3. Many-to-many relationship
4. Logical-to-one relationship

Answer: Option a

Explanation: In a one-to-one relationship, a record of one table is linked to only one record of the second table.

Q5. Which of these is a normalization form?

1. Seven normal form
2. Conceptual data model form
3. Entity-relationship normal form
4. Second normal form

Answer: Option d

Explanation: Second normal form is one of the normalization forms in a database.

Common Mistakes

The concepts database model and data model sound similar, but they are different terms in computer science. Students should not get confused by these terms and avoid interchanging them.

Related Concepts

• Relational database
• Entity-attribute-value model
• Denormalization
• Concept map
• Three schema approach