EcoRide – Electric Bike Rental Database System

• Task 1 deals with technical implementation: entities, ERD, normalization, SQL and queries [1].
• Task 2 includes analysis of design options, implementation and testing with reference to specifications.

Scenario Overview & Requirements Analysis 

EcoRide is an urban electric bike rental business that is based on short-term rent by using a designated station. Due to the increased demands, the existing manual and spreadsheet-based system will prove inefficient, resulting in inconsistencies in data and operational obstacles [2]. A centralized database is needed in order to handle rising amounts of riders, bikes, rentals, and maintenance logs.

The major users of the system are riders, operational and maintenance staff. Rider registration, real-time tracking of the bike availability, a kiosk of rentals, and maintenance history are examples of functional requirements. Non-functional requirements lay a stress on the data integrity by the use of the primary and foreign key constrain, accuracy to generate reliable reports, and those that can scale with the growth of the business without the need to restructure them. The database should not only meet the requirements of various users of data but must also make sure that its operations are efficient, secure, and scalable [3].

A clear relational database assignment example for students

Database Design Methodology 

The EcoRide database was created with the help of the organized design approach, guaranteeing that its use fits the business requirements and is based on the principles described in lectures. It started with requirements analysis to find out the key data to be stored that included rider information, bike records, rentals, maintenance records and staff activities, which will be used in running the daily operations and in reporting [4]. This was then followed by entity identification where core entities were identified (Rider, Bike, Rental, Maintenance, Staff and Station) and lookup tables (e.g. BikeType, BikeStatus) to give values standardization and to reduce redundancy. The next step was the definition of attributes, where the relevant data types were considered to be accurate and valid. Foreign keys were used to establish relationships between entities and mainly establish one-to-many relations, like between Rider and Rental. Lastly, the design was reduced to 3NF to remove redundancy and dependency problems and visualized using an Entity Relationship (ER) diagram to make it easy to implement.

DEADLINE TOO
CLOSE?

ORDER NOW

Get Assignments
Delivered in 3 Hours

Conceptual Design - Entity Relationship Diagram 

The analysis design of the EcoRide database is represented through the Entity Relationship Diagram (ERD) presented in Figure 1. It is an illustration of the key components of the system, namely, Rider, Bike, Rental, Maintenance, Staff, and Station, their attributes, and their relationships. An example is the Rider entity that stores the user information, and the Bike that maintains information about single electric bicycles [5]. Station records physical rental locations, and Rental records all the hires, connecting the riders with bikes and recording time and the price. Maintenance records, service records, and personnel information are known to the Staff.

To bring normality and facilitate normalization, the lookup tables like Biketype, bike status, bike condition, maintenance type, and staff role were added. These tables substitute free-text entries with standard values, lessening duplication and simplifying data administration. There are clearly defined relationships and cardinalities with each other that are based on real-life interaction: there can be many rentals per rider (one to many), and many maintenance events per bike (one to many) [6]. Such associations are imposed on the final database implementation by means of the foreign key constraints.

Read more samples-Barriers to Implementing the Circular Economy: Challenges and Managerial Solutions

Logical Database Design & Table Structures 

The logical design step entailed how to convert the conceptual ERD to a well-organized series of relational tables that could be fulfilled using MySQL. In accordance with the convention in mapping, all the identified entities (Rider, Bike, Rental, Maintenance, Staff and Station) were transformed into separate tables. Every table had its own primary key (e.g., RiderID, BikeID), which was represented in the form of auto-incremented integers in order to make each record identifiable.

The association of entities was bound by the foreign key constraints [7]. An example is the Rental table that has foreign keys of both Rider and Bike so that every rental can be related to an existing rider and bike. Likewise, the Maintenance table is linked to the service records through the foreign keys; these keys link all three components of bikes, staff personnel, and maintenance type. These constraints ensure referential integrity, where orphaned records are prevented, and there is consistency in the entire database.

To enforce standardization and reduce redundancy, some lookup tables such as BikeType, BikeStatus, BikeCondition, Maintenance type and staff role were developed. These tables hold predefined categorical values and are associated with the core tables via foreign keys, representing a replacement of the free-text fields and guaranteeing the validity of the data.

MySQL was directly used as the logical schema by the use of proper data types and constraints that guarantee correspondence with the ERD [8]. This high fidelity of the conceptual design to physical implementation contributes to the high data management, the efficiency of querying, and the maintainability of the EcoRide database system.

Normalisation to Third Normal Form (3NF) 

Normalisation is that of arranging information in a database to minimise redundancy and enhance data integrity. The design was normalised in the EcoRide database to the Third Normal Form (3NF) using the concepts covered in lectures.

The First Normal Form (1NF) was obtained through ensuring that the attribute values are all atomic and the tables do not have repetitions of groups. The value of each field is different, and it cannot be broken down, like the name of a rider or the status of a bike.

The Second Normal Form (2NF) was met as all the non-key attributes are completely dependent on the whole primary key. No problem of partial dependency occurred since a single-attribute primary key was used in each table.

Transitive dependencies were eliminated to achieve 3NF. Look-up tables, which included Biketype, Bike status, Bike condition, Maintenance type, and staff role, among others, were moved out of the main key [9]. This helps get rid of the redundancy, and the information is stored only once.

Look-up tables also discourage update anomalies; any modification in a predefined value must be made in a single location. This enhances uniformity, ease of maintenance and stability of data management within the system.

Database Implementation (SQL) 

The EcoRide database was deployed on a MySQL relational database management system, which is very common and supports structured query language (SQL), which has high data integrity constraints. The process of implementing was an issue of translating logical database design into executable SQL statements, which create and maintain the database structure.

All the tables that were identified during the design stage were defined using SQL CREATE TABLE statements, both core tables and lookup tables. Each attribute was given an appropriate type of data in order to make it precise and easily stored. Auto-incrementing integer fields were used to come up with primary keys that uniquely identify records in each table [10].

Foreign keys and constraints were used to ensure relationships between tables and ensure referential integrity. As an illustration, the Rental table will refer to the Rider and Bike one, which guarantees that every record of a rental will be related to valid ones. On the same note, a lookup table is associated using foreign keys with attribute values being restricted to pre-defined ranges.

Once the tables were created, the SQL INSERT statements were used to insert sample data. This information is a real-world usage scenario and allows using the database functionality and performing meaningful queries, and testing.

Sample Data Population 

To make the EcoRide database look and feel more realistic, it was given sample data, which would be useful in testing and demonstrating the system functionality. The core tables were filled with several records of the riders, bikes, rental service, servicing activities, employees, and stations. There were also predefined values that were fed to look-up tables, like the type of bike, conditions, status, maintenance and staff roles [11].

The data values were selected to be realistic in terms of how they are used in the real world, e.g. active and completed rentals, bikes of various availability states, and maintenance logs of serviced bikes. Adding enough and realistic data into the database will facilitate meaningful running of queries, and hence, this makes the system provide informative reports on the availability of the bikes, their rental history, and past maintenance.

Query Design & Outputs

A sequence of SQL statements was developed to emphasize the way the EcoRide database facilitates operational decision-making. A single query can get bikes that are on hire, with the staff able to check the availability at any given time. One more query detects the bikes which need to be maintained by sorting records with the condition, which helps in planning the safety and service. A third query is the calculation of the most rented bikes, and this can help see how they are used, and the management can plan how to allocate the resources [12]. Lastly, a query of rider rental history shows the previous rental history of individual users, which can be utilized in either customer analysis or support. The result of these queries has meaningful outputs that help EcoRide employees perform performance monitoring and operations management effectively.

Validation & Testing 

The validation and testing were done to make sure the EcoRide database is capable of satisfying functional requirements and avoiding the entry of invalid data. Key attributes were validated using validation rules, including one that forces cost values to be greater than or equal to zero, as well as one that makes some of the fields mandatory to avoid missing key information. Referential integrity was also tested by trying to add records with foreign key references that were not valid, which were rightly rejected by the system [13]. Further test cases were used to verify that there can not exist any rental records without valid riders and bikes, and maintenance records need to be associated with the existing staff and bikes. The results of the above tests showed that the database satisfies the requirements of data consistency, accuracy, and reliability.

Reflection & Limitations 

The design and implementation of the database were mostly effective, especially in the application of normalisation principles and incorporating the rules of data integrity by means of relations and constraints. Look-up tables were effective in minimizing redundancy and enhancing uniformity. The problems encountered included making sure that the ERD represented all the business rules with a lot of clarity, including making it readable. Because of the simplification of the scenario, certain real-life elements, like the authentication of a user, dynamic pricing, and real-time tracking of a bike, were omitted. The potential developments may include updating the database to accommodate such features and combining the system with a web application or mobile application to make the system more usable [14].

Conclusion

This coursework aimed to make an implementation design of a relational database system for the EcoRide electric bike rental plan. The requirements analysis, design of the ERD, implementation of the database on SQL and system validation were achieved as objectives. The last solution illustrates the best database design concepts, such as normalisation, referential integrity, and codified querying. The strategy and execution are perfectly related to the concepts and methodologies studied during the weekly lectures and tutorials, which proves the definite grasp of relational database design and implementation.

Practical ERD and SQL based assignment help for any query.

Bibliography

• [1] Addy, P. L. Hang, C. Z. Hei, L. T. Hua, and N. U. Amin, “Design and Implementation of a Relational Database System for Efficient Facility and Booking Management,” Mar. 2025, doi: https://doi.org/10.20944/preprints202503.0070.v1.
• [2] I. Lemberger, “Optimising Inventory Management to Enhance Operational Efficiency : gaining advantages in manufacturing start-ups and SMEs,” Theseus.fi, 2025, doi: http://www.theseus.fi/handle/10024/901955.
• [3] D. A. Karunamurthy, M. Yuvaraj, J. Shahithya, and V. Thenmozhi, Cloud Database: Empowering Scalable and Flexible Data Management. Quing: International Journal of Innovative Research in Science and Engineering, 2023.
• [4] K. Afiifah, Z. F. Azzahra, and A. D. Anggoro, “Analisis Teknik Entity-Relationship Diagram dalam Perancangan Database Sebuah Literature Review,” INTECH, vol. 3, no. 1, pp. 8-11, Apr. 2022, doi: https://doi.org/10.54895/intech.v3i1.1261.
• [5] S. Pulungan, R. Febrianti, T. Lestari, N. Gurning, and N. Fitriana, “Analisis Teknik Entity-Relationship Diagram Dalam Perancangan Database,” vol. 02, no. 1, pp. 98-102, 2023, doi: https://doi.org/10.47233/jemb.v2i1.533.
• [6] J. Kenneman et al., “Development and evaluation of a Trustworthy and User-Friendly bicycle rental platform for students,” DIVA, 2025. https://www.diva-portal.org/smash/record.jsf?pid=diva2:1968864
• [7] M. Besta et al., “Demystifying Graph Databases: Analysis and Taxonomy of Data Organization, System Designs, and Graph Queries,” ACM Computing Surveys, vol. 56, no. 2, pp. 1-40, Sep. 2023, doi: https://doi.org/10.1145/3604932.
• [8] W. Miao, “The Practice of MySql in Realizing the Background Data Management of the Hybrid Teaching Management System,” Lecture notes on data engineering and communications technologies, pp. 586-592, Jan. 2022, doi: https://doi.org/10.1007/978-3-031-05484-6_74.
• [9] Z. Zhang and S. Link, “Synthesizing Third Normal Form Schemata that Minimize Integrity Maintenance and Update Overheads: Parameterizing 3NF by the Numbers of Minimal Keys and Functional Dependencies,” Proceedings of the ACM on Management of Data, vol. 3, no. 3, pp. 1-25, Jun. 2025, doi: https://doi.org/10.1145/3725362.
• [10] Z. Hong et al., “Next-Generation Database Interfaces: A Survey of LLM-based Text-to-SQL,” IEEE Transactions on Knowledge and Data Engineering, pp. 1-20, 2025, doi: https://doi.org/10.1109/tkde.2025.3609486.
• [11] S. Raifman, M. A. DeVost, J. C. Digitale, Y.-H. Chen, and M. D. Morris, “Respondent-Driven Sampling: a Sampling Method for Hard-to-Reach Populations and beyond,” Current Epidemiology Reports, vol. 9, no. 1, pp. 38-47, Mar. 2022, doi: https://doi.org/10.1007/s40471-022-00287-8.

UPTO55%

Avail The Benefit Today!

To View this & another 50000+ free

Enter Email

Submit