Tables and their Relationships

Table Department

DID primary key (PK)

PKs must be unique

Table Student

SID primary key

Dept foreign key (FK)

Dept contains references to department records

FK and PK must have same data type

FKs must not be unique

Values in FK column must exist in PK column

FKs represent many-to-one relationship

Student 1 and 2 are registered in the MBAE programme

Student 3 is registered in Business Computing

Data Types (Example Subset)

Integrity Constraints

• Primary key / Unique Constraint
• Uniqueness of column values
• Identification of records
• Foreign key
• Relationships between tables
• Referential integrity, target must exist
• Check Constraint
• Restriction on column values
• Quantities of line items within orders must be greater than 0
• Not Null Constraint
• Must contain values, i.e. cannot be empty

Reasons for Null Values

• Information not provided
• Private telefon number
• Information not existing, yet
• Exmatriculation date exists only after exmatriculation
• Column not suitable
• Table for person records with data for students and other persons
• Column MatrNo only applicable for student records

Structural Representation

Structural overview of tables and relationships

No table contents, no details

Compact and clear

FK and PK column above arrow

Can be omitted if FK and PK have same name

SQL Code

Table Student

create table Department (
  DID integer not null primary key,
  Name varchar(200) not null,
  Location varchar(50) not null
);

insert into Department values (1, 'MBAE', 'TGS');
insert into Department values (2, 'BC', 'TA');

create table Student (
  SID integer not null primary key,
  MatrNo varchar(10) not null,
  LastName varchar(200) not null,
  ImDate date not null,
  ExdDate date,
  Dept integer not null,
  foreign key (Dept) references Department (DID)
);

insert into Student values (1, '500123', 'Agarwal', '2024-10-01', null, 1);
insert into Student values (2, '503456', 'Ali', '2019-10-01', '2023-03-24', 1);
insert into Student values (3, '505789', 'Nguyen', '2021-04-01', null, 2);

update Student set ExdDate = '2025-03-02' where SID = 3;
delete from Student where SID = 2;