SCHEMA

Posted by sayovel

3 months ago

SCHEMA

What is a Star Schema? A star schema is a way to organize a database that makes it easier to analyze data. It consists of: <ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l3 level1 lfo1;tab-stops:list .5in">Central Table (Fact Table): Imagine you have one big table in the middle that holds the main data you want to analyze, like sales transactions. This table is called the "fact table."</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l3 level1 lfo1;tab-stops:list .5in">Surrounding Tables (Dimension Tables): Around this big table, you have smaller tables that provide more details about the data, like products, dates, and stores. These are called "dimension tables."</li> </ul> How it Looks <ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l0 level1 lfo2;tab-stops:list .5in">The central fact table connects to each dimension table directly, forming a shape that looks like a star, hence the name "star schema."</li> </ul> Example <ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l1 level1 lfo3;tab-stops:list .5in">Fact Table (Sales): Contains information about each sale, like the amount sold, quantity, and sale date.</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l1 level1 lfo3;tab-stops:list .5in">Dimension Tables:</li> <ul type="circle"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l1 level2 lfo3;tab-stops:list 1.0in">Product Table: Details about each product, like name and category.</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l1 level2 lfo3;tab-stops:list 1.0in">Date Table: Information about dates, like year, month, and day.</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l1 level2 lfo3;tab-stops:list 1.0in">Store Table: Details about each store, like location and size.</li> </ul> </ul> Why Use a Star Schema? <ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l2 level1 lfo4;tab-stops:list .5in">Simplicity: It is easy to understand and use because everything is directly connected to the central fact table.</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l2 level1 lfo4;tab-stops:list .5in">Efficiency: Queries (questions to the database) are faster because you don’t need to go through multiple layers of tables.</li> </ul> In summary, a star schema organizes data in a simple and efficient way with a big central table (fact table) and smaller tables around it (dimension tables) that provide more details.What is a Snowflake Schema?A snowflake schema is a more detailed way to organize a database for analyzing data. It consists of: <ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l4 level1 lfo1;tab-stops:list .5in">Central Table (Fact Table): Just like in a star schema, there's a main table in the middle that holds the key data you want to analyze, like sales transactions. This table is called the "fact table."</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l4 level1 lfo1;tab-stops:list .5in">Surrounding Tables (Dimension Tables): Around this big table, you have smaller tables that provide more details about the data, like products, dates, and stores. These are called "dimension tables."</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l4 level1 lfo1;tab-stops:list .5in">More Detailed Tables (Normalization): In a snowflake schema, the dimension tables are further split into more tables. This is called "normalization." For example, instead of one big product table, you might have a product table split into product categories and subcategories.</li> </ul>How it Looks<ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l3 level1 lfo2;tab-stops:list .5in">The fact table connects to the dimension tables, and the dimension tables can connect to even more tables, making the shape look more like a snowflake.</li> </ul>Example<ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l2 level1 lfo3;tab-stops:list .5in">Fact Table (Sales): Contains information about each sale, like the amount sold, quantity, and sale date.</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l2 level1 lfo3;tab-stops:list .5in">Dimension Tables:</li> <ul type="circle"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l2 level2 lfo3;tab-stops:list 1.0in">Product Table:</li> <ul type="square"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l2 level3 lfo3;tab-stops:list 1.5in">Split into:</li> <ul type="square"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l2 level4 lfo3;tab-stops:list 2.0in">Product Details Table: Information about each product.</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l2 level4 lfo3;tab-stops:list 2.0in">Category Table: Categories of products.</li> </ul> </ul> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l2 level2 lfo3;tab-stops:list 1.0in">Date Table:</li> <ul type="square"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l2 level3 lfo3;tab-stops:list 1.5in">Split into:</li> <ul type="square"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l2 level4 lfo3;tab-stops:list 2.0in">Year Table: Information about years.</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l2 level4 lfo3;tab-stops:list 2.0in">Month Table: Information about months.</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l2 level4 lfo3;tab-stops:list 2.0in">Day Table: Information about days.</li> </ul> </ul> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l2 level2 lfo3;tab-stops:list 1.0in">Store Table: Details about each store.</li> </ul> </ul>Why Use a Snowflake Schema?<ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l1 level1 lfo4;tab-stops:list .5in">Reduced Redundancy: It reduces duplication of data by splitting tables into more detailed tables.</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l1 level1 lfo4;tab-stops:list .5in">Storage Efficiency: Takes up less storage space because similar data is grouped together.</li> </ul>Drawbacks<ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l0 level1 lfo5;tab-stops:list .5in">Complexity: It can be more complex to understand and use because there are more tables to join.</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l0 level1 lfo5;tab-stops:list .5in">Query Complexity: Queries can be slower and more complex because they have to join more tables.</li> </ul> In summary, a snowflake schema organizes data in a detailed and efficient way by splitting tables into smaller related tables, reducing redundancy but increasing complexity. It looks like a snowflake because of its branching structure.Key Differences<ul><li>Star Schema: Simpler to use, has fewer tables, more redundancy(repeated data), faster queries.</li><li>Snowflake Schema: Complex, has  more tables, less redundancy, slower queries.</li></ul>In essence, the star schema is simpler and easier to use, while the snowflake schema is more detailed and efficient in terms of storage.

0 Comments
45 Views
Share:

Topic Detail

Posted by sayovel

SCHEMA