QuestionQuestion

Transcribed TextTranscribed Text

Problem Statement Hotel for Pets is a business where owners leave their pets (dogs or cats) when they go away for vacations. Hotel for Pets has 50 luxurious pet kennels. Each kennel is either size large, medium or small (L, M, S) and may have a pet bathroom. The cost per day for staying at the kennel is $25. Hotel for Pets also provides several spa treatment options for pets. The spa treatment options and costs are as follows: Option ID Spa Treatment Option Cost FDSP Full Day Spa $100 SHAM Shampoo $20 SHED Shedicure $30 PAWD Pawdicure $35 TTHF Teeth Freshening $50 Due to a recent increase in business, the manager, Ms. Terrier, has hired you to replace her old paper and filing cabinet system with a new up-to-date database system. Some of the data to be maintained in the database is as follows: pet name, year of birth, gender, insurance value of pet, owner’s last name, first name, address and phone number, pet type (indicates whether the pet is a dog or a cat), special instructions for that pet, kennel number (see note below) of kennel occupied on a particular visit, kennel size, bathroom?, start and end date of visit, pet who was treated at spa, treatment option requested, date of treatment Note: The hotel is a five-story building. There are 10 kennels on each floor. The kennel number includes the floor number (1, 2, 3, 4, or 5) and a letter for the room (A, B, …, J), e.g., kennel 3H is the eighth room on the third floor. You may need to add other fields/tables depending on the types of queries that may be asked, some of which are as follows (note that you do not need to implement these queries for this assignment): 1. A list of all guest pets and the special instructions for the pet given by their owner. 2. A listing of all the pets whose owner’s last name is “Coffey” or the pet’s name is “Bouncy”. The list should include each pet’s owner’s first name, street, city and phone number. Your list should be sorted by the owner’s first name and the phone number, both in ascending order. 3. A listing of all large kennels that were occupied on February 14, 2015. Your list should include the kennel number, the name and type of the pet that stayed in the kennel, and the insurance value of the pet. 4. A list of all kennels where the pet “Sharpie” stayed. Your list should display the kennel number and size, the start occupancy date, end occupancy date and the number of days the kennel room was rented on each visit. 5. A listing of all the pets that are leaving the hotel today. Your list should include the name of the pet, the last name and first name of the owner, the size of room and the amount owed to the hotel. Your list should be sorted by pet’s name and then by owner’s last and first name, all in ascending order. 6. Count the number of kennels, by kennel size, that are occupied today. Display the kennel size and the occupation count of each size of kennel. 7. List all the pet names who received the shampoo treatment. Your list should include the name of the pet and the date of the treatment. While designing the database keep in mind the issues of redundancy and duplication of information, it will be necessary to incorporate more than one table in your design. Not all the information needed in the database has been specified above. Design decisions of inclusion or exclusion of information will be made by you, the designer after considering the information requirements of Hotel for Pets. Provide a design of the database (in a file called 1600_a3_design.docx) with normalized tables by completing questions a-e given below. Then, for question f, provide the implementation of the database, with sample testing data. Note: No queries are required to be designed or implemented for this assignment. The query details (listed above) are to be kept in mind for the database design, implementation and when choosing sample testing data. a). List all of the tables/entities that will exist in the database. b). Give the tables/entities and their relationships (in tabular form) indicating the cardinality (also known as multiplicity) of the relationship from Entity 1 to Entity 2 by completing the table given below. For example, consider a database for a walk-in clinic where a patient can be seen by any doctor, and a doctor can see any of the patients. Entity 1 Entity 2 Cardinality of Relationship From Entity 1 to Entity 2 (1:M, M:1, M:M or 1:1) PatientsVisits 1:M Doctors Visits 1:M c). List each table in the database, including the name of each table with its attributes listed within parenthesis, with the primary key being the first field listed and being underlined. The following notation should be used: Employee (SIN, Firstname, Lastname, Address, Phone Number) d). Using the From/To table given below, indicate how the primary and foreign key fields of the tables in the database are used to connect the related tables. For example, Department(DCode, Department Name) Employee(EID, Last, First, Department, StartDate) From Table From Field To Table To Field Department DCode EmployeeDepartment e). Provide the structure of the Pets table only (including the field name, data type, size and description), in the manner shown here: Table Name: Patients Name Type Size Description PatientID Text 10 Primary key that uniquely identifies a patient Phone Text 10 Contact phone number f). Using MS-Access (create a file called 1600_a3_implement.accdb), create all the tables for your database and add data to the tables (at least 5 records in each table where possible; the data is to be made up by you). The data you enter should be such that you could answer the potential queries listed above; however these do not have to be implemented for this assignment.

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

    By purchasing this solution you'll be able to access the following files:
    Solution.zip.

    $70.00
    for this solution

    PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

    Find A Tutor

    View available Computer Science - Other Tutors

    Get College Homework Help.

    Are you sure you don't want to upload any files?

    Fast tutor response requires as much info as possible.

    Decision:
    Upload a file
    Continue without uploading

    SUBMIT YOUR HOMEWORK
    We couldn't find that subject.
    Please select the best match from the list below.

    We'll send you an email right away. If it's not in your inbox, check your spam folder.

    • 1
    • 2
    • 3
    Live Chats