7.1 The concept of a database/ 7.2 The concept of a relational database/ 7.3 Structured Query Language

Database

A structured collection of data

Record

A collection of data for one object

Field

Used in a database to provide category headings for each piece of data

Table

Holds a collection of records

Primary key

Used to give each record a unique identifier

Relational Database

A database with multiple tables that are linked together using keys

Data redundancy

When the same piece of data is held in several places, leading to inconsistencies and wasted storage space

Data inconsistency

When the same data exits in multiple places but instances of the data aren’t identical

Foreign key

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

Advantages of relational databases

Minimises inconsistencies and eliminates data redundancies

3 types of entity relationships

One-to-one, one-to-many, many-to-many

List of fields to be displayed

SELECT

List the table or tables where the data will come from

FROM

List of search criteria

WHERE

List the fields that the results are to be stored on

ORDER BY ASC/DESC

Data type that stores whole numbers

Integer

Data type that stores decimal numbers

Real/ Float

Data type that stores dates and times

Date, Time, Datetime

Data type that stores fixed strings up to 8,000 characters

Char

Data type that stores variable strings up to 8,000 characters

Varchar

Data type that stores variable length strings up to 2GB of data

Text

Write an SQL query that finds the names (PetName) and dates of birth (DateOfBirth) of all dogs (Type) born on or after 1st January 2019 (from Animal)

SELECT PetName, DateOfBirth

FROM Animal

WHERE DateOfBirth >= 01/01/2019

AND Type = “dog”

Wildcard to display all fields in the records that satisfy given criteria

*

Equal to a value within a set of values

IN(…)

Within a range, including the two values which define the limits

BETWEEN … AND …

Field does not contain a value

IS NULL

Syntax for specifying a field in a relational database (tablename is option unless the field name appears in more than one table)

tablename.fieldname

Linking two tables in SQL

WHERE tablename.primary key = othertablename.foreignkey

Wildcard for 0, 1 or multiple characters

%

Wildcard for one single character

_

Used in a WHERE clause to search for a specified pattern in a column

LIKE

Insert a new record into a database

INSERT INTO tableName(column1, column2, …)

VALUES (value1, value2, …)

Amend a record in a database

UPDATE tableName

SET column 1 = value1, column2 = value2, …

WHERE column = value

Delete a record from a database

DELETE FROM tableName

WHERE column = value