Database Design

A database is a computer representation of information that exists in the real world. Like a painting, a database tries to imitate reality. Designing a database is an art form, and a successful database bears the mark of a thoughtful, creative designer.
idms19
A database is a computer representation of information that exists in the real world. Like a painting, a database tries to imitate reality. Designing a database is an art form, and a successful database bears the mark of a thoughtful, creative designer.
For a given database problem, there may be several solutions. While some designs are clearly better than others, there is no right or wrong design. The structure of your database will therefore be determined not only by the requirements of the business but also by your individual style as a designer. As you develop and refine the design for a database, let your intuition be your guide.
The purpose of creating a database is to satisfy the information requirements of business application programs. Before users can run their application programs, the database administrator (DBA) must design and implement the corporate database. As the DBA or database designer, you are responsible for database design and implementation.
Data models
To design a database, you must develop two different data models:
  • The
    logical model
    describes all corporate information to be maintained in the database. This model represents the way the user perceives the data.
  • The
    physical model
    describes how the data is stored and accessed by the system. The physical design for a database builds on the logical model. During the physical design process, you tailor the logical design to specific application performance requirements and plan the best use of storage resources.
Iterative process
Creating a design for a database is an iterative process. After you have developed the logical and physical models, you need to review the design process and the available documentation with users in your corporation. As users make suggestions for improvement, you should make appropriate changes to the design. Review the design repeatedly until it is acceptable to the user community.
The following topics are discussed on this page:
Design Implementation
The database design you create can be implemented using either of two implementation languages provided by CA IDMS/DB:
  • SQL DDL
  • Non-SQL DDL
Design considerations are documented in this section.
For complete SQL DDL statements, see the CA IDMS SQL Reference page. For complete non-SQL DDL statements, see the CA IDMS Database Administration page.
Syntax Diagram Conventions
The syntax diagrams presented in this section use the following notation conventions:
UPPERCASE OR SPECIAL CHARACTERS
Represents a required keyword, partial keyword, character, or symbol that must be entered completely as shown.
lowercase
Represents an optional keyword or partial keyword that, if used, must be entered completely as shown.
italicized lowercase
Represents a value that you supply.
lowercase bold
Represents a portion of the syntax shown in greater detail at the end of the syntax or elsewhere in the document.
◄─
Points to the default in a list of choices.
►►────────────────────
Indicates the beginning of a complete piece of syntax.
────────────────────►◄
Indicates the end of a complete piece of syntax.
─────────────────────►
Indicates that the syntax continues on the next line.
►─────────────────────
Indicates that the syntax continues on this line.
────────────────────►─
Indicates that the parameter continues on the next line.
─►────────────────────
Indicates that a parameter continues on this line.
►── parameter ─────────►
Indicates a required parameter.
►──┬─ parameter ─┬─────► └─ parameter ─┘
Indicates a choice of required parameters. You must select one.
►──┬─────────────┬─────► └─ parameter ─┘
Indicates an optional parameter.
►──┬─────────────┬─────► ├─ parameter ─┤ └─ parameter ─┘
Indicates a choice of optional parameters. Select one or none.
┌─────────────┐ ►─▼─ parameter ─┴──────►
Indicates that you can repeat the parameter or specify more than one parameter.
┌─── , ─────────┐ ►─▼─ parameter ───┴──────►
Indicates that you must enter a comma between repetitions of the parameter.
Sample Syntax Diagram
The following sample explains how the notation conventions are used:
!Admin_DB_Design_Sample_Syntax.png