Relational databases

Overview

Recall questions on attributes, primary, composite and foreign keys, which table carries the foreign key in a one-to-many relationship, and referential integrity.

What you need to know

Answer each one before you reveal it.

Questions from this subtopic

Each one with the model answer underneath.

1. 1

   What makes a database relational?

   Model answer

   A relational database stores data in a number of separate two-dimensional tables, each table holding data about one entity. Each row of a table, sometimes called a record or tuple, holds the data for one occurrence of that entity, and each column holds one attribute, with every value atomic — one indivisible item per cell. Each table has a primary key that uniquely identifies its rows. Tables are not physically connected; they are linked by data values, where an attribute in one table holds values drawn from the primary key of another. Relationships are therefore formed when a query is run, by matching those values, rather than being built into the storage. The benefits are that each fact is stored once in the table where it belongs, which removes duplication and the inconsistency it causes, and that new tables and new queries can be added without restructuring what already exists.

2. 2

   Define the term attribute.

   Model answer

   An attribute is a named characteristic or property of an entity — one item of data recorded about it, such as Surname or DateOfBirth. In an implemented relational database an attribute corresponds to a column of a table, and every row has one atomic value for it.

3. 3

   Define the term primary key.

   Model answer

   A primary key is the attribute, or set of attributes, whose value uniquely identifies each record in a table. No two records may have the same primary key value and the value may not be left empty, so given a primary key value exactly one record can be found. Every table in a relational database has one primary key.

4. 4

   Define the term composite primary key.

   Model answer

   A composite primary key is a primary key made up of two or more attributes taken together, used when no single attribute uniquely identifies a record but a combination of them does. In Enrolment(StudentID, CourseCode, DateEnrolled) neither StudentID nor CourseCode alone is unique, since a student takes several courses and a course has several students, but the pair identifies exactly one enrolment, so the composite primary key is StudentID together with CourseCode. Composite primary keys occur most often in the link tables that resolve many-to-many relationships.

5. 5

   Define the term foreign key.

   Model answer

   A foreign key is an attribute in one table which is the primary key of another table, and whose purpose is to link the two. In Order(OrderID, CustomerID, OrderDate), CustomerID is a foreign key referring to the primary key of Customer, so each order is connected to the customer who placed it. The foreign key is placed in the table at the 'many' end of a one-to-many relationship.

6. 6

   In a one-to-many relationship, which table holds the foreign key, and why must it be that one?

   Model answer

   The table at the 'many' end holds the foreign key. One customer has many orders, so CustomerID goes into Order and not the other way round. It must be that way because each order relates to exactly one customer, so a single value fits in the row; putting an OrderID into Customer would only allow one order per customer, and holding several would require more than one value in a cell, which is not permitted.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.