[Year 12 SofDev] Database question

[Steven Bird]

On Tue, Oct 14, 2008 at 11:13 AM, Adrian Janson
<janson.adrian.a at edumail.vic.gov.au> wrote:
> Yes – quite correct.  My students spotted this as well.  The x5 is not
> required.

There's some other problems though.  A database with 20,000 customers
should have one or more indexes to support efficient retrieval, and
these take space.  Nightly dumps take space as well.  If the question
is getting at how much disk space we should budget for, these factors
are relevant.

It would be bad database design to store postcode and suburb with each
customer record (cf [1]).  Instead a customer record should contain a
foreign key to a table of postcodes, e.g. a subset of [2].  We're told
the company only has sales in a "local area", so perhaps we'd only
have to deal with a dozen postcodes.  That table would be a constant
size overhead which is easy to compute, and the foreign key would be a
short integer (e.g. TINYINT).

The question treats a database as if it was a spreadsheet, and only
requires students to know the relative sizes of characters, bytes, and
bits.

It would be good if students learnt about normalization and the
importance of avoiding duplication in a database.  It would also be
nice if they learnt what Unicode is, and that 1 character != 1 byte.

[1] http://en.wikipedia.org/wiki/Database_normalization
[2] http://www1.auspost.com.au/download/pc-full.zip

-Steven Bird
http://www.csse.unimelb.edu.au/~sb/

> A database for an online marketing company keeps records of all customers
> who have requested coupons and notifications of sales in their local area.
> They currently have a dedicated customer base of around 5000 customers, but
> wish to grow this to 20,000 customers within the next 5 years. How much
> storage capacity will be required to store 20,000 customer records for the
> entire 5 year period, given the following information:
> First Name – 50 characters
> Last Name – 50 characters
> Address – 100 characters
> Suburb – 50 characters
> Phone number – 10 characters each (2 phone numbers per customer)
> Email address – 50 characters
> Postcode – 4 characters
> Number of coupons received – 32-bit number
>
>
>
> A. 34,200,000 bytes
>
> B. 32,400,000 bytes
>
> C. 35,600,000 bytes
>
> D. 32,800,000 bytes
>
>
>
> The solution gives D with the explanation
>
> 50+50+100+50+10+10+50+4+4 =
>
> 328
> 328 x 20000 x 5 = 32,800,000
>
> Why multiply by 5?  Would the company recreate the entire records each
> year?  I'd design it differently..
>
>
>
> Appreciate your help.
>
>
>
>
>
> Quentin Lydall
>
> Head of ICT
>
> Beaconhills College Valley Campus
>
> 30-34 Toomuc Valley Road,
>
> Pakenham
>
> 3810
>
> Phone: 1300 002 225 Extn 3016
>
> P Please consider the environment before printing my email
>
>
>
> ________________________________
>
> This email is confidential and intended solely for the use of the
> individual to whom it is addressed. Any views or opinions presented are
> solely those of the author and do not necessarily represent those of
> Beaconhills College.  If you are not the intended recipient, be advised that
> you have received this email in error and that any use, dissemination,
> forwarding, printing, or copying of this email is strictly prohibited.  If
> you have received this email in error please notify Beaconhills College by
> telephone on (+61 3)  1300 002 225.
>
> ________________________________
>
> _______________________________________________
> http://www.edulists.com.au IT Software Development Mailing List kindly
> supported by
> http://www.vitta.org.au/vce/studies/infotech/softwaredevel3-4.html -
> Victorian Curriculum and Assessment Authority and
> http://www.vitta.org.au - VITTA Victorian Information Technology Teachers
> Association Inc
>
> Important - This email and any attachments may be confidential. If received
> in error, please contact us and delete all copies. Before opening or using
> attachments check them for viruses and defects. Regardless of any loss,
> damage or consequence, whether caused by the negligence of the sender or
> not, resulting directly or indirectly from the use of any attached files our
> liability is limited to resupplying any affected attachments. Any
> representations or opinions expressed are those of the individual sender,
> and not necessarily those of the Department of Education and Early Childhood
> Development.
>
> _______________________________________________
> http://www.edulists.com.au IT Software Development Mailing List kindly
> supported by
> http://www.vitta.org.au/vce/studies/infotech/softwaredevel3-4.html -
> Victorian Curriculum and Assessment Authority and
> http://www.vitta.org.au - VITTA Victorian Information Technology Teachers
> Association Inc