3.7 relational databases and SQL

Relational databases and SQL

Databases

a persistent(will stay until specifically deleted) store of organised data

Field (column)

a single type of data from a database table

Record

a set of related fields on a single entity within a database table

Table

a complete set of relateed records on the same topic

Primary key

a field that uniquely identifies a record in a table

Foreign key

the linking field in the foreign table formed when a relationship is made, the foreign key becomes the primary key of the new table

Data redundancy

data is repeated in a database table, making the database file much larger then necessary

Data inconsistency

the more redundant data there is, its more likely errors are introduced and that some of the data will be inconsistent

Relational database

made up of more than one table, removes data redundancy and inconsistency problems, each entity has its own table which refers to only one entity

What is SQL used for?

to access data stored in a data base

SELECT

identifies the fields to return from the database

FROM

identifies which table(s) the data will be returned from

WHERE

allows the programmer to include criteria, with only matching records being returned

ORDER BY

allows the programmer to put the data returned by the query into either ascending (ASC) or descending (DESC) order

Compulsory in an SQL query

SELECT and FROM

Not compulsory in an SQL query

WHERE and ORDER BY

Wildcards

special symbols used in searches

‘*‘ wildcard in SQL

used as SELECT* meaning all fields from the table should be returned

Extracting data from relational databases

an extra WHERE clause joins the primary key in one table to the foreign key in the other table

Extracting data from relational databases (SQL)

WHERE table1.primarykey = table2.foreignkey

What does the WHERE…AND clause do?

specifies further criteria if only certain records are required between the two tables

INSERT INTO…VALUES

used to add new records, full or partial, into a database table

UPDATE…SET…WHERE

used to update existing records in a database table

DELETE FROM…WHERE

used to delete records from a database table