Database Concepts

8.1 Relational Database Model

The relational database model, introduced by Edgar F. Codd in 1970, is the most widely used database model today. It organizes data into tables with rows and columns, representing entities and their attributes. The key principles of this model are based on set theory and relational algebra.

Key Terminology

Understanding the following terms is crucial for working with relational databases:

• Table: A collection of related data organized into rows and columns. Represents an entity.
• Relation: Another term for a table in relational database terminology.
• Tuple: A row in a table. Represents a single instance of the entity.
• Attribute: A column in a table. Represents a characteristic of the entity.
• Primary Key: An attribute (or set of attributes) that uniquely identifies each tuple in a table.
• Foreign Key: An attribute in one table that refers to the primary key of another table. Used to establish relationships between tables.
• Relationship: A link between two or more tables based on shared attributes.
• Normalization: A process of organizing data in a database to reduce redundancy and improve data integrity.

Relational Algebra Operations

Relational algebra provides a set of operations for manipulating data in relational databases. These operations are used to query and modify data.

Operation	Description	Example
Selection (σ)	Filters tuples based on a specified condition.	σage > 25 (Selects all employees older than 25)
Projection (π)	Selects specific attributes from a table.	πname, salary (Projects only the name and salary columns)
Union (∪)	Combines the tuples of two relations.	Employee Union Department (Combines all employees and all departments)
Intersection (∩)	Returns tuples that exist in both relations.	Employee Intersection Manager (Finds employees who are also managers)
Difference (–)	Returns tuples that exist in the first relation but not in the second.	Employee Difference Department (Finds employees who are not in any department)
Cartesian Product (×)	Combines each tuple from the first relation with each tuple from the second relation.	Employee × Department (Creates all possible combinations of employees and departments)
Rename (ρ)	Renames a relation or its attributes.	ρNewEmployeeTable(EmployeeTable)

Relationships between Tables

Relational databases use relationships to link tables together. Common types of relationships include:

• One-to-One: One tuple in table A relates to at most one tuple in table B.
• One-to-Many: One tuple in table A can relate to multiple tuples in table B.
• Many-to-Many: Multiple tuples in table A can relate to multiple tuples in table B. This is typically resolved using a junction table.

Relationships are implemented using foreign keys. A foreign key in one table references the primary key in another table, establishing the link between the two.

Normalization

Normalization is a process of organizing the attributes and relations in a database to reduce redundancy and improve data integrity. There are several normal forms (1NF, 2NF, 3NF, BCNF) that define different levels of normalization. Higher normal forms generally result in more efficient and reliable databases.

For example, consider a table containing employee information with redundant information about the department name for each employee. Normalization would involve splitting this table into two separate tables: one for employees and one for departments, with a foreign key linking them.

Example

Consider a database for a library. We might have two tables:

Table	Attributes
Books	BookID (PK), Title, Author, ISBN
Members	MemberID (PK), Name, Address, Phone
Loans	LoanID (PK), BookID (FK), MemberID (FK), LoanDate, DueDate

Here, the Loans table uses foreign keys BookID and MemberID to relate to the Books and Members tables, respectively. This allows us to easily query for information about which books are currently loaned out to which members.