DATABASES

DATABASES

1. The Database Concept

   • A database is a persistent, related and organised store of data
     • Persistent- data and structured are maintained even when data handling applications are no longer running
     • Related- the data in a database is not just a haphazard collection of facts but individual terms of data have a connection of some sort
     • Organised- data is stored in a structured way using tables, records and fields. It is easy for users and applications to add, delete, edit, search and manipulate data
   • Data handling software can be used for creating, retrieving, updating and deleting data (CRUD)
   • Computerised data bases are better than manual databases because:
     • Data can be accessed by multiple users at the same time
     • Data can be queried, interrogated and resulting answers viewed
     • There is a lower risk of making errors in data processing
     • Data can be outputted to suit users needs, on screen or printed
     • They can hold a vast amount of data, not limited by physical storage space
     • Data can be sorted more easily
   • Examples of computerised databases
     • Search engines e.g. Google
     • Police criminal record database
     • School databases

2. DBMS

   • The DBMS will allow enable a user to perform specific actions such as:
     • Creating an empty database
     • Deleting an entire database
     • Creating an deleting tables
     • Inserting new records into tables
     • Updating and deleting records
     • Being able to extract data sets
     • Being able to produce reports that summarise the data
     • Being able to process the data to create new data
     • A DBMS allows multiple user access, but only holds one copy of data, so avoids redundant data
     • Data is presented to different users in different ways
   • Database Management System
   • A DBMS is an application designed to control all aspects of a database
   • A DBMS is desirable as separating an application and database means the database can be accessed separately by other means. Also data is protected from corruption by multiple access

3. Relational Databases

   • A relational database holds its data over a number of tables. Records within the tables are linked to records held in other tables
   • Advantages of relational databases:
     • More efficient storage
     • Data is only stored once so redundant data is avoided
     • Complex queries can be carried out
     • Better security- certain tables can be made confidential
     • Data can be added and removed easily
     • Avoids inconsistent records
     • Easier to change data and data format
   • A foreign key is used to link tables together and create a relationship. It is a field in one table that is linked to the primary key in another table so data does not need to be repeated
   • An entity is a person, place, thing or concept about which data can be collected
   • Logical Operators- these allow more than one field at the same time to be used to extract certain records
     • The AND operator lets you combine two fields
     • The OR operator allows for either part of the statement to be true in order to return a record
     • The NOT operator wants the condition to be false in order to return a record
   • Validation
     • Validation aims to make sure that data is sensible. reasonable, complete and within acceptable boundaries
       • Range check
         • Commonly used when you are working with data which consists of numbers, currencies, dates or times
         • Allows you to set suitable boundaries
       • Type check
         • Ensures the correct type of data is entered into a field
       • Length check
         • Ensure the correct number of characters are entered
         • Can be used to make sure a password is not too short (minimum 8 characters)
       • Presence check
         • Makes sure that a critical field cannot be left blank
       • Lookups can be used to pre-fix the possible inputs