1
Optimizing database design
Student's name
Institution
Course
Professor
Date
2
Introduction
When practising the normalization process for the database design, several factors must
be considered to produce the best set of normalized tables. Two significant issues are functional
dependencies and data redundancy. This section provides a breakdown of each factor and an
example of how it contributes to the removal of data redundancy.
Factor 1: Functional Dependencies
Functional dependency is a constraint elicited when a specific attribute determines
another attribute in a database table. Determining functional dependencies is essential for
normalization because that is how one determines the tables required and their columns to avoid
a subpar storage system that entices redundancy (Zhang et al., 2020).
Example: Suppose a university database has only one table where students' data, courses they
are enrolled in, and professors are stored.
Students ID Students
Name
Course ID Course
Name
Professors
ID
Professors
Name
101 Alice CSE101 Databases P001 Dr. Smith
102 Bob CSE101 Databases P001 Dr. Smith
101 Alice CSE102 Algorithms P002 Dr. Jones
In this table, Course ID determines Course Name, and Professor ID determines Professor
Name. We should normalize the table by decomposing it based on these functional dependencies
to eliminate redundancy.
Normalized Tables:
3
Students Table:
Student ID Student Name
101 Alice
102 Bob
Courses Table:
Course ID Course Name
CSE101 Databases
CSE102 Algorithms
Professors Table:
Professor ID Professor Name
P001 Dr. Smith
P002 Dr. Jones
Enrollments Table:
Student ID Course ID Professor ID
101 CSE101 P001
102 CSE101 P001
101 CSE102 P002
We eliminate redundancy by repeating the Course Name and Professor Name multiple times and
breaking down the original table into smaller, related tables.
Factor 2: Data Redundancy
Data redundancy occurs when the same data is stored in multiple places within a
database. Reducing data redundancy is essential to ensure data integrity and minimize storage
requirements.
Example: Consider a company database with a single table storing team member information
and their respective department details:
Team member ID Team Member Department ID Department Name
4
Name
201 John D001 Sales
202 Jane D001 Sales
203 Mike D002 Marketing
In this table, Department Name is repeated for each team member in the same department. To
reduce redundancy, we normalize the table by creating separate tables for employees and
departments.
Normalized Tables:
Employees Table:
Team member ID Employee Name Department ID
201 John D001
202 Jane D001
203 Mike D002
Departments Table:
Department ID Department Name
D001 Sales
D002 Marketing
By separating the department information into its own table, we ensure that the department name
is stored only once, reducing redundancy and the potential for inconsistencies.
Conclusion
Thus, depending on functional dependencies and data redundancies during
normalization, we can create a database with low redundancy and high data integrity. Functional
dependencies assist in noting the associations among the attributes, resulting in a better-
5
organized schema (Martins & Ning, 2021). MinimizingMinimization data duplication is
essential because it allows particular data to be stored in only one location, thus saving storage
space and conflicting data. It is possible to design an optimal structure for the database by
analyzing and normalizing it.
6
References
Martins, J. R., & Ning, A. (2021). Engineering design optimization. Cambridge University Press.
Zhang, J., Petersen, S. D., Radivojevic, T., Ramirez, A., Pérez-Manríquez, A., Abeliuk, E., ... &
Jensen, M. K. (2020). Combining mechanistic and machine learning models for
predictive engineering and optimization of tryptophan metabolism. Nature
communications, 11(1), 4880.
