Relational Databases (Leaving Cert Computer Science): Revision Notes
Relational Databases
A relational database is a type of database that stores and provides access to data points that are related to one another.
Consider two databases that the State Examinations Commission may use :
- A database to store students who are taking computer science.
- A database to store the grade that the student got in computer science
Student Table
A primary key is a unique identifier for each record in a database table. It ensures that each row in the table can be uniquely identified by the values in one or more columns.
Key Characteristics of a Primary Key:
- Uniqueness: The primary key value must be unique for each row in the table. No two rows can have the same primary key value.
- Non-Null: The primary key column (or columns) cannot contain null values. Every record must have a value for the primary key.
- Immutability: Ideally, the primary key value should not change over time. It should be stable and permanent throughout the lifetime of the record.
- Single or Composite: A primary key can consist of a single column or multiple columns combined together. When it consists of multiple columns, it is known as a composite primary key.
For the Student Table defined above, the primary key would be the ID column since each student can be identified by their ID.
The full name table would be unsuitable to be the primary key column since there may be multiple people with the same name.
A foreign key is a column or a set of columns in one database table that creates a link between data in two tables. It is a field that refers to the primary key in another table which creates a relationship between the two tables.
Student Table & Grade Table
Here, the Exam ID in the Student Table is a foreign key that reference the Grade Table. The Exam ID in the Grade Table is the primary key for the Grade Table
It is important to note that a primary key can also be a foreign key to another table.