[Solved] CS4513 INDIVIDUAL PROJECT: A DATABASE SYSTEM FOR FUTURE, INC.

1. DESCRIPTION

FUTURE, Inc. has three different types of employees: technical staff, quality controller and worker. Each employee has a unique name, and address. A technical staff has an education record indicating the degrees he/she obtained (BS, MS, Ph.D) and technical position. For each quality controller, the company records the type of the product he/she will check. Each controller can check only one type of product. The company records the maximum number of products a worker can produce per day.

Workers are responsible for making the products. Quality controllers are responsible for testing the quality of the products. Technical staffs will fix the problems on products. The products will not be allowed to get out of the company without the certification of a quality controller. However, a defected product may get out due to an error made by a quality controller. Each product is produced, checked and repaired by one worker, technical staff, and quality controller, respectively.

Each product is assigned a unique product ID. The following information about a product should also be known:

• Date the product is produced;
• Time spent to make the product;
• The person who produced the product;
• The person who tested the product and the person who repaired the product if the product has been repaired.

There are three different types of products: product1, product2, product3. For product1, the size (small, medium, large) of the product and name of the major software used will be recorded. For product2, the size and color of the product will be recorded. For product3, the size and weight of the product will be recorded. If a product1 has any problem, only a technical staff who has graduate education can repair it. For the other products, any technical staff can repair it.

An account is maintained by the company to keep track of cost for each product. For each account, the database stores its unique account number and the date the account established, and the cost for the product. Three types of accounts are maintained:

• product1-account to record cost for product 1;
• product2-account to record cost for product 2;
• product3-account to record cost for product 3.

A customer has a unique name, and an address. A customer can purchase one or more products. If a product purchased by a customer is defected due to an error made by a quality controller, the customer will make a formal complaint to the company with the following information:

• Date of the complaint;
• Detailed description of the complaint;
• Treatment expected (get money back or exchange for another product).

Each complaint is identified uniquely by its own id.

A product can be repaired by a technical staff either because it got a complaint or because the repair was requested by a quality controller. The date of repair will be recorded.

When workers produce products or technical staffs repair products, there may be an accident. A unique accident number, accident date and number of work days lost due to the accident will be recorded for each accident.

ON-LINE QUERIES AND THEIR FREQUENCIES FOR THE DATABASE SYSTEM

• Enter a new employee (2/month).
• Enter a new product associated with the person who made the product, repaired the product if it is repaired, or checked the product (400/day).
• Enter a customer associated with some products (50/day).
• Create a new account associated with a product (40/day).
• Enter a complaint associated with a customer and product (30/day).
• Enter an accident associated with appropriate employee and product (1/week).
• Retrieve the date produced and time spent to produce a particular product (100/day).
• Retrieve all products made by a particular worker (2000/day).
• Retrieve the total number of errors a particular quality controller made. This is the total number of products certified by this controller and got some complaints (400/day).
• Retrieve the total costs of the products in the product3 category which were repaired at the request of a particular quality controller (40/day).
• Retrieve all customers who purchased all products of a particular color (5/month).
• Retrieve the total number of work days lost due to accidents in repairing the products which got complaints (1/month).
• Retrieve all customers who are also workers (10/month).
• Retrieve all the customers who have purchased the products made or certified or repaired by themselves (5/day).
• Retrieve the average cost of all products made in a particular year (5/day).
• Switch the position between a technical staff and a quality controller (1/ 3 months). 17) Delete all accidents whose dates are in some range (1/day).

1. TASKS TO BE PERFORMED

Task 1. Design an ER diagram and a relational database to represent the database for FUTURE, Inc. defined in Section I.

Task 2. Provide a Data Element Dictionary that lists the names, types, and sizes (in bytes) of all attributes and associated constraints for each table.

Task 3.

3.1. Discuss the choices of appropriate storage structures for each relational table assuming that all types of storage structures discussed in class are available.

3.2. Discuss the choices of storage structures for each relational table when implementing it in Azure SQL Database (if different from the previous choices specified in 3.1).

Task 4. Construct SQL statements to create tables and implement them on Azure SQL Database. All Create statements must include appropriate constraints as defined in Task 2. For each table, you must include SQL statements that create the same storage structure as the one you selected for Azure SQL Database implementation in Task 3.2 (e.g., if you have decided that a table X must have an index on attribute Y, then you must include an SQL statement to create an index on attribute Y for table X).

Task 5. Write SQL statements for all queries (1-17) defined in Section I. Write a Java application program that uses JDBC and Azure SQL Database to implement all SQL queries (options 1-17), two additional queries for import and export (options 18-19), and the Quit option (option 20) as specified in the menu given below. The program will stop execution only when the user chooses the Quit option; otherwise all options must be available for the user to choose at all times. Remember to observe good user-interface design principles when designing your menu. Your program must be commented properly.

WELCOME TO THE DATABASE SYSTEM OF FUTURE, INC.

• Description of query 1;
• Description of query 2;

.

.

.

• Description of query 17;
• Import: enter new customers from a data file until the file is empty (the user must be asked to enter the input file name);
• Export: Retrieve all customers (in name order) and output them to a data file instead of screen (the user must be asked to enter the output file name); (20) Quit.

Task 6. Run the program created for Tasks 5 to test its correctness. To populate the database, perform 10 queries for each type (1-4) and 3 for each type (5-6). To show database access is possible, perform 3 queries for each type (7-11), and 1 query of each type (12-17). To show the import and export facilities are available, run each option (18-19) once. To show the Quit option is available, run option (20) at least once. To demonstrate that Azure SQL Database can detect errors, perform 3 queries of different types that contain some errors.

Important Notes for the Java + JDBC + Azure SQL Database (Tasks 5 and 6):

Data manipulation and error checking must be done by Azure SQL Database. Your program is only to create the menu, accept choices, form queries, submit them to Azure SQL Database for execution, and display results or error messages.

III. SUBMISSION

1. The project must be typed (diagrams must be computer-generated).
2. The project must be paginated.
3. The project must include a cover page, which contains the following information: course name and number, semester and year, instructors name, authors name, id, and email address, and title of the project.
4. The project must include a table of contents using the format specified in Section IV.
5. Appropriate section headings must be included to show your solutions for the corresponding tasks/subtasks.
6. In-line comments must be provided for the Java programs.
7. Submit one ZIP file (with the file name as IP_your last name_your first name) that contains three files: 1) one single PDF file containing the entire project report with the contents as specified in Section IV (with the file name as IP_REPORT_your last name_your first name); 2) one SQL file (extension .sql) containing the SQL statements to create tables as explained in Task 4 in Section II (with the file name as IP_SQL_your last name_your first name); and 3) one Java file (extension .java) containing the Java source program (with the file name as IP_JAVA_your last name_your first name).
8. The project is due on the class website at 1:30 PM Monday, November 19, 2018. Late projects will be accepted until 11:59 PM Tuesday, November 20, 2018 with 5% penalty.
9. The overall project is worth 250 points (25% of your course grade); the late submission penalty is 12.5 points.

1. REQUIRED FORMAT FOR THE TABLE OF CONTENTS

For each task, indicate the pages in which it appears. For each subtask, indicate its start and end page numbers. Below is an example of the table of contents.

Tasks Performed Page Number

Task 1. 1-10

• ER Diagram 1-1
• Relational Database Schema 2-10

Task 2. Data Dictionary 11-11

Task 3. 12-20

• Discussion of storage structures for tables 12-14
• Discussion of storage structures for tables (Aruze SQL Database) 15-20

Task 4. SQL and text files showing the creation of tables 21-50

in Aruze SQL Database

Task 5. Screenshots showing the entire Java program 51-60 and its successful compilation

Task 6. Java program Execution 61-100

• Screenshots showing the testing of query 1 61-63
• Screenshots showing the testing of query 2 63-65

.

.

.

6.17. Screenshots showing the testing of query 17 90-91 6.18. Screenshots showing the testing of the import and 92-94 export options

• Screenshots showing the testing of three types of errors 95-98
• Screenshots showing the testing of the quit option 99-100