1.3.2 databases

relational databases

• an entity is an item of interest about which information is stored

• a relational database recognises the differences between entities by creating different tables for each entity

flat file database

• a database that consists of a single file

• the flat file will most likely be based around a single entity and its attribute

• Entity1 (Attribute1, Attribute2, Attribute3 …)

primary key

• a primary key is a unique identifier for each record in the table

• the unique identifier is the “ID” as this is always different for each row in the table

• the primary key is shown by underlining it

foreign key

• attribute which links two tables together

• the foreign key will exist in one table as the primary key and act as the foreign key in another

secondary key

• allows a database to be searched quickly

• the patient is unlikely to remember their patientID but will know their surname

• therefore, a secondary index (secondary key) is set up on the surname attribute

• this makes it possible to order and search by surname which makes it easier to find specific patients in the database

entity relationship modelling

• One-to-one

  • each entity can only be linked to one other entity

• One-to-many:

  • table can be associated with many other tables

• Many-to-many:

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

normalisation

the process of coming up with the best possible layout for a relational database, normalisation tries to accomplish the following things

• no redundancy (unnecessary duplicates)

• consistent data throughout linked tables

• records can be added and removed without issues

• complex queries can be carried out

first normal form - normalisation

must be no attribute that contains more than a single value

second normal form - normalisation

• a database which doesn’t have any partial dependencies and is in first normal form can be said to be in second normal form

• this means that no attributes can depend on part of a composite key

third normal form - normalisation

• if the database is in second normal form and contains no non-key dependencies, it is in third normal form

• a non-key dependency means the attribute only depends on the value of the primary key and nothing else

indexing

• a method used to store the position of each record ordered by a certain attribute

• this is used to look up and access data quickly.

• the primary key is automatically indexed; however, the primary key is almost never queried since it is not normally remembered

• this is why secondary keys are used

• secondary keys are indexed to make the table easier and faster to search through on those particular attributes

SELECT, FROM, WHERE, ORDER BY - SQL

• the SELECT statement is used to collect fields from a given table and can be paired with the FROM statement to specify which table(s) the information will come from

• the WHERE statement can be used in conjunction to specify the search criteria

SELECT MovieTitle, DatePublished  

FROM Movie 

WHERE DatePublished BETWEEN #01/01/2000# AND #31/12/2005#  

ORDER BY DatePublished;

ORDER BY - SQL

values are automatically placed in ascending order and adding ‘Desc’ to the end of statement will cause values to be displayed in descending order

ORDER BY DatePublished Desc

JOIN - SQL

JOIN provides a method of combining rows from multiple tables based on a common field between them

SELECT Movie.MovieTitle, Director.DirectorName, Movie.MovieCompany 

FROM Movie  

JOIN Director  

ON Movie.DirectorName = Director.DirectorName

CREATE - SQL

allows you to make new databases

CREATE TABLE TableName 

( Attribute1 INTEGER NOT NULL, PRIMARY KEY,  Attribute2 VARCHAR(20) NOT NULL, …  ) 

Data Types - SQL

• CHAR(n): this is a string of fixed length n

• VARCHAR(n): this is a string of variable length with upper limit n

• BOOLEAN: TRUE or FALSE

• INTEGER/INT: integer

• FLOAT: number with a floating decimal point

• DATE: the date in the format Day/Month/Year

• TIME: the time in the format Hour/Minute/Second

• CURRENCY: sets the number as a monetary amount

ALTER - SQL

used to add, delete or modify the columns in a table

adding a column

ALTER TABLE TableName  

ADD AttributeX and their dataTypes  

deleting a column

ALTER TABLE TableName 

DROP COLUMN AttributeX  

modifying a column

ALTER TABLE TableName 

MODIFY COLUMN AttributeX NewDataType 

INSERT INTO - SQL

used to insert a new record into a database table

INSERT INTO (column1, column2)  

VALUES (value1, value2)  

UPDATE - SQL

used to update a record in a database table

UPDATE TableName  

SET column1 = value1, column2 = value2

WHERE columnX = value 

DELETE - SQL

used to delete a record from a database table

DELETE FROM TableName  

WHERE columnX = value 

referential integrity

• the process of ensuring consistency

• this ensures that information is not removed if it is required elsewhere in a linked database

• if two database tables are linked, one of these tables cannot be deleted as the other table requires its contents

transaction processing

A transaction is defined as a single operation executed on data. However a collection of operations can also sometimes be considered a transaction.

ACID (Atomicity, Consistency, Isolation, Durability)

• Atomicity: - A transaction must be processed in its entirety or not at all

• Consistency: - A transaction must maintain the referential integrity rules between linked tables

• Isolation: - Simultaneous executions of transactions should lead to the same result as if they were executed one after the other

• Durability - Once a transaction has been executed it will remain so regardless of the circumstances surrounding it, such as in the event of a power cut.

record locking

• the process of preventing simultaneous access to records in a database

• it is used in order to prevent inconsistencies or a loss of updates

• while one person is editing a record, this ‘locks’ the record so prevents others from accessing the same record

redundancy

• the process of having one or more copies of the data in physically different locations

• this means that if there is any damage to one copy the others will remain unaffected and can be recovered.

capturing data

• data needs to be input into the database and there are multiple methods of doing this

• the chosen method is always dependent on the context

• for example, if pedestrians are participating in a survey, their responses will need to be manually entered into the database

• data is also captured when people pay cheques

• banks scan cheques using Magnetic Ink Character Recognition (MICR)

• all of the details excluding the amount are printed in a special magnetic ink which can be recognised by a computer but the amount must be entered manually

  • Optical Mark Recognition (OMR)

• Other forms use Optical Character Recognition (OCR)

selecting and managing data

• selecting the correct data is an important part of data preprocessing

• this could involve only selecting data that fits a certain criteria to reduce the volume of input

• for example, a camera catching speeding cars will only select cars going above a certain speed

• then, background information will be removed so only the number plate is added to a record

• collected data can alternatively be managed using SQL to sort, restructure and select certain sections

exchanging data

• the process of transferring the collected dat

• one common way of exchanging data is EDI (Electronic Data Interchange)

• this doesn’t require human interaction and enables data transfer from one computer to another

what is SQL

• Structured Query Language

• declarative language used to manipulate databases

• enables the creating, removing and updating of databases