1.3.2 Databases

What is an entity?

What is an entity?

An item of interest about which information is stored

What is a relational database?

A database which recognises the differences between entities

What is a flat file?

A database that consists of a single file.

What is a primary key?

A unique identifier for each record in the table, for example CarID for a table of car information

What is a foreign key?

The attribute which links two tables together. Exists as primary in one table and foreign in another.

What is a secondary key?

Allows a database to be searched quickly, acts as a secondary index to order and search

What is one-to-one database relation?

Each entity can be linked to 1 other entity, such as husband and wife

What is one-to-many database relation?

One entity can be related to many other entities, such as a mother with multiple children

What is many-to-many database relation?

One entity can be associated with many other entities and the same applies the other way round

What is normalisation?

Process of coming up with the best possible layout for a relational database

What are the 3 types of normalistation?

First normal form, second normal form and third normal form

What is first normal form?

No attribute that contains more than a single value

What is second normal form?

A database which doesn’t have any partial dependencies.

What is 3rd normal form?

If database is in 2nd form and contains no non-key dependencies it is in third normal form

Ways of capturing data

Manual entry, Magnetic Ink Character Recognition, Optical Mark Recognition, Optical Character Recognition

MICR

Magnetic Ink Character Recognition, used by banks for cheques

OMR

Optical Mark Recognition, used for multiple choice questions on a test

OCR

Optical Character Recognition, used for other forms of responses on tests than multiple choice

Selecting and Managing data

Important part of data pre-processing, involving selecting data that fits a certain criteria to reduce the volume of input

Exchanging data

Transferring the collected data, commonly EDI (electronic data interchange), which requires no human interaction

SQL

Structured query language, declarative language used to manipulate databases

SELECT

Used to collect fields from a given table

FROM

Specifies which table information will come from

WHERE

Specifies the search criteria

ORDER BY

Specifies whether you want it in ascending or descending order

JOIN

Combines rows from multiple tables based on a common field between them

CREATE

Allows you to create new databases

ALTER

Add, delete, modify

UPDATE

Update a record in a database table

DELETE

Delete a record from a database table

Data types

CHAR, VARCHAR, BOOLEAN, INT, FLOAT, DATE, TIME, CURRENCY

Referential Integrity

Process of ensuring consistency, such as making sure data is not removed if it is required elsewhere

Transaction processing

Single operation executed on data

ACID

Atomicity, consistency, isolation, durability

Atomicity

Transaction must be completed in its entirety or not at all

Consistency

Must maintain referential integrity rules

Isolation

Simultaneous executions of transactions should lead the to the same result as if they were run one after another

Durability

Once a transaction is executed it will remain so regardless of other circumstances

Record Locking

Preventing simultaneous access to records in a database in order to prevent inconsistencies or loss of updates

Deadlock

Problem with record locking