Week 24 Databases

Question 1

DESCRIBE two problems that can occur with databases that are not fully normalised.

Answer 1

Redundant/duplicated data may waste storage space;
NE. data redundancy, data duplication

If data is stored more than once then it could be inconsistent // two copies of the ‘same’ data item might store different values;

If data is stored more than once then each copy of the data would need to be updated if it changed;
NE. eliminate update anomalies

It might not be possible to store data about one type of entity without creating a record for another type of entity // if a record for one type of entity does not exist then it might not be possible to store data about another type of entity;
NE. eliminate insertion anomalies

When a record for one type of entity is deleted it might delete the data about another type of entity // it might not be possible to delete a record for one type of entity without deleting the data about another type of entity;
NE. eliminate deletion anomalies

May be difficult to select/edit data if it is not atomic // if there are repeating groups;

Question 2

What does it mean for a database to be fully normalised (3rd normal form)?

Answer 2

every attribute is dependent on the key;
the whole key;
and nothing but the key;

Question 3

Explain the term ‘relational database’

Answer 3

A collection of tables which can be linked together by means of primary and foreign keys.

Question 4

Define the term attribute

Answer 4

A property or characteristic of an entity (databases).

Question 5

Define the term primary key

Answer 5

A field that is unique for every record in a table.

Question 6

Define the term composite primary key

Answer 6

A primary key made up of two or more fields.

Question 7

Define the term foreign key

Answer 7

A field in one table that is also a primary key in another table.

Question 8

Describe how timestamp ordering can be used to manage concurrent access to a database. [3]

Answer 8

Timestamps are generated for each transaction // timestamps indicate the order that transactions occurred in;

Database records time(stamp) of last read / last write transaction for each record;

Database server applies rules to determine if processing a transaction will result in loss of data integrity (and if so aborts the transaction);

•   if a transaction tries to write to a record, then the transaction should be aborted if the read/write timestamp on the record is greater than the time at which the transaction started

•   if a transaction tries to read a record, then the transaction should be aborted if the write timestamp on the record is greater than the time at which the transaction started.

Question 9

Describe how record locks manage concurrent access to a database [2]

Answer 9

When a user starts to edit a record a lock is set on the record;

Other transactions/users cannot edit (A. access) the record until the lock is released;

Question 10

Describe how commitment ordering manages concurrent access to a database

Answer 10

Commitment ordering looks to optimise the order in which transactions are completed.

It will consider the order of the transactions alongside dependencies to ensure any conflicts are resolved.

Transactions which will cause a deadlock will be blocked until any dependant action is complete.

An algorithm is used to create a commit order which avoids conflicts between transactions.