IB Computer Science Topic A3.1.1: Features, Benefits, and Limitations of a Relational Database Notes

Features of a Relational Database: Tables Linked by Keys

1. A relational database stores data in separate tables that are joined by shared key columns, so each fact is written once and read wherever it is needed.
2. The syllabus names five features you must be able to explain: tables, primary keys, foreign keys, composite keys and relationships.

Tables: Rows Are Records, Columns Are Attributes

1. Each table stores one type of entity, such as customers, products or orders.
2. A row is one record: a single customer, one product, one order.
3. A column is one attribute of that entity, and every value in it has the same data type.
4. Row order carries no meaning, because a row is found by its key values rather than by its position in the table.

Primary Keys: One Value That Identifies Every Row

1. No two rows may hold the same primary key value, and the value cannot be left empty.
2. The DBMS enforces that rule as data is entered, so a duplicate is refused instead of being stored.
3. A primary key gives every row a stable handle that other tables can point at.

Foreign Keys: The Column That Points at Another Table

1. A foreign key column holds a value that already exists as a primary key in the table it references.
2. The DBMS checks this on every insert and update, so an order cannot be recorded against a customer who does not exist.
3. Because the link is stored as a single key value, the customer's details stay in the Customer table instead of being copied into every order.

Composite Keys: When No Single Column Is Unique

1. A composite key is used when no single column identifies a row on its own, so two or more columns together form the primary key.
2. Each column may repeat, but the combination must be unique.
3. The junction table that resolves a many-to-many relationship normally uses a composite key built from its two foreign keys.

Relationships: One-to-One, One-to-Many and Many-to-Many

1. A relationship exists wherever a foreign key in one table matches a primary key in another.
2. One-to-one: each row matches at most one row in the other table, such as a person and their passport.
3. One-to-many: one row matches many rows in the other table, and the foreign key sits on the many side, such as a customer with many orders.
4. Many-to-many: this cannot be stored directly, so a junction table holds one row per pairing, such as students and the courses they take.

Benefits of Relational Databases: One Fact Stored Once, Read Many Ways

Data Consistency and Data Integrity

1. Each fact is stored in one place, so there is no second copy that can drift out of step.
2. Integrity constraints such as primary keys, foreign keys, NOT NULL and data types are checked by the DBMS at entry, so invalid data is refused rather than repaired later.
3. The rules sit in the database rather than in the application, so every program that connects to it obeys the same rules.

Reduced Data Duplication and Redundancy

1. Splitting data into related tables means a customer's address is stored once and referenced by key, instead of being repeated on every order.
2. That cuts storage, and more importantly it removes the update anomalies that duplicated data causes.
3. Three anomalies appear in badly structured tables, and normalization (A3.2.5) is the design process that removes them:
   1. Insert anomaly: you cannot record one fact without inventing another, so a new course cannot be added until some student enrols on it.
   2. Update anomaly: a teacher's phone number stored on every row of their timetable has to be changed on every row at once, and a missed row leaves two different numbers with nothing to say which is right.
   3. Delete anomaly: removing the last student on a course also removes the only record of the course itself.

Efficient Data Retrieval with SQL

1. SQL describes what data you want and the DBMS works out how to fetch it, so one query can join several tables and filter, sort and group the result.
2. An index lets the DBMS jump straight to matching rows instead of reading every row in the table.
3. Because the structure is separate from the queries, a new question can be answered without changing how the data is stored.

Concurrency Control: Many Users Writing at Once

1. A relational DBMS lets many users read and write the same tables at the same time, and concurrency control stops their work colliding.
2. The DBMS locks a row while one transaction is changing it, so a second transaction waits instead of reading a half-finished value.
3. Without that control, two updates that both started from the same original value overwrite each other and one change is silently lost.

Reliable Transaction Processing

1. A transaction groups several operations into one unit of work that either completes in full or leaves the database untouched.
2. A bank transfer is two writes, a debit and a credit, so if the second fails the DBMS rolls back the first and money is never created or destroyed.
3. Once the DBMS confirms a commit, the change is on non-volatile storage and survives a crash or power cut.

Scalability: Vertical and Horizontal

1. A relational database can hold very large tables and serve many concurrent users.
2. Vertical scaling adds CPU, memory or faster storage to the single server that holds the database.
3. Horizontal scaling spreads the work across machines, usually by sending read queries to replicas of the data.

Security Features Built Into the DBMS

1. Access is granted per user or per role, and can be limited to particular tables, columns or operations.
2. A payroll clerk can be allowed to read salary columns while a receptionist is not, without changing the application code.
3. Data can be encrypted at rest and in transit, and access logs record who read or changed what.

Community Support and Mature Tooling

1. MySQL, PostgreSQL, Oracle and SQLite have been in use for decades, so documentation, tutorials and answered questions are plentiful.
2. Drivers exist for most languages, so the same database can be reached from Java, Python or a web framework.
3. A large pool of developers already knows SQL, which lowers the cost of maintaining the system.

Limitations of Relational Databases: Where the Table Model Costs You

Big Data Scalability Issues

1. Vertical scaling ends at the largest single server available, which puts a ceiling on this approach.
2. Horizontal scaling is harder here than elsewhere, because a join and a foreign key check have to be resolved across machines and that adds network delay to every query.
3. High-velocity data such as sensor streams or clickstreams can arrive faster than the DBMS can enforce constraints on it.
4. NoSQL stores give up joins and fixed schemas in exchange for spreading writes across many servers, which is why they are common in big data work.

Rigid Schema: Every Row Must Fit the Same Columns

1. Every row in a table must have the same columns with the same data types, so a new attribute means altering the table rather than just adding a value.
2. Running ALTER TABLE on a large live table can lock it, and queries or application code written against the old columns can break.
3. The schema also has to be designed before any data is stored, which is awkward when the shape of the data is still changing.

Design Complexity

1. Entities, keys, relationships and constraints all have to be decided before the database is useful.
2. Normalizing to 3NF splits data across many tables, so answering a simple question can need several joins.
3. Each join costs work at query time, which is why read-heavy systems sometimes denormalize on purpose, as covered in A3.2.7.

Hierarchical Data Handling

1. Rows are flat, so a hierarchy has to be modelled as a row holding a foreign key that points back at its own table.
2. Walking a hierarchy of unknown depth needs one join per level or a recursive query, and it slows down as the tree gets deeper.
3. An organisational chart is the standard case: listing everyone below a manager at any depth cannot be written as a fixed number of joins.

Object-Relational Impedance Mismatch

1. Object-oriented code holds objects with inheritance, nested objects and collections, while a table holds flat rows of typed columns.
2. One object often maps onto several tables, so loading it means joining them and rebuilding the object in memory.
3. Inheritance has no table equivalent, so the designer has to choose between one wide table with unused columns or a separate table per subclass.

Unstructured Data Handling

1. Text documents, images, audio and video do not break down into typed columns, so the DBMS stores each one as a single binary value.
2. The DBMS cannot filter, sort or join on the contents of that value, so the queries a relational database is good at no longer apply.
3. Large binary values also bloat the table and slow down backups and ordinary queries against it.
4. The usual pattern is to keep the file in object storage or a file system, and store its path plus searchable metadata in a table.