AQA A2 Computing 5.2 Database Design

Question 1

Relation

Answer 1

Aset of attributes and tuples, modelling an entity (a table)

Question 2

Attribute

Answer 2

A property or characteristic of an entity (a named column in a table)

Question 3

Tuple

Answer 3

A set of attribute values (a row in a table)

Question 4

Primary Key

Answer 4

An attribute which uniquely identifies a tuple

Question 5

Relational Database

Answer 5

A collection of tables

Question 6

Composite Key

Answer 6

A combination of attributes that uniquely idetify a tuple

Question 7

Foreign key

Answer 7

An attribute in one table that is a primary key in another table(used to form a link between tables)

Question 8

Referential integrity

Answer 8

If a value appears in a foreign key in one table, it must also appear in the primary key in another table

Question 9

Normalised entities

Answer 9

a set of entities that contain no redundant data

Question 10

Normalisation

Answer 10

A technique used to produce a normalised set of entries

Question 11

1NF

Answer 11

Atomic data test
A relation is in 1NF if it contains no repeating groups
To convert an unnormalised relation to 1NF either:
Flatten the table and change the primary key, or
Decompose the relation into smaller relations, one for the repeating groups and one for the non-repeating groups.
Remember to put the primary key from the original relation into both new relations.
This option is liable to give the best results.

Question 12

Atomic data test

Answer 12

The table does not contain repeating groups of attributes

Question 13

2NF

Answer 13

Partial key dependance test
A relation is in 2NF if it contains no repeating groups and no partial key functional dependencies
Rule: A relation in 1NF with a single key field must be in 2NF
To convert a relation with partial functional dependencies to 2NF. create a set of new relations:
One relation for the attributes that are fully dependent upon the key.
One relation for each part of the key that has partially dependent attributes

Question 14

Partial Key dependance test

Answer 14

The data is wholly reliant on the primary key.
E.g. an order table which contains customer address would fail as the address is not dependant on the order number. This should be resolved by creating a customer table and linking the order with the customer number

Question 15

3NF

Answer 15

Non-Key dependance test
A relation is in 3NF if it contains no repeating groups, no partial functional dependencies, and no transitive functional dependencies
To convert a relation with transitive functional dependencies to 3NF, remove the attributes involved in the transitive dependency and put them in a new relation
Rule: A relation in 2NF with only one non-key attribute must be in 3NF
In a normalised relation a non-key field must provide a fact about the key, the whole key and nothing but the key.
Relations in 3NF are sufficient for most practical database design problems. However, 3NF does not guarantee that all anomalies have been removed.