Transcript Star-Schema - SaLearningSchool.com

SKILLS TO BE A DATABASE PROFESSIONAL
 Sayed








Ahmed
Computer Engineering, BUET, Bangladesh
MSC, Computer Science, U of Manitoba, Canada
Software Engineer/Developer, Canada
Owner/President/Architect/Developer
Justetc (Just et cetera) Technologies
http://www.justetc.net
http://sayed.justetc.net
[email protected]
NOTE
Still under construction
 Will be updated later

MUST WATCH:PREREQUISITE
In Bengali, Fundamentals of Database
Management Systems
 In English, Fundamentals of Database
Management Systems

LOGICAL DATA MODELING

Logical Data Modeling: Logical Database Design Steps: RDBMS


http://salearningschool.com/displayArticle.php?table=Articles&articleID=773
Logical Data Modeling












Identify major entities
Det ermine relationships between entities
Determine primary and alternate keys
Determine foreign keys
Determine key business rules
Add remaining attributes
Validate user views through normalization
Determine domains
Determine triggering operations
Combine user views
Integrate with existing data models
Analyze for stability and growth
LOGICAL MODEL INTO THE REAL DATABASE SYSTEM IDENTIFY TABLES

Translate Logical Model into the Real Database System Identify
tables












Identify columns
Adapt data structure to product environment
Design for business rules about entities
Design for business rules about relationships
Design for additional business rules about attributes
Tune for scan efficiency
Define clustering sequences
Define hash keys
Add indexes
Add duplicate data
Redefine columns
Redefine tables
SPECIAL DESIGN CHALLENGES

Design for Special Design Challenges
 Provide
for access through views
 Establish security
 Cope with very large databases
 Access and accommodate change
 Anticipate relational technology evolution
3-NF NORMALIZATIONS
http://en.wikipedia.org/wiki/Third_normal_for
m
 Boyce/Codd and Fourth Normal Form

 http://salearningschool.com/displayArticle.php?tab
le=Articles&articleID=640

Normalization in Relational DBMS Systems
 http://salearningschool.com/displayArticle.php?tab
le=Articles&articleID=639
NORMALIZATION (1NF TO 5TH NF)

Normalization (1NF to 5th NF)
 http://salearningschool.com/displayArticle.php?tab
le=Articles&articleID=600
MODELS

Conceptual, logical, and Physical
 http://en.wikipedia.org/wiki/Logical_data_model
EXAMPLES OF DATA MODELS

Must Watch: Understanding Models
 http://www.learndatamodeling.com/cdm.php#.UiK
HVz_OCys
TOOLS THAT YOU SHOULD LEARN
Tools that You Should Learn
 Just learn them
 If you are good with DBMS theories, they will
not be difficult, you can do it mostly on your
own

ER-STUDIO
http://www.embarcadero.com/products/erstudio
 http://en.wikipedia.org/wiki/ER/Studio

ER-STUDIO
ER/STUDIO DATA ARCHITECT

Universal Mappings Map between and within
conceptual, logical and physical model objects to view
upstream or downstream "Where Used" Analysis Display
mapping between conceptual and logical models and
their implementations across physical designs Visual
Data Lineage Visually document source/target mapping
and sourcing rules for data movement across systems
Round-trip Database Support Round-trip database
support for forward and reverse engineering Advanced
Compare and Merge Enable advanced, bidirectional
comparisons and merges of model and database
structures
ER/STUDIO PORTAL
ER/STUDIO PORTAL

Structured Browsing & Navigation Provide a webbased navigation of the repository diagrams
Technical Reports Pre-installed for implementation
details such as data types, column width, column
names, how objects are related, data lineage
between models and security classification
information Automatic Data Synchronization
ER/Studio diagrams and objects are synchronized
to the Portal on an administrator controlled
schedule. Advanced Searching Wildcard searching
with the ability to limit the search to specific object
types
ER/STUDIO REPOSITORY
ER/STUDIO REPOSITORY

Concurrent Model and Object Access Allows real-time
collaboration between modelers working on data models
down to the model object level Reviewing Changes and
Resolving User Conflict Conflict resolution through simple
and intelligent interfaces to walk users through the discovery
of differences Version Management Manages the individual
histories of models and model objects to ensure incremental
comparison between, and rollback to, desired diagrams
Component Sharing and Reuse Pre-defined Enterprise Data
Dictionary that eliminates data redundancy and enforces
data element standards Security Center Groups Streamline
security administration with local or LDAP groups improving
productivity and reducing errors
ER/STUDIO BUSINESS ARCHITECTS
Skip this
 Conceptual Model Creation Supports high-level
conceptual modeling using elements such as
subject areas, business entities, interactions,
and relationships Process Model Creation
Support for straightforward process modeling
that uses standard elements such as
sequences, tasks, swim lanes, start events,
and gateways

ER/STUDIO SOFTWARE ARCHITECT
Skip this
 Model Driven Architecture & Standards
Supports Unified Modeling LanguageTM(UML®
2.0 ), XML Metadata Interchange (XMI® ),
Query/ Views/Transformations (QVT) and
Object Constraint Language (OCL) Model
Patterns Powerful re-use facilities to jumpstart
projects through predefined patterns.

ER-WIN








http://en.wikipedia.org/wiki/CA_ERwin_Data_Modeler
Logical Data Modeling: Purely logical models may be created, from which physical models may be
derived. Combinations of logical and physical models are also supported. Supports entity-type and
attribute logical names and descriptions, logical domains and data types, as well as relationship
naming.
Physical Data Modeling: Purely physical models may be created as well as combinations of logical and
physical models. Supports the naming and description of tables and columns, user defined data
types, primary keys, foreign keys, alternative keys and the naming and definition of constraints.
Support for indexes, views, stored procedures and triggers is also included.
Logical-to-Physical Transformation: Includes an abbreviation/naming dictionary called "Naming
Standards Editor" and a logical-to-RDBMS data type mapping facility called "Datatype Standards
Editor", both of which are customizable with entries and basic rule enforcement.
Forward engineering: Once the database designer is satisfied with the physical model, the tool can
automatically generate a SQL Data Definition Language (DDL) script that can either be directly
executed on the RDBMS environment or saved to a file.
Reverse engineering: If an analyst needs to examine and understand an existing data structure,
ERwin will depict the physical database objects in an ERwin model file.
Model-to-model comparison: The "Complete/Compare" facility allows an analyst or designer to view
the differences between two model files (including real-time reverse-engineered files), for instance to
understand changes between two versions of a model.
An "Undo" feature is available in version 7.
POWER-DESIGNER














http://en.wikipedia.org/wiki/PowerDesigner
PowerDesigner includes support for:
Business Process Modeling (ProcessAnalyst) supporting BPMN
Code generation (Java, C#, VB .NET, Hibernate, EJB3, NHibernate, JSF, WinForm
(.NET and .NET CF), PowerBuilder, ...)
Data modeling (works with most major RDBMS systems)
Data Warehouse Modeling (WarehouseArchitect)
Eclipse plugin
Object modeling (UML 2.0 diagrams)
Report generation
Supports Simul8 to add simulation functions to the BPM module to enhance
business processes design.
Repository
Requirements analysis
XML Modeling supporting XML Schema and DTD standards
Visual Studio 2005 / 2008 addin
DATAWAREHOUSE SCHEMAS
Datawarehouse Schemas
SNOWFLAKE SCHEMA VS STAR SCHEMA

http://www.diffen.com/difference/Snowflake_
Schema_vs_Star_Schema
SNOWFLAKE SCHEMA VS STAR SCHEMA
SNOWFLAKE SCHEMA VS STAR SCHEMA
DATAWAREHOUSE VS OLTP
In School, you may study a bit on Datawarehouse
However, you may not learn that though there are very few opportunities but the
successful professional are highly paid
DATA WAREHOUSE

http://salearningschool.com/searchResult.php?q
ueryStr=warehouse&submit=Search+Database

How to implement BI/Warehouse
Overview on SAP CRM
Random Information on BI
Steps in Data Warehouse Design and Implementation
What is Data Warehousing?
STAR AND SNOWFLAKE SCHEMAS
http://www.oracle.com/webfolder/technetwork
/tutorials/obe/db/10g/r2/owb/owb10gr2_gs/
owb/lesson3/starandsnowflake.htm
 Star and Snowflake Schemas

 In
relational implementation, the dimensional
designs are mapped to a relational set of tables.
You can implement the design into following two
methods:
 Star
Schema
 Snowflake Schema
STAR SCHEMA



What Is a Star Schema?
A star schema model can be depicted as a simple star: a
central table contains fact data and multiple tables radiate
out from it, connected by the primary and foreign keys of the
database. In a star schema implementation, Warehouse
Builder stores the dimension data in a single table or view
for all the dimension levels.
For example, if you implement the Product dimension using
a star schema, Warehouse Builder uses a single table to
implement all the levels in the dimension, as shown in the
screenshot. The attributes in all the levels are mapped to
different columns in a single table called PRODUCT.
EXAMPLE: STAR SCHEMA
WHAT IS A SNOWFLAKE SCHEMA?



What Is a Snowflake Schema?
The snowflake schema represents a dimensional model
which is also composed of a central fact table and a set
of constituent dimension tables which are further
normalized into sub-dimension tables. In a snowflake
schema implementation, Warehouse Builder uses more
than one table or view to store the dimension data.
Separate database tables or views store data pertaining
to each level in the dimension.
The screenshot displays the snowflake implementation
of the Product dimension. Each level in the dimension is
mapped to a different table.
SNOW-FLAKE SCHEMA
WHEN TO USE STAR/SNOW-FLAKE SCHEMAS
Ralph Kimball recommends that in most of the other cases, star
schemas are a better solution. Although redundancy is reduced in a
normalized snowflake, more joins are required. Kimball usually
advises that it is not a good idea to expose end users to a physical
snowflake design, because it almost always compromises
understandability and performance.
WHEN DO YOU USE SNOWFLAKE SCHEMA IMPLEMENTATION?



When do you use Snowflake Schema Implementation?
Ralph Kimball, the data warehousing guru, proposes three cases where snowflake
implementation is not only acceptable but is also the key to a successful design:
Large customer dimensions where, for example, 80 percent of the fact table
measurements involve anonymous
visitors about whom you collect little detail, and 20 percent involve reliably
registered customers about
whom you collect much detailed data by tracking many dimensions


Financial product dimensions for banks, brokerage houses, and insurance
companies, because each of
the individual products has a host of special attributes not shared by other products


Multienterprise calendar dimensions because each organization has idiosyncratic
fiscal periods,
seasons, and holidays
GOT QUESTIONS?
http://ask.justetc.net