MySQL

Objective:

This assignment will focus on developing a Store Management System using MS SQL Server. Students will implement SQL objects such as stored procedures, views, triggers, and functions aligned with UI mockup displayed at the bottom of this webpage. Students are required to utilize the tables created during the Chapter 5 DDL assignment.

Requirements:

• Complete previous DDL, DML, and DQL assignments to use table structures and data.
• Submit each SQL object (procedure, view, function, trigger) as a separate.sqlfile, named according to the database object.
• Provide a report explaining each SQL object’s purpose and relation to the UI.

Assignment Tasks:

Stored Procedure Tasks:

1. Stored Procedure for All Contracts Data Grid
   • Write a stored procedure to retrieve project name, client name, start date, end date, total amount, paid amount, balance amount and project status.
2. Stored Procedure for Customer Management
   • Create a stored procedure to insert customer information for the Customer Management screen.
3. Stored Procedure for Supplier Management
   • Create a stored procedure to update supplier information for the Supplier Management screen.
4. Stored Procedure for Contract Management
   • Create a stored procedure to add a new contract and a new project, ensuring the contract is associated with a client. Additionally, generate three invoices by dividing the total amount into three equal parts, with each invoice having a due date spaced one month apart.
5. Stored Procedure for Purchase Order
   • Create a stored procedure that creates new purchase order by inserting data into purchase order and purchase order detail tables. Also update current stock quantity in product table.
6. Stored Procedure for Receivable Payment Insertion
   • Write a stored procedure to insert new payment information for an invoice into receivable transaction table, also update invoice status in invoice table as paid.
7. Stored Procedure for Analysing Project Work Hours
   • Create a stored procedure to analyze the work hours logged by employees for a all calendar dates of a given project. The procedure should list each calendar date within the project’s start and end dates and display the total work hours logged by all employees. If any calendar date has no logged work hours, it should show zero. You would need to use stored procedure's temporary table technique.

View Tasks:

1. View for Employee Details
   • Create a view to display employee details.
2. View for Contract Details
   • Create a view to display project name, client name, start date, end date, Total amount, balance amount and project status.
3. View for Inventory Overview
   • Write a view to display the available inventory of each product, showing product name, product_category, quantity in stock and re-order level.
4. View for Client Analysis
   • Create a view to analyze client business deals for a specific year. The view should display the following information: client name, calendar year, calendar month, project count, contract count, and project amount. Ensure that all months are displayed, even if there are no business transactions with the client. To achieve this, use the date dimension table as main table and apply the LEFT JOIN technique on other tables.

Function Tasks:

1. Function to Calculate Total Contracts for Customer
   • Write a function that calculates the total number of contracts placed by a specific customer.
2. Function to Get Available Stock
   • Write a function that retrieves available stock for a given product.
3. Function to Calculate Profit for a given Project
   • Write a function that retrieves profit of a given project after considering employee cost, product used cost and contract value.

Trigger Task:

1. Trigger on Amount Change
   • Create a trigger on the CONTRACT table that logs any changes made to the total_amountfield into theAUDIT_LOGtable. Ensure it captures thecontract_id, old_total, new_total, and log_date.

Submission Guidelines:

• Each SQL object (procedure, view, function, trigger) should be in a separate .sql file named after the object.
• Provide a report explaining the purpose of each SQL object and its relationship to the UI elements.

This assignment focuses on building a comprehensive Store Management System using MS SQL Server. You will manage data for products, customers, orders, inventory, and employees using advanced SQL objects.

UX Screens for Company Management System Mockup