Comsci

Database

A database is an organised collection of data that can be easily accessed, managed and updated. Databases are used in everyday systems such as school records, banking systems, and social media platforms. They help ensure information is structured, searchable and efficiently stored

DBMS

Databases are managed using Database Management Systems (DBMS) which provide tools to insert, update, delete and query data.   

DMS

1.        DMS different systems are used to manage data depending on complexity and use case

a.       Text files – basic storage, simple but lacks structure and validation

b.       Spreadsheets – flexible and user friendly, suitable for small datasets. Limited in handling relationships

c.       Relational Databases(RDBMS): Use tables with rows and columns. Allow relationships between tables using keys. SQL is commonly used

d.       NoSQL Databases- designed for unstructured or large-scale data.

Data problems

a.       Redundancy – repeated data wastes space and risks inconsistency

b.       Security – Data must be protected from unauthorised access

c.       Size – large datasets can shlow down access and require more storage

d.       Ease of updating – Manual systems are error-prone. Centralised updating helps avoid inconsistencies

e.       Importance – critical data(eg.medical record) require higher reliability and protection

f.          Accuracy – errors in data lead to faulty decisions

Data solutions

a.       Scalable – can handle increasing data without losing performance

b.       Accurate – validation rules ensure correct data is entered

c.       Consistent – centralised data updates reduce errors

d.       Accessible - available across devices and networks

e.       Permanent – Data is not lost unexpectedly due to backups and transaction logs

f.          Secure – Access controls, encryption, and backups protect data

ACID test

a.       Atomic – All parts of a transaction succeed or none do. For example, transferring money must withdraw from one account and deposit to another

b.       Consistent – Transactions bring database from one valid state to another

c.       Isolated – transaction do not interfere with each other. For example, two people booking the same seat

d.       Durable – once a transaction is completed, it remains so, even after a power failure

Features of a relational database

1.        Relational databases organise data into tables, which resemble spreadsheets but with strict rules and defined relationships

a.       A table stored data about one entity (eg. Students, books, orders)

b.       Each table has multiple columns(also called fields) where each column stores one type of data (eg. Name, Age, ISBN)

c.       Each row(also called a record) contains all the information about a single instance of the entity(eg. A particular student or book)

d.       Fields may include numbers, text,dates or links to other tables

Tables are linked together using keys, which  help manage relationships and prevent data duplication

keys

a.       Primary key – A single field that uniquely identifies a record (e.g., Student ID).

b.       Composite Key - A combination of two or more fields used together to create a unique identity (e.g., Student ID + Subject Code to identify a course enrolment).

c.       Synthetic/surrogate key - An artificially created unique identifier, such as an auto-incrementing number. These are often used when no natural unique field exists.

d.       Naturally occurring primary key - A field already in the data that is unique (e.g., a government-issued ID number or ISBN for a book).

relational databases

1.        Relational databases use relationships to link tables and reduce data redundancy. These links are made using keys-typically a primary key in one table and a foreign key in another. ONE - ONE, ONE - MANY , MANY - MANY

Data dictionary

A data dictionary is a detailed document or table that outlines all elements of a database. Data dictionaries support both technical and non-technical users in working with the database structure correctly.

Normalisation

1.        Normalisation is the process of refining a database to reduce duplication, ensure consistency, and make it easier to maintain. Normalisation is often performed during the design phase and improves both storage efficiency and data integrity.

1NF

First Normal Form (1NF) ensures that each field contains only one value (no lists or sets), and that all records are unique. Additionally, there should be no repeating groups (i.e., if you find yourself creating fields in a table like Subject1, Subject2, etc., this indicates that there should be a linking table).

2NF

a.       Second Normal Form (2NF) builds on 1NF by ensuring that all non-key fields are fully dependent on the whole primary key, which is especially relevant when using composite keys.

3NF

a.       Third Normal Form (3NF) removes fields that do not depend directly on the primary key, helping avoid indirect duplication (e.g. storing a product's category description in the order table instead of linking to a category table).

Dernormalisation

a.       Denormalization is the process of intentionally violating the rules of normalisation. Sometimes, it is simply more convenient to have repeating groups in a table, so the database designer may intentionally include Email1 and Email2 for a User if the solution requires it, for example.

Validation

a.       Validation - Automatically checks data as it is entered (e.g. ensuring a date of birth is in the past, or an email contains “@”). This prevents incorrect or unusable data from being stored.

Null

A special marker that indicates a field has no value. It is different from zero or an empty string and must be used carefully. Fields may be set to disallow nulls if the information is required.

Referntial intergrity

a.       A rule that ensures foreign keys must match existing primary keys. It prevents, for example, an Order referring to a Customer ID that no longer exists. Maintaining referential integrity ensures all data links are valid and the database remains trustworthy.

                                                                                 i.        Cascading deletion - When a record is deleted, related records in other tables may also be deleted automatically. For example, deleting a Customer might also delete all their Orders. This must be used with caution to avoid accidental data loss.