Database Concepts

Why a DBMS is better than a file system, and the relational model: relation, attribute, tuple, domain, degree and cardinality, and candidate, primary, alternate and foreign keys.

Key ideas

Separate files repeat data (redundancy) and let copies disagree (inconsistency); a DBMS keeps the data together and controls sharing, access and security.
In the relational model data is kept in relations (tables): a row is a tuple and a column an attribute.
The domain of an attribute is the set of values it may take; NULL means a value is unknown or missing.
Degree is the number of attributes (columns) and cardinality the number of tuples (rows).
A candidate key is an attribute (or set of them) whose values are different in every tuple and never NULL.
The primary key is the candidate key chosen to identify the tuples; the other candidate keys are alternate keys.
A foreign key is an attribute that refers to the primary key of another relation, linking the two tables.

Remember

Degree and cardinality	a table of 6 columns and 20 rows: degree 6, cardinality 20
Adding to a table	a new row adds 1 to the cardinality; a new column adds 1 to the degree
Key test	unique in every row and never NULL: Marks or City, which repeat, cannot be keys
Alternate keys	the candidate keys minus the one chosen as the primary key
Foreign key	STUDENT(RollNo, Name, ClassID): ClassID refers to the primary key of CLASS
Schema	the design of a database: its relations, attributes and constraints

Watch out

Swapping degree (columns) and cardinality (rows).
Taking an attribute whose values repeat as a candidate key.
Thinking a foreign key must be unique in its own table: many rows may share one value.

Worked example

In a relation STUDENT, the attributes AdmNo, RollNo, Email each have unique values for every student and are never NULL. AdmNo is chosen as the primary key. Name (a) the candidate keys (b) the alternate keys.

Working: every attribute that can identify a tuple is a candidate key; those not chosen are alternate keys

Answer: (a) AdmNo, RollNo, Email; (b) RollNo, Email