• Foundation
  • About 12 min
  • 5 practice questions

Integrity, security and database users

Understand integrity rules, normalisation in simple terms, backups and the roles of database users.

If something looks wrong, please tell us.

A database is only valuable if its data is correct, safe and available. This lesson covers the ideas that protect it and the people who use it.

Integrity

Data integrity means data is accurate and consistent. Typical rules include:

  • Entity integrity: the primary key cannot be empty or repeated.
  • Referential integrity: a foreign key must match an existing primary key in the other table, so a mark cannot be stored for a student who does not exist.
  • Domain integrity: a value must fit its field, for example a date field cannot hold a name.

Normalisation

Normalisation is organising tables to remove repeated data by splitting them and linking by keys. It reduces redundancy and update mistakes.

Security and backup

  • Authentication: proving who you are, usually with a login.
  • Authorisation: deciding what each user may do, such as read only or edit.
  • Backup: a copy kept elsewhere so data can be restored after failure or loss.
  • Recovery: bringing the data back from a backup.

Database users

Roles
RoleJob
Database administrator (DBA)Controls, secures and maintains the database
Application programmerWrites programs that use the database
End userUses the data through forms and reports

Worked examples

Example 1. A marks table holds Roll 99 but the Students table has no Roll 99. Which rule is broken? Referential integrity.

Example 2. Two students are entered with the same Roll, the primary key. Which rule is broken? Entity integrity, since the key must be unique.

Example 3. A clerk may view but not delete records. Which security idea is this? Authorisation.

Integrity keeps data correct, authorisation limits actions, backups allow recovery.

— Remember

Key takeaways

  • Integrity rules keep data valid.
  • Normalisation reduces repetition.
  • Authentication, authorisation and backups protect the data.

Check your understanding

  1. 1. A Marks table holds Roll 99, but the Students table has no Roll 99. Which rule is broken?

    1. Referential integrity
    2. Entity integrity
    3. Domain integrity
    4. Authentication
    Show answer

    Answer: A. A foreign key must match an existing primary key in the other table; Roll 99 matches nothing.

  2. 2. A date field is given the value 'Ravi'. Which integrity rule is broken?

    1. Referential integrity
    2. Domain integrity
    3. Entity integrity
    4. Authorisation
    Show answer

    Answer: B. Domain integrity requires a value to fit its field; a name cannot go in a date field.

  3. 3. Proving who you are, usually with a login, is called

    1. Authorisation
    2. Backup
    3. Authentication
    4. Normalisation
    Show answer

    Answer: C. Authentication checks identity; authorisation then decides what you may do.

  4. 4. Who controls, secures and maintains the database?

    1. The end user
    2. The application programmer
    3. The report printer
    4. The database administrator (DBA)
    Show answer

    Answer: D. The DBA controls and secures the database; programmers write programs that use it and end users work through forms and reports.

  5. 5. Splitting tables and linking them by keys to remove repeated data is called

    1. Normalisation
    2. Recovery
    3. Authentication
    4. Sorting
    Show answer

    Answer: A. Normalisation organises tables to reduce redundancy and update mistakes.

Privacy · Terms · Refunds · Delivery