Note: This is a re-write of an earlier post (which now links here), to bring it into line with the current understanding of the RDM derived from McGoveran formalization and interpretation of Codd's work[1]. Reference [9] is also an important re-write and is recommended pre-requisite for this post.

Continued from Part 1

 “The term database design can be used to describe many different parts of the design of an overall database system. Principally, and most correctly, it can be thought of as the logical design of the base data structures used to store the data. In the relational model these are the tables and views ... However, the term database design could also be used to apply to the overall process of designing, not just the base data structures, but also the forms and queries used as part of the overall database application within the database management system(DBMS). The process of doing database design generally consists of a number of steps which will be carried out by the database designer. Usually, the designer must:

• Determine the data to be stored in the database.
• Determine the relationships between the different data elements.
• Superimpose a logical structure upon the data on the basis of these relationships.

Within the relational model the final step above can generally be broken down into two further steps, that of determining the grouping of information within the system, generally determining what are the basic objects about which information is being stored, and then determining the relationships between these groups of information, or objects.”
--What is a Relational Database, Quora.com

Enterprise-specific Models and Data Model

Logical Relations and Stored Data

Note: SQL has muddied the waters by calling its tables -- which are not relations - with stored data "base tables". If and how data is actually stored is part of the implementation, not the model -- physical independence (PI) being one of the core advantages of the RDM[5]. We refer to the common failure to distinguish between relations and the representation of their data in storage logical-physical confusion (LPC)[6].

Business Rules, Predicates and Constraints

Database design is formalization of a conceptual model consisting of business rules that specify properties of related entity groups that form a multigroup, namely:

• First order properties (1OP) of individual entities;
• Second order properties (2OP) of individual entities that are relationships among 1OPs;
• Third order properties (3OP) of groups that are relationships among all of an entity group members;
• Fourth order properties (4OP) of the multigroup that are relationships among the groups[7,8].

Some properties are relationships.

• Properties formalize as domains;

• Entity groups formalize as relations: properties in a group's context formalize as attributes defined on the corresponding domains;

- Property rules as domain constraints;
- Property in context rules as attribute constraints;
- Entity rules as tuple constraints;
- Group rules as multi-tuple constraints;
- Multigroup rules as database (multirelation) constraints.

Business rules specify in specialized natural language the properties/relationships of entities, groups and multi-groups that formalize as first order predicates.  Expressed as constraints in a FOPL-based relational data sublanguage, they can be implemented by algorithms to ensure  semantic consistency with the rules (i.e., constraints capture the meaning of the model assigned by the designer to the data)[9].

For each relation, the domain, attribute, tuple and multituple constraints jointly correspond to the relation predicate (RP). All RPs jointly with the database constraints correspond to the database predicate (DBP) [10].

The database designer must keep the conceptual (model) and logical (database) levels distinct in mind and avoid conceptual-logical conflation (CLC).

Note: Properties (e.g., $amount) formalize as abstract domains (MONEY). Properties in context (e.g., salary in context of employees, budget in context of departments) formalize as attributes (SALARY, BUDGET), derived from domains by imposing attribute constraints. Attributes are domain representations (i.e., simple functions -- 1:1 mappings) in relations. Try to express all these constraints in SQL and see how relational it is[11]. 

Data Sublanguage, DBMS Language and Host CCL

As we explained elsewhere, a DBMS language consists of two components (1) a relationally complete data sublanguage that expresses constraints/RA operations) and (2) language for other data management functions (data definition, concurrency control, security, transactions, storage management, and so on). The DBMS language is hosted by a  computationally complete language (CCL). Only the data sublanguage is restricted to FOPL, the other DBMS language component is based on higher logic, which is why they are implemented and invoked in the CCL, both of which must not subvert the data sublanguage and lose relational advantages[12].

The following functions are the responsibility of applications:

• User communication with the DBMS;
• Computation;
• Presentation of results to users

and are implemented in the host CCL.

Guaranteed Correctness

Under the SST/FOPL theoretical foundation of the RDM, correctness of query results is guaranteed as follows:

• Logical validity is guaranteed by the RDBMS;
• Semantic consistency is guaranteed by database design adherence to three principles:

- Principle of Expressive Completeness (POEC);
- Principle of Representational Parsimony (PORP);
- Principle of Orthogonal Design (POOD).

This produces a set of independent base relations (POOD), which is minimally necessary (PORP) to derive via RA operations all relations meaningful to the user applications (POEC) which the database is intended to satisfy[13].

Base relations are independent in the sense that none has a RP derivable from the others'. Base does not mean stored:

"Codd's original meaning of base relations is "members of this base set" from which all other meaningful relations are RA-derived. Although their data is usually stored, that was not what he meant. Entity groups are modeling primitives (i.e., non-decomposable) and, therefore, base relations cannot (must not) be analyzed into parts. A projection or restriction is not a "decomposition" -- as they are commonly viewed -- but a relation derived from, that depends on, the base relation. In a sense, base relations have a kind of atomicity which avoids circular definitions. If, in the conceptual model, object group A is defined in terms of group B and B in terms of C, and C in terms of A, nothing is defined." --David McGoveran

Note: I will not publish or respond to anonymous comments. If you have something to say, stand behind it. Otherwise don't bother, it'll be ignored.