Names of database relations, their database attributes and value domains, together with applicable database integrity constraints.
Database normalization organizes relational schemas using their dependencies to reduce redundant facts and update anomalies. If a table stores
(student,course,instructor,office) and each instructor has one office, repeating the office in every enrolment creates multiple places to update. Splitting instructor-office facts into a separate relation can remove that redundancy. A decomposition must be checked for lossless join and, when needed, dependency preservation; merely creating more tables is not a correctness argument. Different normal forms impose different conditions on functional dependencies and other dependency types.A decomposition has a lossless join when projecting any permitted Database relation onto the component schemas and taking their natural join reconstructs exactly the original relation. Every original tuple reappears; the substantive requirement is that no spurious tuples are introduced. The dependencies defining permitted instances are essential to the claim.
For example, decompose into and . If is a functional dependency, this is lossless. Any joined tuple combines and from original rows; the dependency forces every row with that to have the same , so the combined tuple was already present. Without that dependency, original rows and yield two additional crossed combinations on rejoining. Removing redundancy is therefore insufficient: the decomposition must preserve which facts belonged together. A binary decomposition is lossless under functional dependencies when the common attributes determine one of its component schemas.
A decomposition in which the database integrity constraints can be enforced using constraints on individual component database relations, without computing their relational join.
A decomposition of a relational schema whose component relational projections always reconstruct the original database relation by natural join, without extra database tuples.
A relational schema is in fourth normal form if the determinant of every non-trivial multivalued dependency is a superkey. This implies Boyce-Codd normal form because every functional dependency implies a multivalued dependency.
A relational schema is in Boyce-Codd normal form if the determinant of every non-trivial functional dependency is a superkey.
For database tuples agreeing on , requires the combinations obtained by swapping their values on and on the remaining database attributes to occur too. A functional dependency implies the corresponding multivalued dependency; complementation exchanges with the remaining database attributes. A multivalued dependency is trivial if or is the full relational schema.
A database integrity constraint saying that any two database tuples agreeing on database attributes also agree on database attributes .
The set of database attributes determined by under given functional dependencies. Start from and repeatedly add the right-hand side of each functional dependency whose left-hand side is already included.
A foreign key is a database integrity constraint requiring designated database attributes to match a referenced identifier in a database relation. The referenced database relation may be the same one: a self-reference can represent each employee's manager. The identifier being referenced must have the required uniqueness, but the referencing database attributes need not be unique; several employees can have the same manager.[ref]
Constraints in the PostgreSQL documentation. version 18, section 5.5.5, foreign keys and self-referential tree example
For a simple example, let
Instrument(id) contain identifiers . A measurement carrying instrument_id=4 has a matching scientific instrument; one carrying instrument_id=7 violates the foreign key. Deleting scientific instrument four while measurements still refer to it requires a stated policy: reject the deletion, cascade a suitable deletion, or apply another permitted referential action.[ref]SQL NULL and compound foreign keys require an explicit matching policy. A nullable simple reference can represent no parent, whereas
NOT NULL can require a reference. A foreign key enforces reference existence; it does not by itself prove that a self-referencing structure has no directed cycles.A set of database attributes that determines every database attribute of a database relation under its database integrity constraints.
An inclusion-minimal superkey: removing any of its database attributes destroys the superkey property. Minimal means inclusion-minimal, not necessarily smallest in size.
Articles by others on the same topic
There are currently no matching articles.