Databases AQA

Database

An organised and persistent collection of data

Flat-File Database

A database for only 1 table at a time, storing those attributes independently

Disadv of flat file

Can lead to data redundancy if a field is repeated and trying to be updated

Primary key

A field that uniquely identifies a record

Foreign Key

The linking field in the foreign table formed when a relationship is made

Secondary Key

A key field that can be used to access a table in a different way

Entity Relationship

A graphical representation that depicts relationships in relational databases (e.g 1→M, M→M)

Index

The process of creating a database index, which is a data structure that improves the speed of data retrieval

Normalisation

The process of arranging data into tables

1NF → 1 table, 2NF → 2 tables etc…

Atomic Data

Data that can’t be broken down

1NF (First normal form)

Data is atomic, no repeating attributes, records have primary keys

2NF (Second normal form)

meets 1NF, no partial dependencies on non-key attributes

3NF (Third normal form)

meets 1NF and 2NF, no dependencies on non-key attributes

SQL for selecting an item from a table

SELECT “wantedField”

FROM “tableName”

WHERE “item = wantedItem”

SQL for ordering by field

ORDER BY “fieldName” (asc/descending)

SQL for “like”

WHERE “fieldName” LIKE “wantedItem”

SQL for inserting to a table

INSERT INTO “tableName”

“fieldName1, fieldName2…..”

VALUES “value1, value2….”

SQL for deleting from a database

DELETE FROM “tableName”

WHERE “fieldName” = “condition”

SQL for updating a table

UPDATE “tableName”

SET “fieldName” = “value1, value2…”

WHERE “fieldName” = “condition”

SQL for joining 1 table to another

JOIN “tableName1”

ON “tableName2”.”fieldName2” = “tableName1”. “fieldName1”

SQL for dropping a table

DROP TABLE “tableName”