Mastering Database Management Systems

What is SQL query optimization and why is it important?

• SQL query optimization is the process of creating new databases.

• SQL query optimization is only necessary for large datasets.

• SQL query optimization is about writing SQL queries in different programming languages.

• SQL query optimization is the process of improving the performance of SQL queries, which is important for enhancing application speed and efficiency, reducing server load, and improving user experience.

Name three common techniques for optimizing SQL queries.

• Database Normalization

• Using Stored Procedures

• Indexing, Query Rewriting, Analyzing Execution Plans

• Data Encryption

What is the purpose of indexing in SQL databases?

• To create backups of the database

• To ensure data integrity

• To increase the size of the database

• The purpose of indexing in SQL databases is to speed up data retrieval.

Explain the concept of normalization in database design.

• Normalization is the process of combining all data into a single table.

• Normalization eliminates the need for any relationships between tables.

• Normalization involves structuring a database to minimize redundancy and dependency by dividing data into related tables.

• Normalization is only concerned with increasing data storage capacity.

What are the different normal forms in database normalization?

• 1NF, 2NF, 3NF, 4NF, 5NF, 6NF

• 1NF, 2NF, 3NF, 6NF

• 1NF, 2NF, 3NF, 4NF, 5NF, 7NF

• 1NF, 2NF, 3NF, BCNF, 4NF, 5NF

How does denormalization affect database performance?

• Denormalization can improve read performance but may negatively impact write performance due to increased redundancy.

• Denormalization eliminates redundancy in the database.

• Denormalization has no effect on database performance.

• Denormalization only improves write performance without affecting reads.

What is a composite key and how is it used in normalization?

• A composite key is a single column used to identify records.

• Normalization eliminates the need for any keys in a database.

• A composite key is used only for indexing and has no role in normalization.

• A composite key is a unique identifier made up of multiple columns, used in normalization to ensure unique records and reduce redundancy.

Describe the process of converting a database to 3NF.

• The process involves ensuring the database is in 2NF, identifying functional dependencies, removing transitive dependencies, creating new tables, and establishing foreign key relationships.

• 3NF can be achieved by simply deleting all duplicate records.

• The process involves merging all tables into one.

• Converting a database to 3NF requires only adding indexes.

What are the advantages of using stored procedures for query optimization?

• Stored procedures improve performance through pre-compilation, reduced network traffic, and better execution plan reuse.

• Stored procedures eliminate the need for indexing completely.

• Stored procedures increase data redundancy and complexity.

• Stored procedures require more manual coding for each query.

How can query execution plans help in optimizing SQL queries?

• They automatically rewrite SQL queries for better performance.

• Query execution plans help in optimizing SQL queries by revealing inefficiencies and guiding improvements.

• They provide a graphical representation of the database schema.

• They eliminate the need for indexing in databases.