Minimum Relations Satisfying First Normal Form (1NF)
Last Updated : 23 Nov, 2023
A relation that does not contain any composite or multivalued attribute, then the relation is in its First Normal Form. Relations that contain a single-valued attribute comes under First Normal Form. In this article, we will be going to discuss the minimum relations satisfying the First Normal Form. Before proceeding to First Normal Form, let's discuss how to design a database.
How to Design a Database?
- Talk to the stakeholder for which we are designing the database. Get all the requirements, what attributes need to be stored, and establish functional dependencies over the given set of attributes.
- Draw an Entity-Relationship Diagram on the basis of requirements analysis.
- Convert the ER diagram into the relational model and finally create these relations into our database with appropriate constraints.
Designing ER Diagrams is easier than finding minimum relations that satisfy the First Normal Form. We establish certain simple rules which are formed after a deep analysis of each case and hence, could be used directly by understanding the logic behind them.
- If there is total participation on both sides; Merge the two entities involved and the relationship into 1 table.
- Else if, one side is total participation and one side is partial
- M: N - Merge the relationship on the total participation side.
- 1: N - Merge the relationship on the total participation side.
- 1: 1 - Merge the two entities involved and the relationship into 1 table.
- Else if, both sides are partial participation
- M: N - Separate table for each entity as well as relationship. Hence, 3 tables.
- 1: N - Merge the relationship on the N-side using foreign key referencing 1-side.
- 1: 1 - Merge the relationship and one entity into 1 table using the foreign key and 1 table for the other entity.
Now, you would definitely have a question in your mind, how do we form such rules? This is very easy and logical. Let's understand the logic behind it for one case and you can similarly establish the results for other cases too. We have been given a scenario of a 1:N relationship with two entities E1(ABC) and E2(DEF), where A and D are primary keys, respectively. E1 has partial participation while E2 has total participation in the relationship R. Based on the above scenario, we create certain tuples in E1:
|
a1 | b1 | c1 |
a2 | b2 | c2 |
a3 | b3 | c3 |
Similarly, create certain tuples for E2:
|
d1 | e1 | f1 |
d2 | e2 | f2 |
d3 | e3 | f3 |
Now, create a relationship R satisfying the above conditions, i.e. E1 is partial participation and E2 is total participation, and E1 to E2 is a 1:N relationship.
Ways of Merging Two Entities into a Single Table
- Way-1: Merge the two entities and relationships into a single table. This is not correct as (AD) will become the primary key for this table, but the primary key can never have a NULL value.
|
a1 | b1 | c1 | d1 | e1 | f1 |
a1 | b1 | c1 | d2 | e2 | f2 |
a2 | b2 | c2 | d3 | e3 | f3 |
a3 | b3 | c3 | NULL | NULL | NULL |
- Way-2: Merge relationship on 1-side. This is not correct as (AD) will become the primary key for this table, but the primary key can never have a NULL value.
|
a1 | b1 | c1 | d1 |
a1 | b1 | c1 | d2 |
a2 | b2 | c2 | d3 |
a3 | b3 | c3 | NULL |
- Way-3: Merge relationship on N-side. This is correct.
|
d1 | e1 | f1 | a1 |
d2 | e2 | f2 | a1 |
d3 | e3 | f3 | a2 |
On the same grounds, could you think why we allow merging the two entities as well as relationships into 1 table when it is a 1:1 relationship? Simply, we would not have a composite primary key there, so we will definitely have a primary key with no NULL values present in it. Stress some more, why do we allow merging the entities and relationship with both sides' total participation? The reason is even if we have a composite primary key for such a merged table, we are sure that it will never have any NULL values for the primary key.
Note - You can follow the same procedure as stated above to establish all the results.
Similar Reads
First Normal Form (1NF) In relational database design, normalization is the process of organizing data to reduce redundancy and improve data integrity. First Normal Form (1NF) is the first step in this process. It ensures that the structure of a database table is organized in a way that makes it easier to manage and query.
4 min read
How to find the highest normal form of a relation Normalization is the process of structuring data in a database by creating tables and defining relationships between them. This ensures data consistency, protection, and improves the database's efficiency and flexibility. Typically, every table in a relational database is assumed to be in the first
5 min read
What is Fifth Normal Form (5NF) in DBMS? Normalization is a process that involves removing or decreasing the redundancy present in the database. Normalization mainly focuses on removing duplicate data from the database and making it more consistent. There are various types of normalization such as 1NF,2NF, 3NF, BCNF, 4NF, and 5NF. 5NF is o
5 min read
Types of Normal Forms in DBMS Database normalization is nothing but the process of structuring an RDBMS by applying some general rules either by creating a new database design or by decomposition with a series of so-called normal forms which are: Unnormalized form or UNF First Normal Form or 1NF Second Normal Form or 2NF Third N
6 min read
What is PJNF(Project-Join Normal Form)? The Fifth Normal Form (5NF), or Project-Join Normal Form (PJNF), is the highest level of database normalization that is designed to solve problems of data redundancy. A relation is called 5NF when it is in 4NF and does not have a join dependency. It also shows that when different relations are compl
4 min read