OCR AS ICT Unit 1 Chapter 5

Table (Entity) - A data structure made up of rows and columns containing data about items and must have a unique name (inc. fields and records).

Record - A single row within a table that contains data about a single item/event.

Field (Attribute) - An individual data item within a record and holds a single data item.

Primary Key - A unique identifier for a field in a record.

• Simple Primary Key - Made up of a single field only.
• Compound Primary Key - Combines more than one field to make a unique value.

Foreign Key - A field in one table that is linked to a primary key in another, used to link tables together. (data types must be the same)

Referential Integrity - The values found in the table with the foreign key field which have a corresponding value in the table with the primary key. It makes sure that it's not possible to enter a reference in a database to a link which doesn't exist.

Duplicate Data - When data has been repeated more than once.

• Increases storage space required.
• Makes errors more likely.
• Breaks rules of nromalisation.

.

Relationships - The links between fields in different tables.

• One-to-One
• One-to-Many
• Many-to-Many

Normalisation - A process applied to data structures to lower redundancy and to increase its integrity.

First Normal Form (1NF):

• Each table have a primary key.
• Each field name is unique
• Each record is unique.
• All data is atomic (can't be broken down any futher).

Second Normal Form (2NF):

• Must be in 1NF
• All non-key fields can be found using the entire primary key.

Third Normal Form (3NF):

• Must be in 2NF
• All non-key fields are fully dependent on the primary key.

Advantages of Normalisation:

• Removes redunant and duplicate data > Saves storage space
• Increases Integrity.
• Increases Consistency
• Easier Maintenance
• Flexibility for future expansion.

.

Disadvantages of Normalisation:

• Reduces database performance - greater memory/CPU use.
• Costs time due to lots of editing and deleting of data.
• Problems with historical calculations.
• Not appropriate for some types of databases.

Data Dictionary - Contains metadata or descriptions of the key items in a database.

It includes:

• Table Name
• Field Name
• Field Data Type
• Field Length
• Field Default Value - The value that automatically appears on the creation of a new record.
• Field Validation
• Table Security
• Keys - Primary and foreign keys are identified.
• Indexes
• Relationships

Parameter queries return fields from tables where the value of the parameter is matched.

• Simple Query - One parameter is searched.
• Complex Query - More than one parameter is searched.

.

Complex queries are join together in 3 ways:

• AND - To include all records found in the queries.
• OR - To find records that match one or the other searches.
• NOT - Searches for fields NOT within the parameter.

.

Parameters can be static or dynamic:

• Static Query - Parameters can't be changed so the same parameters are used every time it's run.
• Dynamic Query - The end user sets the parameters every time the query is run (usually by a dialogue box).