National 4 and National 5 Computer Science Databases

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, G 12 3 AB Phone number: 0141 582 0230 Membership ID: AB 1234 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) 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 or equal to 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?

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