Relational Databases: Tables, Keys and Relationships

A school tracks its projectors. Each row in Equipment describes one projector. Columns hold details such as EquipmentID, Room and Condition. Two projectors may be in the same room or have the same condition, so those details cannot identify a projector on their own.

The school assigns a different EquipmentID to every projector. If EquipmentID is the primary key, duplicate IDs and null IDs are not allowed. The key identifies a record; it does not prove that the recorded room or condition is correct.

A primary key can use several columns. Their combination must be unique, and none of the key columns may be null. Test the proposed combination against all the rows the design permits, not just the records entered so far.

An invented ID still needs a process that assigns it uniquely. Keeping that ID unchanged lets other records continue to refer to the projector when its room or condition changes.

A database management system is software for storing, retrieving and changing database records. It can also manage access, coordinate users and support recovery. These features need suitable settings and procedures; their presence does not make every stored claim true.

A table definition can specify column types and constraints. A primary-key constraint rejects duplicate key values and null key columns. A foreign-key constraint checks references to an eligible unique key, which may be in the same table or a different table.

Checks have limits. A valid equipment ID may refer to the wrong projector. A room number may have an accepted type but still name the wrong room. Rules catch violations of the rules that were declared, not every mismatch with the world.

Reducing repeated copies of changing details also reduces update work. It does not replace careful data entry, access controls or recovery planning. Different database systems handle types differently, so check the behaviour of the system being used.

A study centre lends tablets. Members stores one row per member, Tablets stores one row per tablet, and Loans stores one row per loan event. Each loan includes a MemberID and a TabletID that refer to the matching records.

A member may borrow several tablets over time. A tablet may also be borrowed by several members over time. Loans records those connections without placing every loan inside a member or tablet row.

The same member can borrow the same tablet again. The MemberID and TabletID pair is therefore not unique across loan events. This design can give each event a unique, non-null LoanID instead. Key choices depend on what one row represents and which repetitions the rules allow.

Keep a member's current contact details in Members rather than copying them into every loan. A contact change then has one record to update. A detail about the loan itself, such as its return date, belongs in Loans. Separating records reduces repeated changing details, but it does not prevent every recording mistake.

Start by deciding what one row represents. Then ask whether two permitted rows could share the proposed key. A surname fails this test in a people table because different people may share it. A generated number passes only when the assignment process gives each record a different number.

Next, check for missing values. No column in a primary key may be null. For a key made from several columns, check the whole combination for uniqueness and each column for a non-null value.

Finally, consider changes. Stability is useful, but a primary key is not automatically immutable. If a referenced key changes, its foreign-key references must stay consistent. A database may support automatic updates or reject a change until the references are handled.

For a table with one row per participant per workshop, the participant and workshop IDs can form a key if both are non-null and each pair is allowed only once. If repeated attendance events are separate rows, the pair alone no longer identifies an event. The rule about permitted rows decides the key.