ICT 0417 - Databases

1. Introduction to Databases

A database is a structured collection of data that is organized for efficient storage, retrieval, and management. Databases are used in a wide variety of applications, from managing customer information to tracking inventory.

2. Key Concepts: Primary Key and Foreign Key

To ensure data integrity and relationships between different tables in a database, we use primary keys and foreign keys. These are fundamental concepts in relational database management.

2.1 Primary Key

A primary key is a column (or a set of columns) in a table that uniquely identifies each record (row) in that table. It must contain unique values and cannot contain null values.

Key characteristics of a primary key:

• Uniqueness: Each value in the primary key column must be unique.
• Not Null: A primary key column cannot contain null values.
• Stability: The value of a primary key should not change over time.

2.2 Foreign Key

A foreign key is a column (or set of columns) in one table that refers to the primary key of another table. It establishes a link between the two tables. It ensures referential integrity, meaning that relationships between tables are maintained.

Key characteristics of a foreign key:

• References Primary Key: The foreign key column must reference the primary key of another table.
• Can Contain Nulls: A foreign key column can contain null values, indicating that there is no relationship to a record in the referenced table.
• Enforces Referential Integrity: The database management system (DBMS) enforces referential integrity, preventing actions that would break the relationship between tables (e.g., deleting a record in the primary table if there are related records in the foreign table).

2.3 Relationship between Primary and Foreign Keys

The primary key of one table and the foreign key in another table define a relationship between the two tables. This relationship can be one-to-one, one-to-many, or many-to-many.

Concept	Description
Primary Key	Uniquely identifies each record in a table.
Foreign Key	References the primary key of another table, establishing a link.
Relationship	The link between the primary and foreign keys defines the relationship between the tables.

2.4 Example

Consider two tables: Customers and Orders.

Customers Table:

• CustomerID (Primary Key)
• Name
• Address

Orders Table:

• OrderID (Primary Key)
• CustomerID (Foreign Key referencing Customers.CustomerID)
• OrderDate
• TotalAmount

In this example, the CustomerID in the Orders table is a foreign key that links each order to a specific customer in the Customers table. This allows us to easily retrieve all orders placed by a particular customer.

3. Importance of Primary and Foreign Keys

Using primary and foreign keys is crucial for:

• Data Integrity: Ensures that data is accurate and consistent.
• Data Retrieval: Allows for efficient retrieval of related data from multiple tables.
• Data Consistency: Maintains consistency across related tables.
• Database Design: Provides a framework for designing well-structured and efficient databases.