On This Page
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
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
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
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
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
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
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
Copyright © 1995 Craig Edward Given