Topic 8: database

Topic 8: database

1. Database: is an organised collection of related data. It is possible to add, edit, manage and retrieve data from a database. The structure of a database allows data to be found and used quickly.

   • • A field is a single item of data within a record.
     • • surname: text • date of birth: date/time • selling pricing: currency
   • • A record consists of a collection of related fields.
     • • 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
   • • A file (Known as a table in Microsoft Access) is a collection of related fields.
     • • The table of records of all pupils in the school.
   • • 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

2. 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 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.

3. 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.

4. 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

5. 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. 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.

7. Records do not stay the same forever. They will need to be updated at some point.

   • • 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

8. 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.

9. 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.

   • • Easier if lists a printed as reports.
   • • You can identify best customer by amount they have spent – sort them according to total amount spent.

10. 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
    • Consequences for a business where data is not secure include: • Loss of customers – unhappy customers go elsewhere • Payments for gods not being received • Bad publicity – all businesses rely on a good reputation

11. keeping data secure

    • • Physical security: o Locks on doors o Alarms o ID cards o Biometric systems
    • Software methods for keeping data secure:
      • • 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)

12. 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.
    • • Sharing: database can be shared with any number of users on a network. Any user can edit the data.
    • • Security: passwords may be set so that no unauthorised user can access the database, which may contain personal and sensitive data