Database System: Chapter 7 - Functional Dependencies includes Informal Design Guidelines for Relational Databases (Semantics of the Relation Attributes, Redundant Information in Tuples and Update Anomalies, Null Values in Tuples, Spurious Tuples), Functional Dependencies (FDs).
Chapter 7: Functional Dependencies presents about Informal Design Guidelines for Relational Databases (Semantics of the Relation Attributes, Redundant Information in Tuples and Update Anomalies, Null Values in Tuples, Spurious Tuples), Functional Dependencies (FDs).
GUIDELINE 3: Relations should be designed such that their tuples will have as few NULL values as possible
Attributes that are NULL frequently could be placed in separate relations (with the primary key)
Reasons for nulls:
attribute not applicable or invalid
attribute value unknown (may exist)
value known to exist, but unavailable
Relation Decomposition and Insufficiency of Normal Forms (cont.):
Attribute preservation condition: Each attribute in R will appear in at least one relation schema Ri in the decomposition so that no attributes are “lost”.
Another goal of decomposition is to have each individual relation Ri in the decomposition D be in BCNF or 3NF.
Additional properties of decomposition are needed to prevent from generating spurious tuples
For each weak entity type W in the ER schema with owner entity type E, create a relation R and include all simple attributes (or simple components of composite attributes) of W as attributes of R.
In addition, include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s).
The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W, if any.
Informal design guidelines for relational databases: Semantics of the relation attributes, reducing the redundant values in tuples, reducing the null values in tuples, disallowing the possibility of generating spurious tuples.