ITGS

1. ITGS Databases

2. Databases • Databases - organized collections of related data • Databases are at the heart of many IT systems • Example: student records • Databases • Data is stored in an organized manner so that it can be retrieved later • Business databases , transport databases • Search engines - (I.e. Google) use databases to store both the contents of web pages (to enable their searches to work) and details about user’s behavior, including common searches and topics of interest

3. DBMS • Online databases (Wikipedia) - hold huge volumes sof articles, images and video accessible to anyone with an Internet connection • Government databases • Database Management Systems (DBMS) • The software used to create and maintain databases • Spreadsheet applications like Microsoft Excel focus on numerical calculation and are not considered database programs • Database structure - organizing information into fields, records, and tables, determined by how the pieces of data relate to eachother

4. Database Structure • Fields - smallest pieces of information in a database • A property of an item • Record - collection of fields that relate to the same item • Table - collections of records that are all the same type • Primary key, or key field - a field which is different for every record in that table, allowing each record to be uniquely identified

5. Structure of keys • Composite key - using two separate fields together to form a composite key • separately they do not uniquely identify records, but they do when combined • Secondary key or alternative key or index - a field which might frequently be used to search a table. • This key doesn’t have to be unique; a table can have several secondary keys • Data type - determines what sort of data can be stored in each field in a database - text, numbers, a date or an image

6. Flat File Databases • Data types are a good first step to helping ensure alidation of data by allowing only data of the desired type • Flat file databases - a database which contains just one table • Adequate for storing small amounts of simple data • Redundant data - data that is needlessly repeated • Redundant data causes several problems: • Data integrity • The more often data is repeated, the higher the chance that a mistake will be made

7. Relational Database • Relational database - splits data into multiple tables, each containing records related to one type of item or entity • Normalization - creating a relational database which has no redundant data • Relationships • Once the data has been normalized into separate tables, those tables must be linked, or related, to other tables • This way, the details can be referenced from one table to another • One-to-many relationship • for example, one author has many books, but each book has only one author • Foreign key • A field which exists to be part of a relationship with another table

8. Queries • Query - a way of selecting only the records in a database that match certain criteria • Queries are essential for accessing only the required information from a database • Boolean operators - mathematical operators such as greater than or less than to compare values, such as AND, OR, and NOT are used to combine criteria in a search • Parameter queries • Prompt the user for a value when the query is run • The data entered by the user is used as the criteria for the field • Forms • Used to present a user friendly graphical interface for entering and altering data in a database • Forms shield users from the complexity of database tables, queries and relationships

9. CSV and TSV • CSV and TSV are common ways of transferring data between different database and spreadsheet applications • Useful when programs use different file formats to store their data • Both save fields and records into plain text files, which can be read by most database and spreadsheet programs • Also referred to as comma or tab delimited values • CSV (comma separated values) - separate each field value with a comma • TSV (Tab Separated Values) - separate each value with a tab • Reports • A way of presenting data from a table in a database in a more professional manner than the default data sheet view • Report layouts can be customized • Color, data field layout, etc. • Structured Query Language • SQL - a language for writing instructions to manage databases

10. SQL • Designed to be easily understandable by humans • Compatible across different database products • Can be used to create fields, records, tables, run queries, and edit or delete information • Issues: Integrity • Data integrity - ensuring that data is correct, relevant and up to date - one of the biggest issues related to databases • Validation • The process of ensuring that data is in the correct format for a particular field • For example: when a credit card number is entered, it shoul contain exactly 16 numbers • Just ensures correct format, not correct data • Microsoft Access - input mask allows the user to specify requirements for each character of the input

11. Verification • Verification - checks that data is actually correct • Very hard to achieve • Several possible techniques: • Entering data twice • People enter data once, then enter it again, and the system highlights any differences between the two sets of data and requires resolution • Comparing with the original documents - comparing the original paper with the data entered • Verifying with the data subject - asking someone if the data is correct • Issues: Privacy • Data warehouses and centralized databases have allowed vast amounts of data to be collected, stored and processed • Data matching - used to build up a profile of an individual by combining data bout them from several databases • Data mining - discovering hidden patterns and trends in large databases

12. Privacy • The UK has a really large DNA database • UK Data Protection Act specifies how companies can collect, store and use personal data: • Obtain consent • State purpose of collection • Distribution to 3rd parties (disclosure) • Length of storage (destroy when no longer needed) • Security

13. Security • Database auditing - allows data controllers to view both successful and unsuccessful attempts to access, change or delete data in a database