DataBases

A Level Fundamentals of databases

Data modelling

the process of of producing an abstract model representing the organisation and structure of the data within a given context.

Entity

A concrete or conceptual distinct object about which data must be stored. For instance, a book for a library.

Attribute

a single property of an entity. title of a book for instance

Entity identifier

An attribute or collection of attributes that uniquely identifies each instance of an entity.

Relationship

A two-way link between two entities.

Flat File databases

simple way of storing data in a text file. Each line represents a record, with fields separated by commas.

Relational Database

It organises data across multiple tables, where each table stores data for a particular entity. Tables are related through common fields namely primary and foreign key fields. Avoids data duplication making querying more efficient.

Foreign Key

The field of one table that is the primary key of another table.

Select from with where & and

using like

using between

using join on multiple tables

updating tables

deleting data

Issues with Data Duplication

Data Redundancy - unnecessary duplication of data within the database. Increased storage costs and reduced performance because of slower querying times.

Data Inconsistency - update anomalies may cause certain instances of duplicated data to vary after updates, meaning the data is no longer reliable.

Database Normalisation and why

organizing data into related tables so that each piece of data is stored once. It minimizes data duplication, data redundancy, and data inconsistency by preventing update, insertion, and deletion anomalies.

Atomic data

fully decomposed into multiple attributes

Repeating group

two or more fields store data of the same attribute in a single record.

Properties of 3rd normal form

Every non-key attribute is dependent upon the key, the whole key, and nothing but the key. All data is fully atomic, and there are no repeating groups.

Client-server database

provides simultaneous access to a single central database for multiple clients.

Pros and Cons of Client Server Databases

Pros: all users have access to the same, up-to-date data. Data can be accessed by all clients without duplication or distribution of data, reducing redundancy and eliminating data duplication.

Cons: simultaneous access of a server’s resources can lead to network congestion and worse performance.

Concurrent Access

when two client access the database simultaneously. Causes data integrity issues due to lost updates and insertion anomalies.

Lost Update problem

when two clients access the same record and try update it simultaneously, the second update overwrites the effects of the first on the record causing incorrect data to be written.