The relational model is a foundational concept in database management, introduced by E.F. Codd in 1970. It organizes data into tables (also known as relations) which can be easily manipulated using a structured query language (SQL). This module will cover the basic principles of the relational model, its components, and how it is used to manage data efficiently.

• Definition: A table is a collection of related data entries and consists of rows and columns.
• Structure:
  • Rows (Tuples): Each row in a table represents a single record.
  • Columns (Attributes): Each column represents a specific attribute of the data.

• Definition: The schema of a table defines its structure, including the table name, column names, and data types.
• Example:

  CREATE TABLE Employees (
      EmployeeID INT PRIMARY KEY,
      FirstName VARCHAR(50),
      LastName VARCHAR(50),
      BirthDate DATE,
      Position VARCHAR(50)
  );
  

• Primary Key: A unique identifier for each record in a table.
  • Example: EmployeeID in the Employees table.
• Foreign Key: A field in one table that uniquely identifies a row of another table.
  • Example: DepartmentID in the Employees table that references DepartmentID in the Departments table.

• One-to-One: Each row in Table A is linked to one and only one row in Table B.
• One-to-Many: Each row in Table A can be linked to multiple rows in Table B.
• Many-to-Many: Rows in Table A can be linked to multiple rows in Table B and vice versa.

• Entity Integrity: Ensures that each table has a primary key and that the key is unique and not null.
• Referential Integrity: Ensures that foreign keys correctly and consistently reference primary keys in other tables.

1. Create Departments Table:

   CREATE TABLE Departments (
       DepartmentID INT PRIMARY KEY,
       DepartmentName VARCHAR(50)
   );
   

2. Create Employees Table with Foreign Key:

   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50),
       BirthDate DATE,
       Position VARCHAR(50),
       DepartmentID INT,
       FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
   );
   

1. Insert Data into Departments Table:

   INSERT INTO Departments (DepartmentID, DepartmentName)
   VALUES (1, 'Human Resources'), (2, 'Engineering'), (3, 'Marketing');
   

2. Insert Data into Employees Table:

   INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, Position, DepartmentID)
   VALUES (101, 'John', 'Doe', '1980-01-15', 'Manager', 1),
          (102, 'Jane', 'Smith', '1985-03-22', 'Engineer', 2),
          (103, 'Emily', 'Jones', '1990-07-30', 'Marketer', 3);
   

1. Retrieve All Employees:

   SELECT * FROM Employees;
   

2. Join Employees and Departments Tables:

   SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
   FROM Employees
   JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
   

1. Create a Projects Table:

   CREATE TABLE Projects (
       ProjectID INT PRIMARY KEY,
       ProjectName VARCHAR(100),
       StartDate DATE,
       EndDate DATE
   );
   

2. Insert Data into Projects Table:

   INSERT INTO Projects (ProjectID, ProjectName, StartDate, EndDate)
   VALUES (1, 'Project Alpha', '2023-01-01', '2023-06-30'),
          (2, 'Project Beta', '2023-02-01', '2023-12-31');
   

1. Add a Foreign Key to Employees Table:

   ALTER TABLE Employees
   ADD ProjectID INT,
   ADD FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID);
   

2. Insert Data with Foreign Key:

   UPDATE Employees
   SET ProjectID = 1
   WHERE EmployeeID = 101;
   
   UPDATE Employees
   SET ProjectID = 2
   WHERE EmployeeID = 102;
   

3. Query Employees and Their Projects:

   SELECT Employees.FirstName, Employees.LastName, Projects.ProjectName
   FROM Employees
   JOIN Projects ON Employees.ProjectID = Projects.ProjectID;
   

1. Solution for Exercise 1:

   CREATE TABLE Projects (
       ProjectID INT PRIMARY KEY,
       ProjectName VARCHAR(100),
       StartDate DATE,
       EndDate DATE
   );
   
   INSERT INTO Projects (ProjectID, ProjectName, StartDate, EndDate)
   VALUES (1, 'Project Alpha', '2023-01-01', '2023-06-30'),
          (2, 'Project Beta', '2023-02-01', '2023-12-31');
   

2. Solution for Exercise 2:

   ALTER TABLE Employees
   ADD ProjectID INT,
   ADD FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID);
   
   UPDATE Employees
   SET ProjectID = 1
   WHERE EmployeeID = 101;
   
   UPDATE Employees
   SET ProjectID = 2
   WHERE EmployeeID = 102;
   
   SELECT Employees.FirstName, Employees.LastName, Projects.ProjectName
   FROM Employees
   JOIN Projects ON Employees.ProjectID = Projects.ProjectID;
   

In this module, we covered the fundamental concepts of the relational model, including tables, schemas, keys, relationships, and integrity constraints. We also provided practical examples and exercises to help solidify your understanding. Mastering these concepts is crucial as they form the backbone of relational database management systems (RDBMS). In the next module, we will delve into the SQL language, which is used to interact with relational databases.