Janet, after years of working as a hairdresser and as a makeup artist, decided to go into business for herself. Over time the business has grown into a spa and beauty retreat and she now employs a few staff. With her growing operation she has identified the need to implement a computerized accounting information system. In order to have a system that meets her needs exactly and also to save cost, she has asked you to draw a simple, customized solution for her business. Her immediate need is for a customer sales information system, so that is what you will now create.

With customer service being so important to Janet, her customers tend to become regulars and she wants to maintain a record of each customer's contact information and preferences so that the spa will know what is important to the customer without having to be told each visit. The customer information that Janet considers important to maintain is the full name, cellphone number, favourite treatment, and special considerations (e.g. some customers have allergies or require fragrance-free products).

For employees, Janet just needs to record the names and cell phone numbers in the database. The spa also has a standard list of services which each have a standard name, description and price.

Janet also wants a database record of each customer visit both for customer billing at the time of service, as well as for future analysis. Janet wants to refer to these sales transactions as "Visits" in the database in order to reflect the spa's focus on customers' experiences. For each of these customer visits it is important to record a unique visit identifier, the date, the customer, the main staff member who served the customer, and the service or services experienced (sold).

Question 1 (3 points):

Based on your analysis of Janet's requirements, list out the entities and relationships that should be present in the database, indicating the cardinality of each relationship.

Question 2 (3 points):

Derive the Logical E-R data model

Question 3 (5 points):

Show the Physical E-R data model, showing how you went from the Logical to the Physical using best practices.

Question 4 (4 points):

assume the resulting accounting information system in LibreOffice Base (which is free) or in Microsoft Access.