Assignment: Customer Orders Management System

Objective:

Build a simple database management system for a fictional company that tracks customer

orders and inventory levels. Use stored procedures to handle data manipulations and

triggers to maintain data integrity and perform logging actions.

Part $1$: Database Setup

$1.$ Create Tables:

Students will create the following tables:

$$ Customers: Contains customer details.

Fields: CustomerID $($primary key$),$ CustomerName, ContactName,

Country

$$ Products: Contains product details.

Fields: ProductID $($primary key$),$ ProductName, Price, QuantityAvailable

$$ Orders: Records orders placed by customers.

Fields: OrderID $($primary key$),$ CustomerID $($foreign key$),$ OrderDate,

TotalAmount

$$ OrderDetails: Records details about each product ordered.

Fields: OrderDetailID $($primary key$),$ OrderID $($foreign key$),$ ProductID

$($foreign key$),$ Quantity, Price

$2.$ Populate Tables:

Provide a set of sample data to populate these tables or instruct students to create

their own realistic data.

Part $2$: Stored Procedures

$1.$ Create a Stored Procedure AddNewOrder:

This procedure should accept details for a new order, including customer ID$,$ an

array of product IDs, and their quantities. The procedure should:

$$ Insert a new order into the Orders table.

$$ Update the OrderDetails table for each product ordered.

$$ Calculate the total amount for the order and update the Orders table.

$$ Ensure the product quantity is available before confirming the order.

$2.$ Create a Stored Procedure UpdateOrder:

This procedure should allow updating the quantity of products in an existing order. It

should check for product availability before making changes.

Part $3$: Triggers

$1.$ Inventory Management Trigger:

Create an AFTER INSERT and AFTER UPDATE trigger on the OrderDetails table to

automatically update the QuantityAvailable in the Products table whenever an

order is placed or updated.

$2.$ Logging Trigger:

Create an AFTER INSERT trigger on the Orders table that logs each new order into

an OrderLog table. The OrderLog table should have fields for LogID, OrderID,

LoggedTime, and Action $($e$.$g$.,$ 'New Order Placed'$).$

Part $4$: Testing and Validation

$$ Students must write SQL queries to demonstrate the functionality of their stored

procedures and triggers.

$$ They should include test cases where orders exceed the available product quantity

and show how the system handles these situations.

Deliverables:

$$ SQL script for creating tables and inserting sample data.

$$ SQL script for stored procedures and triggers.

$$ A report documenting the functionality of each component, including test cases

and their outcomes.

Evaluation Criteria:

$$ Correctness of SQL syntax and logic.

$$ Effective use of stored procedures for encapsulating business logic.

$$ Correct implementation of triggers to ensure data integrity and perform auxiliary

actions.

$$ Quality and clarity of the documentation and test cases.

Grading Rubric for Customer Orders Management System Assignment

$1.$ Correctness of SQL Scripts $(50$ points$)$

$$ Tables Creation $(10$ points$)$: Properly defined tables with all required fields and

constraints.

$$ Correct data types and field sizes $(5$ points$)$

$$ Appropriate use of primary and foreign keys $(5$ points$)$

$$ Stored Procedures $(20$ points$)$: Correctly implemented stored procedures that

perform the expected operations without errors.

$$ Logical implementation of AddNewOrder procedure $(10$ points$)$

$$ Logical implementation of UpdateOrder procedure $(10$ points$)$

$$ Triggers $(20$ points$)$: Triggers should accurately perform the tasks as specified,

updating and logging data correctly.

$$ Inventory management trigger functions correctly after inserts$/$updates $(10$

points$)$

$$ Order logging trigger captures new orders accurately $(10$ points$)$

$2.$ Execution and Testing $(20$ points$)$

$$ Functional Testing $(10$ points$)$: Demonstrates that all database operations perform

as intended through provided test cases.

$$ Test cases cover all functionalities $(5$ points$)$

$$ Test cases validate boundary conditions and error handling $(5$ points$)$

$$ Result Validation $(10$ points$)$: Clear evidence is provided that the system behaves

as expected, with sample outputs and explanations.

$$ Correctness of outputs for given inputs $(5$ points$)$

$$ Detailed explanation correlating outputs with expected outcomes $(5$ points$)$

$3.$ Documentation and Presentation $(20$ points$)$

$$ Code Documentation $(10$ points$)$: SQL scripts are well$-$documented with

comments explaining the purpose of functions, procedures, and trigger logic.

$$ Comments on major sections of the code $(5$ points$)$

$$ Inline comments for complex logic $(5$ points$)$

$$ Report Quality $(10$ points$)$: Submission includes a clear, coherent report that

explains the design, functionality, and testing process.

$$ Clarity and structure of the report $(5$ points$)$

$$ Depth of discussion on testing and outcomes $(5$ points$)$

$$ Bonus Points $($up to $10$ extra points$)$: Exceptional work, such as