Free ADC Database Management & Normalization Questions and Answers — Questions and Answers

Question 1: What is the primary purpose of database normalization?

To eliminate data redundancy. (Correct answer)
To speed up all queries.
To simplify user interfaces.
To add more fields to a table.

Correct answer: To eliminate data redundancy.

Database normalization is a systematic process of organizing the columns and tables of a relational database to minimize data redundancy and improve data integrity. By breaking down large tables into smaller, related tables and defining relationships, it ensures data is stored efficiently and consistently. This reduces storage space and prevents update anomalies.

Question 2: What does the First Normal Form (1NF) require?

All data is in uppercase.
Each column contains only atomic values. (Correct answer)
There are foreign keys in every table.
Each row has more than one primary key.

Correct answer: Each column contains only atomic values.

The First Normal Form (1NF) requires that each column in a table contains only atomic, single-valued entries, meaning no repeating groups or multi-valued attributes. Additionally, each row must be uniquely identifiable, typically through a primary key. This foundational step ensures that data is structured in a basic, consistent format, preparing it for further normalization.

Question 3: Which key uniquely identifies a record in a table?

Foreign key
Composite key
Primary key (Correct answer)
Candidate key

Correct answer: Primary key

A primary key is a column or a set of columns in a table that uniquely identifies each record (row) in that table. It ensures that every row can be distinctly referenced and prevents duplicate records, which is fundamental for maintaining data integrity. Primary keys are also crucial for establishing relationships between different tables in a relational database.

Question 4: What is a foreign key used for?

To make queries faster.
To delete records automatically.
To create indexes.
To reference a primary key in another table. (Correct answer)

Correct answer: To reference a primary key in another table.

A foreign key is a column or a set of columns in one table that refers to the primary key in another table. Its main purpose is to establish and enforce a link or relationship between two tables, ensuring referential integrity. This allows for consistent data across related tables and prevents orphaned records, maintaining the database's structural soundness.

Question 5: Which normal form removes partial dependencies?

Second Normal Form (2NF) (Correct answer)
First Normal Form (1NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)

Correct answer: Second Normal Form (2NF)

The Second Normal Form (2NF) builds upon 1NF by requiring that all non-key attributes in a table be fully functionally dependent on the entire primary key. This means that if a table has a composite primary key, no non-key attribute should depend only on a part of that primary key. This process helps eliminate partial dependencies and further reduces data redundancy.

Question 6: Which statement is true about denormalization?

It eliminates all indexes.
It can improve read performance by reducing joins. (Correct answer)
It is mandatory for database design.
It removes all data types.

Correct answer: It can improve read performance by reducing joins.

Denormalization is the process of intentionally introducing redundancy into a database, often by combining tables or adding duplicate data. While it goes against normalization principles, it can significantly improve read query performance by reducing the number of complex joins required to retrieve data. This trade-off is frequently made in data warehousing or reporting systems where read speed is critical.

Question 7: What is a composite key?

A key for security encryption.
A temporary key for sorting.
A key made from numbers only.
A combination of two or more columns to form a unique key. (Correct answer)

Correct answer: A combination of two or more columns to form a unique key.

A composite key is a primary key that consists of two or more columns whose combined values uniquely identify each row in a table. No single column within the composite key can uniquely identify a row on its own. This is often used when a natural single-column primary key does not exist, requiring multiple attributes to ensure uniqueness.

Question 8: What happens if a table lacks a primary key?

It may contain duplicate records. (Correct answer)
It runs faster queries.
It creates automatic indexes.
It enforces stronger security.

Correct answer: It may contain duplicate records.

If a table lacks a primary key, there is no mechanism to uniquely identify each row within that table. This means the database system cannot prevent the insertion of identical records, leading to data redundancy and potential inconsistencies. A primary key is crucial for data integrity and for establishing reliable relationships with other tables.

Question 9: Which command is used to define a primary key in SQL?

FOREIGN KEY
DEFAULT
PRIMARY KEY (Correct answer)
UNIQUE INDEX

Correct answer: PRIMARY KEY

The `PRIMARY KEY` constraint is used in SQL to define a column or a set of columns as the primary key for a table. This constraint ensures that the values in the specified column(s) are unique and not null, thereby uniquely identifying each record. It is typically defined during table creation or alteration to enforce data integrity.

Free ADC Database Management & Normalization Questions and Answers

What is the primary purpose of database normalization?