Hands-On Exercises

• Instructions
• Exercise 1
• Exercise 2
• Exercise 3
• Exercise 4
• Exercise 5
• Solutions

Instructions

Modify the ERD that we've just normalized (click here to open it in another window for reference and printing"). Use pencil so you can erase and don't worry about making mistakes — you're learning!

Add the minimal number of fields to solve the problem. You will come up with different ER diagrams if you try to track associated data that isn't requested by the problem! Don't assume and don't track related data that may seem implied.

Solutions are given at the end of this page, but if you look there without giving due diligence, then you're not going to remember anything that you've read. Be sure to work the exercises and THEN compare them to the solutions. Learning dramatically improves when immediate feedback is received on an incorrect attempt.

See Solution     Top of page  

Exercise 1

TPI is a member of F.L.E.A., and in order to retain our accreditation we must rank each dog using a formula that F.L.E.A. provided:

(SUM((skill/difficulty)*100))/tricks

That is, we need to assign each trick a difficulty rating between 01 and 100. To calculate a dog's rank we divide each skill he's learned by the difficulty rating for that trick. Each of these results are then summed and the total is multiplied by 100. That number is then divided by the number of tricks known by the dog. This final figure is the dog's rank, and the ranks for all dogs enrolled at TPI will be printed and mailed to F.L.E.A. headquarters each month. Please modify the ERD to show any new fields, tables, and relationships required to support this business requirement.

See Solution     Top of page  

Exercise 2

TPI is also charging per trick taught. Therefore, we need to track the price paid for each trick. We want to know what we CHARGED for the class (i.e., what did we charge that DAY when training began for that dog?). This price won't be updated, even if the price of the trick should change. For example, Fido learns to "Jump" for $10.00, but a year later the cost is $12.00. Despite the price hike, we can still see that Fido learned "Jump" for just $10.00.

We're not concerned with tracking the history of prices. The boss only wants to know what was paid to learn a skill, and what the price of a trick is currently. He does NOT want to store a list of "The price is $12.00 now, but before that it was $11.50, and before that it was $10.00, and before that it was $9.75."

See Solution     Top of page  

Exercise 3

The boss feels like a dog's ability to learn is influenced by the kennel where he's housed. When we have a year's worth of data, we'll analyze this and see if there's any correlation. However, we need to begin collecting data now, so we'll have something to work with next year. Therefore, we want to track where a dog was staying when he learned one of our tricks. We've examined our business process, an we've decided that we will record the information on the first day of class. We don't care where the dog was staying after that day (in case the dog switches kennels mid-class).

See Solution     Top of page  

Exercise 4

The boss wants to track information on each employee at TPI. He insists on using each employee's social security number as their identifying and linking key. Other than SSN, all that is needed right now is first name, middle name, and last name. He also wants you to change the database model so we can track which employee taught a dog any new skills.

See Solution     Top of page  

Exercise 5

Since not all TPI employees are trainers, the boss wants to add a way to track an employee's job title. While employees may perform several job functions they will be assigned only one job title based on their primary expertise. Change the database model to allow us to track job titles.

See Solution     Top of page  

Solutions

Exercise 1: Accreditation Ranking

The only new entity is the DIFFICULTY rating. The rank can be calculated, so it doesn't need to be (and shouldn't be) stored in the database. Difficulty is an attribute of a trick, so it is placed in the TRICK table.



There might be an situation where you might break the normalization, and store the calculation results in a RANK field: If RANK was frequently needed and you were experiencing a performance degradation that couldn't be address in any other way. Then you might break the rules by adding a non-editable RANK field to the DOG table. If you did this, then you would also have to create triggers for both the SKILL and TRICK tables. These triggers would recalculate the RANK and update the DOG table whenever any field affecting a ranking was inserted, updated, or deleted.


Exercise 2: Current vs. Charged Price

The only new entity is PRICE. However, we want to keep the paid price distinct from the current price. Therefore, the PRICE paid is an attribute of the SKILL learned. So the first PRICE field is added to the SKILL table. The current PRICE is an attribute of the TRICK, so we add a PRICE field to the TRICK table that has the same domain. When a record is inserted into the SKILL table, we simply copy the TRICK.PRICE into SKILL.PRICE, and from there on, we don't touch SKILL.PRICE.



You'll notice that we show a special relationship that was only hinted at before. Notice the dashed line connecting the two PRICE fields. If you look back to the session on ERD notation, you'll see that the physical ERD uses a dashed line to indicate a "non-identifying" relationship. In this hybrid ERD the arrow head shows that SKILL.PRICE comes from TRICK.PRICE, but it isn't linked to TRICK.PRICE (it's not a linking key).


Exercise 3: Kennel / Trick Correlation

This problem was straight forward: we wanted to track the relationship between the KENNEL and the SKILL learned by the dog. So we simply link SKILL to KENNEL with a linking field. We add SKILL.KENNEL (a foreign key) which is the link to KENNEL.KENNEL (the KENNEL table's primary key). You'll notice that KENNEL.KENNEL has two links going "out" to other tables. In core tables of a commercial database, you can see many, many relationships tied to a single field.



Exercise 4: Track Employee Trainers

The new entities SSN, LASTNAME, FIRSTNAME, and MIDDLENAME are added to an EMPLOYEE table. To link EMPLOYEE with SKILL we add SKILL.TRAINER which links to EMPLOYEE.SSN. The ERD signifies that one employee may be the trainer of zero or more skills



Exercise 5: Track Employee Job Titles

The new entity is primarily TITLE which we store in a JOB table. We relate JOB.TITLE to the EMPLOYEE via the JOB.JOB and EMPLOYEE.JOB fields. The relationship is diagrammed to show that one job may be held by zero or more employees.

Top of page