We are using Anaconda Python for this database.

Overview

A local kennel needs a database that will help manage all of its activities. They have asked for your assistance in developing a database to meet their needs. Before writing any code you have decided to develop an entity-relationship (ER) diagram that will capture the data necessary to generate any requested reports. An ER diagram will allow you to meet with the end users and effectively communicate the intended design and to determine if any items are missing from the design.

The Problem

Pets staying at the kennel are divided into one of three categories; dogs, cats, and exotics. Each group has a designated room - the yellow room, the blue room and the green room respectively.

The information requirements are as follows:

The information kept about a pet includes the name of the pet, date of birth, the name of each owner, contact telephone numbers for each owner, a list of any medicines the pet is taking, a description of any dietary restrictions, whether the pet has any siblings in the kennel and who these siblings are, and the name and telephone of the owners preferred veterinarian.

At the beginning of each stay, pets are scheduled for both the length of their stay and any additional activities. For example, a pet may be staying overnight in a kennel and may be participating in daycare activities (play dates!). In addition, a pet may be scheduled for a bath and/or having his or her nails trimmed.

Information kept about the owners includes name, address, and contact phone numbers for all time slots their pet is in the kennel. This could mean, for example, that on Tuesday the kennel might need to call one owner at work if the pet is sick but on Wednesday they might instead call the other owner at home for a similar problem.

Owners can be "red-tagged". This means that they are not allowed to pick up a pet even though they are a listed owner.

The staff at the kennel are also part of the database. The kennel keeps their social security number, name, address and any and all phone numbers. The staff are scheduled to work at different times and on different days but always work in the same room. This is so the pets don't have to get used to too many new faces. The director of the kennel and one assistant do not work directly with pets but work in the office. It must be possible to print out a staff member's proposed schedule. The total number of hours a staff member is supposed to work on each day as well as the total planned number of hours per week. It is possible that a staff member may work more or fewer hours than he or she is actually scheduled to work. The precise days/hours worked by a staff member are recorded. Staff members are assigned to work in a specific room except for a couple of people who do not work directly with the pets. These are the office staff.

It also must be possible to print out a planned daily roster for each room in the day care kennel indicating what pets are supposed to be in the room for what hours and what staff members are working in the room at that time.

Deliverables

Part 1: Begin the database design process by identifying and listing the entities for the following problem. For each entity include the entity name and a list of attributes. Identify an attribute, or attributes, to be used as a primary key.

Part 2: Create an ER-Diagram for this exercise. Include relationships, relationship attributes and participation numbers. Use the Crows Foot notation in your ER design. You may use any drawing tool you wish. It is fairly easy to use the drawing tools in Microsoft Word or PowerPoint to create ER drawings. Another option would be to use LucidChart. LucidChart is a cloud-based diagramming tool that is included as part of the web applications available under your ISU Google account. (See the demonstration for further explanation.) If you use LucidChart you can export the drawing as a JPEG file and incorporate it into a Word document incorporating the list from Part 1.