AM

Week_2_Lecture_-_The_Relational_Model

CO162 DATABASE SYSTEMS

Lecture 2: The Relational Model

Learning Objectives

  • Understand Data Models and the Objectives of Relational Model

  • History of the Relational Model

  • Terminology of Relational Model

  • Data Representation in Relational Model

  • Properties of Database Relations

  • Identifying Relational Keys

    • Candidate Keys

    • Primary Keys

    • Foreign Keys

  • Integrity Rules

    • Entity Integrity

    • Referential Integrity

Data Models

  • Components of a Data Model:

    • Structural Part: Rules for constructing databases.

    • Manipulative Part: Defines allowed operations on data.

    • Integrity Rules: Ensures data accuracy.

  • Definition: Integrated collection of concepts for describing data, relationships, and constraints.

Representation of Data Models

  • Purpose: To represent and clarify data understanding.

  • Types of Data Models:

    • External Model: User-specific view.

    • Conceptual Model: Logical community view, independent of DBMS.

    • Internal Model: Physical representation for DBMS understanding.

Examples of Older Data Models

  • Hierarchical Model: Used tree structures for parent/child relationships.

  • Network Model: Also record-based, relationships represented by sets.

The Relational Model

  • Proposed by: Edgar Codd in 1970.

  • Key Features:

    • Based on 2D tables, set theory, and relational algebra.

    • Logical design decoupled from physical implementation.

    • Dominant data-processing software today (RDBMS).

Objectives of the Relational Model

  • Data Independence: Applications unaffected by internal data representation changes.

  • Consistency and Redundancy Management: Introduction of normalisation.

  • Use of Set-oriented Data Manipulation Languages.

Relational Model Terminology

  • Relation: A table with rows and columns.

  • Attributes: Named columns (can appear in any order).

  • Tuples: Rows in a relation.

  • Degree of a Relation: Number of attributes.

  • Cardinality: Number of tuples.

ANSI-SPARC DBMS Architecture

  • Three Levels of Abstraction:

    • External Level: Users' specific views of the database.

    • Conceptual Level: Community view of stored data and relationships.

    • Internal Level: Physical data storage representation.

  • Notes: One conceptual/internal level but multiple external views.

Attributes and Domains

  • Domain: Set of allowable values for an attribute.

  • Examples:

    • BranchNo: Range B1-B999, (varchar, size 4)

    • Street: UK street names, (varchar, size 25)

    • City: UK city names, (varchar, size 15)

    • etc.

Alternative Terminology for Relational Model

  • Relation: Table, File

  • Tuple: Row, Record

  • Attribute: Column, Field

Properties of Relations

  • Uniqueness: Each relation must have a unique name.

  • Distinct Names: Attributes must have distinct names within a relation.

  • Same Domain: Values must come from the same domain.

  • Order Irrelevance: Order of attributes and tuples is insignificant.

  • Atomic Values: Each cell contains one value; no multiple values allowed.

  • Distinct Tuples: No duplicate tuples allowed.

Relational Keys

Superkey

  • Definition: Attributes that uniquely identify each tuple.

  • Issue: May contain unnecessary (redundant) attributes.

Candidate Key

  • Minimal Superkey: Must uniquely identify a tuple without unnecessary attributes.

  • Examples: BranchNo is a candidate key, while (BranchNo, Postcode) is not.

Properties of Candidate Keys

  • Uniqueness: No two rows can have the same key values.

  • Irreducibility: Cannot remove attributes without losing identification.

Primary Key

  • Definition: A candidate key selected to uniquely identify a tuple in a relation.

  • Example: BranchNo as Primary Key in the BRANCH relation.

Alternate Key

  • Definition: Candidate keys not selected as Primary Key.

  • Example: NatInsNo as an alternate key in STAFF relation if StaffNo is the primary key.

Foreign Key

  • Definition: An attribute in one relation that matches a candidate key in another relation.

  • Purpose: Establishes relationships between tables; allows duplicates if it references a PK.

Relational Integrity

  • Integrity Constraints: Ensure data accuracy in the relational data model.

  • Types of Integrity Rules:

    • Entity Integrity: Primary key must be unique and not null.

    • Referential Integrity: Validates relationships between foreign keys and corresponding primary keys in linked tables.

NULL Values

  • Definition: Represents unknown or non-applicable values.

  • Difference: Null is not the same as zero or empty string.

Summary

  • Understanding of data models, relational model properties, relational keys, entity integrity, and referential integrity is crucial.