Second Situation

Situation:

            Puppy Palace works with TV and movie producers who need dogs that can perform

special tricks, such as headstands, somersaults, ladder climbs, and various dog-and pony

tricks. Puppy Palace has about 16 dogs and a list of 50 tricks from which to choose. Each dog

can perform one or more tricks, and many tricks can be performed by more than one dog. When a dog learns a new trick, the trainer assigns a skill level. Some customers insist on using dogs that score a 10, which is the highest skill level. As an IT consultant, you have been asked to suggest 3NF table designs. You are fairly certain that a M:N relationship exists between dogs and tricks.

Questions:

1. Draw an ERD for the Puppy Palace information system.

2. Indicate cardinality.

3. Identify all fields you plan to include in the dogs and tricks tables. For example,
in the dogs table, you might want breed, size, age, name, and so on. In the tricks
table, you might want the trick name and description. You will need to assign a
primary key in each table. Hint: Before you begin, review some database design
samples in this chapter. You might spot a similar situation that requires an associative
entity that you can use as a pattern. In addition, remember that numeric
values work well in primary key fields.

Answer: 

• PUPPY(PuppyId,Name,Age,Size,Breed,Color);
• TRICK(TrickNumber,TrickName,TrickDescription);
• TRICK_PUPPY(PuppyTrickId,PuppyId,TrickNumber,Price,SkillLevel);
• CUSTOMER(CustomerID,Name,Address);
• CUSTOMER_REQUEST(CustomerID,PuppyTrickId);