What is the difference between a unique index and a non-unique index?
- A non-unique index allows duplicate values in the indexed column(s)
- A non-unique index does not allow NULL values in the indexed column(s)
- A unique index allows NULL values in the indexed column(s)
- A unique index allows only unique values in the indexed column(s)
A unique index enforces uniqueness, ensuring that each indexed value is unique, while a non-unique index allows duplicate values to be stored. Understanding this difference is crucial for data integrity and query optimization.
________ is a technique used in Dimensional Modeling to handle changes to dimension attributes over time.
- Fast Updating Dimension (FUD)
- Quick Altering Dimension (QAD)
- Rapidly Changing Dimension (RCD)
- Slowly Changing Dimension (SCD)
Slowly Changing Dimension (SCD) is a technique used in Dimensional Modeling to handle changes to dimension attributes over time. It involves maintaining historical data to accurately reflect changes in dimension attributes.
________ is a NoSQL database that is optimized for high availability and partition tolerance, sacrificing consistency under certain circumstances.
- Cassandra
- MongoDB
- Neo4j
- Redis
Cassandra is a NoSQL database designed for high availability and partition tolerance in distributed environments. It follows the principles of the CAP theorem, prioritizing availability and partition tolerance over consistency in certain scenarios.
In an ERD, a ________ is a property or characteristic of an entity.
- Attribute
- Entity
- Key
- Relationship
An attribute in an ERD represents a property or characteristic of an entity. It describes the data that can be stored for each instance of the entity, contributing to the overall definition of the entity's structure.
What are the main challenges faced in distributed computing?
- Bandwidth, User authentication, Encryption, Application logic
- High availability, Machine learning, Algorithm complexity, Database normalization
- Network latency, Consistency, Fault tolerance, Data security
- Scalability, Data storage, CPU performance, User interface design
Distributed computing presents several challenges, including network latency, which affects the speed of communication between nodes, consistency issues arising from concurrent updates, the necessity of fault tolerance to handle node failures gracefully, and ensuring data security across distributed environments. These challenges require careful consideration and design to build robust distributed systems.
Scenario: A company's database system is struggling to handle a surge in concurrent transactions during peak hours. What strategies would you recommend to improve database performance and scalability?
- Implementing asynchronous processing
- Implementing connection pooling
- Optimizing indexes and queries
- Vertical scaling by upgrading hardware
Optimizing indexes and queries involves identifying and fine-tuning inefficient queries and creating appropriate indexes to speed up data retrieval. By optimizing database access patterns, unnecessary resource consumption is minimized, improving overall performance. This strategy is essential for handling high concurrency levels effectively without overloading the database system.
A common method for identifying outliers in a dataset is through the use of ________.
- Box plots
- Correlation matrices
- Histograms
- Mean absolute deviation
Box plots, also known as box-and-whisker plots, are graphical representations of the distribution of data points in a dataset. They visually display key statistical measures such as median, quartiles, and outliers, making them a useful tool for identifying and visualizing outliers in a dataset. Outliers are data points that significantly deviate from the overall pattern of the data and may indicate errors, anomalies, or interesting phenomena worthy of further investigation.
Scenario: Your company has decided to implement a data warehouse to analyze sales data. As part of the design process, you need to determine the appropriate data modeling technique to represent the relationships between various dimensions and measures. Which technique would you most likely choose?
- Dimension Table
- Fact Table
- Snowflake Schema
- Star Schema
In a data warehouse scenario for analyzing sales data, a Star Schema is commonly used. It consists of a central Fact Table surrounded by Dimension Tables, providing a denormalized structure optimized for querying and analysis.
Scenario: You're designing a database for a highly transactional system where data integrity is critical. Would you lean more towards normalization or denormalization, and why?
- Denormalization, as it facilitates faster data retrieval and reduces the need for joins
- Denormalization, as it optimizes query performance at the expense of redundancy
- Normalization, as it reduces redundancy and ensures data consistency
- Normalization, as it simplifies the database structure for easier maintenance and updates
In a highly transactional system where data integrity is crucial, leaning towards normalization is preferable. Normalization minimizes redundancy and maintains data consistency through the elimination of duplicate data, ensuring that updates and modifications are efficiently managed without risking data anomalies.
Scenario: Your company is merging data from two different databases into a single system. How would you apply data quality assessment techniques to ensure that the merged data is consistent and reliable?
- Data integration
- Data matching
- Data normalization
- Data reconciliation
Data reconciliation involves comparing and resolving inconsistencies between datasets from different sources. By applying data reconciliation techniques, you can identify discrepancies in data attributes, resolve conflicts, and ensure consistency and accuracy in the merged dataset. This process is essential for integrating data from disparate sources while maintaining data quality and integrity.