Advanced Database Concepts Assessment

What is the purpose of normalization in database design?

• To increase data duplication for faster access.

• To enhance the visual representation of data in reports.

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

• To simplify the database structure by merging tables.

Explain the difference between 1NF, 2NF, and 3NF.

• 1NF ensures atomicity, 2NF eliminates partial dependencies, and 3NF removes transitive dependencies.

• 1NF is about data types, 2NF is about indexing, and 3NF is about query optimization.

• 1NF groups related data, 2NF allows for multi-valued attributes, and 3NF is about data normalization.

• 1NF allows duplicate rows, 2NF requires all attributes to be unique, and 3NF focuses on data redundancy.

What is an entity in an Entity-Relationship model?

• An entity is a process that describes how data is manipulated.

• An entity is a collection of attributes without any distinct identity.

• An entity is a type of relationship between two objects.

• An entity is a distinct object or thing in the real world that can be identified and has attributes.

How do you represent relationships in an ER diagram?

• Relationships are represented by squares in an ER diagram.

• Relationships are represented by circles connecting entities in an ER diagram.

• Relationships are represented by arrows pointing to entities in an ER diagram.

• Relationships are represented by diamonds connecting entities in an ER diagram.

What is the significance of primary keys in a database?

• Primary keys are used to store large amounts of data.

• Primary keys are optional for all database tables.

• Primary keys can be duplicated across records.

• Primary keys ensure uniqueness and integrity of records in a database.

Describe the process of SQL query optimization.

• SQL query optimization involves only rewriting the SQL syntax without analysis.

• SQL query optimization is only about adding more hardware resources.

• SQL query optimization is the process of improving the performance of SQL queries through analysis, execution plan evaluation, indexing, query rewriting, and statistical analysis.

• SQL query optimization is the process of converting SQL queries to NoSQL queries.

What are some common techniques for optimizing SQL queries?

• Common techniques for optimizing SQL queries include using indexes, avoiding SELECT *, limiting rows with WHERE clauses, efficient JOINs, analyzing execution plans, and denormalization.

• Ignoring execution plans completely

• Always using subqueries for better performance

• Using only SELECT * for simplicity

How can indexing improve database performance?

• Indexing enhances data retrieval speed and efficiency in databases.

• Indexing increases the amount of data stored in the database.

• Indexing slows down data retrieval processes.

• Indexing makes data updates more complex and time-consuming.

What are the main types of database security measures?

• Access Control, Encryption, Auditing, Backup and Recovery

• User Interface Design

• Network Security

• Data Mining

Explain the concept of data encryption in databases.

• Data encryption in databases makes data more accessible to unauthorized users.

• Data encryption in databases protects sensitive information by converting it into a coded format that is unreadable without the proper decryption key.

• Data encryption in databases is used to speed up data retrieval.

• Data encryption in databases involves storing data in plain text format.