Design the dimension tables needed for an eventual data warehouse;

Design the fact table needed for the data warehouse;

Several items to remember and consider when doing your dimension design:

Don't forget to include your surrogate (aka 'technical') keys;

De-normalize tables where appropriate. You will have third normal form violations in your dimension tables; that is expected in dimension modeling.

Converting Look-up Tables: A few heuristics about de-normalizing:

When a table has a 1:M relationship where a look-up table is the '1' side, put the look-up value in the 'many side' table and get rid of the look-up table;

When a data table has a M:N relationship with a look-up table, and the look-ups have a finite number of possible values, consider putting a field in the data table for each possible look-up value, and make that field a boolean (i.e., Y/N) field;

When a data table has a M:N relationship with another data table, consider using a 'bridge' dimension (hint: you will need at a maximum only one of these in this model);

Fact Table: You should have only ONE fact table for this business process. The challenge will be to decide what the relevant facts are versus what you can derive from the dimension tables.

Temporal Dimensions: Don't forget to include date and time;

Hint #1: Your final dimensional model should NOT have as many tables as the original ERD;

Hint #2: The executives at Sakila probably do not care which copy of a film was rented.

, Simplified ERD of the Sakila Database film address inventory inventory id store film jd address id store id title address 10 address2 staff id descripti on release-year language-id store-'d addressd last update lst update district language original-language-id postal-code nam e rental duraton rentalrate phone last-update last-update - 1. rating > special-feaures last-update film category rental customer id store id first name 1 fim_id rental_id category_id last update rentaldate inventory..id as tomerd return-date - | 1.. * last_name city v city id - address id staff id active film actor t actor id tfilm id country-id last-update last-update createdate - last-update ca tegory categoryid nam e 1st-update last update | actor actor id first-name last-name country country_id country lastupdate last update CIS4400 Dimension Modeling Assignment , Simplified ERD of the Sakila Database film address inventory inventory id store film jd address id store id title address 10 address2 staff id descripti on release-year language-id store-'d addressd last update lst update district language original-language-id postal-code nam e rental duraton rentalrate phone last-update last-update - 1. rating > special-feaures last-update film category rental customer id store id first name 1 fim_id rental_id category_id last update rentaldate inventory..id as tomerd return-date - | 1.. * last_name city v city id - address id staff id active film actor t actor id tfilm id country-id last-update last-update createdate - last-update ca tegory categoryid nam e 1st-update last update | actor actor id first-name last-name country country_id country lastupdate last update CIS4400 Dimension Modeling Assignment