Using a Star Database Schema - Research Paper Example

Database Schema Fig A Star schema diagram for an online company Data warehouses form the bases for solid business intelligence solutions. According to Michelle (2007), a data warehouse is a repository for a company’s historical data. And it entails a set of technologies and techniques that are used in management of data (Michelle, 2007, p.1).These data warehouses are made using a dimensional model such as the star schema diagram (SSD) or a relational model like the entity relationship model.

Comparatively SSDs are more preferred as they are easy to comprehend. The main aim of a marketing department adopting the use of a dimensional model is to help decision makers in making strategic decisions without the need of help and support from database experts thereby avoiding extra cost. As a result timely and quality decisions are made.Research by Karen, David and Robert (2006), indicate that denormalization is advocated for in data warehouses as it promotes efficiency of the highest order when queries are run against the warehouse (Karen, David, & Robert, 2006).

The concept of dimensional model was developed to cater denormalized data. A star schema model can arguably be referred to as a constrained ERD model. It consists of central entities known as the fact tables that get business event details. Star schema derives its name from the arrangement of the fact tables and dimensional entities forming a star-like arrangement. The representation of this arrangement diagrammatically constitutes the SSD. As a result of this arrangement around a business fact, SSD model is easy to understand.

Star schemaStar schema data model consists of one or more fact tables referencing any given number of dimension tables. There are several benefits of using star schema. First is convenient for simple queries. The star schema simplifies business logic in the realms of reporting. It also provides high level operation performance as a result of simpler queries execution. Lastly star schemas are deployed in most of OLAP systems to make proprietary OLAP cubes efficiently.The star schema designs in the figure shows various characteristics that include: Each table in the design has an identity primary key.

In data warehouse such as in our case the surrogate primary key is used.According to Fig.1, most of the columns if not all in both the dimensions and fact tables are of null value. This means that the primary key is unique and mandatory. Data in the data warehouse comes from various multiple operational databases and therefore in some instances constraints applied in transactional databases need to be loosened. For instance in the star schema diagram in Fig.1, almost all the columns are of null value due to the fact they might lack data.

Relationships on the parent sides are not mandatory meaning that there is no control of source data unless the orphan records in lower levels of dimensional hierarchy and the fact tables are permitted.Most columns in the fact tables (measures) are numbers.High degrees of data redundancy so as achieve high level of performance. It is the sole reason as to why data warehouse have huge amount of data compared to a transactional database. Redundancy reduces the number of joins required in the execution of a T-SQL query in the data warehouse’s star schema.

Fact tablesA fact table is a collection of keys and measures (operational data). The keys relate each row in the fact table to an associated row in a dimension table. They comprise of values in numerals and dimensional data keys. The fact tables have the capability of having a large collection of records of events at very low level as evident in the figure.In Fig.1 database schema design the primary key of the Categories dimension is the foreign key in the Product fact table. The schema dimensions (Suppliers, Categories Sales Invoices, Current Product List and Products Above Average Price) are mapped to tables and views in the sales database and thereby enable transfer of operational data into the data warehouseDimension tablesIn a database setting the dimension tables have small number of records as compared to the fact tables.

Dimension tables define different forms of data where each entry has attributes that describe the fact data. The simple hierarchy of the dimensions greatly cut down redundancy. Work citedMichelle A. P. (2007, June 20). Discover the Star Schema: A basic schema design for the data warehouse. SQL Server, 29-31. Retrieved from http://sqlmag.com/database-administration/discover-star-schema.Karen C., David S., Robert D. (2006).The impact of alternative diagrams on the accuracy of recall: A comparison of star-schema diagrams and entity-relationship diagrams.

Decision Support Systems, 42(1), 450–468. Retrieved from http://www.sciencedirect.com/science/article/pii/S016792360500031X.