Relational Algebra in Systems Analysis and Design

Relational Algebra

• Enables users to specify basic retrieval requests
• Operations produce new relations that can be further manipulated
• Fundamental operations: • Union (∪) • Set difference (-) • Selection (σ) • Projection (π) • Cartesian product (X)
• Additional operations: • Rename (ρ) • Intersection (∩) • Join • Division (÷)
• Type compatibility requirements: • Same degree • Corresponding attributes defined over the same domain

SQL

• Language for expressing queries on relations
• Based on relational algebra for sets and bags
• Supports creation and modification of relations
• Examples of SQL queries: • CREATE TABLE R () • INSERT INTO R VALUES (v1; … ; vn) • DELETE FROM R WHERE C • UPDATE R SET A = v WHERE C • SELECT .. FROM … WHERE…

Relations

• A two-dimensional table
• Attributes ≈ column names
• Tuples ≈ rows (not including header row)
• Database ≈ collection of relations
• Relation characteristics: • Each relation has a distinct name • Each attribute has a distinct name • Values of an attribute are from the same domain • Each tuple is distinct • Entity degree is the number of fields/attributes in schema • Entity cardinality is the number of tuples in relation

Relational Model Integrity Constraints

• Conditions that must hold on all valid relation instances
• Three main types: • Key constraints • Entity integrity constraints • Referential integrity constraints • Semantic integrity constraints
• Specified when schema is defined
• Checked when relations are modified

Relational Data Model Operations

• Two categories: • Retrieval operations (extract information) • Update operations (cause relation state changes)

EERD (Enhanced ER or Extended ER)

• EER diagrams extend ER diagrams to represent additional subgroupings
• Subclasses or subtypes
• Superclass/subclass relationships
• Inheritance: • All attributes of the superclass • All relationships of the superclass
• Specialization: defining subclasses of a superclass
• Generalization: reverse of specialization process

Constraints on Specialization and Generalization

• Three basic constraints: • Condition-Defined Constraint • Disjointness Constraint • Completeness Constraint

Lattices & Shared Subclasses

• A subclass may have further subclasses specified on it
• Forms a hierarchy or lattice
• Hierarchy: every subclass has only one superclass (single inheritance)
• Lattice: a subclass can be subclass of more than one superclass (multiple inheritance)

ER-to-Relational Mapping

• Step 1: Mapping of Regular Entity Types
• Step 2: Mapping of Weak Entity Types
• Step 3: Mapping of Binary 1:1 Relation Types
• Step 4: Mapping of Binary 1:N Relationship Types
• Step 5: Mapping of Binary M:N Relationship Types
• Step 6: Mapping of Multi-valued attributes
• Step 7: Mapping of N-ary Relationship Types
• Step 8: Mapping Super class/ Sub class relationship