Babysitter Example Dimensional Modeling Babysitter Service The MISSA

Babysitter Example Dimensional Modeling

Babysitter Service The MISSA Service Club wants to run a babysitting service. Customers call to request a sitter and the Club Coordinator assigns an employee to sit for the customer from a list of employees available for the particular day requested.

Usage n n n Customers call in to request an appointment The coordinator checks to see if the customer is registered and enters the appointment in the system The coordinator finds an employee to take the appointment The club sends a confirmation to the employee and the customer Once the engagement is complete the club bills the customer for the service.

ERD Customer CNumber Last. Name First. Name Address City State Zip Phone Job. Number ENumber CNumber Date Time. Begin Time. End Rate Notes Employee ENumber Club. Number Last. Name First. Name Address City State Zip Phone Days. Available Club. Number Club. Name Club. Account

Entity Relationship Model MINIMIZES DATA REDUNDANCY Easier to insert, delete and update data n n n Each “thing” in the system has its own table Each row in the table is uniquely identified with a primary key Attributes (variables) are properties of the entity Rows in different tables are connected by matching primary keys Retrievals use professionally written, sometimes complex SQL statements

Dimensional Model Customer. Key Last. Name First. Name Address City State Zip Phone Date. Key SQLDate Day. Name Holiday Job. Number Employee. Key Customer. Key Club. Key Date. Key Time. Begin Time. End Amount Duration Employee. Key Club. Number Last. Name First. Name Address City State Zip Phone Club. Key Club. Name Club. Account

Dimensional Model MAXIMIZE RETRIEVAL EFFICIENCY Produces simpler, faster retrievals n One central fact table per model ¨A fact represents an event or item that the user wants to track ¨ Fact data is usually numeric and additive n n Descriptive properties are connected with dimension tables Queries involve simple SQL code written by users