Microsoft Azure Data Fundamentals Flashcards

Which kind of items in a relational database should you configure a datatype for?

Answer: columns

In a relational database, datatypes are configured for columns (also known as attributes). Each column stores a specific type of data, such as integers, text, or dates, for all rows in the table. This ensures data integrity and consistency within that particular column.

To aggregate the content of two tables based on a shared column, which SQL operation is used?

Answer: JOIN

The `JOIN` operation in SQL is used to combine rows from two or more tables based on a related column between them. This allows you to retrieve data that is spread across multiple tables, creating a single, unified result set. Other operations like `UNION` combine rows from result sets, not based on shared columns.

What SQL clause should be used to copy every row from one table to another?

Answer: SELECT - INTO

The `SELECT ... INTO` statement in SQL is specifically designed to copy all or a subset of rows from one table into a *new* table. It creates the new table and inserts the selected data in a single operation. `INSERT - VALUES` adds rows to an existing table, while `UPDATE` modifies existing rows.

Data integrity is ensured and duplicate data is decreased in databases using the ______________ procedure.

Answer: Normalization

Normalization is a systematic process of organizing the columns and tables of a relational database to minimize data redundancy and improve data integrity. It involves decomposing tables into smaller, related tables and defining relationships between them. This structured approach helps prevent anomalies and ensures data consistency.

Which three claims about a table's characteristics in a relational database are true? Select all applicable answers.

Answer: A table's rows and columns all have the same number of columns.

In a relational table, all rows must have the same number of columns, ensuring a consistent structure. Each column also requires a defined datatype to specify the kind of data it will hold, enforcing data integrity. A primary key, which uniquely identifies each row, can indeed be composed of one or more columns (a composite primary key).

You must suggest a remedy that satisfies the following criteria: - Comprises business logic that allows for database product renaming - Increases table entries What should the recommendation contain?

Answer: a stored procedure

A stored procedure is a pre-compiled collection of SQL statements and business logic stored in the database. It can accept parameters, perform complex operations like renaming products (updating multiple tables) and inserting new entries, offering better performance and security. Inline functions and views are primarily for returning data, not for complex transactional logic.

What can you use in a relational database to make a virtual table out of the results of a SELECT statement?

Answer: a view

A view in a relational database is a virtual table based on the result-set of a SQL `SELECT` statement. It does not store data itself but rather provides a dynamic window into the underlying tables, allowing users to query it as if it were a real table. Views simplify complex queries and can enhance security by restricting access to certain data.

You have a folder with text files, pictures, and audio files in it. What kind of data do you have?

Answer: unstructured

Unstructured data refers to information that does not have a predefined data model or is not organized in a pre-defined manner. Text files, images, and audio files fall into this category because their content is not easily searchable or analyzable by traditional database methods without further processing. Structured data is highly organized, while semi-structured has some organizational properties but lacks a fixed schema.

What kind of database would work best for an organizational chart?

Answer: graph

A graph database is ideal for representing and querying relationships between entities, making it perfect for an organizational chart. In this model, employees would be "nodes" and their reporting lines or departmental connections would be "edges," allowing for efficient traversal and analysis of hierarchical and networked structures. Other database types are less suited for complex relationship modeling.

Which database type should you employ to store sequential data as quickly as possible?

Answer: time series

A time series database is specifically optimized for storing and querying data points that are indexed by time, such as sensor readings or system metrics. They are designed for high-volume writes and efficient retrieval of sequential data, making them the best choice for quickly storing and analyzing time-stamped information. Other database types are not specialized for this kind of sequential data.

You create an application that must store data in accordance with the following specifications: - Save historical information from many data sources. - Add data on a recurring basis. Use a denormalized snowflake or star schema. Which database type should you employ?

Answer: OLAP

Online Analytical Processing (OLAP) databases are designed for complex analytical queries and reporting, often using denormalized star or snowflake schemas. They are optimized for reading large volumes of historical data from multiple sources and are typically updated periodically, making them ideal for data warehousing and business intelligence scenarios. OLTP databases are for transactional workloads with frequent updates.

Which service enables the storage of information as a graph database?

Answer: Azure Cosmos DB

Azure Cosmos DB is Microsoft's globally distributed, multi-model database service that supports various API models, including Gremlin for graph databases. It allows you to store and query data as a graph, making it suitable for scenarios requiring complex relationship modeling. Azure SQL Database and Managed Instance are relational databases, and Synapse Analytics is for data warehousing and analytics.

Which two file types use columnar data storage? Each accurate response gives a complete resolution. Select all applicable answers.

Answer: Parquet

Parquet and ORC (Optimized Row Columnar) are popular columnar data storage formats, especially in big data ecosystems. Columnar storage stores data column by column rather than row by row, which significantly improves query performance for analytical workloads by allowing systems to read only the necessary columns. CSV and Avro are typically row-oriented formats.

Which two traits best describe a transactional data workload? Select each appropriate response.

Answer: highly normalized

Transactional data workloads, typically handled by OLTP systems, are characterized by frequent, small, and concurrent create, read, update, and delete (CRUD) operations. To ensure data integrity and minimize redundancy, these databases are usually highly normalized. This design prioritizes fast transaction processing and data consistency over analytical query performance.

Which sort of data workload stores a lot of aggregated data and is best for read operations?

Answer: analytical

Analytical data workloads are specifically designed for processing and querying large volumes of historical and aggregated data. They are optimized for read-heavy operations, enabling users to perform complex analyses, generate reports, and derive insights from the data. This contrasts with transactional workloads, which prioritize rapid, individual write operations.

What three characteristics distinguish analytical data workloads? Each accurate response provides a complete solution. Select each appropriate response.

Answer: Over time, they compute business metrics.

Analytical data workloads are characterized by their focus on historical data to compute business metrics over time. They are primarily optimized for read operations, allowing for efficient querying and analysis of large datasets to uncover trends and insights. This design prioritizes complex data retrieval and aggregation over rapid, individual write operations or real-time data processing.