Case Study: Recycle4Change (R4C) - Enabling a Sustainable Recycling System

Task 1. Entity Relationship diagram

Entities:

• Connolly and Begg (2015) say that entity-relationship diagram is essential for database architecture since it organizes and links data.
• The term "customer" is used to describe the people and businesses who have dealings with R4C. Name, address, phone number, and payment options are all part of this information. A distinction is made between "Individual," "Commercial," and "Residential" clients.
• Data pertaining to residential customers' bins is kept in the 'Bin' entity. Bin size and a unique identifier are two of the attributes included.
• This section keeps tabs on R4C's fleet of pickups and delivery vehicles, recording details such as mileage, storage capacity, and service intervals.
• The Collection Point symbols stand for the mechanized storage facilities and recycling hubs. Details such as whether a venue is currently open and how many people can fit inside are included.
• This organization keeps track of consumer donations, letting them give a set portion of each transaction to a designated cause.

Relationships:

• Since a single residential customer may have numerous bins, a one-to-many link between 'Customer' and 'Bin' is used to indicate this reality.
• A one-to-many link between 'Customer' and 'Truck' represents the collection of containers from business clients.
• There is a connection between bins and the locations (depots or centers) where they are stored.
• Relationship between Trucks and Collection Stations: Monitors Delivery Vehicles in Real-Time as They Travel Between Stations.
• Connecting donors with their desired recipients is the goal of the donation-customer relationship.

Attributes and Cardinality:

Name, address, and phone number are all characteristics of a "Customer."

Size and a special identifier are two of the characteristics of a 'bin.

In a database called "Truck," data like capacity and maintenance schedules can be kept.

The term "Collection Point" can be used to describe a location's characteristics.

The term "donation" is used to document monetary contributions and their associated tax documentation.

Aligning with their social enterprise goals, this ER diagram allows R4C to efficiently handle customer profiles, bin allocations, truck fleet operations, billing, communication, and reporting. The ability to clearly organize and relate data is crucial for building the Recycle4Change software system.

Task 2. Relational Diagram

It is essential for effective data storage and management, and this model will guarantee that the data is in third normal form (3NF). Silberschatz, Korth, and Sudarshan (2010) emphasize the importance of maintaining data in third normal form (3NF) for effective data storage and management. In addition, I'll share some presumptions and operational guidelines.

Relational Model for Interview Scheduling:

Table: Interviews

Fields:

• interview_id (Primary Key): Unique identifier for each interview.
• candidate_name: Stores the name of the candidate.
• interview_date: Represents the date of the interview.
• interview_time: Represents the time of the interview.
• interview_location: Stores the location of the interview.

Assumptions/Business Rules:

• The interview_id column is the primary key, which means it must be used to uniquely identify each interview record.
• All non-key properties are functionally dependent on the main key, and the relational model is normalized by being developed in the third normal form (3NF).
• To ensure data integrity, it is assumed that only correct data, such as dates and times, is entered into the system. oronel, Morris, and Rob (2012) highlight that the primary key, as the unique identifier, plays a critical role in ensuring data integrity in the relational model.
• The concept eliminates unnecessary duplication by storing information in relational databases.
• There are no inconsistencies in the data, and inaccurate or incomplete entries are prevented by the constraints set up.
• Data gathered by the application will match the table structures in the database since the model is in sync with the UI design.
• The model is extensible, so new data structures can be added as the application's needs develop.

This 3NF-compliant relational architecture effectively records and stores interview scheduling data while guaranteeing its quality and consistency. Each interview may be tracked back to its original source, and the data can be expanded and maintained with ease.

Task 3. A sample dummy input screen

The below figure shows that sample dummy screen for interview scheduler where the candidate need to enter data and it will be stored into the database.

The interview schedule example input screen is consistent with the original database layout. Candidates' names, interview dates, times, and locations can all be recorded in this way. Attributes from the relational model, such as "candidate_name," "interview_date," "interview_time," and "interview_location," are reflected in the names and types of form fields. The same database attributes are also used in the table showing scheduled interviews, guaranteeing uniformity in data presentation.

After developing the prototype input screen, only minor adjustments were needed to the existing ER diagram and relational architecture. It turned out that the initial plan, which included the 'interviews' table with the required fields, was comprehensive enough to hold the information gathered via the user interface. The relational model's third normal form ensured reliable and efficient data storage even after its initial implementation.

According to Kroenke and Auer (2015), aligning the database design with user interface requirements is essential for effective system development. An effective starting database design is highlighted here. Hoffer, Prescott, and Topi (2013) stress that effective database design is crucial for creating an efficient and reliable system. An effective user interface can be designed with minimal effort and iteration by first conducting a thorough analysis of the data requirements, then building an ER diagram and relational model that are in line with best practices.

References

Connolly, T., & Begg, C. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.

Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts. McGraw-Hill Education.

Coronel, C., Morris, S., & Rob, P. (2012). Database Systems: Design, Implementation, and Management. Cengage Learning.

Kroenke, D. M., & Auer, D. J. (2015). Database Concepts. Pearson.

Hoffer, J. A., Prescott, M. B., & Topi, H. (2013). Modern Database Management. Pearson.

Related Topic:- Sustainability