System Requirements Document

Introduction

This document outlines the system requirements for a SQL-based data management and query system. The requirements are derived from the user's chat input and the uploaded document detailing various SQL assignments and tasks.

System Overview

The system is designed to handle various SQL operations, including importing CSV files into tables, performing complex queries, and managing database constraints. The primary focus is on executing T-SQL queries to manipulate and retrieve data from multiple tables.

Functional Requirements

As a Database Administrator:

1. I want to import RetailInvoiceDetails.csv as the RetailInvoiceDetails table to facilitate data queries.
2. I want to retrieve the top 3 highest invoice transactions based on the total amount (Quantity × UnitPrice).
3. I want to add a new column OrderType to invoice records, displaying 'Bulk Order' if the quantity is 5 or more, and 'Normal Order' otherwise.
4. I want to add a new column DeliveryDeadline to invoice records, calculated by adding delivery days to the InvoiceDate for each country.
5. I want to identify customers who have purchased the same product in more than one country.
6. I want to retrieve all products purchased in France or India that do not belong to the 'Lighting' or 'Gadgets' categories.
7. I want to calculate the total monthly sales revenue for the current year, where revenue is calculated as Quantity × UnitPrice.
8. I want to calculate the total sales revenue for each country, excluding null values in the country column.
9. I want to calculate the total sales revenue and number of invoices for each country and sales channel, only including records where total revenue exceeds 5,000.
10. I want to increase the UnitPrice by 10% for all 'Lighting' category products sold in India.
11. I want to remove all records from the RetailInvoiceDetails table where any columns contain a NULL value.
12. I want to create tables with appropriate constraints, differentiating between Primary Key and Unique Constraint.
13. I want to ensure multiple columns are both unique and not null in a single table.
14. I want to create an Employee table with constraints on the BasicSalary column.
15. I want to design a database with Departments and Employees tables, applying the ON DELETE CASCADE option.
16. I want to import BankTranctionDetails.csv as the BankTranctionDetails table.
17. I want to retrieve details for all "Salary Credit" transactions, including the day of the week and days since the transaction.
18. I want to display the Year, Month, Total Credit Amount, Total Debit Amount, and Transaction Count for each month and year.
19. I want to import SalesmanTarget.csv and SalesmanSale.csv as tables and calculate remaining sales targets for salesmen.
20. I want to calculate the total sum of all positive values as Sum_Pos_Values.
21. I want to calculate the total sum of all negative values as Sum_Neg_Values.
22. I want to import CustomerInfoNew.csv and CustomerInfoOld.csv as tables and classify records as 'UPDATE', 'INSERT', or 'DELETE'.
23. I want to import EmployeeData.csv and DepartmentData.csv as tables and fetch the department name and number of employees in each department.
24. I want to use the MERGE statement to perform insert and update operations based on matching records between two tables.
25. I want to import EmployeeSalary.csv as the EmployeeSalary table and display all employee records in ascending order by Id, appending a final row showing the total salary paid.
26. I want to import FacultyAssignments.csv as the FacultyAssignments table and return one row per department with faculty names concatenated as a comma-separated list.
27. I want to import CustomerGenInfo.csv and update all gender values in the CustomerInfo table by flipping 'M' to 'F' and 'F' to 'M'.

User Personas

Database Administrator

• Responsible for importing data, executing queries, and managing database constraints.

Core User Flows

Importing Data

1. Access the database management system.
2. Select the CSV file to import.
3. Map CSV columns to table columns.
4. Execute the import operation.

Executing Queries

1. Access the query interface.
2. Input the T-SQL query.
3. Execute the query.
4. Review and analyze the results.

Visuals Colors and Theme

• Default database management system interface colors and theme.

Signature Design Concept

• Standard SQL query interface with a focus on functionality and efficiency.

Interaction Model & Motion Direction

• Standard database interaction model with query execution and result display.

Non-Functional Requirements

• The system must handle large datasets efficiently.
• Queries should execute within a reasonable time frame.

Tech Stack

• SQL Server for database management.
• T-SQL for query execution.

Assumptions and Constraints

• The system assumes access to CSV files for data import.
• The database must support T-SQL queries.

Glossary

• T-SQL: Transact-SQL, an extension of SQL used in Microsoft SQL Server.
• CSV: Comma-Separated Values, a file format for data import/export.
• Primary Key: A unique identifier for a database record.
• Unique Constraint: Ensures all values in a column are different.
• Foreign Key: A field in one table that uniquely identifies a row in another table.