CSCI3170 Introduction to Database Systems
Introduction to Database Systems
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
CSCI3170
Introduction to Database Systems
Project – Sales System
1 Introduction
You are required to implement a sales system for a computer part store so that all information about
transactions, computer parts and salespersons is stored. The system has to support interactive inquiries
from users. You are required to use Java JDBC API to access the database and implement a Java
application to satisfy all system functions defined in this specification.
There are two phases. In phase 1, you are required to design the database for the sales system
(including an ER-diagram and a relational schema which doesn't contain redundant fields and tables).
After the deadline of Phase 1, a suggested solution will be provided. You are required to use the
suggested solution as a guideline to complete Phase 2. In Phase 2, you are required to implement the
sales system as a Java command-line program. Our tutors will give tutorials on how to connect to MySQL
database system through Java JDBC API and deploy your work on the required platform.
This is a group project and each group consists of three members. Each group is required to submit ONLY
one solution for each project phase. Please sign the group registration form on the course homepage
before the group registration deadline.
2 Milestones
Preparation
§ Read the document thoroughly and make sure you understand all the assumptions and regulations
stated in Section 4.
Phase 1
§ According to the data specifications in Section 3, design an ER-diagram and transform it into a
relational schema which doesn't contain redundant fields and tables.
Phase 2
§ According to the suggested solution of Phase 1, implement a Java application that fulfills all
requirements stated in Section 5.
§ Debug your system with different datasets and user inputs.
§ Write a readme file to describe the compilation and deployment of your system.
3 Data Specifications
All data files for the system are in UNIX text file format (i.e. Newline character is \n) encoded in ASCII.
Your Java application is required to read records stored in the files and insert them into appropriate
tables in the provided MySQL DBMS via JDBC API. There are five input files, a list of categories, a list of
manufacturers, a list of parts, a list of salesperson and a list of transaction records.
CSCI3170 Project (Fall 2022) Sales System
3.1 Category
Each computer part belongs to a category. In this system, there are several categories and each of them
has its own name.
Item Name Format Description
Category ID Non-empty positive integer
with exactly 1 digit.
A unique identifier for a category.
Category Name Non-empty string with at
most 20 characters.
The name of a category.
3.2 Manufacturer
Each computer part is produced by a manufacturer. Each manufacturer has its name, address and phone
number.
Item Name Format Description
Manufacturer ID Non-empty positive integer
with at most 2 digits.
A unique identifier for a manufacturer.
Manufacturer Name Non-empty string with at
most 20 characters.
The name of a manufacturer.
Manufacturer
Address
Non-empty string with at
most 50 characters.
The address of a manufacturer.
Manufacturer
Phone Number
Non-empty positive integer
with exactly 8 digits.
The phone number of a manufacturer.
3.3 Part
Each computer part has its name, manufacturer, category and available quantity.
Item Name Format Description
Part ID Non-empty positive integer
with at most 3 digits.
A unique identifier for a part.
Part Name Non-empty string with at
most 20 characters.
The name of a part.
Part Price Non-empty positive integer
with at most 5 digits.
The price of a part.
Part Manufacturer
ID
Non-empty positive integer
with at most 2 digits.
The manufacturer ID of a part.
Part Category ID Non-empty positive integer
with exactly 1 digit.
The category ID of a part.
Part Warranty Non-empty positive integer
with exactly at most 2 digits.
The length of the warranty period of a
product in terms of weeks.
Part Available
Quantity
Non-empty non-negative
integer with at most 2 digits.
The quantity of a part available for sale.
CSCI3170 Project (Fall 2022) Sales System
3.4 Salesperson
Salespersons are responsible for selling computer parts in a store. Each salesperson has his/her name,
address and phone number.
Item Name Format Description
Salesperson ID Non-empty positive integer
with at most 2 digits.
A unique identifier for a salesperson.
Salesperson Name Non-empty string with at
most 20 characters.
The name of a salesperson.
Salesperson
Address
Non-empty string with at
most 50 characters.
The address of a salesperson.
Salesperson Phone
Number
Non-empty positive integer
with exactly 8 digits.
The phone number of a salesperson.
Salesperson
Experience
Non-empty positive integer
with exactly 1 digits.
The experience of salesperson in terms of
years
3.5 Transaction Records
There is a record for each transaction performed in the sales system. It records the part sold,
salesperson involved and the date of the transaction.
Item Name Format Description
Transaction ID Non-empty positive integer
with at most 4 digits.
The ID of a transaction record.
Part ID Non-empty positive integer
with at most 3 digits.
The ID of the part sold.
Salesperson ID Non-empty positive integer
with at most 2 digits.
The ID of the salesperson.
Transaction Date Non-empty date in the
format of DD/MM/YYYY.
The date of the transaction.
4 Assumptions and Regulations
4.1 System
§ All numerical values will not be larger than the maximum integer value that can be handled by Java.
§ The system is case sensitive.
§ Every date has the following format: [DD]/[MM]/[YYYY] and has the same time zone as Hong Kong
(GMT+8). (Note: Y=year, M=month, D=day)
§ There is no duplicate row in any input and output.
§ There is no empty row in any input and output.
§ The current date is the system date of the MySQL DBMS server.
§ Your Java program may assume that any value entered into any input field is correct in format only.
§ Your Java program may assume that any data file inputted into it is correct in format and content.
4.2 Category
§ The ID and the name of a category are both unique.
§ All categories are identified by their ID.
4.3 Manufacturer
§ The ID of a manufacturer is unique and all manufacturers are identified by their ID.
§ Some manufacturers may share the same name, phone number or address.
CSCI3170 Project (Fall 2022) Sales System
4.4 Part
§ The ID of a part is unique and all parts are identified by their ID.
§ Some parts may have the same name, price, available quantity, warranty, manufacturer ID or
category ID.
4.5 Salesperson
§ The ID of a salesperson is unique and all salespersons can be identified by their ID only.
§ Some salespersons may have the same name, address, phone number or years of experience.
4.6 Transaction Records
§ The ID of a transaction is unique and all transactions can be identified by their ID only.
§ Each transaction corresponds to the sale of one part.
§ Some salesperson may never sell any part and some parts may have never been sold.
§ A part can be sold only if it is still available (i.e. available quantity > 0).
§ A salesperson can sell the same part more than once by having more than one transaction.
§ There may be more than one transaction in one day.
§ After a salesperson sells a part, the system should reduce the available quantity of that part by one
and add a transaction record accordingly.
5 Function Requirements
You are required to write a simple command line application in Java. After performing a function
specified in any of the following sub-sections, the program should go back to the topmost level of
menu. Any error or informative message of the Java program should be displayed in a new line. The
Java program consists of the following functions:
5.1 Administrator
The functions that can be used by an administrator are:
§ Create table schemas in the database: This function creates all tables for the sales system in the
MySQL DBMS based on the relational schema given.
Figure 1: Expected interactive input and output while creating table schemas in MySQL DBMS.
CSCI3170 Project (Fall 2022) Sales System
§ Delete table schemas in the database: This function deletes all existing tables of the sales system
from MySQL DBMS.
Figure 2: Expected interactive input and output while deleting table schemas from MySQL DBMS.
§ Load data from a dataset: This function reads all data files from a user-specified folder and inserts
the records into the appropriate table in the database. (Your program can assume that the user-
specified folder must contain all five input files. These five input files are named category.txt,
manufacturer.txt, part.txt, salesperson.txt and transaction.txt. Each data file stores the data
corresponds to its filename.)
Figure 3: Expected interactive input and output while loading data from data files to the table
schemas in MySQL DBMS.
CSCI3170 Project (Fall 2022) Sales System
§ Show the content of a specified table: This function shows the content of a user-specified table.
Figure 4: Expected interactive input and output while showing content of category table.