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.