A3.1: Database fundamentals

Subjects

Features

Master IB Computer Science A3.1: Database fundamentals with notes created by examiners and strictly aligned with the syllabus.

This content can't be printed from the browser. Use Export PDF with Pro for an offline copy.

IB Syllabus Requirements for Database fundamentals

A3.1.1

Relational databases: features, benefits and limitations

A3.1.1

RELATIONAL DATABASES: FEATURES, BENEFITS AND LIMITATIONS

To explain a relational database, link its structure to what it lets users do—and where it imposes limits. Keeping related facts in separate tables can reduce repetition. Deciding how to divide those facts, then maintaining the tables, takes careful design.

Tables and keys

A relational database stores data in tables, connecting their entries through matching values.

Each table represents a type of entity or association in rows and columns. A row is a record; a column is a field that holds a particular kind of value.

A primary key uniquely identifies each record in a table. It can be a single field or a combination of fields, but its values must be unique and cannot be missing.

Two or more fields used together form a composite key. Values in the individual fields may repeat, even though the combination is unique.

A foreign key is a field, or combination of fields, that references a key in another table. It links a record to a corresponding record elsewhere in the database.

A relationship associates records in different tables. Matching key values represent that association, so all the related information does not have to sit in one record.

Worked example: workshop registrations

A community workshop centre keeps members, workshops and registrations in separate tables. In the headings below, PK marks primary keys and FK marks foreign keys.

Member

MemberID — PK	MemberName
M1	Asha
M2	Leon

Workshop

WorkshopID — PK	WorkshopTitle	PlacesRemaining
W1	Pottery	1
W2	Bookbinding	3

Registration

MemberID — PK part, FK	WorkshopID — PK part, FK
M1	W1
M1	W2
M2	W1

Look at Registration. MemberID alone is not unique because M1 appears more than once. Nor is WorkshopID: W1 also appears more than once. Used together, MemberID and WorkshopID form a composite primary key. This prevents the same member from being registered for the same workshop twice.

There are also two foreign keys in Registration. MemberID refers to Member.MemberID, while WorkshopID refers to Workshop.WorkshopID. Enforcing these references prevents a registration from pointing to a member or workshop that does not exist.

The relationship diagram below shows the entities Member, Registration and Workshop. One Member can have zero or many Registration records; each Registration belongs to exactly one Member. Similarly, one Workshop can have zero or many Registration records, while each Registration belongs to exactly one Workshop. These relationships let many members attend many workshops.

Member names stay in Member rather than being copied into every Registration record. To find the names of people attending Pottery, the database follows matching WorkshopID and MemberID values across the tables.

Why databases are useful

Benefits depend on the database design as well as the facilities the database management software provides. Tables alone don't automatically make data secure, consistent or reliable.

Benefit	How it helps, and why
Community support	An established database system may have a large community that produces documentation and tools, along with troubleshooting advice. These resources make problems easier to resolve and applications easier to maintain.
Concurrency control	The database coordinates operations from different users working at the same time. It prevents conflicting updates—for example, two people both successfully claiming the final workshop place.
Data consistency	Keeping a fact in one authoritative location helps users obtain agreeing values. A member's name can be updated once, avoiding different versions of the name in different registrations.
Data integrity	Rules restrict the data that can be stored. Primary-key rules prevent duplicate identities, while foreign-key rules prevent broken references. Validation rules can reject impossible values.
Data retrieval	Queries select, combine and organize records using specified conditions. Staff can retrieve workshop attendance information without searching several separate lists by hand.
Reduced data duplication	Keeping members separate from registrations avoids storing the same member details repeatedly. With less repeated data, there are fewer copies to maintain.
Reduced redundancy	Storing each fact where it belongs removes unnecessary repetition across the database. Less storage is wasted, and there is less risk of an update changing one copy while leaving another unchanged.
Reliable transaction processing	Related changes can form one transaction: either all take effect or none do. A failure need not leave a booking operation only partly completed.
Scalability	With suitable resources and configuration, database systems can support growing collections of records and increasing demand. Applications can keep using organized, shared data as their workload grows.
Security features	Authentication and permissions can limit access to authorized users and operations. Workshop staff might, for example, view registrations while only designated administrators may alter member details.

Reduced data duplication and reduced redundancy are closely related. Reduced data duplication focuses on avoiding repeated copies; reduced redundancy focuses on removing repetition that serves no necessary purpose.

Concurrency control and transactions solve different problems

Concurrency control deals with interference between simultaneous users. Suppose two users request the final place. The database must coordinate their requests so they cannot both complete successfully on the assumption that the place is still available.

Reliable transaction processing deals instead with whether related changes are completed in full. Here, a booking transaction must create a Registration record and decrease PlacesRemaining. If only one change were completed, the stored information would be inconsistent.

In the transaction flow below, a Booking request starts the process. Create registration and Decrease places remaining are grouped into one transaction. Success leads to Commit both changes; Failure leads to Roll back both changes. A rollback undoes the changes made by the unsuccessful transaction.

Where relational databases become difficult

A fixed, connected structure helps keep records reliable. That same structure can be awkward for highly variable or deeply nested data, or when the data becomes extremely large.

Limitation	Why it creates difficulty
“Big data” scalability issues	Very large data volumes can make relational processing expensive, as can rapid incoming data and heavy workloads. Spreading related tables across machines may take substantial coordination, especially when an operation must combine or update data stored in different places.
Design complexity	Designers must choose the tables and decide which fields belong in each, then work out how keys and relationships should operate. Poor choices can cause repetition or broken associations, or make retrieval unnecessarily complicated.
Hierarchical data handling	A hierarchy has parent–child relationships, potentially across many levels. Tables can represent these links, but retrieving a whole branch may mean following relationships repeatedly rather than reading a naturally nested structure.
Rigid schema	A schema specifies how tables are structured, including their fields and rules. If requirements change, altering the structure may also require changes to existing records and the applications that use them.
Object-relational impedance mismatch	Object-oriented programs organize information as objects; relational databases use table rows and relationships. Software has to translate between the two representations, which adds mapping work and potential errors.
Unstructured data handling	Free-form documents, audio and images do not naturally divide into uniform fields. A relational database can store this content, but organizing and searching its internal meaning may require additional tools.

Scalability can be a benefit or a limitation, depending on the workload. A relational database may comfortably handle growth in a workshop registration service yet become costly to manage for a much larger, rapidly changing collection of varied data.

A rigid schema isn't simply a defect either. Predictable fields and rules help protect data integrity, but leave less flexibility when different records need very different structures. The appropriate choice depends on the data and the operations required, as well as how important reliable relationships are.

Key terminology: tables, primary keys, composite keys, foreign keys, relationships, concurrency control, data consistency, data integrity, reliable transaction processing, rigid schema, object-relational impedance mismatch.

A3.2 Database design

The Ultimate All-in-One
IB Platform

All content on this website has been developed independently from and is not endorsed by the International Baccalaureate Organization. International Baccalaureate and IB are registered trademarks owned by the International Baccalaureate Organization.