Database Normalization Report: Professor Database Case Study Analysis

This report provides a detailed analysis of database normalization, focusing on a professor database case study. The report begins by defining normalization and its importance, followed by an explanation of functional and multivalued dependencies, including examples. It then delves into the Third Normal Form (3NF) and its application to the case study. The document also includes an Entity-Relationship Diagram (ERD) using Crow's Foot notation to represent the database structure. The report references relevant literature and offers a complete solution for a database assignment, covering key concepts and practical applications within the field of database design and management.

Course Title and Code:
Student Code and Name:
Lecture Name:

Solution
Normalization refers to the formal process of assignment of attributes to their relevant entities. This
process is required in database design because:
- It manages and reduces data control redundancies.
- It assists in the elimination of data anomalies in which updates, deletions insertions destroy
integrity of the database.
- Normalization ensures that the database tables are logically correct.
QUESTION 1
A dependency refers to a constraint which applies or defines the relationship among attributes of a
table. It takes place in databases when data stored in same database table primarily determines other
data kept in the same relation (Coronel & Morris, 2016). Below is the dependency diagram for our given
table structure for sample professor records.
Question 2
A functional dependency can be described as the relationship between the 2 attributes of an entity
especially between the primary key and another non-primary key fields within an entity. For example,
for any relation table M, field F is functionally dependent on field N (typically the primary key), if for
every valid instance of N, the value of M uniquely identifies the value of N.
A multivalued dependency refers to a complete constraint among two sets of fields in a table. This
necessitates that particular tuples have to be presented in a table.
It is important to understand dependencies in order to perform normalization on a database.
Dependencies are built on the basis of the business rules which apply. They follow the below rules;
- The unique key constituents are underlined shaded in a different color and bolded.
- The arrows which are above the entities show all the required dependencies where the arrows
below the dependencies diagram will show the less required dependencies. The less desired
dependencies are not built on the basis of the primary key (Eckstein & Schultz, 2018).
Below are the multivalued dependencies from the provided case study
PROF_RANK DEPT_CODE
Ass.prof CHEM
Ass.prof CIS
Ass.prof ENG

DEP_NAME DEP_CODE
Computer information System CIS
Computer information Sys CHEM
QUESTION 3
3NF is a third step in normalization of database and it builds on the 1NF and 2NF. Third Normal form
dictates that for a relation to be in 3NF, it must be in second normal form and it requires that all
attributes reference in a referenced data which are not dependent on the unique have to be removed.
Below are the third normal form of our tables mentioned above.
Professor [emp_num, emp_name]
Professor [prof_rank, dep_code, dep_name, prof_office]
ERD
An ERD refers to a data modeling method which graphically illustrates the information systems’ relations
and the relationships among these relations. It is described as the conceptual representation model of
the data used to represent the table framework infrastructure. The ERD has been in existent for so many
years because is easy to discuss a database. The model is readily translated to tables. An ER model can
also be called Entity Relationship Schema Below is Crows Foot Notation ERD for our case study database.

(Captain, 2013)

References
Captain, F. A., 2013. Six-Step Relational Database Design: A Step by Step Approach to Relational. s.l.:
Fidel A Captain.
Coronel, C. & Morris, ., 2016. Database Systems: Design, Implementation, & Management. 3rd ed.
Boston: Cengage Learning.
Eckstein, J. & Schultz, . R., 2018. Introductory Relational Database Design for Business, with Microsoft
Access. 4th ed. Hoboken: John Wiley & Sons.