Databases Introduction to Databases Typical spreadsheet Typically users

Databases Introduction to Databases

Typical spreadsheet Typically users will confuse database Let’s look at a typical spreadsheet and spreadsheet ID Initial Surname Title Address Postcode Dog Name Gender DOB Breed 1 A Smith Mrs 4 High Street Hereford HR 1 1 ZX Ant M 21/08/2002 Alsatian 1 A Smith Mrs 4 High Street Hereford HR 1 1 ZX Dec M 21/08/2002 Alsatian 1 A Smith Mrs 4 High Street Hereford HR 1 1 ZX F 08/08/2004 Terrier 1 A Smith Ms 4 High Street Hereford HR 1 1 ZX Rooney M 23/10/2005 Poodle 2 C Miles Mr 72 Castle Road, Ledbury HR 7 AA Fang M 14/03/2007 3 D Jones Mr 9, Back Street, Ludlow, Shropshire SY 21 BB Sammy M 18/03/2006 3 D Jones Mr 9, Back Street, Ludlow, Shropshire SY 21 BB Billy M 14/11/2005 Spaniel 4 R Dean Dr 14 Bryngwyn, Monmouth NP 7 AS Suzy F 09/03/2003 Bloodhound 5 F Read Miss 18 Low Terrace, Orcop HR 2 6 DZ Judy F 22/10/2004 Foxhound 5 F Read Miss 18 Low Terrace, Orcop HR 2 6 DZ Trudy F 25/11/2005 Foxhound 5 F Read Miss 18 Low Terrace, Orcop HR 2 6 DZ Billy M 14/11/2005 Spaniel 5 F Read Miss 18 Low Terrace, Orcop HR 2 6 D 2 Jamie M 08/09/2006 Dachshund 5 F Read Miss 18 Low Terrace, Orcop HR 2 6 DZ Tammy M 26/01/2007 Dachshund

Naming Conventions Field or Column Entity Attribute ID Initial Surname Title Address Postcode Dog Name Gender DOB Breed 1 A Smith Mrs 4 High Street Hereford HR 1 1 ZX Ant M 21/08/2002 Alsatian 1 A Smith Mrs 4 High Street Hereford HR 1 1 ZX Dec M 21/08/2002 Alsatian 1 A Smith Mrs 4 High Street Hereford HR 1 1 ZX F 08/08/2004 Terrier 1 A Smith Ms 4 High Street Hereford HR 1 1 ZX Rooney M 23/10/2005 Poodle 2 C Miles Mr 72 Castle Road, Ledbury HR 7 AA Fang M 14/03/2007 3 D Jones Mr 9, Back Street, Ludlow, Shropshire SY 21 BB Sammy M 18/03/2006 3 D Jones Mr 9, Back Street, Ludlow, Shropshire SY 21 BB Billy M 14/11/2005 Spaniel 4 R Dean Dr 14 Bryngwyn, Monmouth NP 7 AS Suzy F 09/03/2003 Bloodhound 5 F Read Miss 18 Low Terrace, Orcop HR 2 6 DZ Judy F 22/10/2004 Foxhound 5 F Read Miss 18 Low Terrace, Orcop HR 2 6 DZ Trudy F 25/11/2005 Foxhound 5 F Read Miss 18 Low Terrace, Orcop HR 2 6 DZ Billy M 14/11/2005 Spaniel 5 F Read Miss 18 Low Terrace, Orcop HR 2 6 D 2 Jamie M 08/09/2006 Dachshund 5 F Read Miss 18 Low Terrace, Orcop HR 2 6 DZ Tammy M 26/01/2007 Dachshund Record or Row

Tables • The whole spreadsheet is a table • A spreadsheet is a one table database, sometimes known as a flat file database • There are problems with using just one table

Problems? • Look for any problems you can see with the spreadsheet

Problems with a flat file design • The same ID number can appears in multiple rows. You cannot retrieve a unique record with the ID number • Entities may not have an ID number. • Details are repeated –DATA REDUNDANCY, wasting space. • Data can be INCONSISTENT as it is stored more than once • Multiple records have to be updated. • Multiple records have to be deleted. • More than one record may need to be added. • Data may not be ATOMIC.

ANOMALIES • ADDITION User tries to create a record in a table with a foreign key value which does not already exist in another table as a primary key. • AMENDMENT User changes a value in a primary key but does not make corresponding changes where that value is used as a foreign key. • DELETION User deletes a record in one table with a given primary key value but leaves record(s) with this value as a foreign key in another table.

Improving flat files • Split into smaller tables with a defined relationship between them • At the design stage we can use an Entity Relationship Diagram (E-R)

E-R diagram The relationship is that: • A dog can only have one owner • One owner can have many dogs • This is a “one to many” relationship

Result of E-R diagram Initial Surname Title Address Postcode Dog Name Gender DOB Breed A Smith Mrs 4 High St, Hereford HR 1 1 ZX Ant M 21/08/2002 Alsatian A Smith Mrs 4 High St, Hereford HR 1 1 ZX Dec M 21/08/2002 Alsatian A Smith Mrs 4 High St, Hereford HR 1 1 ZX Jordan F 08/08/2004 Terrier A Smith Ms 4 High St, Hereford HR 1 1 ZX Rooney M 23/10/2005 Poodle C Miles Mr 72 Castle Road, Ledbury HR 7 AA Fang M 14/03/2007 Chihuahua ID Initial Surname Title Address Postcode Dog. ID Dog Name Gender DOB Breed ID 1 2 A C Smith Miles 4 High St, Hereford 72 Castle Road, Ledbury HR 1 1 ZX HR 7 1 AA 1 2 3 4 5 Ant Dec Jordan Rooney Fang M M F M M 37489 38207 38648 39155 Alsatian Terrier Poodle Chihuahua 1 1 2 Mrs Mr

Keys Primary Key ID Initial Surname Title Address Postcode 1 A Smith Mrs 4 High St, Hereford HR 1 1 ZX 2 C Miles Mr 72 Castle Road, Ledbury HR 7 1 AA Primary Key Foreign Key Dog. ID Dog Name Gender DOB Breed ID 1 2 3 4 5 Ant Dec Jordan Rooney Fang M M F M M 37489 38207 38648 39155 Alsatian Terrier Poodle Chihuahua 1 1 2

Why is this better? • Member’s details are only stored once • Each dog has a unique ID • New owners only entered once • Changes only made in one place • Details need only be deleted from one record ID Initial Surname Title Address Postcode Dog. ID Dog Name Gender DOB Breed ID 1 2 A C Smith Miles 4 High St, Hereford 72 Castle Road, Ledbury HR 1 1 ZX HR 7 1 AA 1 2 3 4 5 Ant Dec Jordan Rooney Fang M M F M M 37489 38207 38648 39155 Alsatian Terrier Poodle Chihuahua 1 1 2 Mrs Mr

Further improvements Now many dogs can belong to a breed A dog can only be of one breed

The tables now look like this ID Initial Surname Title Address Postcode Dog. ID Dog Name Gender DOB Breed ID 1 2 A C Smith Miles 4 High St, Hereford 72 Castle Road, Ledbury HR 1 1 ZX HR 7 1 AA 1 2 Ant Dec M M 37489 Alsatian 1 1 3 4 5 Jordan Rooney Fang F M M 38207 38648 39155 Terrier Poodle Chihuahua 1 1 2 Mrs Mr Breed ID Breed 1 Alsatian 2 Terrier 3 Poodle 4 Chihuahua

The benefits are • Member’s details are only stored once • Each dog has a unique ID • New owners only entered once • Changes only made in one place • Details need only be deleted from one record • We only need to store the details of each breed once • A new breed can be added without an owner

Exercise • Make a three table database in MS Access • Conventions • tbl. NAME • eg tbl. Dogs • No spaces (required for programming with VBA) • Uniquely identify attributes • Eg dogname not name (don’t have the same attribute in different tables)