Description
A simple CLI application written in Java for an ordering system for a mock bookstore such that all information on books, customers, and purchase information are stored and accessible easily.
Technologies
Java
Oracle
Database
Project Overview
Figure 1: Booking Ordering CLI Cover Page
This book ordering system was done as part of Chinese University of Hong Kong (CUHK) CSCI3170 Database System Project. The purpose of the project was to provide good practice in designing database systems as well as successfully implement semi-complex SQL queries.
I worked on the database design and led the implementation for most of the features in this project.
View project Video
Problem Definition
The project required designing a robust relational database system and implementing a Java-based command-line application connected to a MySQL database via JDBC.
Key features included real-time book queries, order management, customer tracking, and administrative interfaces for data maintenance.
This system effectively streamlined customer interactions and internal operations while ensuring error handling and data integrity.
Key Deliverables included:
1. Designing an Entity-Relationship (ER) diagram and optimised relational schemas to eliminate redundancy.
2. Building a Java application that automated table creation, data insertion, and dynamic querying functionalities.
3. Detailed book search, order management, and shipping status updates through command line interfaces.
Database Design
Entity Relation Diagram
We designed an ER diagram as shown below that correlates to the requirements of the project.
Figure 2: Entity Relation Diagram
This ER diagram allows us to identify the entities of our system as represented by the rectangles and the circles represents their attributes. Constraints such as primary key, unique key, weak and strong entities and relationships are also noted.
Relational Schema
Figure 3: Relational Schema
We also created the relational schema which also helps to define the structure and organization of data in a relational database.
A way it was used was to more easily conduct normalization to eliminate redundancy and ensures data integrity using Boyce-Codd Normal Form (BCNF).
Implementation
Code Design
In order to ensure that the project is maintainable, I devised the following code structure as described below.
Interfaces
Interfaces were used as much as possible to reduce redundant code and ensure consistency between similar components. One example are the menus where they are all required to be able to choose options and print their displays.
Figure 4: Menu Interfaces
Controller and Service Layer
While not named as such, the file "Database.java" is the controller of the application. It is responsible for handling incoming requests from the Menus and returning the appropriate responses. It acts as an intermediary between the user interface and the service layer
Figure 5: Database.java representing the controller layer
The operations methods as can be seen in the controller layer are therefore synonymous with the service layer. The service layer encapsulates the core business logic, and processes and communicates with the repository layer (Entity model) to update, read and write to the database.
Entity Model
An entity model is a representation of the database tables, with each columns defining their attributes and relationships. Operations such as INSERT and UPDATE are also written here. An example is shown below:
Figure 6: Order Entity Model
SQL Queries
Many SQL prepared statements were written for this application. An example can be seen below:
Figure 7: N most popular query prepared statements
The above is a good example of a slightly more complex query. It seeks to allow the user to find the most popular books based on the quantity of orders for each book.
The first prepared statement query counts the total number of distinct books that have been ordered. This is meant to determine the possible range for user input.
The second query then retrieves the most popular books, ordered by the quantity ordered, limited to the number the user specifies (using the rank).
Conclusion
Through this project, I significantly enhanced my understanding of controllers, service layers, and repository layers. I also refined my skills in database design and deepened my knowledge of SQL, including the use of prepared statements, database triggers, and a basic understanding of stored procedures. Most importantly, I gained valuable experience in managing database transactions.