5.Databases

5.Databases

1. The Database Concept

   • A persistent organised store of data
   • A database can be created using off the shelf database such as Microsoft Access or MySQL. You can then customise the databse using a high level programming language
   • Fields (columns in the table) for a product database might include , ProductID, Description, Supplier, Price, NumberInStock
   • Records (rows in the table) would be required for each product
   • The database must be able to be interrogated (a query created)
   • The information you get from a database application is only as good as the data you put in. GIGO, Garbage in Garbage out
   • A database must be maintained , as data changes in the real world it must be updated in the database, to ensure that it is up to date and correct

2. Database Management System (DBMS)

   • How the database is constructed
   • Must avoid duplicate data
     • duplicate data leads to data inconsistency
     • Sharing data leads to security issues, as changing the data in one area will affect the data in other areas
   • Provides a separation of the programs from the data
     • Users must login to the database and it determines which parts of the database will be read only, which parts a user will be able to read and write and which parts will not be able to be seen by the user
   • Key features of a DBMS
     • Provides security in terms of views and access rights
     • Enables the creation of reports based on queries
     • Enables a relational database structure
     • Can provide automatic backups
     • Allows interrogation of the data (query)
   • Most common DBMS is Microsoft Access

3. Relational Databases

   • If all the data is stored in one worksheet/ table it is known as a flat file database
     • OK for simple storage but problems can occur
     • Every time a new record is added a lot of data has to be entered which could cause inconsistencies
       • i.e. a school database that has student details such as address and tutor details should have 2 tables to minimise that data that has the entered, related data is kept separate
   • Reducing data duplication (data redundancy) has many benefits
     • Reduces risk of inconsistency
     • Makes maintaining the database much easier
     • Reduces the size of the database

4. Creating a Relational Database

   • A collection of data stored in related tables
   • Entities are groups of data known as either people, objects or events
     • i.e. In the school database example used in the DBMS section the database would have 2 entities students and tutor group
     • In a relational database we would create a table for each entity
   • The tables in a relational database are related by the primary key
     • The primary key in each table makes each record unique
     • For example in the student details and tutor group database the REG initials would be the primary key in the Tutor group table and would also appear in the students table as the foreign key. the Student Number would be the primary key in the student details table
     • A foreign key is a field in one table that is a primary key in another table and is used to create a relationship between those two tables, i.e. the REG initial in the StudentDetails table

5. Forms

   • used to update tables , i.e. add records, deleted records, amend records
   • Can be created with a DBMS to control access to the underlying database
   • Validation can also be added to ensure that the data being entered is reasonable
     • Pick options from a list so that other variations can not be entered, i.e. useful for the REGInitial
     • Validation checks
       • Range checks , a number / date is in a sensible allowed range
       • Type Check, data that is the right type is entered i.e. integer, string
       • Length check, text entered is not too long or too short
       • Presence check, check data exists
       • Format check, checks the format is appropriate i.e. postcode
       • CAN NOT CHECK WHETHER DATA IS CORRECT
   • verification
     • double checks that it has been entered correctly
       • Data is entered twice and the 2 versions are compared, i.e. passwords

6. Queries / Reports

   • Once the data is stored in the database it needs to be interrogated so that the user can find things out about it
   • A DBMS will allow you to construct queries that find data matching specified criteria
     • In MSAccess the Query by Example grid makes this easy
   • Reports present the data into useful infromation
     • A good report should have
       • a date
       • Have a clear title
       • Be laid out in a clear manner
       • Have page numbers
       • Be sorted in a logical sequence

Images and files in the map