User-database interaction at the knowledge level of abstraction

ELSEVIER

INFORMATION AND SOFTWARE TECHNOLOGY

Information and Software Technology 39 (1997) 657-668

User-database

interaction at the knowledge level of abstraction Hock C. Chana3*, Robert C. Goldsteinb

aDepartment of Information Systems and Computer Science, National University of Singapore, Kent Ridge, Singapore 119260 bFaculty of Commerce and Business Administration, University of British Columbia, 2053 Main Mall, Vancouver, B.C., Canada VCiT122 Received 4 June 1996: revised 16 June 1997; accepted 17 June 1997

Abstract Despite the widespread cause of this difficulty is to the user’s world. This the user and the database the user.

popularity of relational databases, it is still very difficult for trained users to formulate relational queries. The major the fact that the user and the database system are forced to communicate using constructs that are not closely related paper proposes and develops a new level of user-database interaction, the knowledge-level (KL) interface, where system exchange only knowledge from the user’s world. The data structure used in the database is fully hidden from

Under the new KL approach, the database system is no longer seen as a store of data. Rather, it is an agent to hold domain knowledge and provide it upon request. It is shown that the KL interface is in many ways better than the relational interface. Users of the KL interface need to know less and perform fewer data manipulation operations than users of the relational interface. 0 1997 Elsevier Science B.V. Keywords:

User-database

interface;

Abstraction

levels; Entity-relationship

1. Introduction The user interface to a database system is an important research area, since a better interface allows easier interaction with the database and faster, more accurate query formulation. Despite numerous research efforts in this area, the problem remains that database interfaces are complicated, even for users who are trained [ 1,2]. The major difficulties arise because the user has to deal with unfamiliar data objects, such as relations and fields, in the database [3-51, and because the user may have to carefully manipulate many data elements in order to specify simple real-world concepts. Most query languages for the relational data model, for example, require users to know the relations in the database, and the names and meanings of each column. We shall use the term logical data-level inteeace to refer to this level of interaction where the user needs to know and manipulate the data elements in the database. We shall propose and develop a new level of interface, knowledge-level (KL) integace, that eliminates the difficulties of the logical data-level interface. These difficulties are described in more detail in Section 2. * Corresponding author. Tel.: 00 65 772 3393; fax: 00 65 772 4.580; e-mail: [email protected] 0950.5849/97/$17.00 0 1997 Elsevier Science B.V. All rights reserved PII SO950-5849(97)00022-O

model; Knowledge

level: Relational model; SQL

Section 3 introduces the KL and proposes the view of a database as a knowledge agent rather than a store of data. Section 4 develops a KL interface using elements of the entity-relationship (ER) model. A specific language, KQL, developed as a small example of the KL interface, is described in Section 5. KQL incorporates only a very small number of the operations possible in the KL interface. This is followed by Section 6, giving a comparison with other kinds of interface, and the conclusion in Section 7.

2. Query difficulties of the logical data-level interface The relational interface is an example of the logical-level interface [6-91. Since a relation can mean one or more of many concepts, the user must understand correctly and manipulate accordingly. The following paragraphs summarize the major difficulties for relational users. Data-oriented retrievals and updates are difficult. Before a user can retrieve information from a relational database about the real world, she first has to understand the meanings represented by the data elements and by the arrangement of the data elements (for example, that two columns in a particular relation represent a relationship, and that attributes of the participating entities are to be found in two

658

H.C. Chart, R.C. Goldstein/Information

other relations). She then has to know how to manipulate the relations to obtain the desired result for example, by performing joins based on the key columns. The primitive relational operations are on data, with no clear correspondence to operations on real-world objects. As a result, many of these data manipulation operations, especially the join operation, are difficult for users [2,3,10,1 I]. Updates to a relational database focus on tables and columns. A single real-world event may necessitate changes to many relations. If, for example, as a result of normalization, an entity is represented by two or more relations, deleting an entity instance requires changes to the tuples of all these relations. The user is expected to know the relevant relations, and when to delete something or set something to ‘null’. For example, consider an event where an employee X is no longer a head of department. If there is a separate relation HEAD containing foreign keys employee number and department number, then the record for X must be deleted. If, alternatively, there is no separate relation, and the department number appears as a foreign key in the employee relation, then this column for employee X must be set to null. It is also tedious for the user to maintain the integrity and consistency of a relational database. Maintenance is required when changes are made to the database, through insertion, deletion or changes. For example, when an entity occurrence is deleted, all its relationships have to be deleted as well. The user has to perform different actions, depending on how the entity and its relationships are represented in the relations. The user may have to delete tuples in other relations, may have to set certain values in other relations to null, or perhaps, both of these things. Some systems have triggers and constraints capabilities, but that only means that the database designers have to manually define all the triggers and constraints. Recognizing this difficulty, many design tools incorporate automatic constraint generation to alleviate this problem. The relational model does not directly support advance concepts such as is-a relationships and inheritances. As a result, further more complicated knowledge and operations are expected from the users.

3. The knowledge

agent

The traditional view of databases is as passive stores of data. We show how the idea of a knowledge level [ 12,131 can be adopted to change databases into active knowledge agents. Communication between user and database system is based on the knowledge level of application domains. A brief summary of abstraction levels commonly used in information systems and database modeling is given in Section 3.1. Section 3.2 then proposes a higher level of abstraction - the knowledge level - which is used by the knowledge agent.

and Software

Technology

39 (1997) 657-668

3.1. General a,bstraction levels Abstraction is the process of generalizing and discarding details that are not needed. The three common abstraction levels are the conceptual, the logical and the physical levels. The conceptual level is a description of the possible states, events and derivation rules of the real-world system; that is, it expresses the real-world system’s statics and the dynamics. The logical level is an operational description of the information system, independent of the underlying technology that could be used to implement it. The physical level describes the physical structure of the database and detailed structure of each process. In information systems and databases, it is usual to deal separately with the different levels. For example, Structured Analysis produces separate physical and logical data how diagrams, and Structured Design produces the logical design before the physical design. It should be noted that there is not much global consensus on the definitions of the various abstraction levels. For instance, there are different interpretations for ANSVSPARC’s three schema levels. [ 141 considers the conceptual schema as a description of the logical relationship between data items, together with integrity constraints, but [ 1.51 considers that to be either the conceptual or the logical level. 3.2. The knowledge

level

In the artificial intelligence field, there is a clear distinction between knowledge and its representation. The concept of a knowledge level [ 12,131 defines knowledge as whatever can be ascribed to an agent such that its behavior can be considered rational. The agent has goals and can act rationally to satisfy its goals. This is a purely functional definition of knowledge, with no implication of any structure, nor of inference or search strategies. The importance of the knowledge level was noted ([ 161, p. 77): “We might even consider that the single most important contribution that AI can make to databases is the notion of the Knowledge Level itself”. A complementary idea appears in computer programming - knowledge programming [17] - which is defined as producing programs where the domain-level knowledge is explicitly visible. With knowledge programming, the programmer should care very little, if at all, about the computer-level representation of the knowledge. In particular, the order in which the data appear (that is, the data structure) should not have to be considered. This separation of knowledge from its representation can be viewed as a further split at the conceptual level. The conceptual level is concerned about the real world and usually produces a model which is a representation of the real world [ 181; there can be many possible representations of the same knowledge. For example, it can be represented by using logic, networks or frames. Even within the logic domain, there are many ways of representing the same facts, even graphically. Therefore it makes sense to distinguish

H.C. Ghan, R.C. Goldstein/Information

Conceptual Knowledge Conceptual Knowledge Representation Fig.

I. Conceptual levels.

between the conceptual knowledge level and the conceptual representation level. The conceptual levels are shown in Fig. 1. An example will help illustrate the difference between the two conceptual levels. Consider the following small schema using the conceptual language Galileo [ 19,201: Organization: = ( REC Departments CLASS Department

(-)

(Name: string AND Employees: AND Employees Employee

VAR SEQ employees) CLASS

(->

(Name: string AND Nameofdept:

= DERIVED

Name of

get Departments with this isin (at Employees))). Note that the relationship between a department and employees is defined as a property of Department which is then referenced in the definition of Employee. In Galileo, a derived attribute (such as Employee.Nameofdept) cannot be updated. If an employee moves to another department, the user must update Department.employees, not Employee. Nameofdept. This is thus an update to a knowledge representation. A pure knowledge update should be free of such representational restrictions. 3.3. The knowledge The two programming environment:

agent

ideas of knowledge level and knowledge can be incorporated into the database

The new database system is seen as an agent whose goals are to keep track of knowledge and to accept and answer queries, including both retrievals and updates, that are formulated as knowledge programs. Obviously, a database must rely, internally, on data structures to represent knowledge. However, these data structures, be they physical pointers or logical pointers of keys, need not be known to the users. The user no longer performs operations on the data in a database. Rather, the user will now communicate with the database system through exchange of domain knowledge. For example, the user will tell the database system a piece of knowledge such as that the supplier with number ‘S2’ is

and Sqftwure

Technology

39 (1997) 657-668

659

providing the part with number ‘P3’, instead of telling the database to insert a record “‘S2’,‘P3” in the table SUPPLY. This can be seen as programming using only domain knowledge. A substantially new way of looking at databases is advocated in which the database is a store of domain knowledge rather than of logical data elements. Traditionally, a DBMS helps the user to organize her data. The user knows what data are inside the database and has the responsibility for ensuring that the data remain consistent. In the new way, the user does not know what data are inside the database. An immediate benefit is that the user is relieved of the responsibility of keeping the data consistent. One way to visualize this new system is to think of it as a human assistant. You tell her what you know of the real world and allow her to choose the method for representing this knowledge. When you need to refresh your memory, you ask her for the required knowledge rather than asking to see the files and pieces of paper that she uses to record the knowledge. You have to tell a relational DBMS the relevant relations and the correct join conditions. But you do not have to tell your assistant how to operate on her files. In fact, you will have no knowledge of the files. This idea of a knowledge agent also has support from the object-oriented programming paradigm. “The most important concept in object-oriented approach is data abstraction.. . We are interested in the behavior of an object rather than in its representation” [21]. Here we are interested in the behavior (the user-database interaction) of the database rather than in the representations used inside the database. The database system proposed here will not provide any mechanism for manipulating the variables inside the database. The internal variables do not concern the user. Another advantage of the user’s not having to deal with representational details is the provision of entity identity. Entity identity exists if an entity can be referenced directly as a unit even though pieces of its representation may appear in multiple places in the database. A data model that provides entity identity can avoid many inconveniences and proliferation of constraints. Some examples of this have been discussed in the section on preserving consistency in a relational database. 3.4. The knowledge-level

inte$ace

The communication of knowledge between the user and the system must be based on some shared conceptual schema [22]. We need a model that can describe concepts of the real world. On the other hand, we do not want a model that is too specific, such as one with persons and houses already built in. There are many models to choose from, e.g., NIAM, relational, entity-relationship, object-oriented, or functional models. The relational model is not suitable for communication of domain knowledge. Humans simply do not communicate in

660

H.C. Chm, R.C. Goldstein/lnfornlation

terms of relations, columns, keys and foreign keys. The same reasoning applies in the rejection of other low-level models such as hierarchical or network. Although objectoriented mode1.s are popular, there are presently many different models with not much standardization. Of the semantic models, the entity-relationship model is the “most widely used model” ([6], p. 30, 32) and it “remains the premier model” ([8], p. 3). The ER model is also noted for its successful role as a communication tool between designers and end-users [9,23]. This is an important fact, as the knowledge interface aims to allow easy communication between humans and database systems. The elements of the ER model are widely considered as natural [ 18,241. In addition, ER model has many of the features desired for knowledge-level communication: it inherently contains simple real-world integrity constraints such as the requirement that relationship instances can exist only if the participating entity instances exist; it allows for implicit inheritance of attributes and even relationships from parent entities; and it provides entity identity. With the above considerations, the concepts of the ER model are chosen to form the basic conceptual schema shared between the user and the system. With this shared schema, the user can then proceed to tell the system of the existence of specific entity types such as supplier, part and department; and specific relationship types. After the types have been defined (that is, the shared conceptual schema has been expanded), the user can proceed to tell the system of specific entity instances and relationship instances. We hasten to qualify that the ER model is not the perfect choice. This is also evident from the continual enhancement of the ER model that has occurred since it was initially proposed [ 181. If and when a better model is identified, the basic ideas of the knowledge level can be applied to that model. Three basic principles are suggested for the design of a knowledge-level interface: Principle 1: The user-database communication should use only domain knowledge, with some appropriate syntax. Principle 2: The communication should not be restricted by any peculiarity of the data structure (or knowledge representation) used in the database. For example, knowledge of the order of the data in the data structure should not be required in the communication. Principle 3: The interface should avoid all operations that are not required by the domain. An example of such an operation is the joining of relations. Another important example is the manipulation or definition of physical or logical pointers. A totally different example is the requirement for follow-up deletions. If a user deletes an entity instance, she should not be required to do a follow-up deletion of the relevant relationship instances. A further example is the requirement to manually inherit attributes and relationships.

and Sofnyare Technology 39 (1997) 657-668

3.5. Human knowledge

and system knowledge

To summarize, there are three distinct types of knowledge required to manipulate data structures effectively: domain knowledge, data knowledge and knowledge of the transformation between them (Fig. 2). These three types of knowledge may be distributed in different ways between the user and the DBMS, providing only that there must be some shared knowledge in order to permit meaningful communication. A KL-system knows all three types, so that KL-users only need to know the domain knowledge. The Data-system knows only the data knowledge; as a result, Data-users have to know all three types of knowledge. Since KL-users need to know less and perform fewer transformation, the interface should be easier for them [25].

4. Knowledge

interface

communication

Most existing database systems communicate with the user at the data level. That is, the things which are exchanged between the user and the database are pieces of data. The user is expected to know how to interpret these data to answer his or her questions. This research proposes that the user-database interface will be much more effective if units of knowledge, rather than data, are exchanged. This section discusses knowledge-level interfaces without adopting any specific syntax. Then, Section 5 introduces KQL as a specific example of a knowledge-level language. 4.1. Knowledge-level

retrieval

To illustrate how a knowledge-level interface will work, this section describes the retrieval facilities that would be required. A retrieval may be composed of a number of operations andfor conditions. An operation is a process that accepts some input and produces some output. A special kind of operation that returns a Boolean value of true or false is referred to as a condition. The following notation is used in the description of the categories of operations that may exist: 1. 0 stands for an operation as well as the result of the operation. For example, + (1,2) stands for the addition operation as well as the result, which is the number 3. KL-Database System b Data-Database System + 4

4

Transformation Knowledge

Domain Knowledge

Data Knonledge

KL-User 4

* Data-User

4 Fig. 2. Knowledge

requirement

of users and systems.

b

H.C. Chm, R.C. Goldstein/tnformation

2. C stands for an operation whose result can only be true or false. That is, C is a condition. 3. Ei stands for an entity type. 4. Ri stands for a relationship type. 5. Si stands for either an entity type or a relationship type. 6. ei, ri and si stand for entity or relationship instances. The type of the instance is given by the function TYPE(ei), TYPE(ri) and TYPE(si). 7. c stands for a value. This may be a value that is not from any instance or type. Operations can be divided into the following categories based on the kind of operands: nesting of operations, operations on values, operations on instances, operations on entity/relationship types and operations on instances and types. In addition, sub-categories can be derived based on the output of the operations. The main categories will now be described in detail. The descriptions show the kind of operations the interface can have, not necessarily the syntax used to specify them. Many different query languages can be designed to express these operations. 4.1.1. Nesting of operations An operation can be nested wherever an operand of the same kind is expected. Let us now define the various kinds of “things” that the system can accept or produce. At the knowledge level, a “thing” can be a list of values, a list of instances, or a list of types. A list can have a single member. A value may be of a certain datatype, such as date, integer or character. A value can be a single value or a list of values. The instances are of various entity/relationship types; and the types are entity or relationship types. For example, a set of supplier instances can be a thing; a single supplier instance can also be a thing. An operation always operates on certain things and produces other things. In general, an operation may be written as: O(XI,XZ ,..., X,) where Xi is a thing. The output of the operation is itself a thing of some kind. The nesting of operations is defined as follows: given 0(X1,X2,. .,X,) and O’(. . .) which is a thing of the same kind as Xj, then 0(X, 7x2,. .,Xj_ 1,O’(. . .),Xj+,,. .,X,) is also a valid operation. For example, if + (X,Y) operates on two numbers X and Y, and if * (A,@ is a number, then +(X, * (A&)) is also a valid operation. 4.1.2. Operations on values: O(cI,c2 ,..., c,) When we consider values, there is not much difference whether we consider them at the knowledge level or the logical data level. A “ 1” remains a “ 1” at either level. Therefore these operations are the usual ones applicable to values. The common operations are arithmetic operations,

661

and Sofbvare Technology 39 (1997) 657-668

string operations and other mathematical operations such as logarithms, exponentials, set operations on sets of values, (for example, membership, subset and set equality conditions), statistical operations on lists of values (for example, minimum, maximum, average, sum and count operations), and the common magnitude comparisons: ’ = ‘, ’ < ‘, ‘ > ‘, ’ < = ’ and ‘ > = ‘. Even more complex operations are possible, e.g., regression analysis or multivariate analysis. These operations are well known and need no further description. In addition, since values can be of certain abstract datatypes, operations specialized to these datatypes can be included. For example, if c, and c2 are dates, then c , - c2 can be defined to produce the number of days between the two dates. Note that operations on values can be applied to the values of attributes of entities and/or relationships. This requires the existence of the operation GetValue(attmame,s) which obtains the value of the specified attribute of the referenced entity or relationship. Attributes may also be multivalued and complex. Suitable operations, such as list comparison or list addition, can be defined on multi-valued attributes. In addition, operations on instances and types may return lists of values which can be the operands for aggregate value operations such as average and sum.

4.1.3. Operations on instances: Some examples are:

O(sI,s2,...,s,)

1. GetValue(attrname,s) returns the value of the attribute attrname of the instance s. 2. The condition that two instances are equal. This may be written as = (s,,s~). 3. The condition that a list of entity instances are related through a particular relationship instance. This condition may be written as: related(r,e I,e2,. . .). 4. The condition that a list of entity instances are related through some instance of a relationship type. The particular instance of that relationship type is not specified: related(R,e,,e2 ,... ). 5. The condition that two entity instances are related through an instance of some relationship type: related (ehe2).

6. The condition that two entity instances are related, possibly remotely, through any number of relationship instances and other entity instances: remote-related (e

183.

4.1.4. Operation on types: O(S1,S2.. . .,S,) An example where the result is a Boolean value is the subset condition: subset(S,,S2). The result of this type of operation can also be: 1. a value (e.g., Count(S), the count instances in a type; CountSubtypes( number of subtypes of S),

of the number of the count of the

H.C. Chan, R.C. Goldstein/Information and Software Technology 39 (1997) 657-668

662 2.

an instance (GetInstanceOf(S), instance from a type), 3. another type (union, intersection,

the extraction

of an

or subset operations).

For some complex operations, the output may be a list of values (GetAllValue(attrname,S) returns the attrname values of all instances in S), a list of instances, or another list of types. 4.1.5. Operation on instances and types: O(sI,sz,. . .,s,,,Si, SZ>...,SJ Examples

are:

1. A condition that an instance of one entity type is related to N (or more than N, or less than N) instances of another entity type. This may be written as: related(R,e ,,Ez, = ,A$ 2. A condition that an entity instance is is-a-linked to some instance of another entity type. This condition may be written as: is_a(e ,,Ez).

4.1.6. Completeness These categories of operations cover all the possible operations, by exhaustive partitioning based on possible inputs. In particular, they cover the common conditions and operations that are found in popular database query languages. For example, SQL provides these conditions/ operations: arithmetic operations + , - , * ,I; simple statistical functions COUNT, SUM, AVG, MAX, MIN; comparison operations = , < , > , < = , > = ; and the special operations SORT BY, GROUP BY, IN, EXISTS, DISTINCT and UNION. All these belong to one category: operations on values. Since a value may be a list of values, a relation (which may be thought of as a list of lists of values) is a value. It is to be expected that all SQL operations fall into the category of operations on values, since the relational model has no concept of entity or relationship instances or types. 4.1.7, Closure The closure of a query language refers to the ability of one query to accept as input the result of another query. In relational languages, the output of a query is a relation (however big or small) which can be used in subsequent queries. But note that not all relational operations will accept any relations as input: the union operator, for example, requires relations to be union compatible. SQL also treats some results as simply lists of values for use with the “IN” function. Closure implies that all operations produce “things’ ’ of the kinds already in existence. As long as the operations allowable in the KL inte$ace are restricted to those that produce “things” of the kinds already described, closure is maintained.

5. KQL The

a knowledge-level

previous

section

query language

describes

the

knowledge-level

interface in a way that is independent of any specific language. In that section, what is important is the knowledge of concepts and possible operations, not the exact mathematical notations. In contrast, this section describes the syntax of a specific language KQL for knowledge-level database manipulation. KQL has been developed, implemented and empirically tested for its effects on user-database interaction. The functionality of KQL includes model definition, instances update, and retrieval of instances. Particular care has been taken to design the syntax for the numerous conditions/operations so as to allow natural, unambiguous, and concise expression. Much past research [26-281 into query language usability has been considered in the design of KQL. For example, KQL has clear separation of outputs and conditions. This separation has worked very well in relational query languages such as SQL and QUEL. The similarity between English structures and ER concepts [29] is used to make KQL as natural and simple as possible. KQL provides primitive conditions as well as certain common combination conditions. These combination conditions allow more direct specification of conditions. Users need not decompose a common condition into many primitive conditions. 5.1. The general query format This section presents the general format of a KQL query. The use of specific conditions and operations will be described in detail later. A query is represented by the following BNF grammar. A pair of square brackets in the grammar means that the content inside the brackets is optional. Curly brackets mean that the content can be listed zero or more times. (query):: =

[(instance-clause)] (action-clause) [(report-formatting-clause)] [(where-clause)].

This is interpreted as follows: the system will find all possible instances defined in the instance-clause and meeting all conditions specified in the where-clause; the system then performs the actions specified in the action-clause in accordance with any additional instructions contained in the report-formatting-clause. To facilitate comprehension, the following description will depend more on examples than on formal BNF grammar. The instance-clause declares the instances. The user must provide a name for the instance and declare the type that the instance belongs to; e.g., s is a supplier. The action-clause can be one or more of: select-clause, delete-clause, insertclause or change-clause. This allows the user to perform all four functions in a single query. We concentrate here on the select-clause. Other types of action clauses are discussed in a later section. The select-clause can have one of the following forms, selecting either instances or attribute values from

H.C. Ghan, R.C. Goldstein/Information and Software

the instances. s is a supplier

or

select S

s is a supplier select s name.

The report-formatting-clause contains instructions for organizing the output of the query. The instructions may include the common sorting commands and more advanced commands such as sub-totals, totals, titles and even graphics. In general, any command that works on a single table can be included here. For example, a user may want to list suppliers and the parts they supply without duplicating the supplier names, as in Table 1. A sort operation based on supplier is needed, as the KL interface does not imply any specific order of retrieval. An operation to blank duplicate rows of supplier names is then required. Another operation to print titles in bold font is also needed. Though the report formatting commands will be very useful to users, they are not directly related to the knowledge-level aspects of KQL, and so will not be discussed further in this paper. The where-clause contains the conditions that the instances specified in the query must meet. (where-clause)::

=

WHERE (condition-list)

A condition-list is a logical combination of conditions. A condition can be one of the many conditions in the various categories described previously. It can include any appropriate operations and values; for example, 60 < max (employee age) can be a condition. The exact syntax can only be given for specific conditions and operations. Since a retrieval is an operation, a suitable condition can include a sub-query. 5.2. Fundamental

operations

Obviously, the number of possible operations for the knowledge-level interface is very large, if not infinite. Each query can be considered as a distinct operation. To place reasonable bounds on the actual language, we first consider the fundamental conditions in an ER model. Then we shall look at the operations that are provided in our comparison language SQL. The fundamental conditions in the ER model are defined as those that are inherent in the structure of the model. These include only the following: 1. that an instance

belongs to a certain entity type,

Supplier name

Part name

Beka Co.

A4 paper A3 paper Pencil Staple Paper clip

Paper Works Ltd

2. that an instance belongs to a certain relationship type, 3. that two (or more) entity instances are related through a certain relationship instance. In addition, the instances may have certain roles in the relationship, 4. that the value of a certain attribute of a certain instance equals a certain value. It is debatable whether cardinalities (that is, the lower and upper degrees) of a relationship should be counted as being contained in the ER model. Cardinalities may be more appropriately thought of as constraints on the behavior of the entity instances. Many descriptions of the ER model omit cardinalities, or simply use “one” or “many” without specific numbers. Inclusion of cardinalities will require an operation to count the number of entity instances (of a certain type) that are related (through a certain relationship) to another entity instance. Counting is usually not considered basic, e.g., relational algebra has no count operation. Thus, it seems reasonable to exclude cardinalities. Inheritance for is-a relationships is generally considered to be an extension to the ER model, and will be treated in a later section. We now define the syntax and semantics of each of the fundamental conditions.

52.1. Entity/relationship type conditions This is the condition that declares an instance to be of a certain entity/relationship type. The condition is stated in the instance-clause of the query. The syntax has been described in the previous section on the general format of a query. Some examples are: s is a supplier, sp is a supply, sp is a supplies. To reduce the number of instance-of declarations, the user may omit the instance declaration and use the actual entity type names, relationship type names or role names in the select-clause and where-clause wherever this will not be ambiguous. In this case, the type name will be understood as the name for an instance of that type.

5.2.2. Relationship existence condition This is a condition relating two entity instances and either a relationship instance or a role instance. The syntax is: (relationship-existence-condition)::

=

(entity-instance)[((role-name))] (related-to-instance) [((role-name))](entity-instance) (related-to-instance):: instance)

Table 1 An example report

663

Technology39 (1997) 657-668

=

(relationship-instance)](role-

The first role-name refers to the role of the first entity instance in the relationship, the second role-name refers to the role of the second entity instance in the relationship. In most cases, these role-names can be omitted without ambiguity. In rare cases, they are needed. One such case is when an entity type plays more than one role in the same relationship.

H.C. Chan, R.C. Goldstein/Information and Software Technology 39 (1997) 657-668

664

A relationship existence condition is true if the two entity instances are related through the relationship instance, with the stated roles. It is false otherwise. For example, it is false if the two entity instances are related through this relationship instance but with different roles, or if they are related through another relationship instance, or if one of them is related through this relationship instance to some other entity instance. Example queries with relationship existence conditions are: s is an instance of supplier, p is an instance of part, sp is an instance of supply select s name, p name, sp price where s sp p.

ii)

s is an instance of supplier, p is an instance of part, sp is an instance of supplies select s name, p name, sp price where s sp p.

Note that “supply” in the first example is the name of a relationship, whereas “supplies” in the second is a role. 52.3. Equality comparison of attribute values This compares an attribute value with another attribute value or some other value. Examples are: s city = London and s city = s2 city. This concludes the description of the fundamental conditions. 5.3. Additional

spec$c

operations

KQL was developed primarily as a “proof-of-concept” for the Knowledge Level. It demonstrates that such languages can be implemented and is complete enough to allow meaningful empirical evaluation. However, it was not considered necessary or useful to implement all of the possible KL operations. It is envisaged that a complete KL database system would be built on top of an existing relational system and would interface with the relational system through SQL. Since we are using SQL as a benchmark language, we will consider the additional operations that are available in SQL: Arithmetic operations on attribute values. These include addition, subtraction, multiplication, and division. These operations are applied to attribute values and/or other values provided by the user. An example of a query with arithmetic is:

number, and the average price at which it is supplied by its suppliers is: select part number, part name, each part) where supplier supply part. l

avg(supply

Miscellaneous other functions that are available in SQL, such as IN, EXISTS and UNION. SQL does not provide explicit intersection and difference functions, which can be built by using the EXISTS function. However for ease of use, a fully implemented KQL should provide explicit intersection and difference functions so that users can use these if they choose not to use the EXISTS function. There are two kinds of UNION function in KQL. One is the union of sets of entity instances, where the instances must all be of the same entity type. The second is the union of tables of attribute values. This is similar to the union of two relations. The KQL syntax for these functions is similar to SQL’s.

5.4. Advance concepts In addition to the features described earlier, the KQL language explicitly incorporates inclusion (is-a) relationships and some conditions that are combinations of the specific conditions described previously and that make certain queries easier to write. 5.4.1. Inclusion relationships Some entity types may be considered to be subtypes of other entity types. For example, the ENGINEER entity type in Fig. 3 is a subtype of the EMPLOYEE entity type. It is usual to say that ENGINEER is is-a related to EMPLOYEE, or more simply that ENGINEER is-a EMPLOYEE. The is-a relationship means that an instance of the subentity can exist only if the corresponding instance of the superentity also exists. The critical property of an is-a relationship is attribute inheritance. The is-a relationship has no attributes of its own. Attribute inheritance means that attributes of the superentity type - EMPLOYEE in this case - also apply to the subentity type, ENGINEER. When formulating queries, the user can refer to attributes of the superentity as if they belonged directly to the subentity. For example, the following are

ei is an instance of employee, e2 is an instance of employee select et name where et salary = 0.5 * e2 salary, e2 name = ‘MARY’ address

More comparison conditions: ‘ < ’ and ‘ > ‘. The equality ‘ = ’ comparison described under the fundamental conditions will be extended. The usual combinations ‘ < = ’ and ‘ > = ’ will also be included. Simple statistical operations: COUNT, SUM, AVG, MAX and MIN. These can be specified with subgroupings. An example query to get a part’s name and

price for

profession

rank Fig. 3. Is-a linked entities.

H.C. Chart, R.C. Goldstein/information and Sofrware Technology 39 (1997) 657-668 ii)

valid queries: select engineer

ii)

iii)

name

where engineer number = 12354 select engineer name where engineer profession = electronics

5.4.2. Combination conditions In practice, it may be necessary to determine whether two entity instances are related without necessarily about the particular relationship concerned being instance that connects them. It is possible to express this condition and its negation by using a combination of the relationship-existence condition and the EXIST condition. However, in KQL, this can be expressed much more easily of part

This condition is true if the two entity instances are related through some instance of the relationship type. It is false if there is no instance of the relationship type that relates the two entity instances. Adding the keyword -related to the relationship name, as in supply-related, changes that from a specific instance to a type. This condition is extended to allow easier writing of more complex conditions which are used frequently; for example, in the query “Find students who take more than 4 courses”. It allows the counting of instances to be done without explicitly using the COUNT operation. Some examples of queries with this combination condition are: select supplier name where supplier supply-related suppliers who supply more than 5 parts.)

name do not name supply

where supply where every

no part (Find all part (Find

> 5 part (Find

Corresponding to the usual database definition process for relational DBMSs, KQL allows the user to define her world of entities, relationships and is-a relationships. The language also allows modifications to the entity/relationship types. For example, Fig. 4 shows a small ER model about orders, customers and products. An order is placed by a customer, and it can include many products. The creation of a new entity instance is very simple, requiring only the values of the attributes. The creation of a relationship instance involves specifying (retrieving) the participating entity instances. KQL also allows for the deletion of instances using the delete-clause in the action-clause of the general query format. KQL automatically maintains the ER knowledge constraints. For example, the small is-a hierarchy in Fig. 3 could be implemented as the following relations: employee (number, name, salary, address) engineer (number, profession) manager (number, rank) If overlapping is allowed, there could be a manager X who is also an engineer. Information on this employee will appear in all three relations. When employee X is to be deleted, a relational user must delete tuples from all three relations. In KQL, the single real-life event (e.g., an employee resigns) can be updated to the database with one single delete of employee with some attribute values to identify the particular employee. The system will automatically delete the lower connected entities in the hierarchy, to preserve the constraint that a subentity instance cannot exist without existing at its superentity types. In addition, if these entities participate in relationships, the relevant relationship instances will also be automatically deleted, to preserve the constraint that a relationship instance cannot exist without the existence of its participating entity instances. 5.6. Implementation Prototypes of KQL have been implemented in Prolog and in C. These accept KQL queries, and translate them into SQL for execution by a relational database system. Results obtained from the DBMS are translated back into KQL terms for the user. The implementation in Prolog in based

product number name

supplier supply-related any parts.) supplier supply-related part.)

5.5. KQL definitions and updates

In addition to this normal inheritance, full implementation of entity identity in KQL makes it possible for the system to perform multi-directional references of attributes and relationships. In other words, it is possible to refer directly to an instance’s attribute or relationship anywhere in the is-a hierarchy, such as manager rank or employee profession. Any ambiguity is resolved with clarification from the user. The addition of is-a relationships to the language creates the need for two additional conditions: a given entity instance is is-a related to some instance of a specified entity type (e is-a E), and equality of two entity instances (et = e2). (If e 1is an instance of ENGINEER, and e2 an instance of MANAGER, et = e2 means that they refer to the same person.)

s is an instance of supplier, p is an instance select s name, p name where s supply-related p

select supplier suppliers who select supplier suppliers who

665

number date

Fig. 4. An order ER model.

NY price

number name

H.C. Ghan, R.C.

666

Goldsrein/lnformarion and Software Technology 39 (1997) 657-668

on the availability of SQL in Prolog. All the conditions described for KQL have been successfully implemented in C with embedded SQL on top of INGRES. Thus KQL achieves the many advantages of using “commercialrelational DBMSs. It has also strength”, industry-standard been implemented in Microsoft Windows environment. There is no doubt that the general KL interface defined in Section 4.1 is relationally complete. In fact, it is much more powerful. The implemented KQL language is also relationally complete. There are five basic relational algebra operations: select, project, product, union, and difference. The other three common operations of join, intersect and divide can be defined based on the five basic operators. Each of the five operators has its equivalent in KQL, thus showing that KQL is relationally complete [30].

6. Discussion The first subsection of this section presents empirical results to show that users actually find the knowledgelevel interface different from logical-level interface. The empirical studies cover the major tasks of retrieval and design. The results show that users communicating in terms of application knowledge perform definitely better than users communicating in terms of logical data. The second subsection discusses KL interface and ER languages. It shows that not all ER languages qualify as KL interfaces. 6.1. Empirical

results

An empirical study [lo] of KQL and SQL for database retrievals showed that KQL users performed significantly better than SQL users. The experiment involved 47 firstyear computer science undergraduates. These were divided into an EWKQL group (24) and a relational/SQL group (23). After training sessions of about an hour to an hour and a half, the students wrote 10 queries using either KQL or SQL. The results showed that the ER group performed better on all three measures of query accuracy, time taken and user confidence. The ER group scored 88.8% accuracy, 82.6% confidence and took 14.9 min compared to 64.4% accuracy, 71.2% confidence and 42.8 min for the relational group. In other words, KQL users were 36% more accurate, 16% more confident, and wrote the queries in 35% of the time taken by SQL users. The differences were statistically significant with p values of less than 0.003. Two other experiments compared ER and relational for the task of database design [31,32]. These showed better user design with the ER models than the relational model. Six database design tasks were tested with 42 graduate MIS evidence is in favor of students [31]. “The overwhelming the ER model” ([31], p. 137). The other experiment [321 involved 56 students. The results suggest that end-users can

learn the ER model easier than the relational model. In repeated tests of data model design, the ER group performed generally better than the relational group in terms of modeling accuracy and time taken. 6.2. Comparison languages

of knowledge-level

inter&ace and ER

Since the KL interface is based on the ER model, it is reasonable to wonder if all ER languages are examples of the KL interface? The answer is clearly “No!“. The KL interface requires that user-database communication be at the knowledge level. It is not merely the communication of ER model constructs, which can also be done at the logical data level. Whether a particular ER language is an example of the KL interface can be judged by evaluating it against the three principles in Section 3.4. Do the ER languages use only domain knowledge? Some ER languages require the specification of logical data, e.g., users are required to define foreign keys for relationships [33], users need to write relational algebra expressions [34], or users need to understand implementational details in order to understand ER operations [35]. For use at the knowledge interface, the user must only specify that a relationship exists between certain entities. The system decides whether to include the key attributes of the entities, to use pointers, or to use some other identification methods such as system surrogates. Whatever the method used, it is internal to the system. Are the ER languages free of implementational data structure restrictions? Many are not, some impose ordering on the entities participating in a relationship [36,37], and some impose ordering on an entity’s attributes [38]. Incidentally, these languages will not provide logical data independence. If the orders of the entities or attributes change, the queries will have to be changed. Do the ER languages avoid operations not required by the domain? In [33], entity and relationships are explicitly linked by a link operation, e.g., link supplier, link supply, link part. This is not a knowledge-level operation as supplier supply part expresses the same content. Entity identity is not fully supported in some ER languages, e.g., the comparison of key values instead of direct comparison of entity instances [33], i.e., a condition of supplier,. number = supplierz.number instead of the more natural = supplier*. Inheritance within is-a entity supplier 1 hierarchies is not supported in some languages [39,40]. Even though they allow is-a relationships, the inheritance has to be explicitly performed by users. Some languages include very complex operations which are not close to operation the domain, e.g., the add_relationship_union [34], the transformation of graphical ER model to hierarchical diagrams [41], and the transformation of a list of entities into an attribute of another entity [42]. The idea of a database system as an agent is important. An agent can understand the meanings of entities and

H.C. Ghan, R.C. Goldstein/Information and Software Technology 39 (1997) 657-668

relationships, including special relationships such as inclusion. It can therefore maintain ER integrity on its own, and can perform inheritance automatically. To qualify as a knowledge-level language, any language must be able to perform integrity maintenance and inheritance by itself.

7. Conclusion A knowledge-level view of database systems is proposed, whereby database systems behave as agents who know the application domains, and interact with users using only domain knowledge. This is an enhancement to traditional views of database systems as stores of logical or physical data. The knowledge-level user-database interaction consists of application knowledge and nothing of the data, such as pointers and foreign keys, within the database system. It is also shown that a knowledge-level interaction has many advantages over the logical data-level interaction. These advantages include: the system provides entity identity, the system performs inheritance automatically, the system maintains ER integrity, the system performs follow-up updates, and the user needs to know less and perform fewer data manipulation operations. In order to develop the knowledge-level interface, the concepts of the ER model are chosen as the knowledge elements for user-database communication. The ER model is chosen as it is the most widely used and accepted model. With its many extensions, it also has many general real-world concepts, such as is-a relationship, part-of relationship, category and various forms of aggregation. Based on the ER concepts, we identify the categories of operations that can be done at the knowledge level, regardless of syntax and physical systems. The categories of operations identified for the knowledge-level interface formed the basis for the design of an actual knowledge-level query language - KQL. This language has been implemented on top of a relational DBMS. Empirical studies comparing KQL with SQL have confirmed the conclusions from theoretical analysis that KL-users will perform better than Data-users. The analyses and empirical studies, in showing that KL interfaces are better than logical-level interfaces, have also demonstrated that ER languages used appropriately at the knowledge level are better than relational languages. Let us conclude with the emphasis that this article is on the knowledge-level view of database systems, and not on any specific models or languages. The contribution of this research lies in defining an ideal level of user-database interaction and in demonstrating, through the implementation and testing of KQL, that this level is both attainable and valuable.

References [l] A. Motro, Constructing queries from Tokens, Proceedings SIGMOD, 1986, SIGMOD Record, IS (2) (June 1986).

of ACM

667

[2] C. Welty, D.W. Stemple, Human factors comparison of a procedural and a nonprocedural query language, ACM Transactions on Database Systems 6 (4) (198 1) 626-649. [3] Marc Junet, Design and implementation of an extended entityrelationship data base management system (ECRINS/86), in: S. Spaccapietra (Ed.), Entity-Relationship Approach, Elsevier Science Publishers, 1987. [4] G. Poonen, CLEAR: a conceptual language for entities and relationships, in: W.W. Chu, P.P. Chen (Eds.), Centralized and Distributed Data Base Systems, IEEE Computer Society, 1979, pp. 194-215. [5] M. Templeton, J. Burger. Considerations for the development of natural language interfaces to database management systems, in: L. Bolt, M. Jarke (Eds.), Cooperative Interfaces to Information Systems, Springer-Verlag, Germany, 1986. [6] C. Batini, S. Ceri, S.B. Navathe, Conceptual Database Design, An Entity-Relationship Approach, The Benjamin/Cummings Publishing Company, Inc., USA, 1992. [7] R. Elmasri, S.B. Navathe, Fundamentals of Database Systems, Addison Wesley, 1989. [8] J.G. Hughes, Object-oriented Databases, Prentice-Hall, UK, 1991. [9] T.J. Teorey. D. Yang, J.P. Fry, A logical design methodology for relational databases using the extended entity-relationship model, Computing Surveys, 18 (2) (June 1986). [ 101 H.C. Chan. K.K. Wei, K.L. Siau, User-database interface: the effect of abstraction levels on query Performance, MIS Quarterly 17 (4) (1993) 441-464. [ 11 I D. Greenblatt, J. Waxman, A study of three database query languages, in: B. Shneiderman (Ed.), Databases: Improving Usability and Representativeness, Academic Press, New York, 1978. [ 121 A. Newell, The knowledge level, Artificial Intelligence 18 (I 982) 87127. [ 13) A. Newell, Unified Theories of Cognition, Harvard University Press, Cambridge, MA, USA, 1990. [ 141 Peter Gray, Logic, Algebra and Databases, Ellis Horwood Ltd, UK, 1984. [I.51 Chao-Chih Yang, Relational Databases, Prentice-Hall, 1986. [I61 R.J. Brachman, H.J. Levesque, in: Larry Kerschberg (Ed.), What Makes a Knowledge Base Knowledgeable? A View of Databases from the Knowledge Level, Expert Database Systems, The Benjamin/Cummings Publishing Company, Inc., USA, 1986. [ 171 R.J. Abbott, Knowledge abstraction, Communications of the ACM 30 (8) (1987) 664-671. [18] M.L. Brodie, in: M.L. Brodie, J. Mylopoulos, J.W. Schmidt (Eds.), On the Development of Data Models, On Conceptual Modelling, Springer-Verlag, 1984. [ 191 A. Albano, L. Cardelli, R. Orsini, Galileo: a strongly typed, interactive conceptual language. ACM Transactions on Database Systems, June 1985. [20] M. Capaccioli, M.E. Occhinto, A workbench for conceptual design in Galileo, in: A. Albano, V. de Antonellis, A. di Leva (Eds.), Computeraided Database Design, Elsevier Science Publishers, The Netherlands, 1985. [21] O.M. Nierstrasz, What is the ‘object’ in object-oriented programming, in: D.C. Tsichritzis (Ed.), Objects and Things, University of Geneve, March 1987, pp. l-13. [22] D.A. Jardine, Semantic agreement and the communication of knowledge, in: T.B. Steel Jr., R. Meersman (Eds.), Data Semantics (DS-I), Elsevier Science Publishers, The Netherlands, 1985. [23] L.I. Brady, C.N.G. Dampney, in: S. Spaccapietra (Ed.), The Semantics of Relational Database Functions, Entity-Relationship Approach, Elsevier Science Publishers, 1987. [24] P.P. Chen, The entity-relationship model: towards a unified view of data, ACM Trans. Database Systems 1 (1) (1976) 9-36. [25] H.A. Simon, Administrative Behavior, MacMillian, New York, 1957. [26] C.J. Date, Some principles of good language design, Relational Database, Selected Writings by C.J. Date, Addison Wesley, USA, 1986.

668

H.C. Ghan, R.C. Goldstein/information

[27] S.L. Ehrenreich, Query languages: design recommendations derived from the human factors literature, Human Factors 23 (1981) 709-725. [28] M.M. Zloof, Design aspects of the query-by-example data base manipulation language, in: B. Shneiderman (Ed.), Databases: Improving Usability and Representativeness, Academic Press, New York, 1978. [29] P.P. Chen, English sentence structure and entity relationship diagrams, Information Sciences, Vol. 29, Elsevier Science Pub. Co., New York, 1983, pp. 127-149. [30] H.C. Chan, A knowledge level user interface using the entity relationship model, Ph.D. Dissertation, The University of British Columbia, Canada, 1989. [31] D. Batra, J.A. Hoffer, R.P. Bostrom, Comparing representations with relational and EER models, Communications of the ACM 33 (2) (1990) 126-139. [32] S.L. Jarvenpaa, J.J. Machesky, End user learning behavior in data analysis and data modeling tools, International Conference on Information Systems, USA, 1986, pp. 152- 167. [33] A. Dogac, F. Eyupoglu, E. Arkun, in: S. Spaccapietra (Ed.), VERS A Vector Based Entity Relationship Database Management System, Entity-Relationship Approach, Elsevier Science Publishers, 1987. [34] D.M. Campbell, D.W. Embley, B. Czejdo, A relationally complete query language for an entity relationship model, The 4th International Conference on Entity-Relationship Approach, IEEE Computer Society Press, 1985. [35] K. Subieta, M. Missala, Semantics of query languages for the entity relationship model, in: S. Spaccapietra (Ed.), Entity-Relationship Approach, Elsevier Science Publishers, 1987.

and Software Technology 39 (1997) 657-668 [36] H.Y. Hwang, U. Dayal, Using the entity relationship model for implementing multi-model database systems, in: P.P. Chen (Ed.), Entity-Relationship Approach to Information Modeling and Analysis, North Holland, 1981, pp. 235-256. [37] V.M. Markowitz, Y. Raz, ERROL: an entity relationship, roleoriented, query language, in: C.G. Davis, S. Jajodia, P.A.B. Ng, R.T. Yah (Eds.), Entity-Relationship Approach to Software Engineering, North Holland, 1983, pp. 329-345. [38] R. Nakano, Integrity checking in a logic oriented ER model, in: C.G. Davis, S. Jajodia, P.A.B. Ng, R.T. Yah (Eds.), Entity-Relationship Approach to Software Engineering, North Holland, 1983, pp. 551564. [39] W. Roesner, DESPATH: an entity relationship manipulation language, The 4th International Conference on Entity-Relationship Approach, IEEE Computer Society Press, 1985. [40] M. Schneider, C. Trepied, A graphical query language for semantic data models, in: J.I. DeGross, J.C. Henderson, B.R. Konsynski (Eds.), Proceedings of the Tenth International Conference on Information Systems, Boston, MA, USA, 4-6 December 1989, pp. 153-164. [41] R.A. Elmasri, J.A. Larson, A graphical query facility for ER databases, The 4th International Conference on Entity-Relationship Approach, IEEE Computer Society Press, 1985, pp. 236-245. [42] C. Parent, S. Spaccapietra, Enhancing the operational semantics of the entity-relationship model, in: T.B. Steel Jr., R. Meersman (Eds.), Data Semantics (DS- 1), Elsevier Science Publishers, The Netherlands, 1985.