The logical schema: tables, data types and integrity constraints

Terms

• Relation / table: a set of data about one entity, arranged in rows and columns.
• Attribute / column (field): one property, with the same data type in every row.
• Tuple / row (record): the data about one instance of the entity.
• Data types set what kind of values a column can hold: integer, real (float), text (string), Boolean, date/time. Choosing the right type saves storage, enables correct sorting and calculations and blocks invalid values. For example a telephone number should be text (it can have a leading 0 and is never calculated with).

A relation with four attributes (columns) and two tuples (rows).

Integrity constraints

Integrity constraints are rules the database management system enforces so that data stays accurate and consistent:

• Entity integrity: every table has a primary key, and its value must be unique and not null (not empty) in every row.
• Referential integrity: every foreign key value must match an existing primary key value in the related table (or be null if allowed). This stops 'orphan' records, such as a Student in a Form that does not exist, and stops a record being deleted while others still refer to it.
• Domain integrity: every value must come from the allowed set for its column: correct data type, format and range (for example Mark between 0 and 100, Grade one of A to E).

Why they are needed

Constraints catch errors at the point of entry, so reports and queries give correct answers, links between tables never break, and the same entity cannot be stored twice under one key. The drawback is that every insert, update and delete must be checked, which adds a small amount of processing.

Worked example

A Loan record is entered with MemberID 875, but there is no member 875 in the Member table. Which constraint is broken and what should happen?

1. MemberID in LOAN is a foreign key referencing MEMBER.
2. There is no matching primary key value 875.
3. This breaks referential integrity, so the DBMS should reject the record.

Answer: Referential integrity; the insert is rejected.

Check yourself

1. In a relational database, what is a tuple?
   • A row in a table
   • A column in a table
   • A whole table
   • A link between two tables
   Show answer

   A row in a table. Tuple = row = record.

2. A new record is added with an empty primary key. Which constraint is broken?
   • Referential integrity
   • Domain integrity
   • Entity integrity
   • No constraint, empty keys are allowed
   Show answer

   Entity integrity. Primary keys must be unique and not null.

3. A Mark column only allows whole numbers from 0 to 100. A user enters 140. Which constraint is broken?
   • Domain integrity
   • Entity integrity
   • Referential integrity
   • Composite key integrity
   Show answer

   Domain integrity. The value is outside the column's allowed range.

4. Which action would break referential integrity?
   • Adding a new Form with a unique FormID
   • Changing a student's name
   • Adding a Student with a FormID that exists
   • Deleting a Form record while Student records still refer to that FormID
   Show answer

   Deleting a Form record while Student records still refer to that FormID. The students would refer to a form that no longer exists.

5. Which data type is most suitable for a telephone number such as 07700 900123?
   • Integer
   • Text (string)
   • Real
   • Boolean
   Show answer

   Text (string). It has a leading zero and a space and is never used in calculations.

6. A table has 6 attributes and 250 tuples. How many data values does it hold?
   Show answer

   1500. 6 columns x 250 rows = 1500 values.

7. Explain why a database enforces referential integrity.
   Show answer

   It makes sure every foreign key refers to a record that actually exists, so links between tables stay valid and there are no orphan records.. Mention foreign keys matching existing records and the consequence.

Practise with instant feedback Print this lesson

Sources

• Pearson Edexcel International Advanced Subsidiary/Advanced Level in Computer Science specification (2026)

Last updated 8 November 2026. Spotted a mistake? Tell us.