Transcript Databases - Glow Blogs

National 4 and National 5
Computer Science
Databases (ISDD)
What is a database?
•Structured/Organised collection of
information which you can search through.
•Manual database: filing cabinet or index
cards
What is a database? (cont)
•Electronic database: database package or
database management system
•Advantages of computer based database:
•quicker and easier to access if organised
correctly
•can be backed up
•takes up less physical space
•multiple people can search/access at once
•access remotely
What database are you all (well most of
you) carrying with you?
•Phone contacts:
Electronic database examples
•Police database:
Electronic database examples
•School database:
Database structure
•Data is organised into fields, records and
files:
•Field contains an individual piece of data
•Record contains one or more fields about a
particular person or thing
•File is a collection of records
Field
Record
Name: Mr Smith
Age: 23
Address: 23 High Street, Glasgow, G12 3AB
Phone number: 0141 582 0230
Membership ID: AB1234
Membership category: Green
File
Field Types (Data Types)
•Each field in a database must have a suitable
field type chosen for it
•The field type decides the kind of information
which can be stored in the field
Field Types (Data Types)
•Text
•can store letters, numbers and symbols
•Number
•only stores numbers
•can be used in calculations (numbers in a
text field cannot)
Field Types (Data Types)
•Date
•holds dates only
•Time
•stores hours, minutes and seconds
•stored as a time of the day (e.g. 14:20:20)
rather than 55 minutes
Field Types (Data Types)
•Graphics
•holds a picture
•Object
•may store a variety of different types of
data file, e.g. a video file, a spreadsheet, a
picture file etc.
Field Types (Data Types)
•Calculated
•like a formula in a spreadsheet
•can carry out a calculation on another field
or fields and give you an answer
•Link
•stores a hyperlink or URL
•the link may be to another file, a document
or a web page
Field Types (Data Types)
•Boolean
•can contain only two values – true or false,
yes or no, or
Sorting
•Sorting data in a database
means to put the records in
order.
•The order may be ascending or descending
and is based on the values in fields.
Sorting (cont)
•Ascending (small to big or A to Z)
•e.g. 1, 2, 3, 4, …
•e.g. A, B, C, D,
•Descending (big to small or Z to A)
•e.g. 50, 49, 48, 47, …
•e.g. Z, Y, X, W, …
Sorting (cont)
•Sorting on one field at a time is called simple
sorting.
•Sorting on more than one field (multiple
fields) is called complex sorting.
How has this data been sorted? (simple)
ID Ascending
How has this data been sorted? (simple)
Height Descending
How has this data been sorted? (complex)
Age Ascending
Crime Ascending
How has this data been sorted? (complex)
Surname Descending
Age Ascending
Searching
•The search facility in a
database allows you to look
for specific information.
•You can search on a single field or
multiple fields to find specific records
Database Search Conditions
•Relational operators are used to
create search conditions.
Operator
<
<=
=
>
>=
≠ or <>
Description
less than
less than or equal to
equal to
greater than
greater than or equal to
not equal to
Searching Example
•Consider the following part of a
database of used cars:
What simple Search has been
applied here?
Make = BMW
Search Types
•Searching on a single field using a
single condition is called a simple
search.
What complex Search has
been applied here?
Make = FORD
Colour = Blue
Search Types
•A complex search looks at multiple
fields or multiple conditions on one
field
e.g. Make = “Ford”
Colour = Blue
e.g. Cost > 2000 AND < 2500
Logical Operators
•Join complex conditions using AND or
OR
•AND – both conditions must be met
•OR – one of the conditions must be
met
•NOT – indicates that the condition
should not be met
Wildcards
•Wildcards are used when searching but
you cannot remember the exact
wording.
•The wildcard character is *
e.g. Make = F* would list all the
FIATs and FORDs
What complex search criteria
used here?
Make = V*
Colour = B*
Field Validation
•Field validation is a
check or checks to
make sure that an item
of data is sensible
•It does not eliminate
mistakes but makes
it more difficult for
wrong data to get
into the database
Presence Check
•This check makes sure that a field has not
been left empty
•Often indicated on forms by (required) or (*)
Restricted Choice
•This gives the user a list of options to choose
from.
•Limits the users input to allowed values.
Field Length
•Ensures the correct number of characters
or numbers is entered into the field.
•Small field sizes reduce storage space
required so databases can be loaded or sent
over a network quicker.
30 characters
Range
•This keeps the data within given limits
•Range check can be made on fields which
contain numbers, like ages, money or dates
•E.g.
•age not less than 0 or more than 120
•day of month not less than 1 or more than
31
Database Structure (Flat file)
•A flat file database organises its data using a
single table
Database Structure
•It is good practice that information should
only be stored once in an information system
What is wrong with this database?
What is wrong with this database?
Why is that a problem?
Why is that a problem?
Flat file database problems
•Duplicate (repeated) data causes
modification errors (update anomalies):
•might miss a record when you update
details
•might miss a record when you need to
delete details
•cannot insert a new animal into the
database without a customer or vice-versa
Database Structure (Relational)
•A relational database stores data in more
than 1 table to stop duplication
•Splitting a single table db (flat file) into a
relational db (multiple tables) is called
normalisation
Normalising the Vet table
How does this solve the problem?
•Splitting the table into two tables means:
•less data storage required - information is
now only stored once
•updates are less error prone as not
multiple records of same information to
update
Relationships
•A field used to link two tables is called a key
field, it creates a relationship between the
tables
•If the values are unique (all different), the
key field is a Primary Key
•A Primary key that is used in another table is
called a Foreign Key
•The values in a foreign key do not need to be
unique
Relationships (cont’d)
•The relationship between tables is shown with
an Entity Relationship Diagram (ER Diagram)
•In the pets example its 1 to many