IB Computer Science SL | Options A - Databases

Data

Raw fact

Information

Data + Context, It is process and structured, and gives meaning to data

Information system

An information system is a combination of hardware, software, data, people, and processes that work together to collect, process, store, and distribute information to support decision-making and operations.

Database

Organized collection of data on a specific topic

Subset of information system.

How are information systems helpful?

1) Present information in a visually organized way

2) Aids decision making

Transaction

Groups together multiple operations into One unit of logical action.

Independent of other transactions.

ACID

Atomicity, Consistency, Isolation, and Durability. These four properties are crucial for ensuring reliable and valid database transactions.

Atomicity

All-or-Nothing, A property of transactions in a dataase that ensures if of part of the transaction doesn’t go through, the entire transaction rolls back to the previous state

Consistency

Ensures that data is in a consistent state from beginning to end of transaction, only valid data is written in the database.

Isolation

Prevents modification of same data item by two different transactions. Ensures that intermediate state of a transaction is invisible to other transactions.

Durability

Ensures that transactions are saved permanently and committed, does not accidentally get deleted.

Example of Atomicity

Imagine a bank transaction where a user transfers $500 from Account A to Account B.

This transaction involves two steps:

1. Withdraw $500 from Account A

2. Deposit $500 into Account B

Example of Consistency

When moving money from one account to another, ensures total value of money in both accounts is same from beginning to end of each transaction

Example of Isolation

Imagine two users, User A and User B, both trying to buy the last concert ticket at the exact same time.

When User A starts the transaction, the system locks the ticket row.

User B's transaction must wait until A's finishes (committed or rolled back).

Only one user can successfully book the last ticket.

Example of Durability

When moving money from one account to another, ensures that changes are permanent to each account.

Lost Update Problem

When two users update the same data item, but only one of those changes is recorded in the data.

Temporary Update Problem

When one transaction updates a database item and then the transaction fails for some reason. Meanwhile updated item is accessed by another transaction before it is changed back to its original value.

Incorrect Summary Problem

When calculating an aggregate of items on a database while other transactions are updating these items, the aggregate function may calculate some values before they are updates and therefore give an incorrect sum.

Rollback

Signals that a transaction has ended unsuccessfully

Needs of Data Sharing

1) Allows sharing amongst multiple users

2) Any changes are accessible and visible to all users

3) Allows reliable and consistent database management because of different sets of data shared with users having different levels of permission

4) Scaleability and reusability as operation grows

Verification

Make sure data entering the system matches data at the original source exactly

Double Entry Verification

Method used to ensure data has been entered accurately

Asking the user to input the same data twice and comparing the two entries to confirm they match exactly.

Manually Checking Verification

User manually checks if the data in the computer matches the data in the source document

Look up

Ensures the value entered is from a predefined list (e.g., dropdown)

Range Check

Checks that a value falls between two limits (e.g., age between 0 and 120)

Length Check

Ensures data entered is the correct number of characters (e.g., phone numbers have 8 digits)

Validation

Does not allow any data to be entered to a databsae that does not match a specific set criteria, ensures accurate data input.

Characteristics of RDBMS

1) Normalized so data is not repeated
2) All columns depens on a primary key to identify a column
3) Distinct separation between storage of data and application using them

Characteristics of RDBMS

Data locking

Ensures multiple transactions are happening safely through a row locking mechanism which locks data, which means another database session cannot update that data until the lock is released which unlocks the data and other sessions are then allowed to update that data.

Schema

Logical strucutre of data in a database

Table

Objects that contain all the data in a database

Field(Column)

A field is a single column in a database table that holds one type of data for each record (row) in the table.

Tuple/Row/Record

Object that can contain one or more values

Primary Key

A primary key is a field or combination of fields in a database table that uniquely identifies each record in that table.

Foreign Key

Fields in one table that references the primary key in another table, creating a relationship between the two tables.

Composite Primary Key

Combination of two or more columns that uniquely identify rows in a table.

One-to-one Relationship

A record in the primary key table can be linked to at most one (or zero) record in the related table

One-to-many Relationships

Each record in the first table can be related to multiple records in the second table, but each record in the second table is related to only one record in the first table.

This is the most common type of relationship.

Many-to-many Relationships

Each record in the first table can be related to multiple records in the second table, and vice versa. 

(Requires a third table called junction table because relational systems cannot directly accomodate the relationship.)

Many to One relationship

Multiple records in the first table can be related to only one record in the second table, but each record in the second table is related to multiple records in the first table.

Normalization

Normalization is the process of organizing data in a relational database by dividing large tables into smaller, related tables.

Used to help reduce data redundancy when designing data strictures

Benefits of Normalisation

Reduces data redundancy (no duplicate values stored)

Improves data integrity (less risk of inconsistent data)

Easier to maintain and update

Minimizes update anomalies (insert, update, delete errors)

Supports referential integrity through foreign keys

Improves query reliability (accurate joins across tables)

1NF

1) Data is atomic (Fields store single, indivisible values)

2) Each record has a primary key

3) No repeating groups of attributes

2NF

1NF +

No partial dependencies

3NF

2NF+

No non-key dependencies (Transitive Dependency)

Insertion Anomalies

Attempt to store an attribute value in the database but unable to because the value of another attribute is not known

Deletion Anomalies

When data is lost unintentionally by deleting other data.

Update Anomalies

If data is held in more than one place, and an update is made to only one version of the data.

Characteristics of a normalized database?

• Eliminates data redundancy

• Ensures data integrity

• Organizes data into logical, related tables

• Uses primary and foreign keys to link data

• Removes partial and transitive dependencies

• Supports efficient updates, queries, and maintenance

• Structures data in stages: 1NF → 2NF → 3NF

Need for Databases in Modern Organisations

• Data volume and complexity
  
  

• Data accessibility and retrieval
  
  

• Collaboration and concurrency

Table

Collection of related data stored in rows and columns. Each table represents a single entity, such as Students or Orders.

Record

A single row in a table with one instance of the entity

Field

The column of a table that stores one type of data (Name, Age, etc)

Primary Key

A field/mulitple fileds that act as an A unique Identifier for each record in a table

Foreign Key

A filed of one table being he primary key of a different table, establishing a connection between the two

Candidate Key

Any field (or set of fields) that could serve as a unique identifier for a record. One candidate key is chosen as the primary key.

Composite Primary Key

Two or more fields being used as the primary key for a table - Often used in conjunction tables

Join

A database operation that combines rows from two or more tables based on a related column

Query

A function that retrieves or filters data from one or more tables based on specific conditions.

Update

A function that modifies existing data in a database record using a command.