Dove Inc. Relational Database Solution

Introduction

Dove Inc. is a fictional company involved in the procurement and supply of stationery to institutions and organizations. They seek to have a database that stores and manages information about the company’s employees, branches, clients, and suppliers. Based on the activities of the company, it has some specific data storage requirements. As an SQL Database Administrator, I have been tasked with designing the database based on the Company’s requirements.

Company Data Storage Requirements

The company is organized into branches. Each branch has a unique number, a name, and a particular employee who manages it.

The company makes its money by selling to clients. Each client has a name and a unique number to identify it.

The foundation of the company is its employees. Each employee has a name, birthday, sex, salary and a unique number.

An employee can work for one branch at a time, and each branch will be managed by one of the employees that work there. We’ll also want to keep track of when the current manager started as manager.

An employee can act as a supervisor for other employees at the branch, an employee may also act as the supervisor for employees at other branches. An employee can have at most one supervisor.

A branch may handle a number of clients, with each client having a name and a unique number to identify it. A single client may only be handled by one branch at a time.

Employees can work with clients controlled by their branch to sell them stuff. If necessary, multiple employees can work with the same client. We’ll want to keep track of how many dollars worth of stuff each employee sells to each client they work with.

Many branches will need to work with suppliers to buy inventory. For each supplier, we’ll keep track of their name and the type of product they’re selling the branch. A single supplier may supply products to multiple branches.

When an Employee gets to age 65, they officially retire from the company and should be deleted from the Database.

Creating the Company ER Diagram

First, I created the ER Diagram to define the relationship model of the DB Schema with the given data storage requirements. To do this, I had to go over every requirement step by step to properly establish the entities (Tables) and their attributes (Columns), the relationships between entities, the cardinality of the relationships, and the level of participation between entities in a relationship.

Step One:- The company is organized into branches. Each branch has a unique number, a name, and a particular employee who manages it.

Table - Branch

Columns - branch_id (Unique number - Primary key), branch_name

Step Two:- The company makes its money by selling to clients. Each client has a name and a unique number to identify it.

Table - Client

Columns - client_id (Unique number - Primary key), Client_name

Step Three:- The foundation of the company is its employees. Each employee has a name, birthday, sex, salary, and a unique number.

Table - Employee

Columns - employee_id (Unique number - Primary key), first_name, last_name, birth_date, sex, salary, age (derived attribute).

Step Four:- An employee can work for one branch at a time, and each branch will be managed by one of the employees who work there. We’ll also want to keep track of when the current manager started as a manager.

This establishes a “works for” relationship between the employees and branch. The relationship is of total participation on both sides, as all employees must work for a branch and all branches must have an employee working in them. The cardinality of the employee-to-branch relationship is N:1, meaning any number of employees can work for at most one branch.

This also establishes a “manages” relationship between employee and branch. The relationship has a cardinality of 1:1, as one branch can be managed by only one employee, and one employee manages only one branch. The relationship is a partial participation on the employee to branch side, as not all employees will manage a branch, and a total participation on the branch to employee side, as every branch will have an employee managing it.

Column - Manager_start_date (attribute of the branch table indicating when the employee who manages it was appointed as manager there.)

Step Five:- An employee can act as a supervisor for other employees at the branch, an employee may also act as the supervisor for employees at other branches. An employee can have at most one supervisor.

This establishes a supervision relationship between employees with a cardinality of 1:N between Supervisor and Supervisee. This means an employee (a supervisor) can supervise any number of employees, and an employee (supervisee) can only be supervised by one employee.

Step Six:- A branch may handle a number of clients, with each client having a name and a unique number to identify it. A single client may only be handled by one branch at a time.

This establishes a “handles” relationship between clients and branches with a cardinality of N:1. This means that a branch can handle any number of clients, but a client can only be handle by one branch. The client's name and unique number have been covered in step two.

The relationship is of total participation on the client to branch side, as every client must be handled by a branch, and of partial participation on the branch to client side, as not all branches deal directly with clients. Like a cooperate branch, for example.

Step Seven:- Employees can work with clients controlled by their branch to sell them stuff. If necessary, multiple employees can work with the same client. We’ll want to keep track of how many dollars worth of stuff each employee sells to each client they work with.

This establishes a “works with” relationship between clients and employees with a cardinality of N:M, which means that any number of employees can work with any number of clients. The relationship is of total participation on the client to employee side and partial participation on the employee to client side.

This “works with” relationship has a sales attribute that seeks to track the sales made by individual employees to each client.

Step Eight:- Many branches will need to work with suppliers to buy inventory. For each supplier we’ll keep track of their name and the type of product they’re selling to the branch. A single supplier may supply products to multiple branches.

Table - Branch Supplier

Column - Supplier_name, Supply_type

This is a weak entity (supplier name cannot uniquely identify each supplier and supply type) that has a relationship with the branch entity, which has a cardinality of N:M; any number of suppliers can work with any number of branches. There is a total participation in this relationship on the suppliers to branch side and a partial participation on the branch’s side.

Step Nine:- When an Employee gets to age 65, they officially retire from the company and should be deleted from the Database.

This is an event that should happen when an employee becomes 65 years old.

Company ER Diagram

Converting the ER Diagram into the Company Database Schema

Step One - Mapping of Regular Entity Types

Creation of a table for each of the regular entities with the simple attributes as columns.

Table - Employee

Columns - employee_id (Unique number - Primary key), first_name, last_name, birth_data, sex, salary

Table - Branch

Columns - branch_id (Unique number - Primary key), branch_name

Table - Client

Columns - client_id (Unique number - Primary key), Client_name

Step Two - Mapping of Weak Entity Types

Table - Branch Supplier

Column - branch_id, Supplier_name, Supply_type ( branch_id & Supplier_name as composite key or compound primary key).

Step Three - Mapping of Binary 1:1 Cardinality Relationships

For 1:1 binary relationship, the primary key of the entity with partial participation is included in the attributes of the other entity as a foreign key. In this case, the primary key of the employee table is included in the column of the branch table as a foreign key named Manager_Id to define the 1:1 manages relationship between employee and branch, in which Branch has total participation. Also, the attribute manager's start date is included in the Branch table

Step Four - Mapping of Binary 1:N Cardinality Relationships

For binary 1:N relationships, the 1 side’s primary key is included as a foreign key in the N side’s table.

Employee N: Works for: 1 Branch (Branch_id as a foreign key in Employee table)

Branch 1: Handles: N Clients (Branch_id as a foreign key in the Clients table)

Supervisor 1 : Supervises: N Employees (Supervisor_Id as a foreign key in the Employee Table)

Step Five- Mapping of Binary M:N Cardinality Relationships

For M:N relationships, a new table is created that has both entities’ primary keys as a compound primary key and includes any attributes of the relationship.

Client N: Works With: M Employees.

Table - Works With

Columns - Employee_id, Client_id, Sales

SQL Script To Create The Database

CREATE DATABASE Company_DB;

CREATE TABLE Company_DB.Employee (

emp_id INT PRIMARY KEY,

First_Name VARCHAR (30),

Last_Name VARCHAR (30),

Birth_Date DATE,

Sex VARCHAR (1),

Salary INT,

Super_id INT,

branch_id INT

);

CREATE TABLE Company_DB.Branch (

branch_id INT PRIMARY KEY,

branch_Name VARCHAR (30),

mgr_id INT,

mgr_start_Date DATE,

FOREIGN KEY (mgr_id) REFERENCES Employee(emp_id) ON DELETE SET NULL

);

ALTER TABLE company_db.employee

ADD FOREIGN KEY (Super_id) REFERENCES company_db.Employee(emp_id) ON DELETE SET NULL

;

ALTER TABLE company_db.employee

ADD FOREIGN KEY (branch_id) REFERENCES company_db.branch(branch_id) ON DELETE SET NULL

;

CREATE TABLE company_db.client (

Client_id INT PRIMARY KEY,

Client_name VARCHAR (30),

Branch_id INT,

FOREIGN KEY (branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL

);

CREATE TABLE company_db.Works_with(

Emp_id INT,

Client_id INT,

Total_Sales INT,

PRIMARY KEY (Emp_id, Client_id),

FOREIGN KEY (Emp_id) REFERENCES Employee(emp_id) ON DELETE CASCADE,

FOREIGN KEY (Client_id) REFERENCES Client(Client_id) ON DELETE CASCADE

);

CREATE TABLE company_db.Branch_supplier(

Branch_id INT,

Supplier_Name VARCHAR (40),

Supply_Type VARCHAR (40),

PRIMARY KEY (Branch_id, Supplier_Name),

FOREIGN KEY (Branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE

);

DELIMITER $$

CREATE

EVENT Delete_Retirees

ON SCHEDULE EVERY 1 MONTH

DO

BEGIN

DELETE

FROM company_db.employee

WHERE age > 65

;

END $$

DELIMITER ;

Conclusions

This SQL Database project was initiated to address the challenge faced by Dove Inc, a fictional procurement and supply company, in managing its operational data related to employees, branches, clients, and suppliers. The absence of a structured database system posed risks to data integrity, accessibility, and overall efficiency. To resolve this, a thorough analysis of the company's data requirements was conducted, followed by the creation of an Entity-Relationship (ER) diagram that captured all relevant entities, their attributes, and relationships. The database schema was then designed and implemented using SQL, ensuring proper mapping of 1:1, 1:N, and M:N relationships, the inclusion of weak entities, and the use of foreign keys to enforce data consistency. Additional functionality such as automatic removal of retired employees was achieved using SQL event scheduling.

In executing this project, key principles of relational database design were applied, including normalisation, integrity constraints, and referential integrity. Skills demonstrated include ER modelling, SQL scripting, event scheduling, and professional documentation. The outcome is a scalable and maintainable SQL database that enables efficient data handling and retrieval across the organisation. This solution provides a blueprint for companies with similar needs, highlighting the importance of clear requirements gathering, systematic modelling, and robust implementation. Businesses are advised to invest in database planning, maintain clean schema architecture, and adopt automation where possible to improve long-term data operations.