only section A and B
Database Management SystemsCSE3DMS La Trobe University Department of Computer Science and Information Technology CSE3DMS Assignment-2 2021 (Semester One) Objectives: · To Identify the purposes of existing database · To develop SQL statements that will suit the business needs · To analyse the performance of proposed SQL statements · To enhance the performance of proposed SQL statements Weight of the assessment: 30% The instruction: You have to submit the assignment in LMS in Week-12 by Wednesday, 26 May 2021 at 09:00 pm Melbourne time. Delays caused by computer downtime cannot be accepted as a valid reason for late submission without penalty. Students must plan their work to allow for both scheduled and unscheduled downtime. No extensions will be given. No email submission will be accepted. The Output: 1. Documentation that contains the answer for this assessment in PDF format. Submission Details & What to submit A group cover page. Please ensure that all names of your group are on the first cover page. You should include a list of submitted files in this file. All files should be submitted with “username_ DMS_Assignment_Part2.pdf”. Please note, only one copy of your submission is required. Other members of the group should be on the cover page already. No duplicated submissions! You must submit an electronic copy of your assignment via LMS under the subject title CSE3DMS. It is the student's responsibility to ensure that they understand the submission instructions. If you have ANY difficulties, ask your Lab Tutor for assistance (prior to the submission date). Plagiarism Please do not forget to read the following statement of Copying, Plagiarism in this assignment. Make sure the submitted assignment is your own work. Copying, Plagiarism: Plagiarism is the submission of somebody else’s work in a manner that gives the impression that the work is your own. Penalties for Academic Misconduct There are serious penalties for academic misconduct. You may be asked to leave the University and will not be able to complete your course if a serious academic misconduct is involved in the case. Some students have even been barred from registration in their chosen profession! http://www.latrobe.edu.au/students/academic-integrity Problem Scenarios: You have been accepted to start your internship in TelStar, an international IT company, as a Database Engineer. This company has a good reputation for handling large projects worldwide from various industries. The company has set a very high-quality standard for the projects and applied this standard throughout their activities. You have been assigned to a project named “EPIC” where an overseas client is in the process of complete system migration. As a member of the data transformation team, your primary role is to identify the new structure for the database. The client has sent the dump file in a CSV format from their previous database, and the relational structure is unknown. The first phase is aimed to perform a complete data restoration to Oracle RDBMS. As the first phase of the project has completed, your team move forward to the next phase, which is enhancing the performance of the database. This database will be used with several systems, and your main task is to ensure that these systems will have the best performance in the new database platform The relational database model is given as below In this assignment, you are required to restore the following data to your schema using all files provided in for this assignment, which are: 1. DDL file to create the tables 2. 5 SQL file to insert the data Verify your data by issuing the following script: SELECT table_name, num_rows, blocks, avg_row_len FROM user_tables WHERE table_name IN ('ARTICLE','INVENTORY','CASHIER','CASHIERDETAIL','CASHIERPAYMENT'); Section A: (4 Questions, @10 mark) Using the following tables, provide two different SQL queries to suit the business need to produce identical results. Two SQL queries will be considered as different if they use different approach in retrieving the data. Example of identical/similar SQL: SELECT last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND department_name=’IT’; SELECT last_name FROM employees e, departments d WHERE e.department_id=d.department_id AND department_name=’IT’; Example of different SQL: SELECT last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND department_name=’IT’; SELECT last_name FROM employees e WHERE department_id = (SELECT department_id FROM departments WHERE department_name=’IT’); Example of different SQL: SELECT last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND department_name=’IT’; SELECT last_name FROM employees e JOIN (SELECT department_id FROM departments WHERE department_name=’IT’) d ON (e.department_id = d.department_id); Match your query output with the table given below each question. Notes: All price is in IDR currency. AU$ = IDR 10,000 1. Display the article name and total quantity sold for the highest sales price article sold in May 2019. 2. Display the article name, quantity sold and total loss in May 2019 for all articles where the sale price is less than the base price. Use the sale price and base price from cashierdetail table. 3. Display the list of articles profit sold in May 2019 where the total profit is more than IDR 5M. Display the article name, total profit and qty sold. 4. Create two SQL statements to list the total sale and total payment for all transcation number. … Section B: (4 Questions, @10 mark) For each SQL statement in section A, using SQL Developer Autotrace function, identify the most efficient SQL statement. Provide the screenshoot for each statement for your justification. Them provide the Relational Algebra for the most efficient SQL statement. 1. Autotrace for both SQL in A.1 Relational Algebra for the most efficient SQL in A.1 2. Autotrace for both SQL in A.2 Relational Algebra for the most efficient SQL in A.2 3. Autotrace for both SQL in A.3 Relational Algebra for the most efficient SQL in A.3 4. Autotrace for both SQL in A.4 Relational Algebra for the most efficient SQL in A.4 Section C: (20 Mark) 1. An SQL script is being used check if there were any outstanding payments in the system. Outstanding payment is a condition where the total payment is not equal than the purchase price. Note: Split payment is possible for a transaction. SELECT c.notrans, (SELECT SUM(subtotal) FROM cashierdetail WHERE notrans = c.notrans) totalsale, (SELECT SUM(totalpaid) FROM cashierpayment WHERE notrans = c.notrans) totalpayment FROM cashier c WHERE (SELECT SUM(subtotal) FROM cashierdetail WHERE notrans = c.notrans) != (SELECT SUM(totalpaid) FROM cashierpayment WHERE notrans = c.notrans); Due to the code inefficiency, the code requires a large number of unnecessary workload. Rewrite the SQL query to produce identical result, while at the same time enhance the performance significantly. A maximum point will be awarded if the new SQL script can cut the workload (time) into half. Provide the SQL, result table and AUTOTRACE screen capture as your answer.