Functional Dependencies and Normalization Forms in Database Design

Verified

Added on 2024/07/01

AI Summary

This document explores the concept of functional dependencies in database design. It delves into different types of dependencies, including full, partial, and transitive dependencies. The document then explains how normalization forms (1NF, 2NF, and 3NF) are used to eliminate data redundancy and improve database efficiency. It provides a practical example of a database schema and demonstrates how to apply normalization principles to achieve a well-structured and optimized database. The document also discusses relational schema and referential integrity constraints, highlighting the importance of maintaining data consistency and relationships between tables. Finally, an Entity Relationship Diagram (ERD) is presented to visually represent the relationships between entities in the normalized database.

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.

Functional Dependencies
Functional dependencies in a schema are defined by when a single attribute represents a group of
attributes completely. These dependencies can be of many types that are a full functional
dependency, partial dependency, and transitive dependency. To represent and remove data
redundancy in the schema, normalization forms are used. These normalization forms are
represented as follows:
1NF – First Normal Form
This normal form represents all types of dependency present in the schema.
1NF (Meeting_id, User_id, Meeting_Date, Meeting_Time, Meeting_Venue, Event_ID, Rating,
Person_ID, Status)
Partial dependencies of the given schema:
Table 1: Meeting (Meeting_id, Meeting_Date, Meeting_Time, Meeting_Venue, Event_ID)
Table 2: Person (Person_ID, Status)
Table 3: Event (Event_ID, User_ID, Rating)

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.

2 NF – Second Normal Form
In this normal form, all the partial dependencies are converted into full functional dependencies.
Table 1: Meeting (Meeting_id, Meeting_Date, Meeting_Time, Meeting_Venue, Event_ID)
Table 2: Person (Person_ID, Status)
Table 3: Event (Event_ID, User_ID, Rating)

3 NF – Third Normal Form
After removing the partial dependency from the schema, a transitive dependency is removed in
the third normal form. And since this table does not have any transitive dependencies hence; 2NF
is 3NF.
Table 1: Meeting (Meeting_id, Meeting_Date, Meeting_Time, Meeting_Venue, Event_ID)
Table 2: Person (Person_ID, Status)
Table 3: Event (Event_ID, User_ID, Rating)

Meeting ID Meeting Date Meeting Time Meeting Venue
1 24/1/2018 11:40 am Library
2 25/1/2018 10:30 am Trail St
3 26/1/2018 9:00 am Blessed Bean
Event ID User ID Rating
1 IA 3
1 DD 4
1 KC 2
2 IA 5
2 DD 4
2 KC 2
3 AK 3
3 DD 3
3 IA 2
Person ID Status
KC Yes
YA No
DD Yes
IA Yes
AK Yes

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.

Relational Schema & Referential Integrity Constraints
Relational Schema represents the connection of the tables of 3NF with each other
Table 1: Meeting (Meeting_id, Meeting_Date, Meeting_Time, Meeting_Venue, Event_ID)
Primary Key: Meeting_id
Foreign Key: No foreign key
Table 2: Person (Person_ID, Status)
Primary Key: Person_ID
Foreign Key: No foreign key

Table 3: Event (Event_ID, User_ID, Rating)
Primary Key: Event_ID, User_ID
Foreign Key: Meeting_id

ERD
Entity relationship diagram of its third normal form is given as follows:
The ERD has a relationship of one-to-many and many-to-one. Each relationship shows the
cardinality of 1,1 and 1, N.

1 out of 7

+13062052269

info@desklib.com

Functional Dependencies and Normalization Forms in Database Design

Contribute Materials

Secure Best Marks with AI Grader

Secure Best Marks with AI Grader

Related Documents

Normalization: A Comprehensive Analysis of Database Design Principles

Database Management System - Desklib

Database Management System | Report | Assignment

Database Concepts and Schema Design