SLR10 - Databases

Benefits of digital databases

• Easier to retrieve, add/remove, update, modify data

• Easier to back up & make copies of data

• Multiple people can access the data at same time & from different locations

Database terms

• Data sored in table (file)

• Table contains records (rows)

• Table contains fields (columns)

Flat file database

• Contains single table

• Crated using database or spreadsheet software

• Often comma separated

• Often store small amount data eg: storing contact details, small product database, game collection

Flat file database benefits & drawbacks

• Easy setup

• Repeated data

• Slow query

• Difficult to maintain

Relational database

• Multiple tables = reduce repeated data

• Need a common field in tables for relationship to work

Primary key

A field in each record that is guaranteed to be unique

Eg: Student ID number would be unique for each student ∴ would be the primary key

Foreign key

• A field that is the primary key in a related table

• Makes the link

Index

• Provides the position of each record according to its primary key

• Makes retrieval of records referenced by the index much quicker

Secondary key

• Set up using a field that would be used as a search criteria

• Uses to query faster/easier

Handling data

• Capturing - how get data into database if first place

• Selecting - How query data & retrieve

• Managing - How manage, manipulate, add edit, delete data

• Exchanging - How exchanging data with other people/system

Capturing data - Paper based forms

• Manual input by typing into computer system

• Ensured to be fast by:

  • Parts form clearly labelled

  • Instruct to complete in black pen

  • Instruct to complete in capital letters

  • Use tick boxes

  • Squares for entering each letter separately

Capturing data - Optical character recognition (OCR)

• Technology automatically reads text by interpret shape of letters

• Often used by post office and cameras

Capturing data - Optical mark recognition (OMR)

• OMR often used for multiple choice & lottery tickets

• Fast & efficient collect data & input

Capturing data - Automated methods

• Magnetic strips

• Chip and pin

• Barcode

• QR codes

• Sensors

Selecting data - Select Query Language (SQL)

• Allow efficient retrieval, deletion, manipulation of data

• Commands:

  • SELECT

  • FROM

  • WHERE

Selecting data - Query by Example (QBE)

• Graphical query language - uses visual tables

• Query built using QBE then converted to statement that can be executed against database

• Using QBE means you don’t need to remember SQL syntax

Both SQL and QBE allow:

• Specify table

• Specify field

• Specify criteria

• Specify output storing

• Use Boolean expression

Managing data

• Can be done by data manipulation language (DML) such as SQL

• Use commands:

  • UPDATE

  • DELETE

  • INSERT

• Can also use Database management system (DMS)

• Hides underlying structures and remains integral by:

  • Enforcing validation rules

  • Encryption

  • Manage multiple users

Exchanging data - common formats

• XML and JSON both human readable, formats for structuring data

• Both are standards designed for transporting data

• Comma separated values (CSV)

Exchanging data - manual methods

• Memory stick

• Optical media

• Removable hard disk

• Email

• Paper - based

Exchanging data - automatic methods

• One live connection method is Electronic Data Interchange (EDI), protocol between two systems to facilitate exchange of data

Normalisation

• The concept of splitting table in database

• 0NF - flat file

1NF

1. All field names should be unique

2. Values in fields should be the same type

3. Values in fields should be atomic

4. No two identical records

5. Each table needs primary key/composite key

2NF

1. Data already in 1NF

2. Any partial dependencies have been removed (all fields depend on primary key)

Dependency & Partial Dependency

Dependency - Only one field needed and every other field can be fetched using it

Partial Dependency - one or more fields depend on only part of the primary key - in case of composite key

Spliting table

• Eg: The fields course name, lecturer initials, lecturer name are only dependant on course num, not on student name, gender, DOB etc.

• To fix, split table at course num and remove remaining data to new table

• However creates many-to-many relationship

How to fix many-to-many

• Create linking table

• Assign primary keys from two initial tables as composite key for new linking table

3NF

1. Data already 2NF

2. Any transitive dependencies have been removed - field is dependant on field that is not part of primary key

• Split where there is a transitive dependency

How to use SELECT,FROM,WHERE

SELECT population (field)

FROM World (table)

WHERE name = “Albania” (name is a field)

• SELECT * (returns all fields)

• WHERE name LIKE “A%” (any names starting with A)

• Can also have AND after WHERE

• Can also have nested selection:

SELECT name

FROM World (table)

WHERE population >

( SELECT population

FROM World

WHERE name = “Algeria”)

• Selects country name where pop > Algeria’s

Inserting data

INSERT INTO myTable(field1, field2, field3, …)

VALUES (value1, value2, value3)

Deleting record from database

DELETE FROM myTable

WHERE fieldName = <condition> (eg. pop > 100000)

Update command

UDDATE myTable

SET <field name> = <value>, <field name> = <value>, etc.

WHERE <field name> = <condition>

Joining data from two or more tables

JOIN myTable

ON myTable.field = myTable2.field

(rewatch)

Delete a table

DROP TABLE myTable

Data integrity

Process of maintaining the consistency of a database

Referential integrity

• Refers to the consistency of data within a relationship

• Requires that, whenever a foreign key value is used it must reference a valid, existing primary key in related table

Transactional processing

To ensure data integrity of a transaction, it must conform to ACID rules

What does ACID stand for

• Atomicity

• Consistency

• Isolation

• Durability

Atomicity

Changes to data base either completely performed or not at all

Consistency

• Any changes in database must retain overall state of database

• Eg: transferring money, total value of funds in both accounts is same at start & end

Isolation

• Transaction must not be interrupted by another

• DBMS enforces this by record locking - record being affected by transaction are locked

• Other transaction must wait till current is complete

Durability

After transaction successful, changes to data persist and are not undone, even in the event of a system failure