3. Stroke of Genius Model Mary Richards owns a house painting company, named Stroke of Genius. She...
Question:
3. Stroke of Genius Model Mary Richards owns a house painting company, named "Stroke of Genius". She has decided that she needs a better record-keeping system that can be used to quickly retrieve information about her current and prospective customers. She would like you to design a data model of the system, so she can have it built. Mary wants to capture a customer's name (first and last), address (street address, city, state, zipcode), phone number, and their email address. Often customers are referred by other customers. When a customer refers another customer, Mary sends $10 to the referring customer with a thank you note. Mary does not recognize multiple referral sources for an individual customer. Of course, a happy customer may refer many other customers to "Stroke of Genius" for future jobs, but a referral from an unhappy customer is unlikely. She already has detailed data about the individual jobs that her company has done for existing customers, such as the job number, the beginning and end dates of the job, the type of job (e.g., residential, commercial, industrial) and the amount billed. However, Mary would like to be able to easily relate this data to the job's customer and to the employees who worked on the job. Each job will have a foreman, several painters, and several apprentices working on it. She has information about all her employees (their SSN, their name (first and last), their hourly payrate, and their phone number). Sometimes an employee will be a painter on one job and a foreman on another. A foreman may have one or more jobs they manage. Not all the jobs have been assigned to a foreman to manage them yet. When a customer calls with a complaint about the work being done on their house Mary needs to be able to determine who is or was the foreman on the job and who else worked on the job. Painter are specialized in one or more skills (e.g., handling toxic paints, detailed brushwork, cabinetry). A painter may have several apprentices assigned to them for on-the-job training. An apprentice trains under a maximum of one painter, but may not be training under a painter on every job. For apprentices, Mary records whether they are a junior or senior level apprentice. Once fully trained, an apprentice can be promoted to painter.