In which scenarios would you consider adding an index to a database table?

  • Frequently updated columns
  • Small tables with a few records
  • Tables used for historical data
  • Tables with sequential data access patterns
Indexing is beneficial in scenarios where columns are frequently updated. However, it may not be suitable for small tables or those with sequential data access patterns, as the overhead may outweigh the benefits.

Scenario: A social media platform wants to store user profiles with varying attributes and preferences. Each user profile may have different sets of data. Which type of database would you recommend for this scenario and why?

  • Document Database
  • Graph Database
  • Key-Value Store
  • Relational Database
In this scenario, a Document Database would be suitable. Document databases allow flexible storage of data with varying attributes, making them ideal for user profiles with different sets of data. Each user profile can be represented as a document, and the database can handle varying attributes efficiently.

In data warehousing, what is the purpose of a roll-up operation?

  • Roll-up is a compression technique to reduce storage space in data warehousing.
  • Roll-up is a dimension hierarchization process to move from detailed to summary levels.
  • Roll-up is a security feature restricting access to certain data in a data warehouse.
  • Roll-up is used to drill down into detailed data, providing a more granular view.
In data warehousing, a roll-up operation involves moving from a detailed level to a higher-level summary. It is part of the dimension hierarchization process, allowing users to analyze data at different levels of granularity. Understanding roll-up operations is essential for designing efficient and user-friendly data warehouses.

What is a subtype in an ERD representing an inheritance hierarchy?

  • Child
  • Parent
  • Subclass
  • Superclass
In an ERD, a subtype represents a subclass in an inheritance hierarchy. It inherits attributes and relationships from a superclass or parent entity. This modeling technique helps capture commonalities and differences among entities, promoting a more flexible and efficient database design.

Conceptual schema design helps in achieving __________, ensuring data consistency and integrity.

  • Abstraction
  • Integrity
  • Normalization
  • Redundancy
Conceptual schema design helps in achieving integrity, ensuring data consistency and integrity throughout the database. It involves defining rules and constraints to maintain the accuracy and reliability of the data.

_______ is a technique that exploits repeating patterns in data to reduce storage size.

  • Burrows-Wheeler Transform
  • Delta Encoding
  • Huffman Coding
  • Run-Length Encoding
Run-Length Encoding is a technique that exploits repeating patterns in data to reduce storage size. It represents consecutive identical symbols with a single code, followed by the count of occurrences, effectively reducing redundancy in the data.

The _______ model in NoSQL databases allows nested structures and dynamic schemas.

  • Column-Family
  • Document
  • Graph
  • Key-Value
The Document model in NoSQL databases allows nested structures and dynamic schemas. This means that each document in the database collection can have a different structure, allowing for flexibility in data representation.

What is a transitive dependency in the context of normalization?

  • Dependency between non-prime attributes
  • Dependency where a non-prime attribute determines a prime attribute
  • Dependency where a non-prime attribute determines another non-prime attribute
  • Dependency where a prime attribute determines another prime attribute
In normalization, a transitive dependency occurs when a non-prime attribute determines another non-prime attribute. It violates the principles of normalization, and resolving such dependencies is crucial for achieving higher normal forms.

How does indexing improve query performance in a database?

  • Decreases the overall database size
  • Increases the complexity of queries
  • Reduces the number of records in a table
  • Speeds up data retrieval by providing a quick access path
Indexing improves query performance by creating a quick access path to the data. It reduces the number of records that need to be scanned, making data retrieval faster for specific queries.

What is the central table in a Star Schema?

  • Dimension Table
  • Fact Table
  • Lookup Table
  • Reference Table
In a Star Schema, the central table is the Fact Table. This table contains the core data of the schema and is usually surrounded by Dimension Tables that provide context and additional details for the data in the Fact Table. The Fact Table typically contains numerical measures or facts that business users want to analyze.

What is aggregation in the context of data modeling?

  • Breaking down a large dataset into smaller pieces
  • Combining multiple entities into a single entity
  • Creating relationships between entities
  • Summarizing and combining data at a higher level
Aggregation in data modeling involves summarizing and combining data at a higher level. It is used to provide insights into the overall trends and patterns within a dataset, often involving the use of aggregation functions like SUM, AVG, COUNT, etc., to analyze and present data in a more meaningful way.

In an ERD, an _______ key is a key that consists of more than one attribute.

  • Composite
  • Foreign
  • Primary
  • Unique
In an ERD, a composite key is a key that consists of more than one attribute. It is used to uniquely identify an entity when a single attribute is not sufficient. Composite keys are common in situations where a combination of attributes is needed for uniqueness.