Database Concepts

Why databases replace file systems, the relational model (relation, tuple, attribute, domain, degree and cardinality) and keys: candidate, primary, alternate, composite and foreign.

Key ideas

• Separate files cause redundancy (the same data stored many times), inconsistency (copies that disagree), data isolation, dependence on file layouts and no control over who may read or change what.
• A DBMS is software to create, store, manage and query databases while controlling access; MySQL is one.
• In the relational model a table is a relation, a row a tuple, a column an attribute, and the set of allowed values of an attribute its domain.
• Degree is the number of attributes (columns); cardinality is the number of tuples (rows).
• A candidate key has a different, non-NULL value in every tuple; the one chosen is the primary key and the others are alternate keys.
• When no single attribute is unique, two or more together form a composite primary key, such as (AttDate, RollNo) in an attendance table.
• A foreign key refers to the primary key of another relation; referential integrity means each of its values must exist there (or be NULL).

Remember

Watch out

• Swapping degree and cardinality: degree counts columns.
• Choosing Name or City as a key: their values can repeat.
• Counting the primary key among the alternate keys.
• Expecting one attribute of an attendance table to be unique: only the pair of date and roll number is.

Worked example