Topic 8: Databases

Topic 8: Databases

1. Database: a database is a collection of related data that can be added, deleted, copied and edited; a database allows people to find information easier

   • Benefits of a database: Faster access compared to paper database, Easy to edit, Sharing is easy with users over the same network, validation checks can be set up to make sure the data is sensible and reasonable, printable reports can make management decisions easier to make

2. Data types

   • Number (integer)- a whole number e.g. 43
   • Number (real)- any number including decimals and fractions e.g. 43.2
   • Currency- money including foreign currency e.g. £, $, €
   • Date/Time- any date or time, the way the day looks is affected by the way it is formatted e.g. 14/11/13
   • Boolean- two opposites e.g. Yes and no, male and female
   • Text or string- any letters or characters including punctuation e.g. John Smith

3. Files, records and fields

   • A field is a single item of data within a field e.g. surname
   • A record consists of a collection of related fields e.g. a pupils record only hold fields about that pupil
   • A file is a collection of related fields e.g. table of pupils in the school

4. Key field/Primary key: uniquely identifies a person or information in a database E.g. pupil ID

5. Validation is needed to make sure information entered is sensible and reasonable otherwise we get GIGO, is a cell has a validation rule on it then when data is entered it is checked to make sure it follows this rule

   • Range check: data entered must be within a sensible range
   • Type check: this makes sure data is of the right type
   • Format check: if data has a particular pattern then this rule makes that data follow a specific layout
   • Length check: length of data imputed is checked for amount of characters used
   • Presence check: makes sure data must be entered into the cell
   • Lookup list: if data entered can only be a number of answers the computer can compare it to a list of allowed answers

6. Reports: a printed document containing information from the database that has been organised and analysed in a specific way

7. Forms: used to make data entry more attractive and secure rather then entering raw data into tables

8. Calculated fields are used for things such as age because it changes day by day which would mean you would be constantly editing the database. The solution is for the calculated field to subtract the date of birth from the date of the report

9. A query specifies what records you are looking for and what ones to display; simple query searches only look for data in one field whereas a complex query search searches in multiple fields

   • Operators: =, <, >, <>, AND, OR, LIKE
     • Meaning: Equals, Less than, Greater than, Not equal to, Data is not listed if both conditions are true, Data is listed if either of the conditions are true, Where are perfect match is not needed
   • Wild card searches are used when you only know part of the information
     • Wild cards: *, ?, [ ], [-], !
       • Meaning: Ay number of characters e.g. Like "S*" finds Scott and Smith, Any character after e.g. Like "Ca?" Finds Cat and Cab, Any character n the bracket e.g. Like "10[TS] finds 10T and 10S, Ay character within a range e.g. Like "10[A-D] finds 10B but not 10E, Any character not in the bracket e.g. Like "10[!ED] finds 10T but not 10E or 10D
   • Results of queries can be sorted alphabetically or numerically so that its easier if lists are printed as reports and you can identify things easier

10. Security issues with a database

    • Consequences for businesses if databases aren't secure are loss of customers, payment for goods not being received, bad publicity and management not being able to make decisions based on lack of information
    • Passwords: only people that Ayr authorised can access the database by entering a password or username, this is authentification
    • Access right: restrict certain access to specie files so people can't access them
    • Back-ups: second copies of the database so that if the first gets hacked or deleted you have another copy somewhere