SQL-based English Query applications work best with normalized databases. In general, it is easiest to create English Query applications against normalized SQL databases. In addition, the resulting applications are more flexible and powerful than those developed against databases that are not normalized.
This topic describes normalization rules as they pertain to English Query SQL applications. It describes problematic database structures that break these rules and how to solve these problems by creating views in Microsoft® SQL Server™, which can be used in English Query just like any other table.
Rule 1: There should be a one-to-one relationship between the instances of an entity and the rows of the table.
For every table that represents an entity, each and every row in that table should represent one and only one instance of that entity. Conversely, each and every instance of that entity should be represented by one and only one row in the table.
In this situation, this rule is not met:
Fields: Emp_id, Emp_name, Status, Position, Salary
Keys: Emp_id, Status
This table stores information about employees. It contains their names, positions, and salaries. But sometimes employees move around from position to position, and when they do, their salaries change. So for some employees, this table also stores information about their projected position and salary. If the value of the Status field is C, the row contains the current information for the employee. If the value is P, it contains the projected information. Thus, an individual employee may appear twice in this table.
Because an employee can appear twice, you cannot use this table to represent the employees entity. If you were to associate the employees entity with this table, even simple requests, such as “Count the employees”, would give the wrong answer. The solution to this problem is to create a view in the database that contains a single row for each employee and to tell English Query about this view.
Here is what the view would look like:
CREATE VIEW Emps AS
SELECT Emp_id, Emp_name, Position, Salary
WHERE status = 'C'
You now have a view that contains exactly one row per employee. The employees entity can now be represented by this view. (more…)