LAB #2 –Java Methods Instructions
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 3
Java Programming COMP-228
Lab #4 Page 1 of 3
Lab Assignment #4 – Developing a Java Application with JDBC capabilities
Due Date: 23-Nov-2018 4:00pm.
Purpose: The purpose of this Lab assignment is to:
• Practice the use of JDBC components.
• Practice the database manipulation through a Java application.
References: Read the Lecture Notes 10 and the textbook.
- This material provides the necessary information you need to complete the exercises.
Be sure to read the following general instructions carefully:
This lab should be completed individually by all the students. You will have to
demonstrate your solution in a scheduled lab session and submitting the project through
the dropbox link on D2L.
You must name your Eclipse project according to the following rule:
YourFullName_COMP228Labnumber
Example: JohSmith_COMP228LabAssign4
Apply the naming conventions for variables, methods, classes, and packages:
- variable names start with a lowercase character
- classes start with an uppercase character
- packages use only lowercase characters
- methods start with a lowercase character.
Instructions and Requirements:
Centennial College bookstore maintains a database to store the “books” and “book sales”
information. This database is manipulated with a Java app and each book sale is recorded. All the
information is stored in a database named “CENTENNIAL_BOOKSTORE_DB” with the
following tables with given columns. (You can add any missing columns in both tables)
BOOKS (BOOKCODE, TITLE, AUTHOR, PRICE, TYPE, SUBJECT)
SALES (BOOKCODE, SALEDATE, QUANTITY, PRICE)
Task 1: SQL Script (20 marks)
1. Create a BookStore.sql file. (All SQL scripts go in this file). 1 mark
2. Create a new database called CENTENNIAL_BOOKSTORE_DB. 1 mark
3. To ensure the data integrity set appropriate constraints (like primary key, null etc.)
Note: all primary keys are not null and auto incrementing 2 marks
Java Programming COMP-228
Lab #4 Page 2 of 3
4. Create BOOKS table with following fields(columns): 4 marks
BOOKCODE : INT,PRIMARY KEY
TITLE: VARCHAR(50) , NOT NULL
AUTHOR: VARCHAR(25), NOT NULL
PRICE: DECIMAL(7,2)
TYPE: VARCHAR(25)
SUBJECT: VARCHAR(25)
5. Create SALES table with following fields: 4 marks
BOOKCODE: INT, FOREIGN KEY,
SALESDATE: DATE, NOT NULL
QUANTITY: INT, NOT NULL
PRICE: DECIMAL(7,2)
6. Insert the following records into BOOKS table: 3 marks
001, Servlets and JSP, Murach,40.75, Technology, Software Engineering
002, Learning Android 2, Marco, 56.97, Technology, Internet
003, Under the Sea, Johnson, 43.00, Science, Marine life
7. Insert the following records into SALES table: 3 marks
001, 23-Sep-2018, 5, 40.75
001, 14-Oct-2018, 10, 40.75
002, 20-Oct-2018, 15, 56.97
8. Every tables (BOOKS and SALES) should have at least 5 rows including above given
rows / records. 2 marks
Task 2: Java Applications (30 marks)
You are asked to design a Java application (Console based) that uses JDBC to query and update
the database. Your application should be able to do the following tasks / functions:
1. Create a simple menu with the following options: 5 marks
Option selection: Books / Sales
Add Record: this will accept an input based on the option selection (example if you
chose Books option, you have to get input to add a book row / record)
View Record: will show all the records from books or sales table base on the options.
Update Record: this will update the one or more rows for Books or Sales based on the
option selected.
Delete Record: this will delete the one or more rows for Books or Sales based on the
option selected.
Search: this will find a book based on the author or title, sales based on sales date.
Quit: this will exit the program.
Java Programming COMP-228
Lab #4 Page 3 of 3
2. Define proper definition with any required private members to implement a connect()
method in the your program to establish a connection to a database. It takes 3 parameters:
connect(String url, String user, String pass) 2 marks
3. List / Display all Books and Sales tables information. 4 marks
4. Add, update and delete Books and Sales tables information. When a Book code is
deleted, their sales information must be deleted as well. 12 marks
5. Find the books or stores details based on the search criteria: 3 marks
For Books- Search Criteria Title or Author
For Sales - Search Criteria Sales Date
6. Calculate the total amount sold for a given SALESDATE. 2 marks
add this option into your menu.
7. User friendliness and code standards (include your student name and number in each
files) and white spaces and exception handling. 2 marks
Submission
Submit your assignment in a zip file that is named according to the following rule:
YourLastName_COMP228Labnumber.zip
Example: JohSmith_COMP228LabAssign4.zip
Academic honesty (Plagiarism and cheating)
All students must follow the academic honesty policies regarding Plagiarism and cheating on
assignments, Quizzes or Tests. Centennial college's Academic Policy will be strictly enforced.
To support academic honesty at Centennial College, all academic work submitted by students
may be reviewed for authenticity and originality, with utilizing software tools.
For more details, please visit the Academic Honesty site on
https://www.centennialcollege.ca/mycentennial/your-support/academic-support/student-
academic-advising/academic-honesty/
