DBMS Notes
Complete guide to First Normal Form (1NF) normalization. Learn what 1NF is, why it
Understanding First Normal Form (1NF)
First Normal Form (1NF) is the foundational level of database normalization that ensures a relation contains only atomic (indivisible) values. It eliminates repeating groups and ensures that each attribute contains only single values, not sets or arrays of values.
Definition of 1NF
A relation is in First Normal Form if:
- Every attribute contains only atomic values (single values, not arrays or sets)
- No repeating groups exist in the table
- Each column contains values of the same type
- All values in a column are of the same domain
Why 1NF Matters
| StudentID | Name | Hobbies |
|---|---|---|
| StudentID | Name | Hobby |
| 1 | Alice | Reading |
| 1 | Alice | Gaming |
| 2 | Bob | Sports |
| 2 | Bob | Coding |
Rules for Converting to 1NF
Rule 1: Remove Repeating Groups
Identify columns with multiple values and create separate rows for each value.
Example:
Before 1NF
Employee(EmpID, Name, Projects)
1, Alice, [Project A, Project B, Project C]
After 1NF
Employee(EmpID, Name, Project)
1, Alice, Project A
1, Alice, Project B
1, Alice, Project C
Rule 2: Identify Primary Key
Once repeating groups are removed, establish a primary key. It may now be composite.
CREATE TABLE Employee (
EmpID INT,
Name VARCHAR(50),
Project VARCHAR(50),
PRIMARY KEY (EmpID, Project)
);Rule 3: Separate Multi-valued Dependencies
If a column contains arrays, lists, or multiple values separated by delimiters, decompose them.
Example - Phone Numbers:
Before
Customer(CustID, Name, Phones)
101, John, "9876543210, 9876543211"
After
Customer(CustID, Name)
101, John
CustomerPhone(CustID, Phone)
101, 9876543210
101, 9876543211
Practical Examples
Example 1: Student Courses
Non-Normalized Table:
1NF Compliant:
Example 2: Product Orders
Before 1NF:
| OrderID | Items |
|---|---|
| O001 | Laptop, Mouse, Cable |
| O002 | Monitor |
After 1NF:
| OrderID | Item |
|---|---|
| O001 | Laptop |
| O001 | Mouse |
| O001 | Cable |
| O002 | Monitor |
SQL Implementation
Converting to 1NF Using SQL
-- Original Non-Normalized Table
CREATE TABLE Authors_NonNorm (
BookID INT PRIMARY KEY,
Title VARCHAR(100),
AuthorNames VARCHAR(500) -- Multiple authors as string
);
-- Convert to 1NF
CREATE TABLE Books (
BookID INT PRIMARY KEY,
Title VARCHAR(100)
);
CREATE TABLE BookAuthors (
BookID INT,
Author VARCHAR(100),
PRIMARY KEY (BookID, Author),
FOREIGN KEY (BookID) REFERENCES Books(BookID)
);Advantages of 1NF
- Eliminates Redundancy - Reduces data duplication
- Improves Data Integrity - Atomic values are easier to validate
- Simplifies Queries - Standard SQL operations work better
- Facilitates Updates - Changes to values don't affect multiple entries
- Better Performance - Searches are more efficient
- Prevents Anomalies - Reduces insertion, update, and deletion anomalies
- Standardization - Follows database design best practices
Disadvantages of 1NF
- Increased Table Size - More rows due to decomposition
- Join Complexity - More joins required in queries
- Storage Overhead - Key repetition across multiple rows
- Query Complexity - Some queries become more complex
- Performance Trade-off - Initial write performance may decrease
Comparison Table
| Aspect | Unnormalized | 1NF |
|---|---|---|
| Repeating Groups | Yes | No |
| Non-atomic Values | Yes | No |
| Redundancy | High | Reduced |
| Data Anomalies | Likely | Minimized |
| Query Complexity | Moderate | Higher |
| Performance | Faster reads | Moderate |
| Maintenance | Difficult | Easier |
Quick Revision Notes
- 1NF Rule: Each attribute must contain only atomic values
- No Multi-valued Attributes: Eliminate columns with arrays or lists
- Atomic Data: Every field should contain a single, indivisible value
- Primary Key: Establish a unique identifier for each row
- Decomposition: Break down tables with repeating groups
- Foreign Keys: Link decomposed tables through relationships
- Trade-offs: Balance between normalization and performance
Interview Q&A
Q1: What is First Normal Form (1NF)?
A: 1NF is the first level of database normalization that requires all attributes to contain only atomic (single, indivisible) values. It eliminates repeating groups and ensures each column contains values of the same type.
Q2: How do you identify if a table violates 1NF?
A: A table violates 1NF if:
- Any column contains multiple values (arrays, lists, or comma-separated values)
- There are repeating groups of columns
- Any attribute is not atomic
- The same data is stored in multiple columns
Q3: What's the difference between 1NF and unnormalized data?
A: Unnormalized data can have repeating groups and multi-valued attributes, while 1NF eliminates these by requiring atomic values and no repeating groups. 1NF is the minimum normalization level.
Q4: How do you convert a non-1NF table to 1NF?
A:
- Identify columns with multiple values
- Create separate rows for each value
- Remove repeating groups
- Define primary keys (may be composite)
- Use foreign keys to relate decomposed tables
Q5: What are the performance implications of 1NF?
A: 1NF improves data integrity and reduces anomalies but may require more storage space and table joins in queries. Write operations might be slower, but data consistency improves significantly.
Q6: Can a table be partially in 1NF?
A: No, a table either satisfies 1NF completely or it doesn't. All attributes must be atomic for a table to be in 1NF. A table cannot be "partially" normalized.
Created: 2024 | Author: Database Design Expert
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for First Normal Form (1NF) - Database Normalization.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this Database Management Systems (DBMS) topic.
Search Terms
dbms, database management systems (dbms), unit, first, normal, form, first normal form (1nf) - database normalization
Related Database Management Systems (DBMS) Topics