A logical design methodology for relational databases using the extended entity-relationship model

A database design methodology is defined for the design of large relational databases. First, the data requirements are conceptualized using an extended entity-relationship model, with the extensions being additional semantics such as ternary relationships, optional relationships, and the generalization abstraction. The extended entity-relationship model is then decomposed according to a set of basic entity-relationship constructs, and these are transformed into candidate relations. A set of basic transformations has been developed for the three types of relations: entity relations, extended entity relations, and relationship relations. Candidate relations are further analyzed and modified to attain the highest degree of normalization desired. The methodology produces database designs that are not only accurate representations of reality, but flexible enough to accommodate future processing requirements. It also reduces the number of data dependencies that must be analyzed, using the extended ER model conceptualization, and maintains data integrity through normalization. This approach can be implemented manually or in a simple software package as long as a "good" solution is acceptable and absolute optimality is not required.

[1]  Peter P. Chen,et al.  A Decomposition of Relations Using the Entity-Relationship Approach , 1981, ER.

[2]  Shamkant B. Navathe,et al.  A Methodology for View Inegration in Logical Database Design , 1982, VLDB.

[3]  Y. Edmund Lien,et al.  Hierarchical schemata for relational databases , 1981, TODS.

[4]  Henry C. Smith,et al.  Database design: composing fully normalized tables from a rigorous dependency diagram , 1985, CACM.

[5]  David Scott Warren,et al.  Formal semantics for time in databases , 1982, TODS.

[6]  Carlo Zaniolo,et al.  On the design of relational database schemata , 1981, TODS.

[7]  William Kent.  Consequences of assuming a universal relation , 1981, TODS.

[8]  Maurizio Lenzerini,et al.  Cardinality Constraints in the Entity-Relationship Model , 1983, ER.

[9]  Henri Briand,et al.  Expert System for Translating an E-R Diagram into Databases , 1985, International Conference on Conceptual Modeling.

[10]  Sushil Jajodia,et al.  On the Representation of Relational Structures by Entity-Relationship Diagrams , 1983, ER.

[11]  James Martin,et al.  Strategic data-planning methodologies , 1982 .

[12]  E. F. Codd,et al.  Recent Investigations in Relational Data Base Systems , 1974, ACM Pacific.

[13]  W. C. McGee,et al.  A Contribution to the Study of Data Equivalence , 1974, IFIP Working Conference Data Base Management.

[14]  Norman Weizer,et al.  Sierra: where will it lead? , 1985 .

[15]  Toby J. Teorey,et al.  A practical approach to transforming extended ER diagrams into the relational model , 1987, Inf. Sci..

[16]  Hirotaka Sakai.  Entity-Relationship Approach to Logical Database Design , 1983, ER.

[17]  Philip A. Bernstein,et al.  Synthesizing third normal form relations from functional dependencies , 1976, TODS.

[18]  Stephen Ferg.  Modelling the Time Dimension in an Entity-Relationship Diagram , 1985, ER.

[19]  Toby J. Teorey,et al.  Design of Database Structures , 1982 .

[20]  Maurizio Lenzerini,et al.  INCOD: A System for Conceptual Design of Data and Transactions in the Entity-Relationship Model , 1981, ER.

[21]  Peter Scheuermann,et al.  Abstraction Capabilities and Invariant Properties Modelling within the Entity-Relationship Approach , 1979, ER.

[22]  Tok Wang Ling.  A Normal Form For Entity-Relationship Diagrams , 1985, ER.

[23]  I. T. Hawryszkiewycz,et al.  Database analysis and design , 1984 .

[24]  Ronald Fagin,et al.  Multivalued dependencies and a new normal form for relational databases , 1977, TODS.

[25]  Laurian M. Chirica,et al.  The entity-relationship model: toward a unified view of data , 1975, SIGF.

[26]  C. J. Date.  A guide to DB2 , 1984 .

[27]  Antonio Di Leva,et al.  Logical Design in Codasyl and Relational Environment , 1983, Methodology and Tools for Data Base Design.

[28]  William Kent.  Fact-based data analysis and design , 1984, J. Syst. Softw..

[29]  Ole Oren.  Integrity Constraints in the Conceptual Schema Language SYSDOC , 1985, ER.

[30]  D. A Jardine,et al.  Concepts and terminology for the conceptual schema and the information base , 1984 .

[31]  Michael L. Brodie,et al.  A Prototyping Approach to Database Applications Development , 1984, IEEE Database Eng. Bull..

[32]  Diane C. P. Smith,et al.  Database abstractions: aggregation and generalization , 1977, TODS.

[33]  Neil W. Webre.  An Extended Entity-Relationship Model and Its Use on a Defense Project , 1981, ER.

[34]  ChenPeter Pin-Shan.  The entity-relationship modeltoward a unified view of data , 1976 .

[35]  Roger King,et al.  Applying a Semantic Data Model , 1979, ER.

[36]  James A. Larson,et al.  Integrating User Views in Database Design , 1986, Computer.

[37]  Ramez Elmasri,et al.  Relationship Merging in Schema Integration , 1984, VLDB.

[38]  Charles W. Bachman,et al.  The Role Concept in Data Models , 1977, VLDB.

[39]  Ramez Elmasri,et al.  Data model integration using the structural model , 1979, SIGMOD '79.

[40]  Giampio Bracchi,et al.  Binary Logical Associations in Data Modelling , 1976, IFIP Working Conference on Modelling in Data Base Management Systems.

[41]  Shamkant B. Navathe,et al.  A Methodology for Database Schema Mapping from Extended Entity-Relationship Models into the Hierarchical Model , 1983, International Conference on Conceptual Modeling.

[42]  Ramez Elmasri,et al.  The Category Concept: An Extension to the Entity-Relationship Model , 1985, Data Knowl. Eng..

[43]  David Maier,et al.  The Theory of Relational Databases , 1983 .

[44]  George Pangalos,et al.  Design of relational database schemas , 1988 .

[45]  James Martin.  Strategic Data Planning Method , 1982 .

[46]  S. Bing Yao.  Principles of Database Design: Logical Organizations , 1985 .

[47]  Jeffrey D. Ullman,et al.  Principles of Database Systems , 1980 .

[48]  James Martin.  Managing the data-base environment , 1983 .

[49]  Randy H. Katz,et al.  Logical Design and Schema Conversion for Relational and DBTG Databases , 1979, ER.

[50]  C. J. Date.  An Introduction to Database Systems, Volume I, 5th Edition , 1986 .

[51]  Peter P. Chen.  The entity-relationship model: toward a unified view of data , 1975, VLDB '75.

[52]  Catriel Beeri,et al.  A Sophisticate's Introduction to Database Normalization Theory , 1978, VLDB.

[53]  Sushil Jajodia,et al.  Translation of entity-relationship diagrams into relational structures , 1984, J. Syst. Softw..

[54]  E. F. Codd,et al.  A relational model of data for large shared data banks , 1970, CACM.