1. Exploring Conceptual and Logical Data Models
Welcome back! After introducing data models, let's explore the different types. We'll learn how to differentiate them, and determine when to apply each. Let's dive in.
2. What is a conceptual model?
Imagine you're planning to build a house. Before laying the foundation, buying materials, or hiring workers, we should sketch a rough design idea first.
With just the basic outlines, this high-level drawing is like a conceptual data model in the data world. It provides a broad overview of what we want to achieve.
3. What is a logical data model?
Continuing with our house analogy, after the rough sketch, we would need a detailed blueprint with information on room sizes, window placements, etc. A logical data model is very similar. It's more detailed than the conceptual model and sets the stage for actual construction.
4. Applying the conceptual model
Remember our e-commerce platform from the previous video? Let's apply the conceptual model to it. We will split the data, focusing on primary entities like products, customers, and orders.
5. Applying the conceptual model (1)
Considering how these entities relate, we see that customers generate orders, and orders contain products.
The conceptual model is a high-level overview of the primary data entities. We do not need more details than that for a conceptual model, just like when initially sketching the layout for a house design we were not thinking about specific measurements and dimensions of that house.
6. Applying the logical model
Moving to the logical model, we detail entities with attribute definitions. The logical model will need to describe the attributes of each entity.
For instance, for orders, there is an invoice ID, prices, quantities, etc. Orders will include all the attributes generated by the customer. One customer could place multiple orders, generating a new invoice ID for an order.
7. Applying the logical model (1)
This leads us to define relationships by adding cardinality. In data modeling, relationship cardinality is the number of times entities are associated; one-to-one, one-to-many, or many-to-many.
In our e-commerce example, one customer can generate multiple orders. Each order can contain many products, and one product can appear across various orders. This setup indicates a one-to-many relationship from orders to products.
This logical model is like the blueprint from our analogy of the house, where we were giving a measurement to each part.
8. Implementing the logical model
Do you recall what the original e-commerce online retail entity contains? Let's refresh our memories. We can run a straightforward SQL query to grasp the available data, which fetches everything from the entity.
The results allow us to identify the attributes of the entities we defined previously. For example, there is a customer ID and the country from where this customer is from.
9. Implementing the logical model (1)
For our logical data model, we'll initiate it by creating the customers table. To create a new table, we need to know the data types of the entity attributes.
We will write the script to describe the table, as we learned previously.
The result allows us to see the data type of each column currently existing in the e-commerce online retail table, so we can create the customers table following the same structure.
10. Implementing the logical model (2)
Now that we know all the details, let's use the SQL command CREATE OR REPLACE TABLE, followed by the name of the new entity and the list of attributes. Upon execution of the create table command, we establish our entity's structure based on the logical model's specifics.
11. Terminology and functions overview
Let's take a moment to review the new concepts we have learned so far.
12. Let's practice!
We've come a long way! Next up, we'll dive into the final model, the physical model. But first, let's put your knowledge into practice.