MySQL multiple table assignment

From Knowledge Kitchen
Jump to navigation Jump to search


Complete either Option A or Option B for this assignment, but not both.

You may work with one other student on this assignment. However, you must both run and submit the resulting script(s), and please indicate who you worked with as a comment in all your SQL scripts, as well as in the message body of your submission.


Option A: Greenwich Village Veterinary Practice

In this case study, you have been hired by a small veterinary practice of three veterinarians in Greenwich Village. Each vet takes care of specific animals within the practice. You have been given a table with the following information on every one of animals in their care. They have asked you to design a database. You may assume the following:

  1. You may assume that each pet has only one owner (but a given family can have many pets).
  2. You may assume that each animal is assigned primarily to one veterinarian.
  3. For this exercise, you may assume that each animal , owner, and veterinarian has a unique name so that you can work with these tables with textual data rather than using AUTO_INCREMENT to create surrogate keys if you prefer.

Here are the data that you have been given as though in a spreadsheet:

  • animal's name
  • type of animal (dog, cat, bird etc.)
  • breed (if relevant; for example, the dog could be a poodle, a French bulldog, a dachsund, etc)
  • owner's name
  • owner's email
  • owner's mobile
  • name of the primary veterinarian assigned to the animal's care
  • veterinarian's mobile
  • veterinarian's email
  • is the animal male or female?
  • animal's date of birth
  • owner's address (street, borough/city and zip code suffice for this exercise)
  • immunizations: for each immunization given, track the cost, name and date

Part 1

  • Write one or more .SQL scripts to create all of the tables (including any controlled vocabularies) that you need and insert at least five records into each table.
    • Note: You only need three records in the table of veterinarians.
  • HINTS:
    • This is a sample database; do feel free to make up all of the data (but try to pick animals and breeds that make sense, e.g. a poodle is a dog, not a cat!)
    • Be sure to define a unique PRIMARY KEY for every table.
    • Include any FOREIGN KEY information in your script where it applies.


Part 2

Write queries to answer the following questions:

  1. Display the name of the animal, type of animal and breed (if available) for all of the animals who live with owners in our zip code (10012).
  2. How many female cats are in the practice combined?
  3. How many poodles are in the practice? (A poodle is a dog.)
  4. List every dog and its owner on file by name in alphabetical order by the owner's last name along with every immunization and the date.
  5. How much money has this veterinary practiced charged for immunizations for dogs during the current year?


Option B: Rare Books Collection

In this case study, you have been given a table with the following information excerpted regarding a small collection of rare and valuable books. You may assume the following:

  1. You may assume that each rare book was written by one author.
  2. You may assume that rare book was published by one publisher.
  3. For this exercise, you may assume that each book has a unique title and that each authbor and publisher have a unique name so that you can work with these tables with textual data rather than using AUTO_INCREMENT to create surrogate keys if you prefer.

Here are the data that you have been given as though in a spreadsheet:

  • title
  • author
  • publisher
  • year of publication
  • language (Note: You may assume the book is written in one language.)
  • original language (if this book is a translation)
  • translator (if relevant; note that translators are also often authors themselves)
  • author's native language
  • number of pages
  • library call number (for finding the book in the stacks)
  • author's country of origin
  • author's year of birth
  • author's year of death (if relevant)
  • publishing house city
  • publishing house country
  • genre (e.g. fiction, non-fiction, poetry, prose, etc) (Note: you may select one genre as the predominant one for the purposes of this collection.)
  • appraisal amount (You may assume that all of the appraisals are in U.S. dollars).

Part 1

  • Write one or more .SQL scripts to create all of the tables (including any controlled vocabularies) that you need and insert at least five records into each table.
  • HINTS:
    • This is a sample database; do feel free to make up all of the data.
    • Be sure to define a unique PRIMARY KEY for every table.
    • Include any FOREIGN KEY information in your script where it applies.

Part 2

Write queries to answer the following:

  1. Display the author, title, publishing house and year of publication in chronological order.
  2. How many books in this collection were written by native Spanish speakers? (In this context, a "native Spanish speaker" is an author for whom Spanish is his/her native language.)
  3. How many books in this collection were written by African authors?
  4. Display the title, author and call number for books published by Hachette in order by call number.
  5. What is the total value of this collection based on the appraisal amounts on file?

What to submit

Submit a zip file that contains a folder with your full name on it. This folder must include the following:

  1. your MySQL script (.sql file) that you used to create the data
  2. your MySQL script (.sql file) that you used to run the queries
  3. a transcript of all or part of your session on i6 and/or screenshots of queries in PHPMyAdmin

Note: If you work in PHPMyAdmin, please be sure to write out all of your queries and all of your table structures and submit that information along with at least three screenshots capturing how you worked with the data. You can use STRUCTURE / PRINT VIEW to capture the table structures.


What links here