Data Modelling and Database Design
Student Name:
Student ID:
Table of Contents
Task 1: Conceptual Data Model Design 4
Properties of the conceptual model 4
Cardinality and Multiplicity 9
Task 2: Logical Relational Model Design 11
Primary key and foreign key 13
Relationship and Cardinalities 14
Task 3: A sample dummy input screen 16
Residential bin collection Request 16
List of Figures
Introduction
Recycle4Change (R4C) is a social creativity focused on sustainability using the Victorian Government Container Deposit Scheme and its purpose of minimizing landfills and waste while promoting the employment of disadvantaged individuals. R4C has a collaboration with TOMRA Cleanaway Victoria and has engaged in the operation of two automated depots in North Geelong and Hoppers Crossing. This system will engage customers, commercial and residential, in the recycling process as it’s a strategy for the circular economy. Based on these objectives, a sound information system is needed to support all the aspects of the recycling business including customer sign-up, bin, fleet, billing, and donation as well as real-time communication.
This report thinks about the assessment of business requirements given in the case study, mapping these requirements into a conceptual model and creating a logical model that could be mapped to a relational DBMS. The last activity is to develop a sample dummy input screen that shows how the information will be entered through the system. The purpose of this report is therefore to make certain that the new system design can support the growth and stability of R4C in the achievement of its goal which is to support sustainable recycling employment in the society.
Task 1: Conceptual Data Model Design
The conceptual data model represents the general concept for the system’s database by identifying essential business entities, their fields, and the connection between them but does not consider database characteristics. The model draws a workflow of the company, such as customer registration, bins, trucking, billing, donation, and the route plan of Respiratory LLC also named R4C. The major entities are Customer, Bin, Truck, Collection, Payment, Donation, Depot, and many more, all of which store the basic information that is essential for the operation of the company – R4C (Guo and Onstein, 2020, p.2).
Some of the techniques used in the conceptual design include the ability to define primary entities, relations, and constraints like cardinality and multiplicity. From the above ER model Entities such as Customer and Bin relate to a one-to-many association this means that for a given customer he or she may be assigned to many bins. Cardinality and multiplicity are also useful to define how many of any one instance can refer to another instance of another. The above conceptual model also shows the relationships between these entities to pave the way for efficient and scalable extraction, storage, and analysis of data.
Properties of the conceptual model
Entity Concept:
The entities identified at the conceptual model's higher level are Customer, Bin, Truck, Payment, and Depot. These systems are all real-world organizations involved in the recycling system but in the simulation, are viewed more in terms of their role and interaction within the system rather than specific technical parameters (Elmasri, 2021,p.15).
Relationships Between Entities:
It explains how these objects are related and for instance, Customers to Bins or Trucks about Collection. Such relationships are many-to-ones, for example, any specific Customer may have several Bins, representing interactions between entities (Ramírez-Montoya et al., 2021, p.5).
Business Rules Interpretation:
The high-level business rules are built into the conceptual model. For instance, a Bin is exclusive to exactly one Customer, on the other hand, a Truck can undertake several Collections in some time frame. Such rules are useful in retaining data in security and also to guarantee that actions performed conform to business procedures.
Focus on Must Data:
The model largely overlooks aspects like primary keys, indexes, and data types that are important in the design of the system. It defines the basic data elements that map to major business activities supported by the system, the schedule of collection, billing, and donations.
The conceptual Database model for the design of the Case study
These are the details for the conceptual database model:
Entities
Customer
User
Customer Type
Bin
Bin Size
Truck
Driver
Depot
Container
Collection
Route
Maintenance Schedule
Payment
Donation
Charity
Invoice
Billing Preference
User Role
Notification
Recycling Impact Report
Attributes
Entity |
Attributes |
User Role |
User_Role_ID (PK), Role_Name, Role_Description |
User |
User_ID (PK), Username, Password, Email, User_Role_ID (FK) |
Billing Preference |
Billing_Preference_ID (PK), Preference_Type, Payment_Method |
Route |
Route_ID (PK), Start_Location, End_Location, Distance |
Depot |
Depot_ID (PK), Location, Contact_Number |
Bin Size |
Bin_Size_ID (PK), Size_Description, Capacity |
Driver |
Driver_ID (PK), Name, License_Number, Phone |
Truck |
Truck_ID (PK), License_Plate, Capacity, Driver_ID (FK) |
Customer |
Customer_ID (PK), Name, Address, Phone, Email, Customer_Type_ID (FK), Billing_Preference_ID (FK), User_ID (FK) |
Customer Type |
Customer_Type_ID (PK), Type_Description |
Container |
Container_ID (PK), Type, Volume, Customer_ID (FK) |
Bin |
Bin_ID (PK), Bin_Size_ID (FK), Customer_ID (FK), Depot_ID (FK), Bin_Status |
Invoice |
Invoice_ID (PK), Customer_ID (FK), Amount, Invoice_Date, Payment_Status |
Payment |
Payment_ID (PK), Customer_ID (FK), Amount, Payment_Date, Payment_Method, Invoice_ID (FK) |
Charity |
Charity ID (PK), Name, Description |
Donation |
Donation_ID (PK), Payment_ID (FK), Donation_Amount. Charity ID (FK) |
Maintenance Schedule |
Maintenance_ID (PK), Truck_ID (FK), Maintenance_Date, Maintenance_Notes |
Collection |
Collection_ID (PK), Bin_ID (FK), Truck_ID (FK), Route_ID (FK), Collection_Date, Collection_Status |
Notification |
Notification_ID (PK), Customer_ID (FK), Notification_Type, Message, Date_Sent |
Relationship
In the database, the term relationship implies the way two or more entities work or alter each other. From the outcome of an experiment, relationships are fundamental to interpret data and also to obtain accurate data.
Relationships In the Case of Entities
User Role - User
One User Role can be assigned to many Users User Roles can only be assigned to Users. In addition, implemented similarly, each User is connected with just one User Role.
User - Customer
one particular User can work with several Customers associated with the account. At the same time, one Customer is connected with one User only.
Billing Preference – Customer.
One Billing Preference may relate to many Customers. Consequently, a Customer shall have only one Billing Preference. It should be noted that this is a one-to-many association.
Route–collection
Many Collections can be associated with one Route. Likewise, each Collection is linked with only one Route since both of them are integrated into the third component of the system architecture referred to as the Processing Component.
Depot - Bin
One Depot may accommodate many Bins. Consequently, every Bin is located in completely one Depot or is incorporated into the structure of a definite Depot. This is a many-to-one relationship where it is clear that a depot can oversee many bins.
Bin Size - Bin
One Bin size can be assigned to several Bins. Consequently, every Bin has only one Bin Size linked to it.
Driver - Truck
One Driver can drive multiple Trucks. Consequently, each Truck is linked exactly with one particular Driver.
Truck – Maintenance Schedule
Maintenance Schedules can be many for one Truck. Moreover, each Maintenance Schedule is related to only one Truck, if one exists, that serves for its creation.
Customer Type – Customer
One Customer Type can accommodate several Customers. Similarly, each Customer is classified into only one Customer Type.
Container - Bin
One Container type can be kept in different Bins. Consequently, each Bin receiving information can hold only one kind of Container at any given time.
Bin - Collection
One Bin can be collected more than once. Consequently, each Collection is associated with precisely one Bin. This association is one to many for the reason that a bin can be emptied many times but each time refers to a unique collection event which refers to a particular bin.
Invoice – Payment
One Invoice can have many Payments. Consequently, each Payment has a link to only one Invoice. This type of relationship is one-to-many.
Payment – Donation
One Payment is common for several Donations possible. Instead, each Donation is connected to exactly one Payment which effectively says the same thing.
Donation – Charity
One Donation might be useful for some Charities. On the other hand, a Charity can receive several Donations in return Depending again on the specifics of the donors’ transactions or agreements.
Collection – Notification
One Collection may create multiple Notifications to Customers. Consequently, each Notification is associated with only one Collection.
Cardinality and Multiplicity
Entities |
Cardinality |
Multiplicity |
User – customer |
One–to–many |
(1,*) |
User – User Role |
Many to – one |
(* , 1) |
Customer – Bin |
One–to–many |
(1,*) |
User – billing Preference |
One–to–many |
(1,*) |
Customer – Donation |
One–to–many |
(1,*) |
Donation - Charity |
Many-to-one |
(*, 1) |
Depot – Truck |
One-to-Many |
(1, *) |
Truck – Driver |
One-to-One |
(1, 1) |
Bin Size – Bin |
One-to-Many |
(1, *) |
Route – Truck |
Many-to-Many |
(*, *) |
Maintenance Schedule – Truck |
One-to-Many |
(1, *) |
Customer – Invoice |
One-to-Many |
(1, *) |
Invoice – Payment |
One-to-One |
(1, 1) |
Collection – Bin |
One-to-One |
(1, 1) |
Notification – Collection |
One-to-One |
(1, 1) |
Customer – Customer Type |
Many-to-One |
(*, 1) |
Relationship names
Relationship |
Description |
User - Customer |
Manages (User manages Customer) |
User – user Role |
Has (User has a User Role) |
Customer - Bin |
Owns (Customer owns Bin) |
User - Billing Preference |
Specifies (User specifies Billing Preference) |
Customer - Donation |
Makes (Customer makes Donation) |
Donation - Charity |
Supports (Donation supports Charity) |
Depot - Truck |
Services (Depot services Truck) |
Truck - Driver |
Assigned To (Truck is assigned to Driver) |
Bin Size - Bin |
Determines (Bin Size determines Bin) |
Route - Truck |
Follows (Route follows Truck) |
Maintenance Schedule - Truck |
Contains (Maintenance Schedule contains Truck) |
Customer - Invoice |
Receives (Customer receives Invoice) |
Invoice - Payment |
Corresponds To (Invoice corresponds to Payment) |
Collection - Bin |
Includes (Collection includes Bin) |
Notification - Collection |
Relates To (Notification relates to Collection) |
Customer - Customer Type |
Belongs To (Customer belongs to Customer Type) |
Task 2: Logical Relational Model Design
The logical model also known as the structure is a representation of a real-world system through relational database design derived from the conceptual model. In this phase, the emphasis is placed on maintaining the data normalized. All the entities that were identified in the conceptual design phase are presented as tables some of the fields defined include PK and FK to maintain referential integrity (Nasar et al., 2021,p.4).
The logical model uses key fields, primary and foreign ones these fields show the connection between entities. For instance, the CustomerID is the primary key in the Customer table, and the foreign key in the Collection table shows one-to-many relationships. In the same context, relationships between the overall structure of the Truck and Collection entities are established through the TruckID (Fernandy et al., 2023,p.2).
The design for the logical model for the R4C.
Entity and Attributes
Entity |
Attributes |
User Role |
User_Role_ID (PK), Role_Name, Role_Description |
User |
User_ID (PK), Username, Password, Email, User_Role_ID (FK) |
Billing Preference |
Billing_Preference_ID (PK), Preference_Type, Payment_Method |
Route |
Route_ID (PK), Start_Location, End_Location, Distance |
Depot |
Depot_ID (PK), Location, Contact_Number |
Bin Size |
Bin_Size_ID (PK), Size_Description, Capacity |
Driver |
Driver_ID (PK), Name, License_Number, Phone |
Truck |
Truck_ID (PK), License_Plate, Capacity, Driver_ID (FK) |
Customer |
Customer_ID (PK), Name, Address, Phone, Email, Customer_Type_ID (FK), Billing_Preference_ID (FK), User_ID (FK) |
Customer Type |
Customer_Type_ID (PK), Type_Description |
Container |
Container_ID (PK), Type, Volume, Customer_ID (FK) |
Bin |
Bin_ID (PK), Bin_Size_ID (FK), Customer_ID (FK), Depot_ID (FK), Bin_Status |
Invoice |
Invoice_ID (PK), Customer_ID (FK), Amount, Invoice_Date, Payment_Status |
Payment |
Payment_ID (PK), Customer_ID (FK), Amount, Payment_Date, Payment_Method, Invoice_ID (FK) |
Charity |
Charity ID (PK), Name, Description |
Donation |
Donation_ID (PK), Payment_ID (FK), Donation_Amount. Charity ID (FK) |
Maintenance Schedule |
Maintenance_ID (PK), Truck_ID (FK), Maintenance_Date, Maintenance_Notes |
Collection |
Collection_ID (PK), Bin_ID (FK), Truck_ID (FK), Route_ID (FK), Collection_Date, Collection_Status |
Notification |
Notification_ID (PK), Customer_ID (FK), Notification_Type, Message, Date_Sent |
Primary key and foreign key
Entity |
PK & FK |
User |
PK: user_ID |
Customer |
PK:customer_ID |
Bin |
PK:bin_ID |
Billing Preference |
PK: Billing_Preference_ID |
Charity |
PK: charity_ID |
Donation |
PK: Donation_ID |
Depot |
PK: depot_ID |
Truck |
PK: Truck_ID |
Driver |
PK: driver_ID |
Bin Size |
PK: bin_size_ID |
Route |
PK: Route_ID |
Maintenance Schedule |
PK:schedule_ID |
Invoice |
PK:invoice_ID |
Payment |
PK:payment_ID |
Collection |
PK:collection_ID |
Notification |
PK: Notification_ID |
Customer Type |
PK: customer_type_ID |
Relationship and Cardinalities
Entities |
Cardinalities |
User – customer |
(1,*) |
User – User Role |
(*, 1) |
Customer – Bin |
(1,*) |
User – billing Preference |
(1,*) |
Customer – Donation |
(1,*) |
Donation - Charity |
(*, 1) |
Depot – Truck |
(1, *) |
Truck – Driver |
(1, 1) |
Bin Size – Bin |
(1, *) |
Route – Truck |
(*, *) |
Maintenance Schedule – Truck |
(1, *) |
Customer – Invoice |
(1, *) |
Invoice – Payment |
(1, 1) |
Collection – Bin |
(1, 1) |
Notification – Collection |
(1, 1) |
Customer – Customer Type |
(*, 1) |
Business Rule
Some accounts require user ID confirmation which includes name and email contact details to efficiently handle associated customer accounts (Döhmen et al., 2024,p.7).
Users are required to provide correct account details and communication and payment information to facilitate appropriate delivery of services and billing.
It is mandatory to select one or multiple billing options during the sign-up process whether through cash, bank transfer, or direct debit facilities.
Based on customer locations, the system should calculate the best path that each truck should take to reduce the cost of time and fuel costs.
Every depot should ensure that it has a proper record of trucks and bins that should be assigned to the depots to achieve an efficient system.
The bins come in standard sizes and clients need to choose which bin size is suitable for their use, and alteration of that size requires the approval of R4C due to the size stocking.
The tracking system requires that drivers update their availability and route progress at regular intervals to log deliveries as well as collections.
From the field, it was determined that trucks need regular check-ups for them to continue working and to meet safety requirements.
Customers must be properly grouped into Individuals, Companies, or Houses for charging bills and collection.
The customers remain responsible for placing the containers in the right zones to be picked up by the recycling companies.
Bins supplied to customers have to be managed and documented in the system for identification of bin location and its condition.
Price lists have to be prepared by the number of items sold to customers and must include any given donations.
All payments should be made and processed within five business days for all customers and clients but the ‘Individual’ customer type paid immediately.
Customers can make part or full share of their recycling money to a charity organization of their choice.
Trucks need to operate optimally and safely hence require a maintenance schedule.
This means that collections have to be planned in relation to the bins’ ability to accommodate material and the distance to the customer’s place.
Notifications to customers are required to be automated for future collections, services, updates, or changes in schedule (Thanuja et al., 2022,p.17).
Task 3: A sample dummy input screen
Residential bin collection Request
This screen can let a residential customer request for collection of bins. It comprises sections to fill in the details such as name, address, phone number, and bin size preferences as well as photo upload for proof of bin volume. Finally, the user chooses the type of payment, such as cash or bank transfer, and is informed of the result after submission is made. Input includes a Submit button, to confirm the request and a Cancel button to dismiss the form (Rasmussen et al., 2020,p.3).
Figure 3: Screen for Bin collection request
Fields and buttons
Name (Text input): A box that supplies a label for the customer's full name.
Address (Text input): A space that is used by clients to give their physical address for service provision.
Contact (Phone number input): A field for the phone number of communications concerning the service being provided.
Bin Size (Dropdown: 280L, 660L): An order list under which customers decide which bin size is needed for the collection of waste.
Upload Bin Photo (File upload): One of the options is that the client can take and upload a picture of the bin that want to book.
Payment Method (Dropdown: Cash, Bank Transfer): A drop-down menu that will display options of payment that customers would like to make.
Submit (Button): A button that confirms the request and sends the entered details to the base.
Cancel (Button): A button that directs the customer back without entering any information into the form.
Description
Data Completeness:
The database design fully accommodates all the requisite information based on the business requirements of the case study. Each customer type (residential, commercial, individual) has appropriate fields for tracking collections, bin size, billing, and payment preferences. The design also accounts for special cases like donations and tax receipts for charity contributions.
Design Iterations:
After creating the mock-up, minor adjustments were made to the logical model. Specifically, a new attribute for "PhotoUpload" was added to the Bin entity to accommodate the customer upload feature. Relationships between customers and trucks were thought again, conducting a more manager approach to group assignment by introducing a "Collection Schedule" entity for better operating controlling (Rasmussen et al., 2020, p.3).
References
Döhmen, T., Geacu, R., Hulsebos, M. & Schelter, S., (2024). SchemaPile: A Large Collection of Relational Database Schemas. Proceedings of the ACM on Management of Data, 2(3), pp.1-25.https://dl.acm.org/doi/abs/10.1145/3654975
Elmasri, R., (2021). Fundamentals of database systems seventh edition. http://debracollege.dspaces.org/bitstream/123456789/168/1/Fundamentals-of-Database-Systems-Pearson-2015-Ramez-Elmasri-Shamkant-B.-Navathe.pdf
Fernandy, M., Darmawan, K.R. & Kristiyanto, D.Y., (2023). Comparison Analysis of Native Database Design with Object-Oriented Design. Journal of Dinda: Data Science, Information Technology, and Data Analytics, 3(1), pp.6-10.https://journal.ittelkom-pwt.ac.id/index.php/dinda/article/view/707
Guo, D. & Onstein, E., (2020). State-of-the-art geospatial information processing in NoSQL databases. ISPRS International Journal of Geo-Information, 9(5), p.331. https://www.mdpi.com/2220-9964/9/5/331
Nasar, Z., Jaffry, S.W. & Malik, M.K., (2021). Named entity recognition and relation extraction: State-of-the-art. ACM Computing Surveys (CSUR), 54(1), pp.1-39. https://dl.acm.org/doi/abs/10.1145/3445965
Ramírez-Montoya, M.S., Álvarez-Icaza, I., Sanabria-Z, J., Lopez-Caudana, E., Alonso-Galicia, P.E. & Miranda, J., (2021). Scaling complex thinking for everyone: A conceptual and methodological framework. In Ninth International Conference on Technological Ecosystems for Enhancing Multiculturality (TEEM'21) (pp. 806-811). https://dl.acm.org/doi/abs/10.1145/3486011.3486562
Rasmussen, M.B., Pagels, K.Ø. & Ramanujan, D., (2020). Supporting household waste sorting practices by addressing information gaps. Journal of Computing and Information Science in Engineering, 20(4), p.041013.https://asmedigitalcollection.asme.org/computingengineering/article-abstract/20/4/041013/1081975
Thanuja, K., Thirumagal, E., Amuthabala, K. & Patil, S.D., (2022). Database Management Systems: An Introduction. MileStone Research Publications.https://books.google.com/books?hl=en&lr=&id=BmeWEAAAQBAJ&oi=fnd&pg=PA13&dq=What+is+PK+and+FK++in+Database+&ots=-uys3M_ekE&sig=A0YITiMD37DKdRoyv085U_bg7Y8
