CSDSA 4513-Project Solved

I.     DESCRIPTION 
 

MyProducts, Inc. has three different types of employees:  technical staff, quality controller and worker.  Each employee has a unique name, an address, and a salary.  A technical staff has an education record indicating the degrees he/she obtained (BS, MS, PhD) 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, quality controller, and technical staff, 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 

 

1)    Enter a new employee (2/month).

2)    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).

3)    Enter a customer associated with some products (50/day).

4)    Create a new account associated with a product (40/day).

5)    Enter a complaint associated with a customer and product (30/day).

6)    Enter an accident associated with an appropriate employee and product (1/week).

7)    Retrieve the date produced and time spent to produce a particular product (100/day).

8)    Retrieve all products made by a particular worker (2000/day).

9)    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).  

10)  Retrieve the total costs of the products in the product3 category which were repaired at the request of a particular quality controller (40/day).

11)  Retrieve all customers (in name order) who purchased all products of a particular color (5/month).  

12)  Retrieve all employees whose number of years of experience is above a particular number of years of experience (1/month).

13)  Retrieve the total number of work days lost due to accidents in repairing the products which got complaints (1/month).  

14)  Retrieve the average cost of all products made in a particular year (5/day). 15) Delete all accidents whose dates are in some range (1/day).

 

 

II. TASKS TO BE PERFORMED 

 

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

 

Task 2. Draw a Schema Diagram that shows the dependency relationships of all the foreign keys in the relational database designed in Task 1.

 

Task 3.  

3.1. Discuss choices of appropriate storage structures for each relational table assuming that all types of storage structures discussed in class (Lecture Topic 4) are available. For each table, identify the queries (from the list of the given queries) that access the table, the type of each of those queries (insertion, deletion, random search, or range search), the search keys (if any) involved in each of those queries, the frequency of each of those queries, your choice of the file organization for the table, and your detailed justifications. Use the following format to fill out your answers:

 

Table Name
Query# and Type
Search Key
Query

Frequency
Selected File Organization
Justifications
 
 

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 Task 3.1).

Part of this task is for you to find and study the relevant documentation on your own.

 

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 Tasks 1 and 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-15) defined in Section I.  Write a Java application program that uses JDBC and Azure SQL Database to implement all SQL queries (options 1-15), two additional queries for import and export (options 16-17), and the “Quit” option (option 18) as specified in the menu given below. You are free to pick any file format you wish to use for file import and export options. 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.  Your program must be commented properly.

 

WELCOME TO THE DATABASE SYSTEM OF MyProducts, Inc. 

 

(1)   Description of query 1; 

(2)   Description of query 2; 

. 

. 

(15) Description of query 15; 

(16) Import: enter new employees from a data file until the file is empty (the user must be asked to enter the input file name); 

(17) Export: Retrieve all customers (in name order) who purchased all products of a particular color and output them to a data file instead of screen (the user must be asked to enter the output file name); (18) Quit. 

 

Task 6. Run the program created for Tasks 5 to test its correctness as follows:  

•       To populate the database, perform 10 queries for each type (1-4) and 3 queries for each type (5-6) and show the contents of the affected tables after the 10 queries of each type (1-4) are completed and after the 3 queries for each type (5-6) are completed.

•       To show database access is possible, perform 3 queries for each type (7-11), and 1 query for each type (12-15).   

•       To show the Import and Export facilities are available, run each option (16-17) once.   

•       To show the Quit option is available, run option (18) 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.   

 

Task 7. Write a Web database application using Azure SQL Database and JSP which provides the Web pages for query 1 and query 12. Since both queries take the input data from the user, there should be two Web pages for each query as follows: for query 1, one Web page to allow the user to enter the input data and one to display a message confirming the successful execution of the insertion; and for query 12, there should be one Web page to allow the user to enter the input data and one to display the retrieval results with appropriate headings. To show that your Web application works correctly, run the Web application so that queries 1 and 12 will be executed in this order: first query 12, then query 1, and then query 12 again, making sure that the results of query 1 will change the results of query 12 that follow query 1.

1)    One single PDF file containing the entire project report with the contents as specified in Section IV (with the file name as Your Last Name_Your First Name_IP_REPORT);

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 Your Last Name_Your First Name_IP_Task4);  3) One SQL file (extension .sql) containing the SQL statements (and Transact SQL Stored Procedures, if you choose to use any) to implement all queries (1-15 and error checking) for Task 5 in Section II (with the file name as Your Last Name_Your First Name_IP_Task5a);

4)    One Java file (extension .java) containing the Java source program for Task 5 in Section II (with the file name as Your Last Name_Your First Name_IP_Task5b);

5)    Java, JSP (extension .jsp) and HTML files (if any, extension .html) which comprise your Web application for Task 7 in Section II (just the files with the source code you created/edited, do not try to retain their directory structure used during the running of the application) (each of the files in item (4) must have a file name starting with Your Last Name_Your_First_Name_IP_Task7, for example,

Smith_Joe_IP_Task7_add_employee.jsp).

8.  The project is due on Canvas at 1:30 PM Wednesday, November 16, 2022. Late projects will be accepted until 11:59 PM Thursday, November 17, 2022 with 5% penalty.  

9.  The overall project is worth 250 points (25% of your course grade); the late submission penalty is 12.5 points.    

IV.  REQUIRED FORMAT FOR THE TABLE OF CONTENTS OF THE PROJECT REPORT

 

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

 

Tasks Performed                                                                                 Page Number 

 

Task 1.                                                                                                  
1-10
      1.1. ER Diagram                                                                               
1-1
      1.2. Relational Database Schema                                                     

 
2-10
Task 2. Schema Diagram                                                                        

 
11-11
Task 3.                                                                                                  
12-20
      3.1. Discussion of storage structures for tables                                   
12-14
      3.2. Discussion of storage structures for tables (Azure SQL Database)  

 
15-20
Task 4.  SQL statements and screenshots showing the creation of                            tables in Azure SQL Database                      
21-50
Task 5.                                                                                                  
51-60                 
      5,1  SQL statements (and Transact SQL stored procedures, if any)

             Implementing all queries (1-15 and error checking)
51-54
            5,2 The Java source program and screenshots showing                                   its successful compilation

 
55-60
Task 6.   Java program Execution                                                            
61-90
      6.1. Screenshots showing the testing of query 1                                 
61-63
      6.2. Screenshots showing the testing of query 2                                 

             .

             .

             .
63-65
      6.15. Screenshots showing the testing of query 15                             
80-81
      6.16. Screenshots showing the testing of the Import and                     

               Export options
82-84
      6.17. Screenshots showing the testing of three types of errors            
85-88
      6.18. Screenshots showing the testing of the Quit option                     

             
89-90
Task 7.  Web database application and its execution                                
91-100
      7.1. Web database application source program and screenshots showing

             Its successful compilation                                               
91-97
      7.2. Screenshots showing the testing of the Web database application
98