Application Requirements Document

Faculty of Engineering Science

Software Engineering Program

Fourth Year Engineering Project

SmartSQL

1 Introduction

1.1 Vision

The target of this project is a package, written in C#, which will accept an arbitrary English language sentence and will translate it into exact DB query in SQL.

This project performance is NOT expected to be complete and to understand any human request. However, it should be very usable in practice, and be able to handle even quite complex queries in a variety of application domains. In cases where the engine will not succeed to understand it will ask the user one or more clarification questions.

1.2 The Problem Domain

The SmartSQL is useless as standalone package. In order to demonstrate the performance of SmartSQL we integrate it into AlfaMagic. With SmartSQL AlfaMagic’s users will be able to express their requests in their own language and AlfaMagic will response by presenting on the screen the desired result. The system will be developed to deal with several popular DBMS. When a new database is needed to be integrated into the system, the system will first scan the database to extract all the needed information, and then the database will be ready for the clients use.

1.2.1 Alfa Magic:

Alfa Magic is a development platform that has been optimized for developers of administrative applications. Alfa Magic can be used to create computer software without using any computer languages. Alfa Magic Designer lets you design your applications graphically.

1.2.2 The System Architecture:

1.3 Stakeholders

  1. User:

The users of the system could be a company or organization employee which wants to extract the info from the database.

  1. AlfaTech employees:

Will integrate this package in their system.

  1. System administrator:

Will configure the system and will integrate new databases into the system.

1.4 Software Context

1.4.1The major system inputs:

  1. Database that the system will work with.
  2. User queries in natural language sentence.
  3. Glossaries which will make our system smarter and will allow it to deal with more complicated natural language queries.

1.4.2System functionality:

  1. The system will be able to extract from database its schema, tables, fields and other information.
  2. The system will analyze given sentence and generate SQL query.
  3. The system will extract and present the information from database.’
  4. Another way to build a query is by using a graphical interface. This component will be called- “SQL builder”.

1.4.3The major system outputs:

  1. Presentation of the extracted information.
  2. Messages which will guide the user to the correct query, in case of misunderstanding.

1.5 System Interfaces

1.5.1 Hardware Interfaces

Not applicable.

1.5.2 Software Interfaces

  1. Our package will supply interface for presenting extracted information from database in different modes and with various parameters to “Alfa Magic” ( “AlfaTech” tool),which will get the extracted information from database and will interact with the user through this interface for the interactive presentation of data.
  2. MS SQL Server 2005 DBMS.
  3. Access DBMS.
  4. WordNet- It is a glossary which holds all the connections between words in English language as tree. It gives us the capability to retrieve for a given word its synonyms, generalizations, etc.
  5. Tagger- tags word in sentence for their syntax role.
  6. Microsoft’s speech recognition interface which will provide the speech capability.

1.5.3 Events

1.5.3.1 External Events:

  1. Input a query.
  2. Voice command.

1.5.3.2 Internal Events:

1. If misunderstanding occurs, SmartSQL asks the user question.

  1. User answer.
  2. Query result is returned from DBMS.

2 Functional Requirements

  1. The system will analyze given natural language sentence and generate SQL query.
  2. In case of misunderstanding SmartSQL will ask the user for clarification with alternative word suggestion.
  3. The system will integrate glossaries relevant to the specified database in order to understand the given NLP query and make them more complicated.
  4. The system will support all SQL main commands: Select, Join, group by, order by, insert into, update and delete.

Example for queries that the system will support:

  1. List all the products with price less than 30 and it left in stock more than 40.
  2. Sort products which stock is over 15 in descending order by id
  3. Give me employees who were hired more than 14 years ago.
  4. Erase from employees when id equal 222.
  1. The system will not support: having, Reports.

Example for query that the system will not support:

Produce a report which will hold the number of trips for every nurse sorted by department, excluding weekends and holidays.

  1. The system will extract and present the information from database.
  2. The system will support the English language.
  3. The system will support the Hebrew language (optionally).
  4. The system will allow the system administrator to login using a password and add/remove a database (means to add a database to system’s domain).
  5. The system will allow the system administrator to change working DBMS (for example: from SQL server to access).
  6. The system will allow the system administrator to add users to system.
  7. The system will allow creating and manipulating queries using graphical interface.
  8. The system will allow saving queries.
  9. The system will allow opening saved queries.
  10. The user will interact with the system through several graphical interfaces:
  11. Administrator window.
  12. Natural language window.
  13. SQL builder window.
  14. Login window.

16. The system will generate following glossaries for the NL analyzing process:

  1. DB schema- fields and tables of the database.
  2. DB connection-connections between tables.
  3. Table conversion- list of words that user will use for the given table of the database. This glossary will be build manually by administrator with help from “WordNet” which generate possible words to represent given table.
  4. Field conversion- list of words that user will use for the given field of the database. This glossary will be build manually by administrator with help from “WordNet” which generate possible words to represent given field.
  5. DB values- will hold possible values for the given field in order to recognize field by the given value during NL analyzing process, only relevant for fields with close range of values.
  6. Key words db- holds SQL query constants that common to all databases, for example: or, and, count, sum. Will be build manually by administrator of the system using GUI.
  7. Query type db- holds list of words which represents different query types, for example: Select, Insert, etc. Will be build manually by administrator of the system using GUI.

3 Non-functional requirements

3.1 Performance constraints

Speed

  1. The system should respond within 2 seconds.
  2. In case of data extraction from DBMS, the system will be timed out after 20 seconds.

Capacity

  1. The system is one threaded.

3.1.3Safety & Security

  1. User should login first before using the system.
  2. Access control will be applied for two kinds of users:
  1. System administrator.
  2. User.

3.1.4 Usability

1. The system will have a user-friendly interface and within a couple of minutes user will be able to manage and operate with the system.

2. The user doesn’t have to know any specific programming language to retrieve the data.

3.1.5 Availability

  1. The system will be available 24 hours a day for the users use.

3.2Platform constraints

  1. The system will be developed in Visual Studio 2005, in C#.
  2. The system will support access and SQLServer databases.

3.3SE Project constraints

  1. The system will be demonstrated on two databases:
  2. Northwind – a database of SQL Server.
  3. Database which deals with employees,

3.4Special restrictions & limitations

1.The system will be located on machine with operating system Windows 2000 and later.

  1. The system will be developed in .NET environment.
  2. The system interfaces to databases of type:
  1. MS SQL Server 2005 DBMS.
  2. Access DBMS.

4 Usage Scenarios

4.1 User Profiles — The Actors

  1. User – it’s a main user of the system. For example: company manager, secretary, etc. They use the main functionality of the system, they shouldn’t know any programming language to operate the system.
  2. System Administrator-configures various parameters of the system, and supports users in the problematic situations.
  3. DBMS-system makes a connection to it in order to extract or change the data from its databases.
  4. “AlfaMagic”-an application which contains our system and operates the presentation package through supplied interface.

4.2 Use-cases

Use Case no. 1: Creating SQL select queries by SQL builder

The story: The user selects the relevant parameters for his query and the system response with the correct SQL query and his execution.

Primary Actors: System Administrator, User

Stakeholders and Interests:

  • System Administrator: wants to create SQL query
  • User: wants to create SQL query

Pre-conditions: Relevant tables and fields exist in database.

Post-conditions: SQL query was generated correctly and executed.

Main Success Scenario:

Actor Action (or Intention) / System Responsibility
  1. The user chooses the table.
  1. The user chooses the field.
5. User chooses:
  1. If the field is viewable.
  2. Size of field’s column.
  3. Format of fields presentation
(for example: wants to see only year in Date type field).
  1. Color of field’s column.
7. User asks to see the result. / 2. The system displays table’s name
on the special panel and populates the list of relevant fields.
4. The system displays field’s name and type on the special panel.
  1. The system shows generated SQL query.
8. The system executes SQL query and shows result.

Note: steps 1-5 can be executed several times.

Use Case - Creating SQL select queries by SQL builder :

Alternative Flow 1 – JOIN query:

Actor Action (or Intention) / System Responsibility
6. User chooses first table for join.
7. User chooses joined type.
8. User chooses second table for join to this table.
9. User chooses field name for join on both tables.
11. User asks to see the result. / 10. The system shows generated SQL query.
12. The system executes SQL query and shows result.

Note: steps 6-10 can be executed several times.

Use Case - JOIN query:

Alternative Flow 2- Selecting additional query parameters:

Actor Action (or Intention) / System Responsibility
  1. User defines if the field is used in “where” or/and in “Order by” or/and in “Group by”.
  1. If “where” selected, user enters condition.
9. User asks to see the result. / 8. The system shows generated SQL query.
10. The system executes SQL query and shows result.

Use Case - Selecting additional query parameters:

Use Case no. 2: Creating new table in database or changing/deleting existing one by SQL Builder.

The story: The user selects or inputs all the relevant data to create a new table.

Primary Actors: System Administrator, User.

Stakeholders and Interests:

  • System Administrator: wants to create update/create/delete table in database.
  • User: wants to create update/create/delete table in database.

Pre-conditions: Relevant table exist in case of update/delete.

Post-conditions: SQL query was generated correctly and executed.

Main Success Scenario: Table Creation:

Actor Action (or Intention) / System Responsibility
1. The user chooses creation of new table option.
3. The user enters new table name.
  1. The user enters new field parameters
(this done as number of fields we want to add):
  1. Field name.
  2. Field type.
  3. Field size.
  4. Default value.
  5. Allow null.
  6. Is primary key.
  7. Is foreign key and to which table.
6. User saving the query in the system.
7. User asks to execute the query and see the result. /
  • The system displays the special (creation table) panel.
5. The system shows generated SQL query.
  1. The system executes SQL query and shows result.

Use Case – Table Creation:

Alternative Flow 1 – Update table (update of fields):

Actor Action (or Intention) / System Responsibility
1. The user chooses updating the table option.
  • The user chooses the table to update.
  1. The user chooses field to update.
  2. The user chooses update parameters (changing type, name, etc.)
*(steps 5-6 executed as number of fields we want to update)
7. The user requests the update to be executed. /
  • The system displays the special (updating the table) panel.
4. The system displays table’s fields.
8. The system shows generated SQL query executes it and shows result.

Use Case – Update Table (update of fields):

Alternative Flow 2 – Update table(add fields):

Actor Action (or Intention) / System Responsibility
1. The user chooses updating the table option.
  • The user chooses the table to update.
5. The user chooses to add field option.
6. The user enters new field parameters
(this done as number of fields we want to add):
  1. Field name.
  2. Field type.
  3. Field size.
  4. Default value.
  5. Allow null.
  6. Is primary key.
  7. Is foreign key and to which table.
7. The user requests the update to be executed. /
  • The system displays the special (updating the table) panel.
4. The system displays table’s fields.
8. The system shows generated SQL query executes it and shows result.

Use Case – Update Table (add fields):

Alternative Flow 3 – Update table(delete fields):

Actor Action (or Intention) / System Responsibility
1. The user chooses updating the table option.
  • The user chooses the table to update.
5. The user chooses field to delete.
*(step 5 executed as number of fields we want to delete)
6. The user requests the update to be executed.
8. The user responds for the system request. /
  1. The system displays the special (updating the table) panel.
4. The system displays table’s fields.
7. The system asks user if he sure that he wants this field to be deleted.
9. The system shows generated SQL query and updates the table.

Note: steps 7-8 executed as number of fields we want to delete.

Use Case – Update Table (delete fields):

Alternative Flow 4 – Delete table:

Actor Action (or Intention) / System Responsibility
1. The user chooses updating the table option.
  • The user chooses the table to update.
  • The user chooses delete table option.
6. The user requests the update to be executed.
8. The user responds for the system request. /
  1. The system displays the special (updating the table) panel.
7. The system displays warning if the user is sure he wants to delete the table.
9. The system shows generated SQL query and updates the table.

Use Case - Delete table:

Alternative Flow 5 – Delete table (can not be performed):

Actor Action (or Intention) / System Responsibility
1. The user chooses updating the table option.
  1. The user chooses the table to update.
  2. The user chooses delete table option.
5. The user requests the update to be executed. /
  1. The system displays the special (updating the table) panel.
6. The system displays message the table can not be deleted in case his primary key is foreign key in another table.

Use Case - Delete table (can not be performed):

Use Case no. 3: Generating SQL from NL.

The story: The user inputs request in free language and the system generates the SQL query and executes it.

Primary Actors: User

Stakeholders and Interests:

  • User: wants to create and execute SQL query

Pre-conditions: Relevant tables, fields and schema exist in database. The database was analyzed and keywords were extracted.

Post-conditions: SQL query was generated and executed correctly.

Main Success Scenario:

Actor Action (or Intention) / System Responsibility
  1. The user inputs question in natural language in the input field.
  1. User asks to see the result.
7. User chooses to save the query. /
  1. The system analyzes the question.
  2. The system generates corresponding SQL query.
  3. The system shows generated SQL query.
6. SQL query executes and the system shows results.
8. The system saves the query.

Use Case - Generating SQL from NL:

Alternative Flow 1: Misunderstanding.

Actor Action (or Intention) / System Responsibility
4. The user changes his question
according to the presented message
by rewriting the question or by
choosing one of the presented
choices.
8. User asks to see the result.
10. User chooses to save the query. / 3. There are orders that the system does not understand. The system notifies the user about the problem and asks him to reformulate the question (possibly presenting options)
5. The system analyzes the question
again.
6. The system generates corresponding
SQL query.
7. The system shows generated SQL query.
9. SQL query executes and the system shows results.
11. The system saves the query.

Note: steps 3-5 can be executed several times