Databases and records in C

1. Why shared information needs more than a file

Imagine an equipment desk lending sensor kits. One assistant keeps a list of available kits, another records borrowers, and a third schedules repairs. If each list is an independent copy, returning a kit may update one list while the reservation list still says it is unavailable. The difficulty is not simply storing characters. It is deciding which facts are authoritative and keeping related changes consistent.

A database organizes related information so that applications can use it under shared rules. The database management system, or DBMS, is the software that interprets requests, manages storage and controls access. The database is the stored information; the DBMS is the manager. Applications still express the meaning of tasks such as borrowing a kit. A DBMS cannot infer every real-world rule unless that rule is represented in the design.

A CSV file can be entirely suitable for one person processing a small dataset. As concurrent users, relationships and recovery requirements grow, a DBMS provides services that otherwise have to be built into each application. Its implementation still uses files and other storage mechanisms; “database versus file” is a comparison of managed services, not a claim that databases avoid files.

2. Tables describe entities and observations

These fictional tables distinguish a device from an observation made by that device. Device 7 has two readings; neither observation needs its own copy of every device property. We assume raw values are integers from 0 through 1023 for this example. That is a chosen measurement format, not a range that applies to every sensor or Arduino board.

A row, also called a tuple in the relational model, represents one item. Columns, or attributes, name properties. The schema describes the structure and rules: names, types, keys and constraints. The instance is the collection of rows stored at a particular moment. Changing raw value 640 to 650 changes the instance; adding a timestamp column changes the schema.

The order in which rows are printed is not their identity. SQL does not promise a particular result order without an ordering clause. Likewise, sorting a C array changes positions but should not change the IDs of its records. This distinction will become important when a record is searched and updated.

3. Keys preserve identity and relationships

A candidate key is a minimal combination of attributes whose values uniquely identify a row. Minimal means that removing an attribute would lose that guarantee. The chosen candidate key is called the primary key. In our readings table it is reading_id. Two observations can have the same raw value, so raw is not a suitable key. A primary key must be unique and non-null.

The readings table’s device_id is a foreign key referring to the devices table. Several readings may refer to device 7: the relationship is one device to many observations. The repeated foreign-key value is legitimate. Repeating a reading’s primary key would instead create ambiguous identity. Database constraints help enforce this difference. See the PostgreSQL constraints guide.

For a many-to-many relationship, introduce an association table. A student may borrow different kits, and a kit may be borrowed by different students over time. A loan record can identify the student, kit, dates and its own loan ID. A relationship involving time needs that meaning in its data model; a pair of names alone may not distinguish repeated loans.

4. Repeated facts cause update problems

The original employee example repeats personal details across assignments. If an employee changes address, every repeated copy must agree. Missing one update creates contradictory answers. There are also insertion and deletion problems: a design might force us to invent an assignment before storing an employee, or lose employee details when the last assignment is removed.

Separating employee, job and assignment facts gives each fact an appropriate home. In our device example, storing the current room once lets a room change affect one row. This is the motivation for normalization: organize dependencies so that independent facts do not have to be maintained repeatedly. Formal normal forms come later; first ask what each row represents and which attributes depend on its key.

Be precise about historical meaning. If we want the room in which an old measurement was taken, joining with the device’s current room may be wrong after a move. We might store measurement location as a historical fact or model device locations over time. Avoiding duplication does not mean discarding facts with different meanings.

5. A split must preserve the intended information

Decomposing a table is not automatically safe. Suppose a job title can occur in two departments. If we separate employee–title pairs from title–department pairs, then join only on title, we may produce employee–department combinations that were never present. The shared attribute was not sufficient to recover the original association.

A good decomposition removes inappropriate repetition while allowing the intended information to be reconstructed. Keys and dependencies explain why reconstruction works. Splitting by visual convenience alone is not enough. Trace a particular employee through the original and reconstructed tables and check whether extra pairs have appeared or valid pairs have disappeared.

6. Selection, projection and join answer different questions

Relational selection chooses rows satisfying a condition, such as raw ≥ 600. Projection chooses attributes, such as reading_id and raw. A join combines rows according to a matching condition, allowing an observation to be shown with the corresponding device room. These operations describe what information is wanted; they do not prescribe one disk-access procedure.

SQL terminology can be confusing: its SELECT clause chooses output expressions, while WHERE commonly performs the row filtering called selection in relational algebra. Classical relations are sets; ordinary SQL results may retain duplicates unless DISTINCT is requested. A join can also multiply rows when several matches exist. Never assume it returns exactly one row per input row without inspecting the relationship.

7. Read a query and predict its result

SELECT reading_id, raw
FROM readings
WHERE raw >= 600
ORDER BY reading_id;

The query gives a condition and desired columns; the DBMS decides how to obtain the result. ORDER BY makes the displayed ordering part of the request. Without it, a convenient order observed during one run is not a guarantee. These examples introduce query reasoning rather than requiring a server installation.

SELECT r.reading_id, d.room, r.raw
FROM readings AS r
JOIN devices AS d ON r.device_id = d.device_id
WHERE r.raw >= 600
ORDER BY r.reading_id;

Aliases r and d make the source of each column explicit. The ON condition matches a reading to its device; WHERE then limits the results. An inner join omits rows without a matching device. A left join can retain unmatched readings with null values for unavailable device columns. The PostgreSQL join tutorial develops these cases.

8. Validity rules and missing values

A useful schema rejects states that do not make sense for the application. Our rules require unique reading IDs, existing device IDs and raw values between 0 and 1023. A type such as INTEGER does not enforce all of those conditions. It accepts many integers that are not valid measurements under this format.

SQL NULL represents missing or unknown information, not the number zero. A sensor reading of zero may be meaningful. Use IS NULL to test for null; ordinary equality with NULL does not behave like equality between two ordinary values. A CHECK constraint alone may permit null because an unknown condition is not false; use NOT NULL when absence is forbidden. Rules can prevent invalid entries but cannot prove a sensor was physically accurate.

9. Related changes must succeed together

Borrowing a kit might both create a loan record and change availability. If only the first change survives, the database may tell two conflicting stories. A transaction groups changes so that they commit together or are rolled back. Atomicity concerns this all-or-nothing outcome; durability concerns keeping an acknowledged committed result despite the failures covered by the system’s guarantees.

BEGIN, COMMIT and ROLLBACK express transaction boundaries in common SQL usage. They do not make an incorrect business rule correct. The application still needs to check whether a kit can be borrowed. See PostgreSQL’s transaction introduction.

Two withdrawals occurred, but the stored result represents only one. Coordinating concurrent operations needs suitable isolation, locks or atomic conditional updates. Simply naming a group a transaction is not a proof that every isolation level prevents every anomaly. A robust design also checks whether an update actually found and changed the intended record.

10. Recovery, indexes and views solve different problems

Recovery mechanisms can use logs to reconstruct committed changes and undo incomplete work after certain failures. Backups address other needs, such as recovering from loss of storage or an unwanted deletion discovered later. A backup that has never been restored is weaker evidence than a tested recovery procedure. Transactions and backups complement one another.

An index is an auxiliary access structure that can help locate rows without scanning the whole table. It does not change what a query means. Indexes take space and need maintenance when indexed data changes. A small table or a query returning most rows may be cheaper to scan. The DBMS chooses among alternatives based on its implementation and estimates; an index is not a promise that every request becomes faster.

A view presents a query-defined perspective on data. It can make an application simpler and, with appropriate privileges, limit what a role can access. Merely hiding a column in the interface does not prevent direct access to the underlying table. Combining datasets can also reveal information absent from either isolated display: identification may become possible through linked attributes. Think about permitted inferences as well as visible fields.

11. A C record groups fields into one object

A C array contains elements of one type. A structure groups named members that may have different types. The historical figure uses “heterogeneous array” informally; in C the precise term here is structure. Its drawn offsets are conceptual and must not be treated as the layout of every compiler or machine.

typedef struct {
    int id;
    int device_id;
    int raw;
} Reading;

Reading r = {101, 7, 512};
r.raw = 520;

The declaration introduces a structure type and the typedef name Reading. It does not create a database table or automatically store anything in a file. The variable r is one in-memory object. The dot operator selects a member. A typedef gives a type another name; it is not a separate storage allocation.

12. An array of records needs both capacity and count

Reading rows[16] = {
    {101, 7, 512},
    {102, 9, 640},
    {103, 7, 700}
};
size_t count = 3;

Capacity is the number of available elements: 16. Count is the number currently regarded as active: 3. The remaining elements exist but are not active records. Traversals should use count, while insertion checks count against capacity. Confusing these two quantities can turn unused storage into fake records or allow an out-of-bounds write.

The initializer puts ID 101 at index 0. That association is temporary. Deleting or sorting records can move it. The ID belongs to the represented observation; the index belongs to this array arrangement. A database key and an array index are therefore not interchangeable.

13. Search before updating

size_t find_id(const Reading rows[], size_t n, int id)
{
    for (size_t i = 0; i < n; ++i) {
        if (rows[i].id == id) return i;
    }
    return n;
}

/* In a function where rows and count are available: */
size_t pos = find_id(rows, count, 102);
if (pos < count) {
    rows[pos].raw = 650;
}

The search returns an active index when found and n otherwise. The caller must test the result before subscripting. Returning n works even for an empty array: no valid active index exists, so the caller skips the update. This contract avoids treating a failed search as index zero.

The function assumes n does not exceed the actual accessible array length. const prevents modification through this parameter; it does not validate the bound. The chosen ID is expected to be unique. If duplicates are allowed accidentally, this helper updates only the first match, which is another reason to validate input.

Binary search requires ordering by the same key used for the comparisons. An array sorted by raw value is not automatically suitable for binary search by ID. For this small bounded collection, linear search is straightforward. Faster search is useful only when its preconditions and maintenance costs fit the task.

14. Copies and pointers express different relationships

Reading original = {101, 7, 512};
Reading copy = original;
Reading *alias = &original;
copy.raw = 600;
alias->raw = 700;
/* original.raw is 700; copy.raw is 600. */

Structure assignment copies member values. The alias is a pointer to the original object, so changing a member through it changes that original object. The arrow expression alias->raw is equivalent to (*alias).raw. The pointer must designate a valid live Reading object before either expression is used.

An array member is included when its containing structure is copied. A pointer member instead copies an address, not the separately allocated object to which it points. Two structure copies may therefore still refer to the same external data. Copying a pointer does not extend the lifetime of that data or transfer ownership automatically.

Compilers can insert padding between members or after the last member to satisfy alignment requirements. sizeof a structure need not equal the sum of member sizes. Use members by name rather than guessing byte offsets from a drawing. These details also matter when someone proposes saving raw structure bytes.

15. Loading should validate a complete record

A line such as 102 9 640 is an external representation, not yet a trusted Reading. Loading should check that all three fields exist, conversions succeed, values fit the destination types, the device is permitted, the raw value is in range, the ID is not already present, and capacity remains. Trailing unexpected text should not silently become part of a valid record.

101 7 512
102 9 640
103 7 700

The checked companion from the slide preparation uses bounded input, strtol with range/error checks and a temporary array. It rejects embedded NUL bytes and overlong lines. Only after the complete input passes does it replace the caller’s collection. This staging avoids leaving half of a new dataset mixed with an old dataset when line three is invalid.

Staging is a program design choice, not a feature automatically supplied by a struct. The companion supports at most 16 records and accepts a final line without a newline. A different application might collect all errors or accept partial input, but it must make that policy explicit. The lecture focuses on the invariant: either a complete valid replacement is available, or the original collection remains intact.

16. Saving converts active fields to an agreed format

int write_one(FILE *out, const Reading *r)
{
    return fprintf(out, "%d %d %d\n",
                   r->id, r->device_id, r->raw) >= 0;
}

This helper reports whether the formatted output call succeeded. A complete caller must open the stream, iterate only over active records, check each write and check closing the output stream. Buffered output means a problem may become visible only later. A success message should follow the checks, not precede them.

Updating an array is not the same operation as updating its file. Saving and reloading provides evidence that the external representation preserves the intended fields. After changing ID 102 to raw value 650, reloading should show 650 for that ID while retaining the other observations. The equality of selected fields is more meaningful than comparing raw structure bytes containing padding.

Opening an existing file with mode w truncates it. Use distinct input and output files for the teaching example. The companion rejects identical path strings, but different strings can still designate the same file through links; its precondition is genuinely distinct underlying files. A temporary file and carefully designed replacement procedure can improve failure handling, but are beyond this short exercise. A plain save routine is not a database transaction.

17. Object bytes are not a portable record format

Text output converts values into characters. Binary block I/O transfers bytes. The latter can be convenient in a controlled environment, but writing sizeof(Reading) bytes does not define a portable format. Integer sizes, byte order, alignment, padding and representation choices may differ between implementations. Pointer members would save addresses meaningful only in an appropriate process context, not the pointed-to objects.

fread and fwrite return counts of complete items transferred. Check them against the requested count. If a read stops early, distinguish end of file from an I/O error and do not assume a partially read object is valid. A portable binary format specifies field widths, encoding, byte order, versioning and validation explicitly. Lab 10 does not require designing such a format.

18. A union represents alternatives, with a separate tag

enum Kind { RAW_VALUE, VOLTAGE_VALUE };
typedef struct {
    enum Kind kind;
    union {
        int raw;
        double voltage;
    } value;
} Sample;

Sample s = { .kind = RAW_VALUE, .value.raw = 512 };
if (s.kind == RAW_VALUE) {
    printf("%d\n", s.value.raw);
}

Structure members have separate storage; union members overlap. A union is large enough for its largest member, with possible additional padding, but it is not a way to retain all alternative values simultaneously. The enum tag tells this program which interpretation is intended. The union does not maintain that tag automatically.

Every update must keep tag and payload consistent. If the program changes the tag to VOLTAGE_VALUE without storing a corresponding voltage member, its logical invariant is broken. C has nuanced rules for reading another union member, but such reinterpretation is not needed for this tagged-variant pattern. Follow the tag and access the selected member.

19. Connect the ideas without building a DBMS

Recitation can trace field access, searches, copies and failed loads. Lab 10 builds on Lab 9’s PC-side file handling: load a small bounded collection, locate and update a record, then save and reload it. Arduino events can supply context using the established acquisition support. This is not a requirement to implement SQL, an SD-card system or a six-operation database application.

The comparison with a DBMS is useful precisely because the small program lacks many services. It has records and files, but no automatic foreign-key enforcement, concurrency control, query optimizer or transaction recovery. Recognizing those missing responsibilities explains why database systems exist.

20. Optional: more database and storage diagrams

The following original figures extend the main argument. Read them as different ways to organize or combine records, rather than as another implementation assignment. For each, identify the key, the relationship and the assumptions needed to obtain a correct result.

A join condition need not be equality, although equality on related identifiers is common. An alternative database model changes how relationships are represented; a relational table is not the only model. Fixed-width records simplify position calculations only when their encoding and width are defined. Merging sorted inputs assumes compatible ordering. Hashing selects a bucket, but collisions require a policy: two distinct keys can select the same bucket, so the full key still needs checking.

21. Optional: the original grade-file example

These images preserve the complete original teaching sequence. Follow the separation between declarations, opening streams, reading fields, checking values, computing a result and writing output. They are historical reference material, not the checked loader used in the core discussion. Review conversion return values, bounds, numeric ranges and every output/close result before adopting such code.

The conceptual pipeline is reusable: parse → validate → compute → format. A result should not be calculated from fields whose conversion failed. Likewise, a category or grade should be determined by an explicit boundary rule, including what happens exactly at a threshold. A sample output demonstrates one case; it is not evidence that all boundary and failure cases work.

22. Optional: block I/O and file positioning

The final reference figures revisit stream modes, block transfers and positioning. Their short captions identify assumptions that are easy to miss in compact code. Opening modes and stream positions affect what later calls mean; neither successful opening nor a completed loop proves that every byte was written successfully.

In an update stream, switching between reading and writing requires the synchronization specified by C; the rules are not simply “alternate calls freely.” ftell can fail and its result must be checked. For text streams, a reported position is not generally a portable character count. fseek offsets and allowed origins depend on the stream. In particular, do not treat opening in append mode as a portable file-size query.

The pictured negative offset involving sizeof also deserves care: sizeof has unsigned type, so arithmetic can become unsigned before conversion. Compute representable offsets deliberately. Finally, the old “No output” message after an append read error is misleading: earlier writes may already have happened. This illustrates the difference between sequential file operations and atomic transactions.

23. Sources and further study

The instructor’s accepted Week 11 slides supply the preserved figures and example sequence. Original image credits remain in the deck; the local coverage record maps the extracted figures. The equipment-desk opening is a fictional AI-generated conceptual illustration. The historical source figures are retained with qualifications where their notation or code needs explanation.

• Brookshear & Brylow, Computer Science: An Overview, 12th Global Edition, Chapter 9: database models, relational operations, integrity and file organization. Use the supplied course copy.
• PostgreSQL: relational concepts — start here for rows and tables.
• PostgreSQL: joins — trace matched and unmatched rows.
• PostgreSQL: constraints — distinguish primary keys, foreign keys, checks and nullability.
• PostgreSQL: transactions — study grouped updates and rollback.
• PostgreSQL: indexes — consider both lookup benefits and update costs.
• WG14 N1570, public C11 committee draft — reference sections 6.7.2.1 (structures/unions), 6.5.2.3 (member access) and 7.21 (I/O). This is a reference document, not required cover-to-cover reading and not labelled as the C17 standard.

For further study, design a tiny library-loan schema before writing any code. Identify entities, candidate keys, one-to-many relationships and a rule that must hold across two changes. Then compare those requirements with the responsibilities of the bounded C record program. The goal is to explain what each representation guarantees and what it leaves to its users.