Problem definition:

Create an entity relationship diagram for the above description in 3rd normal form and derive a relational model from it.

Both primary and foreign keys must be marked.

All necessary attributes must be specified for the tables

The questions are to be answered by SQL statements

If the information is insufficient, assumptions must be made and quoted.

Modeling scenario: Wellness Center For a medium-sized bathing and wellness center a data model is to be created based on the following description. The bath and wellness center is visited by customers. Their names and addresses are stored when seasonal passes are issued. There are numerous possibilities to visit the bath: Weekday discount Regular price Wellness Center - Ticket, 2 hrs 11.50 13.00 Wellness Center - Ticket all-day 16.00 17.50 Eveningticket, from 18:00 13.00 14.50 Groupticket, more than 12 people 14.00 15.00 WellnessNight, Saturday from 18:00 17.00 Wellness Pass (all-day full facility usage) 25.00 27.50 Season Ticket 250.00 Every visitor consumes products, e.g. food and drinks in the in-house restaurants or visits to certain facilities (tanning studios, etc.). These products are always assigned to an organizational unit (OU) such as pool restaurant, sunshine bar, sauna area, sulphur bath, etc. The employees work in the individual Us. Examples for products: 30min solarium, 1h solarium, small beer, Wiener Schnitzel, vegetable strudel, 20min full body massage, 30min aroma therapy. Note: One area (sauna) can comprise several OUs (e.g. the restaurant area includes the pool restaurant and sunshine bar). There are also facilities (and equipment) available in each OU (e.g. tanning beds in the tanning studios, water treatment systems for the individual baths, etc). For these systems (and devices) certain maintenance is mandatory (target maintenance), If the time has come for such a prescribed target maintenance, it is actually carried out (actual maintenance). It is particularly interesting to know which employees have worked on the maintenance and for how long. Of course, more than one person can work on a maintenance. The data model should be able to answer the following questions: 1. What is the annual turnover in the individual areas (entertainment pool, sulphur bath, restaurant, sauna, tanning studio, massage studio)? 2. Which installations (and equipment) are to be maintained in the next quarter (assuming that maintenance is to be carried out at certain times and not on the basis of operating hours)? 3. What is the proportion of regular customers (with seasonal pass) compared to total visitors 4. The sum of the procurement values of all equipment and facilities in the individual OUs 5. Has a customer with a 2h ticket left the bathroom in time? Modeling scenario: Wellness Center For a medium-sized bathing and wellness center a data model is to be created based on the following description. The bath and wellness center is visited by customers. Their names and addresses are stored when seasonal passes are issued. There are numerous possibilities to visit the bath: Weekday discount Regular price Wellness Center - Ticket, 2 hrs 11.50 13.00 Wellness Center - Ticket all-day 16.00 17.50 Eveningticket, from 18:00 13.00 14.50 Groupticket, more than 12 people 14.00 15.00 WellnessNight, Saturday from 18:00 17.00 Wellness Pass (all-day full facility usage) 25.00 27.50 Season Ticket 250.00 Every visitor consumes products, e.g. food and drinks in the in-house restaurants or visits to certain facilities (tanning studios, etc.). These products are always assigned to an organizational unit (OU) such as pool restaurant, sunshine bar, sauna area, sulphur bath, etc. The employees work in the individual Us. Examples for products: 30min solarium, 1h solarium, small beer, Wiener Schnitzel, vegetable strudel, 20min full body massage, 30min aroma therapy. Note: One area (sauna) can comprise several OUs (e.g. the restaurant area includes the pool restaurant and sunshine bar). There are also facilities (and equipment) available in each OU (e.g. tanning beds in the tanning studios, water treatment systems for the individual baths, etc). For these systems (and devices) certain maintenance is mandatory (target maintenance), If the time has come for such a prescribed target maintenance, it is actually carried out (actual maintenance). It is particularly interesting to know which employees have worked on the maintenance and for how long. Of course, more than one person can work on a maintenance. The data model should be able to answer the following questions: 1. What is the annual turnover in the individual areas (entertainment pool, sulphur bath, restaurant, sauna, tanning studio, massage studio)? 2. Which installations (and equipment) are to be maintained in the next quarter (assuming that maintenance is to be carried out at certain times and not on the basis of operating hours)? 3. What is the proportion of regular customers (with seasonal pass) compared to total visitors 4. The sum of the procurement values of all equipment and facilities in the individual OUs 5. Has a customer with a 2h ticket left the bathroom in time