Relational Model: Data Structure, Manipulation

• The relational model comprises data structure, manipulation, and integrity.

Components of the Relational Model

• The data structure involves tables (relations) with rows and columns.
• Data manipulation uses powerful SQL operations for data retrieval and modification.
• Data integrity employs mechanisms to implement business rules, maintaining integrity of manipulated data.

Defining Relations

• A relation is a named, two-dimensional data table consisting of rows (records) and columns (attributes or fields).
• To qualify as a relation, a table must adhere to the following:
• Have a unique name.
• Ensure every attribute value is atomic (not multivalued or composite).
• Maintain unique rows with no identical rows across all fields.
• Ensure attributes (columns) have unique names.
• Not have importance based on the order of columns and rows.
• All relations are in the 1st Normal Form.

Relations and E-R Model Correspondence

• Relations (tables) correspond with entity types and many-to-many relationship types in the E-R model.
• Rows align with entity instances and many-to-many relationship instances.
• Columns align with attributes.
• The term "relation" in relational databases should not to be confused with "relationship" in the E-R model.

Key Fields

• Keys are special fields which uniquely identify records and establish relationships between tables
• Primary keys are unique identifiers for a relation, ensuring unique rows using examples like employee or social security numbers.
• Foreign keys enable a dependent relation to reference its parent relation.
• Keys can be simple (single field) or composite (multiple fields).
• Keys also serve as indexes to accelerate response times for user queries

Integrity Constraints

• Domain constraints define allowable values for an attribute.
• Entity integrity ensures no primary key attribute is null and all primary key fields have data.
• Action assertions are business rules derived from Chapter 3
• Referential integrity dictates that a foreign key value must match a primary key value in the related table unless the foreign key is null.
• Restrict prevents deletion of a "parent" side if related rows exist in the "dependent" side.
• Cascade automatically deletes "dependent" side rows corresponding to the deleted "parent" side row.
• Set-to-Null sets the foreign key in the dependent side to null if deleting from the parent side which is disallowed for weak entities.

SQL Table Definitions

• Referential integrity constraints are implemented with foreign keys referencing primary keys.

Transforming EER Diagrams into Relations

• Regular entities' simple E-R attributes map directly onto the relation.
• Composite attributes use only their simple, component attributes.
• A multivalued attribute becomes a separate relation with a foreign key from the superior entity.

Mapping Weak Entities

• Weak entities become a separate relation referencing the superior entity via a foreign key.
• The primary key for the weak entity must include a partial identifier and the primary key of the identifying relation.

Mapping Binary Relationships

• One-to-Many relationship: the primary key on the one side becomes a foreign key on the many side.
• Many-to-Many relationship: Requires creating a new relation with the primary keys of the two entities acting as its primary key.
• One-to-One Relationship: the primary key on the mandatory side becomes a foreign key on the optional side.

Mapping Associative Entities

• For entities without an identifier, the default primary key includes the primary keys of the associated entities.
• If an identifier is assigned, using one will be considered natural and familiar, but needs to be unique.

Mapping Unary Relationships

• One-to-Many associations use a recursive foreign key within the same relation.
• Many-to-Many associations need two relations, one for the entity type and another associative one using two attributes (its primary key) taken from the original entity's one

Mapping Ternary Relationships

• Involves one relation for each entity and an additional one for the associative entity.
• The associative entity holds foreign keys referencing each entity involved in the relationship.

Mapping Supertype/Subtype Relationships

• Creates one relation for the supertype and each subtype.
• Supertype attributes are inherited by the supertype relation.
• Subtype attributes go to into their specific subtypes.
• A 1:1 relation is established between supertype and subtypes, with the former being the primary table.

Data Normalization

• Validation and improvement of a logical design ensures satisfaction of constraints while eliminating unneeded data duplication.
• Transforms anomalous relations into smaller, well-structured relations.

Understanding Well-Structured Relations

• A well-structured relation will have minimal data redundancy.
• The design of the Relation also allows users to insert, delete, and update rows minus data inconsistencies.
• The Goal of a well-structured relation is to reduce or eliminate anomalies.
• Insertion anomalies happen when rows force duplicate data entry by user.
• Deletion anomalies results in unintended data loss.
• Modification anomalies occurs when changes prompts more changes due to duplication.
• The general rule of thumb is: A table should pertain to one entity type.

Functional Dependencies

• Functional dependency exists when the value of one attribute (the determinant) determines the value of another attribute.
• A candidate key is a unique identifier. One of the candidate keys becomes the primary key
• Each non-key field is functionally dependent on every candidate key.

Normal Forms

• First Normal Form (1NF): No multivalued attributes and every attribute value is atomic, meaning it cannot be further subdivided.
• Second Normal Form (2NF): Meets 1NF criteria plus every non-key attribute is fully functionally dependent on the ENTIRE primary key
• Non-key attributes must be defined by the entire key.
• Second Normal Form also means that there are no partial functional dependencies.
• Third Normal Form (3NF): Meets 2NF with no transitive dependencies, and functional dependencies exist only on non-primary key attributes.
• It is generally considered sufficient as a normal form.

Merging Relations

• It Combines entities from multiple ER models into common relations (View Integration).
• When merging entities, watch out for synonyms (attributes with different names but same meaning) and homonyms (attributes with same name but different meanings)
• Watch for transitive dependencies, since if relations are in 3NF prior to merging, they may not be after merging
• Be aware of Supertype/subtype relationships, since they may be hidden prior to merging.

Enterprise Keys

• Primary keys are unique in the whole database, unlike relation-specific keys.
• The keys Correspond with the object ID concept in object-oriented systems.