PROG2390 Small Business Solutions Task: You are required to create SQL scripts to perform the following tasks given in the questions below. Instructions: • You must Provide all completed submissions in Microsoft word OR PDF format o You may use SSMS to check your SQL syntax (using “new Query”) BUT the final submission must be in word or / PDF. • • • • All scripts must be in proper SQL syntax to reflect all elements including the primary keys All statements must be clear, and easy to read. Do NOT create actual tables OR views in the database – these will be run in class. The scripts must NOT be generated from existing tables or views. Questions: Section 1: Write the SQL script to create the following Tables: 1. [2 Marks] Invoice: Table name = “Invoices” 2. [2 Marks] Invoice Details: Table name = “Invoice Details” 3. [2 Marks] Invoice Payments Table name = “Invoices_Payments” 4. [2 Marks] Customer: Table name = “Customer” 5. [2 Marks] Chart of Accounts: Table name = “Chart_Of_Accounts” 6. [2 Marks] Suppliers: Table name = “Suppliers” Section 2: Write the SQL script to create the following views Based on the question determine the type of Join to be used when creating the views (e.g., Left Join, Right Join, Inner Join). 1. One to many relationships between Invoice and Invoice details. [2 Marks] 2. One to many relationships between Invoice and Invoice payments. [2 Marks] 3. One to one relationship between Invoice and invoice payments. [2 Marks] Section 3: The following is required to answer the questions below: • SQL INSERT statements • The Sample Chart of Accounts data in APPENDIX A – below: Create the statements required to update ONLY the RELEVANT tables created in Section 1 based on the scenarios below: 1. You sold furniture to a retail store on credit (hint accounts payable) at a cost of $2,000.00. An invoice was generated to record the sale. Create the statement required to update the Invoice table. [4 Marks] 2. The following day payment was made to you in cash at the full price, based on your knowledge of accounting you know that a double entry must be made. As a hint you are aware that cash and sales are the accounts that are affected. Create the statement required to update the invoice details (which reflects the sale) and the Invoice payments table (which gives information about the payment). [5 Marks] APPENDIX A – SAMPLE CHART OF ACCOUNTS DATA Account Number 110 120 130 160 Account Name Business Bank Account (cash) Accounts Receivable Petty Cash Inventory category Assets Assets Assets Assets 170 180 190 200 205 210 220 310 320 330 340 360 370 380 410 420 430 510 520 540 610 640 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 Land Building Office Equipment Computer Equipment Accumulated Depreciation on Computer Equipment Furniture Motor Vehicles Accounts Payable Accrued Expenses Revenue Received in Advance Wages Payable – Payroll Income Tax Finance Lease Loans Payable Owner – Investments Owner – Drawings Retained Earnings Sales Other Revenue Dividends Received Cost of Goods Sold Advertising Depreciation Entertainment Freight