Relational Model Concepts

• Tables consists of rows (records) and columns (attribute or field).

Components of the Relational Model

• Data structure includes tables, rows, and columns.
• Data manipulation can be performed with SQL operations for retrieving and modifying data.
• Data integrity has mechanisms for implementing business rules that maintain integrity of manipulated data.

Relation Requirements

• Relation is a named, two-dimensional table of data.
• It must have a unique name.
• Every attribute value must be atomic (not multivalued, not composite)
• Every row must be unique
• Attributes must have unique names
• The order of the columns and rows must be irrelevant.
• All relations are in first normal form.

Correspondence with E-R Model

• Relations or tables in the relational model correspond with entity types and many-to-many relationship types in the E-R Model
• Rows correspond with entity instances and many-to-many relationship instances
• Columns correspond with attributes
• The term "relation" in relational database is different from "relationship" in the E-R model.

Key Fields

• Keys are special fields that serve two purposes.
• Primary keys are unique identifiers of a relation, such as employee numbers or social security numbers.
• Foreign keys enable a dependent relation to refer to its parent relation.
• Keys can be simple or composite.
• Keys are used as indexes to speed up query responses.

Integrity Constraints

• Domain Constraints are allowable values for an attribute
• Entity Integrity means no primary key attribute may be null, and all primary key fields must have data.
• Action Assertions are business rules.

Referential Integrity

• Referential Integrity states any foreign key value MUST match a primary key value.
• For Delete Rules, there are three options:
  • Restrict: Don't allow delete of parent side if related rows exist in dependent side
  • Cascade: Automatically delete dependent side rows that correspond with the parent side row being deleted.
  • Set-to-Null: Set the foreign key in the dependent side to null if deleting from the parent side, but this is not allowed for weak entities.
• Referential integrity constraints can be implemented using foreign keys referencing primary keys.

Mapping Regular Entities to Relations

• Simple E-R attributes map directly onto the relation
• Composite attributes utilize only their simple, component attributes.
• Multivalued attributes create a separate relation with a foreign key taken from the superior entity.

Mapping Weak Entities to Relations

• Weak entities become a separate relation with a foreign key from the superior entity.
• The primary key is composed of a partial identifier of weak entity and the primary key of identifying relation (strong entity).

Mapping Binary Relationships to Relations

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

Mapping Associative Entities to Relations

• If the identifier is not assigned, the default primary key for the association relation consists of the primary keys of the two entities.
• If the identifier is assigned it is natural and familiar to end-users, the default identifier may not be unique

Mapping Unary Relationships to Relations

• One-to-Many relationships have a recursive foreign key in the same relation.
• Many-to-Many relationships require two relations.
  • one relation for the entity type
  • one relation for an associative relation in which the primary key has two attributes taken from the primary key of the entity

Mapping Ternary Relationships

• One relation for each entity, and one for the associative entity.
• The associative entity has foreign keys to each entity in the relationship.

Mapping Supertype/Subtype Relationships

• One relation for supertype and for each subtype.
• Supertype attributes include identifier and subtype discriminator that go into the supertype relation.
• Subtype attributes go into each subtype, the primary key of supertype relation becomes the primary key of subtype relation
• 1:1 relationship established between supertype and each subtype, with supertype as primary table

Data Normalization

• Data Normalization is a tool to validate and improve a logical design to satisfy constraints and avoid unnecessary data duplication.
• It decomposes relations with anomalies to produce smaller, well-structured relations.

Well-Structured Relations

• These contain minimal data redundancy and allow users to insert, delete, and update rows without inconsistencies.
• Insertion Anomaly: Adding new rows forces the user to create duplicate data
• Deletion Anomaly: Deleting rows may cause a loss of data that would be needed for future rows
• Modification Anomaly: Changing data in a row forces changes to multiple rows because of duplication.
• A table should not pertain to more than one entity type.

Functional Dependencies and Keys

• Functional Dependency means the value of one attribute determines the value of another attribute
• Candidate Key is a unique identifier, and one of the candidate keys will become the primary key.
• Each non-key field is functionally dependent on every candidate key.

Normal Forms

• First Normal Form: No multivalued attributes, and every attribute value is atomic. All relations are in 1st Normal Form.
• Second Normal Form: Is 1NF with every non-key attribute fully functionally dependent on the entire primary key. Meaning every non-key attribute must be defined by the entire key, not just part of the key, also no partial functional dependencies.
• Third Normal Form: Is 2NF with no transitive dependencies.
• Generally, 3rd normal form is considered sufficient.

Merging Relations

• This combines entities from multiple ER models into common relations
• Watch out for the following issues:
  • Synonyms-two or more attributes with different names, but same meaning
  • Homonyms-attributes with the same name, but different meanings
  • Transitive dependencies-even if relations are in 3NF prior to merging, they may not be after merging
  • Supertype/subtype relationships-may be hidden prior to merging.

Enterprise Keys

• Primary keys are unique in the whole database, not just within a single relation.
• Corresponds with the concept of an object ID in object-oriented systems.