Assignment: Business Rules for Sam Symthe's Database

Overview

Sam Smyth is an expert puppy trainer. His specialty is training troubled puppies to behave for their owners. The name of his business is Sam Smythe's Kennel for Troubled and Deranged Puppies. Sam hires expert trainers who have been personally trained by him to train and manage troubled puppies. Sam's success rate working with such puppies is very good. He conducts all of his business on an old farm he purchased several years ago.

Right now Sam needs a way to mange his business so he can see at any one point in time see how many puppies he has, who is training them, and who owns them. He also needs to know what training each puppy has completed, the date the training was completed, where a puppy is kenneled, and when a puppy has left Sam's farm. He would also like to give a report card to an owner when they leave with their puppy/puppies. Sam would also like to be able to have his trainer's be able to look up the puppies assigned to them and see the training and skill level's attained by the puppies under their care. Sam needs to see this information for all puppies.

Business Rules & Processes

Owners can own more than 1 puppy
A puppy has one and only one owner.
A puppy is assigned to one and only one kennel
A trainer trains one or more puppies
A puppy cannot learn from more than one trainer
A trainer teaches a puppy all tricks
A puppy can learn more than one trick
Once a puppy starts training on a trick, that trainer sees the puppy through the training and grades the puppy
Once a puppy is graded, it is done learning that trick, no repeats
A puppy is graded on a scale of 1 (lowest) to 10 (highest)
Owners can have more than one contact
A trainer completes all training once it has begun
There is a time limit of 2 full weeks to try to train a puppy. After that time period has ended the puppy must be graded.
Puppies are registered on a Monday and released from training on a Sunday
A puppy cannot have multiple registrations since an owner cannot ask to have a puppy re-trained
A puppy must be properly registered before it can begin training.
There are a total of 4 kennels where puppies are roomed and each kennel has a total of 20 rooms each.
There can never be a puppy registered without first confirming there is an open room in one of the kennels.
Owners have one and only one address
All payment information is handled via PayPal.
The owner is paying a flat fee for a choice of tricks offered at Sam's business.
All tricks an owner picks are set in the contract and cannot be modified.

Follow all directions in this document. When you are done, please upload all required files to the Assignment named Mid-Term Exam found in the Assignments section for this course in Canvas. If you have any questions during the exam, please feel free to ask.

Part I: Entity Relationship Modeling

A store deals with many vendors and is trying to find a way to better manage its vendor contacts. It would like to be able to pull up data about a product and know what vendors can supply that product. Your job is to use Workbench to design a database for this store. Please be sure you save your Workbench ER Diagram as Vendor.mwb and upload it to the mid-term drop box in the Assignments section of Canvas. Below are the External and Internal schemas.

External Schema

Vendor (vendor name, vendor address, vendor contact person, vendor contact person's phone/e-mail)

Product (product name, vendor supplier)

Internal Schema

Vendor (vid, vname, vstreet, vcity, vst, vzip, vcontact)
Product (pid, prod_name, vendors)

Assumptions

More than one vendor can supply one product

A vendor can have more than one person act as the contact person for the hardware store

A person working for the vendor can have multiple contact types such as several phone numbers and e-mail addresses

You are allowed to add any assumptions you feel are necessary in order to complete this part. If you make any further assumptions please list them in a Word document and save the file as assumptions.docx and remember to upload it with your work for this part. (30 pts.)

Part II: Normal Form Dependencies

In the diagram below there are dependencies. Identify the dependencies. In a Word document, identify the type dependencies found and the attributes found in them. Save your Word document as dependencies.docx. Using your knowledge of how to resolve various types of dependencies that you identify, create a solution using Workbench. Save your work as dependency_1.mwb

In the diagram below, there are dependencies that should not exist in good database design. Identify the dependency, including what attribute(s) are dependent on other attribute(s) and why the condition should not exist. Please save your work in a Word document and save it as dependencies2.docx. Using your knowledge of good database design, resolve the dependency and illustrate what the design should look like in Visio. Save your work as dependency_2.vsd.

In this course you have analyzed fan traps and chasm traps. Use the Diagram below to identify the type of trap that exists and explain the process you used to reach your conclusion. Then re-design the ER Diagram and if necessary, redesign the entities as well.

Here are the business rules you need in order to determine the problem and how to resolve it:

An employee can only work in one location
A location has many departments
An employee has one and only one manager
A manager can manage one or more departments
A manager can only work in one location

Please use Word or Notepad to answer the first part of the question which deals with identifying the trap lies and how you reached your conclusion. Save the file as trap.docx. Then use Workbench to resolve the trap and save that file as trap.mwb.

Part III: Working With Workbench

Please open the files pertaining to Sam Smythe. One is the Workbench ER Diagram, one is the business rules and the other two are the External and Internal Schemas respectively.

Based on the business rules provided (see document "Business Rules for Sam Smythe" in Mid-term module), normalize the database to 3NF. Re-design it so that it reflects the 3NF and then create the code that will generate this database. In a Word document which you will save as Sam.docx please identify all partial functional dependencies and all transitive dependencies you find in the model as it is right now.