10. Fundamentals of Databases

A Relational Database is a Database which stores information in Relations/Tables. Each Relation will consist of Records/Rows and Attributes/Columns.

SQL is Structured Query Language, a language for communicating with Relational Databases.

A Primary Key is a unique identifier for each Record in a Relation.

A Foreign Key is an identifier for a Record in another Relation.

A Composite Key is a Key (Primary or Foreign) which consists of multiple Attrributes.

A Relation is in First Normal Form if it has no repeating Attribute or group of Attributes.

A Relation is in Second Normal Form if it is in First Normal Form and there are no Partial Key Dependencies. A Partial Key Dependency is when an Attribute is dependent upon only part of a Composite Key.

A Relation is in Third Normal Form if it is in Second Normal Form and there are no Non-Key Dependencies. A Non-Key Dependency is when an Attribute is dependent upon another Attribute which is not part of the Primary Key.

Normalisation removes repetition/redundancy in a Database which has the following benefits: • Easier to update, as each value is only stored in one place. Repeating data can lead to an inconsistent Database. • Quicker to search, as we have smaller Relations with fewer Attributes. • Splitting data over multiple tables places constraints on how the the Database can be manipulated. These constraints may enforce Referential Integrity.

Record Locks prevent multiple users from changing the same Record in a Relation at the same time which can lead to inconsistent results or corrupt data.

DeadLock is when cyclical dependency appears between the Record Locks that users have created whilst interacting with a Database. For example user A may have locked Table 1 whilst waiting for user B to unlock Table 2, whilst user B is waiting for Table 1 to be unlocked to continue.