BALLARI INSTITITE OF TECHNOLOGY & MANAGEMENT
DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING
Steps to Implement Data Warehousing and Data Mining Concepts (22CS73) in
Software
TITLE- Design multi-dimensional data models namely Star, snowflake and Fact
constellation schemas for library management in MySQL
TEAM MEMBERS
USN NAME
3BR22CS001 AADYA PRERNA
3BR22CS002 A BINDU
3BR22CS003 A DIVYA
3BR22CS004 A KAVYA
3BR22CS005 ABDUL MUSAWWIR
Staff-Incharge HoD
Dr Sheetal J [Link]
1) STAR SCHEMA - simple, denormalized dimensions
Grain (fact): one row = one borrowing transaction (a book copy loaned to a member).
Fact table: fact_borrow (measures: borrow_count, days_borrowed, fine_amount,
is_returned)
Dimension tables (denormalized):
dim_book (book attributes + author name, publisher name, genre)
dim_member
dim_date
dim_branch
dim_staff (who processed the transaction)
Concept
fact_borrow → foreign keys to each denormalized dimension (star-shaped).
Dept. of CSE, BITM Page 2
Example queries (star)
Total borrows per month:
SELECT [Link], [Link], COUNT(*) AS borrow_count
FROM fact_borrow f
JOIN dim_date d ON f.borrow_date_id = d.date_id
GROUP BY [Link], [Link]
ORDER BY [Link], [Link];
Top 10 books by borrow count:
SELECT [Link], b.author_name, COUNT(*) AS borrows
FROM fact_borrow f
JOIN dim_book b ON f.book_id = b.book_id
GROUP BY b.book_id
ORDER BY borrows DESC
LIMIT10;
Overdue items (assuming a policy column or expected_return_date stored elsewhere — if
days_borrowed blank and now > allowed days)
SELECT f.borrow_id, m.first_name, m.last_name, [Link], f.copy_barcode, d.full_date
FROM fact_borrow f
JOIN dim_member m ON f.member_id = m.member_id
JOIN dim_book b ON f.book_id = b.book_id
JOIN dim_date d ON f.borrow_date_id = d.date_id
WHERE f.is_returned = 0 AND DATEDIFF(CURDATE(), d.full_date) > 30; -- example 30-
day policy
Pros / cons
Pros: Simple, fast for reads, fewer joins for common queries.
Cons: Redundancy in dimensions (e.g., author_name repeated across many book rows),
larger storage, more ETL work to keep denormalized attributes consistent.
SCD: Use surrogate keys (book_id) and SCD handling: Type 1 (overwrite) or Type 2
(versioning) depending on requirements.
Dept. of CSE, BITM Page 3
2) SNOWFLAKE SCHEMA — normalized dimensions (good for smaller storage,
normalized author/publisher tables)
Grain (fact): same — one borrowing transaction.
Fact table: fact_borrow_snow
Normalized dimension tree:
dim_book (book-level) → dim_author, dim_publisher, dim_genre
dim_member, dim_date, dim_branch, dim_staff (similar to star but can be normalized
further)
Concept
fact_borrow_snow joins to dim_book, which joins to dim_author and dim_publisher. This
reduces redundancy for authors/publishers that appear across many books.
Example queries (snowflake)
Top authors by borrows (requires extra join):
SELECT a.author_name, COUNT(*) AS borrows
FROM fact_borrow_snow f
JOIN dim_book_nf b ON f.book_id = b.book_id
JOIN dim_author a ON b.author_id = a.author_id
GROUP BY a.author_id
ORDER BY borrows DESC
LIMIT 10;
Dept. of CSE, BITM Page 4
Pros / cons
Pros: Eliminates redundancy, smaller dimension storage, easier maintenance of
author/publisher metadata.
Cons: More joins (join depth), slightly slower read queries.
Use when dimension attributes change frequently and normalization reduces ETL
complexity.
3) FACT CONSTELLATION / GALAXY — multiple fact tables sharing dimension
tables
Use case: You want to analyze both borrowing activity and inventory/acquisition
(purchases/donations), plus perhaps book reservations. Multiple fact tables share
dimension tables like dim_book, dim_date, dim_branch, dim_member.
Facts:
fact_borrow — borrowing transactions (same as before)
fact_acquisition — when library acquires copies (purchase/donation) — contains cost,
supplier, copies_added
fact_inventory_snapshot (optional) — periodic snapshots of available copies per branch
Shared dimensions: dim_book, dim_date, dim_branch, dim_staff, dim_member (for
acquisitions maybe staff who recorded it), and dim_supplier for acquisitions.
Dept. of CSE, BITM Page 5
Example queries (constellation)
Acquisition cost by month:
SELECT [Link], [Link], SUM(a.total_cost) AS cost
FROM fact_acquisition a
JOIN dim_date d ON a.acquisition_date_id = d.date_id
GROUP BY [Link], [Link]
ORDER BY [Link], [Link];
Correlate new acquisitions with borrow spikes:
--For a given book, see acquisitions vs borrows by month
SELECT [Link], [Link],
COALESCE(SUM(ac.copies_added),0) AS copies_acquired,
COALESCE(COUNT(b.borrow_id),0) AS borrows
FROM dim_date d
LEFT JOIN fact_acquisition ac ON ac.acquisition_date_id = d.date_id AND ac.book_id
= 123
LEFT JOIN fact_borrow b ON b.borrow_date_id = d.date_id AND b.book_id = 123
GROUP BY [Link], [Link]
ORDER BY [Link], [Link];
Pros / cons
Pros: Supports multiple business processes, reuses dimensions, flexible for enterprise
analytics.
Cons: More complex ETL and modeling; ensure shared dimensions are consistent.
Dept. of CSE, BITM Page 6
Design & Implementation Notes (applies to all schemas)
1. Surrogate Keys
Use integer surrogate keys (AUTO_INCREMENT) in all dimensions and reference them
in facts. This decouples natural key changes.
2. Date dimension
Populate dim_date with a row per day (e.g., 1900-01-01 to 2099-12-31) and use date_id
as YYYYMMDD (int) or an INT surrogate. Query performance is improved by joining
on integer PKs.
3. Slowly Changing Dimensions (SCD)
Member/book metadata can change:
o SCD Type 1: overwrite (simple)
o SCD Type 2: add effective_from, effective_to, is_current and keep history (more
accurate for historical reporting)
For book editions or changed publisher, prefer SCD2 if you need historical accuracy.
4. Indexing
Put primary key on fact table (auto-increment).
Add composite non-clustered indexes on common query patterns, e.g.:
o fact_borrow (borrow_date_id)
o fact_borrow (book_id, borrow_date_id)
o fact_borrow (member_id, is_returned)
Index dimensions on commonly filtered columns (e.g., dim_book(isbn),
dim_member(member_code)).
5. Partitioning (for large facts)
If fact_borrow grows huge, consider RANGE partitioning on borrow_date_id (by
year/month) to speed deletes/archives:
o MySQL partitioning works best on integer/date columns used in WHERE.
Alternatively, store historical data in separate archive table.
6. ETL
Load dimensions first (dedupe natural keys, create surrogate keys).
Load fact tables referencing dimension surrogate keys.
Validate FK constraints during dev; in heavy ETL loads turn off or delay FK checks but
ensure final referential integrity.
Dept. of CSE, BITM Page 7
7. Storage engine
Use InnoDB (transactions + foreign keys). Consider ROW_FORMAT=COMPRESSED
for large dims if disk is a concern.
8. Denormalization trade-offs
Star = performance, simpler queries.
Snowflake = normalized, less disk, more joins.
Constellation = supports multiple facts & processes.
Minimal ETL / population example (insert date dim then book/member then fact)
INSERT INTO dim_date (date_id, full_date, year, quarter, month, day, day_of_week,
is_weekend)
VALUES (20251207, '2025-12-07', 2025, 4, 12, 7, 7, 1);
INSERT INTO dim_author (author_name) VALUES ('George Orwell');
INSERT INTO dim_publisher (publisher_name) VALUES ('Penguin');
INSERT INTO dim_genre (genre_name) VALUES ('Dystopian');
INSERT INTO dim_book_nf (isbn, title, author_id, publisher_id, publication_year, genre_id)
VALUES ('9780141036144', '1984', 1, 1, 1949, 1);
INSERT INTO dim_member (member_code, first_name, last_name, membership_type,
join_date)
VALUES ('M0001','Aadya','Prerna','student','2024-08-01');
-- Insert a borrow fact
INSERT INTO fact_borrow (borrow_date_id, member_id, book_id, branch_id, staff_id,
is_returned)
VALUES (20251207, 1, 1, 1, 1, 0);
Dept. of CSE, BITM Page 8