Transcript

Model behavior: An Introduction to data models

Data models:

• Organizes data elements.
• Standardizes relation of data elements to each other.
• Standardizes relation of data elements to properties of real-world entities.

Database models:

• A type of data model.
• Determines a database’s logical structure.
• Determines how data can be stored, organized, and manipulated.

A normalized relational database and normalized star schema are two of the most common database models, each of which as its advantages.

Normalized relational database

• Uses tables to make databases independent of software applications.

Database normalization:

• Process of structuring relational database.
• Reduces data redundancy and improves data integrity.
• Organizes columns or attributes in tables.
• Enforces various data integrity constraints.
• Must meet requirements for previous normalization level before next level can be achieved.

Unnormalized data:

• May or may not have primary key.
• Table does not have duplicate records.

First normal form (1NF):

• Requires primary key.
• Requires all cells in tables to have single values.
• Move attributes that could have multiple values to separate table.
• Enforces various data integrity constraints.
• Create primary key to connect new table to old table.

Second normal form (2NF)

• 1NF plus removing table’s partial dependencies.
• All attributes must depend on entire primary key.

Third normal form (3NF):

• 2NF plus removing all transitive dependencies.
• Attributes may only depend on primary key.

Star schema:

• Post-relational data model.
• More generalized data model than relational data model.
• Most common data model in data warehouses.
• Unnormalized data.
• Consists of at least one fact table that references any number of dimension tables.

Fact table:

• Records measurements for specific events.
• Usually consists of numeric values and foreign key.
• Foreign key links to dimensional table containing descriptive information for numeric values.
• Records measurements at low level of detail or granularity.
• Results in accumulation of many records over time.
• Three types: Transaction, snapshot, and accumulating snapshot fact tables.

Dimension table:

• Has smaller number of records compared to fact table
• Number of attributes in their records can be very large.
• Defines many types of dimensions:
  Time: (most common) Describe time at which events are recorded in fact table.
  Range: Describe range of measurable quantities to simplify reporting.
  Other: Tables for employees, geography, and products.

Comparison:

Advantages of normalized relational database versus star schema:

• Strictly reinforces data integrity to prevent anomalies like one-off inserts and updates.
• Greater flexibility in performing analytical tasks if they follow database model’s business logic.
• More easily supports many-to-many relationships between business entities.

Benefits of star schema versus normalized relational database:

Simpler reporting logic, especially as-of and period-over-period reporting.

Simpler join-logic:

• Improves performance on read-only operations like reporting and queries.
• Improves performance of aggregration operations.
• Efficiently build proprietary cubs for online analytical processing (OLAP) systems.