1.3.2 Databases

what is a relational database ?

many tables where relationships exist between entities.

what is a flat file ?

database consisting of one table, often related to only one entity

what is a primary key ?

entity identifier that is unique and mandatory.

what is a foreign key ?

field that links to a primary key in another table in a relational database

what is a secondary key?

any field in the database that is not mandatory but still contains unique values.

what is a composite key?

a primary key that consists of more than one attribute

what is referential integrity ?

Ensures that relationships between tables stay consistent. If the foreign key in one table refers to the primary key in another referential integrity stops you from deleting or changing that primary key if its still being used. This prevents data from becoming orphaned.

what is normalisation ?

the process used to come up with the best possible design for a database, organising data in a way that reduces data redundancy and improves data integrity. Tables are organised so that data is necessary and stored consistently, the structures are flexible and can be used for complex queries.

Condition for 1NF

data is atomic, has a primary key, no repeating values or attributes

Conditions for 2NF

must be in 1NF, no partial dependencies (secondary keys must depend on all parts of a composite key)

what is a partial dependency?

when an attribute is only dependent on one part of the composite key.

Conditions for 3NF

must be in 2NF, no transitive dependencies(non-key dependency)

what is a transitive dependency ?

non-key dependency, where an attribute is determined by the value of another attribute which is not part of the key.

secondary key fields must only depend on the primary key

what issues are caused by data redundancy

inefficiencies and inconsistencies in the data

advantages of normalisation

• no data redundancy ( achieved at 1NF)

• easier to modify and maintain.

• data integrity is kept as there is no duplicates.

• faster sorting and searching.

• produces smaller tables and fields, less storage space

ERD shows:

Entities- tables

Attributes - fields

Relationships - lines linking entities

explain how foreign keys and referential integrity work together in s relational database.

A foreign key is a field in one table that links to the primary key of another table. Referential integrity ensures that data remains consistent between tables. It prevents deleting or changing a record that is still being referenced by another table.

transactional proccessing

a single operation executed on data.

what is acid

rules for transactional processing which protect the integrity of a database, they describe the properties a transaction must conform to.

atomicity

must be processes in its entirety or not at all

consistency

a transaction must maintain the referential integrity rules between linked tables

isolation

a transaction must not interfere with other transactions.

Durabilty

once a transaction is complete it is permanently stored.