AN ENTITY-RELATIONSHIP BASED DATABASE MANAGEMENT SYSTEM

Abstract

A three level schema architecture Entity-Relationship (ER) based database management system along the ANSI/X3/SPARC framework [ANSI75] was proposed in [Ling88]. The conceptual schema is represented by a normal form ER diagram. The internal schema is represented by a set of normal form not-necessarily normalized relations (or nested relations). The external schema is represented by Entity-Relationship diagrams, not necessarily in normal form. In our ER based DBMS, we consider features unique to the ER approach such as multivalued and composite attributes, weak entity types, recursive relationship sets and special relationship sets such as ISA, UNION, INTERSECT etc. This thesis first proposes a schema definition language to specify the conceptual schema in an ER based database management system. We also define a view definition language to specify user views and their mappings to the conceptual schema, A data manipulation language, ER Calculus (ERC) which is based on ER Logic, is also proposed for the retrieval and update of data. Next, we describe the use of logic as a tool for implementing the various ER database concepts. We adopt an approach based on nested relations to represent an ER database internally in Prolog. The storage and manipulation of multivalued attributes, weak entity types and relationship sets are taken into consideration, Algorithms to automatically generate the external-to-conceptual and conceptual-to-internal mappings are also developed. With these mapping rules, retrievals from a view can always be mapped into the equivalent retrievals from the conceptual schema. A mapping is also required to translate view updates into the corresponding updates on the conceptual schema. However, such a mapping does not always exist, and even when it does, it may not be unique. The traditional problem of updating relational databases through views is an important practical problem that has attracted much interest. One of the problems in updating through views lies in determining whether a given view modification can be correctly translated by the system. The ability to update views in many DBMS systems is usually severely limited and extremely ad hoc. On the other hand, we develop a theory within the framework of the ER approach that characterizes the conditions under which there exist mappings from view updates into conceptual schema updates. This theory gives a systematic solution to the problem of view update in ER based DBMS where views are modelled by ER diagrams and is the main contribution of this thesis. Concepts such as virtual updates and three types of insertability for view relationship sets are introduced. Based on the theory, we developed two algorithms, namely the View Updatability Algorithm and the View Update Translation Algorithm. Finally, we put together all the ideas presented and describe the design and implementation of a graphical ER based DBMS Workbench. This flexible and user-friendly graphical tool allows the design of conceptual schemas, the construction of external views, and the formulation of queries and updates. Here, we propose a fundamentally two dimensional, graphics-based data manipulation language (DML) for the ER approach.