Topic 8: Databases

Topic 8: Databases

1. Database: is an organised collection of related data.

   • Its possible to add, edit, manage and retrieve data
   • The structure of a database allows data to be found and used quickly.

2. Type: Number (whol;e) Example: 43 Description: Whole number, positive or negative

3. Type: Number (real) Example: 72.367 Description: Decimal number, positive or negative

4. Type: Text (string) Example: Hello number 32! Description: Letters, numbers or other characters such as punctuation marks

5. Type: Date/Time Example: 04?10?12 Description: Any time or date; the way the time or date is displayed depends on how it is formatted

6. Type: Currency Example: £13.55 Description: Money £ (including foreign currency $ or €)

7. Type: Boolean Example: Yes Description: Two opposing states e.g. true or false, male or female, yes or no.

8. Type: Lists Example: Red, Green, Blue. Description: A list of values, sometimes called a "drop-down" list; only data from the list can be selected

9. Type: Picture Example: Image data, from a digital camera, mobile phone, the internet, a clip-art library or scanned in image

10. Type: Sound Example; A sound or music clip Description: A beep from a barcode reader or popular song-audio data

11. Type: Video Example: A moving picture Description: Visual data

12. A field is a single item of data within a record. E.g. surname:text

13. A record consists of a collection of related fields. E.g. pupils record in a school database only has fields relating to that pupil (surname, forename, DOB etc.) and not another pupils data

14. A file (known as a table in Microsoft Access) is a collection of related fields. E.g. the table of records of all pupils in a school

15. A key field uniquely identifies each record. No two records have the same data in their key field. Also known as the primary key.

    • E.g. Pupil ID
    • The purpose of a 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.

16. 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,

17. 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 unacceptable.

    • Validation method: Range check Description: The data entered must be in a sensible range. Example: If a test is out of 100 marks then only marks in the range of 0-100 are acceptable.
    • Validation method: Type check Description: This ensures that the data is of the correct type. Example: If the number of rooms in a house is being recorded, then only an integer (whole number) will be exepted.
    • Validation method: Format/Picture Check Description: If the data entered has a particular pattern e.g. has a postcode or car reg then an input mask can be used to force the data to conform the rule. Example: Input mask for a database would be LLN NLL e.g. ME3 9BY. Where L is a letter and N is a number
    • Validation method: Length check Description: The length of the input is checked for the number of character used. Example: If a forename is being entered it is reasonable to assume that it will be between 2 and 18 characters long.
    • Validation method: Presence check Description: Some data has to be entered. Example: If you were ordering goods from the internet, you would have to give them your address and the item you were ordering. An error would occur if this data was present and would not allow the processing of the order to continue
    • Validation check: Lookup list Description: If the data entered consists of a limited number of possible entries then the computer can compare the entry with a list of allowed answers. Example: If the predicted grade for an exam can only be A, B, C, D, E or U, only these inputs are allowed.

18. Operatior: = Description: Equals Example: Surname=Bell

    • Operator: < Description: Less than Example: DOB <22/09/2000
      • Operator:> Description: More than Example: Surname> Bell
        • Operator:< > Description: Not equal Example: Surname< > Bell
          • Operator: AND Description: Data is only listed when both conditions are true. Example: Surname: Bell AND Form:Cy2
            • Operator: OR Description: Data is only listed where either or both conditions are true. Example: Surname= Bell OR Form=Cy2.
              • Operator: LIKE Description: Where a perfect match is not needed. Example: LIKE Surname= "Bell" This will list all the surnames that start with "Bell".

19. Report- 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 glass in alphabetical order.

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

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

    • The solution is for the database to use the recorded DOB to calculate the age by subtracted the DOB from the date of the report.

21. 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 aspects of the database

22. 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 pupil arrives to a new school
    • Edit existing records when someone moves house
    • Delete an old record when an employee leaves a job

23. Searching for data: One main use of a database is to find info quickly. To search for info, you use a query. You are "querying the database". a query specifies what records you are searching for and what fields from the record 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

24. Wildcard characters: You can use wildcard searches in a query for data that you only know part of.

    • Wildcard: * Meaning: Any number of characters Example: Like "S*"- Finds Smith, Smithers and Scott.
    • Wildcard: ? Meaning: Any character Example: Like "Ca?"-Finds Cat, Cab but not Cart.
    • Wildcard: [ ] Meaning: Any character in the brackets Example: Like "10[TS]"- Finds 10T or 10S.
    • Wildcard: [ - ] Meaning: Any character within a range of characters. Example: Like "10[B-M"- Finds 10G and 10D but not 10T
    • Wildcard: ! Meaning: Any character not in the brackets Example: Like "10 [!TW]"- Finds 10A and 10Z but not 10T or 10W

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

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

26. 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 goods not being received
      • Bad publicity – all businesses rely on a good reputation
      • Management unable to make decisions due to lack of information

27. Physical security: Locks on doors, Alarms, ID Cards and Biometric Systems

    • Passwords: Authorised users are given a username. Each user also has a password that proves the user is really the one whose username is given. Using passwords is a method of authentication.
      • Access rights (file attributes) can be places on individual files to give: No right to access the file Read access (user can view file but not alter it) Write access (allowing the user to alter the contents of the file)
        • Back-ups: 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)
          • 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.