Big picture
A database organises related data so it can be stored, found and updated consistently. A school may need student details, club memberships and event registrations. Keeping everything in unrelated spreadsheets can create conflicting phone numbers, repeated entries and difficult updates.
The relational model addresses these problems through tables, keys and constraints. This chapter explains the vocabulary used to design a small database and to judge whether a proposed structure preserves meaningful relationships. SQL comes next; first understand what the tables should represent.
Learning goals
Distinguish databases from DBMS software.
Explain redundancy and modification anomalies.
Use relational terms and count degree or cardinality.
Identify minimal keys and linked foreign keys.
Design simple relations with integrity constraints.
Core ideas
1. Data, information and database systems
A database is an organised collection of related data. A database management system, or DBMS, provides facilities to define, store, retrieve and manage that data. A database is the collection; a DBMS is the software managing it. The broader system also includes users, procedures and hardware.
For a school event, individual registration rows are data. A count of registrations by class provides information for planning. A DBMS can enforce rules, support controlled access and help concurrent users work with shared records. These benefits require sound design and administration; installing a DBMS does not automatically correct misleading data. State both the problem and the relevant DBMS feature when explaining why it is useful.
2. Why isolated files become difficult
A file-based approach can repeat the same fact in many places. If a student's contact number is copied into separate club lists, one update may leave conflicting versions. Redundancy is unnecessary repetition; inconsistency arises when repeated representations disagree.
An update anomaly makes one logical change require several physical edits. An insertion anomaly makes it difficult to record one fact without an unrelated fact, while a deletion anomaly loses useful information when another record is removed. For example, deleting the final registration should not erase the only stored description of an event. Separate entities and connect them with identifiers. Not all repetition is automatically harmful, but repeated authoritative facts require a deliberate consistency strategy rather than accidental duplication.
3. Relations, attributes, tuples and domains
A relation is represented as a table, an attribute as a column and a tuple as a row. A domain is the permitted set of values for an attribute. StudentID might be an integer identifier, while ClassSection could be a constrained text value.
In the relational model, each cell contains one value from its domain and duplicate tuples are not meaningful distinct records. SQL tables can permit duplicates unless constraints prevent them, so design intent and implementation must be distinguished. Row order has no inherent meaning; a query needs an explicit ordering when order matters. Likewise, a displayed first column is not automatically a key. The attribute's role and constraints determine identity, not its visual position in a table.
4. Degree, cardinality and a worked relation
The degree of a relation is its number of attributes; cardinality is its number of tuples. These terms describe different dimensions and should not be interchanged.
MemberID | Name | Section |
|---|---|---|
201 | Aditi | 11A |
202 | Nikhil | 11B |
203 | Sara | 11A |
204 | Imran | 11C |
This relation has degree 3 and cardinality 4. Adding a fifth member changes cardinality to five but leaves degree three. Adding an Email attribute changes degree to four, even if no new member is added. A header is not a data tuple. In design questions, count the actual attributes and rows instead of inferring them from sample formatting. Cardinality here means row count, not the separate relationship-cardinality concept used in more advanced modelling.
5. Superkeys, candidate keys and primary keys
A superkey is an attribute set that uniquely identifies a tuple. A candidate key is a minimal superkey: no attribute can be removed while preserving uniqueness. A primary key is the candidate key chosen as the main identifier. Other candidate keys are often called alternate keys.
If MemberID is unique, then MemberID plus Name is also a superkey, but the extra Name is unnecessary, so that larger set is not minimal. A person's name is usually unsuitable because different people can share it. A composite key contains more than one attribute. For registrations, the pair MemberID and EventID can prevent the same member registering twice for one event while allowing each identifier to appear in other combinations. Minimal means no redundant attribute, not the smallest numeric value.
6. Foreign keys and referential integrity
A foreign key links attributes in one relation to an appropriate unique key in another relation. A Registration table can contain MemberID referring to the Member table. Referential integrity prevents a non-null reference from pointing to a missing parent entry.
The foreign-key column need not be unique: one member can have many registrations. Whether a foreign key may be null depends on the schema's separate nullability rules. Deleting a referenced parent may be restricted or handled according to an explicit referential action; never assume every system silently removes every child. In a diagram description, identify parent table, referenced key, child table and foreign-key attribute. This explains the relationship more accurately than merely drawing an arrow between two table names.
7. Constraints, NULL and data quality
A constraint is an enforced rule. A primary key must be unique and non-null. NOT NULL requires a supplied value; UNIQUE prevents duplicate key values under the database's null rules; CHECK can express a condition such as a non-negative count. A DEFAULT supplies a value when an insertion omits the attribute under the applicable SQL rules.
NULL represents a missing, unknown or inapplicable value, not necessarily zero or an empty string. A recorded score of zero is a known result; a null score may indicate it has not been supplied. The distinction affects comparisons and summaries. Constraints protect stated rules, but they cannot prove every real-world fact is true. A correctly formatted telephone number may still belong to someone else. Combine structural validation with responsible collection.
8. Schema, instance and a complete design
A schema describes the database structure and rules; an instance is the data present at a particular time. Adding a registration changes the instance, while adding a new column changes the schema. Data independence concerns separating applications from details of data organisation so some changes need not force every application to be rewritten.
For a club-event design, use Member(MemberID, Name, Section), Event(EventID, Title), and Registration(MemberID, EventID). Choose primary keys for the first two tables and a composite primary key for Registration. Its two identifiers also reference the corresponding parent tables. Store an event title once rather than in every registration. To test the design, ask whether two members can share a name, whether one member can join several events, whether duplicates are blocked, and whether an unknown event can be referenced. A good design answers all four requirements without relying on manual memory.
A reliable approach to questions
Read the command word before choosing an answer. A definition needs the distinguishing feature; a comparison needs matching criteria on both sides; an application needs a reason linked to the situation.
Extract the supplied evidence and assumptions. Keep a small working table rather than relying on memory. If information is absent, state the limit instead of inventing a value or claiming certainty.
Work through the relevant rule one step at a time. For a program, record the changing state; for a scenario, connect the requirement to the feature that meets it. Explain why the closest alternative fails.
Check the result against the original question, including boundary cases, units and wording. Present a concise final answer followed by enough reasoning for another student to reproduce your conclusion.
Connections that matter
Python dictionaries introduce keyed lookup but are not full relational databases.
SQL implements table definitions, constraints and retrieval.
Data projects depend on clear schemas and reliable identifiers.
Common mistakes
Calling any unique attribute set a candidate key: minimality also matters.
Assuming names are reliable keys: duplicates can occur.
Treating NULL as zero: missing and measured zero differ.
Assuming foreign keys are always unique: repeated references are common.
Revision checklist
I can distinguish databases from DBMS software.
I can explain redundancy and modification anomalies.
I can use relational terms and count degree or cardinality.
I can identify minimal keys and linked foreign keys.
I can design simple relations with integrity constraints.