What is a Relational Database?

The example of a database we have discussed so far, a phone book, is really just a single table with a single set of fields. Most of the data in the real word is not this simple. Some data, in fact most data, works better in a database with multiple tables. This is where a relational database comes in. A relational database has multiple tables so that you can store and analyze multiple lists of related data. This is necessary because although data is related, there is not always a one-to-one relationship between different categories (fields) of data, making it difficult to figure out how to design the table. For instance, to keep track of clients you can create a table to hold client names and contact information. However, you would also like to keep track of the projects you do. Each project is for a client, but you may do more than one project for a client. This graphic shows how you might store this data on index cards with one card for each client, and one card for each project.

Here's a simple schema to represent the relationship between the "Client" (left rectangle) and the "Tasks/Projects" (two right rectangles):
Schema Representation

1. Client Table
   • Table Name: Clients
   • Fields:
     • ClientID (Primary Key): Unique identifier for the client.
     • ClientName: Name of the client (e.g., "Network Consultants, Inc.").
     • Address: Client address (e.g., "455 Cherry St., Tucson, AZ 85701").
2. Tasks/Projects Table
   • Table Name: Tasks
   • Fields:
     • TaskID (Primary Key): Unique identifier for the task/project.
     • TaskName: Description of the task/project (e.g., "User documentation for new network").
     • DueDate: Due date of the task (e.g., "2/3/00").
     • EstimatedHours: Estimated hours required for the task (e.g., "240").
     • ClientID (Foreign Key): References the ClientID in the Clients table to establish the relationship.

Entity Relationship

• One-to-Many:
  • One client in the Clients table can have many associated tasks in the Tasks table.
  • The relationship is achieved through the ClientID field, which acts as a foreign key in the Tasks table.

Example SQL Code to Create Schema

CREATE TABLE Clients (
    ClientID INT PRIMARY KEY,
    ClientName VARCHAR(255) NOT NULL,
    Address VARCHAR(255)
);

CREATE TABLE Tasks (
    TaskID INT PRIMARY KEY,
    TaskName VARCHAR(255) NOT NULL,
    DueDate DATE,
    EstimatedHours INT,
    ClientID INT,
    FOREIGN KEY (ClientID) REFERENCES Clients(ClientID)
);

This schema models the relationship shown in the image, with `Clients` as the parent entity and `Tasks` as the child entity.

---

Client and Project Cards