CSc460 Program #4: Database Design and Implementation Solution

Designed by Sourav Mangla and Justin Do
Overview: In this assignment, you will build a database–driven web information management system from ground up. We will give you an application domain to work on, your goal is to design the underlying database and define the application functionalities you will provide with the database, and implement this application using Oracle within a text–based JDBC program.
Assignment: In this assignment you are to implement a two–tier client–server architecture.
2. JDBC Front–End, which is the client’s user interface. You need to design a text–based application that appropriately handles all the required functionalities. Your client application will run on lectura.
Application Domain: The problem description for the project is as follows:
When people have basic licensing and identification needs, such as driver’s licenses, their local Department of Motor Vehicles (DMV) office is the place to go. Your task is to design a database and associated manipulation/querying application for a DMV.
The DMV provides multiple services like Driver License, State Id, Vehicle Registration, Permit, etc. To get a service, you need to book an appointment with the DMV. Appointments can be multiple types, as a new license or renewing the expired license. Users can have multiple appointments. Appointments for a single person cannot overlap in time.
Of course, the DMV has employees to handle patron’s needs. Appointments are assumed to always lead to some sort of transaction in which the employee that handles the transaction will be associated with it. Every transaction has logs that hold the service amount paid by the user. For logs, we are only focusing on the service fee. Charges for the available services are as follows:
1. Permit: 7
2. Licence: 25
3. Vehicle Registration: 100
4. State ID: 12
Each employee belongs to one department, each service is handled by a separate department; thus, there are four departments. Each employee has a designation or job type such as supervisor, front desk, security, etc. A job has a salary, job title, job id, etc. Every employee has basic details, such as name, department, etc.
This description does not describe every detail. These are the essentials; we expect that your team will create logical and conceptual designs that incorporate all of these features, at minimum. You are free to add additional details that you feel are appropriate.
We realize that you are not an expert in this domain, but you have dealt with similar organizations in your life. Hopefully, you have enough experience that this problem description makes sense. If you have questions, please ask, and the TAs will help you clear things up.
Required functionalities: Within the framework provided above, your system is expected to perform examples of the following operations:
1. Record insertion: Your application should support inserting a new data record via a JDBC interface.
2. Record deletion: Your application should support deleting an existing data record via a JDBC interface.
3. Record update: Your application should support updating an existing data record via a JDBC interface.
4. Queries: Your application should support querying your database via a JDBC interface for the problem description given above. You are required to implement the three provided queries as well as at least one query of your own design. Details are provided below.
Specifically, the JDBC application’s interface should enable users to:
1. Add, update or delete a client, employee, appointment, and service. When adding, updating, the useris allowed to update everything except the ID. When deleting, the entire row needs to be deleted.While inserting or updating an appointment, appointment time must not overlap with any existing appointment.
Here are the queries that your application is to be able to answer:
3. Write a query that displays the collected fee amount for every department for a given month in the formatMM/YYYY (given by the user). The result should display the amount and department information and sort the result on amount in descending order.
4. One additional non–trivial query of your own design, with these restrictions: The question must use more than two relations and must be constructed using at least one piece of information gathered from the user.
Early on, you will need to agree on a reasonable workload distribution plan for your team, with well–defined responsibilities, deliverables, and expected completion dates. Such a plan will minimize conflicts and debugging effort in the actual implementation.


the start of finals.)
For example, a team whose three members have 1, 1, and 3 late days remaining have use, if needed.

(a) The source code for your application.
(b) A PDF file called “design.pdf” containing the following sections in this order:
i. Conceptual database design: Your final E–R diagram along with your design rationale and any necessary high–level text description of the data model (e.g., constraints, or anything you were not able to show in the E–R diagram but that is necessary to help people understand your database design). ii. Logical database design: The conversion of your E–R schema into a relational database schema. Provide the schemas of the tables resulting from this step.
iii. Normalization analysis: For each of your entity sets (tables), provide all of the FDs of the table and justify why your the table adheres to 3NF / BCNF.
iv. Query description: Describe your self–designed query. Specifically, what question is it answering, and what is the utility of including such a query in the system?
(c) A ReadMe.txt describing:
i. Compilation and execution instructions, to enable the TAs to execute your application andexercise the required functionalities.
ii. The workload distribution among team members (that is, which people were responsible forwhich parts of the project).

Grading Criteria: Total: 100 points
(a) Coding / Implementation: 55
Documentation 15
Style and organization 10
Record insertion: 5
Record deletion: 5
Record update: 10
Record query: 10
(b) Database design: 20
Final E–R diagram: 10
Normalization analysis: 10
Grading Notes:
1. Unless we receive verifiable complaints about inadequate contributions, each member of a team will receive the same score on this assignment.
2. We won’t put much weight at all on the appearance of the text application; concern yourselves with the application’s functionality instead. The main point of the assignment is the DB design.