MIS602
Data Modelling and Database Design
Student Name:
Student id:
Task 1
1. Entities and Attributes
Customer
Attributes:
customer_id (Primary Key)
name
address
phone
email
customer_type (Individual, Commercial, Residential)
billing_preferences (payment methods)
bin_requirements (bin size)
Bin
Attributes:
bin_id (Primary Key)
customer_id (Foreign Key)
size (280L, 660L)
status (active, inactive)
Truck
Attributes:
truck_id (Primary Key)
driver_id (Foreign Key)
capacity
maintenance_schedule
availability
route_history
Collection
Attributes:
collection_id (Primary Key)
customer_id (Foreign Key)
bin_id (Foreign Key)
collection_date
items_collected
payment_status
Payment
Attributes:
payment_id (Primary Key)
collection_id (Foreign Key)
amount
payment_method (cash, direct debit, bank transfer)
donation_amount (if applicable)
receipt
Donation
Attributes:
donation_id (Primary Key)
customer_id (Foreign Key)
amount
charity_name
receipt
User
Attributes:
user_id (Primary Key)
username
password
role (administrator, driver, customer service representative, customer)
Notification
Attributes:
notification_id (Primary Key)
customer_id (Foreign Key)
message
date_sent
Report
Attributes:
report_id (Primary Key)
customer_id (Foreign Key)
report_type (interaction, financial summary, impact assessment, achievement)
content
date_generated
Relationships, Cardinality, and Multiplicity:
Customer and Bin (One-to-Many):
Relationship Name: "Has"
Explanation: Each CUSTOMER can have multiple BIN entities assigned to them, but each BIN is associated with only one customer. This one-to-many relationship enables the system to manage multiple bins per customer efficiently, supporting different bin requirements (e.g., sizes).
Customer and Collection (One-to-Many):
Relationship Name: "Has"
Explanation: Each CUSTOMER can have multiple COLLECTION records associated with their bins over time, while each collection record belongs to one customer. This structure captures the recycling history of each customer, detailing their individual recycling activities.
Bin and Collection (One-to-Many):
Relationship Name: "Is Collected In"
Explanation: Each BIN can have multiple COLLECTION instances as it is collected and emptied repeatedly, but each collection event references only one bin. This one-to-many relationship allows for the tracking of each bin’s collection events.
Collection and Payment (One-to-One):
Relationship Name: "Generates"
Explanation: Each COLLECTION generates one PAYMENT record, and each payment is linked to a single collection event. This one-to-one relationship ensures that every collection event has a corresponding payment record.
Customer and Donation (One-to-Many):
Relationship Name: "Makes"
Explanation: Each CUSTOMER can make multiple DONATION entries, while each donation is linked to only one customer. This one-to-many relationship tracks donations and their details for each customer.
User and Truck (One-to-Many):
Relationship Name: "Manages"
Explanation: Each USER (e.g., driver) can manage multiple TRUCK entities, but each truck is assigned to a single user at a time. This relationship ensures that truck management is organized and linked to specific users.
User and Collection (One-to-Many):
Relationship Name: "Performs"
Explanation: Each USER can perform multiple COLLECTION events, but each collection event is assigned to one user. This tracks which user is responsible for each collection.
Customer and Notification (One-to-Many):
Relationship Name: "Receives"
Explanation: Each CUSTOMER can receive multiple NOTIFICATION messages, but each notification pertains to only one customer. This structure supports communication management for service updates.
Customer and Report (One-to-Many):
Relationship Name: "Receives"
Explanation: Each CUSTOMER can receive multiple REPORT entries detailing their recycling impact, but each report is linked to only one customer. This one-to-many relationship provides insights into customer contributions and environmental benefits.
Cardinality and Multiplicity Summary:
One-to-Many: Common for entities like customers with bins, collections, donations, and reports, indicating one entity can relate to multiple others.
One-to-One: Used for relationships like collection and payment, ensuring each event has a unique financial record.
Conceptual Model
The conceptual data model for Recycle4Change (R4C) illustrates how various entities within the recycling management system interact. The core entities include CUSTOMER, BIN, TRUCK, COLLECTION, PAYMENT, DONATION, USER, NOTIFICATION, and REPORT. Each entity has its attributes and relationships, providing a clear structure for managing R4C's operations.
Customer Management: The CUSTOMER entity stores information like name, address, contact details, and customer type (Individual, Commercial, or Residential). It connects to other entities, such as BIN, COLLECTION, DONATION, NOTIFICATION, and REPORT, ensuring comprehensive tracking of customer interactions, recycling activities, and communications.
Bin and Collection Management: The BIN entity keeps track of bins assigned to customers, including their size and status. It links with the COLLECTION entity, which records when bins are collected, the number of items, and the responsible user (driver). The connection between bins, collections, and customers facilitates efficient recycling operations and ensures accountability.
User and Truck Management: The USER entity stores data about system users, including administrators, drivers, and customer service representatives. Users are connected to TRUCK, indicating the trucks they manage, and to COLLECTION, showing who performs specific collections. This relationship allows for the monitoring of truck routes, collection efficiency, and user responsibilities
Payment and Donation Tracking: The PAYMENT entity links with COLLECTION to record payments based on collected items, while the DONATION entity captures customer contributions to charities. This structure supports accurate billing, payment processing, and donation tracking.
Communication and Reporting: The NOTIFICATION and REPORT entities manage customer communications and generate insights on recycling activities, providing updates and detailed reports on customer behavior and environmental impact.
Task 2
Entity |
Attributes |
Primary Key |
CUSTOMER |
customer_id (int), name (varchar), address (varchar), phone (varchar), email (varchar), customer_type (varchar), billing_preferences (varchar), bin_requirements (varchar) |
customer_id |
BIN |
bin_id (int), customer_id (int, FK), size (varchar), status (varchar) |
bin_id |
TRUCK |
truck_id (int), user_id (int, FK), capacity (varchar), maintenance_schedule (varchar), availability (varchar), route_history (varchar) |
truck_id |
COLLECTION |
collection_id (int), customer_id (int, FK), bin_id (int, FK), user_id (int, FK), collection_date (date), items_collected (int), payment_status (varchar) |
collection_id |
PAYMENT |
payment_id (int), collection_id (int, FK), amount (float), payment_method (varchar), donation_amount (float), receipt (varchar) |
payment_id |
DONATION |
donation_id (int), customer_id (int, FK), amount (float), charity_name (varchar), receipt (varchar) |
donation_id |
USER |
user_id (int), username (varchar), password (varchar), role (varchar) |
user_id |
NOTIFICATION |
notification_id (int), customer_id (int, FK), message (varchar), date_sent (date) |
notification_id |
REPORT |
report_id (int), customer_id (int, FK), report_type (varchar), content (varchar), date_generated (date) |
report_id |
Relationships:
CUSTOMER has multiple BIN records (One-to-Many) as customers can have multiple bins.
CUSTOMER also has multiple COLLECTION records (One-to-Many) representing each time a bin is collected.
BIN is associated with multiple COLLECTION entries, showing which bin was collected in each record.
COLLECTION generates a single PAYMENT record, linking collections to payments.
CUSTOMER can make multiple DONATION records, indicating charitable contributions.
USER manages multiple TRUCK records, indicating which user is assigned to which truck.
USER also performs multiple COLLECTION operations (One-to-Many).
CUSTOMER can receive multiple NOTIFICATION and REPORT records.
Task 3
Dummy Input Screen
Completeness of data is very much important in ensuring that all information required would indeed be captured by the R4C database design, as based on the business needs of the case study (Cammerer et al., 2020, p7). Entities within the design include CUSTOMER, BIN, TRUCK, and COLLECTION. Each has attributes considered relevant to allow this information to store data on customer profiles, bin details, truck management, and collection activities. Apart from the extensive structure that it holds, R4C will be able to carry out its operational objectives only to provide a seamless experience for its users.
After the prototyping of the representative input screen, some modifications were required to finesse both the conceptual and the logical model. First, the interface itself introduced the necessity of adding more data fields in certain parts to develop it. For instance, the addition of the customer's preference fields or even more detailed bin specifications developed the user's interaction (Elmasri et al., 2020). Relationships were further adjusted; for instance, the relationship between CUSTOMER and DONATION was extended to catch more options of donation in a manner more fitting to user needs. Basically, through these design iterations, the database would go further to meet the original requirements but also to allow users through the recycling management process more intuitively and effectively.
References
Cammerer, S., Aoudia, F. A., Dörner, S., Stark, M., Hoydis, J., & Ten Brink, S. (2020). Trainable communication systems: Concepts and prototype. IEEE Transactions on Communications, 68(9), 5489-5503. https://arxiv.org/pdf/1911.13055
Elmasri, R., Navathe, S. B., Elmasri, R., & Navathe, S. B. (2020, August). Fundamentals of Database Systems</Title. In Advances in Databases and Information Systems: 24th European Conference, ADBIS 2020, Lyon, France, August 25–27, 2020, Proceedings (Vol. 12245, p. 139). Springer Nature. https://thuvienso.hoasen.edu.vn/bitstream/handle/123456789/8569/Contents.pdf?sequence=3&isAllowed=y
