Databases and Data Warehousing: Keys, ERD Rules, Normalization and Dimensional Design

Computer science8.27 KB

Summary

The document covers core database concepts for CSC 5301, including keys, relationship and foreign key placement, and normalization up to 3NF. It then introduces data warehousing: dimensional modeling with star and snowflake schemas, the Kimball design steps, fact and dimension types, fact additivity, slowly changing dimensions, ETL, table size estimation, hotel calculations, and sample SQL queries.

Key points

  • A primary key (PK) uniquely identifies each row and can never be NULL.
  • A foreign key (FK) references a key in another table and enforces referential integrity.
  • In a 1:N relationship, the foreign key is placed on the many (N) side.
  • 1NF requires atomic values with no repeating groups; 2NF removes partial dependencies; 3NF removes transitive dependencies.
  • A data warehouse is subject-oriented, integrated, time-variant, and nonvolatile, supporting decision making.
  • Kimball's four design steps are: business process, declare grain, identify dimensions, identify facts.
  • Additive facts can be summed across all dimensions; semi-additive facts are not summed across time; non-additive facts (such as rates and GPA) cannot be summed.
  • SCD Type 1 overwrites old values, while SCD Type 2 adds a new row with a new surrogate key to preserve history.

Test yourself

CSC 5301 | Databases and Data Warehousing

Keys • ERD Rules • Normalization

Keys

PK: uniquely identifies each row; never NULL.
Candidate key: minimal set that uniquely identifies a row.
FK: references a key in another table; enforces referential integrity.
Composite key: two or more columns jointly identify a row.
Natural key: real business identifier (e.g., student_id).
Surrogate key: generated identifier without business meaning.

Relationships and FK Placement

1:N → place FK on the many (N) side: STUDENT.major_id → MAJOR.major_id.
M:N → associative table with both FKs; combined PK usually.
ENROLLMENT(student_id, section_id, grade); PK(student_id, section_id).
1:1 → FK on one side, usually UNIQUE.
Composite FK → include every referenced PK column: SECTION(year, term) → SEMESTER(year, term).
Repeated meetings for the same student/instructor need a meeting_id so each event is retained.

Normalization

Reduces redundancy and insert/update/delete anomalies.

  • 1NF: atomic values; no repeating groups.
  • 2NF: 1NF + every non-key attribute depends on the whole candidate key; no partial dependency.
  • 3NF: 2NF + no transitive dependency of non-key attributes on a key.

Partial: part of a composite key → non-key attribute.
Transitive: key → non-key A → non-key B. Example: student_id → major_id → major_name; store major_name in MAJOR.
An associative entity resolves M:N and may store relationship facts (e.g., grade).

CSC 5301 | Data Warehousing: Dimensional Design

Core Terms

Data warehouse (DW): subject-oriented, integrated, time-variant, nonvolatile data for decision support.

OLTP: daily transaction processing. | OLAP: multidimensional analysis and complex queries.

Data mart: subset of a DW for one subject/process.

Dimensional model: measurements in fact tables; descriptive context in dimensions.

Star schema: central fact joins dimensions directly. | Snowflake: normalized dimensions.

Kimball: Four Design Steps

  1. Business process: activity to analyze.
  2. Declare grain: exactly what ONE fact row represents. Define grain first.
  3. Identify dimensions: who, what, where, when?
  4. Identify facts: numerical measurements at that grain.

Dimensions describe; facts measure.

Facts, Dimensions and Grain

Fact table: business-event measurements plus dimension foreign keys.
Dimension table: descriptive attributes used to filter, group, label, and analyze facts.
Grain: precise meaning of one fact row. Each fact table has one grain.
Star relationship: one dimension member → many fact rows.

Fact Table Types

  • Transactional: one row per event or transaction line.
  • Periodic snapshot: measurements per entity per period (e.g., hotel room per day).
  • Accumulating snapshot: one process row updated through its milestones.
  • Factless: records event or coverage without numeric measures.

Use separate fact tables for separate processes or grains.

Grain Examples

  • FactEnrollment: one student × course-section offering.
  • FactReservation: one room booked within a reservation.
  • FactDailyOccupancy: one room × day.
  • FactServiceUsage: one service-use transaction.

Fact Additivity

  • Additive: sum across all dimensions (e.g., revenue).
  • Semi-additive: sum across some dimensions, but commonly not across time (e.g., account balance).
  • Non-additive: cannot meaningfully sum across dimensions (e.g., percentages, rates, GPA). Derive ratios or averages from underlying values.

Dimension Types and Analysis

  • Degenerate: transaction identifier in fact, no separate dimension (reservation number).
  • Role-playing: one physical dimension used in different roles (booking date and check-in date → DimDate).
  • Junk: combines miscellaneous low-cardinality flags or indicators.
  • Conformed: shared across fact tables/marts with consistent meaning and attributes.
  • Hierarchy: ordered levels for analysis, e.g., Country → Region → City.
  • Flag: Yes/No or True/False condition or status.
  • Roll-up: aggregate detail to a higher summary level.
  • Drill-down: move from summary to increasing detail.

Slowly Changing Dimensions (SCD)

SCD manages changes to descriptive dimension attributes over time.

  • Type 0: retain original; ignore later changes.
  • Type 1: overwrite old value; no history preserved.
  • Type 2: add a new dimension row with a new surrogate key; preserve history.

In Type 2, the old row is retained (often with an end date and a noncurrent flag) and a new row is added with a new surrogate PK, effective start date, and current flag. Historical facts keep the key for their version; new facts use the current version.

ETL and Data Quality

ETL = Extract, Transform, Load: collect source data, prepare it, and load the warehouse.

  • Extract: retrieve relevant data from operational databases or external sources.
  • Transform: clean, validate, standardize, integrate, calculate measures, detect history changes, resolve surrogate keys.
  • Load: insert or update transformed warehouse dimensions and facts.

Data quality: accuracy, completeness, consistency, validity, and fitness for intended use.

CSC 5301 | Estimation, Hotel Formulas and SQL

Table Size Estimation

  • Dimension rows ≈ distinct dimension members (+ SCD Type 2 history).
  • Transaction fact rows = transactions per period × number of periods.
  • Periodic snapshot rows = entities observed × reporting periods.
  • Accumulating snapshot rows = distinct process instances.

Hotel Calculations

  • Available room-nights = rooms × days.
  • Occupied room-nights = available room-nights × occupancy rate.
  • Reservations ≈ occupied room-nights ÷ average stay (nights).
  • Service transactions = reservations × average service transactions per reservation.

Separate reservation, occupancy, and service fact tables because their grains differ.

SQL Quick Rules

  • JOIN combines related rows. SUM totals numeric values. AVG averages non-NULL values.
  • COUNT(*) counts rows; COUNT(column) counts non-NULL values.
  • WHERE filters before grouping; GROUP BY forms groups; HAVING filters after aggregation.
  • ORDER BY sorts; DISTINCT removes duplicates. Group non-aggregated output columns.
  • Join dimensions on matching keys.

Total Revenue by City (2025)

SELECT h.city, SUM(f.total_amount) AS total_revenue
FROM FactReservation f
JOIN DimHotel h ON f.hotel_key = h.hotel_key
JOIN DimDate d ON f.booking_date_key = d.date_key
WHERE d.year = 2025
GROUP BY h.city;

Average Grade by Major

SELECT s.major, AVG(f.grade) AS average_grade
FROM FactEnrollment f
JOIN DimStudent s ON f.student_key = s.student_key
GROUP BY s.major;

Reservations per Hotel

SELECT h.hotel_name, COUNT(*) AS reservations
FROM FactReservation f
JOIN DimHotel h ON f.hotel_key = h.hotel_key
GROUP BY h.hotel_key, h.hotel_name;

Download

PDF Word HTML

FAQ

What is the difference between a primary key and a foreign key?

A primary key uniquely identifies each row in its own table and is never NULL. A foreign key references a key in another table and enforces referential integrity.

What is the difference between a star schema and a snowflake schema?

A star schema has a central fact table that joins directly to its dimensions. A snowflake schema uses normalized dimensions, so dimension tables are split into related sub-tables.

What does SCD Type 2 do?

SCD Type 2 keeps history by retaining the old dimension row and adding a new row with a new surrogate key, an effective start date, and a current flag.

Test yourself

Flashcards

Click a card to see the answer.

Quiz

1. Where is the foreign key placed in a 1:N relationship?
2. Which normal form removes transitive dependencies?
3. What is the grain of a fact table?
4. Which type of fact is average GPA?
5. Which SCD type overwrites the old value without preserving history?

Questions generated by AI from the document: check the answers against the text.