PricingBlogAbout Us

Functional Dependencies, Normalization, and ERD in Database Design

Verified

Added on  2024/07/01

|7
|469
|202
Homework Assignment
AI Summary
This assignment solution provides a detailed explanation of functional dependencies and normalization in database design. It covers the transformation of a database schema from 1NF to 2NF and finally to 3NF by removing partial and transitive dependencies. The solution includes examples of tables in each normal form, along with a relational schema defining primary and foreign keys, and an entity-relationship diagram (ERD) illustrating the relationships between the tables. Desklib offers this and many other solved assignments for students.
Document Page
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)
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
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)
Document Page
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)

⊘ This is a preview!⊘

Do you want full access?

icon

Trusted by 1+ million students worldwide

Document Page
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
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
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
Document Page
Table 3: Event (Event_ID, User_ID, Rating)
Primary Key: Event_ID, User_ID
Foreign Key: Meeting_id

⊘ This is a preview!⊘

Do you want full access?

icon

Trusted by 1+ million students worldwide

Document Page
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.
chevron_up_icon
1 out of 7
circle_padding
hide_on_mobile
zoom_out_icon
logo.png

Your All-in-One AI-Powered Toolkit for Academic Success.

  +13062052269
info@desklib.com

Available 24*7 on WhatsApp / Email

[object Object]
Unlock your academic potential

Copyright © 2020–2025 A2Z Services. All Rights Reserved. Developed and managed by ZUCOL.