Great news! We have a new client! They are a “Daisy’s Dog Groomers” and they want you to design and implement a database for their business. You had some good discussions with the client and your team and below is the ER diagram that you have for the database.

The owner table will house the information regarding the owner of the dogs. So the owners will be the clients of Daisy’s Dog Groomers. We will need to capture the name, email and phone number. We are fine with having owners that don’t have dogs in the database, and we need to take into account that some owners have more than one dog.

For dogs, all dogs needs an owner, and we will capture the dog name and the dog breed. Daisy’s Dog Groomers also asked to create a note field to capture any specific notes for the dog. Turns out that some dogs have specific characteristics (such as being overly barky or having very sensitive skin) that would be helpful to store. Daisy’s is ok with having dogs in the database even if they don’t have appointments yet. Sometimes they will gather info for potential future clients.

We also need a table specifically for groomers. We will capture the phone number and email of the groomer. Groomers don’t necessarily require appointments to be in the database as some could be new and don’t have dog clients yet.

Let’s now take a closer look at the appointment table.

Appointments will be associated with a groomer and a dog. We will need to store not only the date, but also the time for the appointment. There are three service offerings from Daisy’s. The first is a bath. This field would be captured as a boolean value. Meaning that true means that we are doing a bath on this appointment and false means that there will be no bath. The second service offering is a haircut, which could also be boolean. Daisy’s has a third offering, named “the works.” This could be done regardless of (meaning independent of) the first couple offerings and the dog gets a full spa treatment. Starting from a nail clipping and full brush-out all the way to getting a nice cologne spray at the end. The appointment has a status. Currently we are only allowing two types of status, which are “pending” and “complete.” Any other value that is not “pending” or “complete” will not be allowed as a value for this field.

We also need a billing table. Bills will be associated with an appointment. We only the need to record the date that the bill was created, and not the time. The bill will have a price, which will be in dollars and cents. One appointment will have one bill. Appointment won’t have a bill until the appointment has been completed, but all bills need to have an appointment.

Assignment

Create all the necessary tables according to the requirements and ER Diagram above.