Artifact 3: Database

For this final artifact in this portfolio, the original project was an excersice on utilizing data structures to build objects and manipulate them with a service class, then running automated tests to ensure proper storage. It was originally a very simple project that contained three objects classes and three service classes. The objects were created and saved into a list through the service class. The course was heavily focused on automated testing, object building and object manipulation. Since the original artifact did not use a database, the data was not persistent. To improve upon the application, I chose to implement a database.

View Project on GitHub

1. Briefly describe the artifact. What is it? When was it created?

For the database category I decided to create a database to integrate with my category one artifact, the Scheduler app. So far, I have created the database, and I have begun finishing the implementation into the Scheduler app. There was no original database for this application, instead the data was stored in array lists and was not saved anywhere. This resulted in no persistent data so to provide both persistence and scalability of an appointment management application I created a database for the various types of data that needed stored.

Ultimately however, I changed the database being used for the Scheduler application from MySQL to SQLite, as this would more appropriately fit the audience of that application. The rest of this description is for a database that was never used, but could be used if the Scheduler application was to be altered for a larger scope.

2. Justify the inclusion of the artifact in your ePortfolio.

The reason I selected this artifact for my ePortfolio is it displays my ability to enhance an application through combining previously established software with more efficient and effective storage of data. By designing and implementing a MySQL database schema and combining it with Java to make a complete application, I am demonstrating my skills and abilities with both the SQL language, database design and Java programming.

3. Did you meet the course outcomes?

In module one, the category three enhancement was meant to meet the following course outcome:

• Develop a security mindset that anticipates adversarial exploits in software architecture and designs to expose potential vulnerabilities, mitigate design flaws, and ensure privacy and enhanced security of data and resources.

To accomplish this course outcome, I am implementing password encryption, input validation and protections against SQL injection. I have different access levels to the database, based on the type of employee to only allow authorized users and administrators to interact with the database. By validating the values prior to storage the design will help prevent erroneous data from being stored in the database. This project is still a work in progress since it is also going to be used for Category 3 of this course. It is on track to meet the enhancements that I had planned at the beginning of the course. All of the GUI components have been finished and when I create the database for the application, I will finish the remaining components to present the appointment, customer accounts, employee accounts and tasks to the user.

4. Reflect on the process of enhancing and modifying the artifact.

Below is the implemented schema and the original database schema from module 1. I changed the customer table to remove the username and password since the scheduling application will only be for the workers to use. It is not needed for the customer. There were no challenges in making the database, but it is taking longer than I originally thought incorporating it into the overall application. This is the first time I have used Java Swing and JDBC to create an application. I was originally planning on managing all connections to the database through a database Helper class, but as I have been implementing the database connections, I have changed the connections to the individual service classes (Employee, Appointment, Tasks and Customer) with the database helper class managing the creation of the tables. This allows for better adherence to the Single Responsibility Principle, better scalability and better maintainability.

View Project on GitHub

The scheduler program uses a Model View Controller (MVC) architecture which consists of object Models for each object type, and the tables of the database reflect those data types:

• Appointments table: Holds the appointment information
• appointment_id: This acts as the unique id for each appointment. It is the primary key for the table and is unique due to the auto_increment parameter.
• employee_id: A foreign key used to associate an employee assigned to appointment.
• customer_id: Another foreign key used to associate a customer with the appointment.
• date_time: A dateTime feild type for storing the appointment time.
• appointment_desc: Holds appointment details.
• appointment_title: A short description for quick reference.
• Tasks table: Holds the predefined tasks, allowing for assignment to appointments
• task_id: Unique id Primary key that is used to assign tasks to appointments.
• task_description: A description of the task.
• task_status: Provides a way to track the status of the task; Complete, In Progress or Not Started.
• Employees table: Holds the information pertinent to the employee's. This includes the login in creditials and their supervisory status. The supervisory status could be used for access control in the future.
• employee_id: Unique id primary key that auto_increments and is used assign employee's to appointments.
• username: Unique username used for logging employee into the system.
• hashed_password: Passwords are hashed using SHA-256 hashing prior to being stored in database.
• first_name: Employee's first name.
• last_name: Employee's last name.
• email: Store employee's email.
• position: To destinquish between management and workers, for access control later if desire.
• Customers table: Holds the information pertinent to the customer, allowing for assignment to a new appointment.
• customer_id: Unique auto_incremented primary key that is used to assign customers to appointments.
• first_name: Customers first name.
• last_name: Customers last name.
• phone_number: Customers phone number.
• address: Customers address, could later be used for billing purposes if desired.
• AppointmentTasks table: Acts as a bridge between the appointments and tasks table, used for assigning the appointment with tasks.
• appointment_id: Foreign key used to link appointments to tasks.
• task_id: Foreign key used to link tasks to appointments.

Each of these tables has a service class that handles interaction with the database. They are responsible for the Create, Read, Update and Delete (CRUD) functionality:

• Appointment Service: Used to manage the data in the appointments table. Also used for assigning tasks to appointments.
• Task Service: Used to manage the individual tasks.
• Employee Service: Used to manage retrieval of employee data, including login creditials.
• Customer Service: Used to manage retrieval of customer data.
• DatabaseHelper - For creating the tables upon initial use.
• Login Service: Interacts with the employee service and employees table to validate user information.

The database schema. Below is the layout of the database: