Database Normalization

What is the purpose of normalization in databases?

• To increase data redundancy and decrease data integrity.

• The purpose of normalization is to reduce data redundancy and improve data integrity.

• To enhance data security by encrypting all data.

• To simplify database queries and reduce performance.

Answer explanation

The purpose of normalization is to reduce data redundancy and improve data integrity by organizing data efficiently, ensuring that each piece of information is stored only once, which enhances consistency and accuracy.

Define the first normal form (1NF).

• A table is in first normal form (1NF) if it has a primary key and contains only atomic values, with no repeating groups.

• A table is in first normal form (1NF) if it has multiple primary keys.

• A table is in first normal form (1NF) if it contains non-atomic values.

• A table is in first normal form (1NF) if it allows repeating groups of data.

Answer explanation

The correct choice defines 1NF accurately: a table must have a primary key, contain only atomic values, and avoid repeating groups. The other options contradict these fundamental principles of database normalization.

How do you identify a primary key in a relation?

• A primary key is optional and can be NULL.

• A primary key can have duplicate values in a relation.

• A primary key is a foreign key that links to another table.

• A primary key is a unique identifier for each record in a relation, ensuring no duplicate or NULL values.

Answer explanation

A primary key uniquely identifies each record in a relation, meaning it cannot have duplicate or NULL values. This ensures data integrity and allows for efficient data retrieval.

Explain the second normal form (2NF).

• A table is in 1NF if it has no duplicate rows.

• A table is in 2NF if it has at least one foreign key.

• A table is in 2NF if all attributes are dependent on a single non-key attribute.

• 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.

Answer explanation

A table is in Second Normal Form (2NF) if it meets the criteria of First Normal Form (1NF) and ensures that all non-key attributes are fully functionally dependent on the primary key, preventing partial dependencies.

What is the third normal form (3NF)?

• A database table is in third normal form (3NF) if it has a composite primary key.

• A database table is in third normal form (3NF) if all attributes are dependent on the primary key.

• A database table is in first normal form (1NF) if it has no repeating groups.

• A database table is in third normal form (3NF) if it is in second normal form (2NF) and all non-key attributes are not transitively dependent on the primary key.

Answer explanation

A database table is in third normal form (3NF) if it is in second normal form (2NF) and all non-key attributes are not transitively dependent on the primary key, ensuring minimal redundancy and dependency.

How can you eliminate redundancy in a database?

• Add more indexes to the database

• Use a single table for all data

• Normalize the database and use foreign keys to establish relationships.

• Duplicate data across multiple tables

Answer explanation

To eliminate redundancy in a database, you should normalize it and use foreign keys to establish relationships. This process organizes data efficiently, reducing duplication and ensuring data integrity.

What is the difference between 1NF and 2NF?

• 1NF is about normalization, while 2NF is about denormalization.

• 1NF requires a primary key, while 2NF does not.

• 1NF eliminates duplicate rows, while 2NF allows for redundancy.

• 1NF focuses on atomicity and data type uniformity, while 2NF addresses partial dependencies on the primary key.

Answer explanation

1NF (First Normal Form) ensures that all data is atomic and of uniform data types, while 2NF (Second Normal Form) focuses on removing partial dependencies of non-key attributes on the primary key, enhancing data integrity.

Provide an example of a functional dependency.

• StudentName -> StudentID

• StudentID -> StudentName

• TeacherID -> StudentID

• CourseID -> CourseName

Answer explanation

The correct functional dependency is 'StudentID -> StudentName' because each StudentID uniquely identifies a StudentName. This means knowing the StudentID allows us to determine the corresponding StudentName.

What does it mean to decompose a relation?

• Decomposing a relation involves deleting all data from it.

• To decompose a relation means to combine multiple relations into one.

• To decompose a relation means to break it into smaller relations to reduce redundancy and improve data integrity.

• To decompose a relation is to create a backup of the data.

Answer explanation

To decompose a relation means to break it into smaller relations to reduce redundancy and improve data integrity. This process helps organize data more efficiently and maintains consistency across the database.

How do you create an ER diagram?

• Identify entities, relationships, and attributes; then draw them accordingly.

• Draw a flowchart instead

• Use only text descriptions without visuals

• List all possible database types

Answer explanation

To create an ER diagram, you must identify the entities, relationships, and attributes involved in the system. Then, you can visually represent them in a diagram, making the correct choice the first option.