IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
IIS home
Information and Intelligent Systems Social and Ethical Implications Human Computer Interaction Software and Systems Engineering eXercise Files Course Outline and Assessment A-Z of Geeky Acronyms Terrace Work Program 2004 Sillybus FAQ = Frequently Asked Questions Help

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:

    1. how many PINK socks (regardless of size, sex or religion) are on hand?

    2. how many red size 9 male socks that are pure cotton are on hand?

    3. list the batch number of any socks that have been on hand for more than 1 normal year (365 days).

    4. the profit yet to be made on all stock in hand (as you are planning an insurance fire)

    5. the profit made from selling all of batch number 11011 (say) to a visiting sock collector from Akansas USA.

    6. the size and color of batch number 1025(say)

    7. the percentage markup applied to pink poly-cotton size 12 mens socks.

    8. all batch numbers that are now empty (i.e. all are sold)

  • Also, regular system maintenance requirements should allow you to (somehow):

    1. inform your system that a new batch of socks has just been made

    2. 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.

©Copyright t 1992..2018+. Edition 26.150117
Creative Commons License
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 2.1 Australia License