STAR SCHEMA
A database schema is the blueprint of a database system that defines how data is stored and organized in a database. It provides the logical relationships between tables in a database.
What is a database?
A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database can be controlled by a database management system (DBMS).
A database schema contains schema objects like tables, fields, packages, views, relationships, primary key, foreign key.
Types of Database Schema
Database schema is divided into 3 types.
Physical schema:
It specifies how data is stored physically on a storage system or disk storage in the form of files. Designing the database at physical level is called physical schema.
Logical schema:
It specifies the logical constraints that need to be applied to store the data.
View schema:
It generally describes the end-user interaction with the database systems. The view level of the database is called view schema.
Database Schema Designs
- Flat Model
- Hierarchical Model
- Network Model
- Relational Database
- Star Schema
- Snowflake Schema
The well known and mostly used designs among all the database schema is star schema. In this article you can get an overview of star schema.
A data warehouse schema is a combined collection of data from various sources. Among all the data warehouse star schema is the basic model. In star schema data modeling there can be one or more fact tables that index an unlimited number of dimension tables. Every dimension table is connected to the fact table with a foreign key.
Fact Table:
A fact table is a table that contains facts and is linked to dimensions. There are two types of columns in a fact table
- Fact columns and foreign keys to the dimension table
- Fact tables primary key is a composite key made up of all the foreign keys.
Dimension Table:
A dimension table stores attributes or dimensions that determine objects in a fact table. A dimension is a data classification architecture made up of one or more hierarchies.
In a fact table there are both numeric and dimension attribute values. Numeric values are unique and have no relation with the data in the other rows whereas the dimension attribute values are connected to the dimension table using a foreign key.
From the above picture we can see that orderid, order profit, order quantity are numerical columns. Item id, warehouse id, employee id, date id, customer id are dimension attributes and they are further connected to dimension tables using foreign keys. After that the entire structure looks like a star. So it is called a star schema.
Characteristics of a Star Schema:
- Centipede Schema is the word used to describe a star schema with several dimension tables.
- In the star schema each dimension is represented using a single dimension table.
- The collection of attributes are stored in a dimension table.
- The dimension table is connected to the fact table using a foreign key.
- The dimension tables are not connected to each other.
- The star structure is simple to comprehend and optimizes disc consumption.
- There is no normalization in dimension tables.
Advantages of star schema:
- Simple and efficient querying
- Streamlined business reporting logic
- In-built referential integrity
- Maintenance and load performance
- Easy to understand
Disadvantages of star schema:
- Star schema is severely denormalized, data integrity is not property enforced.
- As a normalized data model, it is not as versatile in terms of analytical demands.
- Star schema don't usually encourage many-to-many linkages among business entities.
Do Checkout
To get deeply indulged into AI visit TestAing
References
https://www.javatpoint.com/database-schema
https://hevodata.com/learn/star-schema-data-modelling/#ad
https://www.techtarget.com/searchdatamanagement/definition/dimension-table
-Grandhi Priya