• Sub-question 1: the standard to implement

- Do we always have to denormalize a model? For what kind of project must we use denormalization techniques while others may not?
- Since denormalization has its gains and losses, how well should we denormalize a data model? Perhaps, the more complete we denormalize, the more complex, uncertain and poor the situation will be.

• Sub-question 2: the characteristics of normalization

-Does denormalization have several levels/forms the same as that of normalization? For instance: 1DNF, 2DNF...
- Given we can denormalize a data model, it may never be restored to the original one because to do normalization, one can have many ways while to build a data model, you can have multiple choices in determining entities, attributes, etc.””

Levels of Representation

• Conceptual modeling (defines scope and ontology -- relevant objects and properties, including relationships);
• Logical database design (defines domains, attributes, and relations and relationships among them -- whether expressed implicitly via structure, or explicitly via constraints -- so as to establish a logical model that is correct and consistent under the relational algebra (RA));
• Physical implementation (determines [throughput, response time, concurrency, availability, scalability, and recoverability -- all via choice of physical data structures that can be mapped to and from logical structure without information loss; resource allocation/deallocation for data structures, memory blocks, file structures, indexes, concurrency control methods, recovery methods, and locality of reference.

“The one critical component that connects logical database design to the physical implementation is the DBMS optimizer, which has four key functions:

• To translate any query language expression into an relational algebra (RA) expression (including relevant constraints);
• To optimize that RA expression;
• To map relevant attributes and relations to (or from) their physical implementations via optimal access methods (e.g., indexed, sequential, or hashed); and,
• To optimize RA operations on relations via choice of a suitable algorithm for processing the data.

The optimizer is the "glue" that binds the logical design to the physical implementation and, ultimately, determines the implementation characteristics above. Notice that it mustperform both purely logical and purely physical optimizations and connect them.” --David McGoveran

Normal Forms and Correctness

• A relation in 1NF has attributes drawing their values from simple domains, the values of which are treated as atomic ("non-decomposable") by the data sublanguage (i.e., even if they have internal structure/components, they are not accessible to the language).
• A relation in 5NF is one in which the only attribute dependencies that hold are functional dependencies of the non-key attributes on the primary key (PK) (informally, it represents a group of entities of a single type).

"Unlike a 1NF relation, a 5NF relation need not be susceptible to update anomalies if the engine of a true RDBMS is properly implemented. All RA operations (and in consequence relational expressions) should preserve information. Once this property of RDM is nailed down, all RA operations can be properly defined so that relational closure is obtained without possibility of loss of information. Although this is a consequence of the RDM, the RDM must be slightly enhanced by a minor re-definition of the operations such that they are closed over and produce 5NF relations and thus are information preserving. This is one of the objectives of my current work.” --David McGoveran

“Every update can be formulated in terms of a relational expression (a read-only) and relational assignment (necessarily an update). When a RDBMS processes a relational expression of even moderate complexity (e.g., involving a join), it often creates temporary relations and updates them. If there are non-relations in the database (e.g., SQL tables), voila: update anomalies! Accepting each intermediate result as a relation when it isn't just compounds information loss -- the more the nesting of expressions, the more denormalized the results -- either something valid (i.e., correct inference) is lost, or something invalid (i.e., an incorrect inference) is gained, or both. The consequences for query results may be difficult to detect, or can even be masked in SQL DBMSs. In the more obvious cases, vendors work hard to correct them internally. However, it can be shown that it is impossible to prevent all of them all of the time. If you value correctness, repairing the logical design (i.e., full normalization) is critical.” --David McGoveran

Anomalies vs. Redundancies

“... And usually, going to [higher normal form] ... will improve performance as well ... because it will reduce the size of your data.”

“Database normalization is the process of efficiently organizing data in a database ... Eliminating redundant data ... reduce the amount of space a database consumes and ensures that data is logically stored.”

“There are many physical structures that could be used to represent a logical relation in storage (e.g., a linked list with multiple orderings, or an indexed file storing the join of the relations and so on, all maintained transparently by the DBMS), but products do not offer such choices -- only one is usually possible (essentially one or two flat files, possibly ordered by some key in SQL DBMSs). Because of this 1:1 mapping of a logical model to one specific physical representation, practitioners end up modifying the former to manifest a change in the latter, which creates the (false) impression that denormalization improves performance.” --David McGoveran

Conclusion