Construct Tables

What Are Tables in Relational Databases?

Tables are the core structure of a relational database. They organise data into rows (records) and columns (attributes), ensuring structured data management.

Each table contains a primary key that uniquely identifies each record and may have foreign keys linking to other tables.

Types of Keys in Relational Databases

Primary Key

A primary key is a unique identifier for each record in a table. It ensures that no two rows have the same value for this field, maintaining data integrity.

Example: StudentID in a Students table.

Foreign Key

A foreign key is a field that refers to the primary key of another table. It establishes a relationship between the two tables and enforces referential integrity.

Example: CourseID in an Enrolments table referencing the Courses table.

Composite Key

A composite key is made up of two or more fields that together uniquely identify a record. It's useful when no single field is sufficient to provide uniqueness on its own.

Example: StudentID + CourseID in an Enrolments table.

Concatenated Key

A concatenated key is a specific form of composite key where the fields are conceptually or physically joined to create a unique value.

Example: OrderID + ProductID in an OrderDetails table.

Relationships Between Tables

Presenting Your Table Structure: Overview

These are how you might present your tables during the development of a project:

As Tables

In Relational Schema Notation

Here are some examples:

A One-to-Many Example

Each teacher can teach many classes, but each class has only one teacher. A foreign key in the Classes table establishes the relationship.

As Table
Teachers
Field Name Data Type Notes Sample Data
TeacherID CHAR(4) Primary Key T001
LastName VARCHAR(100) - Roberts
As Relational Schema
Teachers(TeacherID, LastName)
As Table
Classes
Field Name Data Type Notes Sample Data
ClassID CHAR(4) Primary Key C101
Room VARCHAR(10) NULL Room 5
TeacherID CHAR(4) Foreign Key → Teachers T001

Note: The use of NULL means that field could be left empty

As Relational Schema
Classes(ClassID, Room, TeacherID)

A Many-to-Many Example

Students can enrol in many courses, and each course may have many students. A linking (or junction) table (Enrolments) connects them.

As Table
Students
Field Name Data Type Notes Sample Data
StudentID INT Primary Key 1001
FirstName VARCHAR(100) - Amira
DoB DATE - 17/01/2001
As Relational Schema
Students(StudentID, FirstName, DoB)
As Table
Courses
Field Name Data Type Notes Sample Data
CourseID INT Primary Key CS101
CourseName VARCHAR(100) - Computer Science
As Relational Schema
Courses(CourseID, CourseName)
As Table
Enrolments
Field Name Data Type Notes Sample Data
StudentID INT Foreign Key → Students 1001
CourseID INT Foreign Key → Courses CS101
EnrolmentDate DATE NULL 31 October 2023
As Relational Schema
Enrolments(StudentID, CourseID, EnrolmentDate)

A Concatenated Key Example

In some tables, a unique record is identified by combining two or more fields. Here, OrderID and ProductID together form a concatenated key that ensures each product in an order is uniquely identifiable.

As Table
OrderDetails
Field Name Data Type Notes Sample Data
OrderLineID VARCHAR(20) Concatenated Primary Key ORD5001-103
OrderID INT Foreign Key (Orders) 5001
ProductID INT Foreign Key (Products) 103
Quantity INT - 2
As Relational Schema
OrderDetails(OrderLineID, OrderID, ProductID, Quantity)

SQL Example: Creating Database Tables


CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    DoB DATE
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100)
);

CREATE TABLE Enrolments (
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Why Are Well-Defined Tables Important?

 Key Takeaways