| Over the last decade, many new facilities that can be used in combination to express complex constraints in databases have been implemented. These include more powerful integrity constraints, triggers (active rules), materialized views, user-defined types and functions, and object-oriented features.; In this thesis, we explore the functionality available in IBM DB2, Oracle, and Microsoft SQL Server 2000 to express and handle semantic integrity. We show that, due to the increased complexity, it is often hard for a database designer to know how to best express constraints and hard for the database system itself to recognize when certain semantic properties hold and when they do not. We demonstrate several semantic “bugs” that we have found in the aforementioned systems.; We present a database designer's waterfall model for integrity showing how the existing facilities are best used. We also recommend changes and new constructs for database systems that would alleviate some of the current problems and make the definition of semantic integrity simpler. In particular, we propose denial views and foreign keys on views as a better solution for representing semantic information. We also propose a semantic query facility to allow one to query about a database's semantic properties.; Furthermore, we describe how program objects can be mapped to relational databases successfully so that all dependencies and constraints are preserved and reflected. We also show how assertions—inter-table constraints—proposed in the SQL standards (but not implemented yet by any of the commercial systems) could be implemented by using triggers internally. |
