Assignment 2: Creating an ERD
Read the attached story and create an ERD that captures the business requirements. Please do neat work – excessively messy solutions will receive reduced credit. Use look near notation. If multi-value or derived attributes are needed, use the conceptual modeling symbols. Do not decompose any M:N relationships. Do not include any attributes, entities, or relationships that are not specified in the case. You should need only three entities.
Write out any semantic integrity constraints you feel are important that cannot be captured in the ERD.
Attempt this assignment yourself, then when you feel either Lost or Successful, watch this walkthrough video and correct any mistakes you might have made: https://bit.ly/DB1-A2
Shampooch Doggy Spa
Shampooch doggy spa is a high-end dog grooming service where spa technicians (employees) groom dogs by providing services such as bath, teeth brushing, or nail clipping. Employees are identified by an EmpID, and have a first name and last name. Employees may have certifications in specialties such as large dogs, styling long hair, or treating dry skin. All employees must have at least one certification. Some senior employees mentor up to four junior employees. Employees have a maximum of one mentor, or may not have any mentor.
All dogs are assigned to an employee to provide grooming services. An employee may groom several dogs at once, or may not have any dogs to groom at all. If a dog is getting multiple services (i.e., bath and nail clipping), the dog may be groomed by several employees. The date each service was provided and which employee provided the service to the dog should be recorded in the database. Every dog’s name, breed, and weight are recorded. Since multiple dogs might have the same name, dogs are identified by combining the name of the dog with the unique identifier of their owner.
Customers are uniquely identified by their phone number. First and last name are recorded for all customers, and customers have the option to provide their email address to get special offers. Most dogs are owned by customers, however, some employees also own dogs that receive services at Shampooch. Regardless, a dog is always owned by only one employee or customer, and all customers own at least one dog
If an employee quits or is fired all records of the grooming services they have provided are deleted from the database. Any employees they were mentoring will be assigned to have the general manager as their mentor until a new mentor can be identified for them.
Once per quarter the database is scanned for inactive customers. If a customer has not visited Shampooch in the last five years they are deleted from the database. When a customer is deleted their dog(s) should also be deleted from the database. Similarly when an employee is deleted their dog(s) should be deleted from the database as well. When a dog is removed from the database all records of the grooming services they have received should be removed as well.
