October 6, 11:59 PM ISTOct 6, 11:59 PM IST
September 29, 5:17 AM ISTSep 29, 5:17 AM IST
1 left (3 attempts every 8 hours)
30 minutes per attempt30 min per attempt
1 left (1 total within the time limit)
To pass you need at least 80%. We keep your highest score.
Graded Assignment • 30 min
Which of the following statements is true about data modeling as perceived by the course instructor?
Data modeling is not only about organizing and standardizing data in a structured form, but it is also about informing decision-making, and facilitating actions within an organization. This is why it should be aligned with the business requirements, rules and vocab.
What is the correct sequence of steps in building and maintaining a data model?
You start with a high-level conceptual model that includes business logic and rules, then you move to the more detailed logical model where you add information like types of columns and primary and foreign keys, and finally, you design the physical model where you define the implementation in a database system.
Assume you work at a car rental agency. You have a table that contains the information of the cars and another table that contains the information of the rentals.
Which of the following correctly encodes the relationship between the two tables? In a given rental process, only one car can be rented by a customer. And a car can correspond to more than one rental.
Great job! The relationship between car and rental is “one and only one” to “zero or many”.
What is the primary goal of normalization in relational databases?
Normalization applies the “don’t repeat yourself” or DRY principle to data. It aims to remove data duplication and redundancy from a database by applying a series of normalization stages. In this way, it helps improve data consistency. For more information, please check the normalization video in lesson 1.
Which of the following statements is NOT part of the definition of the third normal form (3NF)?
In a third normal form, the conditions of the second normal form should be met and transitive dependencies should be removed. One of the conditions of the second normal form is that the conditions of the first normal form should be met, which includes that each column should have a unique value and non-nested data.
Here’s a table from a relational database representing a car rental system. PK means primary key.
What type of dependency does this table have?
A transitive dependency occurs when a subset of non-key columns depends on some non-key columns. Here the columns customer_name and customer_phone_number depend on the non-key column customer_id.
Consider the following ER diagram for hotel reservation and billing (source):
You want to convert it to a star schema to enable the data analyst to analyze the details of an entire stay such as duration and the detailed charges and how these details vary from one hotel’s location to another, depending on the dates of reservation and on the guest information.
Which of the following best describes what the fact table of the star schema should contain?
Great job! The fact table should contain the business measures generated by a business event at the lowest possible level of detail. In this case, each row in the fact table corresponds to an entire stay at a hotel and contains the facts generated by this event such as the charges and number of nights. The dimension tables contain the descriptive details that correspond to the guests, hotel rooms, hotel and arrival date.
What is the primary characteristic of a dimension table in a star schema?
Nice job! The dimension tables provide the descriptive context for the facts stored in the fact table, which enables grouping or filtering the results of an analytical query.
What is a key difference between Inmon and Kimball approaches to data warehousing?
Inmon emphasizes a highly normalized data model for ensuring data integrity and consistency, whereas Kimball emphasizes dimensional modeling within the data warehouse itself for quicker access to analytics.
Consider again the following ER diagram for hotel reservation and billing:
Let’s say you want to convert this model to a data vault model. You decided to start with two hubs: one hub that represents the guests another hub that represents an entire stay at the hotel, as shown here:
In addition to the load_date and record_source, what does each hub table also contain?
Great job! The hub should contain the business key that is used by the business to identify the business concept. The primary key should be calculated by passing the business key through a hash function.
Let’s continue with the same example of the previous question. In a data vault model, how can you encode the relationship between the two hubs “H_guest” and “H_stay” ?
Great job! The link table in a data vault model is used to capture the relationships between the different business concepts or hubs.
What is the primary purpose of the staging area in a Data Vault architecture?
The staging area is designed to load raw data directly from source systems in an insert-only manner, without altering the data or enforcing business logic.
What is the significant advantage of modeling your data as One Big Table (OBT)?
By de-normalizing data into a single wide table, OBT eliminates the need for performing complex joins, which can significantly speed up analytics queries.
