Topic 8 - Databases

Topic 8 - Databases

1. Database is an organise collection of related data. It is possible to add, edit, rearrange and retrieve data from a database. The structure of a database alloes data to be found and use quickly.

   • Sorting data: Results of queries can be presented in a sorted order e.g. alphabetical or in numerical order. Sorts can also be in ascending (A-Z) or descending (Z-A) order.
   • Security issues with databases: If data is secure then: • It cannot be destroyed • It cannot be accidently or maliciously altered • It cannot fall in to the hands of unauthorised people

2. Files, records and fields: • A field is a single item of data within a record. • A record consists of a collection of related fields. • A file (Known as a table in Microsoft Access) is a collection of related fields.

   • • Example of a field e.g. • surname: text • date of birth: date/time • selling pricing: currency
   • • Example of a record: • Pupil’s record in a school database only has the fields related to that pupil (surname, forename, date of birth, from, etc.) and not another pupils data.
   • • Example of a file / table: • The table of records of all pupils in the school.

3. Key Field: A Key fields uniquely identities each record. No two records have the same data in their key field. Also known as Primary Key.

   • The key field in the example above is Pupil ID.
     • The purpose of the key field is to be able to refer to an individual record using a single field. This often takes the form of an identity number, membership number or bar code.
     • Calculated fields: Not all data needed in a report may be present in a database. For example a person’s age is never included because it changes day by day. If you want to use age in a report it must be calculated. This result is known as a calculated field. The solution is for the database to use the recorded date of birth to calculate the age by subtracting the date of birth from the date of the report.

4. Validation: Data entered in to a database must be sensible or we get GIGO. Validation methods can only ensure reasonable and sensible data is input in to a system, but there is no certainty that the data is correct.

   • Validation rules are needed for some fields. As data is entered it is checked to see that it obeys the rules; any data that does not obey the rules is not accepted. You can also set validation text – the text that appears if the data is not acceptable.

5. Reports: A report is a printed document that contains data from the database, organised and analysed in a certain way. A report from a student database might list the students in each class in alphabetical order.

   • Forms: Forms are used to make data entry more attractive and secure rather than entering data into raw tables. It is more secure for looking through and displaying the data because he person entering the data need not see all other data or even the other fields. It also allows instructions to be entered.

6. Uses of a database: • Add, change or delete records • Search for a particular record • Sort the records into a given order • Print reports about some aspect of the database

   • Updating records: Records do not stay the same forever. They will need to be updated at some point. For example, people get married and change their surname, people move and change their address or prices of goods change. • Add a new record when a new pupil arrives to a new school • Edit existing records when someone move house • Delete an old record when an employee leaves a job
   • Searching for data: One main use a database is to find information quickly. To search for information you use a query. You are “querying the database”. A query specifies what records you are searching for and what fields from the records you would like to display.
     • Simple query search (keyword search) – looks for data in one field.
     • Complex query search – looks at data with multiple criteria

7. Wildcard characters: You use wildcard searches in a query for data that you only know part of. For example find all the surnames beginning with ‘S’ you might write: surname LIKE “*S”

8. A number of methods are used to keep data secure

   • • Physical security: o Locks on doors o Alarms o ID cards o Biometric systems
   • • Passwords: o Authorised users are given a username. Each user also has a password that proves the user is really the one whose username is given. o Using passwords is a method of authentication.
   • • Access rights (file attributes) can be places on individual files to give: o No right to access the file o Read access (user can view file but not alter it) o Write access (allowing the user to alter the contents of the file)
   • • Back-ups: o A back-up is a second copy of the data. If a problem arises with the data file, then the back-up copy is used. Back-ups are: • Saved on to removable media (CD, DVD, memory sticks) • Stored at a separate location in a locked room or fireproof safe. • Made regularly (every day)

9. Benefits of using a database:

   • • Fast access: using a database to find data is much faster compared to paper in a filing cabinet.
   • • Easy to edit: a database allows for data to be easily updated and changed.
   • • Validation: you can set checks on the data being entered to ensure no inappropriate data gets into the system.
   • • Reports: results of searches can be used to print reports that may help management in their decision making.