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

ABSTRACT

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.

REFERENCES

• 1: ABRIAL, J. 1974. Data semantics. In Data Base Management, Proceedings of the IFIP TC2 Conference (Cargese, Corsica). North-Holland, Amsterdam.
• 2: Paolo Atzeni , Carlo Batini , Maurizio Lenzerini , Fulvio Villanelli, INCOD: A System for Conceptual Design of Data and Transactions in the Entity-Relationship Model, Proceedings of the Second International Conference on the Entity-Relationship Approach to Information Modeling and Analysis, p.375-410, October 12-14, 1981
• 3: BACHMAN, C. 1977. The role concept in data models. In Proceedings of the 3rd International Conference on Very Large Data Bases (Tokyo, Oct. 6-8). IEEE, New York, pp. 464-476.
• 4: BEERI, C., BERNSTEIN, P., AND GOODMAN, N. 1978. A sophisticate's introduction to database normalization theory. In Proceedings of the 4th International Conference on Very Large Data Bases (Berlin, Sept. 13-15). IEEE, New York, pp. 113-124.
• 5: Philip A. Bernstein, Synthesizing third normal form relations from functional dependencies, ACM Transactions on Database Systems (TODS), v.1 n.4, p.277-298, Dec. 1976 [doi>10.1145/320493.320489]
• 6: BERTAINA, P., DILEVA, A., AND GIOLITO, P. 1983. Logical design in CODASYL and relational environments. In Methodology and Tools for Data Base Design, S. Ceri, Ed. North-Holland, Amsterdam, pp. 85-117.
• 7: BRACCHI, G., PAOLINI, P., AND PELAGATTI, G. 1976. Binary logical associations in data toodelling. In Modelling in Data Base Management Systems, Proceedings of the IFIP TC2 Conference (Freudenstadt, West Germany). North-Holland, Amsterdam.
• 8: H. Briand , Henri Habrias , J.-F. Hue , Y. Simon, Expert System for Translating an E-R Diagram into Databases, Proceedings of the Fourth International Conference on Entity-Relationship Approach, p.199-206, October 29-30, 1985
• 9: BUBENKO, J. 1977. The temporal dimension in information modelling. In Architecture and Models in Data Base Management Systems, G. Nijssen, Ed. North-Holland, Amsterdam.
• 10: Peter Pin-Shan Chen, The entity-relationship model—toward a unified view of data, ACM Transactions on Database Systems (TODS), v.1 n.1, p.9-36, March 1976 [doi>10.1145/320434.320440]
• 11: Ilchoo Chung , Fumio Nakamura , Peter P. Chen, A Decomposition of Relations Using the Entity-Relationship Approach, Proceedings of the Second International Conference on the Entity-Relationship Approach to Information Modeling and Analysis, p.149-171, October 12-14, 1981
• 12: James Clifford , David S. Warren, Formal semantics for time in databases, ACM Transactions on Database Systems (TODS), v.8 n.2, p.214-254, June 1983 [doi>10.1145/319983.319986]
• 13: E. F. Codd, A relational model of data for large shared data banks, Communications of the ACM, v.13 n.6, p.377-387, June 1970 [doi>10.1145/362384.362685]
• 14: COOD, E. 1974. Recent investigations into relational data base systems. In Proceedings of the IFIP Congress. North-Holland, Amsterdam.
• 15: Chris Date, A Guide to DB2, Addison-Wesley Longman Publishing Co., Inc., Boston, MA, 1984
• 16: C. J. Date, An introduction to database systems: vol. I (4th ed.), Addison-Wesley Longman Publishing Co., Inc., Boston, MA, 1986
• 17: Ramez El-Masri , Gio Wiederhold, Data model integration using the structural model, Proceedings of the 1979 ACM SIGMOD international conference on Management of data, May 30-June 01, 1979, Boston, Massachusetts [doi>10.1145/582095.582127]
• 18: R. Elmasri , J. Weeldreyer , A. Hevner, The category concept: an extension to the entity-relationship model, Data & Knowledge Engineering, v.1 n.1, p.75-116, April 1985 [doi>10.1016/0169-023X(85)90027-8]
• 19: Ronald Fagin, Multivalued dependencies and a new normal form for relational databases, ACM Transactions on Database Systems (TODS), v.2 n.3, p.262-278, Sept. 1977 [doi>10.1145/320557.320571]
• 20: Stephen Ferg, Modelling the Time Dimension in an Entity-Relationship Diagram, Proceedings of the Fourth International Conference on Entity-Relationship Approach, p.280-286, October 29-30, 1985
• 21: I. T. Hawryszkiewycz, Database analysis and design, SRA School Group, 1986
• 22: D. R. Howe, Data Analysis for Data Base Design, Routledge, Chapman & Hall, New York, NY, 1989
• 23: ISO 1982. Concepts and terminology for the conceptual schema and the information base. J. van Griethuysen, Ed. ISO/TC97/SC5/WG3-N695 Report. ANSI, New York, 180 pp.
• 24: JAJODIA, S., AND NG, P. 1983. On the representation of relational structures by entity-relationship Diagrams. In The Entity-Relationship Approach to Software Engineering, G. C. Davis et al., Eds. Elsevier North-Holland, New York, pp. 223-248.
• 25: Sushil Jajodia , Peter A. Ng, Translation of entity-relationship diagrams into relational structures, Journal of Systems and Software, v.4 n.2-3, p.123-133, July 1984 [doi>10.1016/0164-1212(84)90003-7]
• 26: W. Kent, Consequences of assuming a universal relation, ACM Transactions on Database Systems (TODS), v.6 n.4, p.539-556, Dec. 1981 [doi>10.1145/319628.319630]
• 27: W Kent, Fact-based data analysis and design, Journal of Systems and Software, v.4 n.2-3, p.99-121, July 1984 [doi>10.1016/0164-1212(84)90002-5]
• 28: LENZERINI, M., AND SANTUCCI, G. 1983. Cardinality constraints in the entity-relationship model, in The Entity-Relationship Approach to Software Engineering, G. C. Davis et al., Eds. Elsevier North-Holland, New York, pp. 529-549.
• 29: Y. Edmund Lien, Hierarchical schemata for relational databases, ACM Transactions on Database Systems (TODS), v.6 n.1, p.48-69, March 1981 [doi>10.1145/319540.319546]
• 30: Tok Wang Ling, A Normal Form For Entity-Relationship Diagrams, Proceedings of the Fourth International Conference on Entity-Relationship Approach, p.24-35, October 29-30, 1985
• 31: David Maier, Theory of Relational Databases, Computer Science Pr, 1983
• 32: James Martin, Strategic Data Planning Method, Prentice Hall PTR, Upper Saddle River, NJ, 1982
• 33: James Martin, Managing the Data Base Environment, Prentice Hall PTR, Upper Saddle River, NJ, 1983
• 34: MCGEE, W. 1974. A contribution to the study of data equivalence. In Data Base Management, J. W. Klimbie and K. L. Koffeman, Eds. North- Holland, Amsterdam, pp. 123-148.
• 35: Dennis McLeod , Roger King, Applying a Semantic Data Model, Proceedings of the 1st International Conference on the Entity-Relationship Approach to Systems Analysis and Design, p.193-210, January 1980
• 36: NAVATHE, S., AND CHENG, A. 1983. A methodology for database schema mapping from extended entity relationship models into the hierarchical model. In The Entity-Relationship Approach to Software Engineering, G. C. Davis et al., Eds. Elsevier North-Holland, New York.
• 37: Shamkant B. Navathe , Suresh G. Gadgil, A Methodology for View Inegration in Logical Database Design, Proceedings of the 8th International Conference on Very Large Data Bases, p.142-164, September 08-10, 1982
• 38: Shamkant B. Navathe , T. Sashidhar , Ramez Elmasri, Relationship Merging in Schema Integration, Proceedings of the 10th International Conference on Very Large Data Bases, p.78-90, August 27-31, 1984
• 39: NAVATHE, S., ELMASRI, R., ANO LARSON, J. 1986. Integrating user views in database design. IEEE Computer 19, 1, 50-62.
• 40: NIJSSEN, G., VAN ASSCHE, F., AND SNIJDERS, J. 1979. End user tools for information systems requirement definition. In Formal Models and Practical Tools for Information System Design, H. Schneider, Ed. North-Holland, Amsterdam.
• 41: Ole Oren, Integrity Constraints in the Conceptual Schema Language SYSDOC, Proceedings of the Fourth International Conference on Entity-Relationship Approach, p.288-294, October 29-30, 1985
• 42: REINER, D., BRODIE, M., BROWN, G., FRIEDELL, M., KRAMLICH, D., LEHMAN, J., ANO ROSENTHAL, A. 1985. The database design and evaluation workbench (DDEW) project at CCA. Database Eng. 7, 4, 10-15.
• 43: Hirotaka Sakai, Entity-relationship approach to logical database design., Proc. of the third international conference on Entity-relationship approach to software engineering, p.155-187, December 1983, Anaheim, California, United States
• 44: Peter Scheuermann , Gerd Schiffner , H. Weber, Abstraction Capabilities and Invariant Properties Modelling within the Entity-Relationship Approach, Proceedings of the 1st International Conference on the Entity-Relationship Approach to Systems Analysis and Design, p.121-140, January 1980
• 45: SCHKOLNICK, M., AND SORENSON, P. 1980. DENORMALIZATION: A performance oriented database design technique, in Proceedings of the AICA 1980 Congress (Bologna, Italy). AICA, Brussels, pp. 363-377.
• 46: Henry C. Smith, Database design: composing fully normalized tables from a rigorous dependency diagram, Communications of the ACM, v.28 n.8, p.826-838, Aug. 1985 [doi>10.1145/4021.4024]
• 47: John Miles Smith , Diane C. P. Smith, Database abstractions: aggregation and generalization, ACM Transactions on Database Systems (TODS), v.2 n.2, p.105-133, June 1977 [doi>10.1145/320544.320546]
• 48: SWEET, F. 1985. Process-driven data design. Datamation 31, 16, 84-85, first of a series of 14 articles.
• 49: Toby J. Teorey , James P. Fry, Design of Database Structures, Prentice Hall Professional Technical Reference, 1982
• 50: Jeffrey D. Ullman, Principles of Database Systems, W. H. Freeman & Co., New York, NY, 1983
• 51: VERHEIJEN, G., AND VAN BEKKUM, J. 1982. NIAM: An information analysis method. In Information Systems Design Methodologies, Olle, Sol, and Verryn-Stuart, Eds. North-Holland, Amsterdam, pp. 537-590.
• 52: Neil W. Webre, An Extended Entity-Relationship Model and Its Use on a Defense Project, Proceedings of the Second International Conference on the Entity-Relationship Approach to Information Modeling and Analysis, p.173-193, October 12-14, 1981
• 53: Eugene Wong , Randy H. Katz, Logical Design and Schema Conversion for Relational and DBTG Databases, Proceedings of the 1st International Conference on the Entity-Relationship Approach to Systems Analysis and Design, p.311-322, January 1980
• 54: YANG, D., TEOREY, T., AND FRY, J. 1985. A practical approach to transforming extended ER diagrams into the relational model. Computing Research Laboratory CRL-TR-6-85, Electrical Engineering and Computer Science Dept., Univ., of Michigan, Ann Arbor.
• 55: S. Bing Yao, Principles of Database Design: Logical Organizations, Prentice Hall PTR, Upper Saddle River, NJ, 1985
• 56: Carlo Zaniolo , Miachel A. Meklanoff, On the design of relational database schemata, ACM Transactions on Database Systems (TODS), v.6 n.1, p.1-47, March 1981 [doi>10.1145/319540.319542]

INDEX TERMS

Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.1 Logical Design
          Subjects: Data models

General Terms:
Design, Management