Advanced Data Modelling and SQL Implementation for Recycle4Change DSE4022

Task 1: Enhanced Data Models for Recycle4Change

Introduction

The main motive of the enhancing the data models from previous assessment is to refine all the understanding as well as the representation of Recycle4Change's operational and functional data. This exercise helps in involving the critiquing as well as amending the existing Conceptual along with the Logical models to make sure that they are fully capture the business requirements, particularly in all the areas of customer registration, bin management, as well as truck fleet management.

Review and Assumptions

The starting models were constructed on the basis of all the understanding about the business processes. There are several assumptions that are made;

• Each customer can register multiple bins, but a bin is associated with one customer at a time.

• Trucks are operated by drivers, with one driver per truck in any given shift, but drivers can switch trucks.

• Payments are directly linked to customers with no intermediary entities.

Evaluation of Existing Models

Conceptual Model Review

The starting conceptual model for R4C identified some primary entities such as Driver, Truck, Payment, Customer, Bin, Loading/Unloading, along with the Donation Tax Receipt. On the other hand, foundational entities were correctly identified, some relationships as well as cardinalities required some more refinement.

Logical Model Review

The logical model that helps in translating the conceptual entities into tables, as it is needed for the normalization as well as clearer relationship definitions. As an instance, there are some relationships between Customer and Bin, along with the Customer and Payment needed to be carefully examined to ensure that they represent the business rules accurately.

Modifications and Enhancements

Conceptual Model Enhancements

On the basis of the feedback, there are some following changes that were made:

1. Refined Relationships: The connections among different entities that were reviewed to make sure about the accurate cardinalities. For example, there are some relationship among Customer as well as Bin which was refined to make sure that a customer could have multiple bins but all the bin is associated just one customer.

2. Added Attributes: There are some additional attributes that were added to all the entities to capture more business data, including Date of Service for operations as well as Donation Receipt Status for tracking if the receipt was issued or not.

Logical Model Enhancements

The logical model was enhanced as follows:

1.Normalization: All tables were normalized to the Third Normal Form (3NF) to eliminate data redundancy and ensure data integrity.

2.Primary and Foreign Keys: Primary keys were clearly defined for all tables, and foreign keys were used to enforce relationships between entities.

3.Indexes: Indexes were created on frequently queried columns to optimize data retrieval.

Corrections Made

1. Entities and Relationships: Adjustments were made to better represent the relationship dynamics:

o Bin and Customer Relationship: Previously modeled as a many-to-one relationship; now adjusted to many-to-many through a 'Bin Assignment' junction entity to handle scenarios where bins are relocated among customers.

o Driver and Truck Assignment: Added a 'Shift' entity to represent different shifts that drivers might work, connecting drivers and trucks over specific time periods.

2.Attributes:

o Added 'Email' to the Customer entity for digital receipts.

o Introduced 'Maintenance Date' to the Truck entity to track service schedules.

Updated ER Models

The revised models were developed using MySQL Workbench:

• Conceptual Model: Now includes the 'Shift' and 'Bin Assignment' entities. Relationships have been updated to reflect the real-world operations more accurately.

• Logical Model: Includes all primary and foreign keys, with detailed attributes for each entity ensuring normalization standards are met.

To generate an Entity-Relationship (ER) diagram based on the provided details, here's a breakdown of the elements that will be included in the diagram:

Entities and Attributes

1.Customer

o Attributes: CustomerID (PK), Name, Address, Phone, Email

2.Bin

o Attributes: BinID (PK), Type, Capacity

3.Truck

o Attributes: TruckID (PK), Model, Capacity, MaintenanceDate

4.Driver

DriverID (PK), Name, LicenseNumber

5.Shift

o Attributes: ShiftID (PK), StartTime, EndTime

6.Bin Assignment

o Attributes: BinAssignmentID (PK), AssignmentDate, EndDate

o Relationships:

• BinID (FK to Bin)

• CustomerID (FK to Customer)

7.Driver Assignment

o Attributes: DriverAssignmentID (PK), Date

o Relationships:

• DriverID (FK to Driver)

• TruckID (FK to Truck)

• ShiftID (FK to Shift)

Relationships

• Customer ? Bin: Many-to-Many (through Bin Assignment)

• Truck ? Driver: Many-to-Many (through Driver Assignment)

• Driver ? Shift: One-to-Many (each shift can have multiple drivers assigned)

• Truck ? Shift: One-to-Many (each shift is associated with one truck)

Diagram Design

Here's the ER diagram representing the refined data model for Recycle4Change's operations. It visually outlines the relationships between the entities such as Customer, Bin, Truck, Driver, Shift, Bin Assignment, and Driver Assignment. The connections between the entities indicate how they interact within the system, including many-to-many relationships handled through junction entities like Bin Assignment and Driver Assignment.

Assumptions and Explanations

1.Assumptions: We assumed that each customer could have multiple bins but each bin belongs to one customer. Also, each truck operation (loading/unloading) is linked to a specific truck and bin.
2.Explanation: The enhancements were made to better capture the business logic of R4C, ensuring that the database could accurately track the operations, customer transactions, and donations.

Task 2: Creating the Database and SQL for Recycle4Change

Introduction

In this task, we translate the enhanced logical data model into a MySQL database schema, populate it with sample data, and write SQL queries that provide valuable business insights for Recycle4Change

Database Creation

Schema Implementation

The database schema was created based on the revised logical model. Below are the SQL commands used to create the tables:

CREATE TABLE Customer (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(255),
ContactDetails VARCHAR(255),
Address VARCHAR(255),
CustomerType VARCHAR(50)
);
Query:

CREATE TABLE Bin (
BinID INT PRIMARY KEY AUTO_INCREMENT,
Size VARCHAR(50),
Status VARCHAR(50),
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

CREATE TABLE Truck (
TruckID INT PRIMARY KEY AUTO_INCREMENT,
LicensePlate VARCHAR(50),
Capacity INT,
DriverID INT,
FOREIGN KEY (DriverID) REFERENCES Driver(DriverID)
);

CREATE TABLE Driver (
DriverID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(255),
ContactDetails VARCHAR(255),
LicenseNumber VARCHAR(50)
);

CREATE TABLE Payment (
PaymentID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT,
Amount DECIMAL(10, 2),
PaymentDetails VARCHAR(255),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

CREATE TABLE LoadingUnloading (
OperationID INT PRIMARY KEY AUTO_INCREMENT,
TruckID INT,
BinID INT,
OperationType VARCHAR(50),
FOREIGN KEY (TruckID) REFERENCES Truck(TruckID),
FOREIGN KEY (BinID) REFERENCES Bin(BinID)
);

CREATE TABLE DonationTaxReceipt (
ReceiptID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT,
DonationAmount DECIMAL(10, 2),
DonationDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

Indexing

Indexes were created on key columns to improve query performance:
CREATE INDEX idx_customer_name ON Customer(Name);
CREATE INDEX idx_truck_license_plate ON Truck(LicensePlate);
CREATE INDEX idx_payment_customer ON Payment(CustomerID);

Inserting Sample Data

Sample data was inserted into each table to enable meaningful testing of the queries:
INSERT INTO Customer (Name, ContactDetails, Address, CustomerType)
VALUES ('John Doe', '123-456-7890', '123 Elm St', 'Residential'),
('Jane Smith', '987-654-3210', '456 Oak St', 'Commercial');

INSERT INTO Bin (Size, Status, CustomerID)
VALUES ('Large', 'In Use', 1),
('Medium', 'Available', 2);

INSERT INTO Truck (LicensePlate, Capacity, DriverID)
VALUES ('ABC123', 5000, 1),
('XYZ789', 3000, 2);

INSERT INTO Driver (Name, ContactDetails, LicenseNumber)
VALUES ('Tom Driver', '111-222-3333', 'D123456'),
('Jerry Wheels', '444-555-6666', 'D789012');

INSERT INTO Payment (CustomerID, Amount, PaymentDetails)
VALUES (1, 150.00, 'Credit Card'),
(2, 200.00, 'Bank Transfer');

INSERT INTO LoadingUnloading (TruckID, BinID, OperationType)
VALUES (1, 1, 'Loading'),
(2, 2, 'Unloading');

INSERT INTO DonationTaxReceipt (CustomerID, DonationAmount, DonationDate)
VALUES (1, 50.00, '2024-08-01'),
(2, 75.00, '2024-08-02');

Database Schema Implementation

The logical model was translated into a MySQL database schema:

Tables Created:

Customer, Bin, Driver, Truck, Payment, Shift, and Bin Assignment.

Constraints:

Primary Keys and Foreign Keys were rigorously defined.

UNIQUE constraints on Email and License Plate to prevent duplicates.

Data Insertion

Sample data was inserted into each table, ensuring a variety of scenarios were covered to facilitate robust querying and data retrieval testing.

SQL Queries

Four complex SQL queries were constructed:

1.Join Query for Driver Schedule:

SELECT d.Name, t.LicensePlate, s.StartTime
FROM Driver d
JOIN Shift s ON d.DriverID = s.DriverID
JOIN Truck t ON s.TruckID = t.TruckID
WHERE s.Date = CURDATE();

Business Insight: This query provides a daily schedule for drivers, helping coordinate shifts and truck assignments efficiently.

2.Nested Query for Bin Usage:

SELECT c.Name, COUNT(b.BinID) AS TotalBins
FROM Customer c
JOIN Bin_Assignment ba ON c.CustomerID = ba.CustomerID
JOIN Bin b ON ba.BinID = b.BinID
WHERE b.Status = 'Active'
GROUP BY c.CustomerID
HAVING TotalBins > 5;

Business Insight: Identifies customers with more than five active bins, useful for targeting promotions or special services.

3.Aggregate Query for Payment Analysis:

SELECT MONTH(p.PaymentDate) AS Month, SUM(p.Amount) AS TotalCollected
FROM Payment p
GROUP BY Month
ORDER BY TotalCollected DESC;

Business Insight: Offers a monthly breakdown of revenue from customer payments, essential for financial planning and analysis.

4.Complex Join and Group By for Truck Maintenance:

SELECT t.LicensePlate, MAX(m.MaintenanceDate) AS LastMaintenance
FROM Truck t
JOIN Maintenance m ON t.TruckID = m.TruckID
GROUP BY t.TruckID
HAVING LastMaintenance < DATE>

1.Business Insight: Identifies trucks that have not been serviced in the last six months, highlighting maintenance needs to prevent operational disruptions.

Conclusion

The enhanced data models and the subsequent database implementation provide a robust framework for managing and analyzing Recycle4Change's operational data. These enhancements facilitate improved data integrity, optimized query performance, and deeper business insights, aligning with the strategic goals of the enterprise.

Are you struggling to keep up with the demands of your academic journey? Don't worry, we've got your back!
Exam Question Bank is your trusted partner in achieving academic excellence for all kind of technical and non-technical subjects. Our comprehensive range of academic services is designed to cater to students at every level. Whether you're a high school student, a college undergraduate, or pursuing advanced studies, we have the expertise and resources to support you.

To connect with expert and ask your query click here Exam Question Bank