Database Revision

• Database = Organised, structured, persistent store/collation of data
• They enable more efficient processing, reducing storage requirements and try to minimise data duplication and redundancy
• It is persistent because its content remains intact after the program has finished processing
• DBMS (Database Management Systems) allow data to be retrieved quickly, using filters and queries, whilst also allowing for new data to be added easily, in an attempt at reducing data inconsistencies which inevitably lead to errors.
• A DBMS can also allow for access control/security, creation of forms and reports, and an interface to allow other programs to access the data and use it
• Flat-file = A database which has one table and is not linked to any other
• Relational = Multiple tables linked together via foreign keys (primary keys of other tables)
• Flat-file databases lead to data inconsistency and redundancy
• Data Inconsistency = Data may have been updated elsewhere, but not everywhere, so mistakes or incorrect data may exist
• Data Redundancy = Data is duplicated unnecessarily meaning file size is increased pointlessly

• Entity (Tables) = A real world thing which data is collected about e.g. students or a shop's stock
• Attributes = Individual pieces/titles about each entity such as first name, last name (column titles)
• Record = All the data/information held about a particular thing/person like their last name and address (all their information collectively)
• Field = All the information stored about a particular attribute like (Attribute = Last name; Field = Everyone's last name in that database)
• Forms = Easy way to search databases via a user-friendly interface
• Reports = Queried data that is printed as an output from the database, and presented in a  well-formatted way
• Queries = Searches
• Primary Key = Unique identifier of an item
• Foreign Key = Primary key of another table which links the two together
• Compound Primary Key = Two fields are used to create a primary key to make a Unique Identifier
• Secondary Key = A key used in a field to create an index to speed up query speed
• Referential Integrity = Ensures tables linked by a primary key are not deleted, thus do not affect the operation of either table

• Serial Searching = Sequential/Linear searching = searches through each piece of data until it reaches its criterion (the piece it wants) --> i.e. 1-150 --> begins at one, finishes at 150
• Transaction = A change/deletion/addition of information
• Transaction Log/File = Records all transactions/modifications to the database
• Indexing = Files can be searches faster by producing a separate index file which organises data into an ordered format e.g. A-Z
• Master File = Main file held by an organisation that stores the basic details about critical/crucial aspects of business, which do not change often e.g. student personal information
• EDI (Electronic Data Interchange) = Joining two databases together; Increases transfer speed of data; however, if an error is apparent in one database, it will be transferred to the other one (e.g. Exam Board and a School's database are interlinked)

Types:

•            Text =  String of numbers/mixture of letters and numbers
•            Number = Positive/Negative integers and decimals
•            Date/Time = Dates in varying formats/locations
•            Currency = Numbers and monetary values/symbols
•            Boolean = Values either Yes/No, True/False, On/Off
•            Autonumber = Generates a number automatically

Normal field size is 255 characters, but this increases the file size unnecessarily if the amount of characters is smaller than the 255 size, so it can be decreased to reduce file size

• Verification = Checking that the program meets specifications/brief. It includes Double-entry/Read-cehck tnry by looking over the input and ensuring it is correct, otherwise re-ternting the data again to ensure they match each other
• Validation = Checks the program meets a user's needs/requirements by ensuring the format of input is valid and correct; includes Presence checks, Format checks

A database can be represented by an Entity Relationship Diagram

They can be:

• One-to-one = One person has one address = One entity is linked to one item
• One-to-many = One cinema has many customers = One thing has many items in its entity
• Many-to-many = Many subjects can be taken by many students = Many items for many items in its entity

Capturing data:

• Optical Character Recognition (OCR) = Automatically read data from a form i.e. ANPR to read number plates
• Optical Mark Recognition (OMR) = Used for multiple choice tasks i.e. lottery tickets/QR codes (Fast to collect data)

Structured Query Language (SQL) can be used to add or manipulate data

Databases and their contents can be converted and exchanged in various ways

Examples are:

• From XML to JSON
• CSV (Comma Separated Values)
• Easy import/export
• Electronic Data Interchange (EDI)