Unit 7: Relational databases and SQL

How is data stored?

Large amounts of data are collected & organised in a data base.

What is a database?

A database is a structured set of data. It is structured in tables that are easy to search and update.

What are records?

A collection of data for 1 object, person or thing

What are fields?

To provide category headings for each piece of data in the database.

What are tables?

Holds a collection of records for a particular themes

Database data types

Integer, real/float/decimal, date/time/datetime, char, varchar, text

Integer

Whole number.

ex = age

Real/Float/decimal

Number with decimal component.

ex = height

Date/time/datetime

to store data and times.

ex = message sent time

Char

fixed length string up to 8000 characters.

ex = telephone number

Varchar

Variable length string up to 8000 characters; values can change.

ex = telephone number

Text

Variable length string up to 2GB of data.

Flat file databases

- A flat-file database stores a single table of data inside a single text file.

Components of a flat-file database

- Stored using a CSV (comma separated values) format.

- Each record appears on a separate line.

- Each field is separated by a comma

Flat-file database issues:

- Leads to inconsistencies in data; hard to search/sort the data.

- Causes redundant data - so databases uses more memory or storage than it needs to, may take longer to search.

Redundancy:

Data redundancy created within a database when the same piece of data is held in several places.

Leads to records with duplicated data and data inconsistency.

Relational databases:

A relational database contains multiple tables. These tables will have links known as relationships. Each table is also known as an entity.

Relational databases allow us to....

- design tables that reduce inconsistencies and eliminate data redundancy.

Primary key:

A primary key can never be repeated. Ensures that each record in the table is unique; very important when dealing with thousands/millions of records.

Foreign key:

A field in a table that references the primary key of another table.

Relationships between tables (entities) =

- One to one

- many to many

- one to many

relationship; one to one

---------

Relationship; many to many

>-------<

Relationship; one to many

--------<

SQL:

Structure query language; used to communicate with a database. Can be used to manipulate databases and retrieve records.

SELECT

used to retrieve data from a database

FROM

the table

WHERE

the criteria has been met.

SELECT *

select all fields

Retrieving data from more than one table:

- you need to specify table that the field names belong to in the SELECT block = ex- tblMembers.Surname

- Need to list each table that you wish to retrieve data from in the FROM block. ex - tblMembers, tblDownloads

- Need to state how the tables are joined together. ex- WHERE tblMembers.MemberID = tblDownloads.MemberID.

Types of SQL commands

- SELECT

- FROM

- WHERE

- ORDER BY; ASC, DESC

- INSERT INTO; VALUES

- UPDATE; SET

- DELETE FROM

Boolean logic

AND, OR, NOT

ORDER BY

ASC = will go from A-Z or 1-10

DESC = Z-A or 10-1

INSERT INTO

Used to record new records in a table.

ex = INSERT INTO Table (Fields)

VALUES (values to put into fields)

UPDATE

Used to modify existing records in a table

ex = UPDATE Table

SET Fields = 'New value'

DELETE FROM

Used to delete existing records in a table

ex = DELETE FROM Table

WHERE Field = 'Deleted value'