Understanding Database Normalization

What is the primary purpose of database normalization?

• To increase data duplication for faster access.

• To reduce data redundancy and improve data integrity.

• To simplify database queries and reduce complexity.

• To enhance the visual representation of data in reports.

Define First Normal Form (1NF).

• First Normal Form (1NF) allows duplicate records in a database.

• First Normal Form (1NF) requires all attributes to be non-atomic values.

• First Normal Form (1NF) is a method for indexing database tables.

• First Normal Form (1NF) is a property of a relation in a database that ensures all attributes contain only atomic values and each record is unique.

What is a key requirement for a table to be in 1NF?

• Each table must have a primary key.

• No duplicate rows are allowed in the table.

• All entries in a column must be atomic.

• All columns must have the same data type.

Explain the concept of Second Normal Form (2NF).

• A table is in Second Normal Form (2NF) if it has at least three columns.

• A table is in Second Normal Form (2NF) if it is in First Normal Form (1NF) and all non-key attributes are fully functionally dependent on the primary key.

• A table is in Second Normal Form (2NF) if it has no primary key.

• A table is in Second Normal Form (2NF) if it contains only numeric data types.

What is the main goal of achieving 2NF in a database?

• To ensure all attributes are fully dependent on the primary key.

• To remove all duplicate records from the database.

• To simplify the database structure by merging tables.

• To eliminate partial dependencies on a composite primary key.

Describe Third Normal Form (3NF) and its importance.

• Third Normal Form (3NF) eliminates transitive dependencies, ensuring that non-key attributes are only dependent on the primary key, thus reducing redundancy and improving data integrity.

• Third Normal Form (3NF) is primarily concerned with the physical storage of data rather than logical structure.

• Third Normal Form (3NF) allows duplicate data entries to improve performance.

• 3NF requires that all attributes are dependent on each other, not just the primary key.

What is a transitive dependency in the context of 3NF?

• A transitive dependency is when a non-prime attribute depends on another non-prime attribute, violating 3NF.

• A transitive dependency occurs when a primary key depends on a non-prime attribute.

• A transitive dependency is when a non-prime attribute depends on a primary key.

• A transitive dependency is a direct relationship between two primary keys.

Provide a real-world example of a database that could benefit from normalization.

• A library database managing book loans and returns.

• A retail database tracking customer purchases and inventory.

• A university database storing student information, courses, and enrollments.

• A social media database storing user posts and comments.

How would you convert a table from 1NF to 2NF?

• Remove all foreign keys from the table.

• Remove partial dependencies by creating new tables for attributes that depend only on part of a composite primary key.

• Combine all attributes into a single table without any keys.

• Add more attributes to the existing table without normalization.

What are the potential drawbacks of over-normalizing a database?

• Potential drawbacks of over-normalizing a database include increased complexity, performance issues, and difficulties in data management.

• Improved data retrieval speed

• Enhanced data redundancy

• Simplified data relationships