StudentShare
Contact Us
Sign In / Sign Up for FREE
Search
Go to advanced search...
Free

Using a Star Database Schema - Research Paper Example

Cite this document
Summary
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,…
Download full paper File format: .doc, available for editing
GRAB THE BEST PAPER92.3% of users find it useful
Using a Star Database Schema
Read Text Preview

Extract of sample "Using a Star Database Schema"

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.

Read More
Cite this document
  • APA
  • MLA
  • CHICAGO
(“Using a Star Database Schema Research Paper Example | Topics and Well Written Essays - 500 words”, n.d.)
Retrieved from https://studentshare.org/information-technology/1627244-using-a-star-database-schema
(Using a Star Database Schema Research Paper Example | Topics and Well Written Essays - 500 Words)
https://studentshare.org/information-technology/1627244-using-a-star-database-schema.
“Using a Star Database Schema Research Paper Example | Topics and Well Written Essays - 500 Words”, n.d. https://studentshare.org/information-technology/1627244-using-a-star-database-schema.
  • Cited: 0 times

CHECK THESE SAMPLES OF Using a Star Database Schema

Data Warehouse and Business Intelligence in Suite Spot

In both the business intelligence and data warehousing, a star schema refers to the simplest structure of a dimensional model.... ?In addition, a star schema is designed by combining each fact with its related dimensions (Rouse).... In fact, there are many benefits of using a BI system in an organizational arrangement.... In view of the fact that the business executives are not computer experts so these services are provided them using a graphical user interface through which they can use the functionality behind the system (Dayal, Castellanos and Simitsis; Edger-Online; University of Minnesota)....
3 Pages (750 words) Research Paper

Data warehouse presentation evaluation

The major benefit of a Bottom-Up Approach is user-friendly, flexible data structure using dimensional, star schema models.... The first several data marts are also designed in third normal form but deployed using star schema physical models.... Generally a data warehouse is a trace of an enterprise's past transactional and operational information that is stored in a specially designed database with appropriate facilities for data analysis and reporting. There is one common idea in the basis of data warehousing design: first data should be integrated from distributed and differently structured databases and then it should be picked out for purposes of reporting, decision support, analysis or control....
2 Pages (500 words) Essay

Sports Club Entity Model

Choosing a database model is of greater importance.... The database for family budgeting system would include features like atomic values, primary and foreign key relationships, and normalization process to reduce redundancy and anomalies of insertion, modification and deletion errors, row and column structure of the database tables and all kind of relationships are possible, one-to-one, one-to-many, many-to-one and many-to-many....
8 Pages (2000 words) Essay

Knowledge based systems

These definitions give a consensus which is broad in nature but gives us an ample scope for discussion as to the meaning of very key terms A knowledge base is special database that is used primarily for management of knowledge.... The paper “Knowledge based systems” presents a history of the start of knowledge base use and the improvements that has been made to it....
11 Pages (2750 words) Assignment

The Impact of Political Wrangling in the Implementation

The simplest type of data warehouse is the star where there is one set of hierarchies feeding into and out of the warehouse useful these days in an individual department.... The focus of this paper "The Impact of Political Wrangling in the Implementation" is on the types of warehouse structure....
7 Pages (1750 words) Research Paper

HTML, SQL, and PHP Programming Languages

SQL covers data insertion, update and delete, query, schema creation and adjustment, and control data access.... According to Beaulieu (2009), SQL (Structured Query Language) is another programming language used to manage data held in RDBMS (Relational database Management Systems).... PHP is a fast programming language offering stability in various websites created using the language....
9 Pages (2250 words) Essay

Apache and Hadoop as a New Type of Database

the essay "Apache and Hadoop as a New Type of database" outlines that Apache and Hadoop are the open-source servers that have allowed organizations to seamlessly integrate their big data infrastructure with their business intelligence both coming as a package and utilized for a parallel file system.... nbsp;… However, a new type of database known as Apache and Hadoop have been developed to challenge these databases (“Business Intelligence-BI”)....
5 Pages (1250 words) Essay

Using Rank Based KNN Queries Processing to Reduce Location Uncertainty in Wireless Sensor Networks

Imprecise or uncertain data are very common in a number of applications nowadays and this has contributed to the growth of several uncertain database frameworks.... Imprecise or uncertain data are very common in a number of applications nowadays and this has contributed to the growth of several uncertain database frameworks.... Query processing and data collection in a sensor network are challenging research areas in sensor network database management....
17 Pages (4250 words) Term Paper
sponsored ads
We use cookies to create the best experience for you. Keep on browsing if you are OK with that, or find out how to manage cookies.
Contact Us