 |
|
 |
| |
Database Languages
eXercise #11
Table Design -
By Error and Trial
PART1: Initial
Information
A sock factory makes
socks (boy is that obvious) that are a particular colour, size, blend(of
materials) and sex (... for boys or girls, as the company insists on
sexisim).
For each type of product, there is a RRP (reccommended retail price),
CP (cost price of materials and labor) SP (selling price to retailers)
and PROFIT (you know what that means).
The factory manufactures socks in batches of a particular size and color.
Each batch is made completely on a particular day (each batch has a
unique number and manufacture date) and many batches could be made during
a day. All socks are stored in the factory warehouse awaiting shipment
to retailers (we term socks in storage as on hand which is funny
when you think about it as they are actually put on your foot).
Firstly try (by
error and trial) to come up with a workable table structure...it can
consists of as many tables as you like..... how do you decide if it
is workable you ask?
- well, try POPULATING
it first with a few instances
- then it should
be possible to ask your table system some questions, like:
- how many
PINK socks (regardless of size, sex or religion) are on hand?
- how many
red size 9 male socks that are pure cotton are on hand?
- list the
batch number of any socks that have been on hand for more than
1 normal year (365 days).
- the profit
yet to be made on all stock in hand (as you are planning an insurance
fire)
- the profit
made from selling all of batch number 11011 (say) to a visiting
sock collector from Akansas USA.
- the size
and color of batch number 1025(say)
- the percentage
markup applied to pink poly-cotton size 12 mens socks.
- all batch
numbers that are now empty (i.e. all are sold)
- Also, regular
system maintenance requirements should allow you to (somehow):
- inform your
system that a new batch of socks has just been made
- inform the
factory that stocks are getting low (ie < 500 pairs) of a particular
style of sock, so that they can make more
Don't panic.....this
is complex and, yes, you will learn the correct method of database
design elsewhere.
When you have completed
this activity, you will be instructed to move on the PART 2 of
this exercise.
|
|
|
 |
|
 |
|
