Component 9 - Key Definitions

Question 1

Relational database, flat file, primary key, foreign key, secondary key, entity relationship modelling, normalisation and indexing. See appendix 5g.

Answer 1

Databases are “an organised collection of information” which are usually manipulated using SQL.
Flat file: The simplest type of database – all data is kept in one table. Has significant disadvantages such as repeated data (redundancy) and difficulty keeping data accurate/up to date
Relational: A database where each entity has its own table (e.g. customer, appointment etc). Each table is linked by primary/foreign keys which helps to reduce redundancy.
Primary key: A unique identifier for a record in a database table
Foreign key: A primary key from another table acting as a link
Normalisation: The process of taking database design and turning it into a relational design. Normalisation removes repetition, separates data into tables and ensures that there are no many to many relationships. There are 3 normal forms that we care about in the A Level
Entity: An object in a database, e.g. “Vehicle”, “Customer.” Always singular.
Entity relationship modelling: A method of displaying the relationships between tables in a database. Relationships can be 1:1, 1:Many, Many:Many. A many to many relationship breaks normalisation rules and indicates a link table must be created

Question 2

Methods of capturing, selecting, managing and exchanging data

Answer 2

This is down to you – same as with input and output devices, you should be able to suggest sensible methods of collecting data in a database

Question 3

Normalisation to 3NF

Answer 3

1NF: A table is in First Normal Form (1NF) if there are no repeating groups. In other words, each column must contain only a single value and each row must have an item in every column. This can usually be done by putting the data into two tables … separating the repeated data into a separate group.
2NF: To move to 2NF, any partial dependencies must be removed. This basically means each record should not have a composite primary key (the primary key should be unique in its own right). This removes many to many relationships and repeated data
3NF: 3rd Normal Form removes something called “Transitive Dependency.” In plain English this means that the data in each table should only be related to the primary key. If it isn’t, then it needs to be in another table

Question 4

SQL – Interpret and modify. See appendix 5e

Answer 4

SELECT (fields) FROM (table name) WHERE (criteria)
INSERT INTO (table) VALUES (comma separated list of values) UPDATE (table) SET (field=criteria) WHERE (criteria)
DELETE FROM (table) WHERE (criteria)
DROP (table)

Question 5

Referential integrity

Answer 5

Referential integrity refers to the accuracy and consistency of data within a relationship.
In relationships, data is linked between two or more tables. This is achieved by having the foreign key (in the associated table) reference a primary key value (in the primary – or parent – table). Because of this, we need to ensure that data on both sides of the relationship remain intact.
So, referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.

Question 6

Transaction processing, ACID (Atomicity, Consistency, Isolation, Durability), record locking and redundancy

Answer 6

Atomicity: All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are. For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.
Consistency: Data is in a consistent state when a transaction starts and when it ends. For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.
Isolation: The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized (happen one at a time in sequence). For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither. Durability: After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure. For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed