Transcript Resources

Information System Design and Development
What is an Information System?
“An information system involves the hardware, software used to collect, filter,
process, create and distribute data”.
A key component in any information is a database.
What is a database?
“A database is a collection of similar information which can be sorted and
searched”.
Database Structure
All the information in a database is known as the database file.
Information on one particular thing in the database is a database record.
Each of the individual pieces of information that make up a record are called
fields.
In many representations of a database, a file can be viewed as a table, where
records are identifiable in the table rows and fields are the table columns.
E.g. the school contains a file of all pupils attending. Each pupil has their own
individual record. Within this record information such as their name, address and
previous school are stored as records.
Information System Design and Development
Field Types
Each field in a database should be defined with a particular type, depending on
the data to be stored in a field.
The most common field types are:
Text
Number
Date
Time
Graphics
Calculated
Link
Boolean
Choosing the wrong field type can provide problems at a later stage when
performing sorts and searches in the database.
Information System Design and Development
Field Types
Each field in a database should be defined with a particular type, depending on
the data to be stored in a field.
The most common field types are:
Text
Number
Date
Time
Graphics
Calculated
Link
Boolean
Choosing the wrong field type can provide problems at a later stage when
performing sorts and searches in the database.
Information System Design and Development
Flat File Databases
A flat file database is where all the information in the database is stored in a
single file (table).
These are the types of databases you have used in the past.
There are a number of problems in this approach.
Consider the flat file database below:
Puppy ID Puppy Name
Sex
Cost
Litter ID
Date of Birth
Sire
Dame
12
Rex
M
£500
1
14/09/2013
Rover
Susie
15
Pretty
F
£250
1
14/09/2013
Rover
Susie
16
Punch
F
£170
2
23/10/2014
Saul
Judy
17
Jamie
M
£270
2
23/10/2014
Saul
Judy
Information, such as date of birth, sire and dame are needlessly stored many
This is known as redundant or duplicate data.
times.
The more you write the same date, sire et. then the more possibility there is of
making a spelling mistake, leading to data inconsistencies.
Redundant data increases the storage space in a database (which can be
significant if you have millions of records) and can lead to other problems in
storing data efficiently.
Information System Design and Development
To improve the efficiency of a flat file database, it is often a good idea to split the
table into 2 (or more) related tables.
This is called a relational database.
In any relational database, (or even a flat file database) each table should have
its own primary key.
A primary key is a field (or fields) which acts as a unique identifier for each
record in the database.
Once a value appears in the primary key field in a record, then this value can
never be used again in another record.
Which field would act as the primary key in the database below?
Litter ID Date of Birth
Sire
Dame
Puppy ID Puppy Name
Sex
Cost
1
14/09/2013
Rover
Susie
12
15
Rex
Pretty
M
F
£500
£250
2
23/10/2014
Saul
Judy
16
17
Punch
Jamie
F
M
£170
£270
3
23/10/2014
Patch
Sasha
20
Rex
M
£420
In the table, the litter ID is unique to a particular row (there are 3 rows in this
table).
There may be 2 litters with the same date of birth, but they will not be confused
because they cannot have the same litter ID.
Information System Design and Development
Relational Databases
To create a relational database, split the single table into 2 separate tables.
What should the table below be split into?
(Notice Litter ID is underlined to show that it is the primary key).
Litter ID Date of Birth
Sire
Dame
Puppy ID Puppy Name
Sex
Cost
1
14/09/2013
Rover
Susie
12
15
Rex
Pretty
M
F
£500
£250
2
23/10/2014
Saul
Judy
16
17
Punch
Jamie
F
M
£170
£270
3
23/10/2014
Patch
Sasha
20
Rex
M
£420
Each litter contains details of one or more puppies.
The 2 new tables will be about litters and puppies.
Information System Design and Development
Relational Databases
Litter ID Date of Birth
Sire
Dame
Puppy ID Puppy Name
Sex
Cost
1
14/09/2013
Rover
Susie
12
15
Rex
Pretty
M
F
£500
£250
2
23/10/2014
Saul
Judy
16
17
Punch
Jamie
F
M
£170
£270
3
23/10/2014
Patch
Sasha
20
Rex
M
£420
Let’s look at just the field names (and ignore the data):
(Litter ID
Date of Birth
Sire
Dame
Puppy ID
Splitting the litter data from the puppy data would leave the
Puppy Name
2 tables as shown:
Sex
(Litter ID
(Puppy ID
Cost)
Date of Birth
Puppy Name
Sire
Sex
Dame)
Cost)
Information System Design and Development
Relational Databases
(Litter ID
Date of Birth
Sire
Dame)
Litter ID
Date of Birth
Sire
Dame
1
14/09/2013
Rover
Susie
2
23/10/2014
Saul
Judy
3
23/10/2014
Patch
Sasha
Puppy ID Puppy Name Sex
(Puppy ID
Puppy Name
Sex
Cost)
Cost
12
Rex
M
£500
15
Pretty
F
£250
16
Punch
F
£170
17
Jamie
F
£270
20
Rex
M
£420
Problem:
How do we now know which puppies are in which litter?
When splitting the table, a copy of the primary key should be placed in the new
table.
Information System Design and Development
Relational Databases
(Litter ID
Date of Birth
Sire
Dame)
Litter ID
Date of Birth
Sire
Dame
1
14/09/2013
Rover
Susie
2
23/10/2014
Saul
Judy
3
23/10/2014
Patch
Sasha
Puppy ID Puppy Name Sex
(Puppy ID
Puppy Name
Sex
Cost
Litter ID)
Cost
Litter ID
12
Rex
M
£500
1
15
Pretty
F
£250
1
16
Punch
F
£170
2
17
Jamie
F
£270
2
20
Rex
M
£420
3
Problem:
How do we now know which puppies are in which litter?
When splitting the table, a copy of the primary key should be placed in the new
table.
Litter ID in the 2nd table is called a foreign key.
A foreign key is a primary key in one table which appears in another table, to link t
Information System Design and Development
Relational Databases
Finally, the new table also needs a primary key.
In this key, puppy ID is unique to each row in the table and so becomes the new
primary key.
(Litter ID
Date of Birth
Sire
Dame)
Litter ID
Date of Birth
Sire
Dame
1
14/09/2013
Rover
Susie
2
23/10/2014
Saul
Judy
3
23/10/2014
Patch
Sasha
Puppy ID Puppy Name Sex
(Puppy ID
Puppy Name
Sex
Cost
Litter ID)
Cost
Litter ID
12
Rex
M
£500
1
15
Pretty
F
£250
1
16
Punch
F
£170
2
17
Jamie
F
£270
2
20
Rex
M
£420
3
Information System Design and Development
Relational Databases
Finally, the new table also needs a primary key.
In this key, puppy ID is unique to each row in the table and so becomes the new
primary key.
(Notice that Litter ID has an * to show that it is a foreign key in the 2nd table).
(Litter ID
Date of Birth
Sire
Dame)
Litter ID
Date of Birth
Sire
Dame
1
14/09/2013
Rover
Susie
2
23/10/2014
Saul
Judy
3
23/10/2014
Patch
Sasha
Puppy ID Puppy Name Sex
(Puppy ID
Puppy Name
Sex
Cost
Litter ID*)
Cost
Litter ID
12
Rex
M
£500
1
15
Pretty
F
£250
1
16
Punch
F
£170
2
17
Jamie
F
£270
2
20
Rex
M
£420
3
Information System Design and Development
Relational Databases
Review:
What is an information system?
What is a database?
What are files, records and fields?
What other terms are used to describe these parts of a database?
What data types can fields take?
What is a flat file database?
What problems can occur in a flat file database?
What is a relational database?
What is the purpose of a primary key?
What is the purpose of a foreign key?
Let’s put our relational database model on the computer.