Database concepts

Database Concepts

Before writing any database queries, you need to understand the core vocabulary used to describe how a relational database is structured. Six terms are essential: table, record, field, data type, primary key, and foreign key. All six are used both when designing a database on paper and when working with an actual database system.

The Six Concepts

Table, Record, Field and Data Type

StudentID Surname DateOfBirth IsActive 1001 Khan 2009-04-12 TRUE 1002 Lee 2008-11-30 TRUE 1003 Patel 2009-01-08 TRUE Students ā† table ā†’ field (column) record (row) data types: integer, text, date, boolean
  • Table: a collection of related data organised into rows and columns. In the example above, the Students table holds information about all pupils. A relational database contains multiple tables.
  • Record: a single row in a table, representing one complete set of related data. Each student has exactly one record. A record for student 1001 contains all the data about Khan.
  • Field: a single column in a table, representing one specific category of data. Surname is a field; DateOfBirth is a field. Every record in the table has a value in every field.
  • Data type: the kind of data a field is permitted to store. A field's data type determines what values are valid. Common data types include:
    • Integer - whole numbers (e.g. StudentID: 1001)
    • Text / String - characters and words (e.g. Surname: Khan)
    • Date - calendar dates (e.g. DateOfBirth: 2009-04-12)
    • Boolean - true or false only (e.g. IsActive: TRUE)
    • Float / Real - decimal numbers (e.g. a price: 9.99)

Primary Key and Foreign Key

Keys are the mechanism that makes a database relational. They allow tables to be linked to one another without duplicating data.

Students StudentID šŸ”‘PK Surname TutorID FK 1001 Khan T01 1002 Lee T01 1003 Patel T02 Tutors TutorID šŸ”‘PK TutorName T01 Ms Khan T02 Mr Lee FK ā†’ PK StudentID = PK Unique ID for each student. No two students share it. TutorID in Students = FK References TutorID PK in the Tutors table.
  • Primary key (PK): a field (or combination of fields) that uniquely identifies each record in a table. No two records can have the same primary key value, and the field cannot be left empty (NULL). In the Students table, StudentID is the primary key - every student has a different ID. Primary keys allow the database to find, update, or delete exactly the right record.
  • Foreign key (FK): a field in one table that contains a value matching the primary key of another table, creating a link between the two. In the Students table, TutorID is a foreign key - it holds values (T01, T02) that match primary key values in the Tutors table. This link allows the database to retrieve a student's full tutor details without duplicating that information in the Students table.

 Key Takeaways

  • A table holds related data in rows and columns. Each record is one row; each field is one column.
  • Every field has a data type that determines what values are valid (e.g. integer, text, date, boolean).
  • A primary key uniquely identifies each record in its table - no duplicates, no empty values.
  • A foreign key in one table references the primary key of another table, creating the link that makes a database relational.