Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
CSCI3170 Sales System
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.