Database Normalization and Design: Relational Schema and 3NF Relations

This assignment solution addresses database normalization, focusing on the decomposition of a given relation into 3NF (Third Normal Form) relations. The solution begins with a dependency diagram illustrating functional dependencies within the initial table. It then proceeds to decompose the relation, detailing the steps required to achieve 3NF, ensuring that the database design adheres to normalization principles. Finally, a relational schema is drawn to represent the 3NF relations, demonstrating the referential integrity constraints to maintain data consistency. The assignment covers key concepts such as functional dependencies, non-prime attributes, and transitive dependencies, providing a comprehensive guide to database design and normalization. The solution effectively addresses all aspects of the assignment brief, offering a clear and concise approach to database normalization techniques.

Running head: DATABASE NORMALIZATION AND DESIGN
Database normalization and Design
Name of the Student
Name of the University
Authors note

1DATABASE NORMALIZATION AND DESIGN
1. Dependency diagram for the given table
The following functional dependency in determining the association among two
attributes in table of relational database. As depicted below one attribute is called the
determinant and another which is pointed by the previous attribute is known as
determined.
Here, as depicted in the above diagram, the Meeting ID is capable of determining the
meeting date and meeting date, time and venue. In addition to that, the userID attribute itself
is capable of determining the user availability stored in the database.
In the above diagram there is a multivalued dependency, which is the MeetingID,
MeetingDate are capable of determining the Meeting Venue.
2. Decomposition in 3NF
1 NF
According to the rules of normalization, in a first normal form an attribute or column
in a table should not hold multiple values, thus it should have atomic values.

2DATABASE NORMALIZATION AND DESIGN
2NF
In order to normalize the table to 2NF we require the table to meet the following
condition
Table have to be in (First normal form) and non-prime attribute in the table should be
dependent on proper subset of candidate keys available in the table.
In the above diagram, the UserID is dependent on the MeetingID for determining the
Rating for a Meeting.

3DATABASE NORMALIZATION AND DESIGN
3NF
As the tables are in 2NF, thus from the last tables it is important to remove the transitive
functional dependency of different non-prime attributes that depends on super keys. An
attribute or column that is not a part of candidate keys is often denoted by non-prime
attribute.
Here the dependency of the meetingID and userID for Finding the Rating is removed.

4DATABASE NORMALIZATION AND DESIGN
3.Relational Schema with referential integrity