Identify the type dependencies found

Assignment Help Database Management System
Reference no: EM131328267

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

Student_ID

Course_ID

Student_First_Name

Student_Last_Name

Course_Name

Grade

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.

Book_id

Book_title

Book_author

Publisher_name

Publisher_contact

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.

Reference no: EM131328267

Questions Cloud

What three important points would you want to explain : Evaluate and summarize two articles and state what you learned. If you were discussing the article with a colleague, what three important points would you want to explain? How can knowledge of what you learned help you in your career?
What advantage does a non-crystalline structure have : MSE 2010:we talk about several general classes of materials: metals, polymers, ceramics, glasses and composites. We know that these are just the basic categories and we've discussed several specific examples, but this is nowhere near an exhaustive..
Describe how management control theory and concepts apply : ACC702 Managerial Accounting Assignment Task Specification. Describe how management control theory and concepts apply to organisational settings through the generation of accounting and organisational reports
Design plans for business continuity : Describe the impact of telecommuting on energy conservation, IT operational costs, "green computing", and shifts in telecommuters' lifestyles (e.g., parents, disability, etc.).
Identify the type dependencies found : CIT 310- 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.
New baseball stadium is re-spent in the community : Suppose that 38.4% of every dollar invested in a new baseball stadium is re-spent in the community. What percent of the maximum economic impact is attained during the first 2 years after an investment is made?
Develop a process pattern that addresses the given problem : A common problem during communication occurs when you encounter two stakeholders who have conflicting ideas about what the software should be. - Develop a process pattern that addresses the given problem.
What is the bond current price if the market rate : A 12 year bond has 6 years left to maturity and its coupon rate is 8%, paid semi-annually. Consider each of the following situations separately. What is the bond’s current price if the market rate is 4.5%? If the required return on this bond (the cur..
Do you feel you used a rational decision-making model : Finally, what about the third approach to decision-making - intuition? How did you use intuition in your decision-making process? Explain. (Remember that we often combine our "gut feelings" with some rational dimension.)

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd