Mastering Database Management Systems

What is the purpose of database normalization?

• To increase data redundancy for faster access.

• To enhance the visual representation of data in reports.

• The purpose of database normalization is to minimize data redundancy and ensure data integrity.

• To simplify database queries by merging tables.

Explain the first normal form (1NF).

• First Normal Form (1NF) allows duplicate values in columns.

• 1NF requires that all data types in a column be the same.

• First Normal Form (1NF) is only applicable to relational databases.

• First Normal Form (1NF) is a property of a relation in a database that ensures all columns contain atomic values and each column has a unique name.

What are the advantages of normalization?

• Slower query performance

• Advantages of normalization include reduced data redundancy, improved data integrity, simplified data maintenance, enhanced query performance, and increased flexibility.

• Increased data duplication

• Complicated data maintenance

What is an index in SQL and how does it improve query performance?

• An index in SQL is a command that compiles SQL queries into machine code.

• An index in SQL is used to delete records from a database.

• An index in SQL is a database object that enhances query performance by allowing faster data retrieval.

• An index in SQL is a type of data type that stores large amounts of data.

Explain the difference between clustered and non-clustered indexes.

• Clustered indexes are faster than non-clustered indexes in all cases.

• Non-clustered indexes store data rows in the same order as the index key.

• Clustered indexes sort and store the data rows in the table based on the index key, while non-clustered indexes create a separate structure that references the data rows.

• Clustered indexes can be created on any column without restrictions.

What are the common techniques for SQL query optimization?

• Common techniques for SQL query optimization include using indexes, rewriting queries, avoiding SELECT *, using appropriate JOINs, and analyzing execution plans.

• Ignoring database statistics

• Using only aggregate functions

• Relying solely on subqueries

What is a transaction in the context of a database?

• A transaction is a sequence of operations treated as a single unit of work in a database.

• A transaction is a method for deleting records.

• A transaction is a backup of the database.

• A transaction is a type of database query.

Describe the ACID properties of transactions.

• Atomicity, Concurrency, Isolation, Reliability

• ACID properties: Atomicity, Consistency, Isolation, Durability.

• Atomicity, Consistency, Isolation, Accessibility

• Accuracy, Clarity, Integrity, Stability

What is the purpose of a rollback in transaction management?

• To revert changes and maintain data integrity in case of errors.

• To create a backup of the database before making changes.

• To speed up the transaction process by skipping steps.

• To permanently save all changes made during a transaction.

What is the difference between DDL and DML?

• DDL is a programming language; DML is a markup language.

• DDL defines database structure; DML manipulates data within that structure.

• DDL and DML are the same and can be used interchangeably.

• DDL is used for querying data; DML defines database structure.