MSBA$305-$ HOA#$1$ and HOA#$2$ Data modeling Exercise $-$ Heinz J$.$ Schwarz

HOA#$1-$ Create an E$/$R diagram and relational schema based on the information below.

HOA#$2-$ Create a dimensional model based on the relational schema created in HOA#$1$

source: TUN Gym Fitness Database

Requirements The following are the requirements for the Gym Fitness Database

For each MEMBER we keep track of the unique MemdID, a well as Name, Zip, and the Date the

membership was paid

For each MEMBERSHIP type we keep track of the unique Mid, as well as MName and Price

For each PASS CATEGORY we keep track of the unique PassCatID, as well as PCName and Price

For each ONE DAY PASS we keep track of the unique PassID and Date

For each MERCHANDISE item we keep track of the unique MrchID, as well as Name and Price

For each sale TRANSACTION we keep track of the unique Tid and Date

Each member pays for exactly one membership type; each membership type has at least one

member but can have many members

Each member can buy many day passes but does not have to buy any, each day pass was bought

by exactly one member

Each day pass belongs to exactly one pass category; a pass category can have many individual

day passes issued for it but does not have to have any

Each sale transaction involves exactly one member; each member can be involved in many sale

transactions but does not have to be involved in any

There can be sales transactions to non$-$members, i$.$e$.$ Gift Cards

Each merchandise item is sold via at least one sale transaction, but it can be sold via many sale

transactions; each sale transaction involves at least one merchandise item but can involve many

merchandise items

Every time a merchandise item is sold via a sale transaction, we keep track of the quantity $($how

many instances of that particular merchandise item were sold via that particular sale

transaction$)$

Your task is to create an ER Diagram based on these requirements and then map the E$/$R diagram

into a Relational Schema $($HOA#$1)$ and Dimensional or Star Schema $($HOA#$2).$ You can use any tool

you prefer $($if you do a web search, you will find numerous free ER $/$ UML modeling tools$)$ but my

recommendation is to use ERDplus, which is free and available here:

ERDPlus ${?}_{(})$