SQL codes only needed
DROP TABLE article CASCADE CONSTRAINT purge; CREATE TABLE article ( articlecodeCHAR(16) CONSTRAINT artcode_pk PRIMARY KEY, articlenameVARCHAR2(49) NOT NULL, vendorkeyCHAR(3) NOT NULL, vendornameVARCHAR(16) NOT NULL, categoryinitNUMBER(2), categorynameVARCHAR2(11), typeinitNUMBER(2), typenameVARCHAR2(11) NOT NULL, startdateDATE, expiredateDATE, colourinitNUMBER(3), colournameVARCHAR2(17), sexCHAR(2), pictureCHAR(1), basepriceNUMBER(7), salepriceNUMBER(7), notesVARCHAR2(64) ); DROP TABLE inventory CASCADE CONSTRAINT purge; CREATE TABLE inventory ( articlecode CHAR(16) CONSTRAINT artcode_fk REFERENCES article, barcode CHAR(10), sizes VARCHAR(3) NOT NULL, currentbaseprice NUMBER(7), currentsaleprice NUMBER(7), consignment NUMBER(2), consignmentrp NUMBER(8), qty NUMBER(2), status NUMBER(1), CONSTRAINT inv_pk PRIMARY KEY (articlecode, barcode) ); DROP TABLE cashier CASCADE CONSTRAINT PURGE; CREATE TABLE cashier ( notrans CHAR(14) CONSTRAINTS notrans_pk PRIMARY KEY, datetrans DATE NOT NULL, typetrans VARCHAR2(7), notes VARCHAR2(20), userid VARCHAR2(15), referencetrans CHAR(14) ); DROP TABLE cashierdetail CASCADE CONSTRAINT PURGE; CREATE TABLE cashierdetail ( notrans CHAR(14) CONSTRAINTS notrans_fk REFERENCES cashier, articlecode CHAR(16), barcode CHAR(10), sizes VARCHAR(3) NOT NULL, qty NUMBER(2) NOT NULL, baseprice NUMBER(7), saleprice NUMBER(7), discounttype NUMBER(1), discountpersen NUMBER(1), discountrupiah NUMBER(6), discexpenses NUMBER(1), consignment NUMBER(1), consignmentrp NUMBER(6), subtotal NUMBER(7), payment NUMBER(7), CONSTRAINT inv_fk FOREIGN KEY(articlecode, barcode) REFERENCES inventory, CONSTRAINT cashierdetail_pk PRIMARY KEY (notrans, articlecode, barcode) ); DROP TABLE cashierpayment PURGE; CREATE TABLE cashierpayment ( cid NUMBER(5) CONSTRAINTS payid_pk PRIMARY KEY, notrans CHAR(14) CONSTRAINTS notrans_fk2 REFERENCES cashier, paidtype VARCHAR2(13) NOT NULL, cardinit CHAR(3), cardname VARCHAR2(14), cardnumber CHAR(19), totalpaid NUMBER(7), machinename CHAR(4), companycharge NUMBER(7), customercharge NUMBER(7), reffno VARCHAR2(7), progressivedisc NUMBER(2) ); 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 Friday, 28 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 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 a 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 screenshot for each statement for your justification. Then 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 to 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.