Relational databases revision notes

What is a relational database?

A relational database stores data in tables, also called relations. A table contains records (rows) and attributes (columns). Each row represents one occurrence of an entity, while each column stores one type of data about those occurrences. Tables can be connected when they contain related data.

For example, a Student table might contain:

| StudentID | Name | Year | |---|---|---| | 101 | Amina | 12 | | 102 | Leo | 13 |

Here, StudentID, Name and Year are attributes. The row containing 101, Amina and 12 is one record.

Key terms

Attribute

An attribute is a named item of data describing a property of a record. In a table, an attribute is represented by a column, such as StudentID, Name or Year.

Primary key

A primary key is an attribute, or set of attributes, that uniquely identifies each record in a table. In the Student table, StudentID could be the primary key because each student has a different identifier. A name would be less suitable if two students could have the same name.

Composite primary key

A composite primary key is a primary key made from more than one attribute. It is used when one attribute alone does not uniquely identify a record, but the combination does. For example, in an Enrolment table, neither StudentID nor CourseID alone may be unique because a student can take several courses and a course can have several students. The combination (StudentID, CourseID) can identify one enrolment.

Foreign key

A foreign key is an attribute in one table that refers to a primary key in another table. For example, an Enrolment table may contain StudentID as a foreign key referring to Student.StudentID. This connects an enrolment record with the corresponding student record.

Worked reasoning

Suppose the tables are:

Student(StudentID, Name)

Enrolment(StudentID, CourseID)

The primary key of Student is StudentID. The composite primary key of Enrolment could be (StudentID, CourseID). The StudentID in Enrolment is also a foreign key because it refers to the primary key in Student. Thus, a record such as (101, CS01) means that student 101 has the enrolment represented by course code CS01.

Common errors

• Calling every attribute a key: an attribute is only a key when it identifies records as specified.
• Confusing a primary key with a foreign key: the primary key identifies records in its own table; a foreign key refers to a key in another table.
• Assuming a composite primary key is one combined value stored in one column: it is formed from multiple attributes.
• Choosing a name as a primary key without checking whether names are unique.
• Describing a foreign key as a complete copy of another table: it is an attribute used to refer to the relevant primary-key value.