Database Concepts — Notes

Keeping records in separate files (registers, spreadsheets) causes five problems:

1. Data redundancy — the same data is stored in many files.
2. Data inconsistency — copies disagree after an update is made in only one place.
3. Data unavailability — the data exists but is hard to retrieve in the needed form.
4. Unauthorised access — anyone who can open a file sees all of it.
5. Data dependence — programs depend on the file layout, so changing the layout breaks them.

A database is an organised collection of related data. A DBMS (database management system) is software, such as MySQL, that creates and manages databases: it stores data once and links it, applies rules (keys, constraints), answers queries, controls who can see or change what, and supports backup and sharing.

In the relational model data is stored in relations (tables). Terms:

• Tuple (record): one row.
• Attribute (field): one named column.
• Domain: the set of values an attribute may take (for example, Age: whole numbers from 0 upwards).
• Degree: the number of attributes.
• Cardinality: the number of tuples (the heading row is not counted).

Properties of a relation: attribute names are unique; each value is atomic (single); no two tuples are identical; the order of tuples and of attributes does not matter.

Degree counts columns and cardinality counts rows. Adding or dropping a column changes the degree; inserting or deleting a row changes the cardinality.

The Cartesian product A × B pairs every tuple of A with every tuple of B:

• degree(A × B) = degree(A) + degree(B)
• cardinality(A × B) = cardinality(A) × cardinality(B)

For the imagined STUDENT (4 columns, 5 rows) and FEES (4 columns, 4 rows), the product has degree 8 and cardinality 20 — measured by running the query on the two tables. Most product rows pair unrelated tuples; keeping only rows whose linking values match gives the meaningful ones.

A key lets the DBMS identify exactly one tuple.

• Candidate key: an attribute or set of attributes that is unique for every tuple and never NULL. A relation may have several.
• Primary key: the one candidate key chosen to identify tuples. Exactly one per relation; unique and NOT NULL.
• Alternate key: a candidate key not chosen as the primary key.
• Composite key: a key made of two or more attributes, used when no single attribute is unique, e.g. (AdmNo, Subject) in an exam-result relation.

A good primary key is minimal, simple and stable. Attributes such as names or cities can repeat, so they are not keys even if they look unique in a small sample.

A foreign key is an attribute (or set) in one relation whose values must match the primary key of another relation. It links the two: AdmNo in FEES refers to AdmNo in STUDENT; SupCode in ITEM refers to SUPPLIER; DocID in PATIENT refers to DOCTOR.

Referential integrity means every foreign key value must exist in the referenced relation (or be NULL when there is no link yet). The DBMS therefore refuses:

• a child row whose foreign key matches no parent;
• deleting a parent that child rows still refer to (unless the table is set to cascade).

A foreign key value can repeat (one supplier, many items), and its name need not match the referenced key — only its values and domain must.

Use this table to check answers. In exam answers, name the table with every key, and justify it in a few words: "unique and never empty" for candidate keys, "matches the primary key of …" for foreign keys.